mysql5.7配置innodb_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..............................

[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.

[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

(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]

3558.208 TpmC

第二次测试：

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

innodb_buffer_pool_size = 14336M

innodb_log_file_size = 320M

innodb_log_buffer_size = 80M

然后重新进行测试：

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

[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.

[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

(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]

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..............................

[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.

[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

(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]

13095.825 TpmC

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