X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 8FC6AD1EAA2 for ; Tue, 20 Apr 2004 22:51:23 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 16846-08 for ; Tue, 20 Apr 2004 22:51:19 -0300 (ADT) Received: from stubee.d2hosting.net (d2hosting.net [66.70.41.160]) by svr1.postgresql.org (Postfix) with ESMTP id C80EFD1ECD2 for ; Tue, 20 Apr 2004 12:49:29 -0300 (ADT) Received: from idigx.com (stubee.d2hosting.net [66.70.41.160]) by stubee.d2hosting.net (8.11.6/linuxconf) with ESMTP id i3KFnLM24882; Tue, 20 Apr 2004 10:49:26 -0500 Message-ID: <40854698.6070805@idigx.com> Date: Tue, 20 Apr 2004 10:49:44 -0500 From: Thomas Swan User-Agent: Mozilla Thunderbird 0.6a (Windows/20040419) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Randolf Richardson Cc: pgsql-performance@postgresql.org Subject: Re: Persistent Connections References: <20040124173209.933905A8170@kserver17.erfurt12.de> <4013A85C.1010406@chuckie.co.uk> In-Reply-To: X-Enigmail-Version: 0.83.6.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200404/325 X-Sequence-Number: 6625 Randolf Richardson wrote: >"nicky@chuckie.co.uk (Nick Barr)" stated in >comp.databases.postgresql.performance: > > > >>postgres@countup.de wrote: >> >> >[sNip] > > >>>Sorry I m a little bit confused about the persistent thing!! >>>Is it smart to use persistent connections at all if i expect 100K >>>Users to hit the script in an hour and the script calls up to 10-15 pg >>>functions? >>>I have at the mom one function but the server needs 500 ms, its a >>>little bit too much i think, and it crashed when i had 20K users >>> >>> >>Use the persistent connection but make sure the parameters in >>postgresql.conf match up with the Apache config. The specific settings >>are MaxClients in httpd.conf and max_connections in postgresql.conf. >>Make sure that max_connections is at least as big as MaxClients for >>every database that your PHP scripts connect to. >> >> > > Do you happen to have (or know where to get) some sample configuration >files for Apache 2 and PostgreSQL for this? The documentation I've found >so far is pretty sparse, and sample files would be very helpful. > > > > Beware that persistent connections in PHP behave a little differently than you would think. The connections stays open between an apache process and postgres. So each process has its own connection and you may not hit the same process on each request to the apache server. Temporary tables are not dropped automatically between refreshes on persistent connections. An example of this is to enable persistent connections and execute "CREATE TEMPORARY TABLE foo ( id INTEGER );" $conn = pg_pconnect( ... ); if (!$result = pg_query($conn, "CREATE TEMPORARY TABLE tmp_foo ( id INTEGER );")) { echo pg_result_error($result) ; } else { echo "created ok!"; } After a couple of refreshes you will get an error that states the table already exists. This was a pain to learn, especially while I was doing these operations inside of transactions. On most of my servers the connect time for postgresql was 6ms or less, so I disabled persistent connections altogether so that I could be assured that temporary tables and all php launched postgresql sessions were properly reset. As far as I know, there is no way to reset the sesssion ( cleaning up temporary tables, etc ) automatically with an SQL statement without closing the connection