From: Pierre C <lists@peufeu.com>
To: Pavel Stehule <pavel.stehule@gmail.com>
To: Michael Ben-Nes <michael@epoch.co.il>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: MySQL HandlerSocket - Is this possible in PG?
Date: Wed, 22 Dec 2010 09:22:17 +0100
Message-ID: <op.vn3v7flreorkce@apollo13> (raw)
In-Reply-To: <AANLkTin+ZBsfFhdyRJ813PKkw0u0pmSAbXVfhoYwJdiz@mail.gmail.com>
References: <AANLkTinU4jV6AAD_OEt3Pd2mP2gFcEw2xrzLHOf3Of-6@mail.gmail.com>
<AANLkTikPTBgtpZO0O9hTZ0McngwjukJY4NipVN+85s_6@mail.gmail.com>
<AANLkTin+ZBsfFhdyRJ813PKkw0u0pmSAbXVfhoYwJdiz@mail.gmail.com>
> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@peufeu.com, pavel.stehule@gmail.com, michael@epoch.co.il
Subject: Re: MySQL HandlerSocket - Is this possible in PG?
In-Reply-To: <op.vn3v7flreorkce@apollo13>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox