04.11.2020 01:00
MaxRokatansky 7 2200 Источник

Эксперт OTUS - Владислав Родин приглашает всех желающих на бесплатный demo-урок по теме: "Индексы в MySQL: best practices и подводные камни". Урок был подготовлен специально для супер-интенсива «СУБД в высоконагруженных системах».

А мы традиционно делимся с вами полезным переводом по теме.

Понимание того, как 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, но дает некоторые полезные сведения для интерпретации этой информации:

Мы можем увидеть, как долго система и процесс MySQL работали, сколько памяти у системы есть и сколько доступно, всякий раз, когда происходит какая-либо агрессивная операция подкачки и т.д., и все это помогает нам узнать, когда MySQL в настоящее время использует слишком много памяти… или слишком мало. Мы можем вывести количество текущих подключений, которое является известным фактором высокого использования памяти, наряду с парой других показателей, которые могут повлиять на потребление памяти.

Сводка по использованию памяти MySQL (MySQL Memory Usage Summary) показывает, сколько памяти содержат различные объекты, а также каков общий объем измеряемой памяти, выделенной процессом MySQL.

С сотнями разных мест, где может быть выделена память, даже не самые большие потребители памяти, могут аккумулировать достаточно большие накладные расходы:

 

Когда память выделяется хостом и пользователем, эти представления покрывают только память, которая может быть отнесена к конкретным соединениям. Но это отличный инструмент, чтобы узнать, существуют ли хосты, пользователи или приложения, которые являются значительными потребителями памяти.

Вы также можете просмотреть сведения об использовании памяти определенными учетными записями (какой-нибудь конкретный пользователь с конкретного хоста). Это все, что касается дашбордов, поскольку конкретные идентификаторы подключения (и идентификаторы потоков), как правило, меняются так быстро, что не особенно полезны в долгосрочном захвате.

Возможно, вы также заметили, что я отключил из сводки пользователя pmm@localhost, поскольку похоже, что рабочая нагрузка этого пользователя вызывает ошибку в учете памяти, и сообщаются нереальные цифры (это также то, что вам следует иметь в виду, чтобы не поплатиться за слепое доверие данным, которые вы видите).

Наконец, вы можете посмотреть распределение памяти по различным подсистемам, что может быть весьма полезно для расширенного анализа, когда только глобальные топ потребители не предоставляют достаточно информации.

А что думаете вы ? Это было бы для вас полезно, или вы хотели бы увидеть больше дополнительной визуализации, прежде чем мы рассмотрим возможность ее включения в Percona Monitoring and Management?

Записаться на бесплатный урок.