21 ноября 2010 в 12:03
Здравствуйте!
проанализировал работу сайта за ночь всплыли вот такие неприятности:
в slow_log попало почти 400 запросов со временем запроса, большем 1 секунды.
натравил на лог парсер
результат в конце страницы.
Получается, 3 запроса выполняются очень долго и часто, есть ли способ их оптимизировать?
1. запрос - стандартный views taxonomy_term для страницы taxonomy/term/%
среднее время запроса - 2.5 секунды
количество строк для анализа - 170к - 250к
По моему, не самый удачный запрос select * from node where vid in (...) .
И как к этому запросу добавить SQL_NO_CACHE ?
Самый тупой запрос - листалка, пока даже не знаю, как это можно оптимизировать.
2. запрос - как я понял подсчет числа нод определенного термина - это pager считает их?
среднее время запроса - 2.4 секунды
Получается, при плохом раскладе страница может генериться 2.5 (заголовки нод) + 2.4 (отрисовка пейджера) = 5 секунд??
ну, при хорошем раскладе, конечно этот запрос попадет в query_cache, только вот он засирается быстро другими запросами.
нет ли какого модуля в друпале, который кеширует статистику нода-термин в базу, пусть даже по крону, все равно?
3. запрос - views вида content/%year/%month/%day - вывод нод за дату.
среднее время запроса - 1.3 секунды.
делается проход на 340к нод - жесть.
честно говоря, мне больше нравится запрос вида
created between UNIX_TIMESTAMP('2003-08-18') and UNIX_TIMESTAMP('2003-08-19')
чем построенный длинный-длинный where, поскольку created проиндексирован, и запрос будет явно быстрее.
как этот запрос самому переписать? - т.е. построить 2 переменные из трех аргументов и подставить в фильтр?
также этот views у меня не подставляет default аргумент (выбрал provide, php code - там вывожу текущую дату - не работает).
Может можно сделать страницу, которая принимает аргументы и вызывает этот views, а также применяет фильтр, который я хочу? Если да, то как?
вот сами запросы:
### Total TIME: 533.277238, Average TIME: 2.51545866981132
### Taking 1.001591 TO 14.255525 seconds TO complete
### ROWS analyzed 169469 - 255055
SET TIMESTAMP=XXX;
SELECT node.nid AS nid,
node.sticky AS node_sticky,
node.created AS node_created
FROM node node
WHERE (node.status <> XXX OR (node.uid = XXX AND XXX <> XXX) OR XXX = XXX) AND (node.vid IN (
SELECT tn.vid FROM term_node tn
WHERE tn.tid = XXX
))
ORDER BY node_sticky DESC, node_created DESC
LIMIT XXX, XXX;
SET TIMESTAMP=1290324813;
SELECT node.nid AS nid,
node.sticky AS node_sticky,
node.created AS node_created
FROM node node
WHERE (node.status <> 0 OR (node.uid = 0 AND 0 <> 0) OR 0 = 1) AND (node.vid IN (
SELECT tn.vid FROM term_node tn
WHERE tn.tid = 114
))
ORDER BY node_sticky DESC, node_created DESC
LIMIT 60, 20;
### 120 Queries
### Total TIME: 287.128251, Average TIME: 2.392735425
### Taking 1.006031 TO 11.951811 seconds TO complete
### ROWS analyzed 0 - 0
SET TIMESTAMP=XXX;
SELECT COUNT(*) FROM (SELECT node.nid AS nid
FROM node node
WHERE (node.status <> XXX OR (node.uid = XXX AND XXX <> XXX) OR XXX = XXX) AND (node.vid IN (
SELECT tn.vid FROM term_node tn
WHERE tn.tid = XXX
))
) count_alias;
SET TIMESTAMP=1290325552;
SELECT COUNT(*) FROM (SELECT node.nid AS nid
FROM node node
WHERE (node.status <> 0 OR (node.uid = 0 AND 0 <> 0) OR 0 = 1) AND (node.vid IN (
SELECT tn.vid FROM term_node tn
WHERE tn.tid = 4
))
) count_alias;
### 65 Queries
### Total TIME: 88.628773, Average TIME: 1.36351958461538
### Taking 1.009571 TO 4.847449 seconds TO complete
### ROWS analyzed 337974 - 338256
SET TIMESTAMP=XXX;
SELECT node.nid AS nid,
term_data.name AS term_data_name,
term_data.vid AS term_data_vid,
term_data.tid AS term_data_tid,
node.created AS node_created,
node.title AS node_title,
node_data_field_teaser.field_teaser_value AS node_data_field_teaser_field_teaser_value,
node.type AS node_type,
node.vid AS node_vid
FROM node node
LEFT JOIN term_node term_node ON node.vid = term_node.vid
LEFT JOIN term_data term_data ON term_node.tid = term_data.tid
LEFT JOIN content_type_news node_data_field_teaser ON node.vid = node_data_field_teaser.vid
WHERE (node.type IN ('XXX')) AND (node.status <> XXX) AND (term_data.vid IN ('XXX')) AND (EXTRACT(YEAR FROM((FROM_UNIXTIME(node.created) + INTERVAL XXX SECOND))) = 'XXX') AND (EXTRACT(MONTH FROM((FROM_UNIXTIME(node.created) + INTERVAL XXX SECOND))) = 'XXX') AND (EXTRACT(DAY FROM((FROM_UNIXTIME(node.created) + INTERVAL XXX SECOND))) = 'XXX')
ORDER BY term_data_tid ASC, node_created DESC;
SET TIMESTAMP=1290323703;
SELECT node.nid AS nid,
term_data.name AS term_data_name,
term_data.vid AS term_data_vid,
term_data.tid AS term_data_tid,
node.created AS node_created,
node.title AS node_title,
node_data_field_teaser.field_teaser_value AS node_data_field_teaser_field_teaser_value,
node.type AS node_type,
node.vid AS node_vid
FROM node node
LEFT JOIN term_node term_node ON node.vid = term_node.vid
LEFT JOIN term_data term_data ON term_node.tid = term_data.tid
LEFT JOIN content_type_news node_data_field_teaser ON node.vid = node_data_field_teaser.vid
WHERE (node.type IN ('news')) AND (node.status <> 0) AND (term_data.vid IN ('1')) AND (EXTRACT(YEAR FROM((FROM_UNIXTIME(node.created) + INTERVAL 18000 SECOND))) = '2003') AND (EXTRACT(MONTH FROM((FROM_UNIXTIME(node.created) + INTERVAL 18000 SECOND))) = '08') AND (EXTRACT(DAY FROM((FROM_UNIXTIME(node.created) + INTERVAL 18000 SECOND))) = '18')
ORDER BY term_data_tid ASC, node_created DESC;
Комментарии
http://upi.ru -- хороший сайт.
Как я понял у вас выделенный айпишник, возможно VDS. Такие данные по скорости работы скриптов удручают, здесь mysql явно не при чём.
В тоже время возможно стоит написать отдельный модуль, чтоб обрабатывать подобные запросы не через views, а обращаясь к базе напрямик.
В том то и дело, что не vds, а дедик.
это проблема друпала - в части генерации турого запроса / неправильной схемы данных, потому что 340к строк прожевать быстро ну никак не получится.
сервер очень быстрый, на вдс такой запрос будет примерно в 20-30 раз медленнее, вот это и удручает.
1-й запрос уж слишком типичный - он делается на всех сайтах друпала, поэтому его оптимизация по идее полезна всему сообществу.
Вот хочу придумать что с этим можно сделать.
Ну и подскажите с SQL_NO_CACHE - куда его вставить можно?
П.С. выполнил первый запрос с SQL_NO_CACHE - запрос занял 0.7425 сек.
выполнил тот же запрос убрав "node_sticky DESC," - 0.2195 сек.
в три раза реальный прирост производительности - по идее меня такая логика устраивает.
теперь буду думать как обойти where in (...) - left join?
EXPLAIN запустите
Цифры у вас фиговые даже для медленного запроса. Может попробовать mysql памяти побольше дать ?
Памяти и так дал нормально пока - хотелось бы решить вопрос не железом, а логикой, т.к. этот же запрос на обычном десктопе загнется, так как у меня 2 x Xeon E5620.
да дело не в этом
вот запрос, который делает абсолютно тоже самое, НО!
запрос занял 0.0028 сек.
node.sticky AS node_sticky,
node.created AS node_created
FROM term_node tn
LEFT JOIN node node
ON tn.vid=node.vid
WHERE node.STATUS <> 0 AND
tn.tid = 114
ORDER BY node.created DESC
LIMIT 80, 20;
вот explain для оригинального запроса:
1 PRIMARY node range node_status_type node_status_type 4 NULL 171344 Using where; Using filesort
2 DEPENDENT SUBQUERY tn eq_ref PRIMARY,vid PRIMARY 8 const,func 1 Using index
Смысл в том, что в оригинальном запросе сначала делается подзапрос, который выбирает все vid которые относятся к термину, а потом проверяет каждую ноду, содержится ли она в массиве подзапроса.
тогда как left join с перевернутым порядком таблиц делает это сполтыка.
Вопрос - как это сделать в друпале??
П.С. - проблема явно не в софте / настройках / железе и т.д. - в друпале НЕПРАВИЛЬНАЯ ЛОГИКА ЗАПРОСА.
проверьте тот же запрос на своем сайте (только термин другой поставьте) - и убедитесь сами.
3 запроса - 1 медленный, 2-й в 3 раза быстрей, 3-й - мгновенный.
Views за вас оптимизировать запрос не сможет, т.к. у каждого пользователя - свои запросы и свои индексы.
Views - всего лишь инструмент, с помощью которого, как говорят англоговорящие, можно всегда выстрелить себе в ногу.
EXPLAIN вам говорит, что нужно просмотреть 170000 строк, используя ваш WHERE. Если вы добавите индекс, соотв-й критериям WHERE (и ORDER BY), запрос может точно так же стать моментальным.
А вообще запрос можно поправить через hook_views_query_alter().
Проблема именно в node.vid IN (...) , индексы я смотрел - в node они все есть, прям нечего добавить, вот чтобы было понятно:
node
vid - primary
created - index
title
term_node
vid -> ref to node vid (not unique)
tid - index
primary index=vid+tid
1-й запрос оригинал
select title,created from node where vid in (select vid from term_node where tid=4) order by created desc limit 0,20
2-й запрос оптимизирован
select title,created from term_node left join node on term_node.vid=node.vid where tid=4 order by created desc limit 0,20
объяснять надо, почему 2-й запрос ЗНАЧИТЕЛЬНО быстрее?
Он может быть быстрее. Я с этим не спорю. В модуле Views есть понятие основной таблицы. Запрос строится всегда начиная с основной, к которой потом присоединяется все остальное. Ваш вариант запроса - с точки зрения Views вывод терминов, а не нод, т.к. у него node не является основной таблицей.
Понятное дело, что запросы, составленные руками, чаще всего будут быстрее, чем созданные генератором запросов.
Вот пример, человек привёл без views http://xandeadx.ru/blog/drupal/233
Суть в том что Друпал сам по себе инструмент очень хороший и множество задач можно решить штатными модулями, но когда встаёт вопрос оптимизации для высоко нагруженных проектов, без написания собственного, заточенного под конкретную задачу функционала, заменяющего универсальные модули не обойтись.
Заменять Views не надо. Достаточно его доработать напильником. А лучше пропатчить и поделиться патчем со всеми остальными.
а right join там не предусмотрен?
за спасибо, посмотрю.
2-й запрос тоже будет быстрым так как он считает сколько выдал первый еще надо бы вот это
(EXTRACT(YEAR FROM((FROM_UNIXTIME(node.created) + INTERVAL 18000 SECOND))) = '2003') AND (EXTRACT(MONTH FROM((FROM_UNIXTIME(node.created) + INTERVAL 18000 SECOND))) = '08') AND (EXTRACT(DAY FROM((FROM_UNIXTIME(node.created) + INTERVAL 18000 SECOND))) = '18')
заменить на created between UNIX_TIMESTAMP('2003-08-18') and UNIX_TIMESTAMP('2003-08-19')
чтобы индекс нормально работал, а не так по уродски было.
еще не понял зачем он ГМТ+5 туда прибавил.
в коде надпись - "получаем список всех материалов" -
ключевая надпись.
Вашу бы энергию, да в мирное русло. Почему, вместо того, чтобы здесь жаловаться на Друпал, не написать патч к модулю Views и не выложить на drupal.org ?
А то прямо Синкоре уподобляетесь
листалка кстати для многих актуальна. такой запрос "order by created desc limit 0,20" на каждом сайте есть. И в стандартном виде он насилует мускул, как только может, а потом ходят слухи что в друпале запросов много
патч не патч, но я напишу решение, которое сделал.
еще один кандидат - xmlsitemap
### Total time: 629.01497, Average time: 1.06975335034014
### Taking 1.000279 to 1.938043 seconds to complete
### Rows analyzed 185672 - 341872
SET timestamp=1290333420;
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 170800, 100;
делает выборку по access,status на которых нет даже индекса - в принципе, добавлением индексов лечится.
Индекс который у вас используется:
'node_status_type' => array('status', 'type', 'nid'),
Где тут vid ?
Создайте нормальный индекс и проверьте. И ваши AND XXX <> XXX) OR XXX = XXX) тоже влияют
Напоминаю, что mysql может использовать только 1 индекс в запросе (на каждую таблицу).
DEPENDENT SUBQUERY tn eq_ref PRIMARY,vid
vid вот здесь
на друпал.орг пишут что дело в глубине
чем отличается
«Таксономия: ID Термина (с глубиной)
Таксономия: Глубина ID термина
»
от «Таксономия: ID термина» в этом преставлении?
без глубины однако быстро работает, нормально
В принципе вопрос решается без всяких патчей - если в дефолт view удалить аргументы и добавить «Таксономия: ID термина»
спасибо за то, что помогли с решением.
Выложите запросы и EXPLAIN от каждого. Сразу будет видно.
Можно попробовать сделать новый индекс из 'node_status_type' => array('status', 'type', 'nid'), с добавлением node.created и node.sticky
Вам мейнтейнер Views уже рекомендовал добавлять индексы http://drupal.org/node/874470
Большое Вам спасибо за правильные вопросы. многое прояснилось - и насчет индексов тоже. буду и дальше сюда писать запросы, которые медленные.
С архивом тоже вопрос решил - вместо длинного запроса, где created проверяется 3 раза сделал так:
1. создал view archive, который выдает список нод в нужном формате. - группировка по рубрике, время, анонс.
2. Создал панель-страницу, с аргументом %mydate. Другого способа, как получить аргумент в пути - не нашел - либо views, либо panels.
3. В этой панели сделал обработку аргумента - формат даты, и даты + 1 day / либо вывод текущей даты, если аргумент неправильный / его нет.
4. Создание view archive в панели.
5. Добавил через views api фильтр к view archive вида created between min and max.
6. вывод view archive.
В результате стало работать как надо - запрос стал быстрым. Сделал вроде без всяких хаков, по api.
До этого рассматривал модуль archive - но он сделан не на базе views, через views сделать также не получилось нормально, т.к. если передавать аргументы, то запрос получается тупой.