>From my personal experience, performance instability of SQLite queries >(drastically jumping from milliseconds to seconds and back) I haven't seen that sort of instability. We found some queries were consistently poor until we ran ANALYZE, due to nature of the data, indexes and queries. Altering SQL was insufficient to correct the problem.
I'm familiar with EXPLAIN QUERY PLAN. The query involves 5 tables. EXPLAIN QUERY PLAN shows table scans for 3 of them before ANALYZE vs after there's only 1 table scan (and that's the table with 1 record so using an index wouldn't materially affect perf). This is probably a (the?) reason for the perf delta. This doesn't answer my question -- logs show PRAGMA optimize; appears to skip the busy-handler and return SQLITE_BUSY. The documentation is silent re busy. In fact the only related comment is https://sqlite.org/pragma.html#pragma_optimize ...This pragma is usually a no-op or nearly so and is very fast. However if SQLite feels that performing database optimizations (such as running ANALYZE or creating new indexes) will improve the performance of future queries, then some database I/O may be done. Applications that want to limit the amount of work performed can set a timer that will invoke sqlite3_interrupt() if the pragma goes on for too long.... SQLite's not performing too much work. It's performing NO work. Is this intended? How is optimize intended to interact with busy/locked conditions? FYI ANALYZE docs (https://sqlite.org/lang_analyze.html) are equally silent regarding behavior in the face of locks and busy|locked conditions I've looked over the source but we're into the deeper end of the complexity pool. I see hints of locks in a few spots but nothing definitive or clear. How are PRAGMA optimize; and ANALYZE; expected to behave in the face of busy|locked conditions? How are they expected to behave in relation to the busy-handler? - Howard -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Paul Sent: Tuesday, October 3, 2017 10:34 PM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] PRAGMA optimize; == no busy handler? From my personal experience, performance instability of SQLite queries (drastically jumping from milliseconds to seconds and back) that is fixable by running ANALYZE means that your queries are not optimal (some crucial indices are missing). The reason ANALYZE helps is because the info that planner receives helps to mitigate the absence of important indices by adapting the strategy. Say for example, you have a query that involves few tables being a JOIN-ed. There may be the case when SQLite has to scan one of the tables but is does not know which, so it uses some default logic that does not account table size (since SQLite does not know it). As the result, wrong table may be chosen (the largest one). Why this happens exactly after the first record is inserted? Probably because Query Planner is smart and uses some meta info. Probably SQLite does not allocate a page for empty tables before the first record is inserted. Query Planner may take an advantage of this knowledge and optimizing query accordingly. All of this is just a speculation, of course. What you definitely should do is run EXPLAIN QUERY PLAN on the sluggish query. Better yet, run EXPLAIN QUERY PLAN on *every* query, just to be sure. -Paul > >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:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of > Simon Slavin > Sent: Tuesday, October 3, 2017 5:31 PM > To: SQLite mailing list <> sqlite-users@mailinglists.sqlite.org> > Subject: Re: [sqlite] PRAGMA optimize; == no busy handler? > > On 4 Oct 2017, at 12:54am, Howard Kapustein <howard.kapust...@microsoft.com> > 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 > sqlite-users@mailinglists.sqlite.orghttps://na01.safelinks.protection. > outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmail > man%2Flistinfo%2Fsqlite-users&data=02%7C01%7Choward.kapustein%40micros > oft.com%7Cb1fea679d7db4ab4a6bd08d50abf426e%7C72f988bf86f141af91ab2d7cd > 011db47%7C1%7C0%7C636426738940629164&sdata=NKUh3nPjsvQ25UMpcEy%2FEfhwW > zB%2BuQ02a%2B1NrBR7cAY%3D&reserved=0 > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.orghttp://mailinglists.sqlite.org/cgi > -bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org 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%7Cfc1032e85e9c43618e0308d50ae97db0%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636426920328758792&sdata=pcG1kwyWbh4lfLj%2B3CQP3Qk8u0pVW9fEgWjQflW6L%2Fw%3D&reserved=0 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
