Всем доброго времени суток! После прочтения данной статьи (Интернет-магазин цветов, или как мы облажались на День Святого Валентина) решил поделиться опытом оптимизации одного из сайтов на Битриксе. По неизвестной причине именно эта статья дала решительный пинок поделиться своим опытом. Хочется верить, что мой рассказ сэкономит кому-то драгоценное время (из-за моей черты «доводить все до конца» я потратил 2 выходных для достижения цели. Не хотелось бросать клиента без рабочего сайта на выходных), и, надеюсь, что более опытные коллеги укажут на мои ошибки.

В пятницу мне достался сайт на битриксе с каталогом автозапчастей и бд размером 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. По окончанию второго дня мучений вспомнил рассказ «Старик и море», и задумался, что мои потуги также не будут вознаграждены, но все обошлось.

Комментарии (20)


  1. bopoh13
    21.03.2018 00:14

    Всё уже украд написано не раз.


  1. 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. Уже не чистится в последних версиях — обновление ядра что-то поломало


    1. n1ger
      21.03.2018 02:07

      Ежики плакали, кололись — но продолжали есть кактус


      1. 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 месяцев.
        Тут уже каждый сам выбирает.


      1. lolhunter
        21.03.2018 11:52

        Поясню еще по пунктам, что бы было понятно, что это в общем-то не проблемы битрикса.
        1) Memcached в принципе плохая идея на виртуалке с 4 гигами памяти. Потому что кеш может быть больше 1 гига (ведь есть еще ОС, БД и прочее) и, если на файловом кеше он есть, проблема только во времени доступа, то в memcached его регулярно вытесняет и надо заново генерировать область. От движка это никак не зависит, зависит исключительно от размера кеша.
        3) В любом движке есть файл, аналогичный init.php. Просто в битриксе корявые программисты любят пихать свои события в этот файл. Иногда эти события работают медленно, и, чаще всего, им там не место.
        5) b_sale_fuser — это таблица с корзинами пользователей. На мой сайт регулярно травят ботов, которые заходят на рандомную страницу с товаром (видимо с sitemap забирают ссылки), добавляют товар в корзину и трут у себя сессию. Корзины в любом движке хранятся в БД. Срок хранения в битриксе настраивается. Проблема в том, что штатный скрипт очистки запускается раз в 8 часов и удаляет 300 корзин за раз.
        С одной стороны это правильно. Пара миллионов корзин за раз и привет вашему mysql.
        Никто не мешает поменять частоту запуска на раз в час/полчаса/минуту, благо отрабатывает он меньше, чем за секунду.
        По другим движкам — есть вероятность, что они вообще не чистят таблицу с корзинами.
        В битриксе это известная проблема и у нее есть простые решения.


        1. oxidmod
          21.03.2018 21:57

          Не залогиненый юзер хранит корзину в куках =) И никаких проблем. Не знаю только можно ли это сделать на битриксе


          1. lolhunter
            22.03.2018 10:05

            Не тестировал.
            Подводные камни видятся следующие.
            1) Что делаем с разлогиненными юзерами? Человек залогинился, добавил в корзину, разлогинился.
            2) Мы теряем ретаргетинг и прочие фишки, так как корзины мы не знаем.
            3) Что с товарами, которые деактивировали, удалили и тд? Ошибка в корзине? Не думаю, что большая проблема, но разруливать надо.
            4) Есть товары, которые не продаются (а-ля услуги, технические товары для учета и тд). Мы оставляем дырку — пользователь может добавить их.


            1. oxidmod
              22.03.2018 14:52

              На практике я встречал такое решение только в магазинах, которые не позволяют покупать незалогиненым юзерам. Соотвественно проблем с ботами нет.

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


              1. lolhunter
                22.03.2018 15:16

                Ну это довольно большая проблема.
                Когда просишь клиента оформить заказ первый вопрос в 90% случаев — «А у вас там еще регистрироваться надо? А может так?». Поэтому мы, например, прозрачно регистрируем при создании заказа.


        1. Daniyar94
          22.03.2018 06:37

          Могу ляпнуть глупость, но! Нахрена вообще корзину в базу комитить? Проще на фронтенде все держать в куках или localstorage, и ещё можно в пользовательских сессиях (server’s heap). Смысл нагружать базу волатильной датой?


          1. lolhunter
            22.03.2018 10:25

            Эти данные потом можно использовать.
            Ретаргетинг, внутренняя аналитика по корзинам, можно найти корзину пользователя, который не завершил регистрацию и заказ.
            Можно отправить письмо пользователю, который не завершил заказ (если он заполнял поля email/телефон) и узнать в чем проблема.
            Отдавая в localstorage или куки эти данные уходят с юзверем.
            Я не вижу большой проблемы. Если все настроено ОК, то при ДОБАВЛЕНИИ товара в корзину она кладется в бд. Запросов минимум.
            Я проблему заметил когда меня боты 2 месяца мучали и база разрослась до 4 гигов. Вычистил за пол часа.


            1. bopoh13
              22.03.2018 12:27

              Если пользователь не завершил заказ, то скорее всего либо он ему не нужен, либо у вас враждебный интерфейс. В куки не проще данные складывать?


              1. lolhunter
                22.03.2018 13:38

                Далеко не всегда.
                Вот не далее как сегодня отловили, что выбор пунктов самовывоза у одной из курьерских служб не грузится в Safari на Iphone 6S.
                Человек не смог оформить заказ. При наличии большого количества скриптов (расчет стоимости доставки кучей служб, выбор пунктов самовывоза на карте, нацеки и скидки за определенные службы и тд) все оттестировать крайне сложно.
                Данные в куках никак не доступны с сервера, соответственно мы не можем увидеть, что клиент ушел и что у него было в корзине и тд.
                Плюсом идут «особенные» клиенты.
                Приезжает к тебе на самовывоз в магазин клиент и говорит — я вот заказ оформил. Заказа нет. Он дает СКРИНШОТ корзины с 20 товарами.
                У вас есть отличный вариант — перепечатать этот заказ по позициям из скриншота или найти корзину в потерянных по товарам и оформить на ее основании заказ. Ну или заставить его оформить заказ… Но скриншот с компа…
                И таких стабильно 1-2 в неделю.
                Ну или дублировать данные кук в бд, что собственно дает ту же самую корзину в бд.


                1. bopoh13
                  22.03.2018 13:41

                  Благодарю за показательный пример.


              1. Dek4nice
                22.03.2018 16:19

                Не все корзины забиваются одним товаром в один клик. Я могу набирать товар днями с разных компьютеров, подбирая кол-во товара, могу передать логин другому менеджеру для дополнения и т.п. Кейсов на самом деле много, «серверная» корзина позволяет забить более широкий функционал.


    1. Eugeny1987
      21.03.2018 05:27

      мне кажется, что добавление проверки на существование переменных ускорит битрикс, ибо если включить вывод ошибок, при установке из коробки будет 2 экрана варнингов
      ps. последний раз видел это в 2014 году, может сейчас уже исправлено


    1. moxy Автор
      21.03.2018 11:44

      Позвольте уточнить, пару моментов. Вы даете общие советы и рекомендации, и на мой взгляд не дочитали до конца. В бОльшей степени проблема крылась в старой версии mysql, после обновления которого, проблемный запрос стал выполняться в разы быстрей.

      2. Я перешел к логам mysql не потому что противник стандартной отладки битрикс, а потому что сайт отказывался грузиться вообще в принципе.
      3. В конкретном случае в файле init.php не было проблем, т.к. он был пуст.
      4. Невозможно попасть в админку, т.к. бд постоянно падает.
      5. Сколько у вас потребуется времени и сил вручную запускать агент для удаления устаревших данных из этой таблицы где 17+млн записей.

      Поэтому не совсем понятно из вашего комментария, что я сделал все-таки не так.


      1. 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.


  1. oxidmod
    21.03.2018 00:56

    Битрикс тупит с выборкой из таблы на ~24кк записей по индексу? ОО


    1. Sheti
      21.03.2018 11:32

      Опыт разработки 1С виден