Hello !
I have a database with more than 12,000,000 rows in one table and around 6GB
in size, it's in wall mode and when I run this program the wall file grows
unbound, it doesn't seem that the checkpoint is doing any work.
I read the documentation and as I understood the automatic checkpoint should
be running in this case (only this application has the database open and only
one connection and no threads).
This is the big table:
CREATE TABLE "items" (
??? 'id' integer PRIMARY KEY,
??? 'parent' INTEGER,
??? 'by' text COLLATE NOCASE,
??? 'score' integer DEFAULT 0,
??? 'title' text? COLLATE NOCASE,
??? 'type' text? COLLATE NOCASE,
??? 'url' text? COLLATE NOCASE,
??? 'deleted' BOOLEAN DEFAULT 0,
??? 'dead' BOOLEAN DEFAULT 0,
??? 'comment' TEXT COLLATE NOCASE,
??? 'time' integer NOT NULL,
??? comments_count integer default 0
);
And this is the program that when running we get the wall growing unbound:
?
local db = SQLite3("hacker-news-items.db");
//the unique exclusive connection to the database
db.exec_dml("PRAGMA synchronous =OFF;");
db.exec_dml("PRAGMA journal_mode = WAL");
local function setItemsFieldNullOnEmpty(field)
{
??? print("setItemsFieldNullOnEmpty", field);
??? local stmt_find = db.prepare(format("select id from items where %s=''
", field)); //the unique reader
??? local stmt_update = db.prepare(format("update items set %s=null where
id=?", field)); //the unique writer
??? while(stmt_find.next_row())
??? {
??? ??? local id = stmt_find.col(0).tointeger();
??? ??? print("now updating id =", id, field);
??? ??? stmt_update.bind(1, id);
??? ??? stmt_update.step();
??? ??? stmt_update.reset();
??? }
??? stmt_find.finalize();
??? stmt_update.finalize();
}
foreach(field in ["title", "url", "comment"])
setItemsFieldNullOnEmpty(field);
db.close();
When the program above runs the database wall file grows to more than 8GB
then I stopped it to prevent system crash not much disk free on this machine.
Cheers !
