Сайтът „пада“ постоянно, а увеличение на трафика няма [Lifehack.bg Case Study]

Това е от онези случаи, в които причината за проблема е дълбоко скрита в настройките на сайта. По-точно в настройките на един плъгин, като логовете и симптомите не подсказват посоката за разрешаване. Когато причините за проблема не са очевидни, се създава усещането, че те са външни на сайта, например в сървъра.

Сайтът е Lifehack.bg, а казусът е, че от известно време се зарежда много бавно или изобщо не се зарежда.

Както в предишен казус, при който лошите шеги изиграха кеш технологиите, така и за този случай се оказа, че кеширането може да не ускорява сайта, а обратното.

…въпреки всичките активирани технологии за кеширане и ускоряване, WordPress сайт, с един единствен продукт в WooCommerce, отказваше да се зарежда в най-критичните моменти (по време на активна реклама).

🔗 Лошите шеги на технологиите за ускорение – WordPress и казусът lifehack.bg | Blog

Кеширането е най-прилаганият метод за ускоряване, но за да може да изпълни тази цел, кешът трябва да се генерира. А времето и ресурсите, нужни за генерирането му, пряко зависят от информацията, която се обработва. Особено ако информацията първо се извлича от базата данни.

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

Казусът

В определени моменти работи перфектно, в други едвам зарежда, в трети изобщо не зарежда и показва страница с надпис – This site can’t be reached.

Христо, lifehack.bg

Без реално увеличение на трафика към сайта, от време на време той зарежда бавно или понякога изобщо не зарежда.

От гледна точка на сървъра (Managed VPS), в тези проблемни моменти са се стартирали неоптимални SQL заявки. Изпълнението им е причинило забавяне и впоследствие прекъсване на работещите PHP процеси, което е довело и до изчерпване на оперативната памет за акаунта. Това се отразява в голямо забавяне на сайта и администрацията му или невъзможността изобщо да се заредят.

Клиентът не е извършвал промени по сайта, не е инсталирал плъгини или променял настройки. Сайтът просто от определен момент започва да не работи коректно.

В рамките на хостинг услугата и сървъра

При първоначалната сигнализация от клиента, че има проблем със сайта, извършихме подробна проверка.

От проверката забелязахме, че паметта за целия акаунт се изчерпва, което спира изпълнението на всякакви процеси. А причината за това е неоптимално изпълняващи се SQL заявки към базата данни.

С цел да се олекоти изпълнението на SQL заявките, извършихме подобрения по хостинга, така че при обработката им да се намали консумацията на памет и да се ускори завършването им.

Подобренията са в нашата сфера на възможности, тоест всичко, което можем да направим от страна на сървъра:

• Тъй като продължителното изчакване на процеса, обработващ дадена неоптимална заявка, предизвиква отлагане и принудително прекратяване на останалите скриптове, увеличихме времето за процесите – FcgidIOTimeout и FcgidBusyTimeout.
• За да не се блокират таблиците, тъй като те се „заключват“ по време на изпълнението на една заявка и други не могат да се обработят, променихме енджина за таблиците от MyISAM на InnoDB (_posts, _postmeta, _comments, _commentmeta, _options, _users, _usermeta). При InnoDB се „заключва“ само обработваният ред, докато таблицата остава достъпна за други заявки.
• Приложихме специални настройки на сървъра за бази данни и InnoDB, за да гарантираме, че дори дадена SQL заявка да отнема повече време за изпълнение, това няма да попречи на обработката на друга паралелна заявка към същата таблица.
• Приложихме ъпгрейд на MySQL сървъра до MariaDB 10.2.
• Увеличихме паметта на Managed VPS сървъра от 6GB на 10GB.
• Преместихме сайта на ALL SSD платформа, за да може освен базата данни, всички дискови операции да бъдат в пъти по-бързи и оптимални.

Дотук извършените хостинг подобрения дадоха положителен резултат и сайтът започна да работи коректно. Но…

Въпреки постигнатия успех, от многото опит, който нашият екип е придобил в борбата с подобни „екзотични случаи“, както ги определяме, усещахме, че има още нещо, което можем да направим. Затова преминахме към проверки отвъд сървъра и хостинг услугата. И първото, което трябваше да разберем е:

какви са тези SQL заявки, защо се изпълняват неоптимално и коя функционалност в сайта ги използва.

Извън хостинг услугата и сървъра

От допълнителните проверки открихме, че проблемните SQL заявки се използват от плъгина WooCommerce Subscriptions. Целта им е, да се извлече статистическа информация от базата данни за абонаментите в сайта, която след това да се кешира. Но конкретно една от тези SQL заявки, които се изпълняват постоянно и автоматично, към края на годината обработва около 20 мил. редове и се изпълнява за над 150 секунди.

Причината за казуса

Плъгинът WooCommerce Subscriptions има опция за генериране и показване на доклади (Subscription Reports) в администрацията. За да се ускори зареждането на статистическите данни в страницата с доклади, плъгинът генерира предварително докладите и кешира данните им. По този начин, когато потребителят отвори страницата с доклади, той ще получи веднага данните, вместо да изчаква генерирането им на момента.

Докладите се генерират за различни периоди от време:

1. От началото на година до днешна дата.
2. За последните 30 дни.
3. От началото на месеца до днешна дата.
4. За последната седмица.

Най-тежката и проблемна операция е при изпълнението на SQL заявката, която генерира доклада за период „от началото на годината до днешна дата“.

Заявките за кратките периоди обработват по-малък обем от данни и не създават проблем и завършват за кратко време.

SQL заявката за годишния доклад:

Time: 191009 10:20:29
 User@Host: cpuser_admin[cpuser_admin] @ localhost []
 Query_time: 152.424936  Lock_time: 0.000194 Rows_sent: 283  Rows_examined: 19652878
 SET timestamp=1570605629;
 SELECT searchdate.Date as date, COUNT( DISTINCT wcsubs.ID) as count
  FROM (
   SELECT DATE_FORMAT(a.Date,'%Y-%m-%d') as Date, 0 as cnt
  FROM (
   SELECT DATE('2019-10-10') - INTERVAL(a.a + (10 * b.a) + (100 * c.a)) DAY as Date
  FROM (
   SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2
    UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6
    UNION ALL SELECT 7 UNION ALL SELECT 8
    UNION ALL SELECT 9
    ) as a
    CROSS JOIN (
   SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2
    UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6
    UNION ALL SELECT 7 UNION ALL SELECT 8
    UNION ALL SELECT 9
    ) as b
    CROSS JOIN (
   SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2
    UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6
    UNION ALL SELECT 7 UNION ALL SELECT 8
    UNION ALL SELECT 9
    ) AS c
    ) a
    WHERE a.Date >= '2019-01-01' AND a.Date <= '2019-10-10'                                 ) searchdate
 LEFT JOIN (
    wp_posts AS wcsubs
    LEFT JOIN wp_postmeta AS wcsmeta
    ON wcsubs.ID = wcsmeta.post_id AND wcsmeta.meta_key = '_schedule_end'                                 ) ON DATE( wcsubs.post_date ) < searchdate.Date
    AND wcsubs.post_type IN ( 'shop_subscription' )
    AND wcsubs.post_status NOT IN( 'trash', 'auto-draft' )
    AND (
    DATE( CONVERT_TZ( wcsmeta.meta_value , '+00:00', '+3:00' ) ) >= searchdate.Date
    OR wcsmeta.meta_value = 0
    OR wcsmeta.meta_value IS NULL
    )
GROUP BY searchdate.Date
ORDER BY searchdate.Date ASC;

Заявките за всички периоди се изпълняват в пакет, една след друга, през няколко секунди време. Всяка от тях може да се стартира и индивидуално от администрацията на сайта » WooCommerce » Доклади » Subscriptions, като се избере конкретния времеви диапазон.

Колкото повече напредват календарните дни, толкова повече данни трябва да обработи заявката за годишния период. В началото на годината, логично се изпълнява видимо по-бързо, защото се гледа текущата година. Аналогично към края на годината тази заявка ще обработва огромно количество данни.

Проблемът не е в това, че генерирането на доклада за годишния период отнема много ресурс и се извършва бавно, проблемът е, че кешът му се прегенерира при всяка една промяна в данните – добавяне, промяна или премахване на абонамент (създаване на нов, подновяване, смяна на статус и всяка друга промяна по абонаментите). Това инициира изпълнението на проблемната заявка десетки пъти на ден.

Автоматичното прегенериране на кеша се извършва 5 минути след промяна на данните и е активирано за всеки сайт, в който има под 3000 абонамента.

Решението

Деактивиране на автоматичното прегенериране на кеша

За да не се създава описаният казус, разработчиците предлагат автоматичното прегенериране на кеша, което се случва при всяка една промяна в данните, да се деактивира.

Тъй като клиентът не използва докладите и данните за статистики, постоянното прегенериране на кеша им засега е ненужно.

С помощта на „решение“ под формата на плъгин, предоставен от разработчиците, е възможно деактивирането на автоматичното прегенериране на кеша. Това решение е „официално от разработчиците“.

Using WooCommerce Subscriptions? Having troubles with the resource intensive report cache updates?

https://github.com/woocommerce/woocommerce-subscriptions-disable-report-cache-updates

Това решение ще спре автоматичното прегенериране на кеша, но самата заявка ще се изпълни, когато клиентът посети раздел Доклади и избере репорт за периода 1 година.

След като предоставихме решението на клиента и той активира този плъгин, SQL заявките за кеш статистиката на WooCommerce Subscriptions не са се стартирали повече.

Промяна на SQL заявката за генериране на докладите

Промяна и оптимизиране на самата SQL заявка, в случай че е възможна, би могло да се направи от разработчиците на плъгина WooCommerce Subscriptions.

Разработчиците знаят и работят в посока подобряване на изживяването при работата с плъгина и отстраняването на този получаващ се проблем. Това, което те споделят в официалната си документация, е че подобрението би било възможно след като WooCommerce и Subscriptions вече използват свои таблици в базата данни.

These queries will be rewritten once WooCommerce and Subscriptions move to custom database tables to avoid having issues until having much larger databases.

docs.woocommerce.com

Докато това не се осъществи, при подобен проблем, деактивирането на автоматичното и постоянно прегенериране на кеша е решението.

Изводът от този случай

Когато ситуацията налага бързо прилагане на решение, в случая сайтът трябва да заработи коректно колкото се може по-скоро, мерките често са за смекчаване или отстраняване на симптомите на проблема. Това може да означава повишаване на ресурсите на сървъра, когато става въпрос за хостинг услугата. По този начин сайтът започва да работи коректно веднага. А през това време може да се извършат други задълбочени проверки, извън сървъра и хостинга.

Каквото може на момента да се направи с възможностите от страна на сървъра и хостинга, се прави, за да може сайтът да заработи коректно по-скоро.

Допълнителните проверки, по времето на които сайтът вече работи коректно, извършени извън сървъра и хостинга, ни позволяват не просто да лекуваме симптомите на проблема, но да отстраним основната причина за него.

Много е удовлетворяващо и зареждащо да направиш един клиент доволен. А това, че не го приемаш за клиент, а за приятел, на когото имаш възможността да помогнеш в нужда, е отговорът на въпроса защо правим това, което правим. 

🤝SH (ShakingHands) Поздрави от колегите в Техническа поддръжка, работили по случая.