您的位置: 首页  >  文章  >  MYSQL基础：参数调优:3:InnoDB调优参数示例

MYSQL基础：参数调优:3:InnoDB调优参数示例

分类: 文章 • 2025-04-28 12:06:52

MYSQL基础：参数调优:3:InnoDB调优参数示例
这篇文章在前文对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>

设定建议

  • 修改配置文件

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

  • 官方的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

设定示例

由于官方镜像中没有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
#
  • 重新启动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>
  • 设定总结
设定参数 修改前 修改后
innodb_autoinc_lock_mode 1 2
innodb_buffer_pool_size 128M 4G
innodb_buffer_pool_instances 1 4
innodb_buffer_pool_chunk_size 128M 1G
innodb_file_per_table ON ON
innodb_flush_method - O_DIRECT
innodb_io_capacity 200 2000
innodb_io_capacity_max 2000 6000
innodb_log_buffer_size 16M 16M
innodb_log_file_size 48M 256M
innodb_read_io_threads 4 4
innodb_thread_concurrency 0 0
innodb_write_io_threads 4 4

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

相关推荐