Re: performance config help

> So, pgBouncer is pretty good. It doesn't appear to be as good as
> limiting TCON and using pconnect, but since we can't limit TCON in a
> production environment, we may not have a choice.

Actually, you can : use lighttpd and php/fastcgi.

Lighttpd handles the network stuff, and funnels/queues any number of
client connections into a limited number of PHP fastcgi processes. You can
configure this process pool to your tastes.

Rather than instanciating 1 PHP interpreter (and 1 postgres) per client
connection, you can set it up for a max of N PHP procs. If PHP waits a lot
on IO (you use url fopen, that kind of things) you can set N=5..10 per
core, but if you don't use that, N=2-3 per core is good. It needs to be
tuned to your application's need.

The idea is that if you got enough processes to keep your CPU busy,
adding more will just fill your RAM, trash your CPU cache, add more
context swithes, and generally lower your total throughput. Same is true
for Postgres, too.

I've switched from apache to lighttpd on a rather busy community site and
the difference in performance and memory usage were quite noticeable.
Also, this site used MySQL (argh) so the occasional locking on some MyISAM
tables would become really itchy unless the number of concurrent processes
was kept to a manageable level.

When you bring down your number of postgres processes to some manageable
level (plot a curve of throughput versus processes and select the
maximum), if postgres still spends idle time waiting for locks, you'll
need to do some exploration :

- use the lock view facility in postgres
- check your triggers : are you using some trigger that updates a count
as rows are modified ? This can be a point of contention.
- check your FKs too.
- try fsync=off
- try to put the WAL and tables on a ramdisk.
If you have even a few % iowait, maybe that hides the fact that 1
postmaster is fsyncing and perhaps 10 others are waiting on it to finish,
which doesn't count as iowait...

- recompile postgres and enable lwlock timing