Re: [sqlite] WAL and updates
On Tue, Nov 27, 2012 at 11:16 PM, Keith Chew wrote: > I have found that after performing 5000 single transaction inserts, the WAL > size grows to approx 90MB. After a checkpoint, it becomes 0 and the main > DB's size goes up by less than 2MB. Is my observation correct? Ie am I > expecting such a huge size difference between the WAL and main DB file > formats? > In WAL mode, SQLite appends deltas of the database changes to the WAL file. Each transaction commits as its delta is appended. The checkpoint operation transfers these deltas into the main database file. Prior to a checkpoint, the main database file is unchanged in WAL mode. In WAL mode, a checkpoint is the only operation that ever changes the main database. If you do 5000 transactions without a checkpoint, and the delta for each transaction is 4 or 5 pages of 4KB each, that would come to about 90MB. You might want to consider doing checkpoints more frequently to prevent the WAL file from growing so large. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL and updates
> Thanks for this Simon, but unfortunately this is legacy code, which has to > be used for a while. I just did an update to check for column change (we > have a column to tell us the record has changed, so we do not need to check > every column), and the WAL file size growth has dropped significantly. So, > will keep working at it. > > I have found that after performing 5000 single transaction inserts, the WAL size grows to approx 90MB. After a checkpoint, it becomes 0 and the main DB's size goes up by less than 2MB. Is my observation correct? Ie am I expecting such a huge size difference between the WAL and main DB file formats? Regards Keith ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL and updates
> > > > Okay, if you're doing this set up updates to do synchronisation then > you're going about it a very poor way. We've written a lot on this list > about the problems with synchronisation over the years and you'll find that > your way isn't going to be efficient. > > Instead of keeping a list of the modified data, consider keeping a list of > the commands used to modify it. Or logging the fields modified as they're > modified. > > Thanks for this Simon, but unfortunately this is legacy code, which has to be used for a while. I just did an update to check for column change (we have a column to tell us the record has changed, so we do not need to check every column), and the WAL file size growth has dropped significantly. So, will keep working at it. Regards Keith ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL and updates
On 25 Nov 2012, at 9:12pm, Keith Chew wrote: > Ok, thanks for the all the suggestions, I will find a workaround. The > reason I am asking is that I am using sqlite to perform data > synchronisation between a server and client, and after a day, the WAL file > size can grow to 3GB, quite a bit. I will look at improving the SQL update > to reduce some of the overhead. Okay, if you're doing this set up updates to do synchronisation then you're going about it a very poor way. We've written a lot on this list about the problems with synchronisation over the years and you'll find that your way isn't going to be efficient. Instead of keeping a list of the modified data, consider keeping a list of the commands used to modify it. Or logging the fields modified as they're modified. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL and updates
On Sun, Nov 25, 2012 at 4:12 PM, Keith Chew wrote: > Hi > > Ok, thanks for the all the suggestions, I will find a workaround. The > reason I am asking is that I am using sqlite to perform data > synchronisation between a server and client, and after a day, the WAL file > size can grow to 3GB, quite a bit. Performance can get really bad when you let the WAL file grow that large. You should probably try to keep the WAL file size to a maximum of a few megabytes. > I will look at improving the SQL update > to reduce some of the overhead. > > I have also found that when a record is inserted 4K is added to the WAL, > and when the record is deleted, another 3K is added. This > (insertion/deletion) happens quite a lot during data synchronisation, and > it seems to be an expensive thing (7K) in terms of WAL size growth > > > Regards > Keith > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL and updates
Hi Ok, thanks for the all the suggestions, I will find a workaround. The reason I am asking is that I am using sqlite to perform data synchronisation between a server and client, and after a day, the WAL file size can grow to 3GB, quite a bit. I will look at improving the SQL update to reduce some of the overhead. I have also found that when a record is inserted 4K is added to the WAL, and when the record is deleted, another 3K is added. This (insertion/deletion) happens quite a lot during data synchronisation, and it seems to be an expensive thing (7K) in terms of WAL size growth Regards Keith ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL and updates
On 25 Nov 2012, at 4:22pm, Imanuel wrote:
> I'm not saying your statement is inefficient, I'm saying it's wrong
> because it produces unwanted results.
>
> If the fields a,b,c ('12','34','56') should be updated to
> ('1','2345','6') your statement would fail instead of doing the expected
> update.
You're absolutely right. I should be worried about false negatives. It's the
false positives I don't have to worry about. Thanks for the correction.
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL and updates
That's better, but only if you know for sure that the data doesn't
contain '-'.
Otherwise, you run into the same problem with the following values:
'1-','2','3' -> '1','-2','3'
Imanuel
Am 25.11.2012 17:35, schrieb Roger Andersson:
> Then something like
> WHERE a||'-'||b||'-'||c||'-' != ...
>
> Maybe there are other drawbacks?
>
> //Roger
> -Ursprungligt meddelande- From: Imanuel Sent: Sunday, November
> 25, 2012 5:22 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] WAL
> and updates
> I'm not saying your statement is inefficient, I'm saying it's wrong
> because it produces unwanted results.
>
> If the fields a,b,c ('12','34','56') should be updated to
> ('1','2345','6') your statement would fail instead of doing the expected
> update.
>
> Which means with every false hit it has less data to write and thus is
> faster because of the false hits (instead of "even with").
>
> Imanuel
>
>
>
> Am 25.11.2012 17:13, schrieb Simon Slavin:
>>
>> On 25 Nov 2012, at 4:11pm, Imanuel wrote:
>>
>>> Hi Keith
>>>
>>>> UPDATE user SET a=1,b=2,c=3 WHERE a||b||c!=1||2||3;
>>>
>>> It seems to me that this is not reliable.
>>> Think the the following text values:
>>> a='12'
>>> b='34'
>>> c='56'
>>>
>>> If you want to update these values to:
>>> a='1'
>>> b='2345'
>>> c='6'
>>>
>>> Then your statement would not update because '123456' = '123456'.
>>
>> You're quite right, but in some cases it's sufficiently faster than doing
>>
>> WHERE a!=1 OR b!=2 OR c!=3
>>
>> that even with the false hits it takes less time to process.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL and updates
Then something like
WHERE a||'-'||b||'-'||c||'-' != ...
Maybe there are other drawbacks?
//Roger
-Ursprungligt meddelande-
From: Imanuel
Sent: Sunday, November 25, 2012 5:22 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] WAL and updates
I'm not saying your statement is inefficient, I'm saying it's wrong
because it produces unwanted results.
If the fields a,b,c ('12','34','56') should be updated to
('1','2345','6') your statement would fail instead of doing the expected
update.
Which means with every false hit it has less data to write and thus is
faster because of the false hits (instead of "even with").
Imanuel
Am 25.11.2012 17:13, schrieb Simon Slavin:
On 25 Nov 2012, at 4:11pm, Imanuel wrote:
Hi Keith
UPDATE user SET a=1,b=2,c=3 WHERE a||b||c!=1||2||3;
It seems to me that this is not reliable.
Think the the following text values:
a='12'
b='34'
c='56'
If you want to update these values to:
a='1'
b='2345'
c='6'
Then your statement would not update because '123456' = '123456'.
You're quite right, but in some cases it's sufficiently faster than doing
WHERE a!=1 OR b!=2 OR c!=3
that even with the false hits it takes less time to process.
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL and updates
I'm not saying your statement is inefficient, I'm saying it's wrong
because it produces unwanted results.
If the fields a,b,c ('12','34','56') should be updated to
('1','2345','6') your statement would fail instead of doing the expected
update.
Which means with every false hit it has less data to write and thus is
faster because of the false hits (instead of "even with").
Imanuel
Am 25.11.2012 17:13, schrieb Simon Slavin:
>
> On 25 Nov 2012, at 4:11pm, Imanuel wrote:
>
>> Hi Keith
>>
>>> UPDATE user SET a=1,b=2,c=3 WHERE a||b||c!=1||2||3;
>>
>> It seems to me that this is not reliable.
>> Think the the following text values:
>> a='12'
>> b='34'
>> c='56'
>>
>> If you want to update these values to:
>> a='1'
>> b='2345'
>> c='6'
>>
>> Then your statement would not update because '123456' = '123456'.
>
> You're quite right, but in some cases it's sufficiently faster than doing
>
> WHERE a!=1 OR b!=2 OR c!=3
>
> that even with the false hits it takes less time to process.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL and updates
On 25 Nov 2012, at 4:11pm, Imanuel wrote: > Hi Keith > >> UPDATE user SET a=1,b=2,c=3 WHERE a||b||c!=1||2||3; > > It seems to me that this is not reliable. > Think the the following text values: > a='12' > b='34' > c='56' > > If you want to update these values to: > a='1' > b='2345' > c='6' > > Then your statement would not update because '123456' = '123456'. You're quite right, but in some cases it's sufficiently faster than doing WHERE a!=1 OR b!=2 OR c!=3 that even with the false hits it takes less time to process. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL and updates
Hi Keith > UPDATE user SET a=1,b=2,c=3 WHERE a||b||c!=1||2||3; It seems to me that this is not reliable. Think the the following text values: a='12' b='34' c='56' If you want to update these values to: a='1' b='2345' c='6' Then your statement would not update because '123456' = '123456'. Greetings Am 25.11.2012 17:04, schrieb Simon Slavin: > > On 25 Nov 2012, at 10:46am, Keith Chew wrote: > >> I found that when an update SQL is issued against a table, the WAL file >> gets updated with the new record, even though the record is exactly the >> same as the current record. >> >> [snip] >> >> Is there a specific reason for this? Is there a way to improve this by not >> inserting the record into the WAL file if the final record is not changed? > > I think it would take SQLite a lot of time to read every row to see whether > the UPDATE command will change it before generating the new entry. Most > people's code wouldn't issue such a command under those circumstances. > Richard's suggesting of > >> UPDATE user SET user_name='Keith' WHERE user_name!='Keith'; > > is what you're asking for. With that many columns (which is unusual, > generally you should be able to keep an entire schema in your head) you can > either check them one by one, or concatenate them all together and check to > see that the long concatenation hasn't changed: > > UPDATE user SET a=1,b=2,c=3 WHERE a||b||c!=1||2||3; > > I seem to remember that this is a database where you have every column, of 40 > to 50 columns, indexed. This is also unusual, and not something that SQLite > is optimized for, and you're going to get unusual results from it. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL and updates
On 25 Nov 2012, at 10:46am, Keith Chew wrote: > I found that when an update SQL is issued against a table, the WAL file > gets updated with the new record, even though the record is exactly the > same as the current record. > > [snip] > > Is there a specific reason for this? Is there a way to improve this by not > inserting the record into the WAL file if the final record is not changed? I think it would take SQLite a lot of time to read every row to see whether the UPDATE command will change it before generating the new entry. Most people's code wouldn't issue such a command under those circumstances. Richard's suggesting of > UPDATE user SET user_name='Keith' WHERE user_name!='Keith'; is what you're asking for. With that many columns (which is unusual, generally you should be able to keep an entire schema in your head) you can either check them one by one, or concatenate them all together and check to see that the long concatenation hasn't changed: UPDATE user SET a=1,b=2,c=3 WHERE a||b||c!=1||2||3; I seem to remember that this is a database where you have every column, of 40 to 50 columns, indexed. This is also unusual, and not something that SQLite is optimized for, and you're going to get unusual results from it. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL and updates
On Mon, 26 Nov 2012 00:16:39 +1300, Keith Chew wrote: >Hi Richard > > >> >> UPDATE user SET user_name='Keith' WHERE user_name!='Keith'; >> >> >> >The example I provided was simplified only to explain the scenario. In a >production environment, there are over 40-50 columns, and the suggested >workaround above is impractical. Yet it is the way to do this in SQL. It is only impractical if you write the code by hand, perhaps you should generate it instead. Use the output of PRAGMA table_info(yourtablename) for the code generator to make the column list dynamic. Or create a BEFORE UPDATE trigger that takes care of it and RAISE(IGNORE,'Nothing to do') when there's nothing to do. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL and updates
Hi Richard > > UPDATE user SET user_name='Keith' WHERE user_name!='Keith'; > > > The example I provided was simplified only to explain the scenario. In a production environment, there are over 40-50 columns, and the suggested workaround above is impractical. Regards Keith ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL and updates
On Sun, Nov 25, 2012 at 5:46 AM, Keith Chew wrote: > Hi > > I found that when an update SQL is issued against a table, the WAL file > gets updated with the new record, even though the record is exactly the > same as the current record. > > Eg user table has 1 record with user_name = 'Keith'. Issuing this SQL will > increase the WAL file: > > update user set user_name='Keith'; > > Issuing it multiple times will keep increasing the size of the WAL file > each time. > > Yet, the connection.getChanges() returns 0. > > Is there a specific reason for this? Is there a way to improve this by not > inserting the record into the WAL file if the final record is not changed? > UPDATE user SET user_name='Keith' WHERE user_name!='Keith'; > > Regards > Keith > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] WAL and updates
Hi I found that when an update SQL is issued against a table, the WAL file gets updated with the new record, even though the record is exactly the same as the current record. Eg user table has 1 record with user_name = 'Keith'. Issuing this SQL will increase the WAL file: update user set user_name='Keith'; Issuing it multiple times will keep increasing the size of the WAL file each time. Yet, the connection.getChanges() returns 0. Is there a specific reason for this? Is there a way to improve this by not inserting the record into the WAL file if the final record is not changed? Regards Keith ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
