ITPub博客

首页 > 数据库 > MySQL > Mysql-关键参数说明

Mysql-关键参数说明

原创 MySQL 作者:wzgchen 时间:2015-07-22 09:56:04 0 删除 编辑


1、选择Percona或MariaDB版本的话,强烈建议启用thread pool特性,可使得在高并发的情况下,性能不会发生大幅下降。此外,还有extra_port功能,非常实用, 关键时刻能救命的。还有另外一个重要特色是 QUERY_RESPONSE_TIME 功能,也能使我们对整体的SQL响应时间分布有直观感受;


2、设置default-storage-engine=InnoDB,也就是默认采用InnoDB引擎,强烈建议不要再使用MyISAM引擎了,InnoDB引擎绝对可以满足99%以上的业务场景;


3、调整innodb_buffer_pool_size大小,如果是单实例且绝大多数是InnoDB引擎表的话,可考虑设置为物理内存的50% ~ 70%左右;


4、根据实际需要设置innodb_flush_log_at_trx_commit、sync_binlog的值。如果要求数据不能丢失,那么两个都设为1。如果允许丢失一点数据,则可分别设为2和10。而如果完全不用care数据是否丢失的话(例如在slave上,反正大不了重做一次),则可都设为0。这三种设置值导致数据库的性能受到影响程度分别是:高、中、低,也就是第一个会另数据库最慢,最后一个则相反;
innodb_flush_log_at_trx_commit:
innodb_flush_log_at_trx_commit = 0,Innodb 中的Log Thread 没隔1 秒钟会将log buffer中的数据写入到文件,同时还会通知文件系统进行文件同步的flush 操作,保证数据确实已经写入到磁盘上面的物理文件,当MySQL Crash 和OS Crash 或者主机断电之后,最极端的情况是丢失1 秒时间的数据变更。
innodb_flush_log_at_trx_commit = 1,这也是Innodb 的默认设置。我们每次事务的结束都会触发Log Thread 将log buffer 中的数据写入文件并通知文件系统同步文件
这个设置是最安全的设置,能够保证不论是MySQL Crash 还是OS Crash 或者是主机断电都不会丢失任何已经提交的数据。
innodb_flush_log_at_trx_commit = 2,当我们设置为2 的时候,Log Thread 会在我们每次事务结束的时候将数据写入事务日志,但是这里的写入仅仅是调用了文件系统的文件写入操作。而我们的文件系统都是有缓存机制的,所以Log Thread 的这个写入并不能保证内容真的已经写入到物理磁盘上面完成持久化的动作。文件系统什么时候会将缓存中的这个数据同步到物理磁盘文件Log Thread 就完全不知道了。所以,当设置为2 的时候,MySQL Crash 并不会造成数据的丢失,但是OS Crash 或者是主机断电后可能丢失的数据量就完全控制在文件系统上了。



sync_binlog:
sync_binlog 的默认值是0,像操作系统刷其他文件的机制一样进行flush,MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。
当sync_binlog =N (N>0) ,MySQL 在每写 N次 二进制日志binary log时,会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。

sync_binlog=0,当事务提交之后,MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘,而让Filesystem自行决定什么时候来做同步,或者cache满了之后才同步到磁盘。
sync_binlog=n,当每进行n次事务提交之后,MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
在MySQL中系统默认的设置是sync_binlog=0,也就是不做任何强制性的磁盘刷新指令,这时候的性能是最好的,但是风险也是最大的。因为一旦系统Crash,在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候,是最安全但是性能损耗最大的设置。因为当设置为1的时候,即使系统Crash,也最多丢失binlog_cache中未完成的一个事务

双1适合数据安全性要求非常高,而且磁盘IO写能力足够支持业务,比如订单,交易,充值,支付消费系统。双1模式下,当磁盘IO无法满足业务需求时 比如11.11 活动的压力。推荐的做法是 innodb_flush_log_at_trx_commit=2 ,sync_binlog=N (N为500 或1000) 且使用带蓄电池后备电源的缓存cache,防止系统断电异常。



5、设置innodb_file_per_table = 1,使用独立表空间,我实在是想不出来用共享表空间有什么好处了;
6、设置innodb_data_file_path = ibdata1:1G:autoextend,千万不要用默认的10M,否则在有高并发事务时,会受到不小的影响;
7、设置innodb_log_file_size=256M,设置innodb_log_files_in_group=2,基本可满足90%以上的场景;
8、设置long_query_time = 1,而在5.5版本以上,已经可以设置为小于1了,建议设置为0.05(50毫秒),记录那些执行较慢的SQL,用于后续的分析排查
9、根据业务实际需要,适当调整max_connection(最大连接数)、max_connection_error(最大错误数,建议设置为10万以上,而open_files_limit、innodb_open_files、table_open_cache、table_definition_cache这几个参数则可设为约10倍于max_connection的大小;
10、常见的误区是把tmp_table_size和max_heap_table_size设置的比较大,曾经见过设置为1G的,这2个选项是每个连接会话都会分配的,因此不要设置过大,否则容易导致OOM发生;其他的一些连接会话级选项例如:sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size等,也需要注意不能设置过大;
11、由于已经建议不再使用MyISAM引擎了,因此可以把key_buffer_size设置为32M左右,并且强烈建议关闭query cache功能;
12.提高刷新脏页数量和合并插入数量:innodb_io_capacity
innodb_io_capacity=2000
innodb_io_capacity_max=6000
InnoDB存储引擎最多都只会刷新100个脏页到磁盘,合并20个插入缓冲。如果是在密集写的应用程序中,每秒中可能会产生大于100个的脏页,或是产生大于20个插入缓冲,此时master thread似乎会“忙不过来”,或者说它总是做得很慢。即使磁盘能在1秒内处理多于100个页的写入和20个插入缓冲的合并,由于hard coding,master thread也只会选择刷新100个脏页和合并20个插入缓冲。同时,当发生宕机需要恢复时,由于很多数据还没有刷新回磁盘,所以可能会导致恢复需要很快的时间
在合并插入缓冲时,合并插入缓冲的数量为innodb_io_capacity数值的5%。
在从缓冲区刷新脏页时,刷新脏页的数量为innodb_io_capacity。

innodb_io_capacity     磁盘配置
200                          单盘:SAS
2000                        SAS*12 RAID10
5000                        SSD
50000                      FUSION-IO

13.组提交配置 
从mysql 5.5.x开始,会采用组提交的方式来将事务刷入磁盘,如果同时多个用户同时提交事务,哪么就合并在一起一次性的刷入磁盘,大大的提高了吞吐量。组提交工作模式目前只支持以下模式:
sync_binlog=0
innodb_support_xa=0

14.动态关闭innodb更新元数据的统计功能
参数作用:每当查询information_schema元数据库里的表时,innodb都会随机提取其它数据库每个表索引页的部分数据,从而更新information_schema.statistics表,当表很大,且数量很多时,耗费的时间就会很长,且Analyze table和show table status也会造成该操作。
innodb_stats_on_metadata=off


15.中继日志的自我修复,只适合从库
relay_log_recovery=1

16.启用独立清除线程
innodb_purge_threads=1 (默认己开启)
innodb_purge_batch_size=300 (默认值)开启独立线程清除undo页时,表示一次删除多少个页

17.调整mysql后台读写线程数量
有了更多的读写线程的支持,也相应地增加了innodb更大的处理能力,建议配置跟CPU CORE相近,同时参考数据库的读写比例进行调整,如果读比较多,就增加读线程数,用于并发。
innodb_read_io_threads=8
innodb_write_io_threads=8

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/91975/viewspace-1744019/,如需转载,请注明出处,否则将追究法律责任。

上一篇: sysbench压力测试
请登录后发表评论 登录
全部评论

注册时间:2015-05-01

  • 博文量
    383
  • 访问量
    181181