prosody
0aaf67f70015
plugins/mod_storage_sql.lua

Find changesets by keywords by author, files, the commit message, revision number or hash, or revset expression.

Software / code / prosody

Comparison

plugins/mod_storage_sql.lua @ 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
parent 13148:c6f46f33cecf
child 13150:9e6ede86d35d
comparison
equal deleted inserted replaced
13148:c6f46f33cecf 13149:0aaf67f70015
908 local option = row[1]:lower(); 908 local option = row[1]:lower();
909 local opt, val = option:match("^([^=]+)=(.*)$"); 909 local opt, val = option:match("^([^=]+)=(.*)$");
910 compile_options[opt or option] = tonumber(val) or val or true; 910 compile_options[opt or option] = tonumber(val) or val or true;
911 end 911 end
912 engine.sqlite_compile_options = compile_options; 912 engine.sqlite_compile_options = compile_options;
913
914 local journal_mode = "delete";
915 for row in engine:select[[PRAGMA journal_mode;]] do
916 journal_mode = row[1];
917 end
918
919 -- Note: These things can't be changed with in a transaction. LuaDBI
920 -- opens a transaction automatically for every statement(?), so this
921 -- will not work there.
922 local tune = module:get_option_string("sqlite_tune", "default");
923 if tune == "normal" then
924 if journal_mode ~= "wal" then
925 engine:execute("PRAGMA journal_mode=WAL;");
926 end
927 engine:execute("PRAGMA auto_vacuum=FULL;");
928 engine:execute("PRAGMA synchronous=NORMAL;")
929 elseif tune == "fast" then
930 if journal_mode ~= "wal" then
931 engine:execute("PRAGMA journal_mode=WAL;");
932 end
933 if compile_options.secure_delete then
934 engine:execute("PRAGMA secure_delete=FAST;");
935 end
936 engine:execute("PRAGMA synchronous=OFF;")
937 engine:execute("PRAGMA fullfsync=0;")
938 elseif tune == "safe" then
939 if journal_mode ~= "delete" then
940 engine:execute("PRAGMA journal_mode=DELETE;");
941 end
942 engine:execute("PRAGMA synchronous=EXTRA;")
943 engine:execute("PRAGMA fullfsync=1;")
944 end
945
946 for row in engine:select[[PRAGMA journal_mode;]] do
947 journal_mode = row[1];
948 end
949
950 module:log("debug", "SQLite3 database %q operating with journal_mode=%s", engine.params.database, journal_mode);
913 end 951 end
914 module:set_status("info", "Connected to " .. engine.params.driver); 952 module:set_status("info", "Connected to " .. engine.params.driver);
915 end, function (engine) -- luacheck: ignore 431/engine 953 end, function (engine) -- luacheck: ignore 431/engine
916 module:set_status("error", "Disconnected from " .. engine.params.driver); 954 module:set_status("error", "Disconnected from " .. engine.params.driver);
917 end); 955 end);