MySQL内存分配公式

在调整任何InnoDB配置设置（特别是内存缓冲区）之前，我们需要了解MySQL如何将内存分配给处理RAM的应用程序的各个区域。

有两个简单的公式用于引用基于传入客户端连接分配内存的总内存使用情况：

• 局部缓冲区：

  局部缓冲区，也称为每次链接缓冲区，因为MySQL为每个连接使用单独的线程，与全局缓冲区相反，局部缓冲区仅在建立连接时分配内存，并且在某些情况下只会分配连接工作负载所需的内存，因此不一定利用允许缓冲区的整个大小。MySQL手册中描述了这种内存利用方法，如下所示：

  每个客户端线程都与连接缓冲区和结果缓冲区相关联。两者都以 net_buffer_length 给出的大小开始，但根据需要动态放大到 max_allowed_packet 字节。结果缓冲区在每个 SQL 语句之后收缩为 net_buffer_length。

• 全局缓冲区：

  无论处理的连接数如何，都会为全局缓冲区分配内存资源。这些缓冲区在启动过程中请求其内存要求，并保留此资源预留，直到服务器进程结束。

在将内存分配给MySQL缓冲区时，我们需要确保也有足够的RAM可供操作系统执行其任务和进程;

通常，最佳做法是将MySQL的总系统RAM分配限制在85%到90%之间。每个缓冲区的内存利用率公式如下所示：

• 每线程缓冲区内存利用率公式：

  （read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size）* max_connections = 所有连接的总内存分配，或 MySQL 线程缓冲区 （MTB）

• 全局缓冲区内存利用率公式：

  innodb_buffer_pool_size + innodb_additional_mem_pool_size +innodb_ log_buffer_size + key_buffer_size +query_cache_size=MySQL 全局缓冲区 （MGB） 使用的总内存

• 总内存分配公式：

MTB+MGB=MySQL使用的总内存

如果 MTB 和 MGB 组合使用的总内存大于系统总 RAM 的 85% 到 90%，则可能会遇到资源争用、资源瓶颈，或者在最坏的情况下，您将看到内存页交换到磁盘资源（虚拟内存），这会导致性能下降，在某些情况下， 进程失败或连接超时。因此，在更改内存缓冲区或增加数据库的max_connections值之前，通过前面提到的公式检查内存分配是明智的。

InnoDB内存和数据文件配置

在调整InnoDB引擎的任何设置之前，必须定义数据文件的位置，因为MySQL在安装过程中使用这些设置来初始化InnoDB。因此，除非遵循特定的数据文件迁移过程，否则在创建文件后不能简单地更改变量。如果不更改默认设置，则InnoDB数据文件将在由datadir变量定义的通用MySQL数据目录中初始化。

Static variables

静态变量仅在MySQL进程启动序列期间读取。这意味着一旦在my中定义了静态变量。CNF 文件，它将在整个运行时进程中处于活动状态，并且在数据库服务器进程的操作期间无法更改。

innodb_data_home_dir

此变量控制共享表空间文件、状态文件和 InnoDB 特定日志文件的位置。

如果未显式设置，则默认为 MySQL 数据目录的值。 如果您决定启用每文件表空间设置，则此变量将仅确定共享数据字典文件的位置，而每文件表空间文件将存储在特定于模式的目录中。专业意见对此设置有所不同，因为一些管理员希望将其设置为与其常规 datadir 值相同的显式值，而有些管理员会将其显式设置为与 datadir 值不同的分区;每种方法都有其优点，因此，正确的设置将取决于您的环境需求。

例如，innodb_data_home_dir =/var/lib/mysql。

innodb_data_file_path

这控制特定的InnoDB数据文件位置和大小。

设置后，这将与innodb_data_home_dir中指定的设置组合或连接。指定的文件必须大于 10 MB，如果操作系统支持，则可以将其大小设置为大于 4 GB。

此变量是 MySQL 服务器的 InnoDB 容量规划和性能扩展能力的核心元素。

更常见的设置之一是为初始数据目录内容创建一个基线大小的文件，第一个文件介于 10 MB 和 128 MB 之间，然后为第二个文件创建一个最小大小为 10 MB 的文件，该文件设置为自动扩展状态，以便它可以根据需要增长。

例如：innodb_data_file_path= ibdatal：128M;ibdata2 ：10M：autoextend。

innodb_log_group_home_dir

InnoDB 重做日志文件用于在提交和写入磁盘上的表空间文件之前存储事务数据。

这些日志将存储在此变量定义的目录中。

通常，此设置与innodb_data home_dir变量的位置相同，但为了获得最佳性能，我们建议将InnoDB数据记录和数据文件分离到单独的物理存储阵列上。这可确保 I/O 资源在处理大量数据或处理大量并发连接的服务器上不会发生冲突。

innodb_log_file_size

InnoDB 日志文件的大小总是会导致或解决性能瓶颈;因此，为此变量选择适当的大小非常重要。

这默认为 5 MB 的大小，对于大多数生产工作负载来说太小了。由于日志文件以其完整大小初始化，因此无法使用文件系统的报告文件大小来查看日志是否已满，但是可以检查文件修改时间，以查看日志文件的轮换频率。日志以循环方式写入，因此日志文件的快速循环（如紧密的顺序修改时间所示）将指示日志刷新过于频繁，需要增加大小。

innodb_log_file_size变量的常见设置通常被视为innodb_buffer_pool_size变量的函数;

在缓冲池设置相对较小的服务器上（小于 4 GB），建议将组innodb_log_file_size变量的总大小设置为与缓冲池相同的大小。

维护日志文件性能的另一个要求是确保innodb_log_buffer_size变量足够大，以便在写入日志文件本身之前缓冲 RAM 中的更改;

随着缓冲池越来越大，不建议使用 1：1 的比率，因为日志越大，如果 MySQL 崩溃，崩溃恢复过程所需的时间就越长，从而导致潜在的长时间停机。对于具有高性能I / O的日志文件的服务器，MySQL文档中提供的最大建议设置是将日志文件大小设置为缓冲池的值除以正在使用的InnoDB日志文件数。

例如，如果有一个 10 GB 的缓冲池和四个日志文件，则 innodb_log_file_size 变量将设置为 10/4 = 2.5 GB。具有高性能 I/O 的服务器将读取和处理 InnoDB 日志文件条目并比具有较慢 I/O 的服务器更快地完成崩溃恢复过程，这并非巧合，因此明智的做法是在生产使用之前测试日志文件的大小以确定近似的崩溃恢复计时值。小日志文件和大日志文件之间的主要区别在于，在要求将数据刷新到表空间文件之前，较大的日志会保存更多的提交数据。日志刷新和崩溃恢复过程在MySQL发布过程中得到了改进，因此您可能会注意到更高版本可以处理更大的日志文件，而不会对这些情况产生重大影响。

尽管此变量是静态的，并且只能通过进程重新启动来更改，但它也需要特定的进程来更改值。由于InnoDB将数据更改写入这些日志文件的方式，因此不能简单地更改日志的大小，然后让MySQL干净地启动。必须删除或移动日志，并将创建请求大小的新日志文件。

innodb_log_buffer_size

当 InnoDB 收到事务提交时，它会先将更改放入内存缓冲区，然后再写入磁盘。与许多 InnoDB 内存缓冲区一样，此缓冲区有助于将数据保存在快速访问 RAM 中，以便较慢的磁盘在高并发数据库流量期间不会造成性能瓶颈。因此，如果我们将innodb_buffer_pool_size变量调整为大于默认值，我们还应该增加关联的innodb_log_buffer_size和innodb_log_file_size变量的大小。此变量的一般建议范围为 8 MB 到 256 MB。最佳设置将取决于您的工作负载和系统资源。

请记住，所有更改最终都必须写入磁盘，因此将此缓冲区值设置得很高只会在最终使用磁盘 I/O 资源之前将更改缓冲有限的时间。但是，它可以用作预防措施，以帮助减少大量 I/O 资源消耗。

innodb_log_files_in_group

这控制 InnoDB 将写入的日志文件数量。

日志按顺序写入。除非有特定需求，并且已经过测试以提供性能或稳定性增益，否则此变量应保留为默认设置

如果您需要调整日志文件的大小并提供更多日志记录容量，但不想参与删除和调整现有日志文件大小的潜在危险过程，则可以根据需要增加此值，InnoDB 引擎将仅在进程重新启动时创建其他日志文件。

如果您决定减少此变量并使用更少的日志文件，则必须遵循用于调整 InnoDB 日志文件大小的相同过程，如第 5 章 “维护和监控”中所述。

innodb_buffer_pool_size

InnoDB是关于速度的，您可以给基于InnoDB的数据库带来的最大性能影响是将更多的RAM分配给innodb_buffer_pool_大小变量。

InnoDB本质上是基于磁盘的数据库引擎，它存储和访问存储在基于磁盘的文件系统上的数据库数据。但是，它在管理和利用随机存取存储器在操作期间缓冲数据方面也非常有效。

控制 InnoDB 如何在 RAM 中缓冲数据的最重要的值之一是innodb_buffer_pool变量。

缓冲池是基于内存的缓存，用于表数据和索引数据。与基于磁盘的数据缓存相比，这允许快速访问数据。正如我们所讨论的，日志文件用于将数据更改从缓冲池刷新到临时日志缓冲区，也在 RAM 中，刷新到基于文件的日志，最后刷新到磁盘。在进行任何刷新之前，数据位于缓冲池中，因此缓冲池越大，它可以在磁盘表空间文件和磁盘日志之间保存或缓冲的数据就越多。

缓冲池由最近最少使用 （LRU） 算法管理，采用中点插入策略;此算法通过读取数据的访问计时值来控制如何在池中添加和清除数据。LRU 方法将删除存储在访问列表末尾的最近最少使用的数据，然后，由于中点策略，将新数据插入数据访问池的中间。然后，数据将在访问列表中向前或向后移动，具体取决于相对于访问列表中的其余数据请求和使用数据的频率。

在调整此变量之前，请检查正在使用的 MySQL 版本;版本 5.0 使用 8 MB 的默认设置，这对于大多数生产情况来说是不可接受的，5.1 版本的值与等于或小于 5.1.27 的版本上的先前标准 8 MB 和从 5.1.28 及更高版本开始的版本上的更现实的基线设置为 128 MB 不同。

为缓冲池选择值时，常见的建议范围从可用系统 RAM 的 80% 到基于 InnoDB 的架构中使用的 InnoDB 数据和索引大小的 100%。例如，如果您的服务器有 64 GB 的 RAM，而您的 InnoDB 数据加索引的总或工作集只有 20 GB，那么将缓冲池设置为总 RAM 的 80% （64*0.8 = 51.2 GB) 可能不会有太大好处。在这种情况下，将 RAM 资源分配给各种 MySQL 内存缓冲区中的其他位置更为合适，例如 innodb_log_buffer_size、join_buffer_size、read_buffer 大小等。要了解MySQL如何将RAM分配给各种缓冲区，您可以参考本章顶部的公式。

innodb_status_file

此变量提供在 MySQL 命令行上运行 SHOW ENGINE INNODB STATUS 的等效输出，但会定期写入数据目录中的状态文件。状态文件中包含的输出对于自动监控很有用，因为它可以解析为显示InnoDB如何的各种有用数据

经营。此外，如果InnoDB存在操作问题，则可以在故障排除过程中使用它。出于时间轴目的，文件附加了 PID 值。

transaction-isolation

此变量控制 InnoDB 在存在多个连接并访问相似行内容时处理事务数据隔离的方法，事务隔离级别的设置将决定 ACID 合规性和对 XA 事务的支持。尽管此配置设置在服务器级别是静态的，但如果需要，应用程序可以基于每个连接设置隔离级别。

skip-innodb

如果您需要禁用InnoDB引擎，可以在my.cnf文件中设置。如果您在启动 InnoDB 时遇到问题或在表引擎列表中不可用，这将是一个值得检查的变量，以确保它不会错误地启用。在InnoDB不用于任何表的情况下，启用此变量是相关的，从而禁用InnoDB，以便MySQL不会向InnoDB引擎分配任何资源。