ShardingSphere-Proxy压测性能不及预期问题咨询

凡凡鲜 · 2022 年4 月 11 日 09:18

方便更快捷的说明问题，可以按需填写（可删除）

使用环境：

shardingsphere-5.1.0-shardingsphere-proxy

场景、问题：

压测工具：sysbench，读写主库
压测脚本
sysbench oltp_read_write --mysql-host=10.0.0.1 --mysql-port=3307 --mysql-user=root --mysql-password=root321 --mysql-db=sharding_db --tables=10 --table-size=1000000 --report-interval=10 --time=600 --threads=16 --max-requests=0 --percentile=99 --rand-type=uniform --range_selects=off --auto_inc=off run
数据量：10个表每个表100万
目前我们这边有一个项目需要用到分库分表，调研了一下ShardingSphere-Proxy ，发现server.xml 配置XA事务时，压测性能非常差，设置事务 defaultType 为LOCAL时，两个分片压测时，性能也只是同样数据量单个mysql 性能的一半，麻烦大佬们帮忙看看有什么配置不合理的地方？

已进行操作：

server.xml配置：
rules:

!AUTHORITY
users:
- root@%:root321
- sharding@:sharding
  provider:
  type: ALL_PRIVILEGES_PERMITTED
!TRANSACTION
defaultType: LOCAL
!SQL_PARSER
sqlCommentParseEnabled: true
sqlStatementCache:
initialCapacity: 2000
maximumSize: 65535
concurrencyLevel: 256
parseTreeCache:
initialCapacity: 128
maximumSize: 1024
concurrencyLevel: 4
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-opentracing-enabled: false
proxy-hint-enabled: false
sql-show: false
check-table-metadata-enabled: false
show-process-list-enabled: false

config配置：

现状：

shardingsphere-proxy压测性能混合读写模式下
tps不到500 qps不到7000

同样数据量的单个mysql性能混合读写模式下
从性能来看 mysql几乎是shardingsphere-proxy的两倍

jingshanglu · 2022 年4 月 11 日 09:35

threads=16 两种情况下 mysql 实例的资源使用情况是什么样的？ds 配置的minPoolSize 和 maxPoolSize 相对并发数有点高，可以适当调低试下

凡凡鲜 · 2022 年4 月 11 日 09:54

CPU 和内存已经io资源两种压测情况下都没有使用满 minPoolSize 和 maxPoolSize 这两个参数以及调成 1和50了效果是一样的

taojintianxia · 2022 年4 月 11 日 10:00

我们先说 LOCAL 的情况，压测的脚本没什么问题，我不知道凡凡同学现在机器的配置如何，如果配置尚可，可以考虑将 thread 提高一些，数据也会更好一些。

不过这里有个情况我们需要思考一下，就是「为什么要做数据 sharding」。以 sysbench 的表结构，单表 100w 是 MySQL 非常“舒服”的一个数据范围，这个数据量下 MySQL 的 CRUD 还都处于高效状态，没有做 sharding 的必要。以我测试的情况，凡凡同学你可以尝试将单表数据改为 4000w，最好也将 DataSource 设置为 4 个以上。这时候你就会感受到 MySQL “疲软” 的情况，以及 Sharding 的场景。当然，以我过去的经验，单表 7 个亿对比拆分成 16 个分片，那对比效果更明显了。

凡凡鲜 · 2022 年4 月 11 日 10:00

在sysbench read_only 只读模式下发现性能差距更大主要是latency 延迟比较严重
proxy：

mysql：

jingshanglu · 2022 年4 月 11 日 10:05

Proxy 的资源使用有瓶颈吗？

凡凡鲜 · 2022 年4 月 11 日 10:13

嗯后面大数据量我们肯定也会做压测，前期预计是在数据量比较小的情况下如果有20%的损耗，都是可以接受的，但是按目前压测来看差距有点儿大，所以想确定还有没有什么调优的办法

凡凡鲜 · 2022 年4 月 11 日 10:14

目前看是没有达到瓶颈的 cpu内存资源都没有

jingshanglu · 2022 年4 月 11 日 10:15

在数据量较小情况下，mysql 资源不存在瓶颈，proxy 需要和 Mysql 网络交互，如果有两个 Mysql 就多两次网络请求，这种情况理论上 proxy 性能不占优，当单 mysql 存在资源瓶颈时，proxy 才能充分利用起多个 mysql 实例的资源。

jingshanglu · 2022 年4 月 11 日 10:19

4000万数据，5分片场景，local 模型，以前测试的结果比单机 Mysql 不差的

凡凡鲜 · 2022 年4 月 11 日 10:29

好的这种情况我们也压测看看还有麻烦想确认一下目前我们计划的分片方式是表按库进行分片一个分库里放一个表，但是我看官方文档里默认都是一个库里又分了多个表，这两种方式拿一种更好，也就是会不会存在性能的差距什么的

凡凡鲜 · 2022 年4 月 11 日 10:33

还有一个问题想请教一下，目前我是单个sharding proxy 如果是部署多个sharding proxy 性能是否会有提升

jingshanglu · 2022 年4 月 12 日 05:38

当前的实现，如果是 XA，一个事务，每一个 DB 实例只使用一个连接，如果两个事务没有冲突，多个 proxy 会有一定的性能提升，如果两个事务操作同一数据，多个 proxy 理论上不会有什么提升。
对于一个库中进行多分表的情况，和多库不分表的情况，和性能没有必然关系，和数据分布有关系，比如一个查询打到两个DB实例和打到一个DB实例，打到两个DB上，返回时间取决于两个连接的最长返回时间，同时proxy有两个连接的开销。

凡凡鲜 · 2022 年4 月 13 日 02:47

local 模型，目前我们是10个表每个表4000万的数据，四个分片（每个分片1000万）两个proxy 同时压测发现性能差距还是比较大 sysbench thread 512 时跟单个mysql实例10个表每个表4000万数据对比，还只是mysql性能的一般
proxy

mysql

配置:server.yaml

rules:
  - !AUTHORITY
    users:
      - root@%:root321
      - sharding@:sharding
    provider:
      type: ALL_PRIVILEGES_PERMITTED
  - !TRANSACTION
    defaultType: LOCAL
  - !SQL_PARSER
    sqlCommentParseEnabled: true
    sqlStatementCache:
      initialCapacity: 2000
      maximumSize: 65535
      concurrencyLevel: 16
    parseTreeCache:
      initialCapacity: 128
      maximumSize: 1024
      concurrencyLevel: 16
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-opentracing-enabled: false
  proxy-hint-enabled: false
  sql-show: false
  check-table-metadata-enabled: false
  show-process-list-enabled: false

config-sharding配置：

原因分析：
sysbench 的查询更新删除 where条件几乎是 id，但是我的分片建是k字段，那么猜想这样也有很大的网络交互延迟问题

jingshanglu · 2022 年4 月 13 日 03:23

sysbench 的 sql 中 where 条件是 id，没有使用 id 作为分片键，会导致全路由，不能打到较好的性能。使用 id 作为分片键，在性能上会有较好的表现。

taojintianxia · 2022 年4 月 13 日 03:46

凡凡同学，你能分享下你现在的压测机跟 MySQL 机器的配置吗。如果可以的话，能否贴一下 my.cnf 文件

凡凡鲜 · 2022 年4 月 13 日 04:12

跑sysbench的机器 40c 256G内存
proxy机器 18c 180G内存两台
mysql机器 18c 180G内存 ssd

my.cnf配置

[mysqld]
##### server #####

basedir = /usr
datadir = /home/mysql/data
tmpdir = /home/mysql/tmp
socket = /var/run/mysqld/mysqld.sock
server_id = 3223269
bind_address = 0.0.0.0
report_host = x.x.x.x
report_port = 3306
myisam_recover_options = BACKUP,FORCE
secure_file_priv = /home/mysql/tmp
default_password_lifetime = 0
character_set_client_handshake = FALSE
character_set_server = utf8mb4
character_set_filesystem = utf8mb4

##### basic #####
skip_name_resolve = ON
transaction_isolation = READ-COMMITTED
read_only = OFF
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
log_timestamps = system
query_cache_size = 0
host_cache_size = 0
explicit_defaults_for_timestamp = ON
local_infile = OFF
event_scheduler = OFF
gtid_mode = ON
table_open_cache = 20480
thread_cache_size = 5120
performance-schema-instrument = stage/innodb/alter%=ON
performance-schema-instrument = wait/lock/metadata/sql/mdl=ON
performance-schema-consumer-events-stages-current = ON
performance-schema-consumer-events-stages-history = ON
performance-schema-consumer-events-stages-history-long = ON

##### per_thread_buffers #####
max_connections = 30000
max_user_connections = 29950
max_allowed_packet = 67108864
slave_pending_jobs_size_max = 134217728
max_connect_errors = 1000
sort_buffer_size = 2097152
binlog_cache_size = 2097152

##### log #####
log_error = /home/mysql/log/error.log
slow_query_log_file = /home/mysql/log/slowlog/slow.log
long_query_time = 0.1
min_examined_row_limit = 100
slow_query_log = ON
log_queries_not_using_indexes = OFF
log_slow_admin_statements = ON
log_slow_slave_statements = ON
log_throttle_queries_not_using_indexes = 10
log_bin = /home/mysql/log/binlog/binlog
binlog_format = row
expire_logs_days = 3
binlog_rows_query_log_events = ON
binlog_gtid_simple_recovery = ON
relay_log = /home/mysql/log/relaylog/relaylog
general_log = OFF
general_log_file = /home/mysql/log/general.log

##### innodb #####
innodb_page_size = 16384
innodb_buffer_pool_size = 125627793408
innodb_io_capacity = 4000
innodb_io_capacity_max = 16000
innodb_log_file_size = 2147483648
innodb_log_files_in_group = 4
innodb_data_file_path = ibdata1:512M:autoextend
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:10G
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 16777216
innodb_lock_wait_timeout = 5
innodb_print_all_deadlocks = ON
innodb_thread_concurrency = 108
innodb_max_undo_log_size = 2147483648
innodb_buffer_pool_dump_pct = 40
innodb_undo_log_truncate = ON
innodb_undo_directory = /home/mysql/data
innodb_undo_tablespaces = 3
innodb_sort_buffer_size = 67108864
innodb_doublewrite = OFF
innodb_numa_interleave = ON
innodb_flush_neighbors = 0

##### replication #####
relay_log_recovery = ON
log_slave_updates = ON
slave_preserve_commit_order = ON
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 4
master_info_repository = TABLE
relay_log_info_repository = TABLE
enforce_gtid_consistency = ON
skip_slave_start = FALSE

##### semi_sync #####
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled = OFF
rpl_semi_sync_master_wait_no_slave = 1
rpl_semi_sync_master_timeout = 1000
rpl_semi_sync_slave_enabled = OFF

##### other #####
ssl = 0
lock_wait_timeout = 50
disabled_storage_engines = MyISAM,MRG_MYISAM,MEMORY,BLACKHOLE,CSV,ARCHIVE,FEDERATED
innodb_adaptive_hash_index = OFF
tmp_table_size = 83886080
max_heap_table_size = 83886080

taojintianxia · 2022 年4 月 22 日 09:49

抱歉凡凡，最近出差，一直处于无网无信号的现场环境。不知道你这边现在有什么进展吗

cnlinjie · 2023 年12 月 5 日 02:15

快24年了，有什么进展或者优化方案嘛