On 27.04.2018 16:49, Merlin Moncure wrote:
On Thu, Apr 26, 2018 at 6:04 AM, Konstantin Knizhnik
<k.knizh...@postgrespro.ru> wrote:
On 25.04.2018 20:02, Merlin Moncure wrote:
Yep. The main workaround today is to disable them. Having said that,
it's not that difficult to imagine hooking prepared statement creation
to a backend starting up (feature: run X,Y,Z SQL before running user
queries).
Sorry, I do not completely understand your idea.
Yes, it is somehow possible to simulate session semantic by prepending all
session specific commands (mostly setting GUCs) to each SQL statements.
But it doesn't work for prepared statements: the idea of prepared statements
is that compilation of statement should be done only once.
The idea is that you have arbitrary SQL that runs when after the
backend (postgres binary) is forked from postmaster. This would be an
ideal place to introduce prepared statements in a way that is pooling
compatible; you still couldn't PREPARE from the application but you'd
be free to call already prepared statements (via SQL level EXECUTE or
libpq PQexecPrepared()). Of course, if somebody throws a DEALLOCATE
or DISCARD ALL, or issues a problematic DROP x CASCADE, you'd be in
trouble but that'a not a big deal IMO because you can control for
those things in the application.
As far as I know in this way prepared statements can be now handled by
pgbounce in transaction/statement pooling mode.
But from my point of view, in most cases this approach is practically
unusable.
It is very hard to predict from the very beginning all statements
applications will want to execute and prepare then at backend start.
Database performance is mostly limited by disk, so optimal number of
backends may be different from number of cores.
But certainly possibility to launch "optimal" number of backends is one of
the advantages of builtin session pooling.
Sure, but some workloads are cpu limited (all- or mostly- read with
data < memory, or very complex queries on smaller datasets). So we
would measure configure based one expectations exactly as is done
today with pgbouncer. This is a major feature of pgbouncer: being
able to _reduce_ the number of session states relative to the number
of connections is an important feature; it isolates your database from
various unpleasant failure modes such as runaway memory consumption.
Anyways, I'm looking at your patch. I see you've separated the client
connection count ('sessions') from the server backend instances
('backends') in the GUC. Questions:
*) Should non pooled connections be supported simultaneously with
pooled connections?
*) Should there be multiple pools with independent configurations (yes, please)?
Right now my prototype supports two modes:
1. All connections are polled.
2. There are several session pools, each bounded to its own port.
Connections to the main Postgres port are normal (dedicated).
Connections to one of session pools port's are redirected to one of the
workers of this page pool.
Please notice, that the last version of connection pooler is in
https://github.com/postgrespro/postgresql.builtin_pool.git repository.
*) How are you pinning client connections to an application managed
transaction? (IMNSHO, this feature is useless without being able to do
that)
Sorry, I do not completely understand the question.
Rescheduling is now done at transaction level - it means that backand
can not be switched to other session until completing current transaction.
The main argument for transaction level pooling is that it allows not
worry about heavy weight locks, which are associated with procarray entries.
FYI, it's pretty clear you've got a long road building consensus and
hammering out a reasonable patch through the community here. Don't
get discouraged -- there is value here, but it's going to take some
work.
Thank you.
I am absolutely sure that a lot of additional work has to be done before
this prototype may become usable.
merlin
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
