[Tarantool-patches] [PATCH 13/15] sql: introduce cache for prepared statemets

[Georgy Kirichenko]

On Monday, November 11, 2019 9:35:15 PM MSK Konstantin Osipov wrote:
> * Nikita Pettik <korablev at tarantool.org> [19/11/11 21:27]:
> > Today Kirill came to me and said that there's request from solution
> > team to make cache global: according to them they use many connections
> > to execute the same set of queries. That turns out global cache to be
> > reasonable. However, to allow execute the same prepared statement via
> > different sessions we should keep trace of original query - its hash
> 
> > value. So the new proposal is:
> A back of the envelope calculation shows that if you have 40
> nodes, 32 cores, 100 connections on each instance, it gives you
> 
> 40*32*100*5mb = 625 GB of prepared statement cache!
Why did you make this fake up?
1. why did you pack instances so dense?
2. why did you use 5mb instead of 1-2-5kb, for instance.
4. why did you count connections because the only thing is matter is count of 
unique sql statements.
4. why did you use the word `cache`.
Prepared statements and statement cache are the completely different 
substances. Obviously, if somebody prepared a statement then they wish the 
statement to do not be evicted from the prepared statements list.
> 
> Why does someone else have to point it out to the core team?
Seriously?
> 
> > - Each session holds map <stmt_id : query_hash>
> > - There's one global hash <query_hash : stmt>
> > - Each statement now has reference counter: each "prepare" call via
> > 
> >   different session bumps its value; each "unprepare" call results in
> >   its decrement. Disconect of session leads to counter decrements of all
> >   related statements. Statement is not immediately deallocated if its
> >   counter is 0: we maintaint global list of statements to be freed when
> >   memory limit is reached
> > 
> > - On "prepare" call we firstly check if there's enough space for
> > statement.
> > 
> >   If memory limit has reached, we traverse list of statements to be freed
> >   and release all occupied resources. It would allow us to solve possible
> >   overhead on disconnect event.
> 
> Here's how having cache global is connected to using string
> identifiers:
> 
> If you make the cache opaque to the client you don't have
> this mess with explicit referencing and dereferencing from
> sessions.
User should explicitly unprepare any statement they prepared before. Or it 
would be done by on_session_stop trigger. As we do not want to evict a 
prepared statement from prepared statement list, really.
> 
> Besides, as I already mentioned multiple times, a single session
> may use multiple references to the same prepared statement - since
> the protocol is fully asynchronous.
Wrong. The connection could but, please, do not forget about streams (which 
would encapsulate a single user session) we want to introduce in the near 
future.
> 
> The cache has to be pure LRU, with automatic allocation and
> expiration of objects. This will save CPU cycles on disconnect
> as well as make the implementation simpler.
One more time: we do not talk about statement cache but we discuss the 
prepared statement.
In any case we should prefer MPI instead of LRU and the discuss hot/cold zones 
politics. 
> 
> You can't do this if you have numeric identifiers, because you
> must keep the object around as long as the identifier is around.
Yes, and it is the feature requirement - if a statement was prepared then it 
should be prepared until user did not request the statement unprepare.

So I would like to explain my own vision of the feature:
1. Each instance has its own prepared statements table from which a statement 
vm could be fetched using the statement key (sql source string hash, for 
instance). Also the table may contain some meta: list of accessed objects (to 
prevent us from access checking on each invocation), count of invocation, sql 
source, reference counter.
2. Each session has a limit which limits the amount of resources, count of 
prepared statement for instance.
3. Each session contain the list of prepared statement identifiers used by this 
session.
4. Statement cache could be used by the feature but it is not essential. At 
least it relaxes the feature implementation complexity - we should not 
implement a good statement cache, eviction politics and other stuff like this 
right now.
5. If a client loses their session (crash, network failure) then it should 
reestablish a session and reprepare all statement it wish to use. It is more 
simply to implement in comparison with any caching, eviction, tracking and 
other things you suggested.
6. Right now a connection could contain only one session and it is not a stop-
factor - the only thing we should keep in mind - prepared statement relates to 
a session not to a connection. Then, when we will implement streams and 
multiple sessions per connection, we should not do any more changes to support 
prepared statements in this circumstances.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 488 bytes
Desc: This is a digitally signed message part.
URL: <https://lists.tarantool.org/pipermail/tarantool-patches/attachments/20191112/5b7fdb2e/attachment.sig>