Обсуждение: ERROR: out of memory

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...
--
engineer

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

On Nov 16, 2007 1:48 AM, Anton <anton200@gmail.com> wrote:
> My machine has 2G RAM. And I want make postgres utilize it...

You're trying to tune your database based on philosophy.  Making
postgresql use all the RAM may or may not make your machine run
faster.  The OS caches a lot of data for you, so having postgresql do
that as well may or may not speed things up.   By the way, in my
experience, 768Meg is a reasonable number for shared_buffers for a 2
Gig machine.  Most of the time anyway.

And like Erik said, 512Meg maintenance_work_mem is way too much on a 2
gig machine.  What's your work_mem set to?  I'd be worried you might
have that cranked up too high as well.  On my box, btw, anything over
16Megs is slower on most big queries than 16Megs.