proxy批量插入性能问题(5.0.0)

要如何配置才能提升proxy的批量插入性能。

datax通过proxy批量的向mysql插入数据。无论如何调整,插入性能始终在1600条/秒左右。datax配置不变,直接向mysql插入,性能可以到1万/秒
proxy的配置如下

mode:
  type: Cluster
  repository:
    type: ZooKeeper
    props:
      namespace: sharding-sphere-history
      server-lists: 10.10.111.82:2181,10.10.111.83:2181,10.10.111.84:2181
      retryIntervalMilliseconds: 5000
      timeToLiveSeconds: 60
      maxRetries: 3
      operationTimeoutMilliseconds: 5000
  overwrite: true
rules:
  - !AUTHORITY
    users:
      - sharding@%:aaabbbccc
    provider:
      type: ALL_PRIVILEGES_PERMITTED
#  - !TRANSACTION
#    defaultType: XA
#    providerType: Atomikos

props:
  max-connections-size-per-query: 50
  kernel-executor-size: 1024  # Infinite by default.
  proxy-frontend-flush-threshold: 128  # The default value is 128.
  proxy-opentracing-enabled: false
  proxy-hint-enabled: false
  sql-show: false
  sql-simple: false
  check-table-metadata-enabled: false
  show-process-list-enabled: false
    # Proxy backend query fetch size. A larger value may increase the memory usage of ShardingSphere Proxy.
    # The default value is -1, which means set the minimum value for different JDBC drivers.
  proxy-backend-query-fetch-size: 2048
  check-duplicate-table-enabled: false
  sql-comment-parse-enabled: false
  proxy-frontend-executor-size: 0 # Proxy frontend executor size. The default value is 0, which means let Netty decide.
    # Available options of proxy backend executor suitable: OLAP(default), OLTP. The OLTP option may reduce time cost of writing packets to client, but it may increase the latency of SQL execution
    # if client connections are more than proxy-frontend-netty-executor-size, especially executing slow SQL.
  proxy-backend-executor-suitable: OLTP
  proxy-frontend-max-connections: 0 # Less than or equal to 0 means no limitation.
  sql-federation-enabled: false

proxy放在 8核心8G的服务器上,插入1600条/秒
proxy放在64核128G的服务器上,插入1800条/秒
各种微调props里面的参数,除了proxy-backend-query-fetch-size对指标有影响,其他的指标影响都可以忽略不计。

你好,欢迎来到中文论坛。
关于这个问题:
1、批量插入是使用的 values (),(),() 这种语句吗? 一次插入多少条记录?
2、也请看下 dataSouce 连接池相关的配置

语句是replace into(xxx) values(),(),(),一次插入2048条。
连接池配置如下

schemaName: ods_history
dataSources:
  master-0:
    url: jdbc:mysql://10.10.118.32:3306/ods_history_0?useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=utf-8&serverTimeZone=Asia/Shangehai&useAffectedRows=true
    username: user_test
    password: test
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  master-1:
    url: jdbc:mysql://10.10.118.32:3306/ods_history_1?useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=utf-8&serverTimeZone=Asia/Shangehai&useAffectedRows=true
    username: user_test
    password: test
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  master-2:
    url: jdbc:mysql://10.10.118.32:3306/ods_history_2?useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=utf-8&serverTimeZone=Asia/Shangehai&useAffectedRows=true
    username: user_test
    password: test
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  master-3:
    url: jdbc:mysql://10.10.118.32:3306/ods_history_3?useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=utf-8&serverTimeZone=Asia/Shangehai&useAffectedRows=true
    username: user_test
    password: test
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1

@dany 据我的理解,我们可以看看是不是修改这几个地方的参数:

  1. 修改 startup.sh 中的 JVM 参数,例如
JAVA_MEM_OPTS=" -server -Xmx32g -Xms32g -Xmn20g -Xss1m -XX:+DisableExplicitGC -XX:+UseConcMarkSweepGC -XX:+CMSParallelRemarkEnabled -XX:LargePageSizeInBytes=128m -XX:+UseFastAccessorMethods -XX:+UseCMSInitiatingOccupancyOnly -XX:CMSInitiatingOccupancyFraction=70 "
  1. 修改下 MySQL url 上的参数,例如:
jdbc:mysql://127.0.0.1:3306/test?useSSL=false&useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=8192&prepStmtCacheSqlLimit=1024
  1. server.yaml 中有一处前端线程数的参数 proxy-frontend-executor-size,默认是被 netty 控制的,我们可以考虑将其设置大一些,例如 500 试试。

供参考

@dany
请问用的是分库分表规则吗?
如果是的话,一条插入 SQL 的 values 中,不同的 value 有可能会路由到不同的节点,所以表面上是一条插入 SQL,有可能后端会对应非常多不同的 SQL。可以打开 SQL show 看看实际的 SQL。

用了分库分表规则,但是实际测下来影响不大,测试的时候临时修改规则,让所有数据都落到了同一个库,同一个表,插入性能仍然是1600条/秒左右波动。

@taojintianxia
1.修改start.sh启动参数,之前的也修改了,这次找了一台256G内存的机器重新测试了一下,平均插入速度没有改观,仍然是1600,有一个新发现当内存增加到128G的时候,性能不升反降,降低到1400左右了。
2.修改MySQL链接参数,测试了几次,几乎没有效果。
3.修改proxy-frontend-executor-size,这个值调大之后性能略有降低。

:joy: 竟然引起了负优化。

dany 同学可以试试 sysbech 的 write_only 吗,这个数据挺有参考性的。
我把付林在微信群里的命令贴进来:

sysbench --test=/usr/local/share/sysbench/oltp_insert.lua \
--mysql-host=127.0.0.1 \
--mysql-port=3309 \
--mysql-user=root \
--mysql-password='root' \
--mysql-db=encrypt_db \
--db-driver=mysql \
--tables=1 \
--table-size=5000000 \
--report-interval=10 \
--threads=300 \
--time=180  \
run

这里你跟他用的是相同工具,相同命令吗。

通过proxy 写数据(单条insert)到mysql后,QPS下降了4倍多。
环境:

  • cpu:intel i5-9400 6核,2.9Ghz *1
  • 内存:16G
  • 硬盘: KINGSTON SA400M8 (m2 240G),写入速度 350MB/S
  • 系统:ubuntu bullseye/sid
core file size              (blocks, -c) unlimited
data seg size               (kbytes, -d) unlimited
scheduling priority                 (-e) 0
file size                   (blocks, -f) unlimited
pending signals                     (-i) 63088
max locked memory           (kbytes, -l) 2032193
max memory size             (kbytes, -m) unlimited
open files                          (-n) 1000000
pipe size                (512 bytes, -p) 8
POSIX message queues         (bytes, -q) 819200
real-time priority                  (-r) 0
stack size                  (kbytes, -s) 8192
cpu time                   (seconds, -t) unlimited
max user processes                  (-u) 63088
virtual memory              (kbytes, -v) unlimited
file locks                          (-x) unlimited
  • mysql 5.7.33
max_connections = 10000
innodb_buffer_pool_size = 4G
skip_ssl
skip-name-resolve
#关闭binlog,普通日志
  • 测试工具sysbench 1.0.20
sysbench --test=/usr/local/share/sysbench/oltp_insert.lua \
--mysql-host=127.0.0.1 \
--mysql-port=3309 \
--mysql-user=root \
--mysql-password='root' \
--mysql-db=encrypt_db \
--db-driver=mysql \
--tables=1 \
--table-size=5000000 \
--report-interval=10 \
--threads=300 \
--time=180  \
run

proxy相关配置:

#server.yaml
mode:
  type: Cluster
  repository:
    type: ZooKeeper
    props:
      namespace: governance_ds
      server-lists: 172.16.6.219:2181
      retryIntervalMilliseconds: 500
      timeToLiveSeconds: 60
      maxRetries: 3
      operationTimeoutMilliseconds: 500
  overwrite: true
#
rules:
  - !AUTHORITY
    users:
      - root@%:root
      - sharding@:sharding
    provider:
      #type: ALL_PRIVILEGES_PERMITTED
      type: SCHEMA_PRIVILEGES_PERMITTED
      props:
        user-schema-mappings: root@=encrypt_db
#  - !TRANSACTION
#    defaultType: XA
#    providerType: Atomikos

props:
  max-connections-size-per-query: 5
  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
  #sql-simple: true
  check-table-metadata-enabled: false
  show-process-list-enabled: false
    # Proxy backend query fetch size. A larger value may increase the memory usage of ShardingSphere Proxy.
    # The default value is -1, which means set the minimum value for different JDBC drivers.
  proxy-backend-query-fetch-size: -1
  check-duplicate-table-enabled: false
  sql-comment-parse-enabled: false
  proxy-frontend-executor-size: 0 # Proxy frontend executor size. The default value is 0, which means let Netty decide.
#    # Available options of proxy backend executor suitable: OLAP(default), OLTP. The OLTP option may reduce time cost of writing packets to client, but it may increase the latency of SQL execution
#    # if client connections are more than proxy-frontend-netty-executor-size, especially executing slow SQL.
  proxy-backend-executor-suitable: OLTP
  proxy-frontend-max-connections: 0 # Less than or equal to 0 means no limitation.
  sql-federation-enabled: fals
#config-encrypt.yaml
schemaName: encrypt_db
dataSources:
  ds_0:
    url: jdbc:mysql://127.0.0.1:3306/test?serverTimezone=UTC&useSSL=false&useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=8192&prepStmtCacheSqlLimit=1024
    username: test
    password: test
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 500
    minPoolSize: 1

rules:
- !ENCRYPT
  encryptors:
    aes_encryptor:
      type: AES
      props:
        aes-key-value: 123456abc
  tables:
    sbtest1:
      columns:
        pad:
          plainColumn: pad
          cipherColumn: pad_cipher
          encryptorName: aes_encryptor
    t_a:
      columns:
        phone:
          plainColumn: phone
          cipherColumn: phone_cipher
          encryptorName: aes_encryptor
  queryWithCipherColumn: false

测试结果:

#开启 proxy 自动加密 

[ 10s ] thds: 300 tps: 2976.79 qps: 2976.79 (r/w/o: 0.00/2976.79/0.00) lat (ms,95%): 139.85 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 300 tps: 3014.71 qps: 3014.71 (r/w/o: 0.00/3014.71/0.00) lat (ms,95%): 142.39 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 300 tps: 2646.70 qps: 2646.70 (r/w/o: 0.00/2646.70/0.00) lat (ms,95%): 155.80 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 300 tps: 2599.96 qps: 2599.96 (r/w/o: 0.00/2599.96/0.00) lat (ms,95%): 158.63 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 300 tps: 2614.83 qps: 2614.83 (r/w/o: 0.00/2614.83/0.00) lat (ms,95%): 161.51 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 300 tps: 2605.03 qps: 2605.03 (r/w/o: 0.00/2605.03/0.00) lat (ms,95%): 161.51 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 300 tps: 2569.66 qps: 2569.66 (r/w/o: 0.00/2569.66/0.00) lat (ms,95%): 167.44 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 300 tps: 2594.76 qps: 2594.76 (r/w/o: 0.00/2594.76/0.00) lat (ms,95%): 176.73 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 300 tps: 2628.98 qps: 2628.98 (r/w/o: 0.00/2628.98/0.00) lat (ms,95%): 164.45 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 300 tps: 2527.61 qps: 2527.61 (r/w/o: 0.00/2527.61/0.00) lat (ms,95%): 179.94 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 300 tps: 2616.45 qps: 2616.45 (r/w/o: 0.00/2616.45/0.00) lat (ms,95%): 167.44 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 300 tps: 2587.21 qps: 2587.21 (r/w/o: 0.00/2587.21/0.00) lat (ms,95%): 167.44 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 300 tps: 2538.34 qps: 2538.34 (r/w/o: 0.00/2538.34/0.00) lat (ms,95%): 173.58 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 300 tps: 2640.87 qps: 2640.87 (r/w/o: 0.00/2640.87/0.00) lat (ms,95%): 153.02 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 300 tps: 2596.20 qps: 2596.20 (r/w/o: 0.00/2596.20/0.00) lat (ms,95%): 167.44 err/s: 0.00 reconn/s: 0.00
[ 160s ] thds: 300 tps: 2523.33 qps: 2523.33 (r/w/o: 0.00/2523.33/0.00) lat (ms,95%): 170.48 err/s: 0.00 reconn/s: 0.00
[ 170s ] thds: 300 tps: 2526.18 qps: 2526.18 (r/w/o: 0.00/2526.18/0.00) lat (ms,95%): 176.73 err/s: 0.00 reconn/s: 0.00
[ 180s ] thds: 300 tps: 2630.50 qps: 2630.50 (r/w/o: 0.00/2630.50/0.00) lat (ms,95%): 158.63 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           474703
        other:                           0
        total:                           474703
    transactions:                        474703 (2634.44 per sec.)
    queries:                             474703 (2634.44 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          180.1876s
    total number of events:              474703

Latency (ms):
         min:                                    6.09
         avg:                                  113.80
         max:                                  386.59
         95th percentile:                      167.44
         sum:                             54022979.31

Threads fairness:
    events (avg/stddev):           1582.3433/1.82
    execution time (avg/stddev):   180.0766/0.06
#直接写mysql
[ 10s ] thds: 300 tps: 12930.39 qps: 12930.39 (r/w/o: 0.00/12930.39/0.00) lat (ms,95%): 53.85 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 300 tps: 13789.39 qps: 13789.39 (r/w/o: 0.00/13789.39/0.00) lat (ms,95%): 51.02 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 300 tps: 13566.99 qps: 13566.99 (r/w/o: 0.00/13566.99/0.00) lat (ms,95%): 51.94 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 300 tps: 13616.97 qps: 13616.97 (r/w/o: 0.00/13616.97/0.00) lat (ms,95%): 51.94 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 300 tps: 12987.07 qps: 12987.07 (r/w/o: 0.00/12987.07/0.00) lat (ms,95%): 51.02 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 300 tps: 13906.23 qps: 13906.23 (r/w/o: 0.00/13906.23/0.00) lat (ms,95%): 52.89 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 300 tps: 13827.55 qps: 13827.55 (r/w/o: 0.00/13827.55/0.00) lat (ms,95%): 53.85 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 300 tps: 13890.74 qps: 13890.74 (r/w/o: 0.00/13890.74/0.00) lat (ms,95%): 51.94 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 300 tps: 13399.98 qps: 13399.98 (r/w/o: 0.00/13399.98/0.00) lat (ms,95%): 54.83 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 300 tps: 13765.86 qps: 13765.86 (r/w/o: 0.00/13765.86/0.00) lat (ms,95%): 51.94 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 300 tps: 13727.70 qps: 13727.70 (r/w/o: 0.00/13727.70/0.00) lat (ms,95%): 53.85 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 300 tps: 13908.74 qps: 13908.74 (r/w/o: 0.00/13908.74/0.00) lat (ms,95%): 53.85 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 300 tps: 13516.09 qps: 13516.09 (r/w/o: 0.00/13516.09/0.00) lat (ms,95%): 54.83 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 300 tps: 13816.87 qps: 13816.87 (r/w/o: 0.00/13816.87/0.00) lat (ms,95%): 52.89 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 300 tps: 13316.27 qps: 13316.27 (r/w/o: 0.00/13316.27/0.00) lat (ms,95%): 55.82 err/s: 0.00 reconn/s: 0.00
[ 160s ] thds: 300 tps: 13560.56 qps: 13560.56 (r/w/o: 0.00/13560.56/0.00) lat (ms,95%): 54.83 err/s: 0.00 reconn/s: 0.00
[ 170s ] thds: 300 tps: 13341.61 qps: 13341.61 (r/w/o: 0.00/13341.61/0.00) lat (ms,95%): 56.84 err/s: 0.00 reconn/s: 0.00
[ 180s ] thds: 299 tps: 13667.41 qps: 13667.41 (r/w/o: 0.00/13667.41/0.00) lat (ms,95%): 52.89 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           2446156
        other:                           0
        total:                           2446156
    transactions:                        2446156 (13583.37 per sec.)
    queries:                             2446156 (13583.37 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          180.0830s
    total number of events:              2446156

Latency (ms):
         min:                                    0.25
         avg:                                   22.07
         max:                                  740.56
         95th percentile:                       52.89
         sum:                             53996198.43

Threads fairness:
    events (avg/stddev):           8153.8533/109.12
    execution time (avg/stddev):   179.9873/0.03

测试时系统性能表现:
cpu使用率70%左右,磁盘io util时间99% ;

这些操作,应该是发压、proxy、MySQL 都在一台机器吧?

我这边也抽时间测试一下这个情况。

是在同一台机器;压mysql时性能表现很好。

怀疑瓶颈在 MySQL 处。
尝试修改下 MySQL 配置,重启测试下看看。

innodb_log_buffer_size=2G
innodb-log-file-size=2000000000
innodb-log-files-in-group=3

我这里 #开启 proxy 自动加密 的测试:

Running the test with following options:
Number of threads: 300
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 300 tps: 28020.94 qps: 28020.94 (r/w/o: 0.00/28020.94/0.00) lat (ms,95%): 21.50 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 300 tps: 32320.59 qps: 32320.59 (r/w/o: 0.00/32320.59/0.00) lat (ms,95%): 18.28 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 300 tps: 31280.18 qps: 31280.18 (r/w/o: 0.00/31280.18/0.00) lat (ms,95%): 18.95 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 300 tps: 31518.60 qps: 31518.60 (r/w/o: 0.00/31518.60/0.00) lat (ms,95%): 19.29 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 300 tps: 31856.58 qps: 31856.58 (r/w/o: 0.00/31856.58/0.00) lat (ms,95%): 18.28 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 300 tps: 31392.65 qps: 31392.65 (r/w/o: 0.00/31392.65/0.00) lat (ms,95%): 19.29 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 300 tps: 31193.96 qps: 31193.96 (r/w/o: 0.00/31193.96/0.00) lat (ms,95%): 18.95 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 300 tps: 31406.12 qps: 31406.12 (r/w/o: 0.00/31406.12/0.00) lat (ms,95%): 18.95 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 300 tps: 30999.60 qps: 30999.60 (r/w/o: 0.00/30999.60/0.00) lat (ms,95%): 19.65 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 300 tps: 30631.06 qps: 30631.06 (r/w/o: 0.00/30631.06/0.00) lat (ms,95%): 19.65 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 300 tps: 30744.54 qps: 30744.54 (r/w/o: 0.00/30744.54/0.00) lat (ms,95%): 19.65 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 300 tps: 31223.21 qps: 31223.21 (r/w/o: 0.00/31223.21/0.00) lat (ms,95%): 19.65 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 300 tps: 31952.20 qps: 31952.20 (r/w/o: 0.00/31952.20/0.00) lat (ms,95%): 18.61 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 300 tps: 31176.96 qps: 31176.96 (r/w/o: 0.00/31176.96/0.00) lat (ms,95%): 19.65 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 300 tps: 31111.16 qps: 31111.16 (r/w/o: 0.00/31111.16/0.00) lat (ms,95%): 19.65 err/s: 0.00 reconn/s: 0.00
[ 160s ] thds: 300 tps: 31077.15 qps: 31077.15 (r/w/o: 0.00/31077.15/0.00) lat (ms,95%): 18.61 err/s: 0.00 reconn/s: 0.00
[ 170s ] thds: 300 tps: 30794.61 qps: 30794.61 (r/w/o: 0.00/30794.61/0.00) lat (ms,95%): 18.95 err/s: 0.00 reconn/s: 0.00
[ 180s ] thds: 300 tps: 31677.72 qps: 31677.72 (r/w/o: 0.00/31677.72/0.00) lat (ms,95%): 19.65 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           5604409
        other:                           0
        total:                           5604409
    transactions:                        5604409 (31125.27 per sec.)
    queries:                             5604409 (31125.27 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          180.0584s
    total number of events:              5604409

Latency (ms):
         min:                                    2.13
         avg:                                    9.63
         max:                                  337.02
         95th percentile:                       19.29
         sum:                             53987534.16

Threads fairness:
    events (avg/stddev):           18681.3633/42.11
    execution time (avg/stddev):   179.9584/0.02

直接插优化过的 MySQL

Running the test with following options:
Number of threads: 300
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 300 tps: 41667.00 qps: 41667.00 (r/w/o: 0.00/41667.00/0.00) lat (ms,95%): 14.21 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 300 tps: 42929.79 qps: 42929.79 (r/w/o: 0.00/42929.79/0.00) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 300 tps: 42958.57 qps: 42958.57 (r/w/o: 0.00/42958.57/0.00) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 300 tps: 43542.61 qps: 43542.61 (r/w/o: 0.00/43542.61/0.00) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 300 tps: 43873.26 qps: 43873.26 (r/w/o: 0.00/43873.26/0.00) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 300 tps: 42823.37 qps: 42823.37 (r/w/o: 0.00/42823.37/0.00) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 300 tps: 43415.14 qps: 43415.14 (r/w/o: 0.00/43415.14/0.00) lat (ms,95%): 13.70 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 300 tps: 43088.63 qps: 43088.63 (r/w/o: 0.00/43088.63/0.00) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 300 tps: 43680.05 qps: 43680.05 (r/w/o: 0.00/43680.05/0.00) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 300 tps: 42889.94 qps: 42889.94 (r/w/o: 0.00/42889.94/0.00) lat (ms,95%): 13.70 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 300 tps: 43415.14 qps: 43415.14 (r/w/o: 0.00/43415.14/0.00) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 300 tps: 42987.37 qps: 42987.37 (r/w/o: 0.00/42987.37/0.00) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 300 tps: 43265.31 qps: 43265.31 (r/w/o: 0.00/43265.31/0.00) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 300 tps: 42733.59 qps: 42733.59 (r/w/o: 0.00/42733.59/0.00) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 300 tps: 41793.54 qps: 41793.54 (r/w/o: 0.00/41793.54/0.00) lat (ms,95%): 14.21 err/s: 0.00 reconn/s: 0.00
[ 160s ] thds: 300 tps: 43809.87 qps: 43809.87 (r/w/o: 0.00/43809.87/0.00) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00
[ 170s ] thds: 300 tps: 42550.52 qps: 42550.52 (r/w/o: 0.00/42550.52/0.00) lat (ms,95%): 13.70 err/s: 0.00 reconn/s: 0.00
[ 180s ] thds: 300 tps: 42164.09 qps: 42164.09 (r/w/o: 0.00/42164.09/0.00) lat (ms,95%): 13.70 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           7736433
        other:                           0
        total:                           7736433
    transactions:                        7736433 (42961.76 per sec.)
    queries:                             7736433 (42961.76 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          180.0758s
    total number of events:              7736433

Latency (ms):
         min:                                    1.44
         avg:                                    6.98
         max:                                  159.06
         95th percentile:                       13.46
         sum:                             53988019.57

Threads fairness:
    events (avg/stddev):           25788.1100/41.61
    execution time (avg/stddev):   179.9601/0.02

@dany 我这里直接用的你的配置文件,修改了下数据库的用户名密码,其他的都一样。
我这用的 32C 的 CentOS 7.1

@dany

京ICP备2021015875号