Skip site navigation (1) Skip section navigation (2)

Re: MySQL HandlerSocket - Is this possible in PG?

From: "Pierre C" <lists(at)peufeu(dot)com>
To: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>, "Michael Ben-Nes" <michael(at)epoch(dot)co(dot)il>
Cc: "PostgreSQL Performance" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: MySQL HandlerSocket - Is this possible in PG?
Date: 2010-12-22 08:22:17
Message-ID: op.vn3v7flreorkce@apollo13 (view raw or flat)
Thread:
Lists: pgsql-performance

> 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.

In response to

Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2010-12-22 08:52:28
Subject: Re: MySQL HandlerSocket - Is this possible in PG?
Previous:From: Michael Ben-NesDate: 2010-12-22 07:41:51
Subject: Re: MySQL HandlerSocket - Is this possible in PG?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group