From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtpng3.m.smailru.net (smtpng3.m.smailru.net [94.100.177.149]) (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 B84DD45BE39 for ; Fri, 20 Dec 2019 15:47:55 +0300 (MSK) From: Nikita Pettik Date: Fri, 20 Dec 2019 15:47:25 +0300 Message-Id: <57875597d4dc3a373565309cb5d6613f9f196ac6.1576844632.git.korablev@tarantool.org> In-Reply-To: References: In-Reply-To: References: Subject: [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: tarantool-patches@dev.tarantool.org 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. 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 --- src/box/lua/info.c | 25 +++++++++++++++++++++++++ src/box/sql_stmt_cache.c | 16 ++++++++++++++++ src/box/sql_stmt_cache.h | 8 ++++++++ test/box/info.result | 1 + test/sql/prepared.result | 34 +++++++++++++++++++++++++++++++++- test/sql/prepared.test.lua | 12 +++++++++++- 6 files changed, 94 insertions(+), 2 deletions(-) diff --git a/src/box/lua/info.c b/src/box/lua/info.c index e029e0e17..8933ea829 100644 --- a/src/box/lua/info.c +++ b/src/box/lua/info.c @@ -45,6 +45,7 @@ #include "box/gc.h" #include "box/engine.h" #include "box/vinyl.h" +#include "box/sql_stmt_cache.h" #include "main.h" #include "version.h" #include "box/box.h" @@ -494,6 +495,29 @@ lbox_info_vinyl(struct lua_State *L) return 1; } +static int +lbox_info_sql_call(struct lua_State *L) +{ + struct info_handler h; + luaT_info_handler_create(&h, L); + sql_stmt_cache_stat(&h); + + return 1; +} + +static int +lbox_info_sql(struct lua_State *L) +{ + lua_newtable(L); + lua_newtable(L); /* metatable */ + lua_pushstring(L, "__call"); + lua_pushcfunction(L, lbox_info_sql_call); + lua_settable(L, -3); + + lua_setmetatable(L, -2); + return 1; +} + static const struct luaL_Reg lbox_info_dynamic_meta[] = { {"id", lbox_info_id}, {"uuid", lbox_info_uuid}, @@ -509,6 +533,7 @@ static const struct luaL_Reg lbox_info_dynamic_meta[] = { {"memory", lbox_info_memory}, {"gc", lbox_info_gc}, {"vinyl", lbox_info_vinyl}, + {"sql", lbox_info_sql}, {NULL, NULL} }; diff --git a/src/box/sql_stmt_cache.c b/src/box/sql_stmt_cache.c index 742e4135c..a4f5f2745 100644 --- a/src/box/sql_stmt_cache.c +++ b/src/box/sql_stmt_cache.c @@ -34,6 +34,7 @@ #include "error.h" #include "execute.h" #include "diag.h" +#include "info/info.h" static struct sql_stmt_cache sql_stmt_cache; @@ -48,6 +49,21 @@ sql_stmt_cache_init() rlist_create(&sql_stmt_cache.gc_queue); } +void +sql_stmt_cache_stat(struct info_handler *h) +{ + info_begin(h); + info_table_begin(h, "cache"); + info_append_int(h, "size", sql_stmt_cache.mem_used); + uint32_t entry_count = 0; + mh_int_t i; + mh_foreach(sql_stmt_cache.hash, i) + entry_count++; + info_append_int(h, "stmt_count", entry_count); + info_table_end(h); + info_end(h); +} + static size_t sql_cache_entry_sizeof(struct sql_stmt *stmt) { diff --git a/src/box/sql_stmt_cache.h b/src/box/sql_stmt_cache.h index f3935a27f..468cbc9a0 100644 --- a/src/box/sql_stmt_cache.h +++ b/src/box/sql_stmt_cache.h @@ -41,6 +41,7 @@ extern "C" { struct sql_stmt; struct mh_i64ptr_t; +struct info_handler; struct stmt_cache_entry { /** Prepared statement itself. */ @@ -90,6 +91,13 @@ struct sql_stmt_cache { void sql_stmt_cache_init(); +/** + * Store statistics concerning cache (current size and number + * of statements in it) into info handler @h. + */ +void +sql_stmt_cache_stat(struct info_handler *h); + /** * Erase session local hash: unref statements belong to this * session and deallocate hash itself. diff --git a/test/box/info.result b/test/box/info.result index af81f7add..2e84cbbe3 100644 --- a/test/box/info.result +++ b/test/box/info.result @@ -84,6 +84,7 @@ t - replication - ro - signature + - sql - status - uptime - uuid diff --git a/test/sql/prepared.result b/test/sql/prepared.result index 2f4983b00..9951a4e43 100644 --- a/test/sql/prepared.result +++ b/test/sql/prepared.result @@ -64,6 +64,21 @@ test_run:cmd("setopt delimiter ''"); | - true | ... +-- Check default cache statistics. +-- +box.info.sql() + | --- + | - cache: + | size: 0 + | stmt_count: 0 + | ... +box.info:sql() + | --- + | - cache: + | size: 0 + | stmt_count: 0 + | ... + -- Test local interface and basic capabilities of prepared statements. -- execute('CREATE TABLE test (id INT PRIMARY KEY, a NUMBER, b TEXT)') @@ -144,6 +159,15 @@ execute(s.stmt_id, {1, 3}) | rows: [] | ... +assert(box.info.sql().cache.stmt_count ~= 0) + | --- + | - true + | ... +assert(box.info.sql().cache.size ~= 0) + | --- + | - true + | ... + test_run:cmd("setopt delimiter ';'") | --- | - true @@ -584,8 +608,16 @@ unprepare(s1.stmt_id) -- Setting cache size to 0 is possible only in case if -- there's no any prepared statements right now . -- -box.cfg{sql_cache_size = 0} +box.cfg{sql_cache_size = 0 } + | --- + | ... +assert(box.info.sql().cache.stmt_count == 0) | --- + | - true + | ... +assert(box.info.sql().cache.size == 0) + | --- + | - true | ... prepare("SELECT a FROM test;") | --- diff --git a/test/sql/prepared.test.lua b/test/sql/prepared.test.lua index c464cc21a..5820525d1 100644 --- a/test/sql/prepared.test.lua +++ b/test/sql/prepared.test.lua @@ -43,6 +43,11 @@ end; test_run:cmd("setopt delimiter ''"); +-- Check default cache statistics. +-- +box.info.sql() +box.info:sql() + -- Test local interface and basic capabilities of prepared statements. -- execute('CREATE TABLE test (id INT PRIMARY KEY, a NUMBER, b TEXT)') @@ -60,6 +65,9 @@ s.param_count execute(s.stmt_id, {1, 2}) execute(s.stmt_id, {1, 3}) +assert(box.info.sql().cache.stmt_count ~= 0) +assert(box.info.sql().cache.size ~= 0) + test_run:cmd("setopt delimiter ';'") if not is_remote then res = s:execute({1, 2}) @@ -207,7 +215,9 @@ unprepare(s1.stmt_id) -- Setting cache size to 0 is possible only in case if -- there's no any prepared statements right now . -- -box.cfg{sql_cache_size = 0} +box.cfg{sql_cache_size = 0 } +assert(box.info.sql().cache.stmt_count == 0) +assert(box.info.sql().cache.size == 0) prepare("SELECT a FROM test;") box.cfg{sql_cache_size = 0} -- 2.15.1