Excessive Memory Usage - Postgres

[Mike Hardy]

Hiya, we created a new Postgres instance and restored a database. The restore only took two hours, but as you can see in the attached memory usage graph, the memory usage has stayed high ever since. 

This is a test DB and it's had near zero usage since the restore operation. 

We did not change any of the default settings - I've included the settings below. 

What's going on with this? Is there a way to better tune this instance? I know postgres is in beta and we have limited options but suggestions/tips are much appreciated.

Setting	Value
allow_system_table_mods	off
application_name	Postico 1.2.3
archive_command	(disabled)
archive_mode	off
archive_timeout	300
array_nulls	on
authentication_timeout	60
autovacuum	on
autovacuum_analyze_scale_factor	0.1
autovacuum_analyze_threshold	50
autovacuum_freeze_max_age	200000000
autovacuum_max_workers	3
autovacuum_multixact_freeze_max_age	400000000
autovacuum_naptime	60
autovacuum_vacuum_cost_delay	20
autovacuum_vacuum_cost_limit	-1
autovacuum_vacuum_scale_factor	0.2
autovacuum_vacuum_threshold	50
autovacuum_work_mem	-1
backend_flush_after	0
backslash_quote	safe_encoding
bgwriter_delay	200
bgwriter_flush_after	64
bgwriter_lru_maxpages	100
bgwriter_lru_multiplier	2
block_size	8192
bonjour	off
bonjour_name
bytea_output	hex
check_function_bodies	on
checkpoint_completion_target	0.5
checkpoint_flush_after	32
checkpoint_timeout	300
checkpoint_warning	30
client_encoding	UNICODE
client_min_messages	notice
cluster_name
commit_delay	0
commit_siblings	5
constraint_exclusion	partition
cpu_index_tuple_cost	0.005
cpu_operator_cost	0.0025
cpu_tuple_cost	0.01
cursor_tuple_fraction	0.1
data_checksums	on
DateStyle	ISO, MDY
db_user_namespace	off
deadlock_timeout	1000
debug_assertions	off
debug_pretty_print	on
debug_print_parse	off
debug_print_plan	off
debug_print_rewritten	off
default_statistics_target	100
default_tablespace
default_text_search_config	pg_catalog.english
default_transaction_deferrable	off
default_transaction_isolation	read committed
default_transaction_read_only	off
default_with_oids	off
dynamic_shared_memory_type	posix
effective_cache_size	524288
effective_io_concurrency	1
enable_bitmapscan	on
enable_hashagg	on
enable_hashjoin	on
enable_indexonlyscan	on
enable_indexscan	on
enable_material	on
enable_mergejoin	on
enable_nestloop	on
enable_seqscan	on
enable_sort	on
enable_tidscan	on
escape_string_warning	on
event_source	PostgreSQL
exit_on_error	off
extra_float_digits	3
force_parallel_mode	off
from_collapse_limit	8
fsync	on
full_page_writes	on
geqo	on
geqo_effort	5
geqo_generations	0
geqo_pool_size	0
geqo_seed	0
geqo_selection_bias	2
geqo_threshold	12
gin_fuzzy_search_limit	0
gin_pending_list_limit	4096
hot_standby	on
hot_standby_feedback	off
huge_pages	try
idle_in_transaction_session_timeout	0
ignore_checksum_failure	off
ignore_system_indexes	off
integer_datetimes	on
IntervalStyle	postgres
join_collapse_limit	8
krb_caseins_users	off
lc_collate	en_US.UTF8
lc_ctype	en_US.UTF8
lc_messages	en_US.UTF8
lc_monetary	en_US.UTF8
lc_numeric	en_US.UTF8
lc_time	en_US.UTF8
listen_addresses	*
lo_compat_privileges	off
local_preload_libraries
lock_timeout	0
log_autovacuum_min_duration	-1
log_checkpoints	off
log_connections	off
log_destination	stderr
log_disconnections	off
log_duration	off
log_error_verbosity	default
log_executor_stats	off
log_file_mode	600
log_hostname	off
log_line_prefix
log_lock_waits	off
log_min_duration_statement	-1
log_min_error_statement	error
log_min_messages	warning
log_parser_stats	off
log_planner_stats	off
log_replication_commands	off
log_rotation_age	1440
log_rotation_size	10240
log_statement	none
log_statement_stats	off
log_temp_files	0
log_timezone	UTC
log_truncate_on_rotation	off
logging_collector	off
maintenance_work_mem	65536
max_connections	100
max_files_per_process	1000
max_function_args	100
max_identifier_length	63
max_index_keys	32
max_locks_per_transaction	64
max_parallel_workers_per_gather	0
max_pred_locks_per_transaction	64
max_prepared_transactions	0
max_replication_slots	10
max_stack_depth	2048
max_standby_archive_delay	30000
max_standby_streaming_delay	30000
max_wal_senders	10
max_wal_size	64
max_worker_processes	8
min_parallel_relation_size	1024
min_wal_size	5
old_snapshot_threshold	-1
operator_precedence_warning	off
parallel_setup_cost	1000
parallel_tuple_cost	0.1
password_encryption	on
pg_stat_statements.max	5000
pg_stat_statements.save	on
pg_stat_statements.track	top
pg_stat_statements.track_utility	on
port	5432
post_auth_delay	0
pre_auth_delay	0
quote_all_identifiers	off
random_page_cost	4
replacement_sort_tuples	150000
restart_after_crash	on
row_security	on
search_path	"$user", public
segment_size	131072
seq_page_cost	1
server_encoding	UTF8
server_version	9.6.1
server_version_num	90601
session_replication_role	origin
shared_buffers	16384
sql_inheritance	on
ssl	on
ssl_ca_file	root.crt
ssl_cert_file	server.crt
ssl_crl_file
ssl_key_file	server.key
ssl_prefer_server_ciphers	on
standard_conforming_strings	on
statement_timeout	0
superuser_reserved_connections	3
synchronize_seqscans	on
synchronous_commit	on
synchronous_standby_names
syslog_facility	local0
syslog_ident	postgres
syslog_sequence_numbers	on
syslog_split_messages	on
tcp_keepalives_count	9
tcp_keepalives_idle	60
tcp_keepalives_interval	75
temp_buffers	1024
temp_file_limit	-1
temp_tablespaces
TimeZone	UTC
timezone_abbreviations	Default
trace_notify	off
trace_recovery_messages	log
trace_sort	off
track_activities	on
track_activity_query_size	1024
track_commit_timestamp	off
track_counts	on
track_functions	none
track_io_timing	off
transaction_deferrable	off
transaction_isolation	read committed
transaction_read_only	off
transform_null_equals	off
unix_socket_group
unix_socket_permissions	777
update_process_title	on
vacuum_cost_delay	0
vacuum_cost_limit	200
vacuum_cost_page_dirty	20
vacuum_cost_page_hit	1
vacuum_cost_page_miss	10
vacuum_defer_cleanup_age	0
vacuum_freeze_min_age	50000000
vacuum_freeze_table_age	150000000
vacuum_multixact_freeze_min_age	5000000
vacuum_multixact_freeze_table_age	150000000
wal_block_size	8192
wal_buffers	512
wal_compression	off
wal_keep_segments	0
wal_level	replica
wal_log_hints	off
wal_receiver_status_interval	10
wal_receiver_timeout	60000
wal_retrieve_retry_interval	5000
wal_segment_size	2048
wal_sender_timeout	60000
wal_sync_method	fdatasync
wal_writer_delay	200
wal_writer_flush_after	128
work_mem	4096
xmlbinary	base64
xmloption	content
zero_damaged_pages	off

[George (Cloud Platform Support)]

Hello Mike, 

Is memory usage still high in value right now? If your instance hasn't been in use for a while, and the activation policy value is set to "ON_DEMAND", there will be little memory usage shown prior to actual use, in your case the backup restore operation. Your instance remains up for 15 minutes after the completion of the last access, so you won't notice an immediate drop in memory usage. This is documented in the "How is use of my instance calculated?" sub-chapter of the "Cloud SQL FAQ" online document. 

For the actual tuning of your PostgreSQL database, you may have a look at the "18.4. Resource Consumption" page of the PostgreSQL documentation. 

[Mike Hardy]

Hi George, memory usage stayed high until I restarted the instance. That's not practical when we're in production.

The Cloud SQL FAQ resource seems related to billing, which I have no doubts about. We plan to use this instance continuously with no/minimal downtime.

[Vladimir Rusinov]

Hi Mike,

What is your instance size?

Most likely cause of memory usage is PostgreSQL's shared_buffers. It is expected that around 1/3rd of the memory will be used by them pretty much all the time. Memory usage will be low immediately after restart since shared_buffers will be empty, but this is expected to grow over time.

PostgreSQL does not discard any 'old' contents of shared_buffers since they may be still useful, which should explain why usage continued to stay high after import even though instance was idle.

Please let us know if you see memory usage of more than a third of the total memory on idle instance.

Thank you!

--
Vladimir Rusinov
PostgreSQL SRE, Google Ireland

Google Ireland Ltd.,Gordon House, Barrow Street, Dublin 4, Ireland
Registered in Dublin, Ireland
Registration Number: 368047

--
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+unsub...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/3988ec70-98f2-46c0-80a3-acf0118391c5%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Mike Hardy]

Hi Vladimir, thanks for the answer - it's 2 cores + 7.5GB. I believe memory usage was somewhere around 3GB until I restarted the machine.