Дурпал кретинизмы ИЛИ в каких случаях может тормозить формирование ноды с большим количеством комментариев.

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

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

Дано.
Intel(R) Xeon(R) E5310 с 8 гигабайтами оперативной памяти mysql оптимизирован таким образом чтобы вся индексная база была в оперативной памяти.

Сайт с активным комьюнити которое очень много комментирует.
(на тестовой платформе 520 000 комментариев)

Комментарии формируются с order COMMENT_ORDER_NEWEST_FIRST и mode отличным от COMMENT_MODE_FLAT_COLLAPSED СOMMENT_MODE_FLAT_EXPANDED

Выполнения запроса для ноды в которой 21,465 комментариев, длится 0.5 секунды, что чрезвычайно много учитывая конфигурацию сервера, и естественно его нагруженность.

Причина кроется в этом участке кода:

<?phpif ($order == COMMENT_ORDER_NEWEST_FIRST) {
                if ($mode == COMMENT_MODE_FLAT_COLLAPSED || $mode == COMMENT_MODE_FLAT_EXPANDED) {
                    $query .= ' ORDER BY c.cid DESC';
                } else {
                    $query .= ' ORDER BY c.thread DESC';
                }
            } else if ($order == COMMENT_ORDER_OLDEST_FIRST) {
                if ($mode == COMMENT_MODE_FLAT_COLLAPSED || $mode == COMMENT_MODE_FLAT_EXPANDED) {
                    $query .= ' ORDER BY c.cid';
                } else {

                    /*
                     * * See comment above. Analysis learns that this doesn't cost
                     * * too much. It scales much much better than having the whole
                     * * comment structure.
                     */

                    $query .= ' ORDER BY SUBSTRING(c.thread, 1, (LENGTH(c.thread) - 1))';
                }
            }?>

а именно в $query .= ' ORDER BY c.thread DESC';

обратившись к структуре таблицы comments мы видим что даже банального индекса для thread нет. В результате чего это приводит к
mysql отбирает ВСЕ комментарии для этой ноды, создает временную таблицу, которая если не помещается в пределы выделенной памяти под временные таблицы создается на диск. Там осуществляется сортировка. Ну дальше логика работы понятна.

Чтобы избежать таких ресурсозатрат достаточно добавить вот такой составной индекс в таблицу comments
ALTER TABLE `comments` ADD INDEX ( `nid` , `thread` ) ;

Что в результате дает скорость выполнения в вышеозначенных условиях разным 0.002 секунды. Согласитесь неплохо да?

Почему нужно делать именно составной индекс - отсавлю в качестве домашнего задания для любознательных.

Комментарии

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

"Dеmimurych" wrote:
Выполнения запроса для ноды в которой 21,465 комментариев, длится 0.5 секунды

Но согласитесь, в реальных условиях в это слабо верится. Почему не 50.000?

Аватар пользователя Dеmimurych Dеmimurych 15 ноября 2010 в 22:12

"<a href="mailto:volocuga@drupal.org">volocuga@drupal.org</a>" wrote:
Но согласитесь, в реальных условиях в это слабо верится. Почему не 50.000?

потому что взяты именно реальные условия.

тестовая платформа содержит точную копию базы с продакшена.

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

Ну и что ?
А на всеми любимом движке Vbulletin так вообще темы кастрируют - закрывают и создают клоны, чтобы не тормозило. И вроде бы, все довольны ? При таком количестве комментариев у вас элементарно на InnoDB будут запросы тормозить из-за пейджера.
Я бы назвал это экстримальными условиями. Для таких случаев и предусмотрены администраторы, которые могут ручками добавить любуй индекс и оптимизацию.

Аватар пользователя Dеmimurych Dеmimurych 16 ноября 2010 в 0:50

"Crea" wrote:
Ну и что ?
А на всеми любимом движке Vbulletin так вообще темы кастрируют - закрывают и создают клоны, чтобы не тормозило. И вроде бы, все довольны ? При таком количестве комментариев у вас элементарно на InnoDB будут запросы тормозить из-за пейджера.
Я бы назвал это экстримальными условиями. Для таких случаев и предусмотрены администраторы, которые могут ручками добавить любуй индекс и оптимизацию.

вы внимательно прочитали то что я написал?
либо я вас не понял либо нет.

время выполнения запроса среднее равняется 2 тысячным секунды. То есть приблизительно столько сколько нужно для извлечения того же результата из кеша.

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

Аватар пользователя Crea Crea 16 ноября 2010 в 10:24

Dеmimurych wrote:

вы внимательно прочитали то что я написал?
либо я вас не понял либо нет.

Не поняли. Я утверждаю, что 20000 комментариев у 1 ноды - экстремальные, нетипичные условия. Соотв-но кретинизмом тут и не пахнет.

Аватар пользователя Dеmimurych Dеmimurych 16 ноября 2010 в 0:52

"RxB" wrote:
Знатный оптимизатор, правда он за premature optimization

иронизируешь или тролишь?
0.5 секунды на запрос от одного клиента это катастрофа для сервера.

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

А не подскажете как от тупой таксономии избавиться?
т.е. листалку улучшить?

у меня в slow.log не попадают запросы как у Вас, минимальный порог 1 секунда. Вот что попадает:

# TIME: 101114  6:02:25
# USER@host: drupal[drupal] @ localhost []
# Query_time: 1.449663  Lock_time: 0.000055 Rows_sent: 60  Rows_examined: 352945
SET TIMESTAMP=1289696545;
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 (4,1,5,3,2) GROUP BY td.tid, td.vid, td.name, td.description HAVING COUNT(*) > 0 ORDER BY COUNT DESC LIMIT 0, 60;

# TIME: 101110 23:43:27
# USER@host: drupal[drupal] @ localhost []
# Query_time: 1.309537  Lock_time: 0.000058 Rows_sent: 10  Rows_examined: 124503
SET TIMESTAMP=1289414607;
SELECT DISTINCT(n.nid), n.sticky, n.title, n.created FROM node n  INNER JOIN term_node tn0 ON n.vid = tn0.vid WHERE n.status = 1  AND tn0.tid IN (6) ORDER BY n.sticky DESC, n.created DESC LIMIT 80, 10;

Первый я так понимаю, это статистика какая-то, по крону запускается?
А второй листалка по термину таксономии, не знаю что с ней делать.

Вот explain:

id      select_type     TABLE   TYPE    possible_keys   KEY     key_len         REF     ROWS    Extra
1       SIMPLE  tn0     REF     PRIMARY,vid     PRIMARY         4       const   42233   USING INDEX; USING TEMPORARY; USING filesort
1       SIMPLE  n       eq_ref  vid,node_status_type    vid     4       uralpress.tn0.vid       1       USING WHERE

Что, по аналогии с воблой рубрики все время менять?

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

"Crea" wrote:
Не поняли. Я утверждаю, что 20000 комментариев у 1 ноды - экстремальные, нетипичные условия. Соотв-но кретинизмом тут и не пахнет.

Это действительно нетипично. Если бы было типично, у Вас было бы 500к / 20к = 25 нод.
Если же поделить 500к / (количество нод), то получится наверняка в 1к раз меньше (20 комментов, которые влазят на одну страницу), чем 20к.