Прискорюємо сайт. Логування повільних запитів MySQL

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

Один з них, використовує комерційний движок Bitrix, де робота зводиться до повної переробки шаблону сайту (розробникам варто було б для початку ознайомитися з документацією по Bitrix, перш ніж клепати сайт), а також чищення web-сторінок від сміття, яким їх щедро наділив WISIWIG редактор , цього самого Бітрікс.

Движок другого сайту самопісний, і створювався явно якимись двієчниками. При роботі сайту складалося враження, що йому або доводиться перевертати мегатонни інформації, любо у хостера замість серверів стоять калькулятори.

Так що продовжимо тему оптимізації, розпочату в статті Як порахувати час виконання скрипта і кількість запитів до MySQL.

Включаємо лог повільних запитів

Якщо є підозра, що додаток працює повільно через неефективні запитів до бази (в моєму випадку сумніватися і не доводилося), то в першу чергу слід перевірити лог повільних запитів. Саме цей лог допоміг з’ясувати, що необхідно оптимізувати в першу чергу.

І тут нас чекає перша засада – віртуальний хостинг не дозволяє змінювати налаштування MySQL сервера. Для налагодження скриптів доведеться встановити власний web-сервер на віртуальну машину, або скористатися готовими пакетами – LAMP, VertrigoServ, MAMP, Денвер або іншим.

Якщо є впевненість у власних силах і у вас є виділений сервер – можете включити налагодження на ньому, проте оптимізувати скрипти краще все-таки на тестових серверах.

У файлі настройки MySQL сервера (my.cnf) Нам знадобиться задіяти опцію slow_query_log (Старий варіант log_slow_queries). my.cnf може перебувати в різних місцях, в залежності від вашої операційної системи:

; Включаємо лог повільних запитів (шлях можна не вказувати)
slow_query_log = /var/log/mysql/mysql_slow.log
; Час виконання запиту, після якого запит вважається повільним
; (Від 1-10 сек, за замовчуванням 10 секунд)
long_query_time = 5
; Логирование запитів, які не використовують індекси
log-queries-not-using-indexes

Не забудьте перезапустити сервер БД для застосування налаштувань.

Залишилося вдумливо проаналізувати отриманий лог, що за запити ми отримали і спробувати цю справу прискорити. Таким чином я з’ясував основну причину тормознутости сайту – величезна кіль UPDATE запитів до бази даних, котори по суті виявилися не потрібні. Відключивши соотвествующий шматок коду в скрипті, вдалося знизити навантаження на MySQL майже в 1000 разів (ось такий кривий код містився в движку).

Оптимізація налаштувань MySQL сервера

У висновку хочу трохи розповісти про оптимізації на стороні сервера. Перш за все перевірте чи включено кешування запитів (при виконанні запиту SELECT система MySQL “запам’ятовує” сам запит і результат). При повторному відправленні аналогічного запиту на сервер, система поверне відповідь з кешу, замість того що б повторно виконувати даний запит.

Перевіряємо чи включено кешування:

mysql> SHOW VARIABLES LIKE ‘% query_cache%’;
+ —————————— + ———— +
| Variable_name | Value |
+ —————————— + ———— +
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 2147483648 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+ —————————— + ———— +
6 rows in set (0.00 sec)

значення змінної have_query_cache повідомляє про включення кешування.

Мінлива query_cache_type описує який тип кешування використовується: OFF (0) – кешірвоаніе відключено, ON (1) – кешування включено для всіх запитів, за винятком використання операторів SELECT з опцією SQL_NO_CACHE, DEMAND (2) – дозволяє активувати кешування запитів на вимогу, коли використовуються оператори SELECT з опцією SQL_CACHE.

Бажано перевірити значення обсягу пам’яті виділяється сервером під буффер індексів key_buffer_size в my.cnf (рекомендується встановлювати це значення в 15-20% від розміру оперативної пам’яті на сервері).

Всіх, хто цікавиться даною темою, можу порекомендувати дві книжечки, які сам із задоволенням вивчаю, MUST HAVE для всіх web розробників:

Якщо вважаєте статтю корисною, не лінуйтеся ставити лайки і ділитися з друзями.

Ссылка на основную публикацию