It is set at max_fsm_pages = 1500000 .
We are running a
DELL PowerEdge 6650 with 4 CPU's
Mem: 3611320k av from top.
The database is on a shared device (SAN) raid5, 172 GB.
Qlogic Fibre optic cards(desc: "QLogic Corp.|QLA2312 Fibre Channel Adapter")
connected to the Dell version of an EMC SAN (FC4700 I believe).
I have set vacuum_mem = 917504;
and started another vacuum verbose on the table in question.
Tried to set vacuum_mem to 1114112 and vacuum failed, then tried 917504 and
vacuum started.
PWFPM_DEV=# set vacuum_mem = '1114112';
SET
PWFPM_DEV=# show vacuum_mem;
vacuum_mem
------------
1114112
(1 row)
PWFPM_DEV=# vacuum verbose forecastelement;
INFO: vacuuming "public.forecastelement"
ERROR: invalid memory alloc request size 1140850686
PWFPM_DEV=# set vacuum_mem = 917504;
SET
PWFPM_DEV=# show vacuum_mem;
vacuum_mem
------------
917504
(1 row)
PWFPM_DEV=# select now();vacuum verbose forecastelement;select now();
now
-------------------------------
2004-04-25 01:40:23.367123+00
(1 row)
INFO: vacuuming "public.forecastelement"
I performed a query that used a seqscan
PWFPM_DEV=# explain analyze select count(*) from forecastelement;
QUERY PLAN
----------------------------------------------------------------------------
-------------------------------------------------------------------
Aggregate (cost=16635987.60..16635987.60 rows=1 width=0) (actual
time=13111152.844..13111152.847 rows=1 loops=1)
-> Seq Scan on forecastelement (cost=0.00..15403082.88 rows=493161888
width=0) (actual time=243.562..12692714.422 rows=264422681 loops=1)
Total runtime: 13111221.978 ms
(3 rows)
Dan.
-----Original Message-----
From: Manfred Koizar [mailto:mkoi-pg@aon.at]
Sent: Saturday, April 24, 2004 8:29 PM
To: Shea,Dan [CIS]
Cc: 'Josh Berkus'; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Why will vacuum not end?
On Sat, 24 Apr 2004 15:58:08 -0400, "Shea,Dan [CIS]" <Dan.Shea@ec.gc.ca>
wrote:
>There were defintely 219,177,133 deletions.
>The deletions are most likely from the beginning, it was based on the
>reception_time of the data.
>I would rather not use re-index, unless it is faster then using vacuum.
I don't know whether it would be faster. But if you decide to reindex,
make sure sort_mem is *huge*!
>What do you think would be the best way to get around this?
>Increase vacuum_mem to a higher amount 1.5 to 2 GB or try a re-index
(rather
>not re-index so that data can be queried without soing a seqscan).
Just out of curiosity: What kind of machine is this running on? And
how long does a seq scan take?
>Once the index is cleaned up, how does vacuum handle the table?
If you are lucky VACUUM frees half the index pages. And if we assume
that the most time spent scanning an index goes into random page
accesses, future VACUUMs will take "only" 30000 seconds per index scan.
Servus
Manfred
