登录
首页 >  数据库 >  MySQL

MySQL配置再学习（转载）

来源：SegmentFault

时间：2023-01-12 18:49:21 197浏览 收藏

亲爱的编程学习爱好者，如果你点开了这篇文章，说明你对《MySQL配置再学习（转载）》很感兴趣。本篇文章就来给大家详细解析一下，主要介绍一下MySQL，希望所有认真读完的童鞋们，都有实质性的提高。

原文链接：https://segmentfault.com/a/1190000023535567

简介

之前这篇文章MySQL配置基础简要说明了MySQL的配置基础，包括配置文件的位置、配置项的分段、配置变量的生效、以及配置变量和状态变量的查看，对MySQL的配置有了一个基础。
现在则会进一步了解更多底层原理，搞清楚更多配置的含义和作用。

本文为《高性能MySQL》读书笔记，配合文档查阅更佳： https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

InnoDB IO配置

InnoDB 事务日志：

InnoDB在每个事务提交的时候，不会把缓冲池的内容立即刷到磁盘。而是从缓冲池将事务记录到事务日志中，输出日志(持久化)，再由事务日志完成写磁盘的操作。
事务日志会把数据文件的随机I/O转换成几乎顺序的I/O，而且把刷新到磁盘的操作转移到后台，从而让查询更快。
事务日志有固定的大小，采用环形方式写（写到末尾时跳转到开头继续写）。定期将缓冲池传来的事务通过日志方式持久化，再将脏数据刷到磁盘中。

缓冲池  事务日志  磁盘
先将事务变成日志，写入磁盘(持久化)，再慢慢根据日志内容写入磁盘。

事务日志的配置：

事务日志依赖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        |
+---------------------------+----------+

事务日志本身的写入缓存：

事务日志，将事务写入日志文件的时候，也并不是直接写入的，而是使用写入缓存。先写入缓存中，再由缓存定期写入文件。由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 |
+------------------------+----------+

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()允许积累写操作到缓存，再一次性刷新数据。]

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_method

更多可参考：https://www.cnblogs.com/CNty/p/10943626.html
InnoDB I/O配置文档：https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-diskio.html

InnoDB 表空间配置【施工中】

双写缓冲(DoubleWrite)配置【施工中】

MySQL 高并发【施工中】

出现高并发时，如何发现问题？

出现高并发时，如何获得更好的性能？

线程进入内核阶段-并发瓶颈；

innodb_thread_concurrency：限制一次性可以有多少线程进入内核。（0表示不限制

推荐值：并发值 = CPU 数量 * 磁盘数量 * 2【实际建议设置稍小的值，再行调整

两段处理：

如果已进入内核的线程过多，则新线程无法进入内核。会使用两段处理：

两段处理可以减少操作系统导致的上下文切换。

1、如果未能进入内核，则开始第一次休眠，时间为：innodb_thread_sleep_delay。休眠结束后重试。

2、如果仍未进入内核，则将这个线程加入一个等待线程队列，让操作系统处理。

【如果有很多小查询，innodb_thread_sleep_delay可以考虑适当减小。这相当于10毫秒的查询延时。

提交阶段-并发瓶颈：
innodb_commit_concurrency：同一时间提交的线程的数量 上限。
线程池？

安全和稳定配置

  • expire_logs_days：如果使用Binlog就应该打开 ，保存二进制日志的天数。清理过期的日志。根据实际情况，在保证备份的情况下去配置。（主动声明的意义在于，避免二进制日志打满磁盘，如果有需要，可以增长保存时间）
  • max_allowed_packet：禁止服务器收发过大的数据包。

  • max_connect_errors：如果某个主机连续x个连接失败，则会被BAN掉。一旦被BAN，只能命令刷新缓存才能解除。不建议设置的过小，以免出现所有应用程序被BAN的情况。

    https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_connect_errors
  • skip_name_resolve：关闭DNS查找。MySQL在连接时，会正向/反向查DNS，确认连接主机的主机名，根据实际情况判断是否关闭。

备库相关配置

  • read_only：备库强烈建议开启只读，只接受从主库传输过来的变更。
  • skip_slave_start：阻止MySQL自动启动复制，如果备库出现问题重启，绝对不能自动复制，要手动确认检查后才可。
  • slave_net_timeout：备库连接主库失败时，重连前等待时间.默认60分钟太长了，建议改为1分钟或者更短。

备库日志同步写磁盘相关：
【这3个变量都是动态变量，一旦设置，会立即对所有备库生效。】

其他常见配置

  • 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倍开始调整。

以上就是本文的全部内容了，是否有顺利帮助你解决问题？若是能给你带来学习上的帮助，请大家多多支持golang学习网！更多关于数据库的相关知识，也可关注golang学习网公众号。

mysql MySQL
声明：本文转载于：SegmentFault 如有侵犯，请联系study_golang@163.com删除
相关阅读
更多>
最新阅读
更多>
课程推荐
更多>
评论列表