Thread: Apache + PHP + Postgres Interaction
Hello. I have a subject setup and a few questions. The first one is this. PHP establishes a connection to the Postgres database through pg_pconnect(). Then it runs some query, then the script returns, leaving the persistent connection hanging. But the trouble is that in this case any query takes significantly more time to execute than in the case of one PHP script running the same query with different parameters for N times. How can I achieve the same performance in the first case? Persistent connections help but not enough - the queries are still 10 times slower than they would be on the 2nd time. The second one is that the machine with this setup is dual core Xeon 2.8ghz. I've read somewhere about the switching context problem and bad postgres performance. What are the effects? What are the symptoms? And what will be the performance gain if I change the machine to equal Athlon? Thank you in advance.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Max Zorloff wrote: > Hello. > > I have a subject setup and a few questions. > > The first one is this. PHP establishes a connection to the Postgres > database through pg_pconnect(). Don't use pconnect. Use pgbouncer or pgpool. > Then it > runs some query, then the script returns, leaving the persistent > connection hanging. But the trouble > is that in this case any query takes significantly more time to execute > than in the case of one PHP script > running the same query with different parameters for N times. How can I > achieve the same performance in the first > case? Persistent connections help but not enough - the queries are still > 10 times slower than they would be on > the 2nd time. Well you haven't given us any indication of data set or what you are trying to do. However, I can tell you, don't use pconnect, its broke ;) > > The second one is that the machine with this setup is dual core Xeon > 2.8ghz. I've read somewhere about > the switching context problem and bad postgres performance. What are the > effects? What are the symptoms? You likely do not have this problem if you are running anywhere near a current PostgreSQL release but you can check it with vmstat. > And what will be the performance gain if I change the machine to equal > Athlon? Depends on the work load. Sincerely, Joshua D. Drake > > Thank you in advance. > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGzcEAATb/zqfZUUQRAkkEAKCc00kZu6YSDp1RWjY9zZeQVEYeVACeIsOl hzyHOnynNSNWOrBakMeVKpc= =LL5i -----END PGP SIGNATURE-----
In response to "Joshua D. Drake" <jd@commandprompt.com>: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Max Zorloff wrote: > > Hello. > > > > I have a subject setup and a few questions. > > > > The first one is this. PHP establishes a connection to the Postgres > > database through pg_pconnect(). > > Don't use pconnect. Use pgbouncer or pgpool. > > > Then it > > runs some query, then the script returns, leaving the persistent > > connection hanging. But the trouble > > is that in this case any query takes significantly more time to execute > > than in the case of one PHP script > > running the same query with different parameters for N times. How can I > > achieve the same performance in the first > > case? Persistent connections help but not enough - the queries are still > > 10 times slower than they would be on > > the 2nd time. > > Well you haven't given us any indication of data set or what you are > trying to do. However, I can tell you, don't use pconnect, its broke ;) Broke? How do you figure? I'm not trying to argue the advantages of a connection pooler such as pgpool, but, in my tests, pconnect() does exactly what it's supposed to do: reuse existing connections. In our tests, we saw a 2x speed improvement over connect(). Again, I understand that pgpool will do even better ... Also, I'm curious as to whether he's timing the actual _query_ or the entire script execution. If you're running a script multiple times to get multiple queries, most of your time is going to be tied up in PHP's parsing and startup -- unless I misunderstood the question. -- Bill Moran http://www.potentialtech.com
On Aug 23, 2007, at 12:29 PM, Bill Moran wrote: > In response to "Joshua D. Drake" <jd@commandprompt.com>: > >> -----BEGIN PGP SIGNED MESSAGE----- >> Hash: SHA1 >> >> Max Zorloff wrote: >>> Hello. >>> >>> I have a subject setup and a few questions. >>> >>> The first one is this. PHP establishes a connection to the Postgres >>> database through pg_pconnect(). >> >> Don't use pconnect. Use pgbouncer or pgpool. >> >>> Then it >>> runs some query, then the script returns, leaving the persistent >>> connection hanging. But the trouble >>> is that in this case any query takes significantly more time to >>> execute >>> than in the case of one PHP script >>> running the same query with different parameters for N times. How >>> can I >>> achieve the same performance in the first >>> case? Persistent connections help but not enough - the queries >>> are still >>> 10 times slower than they would be on >>> the 2nd time. >> >> Well you haven't given us any indication of data set or what you are >> trying to do. However, I can tell you, don't use pconnect, its >> broke ;) > > Broke? How do you figure? > > I'm not trying to argue the advantages of a connection pooler such as > pgpool, but, in my tests, pconnect() does exactly what it's supposed > to do: reuse existing connections. In our tests, we saw a 2x speed > improvement over connect(). Again, I understand that pgpool will do > even better ... We were just talking about this less than two weeks ago: http:// archives.postgresql.org/pgsql-general/2007-08/msg00660.php Erik Jones Software Developer | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
On Thu, 23 Aug 2007 13:29:46 -0400 Bill Moran <wmoran@potentialtech.com> wrote: > > Well you haven't given us any indication of data set or what you > > are trying to do. However, I can tell you, don't use pconnect, > > its broke ;) > > Broke? How do you figure? I asked that question earlier this month - this thread has some interesting discussion on pconnect: http://archives.postgresql.org/pgsql-general/2007-08/msg00602.php Josh
On Thu, 23 Aug 2007 21:16:48 +0400, Joshua D. Drake <jd@commandprompt.com> wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Max Zorloff wrote: >> Hello. >> >> I have a subject setup and a few questions. >> >> The first one is this. PHP establishes a connection to the Postgres >> database through pg_pconnect(). > > Don't use pconnect. Use pgbouncer or pgpool. > >> Then it >> runs some query, then the script returns, leaving the persistent >> connection hanging. But the trouble >> is that in this case any query takes significantly more time to execute >> than in the case of one PHP script >> running the same query with different parameters for N times. How can I >> achieve the same performance in the first >> case? Persistent connections help but not enough - the queries are still >> 10 times slower than they would be on >> the 2nd time. > > Well you haven't given us any indication of data set or what you are > trying to do. However, I can tell you, don't use pconnect, its broke ;) The data set is some 400mb database with ~100 SELECT queries running in a second and some 7-10 pl/pgsql functions doing select checks and then 2-3 insert/updates. >> >> The second one is that the machine with this setup is dual core Xeon >> 2.8ghz. I've read somewhere about >> the switching context problem and bad postgres performance. What are the >> effects? What are the symptoms? > > You likely do not have this problem if you are running anywhere near a > current PostgreSQL release but you can check it with vmstat. I have 8.0.13 postgres. How do I check the thing with vmstat? >> And what will be the performance gain if I change the machine to equal >> Athlon? > > Depends on the work load. Right now 100 concurrent users completely use the cpu. So I'm trying to find out where the problem lies.
On Thu, 23 Aug 2007 21:29:46 +0400, Bill Moran <wmoran@potentialtech.com> wrote: > In response to "Joshua D. Drake" <jd@commandprompt.com>: > >> -----BEGIN PGP SIGNED MESSAGE----- >> Hash: SHA1 >> >> Max Zorloff wrote: >> > Hello. >> > >> > I have a subject setup and a few questions. >> > >> > The first one is this. PHP establishes a connection to the Postgres >> > database through pg_pconnect(). >> >> Don't use pconnect. Use pgbouncer or pgpool. >> >> > Then it >> > runs some query, then the script returns, leaving the persistent >> > connection hanging. But the trouble >> > is that in this case any query takes significantly more time to >> execute >> > than in the case of one PHP script >> > running the same query with different parameters for N times. How can >> I >> > achieve the same performance in the first >> > case? Persistent connections help but not enough - the queries are >> still >> > 10 times slower than they would be on >> > the 2nd time. >> >> Well you haven't given us any indication of data set or what you are >> trying to do. However, I can tell you, don't use pconnect, its broke ;) > > Broke? How do you figure? > > I'm not trying to argue the advantages of a connection pooler such as > pgpool, but, in my tests, pconnect() does exactly what it's supposed > to do: reuse existing connections. In our tests, we saw a 2x speed > improvement over connect(). Again, I understand that pgpool will do > even better ... > > Also, I'm curious as to whether he's timing the actual _query_ or the > entire script execution. If you're running a script multiple times > to get multiple queries, most of your time is going to be tied up in > PHP's parsing and startup -- unless I misunderstood the question. > I'm timing it with the php gettimeofday(). And I'm timing the actual pg_query() run time, excluding db connection and everything else.
In response to Josh Trutwin <josh@trutwins.homeip.net>: > On Thu, 23 Aug 2007 13:29:46 -0400 > Bill Moran <wmoran@potentialtech.com> wrote: > > > > Well you haven't given us any indication of data set or what you > > > are trying to do. However, I can tell you, don't use pconnect, > > > its broke ;) > > > > Broke? How do you figure? > > I asked that question earlier this month - this thread has some > interesting discussion on pconnect: > > http://archives.postgresql.org/pgsql-general/2007-08/msg00602.php Thanks to you and Erik for the link. Not sure how I missed that thread. I guess I just feel that "broken" is a bit of a harsh term. If your expectations are for full-blown connection management from pconnect(), then you will be disappointed. If you take it for what it is: persistent connections, then those limitations would be expected. *shrug* I'm just glad there aren't any unknown problems waiting to bite me ... -- Bill Moran http://www.potentialtech.com
Bill Moran wrote: > I guess I just feel that "broken" is a bit of a harsh term. If > your expectations are for full-blown connection management from > pconnect(), then you will be disappointed. If you take it for > what it is: persistent connections, then those limitations would > be expected. It's broken because persistent connections get randomly garbage collected where they should not. So broken in the sense of bugged. Expect connections to die for no reason, especially under load. -- Best regards, Hannes Dorbath
On 24.08.2007 00:43, Hannes Dorbath wrote: > Bill Moran wrote: >> I guess I just feel that "broken" is a bit of a harsh term. If >> your expectations are for full-blown connection management from >> pconnect(), then you will be disappointed. If you take it for >> what it is: persistent connections, then those limitations would >> be expected. > > It's broken because persistent connections get randomly garbage > collected where they should not. So broken in the sense of bugged. > Expect connections to die for no reason, especially under load. How did you verify that? It will spawn a new connection silently, if the old got dropped. Did you really verify your logs, that you don't get more new connections than Apache spawns workers? This might not be noticeable for you, if you are running Apache. In a FCGI environment where you have a fixed amount of workers, you notice new connections, as there should not be any. -- Regards, Hannes Dorbath
> On 24.08.2007 02:43, Bill Moran wrote: >> Hannes Dorbath <light@theendofthetunnel.de> wrote: >>> Bill Moran wrote: >>>> I guess I just feel that "broken" is a bit of a harsh term. If >>>> your expectations are for full-blown connection management from >>>> pconnect(), then you will be disappointed. If you take it for >>>> what it is: persistent connections, then those limitations would >>>> be expected. >>> It's broken because persistent connections get randomly garbage >>> collected where they should not. So broken in the sense of bugged. >>> Expect connections to die for no reason, especially under load. >> >> It's funny that you should mention that, since I haven't seen that >> behaviour in 18 months of load testing over a dozen servers. Please reply to the list as well. How did you verify that? It will spawn a new connection silently, if the old got dropped. Did you really verify your logs, that you don't get more new connections than Apache spawns workers? This might not be noticeable for you, if you are running Apache. In a FCGI environment where you have a fixed amount of workers, you notice new connections, as there should not be any. -- Regards, Hannes Dorbath
In response to Hannes Dorbath <light@theendofthetunnel.de>: > > On 24.08.2007 02:43, Bill Moran wrote: > >> Hannes Dorbath <light@theendofthetunnel.de> wrote: > >>> Bill Moran wrote: > >>>> I guess I just feel that "broken" is a bit of a harsh term. If > >>>> your expectations are for full-blown connection management from > >>>> pconnect(), then you will be disappointed. If you take it for > >>>> what it is: persistent connections, then those limitations would > >>>> be expected. > >>> It's broken because persistent connections get randomly garbage > >>> collected where they should not. So broken in the sense of bugged. > >>> Expect connections to die for no reason, especially under load. > >> > >> It's funny that you should mention that, since I haven't seen that > >> behaviour in 18 months of load testing over a dozen servers. > > Please reply to the list as well. Your reply to me did not have the list in the CC. > How did you verify that? It will spawn a new connection silently, if the > old got dropped. Did you really verify your logs, that you don't get > more new connections than Apache spawns workers? This might not be > noticeable for you, if you are running Apache. In a FCGI environment > where you have a fixed amount of workers, you notice new connections, as > there should not be any. As I stated in the other reply to an email that looked similar to this one -- I'm not sure I understand the behaviour you're trying to describe. -- Bill Moran http://www.potentialtech.com
On 24.08.2007 12:56, Bill Moran wrote: >> How did you verify that? It will spawn a new connection silently, if the >> old got dropped. Did you really verify your logs, that you don't get >> more new connections than Apache spawns workers? This might not be >> noticeable for you, if you are running Apache. In a FCGI environment >> where you have a fixed amount of workers, you notice new connections, as >> there should not be any. > > As I stated in the other reply to an email that looked similar to this > one -- I'm not sure I understand the behaviour you're trying to describe. Persistent connections get randomly dropped. Now there is either an Apache worker with mod_php or and FCGI child that has lost its connection to the database. On the next request that is processed by this child / worker it will notice that it has lost its connection and will create a new one. This behaviour might depend on pgsql.auto_reset_persistent. When using a classical Apache config (that is a non-threaded MPM) Apache does adjust its number of workers dynamically, so it's natural that more database connections are created under load and are dropped again after some idle time. That is why it might be hard to verify that you are not hit by this bug, as in your environment there is a native fluctuation. In an FCGI environment however, you will notice this immediatelly, as the number of childs / workers is usually fixed and something must be broken if connection come and go. -- Regards, Hannes Dorbath