[Tarantool-patches] [PATCH v3 20/20] sql: add cache statistics to box.info

[Nikita Pettik]

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.