michaeljinxq opened a new issue #1913: Performance issue URL: https://github.com/apache/incubator-shardingsphere/issues/1913 Which version of Sharding-Sphere do you using? 3.1.0 【测试结构】 (1)Sysbench ------> mysql (2)Sysbench ------> proxy ------> mysql (3)Sysbench ------> proxy ------> mysql 1. 代表直接sysbench直接写MySQL 2 代表通过中间件sharding-proxy写入MySQL(不分表) 3 代表通过sharding-proxy写mysql(数据分片10份) 以下测试中都是这3种场景 服务器信息 CPU | Intel(R) Xeon(R) CPU E5-4610 v2 @ 2.30GHz 64核 内存 | 256G 硬盘 | 480G*8 SSD raid10 1张逻辑表(sbtest1)，数据量1亿，mysql5.7.24，innodb_buffer_pool_size=10G centos7.4 【问题1】 sysbench经过中间件向mysql插入数据速度很慢，下面是具体数据 (1)Sysbench------> mysql #未经过中间件每秒可以插入48000条记录 (2)Sysbench------>proxy------>mysql #经过中间件未分表，每7秒插入2500条记录 (3)Sysbench------>proxy------>mysql #经过中间件分10张表，每秒插入2500条记录 sysbench1.1.0，初始化数据的命令如下 /opt/sysbench/bin/sysbench oltp_read_write.lua --db-driver=mysql --mysql-host=xx.xx.xx.xx --mysql-port=3308 --mysql-db=sysbench --mysql-user=admin --mysql-password='password' --table_size=100000000 --tables=1 --threads=8 --report-interval=1 --auto_inc=off prepare 【问题2】 经过中间件性能损耗较大 测试命令如下 /opt/sysbench/bin/sysbench oltp_update_index.lua --db-driver=mysql --mysql-host=${ip} --mysql-port=${port} --mysql-db=sysbench2 --mysql-user=sysbench --mysql-password=password --table_size=100000000 --tables=1 --threads=${thread_num} --time=3600 --report-interval=10 run oltp_update_index 测试，逻辑表数据 1亿，测试时间10分钟 测试线程 | 类型 | tps | 响应时间ms | Proxy版本 32 | 3306 | 17267 | 1.85 | N 32 | 3308 | 10763 | 2.97 | 3.0.0 32 | 3309 | 11053 | 2.89 | 3.0.0 32 | 3310 | 13447 | 2.38 | 3.1.0 32 | 3311 | 11914 | 2.69 | 3.1.0 其中3306表示直接连接mysql压测，3308/3310表示经过中间件未分表，3309/3311表示经过中间件分了10张表 以32并发线程为例，proxy版本为3.1.0，如上表所示 经过中间件不分表，tps降低22.12%，响应时间增加了28.65% 经过中间件分表，tps降低31%，响应时间增加了45.4% oltp_update_index 测试，逻辑表数据 1亿，测试时1个小时 测试线程 | tps | 响应时间ms | Proxy版本 32|17832|1.79|N #未经过中间件，直接压测mysql 32|7665|4.17|3.1.0 32|12339|2.59|3.1.0 测试过程中发现mysql层面的活跃线程数不稳定，间隔几秒就会变成1，是否和proxy的连接池有关系？ 下面是配置文件 Server.yaml ###################################################################################################### # # If you want to configure orchestration, authorization and proxy properties, please refer to this file. # ###################################################################################################### #orchestration: # name: orchestration_ds # overwrite: true # registry: # serverLists: localhost:2181 # namespace: orchestration # authentication: username: admin password: Admin123%r # props: # max.connections.size.per.query: 1 # acceptor.size: 16 # The default value is available processors count * 2. # executor.size: 16 # Infinite by default. # proxy.transaction.enabled: false # proxy.opentracing.enabled: false sql.show: false schemaName: sysbench dataSources: sysbench: url: jdbc:mysql://xx.xx.xx:3306/sysbench?serverTimezone=UTC&useSSL=false username: sysbench password: password autoCommit: true connectionTimeout: 30000 idleTimeout: 60000 maxLifetime: 1800000 maximumPoolSize: 65 shardingRule: tables: sbtest1: actualDataNodes: sysbench.sbtest_${0..9} tableStrategy: inline: shardingColumn: id algorithmExpression: sbtest_${id % 10} keyGeneratorColumnName: id bindingTables: - sbtest1 defaultDatabaseStrategy: none: defaultTableStrategy: none: defaultKeyGeneratorClassName: io.shardingsphere.core.keygen.DefaultKeyGenerator
---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: [email protected] With regards, Apache Git Services
