Write by lyc at 2019-7-17
参考博文：
MySQL 优化系列（1）– InnoDB重要参数优化

一、内存相关

innodb_buffer_pool_size

  • 主要缓存innodb表的索引，数据，插入数据时的缓存。 是Innodb加速优化最重要的参数，默认分配只有8M，必须配置。
  • 类似于MyISAM的key_buffer_size，但MyISAM只缓存索引，不缓存数据。
  • 如果是一个专用DB服务器，建议分配到物理内存的60%-80%。
  • 这个参数在5.6不能动态更改，在5.7可以在线更改，但需要在业务低估时更改。
1
innodb_buffer_pool_size=物理内存60%~80%

innodb_buffer_pool_instances

  • InnoDB缓存池被分成的区域数
  • 需配置成innodb_buffer_pool_size的整数倍，一般配置为4,8，最多不超过16
1
innodb_buffer_pool_instances=8

innodb_buffer_pool_chunk_size

MySQL5.7新特性：innodb-buffer-pool-size新特性

尽量让innodb_buffer_pool_size = innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 从而获取一个较佳的性能，5.7的参数

二、日志相关

innodb_log_file_size

指定在一个日志组中，每个log的大小。

结合innodb_buffer_pool_size设置其大小，25%-100%。避免不需要的刷新。

注意：这个值分配的大小和数据库的写入速度，事务大小，异常重启后的恢复有很大的关系。一般取256M，可以兼顾性能和recovery的速度。

分配原则：几个日志成员大小加起来差不多和你的 innodb_buffer_pool_size 相等。上限为每个日志上限大小为4G.一般控制在几个Log文件相加大小在2G以内为佳。具体情况还需要看你的事务大小，数据大小为依据。

说明：这个值分配的大小和数据库的写入速度，事务大小，异常重启后的恢复有很大的关系。
设置方法：在my.cnf文件里：

1
innodb_log_file_size = 256M

innodb_log_files_in_group

指定你有几个日志组，默认为两个。一般我们可以用2-3个日志组。

1
innodb_log_files_in_group=3

innodb_log_buffer_size

  • 事务在内存中的缓冲，也就是日志缓冲区的大小
  • 默认8M即可，具有大量事务的可以考虑设置为16M。
  • 另外如果你需要处理大理的TEXT，或是BLOB字段，可以考虑增加这个参数的值。
1
innodb_log_buffer_size=8M

innodb_flush_log_at_trx_commit

  • 控制事务的提交方式，也就是控制log的刷新到磁盘的方式。
  • 这个参数只有3个值[0|1|2]，默认为1，性能更高的可以设置为0或是2，这样可以适当的减少磁盘IO（但会丢失一秒钟的事务）
    • innodb_flush_log_at_trx_commit=1 innodb 的事务log在每次提交后写入日志文件，并对日志做刷新到磁盘。这个可以做到不丢任何一个事务。
    • innodb_flush_log_at_trx_commit=2 在每个提交，日志缓冲被写到文件，但不对日志文件做到磁盘操作的刷新,在对日志文件的刷新在值为2的情况也每秒发生一次。但需要注意的是，由于进程调用方面的问题，并不能保证每秒100%的发生。从而在性能上是最快的。但操作系统崩溃或掉电才会丢失最后一秒的事务。
    • innodb_flush_log_at_trx_commit=0 日志缓冲每秒一次地被写到日志文件，并且对日志文件做到磁盘操作的刷新，但是在一个事务提交不做任何操作。mysqld进程的崩溃会删除崩溃前最后一秒的事务。 
1
2
3
4
5
6
7
# 主库，优先数据安全
innodb_flush_log_at_trx_commit=1
sync_binlog=1

# 从库，可以都等于1，保证数据安全。也可以提高性能，按如下配置。
innodb_flush_log_at_trx_commit=2
sync_binlog=10

三、文件IO分配，空间占用相关

innodb_file_per_table=1

  • 开启独立表空间
  • 当这个参数启用的时候，InnoDB会将新建表的数据和索引单独存放在.ibd格式的文件中，而不是存放在系统表空间中。当这张表被删除或TRUNCATE时，InnoDB表所占用的存储会被释放。这个设定会开启InnoDB的一些其他特性，比如表的压缩。
  • 当这个参数关闭的时候，InnoDB会将表和索引的数据存放到系统表空间的ibdata文件中，这会有一个问题，因为系统表空间不会缩小，这样设置会导致空间无法回放。
1
innodb_file_per_table=1

innodb_open_files=65535

  • 限制Innodb能打开的表的数据。
  • 这个值默认是300。如果库里的表特别多的情况，可以适当增大为1000。
1
innodb_open_files = 65535

innodb_data_file_path=ibdata1:1G:autoextend

  • innodb共享表空间位置及大小：
    指定表数据和索引存储的空间，可以是一个或者多个文件。最后一个数据文件必须是自动扩充的，也只有最后一个文件允许自动扩充。
  • 当空间用完后，自动扩充数据文件就会自动增长（以8MB为单位）以容纳额外的数据。
  • 例如： innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:50M:autoextend 两个数据文件放在不同的磁盘上。数据首先放在ibdata1 中，当达到900M以后，数据就放在ibdata2中。
1
2
innodb_data_file_path = ibdata1:1G:autoextend
innodb_data_file_path = ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:1G:autoextend

四、其他相关参数

innodb_flush_method

  • Innodb和系统打交道的一个IO模型 
1
innodb_flush_method=O_DIRECT

innodb_max_dirty_pages_pct

  • 当Innodb缓存池中脏页所占的百分比达到这个参数的值时，InnoDB会从缓存中向磁盘写入数据。默认值是75。
  • 这个参数的另一个用处：当Innodb的内存分配过大，致使Swap占用严重时，可以适当的减小调整这个值，使达到Swap空间释放出来。
  • 建义：这个值最大在90%，最小在15%。太大，缓存中每次更新需要致换数据页太多，太小，放的数据页太小，更新操作太慢。
1
innodb_max_dirty_pages_pct=50

innodb_thread_concurrency

  • InnoDB存储引擎可以并发使用的最大线程数。
  • 当InnoDB使用的线程超过这参数的值时，后面的线程会进入等待状态，以先进先出的算法来处理。等待锁的线程不计入这个参数的值。这个参数的范围是0~1000。
  • 默认值是0。当这个参数为0时，代表InnoDB线程的并发数没有限制，这样会导致MySQL创建它所需要的尽可能多的线程。
  • 5.6以后为0，让mysqld自动调整
1
2
3
innodb_thread_concurrency=0

也有推荐设置成cpu核心数