>Don't do that
Mostly sure, but there's some cases SQLite will skip the busyhandler and
immediately return, but they're not permanently-busy conditions. We have
multiple threads across multiple processes using the database
> why you’re doing "PRAGMA optimize" while other parts of your program are
> accessing the database
Because we don't necessarily know there's other work in progress, and we don't
have a good opportunity to ensure we do it later but-no-later than when we need
it
>It makes more sense just to put ANALYZE into some sort of end-of-month
>procedure
>or a manual maintenance procedure your users run only when nobody else is
>using the system
That's what we did but the real world isn't that simple
Last month we did...
* ANALYZE when the staterepository service starts (effective boot time) if it
hasn't been done in a while (1 or 3 days, I forget)
* ANALYZE at the end of a deployment operation if it hasn't been done in a
while (1min I think)
@ 1st login there's 30+ operations in a short window (1-2 minutes?) and folks
are very anal^H^H^H^Hconscious re 1st login perf. The workload is rather
variable all told thus the 1min delay. We usually wind up hitting this a couple
of times @ 1st login. That primes sqlite_stat1 sufficiently that statements
execute in ms (at most) as expected. .
We recently found a problem where you click on a hyperlink in Edge which (among
other things) queries the database and _sometimes_ takes 30s+ instead of
near-zero. But we couldn't repro it, and in the rare case someone hit it the
problem went away on its own for no apparent reason either. We finally found
the problem was a table involved in the query had 1 record and executing the
raw SQL took an abnormally long time - but ANALYZE and re-query and time was
near-zero. As if we'd added a row to the table for the first time but didn't do
ANALYZE so SQLite had no stats to inform its planning, and of course if you
reboot after a day or 3 the 'maintenance' would kick in and update stats. Or if
something else happened to get installed on the system causing a new deployment
operation to call ANALYZE. But the user experience was poor and too
unpredictable...
PRAGMA optimize; fixed this. We get the ANALYZE benefits we need, but only for
those tables that grew significantly (and 0 to >0 qualifies), and
We also cache connections for what can be lifetime of a service, so optimize @
connection close is too long to wait.
My quick hack is to change the PRAGMA optimize per deployment operation to a
best-effort - remove the busy handler, try it and restore. I suspect I need
something more involved but I'm still weighing my options. The big Q is
understanding PRAGMA optimize (and ANALYZE) in relation to busy|locked
conditions.
What do you think of an option to only analyze tables that have grown from 0
records to >0 e.g. PRAGMA optimize(0x10002) so 2=ANALYZE if beneficial and
0x10000=change the criteria from "increased by 25 times or more since the last"
to "increased from 0 records to 1+ record since the last? I've seen problems
when we have data w/o analyze but not if we grow from N to >N, thought that
could be coincidental where we analyze often enough stats never get too out of
proportion to the data. So far the only definitive statement I can make is
ANALYZE on a table that went from 0 records to 1+ notably improves queries by
orders of magnitude.
- Howard
-----Original Message-----
From: sqlite-users [mailto:[email protected]] On
Behalf Of Simon Slavin
Sent: Tuesday, October 3, 2017 5:31 PM
To: SQLite mailing list <[email protected]>
Subject: Re: [sqlite] PRAGMA optimize; == no busy handler?
On 4 Oct 2017, at 12:54am, Howard Kapustein <[email protected]>
wrote:
> We use the default busy-handler w/timeout=250ms and call sqlite_exec("PRAGMA
> optimize;") in a loop until success or non-busy/locked error or some ungodly
> amount of time elapses (~5min). The logs indicate PRAGMA optimize; fails due
> to SQLITE_BUSY w/o ever going through the busy-handler, and then we spin
> trying a few thousand times.
Don’t do that. It defeats the purpose of the timeout you set. If you want a
longer timeout than 250ms, set that as your timeout. SQLite’s own retry
process is clever. It uses exponential backoff and can get at the locks at a
lower level than your own code.
I’ve used timeouts in my own code of 5 minutes. And I never had a user report
the error message they’d see if that failed.
> Docs for PRAGMA optimize; says nothing about busy|locked scenarios, nor any
> need to call this in a transaction (nor even if that's legal). Ditto ANALYZE
> docs say nothing about busy|locked scenarios
>
> What's expected if PRAGMA optimize hits a busy|locked scenario?
> What am I the caller expected to do?
The only locking in SQLite is to lock the entire database. So the initial
question is whether you do have some other thread/process accessing the
database.
I would ask you to reconsider why you’re doing "PRAGMA optimize" while other
parts of your program are accessing the database. Right now it tells you only
whether ANALYZE is needed, and there’s no harm in doing ANALYZE when it’s not
needed.
It makes more sense just to put ANALYZE into some sort of end-of-month
procedure or a manual maintenance procedure your users run only when nobody
else is using the system. That way the routine takes a more predictable amount
of time, which users like.
Simon.
_______________________________________________
sqlite-users mailing list
[email protected]
https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&data=02%7C01%7Choward.kapustein%40microsoft.com%7Cb1fea679d7db4ab4a6bd08d50abf426e%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636426738940629164&sdata=NKUh3nPjsvQ25UMpcEy%2FEfhwWzB%2BuQ02a%2B1NrBR7cAY%3D&reserved=0
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
