Thread: Getting Slow

About six months ago, our normally fast postgres server started having performance issues. Queries that should have been instant were taking up to 20 seconds to complete (like selects on the primary key of a table). Running the same query 4 times in a row would yield dramatically different results... 1.001 seconds, 5 seconds, 22 seconds, 0.01 seconds, to complete.

On Thu, Jun 07, 2007 at 01:48:43PM -0400, Joe Lester wrote:
> of a table). Running the same query 4 times in a row would yield
> dramatically different results... 1.001 seconds, 5 seconds, 22
> seconds, 0.01 seconds, to complete.

> - When queries are especially slow, the server shows a big spike in
> read/write activity.

My bet is that you're maxing your disk subsystem somehow.  The
problem with being I/O bound is that it doesn't matter how great you
do on average: if you have too much I/O traffic, it looks like you're
stopped.  Softraid can be expensive -- first thing I'd look at is to
see whether you are in fact hitting 100% of your I/O capacity and, if
so, what your options are for getting more room there.

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
"The year's penultimate month" is not in truth a good way of saying
November.
        --H.W. Fowler

On Thu, Jun 07, 2007 at 01:48:43PM -0400, Joe Lester wrote:
> - The server log shows frequent "archived transaction log file"
> entries. Usually once every 10 minutes or so, but sometimes 2 or 3
> per minute.

Sounds like you've got a lot of writes going. You might want more power in
your I/O?

> Operating System: Mac OS 10.4.7 Client

Is there a particular reason for this? It's not known to be the best server
OS around -- it's hard to say that an OS change would do anything for your
problem, but it looks like an unusual choice.

/* Steinar */
--
Homepage: http://www.sesse.net/

Joe Lester wrote:

> max_fsm_pages = 150000

This may be a bit too low -- it's just a little more than 1 GB, which
means it might fail to keep track of all your tables (or it may not, if
you don't have many updates).

> autovacuum_naptime = 60
> autovacuum_vacuum_threshold = 150
> autovacuum_vacuum_scale_factor = 0.00000001
> autovacuum_analyze_scale_factor = 0.00000001

The scale factors seems awfully low.  How about 0.01 instead and see if
you avoid vacuuming all your tables with every iteration ... have you
noticed how much work autovacuum is really doing?  It may be too much.

Also if autovacuum is eating all your I/O you may want to look into
throttling it back a bit by setting autovacuum_vacuum_cost_delay to a
non-zero value.

--
Alvaro Herrera                        http://www.advogato.org/person/alvherre
"La tristeza es un muro entre dos jardines" (Khalil Gibran)

On Thu, 7 Jun 2007, Joe Lester wrote:

> Memory: 4GB RAM
>
> shared_buffers = 10000
> work_mem = 2048
> effective_cache_size = 30000

With these parameters, your server has 80MB dedicated to its internal
caching, is making query decisions assuming the operating system only has
240MB of memory available for its caching, and is only allowing individual
clients to have a tiny amount of memory to work with before they have to
swap things to disk.  You're not giving it anywhere close to enough memory
to effectively work with a 5GB database, and your later reports show
you're barely using 1/2 the RAM in this system usefully.

Multiply all these parameters by 10X, restart your server, and then you'll
be in the right ballpark for a system with 4GB of RAM.  There might be
some other tuning work left after that, but these values are so far off
that until you fix them it's hard to say what else needs to be done.  See
http://www.westnet.com/~gsmith/content/postgresql/pg-5minute.htm for more
information on this topic.

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

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Also if autovacuum is eating all your I/O you may want to look into
> throttling it back a bit by setting autovacuum_vacuum_cost_delay to a
> non-zero value.

BTW, why is it that autovacuum_cost_delay isn't enabled by default?
I can hardly believe that anyone will want to run it without that.
*Especially* not with multiple workers configured by default.

            regards, tom lane

Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > Also if autovacuum is eating all your I/O you may want to look into
> > throttling it back a bit by setting autovacuum_vacuum_cost_delay to a
> > non-zero value.
>
> BTW, why is it that autovacuum_cost_delay isn't enabled by default?
> I can hardly believe that anyone will want to run it without that.
> *Especially* not with multiple workers configured by default.

Just because we haven't agreed a value.  Default autovacuum parameters
is something we should definitely discuss for 8.3.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support