这篇文章在前文对Innodb调优参数说明的基础上，根据一个具体的设定示例给出查询和设定的命令。

确认系统当前设定

• 使用如下命令确认系统的当前状态

show variables where variable_name in ('innodb_buffer_pool_size', \
'innodb_buffer_pool_instances',\
'innodb_buffer_pool_chunk_size',\
'innodb_file_per_table',\
'innodb_flush_method',\
'innodb_write_io_threads',\
'innodb_read_io_threads',\
'innodb_additional_mem_pool_size',\
'innodb_io_capacity',\
'innodb_io_capacity_max',\
'innodb_thread_concurrency',\
'innodb_autoinc_lock_mode',\
'innodb_log_file_size',\
'innodb_log_buffer_size',\
'innodb_flush_logs_at_trx_commit');

• 执行示例

mysql> show variables where variable_name in ('innodb_buffer_pool_size', \
    -> 'innodb_buffer_pool_instances',\
    -> 'innodb_buffer_pool_chunk_size',\
    -> 'innodb_file_per_table',\
    -> 'innodb_flush_method',\
    -> 'innodb_write_io_threads',\
    -> 'innodb_read_io_threads',\
    -> 'innodb_additional_mem_pool_size',\
    -> 'innodb_io_capacity',\
    -> 'innodb_io_capacity_max',\
    -> 'innodb_thread_concurrency',\
    -> 'innodb_autoinc_lock_mode',\
    -> 'innodb_log_file_size',\
    -> 'innodb_log_buffer_size',\
    -> 'innodb_flush_logs_at_trx_commit');
+-------------------------------+-----------+
| Variable_name                 | Value     |
+-------------------------------+-----------+
| innodb_autoinc_lock_mode      | 1         |
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_instances  | 1         |
| innodb_buffer_pool_size       | 134217728 |
| innodb_file_per_table         | ON        |
| innodb_flush_method           |           |
| innodb_io_capacity            | 200       |
| innodb_io_capacity_max        | 2000      |
| innodb_log_buffer_size        | 16777216  |
| innodb_log_file_size          | 50331648  |
| innodb_read_io_threads        | 4         |
| innodb_thread_concurrency     | 0         |
| innodb_write_io_threads       | 4         |
+-------------------------------+-----------+
13 rows in set (0.00 sec)

mysql>
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

设定建议

• 修改配置文件

为了能够持久化的保持，而不至于重启或者容器重新生成后恢复默认状态，需要设定到配置文件中。

• 官方的mysql镜像配置文件：/etc/mysql/mysql.conf.d/mysqld.cnf

• 建议设定示例（需根据相关性能需求与机器状况进行调整）

# Innodb parameter setting examples
innodb_autoinc_lock_mode=2  
innodb_buffer_pool_size=4G
innodb_buffer_pool_instances=4    
innodb_buffer_pool_chunk_size=1G
innodb_file_per_table=1         
innodb_flush_method=O_DIRECT
innodb_io_capacity=2000
innodb_io_capacity_max=6000
innodb_log_buffer_size=16M
innodb_log_file_size=256M
innodb_read_io_threads=4
innodb_thread_concurrency=0
innodb_write_io_threads=4
1
2
3
4
5
6
7
8
9
10
11
12
13
14

设定示例

由于官方镜像中没有vi，所以可以使用cat + Heredocument直接追加到设定文件。

# cp -p /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.org
# cat <<EOF >>/etc/mysql/mysql.conf.d/mysqld.cnf
> # Innodb parameter setting examples
innodb_autoinc_lock_mode=2  
innodb_buffer_pool_size=4G
innodb_buffer_pool_instances=4    
innodb_buffer_pool_chunk_size=1G
innodb_file_per_table=1         
innodb_flush_method=O_DIRECT
innodb_io_capacity=2000
innodb_io_capacity_max=6000
innodb_log_buffer_size=16M
innodb_log_file_size=256M
innodb_read_io_threads=4
innodb_thread_concurrency=0
innodb_write_io_threads=4> > > > > > > > > > > > > 
> EOF
# diff /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.org
31,44d30
< # Innodb parameter setting examples
< innodb_autoinc_lock_mode=2  
< innodb_buffer_pool_size=4G
< innodb_buffer_pool_instances=4    
< innodb_buffer_pool_chunk_size=1G
< innodb_file_per_table=1         
< innodb_flush_method=O_DIRECT
< innodb_io_capacity=2000
< innodb_io_capacity_max=6000
< innodb_log_buffer_size=16M
< innodb_log_file_size=256M
< innodb_read_io_threads=4
< innodb_thread_concurrency=0
< innodb_write_io_threads=4
# 
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

• 重新启动mysql服务，再次确认设定

mysql> show variables where variable_name in ('innodb_buffer_pool_size', \
    -> 'innodb_buffer_pool_instances',\
    -> 'innodb_buffer_pool_chunk_size',\
    -> 'innodb_file_per_table',\
    -> 'innodb_flush_method',\
    -> 'innodb_write_io_threads',\
    -> 'innodb_read_io_threads',\
    -> 'innodb_additional_mem_pool_size',\
    -> 'innodb_io_capacity',\
    -> 'innodb_io_capacity_max',\
    -> 'innodb_thread_concurrency',\
    -> 'innodb_autoinc_lock_mode',\
    -> 'innodb_log_file_size',\
    -> 'innodb_log_buffer_size',\
    -> 'innodb_flush_logs_at_trx_commit');
+-------------------------------+------------+
| Variable_name                 | Value      |
+-------------------------------+------------+
| innodb_autoinc_lock_mode      | 2          |
| innodb_buffer_pool_chunk_size | 1073741824 |
| innodb_buffer_pool_instances  | 4          |
| innodb_buffer_pool_size       | 4294967296 |
| innodb_file_per_table         | ON         |
| innodb_flush_method           | O_DIRECT   |
| innodb_io_capacity            | 2000       |
| innodb_io_capacity_max        | 6000       |
| innodb_log_buffer_size        | 16777216   |
| innodb_log_file_size          | 268435456  |
| innodb_read_io_threads        | 4          |
| innodb_thread_concurrency     | 0          |
| innodb_write_io_threads       | 4          |
+-------------------------------+------------+
13 rows in set (0.01 sec)

mysql>
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

• 设定总结

注：后续会对此设定进行跟踪。