Slow updates, many with exactly 1000ms?
(1) By Kukulkan on 2020-04-16 10:52:07 [link] [source]
we utilize a sqlite database with many reads and not so many writes but also many updates. The database is hammered by several threads and we initialize with
PRAGMA synchronous = 0 PRAGMA temp_store = 2 PRAGMA journal_mode = WAL
So far this enhanced speed a lot and seems reliable, but we measure the time of all SQL statements and in the logfiles we find hundreds of entries like these:
SLOW 1000ms SQL update files set state = state & ~117440512 where id = 84 SLOW 1000ms SQL update files set state = state & ~117440512 where id = 103
"id" is an indexed column.
We have various slow queries (eg 100 to 300ms) for several inserts and updates, which is okay or we can explain.
But the speciality of the mentioned UPDATE statements is that they all are close to exactly 1000ms (one second). We do not have any timeout or such in our code, but there are many LOG entries with exactly one second.
Is there anything that may explain the duration of exactly 1000ms for UPDATE queries? Is there some timeout in SQLite?
(2) By Gunter Hick (gunter_hick) on 2020-04-16 11:08:16 in reply to 1 [link] [source]
Maybe you are using a busy handler with a timeout of 1000ms and the logged statements are actually aborted with an error code of SQLITE_BUSY, because some other thread is keeping a Transaction open for more than that (e.g. several slow updates within one Transaction).
(3) By Kukulkan on 2020-04-16 12:34:53 in reply to 2 [link] [source]
thanks. We're using SQLite 3.27.0 through PureBasic 5.71 LTS, which does a quite simple wrapper around embedded sqlite only. So there is no handler between except PureBasic. I will ask there for a possible timeout in the wrapper or maybe ignoring SQLITE_BUSY?
Anyway, is a query that returns SQLITE_BUSY still executed and finished in the background? Or does it need to get repeated?
(4) By Keith Medcalf (kmedcalf) on 2020-04-16 14:03:08 in reply to 3 [link] [source]
No, a query that returns SQLITE_BUSY is not executed because SQLITE is BUSY doing something else. You have to resubmit your query at a later time when SQLITE is not BUSY.
Secondly, updates are writes. How do you think something is updated if it does not write?
Thirdly, when using WAL, writes will periodically have to be written to the database from the WAL whenever the number of pages in the WAL exceeds the maximum you have specified. This occurs as transaction commit time so is "credited" to the statement which commits the transaction.
(5) By Ryan Smith (cuz) on 2020-04-16 14:10:56 in reply to 3 [source]
Most wrappers do implement some form of transaction control and busy handling or progress handling, if only to be able to stop a run-away query - but I don't know the Purebasic one personally to confirm.
When SQLite returns any error code, it most certainly did not, and will not, magically complete that query at any later stage*. If it fails it means the ball is in your court again and you can decide how to continue armed with this knowledge of the prior query attempt having not been completed due to the returned error code.
If you did set a good timeout though, it may itself try, pause, try again, pause longer, try again, until total pause time > timeout specified, but then in that case there is never an error code reported back, unless the timeout completely expires without the query starting - which /may/ be the case for you.
Note that After the query started, it may well take any amount of time to complete, which is dependent on the query complexity only and has nothing to do with the timeout.