1、简介
之前这篇文章MySQL配置基础简要说明了MySQL的配置基础,包括配置文件的位置、配置项的分段、配置变量的生效、以及配置变量和状态变量的查看,对MySQL的配置有了一个基础。
现在则会进一步了解更多底层原理,搞清楚更多配置的含义和作用。
2、InnoDB IO配置
2.1、InnoDB 事务日志:
InnoDB在每个事务提交的时候,不会把缓冲池的内容立即刷到磁盘。而是从缓冲池将事务记录到事务日志中,输出日志(持久化),再由事务日志完成写磁盘的操作。
事务日志会把数据文件的随机I/O转换成几乎顺序的I/O,而且把刷新到磁盘的操作转移到后台,从而让查询更快。
事务日志有固定的大小,采用环形方式写(写到末尾时跳转到开头继续写)。定期将缓冲池传来的事务通过日志方式持久化,再将脏数据刷到磁盘中。缓冲池 事务日志 磁盘先将事务变成日志,写入磁盘(持久化),再慢慢根据日志内容写入磁盘。
2.2、事务日志的配置:
事务日志依赖innodb_log_file_size和innodb_log_files_in_groups这两个变量。前者声明每个日志文件的大小,后者声明日志文件的个数。
InnoDB会使用多个日志文件作为循环日志(1号文件写完了写2号,2号写完了写1号)默认是50M,2个文件。共100M。建议增大单个文件的大小,仍然使用2个文件。(本文使用MySQL 8.0.21 不同版本默认参数可能不一致)mysql> show variables where variable_name like "%inno%log_file%";
+---------------------------+----------+
| Variable_name | Value |
+---------------------------+----------+
| innodb_log_file_size | 52428800 |
| innodb_log_files_in_group | 2 |
+---------------------------+----------+
2.3、事务日志本身的写入缓存:
事务日志,将事务写入日志文件的时候,也并不是直接写入的,而是使用写入缓存。先写入缓存中,再由缓存定期写入文件。由innodb_log_buffer_size 参数决定使用写入缓存大小。
以下3个条件,满足任一条件就会刷新缓存到日志文件中。每隔1秒
写入缓存满
有事务提交MySQL 8.0.21 默认大小为16M。由于最长每1秒会刷新一次写入缓存,所以这个参数不用设置的过大,只需要超过每秒产生的事务量即可。mysql> show variables where variable_name like "%inno%log_b%";
+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+
2.4、InnoDB和文件系统的交互方式
InnoDB和磁盘的读写交互都通过innodb_flush_method来选择。主要有如下几种:fdatasync
O_DIRECT
O_DSYNC
1、fdatasync():和fsync()类似,但是只刷新文件数据本身,不包括元数据。而且,这个选项会使用使用双重缓冲(包括操作系统这一层的缓存)
2、O_DIRECT:依然使用fsync()来刷新文件到磁盘,但是会关闭操作系统缓存,告知操作系统不要缓存且不要预读。(所有读写都直接到达存储设备,避免双重缓冲)这个设置只会影响操作系统,不会影响RAID卡的预读。
如果使用这个选项,最好使用带预读的RAID卡,且打开写回(write_back)
3、O_DSYNC
这个选项会使所有写同步,或者说,只有数据确切写到磁盘后,写操作才会返回。
每个write()或pwrite()操作都会函数完成前将数据同步到磁盘,且这个过程是阻塞的。[而fsync()允许积累写操作到缓存,再一次性刷新数据。]
3、InnoDB 表空间配置
3.1、InnoDB表空间简介:
表空间的本质是一个虚拟文件系统,由多个文件组成。InnoDB用表空间实现了很多功能,不仅有存储表和索引,还有回滚日志,插入缓存(Insert Buffer),双写缓冲(Doublewrite Buffer)等。
3.2、基础配置:
可以使用如下2个参数来配置表空间。innodb_data_home_dir:表空间文件存储位置。
innodb_data_file_path:定制表空间文件的名称大小等。
下面是一个例子innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:1G;ibdata2;1G;ibdata3:1G
PS:innodb_data_home_dir的默认值是空,和MySQL的data同目录。
The default value is the MySQL data directory.(也就是datadir变量。mysql> show variables where variable_name like "datadir";
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| datadir | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)定制表空间文件时,可以选择使用autoextend来让表空间可以自动扩展,即使超过了分配的空间。(但是一旦扩展了就很难收缩,不建议使用extend)
回收空间的唯一办法就是导出数据、重启MySQL、重建、再重新导入。(或者说,没法在不重新导入全部数据的前提下给共享表空间瘦身。
3.3、共享表空间与独立表空间
innodb_file_per_table 可以让InnoDB为每张表使用一个文件。另外,即使打开这个选项,依旧会为回滚日志和其他系统数据使用共享表空间(也就是说,这个选项主要是针对业务的表,将其分文件存储。系统数据还是使用默认的共享表空间)注意,使用独立表空间会导致一个问题,DROP TABLE性能会很差。可能会导致服务器阻塞。
建议:打开innodb_file_per_table,每个数据表一个文件。并且关闭共享表空间的自动扩展。毕竟每个表一个文件,管理起来还是好很多。只要没有大量的小表。再加上DROP TABLE的性能差。换句话说,也就是表数量没有太大变动+没有大量小表,就很适合用独立表空间。
4、双写缓冲(DoubleWrite)配置
磁盘和InnoDB交互期间,都会先写入双写缓冲,再写入目标(本质上,双写缓冲就是一个最近写回的页面的备份拷贝。由于InnoDB写页面到双写缓冲时,是顺序写入的,所以性能影响并没有那么大。Disk --> DoubleWrite --> InnoDB
如果写入到双写缓冲时不完整,但是磁盘中必定是有完整数据的。出现问题时可恢复。
如果写入到InnoDB时不完整,可以用双写缓冲的数据验证。
InnoDB --> DoubleWrite --> Disk
与上述类似,双写缓冲会保证数据可恢复。
可以使用innodb_doubewrite来设置为0来关闭双写(这对于备库比较合适)
5、安全和稳定配置expire_logs_days:如果使用Binlog就应该打开 ,保存二进制日志的天数。清理过期的日志。根据实际情况,在保证备份的情况下去配置。(主动声明的意义在于,避免二进制日志打满磁盘,如果有需要,可以增长保存时间)
max_allowed_packet:禁止服务器收发过大的数据包。
skip_name_resolve:关闭DNS查找。MySQL在连接时,会正向/反向查DNS,确认连接主机的主机名,根据实际情况判断是否关闭。
6、备库相关配置read_only:备库强烈建议开启只读,只接受从主库传输过来的变更。
skip_slave_start:阻止MySQL自动启动复制,如果备库出现问题重启,绝对不能自动复制,要手动确认检查后才可。
slave_net_timeout:备库连接主库失败时,重连前等待时间.默认60分钟太长了,建议改为1分钟或者更短。
备库日志同步写磁盘相关:
【这3个变量都是动态变量,一旦设置,会立即对所有备库生效。】
7、其他常见配置tmp_table_size和max_heap_table_size:
如果隐式内存临时表超过这两个设置,则会转换为硬盘表。需要关注Created_tmp_disk_tables和Created_tmp_tables,这两个磁盘临时表状态,来确认。
这两个参数可以简单的配置为一样的大小,不建议设置的过大。如果临时表过大,使用磁盘比使用硬盘好,免得内存溢出。
max_connections
更像是紧急刹车,保证数据库不会因为应用程序连接数突增导致自身不堪重负。(保护数据库本身)当出现问题,导致新建过多新连接时,把多余的错误链接拒绝掉,是一种快速、低代价的失败方式。
这个参数应该设置的足够高(可以处理正常情况下的负载,服务正常运行),其次也要足够安全(保证可以登录上服务器,进行维护操作)比如,正常情况下有300连接数,那么这个值必然不能低于300(保证服务正常运行),可以考虑400~500.
Max_used_connections:这个状态是最高连接数的值。同时还有一个Max_used_connections_time,声明时间。
你可以清楚的知道,连接数的峰值在什么时候。
thread_cache_size
线程缓存要根据实际状态来制定。比如Threads_connected、Threads_created、Threads_cached。
可以根据正在连接中的线程数来估测缓存大小。如果每秒创建的线程数很多或者每秒创建的线程越来越多,那么就需要增大缓存。有时候,也需要根据已经缓存了的线程数来判断缓存大小是否合适。
table_cache_size
表缓存建议配置的足够大,避免需要经常重新打开、重新解析表定义。(如果表不是很多,完全可以配置一个大的表缓存,把所有表结构都缓存了。
相关状态:Opened_tables,如果已打开的表一直在增长,建议适当增大表缓存。
值推荐:1、不建议这个值超过10 000。2、建议从连接数的10倍开始调整。
小结
本文主要是对MySQL配置项进行了进一步学习,主要有InnoDB的IO以及Inno表空间配置(共享表空间和独立表空间的了解),还有一些常见的配置、安全相关的配置、以及备库相关配置。
MySQL的配置项很多,非常灵活,有些还能动态生效。所以更需要深入了解才能更好的使用。这是一个持续的过程。