Re: Apache + PHP + Postgres Interaction

From: "Max Zorloff" <zorloff(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Apache + PHP + Postgres Interaction
Date: 2007-08-23 19:00:32
Message-ID: op.txi0e6ooll0p5y@1-rtt202dnf3uds.mshome.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Erik Jones 2007-08-23 19:01:01 Re: Argument type list
Previous Message Bill Moran 2007-08-23 18:55:47 Re: Apache + PHP + Postgres Interaction