From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtp60.i.mail.ru (smtp60.i.mail.ru [217.69.128.40]) (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 3DD6846970E for ; Mon, 30 Dec 2019 17:23:17 +0300 (MSK) Date: Mon, 30 Dec 2019 17:23:16 +0300 From: Sergey Ostanevich Message-ID: <20191230142316.GH1222@tarantool.org> References: <57875597d4dc3a373565309cb5d6613f9f196ac6.1576844632.git.korablev@tarantool.org> <20191225205340.GB53@tarantool.org> <20191230094635.GA29923@tarantool.org> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline In-Reply-To: <20191230094635.GA29923@tarantool.org> Subject: Re: [Tarantool-patches] [PATCH v3 20/20] sql: add cache statistics to box.info List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , To: Nikita Pettik Cc: tarantool-patches@dev.tarantool.org Hi! Got it with stmts removal. LGTM. Sergos On 30 Dec 11:46, Nikita Pettik wrote: > On 25 Dec 23:53, Sergey Ostanevich wrote: > > Hi! > > > > Thanks for the whole patchset! > > I got one major question: in case session is ended - there should be a > > refcnt reduction for all prepared stmts of this session. I didn't see > > that in the code. > > Look at session_destroy() which calls sql_session_stmt_hash_erase(). > > > Regards, > > Sergos > > > > On 20 Dec 15:47, Nikita Pettik wrote: > > > To track current memory occupied by prepared statements and number of > > > them, let's extend box.info submodule with .sql statistics: now it > > > contains current total size of prepared statements and their count. > > > > > > @TarantoolBot document > > > Title: Prepared statements in SQL > > > > > > Now it is possible to prepare (i.e. compile into byte-code and save to > > > the cache) statement and execute it several times. Mechanism is similar > > > to ones in other DBs. Prepared statement is identified by numeric > > > ID, which are returned alongside with prepared statement handle. > > > Note that they are not sequential and represent value of hash function > > > applied to the string containing original SQL request. > > > Prepared statement holder is shared among all sessions. However, session > > > has access only to statements which have been prepared in scope of it. > > > There's no eviction policy like in any cache; to remove statement from > > > holder explicit unprepare request is required. Alternatively, session's > > > disconnect also removes statements from holder. > > > Several sessions can share one prepared statement, which will be > > > destroyed when all related sessions are disconnected or send unprepare > > > request. Memory limit for prepared statements is adjusted by > > > box.cfg{sql_cache_size} handle (can be set dynamically; > > > > > > Any DDL operation leads to expiration of all prepared statements: they > > > should be manually removed or re-prepared. > > > > Does it mean that for user code there should be called unprepare(s.id) and > > s = prepare(orginal sql text)? If I got it right from previous patches > > the re-prepare is called automatically? > > In current versions there's no auto re-prepare, user should call > prepare again manually. > > > > > > Prepared statements are available in local mode (i.e. via box.prepare() > > > function) and are supported in IProto protocol. In the latter case > > > next IProto keys are used to make up/receive requests/responses: > > > IPROTO_PREPARE - new IProto command; key is 0x13. It can be sent with > > > one of two mandatory keys: IPROTO_SQL_TEXT (0x40 and assumes string value) > > > or IPROTO_STMT_ID (0x43 and assumes integer value). Depending on body it > > > means to prepare or unprepare SQL statement: IPROTO_SQL_TEXT implies prepare > > > request, meanwhile IPROTO_STMT_ID - unprepare; > > > IPROTO_BIND_METADATA (0x33 and contains parameters metadata of type map) > > > and IPROTO_BIND_COUNT (0x34 and corresponds to the count of parameters to > > > be bound) are response keys. They are mandatory members of result of > > > IPROTO_PREPARE execution. > > > > > > To track statistics of used memory and number of currently prepared > > > statements, box.info is extended with SQL statistics: > > > > > > box.info:sql().cache.stmt_count - number of prepared statements; > > > box.info:sql().cache.size - size of occupied by prepared statements memory. > > > > > > Typical workflow with prepared statements is following: > > > > > > s = box.prepare("SELECT * FROM t WHERE id = ?;") > > > s:execute({1}) or box.execute(s.sql_str, {1}) > > > s:execute({2}) or box.execute(s.sql_str, {2}) > > > s:unprepare() or box.unprepare(s.query_id) > > > > > > Structure of object is following (member : type): > > > > > > - stmt_id: integer > > > execute: function > > > params: map [name : string, type : integer] > > > unprepare: function > > > metadata: map [name : string, type : integer] > > > param_count: integer > > > ... > > > > > > In terms of remote connection: > > > > > > cn = netbox:connect(addr) > > > s = cn:prepare("SELECT * FROM t WHERE id = ?;") > > > cn:execute(s.sql_str, {1}) > > > cn:unprepare(s.query_id) > > > > > > Closese #2592 > > > > Misprint above: Closes > > Thx, fixed. >