Re: VACUUM ANALYZE extremely slow

Sergei Shelukhin 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.
>
>
I noticed you didn't mention your disk subsystem. PostgreSQL tends to
use a fair bit of disk I/O when running vacuum and / or analyze. If you
check with top / iostat while vacuum analyze is running, I'm betting
you'll see a lot of waiting on I/O going on.

You do know those two commands (vacuum and analyze) aren't married
anymore, right? You can run analyze all by itself if you want?

And I hope you're not running vacuum analyze full all the time, cause
there's usually no need for that.

Look up pg_autovacuum. Saves a lot of har pulling.
> Is there any way to speed up ANALYZE?
Analyze is usually REALY fast. Even on my rather pokey workstation,
with a single SATA hard drive and other things to do, I can run analyze
on a 31 Gig database in
> 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.
>
You should only need to run analyze every so often. You should only
need vacuum after lots of updates / deletes. You should not need to
take the system down to vacuum, as vacuum doesn't block. Vacuum full
does block, but if you need that you either aren't vacuuming often
enough or you don't have the autovacuum daemon configured.
> 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.
>
And when you ran optimize on those tables, were they not locked for
regular users the whole time?

There may be a way to improve it. Tell us, what OS are you running,
what are your non-default postgresql.conf settings, what ACTUAL commands
are you running here? Vacuum, vacuum analyze, vacuum full analyze? Are
you inserting / deleting / updating tons of rows between vacuums and /
or analyzes?

>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>