Oracle mechanics

08.07.2009

Сложные запросы в MySQL: state STATISTICS и optimizer_search_depth

Filed under: MySQL,optimizer — Игорь Усольцев @ 23:20
Tags:

Проблема: громоздкий и очень медленный запрос MySQL, пример вывода монитора innotop:

CXN   When  Load  QPS    Slow QCacheHit KCacheHit BpsIn   BpsOut
local Now   0.00  286.87   0   68.14%    99.20%   279.27k  1.14M
local Total 0.00  259.85   3   71.51%    99.08%   219.76k  1.44M
CXN  Cmd   ID  User   Host        DB     Time     Query
local Query 13 bitrix 192.168.0.1 bitrix 01:01:07 SELECT DISTINCT
local Query 15 bitrix 192.168.0.1 bitrix    12:37 SELECT DISTINCT

Сервер БД (MySQL 5.0.32, Innodb) достаточно загружен: 200-300 запросов в секунду, обмен данными > 1МБ/сек (BpsOut). Запрос ужасающей длительности (более часа!) находится в статусе statistics:

mysql> show full processlist;
| 13 | bitrix | 192.168.0.1:45057 | bitrix | Query | 3612 | statistics | SELECT ...

Запрос громоздкий — объединение более 30 таблиц (INNER и LEFT OUTER JOIN).

Команда EXPLAIN для этого запроса выполняется долго и «висит» в том же состоянии state statistics: The server is calculating statistics to develop a query execution plan — время расходуется на подготовку плана выполнения, почему так долго, возможно, баг?

Сайт http://bugs.mysql.com принёс интересные известия:

STRAIGHT_JOIN, FORCE INDEX — хинты и вставить их в запрос, генерируемый приложением будет непросто и небыстро.

Переменная optimizer_prune_level = 1 по умолчанию, что уже ограничивает время компиляции плана выполнения запроса за счёт исключения бесперспективных вариантов на основе оценок (heuristics). Значение optimizer_prune_level = 0 снимает ограничения и может только увеличить время компиляции.

Переменная optimizer_search_depth — «The maximum depth of search performed by the query optimizer» — похожа на то, что нужно. В документации по оптимизации MySQL рекомендуется для уменьшения времени компиляции уменьшить этот параметр, либо установить optimizer_search_depth=0, чтобы оптимизатор автоматически выбирал значение. Установлено значение по умолчанию:

mysql> show global variables like 'optimizer_search_depth';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| optimizer_search_depth | 62    |
+------------------------+-------+

Попробуем уменьшить, как рекомендуется и проверить на примере команды EXPLAIN:

mysql> set session optimizer_search_depth=30;
mysql> explain select ...
Query aborted by Ctrl+C
Empty set (7.92 sec)

прервал, всё по-прежнему долго

mysql> set session optimizer_search_depth=10;
mysql> explain select ...
...
28 rows in set (0.22 sec)

намного лучше! Попробуем предоставить оптимизатору MySQL самому подобрать значение optimizer_search_depth:

mysql> set session optimizer_search_depth=0;
mysql> explain select ...
...
28 rows in set (0.03 sec)

!!! Собственно запрос выполняется за 0.2 секунды!

Странным представляется значение по умолчанию для переменной optimizer_search_depth=62.

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

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

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 такие блоггеры, как: