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(1189)

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
TopMemoryContext: 32792 total in 4 blocks; 11664 free (23 chunks); 21128 used
TopTransactionContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
DeferredTriggerXact: 0 total in 0 blocks; 0 free (0 chunks); 0 used
TransactionCommandContext: 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
DeferredTriggerSession: 0 total in 0 blocks; 0 free (0 chunks); 0 used
PortalMemory: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
CacheMemoryContext: 516096 total in 6 blocks; 170872 free (1 chunks); 345224 used
pg_index_indrelid_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_tables: 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_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_shadow_usesysid_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_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_opclass_am_name_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_name_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_type_oid_index: 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_strategy_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_amproc_opc_procnum_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_class_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_class_relname_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_relid_attnam_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_conversion_default_index: 2072 total in 2 blocks; 712 free (0 chunks); 1360 used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_namespace_oid_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_relid_attnum_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_shadow_usename_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_namespace_nspname_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_inherits_relid_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_name_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_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_operator_oprname_l_r_n_index: 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(1189)
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***@bluepolka.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(1189) 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***@bluepolka.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(1189)

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(descriptors) 120

$ psql -c "select name, setting from pg_settings" | egrep stats_
stats_block_level | off
stats_command_string | off
stats_reset_on_server_start | on
stats_row_level | off
stats_start_collector | on

$ psql -c "analyze audit"
ERROR: Memory exhausted in AllocSetAlloc(1189)
---------------------------(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***@bluepolka.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(1189)

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_level | off
stats_command_string | off
stats_reset_on_server_start | on
stats_row_level | off
stats_start_collector | on

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

We actually count on those stats (stats_row_level?) 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 YourEmailAddressHere" to ma*******@postgresql.org)

[Tom Lane]

"Ed L." <pg***@bluepolka.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_level | 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_statistics_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***@bluepolka.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_level | 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_statistics_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_level.

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***@bluepolka.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_statistics_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_statistics_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*******@postgresql.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_statistics_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_statistics_target;"
default_statistics_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

[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?

show default_statistics_target;

Let me add this: I'm seeing this error show up across multiple clusters,
maybe 5 or 6, all with similar schemas, on 2 different hpux boxes, all
running 7.3.4.

Ed
---------------------------(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***@bluepolka.net> writes:

On Wednesday September 29 2004 10:18, Tom Lane wrote:
$ psql -c "show default_statistics_target;"
default_statistics_target
---------------------------
1000
(1 row) Does that look like its been changed?

Uh ... the default is 10.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Murali Mohan Kasetty]

Hi All,

Please let me know how do I unsbscribe to this newsgroup?

Thanks,
Murali

-----Original Message-----
From: pg*****************@postgresql.org
[mailto:pg*****************@postgresql.org] On Behalf Of Ed L.
Sent: Wednesday, September 29, 2004 9:21 PM
To: Tom Lane
Cc: pg***********@postgresql.org
Subject: Re: [GENERAL] 7.3.4 vacuum/analyze error
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_statistics_target;"
default_statistics_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


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

[Murali Mohan Kasetty]

Hi All,
Please let me know How do I unsbscribe from this news group?

Thanks,
Murali

-----Original Message-----
From: pg*****************@postgresql.org
[mailto:pg*****************@postgresql.org] On Behalf Of Ed L.
Sent: Wednesday, September 29, 2004 9:23 PM
To: Tom Lane
Cc: pg***********@postgresql.org
Subject: Re: [GENERAL] 7.3.4 vacuum/analyze error
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?

show default_statistics_target;

Let me add this: I'm seeing this error show up across multiple clusters,
maybe 5 or 6, all with similar schemas, on 2 different hpux boxes, all
running 7.3.4.

Ed
---------------------------(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


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

[Ed L.]

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

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

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

Can you explain a little more of how you interpret the numbers above to draw
your conclusion?

Ed
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Alvaro Herrera]

On Wed, Sep 29, 2004 at 11:23:52PM -0600, Ed L. wrote:

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

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

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

Can you explain a little more of how you interpret the numbers above to draw
your conclusion?

I just means that, from the operating system POV, there are 132263832
bytes in use. The rest is Pg internal bookkeeping that doesn't help you
any.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Uno combate cuando es necesario... ¡no cuando está de humor!
El humor es para el ganado, o para hacer el amor, o para tocar el
baliset. No para combatir." (Gurney Halleck)
---------------------------(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]

Alvaro Herrera <al******@dcc.uchile.cl> writes:

On Wed, Sep 29, 2004 at 11:23:52PM -0600, Ed L. wrote:

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

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

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 ...
Can you explain a little more of how you interpret the numbers above to draw
your conclusion?

I just means that, from the operating system POV, there are 132263832
bytes in use. The rest is Pg internal bookkeeping that doesn't help you
any.

Well, the other potentially interesting deduction is that the failure
came during a request for a small additional amount of memory, viz 1189
bytes. Had the AllocSetAlloc call been requesting hundreds of megs,
my thoughts would have turned to corrupt data (specifically a broken
field-length word) instead of memory exhaustion per se.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org