Re: VACUUM ANALYZE extremely slow

On Sun, 17 Jun 2007, Sergei Shelukhin wrote:

> 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.

I've found I cry a lot less if I actually spend a minute educating myself
about things instead of complaining to a mailing list in a fashion
offensive to the people who might help me.  You should start with
http://www.postgresql.org/docs/current/static/sql-vacuum.html where you'll
discover the amazing fact that VACUUM ANALYZE doesn't require taking down
the system at all.  The way you get it to take less time is to do it more
often.  In fact, if you're using a PostgreSQL version where you can set up
auto-vacuum correctly, you might not ever need to run it manually at all.

Here are the things to consider if you actually want some help here:

0) You might as well keep this threading going, but next time, post to the
performance list instead of the general one; it's more appropriate and
you'll get a better mix of people familiar with this sort of topic.

1) Give some more details about the non-default values in your
postgresql.conf file.
http://www.westnet.com/~gsmith/content/postgresql/pg-5minute.htm will get
you started on the most important ones to worry about and pointers to
additional resources.  You say you've increased the maintenance working
mem; that's a good start, but if everything else is at the default you're
not taking advantage of all the memory in your system.  MySQL manages
memory very differently, and those differences may be the root cause of
your issue.

2) State what version of PostgreSQL you're running.  If it's older than a
recent 8.1 release, there are many known and unresolvable performance
issues and you may have to upgrade to get what you're looking for.

3) Give some information about your disk configuration, and some tests
results to confirm they're working normally if possible.
http://www.westnet.com/~gsmith/content/postgresql/pg-disktesting.htm has
an outline of how to do those tests.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD