pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedVacuum takes more than 1 hr
4+ messages / 3 participants
[nested] [flat]
* Vacuum takes more than 1 hr
@ 1999-10-25 10:52 Postgres <postgres@weblynk.com>
0 siblings, 1 reply; 4+ messages in thread
From: Postgres @ 1999-10-25 10:52 UTC (permalink / raw)
To: pgsql-sql
I have a table with about 30 columns and 200K rows. When I perform a vacuum
on it (no analyze) it takes usually a little more than 1 hour. Since I do it
everyday, for me, this basically means my database, which is now getting
about a million queries a month, is down for an hour every day, since all
selects to that table wait until the vacuum is done. I have smaller tables,
10 columns, 5K rows, which see, to be very quick with vacuum. Any ideas on
how to speed vacuum up?
Thanks,
Rich
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] Vacuum takes more than 1 hr
@ 1999-10-25 14:45 Tom Lane <tgl@sss.pgh.pa.us>
parent: Postgres <postgres@weblynk.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 1999-10-25 14:45 UTC (permalink / raw)
To: Postgres <postgres@weblynk.com>; +Cc: pgsql-sql
Postgres <postgres@weblynk.com> writes:
> I have a table with about 30 columns and 200K rows. When I perform a vacuum
> on it (no analyze) it takes usually a little more than 1 hour.
Hmm, doesn't seem all that large. One question is whether you are all
the way up-to-date on Postgres --- some performance problems in vacuum
were fixed recently. (The problems were excessive memory usage,
actually, but that could translate to long runtime if the process
started to swap. Does the backend that's doing the vacuum seem to grow
to a size much larger than it starts at?) I don't recall whether this
patch is in 6.5.2 or not, but it will be in 6.5.3, or you could pull
the current REL6_5 branch sources from the CVS server.
A performance problem that still remains is that vacuum seems
unreasonably slow at updating indexes. Some people have found that
dropping and recreating indexes around a vacuum nets out faster than
letting vacuum do it. You should also ask yourself whether each
index on the table is earning its keep --- each one costs time on
every insert or update, quite aside from vacuum. Only the indexes
that actually get used for your common queries are likely to be worth
their overhead.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] Vacuum takes more than 1 hr
@ 1999-10-25 15:36 Rich Ryan <postgres@weblynk.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Rich Ryan @ 1999-10-25 15:36 UTC (permalink / raw)
To: pgsql-sql
Instead of using CVS, will ftp'ing the postgresql.snapshot.tar.gz file do? I
looked around for an explanation of what this file is, but didn't find any.
----- Original Message -----
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Postgres <postgres@weblynk.com>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgreSQL.org>
Sent: Monday, October 25, 1999 7:45 AM
Subject: Re: [SQL] Vacuum takes more than 1 hr
> Postgres <postgres@weblynk.com> writes:
> > I have a table with about 30 columns and 200K rows. When I perform a
vacuum
> > on it (no analyze) it takes usually a little more than 1 hour.
>
> Hmm, doesn't seem all that large. One question is whether you are all
> the way up-to-date on Postgres --- some performance problems in vacuum
> were fixed recently. (The problems were excessive memory usage,
> actually, but that could translate to long runtime if the process
> started to swap. Does the backend that's doing the vacuum seem to grow
> to a size much larger than it starts at?) I don't recall whether this
> patch is in 6.5.2 or not, but it will be in 6.5.3, or you could pull
> the current REL6_5 branch sources from the CVS server.
>
> A performance problem that still remains is that vacuum seems
> unreasonably slow at updating indexes. Some people have found that
> dropping and recreating indexes around a vacuum nets out faster than
> letting vacuum do it. You should also ask yourself whether each
> index on the table is earning its keep --- each one costs time on
> every insert or update, quite aside from vacuum. Only the indexes
> that actually get used for your common queries are likely to be worth
> their overhead.
>
> regards, tom lane
>
> ************
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] Vacuum takes more than 1 hr
@ 1999-10-25 15:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rich Ryan <postgres@weblynk.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 1999-10-25 15:55 UTC (permalink / raw)
To: Rich Ryan <postgres@weblynk.com>; +Cc: pgsql-sql
Rich Ryan <postgres@weblynk.com> writes:
> Instead of using CVS, will ftp'ing the postgresql.snapshot.tar.gz file do? I
> looked around for an explanation of what this file is, but didn't find any.
That's a nightly snapshot of current development sources --- not of the
stable REL6_5 branch, which is what you probably want. I thought Marc
had made up a beta-test tarball of 6.5.3 recently, but I don't see one
on the FTP server...
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~1999-10-25 15:55 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-10-25 10:52 Vacuum takes more than 1 hr Postgres <postgres@weblynk.com>
1999-10-25 14:45 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-10-25 15:36 ` Rich Ryan <postgres@weblynk.com>
1999-10-25 15:55 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox