Hi, I just wanted to add that I changed the program a little bit to not use transactions and threads, and I still get the same problem (huge WAL file). All I do is endless loop of insert, and every X insert, I perform a checkpoint on another sqlite connection (but in the same thread). It only worked if I do the checkpoint on the same connection. Yoni.
On Tue, Aug 10, 2010 at 11:46 AM, Yoni Londner <[email protected]> wrote: > Hi Richard, > Thanks for the quick response. > I wrote a little program that demonstrate the error (below). > It opens a new DB, create a table, and start an endless loop of INSERT's. > In a background thread it performs a wal checkpoint. > There is no difference in WAL file size - with or without transactions > (passed by argument to the program). > This is the result after 45 seconds: > -rw-r--r-- 1 1.0K 2010-08-10 11:41 test.db > -rw-r--r-- 1 1.1G 2010-08-10 11:42 test.db-wal > -rw-r--r-- 1 8.0M 2010-08-10 11:42 test.db-shm > > Program compiled with GCC on debian lenny, using sqlite 3.7.0. > > #include "sqlite3.h" > #include "stdio.h" > #include "stdlib.h" > #include "fcntl.h" > > static void sql_exec(sqlite3 *conn, char *query) > { > char *err; > if (sqlite3_exec(conn, query, NULL, 0, &err)) > { > printf("sqlite: failed exec %s. err: %s\n", query, err); > exit(1); > } > } > > static sqlite3 *sql_open_conn(void) > { > sqlite3 *conn; > if (sqlite3_open_v2("test.db", &conn, SQLITE_OPEN_READWRITE, NULL)) > { > printf("sqlite3_open_v2 failed\n"); > exit(1); > } > return conn; > } > > static int do_checkpoint() > { > sqlite3 *conn; > while (1) > { > sleep(2); > printf("calling wal checkpoint\n"); > fflush(0); > conn = sql_open_conn(); > if (sqlite3_wal_checkpoint(conn, NULL)) > { > printf("sqlite3_wal_autocheckpoint failed\n"); > exit(1); > } > sqlite3_close(conn); > } > } > > int main(int argc, char **argv) > { > sqlite3 *conn = NULL; > char *err_msg = NULL; > pthread_t thread; > int fd, i, use_transactions = 0; > time_t start; > if (argc>1) > use_transactions = atoi(argv[1]); > printf("use_transactions=%d\n", use_transactions); > printf("Start\n"); > if (unlink("test.db") || unlink("test.db-wal")) > { > printf("failed unlink test.db\n"); > exit(1); > } > fd = open("test.db", O_CREAT|O_RDWR, 0666); > if (fd<0) > { > printf("could not open test.db\n"); > exit(1); > } > close(fd); > conn = sql_open_conn(); > sqlite3_enable_shared_cache(1); > sql_exec(conn, "PRAGMA journal_mode=WAL"); > sql_exec(conn, "PRAGMA synchronous=normal"); > sql_exec(conn, "PRAGMA temp_store=memory"); > sql_exec(conn, "PRAGMA wal_autocheckpoint=-1"); > sql_exec(conn, "create table tbl1 (one varchar(20), two varchar(20))"); > if (pthread_create(&thread, NULL, do_checkpoint, NULL)) > { > printf("could not start thread\n"); > exit(1); > } > start = time(); > if (use_transactions) > sql_exec(conn, "BEGIN TRANSACTION"); > while (1) > { > if (use_transactions && !(i++%100000)) > { > printf("END BEGIN transactions\n"); > fflush(stdout); > sql_exec(conn, "END TRANSACTION"); > sql_exec(conn, "BEGIN TRANSACTION"); > } > sql_exec(conn, "INSERT INTO tbl1 values('aaaaaaaaaaaaaaaaaaa', " > "'bbbbbbbbbbbbbbbbbbb')"); > } > if (use_transactions) > sql_exec(conn, "END TRANSACTION"); > sqlite3_close(conn); > printf("Finished\n"); > return 0; > } > On Tue, Aug 10, 2010 at 9:36 AM, Richard Hipp <[email protected]> wrote: > >> On Tue, Aug 10, 2010 at 2:24 AM, Yoni Londner <[email protected]> wrote: >> >> > Hello, >> > I have a questions about the correct use of transactions and WAL. >> > >> > I am writing an application that: >> > 1. should very fast >> > 2. should be very responsive >> > 3. don't care if the last N minutes of data will be lost (but DB should >> > never be corrupted) >> > >> > What I tried to do: >> > 1. open a transactions and close it every 3 minutes (So most of the >> tune >> > all work is on memory, and thus is very fast) >> > 2. disable wal auto checkpoint (So I wont have very slow queries due to >> a >> > checkpoint) >> > 3. run wal checkpoint in another thread (with another connection) - so >> it >> > wont affect the responsiveness of the main thread. >> > >> > Results: >> > 1. process memory is increasing with no upper limit >> > 2. eventually I get an I/O error >> > >> >> I'm guessing your write transactions are preventing the checkpoint from >> running to completion. Hence, the WAL grows without bound and the >> wal-index >> (an in-memory structure proportional in size to the WAL file) eventually >> uses up all memory. >> >> Set PRAGMA synchronous=NORMAL. This prevents all fsync() calls on the >> writer thread at the cost of durability, which you say you don't care >> about. >> Omit the 3-minute transactions, allowing each write to be its own >> transaction. Writes then will still be in-memory (if you count the >> operating system filesystem cache as "in-memory"). But then the >> checkpoints >> will be able to run and keep the size of the WAL file under control. >> >> >> >> > >> > Questions: >> > 1. what am I doing wrong. >> > 2. what is the correct way to achieve the goals I mentioned before. >> > >> > Thanks, >> > Jon. >> > _______________________________________________ >> > sqlite-users mailing list >> > [email protected] >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > >> >> >> >> -- >> D. Richard Hipp >> [email protected] >> _______________________________________________ >> sqlite-users mailing list >> [email protected] >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
