Mysql 性能调优一：服务端

全文参考高性能Mysql这本书，并简单总结

操作系统和硬件优化

• CPU瓶颈：许多不同的硬件组合都会影响Mysql的性能，我们看到最常见的瓶颈时CPU耗尽。当Mysql尝试并行执行太多的查询，或者当少量的查询在CPU上运行太长时间时候，就可能会发生CPU饱和
• 内存瓶颈：内存耗尽的情况也会发生，但通常只在你试图将太多内存分配给MySQL时才会发生。
• I/O瓶颈：I/O饱和也会发生，但比CPU耗尽的频率低很多。这主要是因为普遍使用了固态硬盘（SSD）。过去，由于内存不足，数据库必须到机械硬盘（HDD）中获取数据，性能开销是非常大的。SSD通常比HDD快10到20倍（SSD操作在微秒级，HDD在毫秒级，大致记住这个逻辑）
• 网络瓶颈：

CPU

• CPU要强
• CPU核数要多
  

内存

• 内存要大

配置大内存的主要原因并不是为了在内存中保存大量数据，而是为了避免磁盘I/O，因为磁盘I/O比访问内存中的数据要慢几个数量级。重要的是平衡内存和磁盘空间大小、速度、成本和其他因素，以便让工作负载获得良好的性能

• 缓存读：这个大家应该都知道，Buffer Pool会缓存Mysql数据页
• 缓存写：一次数据片段在内存中被多次更改，而无须每一次都将新值写入磁盘。当数据被最终刷新到磁盘时，自上次物理写入以后发生所有的修改都会被持久化。例如，许多语句可以更新内存中的计数器。如果计数器被更新了100次，然后写入磁盘，则100次内存修改被合并为一次磁盘写入（这里有个write-ahead logging，预写日志。很多存储的中间件都会用到，将随机写改成顺序写）

思考：

• 你的工作集（热点数据）是什么：

每个应用都有一个数据“工作集”，即它真正需要的数据。许多数据库也有大量不在工作集的数据。你可以将数据想象成一个带有存档抽屉的桌子。工作集由你需要放在桌面上处理的文件组成。在这个类比中，桌面表示主内存，而文件抽屉则表示硬盘。就像你不需要把每一张纸都放到桌面上一样，也不需要把整个数据库放进内存来获取最佳性能- 仅仅将工作集放到内存中即可

在使用HDD硬盘时，最好尝试找到一个有效的内存/磁盘比率。这主要是由于HDD延迟较高，IOPS较低。使用SSD时，内存对磁盘的比率就变得不那么重要了

硬盘

• 使用固态：固态为了使某些块保持新鲜并为新的写入做好准备，设备会回收块。这需要设备上有可用空间。要么设备内部有一些不可见的预留空间，要么需要用户预留空间，不要把空间填满。当设备被填满时，垃圾收集器必须更加努力的保持某些块的清洁，因此写放大因子会增加，因此，需要设备在空间快慢时会变慢。100G文件在160G的SSD上的性能与在320G的SSD上的性能是不同的。当没有空闲块的时候，必须要等擦除操作
• RAID性能优化

网络

• 增加本地端口范围： 在Linux系统中，默认的本地端口范围可能不足以处理高并发连接。通过增加可用的端口范围，可以提高并发连接的处理能力。例如：

• 增加TCP连接队列大小：

TCP连接队列大小影响着系统能够排队等待接受的连接请求的数量。当系统接收到大量并发请求时，扩大这个队列可以防止连接失败：

• 调整TCP的TIME_WAIT超时时间：

对于大量短连接，TIME_WAIT状态的连接可能会占用大量的端口。减少这个超时时间可以使端口更快地被重用：

• 增加tcp接受和发送缓冲区的大小：

操作系统

• 选择合适的文件系统（ext4、XFS或ZFS）

文件系统的选择在很大程度上取决于操作系统。很大系统，比如Window，实际上只有一两个选择，而且只有一个（NTFS）是真正可行的。GUN/Linux支持很多文件系统。
很多人想知道在GUN/Linux下，哪个文件系统对Mysql性能最好，或者更具体的说，哪个文件系统对InnoDB性能最好。一些基准测试表明，大多数文件系统在很多方面都非常接近，单纯为性能寻找文件系统实际上是一种干扰。文件系统的性能跟特定的工作负载相关，没有哪个文件是万能的。大多数时候，给定的文件跟其他系统相比不会有明显的差别。只有在某些情况下， 达到文件系统的处理极限时，例如，需要处理高并发性、处理许多文件、碎片等等。不同文件系统的差异才会体现出来。
总体来说，最好使用日志型文件系统，如ext4、XFS或者ZFS。否则，系统崩溃后，检查文件系统可能需要很长时间

• 选择磁盘队列调度器

在GNU/Linux上，队列调度器决定了对块设备的请求实际发送到底层设备的顺序。默认设置为完全公平排队，即CFQ（Complete Fair Queuing）。这在笔记本电脑和台式机上偶尔使用是可以的，因为它有助于防止I/O饥渴，但对于服务器来说很糟糕。在MySQL的工作负载类型下，CFQ会导致非常糟糕的响应时间，因为会不必要地阻塞队列中的一些请求。
可以使用以下命令查看哪些调度器可用，哪些处于活动状态：

执行上述命令时应该用想查看的磁盘的设备名替换其中的sda。在这个示例中，方括号表示该设备正在使用哪个调度器。另外两种选择适用于服务器级硬件，在大多数情况下，它们的表现差不多。noop调度器适合于在后台执行自身调度器的设备，比如硬件RAID控制器和存储区域网络（SAN），而deadline则适用于RAID控制器和直接连接的磁盘。我们的基准测试显示，这两者之间的差别非常小。最重要的还是不要使用CFQ，它可能会导致严重的性能问题

• 内存和交换

总结

服务器硬件：

1. CPU: 确保CPU性能足够处理你的数据库负载，特别是在高并发场景下。
2. 内存: 增加物理内存可以直接影响MySQL的性能，因为更多的内存意味着更大的缓冲区和更少的磁盘I/O需求。
3. 存储: 使用SSD而非传统HDD可以显著提高数据的读写速度，减少I/O瓶颈
4. 网络： 确保服务器之间的网络连接具有足够的带宽和低延迟

操作系统：

1. 文件系统： 使用高性能的文件系统, 如XFS和EXT4，可以提高磁盘I/O性能
2. 内核网络I/O参数调整： 调整操作系统内核参数，如文件描述符限制（网络io）、TCP/IP参数（网络io）
3. 内核内存参数调整： 交换空间（内存swap），建议不使用swap或者开启 Swap 并设置低 swappiness 值（作为应急备用，但减少其实际使用频率，以保障系统稳定性和性能）
4. 内核磁盘参数调整： 磁盘队列调度器（磁盘），禁止使用CFQ（Complete Fair Queuing）
5. 减少系统负载： 避免在数据库服务器上运行不必要的服务和应用程序，减少系统负载，提高数据库性能

Mysql设置

首先要知道的是，Mysql从何处获取配置信息：命令行参数和配置文件中的配置项。在类UNIX系统中，配置文件配置于 /etc/my.cnf或者/etc/mysql/my.cnf。如果使用操作系统的启动脚本，这通常是配置设置的唯一位置。如果手动启动mysql，还可以在命令行上指定设置。需要永久使用的任何设置都应该写入到全局配置文件中，而不是在命令行中指定。否则会有风险，可能会在没有指定命令行选项的情况下意外启动服务器。将所有配置文件保存在一个地方也是一个好主意。

如果说不知道服务器会读取哪些文件，可以通过如下命令查询：

创建Mysql配置文件

最小化配置

我们创建一个最小化的示例配置文件，你可以将其用作自己服务器的良好起点。你必须为一些设置选择值，我们将会在本章后面进行解释。我们的基础文件是围绕Mysql8.0构建的，如图所示：

• user：指定运行MySQL服务器进程的操作系统用户
• port：MySQL服务器监听的端口号，默认为3306
• innodb_buffer_pool_size：设置InnoDB缓冲池的大小，这对性能有很大影响。通常设置为系统内存的60%-80%。
• innodb_log_file_size：设置InnoDB日志文件（Redo log）的大小，影响事务日志的处理
• innodb_file_per_table：启用此选项后，InnoDB为每个表创建独立的表空间文件，有助于管理大型数据库。
• innodb_flush_method：设置InnoDB写入磁盘的方法。O_DIRECT可以减少操作系统缓存的干扰，提高性能。
• log_error = /var/lib/mysql/mysql-error.log：指定错误日志文件的位置，用于记录错误信息。
• log_slow_queries = /var/lib/mysql/mysql-slow.log：指定慢查询日志文件的位置，记录执行时间超过设定阈值的查询。
• tmp_table_size = 32M：设置临时表的最大内存大小。当临时表超过这个大小时，会被转移到磁盘上。
• max_heap_table_size = 32M：设置内存表的最大大小。与tmp_table_size一起决定了内存表和临时表的内存使用。
• max_connections = ：设置同时连接到MySQL服务器的最大客户端数量。
• thread_cache_size = ：设置线程缓存的大小，用于缓存线程，提高响应速度。
• table_open_cache = ：设置表打开缓存的数量，用于提高打开表的效率。
• open_files_limit = 65535：设置MySQL进程可打开的最大文件数。

在Mysql8.0中引入一个新的配置选项，innodb_dedicated_server。这个选项检查服务器上可用的内存，并为专用的数据库服务器配置了4个额外的变量（innodb_buffer_pool_size、innodb_log_file_size、innodb_log_files_in_group和innodb_flush_method），从而简化这些值的计算和更改。这在云环境中特别有用，在云环境中，你可以运行一个128G B内存的虚拟机（V M），然后重新启动以扩展到256G B内存。这种情况下MySQL是自配置的，不需要管理配置文件中的值的修改。这通常是管理这4个设置的最佳方式

配置内存

使用innodb_dedicated_server通常会占用50%～75%的内存。这样，至少有25%的内存可用于每个连接的内存分配、操作系统开销和其他内存设置。

每个连接的内存需求

Mysql只需要少量的内存就能保持一个连接（通常是一个相关的专用线程）打开。它还需要基本内存量来执行任何给定的查询。你需要为Mysql留出足够的内存，以便在负载高峰期执行查询，否则查询将因内存不足而无法正常运行或失败。

了解Mysql在峰值使用期间将消耗多少内存时非常有用的，但是一些使用模式可能会意外的消耗大量内存，这使得内存消耗很难预测。

在试图预测内存消耗峰值时，不需要假设最坏的情况。例如，如果将mysql配置为最多允许100个连接，理论上可以同时在所有100个连接上运行大型查询，但实际上这不太可能会发生。使用需要大型临时表或复杂存储过程的查询最有可能占用大量内存

为操作系统保留内存

与查询意义，需要为操作系统保留足够的内存以完成其工作。这包括运行任何本地监控软件、配置管理工具、计划作业等。操作系统有足够内存的最佳判断依据是，它没有主动将虚拟内存交换（分页）到磁盘

InnodDB缓冲池

InnoDB缓冲池需要的内存比任何其他组件都多，就性能而言，InnnoDB缓冲池大小通常是最重要的变量。InnoDB缓冲池不仅缓存索引，还缓存行数据，自适应哈希索引、更改缓冲区、锁和其他内部结构等。InnoDB还使用缓冲池来实现延迟写操作，从而可以将多个写操作合并在一起并按顺序执行。简而言之，InnoDB严重依赖缓冲池，应该确保为其分配足够的内存。你可以使用SHOW命令或innotop等工具中的变量来监控InnoDB缓冲池的内存使用情况。

线程缓存

线程缓存保存了当前没有与连接关联但已准备好为新连接提供服务的线程。创建新连接时，如果缓存中有一个线程，Mysql会从缓存中取出该线程并将其提供给新连接。当连接关闭时，如果缓存中没有空间，Mysql会将现场放回缓存中，如果缓存中已经没有空间，MySQL会销毁线程。只要MySQL在缓存中有空闲线程，它就可以快速响应连接请求，因为不必为每个新连接创建新线程。

变量thread_cache_size指定了Mysql可以保存在缓存中的线程数。其默认值为-1或auto-sized，通常不需要更改这个变量，除非服务器会收到很多连接请求。要检查线程缓存是否大，请查看Threads_created状态变量。应该尽可能保持线程缓存足够大，以使每秒创建的新线程少于10个，但通常很容易这个数字低于每秒1个

一个好的方法是观察Threads_connected变量，并尝试将thread_cache_size设置得足够大，以处理工作负载中的典型波动。例如，如果Threads_connected通常保持在100到120之间，那么可以将缓存大小设置为20。如果停留在500到700之间，那么将缓存大小设置为200也足够大了。可以这样想：当同时有700个连接时，缓存中线程全部用光，当只有500个连接时，将有200个缓存线程作为备用，即便工作负载随后再增加到700也够用了。对于大多数使用场景来说，不需要将线程缓存设置得非常大，但是将线程缓存设置得很小也不会节省太多内存，所以这样做没有什么好处。每个处于线程缓存或休眠状态的线程通常使用大约256KB内存。这与活动连接在处理查询时线程可以使用的内存量相比并不多。通常应该保持线程缓存足够大，这样Threads_created就不会经常增加。但是，如果这是一个非常大的数字（例如，成千上万个线程），可能需要将其设置得更低一些，因为一些操作系统不能很好地处理大量线程，即使这些线程大部分处于休眠状态。

配置Mysql的I/O行为

一些配置选项会影响mysql将数据同步到磁盘和执行恢复的方式。这会涉及I/O操作，因此会极大的影响性能。这些选项还代表了性能和数据安全之间的权衡。一般来说，确认数据且一致性的写入磁盘的代价还是很高。但是如果冒磁盘写入操作没有真正写入持久存储的风险，是可以增加并发性/或减少I/O等待的，但你必须之间决定承受多大风险

这里表空间的双写缓冲区作用：

• 当 InnoDB 需要将脏页（修改过但还未写入磁盘的页）从缓冲池写入磁盘时，它首先将这些数据页复制到双写缓冲区。
• 双写缓冲区位于共享表空间（ibdata 文件）或独立表空间文件中，其大小通常为 2MB，默认包含 64 个连续页。

InnoDB事务日志（redo log）：

InnoDB使用日志来降低提交事务的成本。它不会在每个事务提交时将缓冲池刷新到磁盘，而是将事务记录到日志中。事务对数据和索引所做的更改通常映射到表空间中的随机位置，因此将这些更改刷新到磁盘将需要随机I/O。InnoDB假定它使用的是传统的磁盘，随机I/O比顺序I/O的开销要大很多，因为随机I/O需要在磁盘上寻找正确的位置，并等待将所需的磁盘部分旋转到磁头下。
使用日志，InnoDB可以将随机磁盘I/O转换为顺序I/O。一旦日志被安全地保存在磁盘中，即使更改的数据尚未写入数据文件，事务仍将是持久的。如果发生故障（例如停电），InnoDB可以重放日志并恢复已提交的事务。

日志文件的总大小由innodb_log_file_size和innodb_log_files_in_group控制，这对写入性能非常重要

日志缓冲区（redo log buffer）：

InnoDB修改数据时会将修改记录写入日志缓冲区，并将其保存在内存中。当缓冲区满了、事务提交时，或者每秒1次（这三个条件以先满足者为准），InnoDB会将缓冲区刷新到磁盘上的日志文件中。如果有大型事务，增加缓冲区大小（默认为1MB）有助于减少I/O。控制缓冲区大小的变量是innodb_log_buffer_size。

通常不需要将缓冲区设置得太大。建议的范围是1-8MB，一般来说是足够了，除非写入很多大的BLOG记录。与InnoDB的普通数据相比，日志条目非常紧凑。它们不是基于页面的，所以不会浪费空间一次存储整个页面（这里说明了为什么不利用mysql底层的数据页）。InnoDB也会让日志条目尽量短，有时甚至只用几个整数来表示记录的操作类型和该操作所需的任何参数！

日志缓冲区必须被刷新到持久化存储中，以确保提交的事务完全持久。如果你更关系性能而不是持久性，可以更改InnoDB也会让日志条目尽量短，有时甚至只用几个整数来表示记录的操作类型和该操作所需的任何参数！

• 0：每秒定时的将日志缓冲区写入到日志文件，并刷新日志文件，但提交事务不做任何操作
• 1： 每次事务提交，将日志缓冲区写入到日志文件，并将其刷新到持久化存储中，**这是默认的（也是最安全的）设置；**它保证你不会丢失任何已提交的事务，除非磁盘或操作系统“假装”进行刷新操作（没有将数据真正写入磁盘）。
• 2：这是默认的（也是最安全的）设置；它保证你不会丢失任何已提交的事务，除非磁盘或操作系统“假装”进行刷新操作（没有将数据真正写入磁盘）。

高性能事务需求的最佳配置是将innodb_flush_log_at_trx_commit设置为1，并将日志文件放在具有备用电池的写缓存和SSD的RAID卷上，这既安全又非常快。事实上，我们敢说，任何需要处理重要工作负载的生产数据库服务器都需要这种硬件。

InnoDB表空间：

InnoDB将数据保存在表空间中，表空间本质上是一个虚拟文件系统，由磁盘上的一个或者多个文件组成。Innodb将表空间用于多种用途，而不仅仅是存储表和索引。表空间中还包含了undo日志，修改缓冲区，双写缓冲区和其他内部结构

建议使用innodb_file_per_table并限制共享表空间的大小

其他I/O配置选项：

高性能事务需求的最佳配置是将innodb_flush_log_at_trx_commit设置为1，并将日志文件放在具有备用电池的写缓存和SSD的RAID卷上，这既安全又非常快。事实上，我们敢说，任何需要处理重要工作负载的生产数据库服务器都需要这种硬件。

如果不将sync_binlog设置为1，发生崩溃时可能会导致二进制日志与事务数据不同步。这很容易破坏复制且不可恢复，尤其是当数据库使用全局事务ID。

配置Mysql的并发

通过实验来找到适合系统的最佳值，建议innodb_thread_concurrency设置为与可用CPU核数相同的值，然后根据需要调整大小。

总结：

1. 调整InnoDB缓冲池：使用 innodb_dedicated_server 这个配置会自动优化，可以使用innodb_buffer_pool_instances将缓冲池分割成多个实例，提高并发性能
2. 调整relo log日志文件大小：调整innodb_log_file_size, 确保日志文件大小适中，平衡写入性能和故障恢复时间
3. 调整relo log buffer大小：调整innodb_log_buffer_size，减少日志写入磁盘的频率，提高写入性能
4. 调整并发：最大连接max_connections（用于设置mysql实例允许的最大客户端连接数目，需要与thread_cache_size配合使用，提高线程复用率）、调整连接缓存thread_cache_size（类似于线程池的概念，做线程复用，设置为**max_connections**** 的 10% 到 20%。）, 调整innodb_thread_concurrency(用于限制innodb存储引擎的并发线程数目，默认是0，不做限制，对于高并发负载，可以设置为cpu核心数目)**
5. 调整临时表： 调整**tmp_table_size**和**max_heap_table_size**，以增加内存临时表的大小，减少磁盘临时表的使用**
6. 调整磁盘临时表：如果内存临时表大小不足，MySQL会使用磁盘临时表，可以调整**innodb_tmpdir**和**innodb_tmp_tablespace**，以优化磁盘临时表的性能

总结：

不要调优