Re: ERROR: out of memory - Mailing list pgsql-general

On Nov 16, 2007, at 1:48 AM, Anton wrote:

> Hi.
>
> I got an error when I try to VACUUM ANALYZE table.
>
> # VACUUM ANALYZE n_traf;
> ERROR:  out of memory
> DETAIL:  Failed on request of size 536870910.
>
> In logfile:
> TopMemoryContext: 33464512 total in 12 blocks; 10560 free (61 chunks);
> 33453952 used
> TopTransactionContext: 8192 total in 1 blocks; 7688 free (2
> chunks); 504 used
> SPI Plan: 3072 total in 2 blocks; 976 free (0 chunks); 2096 used
> SPI Plan: 3072 total in 2 blocks; 648 free (0 chunks); 2424 used
> RI query cache: 24576 total in 2 blocks; 14136 free (5 chunks);
> 10440 used
> Local Buffer Lookup Table: 253952 total in 5 blocks; 120536 free (16
> chunks); 133416 used
> Type information cache: 8192 total in 1 blocks; 1800 free (0
> chunks); 6392 used
> Operator class cache: 8192 total in 1 blocks; 4872 free (0 chunks);
> 3320 used
> MessageContext: 8192 total in 1 blocks; 7312 free (2 chunks); 880 used
> smgr relation table: 8192 total in 1 blocks; 1776 free (0 chunks);
> 6416 used
> TransactionAbortContext: 32768 total in 1 blocks; 32752 free (0
> chunks); 16 used
> Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
> PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 used
> PortalHeapMemory: 3072 total in 2 blocks; 3000 free (5 chunks); 72
> used
> Vacuum: 8192 total in 1 blocks; 8136 free (0 chunks); 56 used
> Relcache by OID: 8192 total in 1 blocks; 3376 free (0 chunks); 4816
> used
> CacheMemoryContext: 659000 total in 19 blocks; 219336 free (2 chunks);
> 439664 used
> nn_cpnt_date_time_account_id: 1024 total in 1 blocks; 288 free (0
> chunks); 736 used
> nn_cpnt_account_id_date_time: 1024 total in 1 blocks; 288 free (0
> chunks); 736 used
> nn_cpnt_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
> pg_description_o_c_o_index: 1024 total in 1 blocks; 216 free (0
> chunks); 808 used
> n_traftypes_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632
> used
> n_logins_login_pwd: 1024 total in 1 blocks; 328 free (0 chunks);
> 696 used
> n_logins_login_id: 1024 total in 1 blocks; 352 free (0 chunks); 672
> used
> n_logins_account_id: 1024 total in 1 blocks; 392 free (0 chunks);
> 632 used
> n_logins_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
> n_logins_login_key: 1024 total in 1 blocks; 392 free (0 chunks);
> 632 used
> pg_shdepend_depender_index: 1024 total in 1 blocks; 216 free (0
> chunks); 808 used
> pg_shdepend_reference_index: 1024 total in 1 blocks; 288 free (0
> chunks); 736 used
> pg_depend_depender_index: 1024 total in 1 blocks; 216 free (0
> chunks); 808 used
> pg_depend_reference_index: 1024 total in 1 blocks; 216 free (0
> chunks); 808 used
> pg_toast_2619_index: 1024 total in 1 blocks; 288 free (0 chunks);
> 736 used
> n_traf_login_id_date_time_traftype_id: 1024 total in 1 blocks; 216
> free (0 chunks); 808 used
> n_traf_date_time_login_id: 1024 total in 1 blocks; 288 free (0
> chunks); 736 used
> pg_index_indrelid_index: 1024 total in 1 blocks; 352 free (0
> chunks); 672 used
> pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 288 free (0
> chunks); 736 used
> pg_type_typname_nsp_index: 1024 total in 1 blocks; 288 free (0
> chunks); 736 used
> pg_type_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672
> used
> pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 288 free (0
> chunks); 736 used
> pg_statistic_relid_att_index: 1024 total in 1 blocks; 288 free (0
> chunks); 736 used
> pg_auth_members_member_role_index: 1024 total in 1 blocks; 328 free (0
> chunks); 696 used
> pg_auth_members_role_member_index: 1024 total in 1 blocks; 328 free (0
> chunks); 696 used
> pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 328 free (0
> chunks); 696 used
> pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 216 free (0
> chunks); 808 used
> pg_proc_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672
> used
> pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 152 free (0
> chunks); 872 used
> pg_operator_oid_index: 1024 total in 1 blocks; 352 free (0 chunks);
> 672 used
> pg_opclass_oid_index: 1024 total in 1 blocks; 352 free (0 chunks);
> 672 used
> pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 216 free (0
> chunks); 808 used
> pg_namespace_oid_index: 1024 total in 1 blocks; 352 free (0
> chunks); 672 used
> pg_namespace_nspname_index: 1024 total in 1 blocks; 352 free (0
> chunks); 672 used
> pg_language_oid_index: 1024 total in 1 blocks; 392 free (0 chunks);
> 632 used
> pg_language_name_index: 1024 total in 1 blocks; 392 free (0
> chunks); 632 used
> pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 288 free (0
> chunks); 736 used
> pg_index_indexrelid_index: 1024 total in 1 blocks; 352 free (0
> chunks); 672 used
> pg_authid_oid_index: 1024 total in 1 blocks; 352 free (0 chunks);
> 672 used
> pg_authid_rolname_index: 1024 total in 1 blocks; 352 free (0
> chunks); 672 used
> pg_database_oid_index: 1024 total in 1 blocks; 352 free (0 chunks);
> 672 used
> pg_conversion_oid_index: 1024 total in 1 blocks; 392 free (0
> chunks); 632 used
> pg_conversion_name_nsp_index: 1024 total in 1 blocks; 328 free (0
> chunks); 696 used
> pg_conversion_default_index: 1024 total in 1 blocks; 192 free (0
> chunks); 832 used
> pg_class_relname_nsp_index: 1024 total in 1 blocks; 288 free (0
> chunks); 736 used
> pg_class_oid_index: 1024 total in 1 blocks; 352 free (0 chunks);
> 672 used
> pg_cast_source_target_index: 1024 total in 1 blocks; 288 free (0
> chunks); 736 used
> pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 288 free (0
> chunks); 736 used
> pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 288 free (0
> chunks); 736 used
> pg_amproc_opc_proc_index: 1024 total in 1 blocks; 216 free (0
> chunks); 808 used
> pg_amop_opr_opc_index: 1024 total in 1 blocks; 288 free (0 chunks);
> 736 used
> pg_amop_opc_strat_index: 1024 total in 1 blocks; 216 free (0
> chunks); 808 used
> pg_aggregate_fnoid_index: 1024 total in 1 blocks; 352 free (0
> chunks); 672 used
> MdSmgr: 8192 total in 1 blocks; 7528 free (0 chunks); 664 used
> LOCALLOCK hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280
> used
> Timezones: 48616 total in 2 blocks; 5968 free (0 chunks); 42648 used
> ErrorContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
>
> It occures when I set "shared_buffers =
> 768M" ("maintenance_work_mem = 512MB").
>
> Related sysctl settings are:
> kern.seminfo.semmni=4096
> kern.seminfo.semmns=4096
> kern.seminfo.semmnu=256
>
> kern.shminfo.shmall=1073741824
> kern.shminfo.shmmax=838860800
> kern.shminfo.shmmni=8192
> kern.shminfo.shmseg=1024
>
> My machine has 2G RAM. And I want make postgres utilize it...

512MB is way too much maintenance_work_mem for normal running on a
machine with 2GB of RAM.  Try backing that off to about 128MB.
Notice that the request size that's failing is equal to your
maintenance_work_mem.

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com