7.3.4 vacuum/analyze error

[Ed L.]

I'm getting a slew of these repeatable errors when running ANALYZE
and/or VACUUM ANALYZE (from an autovacuum process) against a
7.3.4 cluster on HP-UX B.11.00:

2004-09-29 18:14:53.621 [520] ERROR: Memory exhausted in AllocSetAlloc(1 189)

This error is in the FAQ, but that answer does not appear applicable.
The error is occurring on 2 different databases, on multiple tables,
and all tables involved are frequently updated.

Any clues?

Here's an example with more context:

2004-09-29 18:20:55.426 [3728] LOG: query: ANALYZE audit
TopMemoryContex t: 32792 total in 4 blocks; 11664 free (23 chunks); 21128 used
TopTransactionC ontext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
DeferredTrigger Xact: 0 total in 0 blocks; 0 free (0 chunks); 0 used
TransactionComm andContext: 8192 total in 1 blocks; 8176 free (9 chunks); 16 used
QueryContext: 8192 total in 1 blocks; 7440 free (1 chunks); 752 used
Analyze: 132263832 total in 27 blocks; 2984 free (35 chunks); 132260848 used
Vacuum: 8192 total in 1 blocks; 8152 free (0 chunks); 40 used
DeferredTrigger Session: 0 total in 0 blocks; 0 free (0 chunks); 0 used
PortalMemory: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
CacheMemoryCont ext: 516096 total in 6 blocks; 170872 free (1 chunks); 345224 used
pg_index_indrel id_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_toast_16410_ index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_stat_all_tab les: 19456 total in 19 blocks; 216 free (0 chunks); 19240 used
pg_settings: 5120 total in 5 blocks; 336 free (0 chunks); 4784 used
pg_conversion_o id_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_index_indexr elid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_shadow_usesy sid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_rewrite_rel_ rulename_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_group_name_i ndex: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_opclass_am_n ame_nsp_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_cast_source_ target_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_language_nam e_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_type_oid_ind ex: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_language_oid _index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_amop_opc_str ategy_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_statistic_re lid_att_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_amproc_opc_p rocnum_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_class_oid_in dex: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_class_relnam e_nsp_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_type_typname _nsp_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_attribute_re lid_attnam_inde x: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_trigger_tgre lid_tgname_inde x: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_conversion_d efault_index: 2072 total in 2 blocks; 712 free (0 chunks); 1360 used
pg_aggregate_fn oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_namespace_oi d_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_group_sysid_ index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_attribute_re lid_attnum_inde x: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_shadow_usena me_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_namespace_ns pname_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_inherits_rel id_seqno_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_operator_oid _index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_conversion_n ame_nsp_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_opclass_oid_ index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_proc_proname _args_nsp_index : 2072 total in 2 blocks; 712 free (0 chunks); 1360 used
pg_proc_oid_ind ex: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_operator_opr name_l_r_n_inde x: 2072 total in 2 blocks; 712 free (0 chunks); 1360 used
pg_amop_opc_opr _index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
MdSmgr: 8192 total in 1 blocks; 6120 free (0 chunks); 2072 used
DynaHash: 8192 total in 1 blocks; 7064 free (0 chunks); 1128 used
DynaHashTable: 8192 total in 1 blocks; 5080 free (0 chunks); 3112 used
DynaHashTable: 8192 total in 1 blocks; 6112 free (0 chunks); 2080 used
DynaHashTable: 8192 total in 1 blocks; 3016 free (0 chunks); 5176 used
DynaHashTable: 8192 total in 1 blocks; 4040 free (0 chunks); 4152 used
DynaHashTable: 24576 total in 2 blocks; 13240 free (4 chunks); 11336 used
DynaHashTable: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
DynaHashTable: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
DynaHashTable: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
DynaHashTable: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
DynaHashTable: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
ErrorContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
2004-09-29 18:20:56.580 [3728] ERROR: Memory exhausted in AllocSetAlloc(1 189)
2004-09-29 18:20:56.580 [3728] LOG: statement: ANALYZE audit
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

"Ed L." <pg***@bluepolk a.net> writes:

I'm getting a slew of these repeatable errors when running ANALYZE
and/or VACUUM ANALYZE (from an autovacuum process) against a
7.3.4 cluster on HP-UX B.11.00: 2004-09-29 18:14:53.621 [520] ERROR: Memory exhausted in AllocSetAlloc(1 189) Analyze: 132263832 total in 27 blocks; 2984 free (35 chunks); 132260848 used

Either increase your per-process memory limit, or reduce the statistics
targets for this table ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Ed L.]

On Wednesday September 29 2004 5:17, Tom Lane wrote:

"Ed L." <pg***@bluepolk a.net> writes:

I'm getting a slew of these repeatable errors when running ANALYZE
and/or VACUUM ANALYZE (from an autovacuum process) against a
7.3.4 cluster on HP-UX B.11.00:

2004-09-29 18:14:53.621 [520] ERROR: Memory exhausted in
AllocSetAlloc(1 189)

Analyze: 132263832 total in 27 blocks; 2984 free (35 chunks); 132260848
used

Either increase your per-process memory limit, or reduce the statistics
targets for this table ...

What am I missing?

$ ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) 131072
stack(kbytes) 8192
memory(kbytes) unlimited
coredump(blocks ) 4194303
nofiles(descrip tors) 120

$ psql -c "select name, setting from pg_settings" | egrep stats_
stats_block_lev el | off
stats_command_s tring | off
stats_reset_on_ server_start | on
stats_row_level | off
stats_start_col lector | on

$ psql -c "analyze audit"
ERROR: Memory exhausted in AllocSetAlloc(1 189)
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Ed L.]

On Wednesday September 29 2004 8:33, Ed L. wrote:

On Wednesday September 29 2004 5:17, Tom Lane wrote:

"Ed L." <pg***@bluepolk a.net> writes:

I'm getting a slew of these repeatable errors when running ANALYZE
and/or VACUUM ANALYZE (from an autovacuum process) against a
7.3.4 cluster on HP-UX B.11.00:

2004-09-29 18:14:53.621 [520] ERROR: Memory exhausted in
AllocSetAlloc(1 189)

Analyze: 132263832 total in 27 blocks; 2984 free (35 chunks);
132260848 used

Either increase your per-process memory limit, or reduce the statistics
targets for this table ...

What am I missing?

$ ulimit -a
memory(kbytes) unlimited

$ psql -c "select name, setting from pg_settings" | egrep stats_
stats_block_lev el | off
stats_command_s tring | off
stats_reset_on_ server_start | on
stats_row_level | off
stats_start_col lector | on

$ psql -c "analyze audit"
ERROR: Memory exhausted in AllocSetAlloc(1 189)

We actually count on those stats (stats_row_leve l?) in order to effectively
autovacuum based on real changes, so turning them off would not be good.
Is this a bug fixed in a later versions? What might be triggering this?
The only thing I see in common is that all the tables are frequently
updated...

Ed
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Tom Lane]

"Ed L." <pg***@bluepolk a.net> writes:

Either increase your per-process memory limit, or reduce the statistics
targets for this table ...

What am I missing? $ ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) 131072 ^^^^^^^^^^^^^^^ ^^^^^^^^^^^^

This is where the limit is coming from ...
$ psql -c "select name, setting from pg_settings" | egrep stats_
stats_block_lev el | off

Those are not the statistics I'm talking about. I was assuming that
you'd done ALTER TABLE SET STATISTICS somewhere along the line, or
changed default_statist ics_target. If you haven't, then this must
be a pretty strange table --- how many columns has it got?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Ed L.]

On Wednesday September 29 2004 8:59, Tom Lane wrote:

"Ed L." <pg***@bluepolk a.net> writes:

Either increase your per-process memory limit, or reduce the
statistics targets for this table ...

What am I missing?

$ ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) 131072

^^^^^^^^^^^^^^^ ^^^^^^^^^^^^

This is where the limit is coming from ...

$ psql -c "select name, setting from pg_settings" | egrep stats_
stats_block_lev el | off

Those are not the statistics I'm talking about. I was assuming that
you'd done ALTER TABLE SET STATISTICS somewhere along the line, or
changed default_statist ics_target. If you haven't, then this must
be a pretty strange table --- how many columns has it got?

No ALTER TABLE SET STATISTICS being done here, but I do enable
stats_row_level and stats_block_lev el.

The table has 3 columns, one of which contains huge text values. Yes, barf.

Should I change the data size to 'unlimited'? Or just bump it up? And is
it possible to change the data size limit for the postmaster and backends
without restarting them?

Ed


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

"Ed L." <pg***@bluepolk a.net> writes:

The table has 3 columns, one of which contains huge text values. Yes, barf.

That shouldn't matter --- ANALYZE actually deliberately ignores
very-wide values so as not to run out of memory.

You sure you haven't changed default_statist ics_target?

I'd think you'd uncovered a memory leak in ANALYZE, except that somebody
else would have found any such thing before now. Which PG version are
you using exactly?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Ed L.]

On Wednesday September 29 2004 9:54, Tom Lane wrote:

You sure you haven't changed default_statist ics_target?
Honestly, I don't even know how. How can I check its value to see if
someone else has? What I *do* do is to enable the stats_* stuff in
postgresql.conf , but that's it.
I'd think you'd uncovered a memory leak in ANALYZE, except that somebody
else would have found any such thing before now. Which PG version are
you using exactly?

7.3.4. If it were a leak, this is where I'd expect to find it (massive text
table, frequent updates).

Ed
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

> Honestly, I don't even know how. How can I check its value to see if

someone else has?

show default_statist ics_target;

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Ed L.]

On Wednesday September 29 2004 10:18, Tom Lane wrote:

Honestly, I don't even know how. How can I check its value to see if
someone else has?

$ psql -c "show default_statist ics_target;"
default_statist ics_target
---------------------------
1000
(1 row)

Does that look like its been changed? I know I haven't changed this before.

Ed

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster