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@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
