Software / code / prosody
Changeset
13149:0aaf67f70015
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.
| author | Kim Alvefur <zash@zash.se> |
|---|---|
| date | Sun, 11 Jun 2023 17:04:11 +0200 |
| parents | 13148:c6f46f33cecf |
| children | 13150:9e6ede86d35d |
| files | plugins/mod_storage_sql.lua |
| diffstat | 1 files changed, 38 insertions(+), 0 deletions(-) [+] |
line wrap: on
line diff
--- 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
