🤴🏼 🙍🏽 ☂️ Examining MySQL Memory Usage with Performance Schema 👩🏻‍🏭 ♠️ 🌳

OTUS expert Vladislav Rodin invites everyone to a free demo-lesson on the topic: "MySQL indexes: best practices and pitfalls" . The lesson was prepared specifically for the super-intensive "DBMS in high-loaded systems" .
And we traditionally share with you a useful translation on the topic.

Understanding how MySQL uses memory is key to tuning your system for optimal performance, as well as troubleshooting abnormal memory usage, such as when you have a MySQL server using a lot more memory than you would expect based on configuration parameters.

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, :