drupal + views = медленный mysql

Главные вкладки

Аватар пользователя andribas@drupal.org andribas@drupal.org 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, а также применяет фильтр, который я хочу? Если да, то как?

вот сами запросы:

### 212 Queries
### 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;

Комментарии

Аватар пользователя PavelZ PavelZ 21 ноября 2010 в 12:54

http://upi.ru -- хороший сайт.

Как я понял у вас выделенный айпишник, возможно VDS. Такие данные по скорости работы скриптов удручают, здесь mysql явно не при чём.
В тоже время возможно стоит написать отдельный модуль, чтоб обрабатывать подобные запросы не через views, а обращаясь к базе напрямик.

Аватар пользователя andribas@drupal.org andribas@drupal.org 21 ноября 2010 в 13:25

"PavelZ" wrote:
Как я понял у вас выделенный айпишник, возможно VDS. Такие данные по скорости работы скриптов удручают, здесь mysql явно не при чём.

В том то и дело, что не vds, а дедик.
это проблема друпала - в части генерации турого запроса / неправильной схемы данных, потому что 340к строк прожевать быстро ну никак не получится.
сервер очень быстрый, на вдс такой запрос будет примерно в 20-30 раз медленнее, вот это и удручает.

1-й запрос уж слишком типичный - он делается на всех сайтах друпала, поэтому его оптимизация по идее полезна всему сообществу.
Вот хочу придумать что с этим можно сделать.
Ну и подскажите с SQL_NO_CACHE - куда его вставить можно?

П.С. выполнил первый запрос с SQL_NO_CACHE - запрос занял 0.7425 сек.
выполнил тот же запрос убрав "node_sticky DESC," - 0.2195 сек.

в три раза реальный прирост производительности - по идее меня такая логика устраивает.
теперь буду думать как обойти where in (...) - left join?

Аватар пользователя Crea Crea 21 ноября 2010 в 13:32

EXPLAIN запустите
Цифры у вас фиговые даже для медленного запроса. Может попробовать mysql памяти побольше дать ?

Аватар пользователя andribas@drupal.org andribas@drupal.org 21 ноября 2010 в 13:48

"Crea" wrote:
EXPLAIN запустите
Цифры у вас фиговые даже для медленного запроса. Может попробовать mysql памяти побольше дать ?

Памяти и так дал нормально пока - хотелось бы решить вопрос не железом, а логикой, т.к. этот же запрос на обычном десктопе загнется, так как у меня 2 x Xeon E5620.

да дело не в этом Smile

вот запрос, который делает абсолютно тоже самое, НО!
запрос занял 0.0028 сек.

SELECT SQL_NO_CACHE node.nid AS nid,
   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 для оригинального запроса:

id      select_type     table   type    possible_keys   key     key_len         ref     rows    Extra
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-й - мгновенный.

Аватар пользователя Crea Crea 21 ноября 2010 в 14:21

Views за вас оптимизировать запрос не сможет, т.к. у каждого пользователя - свои запросы и свои индексы.
Views - всего лишь инструмент, с помощью которого, как говорят англоговорящие, можно всегда выстрелить себе в ногу.

EXPLAIN вам говорит, что нужно просмотреть 170000 строк, используя ваш WHERE. Если вы добавите индекс, соотв-й критериям WHERE (и ORDER BY), запрос может точно так же стать моментальным.

А вообще запрос можно поправить через hook_views_query_alter().

Аватар пользователя andribas@drupal.org andribas@drupal.org 21 ноября 2010 в 14:25

Проблема именно в 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-й запрос ЗНАЧИТЕЛЬНО быстрее?

Аватар пользователя Crea Crea 21 ноября 2010 в 14:26

Он может быть быстрее. Я с этим не спорю. В модуле Views есть понятие основной таблицы. Запрос строится всегда начиная с основной, к которой потом присоединяется все остальное. Ваш вариант запроса - с точки зрения Views вывод терминов, а не нод, т.к. у него node не является основной таблицей.
Понятное дело, что запросы, составленные руками, чаще всего будут быстрее, чем созданные генератором запросов.

Аватар пользователя PavelZ PavelZ 21 ноября 2010 в 14:29

Вот пример, человек привёл без views http://xandeadx.ru/blog/drupal/233

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

Аватар пользователя Crea Crea 21 ноября 2010 в 14:34

PavelZ wrote:

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

Заменять Views не надо. Достаточно его доработать напильником. А лучше пропатчить и поделиться патчем со всеми остальными.

Аватар пользователя andribas@drupal.org andribas@drupal.org 21 ноября 2010 в 14:29

"Crea" wrote:
аш вариант запроса - с точки зрения Views вывод терминов, а не нод, т.к. у него node не является основной таблицей.

а right join там не предусмотрен? Smile
за
"Crea" wrote:
hook_views_query_alter()
спасибо, посмотрю.

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 туда прибавил.

Аватар пользователя Crea Crea 21 ноября 2010 в 14:32

Вашу бы энергию, да в мирное русло. Почему, вместо того, чтобы здесь жаловаться на Друпал, не написать патч к модулю Views и не выложить на drupal.org ?
А то прямо Синкоре уподобляетесь Smile

Аватар пользователя andribas@drupal.org andribas@drupal.org 21 ноября 2010 в 14:41

"Crea" wrote:
Почему, вместо того, чтобы здесь жаловаться на Друпал, не написать патч к модулю Views и не выложить на drupal.org ?

листалка кстати для многих актуальна. такой запрос "order by created desc limit 0,20" на каждом сайте есть. И в стандартном виде он насилует мускул, как только может, а потом ходят слухи что в друпале запросов много Smile

патч не патч, но я напишу решение, которое сделал.

еще один кандидат - xmlsitemap

### 588 Queries
### 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 на которых нет даже индекса - в принципе, добавлением индексов лечится.

Аватар пользователя Crea Crea 21 ноября 2010 в 15:15

Индекс который у вас используется:
'node_status_type' => array('status', 'type', 'nid'),
Где тут vid ?
Создайте нормальный индекс и проверьте. И ваши AND XXX <> XXX) OR XXX = XXX) тоже влияют
Напоминаю, что mysql может использовать только 1 индекс в запросе (на каждую таблицу).

Аватар пользователя andribas@drupal.org andribas@drupal.org 21 ноября 2010 в 15:03

"Crea" wrote:
Где тут vid ?

DEPENDENT SUBQUERY tn eq_ref PRIMARY,vid
vid вот здесь
на друпал.орг пишут что дело в глубине
чем отличается
«Таксономия: ID Термина (с глубиной)
Таксономия: Глубина ID термина
»
от «Таксономия: ID термина» в этом преставлении?
без глубины однако быстро работает, нормально

В принципе вопрос решается без всяких патчей - если в дефолт view удалить аргументы и добавить «Таксономия: ID термина»
спасибо за то, что помогли с решением.

Аватар пользователя Crea Crea 21 ноября 2010 в 15:22

Можно попробовать сделать новый индекс из 'node_status_type' => array('status', 'type', 'nid'), с добавлением node.created и node.sticky
Вам мейнтейнер Views уже рекомендовал добавлять индексы http://drupal.org/node/874470

Аватар пользователя andribas@drupal.org andribas@drupal.org 21 ноября 2010 в 19:06

"Crea" wrote:
Crea

Большое Вам спасибо за правильные вопросы. многое прояснилось - и насчет индексов тоже. буду и дальше сюда писать запросы, которые медленные.

"Crea" wrote:
Вам мейнтейнер Views уже рекомендовал добавлять индексы http://drupal.org/node/874470[/quote]
угу он мне и на этот вопрос ответил - http://drupal.org/node/977850
ему тож спасибо Smile

Аватар пользователя andribas@drupal.org andribas@drupal.org 21 ноября 2010 в 21:34

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