Oracle mechanics

Статусы процессов и параметры MySQL (thread states and variables)

Одним из самых простых и доступных способов настройки производительности сервера MySQL является анализ информации вывода команды:

mysql> show [full] processlist;

В колонке state указаны статусы индивидуальных процессов (thread state) сервера MySQL — «действие, событие [ожидания] или состояние, указывающее на то, что процесс делает». Состояния [state] и связанные с ними параметры [variable] системы/сессии MySQL

statistics [state]

«The server is calculating statistics to develop a query execution plan» — интересно, в MySQL, до версии 5.1 включительно, похоже, по умолчанию не установлено ограничение на время калькуляции плана выполнения и этот процесс может длиться более часа (!). Параметры, влияющие на продолжительность state statistics сложных запросов:

optimizer_prune_level [variable]

указывает оптимизатору MySQL пропускать («обрезать» — prune) при работе некоторые неоптимальные планы, исходя из оценки количества выбираемых строк таблиц (cardinality). Это установка по умолчанию (optimizer_prune_level = 1), которую можно отменить, установив optimizer_prune_level = 0, если есть подозрение, что оптимизатор может пропустить наилучший план. Очевидно, изменение этого параметра может только увеличить продолжительность state statistics.

optimizer_search_depth [variable]

«The maximum depth of search performed by the query optimizer. Values larger than the number of relations in a query result in better query plans, but take longer to generate an execution plan for a query«, «variable tells how far into the “future” of each incomplete plan the optimizer should look to evaluate whether it should be expanded further«. Этот неясно определённый параметр напрямую связан со временем компиляции плана запроса (MySQL thread в состоянии state statistics).

Читаем в документации MySQL по настройке производительности: «… например, запросы с 12, 13 или более таблицами могут легко потребовать часов и даже дней для подготовки плана выполнения, если optimizer_search_depth установлен в значение, близкое к количеству таблиц в запросе«. Тем более удивительно, почему по умолчанию этот параметр установлен в значение 62! Разработчики MySQL планировали «биться» с запросами из 60 таблиц?

Отрадно, что есть специальное (non default!) значение optimizer_search_depth = 0, при котором оптимизатор сам выбирает подходящее значение этого параметры для конкретного запроса, и, вероятно, ограничивает время поиска оптимального плана, и действительно может на порядки уменьшить продолжительность state statistics сложных запросов.

Copying to tmp table

«[MySQL] сервер копирует данные во временную таблицу в памяти«.

Copying to tmp table on disk [state]

«То же самое состояние, но копирование ведётся во временную таблицу на диске. Как только временных данных (the temporary result set) превышает значение tmp_table_size процесс меняет расположение временной таблицы с in-memory на disk-based format для экономии памяти«.

Поскольку оба состояния указывают на одну и ту же операцию, MySQL различает их не всегда, imho. Т.е. в случае появления заметных по времени процессов в состоянии Copying to tmp table, имеет смысл проверить дисковую активность и содержимое папки с временными данными MySQL, определяемой параметром

tmpdir [variable]

(по умолчанию используется system default значение:/tmp, /var/tmp, или /usr/tmp на платформе Linux) на наличие и объём файлов.

Для исключения дисковых операций и переноса активности, связанной с временными таблицами в память, необходимо увеличить параметры:

tmp_table_size [variable]

«Максимальный размер внутренних in-memory временных таблиц  (в действительности этот предел определяется наименьшим из значений параметров max_heap_table_size и tmp_table_size)» — т.е. важно установить ещё и параметр

max_heap_table_size [variable]

«…переменная определяет максимальный размер, до которого могут расти таблицы с типом хранения MEMORY [MEMORY (HEAP) Storage Engine

key_buffer_size [variable]

определяет размер общего для всех пользовательских процессов (thread) буфера индексных блоков MyISAM таблиц. Параметр имеет прямое отношение к скорости дисковых сортировок, поскольку временные таблицы на диске имеют тип MyISAM независимо от используемого для основных таблиц типа хранения. Для контроля активности операций с дисковыми блоками используется статистика MySQL

mysql> SHOW [global] STATUS LIKE "key%";

Увеличением параметра key_buffer_size важно минимизировать операции записи на диск Key_writes.

Наглядный пример влияния 3-х вышеперечисленных параметров на скорость выполнения операций GROUP BY, использующих temporary tables: http://www.mysqlperformanceblog.com/2007/08/16/how-much-overhead-is-caused-by-on-disk-temporary-tables/

В случаях, когда temporary tables не могут быть перенесены в память в силу ограничений MySQL:

  • Присутствие TEXT или BLOB столбца в таблице
  • Присутствие столца размером более 512 байт в констукциях GROUP BY или DISTINCT
  • Присутствие столца размером более 512 байт в списке SELECT, если используются конструкции UNION или UNION ALL

можно перенести в память папку с временными файлами MySQL, используя файловую систему типа tmpfs или ramfs в Linux:

# mkdir -p /tmpfs
# mount -t tmpfs -o size=2048M,mode=0777 tmpfs /tmpfs

и установив параметр tmpdir=/tmpfs.

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

Второй вариант — распределить операции ввода-вывода во временные таблицы по нескольким дискам/контроллерам указав серверу MySQL несколько директорий tmpdir=/tmpfs01:/tmpfs02:/tmpfs03, которые будут использоваться по алгоритму round-robin.

sort_buffer_size [variable]

«Каждый процесс при необходимости сортивки будет использовать буфер указанного размера. Рекомендуется увеличить значение параметра для ускорения операций ORDER BY или GROUP BY«. Следует обратить внимание на слово «каждый» — значит увеличение параметра может пропорционально количеству процессов увеличить потребность сервера в памяти. Также имеет смысл проверить влияние параметра на производительность запросов — эксперименты могут показать противоречивые результаты!

P.S. В столбце Extra плана выполнения запроса операции GROUP BY (без сортировки) планируется использование temporary table:

mysql> explain select * from table group by col1 order by null;
-+--------------------------+-
 | Extra                    |
-+--------------------------+-
 | Using temporary          |
-+--------------------------+-

А при использовании «стандартной операции MySQL» GROUP BY (с сортировкой результатов группировки по умолчанию) — и temporary table, и filesort!

mysql> explain select * from table group by col1;
-+---------------------------------+-
 | Extra                           |
-+---------------------------------+-
 | Using temporary; Using filesort |
-+---------------------------------+-

Добавить комментарий »

Комментариев нет.

RSS feed for comments on this post. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Блог на WordPress.com.

%d такие блоггеры, как: