В предыдущем посте мы видели, что MySQL любит память. Мы также увидели, как выполнять проверки операционной системы и некоторые изменения конфигурации для Swap и NUMA.
Сегодня мы проверим, что сервер MySQL может сказать нам об использовании памяти.
Представленный в MySQL 5.7 и включенный по умолчанию в MySQL 8.0, Performance_Schemaинструментарий памяти позволяет нам лучше понять, что MySQL выделяет и почему.
Давайте проверим наш сервер MySQL, используя SYS:
Обратите внимание, что существует ошибка, связанная с тем, как учитывается статистика InnoDB Buffer Pool Performance_Schema. Это исправлено в 8.0.13.
SYS схема предоставляет 5 таблиц для получения информации о распределении памяти:
+-----------------------------------+ | Tables_in_sys (memory%) | +-----------------------------------+ | memory_by_host_by_current_bytes | | memory_by_thread_by_current_bytes | | memory_by_user_by_current_bytes | | memory_global_by_current_bytes | | memory_global_total | +-----------------------------------+
Обзор можно получить по « code area»:
SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area,
sys.format_bytes(SUM(current_alloc)) AS current_alloc
FROM sys.x$memory_global_by_current_bytes
GROUP BY SUBSTRING_INDEX(event_name,'/',2)
ORDER BY SUM(current_alloc) DESC;
+---------------------------+---------------+
| code_area | current_alloc |
+---------------------------+---------------+
| memory/innodb | 345.23 MiB |
| memory/performance_schema | 198.35 MiB |
| memory/sql | 23.34 MiB |
| memory/mysys | 8.34 MiB |
| memory/temptable | 8.00 MiB |
| memory/mysqld_openssl | 213.11 KiB |
| memory/mysqlx | 33.21 KiB |
| memory/myisam | 674 bytes |
| memory/vio | 564 bytes |
| memory/csv | 77 bytes |
| memory/blackhole | 77 bytes |
+---------------------------+---------------+
Буферный пул
При использовании InnoDB одним из наиболее важных компонентов является пул буферов InnoDB. Каждый раз, когда происходит операция с таблицей (чтение или запись), страница, на которой расположены записи (и индексы), загружается в буферный пул.
Это означает, что если данные, которые вы читаете и пишете чаще всего, имеют свои страницы в пуле буферов, производительность будет выше, чем если бы вам приходилось читать страницы с диска. Также не забывайте, что когда в нем больше нет свободных страниц, старые страницы должны быть удалены, а если они были изменены, синхронизированы обратно на диск (контрольная точка). Поэтому, когда все необходимые данные присутствуют в пуле буферов, мы говорим, что рабочий набор помещается в память. Вы можете иметь набор данных размером 3 ТБ (с журналом исторических данных, который никогда не запрашивается), но рабочий набор объемом в несколько ГБ или даже меньше.
Начиная с MySQL 8.0, если у вас есть выделенный сервер для MySQL, вы можете позволить MySQL настроить для вас размер пула буферов, установив для innodb_dedicated_server значение ON.
Можно проверить, сколько буферного пула InnoDB заполнено данными, используя Performance_Schema:
mysql> SELECT CONCAT(FORMAT(A.num * 100.0 / B.num,2),"%") BufferPoolFullPct FROM (SELECT variable_value num FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_pages_data') A, (SELECT variable_value num FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_pages_total') B; +-------------------+ | BufferPoolFullPct | +-------------------+ | 23.42% | +-------------------+
Если на данный момент вы можете видеть на этом сервере рабочий набор, то он умещается в памяти, так как в пуле буферов InnoDB еще много свободных страниц.
Конечно, также можно увидеть распределение памяти для пула буферов, используя следующий запрос:
mysql> select * from memory_global_by_current_bytes
where event_name like 'memory/innodb_buf_buf_pool';
*************************** 1. row ***************************
event_name: memory/innodb/buf_buf_pool
current_count: 2
current_alloc: 241.76 MiB
current_avg_alloc: 122.66 MiB
high_count: 2
high_alloc: 276.22 MiB
high_avg_alloc: 123.33 MiB
Если вы хотите узнать, какие схемы или таблицы присутствуют в пуле буферов, запросите эти SYSтаблицы схем:
- innodb_buffer_stats_by_schema
- innodb_buffer_stats_by_table
Вы также можете проверить соотношение страниц, запрошенных InnoDB, и количества страниц, прочитанных из пула буферов, чтобы узнать, помещается ли ваш рабочий набор в память или нет. В этом примере я проверю запрос на 1 минуту на моем сервере базы данных:
show global status like 'innodb_buffer_pool_read%s';select sleep(60); show global status like 'innodb_buffer_pool_read%s'; +----------------------------------+---------+ | Variable_name | Value | +----------------------------------+---------+ | Innodb_buffer_pool_read_requests | 2275123 | | Innodb_buffer_pool_reads | 2547 | +----------------------------------+---------+ 2 rows in set (0.0026 sec) +-----------+ | sleep(60) | +-----------+ | 0 | +-----------+ 1 row in set (1 min 0.0006 sec) +----------------------------------+---------+ | Variable_name | Value | +----------------------------------+---------+ | Innodb_buffer_pool_read_requests | 2279428 | | Innodb_buffer_pool_reads | 2547 | +----------------------------------+---------+ 2 rows in set (0.5880 sec)
Мы видим, что за 60 секунд было прочитано 4305 страниц, и все они обслуживались буферным пулом, и это здорово!
Вы можете найти аналогичную информацию из выходных данных SHOW ENGINE INNODB STATUS на линии частоты попаданий в пул буферов. 1000/1000 – это номер, который вы хотите увидеть там. Если вы постоянно видите меньшее число, то вам следует подумать об уменьшении рабочего набора или увеличении пула буферов.
Лучшие индексы, никакие дубликаты не могут помочь уменьшить рабочий набор. В схеме sys вы можете найти 2 таблицы, которые помогут вам нацеливать таблицы с неоптимальными индексами ( schema_redundant_indexes& schema_unused_indexes). Лучшие запросы также могут помочь уменьшить рабочий набор. Вы можете выбрать кандидатов в этих двух других таблицах: schema_tables_with_full_table_scans&statements_with_full_table_scans.
Временные таблицы
Конечно, временные таблицы также используют память.
Вы можете отслеживать их создание в глобальном статусе:
select * from performance_schema.global_status where variable_name like '%tmp%tables'; +-------------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +-------------------------+----------------+ | Created_tmp_disk_tables | 0 | | Created_tmp_tables | 2736 | +-------------------------+----------------+
Вы можете видеть, что некоторые временные таблицы были созданы в памяти. Вы можете найти операторы, создающие их, запрашивая таблицу statements_with_temp_tables.
Мы можем отслеживать количество выделенной оперативной памяти для временных таблиц, как это сообщается в событии memory/temptable/physical_ram:
select * from memory_global_by_current_bytes
where event_name like '%temp%'\G
*************************** 1. row ***************************
event_name: memory/temptable/physical_ram
current_count: 7
current_alloc: 6.00 MiB
current_avg_alloc: 1.00 MiB
high_count: 10
high_alloc: 10.00 MiB
high_avg_alloc: 1.00 MiB
Чтобы ограничить размер оперативной памяти, используемой для одной временной таблицы (количество временных таблиц не ограничено), есть несколько переменных конфигурации, которые можно использовать. Конечно, это зависит от используемого механизма временных таблиц. До MySQL 8.0 был доступен только механизм MEMORY, в 8.0 новый механизм TempTable доступен и используется по умолчанию.
С механизмом MEMORY максимальный размер таблицы в памяти ограничен наименьшим значением этих двух переменных: max_heap_table_sizeи tmp_table_size.
При превышении размера (или несовместимых типов полей) временная таблица отправляется на диск.
Для механизма TempTable, который допускает столбцы VARCHAR, VARBINARY или другие двоичные столбцы типа больших объектов (поддерживается с MySQL 8.0.13), переменная temptable_max_ramограничивает размер таблицы.
Механизм, используемый для внутренних временных таблиц, определен в этих переменных:
select * from performance_schema.global_variables where variable_name like 'internal_tmp%'; +----------------------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +----------------------------------+----------------+ | internal_tmp_disk_storage_engine | InnoDB | | internal_tmp_mem_storage_engine | TempTable | +----------------------------------+----------------+
Небольшой совет: можно увидеть временные таблицы, которые были только что удалены на диске из ОС, используя lsof:
# lsof -p $(pidof mysqld) | grep -i del mysqld 17275 mysql 5u REG 7,1 4123 125440 /tmp/ibsfgUte (deleted) mysqld 17275 mysql 6u REG 7,1 0 125450 /tmp/ibkRdasp (deleted) mysqld 17275 mysql 7u REG 7,1 0 125451 /tmp/ibldORfs (deleted) mysqld 17275 mysql 8u REG 7,1 0 125452 /tmp/ibldlkOA (deleted) mysqld 17275 mysql 12u REG 7,1 0 125453 /tmp/ibkdWifa (deleted)
Мы можем видеть, что только одна временная таблица имеет несколько записей, так как она была немного больше 4k.
Буферы
И, наконец, есть кэши и буферы, которые используют некоторое количество памяти. Некоторые являются глобальными и их легко идентифицировать binlog_cache_size, важно знать, что пользователь может использовать в качестве памяти и посмотреть, следует ли уменьшить max_user_connections.
Например каждая сессия будет иметь свои собственные read_buffer_size, read_rnd_buffer_size, sort_buffer_size, join_buffer_size, thread_stack, max_allowed_packet,net_buffer_length, …
Следующее утверждение также очень интересно иметь представление о выделенной памяти для пользователя. Вот почему мы рекомендуем иметь другого пользователя для каждого приложения.
select * from memory_by_user_by_current_bytes where user in('andreyex','root')\G
*************************** 1. row ***************************
user: andreyex
current_count_used: 18374
current_allocated: 69.05 MiB
current_avg_alloc: 3.44 KiB
current_max_alloc: 23.11 MiB
total_allocated: 3.23 GiB
*************************** 2. row ***************************
user: root
current_count_used: 649
current_allocated: 2.78 MiB
current_avg_alloc: 4.38 KiB
current_max_alloc: 1.10 MiB
total_allocated: 244.42 MiB
Мы надеемся, что вы теперь немного лучше понимаете, как MySQL обрабатывает память, почему не рекомендуется увеличивать размер буферов сессии и что для внутренних временных таблиц доступен новый Engine ( TempTables).
Не стесняйтесь поделиться своими советами тоже.
Ссылка на предыдущую статью: https://andreyex.ru/bazy-dannyx/baza-dannyx-mysql/mysql-i-pamyat-istoriya-lyubvi-chast-1/.