Add a print statement to vacuum.c to see what SQL statements
are actually executed during VACUUM:
diff -u -3 -p -r1.64 vacuum.c
--- src/vacuum.c 10 Oct 2006 13:07:36 -0000 1.64
+++ src/vacuum.c 18 Nov 2006 17:18:07 -0000
@@ -26,6 +26,7 @@
*/
static int execSql(sqlite3 *db, const char *zSql){
sqlite3_stmt *pStmt;
+ printf("%s\n", zSql);
if( SQLITE_OK!=sqlite3_prepare(db, zSql, -1, &pStmt, 0) ){
return sqlite3_errcode(db);
}
$ ./sqlite3.exe v.db vacuum
ATTACH 'C:\TMP\etilqs_SOVEJE7Rni84Zzy' AS vacuum_db;
PRAGMA vacuum_db.synchronous=OFF
BEGIN EXCLUSIVE;
CREATE TABLE vacuum_db.t1(a, b, primary key(b, a))
CREATE TABLE vacuum_db.t2(c, d)
CREATE INDEX vacuum_db.t2i on t2(d, c)
INSERT INTO vacuum_db.'t1' SELECT * FROM 't1';
INSERT INTO vacuum_db.'t2' SELECT * FROM 't2';
INSERT INTO vacuum_db.sqlite_master SELECT type, name, tbl_name, rootpage,
sql FROM sqlite_master WHERE type='view' OR type='trigger' OR
(type='table' AND rootpage=0)
----- Original Message ----
From: Nemanja Corlija <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Saturday, November 18, 2006 9:35:52 AM
Subject: [sqlite] Why is VACUUM so slow?
I have a db with one table that has a text primary key and 16 text
columns in total.
After importing data from CSV file db had 5M rows and file size was
833MB. After some big DELETEs db had around 3M rows and 500MB after
"VACUUMing".
Running VACUUM for more then an hour filled new db with ~300MB worth
of data. I then aborted that and did something like this:
PRAGMA page_size=4096;
PRAGMA synchronous=OFF;
PRAGMA cache_size=100000;
PRAGMA temp_store=MEMORY;
CREATE TABLE tbl1(same structure as in original db);
ATTACH "original.db3" AS old;
INSERT INTO main.tbl1 SELECT * FROM old.tbl1;
This finished in less then 15 minutes. Timings are obviously very
rough here, but time difference is obvious.
Except for page_size, VACUUM had same PRAGMAs applied.
Isn't what I did manually above very similar to what VACUUM does
behind the scenes?
If so, why is there such a big difference?
One observation though, while VACUUM seemed to be completely I/O
bound. INSERT seemed to be CPU bound, which is not surprising given
that it was doing uniqueness check for PRIMARY KEY all over again. I
guess VACUUM doesn't have to do that since its copying from existing
db that already had uniqueness enforced.
This was on Win2000, SQLite 3.3.7 via sqlite3.exe.
Has anyone experienced similar performance difference?
--
Nemanja Corlija <[EMAIL PROTECTED]>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
____________________________________________________________________________________
Sponsored Link
$420k for $1,399/mo.
Think You Pay Too Much For Your Mortgage?
Find Out! www.LowerMyBills.com/lre
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
