You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2019/02/23 16:41:07 UTC
[GitHub] michaeljinxq opened a new issue #1913: Performance issue
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:
users@infra.apache.org
With regards,
Apache Git Services