Hello ! Can someone give any insight here ?
For me the main problem is the wall size that becomes stable at 387MB (+- 8% of the database, but around 15 times the size of the tables it's manipulating). It seems to me that something is not working as advertised. I'm testing sqlite3 with wall enabled and while running a program that exclusively open a database I get this on disk while doing it's work: ls -l -rw-r--r-- 1 xxxx xxxx 6185697280 Mar 23 21:34 hacker-news-items.db // 5.7GB -rw-r--r-- 1 xxxx xxxx???? 786432 Mar 23 21:34 hacker-news-items.db-shm // 786KB -rw-r--r-- 1 xxxx xxxx? 397299872 Mar 23 21:34 hacker-news-items.db-wal // 387MB stable at this size To be able to compare I dumped all users and users_submitted on a separated database to measure it's total size -rw-r--r-- 1 xxxx xxxx?? 25014272 Mar 23 22:06 hacker-news-items-diff.db //24MB db with all users and users_submitted The schema: CREATE TABLE 'users' ( ??? 'id' INTEGER PRIMARY KEY NOT NULL, ??? 'name' TEXT UNIQUE NOT NULL, ??? 'delay' INTEGER DEFAULT 0, ??? 'created' INTEGER DEFAULT 0, ??? 'karma' INTEGER DEFAULT 0, ??? 'about' TEXT DEFAULT '' ); CREATE TABLE users_submitted(user_id integer not null, item_id integer not null, unique(user_id, item_id)); Some stats about the data: select count(*) from users -> 389514 select count(*) from users_submitted -> 9381845 select max(length(about)) from users -> 83197 select count(*) from users where length(about) > 256 -> 4704 select count(*) from users where length(about) > 1024 -> 347 select count(*) from users where length(about) > 2048 -> 92 select count(*) from users where length(about) > 4096 -> 22 select count(*) from ( select count(*) as submissions from users_submitted group by user_id ) where submissions > 10 -> 64639 ? The program (with not important parts stripped): local db = SQLite3("hacker-news-items.db"); db.exec_dml("PRAGMA synchronous =OFF;"); db.exec_dml("PRAGMA journal_mode = WAL"); db.exec_dml("CREATE TABLE IF NOT EXISTS sync_all_users(id integer primary key, name varchar, sync_date timestamp default current_timestamp);"); db.exec_dml("insert or ignore into sync_all_users(id, name) values(1, '');"); //only store one record all the time local users_update_null = SQLite3Stmt(db, "update users set name=? where name=?"); local users_update_stmt = SQLite3Stmt(db, "update users set delay=?, karma=?, about=? where name=?"); local users_submitted_insert_stmt = SQLite3Stmt(db, "insert into users_submitted(user_id , item_id) values(?,?)"); local users_exist_stmt = SQLite3Stmt(db, "select id from users where name=?"); local sync_all_users_stmt = SQLite3Stmt(db, "update sync_all_users set name=?, sync_date=CURRENT_TIMESTAMP where id=1;"); function getUserDataAndUpdate(user_id) { ??? local user = getUserFromSomeWhere(user_id); ??? if(user == "null") ??? { ??? ??? users_update_null.bind(1, "_null_" + user_id); ??? ??? users_update_null.bind(2, user_id); ??? ??? users_update_null.step(); ??? ??? users_update_null.reset(); ??? ??? return; ??? } ??? local record = json2var(user); ??? local rec_id = type(record) == "table" ? table_rawget(record, "id", false) : false; ??? if(rec_id) ??? { ??? ??? users_update_stmt.bind(1, table_rawget(record, "delay", 0)); ??? ??? users_update_stmt.bind(2, table_rawget(record, "karma", 0)); ??? ??? users_update_stmt.bind(3, table_rawget(record, "about", null)); ??? ??? users_update_stmt.bind(4, rec_id); ??? ??? users_update_stmtstep(); ??? ??? users_update_stmt.reset(); ??? ??? ??? ??? local user_id = db.last_row_id(); ??? ??? ??? ??? local submitted = table_rawget(record, "submitted", false); ??? ??? if(submitted) ??? ??? { ??? ??? ??? for(local i = 0, len = submitted.len(); i < len; ++i) ??? ??? ??? { ??? ??? ??? ??? //print("submitted", submitted[i]); ??? ??? ??? ??? users_submitted_insert_stmt.bind(1, user_id); ??? ??? ??? ??? users_submitted_insert_stmt.bind(2, submitted[i]); ??? ??? ??? ??? users_submitted_insert_stmt.step(); ??? ??? ??? ??? users_submitted_insert_stmt.reset(); ??? ??? ??? } ??? ??? } ??? } } function updateAllUsers() { ??? local stmt_all = db.prepare("select? name from users where name >= ? and name not like '|_null|_%' escape '|';"); ??? local name = db.exec_get_one("select name from sync_all_users where id=1"); ??? stmt_all.bind(1, name); ??? local count = 0; ??? //db.exec_dml("begin;"); ??? while(stmt_all.next_row()) ??? { ??? ??? name = stmt_all.col(0); ??? ??? print("Now updating user", name, count); ??? ??? getUserDataAndUpdate(name); ??? ??? ??? ??? if( (++count % 1000) == 0 ) ??? ??? { ??? ??? ??? stmt_all.reset(); //stop the unique active reader to do a checkpoint ??? ??? ??? print("Now processing", name, count); ??? ??? ??? db.exec_dml("PRAGMA wal_checkpoint(FULL);"); ??? ??? ??? //db.exec_dml("commit;"); ??? ??? ??? //db.exec_dml("begin;"); ??? ??? ??? sync_all_users_stmt.bind(1, name); ??? ??? ??? sync_all_users_stmt.step(); ??? ??? ??? sync_all_users_stmt.reset(); ??? ??? ??? stmt_all.bind(1, name); ??? ??? } ??? } ??? //db.exec_dml("commit;"); ??? stmt_all.finalize(); } updateAllUsers(); ? ?