From: Sergey Ostanevich <sergos@tarantool.org>
To: Nikita Pettik <korablev@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 17:23:16 +0300 [thread overview]
Message-ID: <20191230142316.GH1222@tarantool.org> (raw)
In-Reply-To: <20191230094635.GA29923@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.
>
next prev parent reply other threads:[~2019-12-30 14:23 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
2019-12-30 14:23 ` Sergey Ostanevich [this message]
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=20191230142316.GH1222@tarantool.org \
--to=sergos@tarantool.org \
--cc=korablev@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