mysql 配置调优

mysql 配置调优

1. 参考文档

• mysql配置调优（16G/32G）
• mysql优化—第7篇：参数 innodb_buffer_pool_instances设置;
• MySQL5.7优化InnoDB配置以及调优方案;

2. 为什么要进行mysql配置调优

1. 在开发一个管理大批量数据的项目时，发现数据库进行长期大批量数据操作时，读写速度会逐渐变慢；因此进行mysql配置的修改，去解决这个问题；

3. 具体调优配置项

3.1. temp_table_size

1. 内存临时表大小,默认32M。MYSQL在需要时也会自动将大表或结果集放到磁盘上,以避免内存耗尽。可根据需要调大,但不要超过物理内存的50%;
2. 临时表是指sql执行时生成临时数据表;
3. 默认为16M，可调到64-256最佳;tmp_table_size如果过小，存不下了就会存到磁盘上。对于group by会有性能影响。

3.2. 什么是InnoDB Buffer Pool

1. InnoDB Buffer Pool是InnoDB存储引擎用于缓存表和索引数据的主要内存结构。它的主要功能是将经常访问的数据页保存在内存中，从而减少磁盘I/O操作，提高数据库的读写性能。

3.3. innodb_buffer_pool_size

• InnoDB存储引擎表数据缓存区大小,对于InnoDB来说,是最重要的参数之一。默认为8M,通常设置为实例物理内存的50-80%,最大不要超过物理内存的80%

3.4. innodb_buffer_pool_instances

• 设置 innodb_buffer_pool_size 参数的时候，innodb_buffer_pool_instances 的取值约为 innodb_buffer_pool_size/1G;

1. 官方建议每个buffer pool实例至少为1GB;
2. innodb_buffer_pool_instances可以开启多个内存缓冲池，把需要缓冲的数据hash到不同的缓冲池中，这样可以并行的内存读写。通过将多个缓冲池实例化，可以提高并行处理能力，从而提升数据库的性能，特别是在高并发环境下。
3. innodb_buffer_pool_instances 参数显著的影响测试结果，特别是非常高的 I/O 负载时。

3.5. innodb_log_file_size

• 这个参数指定在一个日志组中，每个log的大小；一般取256M可以兼顾性能和recovery的速度；
• 指定日志文件的大小，影响数据库的写入速度和事务大小。较大的日志文件可以减少日志刷新频率，提高性能，但可能增加故障恢复时间;

3.6. innodb_log_buffer_size

• 控制事务日志在内存中的缓冲大小。适当增加该值可以减少日志写入磁盘的频率，提高写入性能;
• 这个参数决定了InnoDB引擎可使用的日志内存空间。只要没有类似插入blob类型数据的操作（也不建议有这样的操作），这个内存空间都不需要设置得太大。5MB-10MB是一个推荐的设置值，不过这个参数还是要和innodb_flush_log_at_trx_commit参数配合使用；

3.7. innodb_flush_log_at_trx_commit

• 控制事务日志的刷新到磁盘的方式，对性能有重要影响。设置为0或2可以减少磁盘IO，但可能会丢失部分事务数据;
• 如果您的Linux操作系统使用的是带有日志功能的文件系统并且日志功能是开启的，那么还是建议将该参数设置为2；

4. 32G内存配置案例

• my.cnf文件配置；

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8
[mysqld]
init_connect='SET collation-server = utf8_general_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake
skip-name-resolve
lower_case_table_names=1
key_buffer_size=128M
max_allowed_packet=256M
table_open_cache=4096
sort_buffer_size=16M
read_buffer_size=32M
read_rnd_buffer_size=16M
myisam_sort_buffer_size=128M
thread_cache_size=64
query_cache_type=0
tmp_table_size=128M
max_connections=2000
max_user_connections=1700
max_connect_errors=100
open_files_limit=65535
innodb_file_per_table=1
innodb_buffer_pool_instances=8
innodb_buffer_pool_size=16G
innodb_log_file_size=4G
innodb_log_buffer_size=256M
innodb_flush_log_at_trx_commit=2
innodb_lock_wait_timeout=60
back_log=128
log-bin=mysql-bin
server-id=1