Thread: 7.3.4 vacuum/analyze error
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
"Ed L." <pgsql@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
On Wednesday September 29 2004 5:17, Tom Lane wrote: > "Ed L." <pgsql@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)
On Wednesday September 29 2004 8:33, Ed L. wrote: > On Wednesday September 29 2004 5:17, Tom Lane wrote: > > "Ed L." <pgsql@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
"Ed L." <pgsql@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
On Wednesday September 29 2004 8:59, Tom Lane wrote: > "Ed L." <pgsql@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
"Ed L." <pgsql@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
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
> 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
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
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
"Ed L." <pgsql@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
Hi All, Please let me know how do I unsbscribe to this newsgroup? Thanks, Murali -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ed L. Sent: Wednesday, September 29, 2004 9:21 PM To: Tom Lane Cc: pgsql-general@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
Hi All,
Please let me know How do I unsbscribe from this news group?
Thanks,
Murali
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ed L.
Sent: Wednesday, September 29, 2004 9:23 PM
To: Tom Lane
Cc: pgsql-general@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
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
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)
Alvaro Herrera <alvherre@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