Database occasionally very slow for trivial query

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Database occasionally very slow for trivial query

Joshua Watt
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 <[hidden email]>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Database occasionally very slow for trivial query

Hick Gunter
NB: SELECT COUNT(<field>) FROM <table> requires a traversal of the whole table (or index, if one exists for the field) and returns the number of non-NULL entries, whereas SELECT COUNT() FROM <table> invokes a special opcode to retrieve the total number of rows without actually acessing any of them and so is very much faster.

-----Urspr√ľngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Joshua Watt
Gesendet: Montag, 10. September 2018 16:28
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] [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 <[hidden email]>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database occasionally very slow for trivial query

Rowan Worth-2
In reply to this post by Joshua Watt
On 10 September 2018 at 22:28, Joshua Watt <[hidden email]> wrote:

>   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).
>


> 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.
>

I don't have much experience with WAL mode, but these two statements jump
out at me. Is it simply the auto-checkpoint which ends up taking a long
time when the machine is already under heavy i/o load?

-Rowan
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users