调整MySQL系统变量以实现高性能

本文概述

• 优化MySQL查询缓存
• 线程, 线程池, 等待和超时
• 等待和超时
• 调整临时表

对于大多数应用程序开发人员而言, 数据库是一个最好的人妖祭坛。但是不必那样！

在其他条件相同的情况下, 开发人员对基础数据库的满意程度定义了他们的资历级别。很少的数据库和很少的编码经验=初级开发人员；很少的数据库和良好的编码经验=中级开发人员；良好的数据库和良好的编码经验=高级开发人员。

即使是拥有6至8年工作经验的开发人员也难以解释查询优化器的复杂性, 并且在询问数据库优化时更偏向于现实, 这是一个严酷的现实。

为什么？

令人惊讶的是, 原因不是懒惰(尽管在某种程度上是懒惰)。

关键是数据库本身就是一种不可抗拒的力量。甚至传统上, 当只有关系型数据库需要处理时, 掌握它们本身就是一个奇迹和职业道路。如今, 我们拥有众多类型的数据库, 以至于不可能只有一个凡人的灵魂来掌握一切。

就是说, 你很有可能仍然对关系数据库感到满意, 或者你的团队很长时间以来一直令人满意地在关系数据库上运行产品。在十分之九的情况下, 你使用的是MySQL(或MariaDB)。对于这些情况, 深入研究可能会为提高应用程序性能带来巨大好处, 并且值得学习。

好奇？让我们潜入吧！

不好奇吗好吧, 无论如何都要潜水, 因为你的事业取决于它！ ????

优化MySQL查询缓存

计算机领域的几乎所有优化都归结为缓存。一方面, CPU维护多个缓存级别以加快其计算速度；另一方面, Web应用程序积极使用诸如Redis之类的缓存解决方案将预先计算的结果提供给用户, 而不是每次都访问数据库。

但是, 即使糟糕的MySQL数据库也有其自己的查询缓存！也就是说, 每次查询某些内容并且数据仍然过时时, MySQL都会提供这些缓存的结果, 而不是再次运行查询, 从而使该应用程序的运行速度异常地快。

你可以通过在数据库控制台中运行以下查询来检查数据库中是否有查询缓存可用(注释, 可用, 未启用)：

MariaDB [(none)]> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

因此, 你可以看到我正在运行MariaDB, 并且可以打开查询缓存。如果你使用的是标准MySQL安装, 则极不可能将其关闭。

现在让我们看看我是否真的打开了查询缓存：

MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache_type';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | ON    |
+------------------+-------+

是的, 我愿意。但是, 如果你不这样做, 则可以通过以下方式将其打开：

MariaDB [(none)]> SET GLOBAL query_cache_type = ON;

有趣的是, 该变量还接受表示”按需”的第三个值, 这意味着MySQL将仅缓存我们告诉它的那些查询, 但在此不再赘述。

有了这个, 你就可以进行查询缓存了, 并且迈出了更健壮的MySQL设置的第一步！我说第一步是因为打开它是一个重大改进, 我们确实需要调整查询缓存以适合我们的设置。因此, 让我们学习如何做。

这里感兴趣的另一个变量是query_cache_size, 其功能不言自明：

MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_cache_size | 16777216 |
+------------------+----------+

因此, 我有一个大约16 MB大小的查询缓存。请注意, 即使打开了查询缓存, 但此大小为零, 缓存实际上仍处于关闭状态。这就是为什么仅检查一个变量是不够的。现在, 你应该设置查询缓存大小, 但是应该是多少？首先, 请注意, 查询缓存功能本身将需要4 KB的空间来存储其元数据, 因此, 选择的内容应高于此值。

假设你将查询缓存的大小设置为500 KB：

MariaDB [(无)]> SET GLOBAL query_cache_size = 500000;

这样够了吗？好吧, 不, 因为查询引擎实际上将如何最终执行取决于另外几件事：

• 首先, query_cache_size变量必须足够大才能容纳你的查询结果。如果太小, 将不会缓存任何内容。
• 其次, 如果将query_cache_size设置为一个太大的数字, 则将出现两种类型的问题：1)引擎将不得不做额外的工作来存储和定位查询结果到如此大的内存区域中。 2)如果大多数查询导致较小的大小, 则缓存将变得零散, 并且使用缓存的好处将会丢失。

你如何知道缓存越来越碎片化？像这样检查缓存中的块总数：

MariaDB [(none)]> show status like 'Qcache_total_blocks';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Qcache_total_blocks | 33    |
+---------------------+-------+

如果数量非常高, 则缓存会碎片化, 需要刷新。

因此, 为避免这些问题, 请确保明智地选择query_cache_size的大小。如果你对我没有在这里留下具体的数字感到沮丧, 那么恐怕这是你过去开发并步入工程设计后的样子。你必须查看正在运行的应用, 并查看重要查询结果的查询大小, 然后设置该数字。即使那样, 你也可能最终会犯错。 ????

线程, 线程池, 等待和超时

这可能是MySQL如何工作的最有趣的部分, 正确执行它意味着使你的应用程序速度提高几倍！

穿线

MySQL是一个多线程服务器。这意味着, 每当与MySQL服务器建立新连接时, 它都会使用连接数据打开一个新线程, 并将其句柄传递给客户端(以防万一你想知道线程是什么, 请参见此)。然后, 客户端通过该线程发送所有查询并接收结果。这导致我们提出一个自然的问题：MySQL可以启动多少个线程？答案在于下一部分。

线程池

计算机系统中的任何程序都无法打开所需数量的线程。原因有两个：1)线程消耗内存(RAM), 而操作系统不允许你发疯并吃光所有内存。 2)例如, 管理一百万个线程本身是一项繁重的任务, 如果MySQL服务器可以创建那么多线程, 它将死于处理开销。

为了避免这些问题, MySQL附带了一个线程池-线程数量固定, 这些线程一开始就是池的一部分。新的连接请求使MySQL选择这些线程之一并返回连接数据, 如果所有线程都用完了, 自然会拒绝新的连接。让我们看看线程池有多大：

ariaDB [(none)]> show variables like 'thread_pool_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| thread_pool_size | 4     |
+------------------+-------+

因此, 我的机器最多同时允许四个连接。有趣的是, 数字4来自我拥有四核处理器的事实, 这意味着我的计算机一次只能运行4个并行任务(我在这里说的是真正的并行任务, 不是并行任务)。理想情况下, 这是应该驱动thread_pool_size值的限制, 但是在功能更强大的计算机上, 这样做确实有好处。如果你不想让所有新连接都等待并且可以提高性能(同样, 可以根据应用程序在负载下的性能来判断这是一个最好的领域), 最好将连接数提高到8。

但是, 除非拥有32核计算机, 否则将其设置为16以上是一个糟糕的主意, 因为性能会大大降低。 MySQL中的线程池非常棘手, 但是, 如果你有兴趣, 这里将进行更详细的讨论。

等待和超时

创建线程并将其附加到客户端后, 如果客户端在接下来的几秒钟(或几分钟)内未发送任何查询, 则会浪费资源。结果, MySQL在一段时间不活动后终止了连接。这由wait_timeout变量控制：

MariaDB [(none)]> show variables like 'wait%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+

结果值以秒为单位。所以是的, 默认情况下, MySQL设置为等待8个多小时才能切断电源！如果你有长期运行的查询并且实际上想等待它们(但这仍然是八个小时, 那是荒谬的！), 但是在大多数情况下这很糟糕, 那么这可能会很好。运行查询时, 该值设置为0(永远意味着), 但通常应将其设置为一个非常低的值(例如5秒, 甚至更少), 以释放其他进程的连接。

调整临时表

让我们从MySQL中的临时表开始。

假设我们有一个MySQL, 其结构看起来像这样：TABLE A UNION(表B内连接C)。也就是说, 我们对联接表B和C感兴趣, 然后将结果与表A进行联合。现在, MySQL首先将联接表B和C, 但是在执行联合之前, 需要将此数据存储在某个地方。这就是临时表的来源-MySQL使用它们在复杂查询中的中间阶段临时存储数据, 一旦查询结束, 该临时表将被丢弃。

现在的问题是：为什么我们要打扰所有这些？

仅仅因为临时表只是查询结果, 是MySQL在计算中使用的数据, 其访问速度(除其他限制外)将决定查询的执行速度。例如, 将临时表存储在RAM中将比将其存储在磁盘上快几倍。

有两个变量可以控制此行为：

MariaDB [(none)]> show variables like 'MariaDB [(none)]> show variables like 'tmp_table_size';  
+----------------+----------+

| Variable_name  | Value    |

+----------------+----------+

| tmp_table_size | 16777216 |

+----------------+----------+
';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 16777216 |
+---------------------+----------+

MariaDB [(none)]> show variables like 'tmp_table_size';
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| tmp_table_size | 16777216 |
+----------------+----------+

第一个是max_heap_table_size, 它告诉我们一个MySQL表可以使用多少RAM(这里的”堆”是指RAM分配和管理中使用的数据结构-在这里阅读更多), 而第二个是tmp_table_size, 它显示了临时表的最大大小是多少。在我的情况下, 两者都设置为16 MB, 尽管我要说的是, 仅增加tmp_table_size不能总体上起作用, 但MySQL仍然会受到max_table_heap_size的限制。

现在要说的是：如果创建的临时表大于这些变量所允许的限制, 则MySQL将被迫将其写入硬盘, 从而导致性能极差。现在, 我们的工作很简单：尽力猜测临时表的最准确数据大小, 并将这些变量调整到该限制。但是, 我想提醒你不要荒唐：在大多数临时表的大小小于24 MB时, 将此限制设置为16 GB(假设你有这么多的RAM)是愚蠢的–你只是在浪费可能的RAM。已被其他查询或系统的某些部分(例如缓存)使用。

总结

当MySQL文档本身跨越几千个单词时, 不可能在一篇文章中涵盖所有系统变量, 甚至在一篇文章中也无法涵盖所有​​重要变量。尽管我们在这里介绍了一些通用变量, 但我还是建议你研究所用引擎的系统变量(InnoDB或MyISAM)。

我写这篇文章最可取的结果是让你拿走了三件事：

1. MySQL是一种典型的软件, 可以在操作系统设置的限制内运行。这不是上帝知道的什么神秘程序, 无法驯服。另外, 值得庆幸的是, 了解它的设置方式并受其系统变量控制并不难。
2. 没有任何一项设置可以使你的MySQL安装变大。你别无选择, 只能查看正在运行的系统(请记住, 优化是在应用程序投入生产后而不是之前进行的), 做出最佳猜测和衡量, 并接受它永远不会完美的现实。
3. 调整变量并不是优化MySQL的唯一方法-高效地编写查询是另一回事, 但这是我将在另一篇文章中解决的问题。但是, 重点是, 即使你进行了如神似的分析并将这些参数调整到最佳状态, 你仍然有可能使一切停滞不前。

你最喜欢的系统变量是什么？ ????