Point-in-time recovery storage overhead?
691 views
Skip to first unread message
Andrew Smith
Jul 23, 2020, 8:55:03 AM7/23/20
to Google Cloud SQL discuss
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 relations
Schema | 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 relations
Schema | 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
Mary (Cloud Platform Support)
Jul 28, 2020, 7:04:26 PM7/28/20
to Google Cloud SQL discuss
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.
Andrew Smith
Aug 3, 2020, 9:05:45 PM8/3/20
to Google Cloud SQL discuss
Mary, thanks for clarifying! I did not see that reference before. Also, our instance's storage usage dramatically decreased to a far more reasonable level. Coincidence? ;-)
Elliott (Google Cloud Platform Support)
Aug 4, 2020, 8:46:50 PM8/4/20
to Google Cloud SQL discuss
Hello Andrew,
Do you have further issue on this?
Andrew Smith
Aug 5, 2020, 12:34:09 PM8/5/20
to google-cloud...@googlegroups.com
Nope, all seems well now! Thank you!
On Tue, Aug 4, 2020 at 5:46 PM 'Elliott (Google Cloud Platform Support)' via Google Cloud SQL discuss <google-cloud...@googlegroups.com> wrote:
Hello Andrew,Do you have further issue on this?
--
You received this message because you are subscribed to a topic in the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-cloud-sql-discuss/o9s3LIMM2vk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-cloud-sql-d...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/28fcd774-d51f-442b-aa46-ad4926ff3ddao%40googlegroups.com.
David (Google Cloud Support)
Aug 5, 2020, 1:24:15 PM8/5/20
to Google Cloud SQL discuss
Hello Andrew,
If you are not aware yet, a Cloud SQL instance's storage is charged by its storage limit value, and not by its storage usage. Considering that your storage usage increased to nearly 32 GB, I would assume that the storage limit has also increased due to Automatic Storage Increase feature being enabled on the instance. It is better to leave it as enabled since you might need your instance to increase its limit for more usage, but right now, it would better to open a request to decrease the limit (since you cannot do so yourself) so you do not get charged as much when the usage is lower. You can do so by opening a Public Issue Tracker entry and provide your Project ID and the Cloud SQL instance that needs the decrease.
Note that whenever you open a Public Issue Tracker entry, you should use a component that is listed as private if there are any private information to provide, such as your Project ID.
Reply all
Reply to author
Forward
0 new messages