Arrangement of VACUUM, INTEGRITY_CHECK and OPTIMIZE

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Arrangement of VACUUM, INTEGRITY_CHECK and OPTIMIZE

spamhole@bluewin.ch
Hello all
As I was not able to find the information I have to ask here. Maybe it is trivial.
In which order should VACUUM, INTEGRITY_CHECK and OPTIMIZE be executed? I have been using this one so far but I am not sure about the latter two.
For the OPTIMIZE the documentation states: "... run just before closing each database connection ...". There isn't a reason to do it instead after opening a connection each time?
Thanks in advance for any feedback.
Cheers, Mischl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Arrangement of VACUUM, INTEGRITY_CHECK and OPTIMIZE

Keith Medcalf
>For the OPTIMIZE the documentation states: "... run just before
>closing each database connection ...". There isn't a reason to do it
>instead after opening a connection each time?

The documentation tells you under what circumstances PRAGMA OPTIMIZE does anything, in the current implementation.  It will  ONLY do anything if ALL THE FOLLOWING CONDITIONS ARE TRUE:

1) MASK bit 0x02 is set.
2) The query planner used sqlite_stat1-style statistics for one or more indexes of the table at some point during the lifetime of the current connection.
3) One or more indexes of the table are currently unanalyzed or the number of rows in the table has increased by 25 times or more since the last time ANALYZE was run.

Since bullet 2 is ALWAYS FALSE when opening a connection, PRAGMA OPTIMIZE will never do anything when run immediately after opening a new connection (in the current implementation).  

You have to actually DO SOMETHING for PRAGMA OPTIMIZE to have any effect.  That is why you run it AFTER you have done something with the connection, right before you close it ... (or, if you keep the connection open for a long time -- long being defined as seconds / minutes / hours / weeks / months / years / decades / centuries / millenia / aeons -- you may want to run it "periodically" ... "periodically" being defined as every 1 or more seconds / minutes / hours / days / weeks / months / years / decades / centuries / millenia / aeons).

https://sqlite.org/pragma.html#pragma_optimize

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Arrangement of VACUUM, INTEGRITY_CHECK and OPTIMIZE

Richard Hipp-3
In reply to this post by spamhole@bluewin.ch
On 1/9/19, [hidden email] <[hidden email]> wrote:
> In which order should VACUUM, INTEGRITY_CHECK and OPTIMIZE be executed?

I suppose that depends on what you are trying to accomplish?  Why do
you execute them at all?

I suppose INTEGRITY_CHECK should be run with first opening the
database file, to ensure that what you are opening really is a valid
SQLite database.  Maybe also run this after some other process that
you do not trust writes to the file.

Run VACUUM after deleting a lot of content.  Maybe also run this after
extensive changes when you want to optimize future access.

The OPTIMIZE pragma uses information saved from prior queries run on
the same database connection to try to determine whether or not
ANALYZE should be run.  If OPTIMIZE thinks ANALYZE should be run, it
runs it for you automatically.  Usually ANALYZE is not necessary and
OPTIMIZE is a no-op, however.  As OPTIMIZE uses statistics gathered
from execution of prior queries on the same database connection, it is
pointless to run it until later in the lifecycle of the database
connection.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Arrangement of VACUUM, INTEGRITY_CHECK and OPTIMIZE

Simon Slavin-3
In reply to this post by spamhole@bluewin.ch
On 9 Jan 2019, at 2:01pm, [hidden email] wrote:

> In which order should VACUUM, INTEGRITY_CHECK and OPTIMIZE be executed?

If you are in any situation where INTEGRITY_CHECK should be run, do not make any changes to the database (e.g. ANALYZE) first.  Those changes might overwrite parts of the file that may show that the database was corrupt.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users