В пятницу мне достался сайт на битриксе с каталогом автозапчастей и бд размером 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 рандомных товара из всего каталога. На тот момент решил закомментировать вызов этого компонента до лучших времен. А вот второй просто так уже не исключишь, т. к. он отвечает за формирование главного меню (см. ниже).
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. По окончанию второго дня мучений вспомнил рассказ «Старик и море», и задумался, что мои потуги также не будут вознаграждены, но все обошлось.
Комментарии (20)
lolhunter
21.03.2018 00:15Частично вы сделали как делать не надо…
1) Не надо поднимать memcached.
Количество багов с ним, а так же вытеснение из кеша нужной инфы злоумышленниками или кривыми скриптами прямо стремится к 100%. Когда памяти много — вроде ничего.
Но тот же ресайзер картинок, работающий со штатным кэшем высадит вам memcached за день.
Пользуйте файловый и ОС сама будет держать горячие файлы в кеше.
2) На 4 гигах памяти и 40 SSD все должно летать. Если не летает — надо запустить отладку и посмотреть что у вас там выжирает и где.
3) Всю отладку стоит начинать с init.php — этот файл поключается на каждом хите. Проверить багует он или нет проще всего — запустив монитор производительности. Попугаев меньше 30 на вашем сайте, когда на пустой установке >50? У вас проблема в init.php
4) Дальше банально в мониторе производительности запускаем слежку и через пол часа видим все проблемы. 99% проблем решаются за час.
5) b_sale_fuser чистится бесплатным модулем с marketplace. Или ручным запуском штатного агента, который чистит корзины не раз в сутки, а раз в час. Это в принципе большая дыра битрикса. У меня магазин чуть не положили тупо ботами, которые кладут в корзину 1 товар и стирают куки. Агент за запуск чистит только 300 корзин. Остальное копится.
P.S. Уже не чистится в последних версиях — обновление ядра что-то поломалоn1ger
21.03.2018 02:07Ежики плакали, кололись — но продолжали есть кактус
lolhunter
21.03.2018 10:52+1Забавляют меня такие комментарии.
Битрикс не идеален.
Но…
Дальше появляется большое и толстое НО.
Глобально интернет-магазин можно или написать самому (самописный движок и нанятая команда) или взять готовый движок. Тут выбор в принципе очевиден. Если у вас есть 10+ лямов и бесконечное время для запуска — пишите, пилите и тд. Если всего этого нет, да и продавать хочется не через год — берите готовый движок.
Из совершенно неочевидных «проблем» самописного решения — стоимость поддержки и ввода новых фич.
Оцените пожалуйста во сколько обойдется написать такие штуки в вашем самописном решении:
marketplace.1c-bitrix.ru/solutions/ipol.sdek
marketplace.1c-bitrix.ru/solutions/yenisite.seofilter
adminvps.ru/blog/kassy-v-internet-magazine-1c-bitrix
Поддержка онлайн касс.
Интеграция с Ebay/VK/1C/Яндекс-Маркет/Директ. Да не идеальная, но написать с нуля обойдется на порядок дороже.
Ну и из приколов самописных движков.
У нас крупнейший магазин в отрасли перешел с самописа на Bitrix. Потому, что команда самописа находится в (на?) Украине. С платежами проблема, цена поддержки — конская. Другие программисты в этом разбираться не хотят и лупят по 3000р в час.
Дальше остаются коробки.
Как бы я не любил битрикс — в общем и целом конкурентов особо нет.
Есть неплохой ShopScript и opencart. Чуть меньше функционала, меньше комьюнити, дешевле программисты, больше писать.
Тут уже каждый сам выбирает.
Есть Magento — функционала много, но нам он не особо полезен. Конские ценники на модули.
Штатный модуль для большинства российских движков:
www.rugento.ru/russianpost-shipping-module.html
Практически все эти модули на битриксе бесплатные:
www.rugento.ru/magento-modules/payments.html
Причем в битриксе, да и в других — покупается или модуль с обновлениями на год или вечная. В Magento — 3-6 месяцев.
Тут уже каждый сам выбирает.
lolhunter
21.03.2018 11:52Поясню еще по пунктам, что бы было понятно, что это в общем-то не проблемы битрикса.
1) Memcached в принципе плохая идея на виртуалке с 4 гигами памяти. Потому что кеш может быть больше 1 гига (ведь есть еще ОС, БД и прочее) и, если на файловом кеше он есть, проблема только во времени доступа, то в memcached его регулярно вытесняет и надо заново генерировать область. От движка это никак не зависит, зависит исключительно от размера кеша.
3) В любом движке есть файл, аналогичный init.php. Просто в битриксе корявые программисты любят пихать свои события в этот файл. Иногда эти события работают медленно, и, чаще всего, им там не место.
5) b_sale_fuser — это таблица с корзинами пользователей. На мой сайт регулярно травят ботов, которые заходят на рандомную страницу с товаром (видимо с sitemap забирают ссылки), добавляют товар в корзину и трут у себя сессию. Корзины в любом движке хранятся в БД. Срок хранения в битриксе настраивается. Проблема в том, что штатный скрипт очистки запускается раз в 8 часов и удаляет 300 корзин за раз.
С одной стороны это правильно. Пара миллионов корзин за раз и привет вашему mysql.
Никто не мешает поменять частоту запуска на раз в час/полчаса/минуту, благо отрабатывает он меньше, чем за секунду.
По другим движкам — есть вероятность, что они вообще не чистят таблицу с корзинами.
В битриксе это известная проблема и у нее есть простые решения.oxidmod
21.03.2018 21:57Не залогиненый юзер хранит корзину в куках =) И никаких проблем. Не знаю только можно ли это сделать на битриксе
lolhunter
22.03.2018 10:05Не тестировал.
Подводные камни видятся следующие.
1) Что делаем с разлогиненными юзерами? Человек залогинился, добавил в корзину, разлогинился.
2) Мы теряем ретаргетинг и прочие фишки, так как корзины мы не знаем.
3) Что с товарами, которые деактивировали, удалили и тд? Ошибка в корзине? Не думаю, что большая проблема, но разруливать надо.
4) Есть товары, которые не продаются (а-ля услуги, технические товары для учета и тд). Мы оставляем дырку — пользователь может добавить их.oxidmod
22.03.2018 14:52На практике я встречал такое решение только в магазинах, которые не позволяют покупать незалогиненым юзерам. Соотвественно проблем с ботами нет.
Незалогиненый юзер может накидать в корзину, но при оформлении он сначала регается\логинится и тогда корзина синкается уже в бд.lolhunter
22.03.2018 15:16Ну это довольно большая проблема.
Когда просишь клиента оформить заказ первый вопрос в 90% случаев — «А у вас там еще регистрироваться надо? А может так?». Поэтому мы, например, прозрачно регистрируем при создании заказа.
Daniyar94
22.03.2018 06:37Могу ляпнуть глупость, но! Нахрена вообще корзину в базу комитить? Проще на фронтенде все держать в куках или localstorage, и ещё можно в пользовательских сессиях (server’s heap). Смысл нагружать базу волатильной датой?
lolhunter
22.03.2018 10:25Эти данные потом можно использовать.
Ретаргетинг, внутренняя аналитика по корзинам, можно найти корзину пользователя, который не завершил регистрацию и заказ.
Можно отправить письмо пользователю, который не завершил заказ (если он заполнял поля email/телефон) и узнать в чем проблема.
Отдавая в localstorage или куки эти данные уходят с юзверем.
Я не вижу большой проблемы. Если все настроено ОК, то при ДОБАВЛЕНИИ товара в корзину она кладется в бд. Запросов минимум.
Я проблему заметил когда меня боты 2 месяца мучали и база разрослась до 4 гигов. Вычистил за пол часа.bopoh13
22.03.2018 12:27Если пользователь не завершил заказ, то скорее всего либо он ему не нужен, либо у вас враждебный интерфейс. В куки не проще данные складывать?
lolhunter
22.03.2018 13:38Далеко не всегда.
Вот не далее как сегодня отловили, что выбор пунктов самовывоза у одной из курьерских служб не грузится в Safari на Iphone 6S.
Человек не смог оформить заказ. При наличии большого количества скриптов (расчет стоимости доставки кучей служб, выбор пунктов самовывоза на карте, нацеки и скидки за определенные службы и тд) все оттестировать крайне сложно.
Данные в куках никак не доступны с сервера, соответственно мы не можем увидеть, что клиент ушел и что у него было в корзине и тд.
Плюсом идут «особенные» клиенты.
Приезжает к тебе на самовывоз в магазин клиент и говорит — я вот заказ оформил. Заказа нет. Он дает СКРИНШОТ корзины с 20 товарами.
У вас есть отличный вариант — перепечатать этот заказ по позициям из скриншота или найти корзину в потерянных по товарам и оформить на ее основании заказ. Ну или заставить его оформить заказ… Но скриншот с компа…
И таких стабильно 1-2 в неделю.
Ну или дублировать данные кук в бд, что собственно дает ту же самую корзину в бд.
Dek4nice
22.03.2018 16:19Не все корзины забиваются одним товаром в один клик. Я могу набирать товар днями с разных компьютеров, подбирая кол-во товара, могу передать логин другому менеджеру для дополнения и т.п. Кейсов на самом деле много, «серверная» корзина позволяет забить более широкий функционал.
Eugeny1987
21.03.2018 05:27мне кажется, что добавление проверки на существование переменных ускорит битрикс, ибо если включить вывод ошибок, при установке из коробки будет 2 экрана варнингов
ps. последний раз видел это в 2014 году, может сейчас уже исправлено
moxy Автор
21.03.2018 11:44Позвольте уточнить, пару моментов. Вы даете общие советы и рекомендации, и на мой взгляд не дочитали до конца. В бОльшей степени проблема крылась в старой версии mysql, после обновления которого, проблемный запрос стал выполняться в разы быстрей.
2. Я перешел к логам mysql не потому что противник стандартной отладки битрикс, а потому что сайт отказывался грузиться вообще в принципе.
3. В конкретном случае в файле init.php не было проблем, т.к. он был пуст.
4. Невозможно попасть в админку, т.к. бд постоянно падает.
5. Сколько у вас потребуется времени и сил вручную запускать агент для удаления устаревших данных из этой таблицы где 17+млн записей.
Поэтому не совсем понятно из вашего комментария, что я сделал все-таки не так.lolhunter
21.03.2018 12:08По Mysql слегка не понятно.
Вы пишите: bitrix environment 5.* (на новом хостинге чистая версия 7.0);
В 7.0 Bitrix Env, если мне память не изменяет была или MariaDB 5.6+ или еще Mysql, но точно не 5.5.
Потому и подумал, что Mysql поставили и все ок.
5. Сколько у вас потребуется времени и сил вручную запускать агент для удаления устаревших данных из этой таблицы где 17+млн записей.
Минут 5?
Решение есть на форуме.
Самый простой вариант без программирования — ставим запуск агента с интервалом минута.
Вариант — нормальный:
dev.1c-bitrix.ru/community/webdev/user/10337/blog/2323/?commentId=51222#com51222
Добавить timelimit и поправить константу 300 на 100000 (если сайт все равно не работает — быстрее пройдет). Запустить ручками — посмотреть время и кинуть на cron.
bopoh13
Всё уже
украднаписано не раз.