Обсуждение: BUG #6186: out of memory while analyze
The following bug has been logged online:
Bug reference: 6186
Logged by: Lampa
Email address: lampacz@gmail.com
PostgreSQL version: 9.0.4/8.4.8
Operating system: Linux Debian testing 32bit
Description: out of memory while analyze
Details:
Trying analyze table (ANALYZE TABLE table or VACUUM ANALYZE table) with
3051316 rows (displayed size 2521MB in \dt+)
Log:
TopMemoryContext: 42800 total in 5 blocks; 5304 free (7 chunks); 37496 used
Type information cache: 8192 total in 1 blocks; 1800 free (0 chunks); 6392
used
TopTransactionContext: 8192 total in 1 blocks; 7696 free (0 chunks); 496
used
MessageContext: 8192 total in 1 blocks; 5688 free (1 chunks); 2504 used
Operator class cache: 8192 total in 1 blocks; 3848 free (0 chunks); 4344
used
smgr relation table: 8192 total in 1 blocks; 2816 free (0 chunks); 5376
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: 1024 total in 1 blocks; 1008 free (13 chunks); 16
used
Analyze: 2236767256 total in 279 blocks; 18080 free (423 chunks);
2236749176 used
Vacuum: 8192 total in 1 blocks; 7872 free (0 chunks); 320 used
Relcache by OID: 8192 total in 1 blocks; 2856 free (0 chunks); 5336 used
CacheMemoryContext: 667696 total in 20 blocks; 135264 free (1 chunks);
532432 used
i_table_zamid: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
i_table_zamestnanci_id: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
i_table_zacatek_delka: 1024 total in 1 blocks; 280 free (0 chunks); 744
used
i_table_zacatek: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
i_table_tymy_instance_id: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
i_table_tymy_id: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
i_table_strid: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
i_table_stavukonu_id: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
i_table_stavcas: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
i_table_stav: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
i_table_splneno_cas: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
i_table_skupinyzamesntnacu_id: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
i_table_sablonyukonu_id: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
i_table_prostredky_id: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
i_table_priorita: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
i_table_pacienti_id: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
i_table_odbid: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
i_table_objednano_id: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
i_table_nadrazeny_ukon: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
i_table_maxid: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
i_table_kpid_spl: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
i_table_kpid_obj: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
i_table_klientskekarty_id: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
i_table_delka: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
i_table_adresareukonu_id: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
table_pkey: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
pg_index_indrelid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_user_mapping_user_server_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_user_mapping_oid_index: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
pg_type_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 280 free (0 chunks);
744 used
pg_ts_template_oid_index: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
pg_ts_template_tmplname_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_ts_parser_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
pg_ts_parser_prsname_index: 1024 total in 1 blocks; 280 free (0 chunks);
744 used
pg_ts_dict_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
pg_ts_dict_dictname_index: 1024 total in 1 blocks; 280 free (0 chunks);
744 used
pg_ts_config_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
pg_ts_config_cfgname_index: 1024 total in 1 blocks; 280 free (0 chunks);
744 used
pg_ts_config_map_index: 1024 total in 1 blocks; 192 free (0 chunks); 832
used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_class_relname_nsp_index: 1024 total in 1 blocks; 240 free (0 chunks);
784 used
pg_proc_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
pg_opfamily_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
pg_opfamily_am_name_nsp_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 128 free (0
chunks); 896 used
pg_namespace_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_namespace_nspname_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
pg_language_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
pg_language_name_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
pg_foreign_server_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_foreign_server_name_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_foreign_data_wrapper_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_foreign_data_wrapper_name_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_enum_typid_label_index: 1024 total in 1 blocks; 280 free (0 chunks);
744 used
pg_enum_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
pg_database_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_conversion_oid_index: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
pg_constraint_oid_index: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_conversion_default_index: 1024 total in 1 blocks; 128 free (0
chunks); 896 used
pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 152 free (0
chunks); 872 used
pg_cast_source_target_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_authid_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_authid_rolname_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
pg_auth_members_role_member_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_auth_members_member_role_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_amop_opr_fam_index: 1024 total in 1 blocks; 280 free (0 chunks); 744
used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_operator_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_amproc_fam_proc_index: 1024 total in 1 blocks; 88 free (0 chunks);
936 used
pg_amop_fam_strat_index: 1024 total in 1 blocks; 88 free (0 chunks); 936
used
pg_opclass_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_index_indexrelid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_class_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
MdSmgr: 8192 total in 1 blocks; 7384 free (0 chunks); 808 used
LOCALLOCK hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
Timezones: 78520 total in 2 blocks; 5968 free (0 chunks); 72552 used
ErrorContext: 8192 total in 1 blocks; 8176 free (3 chunks); 16 used
2011-08-30 12:39:50 CEST ERROR: out of memory
2011-08-30 12:39:50 CEST DETAIL: Failed on request of size 1600.
2011-08-30 12:39:50 CEST STATEMENT: ANALYZE table;
On another machine with similar configuration (memory/cpu) but 64bit
everything is ok. Machine has 4G ram/8G swap, cpu X3430 2.4GHz. Memory seems
to be OK - memtested
Thank you.
On 30.08.2011 14:20, Lampa wrote: > Trying analyze table (ANALYZE TABLE table or VACUUM ANALYZE table) with > 3051316 rows (displayed size 2521MB in \dt+) > > Log: > ... > Analyze: 2236767256 total in 279 blocks; 18080 free (423 chunks); > 2236749176 used > ... Hmm, that looks like a memory like in ANALYZE. What does the table look like? "\d table" would be helpful. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
"Lampa" <lampacz@gmail.com> writes:
> Trying analyze table (ANALYZE TABLE table or VACUUM ANALYZE table) with
> 3051316 rows (displayed size 2521MB in \dt+)
What have you got maintenance_work_mem set to? shared_buffers might
also be interesting.
regards, tom lane
Hello, table design http://pastebin.com/RW6vLAVP And configuration: ssl =3D false shared_buffers =3D 900MB # min 128kB work_mem =3D 100MB # min 64kB maintenance_work_mem =3D 524MB # min 1MB fsync =3D off # turns forced synchronization on= or off synchronous_commit =3D off # immediate fsync at commit full_page_writes =3D off # recover from partial page writes effective_cache_size =3D 1GB default_statistics_target =3D 8000 # range 1-10000 Also tried change maintenance_work_mem to 2G and same result. Imposible to set over 2GB (FATAL: 3145728 is outside the valid range for parameter "maintenance_work_mem" (1024 .. 2097151)) Problem seems to be in default_statistics_target values from approx 6300. Up to 6300 analyze works but on another machine (64bit) works 10000 without problems on same but larger table (more data) 2011/8/30 Tom Lane <tgl@sss.pgh.pa.us>: > "Lampa" <lampacz@gmail.com> writes: >> Trying analyze table (ANALYZE TABLE table or VACUUM ANALYZE table) with >> 3051316 rows (displayed size 2521MB in \dt+) > > What have you got maintenance_work_mem set to? =C2=A0shared_buffers might > also be interesting. > > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0regards, tom lane > --=20 Lampa
On 08/30/11 10:25 PM, Lampa wrote: > Also tried change maintenance_work_mem to 2G and same result. > Imposible to set over 2GB (FATAL: 3145728 is outside the valid range > for parameter "maintenance_work_mem" (1024 .. 2097151)) is this a 32bit postgres ? -- john r pierce N 37, W 122 santa cruz ca mid-left coast
Lampa <lampacz@gmail.com> writes:
> default_statistics_target = 8000 # range 1-10000
> Problem seems to be in default_statistics_target values from approx
> 6300. Up to 6300 analyze works but on another machine (64bit) works
> 10000 without problems on same but larger table (more data)
Well, there's a reason why the default value of that parameter is 100
and not 10000 ;-). The larger it is, the more memory space it's going
to take to do an ANALYZE. I don't see any bug here, you're just trying
to do more than you can do in a 32-bit machine.
I guess we could improve the documentation of default_statistics_target
to point out that it costs not only more time but more memory in
ANALYZE.
regards, tom lane
Hello, ok or write that there is limit for 32bit system. With default value 100 have bad perfomance :-( But thank you for yours time. 2011/8/31 Tom Lane <tgl@sss.pgh.pa.us>: > Lampa <lampacz@gmail.com> writes: >> default_statistics_target =3D 8000 =C2=A0 =C2=A0 =C2=A0 =C2=A0# range 1-= 10000 > >> Problem seems to be in default_statistics_target values from approx >> 6300. Up to 6300 analyze works but on another machine (64bit) works >> 10000 without problems on same but larger table (more data) > > Well, there's a reason why the default value of that parameter is 100 > and not 10000 ;-). =C2=A0The larger it is, the more memory space it's goi= ng > to take to do an ANALYZE. =C2=A0I don't see any bug here, you're just try= ing > to do more than you can do in a 32-bit machine. > > I guess we could improve the documentation of default_statistics_target > to point out that it costs not only more time but more memory in > ANALYZE. > > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0regards, tom lane > --=20 Lampa
Hi Lampa, On Thu, Sep 1, 2011 at 6:26 AM, Lampa <lampacz@gmail.com> wrote: > With default value 100 have bad perfomance :-( Don't set the default value higher: specify a higher value only where needed. See ALTER TABLE ALTER [ COLUMN ] column SET STATISTICS integer ( http://www.postgresql.org/docs/9.0/interactive/sql-altertable.html ). -- Guillaume
> > > ssl = false > shared_buffers = 900MB # min 128kB > work_mem = 100MB # min 64kB > maintenance_work_mem = 524MB # min 1MB > fsync = off # turns forced synchronization on > or off > synchronous_commit = off # immediate fsync at commit > full_page_writes = off # recover from partial page writes > effective_cache_size = 1GB > default_statistics_target = 8000 # range 1-10000 > > > Also tried change maintenance_work_mem to 2G and same result. > Imposible to set over 2GB (FATAL: 3145728 is outside the valid range > for parameter "maintenance_work_mem" (1024 .. 2097151)) > > Actually, it also can be, that your commit limit is reached. Can you also look in the /proc/meminfo -> CommitLimit and Commit_AS. These values should not be too close to each other. What are the values for /proc/sys/vm/swappiness, /proc/sys/vm/overcommit_memory and /proc/sys/vm/overcommit_ratio? What is your swap size. They all influence CommitLimit and the amount of memory, you can get. -- Valentin
Hello,
/proc/sys/vm/swappiness =3D 60
/proc/sys/vm/overcommit_memory =3D 0
/proc/sys/vm/overcommit_ratio =3D 50
total used free shared buffers cached
Mem: 4038 3146 892 0 30 3067
-/+ buffers/cache: 49 3989
Swap: 8581 0 8581
2011/9/1 Valentine Gogichashvili <valgog@gmail.com>:
>>
>> ssl =3D false
>> shared_buffers =3D 900MB =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0# min 128kB
>> work_mem =3D 100MB =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0# min 64kB
>> maintenance_work_mem =3D 524MB =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
# min 1MB
>> fsync =3D off =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 # turns forced synchronization on
>> or off
>> synchronous_commit =3D off =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0# immediate fsync at commit
>> full_page_writes =3D off =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0# recover from partial page writes
>> effective_cache_size =3D 1GB
>> default_statistics_target =3D 8000 =C2=A0 =C2=A0 =C2=A0 =C2=A0# range 1-=
10000
>>
>>
>> Also tried change maintenance_work_mem to 2G and same result.
>> Imposible to set over 2GB (FATAL: =C2=A03145728 is outside the valid ran=
ge
>> for parameter "maintenance_work_mem" (1024 .. 2097151))
>>
>
> Actually, it also can be, that your commit limit is reached. Can you also
> look in the /proc/meminfo -> CommitLimit and Commit_AS. These values shou=
ld
> not be too close to each other.
> What are the values
> for=C2=A0/proc/sys/vm/swappiness,=C2=A0/proc/sys/vm/overcommit_memory
> and=C2=A0/proc/sys/vm/overcommit_ratio? What is your swap size. They all
> influence=C2=A0CommitLimit and the amount of memory, you can get.
> -- Valentin
>
>
--=20
Lampa