Sqlite Wal Performance Improvement

SQLite WAL performance improvement

To avoid fragmentation and remove a need to pre-insert data, you should use sqlite3_file_control() with SQLITE_FCNTL_CHUNK_SIZE to set chunk size. Allocating database file space in large chunks (say 1MB at a time), should reduce file-system fragmentation and improve performance. Mozilla project is currently using this setting in Firefox/Thunderbird with great success.

Regarding WAL. If you are writing a lot of data so often, you should consider wrapping your writes into bigger transactions. Normally, every INSERT is auto-committed and SQLite has to wait until data is really flushed to disk or flash - which is obviously very slow. If you wrap multiple writes to one transaction, SQLite need not to worry about every single row, and can flush many rows at once, most likely into single flash write - which is much faster.
So, if you can, try to wrap at least few hundred writes into one transaction.

In my experience, WAL on flash is not really working very well, and I find it more beneficial to stick to old journalling mode. For example, Android 4 does not use WAL mode for its SQLite databases, and probably for a reason. As you have noticed, WAL has a tendency to grow without bound in some situations (however, it will also happen if transactions are rarely committed - so be sure to do that once in a while).

Improve INSERT-per-second performance of SQLite

Several tips:

1. Put inserts/updates in a transaction.
2. For older versions of SQLite - Consider a less paranoid journal mode (pragma journal_mode). There is NORMAL, and then there is OFF, which can significantly increase insert speed if you're not too worried about the database possibly getting corrupted if the OS crashes. If your application crashes the data should be fine. Note that in newer versions, the OFF/MEMORY settings are not safe for application level crashes.
3. Playing with page sizes makes a difference as well (PRAGMA page_size). Having larger page sizes can make reads and writes go a bit faster as larger pages are held in memory. Note that more memory will be used for your database.
4. If you have indices, consider calling CREATE INDEX after doing all your inserts. This is significantly faster than creating the index and then doing your inserts.
5. You have to be quite careful if you have concurrent access to SQLite, as the whole database is locked when writes are done, and although multiple readers are possible, writes will be locked out. This has been improved somewhat with the addition of a WAL in newer SQLite versions.
6. Take advantage of saving space...smaller databases go faster. For instance, if you have key value pairs, try making the key an INTEGER PRIMARY KEY if possible, which will replace the implied unique row number column in the table.
7. If you are using multiple threads, you can try using the shared page cache, which will allow loaded pages to be shared between threads, which can avoid expensive I/O calls.
8. Don't use !feof(file)!

I've also asked similar questions here and here.

What could be preventing SQLite WAL files from being cleared?

I temporarily switched to journal mode, which helpfully reported errors instead of silently failing.

The error I was having was SQLITE_IOERR_WRITE. After more digging, it turned out the root Windows error causing all this was 665 - hitting into an NTFS file system limitation. An answer here then led me to the actual cause: extreme file fragmentation of the database.

Copying the file reduces fragmentation, which is why the bizarre fix I mentioned, copying the file, temporarily worked. The actual fix was to schedule defragmentation using Sysinternals' Contig.

Core Data sqlite-wal file gets MASSIVE (7GB) when inserting ~5000 rows

It seems my comment to try using the old rollback(DELETE) journal mode rather than WAL journal mode fixed the problem. NOTE that there seem to be a range of problems when using WAL journal mode including the following:

• this problem
• problems with database migrations when using the migratePersistentStore API
• problems with lightweight migrations

Perhaps we should start a Core Data WAL problems page and get a comprehensive list and ask Apple to fix the bugs.

Note that the default mode under OS X 10.9 and iOS 7 now uses WAL mode. To change this back add the following option

@{ NSSQLitePragmaOptions : @{ @"journal_mode" : @"DELETE" } }

Sqlite WAL blocks despite doc sais it doesn't

Turns out my understanding was true, but the locking issues arose from the shared cache, I needed to turn that off. This way there are no SQLITE_LOCKED errors. There are still SQLITE_BUSY errors sometimes when writing, in that case I need to retry, or in the case of being in the middle of a transaction, rollback and retry transaction.

Hope this helps someone!

---

Related Topics