Mysql tmp_table_size max_heap_table_size

На моем сервере 2 дня назад мой tmp_table_size= max_heap_table_size(16M).

Я сделал задание cron, которое выполняется один раз в час и генерирует отчет, начиная с: created_tmp_disk_tables, created_tmp_files, created_tmp_tables

В моем отчете: created_tmp_disk_tables + created_tmp_files + created_tmp_tables= 100% моих временных данных

С этим:

  • с tmp_table_size= max_heap_table_size= 16M отчет показал мне следующий средний отчет:
    • 27.37% (created_tmp_disk_tables)
    • 1.16% (created_tmp_files)
    • 71.48% (created_tmp_tables)

Как я могу оптимизировать эти результаты?

  1. с tmp_table_size= max_heap_table_size= 20M в первый час:

    • 23.48% (created_tmp_disk_tables)
    • 32.44% (created_tmp_files)
    • 44.07% (created_tmp_tables)

Через 7 часов (после перезапуска):

  • 21.70% (created_tmp_disk_tables)
  • 33.75% (created_tmp_files)
  • 44.55% (created_tmp_tables)

Это не то, что я ожидал.

  • таблицы дисков уменьшены с 27.37% до 21.70% → ожидаются гораздо больше
  • временные файлы увеличивают форму 1.16% до 33.75% → почему?
  • таблицы памяти уменьшены с 71.48% до 44.55% → странно; ожидаемый рост

Ответы

Ответ 1

Всякий раз, когда вы увеличиваете tmp_table_size и max_heap_table_size, имейте в виду, что настройка этих параметров не приводит к тому, что запросы ведут себя лучше. Фактически это приводит к тому, что неэффективные запросы ведут себя хуже, чем раньше. При каких обстоятельствах?

Когда запрос выполняет объединение или сортировку (через ORDER BY) без преимущества индекса, в памяти должна быть сформирована таблица temp. Это увеличит Created_tmp_tables.

Что делать, если таблица temp растет до количества байтов в tmp_table_size и требует больше места? Происходит следующая последовательность событий:

  • Обработка запросов должна прекратиться.
  • Создайте временную таблицу на диске
  • Переносить содержимое временной таблицы на основе памяти в временную таблицу на основе диска
  • Отбросить временную таблицу на основе памяти
  • Обработка запросов продолжается с использованием временной таблицы на диске

Этот процесс увеличивает Created_tmp_disk_tables

Зная эти механизмы, давайте исследовать, что произошло в каждом случае

таблицы дисков уменьшились с 27,37% до 21,70% → ожидалось гораздо больше

Это может легко произойти, если в запросах, которые раньше выполнялись, были сохранены кешированные результаты в ОЗУ. Это избавит вас от необходимости обрабатывать запрос с самого начала и не воссоздавать те же самые большие временные таблицы.

временные файлы увеличиваются с 1,16% до 33,75% → почему?

Это не удивительно. Это просто объясняет тот факт, что есть запросы, требующие временные таблицы. Сначала они были созданы в оперативной памяти. Это просто указывает на наличие запросов, которые не присоединяются хорошо (возможно, join_buffer_size слишком мало) или ORDER BY неиндексированные столбцы или столбцы с временной таблицей (возможно, sort_buffer_size слишком мало).

таблицы памяти уменьшились с 71,48% до 44,55% → странно; ожидается повышение

Это тоже не удивительно. Если есть достаточно вызовов для одного и того же запроса с одинаковыми значениями, сортировки и объединения могут быть вытеснены выполнением запросов из ранее кэшированных результатов.

РЕКОМЕНДАЦИЯ

В свете этих вещей, вот что можно было бы скорректировать:

  • join_buffer_size
  • sort_buffer_size
  • Создание индексов, которые будут использоваться
    • в соединениях через eq_ref
    • сортирует по индексу в порядке

Общая цель должна заключаться в том, чтобы предотвратить создание темповой таблицы как можно больше. Простое увеличение tmp_table_size и max_heap_table_size позволяет использовать неэффективные запросы и таблицы, которые не имеют надлежащего индексации, работают amok.