You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2020/11/27 10:30:19 UTC
[GitHub] [shardingsphere] iamif3000 opened a new issue #8383: [question] the insert performance is bad. please help to check the config.
iamif3000 opened a new issue #8383:
URL: https://github.com/apache/shardingsphere/issues/8383
Hi,
I made a performance test on sharding-proxy(4.1.1), the performance is a bad. And the result looks strange.
server.yaml
-----------------------
```
authentication:
users:
root:
password: root
sharding:
password: sharding
authorizedSchemas: master_slave_db
props:
max.connections.size.per.query: 1
acceptor.size: 4
executor.size: 100
proxy.frontend.flush.threshold: 128
proxy.transaction.type: LOCAL
proxy.opentracing.enabled: false
proxy.hint.enabled: false
query.with.cipher.column: true
sql.show: true
allow.range.query.with.inline.sharding: false
```
config-master_slave.yaml
---------------------------
```
schemaName: master_slave_db
dataSources:
master_ds:
url: jdbc:mysql://172.31.197.149:3306/proxy?serverTimezone=UTC&useSSL=false
username: proxy
password: p123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 1000
minPoolSize: 400
slave_ds_0:
url: jdbc:mysql://172.31.197.150:3306/proxy?serverTimezone=UTC&useSSL=false
username: proxy
password: p123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 1000
minPoolSize: 400
masterSlaveRule:
name: ms_ds
masterDataSourceName: master_ds
slaveDataSourceNames:
- slave_ds_0
```
Test env
--------------------
```
4 2core4G vms.
2 for mariadb8.5 master-slave cluster
1 for sharding-proxy
1 for jmeter
And the test is simple.
insert: insert into jmeter_test values(null, now(), now(), ..., unix_timestamp(), ...); // 25 now(), and 4 unix_time_stamp()
query: select id, field1, field29 from jmeter_test limit 100;
```
The test result
---------------------------

You can see that "select" works OK. But for "insert", the cpu of jmeter, proxy are both low.
So, my question is "is there any problem with my configuration? or is there any other config items for performance tuning?"
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] iamif3000 commented on issue #8383: [question] the insert performance is bad. please help to check the config.
Posted by GitBox <gi...@apache.org>.
iamif3000 commented on issue #8383:
URL: https://github.com/apache/shardingsphere/issues/8383#issuecomment-736202951
@tuohai666 Thanks for you help. I tuned the configuration and the performace is better, but still not good enough.
And I have another question: does sharding-proxy support native mysql/mariadb protocol in the backend db connections?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuohai666 commented on issue #8383: [question] the insert performance is bad. please help to check the config.
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #8383:
URL: https://github.com/apache/shardingsphere/issues/8383#issuecomment-735538824
@iamif3000 I noticed the env is 2core4G, so we should not use so many threads.
```
acceptor.size: 16
executor.size: 16
```
```
dataSources:
master_ds:
url: jdbc:mysql://172.31.197.149:3306/proxy?serverTimezone=UTC&useSSL=false
username: proxy
password: p123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 16
minPoolSize: 16
slave_ds_0:
url: jdbc:mysql://172.31.197.150:3306/proxy?serverTimezone=UTC&useSSL=false
username: proxy
password: p123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 16
minPoolSize: 16
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuohai666 commented on issue #8383: [question] the insert performance is bad. please help to check the config.
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #8383:
URL: https://github.com/apache/shardingsphere/issues/8383#issuecomment-735525706
Hi @iamif3000 , according to your test result, I'd suggest:
1. Set sql.show to false
2. Set acceptor.size to 100
You can test again with these parameters.
Additionally,
1. If you use jmeter jdbc directly, the SQL request will not be prepared statement. It's better to write a jdbc plugin with HikariCP to perform the test.
2. We have improve the performance after 4.1.1(https://github.com/apache/shardingsphere/pull/7602), you may alse want to test 5.0.0-alpha.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuohai666 commented on issue #8383: [question] the insert performance is bad. please help to check the config.
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #8383:
URL: https://github.com/apache/shardingsphere/issues/8383#issuecomment-736380750
@iamif3000 Can you provide the expected test results and the reason? So that we can estimate how to improve the performance.
Proxy does not support mysql protocol in the backend.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuohai666 closed issue #8383: [question] the insert performance is bad. please help to check the config.
Posted by GitBox <gi...@apache.org>.
tuohai666 closed issue #8383:
URL: https://github.com/apache/shardingsphere/issues/8383
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] iamif3000 commented on issue #8383: [question] the insert performance is bad. please help to check the config.
Posted by GitBox <gi...@apache.org>.
iamif3000 commented on issue #8383:
URL: https://github.com/apache/shardingsphere/issues/8383#issuecomment-737798856
@tuohai666 I tested the performance again on PM(40core128G). The performance of insert looks OK. But I found a bug. I'll report it.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuohai666 edited a comment on issue #8383: [question] the insert performance is bad. please help to check the config.
Posted by GitBox <gi...@apache.org>.
tuohai666 edited a comment on issue #8383:
URL: https://github.com/apache/shardingsphere/issues/8383#issuecomment-735525706
Hi @iamif3000 , according to your test result, I'd suggest:
1. Set sql.show to false
2. Set acceptor.size to 100
You can test again with these parameters.
Additionally,
1. If you use jmeter jdbc directly, the SQL request will not be prepared statement. It's better to write a jdbc plugin with HikariCP to perform the test.
2. We have improve the performance after 4.1.1(https://github.com/apache/shardingsphere/pull/7602), you may alse want to test 5.0.0-alpha.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org