From: Nikita Pettik <korablev@tarantool.org> To: Sergey Ostanevich <sergos@tarantool.org> Cc: tarantool-patches@dev.tarantool.org Subject: Re: [Tarantool-patches] [PATCH v3 20/20] sql: add cache statistics to box.info Date: Mon, 30 Dec 2019 11:46:35 +0200 [thread overview] Message-ID: <20191230094635.GA29923@tarantool.org> (raw) In-Reply-To: <20191225205340.GB53@tarantool.org> 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.
next prev parent reply other threads:[~2019-12-30 9:46 UTC|newest] Thread overview: 51+ messages / expand[flat|nested] mbox.gz Atom feed top 2019-12-20 12:47 [Tarantool-patches] [PATCH v3 00/20] sql: prepared statements Nikita Pettik 2019-12-20 12:47 ` [Tarantool-patches] [PATCH v3 01/20] sql: remove sql_prepare_v2() Nikita Pettik 2019-12-23 14:03 ` Sergey Ostanevich 2019-12-24 0:51 ` Nikita Pettik 2019-12-27 19:18 ` Sergey Ostanevich 2019-12-20 12:47 ` [Tarantool-patches] [PATCH v3 02/20] sql: refactor sql_prepare() and sqlPrepare() Nikita Pettik 2019-12-24 11:35 ` Sergey Ostanevich 2019-12-20 12:47 ` [Tarantool-patches] [PATCH v3 03/20] sql: move sql_prepare() declaration to box/execute.h Nikita Pettik 2019-12-24 11:40 ` Sergey Ostanevich 2019-12-20 12:47 ` [Tarantool-patches] [PATCH v3 04/20] sql: rename sqlPrepare() to sql_stmt_compile() Nikita Pettik 2019-12-24 12:01 ` Sergey Ostanevich 2019-12-20 12:47 ` [Tarantool-patches] [PATCH v3 05/20] sql: rename sql_finalize() to sql_stmt_finalize() Nikita Pettik 2019-12-24 12:08 ` Sergey Ostanevich 2019-12-20 12:47 ` [Tarantool-patches] [PATCH v3 06/20] sql: rename sql_reset() to sql_stmt_reset() Nikita Pettik 2019-12-24 12:09 ` Sergey Ostanevich 2019-12-20 12:47 ` [Tarantool-patches] [PATCH v3 07/20] sql: move sql_stmt_finalize() to execute.h Nikita Pettik 2019-12-24 12:11 ` Sergey Ostanevich 2019-12-20 12:47 ` [Tarantool-patches] [PATCH v3 08/20] port: increase padding of struct port Nikita Pettik 2019-12-24 12:34 ` Sergey Ostanevich 2019-12-20 12:47 ` [Tarantool-patches] [PATCH v3 09/20] port: add result set format and request type to port_sql Nikita Pettik 2019-12-25 13:37 ` Sergey Ostanevich 2019-12-20 12:47 ` [Tarantool-patches] [PATCH v3 10/20] sql: resurrect sql_bind_parameter_count() function Nikita Pettik 2019-12-24 20:23 ` Sergey Ostanevich 2019-12-20 12:47 ` [Tarantool-patches] [PATCH v3 11/20] sql: resurrect sql_bind_parameter_name() Nikita Pettik 2019-12-24 20:26 ` Sergey Ostanevich 2019-12-20 12:47 ` [Tarantool-patches] [PATCH v3 12/20] sql: add sql_stmt_schema_version() Nikita Pettik 2019-12-25 13:37 ` Sergey Ostanevich 2019-12-20 12:47 ` [Tarantool-patches] [PATCH v3 13/20] sql: introduce sql_stmt_sizeof() function Nikita Pettik 2019-12-25 13:44 ` Sergey Ostanevich 2019-12-20 12:47 ` [Tarantool-patches] [PATCH v3 14/20] box: increment schema_version on ddl operations Nikita Pettik 2019-12-25 14:33 ` Sergey Ostanevich 2019-12-20 12:47 ` [Tarantool-patches] [PATCH v3 15/20] sql: introduce sql_stmt_query_str() method Nikita Pettik 2019-12-25 14:36 ` Sergey Ostanevich 2019-12-20 12:47 ` [Tarantool-patches] [PATCH v3 16/20] sql: move sql_stmt_busy() declaration to box/execute.h Nikita Pettik 2019-12-25 14:54 ` Sergey Ostanevich 2019-12-20 12:47 ` [Tarantool-patches] [PATCH v3 17/20] sql: introduce holder for prepared statemets Nikita Pettik 2019-12-23 20:54 ` Sergey Ostanevich 2019-12-20 12:47 ` [Tarantool-patches] [PATCH v3 18/20] box: introduce prepared statements Nikita Pettik 2019-12-25 15:23 ` Sergey Ostanevich 2019-12-30 10:27 ` Nikita Pettik 2019-12-30 14:15 ` Sergey Ostanevich 2019-12-20 12:47 ` [Tarantool-patches] [PATCH v3 19/20] netbox: " Nikita Pettik 2019-12-25 20:41 ` Sergey Ostanevich 2019-12-30 9:58 ` Nikita Pettik 2019-12-30 14:16 ` Sergey Ostanevich 2019-12-20 12:47 ` [Tarantool-patches] [PATCH v3 20/20] sql: add cache statistics to box.info Nikita Pettik 2019-12-25 20:53 ` Sergey Ostanevich 2019-12-30 9:46 ` Nikita Pettik [this message] 2019-12-30 14:23 ` Sergey Ostanevich 2019-12-30 1:13 ` [Tarantool-patches] [PATCH v3 00/20] sql: prepared statements Nikita Pettik 2019-12-31 8:39 ` Kirill Yukhin
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox Avoid top-posting and favor interleaved quoting: https://en.wikipedia.org/wiki/Posting_style#Interleaved_style * Reply using the --to, --cc, and --in-reply-to switches of git-send-email(1): git send-email \ --in-reply-to=20191230094635.GA29923@tarantool.org \ --to=korablev@tarantool.org \ --cc=sergos@tarantool.org \ --cc=tarantool-patches@dev.tarantool.org \ --subject='Re: [Tarantool-patches] [PATCH v3 20/20] sql: add cache statistics to box.info' \ /path/to/YOUR_REPLY https://kernel.org/pub/software/scm/git/docs/git-send-email.html * If your mail client supports setting the In-Reply-To header via mailto: links, try the mailto: link
This is a public inbox, see mirroring instructions for how to clone and mirror all data and code used for this inbox