From: Pierre C <lists@peufeu.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: MySQL HandlerSocket - Is this possible in PG?
Date: Wed, 22 Dec 2010 22:50:16 +0100
Message-ID: <op.vn4xl2xzeorkce@apollo13> (raw)
In-Reply-To: <AANLkTikceO9qCdDWRmtYd+wTmXs=AJ5Nh3_TNaCniNOh@mail.gmail.com>
References: <AANLkTinU4jV6AAD_OEt3Pd2mP2gFcEw2xrzLHOf3Of-6@mail.gmail.com>
<AANLkTikPTBgtpZO0O9hTZ0McngwjukJY4NipVN+85s_6@mail.gmail.com>
<AANLkTin+ZBsfFhdyRJ813PKkw0u0pmSAbXVfhoYwJdiz@mail.gmail.com>
<op.vn3v7flreorkce@apollo13>
<AANLkTikceO9qCdDWRmtYd+wTmXs=AJ5Nh3_TNaCniNOh@mail.gmail.com>
On Wed, 22 Dec 2010 14:17:21 +0100, Michael Ben-Nes <michael@epoch.co.il>
wrote:
> Thanks, it is most interesting
>
> --------------------------------------------------
> Michael Ben-Nes - Internet Consultant and Director.
> http://www.epoch.co.il - weaving the Net.
> Cellular: 054-4848113
> --------------------------------------------------
>
In fact, it would be possible to implement something like MySQL
HandlerSocket, using the following Very Ugly Hack :
This would only work for ultra simple "SELECT 1 row WHERE primary key =
constant" queries.
- a pooler (separate process) waits for connections
- clients connect to the pooler and send queries
- pooler accumulates enough queries to justify the overhead of what's
going to come next
- pooler takes a bunch of queries and encodes them in some custom ad-hoc
format (not SQL)
- pooler says to postgres "SELECT do_my_queries( serialized data )"
- do_my_queries() is a user function (in C) which uses postgres access
methods directly (like index access method on primary key), processes
queries, and sends results back as binary data
- repeat for next batch
Nested Loop Index Scan processes about 400.000 rows/s which is 2.5
us/query, maybe you could get into that ballpark (per core).
Of course it's a rather extremely ugly hack.
-------------------
Note that you could very possibly have almost the same benefits with
"almost" none of the ugliness by doing the following :
same as above :
- a pooler (separate process) waits for connections
- clients connect to the pooler and send queries in the format query +
parameters (which libpq uses if you ask)
- pooler accumulates enough queries to justify the overhead of what's
going to come next
different :
- pooler looks at each query, and if it has not seen it yet on this
particular pg connection, issues a "PREPARE" on the query
- pooler sends, in one TCP block, a begin, then a bunch of "execute named
prepared statement with parameters" commands, then a rollback
- postgres executes all of those and returns all replies in one TCP block
(this would need a modification)
- pooler distributes results back to clients
This would need a very minor change to postgres (coalescing output
blocks). It would make the pooler pay TCP overhead instead of postgres,
and greatly improve cache locality in postgres.
Since error handling would be "problematic" (to say the least...) and
expensive it would only work on simple selects.
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
Subject: Re: MySQL HandlerSocket - Is this possible in PG?
In-Reply-To: <op.vn4xl2xzeorkce@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