SQLite Forum

Is it safe to VACUUM on a new database connection?

Is it safe to VACUUM on a new database connection?

(1) By anonymous on 2020-06-08 03:59:59 [link] [source]


I continuously get an error when I run VACUUM that some statements are still running. I have no way of telling which statements those are, app is multi-threaded, even though I try to close all readers right away.

I read in the docs about VACUUM (https://sqlite.com/lang_vacuum.html):

"A VACUUM will fail if there is an open transaction on the database connection that is attempting to run the VACUUM. Unfinalized SQL statements typically hold a read transaction open, so the VACUUM might fail if there are unfinalized SQL statements on the same connection. VACUUM (but not VACUUM INTO) is a write operation and so if another database connection is holding a lock that prevents writes, then the VACUUM will fail."

I decided to open a new connection, and run just the VACUUM command on that. I understand that the other open transactions are gone. But is it safe to do this. What will happen with the other running statements? Will they fail? They seem to finish fine as far as I can tell.

So main question is: Is it safe to do VACUUM in a new connection, and will the DB end up properly defraged?

Thanks Andy

(2) By Rowan Worth (sqweek) on 2020-06-08 04:29:07 in reply to 1 [link] [source]

Yes VACUUM in a separate connection is safe. It will still have to wait for locks held by the old connection to clear before it can proceed, and while it is running it will prevent other write transactions (and eventually read transactions) from starting (they'll fail with SQLITE_BUSY).

Note it's generally advisable to have an sqlite connection used by only one thread at a time, as transactionality gets hard to reason about if you have a bunch of threads interleaving different queries. Transactions happen at the connection level not the thread level.

Also note it is possible to determine what statement(s) are currently active via the sqlite3_next_stmt() interface or the SQLITE_STMT virtual table.

(3) By Simon Slavin (slavin) on 2020-06-08 16:35:47 in reply to 1 [source]

Rowan replied very well to your question, but I'm alarmed that you have unexpected access.

Ignoring this new VACUUM you want to add, do you have one connection being used by many threads, or many connections ?

Have you set a timeout on every connection to the database ? If not, you should do, and this will reduce access clashes.

(4) By Warren Young (wyoung) on 2020-06-08 20:14:25 in reply to 1 [link] [source]

I continuously get an error when I run VACUUM that some statements are still running.

VACUUM is best run at a time when you can predict that the DB will be idle. Good times include:

  • App shutdown time, just before the last conn is closed
  • App startup time, before any of these other threads start
  • 3 a.m. in an always-running app serving a single regional organization

If you can never predict a time when the DB will be idle, you might not want to VACUUM automatically at all, since it can take a long time, and such always-in-use apps generally have tight service levels. You might not be allowed to lock the whole system up for arbitrarily long times. In such cases, you'd schedule the VACUUM for manual downtime, such as during upgrades.

Frequent VACUUM isn't super useful in a lot of apps anyway. Once a month or once a year might suffice.

...even though I try to close all readers right away.

If you literally mean that you sqlite3_close() the conn after each query, it's both unnecessary from the point of view of this question and it'll also slow SQLite down considerably.

It's unnecessary because VACUUM will still run if there's simply an idle connection to the DB. It takes an unfinalized transaction to do that. If you aren't using explicit BEGIN/COMMIT, you're still creating transactions, one per SQL statement. Thus, an app doing hundreds of simple overlapping queries a second will likely have some transaction open at any given instant.

It slows SQLite down to reopen the conn for each query because SQLite has to do a lot of work (parsing the schema, priming caches, etc.) on each connection. For a threaded app like yours, you probably want to set up a connection pool, so that the conn gets put back into the pool when a thread is done with it rather than close it, so another thread can then reuse that opened conn.

(5) By Keith Medcalf (kmedcalf) on 2020-06-09 08:48:08 in reply to 1 [link] [source]

even though I try to close all readers right away

Define what is "close all readers" in your mind.

There is no way to "close" a "reader". You may "close" a "connection" and you may "finalize" a "statement". Which one of these options equates with "close a reader" in your mind?

You will note that the error is telling you that you forgot to "finalize a statement" or to commit/rollback a transaction. These states can exist notwithstanding that you "closed the connection" embodying the abberent behaviour.