On Sat Oct 23 20:57:56 GMT 2010, H. Phil Duby wrote: > Give the described conditions, I do not think you need to worry about the > WAL file growing without bound. I think that each increase should be > smaller than the previous one, and will stop growing all together [for your > stress test] when the WAL file has increased to the point that all of the > [WAL] writes [during your forced lock conditions time frame] fit in the > expanded file
After thinking about that and also after some testing, I concur. I ran my read/write stress test again inserting 2 million rows over 9 hours while doing my reads. The WAL file occasionally kept growing even in the 8th hour, but the growth really slowed. It grew up through 12MB during the first 2 hours or so and then only grew another 700K during the next 2.5 hours and only grew another 400K over the next 4.5 hours to a total of 13.1MB through the 9 hour test. Every once in a while a read is going to take an extra long time from the norm or the writes are going to go faster than the norm (this a somewhat controlled test, but on a a general purpose machine - other processes do exist who get time slices...) and the file grows a little bit every once in a while after it initially settles down. I do believe on a longer run it would pretty much stop growing all together. Very good. The reason I looked into this in the first place and put together my more controlled test is my colleague was doing some testing with my full system and reported that his WAL file had grown to 306MB and his system had become sluggish. After my initial results, I thought the culprit causing the huge WAL file was the simultaneous reads and writes However, it turns out this was not the culprit. We also periodically delete the oldest X rows from the database in a purge. He had deleted 750K rows in one shot. That is definitely what grew the WAL file so large. I have not yet determined why his system got sluggish afterwards. It may not have anything to do with sqlite at all. So, I won't dwell on it here. I will ask one question, however. I know that reads are expected to slow down as the WAL file gets larger. Is that just in terms of real data/pages in the WAL file or does the actual size of the file have an impact? For example, I have a 300MB WAL file due to a previous operation. However, everything has been checkpointed, and we are back to really only having ~1MB of actual data being written into the WAL file and then checkpointed. The rest (vast majority) of the file is not being used. Would we expect performance degradation here as compared to a WAL file with a real physical size of ~1MB at this point? Considering things like bulk deletes (and updates) potentially really growing a WAL file to be quite large and having a system that is constantly running and inserting data into the database over a long period of time with reads also coming in, I wonder about the value of adding an optional feature to sqlite. What if there was an API to to specify a max desired limit to the size of the WAL file? Whenever a checkpoint was 100% successful and it was determined that the entire WAL has been transferred into the database and synced and no readers are making use of the WAL, then in addition to the writer rewinding the WAL back the beginning, the WAL file was truncated IF this option was configured and the size of the WAL file was greater than the specified value? This seems like it would be simple to implement without costing anything by default to those who don't configure it... If I was to use such a feature and was to do the default 1000 page checkpoint (which seems to correspond to a little over a 1MB WAL file size), I would make the limit something like 50MB. Under normal conditions, the limit would never be reached anyways. But, in the case where a large WAL file did get created at some point, this would be used to get it truncated. Thoughts? Best Regards, Bob _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
