PgSQL can't accept too much connections

Anon

Hi guys,

We just did a stress test on our web server. Approximately 30 minutes into the test, when number of concurrent users (60 users at this time) are increased, PHP would not connect to PostgreSQL.

Why does PosgreSQL shuts itself down for additional connections?

Any help would be much appreciated.
Joe

Anon

when you start postgres, what is the max number of connections you give it ?? (this setting can be found in postgresql.conf file, or set at run-time with the -N option)

This is probably your problem.

Anon

Keith

The max number of connections is 32. Can I increase the number of connections? How many of connections are ideal?

And BTW I'm using persistent connections.

Thanks.
Joe

Anon

Well what you should increase it to depends on your machine's capabilities (the kernel configuration - it will tell you what to do if you try to raise it beyond what it can handle)...as well as how many max users are you expecting ??

I've had many problems with postgres/php persistent connections and am now using normal connect with no trouble, you might have to do the same (if you do, turn keepalive off in httpd.conf).

Hope that helps

smarlowe

Keith is right. You need to know how to configure your kernel to support a program that will be opening as many connections as apache / postgresql / php will be in this configuration.

Here's the short version.

You need to increase shmall and shmmax, as well as inode-max and file-max (except on 2.4.x kernels, where inode-max is handled by the kernel.)

You need to recompile postgresql to handle a few hundred backends. The max in the default build is 32.

the --with-maxbackends=nn switch lets you do that.

Next, configure the $PGDATA/postgresql.conf file to tell postgresql to come up with a hundred or so connections and configure httpd.conf to have a max clients of 10 or so less than what you set postgresql to in the postgresql.conf file.

Restart everything and good luck, but know that persistant connections can create as many headaches as they prevent...

Anon

First of all I would really like to thank you and Keith for offering the help.

It seems like a very complex job to me but I think I still can handle it. Do you have any specific articles on these?

And how many connections is ideal for a medium traffic site?

Lastly, I would not have to fiddle with all of these if I were to use non-persistent connections, would I?

Thanks again for your help.
Joe

Anon

First, what OS are you using ? On FreeBSD, recompiling the kernel is very simple, so making the few changes needed to increase the number of postgres connections you can allow as well as buffers (-B option, should be MINIMUM 2x connection) should take no more than 5 minutes (with a quick reboot).

On other OSes, I'm unfamilar with the kernel recompiling procedure, so you'll have to figure that out yourself.

For a medium traffic site, you should have no problems using pg_connect as opposed to pg_pconnect, the speed difference is really not onticeable (atleast with all my testing on a very high-traffic DB-heavy site).

The best piece of advice I can give you is if your site is slow, or your connections keep opening and not closing: CHECK YOUR QUERIES. The queries are the problem for sure. I've had to go through my whole site EXPLAINing every single query to optimize them as best I can, and now postgres flies. It opens and closes the connections so fast I can't even see the processes on the DB server, since they close too fast. with EXPLAIN you should be able to find what queries cost too much and try to fix them, perhaps you are simply missing an index or two.

Hope that helps - Keith

Anon

Keith,

Thanks for the information but I just came across the Optimizing PostgreSQL article on PHPbuilder http://www.phpbuilder.com/columns/smith20010821.php3?print_mode=1

And I noticed that the author did not recompile the kernel or postgresql in order to increase max_connections??

Thanks for your help.

Joe

Anon

it is not mandatory to rebuild your kernel, it depends on how high you want to set connections...Like Scott mentioned, the default is 32 when you compile postgres, you would need ot recompile to raise it higher than that with the proper option (which i think he gave you)....

If you try a value too high, it will tell you what to change in the kernel and what to change it to and postmaster won't start.

Anon

Keith,

Ok thank you I will toy around with it and let you know the results soon.

Thanks for your help.
Joe

Keith Bussey wrote:

If you try a value too high, it will tell you what to change in the kernel and what to change it to and postmaster won't start.