在本博客中,我们将会讨论在安装数据库以后可以调整的影响MySQL性能的十个参数设置。本文主要内容翻译自 MySQL Performance Tuning Settings ,为了便于理解,对于其中部分涉及到的知识,我在查阅相关资料的基础上做了补充,并且也增加了相关的一些验证。

本文所使用的云服务器配置为 1核CPU 1G内存 25G SSD硬盘,其中SSD硬盘用测试软件测试的顺序写的速率是120M每秒。

在进行MYSQL性能调优之前

即使是一个有经验的人也会犯一些会造成很大麻烦的失误。因此在应用本篇文章的建议之前,请牢记以下几点

  • 一次只改一个设置!这是判断修改的配置是否有用的唯一方法
  • 大多数设置可以在运行的时候用SET GLOBAL来进行更改。这个非常方便,而且可以快速撤销更改。但是最终可能还是要在配置文件中修改。
  • 修改了以后即使重启数据库也没有生效?你是否使用了正确的配置文件?或者是否将其放在了正确的部分?本文中的所有配置都在[mysqld]下
  • 在修改了配置以后数据库启动不起来了?你是否使用了正确的单位?例如innodb_buffer_pool_size应该是按照字节设置的,而max_connection是没有单位的
  • 不要在配置文件中重复进行设置。如果想要跟踪修改,请用版本控制
  • 不要做纯数学计算。例如“我的新服务器有两倍的RAM,我只要把以前的配置都乘以2就行了”

基本配置

这里有3个应该经常查看的配置。如果没有调整的话,可能很快就出现问题。

innodb_buffer_pool_size 这是在安装使用InnoDB时应该首先查看的一个配置。缓冲池是数据和索引被缓存的地方:让它尽可能大来确保数据库在大多数读操作的情况下使用的是内存而不是磁盘。通常的设置是5-6GB(8GB内存)。20-25GB(32GB内存),100-120GB(128GB内存)

innodb_log_file_size redo日志的大小。redo日志用来确保写入是快速而且持久的,并且能够从故障中恢复。在MySQL5.1之前,这个很难调整,因为这个值设置大的话可以保证性能,但是设置的小一点可以进行更快的故障恢复。幸运的是,在MySQL5.5版本以后,崩溃恢复的性能有了很大的提高,即可以同时拥有好的写入性能和快速的故障恢复。在MySQL5.5以后,总的redo日志被限制为了4GB(默认有两个日志文件)。这个限制在MySQL5.6以后被取消。

设置成innodb_log_file_size = 512M应该就可以提供足够的写入空间。如果可以预测到应用是写入密集型的,而且使用的是MySQL5.6,那么应该从innodb_log_file_size=4G开始设置。

注:InnoDB处理的是内存中的数据,即对数据的修改和更新都是在内存中进行,因此,为了能够在数据库崩溃或者系统故障之后可以恢复数据,需要将更改记录到redo日志中。因此,简单来说,redo日志越大,InnoDB能够在内存中操作的更改块就越多,如果内存的更改块的大小(checkpoint_age)快要超过redo日志的大小(异步点)的时候,InnoDB就会尝试刷新内存中的块来防止checkpoint_age超过innodb_log_file_size,频繁的刷新会影响到数据库的查询性能。显然,如果redo日志的大小设置的比较小的话,而且写入更新操作比较多的话,checkpoint_age就可能会频繁的到达异步点。不过redo日志也不是设置的越大越好,设置的大的话,会导致数据库的崩溃恢复时间变长。详情请参阅InnoDB Flushing: Theory and solutions

max_connections 如果经常遇到了“太多链接”错误,那就是max_connections设置的太小了。如果程序没有正确的关闭与数据库的连接,那么这个情况就会经常发生。这时候可能需要设置比默认的151大的多的连接数。 max_connections设置的很高(比如1000或者更多),会造成服务器的无响应。在应用程序级别设置连接池或者线程池可能会对此又帮助。

基本配置测试及说明

Innodb_buffer_poll_size

我们先来看一下数据库默认的innodb_buffer_pool_size设置

默认的设置

从上图中,可以看到默认的innodb_buffer_pool_size是128M,默认的innodb_log_file_size是48M。

先用下面的脚本插入一些准备数据。

1
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=test --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable prepare

然后用下面命令来对数据库进行测试

1
sysbench --db-driver=mysql --time=60 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=test --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable run

测试结果如下图

从上图中,大概可以看出每秒的事务数是150,每秒的查询数是3000左右。

然后我们修改innodb_buffer_pool_size的值,再次进行测试

1
set global innodb_buffer_pool_size = 1073741824; // 设置为了1G

这个地方一定要小心,如果设置的太大,mysql可能会吃掉所有的可用内存,然后导致机器卡死。

测试结果如下图

修改以后每秒事务数是220左右,每秒查询数大概在4500左右。与修改之前相比,性能已经有了50%的提升。

innodb_log_file_size

这里只说明怎么设置一个最佳的值。一个粗略的经验法则是日志的大小应该足够大,最多能容得下一个小时的日志。基于这个经验法则来配置innodb_log_file_size的大小。在服务器运行的高峰时期执行下面的查询。

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> pager grep sequence
PAGER set to 'grep sequence'
mysql> show engine innodb status \G; select sleep(60); show engine innodb status \G
Log sequence number 10823613000
1 row in set (0.00 sec)

ERROR:
No query specified

1 row in set (1 min 0.01 sec)

Log sequence number 10887232498
1 row in set (0.00 sec)

根据log sequence number的值来计算每小时写入日志的大小

1
2
3
4
5
6
7
mysql> select (10887232498 - 10823613000) / 1024 / 1024 * 60 as  MB_per_Hour;
+---------------+
| MB_per_Hour |
+---------------+
| 3640.33687592 |
+---------------+
1 row in set (0.00 sec)

然后计算出距离这个值最近的一个2的n次方的值4096,由于默认有两个日志文件,所以实际设置的值应该是一半,即2048M,即如下设置

1
innodb_log_file_size=2048M

本节参考连接How to calculate a good InnoDB log file size

InnoDB的设置

InnoDB在MySQL5.5以后是默认的存储引擎,使用的频率也要比其它的存储引擎要多。这就是为什么要小心配置的原因。

innodb_file_per_table 这个参数用来告诉InnoDB是将表和索引存储在一个共享的表空间中(innodb_file_per_table = OFF) ,还是将每个表存储在一个.ibd文件中 (innodb_file_per_table= ON)。每个表对应一个文件,可以在表被删除、截断或者重建的时候回收空间。对于一些例如压缩之类的高级属性也需要这个特性。当然,这个对提升数据库的性能没有任何帮助。只有在表的数目特别多的时候(例如超过1万),可能才不需要每个表一个文件。

在MySQL5.6版本以后,这个开关默认是ON。

innodb_flush_log_at_trx_commit 这个值默认是1代表完全符合ACID特性,此时对于数据库的每一个更新操作都会记录到redo日志并刷新到磁盘上,因此也是最可靠的,当然,在具有慢速磁盘的系统上可能会有大量开销。在数据安全性比较重要时,设置成1是最合适的选择,例如在master节点上。将其设置为2代表每次提交事务都会记录日志,但是同步到磁盘的操作则是每秒一次,对于副本节点来说,这是一个很好的选择。 将其设置为0,则是每秒写一次日志和刷新到磁盘中,此时数据库的速度会更加快,但是系统崩溃的时候也更容易丢失数据。

innodb_flush_method 这个设置了怎么把数据和磁盘刷新到硬盘上的方法,当您拥有带有电池保护的回写缓存的硬件 RAID 控制器时,这个值一般设置为 O_DIRECT。其它情况下设置为fdatasync(默认值)。可以用sysbench工具来测试选择哪一个值更好一点。

innodb_log_buffer_size:这是尚未提交的事务的缓冲区大小。 默认值 (1MB) 通常是没问题的,但是一旦您的事务中包括了大的 blob/文本字段,缓冲区就会很快被填满并触发额外的 I/O 负载。 查看 Innodb_log_waits 状态变量,如果它不是 0,则增加 innodb_log_buffer_size的值。

1
2
3
4
5
6
7
mysql>  SHOW GLOBAL STATUS where variable_name like 'Innodb_log_waits';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Innodb_log_waits | 0 |
+------------------+-------+
1 row in set (0.00 sec)

其它的设置

query_cache_size 查询缓存是一个众所周知的瓶颈,即使在中等程度的并发下,也可能会被影响到。因此最好将其设置为0来禁用查询缓存。如果使用了查询缓存,而且将其设置的比较大,在并发比较高的情况下,查询缓存区中可能会有很多的缓存数据,此时对于表中任何插入、更新、删除都会导致查询缓存中的相关数据被锁定并刷新。查询缓存越大,用于锁定、刷新的时间就越多,此时,大多数命中查询缓存的查询可能都会卡在“等待查询缓存锁定”状态中,从而影响了数据库的吞吐量。

log_bin:如果您希望服务器充当复制主机,则必须启用二进制日志记录。如果是这样,不要忘记将server_id为唯一值。当您希望能够进行时间点恢复时,它对于单个服务器也很有用:恢复最新的备份并应用二进制日志。一旦创建,二进制日志文件将永远保留。因此,如果您不想用完磁盘空间,您应该使用PURGE BINARY LOGS清除旧文件或设置expire_logs_days为指定在多少天后自动清除日志。

但是,二进制日志不是没有代价的,因此如果您不需要(例如在不是主服务器的副本上),建议将其禁用。

skip_name_resolve: 当客户端连接时,服务器会进行主机名解析,当 DNS 很慢时,建立连接也会变慢。因此,建议启动服务器skip-name-resolve以禁用所有 DNS 查找。唯一的限制是这些GRANT语句只能使用 IP 地址,因此在将此设置添加到现有系统时要小心。