Thread: Re: VACUUM ANALYZE extremely slow

A long time ago, in a galaxy far, far away, Sergei Shelukhin <realgeek@gmail.com> wrote:
> This is my first (and, by the love of the God, last) project w/pgsql
> and everything but the simplest selects is so slow I want to cry.
> This is especially bad with vacuum analyze - it takes several hours
> for a database of mere 15 Gb on a fast double-core server w/2Gb of RAM
> and virtually no workload at the moment. Maintenance work mem is set
> to 512 Mb.
>
> Is there any way to speed up ANALYZE? Without it all the queries run
> so slow that I want to cry after a couple of hours of operation and
> with it system has to go down for hours per day and that is
> unacceptable.
>
> The same database running on mysql on basically the same server used
> to run optimize table on every table every half an hour without any
> problem, I am actually pondering scraping half the work on the
> conversion and stuff and going back to mysql but I wonder if there's
> some way to improve it.

That does seem surprisingly slow fo that sort of hardware.

One thing I would suggest: Try JUST doing ANALYZE, with no VACUUM
involved.

That will merely do statistical sampling on the tables, and should
complete quite quickly, even for large tables.

That ought to fix the statistics problem.

Those tables that see frequent UPDATE/DELETE requests will still need
to be vacuumed, but fixing the stats doesn't require this.
--
let name="cbbrowne" and tld="gmail.com" in String.concat "@" [name;tld];;
http://linuxdatabases.info/info/postgresql.html
"Learning is weightless,  a treasure you  can always carry easily."
-- Chinese Proverb

Sergei Shelukhin <realgeek@gmail.com> wrote:
> This is my first (and, by the love of the God, last) project w/pgsql
> and everything but the simplest selects is so slow I want to cry.

Please post an example query and its EXPLAIN ANALYZE output.  The
pgsql-performance mailing list is a good place to discuss performance
problems.

> This is especially bad with vacuum analyze - it takes several hours
> for a database of mere 15 Gb on a fast double-core server w/2Gb of RAM
> and virtually no workload at the moment. Maintenance work mem is set
> to 512 Mb.

What other non-default configuration settings do you have?  What
version of PostgreSQL are you using and on what OS?  What kind of
disks and controllers do you have?

> Is there any way to speed up ANALYZE? Without it all the queries run
> so slow that I want to cry after a couple of hours of operation and
> with it system has to go down for hours per day and that is
> unacceptable.

Why does the system have to go down?  Are you running VACUUM FULL
ANALYZE?  If so then drop the FULL and do an ordinary VACUUM ANALYZE
instead -- it should run faster and it doesn't require exclusive
access to the table.

As Christopher Browne mentioned, a bare ANALYZE (without VACUUM)
should be fast even on large tables so if necessary you could run
ANALYZE more often than VACUUM ANALYZE.

Have you enabled autovacuum (or contrib/pg_autovacuum in 8.0 and
earlier)?  I sometimes prefer to run VACUUM ANALYZE manually but
for many databases autovacuum is a good way to maintain statistics
and clean up dead rows automatically.

--
Michael Fuhr