El experto en OTUS Vladislav Rodin invita a todos a una lección de demostración gratuita sobre el tema: "Índices MySQL: mejores prácticas y trampas" . La lección se preparó específicamente para el superintensivo "DBMS en sistemas de alta carga" .
Y tradicionalmente compartimos contigo una traducción útil sobre el tema.
Comprender cómo MySQL usa la memoria es clave para ajustar su sistema para un rendimiento óptimo, así como para solucionar problemas de casos de uso de memoria anormales, como cuando tiene un servidor MySQL que usa mucha más memoria de la que esperaría según parámetros de configuración.
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.ymlMySQL (MySQL Memory Usage Details dashboard) Grafana.com. , , .
-, , , Performance Schema, :
, MySQL , , , - .., , MySQL … . , , , .
MySQL (MySQL Memory Usage Summary) , , , MySQL.
, , , :
, , . , , , , .
(- ). , , ( ), , , .
, , pmm@localhost, , , ( , , , ).
, , , .
? , , Percona Monitoring and Management?