From 5d6098bc1a1fc0121c4722f89d3acc13ca8475b6 Mon Sep 17 00:00:00 2001 From: Kim Alvefur Date: Sun, 11 Jun 2023 17:04:11 +0200 Subject: mod_storage_sql: Add setting to tune SQLite3 performance vs safety Notably the default journal_mode of DELETE is somewhat slow, some users might want to catch up to the amazing performance of internal storage. --- plugins/mod_storage_sql.lua | 38 ++++++++++++++++++++++++++++++++++++++ 1 file changed, 38 insertions(+) (limited to 'plugins/mod_storage_sql.lua') diff --git a/plugins/mod_storage_sql.lua b/plugins/mod_storage_sql.lua index 3caf4572..480cd517 100644 --- a/plugins/mod_storage_sql.lua +++ b/plugins/mod_storage_sql.lua @@ -910,6 +910,44 @@ function module.load() compile_options[opt or option] = tonumber(val) or val or true; end engine.sqlite_compile_options = compile_options; + + local journal_mode = "delete"; + for row in engine:select[[PRAGMA journal_mode;]] do + journal_mode = row[1]; + end + + -- Note: These things can't be changed with in a transaction. LuaDBI + -- opens a transaction automatically for every statement(?), so this + -- will not work there. + local tune = module:get_option_string("sqlite_tune", "default"); + if tune == "normal" then + if journal_mode ~= "wal" then + engine:execute("PRAGMA journal_mode=WAL;"); + end + engine:execute("PRAGMA auto_vacuum=FULL;"); + engine:execute("PRAGMA synchronous=NORMAL;") + elseif tune == "fast" then + if journal_mode ~= "wal" then + engine:execute("PRAGMA journal_mode=WAL;"); + end + if compile_options.secure_delete then + engine:execute("PRAGMA secure_delete=FAST;"); + end + engine:execute("PRAGMA synchronous=OFF;") + engine:execute("PRAGMA fullfsync=0;") + elseif tune == "safe" then + if journal_mode ~= "delete" then + engine:execute("PRAGMA journal_mode=DELETE;"); + end + engine:execute("PRAGMA synchronous=EXTRA;") + engine:execute("PRAGMA fullfsync=1;") + end + + for row in engine:select[[PRAGMA journal_mode;]] do + journal_mode = row[1]; + end + + module:log("debug", "SQLite3 database %q operating with journal_mode=%s", engine.params.database, journal_mode); end module:set_status("info", "Connected to " .. engine.params.driver); end, function (engine) -- luacheck: ignore 431/engine -- cgit v1.2.3