innodb配置参数对Mysql5.7.29性能影响

测试说明

配置不同innodb参数对Mysql5.7.29进行测试。

测试环境：

Centos7.6

测试工具：TPCC-MySql
测试参数：30个仓库，30个并发线程，每次测试重新导入数据库，清空缓存，重启数据库
数据预热30分钟，测试2个小时

MySql5.7.29(编译安装)

模板配置文件:

[client]
port=3306
socket=/tmp/mysql.sock

[mysqld]
port=3306
socket=/tmp/mysql.sock
user=mysql


basedir = /usr/local/mysql
datadir = /data/mysql/data
pid-file = /data/mysql/mysql.pid

log_error = /data/mysql/mysql-error.log
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/mysql-slow.log


skip-external-locking
key_buffer_size = 32M
max_allowed_packet = 1024M
table_open_cache = 128
sort_buffer_size = 768K
net_buffer_length = 8K
read_buffer_size = 10M
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
thread_cache_size = 16
query_cache_size = 16M
tmp_table_size = 32M
performance_schema_max_table_instances = 1000

server-id=1
explicit_defaults_for_timestamp = true
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535
#
log_bin=mysql-bin
binlog_format=mixed
expire_logs_days = 10
early-plugin-load = ""
#
default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_buffer_pool_size = 128M
innodb_log_file_size = 32M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
#
[mysqldump]
quick
max_allowed_packet = 16M
#
[mysql]
no-auto-rehash
#
[myisamchk]
key_buffer_size = 32M
sort_buffer_size = 768K
read_buffer = 2M
write_buffer = 2M

第一次测试结果：

STOPPING THREADS..............................

<Raw Results>
  [0] sc:413380  lt:13605  rt:0  fl:0 
  [1] sc:426963  lt:1  rt:0  fl:0 
  [2] sc:42710  lt:0  rt:0  fl:0 
  [3] sc:42708  lt:0  rt:0  fl:0 
  [4] sc:42706  lt:3  rt:0  fl:0 
 in 7200 sec.

<Raw Results2(sum ver.)>
  [0] sc:413482  lt:13605  rt:0  fl:0 
  [1] sc:427090  lt:1  rt:0  fl:0 
  [2] sc:42710  lt:0  rt:0  fl:0 
  [3] sc:42710  lt:0  rt:0  fl:0 
  [4] sc:42707  lt:3  rt:0  fl:0 

<Constraint Check> (all must be [OK])
 [transaction percentage]
        Payment: 43.48% (>=43.0%) [OK]
   Order-Status: 4.35% (>= 4.0%) [OK]
       Delivery: 4.35% (>= 4.0%) [OK]
    Stock-Level: 4.35% (>= 4.0%) [OK]
 [response time (at least 90% passed)]
      New-Order: 96.81%  [OK]
        Payment: 100.00%  [OK]
   Order-Status: 100.00%  [OK]
       Delivery: 100.00%  [OK]
    Stock-Level: 99.99%  [OK]

<TpmC>
                 3558.208 TpmC

第二次测试：

第二次测试我们修改了innodb_buffer的一些参数：

innodb_buffer_pool_size = 14336M
innodb_log_file_size = 320M
innodb_log_buffer_size = 80M

然后重新进行测试：

STOPPING THREADS..............................

<Raw Results>
  [0] sc:3029770  lt:529980  rt:0  fl:0 
  [1] sc:3559602  lt:9  rt:0  fl:0 
  [2] sc:355989  lt:0  rt:0  fl:0 
  [3] sc:355988  lt:0  rt:0  fl:0 
  [4] sc:354717  lt:1275  rt:0  fl:0 
 in 7200 sec.

<Raw Results2(sum ver.)>
  [0] sc:3029914  lt:529983  rt:0  fl:0 
  [1] sc:3559889  lt:9  rt:0  fl:0 
  [2] sc:355989  lt:0  rt:0  fl:0 
  [3] sc:355990  lt:0  rt:0  fl:0 
  [4] sc:354717  lt:1275  rt:0  fl:0 

<Constraint Check> (all must be [OK])
 [transaction percentage]
        Payment: 43.48% (>=43.0%) [OK]
   Order-Status: 4.35% (>= 4.0%) [OK]
       Delivery: 4.35% (>= 4.0%) [OK]
    Stock-Level: 4.35% (>= 4.0%) [OK]
 [response time (at least 90% passed)]
      New-Order: 85.11%  [NG] *
        Payment: 100.00%  [OK]
   Order-Status: 100.00%  [OK]
       Delivery: 100.00%  [OK]
    Stock-Level: 99.64%  [OK]

<TpmC>
                 29664.584 TpmC

修改了buffer参数后，我们可以明显的看到(每分钟成功事务数)Tpmc上升了差不多9倍
但是New-Order: 85.11% [NG] 居然出现了NG的情况

第三次测试

我们适当减小innodb_buffer参数进行第三次测试

innodb_buffer_pool_size = 11520M
innodb_log_file_size = 32M
innodb_log_buffer_size = 8M

测试结果

STOPPING THREADS..............................

<Raw Results>
  [0] sc:1448251  lt:123248  rt:0  fl:0 
  [1] sc:1571481  lt:4  rt:0  fl:0 
  [2] sc:157170  lt:0  rt:0  fl:0 
  [3] sc:157166  lt:0  rt:0  fl:0 
  [4] sc:156992  lt:177  rt:0  fl:0 
 in 7200 sec.

<Raw Results2(sum ver.)>
  [0] sc:1448452  lt:123249  rt:0  fl:0 
  [1] sc:1571695  lt:4  rt:0  fl:0 
  [2] sc:157170  lt:0  rt:0  fl:0 
  [3] sc:157169  lt:0  rt:0  fl:0 
  [4] sc:156993  lt:177  rt:0  fl:0 

<Constraint Check> (all must be [OK])
 [transaction percentage]
        Payment: 43.48% (>=43.0%) [OK]
   Order-Status: 4.35% (>= 4.0%) [OK]
       Delivery: 4.35% (>= 4.0%) [OK]
    Stock-Level: 4.35% (>= 4.0%) [OK]
 [response time (at least 90% passed)]
      New-Order: 92.16%  [OK]
        Payment: 100.00%  [OK]
   Order-Status: 100.00%  [OK]
       Delivery: 100.00%  [OK]
    Stock-Level: 99.89%  [OK]

<TpmC>
                 13095.825 TpmC

从结果可以看出，将innodb_log_buffer_size (事务提交缓存区)降低后，每分钟的执行的事务数量降了下来，但请求响应时间都回归了正常状态，由此可见此参数的调整需要结合实际情况分析，不可设置太大