Re: Why will vacuum not end? - Mailing list pgsql-performance

From Shea,Dan [CIS]
Subject Re: Why will vacuum not end?
Date
Msg-id F2D63B916C88C14D9B59F93C2A5DD33F0B9254@cisxa.cmc.int.ec.gc.ca
Whole thread Raw
In response to Why will vacuum not end?  ("Shea,Dan [CIS]" <Dan.Shea@ec.gc.ca>)
Responses Re: Why will vacuum not end?
List pgsql-performance
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

pgsql-performance by date:

Previous
From: Andrew McMillan
Date:
Subject: Re: Wierd context-switching issue on Xeon patch for 7.4.1
Next
From: Manfred Koizar
Date:
Subject: Number of pages in a random sample (was: query slows down with more accurate stats)