Здравствуйте!

Напишу сюда результат анализа лога mysql за примерно пол-месяца работы сайта на друпале.
О том, как нехорошо кешировать данные в mysql, я уже писал. Там рассматривалась работа CacheRouter при выборе хранилища в БД – то есть стандартное кеширование друпала.
Теперь был проведен анализ после перевода кеша в memcache.

Вот некоторая статистика:
#memcached-tool localhost stats
bytes_read 1593512968640 = 1.5 Терабайта
bytes_written 5899978353864 = почти 6 Терабайт

mysql:
Трафик			ø в час
Принято 	81 ГБ 	217 МБ
Отправлено 	464 ГБ 	1,234 МБ
Всего	 	545 ГБ 	1,451 МБ

Анализ бинарного лога

Вот список запросов, которые изменяют информацию:

1. CAPTCHA – стоит графическая, при вводе комментариев: 36% запросов

Модуль: CAPTCHA
Число запросов: 696.77k (12.12%)
Запрос: UPDATE captcha_sessions SET token='S' WHERE csid=N

Модуль: CAPTCHA
Число запросов: 696.11k (12.11%)
Запрос: UPDATE captcha_sessions SET timestamp=N, solution='S' WHERE csid=N

Модуль: CAPTCHA
Число запросов: 695.75k (12.10%)
Запрос: INSERT INTO captcha_sessions (uid, sid, ip_address, timestamp, form_id, solution, STATUS, attempts) VALUES (N, 'S', 'S', N, 'S', 'S', N, N)

2. Стандартный модуль статистики, считает количество посещений ноды: 28% запросов
Модуль: Statistics
Число запросов: 1.60M (27.84%)
Запрос: UPDATE node_counter SET daycount = daycount + 1, totalcount = totalcount + 1, timestamp = N WHERE nid = N

3. Ядро (сессии) – записывает и обновляет сессии: 18% запросов
Модуль: Core
Число запросов: 835.58k (14.53%)
Запрос: UPDATE sessions SET uid = N, cache = N, hostname = 'S', session = 'S', timestamp = N WHERE sid = 'S'

Модуль: Core
Число запросов: 213.20k (3.71%)
Запрос: INSERT INTO sessions (sid, uid, cache, hostname, session, timestamp) VALUES ('S', N, N, 'S', 'S', N)

4. Database logging (core) – записывает события в базу данных: 10% запросов

Модуль: Database logging
Число запросов: 597.70k (10.39%)
Запрос: INSERT INTO watchdog (uid, type, message, VARIABLES, severity, link, location, referer, hostname, timestamp) VALUES (N, 'S', 'S', 'S', N, 'S', 'S', 'S', 'S', N)

Модуль:
Число запросов:
Запрос:  

Итого, 4 модуля делают 36% + 28% + 18% + 10% = 92% !!! запросов на изменение информации

Что может быть предложено:

1. Капча – сейчас она показывается всем, кто смотрит ноду, т.к. стоит модуль ajax_comments и комментарии вместе с формой расположены на той же странице. Т.к. оставляет комментарии намного меньше людей, чем те, кто смотрят ноду, то если форму для комментариев разместить на отдельной странице – нагрузка снизится почти до нуля.
Либо альтернативный вариант помещать ее каким-то образом в кеш – такое возможно?

2. Модуль статистики – вещь полезная, например, для определения популярного контента за сегодня / за все время по числу просмотров. Сейчас ВСЯ таблица node_counter у меня весит 5-6 мегабайт!!! есть ли какой-то способ (модуль), чтобы поместить эту таблицу также в кеш, и, допустим, сбрасывать в mysql по крону? При таком подходе нагрузка также станет 0.

Также было замечено, что daycount имеет некорректное значение – по моему представлению, если я запускаю крон раз в час, то обновление должно происходить например в 00:00, когда вызывается крон – там же реализовано обновление через переменную, это неправильно, поскольку вначале переменная не установлена в 00:00, а потом, т.к. крон запускается в одно и то же время, statistics_day_timestamp может “убежать” на 1 сек, и следующий запрос уже будет выполнен в 01:00

3. Сессии – я уменьшил время сессии в default.settings.php с 23 дней до 5 дней, сейчас размер этой таблицы 10-15 мегабайт. Хотелось бы также ее в кеш.

4. Лог – как изменится производительность системы, если весь лог отправлять в syslog? По идее, любая серверная программа отправляет в syslog – web сервер, mysql, почта и т.д. и тормозами от этого не страдает – думаю, надо настроить src и destination для друпала, пусть складывается все туда.

Итого: при выполнении этих действий нагрузка на сервер mysql по части изменений (update, insert) снизится более, чем в 10 раз!!!

Анализ медленных (или использующих много строк) запросов

Также был проанализирован slow_query.log на предмет отлавливания нехороших запросов.
Предпосылкой к этому стали следующие данные:

Количество запросов, на чтение строки, основанных на ее позиции. Большое значение переменной может быть обусловлено частым выполнением запросов использующих сортировку результата, выполнением большого числа запросов требующих полного сканирования таблиц, наличием объединений не использующих индексы надлежащим образом.

Handler_read_rnd 150 M

Количество запросов на чтение следующей строки из файла данных. Данное значение будет высоким, при частом сканировании таблиц. Обычно это означает, что таблицы не проиндексированы надлежащим образом или запросы не используют преимущества индексов.

Handler_read_rnd_next 1,976 M

Количество временных таблиц, автоматически созданных сервером на диске, во время выполнения SQL-выражений. Если значение Created_tmp_disk_tables велико, следует увеличить значение переменной tmp_table_size, чтобы временные таблицы располагались в памяти, а не на жестком диске.

Created_tmp_disk_tables 2,458 k

Количество запросов-объединений, выполненных без использования индексов. Если значение переменной не равно 0, рекомендуется проверить индексы таблиц.

Select_full_join 8,620

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

Sort_merge_passes 502

А также

Количество запросов, добавленных в кеш запросов.

Qcache_inserts 33 M

Количество запросов, удаленных из кеша для освобождения памяти под кеширование новых запросов. Эта информация может помочь при настройке размера кеша запросов. Кеш запросов использует стратегию LRU (дольше всего не использующиеся страницы заменяются новыми) при принятии решения об удаления запроса из кеша.

Qcache_lowmem_prunes 20 M

Очевидно, что делается скан таблиц с большим количеством строк

Вот анализ:

1. views – материал: новость, опубликован, рубрика = 10, 3 последних

Количество запросов: 14199 (3 последних) + 1485 (10 последних) + 268 (4 последних) + 153 (20 последних – таксономия)
Общее время: 16760 + 1906 + 227 + 185 = 19078 секунд, в среднем: 1.18 / 1.28 / 0.84 / 1.21
Количество строк: 13674 – 126552 /

SET timestamp=1292480648;
SELECT node.nid AS nid,
   node.created AS node_created,
   node.title AS node_title
 FROM node node
 INNER JOIN term_node term_node ON node.vid = term_node.vid
 WHERE (node.type IN ('news')) AND (node.STATUS <> 0) AND (term_node.tid = 10)
   ORDER BY node_created DESC
 LIMIT 0, 3;

2. Облако тегов по словарю, модуль tagadelic

Количество запросов: 1053
Общее время: 936, в среднем: 0.89
Количество строк: 15154 – 15326
Запрос:

SELECT COUNT(*) AS count, td.tid, td.vid, td.name, td.description FROM term_data td INNER JOIN term_node tn ON td.tid = tn.tid INNER JOIN node n ON tn.vid = n.vid WHERE td.vid IN (5) GROUP BY td.tid, td.vid, td.name, td.description HAVING COUNT(*) > 0 ORDER BY count DESC LIMIT 0, 12;

3. xmlsitemap – я так понимаю, это запрос по крону на добавление новых нод в сайтмап – ПОЛНЫЙ СКАН

Количество запросов: 368
Общее время: 436, в среднем: 1.18
Количество строк: 171575 – 172750
Запрос:

SELECT n.nid FROM node n LEFT JOIN xmlsitemap x ON x.type = 'node' AND n.nid = x.id WHERE x.id IS NULL AND n.type IN ('news','reviews') ORDER BY n.nid DESC LIMIT 0, 100;

4. Снова xmlsitemap – выдача “подкарты” сайта – это даже хуже, чем полный скан

Количество запросов: 459
Общее время: 326, в среднем: 0.71
Количество строк: 134500 – 344905
Запрос:

SELECT x.loc, x.lastmod, x.changefreq, x.changecount, x.priority, x.LANGUAGE FROM xmlsitemap x WHERE x.access = 1 AND x.STATUS = 1 ORDER BY x.LANGUAGE, x.loc LIMIT 172500, 500;

5. url_alias – снова xmlsitemap ?

Количество запросов: 388
Общее время: 210, в среднем: 0.54
Количество строк: 171762 – 345745
Запрос:

SELECT src, dst FROM url_alias WHERE LANGUAGE = 'ru' ORDER BY pid;

Остальные запросы не такие затратные, или их мало.

Итого, самый “трудный” запрос является запрос views – материал: новость, опубликован, рубрика = 10, 3 последних – самая большая нагрузка – 19078 секунд – это больше 5 часов из 15 дней полной работы

Следующим запросом идет облако тегов по словарю – 936 секунд, а словарь еще до конца не заполнен.

И последние запросы относятся к xmlsitemap.

Получается, самые дорогие запросы делает модуль views при выборке из словаря с сортировкой по дате, модуль tagadelic при составлении облака тегов и xmlsitemap.

Самым “болезненным” запросом для моего сайта получается все-таки views, о том, что с ним можно сделать хотел бы услышать Ваши комментарии, с остальным вроде более-менее понятно, с tagadelic и xmlsitemap я пока готов смириться.

Источник: http://drupal.ru/node/54345

• drupal + mysql – самые “дорогие” модули
• Увеличения скорости загрузки странички: Content Delivery Network (CDN) и распараллеливание загрузки данных
• Тормозит трекер ? Есть решение – Views Tracker !
• Резервныe ApacheSolr серверa для Drupal’a
• Оптимизация Drupal (кеширование и все остальное)