# HG changeset patch # User Kim Alvefur # Date 1686495851 -7200 # Node ID 0aaf67f700150174d10c2335d3cb15ed196078ef # Parent c6f46f33cecf4c361be7d73a7679c5b6c4bfd0a0 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. diff -r c6f46f33cecf -r 0aaf67f70015 plugins/mod_storage_sql.lua --- a/plugins/mod_storage_sql.lua Sat Jun 10 14:01:56 2023 +0200 +++ b/plugins/mod_storage_sql.lua Sun Jun 11 17:04:11 2023 +0200 @@ -910,6 +910,44 @@ 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