Обсуждение: slow vacuuming...

Hi,

    I'm running into major problems with slow vacuuming.  I'm running Postgres
6.5.3 on a Sun Enterprise 250 with dual 300 CPUs and 1 GB of ram.  Currently
there is only one db/table being used in postgres and it's not that big a data
structure (used for PHP session info with IMP).
------------------------------------

horde=> \d active_sessions;
Table    = active_sessions
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| sid                              | varchar() not null default ''    |    32 |
| name                             | varchar() not null default ''    |    32 |
| val                              | text                             |   var |
| changed                          | varchar() not null default ''    |    14 |
+----------------------------------+----------------------------------+-------+

----------------------------------------------

Each day the DB grows to ~70MB in size and takes upwards of 25 minutes to
vacuum.  A 70MB DB doesn't sound too big and 25 minutes is a painfully long time
to wait.

Any ideas?

David



--

David Powicki     Network Analyst/Postmaster     OIT Network Services
Voice: 413.545.1605  Fax: 413.545.3203    University of Massachusetts
email: dpowicki@nic.umass.edu             Amherst, MA 01003-4640

That does seem like a long time...  If you have any indexes (I don't
remember if \d in 6.5 showed those) you might want to try dropping them,
doing the vacuum and recreating them and see if that's any faster.

Stephan Szabo
sszabo@bigpanda.com

On Mon, 13 Nov 2000, David Powicki wrote:

> Each day the DB grows to ~70MB in size and takes upwards of 25 minutes to
> vacuum.  A 70MB DB doesn't sound too big and 25 minutes is a painfully long time
> to wait.
>
> Any ideas?

On Mon, 13 Nov 2000, Stephan Szabo wrote:

> On Mon, 13 Nov 2000, David Powicki wrote:
>
> > Each day the DB grows to ~70MB in size and takes upwards of 25 minutes to
> > vacuum.  A 70MB DB doesn't sound too big and 25 minutes is a painfully long time
> > to wait.

I have had the same problem; I think it only occurs if you have any BLOB
field. In my case, vacuum on a ~100MB table took around 40 minutes (on a
dual PII 450, 256MB RAM, all 10,000RPM SCSI HDDs).

Here's what I did; I created a script that rather than just running vacuum
did the followings:

create a temp table identical to the large table
insert into temp (select * from old);
drop old
vacuum
create the old table
insert into old (select * from temp);
drop temp
vacuum

You can ditch the last vacuum if you wish (I was just being pedantic :-)
This whole thing took less than a minute on the same machine!!!


cheers,
Hossein