La base de données semble inexplicablement volumineuse

Soutien Installation
1

tl;dr : il semble qu’environ 10 Go de données soient rapportés par rake db:stats, mais que postgres_data occupe 18 Go. Est-il probable qu’il y ait 8 Go d’index occupant le reste de l’espace, ou faut-il effectuer un nettoyage ?

Version plus détaillée de la même question

Le répertoire de données PostgreSQL fait 18 Go. Une sauvegarde de la base de données fait environ 5 Go.

var/discourse# du -hs shared/data/postgres_data/
18G     shared/data/postgres_data/

Voici ce que révèle rake db:stats. J’ai tronqué les lignes rapportées en ko (environ 10 Go).

table_name row_estimate size
post_timings 49643076 3011 MB
posts 2289502 990 MB
user_actions 5506773 915 MB
post_search_data 2136003 875 MB
email_logs 2934363 717 MB
topic_views 3498593 507 MB
topic_users 3714046 448 MB
post_stats 1894296 319 MB
notifications 1178136 296 MB
post_replies 1077331 127 MB
topics 146814 107 MB
post_actions 456903 63 MB
draft_sequences 671770 54 MB
post_revisions 74596 51 MB
user_profile_views 465818 49 MB
topic_search_data 124471 46 MB
topic_links 160437 33 MB
scheduler_stats 168878 29 MB
uploads 82100 22 MB
post_custom_fields 35255 22 MB
top_topics 106478 21 MB
unsubscribe_keys 109936 19 MB
optimized_images 67107 18 MB
user_histories 77643 13 MB
topic_link_clicks 124501 12 MB
1 « J'aime »
2

You may have a restore schema with tables, try list those with psql.

3

Thanks. Is this what I’m looking for? I don’t think I see a restore schema, but I’m not quite sure what I’m looking for.

                List of relations
Schema Name Type Owner
public api_keys table discourse
public application_requests table discourse
public ar_internal_metadata table discourse
public badge_groupings table discourse
public badge_types table discourse
public badges table discourse
public categories table discourse
public categories_web_hooks table discourse
public category_custom_fields table discourse
public category_featured_topics table discourse
public category_featured_users table discourse
public category_groups table discourse
public category_search_data table discourse
public category_tag_groups table discourse
public category_tags table discourse
public category_users table discourse
public child_themes table discourse
public color_scheme_colors table discourse
public color_schemes table discourse
public custom_emojis table discourse
public developers table discourse
public directory_items table discourse
public draft_sequences table discourse
public drafts table discourse
public email_change_requests table discourse
public email_logs table discourse
public email_tokens table discourse
public embeddable_hosts table discourse
public facebook_user_infos table discourse
public github_user_infos table discourse
public given_daily_likes table discourse
public google_user_infos table discourse
public group_archived_messages table discourse
public group_custom_fields table discourse
public group_histories table discourse
public group_mentions table discourse
public group_users table discourse
public groups table discourse
public groups_web_hooks table discourse
public incoming_domains table discourse
public incoming_emails table discourse
public incoming_links table discourse
public incoming_referers table discourse
public instagram_user_infos table discourse
public invited_groups table discourse
public invites table discourse
public message_bus table discourse
public muted_users table discourse
public notifications table discourse
public oauth2_user_infos table discourse
public onceoff_logs table discourse
public optimized_images table discourse
public permalinks table discourse
public plugin_store_rows table discourse
public post_action_types table discourse
public post_actions table discourse
public post_custom_fields table discourse
public post_details table discourse
public post_replies table discourse
public post_revisions table discourse
public post_search_data table discourse
public post_stats table discourse
public post_timings table discourse
public post_uploads table discourse
public posts table discourse
public queued_posts table discourse
public quoted_posts table discourse
public remote_themes table discourse
public scheduler_stats table discourse
public schema_migration_details table discourse
public schema_migrations table discourse
public screened_emails table discourse
public screened_ip_addresses table discourse
public screened_urls table discourse
public search_logs table discourse
public single_sign_on_records table discourse
public site_settings table discourse
public stylesheet_cache table discourse
public tag_group_memberships table discourse
public tag_groups table discourse
public tag_search_data table discourse
public tag_users table discourse
public tags table discourse
public theme_fields table discourse
public themes table discourse
public top_topics table discourse
public topic_allowed_groups table discourse
public topic_allowed_users table discourse
public topic_custom_fields table discourse
public topic_embeds table discourse
public topic_invites table discourse
public topic_link_clicks table discourse
public topic_links table discourse
public topic_search_data table discourse
public topic_tags table discourse
public topic_timers table discourse
public topic_users table discourse
public topic_views table discourse
public topics table discourse
public translation_overrides table discourse
public twitter_user_infos table discourse
public unsubscribe_keys table discourse
public uploads table discourse
public user_actions table discourse
public user_api_keys table discourse
public user_archived_messages table discourse
public user_auth_token_logs table discourse
public user_auth_tokens table discourse
public user_avatars table discourse
public user_badges table discourse
public user_custom_fields table discourse
public user_emails table discourse
public user_exports table discourse
public user_field_options table discourse
public user_fields table discourse
public user_histories table discourse
public user_open_ids table discourse
public user_options table discourse
public user_profile_views table discourse
public user_profiles table discourse
public user_search_data table discourse
public user_stats table discourse
public user_visits table discourse
public user_warnings table discourse
public users table discourse
public versions table discourse
public watched_words table discourse
public web_hook_event_types table discourse
public web_hook_event_types_hooks table discourse
public web_hook_events table discourse
public web_hooks table discourse

(131 rows)

1 « J'aime »
4

Is that giant inline table really necessary?

1 « J'aime »
5

La taille de notre table post-timings de Discourse est d’environ 17 Go. Est-ce normal ?

table_name row_estimate table_size index_size total_size
post_timings 120967448 6676 Mo 10 Go 17 Go
posts 3616224 2413 Mo 3219 Mo 5632 Mo
user_actions 15159571 1215 Mo 3699 Mo 4914 Mo
post_search_data 3531969 3756 Mo 1031 Mo 4787 Mo
notifications 2241273 687 Mo 824 Mo 1510 Mo
topic_views 7696258 369 Mo 1110 Mo 1478 Mo
post_actions 3475097 350 Mo 618 Mo 968 Mo
topic_users 3343623 365 Mo 257 Mo 622 Mo
user_auth_token_logs 1123545 410 Mo 110 Mo 520 Mo
post_stats 2779471 354 Mo 155 Mo 509 Mo
incoming_links 3154654 219 Mo 251 Mo 469 Mo
topics 64301 142 Mo 286 Mo 429 Mo
post_replies 2327812 220 Mo 120 Mo 340 Mo
user_visits 289994 17 Mo 229 Mo 246 Mo
stylesheet_cache 4605 204 Mo 840 Ko 205 Mo
top_topics 40202 19 Mo 148 Mo 167 Mo
user_profile_views 593591 61 Mo 97 Mo 158 Mo
post_revisions 102832 87 Mo 8072 Ko 95 Mo
6

Oui, c’est la plus grande table de n’importe quel site Discourse actif, avec l’un de ses index occupant la majeure partie de l’espace.

Nous déploierons cette année une mise à jour de PostgreSQL qui réduira cette occupation de moitié, alors restez à l’écoute :wink:

8 « J'aime »