Re: MySQL HandlerSocket - Is this possible in PG?

От: Pierre C
Тема: Re: MySQL HandlerSocket - Is this possible in PG?
Дата: ,
Msg-id: op.vn4xl2xzeorkce@apollo13
(см: обсуждение, исходный текст)
Ответ на: MySQL HandlerSocket - Is this possible in PG?  (Michael Ben-Nes)
Список: pgsql-performance

Скрыть дерево обсуждения

MySQL HandlerSocket - Is this possible in PG?  (Michael Ben-Nes, )
 Re: MySQL HandlerSocket - Is this possible in PG?  (Pavel Stehule, )
  Re: MySQL HandlerSocket - Is this possible in PG?  (Michael Ben-Nes, )
   Re: MySQL HandlerSocket - Is this possible in PG?  ("Pierre C", )
 Re: MySQL HandlerSocket - Is this possible in PG?  (Pavel Stehule, )
  Re: MySQL HandlerSocket - Is this possible in PG?  (Merlin Moncure, )
   Re: MySQL HandlerSocket - Is this possible in PG?  (Michael Ben-Nes, )
    Re: MySQL HandlerSocket - Is this possible in PG?  (Dimitri Fontaine, )
 Re: MySQL HandlerSocket - Is this possible in PG?  (Scott Marlowe, )
 Re: MySQL HandlerSocket - Is this possible in PG?  (Marti Raudsepp, )
 Re: MySQL HandlerSocket - Is this possible in PG?  ("Pierre C", )

On Wed, 22 Dec 2010 14:17:21 +0100, Michael Ben-Nes <>
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.


В списке pgsql-performance по дате сообщения:

От: Bruce Momjian
Дата:
Сообщение: Re: BBU Cache vs. spindles
От: tuanhoanganh
Дата:
Сообщение: Re: PostgreSQL 9.0 x64 bit pgbench TPC very low question?