I have been using WAL mode for a few months and have been quite happy with the write performance increases.
I might possibly have found an issue/concern with the way sqlite handles doing new writes to the WAL file during a time that checkpoints are unable to checkpoint data from the WAL file back into the database due to a reader being in the process of reading data from the WAL file. The result is the WAL file growing unbounded. I have an application running on Linux which is an event logger. I am storing the events in a table in a sqlite database. I have been using version 2.7.2 until this week when I moved to 2.7.3 This application stays up and running for long periods of time. The database is opened at startup and not closed until the application is shutdown. This application is the only writer to the database. The transaction sizes when writing to the database are quite small. A single transaction writes only a single row to the database. The size of a given row is pretty small (less than 1 KB). Other processes/applications read from the database while writes are ongoing. In the testing I discuss below, I do reads by starting a process that opens a database connection, reads a set amount of data, gracefully closes the database connection making sure all statements are finalized, and then exits. I am currently using the default checkpointing and it works well when I am only writing. My WAL file never grows above 1MB when I am only writing. I startup the app and start writing to the DB. The WAL file grows up to around 1MB. During one of the subsequent writes, a checkpoint is done. All data is checkpointed / written to the main DB. The next write will start writing to the beginning of the WAL file again. (well, I believe this is what is happening based on my understanding of http://www.sqlite.org/wal.html and based on what I am seeing while testing) This process occurs over and over again during the course of many writers. The WAL file remains at right around 1MB. Then a read operation comes in. If it is still going on at the time a checkpoint needs to occur, the checkpoint won't be able to do it's job. We expect the WAL file to now grow larger here based on writes that are done while the read is still in progress. Let's say it grows to 1.5MB while the read transaction is ongoing and the writing process is concurrently writing. The read operation now completes. A subsequent write will kick off a checkpoint and all data will make it to the main db. Subsequent writes now start writing to the WAL file at the beginning of the file. Writes can go on and on, kicking off checkpoints over time. The WAL file will stay at 1.5 MB over many more writes and checkpoints over time. All of the above matches the understanding I got from reading http://www.sqlite.org/wal.html and matches what I have seen in testing. So far so good. Now consider another read operation that comes in while we are still doing writes. The read is still going on when a checkpoint is kicked off (we are up to 1MB of live data in the WAL file in my case). The checkpoint once again can't do its thing. OK, so the WAL file is going to get written to beyond the 1MB "mark". At this point what I would have expected is that the WAL file gets first written to between the 1MB and 1.5MB "mark" while new writes are being written while the read transaction is in progress. If less than .5 MB is written, then the WAL file would not grow. What I have seen in my testing, however, leads me to believe that at this point, new writes actually start at after the 1.5 MB "mark". The WAL file now grows again. Let's say the write frequency and read transaction duration are exactly as they were the last time. Instead of the WAL file remaining at 1.5MB, the WAL file now grows to 2MB. This seems to be a concern/issue with how these writes to the WAL file are being done under this condition. If I repeat this over and over again over time while we are constantly writing and periodically doing reads (I spread the reads out far enough so that I know a checkpoint will successfully complete in between two sets of reads), the WAL file will continue to grow indefinitely. During this test, my write frequency remains constant (or even slows over time). I do periodic reads, spread well out, all reading the same amount of data and taking about the same amount of time. The reads all complete in a timely fashion and clean up / close their connection afterwards. As such, I would not expect my WAL file to keep growing indefinitely, but it does. My only solution has been to make sure that my writing application periodically closes and re-opens the database while making certain all readers close the database after their read queries. Then, If I happen to close the database from my writer at a point in time when no readers have the database open, then the WAL file gets truncated and is back to size 0 when my writer reopens the DB. This solution is not perfect because I can't control when the readers will be reading compared to when my writer process can periodically close its database connection. Does it sound like I have uncovered a concern here? Thanks in advance, Bob Smith _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
