Улучшение производительности инстанса (Megatopics, размер базы данных и экстремальная нагрузка)

Поддержка Самостоятельное хостинг
1

Привет, сообщество Discourse!

В последнее время я пытаюсь улучшить производительность моей установки Discourse и немного навести порядок, так как мой сайт в последнее время растет экспоненциально.

Я выявил две проблемы, поискал информацию о них здесь, но не нашел четких ответов по некоторым деталям, поэтому надеюсь, что это не создаст лишних неудобств.

Первая проблема — размер базы данных, который довольно велик. Я использую экземпляр на Digital Ocean с 4 ГБ оперативной памяти и 80 ГБ дискового пространства, и база данных уже занимает почти всё место на диске. Я полагаю, что в конечном итоге мне придется её перенести (если да, то как?), но возможно, я что-то делаю не так. Следуя инструкции Сэма, я экспортировал следующие данные для наглядности:


table_name                  | row_estimate | table_size | index_size | total_size
--------------------------------------------------------------------------
post_timings                | 155307152    | 8004 MB    | 16 GB      | 24 GB
posts                       | 2257277      | 2432 MB    | 4810 MB    | 7242 MB
post_search_data            | 2279749      | 1992 MB    | 769 MB     | 2761 MB
user_actions                | 6549714      | 570 MB     | 2189 MB    | 2759 MB
topic_views                 | 8843734      | 444 MB     | 1494 MB    | 1937 MB
user_visits                 | 569317       | 33 MB      | 1892 MB    | 1925 MB
notifications               | 1482664      | 465 MB     | 914 MB     | 1379 MB
topic_users                 | 4821392      | 500 MB     | 449 MB     | 949 MB
top_topics                  | 47437        | 28 MB      | 773 MB     | 802 MB
user_auth_token_logs        | 1690555      | 515 MB     | 133 MB     | 648 MB
post_actions                | 1610428      | 145 MB     | 367 MB     | 512 MB
post_revisions              | 113187       | 396 MB     | 9312 kB    | 406 MB
topic_links                 | 605525       | 135 MB     | 254 MB     | 389 MB
topics                      | 56970        | 104 MB     | 227 MB     | 331 MB
web_hook_events             | 107760       | 295 MB     | 10 MB      | 306 MB
post_stats                  | 1955191      | 151 MB     | 97 MB      | 248 MB
directory_items             | 13026        | 1312 kB    | 157 MB     | 158 MB
incoming_links              | 812553       | 73 MB      | 82 MB      | 155 MB
post_replies                | 1111686      | 69 MB      | 71 MB      | 139 MB
topic_link_clicks           | 806821       | 54 MB      | 43 MB      | 97 MB
draft_sequences             | 654989       | 36 MB      | 48 MB      | 84 MB
topic_search_data           | 54056        | 40 MB      | 24 MB      | 65 MB
stylesheet_cache            | 901          | 57 MB      | 200 kB     | 57 MB
user_profile_views          | 204427       | 15 MB      | 30 MB      | 46 MB
quoted_posts                | 223337       | 18 MB      | 23 MB      | 41 MB
poll_votes                  | 142349       | 13 MB      | 20 MB      | 32 MB
users                       | 2211         | 2360 kB    | 29 MB      | 32 MB
given_daily_likes           | 252806       | 12 MB      | 16 MB      | 28 MB
scheduler_stats             | 115081       | 17 MB      | 4736 kB    | 21 MB
user_histories              | 30331        | 8848 kB    | 10040 kB   | 18 MB
reviewables                 | 16263        | 10032 kB   | 8344 kB    | 18 MB
optimized_images            | 34463        | 8088 kB    | 10 MB      | 18 MB
post_uploads                | 73123        | 4104 kB    | 13 MB      | 17 MB
uploads                     | 18897        | 5088 kB    | 9080 kB    | 14 MB
email_logs                  | 23224        | 4024 kB    | 9960 kB    | 14 MB
post_custom_fields          | 11043        | 3192 kB    | 9328 kB    | 12 MB
search_logs                 | 68429        | 7480 kB    | 4776 kB    | 12 MB
user_badges                 | 37176        | 2920 kB    | 5008 kB    | 7928 kB
unsubscribe_keys            | 14820        | 3352 kB    | 4480 kB    | 7832 kB
user_auth_tokens            | 5328         | 2536 kB    | 3608 kB    | 6144 kB
reviewable_scores           | 14681        | 3144 kB    | 2768 kB    | 5912 kB
reviewable_histories        | 31482        | 2976 kB    | 2616 kB    | 5592 kB
poll_options                | 20886        | 2560 kB    | 2552 kB    | 5112 kB
skipped_email_logs          | 11164        | 2528 kB    | 2328 kB    | 4856 kB
topic_allowed_users         | 21933        | 1424 kB    | 1872 kB    | 3296 kB
user_uploads                | 19038        | 1040 kB    | 1688 kB    | 2728 kB
user_stats                  | 2211         | 1888 kB    | 160 kB     | 2048 kB
drafts                      | 1324         | 1424 kB    | 368 kB     | 1792 kB
user_custom_fields          | 7467         | 688 kB     | 1064 kB    | 1752 kB
application_requests        | 11244        | 792 kB     | 528 kB     | 1320 kB
topic_tags                  | 10257        | 696 kB     | 528 kB     | 1224 kB
user_associated_accounts    | 670          | 1032 kB    | 184 kB     | 1216 kB
user_profiles               | 2211         | 424 kB     | 720 kB     | 1144 kB
email_tokens                | 3439         | 480 kB     | 528 kB     | 1008 kB
polls                       | 4030         | 520 kB     | 408 kB     | 928 kB
user_search_data            | 2215         | 376 kB     | 520 kB     | 896 kB
topic_custom_fields         | 2738         | 280 kB     | 568 kB     | 848 kB
group_users                 | 4364         | 344 kB     | 448 kB     | 792 kB
plugin_store_rows           | 2090         | 488 kB     | 296 kB     | 784 kB
incoming_referers           | 3779         | 352 kB     | 424 kB     | 776 kB
user_avatars                | 2210         | 208 kB     | 560 kB     | 768 kB
web_crawler_requests        | 1389         | 264 kB     | 440 kB     | 704 kB
group_histories             | 2210         | 272 kB     | 416 kB     | 688 kB
user_emails                 | 2218         | 224 kB     | 376 kB     | 600 kB
user_archived_messages      | 3019         | 240 kB     | 232 kB     | 472 kB
user_options                | 2218         | 384 kB     | 72 kB      | 456 kB
topic_allowed_groups        | 2098         | 128 kB     | 216 kB     | 344 kB
schema_migration_details    | 994          | 192 kB     | 88 kB      | 280 kB
group_mentions              | 933          | 104 kB     | 152 kB     | 256 kB
categories                  | 23           | 96 kB      | 112 kB     | 208 kB
google_user_infos           | 314          | 136 kB     | 72 kB      | 208 kB
theme_fields                | 24           | 168 kB     | 32 kB      | 200 kB
category_users              | 569          | 64 kB      | 136 kB     | 200 kB
javascript_caches           | 8            | 112 kB     | 64 kB      | 176 kB
incoming_domains            | 701          | 80 kB      | 96 kB      | 176 kB
groups                      | 51           | 120 kB     | 48 kB      | 168 kB
category_tag_stats          | 173          | 48 kB      | 104 kB     | 152 kB
tag_search_data             | 109          | 64 kB      | 72 kB      | 136 kB
schema_migrations           | 994          | 88 kB      | 48 kB      | 136 kB
topic_embeds                | 218          | 80 kB      | 56 kB      | 136 kB
badges                      | 51           | 80 kB      | 48 kB      | 128 kB
translation_overrides       | 170          | 72 kB      | 48 kB      | 120 kB
invites                     | 21           | 56 kB      | 64 kB      | 120 kB
user_api_keys               | 4            | 48 kB      | 64 kB      | 112 kB
category_search_data        | 20           | 48 kB      | 64 kB      | 112 kB
tags                        | 109          | 56 kB      | 48 kB      | 104 kB
screened_ip_addresses       | 9            | 48 kB      | 48 kB      | 96 kB
user_second_factors         | 26           | 48 kB      | 48 kB      | 96 kB
oauth2_user_infos           | 4            | 48 kB      | 48 kB      | 96 kB
site_settings               | 165          | 64 kB      | 32 kB      | 96 kB
api_keys                    | 1            | 48 kB      | 48 kB      | 96 kB
category_featured_topics    | 123          | 48 kB      | 48 kB      | 96 kB
screened_emails             | 4            | 48 kB      | 48 kB      | 96 kB
screened_urls               | 1            | 48 kB      | 48 kB      | 96 kB
topic_groups                | 245          | 56 kB      | 32 kB      | 88 kB
muted_users                 | 103          | 40 kB      | 48 kB      | 88 kB
tag_group_permissions       | 11           | 40 kB      | 48 kB      | 88 kB
tag_users                   | 8            | 40 kB      | 48 kB      | 88 kB
child_themes                | 6            | 40 kB      | 48 kB      | 88 kB
category_tags               | 9            | 40 kB      | 48 kB      | 88 kB
topic_timers                | 15           | 40 kB      | 48 kB      | 88 kB
ignored_users               | 10           | 40 kB      | 48 kB      | 88 kB
group_requests              | 0            | 24 kB      | 64 kB      | 88 kB
user_warnings               | 7            | 40 kB      | 48 kB      | 88 kB
email_change_requests       | 64           | 56 kB      | 32 kB      | 88 kB
web_hooks                   | 1            | 72 kB      | 16 kB      | 88 kB
custom_emojis               | 132          | 56 kB      | 32 kB      | 88 kB
color_scheme_colors         | 110          | 56 kB      | 32 kB      | 88 kB
tag_group_memberships       | 192          | 48 kB      | 32 kB      | 80 kB
category_custom_fields      | 17           | 48 kB      | 32 kB      | 80 kB
themes                      | 10           | 48 kB      | 32 kB      | 80 kB
badge_types                 | 3            | 48 kB      | 32 kB      | 80 kB
onceoff_logs                | 39           | 48 kB      | 32 kB      | 80 kB
category_tag_groups         | 8            | 40 kB      | 32 kB      | 72 kB
group_archived_messages     | 56           | 40 kB      | 32 kB      | 72 kB
category_groups             | 3            | 40 kB      | 32 kB      | 72 kB
push_subscriptions          | 12           | 48 kB      | 16 kB      | 64 kB
tag_groups                  | 10           | 48 kB      | 16 kB      | 64 kB
theme_settings              | 6            | 48 kB      | 16 kB      | 64 kB
ar_internal_metadata        | 1            | 48 kB      | 16 kB      | 64 kB
backup_metadata             | 6            | 48 kB      | 16 kB      | 64 kB
user_fields                 | 9            | 48 kB      | 16 kB      | 64 kB
remote_themes               | 7            | 48 kB      | 16 kB      | 64 kB
badge_groupings             | 5            | 48 kB      | 16 kB      | 64 kB
web_hook_event_types        | 10           | 48 kB      | 16 kB      | 64 kB
user_security_keys          | 0            | 8192 bytes | 56 kB      | 64 kB
color_schemes               | 11           | 48 kB      | 16 kB      | 64 kB
permalinks                  | 0            | 24 kB      | 32 kB      | 56 kB
incoming_emails             | 0            | 8192 bytes | 48 kB      | 56 kB
post_action_types           | 8            | 40 kB      | 16 kB      | 56 kB
web_hook_event_types_hooks  | 1            | 40 kB      | 16 kB      | 56 kB
watched_words               | 0            | 24 kB      | 32 kB      | 56 kB
user_exports                | 0            | 24 kB      | 16 kB      | 40 kB
github_user_infos           | 0            | 8192 bytes | 24 kB      | 32 kB
backup_draft_posts          | 0            | 8192 bytes | 24 kB      | 32 kB
categories_web_hooks        | 0            | 16 kB      | 16 kB      | 32 kB
theme_translation_overrides | 0            | 8192 bytes | 24 kB      | 32 kB
single_sign_on_records      | 0            | 8192 bytes | 24 kB      | 32 kB
post_reply_keys             | 0            | 0 bytes    | 24 kB      | 24 kB
backup_draft_topics         | 0            | 0 bytes    | 24 kB      | 24 kB
user_open_ids               | 0            | 8192 bytes | 16 kB      | 24 kB
post_details                | 0            | 8192 bytes | 16 kB      | 24 kB
message_bus                 | 0            | 8192 bytes | 16 kB      | 24 kB
anonymous_users             | 0            | 0 bytes    | 24 kB      | 24 kB
group_custom_fields         | 0            | 8192 bytes | 16 kB      | 24 kB
topic_invites               | 0            | 0 bytes    | 24 kB      | 24 kB
shared_drafts               | 0            | 0 bytes    | 24 kB      | 24 kB
instagram_user_infos        | 0            | 8192 bytes | 8192 bytes | 16 kB
reviewable_claimed_topics   | 0            | 0 bytes    | 16 kB      | 16 kB
user_field_options          | 0            | 8192 bytes | 8192 bytes | 16 kB
embeddable_hosts            | 0            | 8192 bytes | 8192 bytes | 16 kB
invited_groups              | 0            | 0 bytes    | 8192 bytes | 8192 bytes
developers                  | 0            | 0 bytes    | 8192 bytes | 8192 bytes
tags_web_hooks              | 0            | 0 bytes    | 8192 bytes | 8192 bytes
groups_web_hooks            | 0            | 0 bytes    | 8192 bytes | 8192 bytes
badge_posts                 | 0            | 0 bytes    | 0 bytes    | 0 bytes

(Кроме того, папка /var/discourse/shared/standalone/postgres_data/base занимает более 47 ГБ)

Можно ли как-то уменьшить этот размер, или он логичен исходя из размера некоторых тем? (Подробнее ниже).

Другая проблема, которая, возможно, связана с этим, заключается в том, что я постоянно получаю известное сообщение: «Из-за экстремальной нагрузки это временно отображается всем пользователям так, как если бы они были неавторизованы». Я читал в одном посте, что увеличение числа воркеров Unicorn может быть вариантом (честно говоря, я никогда не трогал эту настройку, так как не знал, как это сделать или насколько это жизнеспособно без риска для всей установки). Как заметка: у нас есть довольно много тем с более чем 10 тысячами ответов, так что, возможно, есть корреляция между размером базы данных и проблемами производительности? (Высказываю предположение, не уверен).

Интересует, есть ли способ оптимизировать это (я нашел вот это), либо на том же самом экземпляре, либо с использованием чего-то вроде HA-установки (не знаю, поддерживается ли это), но я полагаю, что в своем невежестве я забегая вперед. Буду признателен за любую помощь.

Как заметка: когда экземпляр был меньше, я использовал файл подкачки, сейчас, как мне кажется, он отключен (есть ли способ это проверить?).

Спасибо и извините за мою неопытность.

2

Таблица post_timings — это монстр. Есть ли способ сократить или «сократить» или «суммировать» эту таблицу, ничего не сломав, @sam?

3

Мини-обновление: Попытался настроить параметры Unicorn Runners, но не совсем понимаю, есть ли связь «ядро–воркер» или это что-то вроде общего процессора, например CPU % x Second. Есть ли здесь какие-то лучшие практики?

Также читал о PostgreSQL, чтобы понять, как справиться с объёмом данных. Но даже при наличии резервной копии не знаю, как приложение отреагирует в долгосрочной перспективе, если я просто начну удалять лишнее.

Быстрая правка: Файл подкачки удалён. Сейчас анализирую, как поступить с более чем 10 000 тем. Пользуясь случаем, спрошу: насколько они опасны для общей картины? (Предполагаю, что какая-то опасность есть, учитывая рекомендации, но если есть что-то ещё, хотел бы об этом узнать, если это возможно).

4

У меня тоже есть темы с более чем 10 тысячами ответов, и я тоже время от времени вижу это сообщение, когда сайт очень загружен.

5

Я настоятельно рекомендую не переопределять настройки по умолчанию в Discourse, которые автоматически закрывают темы с более чем 10 тысячами ответов. У нас есть веская причина включать эту настройку по умолчанию. :scream:

Кроме того, проблема с огромной таблицей post_timings находится в поле нашего зрения :satellite_antenna:, и мы сейчас brainstormим способы её решения, возможно, даже в текущем релизе 2.5 cc @sam @eviltrout

6

Мне кажется, здесь требуется тройное подтверждение, а кнопка «OK» должна иметь подпись «Я согласен на то, чтобы происходили плохие вещи».

Кто-нибудь добивается реальной работы с мега-темами?

7

У нас около 80 мега-тем, которые обновляются постоянно (в самой большой из них 128 тысяч сообщений).

Иногда возникают проблемы с ошибкой 502 (возможно, это связано с этим, но я не уверен). Всё работает стабильно с тех пор, как мы изменили параметр db_shared_buffers, установив его значение выше размера базы данных.

8

Это крайне плохая идея, и вам следует закрыть эти темы, заменив их более мелкими тематическими или сезонными. См.

9

У меня есть сайт с несколькими мега-темами, содержащими более 100 тысяч постов. Я говорил им, что это ошибка, но они всё равно хотели их. Теперь они жалуются на проблемы с производительностью. Надеюсь, скоро смогу вернуть значение к стандартному пределу в 10 тысяч.

Это, впрочем, заставило меня изучить некоторые аспекты оптимизации баз данных, так что это плюс. :slight_smile:

10

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

Представьте тему, посвящённую Трампу в целом, или теме, посвящённой США в целом. На нашем форуме есть тема об одном из бывших президентов нашего клуба, одна — о действующем президенте, и отдельные темы для каждого игрока футбольной команды. Вы понимаете, о чём я. Другие темы легко разделить по сезонам, но не эти. Конечно, это не невозможно, но довольно непрактично.

11

Представьте категорию о Трампе в целом или о Соединённых Штатах в целом.

Никто не будет читать подряд более 10 000 постов в теме. Просто не будет. В какой-то момент можно сделать паузу и начать заново. Я подозреваю, что такие темы больше похожи на чат, чем на обсуждение, и что мало кто читает то, что происходило вчера, не говоря уже о прошлой неделе, прошлом месяце или прошлом году.

12

Хорошо, но мой ответ на это

480×270 1.1 MB

… вы сознательно выбираете причинить себе значительную боль ради «причин».

13

Вот запрос Data Explorer, имитирующий запрос для получения страницы постов:

-- [params]
-- int :topic_id = 107216
-- int :offset = 10000

SELECT "posts"."id" FROM "posts" 
WHERE ("posts"."deleted_at" IS NULL) 
AND "posts"."topic_id" = :topic_id
AND "posts"."post_type" IN (1,2,3) ORDER BY "posts"."sort_order" ASC LIMIT 20 
OFFSET :offset

Вот нормальная тема:

Limit  (cost=1911.35..1915.38 rows=1 width=8)

Вот мега-тема:

Limit  (cost=37475.88..37550.83 rows=20 width=8)
14

:+1:

Мы вам верим, просто наши пользователи привыкли к такому подходу, и потребуется немного усилий, чтобы изменить это… но это вполне реально.

Кстати, я только что прочитал пост @codinghorror по ссылке Natural breakpoints or "chapters" for long topics? - #53 by codinghorror и подумал, что такой оглавление, но только для тем, стало бы отличным решением. Это позволило бы собирать и отображать последовательность тем при просмотре и ответе только в одной из них.

15

Не поймите неправильно: я проделал эту работу не для того, чтобы убедить вас, а чтобы убедить моего клиента с более чем 120 тысячами тем на форуме, который жалуется на производительность!

Я надеюсь, что это поможет и вам. Удачи! :wink:

:clinking_glasses:

16

Очень полезно! Спасибо за分享!

У нас есть конкретные планы на ближайшее время по оптимизации таблицы post_timings, но мега-темы будут источником значительных проблем в течение многих, многих лет.

:warning: И помните, что это после того, как @tgxworld проделал отличную работу около года назад, чтобы уменьшить общий объем работы, которую мы выполняем по мега-темам, по сути переключив их в «режим низкого энергопотребления», чтобы они причиняли меньше вреда.

Но не обманывайте себя: мега-темы всё ещё бьют очень сильно. :boom::boxing_glove:

17

Следуя вашим рекомендациям и пока я пытаюсь разобраться с остальными вопросами (понимая, что вы работаете над таблицой post_timings), я перехожу к восстановлению настроек по умолчанию для размера тем (извиняясь за свою некомпетентность, почему бы и нет).

С учётом этого у меня есть несколько вопросов, на которые, надеюсь, вы сможете ответить:

  1. После восстановления настройки эти темы будут закрыты. Будут созданы новые. Однако опасно ли существование этих старых больших тем для сайта в целом? Имеется в виду, нужно ли мне «разделить» их X раз на более мелкие темы, или, пока они неактивны, всё в порядке?

  2. Я видел дискуссию, которую @Paracelsus вёл относительно удобства для пользователей. В связи с этим хочу спросить: возможно ли добавить настройку для «автоматического продолжения тем, закрытых из-за максимального размера»? То есть, когда мега-тема автоматически закрывается, можно ли автоматически создать новую тему с тем же автором, тем же заголовком (возможно, с номером в конце названия темы?) и единственным содержимым — ссылкой на предыдущую тему? (При этом добавить ссылку на новую тему в закрытую).

Я понимаю, что это довольно запутанно и, возможно, не нужно всем (поэтому я предлагаю это как опциональную настройку), но для сайтов с высоким трафиком и значимыми темами это может быть полезно, полагаю. Что вы думаете?

18

Это всё равно что спрашивать: «Стоит ли поощрять людей курить, автоматически покупая им новую пачку, когда та заканчивается?» :wink:

Нет, если только они не получают много трафика, чего не должно быть, если они закрыты, я так полагаю? Посмотрим, как это будет работать, но остановка утечки активных мега-тем — это шаг ноль, так что вы на правильном пути :+1:

19

В продолжение темы… Мы сейчас пытаемся справиться с этими огромными темами, разбивая их на части по 10 тысяч постов. Однако система, похоже, не готова к нашей стратегии :sweat_smile:

При попытке выбрать сразу не менее 8000 постов и переместить их в новую тему я получаю ошибку “502 Bad Gateway” (пока не пробовал с меньшим количеством постов). Есть ли способ увеличить пропускную способность или другой/более эффективный способ решить эту задачу? @codinghorror @pfaffman

20

Решение — выполнить это в консоли Rails, но я не помню сразу, как именно это сделать.

Что-то вроде:

old_topic=1
new_topic=2
Posts.where(topic_id: old_topic).where("post_number > 10000 and post_number < 20000").update_all(topic_id: new_topic, post_number=...)

но… мне кажется, что, возможно, потребуется поместить это в цикл, чтобы создать новые номера постов. Обязательно протестируйте решение на тестовом сайте. Если что-то пойдёт не так, будут серьёзные проблемы.

Если этого недостаточно, чтобы понять, как это сделать, то, скорее всего, вам придётся написать в Marketplace. Но, полагаю, никто всё равно не будет читать старые посты, так что лучше просто закрыть их и оставить большими, а возможно, даже удалить или исключить из индексации, если вы не считаете, что они имеют какую-то SEO-ценность.