Hi,
Starting this morning I have been getting out of memory errors from the postgres autovacuum process. I have searched through previous similar questions but not found anything other than suggestions to reduce either 'shared_buffers' or 'maintenance_work_mem' but these seem very instance specific and would like a better understanding both of the root cause and solution before making changes to my postgres setup.
We have a single postgres cluster installed from the Debian repository hosting 36 databases the details of which are below. The table being vacuumed at the time is about 19GB in size.
Errors from log:
TopMemoryContext: 232408 total in 13 blocks; 11792 free (8 chunks); 220616 used
TopTransactionContext: 24576 total in 2 blocks; 23312 free (25 chunks); 1264 used
Type information cache: 24576 total in 2 blocks; 11888 free (5 chunks); 12688 used
Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
TOAST to main relid map: 24576 total in 2 blocks; 11872 free (5 chunks); 12704 used
AV worker: 24576 total in 2 blocks; 19816 free (7 chunks); 4760 used
Autovacuum Portal: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used
Vacuum: 8192 total in 1 blocks; 8080 free (0 chunks); 112 used
smgr relation table: 24576 total in 2 blocks; 13920 free (4 chunks); 10656 used
TransactionAbortContext: 32768 total in 1 blocks; 32736 free (0 chunks); 32 used
Portal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
PortalMemory: 0 total in 0 blocks; 0 free (0 chunks); 0 used
Relcache by OID: 24576 total in 2 blocks; 13872 free (3 chunks); 10704 used
CacheMemoryContext: 817840 total in 20 blocks; 171584 free (1 chunks); 646256 used
pbl_product_ix: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
pbl_maxallocation_idx: 2048 total in 1 blocks; 440 free (0 chunks); 1608 used
pbl_endtimenotz_ix: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
pbl_endtimegmt_ix: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
pbl_begintimenotz_ix: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
pbl_begintimegmt_ix: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
pbl_allocated_ix: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
productbookinglevel_product_id_key: 2048 total in 1 blocks; 656 free (0 chunks); 1392 used
productbookinglevel_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
pg_index_indrelid_index: 2048 total in 1 blocks; 704 free (0 chunks); 1344 used
pg_opclass_am_name_nsp_index: 3072 total in 2 blocks; 1448 free (4 chunks); 1624 used
pg_foreign_data_wrapper_name_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_enum_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_class_relname_nsp_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
pg_foreign_server_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_statistic_relid_att_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
pg_cast_source_target_index: 3072 total in 2 blocks; 1600 free (2 chunks); 1472 used
pg_language_name_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_authid_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used
pg_amop_fam_strat_index: 3072 total in 2 blocks; 1384 free (2 chunks); 1688 used
pg_index_indexrelid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used
pg_ts_template_tmplname_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
pg_ts_config_map_index: 3072 total in 2 blocks; 1496 free (4 chunks); 1576 used
pg_opclass_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used
pg_foreign_data_wrapper_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_auth_members_member_role_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
pg_ts_dict_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_conversion_default_index: 3072 total in 2 blocks; 1432 free (3 chunks); 1640 used
pg_operator_oprname_l_r_n_index: 3072 total in 2 blocks; 1432 free (3 chunks); 1640 used
pg_trigger_tgrelid_tgname_index: 3072 total in 2 blocks; 1600 free (2 chunks); 1472 used
pg_enum_typid_label_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
pg_ts_config_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_user_mapping_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_opfamily_am_name_nsp_index: 3072 total in 2 blocks; 1496 free (4 chunks); 1576 used
pg_type_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used
pg_aggregate_fnoid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_constraint_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_rewrite_rel_rulename_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
pg_ts_parser_prsname_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
pg_ts_config_cfgname_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
pg_ts_parser_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_operator_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used
pg_namespace_nspname_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_ts_template_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_amop_opr_fam_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
pg_ts_dict_dictname_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
pg_auth_members_role_member_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
pg_type_typname_nsp_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
pg_opfamily_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_class_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used
pg_proc_proname_args_nsp_index: 3072 total in 2 blocks; 1496 free (4 chunks); 1576 used
pg_attribute_relid_attnum_index: 3072 total in 2 blocks; 1600 free (2 chunks); 1472 used
pg_proc_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_language_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_namespace_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used
pg_database_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used
pg_amproc_fam_proc_index: 3072 total in 2 blocks; 1384 free (2 chunks); 1688 used
pg_authid_rolname_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_foreign_server_name_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_attribute_relid_attnam_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
pg_conversion_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_user_mapping_user_server_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
pg_conversion_name_nsp_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
MdSmgr: 8192 total in 1 blocks; 7872 free (0 chunks); 320 used
LOCALLOCK hash: 24576 total in 2 blocks; 15984 free (5 chunks); 8592 used
Timezones: 83472 total in 2 blocks; 3744 free (0 chunks); 79728 used
Postmaster: 253952 total in 5 blocks; 180336 free (851 chunks); 73616 used
ErrorContext: 8192 total in 1 blocks; 8160 free (3 chunks); 32 used
522f9128.1151 ERROR: out of memory
522f9128.1151 DETAIL: Failed on request of size 336150396.
522f9128.1151 CONTEXT: automatic vacuum of table "client.public.product"
SELECT version();
PostgreSQL 8.4.11 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit
SELECT name, current_setting(name), source FROM pg_settings WHERE source NOT IN ('default', 'override');
autovacuum | on
checkpoint_completion_target | 0.9
checkpoint_segments | 16
constraint_exclusion | on
DateStyle | ISO, MDY
default_statistics_target | 50
default_text_search_config | pg_catalog.english
effective_cache_size | 32GB
external_pid_file | /var/run/postgresql/8.4-main.pid
fsync | on
full_page_writes | on
lc_messages | en_AU.UTF-8
lc_monetary | en_AU.UTF-8
lc_numeric | en_AU.UTF-8
lc_time | en_AU.UTF-8
listen_addresses | *
log_line_prefix | %u %h %c
log_statement | ddl
log_timezone | localtime
maintenance_work_mem | 1GB
max_connections | 1000
max_locks_per_transaction | 130
max_stack_depth | 2MB
port | 5432
shared_buffers | 11GB
synchronous_commit | on
TimeZone | localtime
timezone_abbreviations | Default
track_activities | on
track_counts | on
unix_socket_directory | /var/run/postgresql
update_process_title | on
wal_buffers | 8MB
wal_sync_method | fsync
work_mem | 8MB
SELECT table_size(pg_total_relation_size('product'));
table_size
-----------
19 GB
uname -a:
Linux hostdb2 2.6.32-5-amd64 #1 SMP Sun May 6 04:00:17 UTC 2012 x86_64 GNU/Linux
free -m
total used free shared buffers cached
Mem: 48396 48139 257 0 173 44771
-/+ buffers/cache: 3194 45201
Swap: 956 20 936
ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 16382
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) unlimited
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
TIA,
Tim
Starting this morning I have been getting out of memory errors from the postgres autovacuum process. I have searched through previous similar questions but not found anything other than suggestions to reduce either 'shared_buffers' or 'maintenance_work_mem' but these seem very instance specific and would like a better understanding both of the root cause and solution before making changes to my postgres setup.
We have a single postgres cluster installed from the Debian repository hosting 36 databases the details of which are below. The table being vacuumed at the time is about 19GB in size.
Errors from log:
TopMemoryContext: 232408 total in 13 blocks; 11792 free (8 chunks); 220616 used
TopTransactionContext: 24576 total in 2 blocks; 23312 free (25 chunks); 1264 used
Type information cache: 24576 total in 2 blocks; 11888 free (5 chunks); 12688 used
Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
TOAST to main relid map: 24576 total in 2 blocks; 11872 free (5 chunks); 12704 used
AV worker: 24576 total in 2 blocks; 19816 free (7 chunks); 4760 used
Autovacuum Portal: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used
Vacuum: 8192 total in 1 blocks; 8080 free (0 chunks); 112 used
smgr relation table: 24576 total in 2 blocks; 13920 free (4 chunks); 10656 used
TransactionAbortContext: 32768 total in 1 blocks; 32736 free (0 chunks); 32 used
Portal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
PortalMemory: 0 total in 0 blocks; 0 free (0 chunks); 0 used
Relcache by OID: 24576 total in 2 blocks; 13872 free (3 chunks); 10704 used
CacheMemoryContext: 817840 total in 20 blocks; 171584 free (1 chunks); 646256 used
pbl_product_ix: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
pbl_maxallocation_idx: 2048 total in 1 blocks; 440 free (0 chunks); 1608 used
pbl_endtimenotz_ix: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
pbl_endtimegmt_ix: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
pbl_begintimenotz_ix: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
pbl_begintimegmt_ix: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
pbl_allocated_ix: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
productbookinglevel_product_id_key: 2048 total in 1 blocks; 656 free (0 chunks); 1392 used
productbookinglevel_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
pg_index_indrelid_index: 2048 total in 1 blocks; 704 free (0 chunks); 1344 used
pg_opclass_am_name_nsp_index: 3072 total in 2 blocks; 1448 free (4 chunks); 1624 used
pg_foreign_data_wrapper_name_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_enum_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_class_relname_nsp_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
pg_foreign_server_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_statistic_relid_att_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
pg_cast_source_target_index: 3072 total in 2 blocks; 1600 free (2 chunks); 1472 used
pg_language_name_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_authid_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used
pg_amop_fam_strat_index: 3072 total in 2 blocks; 1384 free (2 chunks); 1688 used
pg_index_indexrelid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used
pg_ts_template_tmplname_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
pg_ts_config_map_index: 3072 total in 2 blocks; 1496 free (4 chunks); 1576 used
pg_opclass_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used
pg_foreign_data_wrapper_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_auth_members_member_role_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
pg_ts_dict_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_conversion_default_index: 3072 total in 2 blocks; 1432 free (3 chunks); 1640 used
pg_operator_oprname_l_r_n_index: 3072 total in 2 blocks; 1432 free (3 chunks); 1640 used
pg_trigger_tgrelid_tgname_index: 3072 total in 2 blocks; 1600 free (2 chunks); 1472 used
pg_enum_typid_label_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
pg_ts_config_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_user_mapping_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_opfamily_am_name_nsp_index: 3072 total in 2 blocks; 1496 free (4 chunks); 1576 used
pg_type_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used
pg_aggregate_fnoid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_constraint_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_rewrite_rel_rulename_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
pg_ts_parser_prsname_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
pg_ts_config_cfgname_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
pg_ts_parser_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_operator_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used
pg_namespace_nspname_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_ts_template_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_amop_opr_fam_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
pg_ts_dict_dictname_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
pg_auth_members_role_member_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
pg_type_typname_nsp_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
pg_opfamily_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_class_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used
pg_proc_proname_args_nsp_index: 3072 total in 2 blocks; 1496 free (4 chunks); 1576 used
pg_attribute_relid_attnum_index: 3072 total in 2 blocks; 1600 free (2 chunks); 1472 used
pg_proc_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_language_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_namespace_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used
pg_database_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used
pg_amproc_fam_proc_index: 3072 total in 2 blocks; 1384 free (2 chunks); 1688 used
pg_authid_rolname_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_foreign_server_name_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_attribute_relid_attnam_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
pg_conversion_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used
pg_user_mapping_user_server_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
pg_conversion_name_nsp_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used
MdSmgr: 8192 total in 1 blocks; 7872 free (0 chunks); 320 used
LOCALLOCK hash: 24576 total in 2 blocks; 15984 free (5 chunks); 8592 used
Timezones: 83472 total in 2 blocks; 3744 free (0 chunks); 79728 used
Postmaster: 253952 total in 5 blocks; 180336 free (851 chunks); 73616 used
ErrorContext: 8192 total in 1 blocks; 8160 free (3 chunks); 32 used
522f9128.1151 ERROR: out of memory
522f9128.1151 DETAIL: Failed on request of size 336150396.
522f9128.1151 CONTEXT: automatic vacuum of table "client.public.product"
SELECT version();
PostgreSQL 8.4.11 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit
SELECT name, current_setting(name), source FROM pg_settings WHERE source NOT IN ('default', 'override');
autovacuum | on
checkpoint_completion_target | 0.9
checkpoint_segments | 16
constraint_exclusion | on
DateStyle | ISO, MDY
default_statistics_target | 50
default_text_search_config | pg_catalog.english
effective_cache_size | 32GB
external_pid_file | /var/run/postgresql/8.4-main.pid
fsync | on
full_page_writes | on
lc_messages | en_AU.UTF-8
lc_monetary | en_AU.UTF-8
lc_numeric | en_AU.UTF-8
lc_time | en_AU.UTF-8
listen_addresses | *
log_line_prefix | %u %h %c
log_statement | ddl
log_timezone | localtime
maintenance_work_mem | 1GB
max_connections | 1000
max_locks_per_transaction | 130
max_stack_depth | 2MB
port | 5432
shared_buffers | 11GB
synchronous_commit | on
TimeZone | localtime
timezone_abbreviations | Default
track_activities | on
track_counts | on
unix_socket_directory | /var/run/postgresql
update_process_title | on
wal_buffers | 8MB
wal_sync_method | fsync
work_mem | 8MB
SELECT table_size(pg_total_relation_size('product'));
table_size
-----------
19 GB
uname -a:
Linux hostdb2 2.6.32-5-amd64 #1 SMP Sun May 6 04:00:17 UTC 2012 x86_64 GNU/Linux
free -m
total used free shared buffers cached
Mem: 48396 48139 257 0 173 44771
-/+ buffers/cache: 3194 45201
Swap: 956 20 936
ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 16382
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) unlimited
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
TIA,
Tim
