[Tarantool-patches] [PATCH 13/15] sql: introduce cache for prepared statemets
Nikita Pettik
korablev at tarantool.org
Thu Nov 7 04:04:53 MSK 2019
This patch introduces cache (as data structure) to handle prepared
statements and a set of interface functions (insert, delete, find,
erase) to operate on it. Cache under the hood is hash table with integer
ids as keys and prepared statements (struct sql_stmt which is an alias
for struct Vdbe) as values. Size of cache is regulated by quota via
box.cfg{sql_cache_size} parameter. Cache is supposed to be attached to
session, which means it is destroyed when session is ended. To erase
session manually, there's special handle - session's method
sql_cache_erase(). Default cache size is assumed to be 5 Mb (like in
PosgreSQL).
Part of #2592
---
src/box/CMakeLists.txt | 1 +
src/box/box.cc | 19 +++++++
src/box/box.h | 1 +
src/box/errcode.h | 1 +
src/box/lua/cfg.cc | 12 +++++
src/box/lua/load_cfg.lua | 3 ++
src/box/lua/session.c | 10 ++++
src/box/prep_stmt.c | 108 ++++++++++++++++++++++++++++++++++++++++
src/box/prep_stmt.h | 88 ++++++++++++++++++++++++++++++++
src/box/session.cc | 14 ++++++
src/box/session.h | 2 +
test/app-tap/init_script.result | 37 +++++++-------
test/box/admin.result | 2 +
test/box/cfg.result | 7 +++
test/box/cfg.test.lua | 1 +
test/box/misc.result | 1 +
16 files changed, 289 insertions(+), 18 deletions(-)
create mode 100644 src/box/prep_stmt.c
create mode 100644 src/box/prep_stmt.h
diff --git a/src/box/CMakeLists.txt b/src/box/CMakeLists.txt
index 9d2fcea4b..5fea534eb 100644
--- a/src/box/CMakeLists.txt
+++ b/src/box/CMakeLists.txt
@@ -124,6 +124,7 @@ add_library(box STATIC
sql.c
bind.c
execute.c
+ prep_stmt.c
wal.c
call.c
merger.c
diff --git a/src/box/box.cc b/src/box/box.cc
index d71afa114..341be9caa 100644
--- a/src/box/box.cc
+++ b/src/box/box.cc
@@ -590,6 +590,15 @@ box_check_vinyl_options(void)
}
}
+static void
+box_check_sql_cache_size(int size)
+{
+ if (size < 0) {
+ tnt_raise(ClientError, ER_CFG, "sql_cache_size",
+ "must be non-negative");
+ }
+}
+
void
box_check_config()
{
@@ -611,6 +620,7 @@ box_check_config()
box_check_memtx_memory(cfg_geti64("memtx_memory"));
box_check_memtx_min_tuple_size(cfg_geti64("memtx_min_tuple_size"));
box_check_vinyl_options();
+ box_check_sql_cache_size(cfg_geti("sql_cache_size"));
}
/*
@@ -877,6 +887,14 @@ box_set_net_msg_max(void)
IPROTO_FIBER_POOL_SIZE_FACTOR);
}
+void
+box_set_prepared_stmt_cache_size(void)
+{
+ int cache_sz = cfg_geti("sql_cache_size");
+ box_check_sql_cache_size(cache_sz);
+ sql_prepared_stmt_cache_set_size(cache_sz);
+}
+
/* }}} configuration bindings */
/**
@@ -2087,6 +2105,7 @@ box_cfg_xc(void)
box_check_instance_uuid(&instance_uuid);
box_check_replicaset_uuid(&replicaset_uuid);
+ box_set_prepared_stmt_cache_size();
box_set_net_msg_max();
box_set_readahead();
box_set_too_long_threshold();
diff --git a/src/box/box.h b/src/box/box.h
index ccd527bd5..f2e88c8a9 100644
--- a/src/box/box.h
+++ b/src/box/box.h
@@ -235,6 +235,7 @@ void box_set_replication_sync_lag(void);
void box_set_replication_sync_timeout(void);
void box_set_replication_skip_conflict(void);
void box_set_net_msg_max(void);
+void box_set_prepared_stmt_cache_size(void);
extern "C" {
#endif /* defined(__cplusplus) */
diff --git a/src/box/errcode.h b/src/box/errcode.h
index c660b1c70..ee44f61b3 100644
--- a/src/box/errcode.h
+++ b/src/box/errcode.h
@@ -258,6 +258,7 @@ struct errcode_record {
/*203 */_(ER_BOOTSTRAP_READONLY, "Trying to bootstrap a local read-only instance as master") \
/*204 */_(ER_SQL_FUNC_WRONG_RET_COUNT, "SQL expects exactly one argument returned from %s, got %d")\
/*205 */_(ER_FUNC_INVALID_RETURN_TYPE, "Function '%s' returned value of invalid type: expected %s got %s") \
+ /*206 */_(ER_SQL_PREPARE, "Failed to prepare SQL statement: %s") \
/*
* !IMPORTANT! Please follow instructions at start of the file
diff --git a/src/box/lua/cfg.cc b/src/box/lua/cfg.cc
index 4884ce013..42070fe49 100644
--- a/src/box/lua/cfg.cc
+++ b/src/box/lua/cfg.cc
@@ -274,6 +274,17 @@ lbox_cfg_set_net_msg_max(struct lua_State *L)
return 0;
}
+static int
+lbox_set_prepared_stmt_cache_size(struct lua_State *L)
+{
+ try {
+ box_set_prepared_stmt_cache_size();
+ } catch (Exception *) {
+ luaT_error(L);
+ }
+ return 0;
+}
+
static int
lbox_cfg_set_worker_pool_threads(struct lua_State *L)
{
@@ -378,6 +389,7 @@ box_lua_cfg_init(struct lua_State *L)
{"cfg_set_replication_sync_timeout", lbox_cfg_set_replication_sync_timeout},
{"cfg_set_replication_skip_conflict", lbox_cfg_set_replication_skip_conflict},
{"cfg_set_net_msg_max", lbox_cfg_set_net_msg_max},
+ {"cfg_set_sql_cache_size", lbox_set_prepared_stmt_cache_size},
{NULL, NULL}
};
diff --git a/src/box/lua/load_cfg.lua b/src/box/lua/load_cfg.lua
index e7f62cf4e..fa74fcbab 100644
--- a/src/box/lua/load_cfg.lua
+++ b/src/box/lua/load_cfg.lua
@@ -81,6 +81,7 @@ local default_cfg = {
feedback_host = "https://feedback.tarantool.io",
feedback_interval = 3600,
net_msg_max = 768,
+ sql_cache_size = 5 * 1024 * 1024,
}
-- types of available options
@@ -144,6 +145,7 @@ local template_cfg = {
feedback_host = 'string',
feedback_interval = 'number',
net_msg_max = 'number',
+ sql_cache_size = 'number',
}
local function normalize_uri(port)
@@ -250,6 +252,7 @@ local dynamic_cfg = {
instance_uuid = check_instance_uuid,
replicaset_uuid = check_replicaset_uuid,
net_msg_max = private.cfg_set_net_msg_max,
+ sql_cache_size = private.cfg_set_sql_cache_size,
}
local dynamic_cfg_skip_at_load = {
diff --git a/src/box/lua/session.c b/src/box/lua/session.c
index b9495e7a6..60f5c03a0 100644
--- a/src/box/lua/session.c
+++ b/src/box/lua/session.c
@@ -366,6 +366,15 @@ lbox_push_on_access_denied_event(struct lua_State *L, void *event)
return 3;
}
+static int
+lbox_session_sql_cache_erase(struct lua_State *L)
+{
+ (void) L;
+ struct session *session = current_session();
+ sql_stmt_cache_erase(&session->prepared_stmt_cache);
+ return 0;
+}
+
/**
* Push a message using a protocol, depending on a session type.
* @param L Lua state. First argument on the stack is data to
@@ -476,6 +485,7 @@ box_lua_session_init(struct lua_State *L)
{"on_auth", lbox_session_on_auth},
{"on_access_denied", lbox_session_on_access_denied},
{"push", lbox_session_push},
+ {"sql_cache_erase", lbox_session_sql_cache_erase},
{NULL, NULL}
};
luaL_register_module(L, sessionlib_name, sessionlib);
diff --git a/src/box/prep_stmt.c b/src/box/prep_stmt.c
new file mode 100644
index 000000000..d254ed453
--- /dev/null
+++ b/src/box/prep_stmt.c
@@ -0,0 +1,108 @@
+/*
+ * Copyright 2010-2019, Tarantool AUTHORS, please see AUTHORS file.
+ *
+ * Redistribution and use in source and binary forms, with or
+ * without modification, are permitted provided that the following
+ * conditions are met:
+ *
+ * 1. Redistributions of source code must retain the above
+ * copyright notice, this list of conditions and the
+ * following disclaimer.
+ *
+ * 2. Redistributions in binary form must reproduce the above
+ * copyright notice, this list of conditions and the following
+ * disclaimer in the documentation and/or other materials
+ * provided with the distribution.
+ *
+ * THIS SOFTWARE IS PROVIDED BY <COPYRIGHT HOLDER> ``AS IS'' AND
+ * ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
+ * TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
+ * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL
+ * <COPYRIGHT HOLDER> OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
+ * INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
+ * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
+ * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR
+ * BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
+ * LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+ * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF
+ * THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
+ * SUCH DAMAGE.
+ */
+#include "prep_stmt.h"
+
+#include "assoc.h"
+#include "error.h"
+#include "execute.h"
+#include "session.h"
+
+/** Default cache size is 5 Mb. */
+size_t prep_stmt_cache_size = 5 * 1024 * 1024;
+
+int
+sql_prepared_stmt_cache_insert(struct sql_stmt *stmt, uint32_t *id)
+{
+ assert(stmt != NULL);
+ struct session *session = current_session();
+ if (session->prepared_stmt_cache.mem_used + sql_stmt_sizeof(stmt) >
+ session->prepared_stmt_cache.mem_quota) {
+ diag_set(ClientError, ER_SQL_PREPARE,
+ "prepared statement cache is full");
+ return -1;
+ }
+ *id = session->prepared_stmt_cache.current_id;
+ const struct mh_i32ptr_node_t node = { *id, stmt } ;
+ struct mh_i32ptr_node_t *old_node = NULL;
+ struct mh_i32ptr_t *hash = session->prepared_stmt_cache.hash;
+ mh_int_t i = mh_i32ptr_put(hash, &node, &old_node, NULL);
+ if (i == mh_end(hash)) {
+ diag_set(OutOfMemory, 0, "mh_i32ptr_put", "mh_i32ptr_node_t");
+ return -1;
+ }
+ assert(old_node == NULL);
+ session->prepared_stmt_cache.current_id++;
+ session->prepared_stmt_cache.mem_used += sql_stmt_sizeof(stmt);
+ return 0;
+}
+
+void
+sql_prepared_stmt_cache_delete(struct sql_stmt *stmt, uint32_t id)
+{
+ struct session *session = current_session();
+ struct mh_i32ptr_t *hash = session->prepared_stmt_cache.hash;
+ mh_int_t id_i = mh_i32ptr_find(hash, id, NULL);
+ mh_i32ptr_del(hash, id_i, NULL);
+ session->prepared_stmt_cache.mem_used -= sql_stmt_sizeof(stmt);
+ sql_finalize(stmt);
+}
+
+struct sql_stmt *
+sql_prepared_stmt_cache_find(uint32_t id)
+{
+ struct session *session = current_session();
+ struct mh_i32ptr_t *hash = session->prepared_stmt_cache.hash;
+ mh_int_t stmt = mh_i32ptr_find(hash, id, NULL);
+ if (stmt == mh_end(hash))
+ return NULL;
+ return mh_i32ptr_node(hash, stmt)->val;;
+}
+
+void
+sql_stmt_cache_erase(struct prep_stmt_cache *stmts)
+{
+ assert(stmts != NULL);
+ mh_int_t i;
+ mh_foreach(stmts->hash, i) {
+ struct sql_stmt *stmt =
+ (struct sql_stmt *) mh_i32ptr_node(stmts->hash, i)->val;
+ sql_finalize(stmt);
+ mh_i32ptr_del(stmts->hash, i, NULL);
+ }
+ /* Reset size to the default state. */
+ stmts->mem_used = sizeof(*stmts);
+}
+
+void
+sql_prepared_stmt_cache_set_size(size_t size)
+{
+ prep_stmt_cache_size = size;
+}
diff --git a/src/box/prep_stmt.h b/src/box/prep_stmt.h
new file mode 100644
index 000000000..4de0c321e
--- /dev/null
+++ b/src/box/prep_stmt.h
@@ -0,0 +1,88 @@
+#ifndef INCLUDES_PREP_STMT_H
+#define INCLUDES_PREP_STMT_H
+/*
+ * Copyright 2010-2019, Tarantool AUTHORS, please see AUTHORS file.
+ *
+ * Redistribution and use in source and binary forms, with or
+ * without modification, are permitted provided that the following
+ * conditions are met:
+ *
+ * 1. Redistributions of source code must retain the above
+ * copyright notice, this list of conditions and the
+ * following disclaimer.
+ *
+ * 2. Redistributions in binary form must reproduce the above
+ * copyright notice, this list of conditions and the following
+ * disclaimer in the documentation and/or other materials
+ * provided with the distribution.
+ *
+ * THIS SOFTWARE IS PROVIDED BY <COPYRIGHT HOLDER> ``AS IS'' AND
+ * ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
+ * TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
+ * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL
+ * <COPYRIGHT HOLDER> OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
+ * INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
+ * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
+ * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR
+ * BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
+ * LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+ * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF
+ * THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
+ * SUCH DAMAGE.
+ */
+#include <stdint.h>
+#include <stdio.h>
+
+#if defined(__cplusplus)
+extern "C" {
+#endif
+
+extern size_t prep_stmt_cache_size;
+
+struct prep_stmt_cache {
+ /** Size of memory currently occupied by prepared statements. */
+ size_t mem_used;
+ /**
+ * Cache is created per session, so cache can't be re-sized
+ * after @prep_stmt_cache is changed. Max memory size that
+ * can be used for cache.
+ */
+ size_t mem_quota;
+ /** Counter of sequential ids. */
+ uint32_t current_id;
+ /** Query ID -> struct prepared_stmt hash.*/
+ struct mh_i32ptr_t *hash;
+};
+
+struct sql_stmt;
+
+/**
+ * Save prepared statement to the prepared statement cache.
+ * Account cache size change. If the cache is full (i.e. memory
+ * quota is exceeded) diag error is raised. In case of success
+ * return id of prepared statement via output parameter @id.
+ */
+int
+sql_prepared_stmt_cache_insert(struct sql_stmt *stmt, uint32_t *id);
+
+/** Find entry by id. In case of search fail it returns NULL. */
+struct sql_stmt *
+sql_prepared_stmt_cache_find(uint32_t id);
+
+/** Remove entry from cache. Account cache size change. */
+void
+sql_prepared_stmt_cache_delete(struct sql_stmt *stmt, uint32_t id);
+
+/** Remove all elements from cache and deallocate them. */
+void
+sql_stmt_cache_erase(struct prep_stmt_cache *stmts);
+
+/** Set @prep_stmt_cache_size value. */
+void
+sql_prepared_stmt_cache_set_size(size_t size);
+
+#if defined(__cplusplus)
+} /* extern "C" { */
+#endif
+
+#endif
diff --git a/src/box/session.cc b/src/box/session.cc
index 59bf226dd..c34be4228 100644
--- a/src/box/session.cc
+++ b/src/box/session.cc
@@ -36,6 +36,7 @@
#include "user.h"
#include "error.h"
#include "tt_static.h"
+#include "execute.h"
const char *session_type_strs[] = {
"background",
@@ -147,6 +148,17 @@ session_create(enum session_type type)
struct mh_i64ptr_node_t node;
node.key = session->id;
node.val = session;
+ session->prepared_stmt_cache.mem_quota = prep_stmt_cache_size;
+ session->prepared_stmt_cache.mem_used =
+ sizeof(session->prepared_stmt_cache);
+ session->prepared_stmt_cache.hash = mh_i32ptr_new();
+ if (session->prepared_stmt_cache.hash == NULL) {
+ diag_set(OutOfMemory, 0, "mh_i32ptr_new",
+ "prepared statement cache");
+ mempool_free(&session_pool, session);
+ return NULL;
+ }
+ session->prepared_stmt_cache.current_id = 0;
mh_int_t k = mh_i64ptr_put(session_registry, &node, NULL, NULL);
@@ -232,6 +244,8 @@ session_destroy(struct session *session)
session_storage_cleanup(session->id);
struct mh_i64ptr_node_t node = { session->id, NULL };
mh_i64ptr_remove(session_registry, &node, NULL);
+ sql_stmt_cache_erase(&session->prepared_stmt_cache);
+ mh_i32ptr_delete(session->prepared_stmt_cache.hash);
mempool_free(&session_pool, session);
}
diff --git a/src/box/session.h b/src/box/session.h
index 85a2d940b..96837ba07 100644
--- a/src/box/session.h
+++ b/src/box/session.h
@@ -36,6 +36,7 @@
#include "fiber.h"
#include "user.h"
#include "authentication.h"
+#include "prep_stmt.h"
#if defined(__cplusplus)
extern "C" {
@@ -105,6 +106,7 @@ struct session {
struct credentials credentials;
/** Trigger for fiber on_stop to cleanup created on-demand session */
struct trigger fiber_on_stop;
+ struct prep_stmt_cache prepared_stmt_cache;
};
struct session_vtab {
diff --git a/test/app-tap/init_script.result b/test/app-tap/init_script.result
index 799297ba0..551a0bbeb 100644
--- a/test/app-tap/init_script.result
+++ b/test/app-tap/init_script.result
@@ -31,24 +31,25 @@ box.cfg
26 replication_sync_timeout:300
27 replication_timeout:1
28 slab_alloc_factor:1.05
-29 strip_core:true
-30 too_long_threshold:0.5
-31 vinyl_bloom_fpr:0.05
-32 vinyl_cache:134217728
-33 vinyl_dir:.
-34 vinyl_max_tuple_size:1048576
-35 vinyl_memory:134217728
-36 vinyl_page_size:8192
-37 vinyl_read_threads:1
-38 vinyl_run_count_per_level:2
-39 vinyl_run_size_ratio:3.5
-40 vinyl_timeout:60
-41 vinyl_write_threads:4
-42 wal_dir:.
-43 wal_dir_rescan_delay:2
-44 wal_max_size:268435456
-45 wal_mode:write
-46 worker_pool_threads:4
+29 sql_cache_size:5242880
+30 strip_core:true
+31 too_long_threshold:0.5
+32 vinyl_bloom_fpr:0.05
+33 vinyl_cache:134217728
+34 vinyl_dir:.
+35 vinyl_max_tuple_size:1048576
+36 vinyl_memory:134217728
+37 vinyl_page_size:8192
+38 vinyl_read_threads:1
+39 vinyl_run_count_per_level:2
+40 vinyl_run_size_ratio:3.5
+41 vinyl_timeout:60
+42 vinyl_write_threads:4
+43 wal_dir:.
+44 wal_dir_rescan_delay:2
+45 wal_max_size:268435456
+46 wal_mode:write
+47 worker_pool_threads:4
--
-- Test insert from detached fiber
--
diff --git a/test/box/admin.result b/test/box/admin.result
index 6126f3a97..852c1cde8 100644
--- a/test/box/admin.result
+++ b/test/box/admin.result
@@ -83,6 +83,8 @@ cfg_filter(box.cfg)
- 1
- - slab_alloc_factor
- 1.05
+ - - sql_cache_size
+ - 5242880
- - strip_core
- true
- - too_long_threshold
diff --git a/test/box/cfg.result b/test/box/cfg.result
index 5370bb870..9542e6375 100644
--- a/test/box/cfg.result
+++ b/test/box/cfg.result
@@ -71,6 +71,8 @@ cfg_filter(box.cfg)
| - 1
| - - slab_alloc_factor
| - 1.05
+ | - - sql_cache_size
+ | - 5242880
| - - strip_core
| - true
| - - too_long_threshold
@@ -170,6 +172,8 @@ cfg_filter(box.cfg)
| - 1
| - - slab_alloc_factor
| - 1.05
+ | - - sql_cache_size
+ | - 5242880
| - - strip_core
| - true
| - - too_long_threshold
@@ -315,6 +319,9 @@ box.cfg{memtx_memory = box.cfg.memtx_memory}
box.cfg{vinyl_memory = box.cfg.vinyl_memory}
| ---
| ...
+box.cfg{sql_cache_size = 1024}
+ | ---
+ | ...
--------------------------------------------------------------------------------
-- Test of default cfg options
diff --git a/test/box/cfg.test.lua b/test/box/cfg.test.lua
index 56ccb6767..e129568e6 100644
--- a/test/box/cfg.test.lua
+++ b/test/box/cfg.test.lua
@@ -51,6 +51,7 @@ box.cfg{replicaset_uuid = '12345678-0123-5678-1234-abcdefabcdef'}
box.cfg{memtx_memory = box.cfg.memtx_memory}
box.cfg{vinyl_memory = box.cfg.vinyl_memory}
+box.cfg{sql_cache_size = 1024}
--------------------------------------------------------------------------------
-- Test of default cfg options
diff --git a/test/box/misc.result b/test/box/misc.result
index b2930515b..78ffbf1dc 100644
--- a/test/box/misc.result
+++ b/test/box/misc.result
@@ -535,6 +535,7 @@ t;
203: box.error.BOOTSTRAP_READONLY
204: box.error.SQL_FUNC_WRONG_RET_COUNT
205: box.error.FUNC_INVALID_RETURN_TYPE
+ 206: box.error.SQL_PREPARE
...
test_run:cmd("setopt delimiter ''");
---
--
2.15.1
More information about the Tarantool-patches
mailing list