Content-Type: text/plain; charset=utf-8; format=flowed; delsp=yes
To: "Pavel Stehule" <pavel.stehule@gmail.com>, "Michael Ben-Nes"
	<michael@epoch.co.il>
Cc: "PostgreSQL Performance" <pgsql-performance@postgresql.org>
Subject: Re: MySQL HandlerSocket - Is this possible in PG?
References: <AANLkTinU4jV6AAD_OEt3Pd2mP2gFcEw2xrzLHOf3Of-6@mail.gmail.com>
	<AANLkTikPTBgtpZO0O9hTZ0McngwjukJY4NipVN+85s_6@mail.gmail.com>
	<AANLkTin+ZBsfFhdyRJ813PKkw0u0pmSAbXVfhoYwJdiz@mail.gmail.com>
Date: Wed, 22 Dec 2010 09:22:17 +0100
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
From: "Pierre C" <lists@peufeu.com>
Message-ID: <op.vn3v7flreorkce@apollo13>
In-Reply-To: <AANLkTin+ZBsfFhdyRJ813PKkw0u0pmSAbXVfhoYwJdiz@mail.gmail.com>
User-Agent: Opera Mail/10.63 (Linux)


> Do you mean I should use PREPARE?
>
> Currently I use PHP to access the DB which use libpq. Is that cosidered a
> fast call API ? if not, can you please refer me to the right info.
>
> PHP pg_pconnect command open a persistent PostgreSQL connection. Is it
> enough or I better use PgPool2 or something similar?
>
> Considering the points above, will I be able to get such high QPS from
> PostgreSQL ? If so, it will be my pleasure to dump Reddis and work solely
> with PG :)

I suppose you already have a web server like lighttpd, zeus, or nginx,  
using php as fastcgi, or apache behind a proxy ? In that case, since the  
number of php processes is limited (usually to something like 2x your  
number of cores), the number of postgres connections a web server  
generates is limited, and you can do without pgpool and use pg_pconnect.  
Be wary of the pg_pconnect bugs though (like if you restart pg, you also  
have to restart php, I suppose you know that).

Here are some timings (Core 2 Q6600) for a simple SELECT on PK query :

using tcp (localhost)
    218 µs / query :  pg_query
    226 µs / query :  pg_query_params
    143 µs / query :  pg_execute

using unix sockets
    107 µs / query :  pg_query
    122 µs / query :  pg_query_params
     63 µs / query :  pg_execute

query inside plpgsql function
     17 µs / query

Don't use PDO, it is 2x-3x slower.

TCP overhead is quite large...

If you have a named prepared statement (created with PREPARE) use  
pg_execute(), which is much faster than pg_query (for very simple queries).

Of course you need to prepare the statements... you can do that with  
pg_pool which can execute a script upon connection initialization.