On 02.08.2019 11:25, Daniel Migowski wrote:
Am 01.08.2019 um 18:56 schrieb Konstantin Knizhnik:
I decided to implement your proposal. Much simple version of
autoprepare patch is attached.
At my computer I got the following results:
pgbench -M simple -S 22495 TPS
pgbench -M extended -S 47633 TPS
pgbench -M prepared -S 47683 TPS
So autoprepare speedup execution of queries sent using extended
protocol more than twice and it is almost the same as with explicitly
prepared statements.
I failed to create regression test for it because I do not know how
to force psql to use extended protocol. Any advice is welcome.
I am very interested in such a patch, because currently I use the same
functionality within my JDBC driver and having this directly in
PostgreSQL would surely speed things up.
I have two suggestions however:
1. Please allow to gather information about the autoprepared
statements by returning them in pg_prepared_statements view. I would
love to monitor usage of them as well as the memory consumption that
occurs. I suggested a patch to display that in
https://www.postgresql.org/message-id/41ed3f5450c90f4d8381bc4d8df6bbdcf02e1...@exchangeserver.ikoffice.de
Sorry, but there is pg_autoprepared_statements view. I think that it
will be better to distinguish explicitly and implicitly prepared
statements, will not it?
Do you want to add more information to this view? Right now it shows
query string, types of parameters and number of this query execution.
2. Please not only use a LRU list, but maybe something which would
prefer statements that get reused at all. We often create ad hoc
statements with parameters which don't really need to be kept. Maybe I
can suggest an implementation of an LRU list where a reusal of a
statement not only pulls it to the top of the list but also increases
a reuse counter. When then a statement would drop off the end of the
list one checks if the reusal count is non-zero, and only really drops
it if the resual count is zero. Else the reusal count is decremented
(or halved) and the element is also placed at the start of the LRU
list, so it is kept a bit longer.
There are many variations of LRU and alternatives: clock, segmented LRU, ...
I agree that classical LRU may be not the best replacement policy for
autoprepare.
Application is either use static queries, either constructing them
dynamically. It will be better to distinguish queries executed at least
twice from one-shot queries.
So may be SLRU will be the best choice. But actually situation you have
described (We often create ad hoc statements with parameters which don't
really need to be kept)
is not applicable to atutoprepare. Autoprepare deals only with
statements which are actually executed.
We have another patch which limits number of stored prepared plans to
avoid memory overflow (in case of using stored procedures which
implicitly prepare all issued queries).
Here choice of replacement policy is more critical.
Thanks,
Daniel Migowski
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
