Thread: BUG #6186: out of memory while analyze

BUG #6186: out of memory while analyze

From
"Lampa"
Date:
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.

Re: BUG #6186: out of memory while analyze

From
Heikki Linnakangas
Date:
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

Re: BUG #6186: out of memory while analyze

From
Tom Lane
Date:
"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

Re: BUG #6186: out of memory while analyze

From
Lampa
Date:
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

Re: BUG #6186: out of memory while analyze

From
John R Pierce
Date:
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

Re: BUG #6186: out of memory while analyze

From
Tom Lane
Date:
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

Re: BUG #6186: out of memory while analyze

From
Lampa
Date:
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

Re: BUG #6186: out of memory while analyze

From
Guillaume Smet
Date:
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

Re: BUG #6186: out of memory while analyze

From
Valentine Gogichashvili
Date:
>
>
> 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

Re: BUG #6186: out of memory while analyze

From
Lampa
Date:
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