On Tuesday, July 28, 2020 at 4:04:26 PM UTC-7 Mary (Cloud Platform Support) wrote:
Hello Andrew,This is due to when enabling PITR it is necessary to archive the Write Ahead Logs (WAL)[1]. The archiving is performed automatically, which by default is every 5 minutes regardless of activity on the database.Similar to binlogging in MySQL, we will only keep the last 7 days of archived WAL logs at any given time.
On Thursday, July 23, 2020 at 8:55:03 AM UTC-4, Andrew Smith wrote:Hello!We have set up a recent Cloud SQL instance running PostgreSQL 12 and point-in-time recovery turned on. We picked the smallest instance size and gave it 10GiB of storage. This instance has sat virtually idle with maybe less than 1k reads/writes over the past 10 days.Today, I noticed in the console that the instance is currently using over 31GiB of storage. It has ticked up at a rate of several GiB/day and appears to be bouncing off of a ceiling of 32GiB currently.When I log in with psql, the sum of the sizes of tables for our database is less than 1MiB total:agency=> \d+List of relationsSchema | Name | Type | Owner | Size | Description--------+-------------------+-------+-------+------------+-------------public | ***************** | table | ***** | 8192 bytes |public | ***************** | table | ***** | 8192 bytes |public | ***************** | table | ***** | 16 kB |public | ***************** | table | ***** | 8192 bytes |public | ***************** | table | ***** | 8192 bytes |public | ***************** | table | ***** | 8192 bytes |public | ***************** | table | ***** | 16 kB |(7 rows)PostgreSQL's own internal metadata is light too:postgres=> \dS+List of relationsSchema | Name | Type | Owner | Size | Description------------+---------------------------------+-------+---------------+------------+-------------pg_catalog | pg_aggregate | table | cloudsqladmin | 56 kB |pg_catalog | pg_am | table | cloudsqladmin | 40 kB |pg_catalog | pg_amop | table | cloudsqladmin | 80 kB |pg_catalog | pg_amproc | table | cloudsqladmin | 56 kB |pg_catalog | pg_attrdef | table | cloudsqladmin | 8192 bytes |pg_catalog | pg_attribute | table | cloudsqladmin | 464 kB |pg_catalog | pg_auth_members | table | cloudsqladmin | 40 kB |pg_catalog | pg_authid | table | cloudsqladmin | 48 kB |pg_catalog | pg_available_extension_versions | view | cloudsqladmin | 0 bytes |pg_catalog | pg_available_extensions | view | cloudsqladmin | 0 bytes |pg_catalog | pg_cast | table | cloudsqladmin | 48 kB |pg_catalog | pg_class | table | cloudsqladmin | 136 kB |pg_catalog | pg_collation | table | cloudsqladmin | 384 kB |pg_catalog | pg_config | view | cloudsqladmin | 0 bytes |pg_catalog | pg_constraint | table | cloudsqladmin | 48 kB |pg_catalog | pg_conversion | table | cloudsqladmin | 56 kB |pg_catalog | pg_cursors | view | cloudsqladmin | 0 bytes |pg_catalog | pg_database | table | cloudsqladmin | 48 kB |pg_catalog | pg_db_role_setting | table | cloudsqladmin | 8192 bytes |pg_catalog | pg_default_acl | table | cloudsqladmin | 8192 bytes |pg_catalog | pg_depend | table | cloudsqladmin | 480 kB |pg_catalog | pg_description | table | cloudsqladmin | 368 kB |pg_catalog | pg_enum | table | cloudsqladmin | 0 bytes |pg_catalog | pg_event_trigger | table | cloudsqladmin | 8192 bytes |pg_catalog | pg_extension | table | cloudsqladmin | 48 kB |pg_catalog | pg_file_settings | view | cloudsqladmin | 0 bytes |pg_catalog | pg_foreign_data_wrapper | table | cloudsqladmin | 8192 bytes |pg_catalog | pg_foreign_server | table | cloudsqladmin | 8192 bytes |pg_catalog | pg_foreign_table | table | cloudsqladmin | 8192 bytes |pg_catalog | pg_group | view | cloudsqladmin | 0 bytes |pg_catalog | pg_hba_file_rules | view | cloudsqladmin | 0 bytes |pg_catalog | pg_index | table | cloudsqladmin | 64 kB |pg_catalog | pg_indexes | view | cloudsqladmin | 0 bytes |pg_catalog | pg_inherits | table | cloudsqladmin | 0 bytes |pg_catalog | pg_init_privs | table | cloudsqladmin | 56 kB |pg_catalog | pg_language | table | cloudsqladmin | 48 kB |pg_catalog | pg_largeobject | table | cloudsqladmin | 0 bytes |pg_catalog | pg_largeobject_metadata | table | cloudsqladmin | 0 bytes |pg_catalog | pg_locks | view | cloudsqladmin | 0 bytes |pg_catalog | pg_matviews | view | cloudsqladmin | 0 bytes |pg_catalog | pg_namespace | table | cloudsqladmin | 48 kB |pg_catalog | pg_opclass | table | cloudsqladmin | 48 kB |pg_catalog | pg_operator | table | cloudsqladmin | 144 kB |pg_catalog | pg_opfamily | table | cloudsqladmin | 48 kB |pg_catalog | pg_partitioned_table | table | cloudsqladmin | 8192 bytes |pg_catalog | pg_pltemplate | table | cloudsqladmin | 48 kB |pg_catalog | pg_policies | view | cloudsqladmin | 0 bytes |pg_catalog | pg_policy | table | cloudsqladmin | 8192 bytes |pg_catalog | pg_prepared_statements | view | cloudsqladmin | 0 bytes |pg_catalog | pg_prepared_xacts | view | cloudsqladmin | 0 bytes |pg_catalog | pg_proc | table | cloudsqladmin | 672 kB |pg_catalog | pg_publication | table | cloudsqladmin | 0 bytes |pg_catalog | pg_publication_rel | table | cloudsqladmin | 0 bytes |pg_catalog | pg_publication_tables | view | cloudsqladmin | 0 bytes |pg_catalog | pg_range | table | cloudsqladmin | 40 kB |pg_catalog | pg_replication_origin | table | cloudsqladmin | 8192 bytes |pg_catalog | pg_replication_origin_status | view | cloudsqladmin | 0 bytes |pg_catalog | pg_replication_slots | view | cloudsqladmin | 0 bytes |pg_catalog | pg_rewrite | table | cloudsqladmin | 632 kB |pg_catalog | pg_roles | view | cloudsqladmin | 0 bytes |pg_catalog | pg_rules | view | cloudsqladmin | 0 bytes |pg_catalog | pg_seclabel | table | cloudsqladmin | 8192 bytes |pg_catalog | pg_seclabels | view | cloudsqladmin | 0 bytes |pg_catalog | pg_sequence | table | cloudsqladmin | 0 bytes |pg_catalog | pg_sequences | view | cloudsqladmin | 0 bytes |pg_catalog | pg_settings | view | cloudsqladmin | 0 bytes |pg_catalog | pg_shadow | view | cloudsqladmin | 0 bytes |pg_catalog | pg_shdepend | table | cloudsqladmin | 40 kB |pg_catalog | pg_shdescription | table | cloudsqladmin | 48 kB |pg_catalog | pg_shseclabel | table | cloudsqladmin | 8192 bytes |pg_catalog | pg_stat_activity | view | cloudsqladmin | 0 bytes |pg_catalog | pg_stat_all_indexes | view | cloudsqladmin | 0 bytes |pg_catalog | pg_stat_all_tables | view | cloudsqladmin | 0 bytes |pg_catalog | pg_stat_archiver | view | cloudsqladmin | 0 bytes |pg_catalog | pg_stat_bgwriter | view | cloudsqladmin | 0 bytes |pg_catalog | pg_stat_database | view | cloudsqladmin | 0 bytes |pg_catalog | pg_stat_database_conflicts | view | cloudsqladmin | 0 bytes |pg_catalog | pg_stat_gssapi | view | cloudsqladmin | 0 bytes |pg_catalog | pg_stat_progress_cluster | view | cloudsqladmin | 0 bytes |pg_catalog | pg_stat_progress_create_index | view | cloudsqladmin | 0 bytes |pg_catalog | pg_stat_progress_vacuum | view | cloudsqladmin | 0 bytes |pg_catalog | pg_stat_replication | view | cloudsqladmin | 0 bytes |pg_catalog | pg_stat_ssl | view | cloudsqladmin | 0 bytes |pg_catalog | pg_stat_subscription | view | cloudsqladmin | 0 bytes |pg_catalog | pg_stat_sys_indexes | view | cloudsqladmin | 0 bytes |pg_catalog | pg_stat_sys_tables | view | cloudsqladmin | 0 bytes |pg_catalog | pg_stat_user_functions | view | cloudsqladmin | 0 bytes |pg_catalog | pg_stat_user_indexes | view | cloudsqladmin | 0 bytes |pg_catalog | pg_stat_user_tables | view | cloudsqladmin | 0 bytes |pg_catalog | pg_stat_wal_receiver | view | cloudsqladmin | 0 bytes |pg_catalog | pg_stat_xact_all_tables | view | cloudsqladmin | 0 bytes |pg_catalog | pg_stat_xact_sys_tables | view | cloudsqladmin | 0 bytes |pg_catalog | pg_stat_xact_user_functions | view | cloudsqladmin | 0 bytes |pg_catalog | pg_stat_xact_user_tables | view | cloudsqladmin | 0 bytes |pg_catalog | pg_statio_all_indexes | view | cloudsqladmin | 0 bytes |pg_catalog | pg_statio_all_sequences | view | cloudsqladmin | 0 bytes |pg_catalog | pg_statio_all_tables | view | cloudsqladmin | 0 bytes |pg_catalog | pg_statio_sys_indexes | view | cloudsqladmin | 0 bytes |pg_catalog | pg_statio_sys_sequences | view | cloudsqladmin | 0 bytes |pg_catalog | pg_statio_sys_tables | view | cloudsqladmin | 0 bytes |pg_catalog | pg_statio_user_indexes | view | cloudsqladmin | 0 bytes |pg_catalog | pg_statio_user_sequences | view | cloudsqladmin | 0 bytes |pg_catalog | pg_statio_user_tables | view | cloudsqladmin | 0 bytes |pg_catalog | pg_statistic | table | cloudsqladmin | 256 kB |pg_catalog | pg_statistic_ext | table | cloudsqladmin | 8192 bytes |pg_catalog | pg_statistic_ext_data | table | cloudsqladmin | 8192 bytes |pg_catalog | pg_stats | view | cloudsqladmin | 0 bytes |pg_catalog | pg_stats_ext | view | cloudsqladmin | 0 bytes |pg_catalog | pg_subscription | table | cloudsqladmin | 8192 bytes |pg_catalog | pg_subscription_rel | table | cloudsqladmin | 0 bytes |pg_catalog | pg_tables | view | cloudsqladmin | 0 bytes |pg_catalog | pg_tablespace | table | cloudsqladmin | 48 kB |pg_catalog | pg_timezone_abbrevs | view | cloudsqladmin | 0 bytes |pg_catalog | pg_timezone_names | view | cloudsqladmin | 0 bytes |pg_catalog | pg_transform | table | cloudsqladmin | 0 bytes |pg_catalog | pg_trigger | table | cloudsqladmin | 8192 bytes |pg_catalog | pg_ts_config | table | cloudsqladmin | 40 kB |pg_catalog | pg_ts_config_map | table | cloudsqladmin | 56 kB |pg_catalog | pg_ts_dict | table | cloudsqladmin | 48 kB |pg_catalog | pg_ts_parser | table | cloudsqladmin | 40 kB |pg_catalog | pg_ts_template | table | cloudsqladmin | 40 kB |pg_catalog | pg_type | table | cloudsqladmin | 120 kB |pg_catalog | pg_user | view | cloudsqladmin | 0 bytes |pg_catalog | pg_user_mapping | table | cloudsqladmin | 8192 bytes |pg_catalog | pg_user_mappings | view | cloudsqladmin | 0 bytes |pg_catalog | pg_views | view | cloudsqladmin | 0 bytes |(126 rows)So where is this storage growth coming from? It isn't like we are doing big inserts and deletes either. Is this some undeclared overhead of using point-in-time recovery? We haven't noticed this with other Cloud SQL instances. Any insights would be appreciated. Nothing on the point-in-time recovery documentation page or FAQ talked about this.Thanks!Andrew
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/9c771c8e-7176-4a9a-b81e-3f5618119ac1n%40googlegroups.com.
No comments:
Post a Comment