In the case of a database that ran hot all the time a better strategy would be to have an online backup of the database running at all time and then run analyze on that periodically, then update the stats table in the live database when that was finished. I'm not sure what the rules are for updating the stats table for an open database are, but that would be a very useful enhancement if it wasn't very complicated to implement.
> On Oct 6, 2017, at 8:26 PM, Toby Dickenson <t...@tarind.com> wrote: > > Thats problematic. It means I have to guess how long my analyze might take, > and guess at a suitable time when that scan wont block other clients. Its a > good reason to never call analyze. > > Does it have to be so? It seems to me (possibly naively) that the scan > could be performed in one long read transaction, then a separate quick > write transaction could be used to update the stat tables. Theres no need > for the whole operation to be atomic, right? > > On 5 October 2017 at 00:37, Simon Slavin <slav...@bigfraud.org> wrote: > >> >> >> On 5 Oct 2017, at 12:07am, Toby Dickenson <t...@tarind.com> wrote: >> >>> How does PRAGMA optimize or ANALYSE affect concurrency? Is there any >>> risk that it might leave the database locked for the full duration of >>> the scan? >> >> ANALYZE does this every time. It’s not possible to analyze a database >> while it’s being changed. >> >> I’d expect "PRAGMA optimize" to do the same but I’m not sure. >> >> Simon. >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
