Hello,
I have seen a strange behavior when using sqlite 3.20.1, and I was
hoping someone could help explain it. I have a database with a very
simple schema:
$ sqlite3 build/cache/bb_persist_data.sqlite3 SQLite version 3.20.1
2017-08-24 16:21:36 Enter ".help" for usage hints. sqlite> .schema
CREATE TABLE BB_URI_HEADREVS(key TEXT PRIMARY KEY NOT NULL, value
TEXT);
When our application starts up, it determines if it need to clear our
the table. In the event that it does (which is most of the time) it
uses the following query:
BEGIN TRANSACTION; DELETE FROM BB_URI_HEADREVS; COMMIT;
Normally, this query takes no more than 1-3 seconds to complete,
however, on rare occasion this will take an order of magnitude more
(20-30 seconds). The real kicker here, is that I am never adding any
rows to the database (e.g. it is always completely empty), so an order
of magnitude increase seems unnecessary to erase an already empty
table. If it makes any difference, the actual delay occurs when the
COMMIT statement is executed, the DELETE FROM goes pretty fast.
For reference, the following pragmas are used:
pragma synchronous = normal; pragma journal_mode = WAL; pragma
wal_autocheckpoint = 100;
I use the small wal_autocheckpoint because the database is read-
mostly, and we would rather have fast readers at the expense of
occasional slow writes. WAL mode is used because we access the database
from multiple processes, and we need the occasional write to not block
readers.
To be completely honest, this problem manifests under heavy I/O load,
so I'm not suggesting it is necessarily sure that it is sqlite at
fault, but the order of magnitude difference seems a bit extreme.
1) I used to use the rollback journal and didn't really see this
problem, is there something about WAL mode that is more sensitive to
I/O delay than the rollback journal?
2) Is there something that sqlite is doing "in the background" that
might be making this query slow?
3) Are the some settings I could change that might make a difference?
4) Is there some sort of profiling I could enable to help pinpoint (or
confirm) that this is indeed due to I/O delay and not something
internal to sqlite?
For reference, here is the complete log of SQL that the application
executes on startup. Only the delay takes a significant amount of time.
BEGIN TRANSACTION; CREATE TABLE IF NOT EXISTS BB_URI_HEADREVS(key TEXT
PRIMARY KEY NOT NULL, value TEXT); COMMIT;
BEGIN TRANSACTION; SELECT COUNT(key) FROM BB_URI_HEADREVS;
COMMIT; BEGIN TRANSACTION; DELETE FROM BB_URI_HEADREVS; COMMIT;
Thanks all for your time,
--
Joshua Watt
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
