Всем здравствуйте! Надеюсь кто-нибудь подскажет правильное решение. Сразу извиняюсь, сам я в мускуле очень неважно разбираюсь. Но хостер тоже ничего не может сказать.

Есть сайт на Битриксе на общем хостинге с каталогом ~12k товаров. База 5.2.14-MariaDB, таблицы MyISAM. Производительность в целом удовлетворительная, а базы хорошая (поэтому не хочется переводить в InnoDB).

Опуская все лишнее, для специальной обработки из базы стандартным функционалом Битрикс (CIBlockElement::GetList) выбирается список элементов. Весь запрос приложил файлом (с абстрактными значениями ключей), но самый интересный блок, а именно подзапрос, видимо, этот:




Проблема в том, что когда этих IBLOCK_SECTION_ID в условии становится ровно 755 или больше, то мускуль загибается почти совсем, когда 754 или меньше выполняется практически мгновенно (для такого большого и редкого запроса). От того, какие именно IDшники убрать/добавить разница не меняется.

Разница катастрофическая - скачек времени выполнения с 0.4 секунды до 2 минут. Запрос висит в состоянии Sending data , блокирует таблицы. При этом это чистый зависон, сам я не могу смотреть загрузку процессоров БД на хостинге, но ТП мне сказала, что никакой значимой нагрузки при выполнении долгого запроса не возникает.

Может быть кто-нибудь с таким сталкивался? Хостер предложил увеличить join_buffer_size - не помогло. Сам пробовал наращивать разные переменные, эффекта ноль. Но судя по всему, просто что-то где-то в среде выполнения запроса резко заканчивается.

Akina, max allowed packet был 1МБ, поменял на дефолтное 4МБ. К сожалению, без эффекта.

ScareCrow, ошибок вроде нет (даже не знаю, где у хостера их смотреть). План запроса это EXPLAIN? Тогда разница есть, хотя я ее понять не могу. Может быть Вы поможете?

"Хороший" запрос:

авторid select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY B const PRIMARY PRIMARY 4 const 1
1 PRIMARY L const PRIMARY PRIMARY 6 const 1 Using index
1 PRIMARY FP0 ref ix_iblock_property_1,ix_iblock_property_2 ix_iblock_property_2 153 const 1
1 PRIMARY FP1 const PRIMARY,ix_iblock_property_1 PRIMARY 4 const 1
1 PRIMARY FP2 const PRIMARY,ix_iblock_property_1 PRIMARY 4 const 1
1 PRIMARY FP3 const PRIMARY,ix_iblock_property_1 PRIMARY 4 const 1
1 PRIMARY FP4 const PRIMARY,ix_iblock_property_1 PRIMARY 4 const 1
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 10582 Using join buffer
1 PRIMARY BE eq_ref PRIMARY,ix_iblock_element_1,ix_iblock_element_4,ix_iblock_element_3,ix_iblock_element_code PRIMARY 4 BES.IBLOCK_ELEMENT_ID 1 Using where
1 PRIMARY CAT_P6 ref IXS_CAT_PRICE_PID,IXS_CAT_PRICE_GID IXS_CAT_PRICE_PID 8 BES.IBLOCK_ELEMENT_ID,const 1 Using index
1 PRIMARY CAT_P8 ref IXS_CAT_PRICE_PID,IXS_CAT_PRICE_GID IXS_CAT_PRICE_PID 8 uXXXXX.BE.ID,const 1 Using index
1 PRIMARY CAT_P9 ref IXS_CAT_PRICE_PID,IXS_CAT_PRICE_GID IXS_CAT_PRICE_PID 8 BES.IBLOCK_ELEMENT_ID,const 1 Using index
1 PRIMARY CAT_PR eq_ref PRIMARY PRIMARY 4 BES.IBLOCK_ELEMENT_ID 1
1 PRIMARY CAT_IB eq_ref PRIMARY PRIMARY 4 uXXXXX.BE.IBLOCK_ID 1
1 PRIMARY FPV0 ref ix_iblock_element_property_1,ix_iblock_element_property_2 ix_iblock_element_property_1 8 BES.IBLOCK_ELEMENT_ID,uXXXXX.FP0.ID 1 Using index
1 PRIMARY FPV1 ref ix_iblock_element_property_1,ix_iblock_element_property_2 ix_iblock_element_property_1 8 uXXXXX.BE.ID,uXXXXX.FP1.ID 1
1 PRIMARY FPV2 ref ix_iblock_element_property_1,ix_iblock_element_property_2 ix_iblock_element_property_1 8 uXXXXX.BE.ID,uXXXXX.FP2.ID 1
1 PRIMARY FPV3 ref ix_iblock_element_property_1,ix_iblock_element_property_2 ix_iblock_element_property_1 8 uXXXXX.BE.ID,uXXXXX.FP3.ID 1 Using index
1 PRIMARY FPV4 ref ix_iblock_element_property_1,ix_iblock_element_property_2 ix_iblock_element_property_1 8 uXXXXX.BE.ID,uXXXXX.FP4.ID 1 Using index
2 DERIVED BS range PRIMARY PRIMARY 4 NULL 752 Using where; Using index; Using temporary
2 DERIVED BSE ref ux_iblock_section_element ux_iblock_section_element 4 uXXXXX.BS.ID 16 Using index

"Плохой" запрос:

авторid select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY B const PRIMARY PRIMARY 4 const 1
1 PRIMARY L const PRIMARY PRIMARY 6 const 1 Using index
1 PRIMARY FP0 ref ix_iblock_property_1,ix_iblock_property_2 ix_iblock_property_2 153 const 1
1 PRIMARY FP1 const PRIMARY,ix_iblock_property_1 PRIMARY 4 const 1
1 PRIMARY FP2 const PRIMARY,ix_iblock_property_1 PRIMARY 4 const 1
1 PRIMARY FP3 const PRIMARY,ix_iblock_property_1 PRIMARY 4 const 1
1 PRIMARY FP4 const PRIMARY,ix_iblock_property_1 PRIMARY 4 const 1
1 PRIMARY BE ref PRIMARY,ix_iblock_element_1,ix_iblock_element_4,ix_iblock_element_3,ix_iblock_element_code ix_iblock_element_code 4 const 10489 Using where
1 PRIMARY CAT_P6 ref IXS_CAT_PRICE_PID,IXS_CAT_PRICE_GID IXS_CAT_PRICE_PID 8 uXXXXX.BE.ID,const 1 Using index
1 PRIMARY CAT_P8 ref IXS_CAT_PRICE_PID,IXS_CAT_PRICE_GID IXS_CAT_PRICE_PID 8 uXXXXX.BE.ID,const 1 Using index
1 PRIMARY CAT_P9 ref IXS_CAT_PRICE_PID,IXS_CAT_PRICE_GID IXS_CAT_PRICE_PID 8 uXXXXX.BE.ID,const 1 Using index
1 PRIMARY CAT_PR eq_ref PRIMARY PRIMARY 4 uXXXXX.BE.ID 1
1 PRIMARY CAT_IB eq_ref PRIMARY PRIMARY 4 uXXXXX.BE.IBLOCK_ID 1
1 PRIMARY FPV0 ref ix_iblock_element_property_1,ix_iblock_element_property_2 ix_iblock_element_property_1 8 uXXXXX.BE.ID,uXXXXX.FP0.ID 1 Using index
1 PRIMARY FPV1 ref ix_iblock_element_property_1,ix_iblock_element_property_2 ix_iblock_element_property_1 8 uXXXXX.BE.ID,uXXXXX.FP1.ID 1
1 PRIMARY FPV2 ref ix_iblock_element_property_1,ix_iblock_element_property_2 ix_iblock_element_property_1 8 uXXXXX.BE.ID,uXXXXX.FP2.ID 1
1 PRIMARY FPV3 ref ix_iblock_element_property_1,ix_iblock_element_property_2 ix_iblock_element_property_1 8 uXXXXX.BE.ID,uXXXXX.FP3.ID 1 Using index
1 PRIMARY FPV4 ref ix_iblock_element_property_1,ix_iblock_element_property_2 ix_iblock_element_property_1 8 uXXXXX.BE.ID,uXXXXX.FP4.ID 1 Using index
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 10672 Using where; Using join buffer
2 DERIVED BS range PRIMARY PRIMARY 4 NULL 756 Using where; Using index; Using temporary
2 DERIVED BSE ref ux_iblock_section_element ux_iblock_section_element 4 uXXXXX.BS.ID 16 Using index

Akinaпосмотри, что у тебя в tmp_table_size и max_heap_table_size
Уже нарастил (с 16 до 24 МБ), не помогло.

Akinaпомониторь во время выполнения "плохого" и "хорошего" запросов значения Created_tmp_tables и Created_tmp_disk_tables.

А где их можно лицезреть, да так, чтобы успеть за хорошим запросом?

Прогрессивная симптоматика! Теперь все виснет на 661 ID в запросе! :) Может это связано с тем, что я правил tmp_table_size на 24 МБ, но вернул на место 16 МБ.

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

Created_tmp_disk_tables 1,730
Created_tmp_files 5
Created_tmp_tables 4,640

Чудесатые чудеса. Решил перезапустить mysql (у хостера это делается пересохранением настроек в админке), чтобы откатить настройки базы к дефолтным (на хостинге). И заработало, больше (пока и на текущей базе) я не могу создать задержку выполнения запросов, что очень странно потому, что ребята из ТП делали это не раз, разбираясь с моей заявкой.

Решил переустановить настройки, вспомнил про optimizer_search_depth, которую давно установил на 0. Зачем написано здесь и здесь . Благодаря перезапуску мускуля ТП значение вернулось к дефолтному 62 (сейчас уже не могу точно сказать, не восстанавливали ли его на 0 админы ТП, когда перезапускали).

Оказалось, что непонятным образом виноват этот параметр (обычно с ним наоборот проблемы). Если ставлю на 0, то начинаются зависоны, со стандартным значением пока работает нормально. Будем смотреть, возможно для исправления обеих проблем (если старая вернется а новая решилась) потребуется установить какое-то среднее значение.

На данный момент, спасибо всем за участие в решении проблемы! Узнал много нового.

ScareCrow, то есть, насколько я понял из мануала , SECTION_ID вдруг перестает быть PRIMARY KEY or UNIQUE NOT NULL ?

Господа, большое спасибо за участие! Особенно спасибо javajdbc за в целом понятное для меня объяснение. На данный момент проблема отсутствует (и я надеюсь, это надолго). Битрикс действительно не славится качеством генерируемых запросов, впрочем, я вряд ли бы сгенерировал лучше. Но теперь буду знать, куда копать, если что.