Re: Apache + PHP + Postgres Interaction

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.