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
   ---------------------------
   ![image](https://user-images.githubusercontent.com/16129632/100438835-aa6b5a80-30dd-11eb-8129-7c3b38cd65be.png)
   
   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