How can I improve the performance of proxy?

[dobet]

Question

For English only, other languages will not accept.

Before asking a question, make sure you have:

• Googled your question.
• Searched open and closed GitHub issues.
• Read documentation: ShardingSphere Doc.

Please pay attention on issues you submitted, because we maybe need more details. If no response anymore and we cannot reproduce it on current information, we will close it.

Recently，I'm using sysbench testing shardingsphere-proxy，maxscale，and dble，I would like to compare the read/write splitting performance between several products。

Now, I used 3 8C 16G machines to form mysql 1 master and 2 slaves。

Then，I use syebench oltp_read_write to test.

this is sysbench run code:

sysbench oltp_read_write  --mysql-user=root --mysql-password=root --mysql-host=sharding-ip --mysql-port=3307 --mysql-db=s1_sbtest --histogram=on --report-interval=1 --time=300  --db-ps-mode=disable  --threads=300 --tables=250 --table_size=25000  --report-interval=1 --percentile=95 --skip-trx=on --mysql-ignore-errors=1062  --forced-shutdown=1 run

When I use sysbench to directly press mysql，tps is 2250 per sec. When I use sysbench to directly press shardingsphere-proxy, tps is only 2300 per sec. But when I use sysbench directly press dble/maxscale， tps have 2600 per sec.

Now, shardingsphere-proxy is 16C16G, dble is 8C8G, maxscale is 4C4G.

I compared and found that the network traffic of the shardingsphere-proxy can only run to 750M。 dble and maxscale can run to 900M。

so， I use arthas run profile，sharding1.html is first run shardingsphere-proxy‘s resutl. sharding-2.html is I change server.yaml proxy-frontend-executor-size to 500 result

sharding.zip

Now, I don't know how to improve the performance, plase help me

[FlyingZC]

@TeslaCN Hi, please have a look.

[TeslaCN]

Please remove the --db-ps-mode=disable.

[dobet]

I remove the --db-ps-mode, but the tps was not improve

[TeslaCN]

Could you try using MySQL JDBC Driver 5.1.49 instead of 8.x?

[dobet]

I use mysql jdbc driver 5.1.49 instead of 8.x , the result is still the same. And I change the cpu-cores to 24 cores, now cpu used 40%, but also run 750M

This is use 5.1.49 run profile

sharding-5.zip

[dobet]

Could you try using MySQL JDBC Driver 5.1.49 instead of 8.x?

I change the storage unit's poolSize, from 200 to 600, then tps up to 2600.

[dobet]

This is my config

server.yaml:

sqlParser:
  sqlCommentParseEnabled: false
  sqlStatementCache:
    initialCapacity: 2000
    maximumSize: 65535
  parseTreeCache:
    initialCapacity: 128
    maximumSize: 1024

props:
  max-connections-size-per-query: 1
  kernel-executor-size: 16  # Infinite by default.
  proxy-frontend-flush-threshold: 128  # The default value is 128.
  proxy-hint-enabled: false
  sql-show: false
  check-table-metadata-enabled: false
    # Proxy backend query fetch size. A larger value may increase the memory usage of ShardingSphere Proxy.
    # The default value is -1, which means set the minimum value for different JDBC drivers.
  proxy-backend-query-fetch-size: -1
  proxy-frontend-executor-size: 1000 # Proxy frontend executor size. The default value is 0, which means let Netty decide.
    # Available options of proxy backend executor suitable: OLAP(default), OLTP. The OLTP option may reduce time cost of writing packets to client, but it may increase the latency of SQL execution
    # and block other clients if client connections are more than `proxy-frontend-executor-size`, especially executing slow SQL.
  proxy-backend-executor-suitable: OLTP
  proxy-frontend-max-connections: 0 # Less than or equal to 0 means no limitation.
    # Available sql federation type: NONE (default), ORIGINAL, ADVANCED
  sql-federation-type: NONE
  proxy-mysql-default-version: 5.7.30 # In the absence of schema name, the default version will be used.
  proxy-default-port: 3307 # Proxy default port.
  proxy-netty-backlog: 1024 # Proxy netty backlog.

other config is defalut in server.yaml

and, I use distsql to create readwrite_splitting rules

1. create database s1_sbtest;
2. use s1_sbtest;
3. register storage unit write_ds ( URL="jdbc:mysql://master_ip:3306/sbtest?useSSL=false&useUnicode=true&characterEncoding=UTF-8", USER="root", PASSWORD=password, PROPERTIES("maxPoolSize"=600, "minPoolSize"=600));
4. register storage unit read_ds_1 ( URL="jdbc:mysql://slave1ip:3306/sbtest?useSSL=false&useUnicode=true&characterEncoding=UTF-8", USER="root", PASSWORD=password, PROPERTIES("maxPoolSize"=600, "minPoolSize"=600));
5. register storage unit read_ds_2 ( URL="jdbc:mysql://slave2ip:3306/sbtest?useSSL=false&useUnicode=true&characterEncoding=UTF-8", USER="root", PASSWORD=password, PROPERTIES("maxPoolSize"=600, "minPoolSize"=600));
6. create readwrite_splitting rule group_0 ( write_storage_unit=write_ds, read_storage_units(read_ds_1, read_ds_2), type(name="random") );

I don't change other config.

Now, 16 cpu-cores used 70%， how to reduce ？

Place give me some suggest。 tks

[TeslaCN]

Could you profile by https://github.com/async-profiler/async-profiler directly? Your flamegraph looks different.

[dobet]

Could you profile by https://github.com/async-profiler/async-profiler directly? Your flamegraph looks different.

this is my jfr file.

20230302-115629-output-1102034.zip

from the jfr, i found the cpu in MysqlIO.sendCommand and SQLStatementParserExecutor.parse

[TeslaCN]

It seems that prepared statement was not used in your tests.

[dobet]

It seems that prepared statement was not used in your tests.

I use prepared statement to test， but no effect.

I find the connection pool use HikariCP, maybe this is the reason？

proxy can use Durid？ Don't change code and retry compiled?

[dobet]

this is profile that use prepared statement.

sharding-10.zip

And the cpu also have 80%.

this is my jvm param, -Xmx8g -Xms8g -Xmn4g

[TeslaCN]

Could you try logging HikariCP pool status? It seems that there were so much yield.

And you may try perf top -F99 to get some information from OS level.

[dobet]

Could you try logging HikariCP pool status? It seems that there were so much yield.

And you may try perf top -F99 to get some information from OS level.

Have exception in log:

[ERROR] 2023-03-08 10:18:17.902 [ShardingSphere-Command-321] o.a.s.p.f.c.CommandExecutorTask - Exception occur: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '12621' for key 'PRIMARY' at jdk.internal.reflect.GeneratedConstructorAccessor40.newInstance(Unknown Source) at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499) at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:480) at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) at com.mysql.jdbc.Util.getInstance(Util.java:408) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530) at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1288) at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:788) at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1242) at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.impl.ProxyPreparedStatementExecutorCallback.execute(ProxyPreparedStatementExecutorCallback.java:43) at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:77) at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:70) at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:47) at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:90) at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:69) at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.syncExecute(ExecutorEngine.java:135) at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.parallelExecute(ExecutorEngine.java:131) at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.execute(ExecutorEngine.java:116) at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:67) at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.ProxyJDBCExecutor.execute(ProxyJDBCExecutor.java:75) at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.useDriverToExecute(ProxySQLExecutor.java:229) at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.doExecute(ProxySQLExecutor.java:186) at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.execute(ProxySQLExecutor.java:151) at org.apache.shardingsphere.proxy.backend.communication.DatabaseCommunicationEngine.execute(DatabaseCommunicationEngine.java:176) at org.apache.shardingsphere.proxy.frontend.mysql.command.query.binary.execute.MySQLComStmtExecuteExecutor.execute(MySQLComStmtExecuteExecutor.java:82) at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:110) at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:77) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) at java.base/java.lang.Thread.run(Thread.java:833)

[dobet]

This is my sysbench code.

sysbench oltp_read_write  --mysql-user=root --mysql-password=root --mysql-host=10.106.34.103  --mysql-port=3307 --mysql-db=s1_sbtest --histogram=on --report-interval=1 --time=300  --threads=300 --tables=250 --table_size=25000  --report-interval=1 --percentile=95 --skip-trx=on --mysql-ignore-errors=1062  --forced-shutdown=1 run

in maxscale or direct connection mysql, this code not error in log.

maybe because this exception ?

[dobet]

This is HirikaPool debug log when run sysbench

stdout.log

[TeslaCN]

I'm not sure why would this happened. https://stackoverflow.com/questions/63386337/hikari-add-connection-elided/64319099#64319099

You may try async-profiler wall event.

[dobet]

this is async-profiler wall result

20230310-032154-output-1225246.zip

[TeslaCN]

It seems that there were too many EventLoop thread. You may try reducing it by proxy-frontend-executor-size.

[dobet]

It seems that there were too many EventLoop thread. You may try reducing it by proxy-frontend-executor-size.

This is my new test. I change proxy-frontend-executor-size to deafult(0).

and this file hava cpu profile , wall profile and HikariCP log

default-size.zip

[TeslaCN]

It seems that there were too many EventLoop thread. You may try reducing it by proxy-frontend-executor-size.

This is my new test. I change proxy-frontend-executor-size to deafult(0).

and this file hava cpu profile , wall profile and HikariCP log

default-size.zip

I've checked the attachments. I found nothing abnormal overhead from it.