ThinkGeo.com    |     Documentation    |     Premium Support

SQLite - Write Ahead Logging (WAL)

#1

MapSuite Team,

I had a Windows Explorer opened to where the SQLite databases are located and noticed, when performing Pan/Zoom/Center, ???.sqlite-wal and ???.sqlite-shm files would appear for a varying number of SQLite databases. After roughly 60 seconds they would disappear. Sometimes I would have WAL/SHM files for all 15 SQLite databases the application supports.

Since the SQLite databases are static (read only) and the WAL is for logging updates is seems wasteful and unnecessary to create these files. I’m always concerned about performance and seems creating these files adds unnecessary overhead.

How can WAL and creation of these files be disabled?

Thanks,
Dennis

#2

Hi Dennis,

It looks the WAL mode provide better performance for write & read scenario, if you think you need a read-only database, you can try to close this mode by PRAGMA journal_mode=DELETE.

Please get more detail information here: http://www.sqlite.org/wal.html

I don’t have further experience on Sqlite, above is my search result, wish that’s helpful.

Regards,

Don

#3

Don,

WAL is still in use with DELETE mode, OFF is the mode to disable WAL. Since my application does not update the databases I would like to use a mode of OFF.

Below is the Connection String for the database.
“Data Source={0};Version=3;PRAGMA journal_mode = OFF;PRAGMA locking_mode = NORMAL;”

But even with OFF being specified the wal & shm files still appear. Is the MapSuite method actually passing the PRAGMA statements to the SQLite engine?

Thanks,
Dennis

#4

Hi Dennis,

Are you using the SqliteFeatureLayer?

I double check the code of it, it looks I hadn’t found any code about journal_mode, the “PRAGMA” is only be used for GetColumnCore and GetColumnNames function, both of them hadn’t mentioned about mode.

So I don’t think our code specified the WAL mode, it should still can be set in the connection string.

Regards,

Don

#5

Don,

I was able to set journal mode = off in the ConnectionString. It was specified as shown below. Notice that there is no underscore.

journal mode = OFF

The OFF setting has definitely helped to improve performance!

Thanks for your assistance.

Regards,
Dennis

#6

Hi Dennis,

Very glad to hear it works. Any questions please let us know.

And thanks for your information, I think it will help more people.

Thanks,
Emil