Hi Discourse Community!
Recently I have been trying to improve the performance of my Discourse installation and clean up a little since my site has been growing exponentially lately.
I have identified two issues, searched about them here but I don’t see a clear answer for some specifics, so I hope that this doesn’t represent much of a bother to anyone.
The first issue I have is the DB Size, which is pretty big. I’m running a 4GB Mem/80GB Disk instance on Digital Ocean and the DB is choking the disk size already. I do believe that eventually I will have to move it (if so, how?) but maybe I’m doing something wrong, following Sam’s Help I exported the following to make it easier:
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
(on the other hand, the /var/discourse/shared/standalone/postgres_data/base folder is over 47GB)
Can it be reduced somehow or is it logical given the size of some topics? (more details below).
The other issue maaaay be related, I believe, which is that I keep getting the famous " Due to extreme load, this is temporarily being shown to everyone as a logged out user would see it" I read on a post that increasing Unicorn Workers may be an option (never touched that, honestly havent found how to do it or how viable it is without endangering the whole installation). As a note we do have quite some topics with more than 10k answers, so that may be a correlation between DB Size and Performance issues? (Throwing it out, not sure).
I wonder if there is some way of optimizing it (I found this one), either on the very same instance or recurring to something like a HA installation (which I don’t know if it is supported), but I do believe that I’m jumping the gun in my ignorance. I do appreciate any help I could get.
As a note: I was using a swap file when the instance was smaller, I do believe it is disabled now (is there a way of verifying this?).
Thanks and sorry for the n00bness.
، ونحن نخطط حاليًا لطرق للتعامل معها، ربما حتى في الإصدار الحالي 2.5، مع الإشارة إلى 
وتذكّر أن هذا يأتي بعد العمل الرائع الذي قام به 
