Re: Apache + PHP + Postgres Interaction

-----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(at)commandprompt(dot)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(at)commandprompt(dot)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(at)myemma(dot)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(at)potentialtech(dot)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

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.

In response to Josh Trutwin <josh(at)trutwins(dot)homeip(dot)net>:

> On Thu, 23 Aug 2007 13:29:46 -0400
> Bill Moran <wmoran(at)potentialtech(dot)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

On Thu, 23 Aug 2007 21:16:48 +0400, Joshua D. Drake <jd(at)commandprompt(dot)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(at)potentialtech(dot)com>
wrote:

> In response to "Joshua D. Drake" <jd(at)commandprompt(dot)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.

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(at)theendofthetunnel(dot)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(at)theendofthetunnel(dot)de>:

> > On 24.08.2007 02:43, Bill Moran wrote:
> >> Hannes Dorbath <light(at)theendofthetunnel(dot)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