From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtpng2.m.smailru.net (smtpng2.m.smailru.net [94.100.179.3]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by dev.tarantool.org (Postfix) with ESMTPS id 89B6C452566 for ; Tue, 12 Nov 2019 10:55:10 +0300 (MSK) From: Georgy Kirichenko Date: Tue, 12 Nov 2019 10:54:54 +0300 Message-ID: <4071647.IRq02QQX9c@localhost> In-Reply-To: <20191111183515.GA25103@atlas> References: <20191107010455.64457-1-korablev@tarantool.org> <20191111105355.GC82024@tarantool.org> <20191111183515.GA25103@atlas> MIME-Version: 1.0 Content-Type: multipart/signed; boundary="nextPart2025594.Ogz3JWrtv5"; micalg="pgp-sha256"; protocol="application/pgp-signature" Subject: Re: [Tarantool-patches] [PATCH 13/15] sql: introduce cache for prepared statemets List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , To: tarantool-patches@dev.tarantool.org Cc: v.shpilevoy@tarantool.org --nextPart2025594.Ogz3JWrtv5 Content-Transfer-Encoding: 7Bit Content-Type: text/plain; charset="us-ascii" On Monday, November 11, 2019 9:35:15 PM MSK Konstantin Osipov wrote: > * Nikita Pettik [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 > > - There's one global hash > > - 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. --nextPart2025594.Ogz3JWrtv5 Content-Type: application/pgp-signature; name="signature.asc" Content-Description: This is a digitally signed message part. Content-Transfer-Encoding: 7Bit -----BEGIN PGP SIGNATURE----- iQEzBAABCAAdFiEEFZT35EtIMRTDS5hJnoTdFFzh6LUFAl3KZU4ACgkQnoTdFFzh 6LWDqggAxESdBASF0/KZ0uSgadMz22xt7hQkPgdM/aR3dDkLJ/wlD+3lDnN7OzKP p0kuIwZ0D/48tyPOw7rnOfjSUfWTIDN/ZnGPoIxtOD2h4nxfyqdV3+Q4ckoOhPx8 P/yc01k3LQFWRJ3dx0Ojm0wcnVPDA0b7xwuSePGkB19SLIUhHTqQThBkpZrIQSBt +mwSHQJZmFoAwfNLzxu89JEqoBaZozr7yDZssGXa7JUStqABibhjG0IdXhhwEUqz izjz1ETwFMCxUJsAOF+p1bXUh1j3p52lpS3y/9jzUUUkOyctnXAQcVNhvyO8vSuR u0hKN2tsStr19j0Y8Rec+uec0O8MXw== =2/Sd -----END PGP SIGNATURE----- --nextPart2025594.Ogz3JWrtv5--