😾 🐳 🙆🏼 Memeriksa Penggunaan Memori MySQL dengan Skema Kinerja 👼🏽 🥛 🧛🏽

Pakar OTUS Vladislav Rodin mengundang semua orang untuk mengikuti pelajaran demo gratis tentang topik: "Indeks MySQL: praktik terbaik dan perangkap" . Pelajaran ini disiapkan secara khusus untuk "DBMS dalam sistem beban tinggi" yang super intensif .
Dan kami biasanya membagikan kepada Anda terjemahan yang berguna tentang topik tersebut.

Memahami bagaimana MySQL menggunakan memori adalah kunci untuk menyetel sistem Anda untuk kinerja yang optimal, serta memecahkan masalah penggunaan memori yang tidak normal, seperti ketika Anda memiliki server MySQL yang menggunakan lebih banyak memori daripada yang Anda harapkan. parameter konfigurasi.

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