Назад | Перейти на главную страницу

Почему MySQL использует так много временных таблиц?

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

Current max_heap_table_size = 200 M
Current tmp_table_size = 200 M
Of 17158 temp tables, 30% were created on disk


table_open_cache = 125 tables
table_definition_cache = 256 tables

You have a total of 97 tables

You have 125 open tables.

Current table_cache hit rate is 3%

Ранее временная таблица была of the 23725 temp tables 38% were created on disk но я изменил max_heap и tmp_table до 200 м с 16 м и снизилась до 30%.

engine myisam 
group_concat_max_len = 32768
key_buffer_size = 3.7 GB,
thread_stack = 256k,
table_cache = 125
query_cache_limit = 1M
query_cache_size = 16M
join_buffer_size = 2.00 M

max_connections = 800

Другая система с конфигурацией по умолчанию показывает of 23725 temp tables, 1% were created on disk

Но я попытался изменить значение по умолчанию на машине с этой проблемой, и она все еще показывает Of 580 temp tables, 16% were created on disk

Я использую 64-разрядную версию Ubuntu 11.4 с оперативной памятью 48 ГБ ... Может ли кто-нибудь предложить решение?

Исправит ли это изменение движка db с myisam на memory в таблицах с использованием "group by"?

Временные таблицы создаются и удаляются по мере необходимости, в зависимости от выполняемых запросов. Цифра, которую вы видите, - это общая сумма временных таблиц, созданных с момента последнего запуска MySQL, а не количество одновременно существующих.

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

Увеличение tmp_table_size value только предотвратит запись некоторых из них на диск, они все равно будут созданы в памяти и заполнены данными. Эти данные, вероятно, в первую очередь поступают с диска, хотя с 48 ГБ оперативной памяти у вас, вероятно, довольно много ее кэшировано. Даже кэшированные, поскольку 30% этих временных таблиц имеют размер больше 200 МБ, копирование этого объема данных в ОЗУ по-прежнему требует времени.

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

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

Если вам нужна помощь в настройке конкретных запросов, DBA.SE хорошее место, чтобы пойти.

TL; DR

Настройте свои запросы.