WAL file size increase

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

WAL file size increase

Sharma, Tanuj [AUTOSOL/FMP/IN]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: WAL file size increase

Richard Hipp-3
On 11/13/18, Sharma, Tanuj [AUTOSOL/FMP/IN] <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: WAL file size increase

Simon Slavin-3
In reply to this post by Sharma, Tanuj [AUTOSOL/FMP/IN]
On 14 Nov 2018, at 4:20am, Sharma, Tanuj [AUTOSOL/FMP/IN] <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: WAL file size increase

Sharma, Tanuj [AUTOSOL/FMP/IN]
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 <[hidden email]> On Behalf Of Simon Slavin
Sent: 14 November 2018 10:11
To: SQLite mailing list <[hidden email]>
Subject: [EXTERNAL] Re: [sqlite] WAL file size increase

On 14 Nov 2018, at 4:20am, Sharma, Tanuj [AUTOSOL/FMP/IN] <[hidden email]> 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
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: WAL file size increase

Sharma, Tanuj [AUTOSOL/FMP/IN]
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 <[hidden email]> on behalf of Sharma, Tanuj [AUTOSOL/FMP/IN] <[hidden email]>
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 <[hidden email]> On Behalf Of Simon Slavin
Sent: 14 November 2018 10:11
To: SQLite mailing list <[hidden email]>
Subject: [EXTERNAL] Re: [sqlite] WAL file size increase

On 14 Nov 2018, at 4:20am, Sharma, Tanuj [AUTOSOL/FMP/IN] <[hidden email]> 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
[hidden email]
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
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: WAL file size increase

Richard Hipp-3
On 11/26/18, Sharma, Tanuj [AUTOSOL/FMP/IN] <[hidden email]> wrote:
> I want to know why auto_checkpoint is not working when there is no Primary
> Key in the table.

As far as I know, you are the only person who has ever seen this
problem.  Nobody else is able to reproduce it, and nobody has any
ideas what might be causing it.

Can you perhaps provide a SQL script that runs in the command-line
shell that will reproduce the problem?  If not, can you provide source
code to a short program that will reproduce the problem?

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: WAL file size increase

Sharma, Tanuj [AUTOSOL/FMP/IN]
Okay Richard. I will create a script to reproduce the error and will share it.


Regards,
Tanuj Sharma
________________________________
From: [hidden email] <[hidden email]> on behalf of Richard Hipp <[hidden email]>
Sent: Monday, November 26, 2018 8:48:21 PM
To: SQLite mailing list
Cc: Sharma, Tanuj [AUTOSOL/FMP/IN]
Subject: Re: [sqlite] [EXTERNAL] Re: WAL file size increase

On 11/26/18, Sharma, Tanuj [AUTOSOL/FMP/IN] <[hidden email]> wrote:
> I want to know why auto_checkpoint is not working when there is no Primary
> Key in the table.

As far as I know, you are the only person who has ever seen this
problem.  Nobody else is able to reproduce it, and nobody has any
ideas what might be causing it.

Can you perhaps provide a SQL script that runs in the command-line
shell that will reproduce the problem?  If not, can you provide source
code to a short program that will reproduce the problem?

--
D. Richard Hipp
[hidden email]

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users