PGSQL Error!

[Anon]

Warning: Unable to connect to PostgreSQL server: Sorry, too many clients already in /usr/home/fast/config/auth.php on line 25

I am using pg_pconnect and not using pg_close, as some in #php on EFNET have asked already. Should I be using pg_connect and pg_close? Is there a standard of when to use pg_pconnect vs pg_connect?

Any and all info will be greatly appreciated.

Thanks,
-Ed

[kparker]

pg_pconnect() has known problems. Change to pg_connect() and see if your problems go away; mine certainly did.

In the average script, using pg_close() is pretty much a matter of personal choice regarding style and housekeeping. Just like closing file descriptors with fclose(), some will insist on it, while others will say, "In two lines my program is exiting, at which point all that stuff will be closed automatically anyway!"

The one case where pg_close() is when you're done using the database, but the script might run for quite a while longer (which, in webserver terms, could be as little as a second or two.) In this case, closing the connection frees it up for another user.

Finally, 25 connections is not very many if you're expecting any load of traffic. You should see about increasing that, and some of the other performance-related postgresql back end configuration items (buffer count and size, etc.)

[smarlowe]

pg_pconnect should only be used on a machine that's been properly configured for it. To do so, first recompile postgresql with the --with-maxbackends switch set to some decently high number like 256 or 512 or something. Then edit the postgresql.conf file (for 7.1) to tell the database to handle a hundred or more connections.

Next, set apache to have fewer maximum children than postgresql can have. I.e. if you set up postgresql to hand 128 connections, set apache for 100 max children.

Finally, put a 'killall -1 httpd' (on linux, not solaris, there killall is another, much more deadly command) into your crontab to make the machine graciously restart all conenctions every hour or so and get rid of hung backends that are tied to httpd children.

[kparker]

... killall ....

That seems kind of complicated. Are you sure the performance gains are worth all that plus the additional memory the pconnect versions consume.

[smarlowe]

Actually, no, it's probably not worth it. I've benchmarked connection times, and on most computers you can connect about 1,000 times a second, or using pconnects, about 100,000 to 1,000,000 times a second. Either way, your queries are much more likely to be your most expensive part of database interaction, not connections.