Немногословный избежит позора, Крупица амбры лучше кучи сора (Саади).

MySQL и память: история любви (часть 2)

6 мин для чтения
FavoriteLoadingДобавить в избранное
1 Звезда2 Звезды3 Звезды4 Звезды5 Звезд (Пока оценок нет)
Загрузка...
5 июня 2019
MySQL и память. История любви (часть 1)
В предыдущем посте мы видели, что MySQL любит память. Мы также увидели, как выполнять проверки операционной системы и некоторые изменения конфигурации для Swap и NUMA.

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

Представленный в MySQL 5.7 и включенный по умолчанию в MySQL 8.0, Performance_Schemaинструментарий памяти позволяет нам лучше понять, что MySQL выделяет и почему.

Давайте проверим наш сервер MySQL, используя SYS:

MySQL и память: история любви (часть 2)

 

Обратите внимание, что существует ошибка, связанная с тем, как учитывается статистика 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/.

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Просмотров: 4

Если статья понравилась, то поделитесь ей в социальных сетях:

Отправить ответ

Войти с помощью: 
avatar
  Подписаться  
Уведомление о

Сообщить об опечатке

Текст, который будет отправлен нашим редакторам:

Заполните форму и наш менеджер перезвонит Вам в самое ближайшее время!

badge
Обратный звонок 1
Отправить
galka

Спасибо! Ваша заявка принята

close
galka

Спасибо! Ваша заявка принята

close