Всем доброго времени суток! После прочтения данной статьи (Интернет-магазин цветов, или как мы облажались на День Святого Валентина) решил поделиться опытом оптимизации одного из сайтов на Битриксе. По неизвестной причине именно эта статья дала решительный пинок поделиться своим опытом. Хочется верить, что мой рассказ сэкономит кому-то драгоценное время (из-за моей черты «доводить все до конца» я потратил 2 выходных для достижения цели. Не хотелось бросать клиента без рабочего сайта на выходных), и, надеюсь, что более опытные коллеги укажут на мои ошибки.
В пятницу мне достался сайт на битриксе с каталогом автозапчастей и бд размером 3.2 ГБ. Проблема: сайт либо совсем не отдавал страницу, либо за время ожидания можно было забыть зачем зашел на этот сайт. Какие попытки я предпринимал и чего удалось добиться в итоге расскажу под катом.
Итак, более предметно, параметры старого хостинга:
Обычно я предпринимаю следующие шаги по оптимизации сайта на битрикс (VDS), но в этот раз ощутимых результатов это не дало:
Когда решил развернуть локальную версию меня сильно удивила бд сайта размером 3.2 Гб, особенно таблица b_sale_fuser (2.4 Гб), которая отвечает за корзины посетителей. Как оказалось в ней находились данные еще с 2014 года. Когда заглянул внутрь этой таблицы, то заметил несколько особенностей:
На этом этапе сделал предположение, что проблема кроется в использовании метода CsaleBasket::GetBasketUserID(bSkipFUserInit) без дополнительного параметра. Нюанс заключается в том, что параметр bSkipFUserInit отвечает за создание записи в таблице, даже если клиент еще ничего не положил в корзину. Моя догадка подтвердилась, когда в одном из файлов result_modifier.php нашел вызов злополучного метода без необходимо параметра. Исправив этот момент и очистив таблицу от неактуальных данных (в районе 3-ех часов, т. к. мускуль постоянно отваливался, а данные необходимо было удалять еще из связанных таблиц. Все это выполнялось стандарными методами битрикса, о чем я позже пожалел. Более подробно сообщу в выводах. После очистки кол-во записей сократилось с 19+ млн до 400+ тыс, что благотворно сказалось на работе локальной версии, однако результат все равно не устраивал. Страница стала отдаваться через 20-30 секунд, а раньше за несколько минут.
Далее было принято решение искать медленные запросы. Т.к. мы используем bitrixenv, то порядок команд на редактирование мускуль-конфига выглядит так:
По прошествии было обнаружено два запроса, которые выполнялись по 300+ сек (см. ниже). Один из них показывал 4 рандомных товара из всего каталога. На тот момент решил закомментировать вызов этого компонента до лучших времен. А вот второй просто так уже не исключишь, т. к. он отвечает за формирование главного меню (см. ниже).
Сперва меня не смутило, что на боевом сервере, этот запрос выполнялся за 300+ сек, а на локальной машине за 20+, и подумал, что причиной этому недостаточная нагрузку на сайт. Т.е. на боевом сайте в минуту посещение было 20чел\ в минуту, а на локальной копии запросы делал лишь я один. Решил воспользоваться утилитой Jmeter (см. ниже).
После запуска данного теста в 20 запросов, решил открыть сайт в браузере и сразу получил следующую ошибку: Incorrect key file for table /tmp/*. Как оказалось на каждый запрос sql мускуль создавал временные таблицы на диске во временной папке, а места не хватало. Т.к. не силен в принципе работы MySql пошел с вопросом к всезнающему гуглу (а у вас был хоть один день без обращения к поиску?!), который объяснил следующее:
если в выборке содержится поля типа TEXT/BLOB, то бд будет создавать временные таблицы на диске
И великий помощник как всегда оказался прав! В таблице b_iblock_section, оказалось парочку таких полей (см. справа), а именно DESCRIPTION и SEARCHABLE_CONTENT.
Изъяв эти поля из запроса и переписав его (см.ниже), удалось выиграть в скорости в несколько раз! В итоге запрос вместо 20+ сек на локальной машине стал возвращать результат через 1.5 сек. Однако радоваться было рано. т. к. этот запрос в бд формировался в системном файле битрикса /bitrix/modules/iblock/classes/mysql/iblocksection.php. К сожалению, не нашел ничего лучше, кроме как исправить его, хотя в курсе, что в при первом же обновлении ядра битрикса моя правка может затереться. Но на тот момент я уже боролся с этим сайтом 3 день подряд и время шло к вечеру воскресенья. Так и оставил это хозяйство…
Однако, и тут радоваться было рано. Когда залил правки на боевой сайт, результат стал стал лучше, но далек от желаемого (300 сек –-> 100+ сек). Просидев какое-то время в недоумении и поматерившись про себя, решил попробовать отработать предположение о разнице версий mysql на боевом сервера и на локальной машине. Можно подумать, что дело в настройках самой бд, однако я отсек этот пункт еще вначале пути, когда выставил такие же настройки, что и на боевой машине. Оставалось только обновиться с версии 5.5.* до 5.6.35 на сервере (последняя доступная версия mysql на машине). На этот шаг возлагал большие надежды, поскольку идеи и предположения, в чем могло быть дело, иссякли. Да и жалко было выходные, которые потратил на поиск и решение проблемы. Вместе с выходными заканчивались и мои нервы… Но как же я был рад, когда после обновления бд все заработало как нужно было, цифры в логах запросов были идентичны цифрам на локальной машине, да и сайт стал просто летать. Радости не было предела, ее хватило на двоих: меня и мою девушку, которая поняла, что остаток выходных проведу все-таки с ней, а не за экраном монитора.
Какие же методы сделал для себя:
Благодарю всех, кто уделил свое время. Будет здорово, если оставите конструктивную критику или советы.
P.S. По окончанию второго дня мучений вспомнил рассказ «Старик и море», и задумался, что мои потуги также не будут вознаграждены, но все обошлось.
P.P.S. Чтобы скорость удаления данных возрасла из таблицы b_sale_fuser и других связанных с ней, можно было удалить из них индексы, а после актуализации вновь добавить.
В пятницу мне достался сайт на битриксе с каталогом автозапчастей и бд размером 3.2 ГБ. Проблема: сайт либо совсем не отдавал страницу, либо за время ожидания можно было забыть зачем зашел на этот сайт. Какие попытки я предпринимал и чего удалось добиться в итоге расскажу под катом.
Итак, более предметно, параметры старого хостинга:
- VDS;
- 8 GB ОЗУ (на новом хостинге 4GB);
- 40GB SSD;
- bitrix environment 5.* (на новом хостинге чистая версия 7.0);
- PHP 5.6 (на новом хостинге PHP 7.0);
- MySql 5.5.*;
- файловое кеширование битрикса;
- агенты выполняются на хитах.
Обычно я предпринимаю следующие шаги по оптимизации сайта на битрикс (VDS), но в этот раз ощутимых результатов это не дало:
- перенос выполнение агентов с хитов на крон (подробней);
- настройка memcached (подробней);
- в этот раз добавился перенос на новый хостинг с обновленными компонентами (php, mysql и т.д.)
Когда решил развернуть локальную версию меня сильно удивила бд сайта размером 3.2 Гб, особенно таблица b_sale_fuser (2.4 Гб), которая отвечает за корзины посетителей. Как оказалось в ней находились данные еще с 2014 года. Когда заглянул внутрь этой таблицы, то заметил несколько особенностей:
- 80% данных были только за последний месяц (всего 17+ млн записей);
- записи создавались с периодичностью в несколько секунд. Стандартный метод по очистке брошенных корзин попросту не справлялся;
- в таблице три индекса, а это значит, что при изменении данных в ней, индексы будут обновляться, что влечет дополнительные расходы на ресурсы;
На этом этапе сделал предположение, что проблема кроется в использовании метода CsaleBasket::GetBasketUserID(bSkipFUserInit) без дополнительного параметра. Нюанс заключается в том, что параметр bSkipFUserInit отвечает за создание записи в таблице, даже если клиент еще ничего не положил в корзину. Моя догадка подтвердилась, когда в одном из файлов result_modifier.php нашел вызов злополучного метода без необходимо параметра. Исправив этот момент и очистив таблицу от неактуальных данных (в районе 3-ех часов, т. к. мускуль постоянно отваливался, а данные необходимо было удалять еще из связанных таблиц. Все это выполнялось стандарными методами битрикса, о чем я позже пожалел. Более подробно сообщу в выводах. После очистки кол-во записей сократилось с 19+ млн до 400+ тыс, что благотворно сказалось на работе локальной версии, однако результат все равно не устраивал. Страница стала отдаваться через 20-30 секунд, а раньше за несколько минут.
Далее было принято решение искать медленные запросы. Т.к. мы используем bitrixenv, то порядок команд на редактирование мускуль-конфига выглядит так:
nano /etc/mysql/bx/bvxat.cnf
# добавляем строки в файл
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 1
service mysqld restart
По прошествии было обнаружено два запроса, которые выполнялись по 300+ сек (см. ниже). Один из них показывал 4 рандомных товара из всего каталога. На тот момент решил закомментировать вызов этого компонента до лучших времен. А вот второй просто так уже не исключишь, т. к. он отвечает за формирование главного меню (см. ниже).
Sql-запрос
Tcp port: 3306 Unix socket: /var/lib/mysqld/mysqld.sock
Time Id Command Argument
# Time: 180318 18:30:07
# User@Host: bitrix[bitrix] @ localhost []
# Thread_id: 96 Schema: testdb QC_hit: No
# Query_time: 301.414008 Lock_time: 0.000324 Rows_sent: 13 Rows_examined: 260456
use testdb;
SET timestamp=1521387007;
SELECT DISTINCT
BS.*,
B.LIST_PAGE_URL,
B.SECTION_PAGE_URL,
B.IBLOCK_TYPE_ID,
B.CODE as IBLOCK_CODE,
B.XML_ID as IBLOCK_EXTERNAL_ID,
BS.XML_ID as EXTERNAL_ID,
DATE_FORMAT(BS.TIMESTAMP_X, '%d.%m.%Y %H:%i:%s') as TIMESTAMP_X,
DATE_FORMAT(BS.DATE_CREATE, '%d.%m.%Y %H:%i:%s') as DATE_CREATE
,COUNT(DISTINCT BE.ID) as ELEMENT_CNT
FROM b_iblock_section BS
INNER JOIN b_iblock B ON BS.IBLOCK_ID = B.ID
INNER JOIN b_iblock_section BSTEMP ON BSTEMP.IBLOCK_ID = BS.IBLOCK_ID
LEFT JOIN b_iblock_section_element BSE ON BSE.IBLOCK_SECTION_ID=BSTEMP.ID
LEFT JOIN b_iblock_element BE ON (BSE.IBLOCK_ELEMENT_ID=BE.ID
AND ((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL )
AND BE.IBLOCK_ID = BS.IBLOCK_ID
)
AND BE.ACTIVE='Y'
AND (BE.ACTIVE_TO >= now() OR BE.ACTIVE_TO IS NULL)
AND (BE.ACTIVE_FROM <= now() OR BE.ACTIVE_FROM IS NULL))
WHERE 1=1
AND BSTEMP.IBLOCK_ID = BS.IBLOCK_ID
AND BSTEMP.LEFT_MARGIN >= BS.LEFT_MARGIN
AND BSTEMP.RIGHT_MARGIN <= BS.RIGHT_MARGIN
AND BSTEMP.GLOBAL_ACTIVE = 'Y'
AND ((((BS.ACTIVE='Y'))))
AND ((((BS.GLOBAL_ACTIVE='Y'))))
AND ((((BS.IBLOCK_ID = '9'))))
AND ((((BS.DEPTH_LEVEL <= '1'))))
AND ((((B.ID = '9'))))
AND ((
B.ID IN (
SELECT IBLOCK_ID
FROM b_iblock_group IBG
WHERE IBG.GROUP_ID IN (2)
AND IBG.PERMISSION >= 'R'
AND (IBG.PERMISSION='X' OR B.ACTIVE='Y')
)
OR (B.RIGHTS_MODE = 'E' AND EXISTS (
SELECT SR.SECTION_ID
FROM b_iblock_section_right SR
INNER JOIN b_iblock_right IBR ON IBR.ID = SR.RIGHT_ID
INNER JOIN b_user_access UA ON UA.ACCESS_CODE = IBR.GROUP_CODE AND UA.USER_ID = 0
WHERE SR.SECTION_ID = BS.ID
AND IBR.OP_SREAD = 'Y'
))
))
GROUP BY BS.ID, B.ID
ORDER BY BS.LEFT_MARGIN asc;
Сперва меня не смутило, что на боевом сервере, этот запрос выполнялся за 300+ сек, а на локальной машине за 20+, и подумал, что причиной этому недостаточная нагрузку на сайт. Т.е. на боевом сайте в минуту посещение было 20чел\ в минуту, а на локальной копии запросы делал лишь я один. Решил воспользоваться утилитой Jmeter (см. ниже).
После запуска данного теста в 20 запросов, решил открыть сайт в браузере и сразу получил следующую ошибку: Incorrect key file for table /tmp/*. Как оказалось на каждый запрос sql мускуль создавал временные таблицы на диске во временной папке, а места не хватало. Т.к. не силен в принципе работы MySql пошел с вопросом к всезнающему гуглу (а у вас был хоть один день без обращения к поиску?!), который объяснил следующее:
если в выборке содержится поля типа TEXT/BLOB, то бд будет создавать временные таблицы на диске
И великий помощник как всегда оказался прав! В таблице b_iblock_section, оказалось парочку таких полей (см. справа), а именно DESCRIPTION и SEARCHABLE_CONTENT.
Изъяв эти поля из запроса и переписав его (см.ниже), удалось выиграть в скорости в несколько раз! В итоге запрос вместо 20+ сек на локальной машине стал возвращать результат через 1.5 сек. Однако радоваться было рано. т. к. этот запрос в бд формировался в системном файле битрикса /bitrix/modules/iblock/classes/mysql/iblocksection.php. К сожалению, не нашел ничего лучше, кроме как исправить его, хотя в курсе, что в при первом же обновлении ядра битрикса моя правка может затереться. Но на тот момент я уже боролся с этим сайтом 3 день подряд и время шло к вечеру воскресенья. Так и оставил это хозяйство…
Было
BS.*,
...
Стало
BS.ID,
BS.TIMESTAMP_X,
BS.MODIFIED_BY,
BS.DATE_CREATE,
BS.CREATED_BY,
BS.IBLOCK_ID,
BS.IBLOCK_SECTION_ID,
BS.ACTIVE,
BS.GLOBAL_ACTIVE,
BS.SORT,
BS.NAME,
BS.PICTURE,
BS.LEFT_MARGIN,
BS.RIGHT_MARGIN,
BS.DEPTH_LEVEL,
BS.CODE,
BS.XML_ID,
BS.TMP_ID,
BS.DETAIL_PICTURE,
BS.SOCNET_GROUP_ID,
...
Однако, и тут радоваться было рано. Когда залил правки на боевой сайт, результат стал стал лучше, но далек от желаемого (300 сек –-> 100+ сек). Просидев какое-то время в недоумении и поматерившись про себя, решил попробовать отработать предположение о разнице версий mysql на боевом сервера и на локальной машине. Можно подумать, что дело в настройках самой бд, однако я отсек этот пункт еще вначале пути, когда выставил такие же настройки, что и на боевой машине. Оставалось только обновиться с версии 5.5.* до 5.6.35 на сервере (последняя доступная версия mysql на машине). На этот шаг возлагал большие надежды, поскольку идеи и предположения, в чем могло быть дело, иссякли. Да и жалко было выходные, которые потратил на поиск и решение проблемы. Вместе с выходными заканчивались и мои нервы… Но как же я был рад, когда после обновления бд все заработало как нужно было, цифры в логах запросов были идентичны цифрам на локальной машине, да и сайт стал просто летать. Радости не было предела, ее хватило на двоих: меня и мою девушку, которая поняла, что остаток выходных проведу все-таки с ней, а не за экраном монитора.
Какие же методы сделал для себя:
- тестирование и выявление проблемы на локальном компьютере логично проводить в условиях приближенным к боевым. К сожалению, об этом додумался несколько часов спустя, обновляя одиночными запросами страницу сайта;
- иногда проще обновить используемые компоненты. Например, это помогло в моем многодневном квесте, правда жаль, что додумался об этом только в конце эпопеи.
- Спустя какое-то время считаю, что лучше было бы проанализировать системные файлы CMS для создания нескольких sql-запросов в бд, которые бы очистили злополучную таблицу b_sale_fuser и связанные с ней данные. А то сидел и ждал, пока системными методами удаляются записи по штуке за проход…
- лучше потратить время на изучения инструментов с которыми работаешь. В моем случае пойду почитаю книгу по MySql, чтобы новые проблемы не были для меня необъяснимым фокусом.
Благодарю всех, кто уделил свое время. Будет здорово, если оставите конструктивную критику или советы.
P.S. По окончанию второго дня мучений вспомнил рассказ «Старик и море», и задумался, что мои потуги также не будут вознаграждены, но все обошлось.
P.P.S. Чтобы скорость удаления данных возрасла из таблицы b_sale_fuser и других связанных с ней, можно было удалить из них индексы, а после актуализации вновь добавить.
