MySQL 5.7 优化InnoDB配置以及调优方案

2018年6月18日09:13:15 发表评论

MySQL 5.7 优化InnoDB配置以及调优方案

InnoDB引擎在mysql应用中使用的非常广泛,一点PHP博客通过本文分享关于Mysql5.7中InnoDB的配置以及调优方案。

InnoDB给MySQL提供了具有提交,回滚和崩溃恢复能力的事务安全(ACID兼容)存储引擎。InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读。这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要,因为在InnoDB中行级锁定适合非常小的空间。InnoDB也支持FOREIGN KEY强制。在SQL查询中,你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来,甚至在同一个查询中也可以混合。

在进行优化前,我们先确认目前数据库的配置,命令如下:

 

一、innodb_buffer_pool_size

这个是Innodb最重要的参数,主要作用是缓存innodb表的索引,数据,插入数据时的缓冲,默认值为128M。 如果是一个专用DB服务器,那么它可以占到内存的70%-80%。
并不是设置的越大越好。设置的过大,会导致system的swap空间被占用,导致操作系统变慢,从而减低sql查询的效率。如果你的数据比较小,那么可分配是你的数据大小+10%左右做为这个参数的值。例如:数据大小为50M,那么给这个值分配innodb_buffer_pool_size=64M就够了。
设置方法:在my.cnf文件里:innodb_buffer_pool_size=4G

 

二、innodb_log_file_size
这个参数指定在一个日志组中,每个log的大小。innodb的logfile就是事务日志,用来在mysql crash后的恢复.所以设置合理的大小对于mysql的性能非常重要,直接影响数据库的写入速度,事务大小,异常重启后的恢复。在mysql 5.5和5.5以前innodb的logfile最大设置为4GB,在5.6以后的版本中logfile最大的可以设为512GB。一般取256M可以兼顾性能和recovery的速度。
设置方法:在my.cnf文件里:innodb_log_file_size=256M

事务在内存中的缓冲,也就是日志缓冲区的大小, 默认设置即可,具有大量事务的可以考虑设置为16M。

 

三、innodb_flush_log_at_trx_commit
控制事务的提交方式,也就是控制log的刷新到磁盘的方式。这个参数只有3个值(0,1,2).默认为1,性能更高的可以设置为0或是2,这样可以适当的减少磁盘IO(但会丢失一秒钟的事务。),游戏库的MySQL建议设置为0。主库请不要更改了。 其中:
0:log buffer中的数据将以每秒一次的频率写入到log file中,且同时会进行文件系统到磁盘的同步操作,但是每个事务的commit并不会触发任何log buffer 到log file的刷新或者文件系统到磁盘的刷新操作;
1:(默认为1)在每次事务提交的时候将logbuffer 中的数据都会写入到log file,同时也会触发文件系统到磁盘的同步;
2:事务提交会触发log buffer 到log file的刷新,但并不会触发磁盘文件系统到磁盘的同步。此外,每秒会有一次文件系统到磁盘同步操作。

说明: 这个参数的设置对Innodb的性能有很大的影响,所以在这里给多说明一下。

当这个值为1时:innodb 的事务LOG在每次提交后写入日志文件,并对日志做刷新到磁盘。这个可以做到不丢任何一个事务。

当这个值为2时:在每个提交,日志缓冲被写到文件,但不对日志文件做到磁盘操作的刷新,在对日志文件的刷新在值为2的情况也每秒发生一次。但需要注意的是,由于进程调用方面的问题,并不能保证每秒100%的发生。从而在性能上是最快的。但操作系统崩溃或掉电才会删除最后一秒的事务。

当这个值为0时:日志缓冲每秒一次地被写到日志文件,并且对日志文件做到磁盘操作的刷新,但是在一个事务提交不做任何操作。mysqld进程的崩溃会删除崩溃前最后一秒的事务。
从以上分析,当这个值不为1时,可以取得较好的性能,但遇到异常会有损失,所以需要根据自已的情况去衡量。

 

四、innodb_flush_method
这个参数控制着innodb数据文件及redo log的打开、刷写模式。有三个值:fdatasync(默认),O_DSYNC,O_DIRECT 。

默认是fdatasync,调用fsync()去刷数据文件与redo log的buffer。

为O_DSYNC时,innodb会使用O_DSYNC方式打开和刷写redo log,使用fsync()刷写数据文件。

为O_DIRECT时,innodb使用O_DIRECT打开数据文件,使用fsync()刷写数据文件跟redo log。在unix操作系统中,文件的打开方式为O_DIRECT会最小化缓冲对io的影响,该文件的io是直接在用户空间的buffer上操作的,并且io操作是同步的,因此不管是read()系统调用还是write()系统调用,数据都保证是从磁盘上读取的。

innodb_flush_method=O_DIRECT

 

MySQL 5.7 提供了更加合适的默认值,一般情况下只要调整下面 3 个选项就可以了,其余参数根据实际情况再进行配置。

#################
innodb_buffer_pool_size=8G
innodb_log_file_size=256M
innodb_flush_method=O_DIRECT
linux服务器,内存是32G的,因为还部署了其他应用,所有这里buffer_pool_size就设置了8G。

 

一点PHP,每天一点技术分享。

x

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: