Dear SQL Developers / Users,
I haven't found the solution for WAL file increase issue. Below is the summary of my issue. The email thread is added for additional information as asked by Simon. I am using Sqlite3 in our project. My database has journal_mode set to WAL and has one writer & multiple readers. During normal mode of operation, only writing process is accessing database and there is no active reader connection I have observed that when I don’t have any primary key in the database table then WAL auto_checkpoint doesn’t work and WAL file keeps growing without any bound. At one point, it consumes all the available memory in the system. I have tried different approaches to fix this issue like setting up SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT = 0, setting SQLITE_DEFAULT_WAL_AUTOCHECKPOINT = 100 etc. but nothing worked. I have confirmed in my code that auto_checkpoint is not disabled. I have referred to information on the link https://www.sqlite.org/wal.html#avoiding_excessively_large_wal_files too and made sure that any of these cases are not happening with my code Write-Ahead Logging<https://www.sqlite.org/wal.html#avoiding_excessively_large_wal_files> www.sqlite.org 1. Overview. The default method by which SQLite implements atomic commit and rollback is a rollback journal.Beginning with version 3.7.0 (2010-07-21), a new "Write-Ahead Log" option (hereafter referred to as "WAL") is available.. There are advantages and disadvantages to using WAL instead of a rollback journal. Another approach, I have tried is that to manually checkpoint WAL file in another process at regular interval. This seems to work for me as WAL file doesn’t grow beyond specified limit. But I don’t want to do manual checkpoint in my code I want to know why auto_checkpoint is not working when there is no Primary Key in the table. FYI, when I make first column as Primary Key in the table then auto_checkpoint starts working with the same code. That’s why I am doubting some relation between auto_checkpoint and Primary Key. I checked the documentation on https://www.sqlite.org/wal.html#ckpt and didn’t find any reference of Primary Key and WAL file. Write-Ahead Logging<https://www.sqlite.org/wal.html#ckpt> www.sqlite.org 1. Overview. The default method by which SQLite implements atomic commit and rollback is a rollback journal.Beginning with version 3.7.0 (2010-07-21), a new "Write-Ahead Log" option (hereafter referred to as "WAL") is available.. There are advantages and disadvantages to using WAL instead of a rollback journal. I request to please share what could be the reason for auto_checkpoint not working in WAL mode. I will appreciate help on this. Thanks for your help in advance. Regards, Tanuj Sharma ________________________________ From: sqlite-users <[email protected]> on behalf of Sharma, Tanuj [AUTOSOL/FMP/IN] <[email protected]> Sent: Thursday, November 15, 2018 8:45 AM To: SQLite mailing list Subject: Re: [sqlite] [EXTERNAL] Re: WAL file size increase Hello Simon, Thanks for looking into my issue. >> If, during your testing, you're using existing database files, please >> create new ones each time. I am not sure if I understood this suggestion. After making my code changes, I creates a new database file in WAL mode and still observed the issue. I cannot create new database file when system is running as the data in database is non-volatile in nature >> Can you please publish your schemae ? Just paste them into an answer. I have six tables in database and below is the schema for each of these tables. CREATE TABLE DB_TYPE_1 (Point INTEGER PRIMARY KEY, val BLOB) CREATE TABLE LOG_TYPE_1 (seq_number INTEGER, timestamp INTEGER, val BLOB, flags INTEGER) CREATE TABLE LOG_TYPE_2 (seq_number INTEGER, timestamp INTEGER, val BLOB, flags INTEGER) CREATE TABLE LOG_TYPE_3 (seq_number INTEGER, timestamp INTEGER, val BLOB) CREATE TABLE LOG_TYPE_4 (seq_number INTEGER, timestamp INTEGER, val BLOB) CREATE TABLE LOG_TYPE_5 (seq_number INTEGER, timestamp INTEGER, val BLOB) I have created indexes on seq_number and timestamp column for LOG_TYPE_* tables. >> Are you defining transactions (with BEGIN) or letting SQLite do it for each >> INSERT ? Yes, I am using "BEGIN TRANSACTION" and "COMMIT TRANSACTION" in my code. I don’t know much about this as this piece of code is legacy >> Do you have any UPDATE or DELETE commands, or just INSERT ? For LOG_TYPE tables, I am using INSERT or RELACE command. I am using following command since database tables are of fixed size. Once entries reach maximum point, then application starts overwriting last records. "INSERT OR REPLACE INTO LOG_TYPE_1 (rowid, seq_number, timestamp, val) VALUES (?, ?, ?, ?)" For DB_TYPE_1 table also , I am using INSERT or RELACE command. " INSERT OR REPLACE INTO DB_TYPE_1 (point, val) values (?, ?)" Regards, Tanuj Sharma -----Original Message----- From: sqlite-users <[email protected]> On Behalf Of Simon Slavin Sent: 14 November 2018 10:11 To: SQLite mailing list <[email protected]> Subject: [EXTERNAL] Re: [sqlite] WAL file size increase On 14 Nov 2018, at 4:20am, Sharma, Tanuj [AUTOSOL/FMP/IN] <[email protected]> wrote: > I want to know why auto_checkpoint is not working when there is no Primary > Key in the table. If, during your testing, you're using existing database files, please create new ones each time. Can you please publish your schemae ? Just paste them into an answer. Are you defining transactions (with BEGIN) or letting SQLite do it for each INSERT ? Do you have any UPDATE or DELETE commands, or just INSERT ? Simon. _______________________________________________ sqlite-users mailing list [email protected] https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers&d=DwIGaQ&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=_gjw_zrI4ADZe7LuhFpe8zUXhlmZyMwy8JPAklVQDxc&m=SpLorZc8tHx50vw-G_xBiLaCT2T2AqURz2BEf1p3vk8&s=rXsfHNEgx8sCbeH4fOmHbxpz4Z-4UVNj5y4VROL9o3g&e= _______________________________________________ sqlite-users mailing list [email protected] https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers&d=DwIGaQ&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=_gjw_zrI4ADZe7LuhFpe8zUXhlmZyMwy8JPAklVQDxc&m=C98HL1PpGIVYgbLtwcf0JsoDUXCW_D2Q5HbHV2h0BIg&s=gJJfUl2gYDt77ADrKTADvQm_KAusOg4eOcGee9qxrQg&e= _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
