В моих PHP-скриптах есть несколько сумасшедших SQL-запросов, которые очень нуждаются в некоторой оптимизации. Однако вопрос НЕ в том, как их оптимизировать.
В этих запросах много «соединений» и некоторый причудливый «порядок по», и они выполняются для таблиц с несколькими десятками тысяч записей. Функция lot_of_joins + crazy_order_by заставляет их нуждаться в шаге «копировать в таблицу tmp».
Странно то, что один и тот же запрос иногда выполняется быстро (менее секунды), а иногда занимает много времени (десятки секунд). В ОБЕИХ случаях «объяснение» и профили показывают шаг «копировать в таблицу tmp». Когда запрос занимает много времени, 99% времени тратится на фазу «копирование в таблицу tmp». СТРАННО то, что за это время mysql потребляет почти 100% ЦП.
Итак, я понимаю, что таблица tmp иногда хранится в памяти, а иногда записывается на диск (в зависимости от текущей доступности памяти). Это прекрасно объясняет, почему один и тот же запрос иногда может выполняться быстро, а иногда - долго. Однако есть две вещи, которых я не понимаю.
Если узким местом является запись временной таблицы на диск, это должно означать, что на ввод-вывод уходит много времени, но средняя загрузка процессора в это время должна быть относительно низкой, конечно, далеко от 100%. Как процессор может быть так загружен, когда выполняет столько операций ввода-вывода?
У меня в my.cnf увеличилось:
max_heap_table_size = 1024M
tmp_table_size = 1024M
(Я думаю, что по умолчанию было 16M)
и я действительно не верю, что таблица tmp требует большего количества ОЗУ.
Насколько я понимаю, таблица tmp записывается на диск, а не в память: a- если запрос и таблица требуют этого, потому что они не удовлетворяют определенным критериям b- если она превышает минимум между max_heap_table_size и tmp_table_size
Если бы (а) было так, то это случилось бы всегда, а не время от времени. С другой стороны, мне кажется маловероятным, что это (б), потому что я значительно увеличил вышеуказанные параметры без каких-либо заметных изменений. Размер нужной таблицы не должен сильно меняться среди повторений одного и того же запроса (данные практически совпадают). Поэтому, если до увеличения размеров памяти это происходило время от времени (это означало бы, что размер требуемой таблицы tmp был примерно равен максимуму), то после такого резкого увеличения неустойчивое поведение должно было полностью исчезнуть.
Итак, мои вопросы в основном таковы:
Действительно ли создание-disk-table единственная причина, по которой этап копирования в tmp-table может занять очень много времени? (Или это может занять много времени, даже если это делается в памяти? И если да, то почему и почему случайно?)
Если да, то
Я действительно не думаю, что пример запроса и профили нужны, но я могу опубликовать их, если они есть.
Я думаю, ваша проблема не столько в tmp_table_size, сколько в том, что ваш "уродливый" запрос (набор результатов) кэшируется через query_cache. Если ваш запрос имеет тип SELECT, используйте SQL_NO_CACHE. При кэшировании он обслуживается быстро, но может перегружать кеш, и у mysql были известные проблемы с внутренней реорганизацией кеша. Кроме того, проверьте свой запрос с помощью EXPLAIN и при необходимости используйте принудительные индексы для соединений.
Это было бы более уместно в качестве комментария, но моя текущая репутация слишком низкая.