Using sqlite3 WAL Journaling

Morpheus suggested in a comment to check recent Mozilla sqlite changes for their places database. They basically switched to the new WAL journaling that is supported since sqlite 3.7. Therefore I performed some measurements on the update behaviour in WAL mode with different sqlite page sizes and "PRAGMA synchronous" settings.

The Results:

FS	Journal Mode	Page Size	Sync Mode	Update Duration
ext4	none	1k (default)	FULL	59s
ext4	WAL	1k	FULL	6,3s
ext4	WAL	4k	FULL	6,2s
ext4	WAL	8k	FULL	5,9s
ext4	WAL	16k	FULL	5,8s
ext4	WAL	32k	FULL	6,7s
ext3	WAL	32k	FULL	1s
ext4	WAL	32k	NORMAL	0,9s

According to the sqlite documentation WAL journaling is expected to have a significantly better write-throughput compared to synchronous mode without journaling. The results above confirm this.

When using "PRAGMA synchronous=FULL" the execution time improves to 1/10 of the original 60s. With WAL journaling the sqlite documentation recommends to use only "PRAGMA synchronous=NORMAL". By doing so we loose the durability guarantee from the ACID criterias, but 1/60 of the original execution time might be worth it. The sqlite documentation says

 "There is a very small (though non-zero) chance that a power failure at just the wrong time could corrupt the database in NORMAL mode."

Additionally the page size is said to have an influence on the performance. This isn't the case for Liferea, but to be on the safe side it doesn't hurt increase from the default setting of 1k to 32k which should cost only a bit more memory.

Disadvantages:

• As already mentioned the chance of data loss will increase...
• Also sqlite3s WAL journal is not supported on network filesystems!

Fazit:

We have to try to switch to WAL mode.There is no guarantee that it will be safe without long term tests, but the immediate performance gain is critical.

I'll create two releases 1.8.3 and 1.9.2 soon and hope you give a lot of feedback! It would be most helpful if you can include the number of feeds, your cache size setting and DB file size.