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.

At the time we upgraded the hardware and the performance problems
went away. But I did not feel like we had solved the underlying problem.

Now, six months later, the same thing is happening... and I'm kind of
glad because now, I'd like to find out what the real issue is. I'm
just starting to diagnose it so I don't know a lot yet, but what I do
know, I'll share with you here in the hopes of starting off on the
right track.

I've already described the main symptom. Here are some other random
observations:
- The server log shows frequent "archived transaction log file"
entries. Usually once every 10 minutes or so, but sometimes 2 or 3
per minute.
- The server box seems otherwise to be responsive. CPU sits at about
90% idle.
- When queries are especially slow, the server shows a big spike in
read/write activity.
- This morning I did a VACUUM ANALYZE. It seemed to help for 30
minutes or so, but then it was back to being slowish. I'd hate to
schedule these because it feels more like a band-aid. For a long time
we've been doing just fine with autovacuum, so why start scheduling
vacuums now?

Here's info about our configuration. Any advise/pointers would be
much appreciated. Thanks!

Computer: Mac Pro Dual Core Intel
Operating System: Mac OS 10.4.7 Client
Memory: 4GB RAM
Data Drives: 3 drives in a software RAID (internal)
Log/Backup Drive: 1 (the startup disk, internal)

Postgres Version: 8.1.4
Data Size: 5.1 GB
# of Tables: 60
Size of Tables: Most are under 100,000 records. A few are in the
millions. Largest is 7058497.
Average Number of Simultaneous Client Connections: 250

max_connections = 500
shared_buffers = 10000
work_mem = 2048
max_stack_depth = 6000
effective_cache_size = 30000
fsync = on
wal_sync_method = fsync
archive_command = 'cp -i %p /Users/postgres/officelink/wal_archive/%f
</dev/null'
max_fsm_pages = 150000
stats_start_collector = on
stats_row_level = on
log_min_duration_statement = 2000
log_line_prefix = '%t %h '
superuser_reserved_connections = 3
autovacuum = on
autovacuum_naptime = 60
autovacuum_vacuum_threshold = 150
autovacuum_vacuum_scale_factor = 0.00000001
autovacuum_analyze_scale_factor = 0.00000001

sudo pico /etc/rc
sysctl -w kern.sysv.shmmax=4294967296
sysctl -w kern.sysv.shmall=1048576

sudo pico /etc/sysctl.conf
kern.maxproc=2048
kern.maxprocperuid=800
kern.maxfiles=40000
kern.maxfilesperproc=30000

Processes: 470 total, 2 running, 4 stuck, 464 sleeping... 587
threads 13:34:50
Load Avg: 0.45, 0.34, 0.33 CPU usage: 5.1% user, 5.1% sys,
89.7% idle
SharedLibs: num = 157, resident = 26.9M code, 3.29M data, 5.44M
LinkEdit
MemRegions: num = 15307, resident = 555M + 25.5M private, 282M shared
PhysMem: 938M wired, 934M active, 2.13G inactive, 3.96G used,
43.1M free
VM: 116G + 90.1M 1213436(0) pageins, 263418(0) pageouts

PID COMMAND %CPU TIME #TH #PRTS #MREGS RPRVT RSHRD
RSIZE VSIZE
29804 postgres 0.0% 0:03.24 1 9 27 1.27M 245M
175M 276M
29720 postgres 0.0% 0:01.89 1 9 27 1.25M 245M
125M 276M
29714 postgres 0.0% 0:03.70 1 10 27 1.30M 245M
215M 276M
29711 postgres 0.0% 0:01.38 1 10 27 1.21M 245M
107M 276M
29707 postgres 0.0% 0:01.27 1 9 27 1.16M 245M
78.2M 276M
29578 postgres 0.0% 0:01.33 1 9 27 1.16M 245M
67.8M 276M
29556 postgres 0.0% 0:00.39 1 9 27 1.09M 245M
91.8M 276M
29494 postgres 0.0% 0:00.19 1 9 27 1.05M 245M
26.5M 276M
29464 postgres 0.0% 0:01.98 1 9 27 1.16M 245M
88.8M 276M
29425 postgres 0.0% 0:01.61 1 9 27 1.17M 245M
112M 276M
29406 postgres 0.0% 0:01.42 1 9 27 1.15M 245M
118M 276M
29405 postgres 0.0% 0:00.13 1 9 26 924K 245M
17.9M 276M
29401 postgres 0.0% 0:00.98 1 10 27 1.13M 245M
84.4M 276M
29400 postgres 0.0% 0:00.90 1 10 27 1.14M 245M
78.4M 276M
29394 postgres 0.0% 0:01.56 1 10 27 1.17M 245M
111M 276M

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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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/

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

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)

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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/conte...pg-5minute.htm for more
information on this topic.

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings