Re: [sqlite] WAL file size increase
On 14 Nov 2018, at 4:20am, Sharma, Tanuj [AUTOSOL/FMP/IN] 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL file size increase
On 11/13/18, Sharma, Tanuj [AUTOSOL/FMP/IN] wrote: > I have observed that when I don't have any primary key in the database table > then WAL auto_checkpoint doesn't work That's surprising because the WAL logic does not have anything to do with primary keys. Those are two completely independent systems. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] WAL file size increase
Dear SQL Developers / Users, 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 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. 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users