Mar 14 2015

Пересчет сообщений пользователей на форуме XenForo: удачные и неудачные варианты

Category: XenForoFractalizeR @ 9:45 pm

Столкнулся с задачей пересчета сообщений у пользователей на форуме и решил поделиться с сообществом исследованиями на тему ее наиболее эффективного решения.

Вариант №1 (аддон sonnbRebuildUserPostCount 1.0.0 20120725).

Этот аддон пересчитывает сообщения пользователей в цикле, для каждого пользователя выполняя запрос

SELECT COUNT(*)
  FROM `xf_post` AS post
  INNER JOIN `xf_thread` AS thread
  ON (post.thread_id = thread.thread_id)
  WHERE post.user_id = $userId AND
  thread.node_id IN (" . XenForo_Application::getDb()->quote(array_keys($nodes)) . ")

Неудачные моменты:

  • Долго работает на больших форумах (на нашем сейчас 700.000+ пользователей). Выполняет количество запросов превышающее количество пользователей на форуме, что достаточно сильно нагружает сервер (нужно ведь дождаться, пока выполнятся эти 700 тысяч запросов)

Вариант №2 от patrikq

Выполнить запрос

UPDATE xf_user AS user
SET message_count = (
  SELECT COUNT(*)
  FROM xf_post AS post
  LEFT JOIN xf_thread AS thread ON (thread.thread_id = post.thread_id)
  WHERE post.user_id = user.user_id
  AND post.message_state = 'visible' AND thread.discussion_state = 'visible'
  GROUP BY post.user_id
);

Неудачные моменты:

  • Неэффективен, поскольку EXPLAIN показывает, что для выполнения такого запроса будут выполняться два подзапроса. Сильно нагружает базу.
  • Не учитывает, что некоторые форумы могут быть настроены так, чтобы не учитывать сообщения пользователей в этих разделах (встроенная возможность движка). Нужно добавить еще JOIN с таблицей xf_forum и условие на count_messages в WHERE.

Поиск альтернативы.

В XenForo существует таблица xf_thread_user_post, в которой закешировано количество сообщений пользователя в каждом треде форума. Запросы по этой таблице будут выполняться быстрее, чем запросы к xf_post поскольку она гораздо меньше по объему (если таблица в память не помещается, InnoDB читает информацию с диска страницами по 16KB даже в том случае, если из всей страницы нужен только 1 байт).

Вариант №3 (ForumHouse):

Выполнить запрос:

Код:
UPDATE xf_user U SET message_count = (
  SELECT
  SUM(post_count) AS user_post_count
  FROM xf_thread_user_post TUP
  INNER JOIN xf_thread T USING (thread_id)
  INNER JOIN xf_forum F USING (node_id)
  WHERE count_messages AND TUP.user_id = U.user_id
  )

Неудачные моменты:

  • EXPLAIN показывает, что будут выполняться три SUBQUERIES, что недостаточно эффективно.

Вариант №4 (ForumHouse):

Выполнить запрос

UPDATE xf_user U
  INNER JOIN (
  SELECT
  SUM(post_count) AS user_post_count,
  TUP.user_id
  FROM xf_thread_user_post TUP
  INNER JOIN xf_thread T USING (thread_id)
  INNER JOIN xf_forum F USING (node_id)
  WHERE count_messages AND T.discussion_state = 'visible'
GROUP BY TUP.user_id) AS PC ON U.user_id = PC.user_id SET message_count = user_post_count

На нащих объемах (700.000+ пользователей, 200.000+ тем и 8.700.000+ сообщений) отрабатывает за 3 секунды. Если посмотреть его EXPLAIN, будет видно, что SUBQUERIES нет. Есть только несколько DERIVED таблиц.

Разница по времени выполнения между вариантом №3 и 4 примерно 10 раз. Так что остановились пока на нем ;)