Re: [sqlite] PRAGMA optimize; == no busy handler?

in.yu.edu> Date:2017-10-06 16:23 (GMT-07:00) To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] PRAGMA optimize; == no busy handler? _______ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http

Re: [sqlite] PRAGMA optimize; == no busy handler?

> On Oct 6, 2017, at 3:13 PM, Roman Fleysher > wrote: > > Vacuuming seems to belong to a different thread, but let me say that it is > not always warranted. Vacuuming may change/reassign ROWIDs. The docs say "The VACUUM command may change the ROWIDs of

Re: [sqlite] PRAGMA optimize; == no busy handler?

From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of Jens Alfke [j...@mooseyard.com] Sent: Wednesday, October 04, 2017 6:29 PM To: SQLite mailing list Subject: Re: [sqlite] PRAGMA optimize; == no busy handler? > On Oct 4, 2017, at 10:30 AM, Richard Hipp

Re: [sqlite] PRAGMA optimize; == no busy handler?

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

Re: [sqlite] PRAGMA optimize; == no busy handler?

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

Re: [sqlite] PRAGMA optimize; == no busy handler?

Android sucks badly.  Dont knpw who it is designed for but it sure aint me ... Anyway, "offline maintenance" is for IT Shops to annoy their customers.  I think everywhere I worked for the past 40 years has been a 25 by 8 by 366 operation. So scheduling something for "idle hours" meant knowing

Re: [sqlite] PRAGMA optimize; == no busy handler?

On 5 Oct 2017, at 12:07am, Toby Dickenson 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

Re: [sqlite] PRAGMA optimize; == no busy handler?

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? On 4 October 2017 at 23:29, Jens Alfke wrote: > > >> On Oct 4, 2017, at 10:30 AM, Richard Hipp wrote: >> >>

Re: [sqlite] PRAGMA optimize; == no busy handler?

> On Oct 4, 2017, at 10:30 AM, Richard Hipp wrote: > > The PRAGMA optimize command is our effort to move further in the > direction of a "smart" SQLite that always automatically "does the > right thing" with respect to gathering and using database statistics. That’s a great

Re: [sqlite] PRAGMA optimize; == no busy handler?

The databases I make are pretty darned small and insignificant that even full table scans wouldn't show that much of a hit in performance. However, if there were an API or SQL code or something we can add to our code that would give you meaningful results (other than "Yes, it works") we could

Re: [sqlite] PRAGMA optimize; == no busy handler?

ed into newly created application files. > > > > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On > Behalf Of Jens Alfke > Sent: Wednesday, October 04, 2017 12:06 PM > To: SQLite mailing list > Subject: Re: [sqlite] PRAGMA optimi

Re: [sqlite] PRAGMA optimize; == no busy handler?

ubject: Re: [sqlite] PRAGMA optimize; == no busy handler? > So why not work around the whole process ? On your development system, > create a database with plausible data in. Run ANALYZE. Then copy all the > tables named "sqlite_stat*" into a new database. That’s a

Re: [sqlite] PRAGMA optimize; == no busy handler?

On 4 Oct 2017, at 5:06pm, Jens Alfke wrote: > That’s an interesting idea. I’ve been assuming that ANALYZE was dependent on > the exact contents of the database, but if its results can be canned and > applied to new databases, that’s a good optimization. It works and I’ve

Re: [sqlite] PRAGMA optimize; == no busy handler?

Simon Slavin wrote: > > It is not expected that you’ll try to run ANALYZE while a database is in use. > It’s intended for offline-maintenance. “Offline maintenance” is for servers  I suspect it’s not relevant to the majority* of use cases for SQLite, like user-facing applications and

Re: [sqlite] PRAGMA optimize; == no busy handler?

ober 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

Re: [sqlite] PRAGMA optimize; == no busy handler?

om: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin Sent: Tuesday, October 3, 2017 6:40 PM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] PRAGMA optimize; == no busy handler? On 4 Oct 2017, at 2:23am, Howard Ka

Re: [sqlite] PRAGMA optimize; == no busy handler?

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 Messa

Re: [sqlite] PRAGMA optimize; == no busy handler?

On 4 Oct 2017, at 2:23am, Howard Kapustein wrote: > 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 > 0x1=change the criteria from "increased by 25

Re: [sqlite] PRAGMA optimize; == no busy handler?

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

Re: [sqlite] PRAGMA optimize; == no busy handler?

On 4 Oct 2017, at 12:54am, Howard Kapustein 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

[sqlite] PRAGMA optimize; == no busy handler?

Using SQLite 3.20.1 I notice a flood of log events sometimes when I call PRAGMA optimize; Warning 0x5: statement aborts at 1: [PRAGMA optimize;] database is locked And a few times Warning 0x5: statement aborts at 2: [PRAGMA optimize;] database is locked And even once Warning 0x5: statement