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

[Sergey Ostanevich]

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.
>