[sqlite] Database occasionally very slow for trivial query
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
Re: [sqlite] Strange behavior with wal_checkpoint PRAGMA
On Wed, 2018-02-14 at 08:13 -0600, Joshua Watt wrote: > Hello, > > I observed the following behavior when using the wal_checkpoint > PRAGMA > in TRUNCATE mode: > > SQLite version 3.22.0 2018-01-22 18:45:57 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > sqlite> .open test.db > sqlite> PRAGMA journal_mode = WAL; > wal > sqlite> create table test ( key INTEGER PRIMARY KEY, val INTEGER ); > sqlite> insert into test values ( 5, 6 ); > sqlite> insert into test values ( 6, 6 ); > sqlite> PRAGMA wal_checkpoint(TRUNCATE); > 0|0|0 > > According to the documentation, the second and third values should > correspond to the number of pages in the write-ahead log and the > number > of pages that have been moved back to the database file. This works > as > expected in the other modes, but in TRUNCATE mode the second and > third > return values are always zero. > > I believe this is because walCheckpoint() triggers a call to > walRestartHdr() if the mode is SQLITE_CHEKPOINT_TRUNCATE. Once > walCheckpoint() returns, sqlite3WalCheckpoint() attempts to extract > the > values from the already restarted wal header. > > Has anyone else expirenced this issue? Ping? > > Thanks, > Joshua Watt ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Strange behavior with wal_checkpoint PRAGMA
Hello, I observed the following behavior when using the wal_checkpoint PRAGMA in TRUNCATE mode: SQLite version 3.22.0 2018-01-22 18:45:57 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .open test.db sqlite> PRAGMA journal_mode = WAL; wal sqlite> create table test ( key INTEGER PRIMARY KEY, val INTEGER ); sqlite> insert into test values ( 5, 6 ); sqlite> insert into test values ( 6, 6 ); sqlite> PRAGMA wal_checkpoint(TRUNCATE); 0|0|0 According to the documentation, the second and third values should correspond to the number of pages in the write-ahead log and the number of pages that have been moved back to the database file. This works as expected in the other modes, but in TRUNCATE mode the second and third return values are always zero. I believe this is because walCheckpoint() triggers a call to walRestartHdr() if the mode is SQLITE_CHEKPOINT_TRUNCATE. Once walCheckpoint() returns, sqlite3WalCheckpoint() attempts to extract the values from the already restarted wal header. Has anyone else expirenced this issue? Thanks, Joshua Watt ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
