🛢️ ☠️ 👩🏼‍💻 通过性能模式检查MySQL内存使用情况 🐹 🍁 🃏

OTUS专家弗拉迪斯拉夫·罗丹（Vladislav Rodin）邀请所有人参加有关该主题的免费演示课程：“ MySQL索引：最佳实践和陷阱”。该课程是专门为超密集型“高负载系统中的DBMS”准备的。
传统上，我们会与您分享有关该主题的有用翻译。

理解MySQL如何使用内存是调优系统以获得最佳性能以及对异常内存使用进行故障排除的关键，例如，当您的MySQL服务器使用的内存比您预期的要多配置参数。

在MySQL诞生之初，就很难理解内存使用的所有细微差别和细微差别，并且涉及很多猜测工作。也许某些查询需要较大的临时表或较大的内存分配来存储用户变量？是否有任何存储过程使用了意外大量的内存？任何原因都可能导致MySQL过度使用内存，但是您没有简单的方法来找出并确定哪个。

这一切在MySQL 5.7中都发生了变化，它添加了Performance Schema内存跟踪工具包，并且MySQL 8.0默认情况下启用了此工具包，因此您几乎可以从任何正在运行的实例中获取此数据。

如果您要查找有关当前内存使用情况的信息，则Sys模式可提供一组出色的信息：

mysql> select event_name,current_alloc from sys.memory_global_by_current_bytes limit 10;
+---------------------------------------------------------------+---------------+
| event_name                                                    | current_alloc |
+---------------------------------------------------------------+---------------+
| memory/innodb/buf_buf_pool                                    | 262.12 MiB    |
| memory/temptable/physical_ram                                 | 64.00 MiB     |
| memory/performance_schema/events_statements_summary_by_digest | 39.67 MiB     |
| memory/sql/TABLE                                              | 33.32 MiB     |
| memory/innodb/ut0link_buf                                     | 24.00 MiB     |
| memory/innodb/lock0lock                                       | 20.51 MiB     |
| memory/innodb/memory                                          | 17.79 MiB     |
| memory/innodb/buf0dblwr                                       | 17.08 MiB     |
| memory/innodb/ut0new                                          | 16.08 MiB     |
| memory/performance_schema/events_statements_history_long      | 13.89 MiB     |
+---------------------------------------------------------------+---------------+
10 rows in set (0.01 sec)

. , , :

mysql> select host,current_allocated from memory_by_host_by_current_bytes;
+-------------------------------+-------------------+
| host                          | current_allocated |
+-------------------------------+-------------------+
| localhost                     | 1.19 GiB          |
| background                    | 101.28 MiB        |
| li1317-164.members.linode.com | 49.61 MiB         |
| li1319-234.members.linode.com | 27.90 MiB         |
| li1316-24.members.linode.com  | 27.00 MiB         |
+-------------------------------+-------------------+
5 rows in set (0.02 sec)

thread_id, , :

mysql> select thread_id,user,current_allocated from memory_by_thread_by_current_bytes limit 5;
+-----------+------------------------------------+-------------------+
| thread_id | user                               | current_allocated |
+-----------+------------------------------------+-------------------+
|        44 | innodb/srv_worker_thread           | 1.99 MiB          |
|        48 | innodb/srv_worker_thread           | 1.16 MiB          |
|     54322 | root@localhost                     | 1.10 MiB          |
|        46 | innodb/srv_worker_thread           | 777.29 KiB        |
|     43881 | app1@li1317-164.members.linode.com | 274.84 KiB        |
+-----------+------------------------------------+-------------------+
5 rows in set (0.43 sec)

, , , . , … Percona Monitoring and Management (PMM).

, PMM 2.11, Performance Schema. (Custom Queries).

MySQL

- , MySQL SQL- , MySQL. Performance Schema, Information Schema , , . Percona Monitoring and Management.

MySQL Performance Schema …

cd /usr/local/percona/pmm2/collectors/custom-queries/mysql/medium-resolution
wget https://raw.githubusercontent.com/Percona-Lab/pmm-custom-queries/master/mysql/ps-memory-summary.yml

MySQL (MySQL Memory Usage Details dashboard) Grafana.com. , , .

-, , , Performance Schema, :