主要依据MySQL部署机器的硬件优化其部署配置。
innodb_buffer_pool_size
一般初始设置为内存总量的50%-70%(后续根据buffer使用量进行调整);
无需设置其大于数据库的总容量。
innodb_log_file_size
一般设置在128M-2G之间;
应该足够大以容纳最多一个小时左右的日志
这足以保证MySQL在刷盘和检查点过程中以顺序IO形式记录日志。
建议后续根据日志空间使用率调整日志文件大小,尽量保证使用率低于50%。
innodb_flush_log_at_trx_commit
设置为1:可提供最高的持久性保证(MySQL 5.7版本中默认设置);
设置为0或者2:性能表现更好,但是持久性保证较低(在例如MySQL集群模式中,可考虑此设置)。
innodb_flush_method
将其设置为O_DIRECT,可以降低因为写盘双缓冲的带来的性能损失;
innodb_file_per_table
设置为ON,为数据库中的每个表生成一个独立的InnoDB表空间;
innodb_stats_on_metadata
设置为“OFF”,可避免非必要的InnoDB统计信息更新,并较大幅度提升读取速率;
innodb_buffer_pool_instances
最佳实践是将其设置为8(若innodb_buffer_pool_size小于1G,设置为1);
query_cache_type & query_cache_size
在8以下版本中,将其设置为0,避免使用查询缓存。
innodb_io_capacity / innodb_io_capacity_max
如果数据库写入频繁,可根据数据盘的写入性能针对性优化此数值,建议根据sysbench或者其他硬盘测试工具以确定硬盘写入性能;
此数值,对数据读取影响较小。