Re: [sqlite] memory wasted shm mapped file (3.7.2)

[Michele Pradella]

  ok, I think I'll use the snapshot in the mean time. Thank you

Il 16/09/2010 15.43, Richard Hipp ha scritto:
> On Thu, Sep 16, 2010 at 9:02 AM, Michele Pradella<
> michele.prade...@selea.com>  wrote:
>
>>   After some days of test the application work fine and with VMMap I see
>> small value of mapped file and I see only 1 instance of -shm allocated,
>> some times 2 but it's all ok. So I'll wait for the next amalgamation
>> release to have an official sources for that fix.
>> When do you think will be released?
>>
> The next scheduled official release is for the middle of October.
>
> But you can download a development snapshot from
> http://www.sqlite.org/draft/download.html and use it in the mean time.  The
> snapshot has not been tested to the same extent that an official release
> has, but it is still stable and should not give you any problems.  (On the
> other hand, if you do encounter problems with a snapshot, please let us
> know.)
>
>
>
>> Il 13/09/2010 8.18, Michele Pradella ha scritto:
>>> Ok I think that the latest patch I used to try the WAL is the patch
>>> that fix the problem. I do not get wasted memory by mapped file ;) good
>>> job. Anyway I leave the application running this day. If I have news
>>> I'll tell you.
>>> Just a question: when you plan to release a new version of sqlite with
>>> this patches?
>>>
>>> Il 10/09/2010 20.46, Max Vlasov ha scritto:
 On Fri, Sep 10, 2010 at 6:32 PM, Shane Harrelson
>> wrote:
> I tried to reproduce this, and could not.
>
> There are some questions inline below.Additionally, I want to
> verify that you've tried this with a version of SQLite containing the
> previously linked fix.
>
>
 Shane, the fix helped, the thread is a bit mixed, the information I
>> posted
 (and you had questions about) was about non fixed 3_7_2, now I finally
>> did
 the test with the patched library.2,000,000 appends were made without
>> any
 problem.


 On Fri, Sep 10, 2010 at 5:37 PM, Michele Pradella<
 michele.prade...@selea.com>wrote:

> ...connection do the operation that in my situation cause the -shm
>> Mapped
> File grow up, Am I sure that sqlite do not waste application's memory
> even if I never close the DB connection? is just a question, and anyway
> I got my application running to test this behavior.
>
>
 Michele, I think, giving this calculation, you can be sure of about
>> 2G*128/2
 (~128G) size of the db till the mapped address space is out. But are you
 sure you want appending without committing for months? I doubt you
>> consider
 the saved information valuable in this case since  a chance of a power
>> or
 system failure is higher and higher each day :)

 Max
 ___
 sqlite-users mailing list
 sqlite-users@sqlite.org
 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


>>
>> --
>> Selea s.r.l.
>>
>>
>> Michele Pradella R
>>
>>
>> SELEA s.r.l.
>>
>> Via Aldo Moro 69
>> Italy - 46019 Cicognara (MN)
>> Tel +39 0375 889091
>> Fax +39 0375 889080
>> *michele.prade...@selea.com*
>> *http://www.selea.com*
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>


-- 
Selea s.r.l.


Michele Pradella R


SELEA s.r.l.

Via Aldo Moro 69
Italy - 46019 Cicognara (MN)
Tel +39 0375 889091
Fax +39 0375 889080
*michele.prade...@selea.com* 
*http://www.selea.com*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory wasted shm mapped file (3.7.2)

[Richard Hipp]

On Thu, Sep 16, 2010 at 9:02 AM, Michele Pradella <
michele.prade...@selea.com> wrote:

>  After some days of test the application work fine and with VMMap I see
> small value of mapped file and I see only 1 instance of -shm allocated,
> some times 2 but it's all ok. So I'll wait for the next amalgamation
> release to have an official sources for that fix.
> When do you think will be released?
>

The next scheduled official release is for the middle of October.

But you can download a development snapshot from
http://www.sqlite.org/draft/download.html and use it in the mean time.  The
snapshot has not been tested to the same extent that an official release
has, but it is still stable and should not give you any problems.  (On the
other hand, if you do encounter problems with a snapshot, please let us
know.)



>
> Il 13/09/2010 8.18, Michele Pradella ha scritto:
> >Ok I think that the latest patch I used to try the WAL is the patch
> > that fix the problem. I do not get wasted memory by mapped file ;) good
> > job. Anyway I leave the application running this day. If I have news
> > I'll tell you.
> > Just a question: when you plan to release a new version of sqlite with
> > this patches?
> >
> > Il 10/09/2010 20.46, Max Vlasov ha scritto:
> >> On Fri, Sep 10, 2010 at 6:32 PM, Shane Harrelson
> wrote:
> >>
> >>> I tried to reproduce this, and could not.
> >>>
> >>> There are some questions inline below.Additionally, I want to
> >>> verify that you've tried this with a version of SQLite containing the
> >>> previously linked fix.
> >>>
> >>>
> >> Shane, the fix helped, the thread is a bit mixed, the information I
> posted
> >> (and you had questions about) was about non fixed 3_7_2, now I finally
> did
> >> the test with the patched library.2,000,000 appends were made without
> any
> >> problem.
> >>
> >>
> >> On Fri, Sep 10, 2010 at 5:37 PM, Michele Pradella<
> >> michele.prade...@selea.com>   wrote:
> >>
> >>> ...connection do the operation that in my situation cause the -shm
> Mapped
> >>> File grow up, Am I sure that sqlite do not waste application's memory
> >>> even if I never close the DB connection? is just a question, and anyway
> >>> I got my application running to test this behavior.
> >>>
> >>>
> >> Michele, I think, giving this calculation, you can be sure of about
> 2G*128/2
> >> (~128G) size of the db till the mapped address space is out. But are you
> >> sure you want appending without committing for months? I doubt you
> consider
> >> the saved information valuable in this case since  a chance of a power
> or
> >> system failure is higher and higher each day :)
> >>
> >> Max
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >>
> >
>
>
> --
> Selea s.r.l.
>
>
>Michele Pradella R
>
>
>SELEA s.r.l.
>
> Via Aldo Moro 69
> Italy - 46019 Cicognara (MN)
> Tel +39 0375 889091
> Fax +39 0375 889080
> *michele.prade...@selea.com* 
> *http://www.selea.com*
> ___
> 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] memory wasted shm mapped file (3.7.2)

[Michele Pradella]

  After some days of test the application work fine and with VMMap I see 
small value of mapped file and I see only 1 instance of -shm allocated, 
some times 2 but it's all ok. So I'll wait for the next amalgamation 
release to have an official sources for that fix.
When do you think will be released?

Il 13/09/2010 8.18, Michele Pradella ha scritto:
>Ok I think that the latest patch I used to try the WAL is the patch
> that fix the problem. I do not get wasted memory by mapped file ;) good
> job. Anyway I leave the application running this day. If I have news
> I'll tell you.
> Just a question: when you plan to release a new version of sqlite with
> this patches?
>
> Il 10/09/2010 20.46, Max Vlasov ha scritto:
>> On Fri, Sep 10, 2010 at 6:32 PM, Shane Harrelson   wrote:
>>
>>> I tried to reproduce this, and could not.
>>>
>>> There are some questions inline below.Additionally, I want to
>>> verify that you've tried this with a version of SQLite containing the
>>> previously linked fix.
>>>
>>>
>> Shane, the fix helped, the thread is a bit mixed, the information I posted
>> (and you had questions about) was about non fixed 3_7_2, now I finally did
>> the test with the patched library.2,000,000 appends were made without any
>> problem.
>>
>>
>> On Fri, Sep 10, 2010 at 5:37 PM, Michele Pradella<
>> michele.prade...@selea.com>   wrote:
>>
>>> ...connection do the operation that in my situation cause the -shm Mapped
>>> File grow up, Am I sure that sqlite do not waste application's memory
>>> even if I never close the DB connection? is just a question, and anyway
>>> I got my application running to test this behavior.
>>>
>>>
>> Michele, I think, giving this calculation, you can be sure of about 2G*128/2
>> (~128G) size of the db till the mapped address space is out. But are you
>> sure you want appending without committing for months? I doubt you consider
>> the saved information valuable in this case since  a chance of a power or
>> system failure is higher and higher each day :)
>>
>> Max
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>


-- 
Selea s.r.l.


Michele Pradella R


SELEA s.r.l.

Via Aldo Moro 69
Italy - 46019 Cicognara (MN)
Tel +39 0375 889091
Fax +39 0375 889080
*michele.prade...@selea.com* 
*http://www.selea.com*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory wasted shm mapped file (3.7.2)

[Michele Pradella]

  Ok I think that the latest patch I used to try the WAL is the patch 
that fix the problem. I do not get wasted memory by mapped file ;) good 
job. Anyway I leave the application running this day. If I have news 
I'll tell you.
Just a question: when you plan to release a new version of sqlite with 
this patches?

Il 10/09/2010 20.46, Max Vlasov ha scritto:
> On Fri, Sep 10, 2010 at 6:32 PM, Shane Harrelson  wrote:
>
>> I tried to reproduce this, and could not.
>>
>> There are some questions inline below.Additionally, I want to
>> verify that you've tried this with a version of SQLite containing the
>> previously linked fix.
>>
>>
> Shane, the fix helped, the thread is a bit mixed, the information I posted
> (and you had questions about) was about non fixed 3_7_2, now I finally did
> the test with the patched library.2,000,000 appends were made without any
> problem.
>
>
> On Fri, Sep 10, 2010 at 5:37 PM, Michele Pradella<
> michele.prade...@selea.com>  wrote:
>
>> ...connection do the operation that in my situation cause the -shm Mapped
>> File grow up, Am I sure that sqlite do not waste application's memory
>> even if I never close the DB connection? is just a question, and anyway
>> I got my application running to test this behavior.
>>
>>
> Michele, I think, giving this calculation, you can be sure of about 2G*128/2
> (~128G) size of the db till the mapped address space is out. But are you
> sure you want appending without committing for months? I doubt you consider
> the saved information valuable in this case since  a chance of a power or
> system failure is higher and higher each day :)
>
> Max
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>


-- 
Selea s.r.l.


Michele Pradella R


SELEA s.r.l.

Via Aldo Moro 69
Italy - 46019 Cicognara (MN)
Tel +39 0375 889091
Fax +39 0375 889080
*michele.prade...@selea.com* 
*http://www.selea.com*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory wasted shm mapped file (3.7.2)

[Max Vlasov]

On Fri, Sep 10, 2010 at 6:32 PM, Shane Harrelson  wrote:

> I tried to reproduce this, and could not.
>
> There are some questions inline below.Additionally, I want to
> verify that you've tried this with a version of SQLite containing the
> previously linked fix.
>
>
Shane, the fix helped, the thread is a bit mixed, the information I posted
(and you had questions about) was about non fixed 3_7_2, now I finally did
the test with the patched library.2,000,000 appends were made without any
problem.


On Fri, Sep 10, 2010 at 5:37 PM, Michele Pradella <
michele.prade...@selea.com> wrote:

> ...connection do the operation that in my situation cause the -shm Mapped
> File grow up, Am I sure that sqlite do not waste application's memory
> even if I never close the DB connection? is just a question, and anyway
> I got my application running to test this behavior.
>
>
Michele, I think, giving this calculation, you can be sure of about 2G*128/2
(~128G) size of the db till the mapped address space is out. But are you
sure you want appending without committing for months? I doubt you consider
the saved information valuable in this case since  a chance of a power or
system failure is higher and higher each day :)

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory wasted shm mapped file (3.7.2)

[Shane Harrelson]

I tried to reproduce this, and could not.

There are some questions inline below.Additionally, I want to
verify that you've tried this with a version of SQLite containing the
previously linked fix.

-Shane


On Fri, Sep 10, 2010 at 12:54 AM, Max Vlasov  wrote:
>> But as a side effect I got not expected result in other area, when I tried
>> to append 1,500,000 records to this 1.7G file having 5G of free space on the
>> disk, I got the error a user reported recently about win7 64bit, "Disk I/O
>> error". (http://www.mail-archive.com/sqlite-users@sqlite.org/msg54935.html,
>> but this seems was not related to WAL)
>>
>>
>
> Now I the problem is fully reproducible. A modified versions of the steps:
>
> Windows 7 64bit Home Premium, sqlite 3.7.2 in dll
>
> 1. Create db with the table
> CREATE TABLE [TestTable] (
> [Id] INTEGER PRIMARY KEY AUTOINCREMENT,
> [Text] VARCHAR(200)
> )
>

Which version of SQLite are you using?   What compilation options?  Do
you have syncs disabled?


> 2. Open the db that should currently be in journal_mode=delete
>

Does the DB contain anything at this point?  Or just the empty table?
What page size are you using?


> 3. Change journal_mode=WAL;

What are you using for the wal_autocheckpoint setting?

>
> 4. BEGIN TRANSACTION
>
> 4. Make 1,300,000 repeated queries
> INSERT INTO TestTable (Text) VALUES ("12345678912345 (the exact length
> of the string = 1152)
>
> 5. While the queries are executed, when the shm file grows to 11M
> (0xAC), the failure occurs with Disk I/O error (both result and extended
> are 10 (SQLITE_IOERR)).
>
> There's a change that there's something wrong with my program, can someone
> do a similar test on another Windows 64bit system?
>
> Thanks
>
> Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory wasted shm mapped file (3.7.2)

[Michele Pradella]

  The question is about the possibility, but I should be sure that this 
do not happen in my application, because I have to make my application 
running for month without restart. So because in first few test I always 
see the -shm Mapped File grow up (even that less than before the patch) 
and I see the memory cleaned up only after the DB connection close, my 
question is: if I have a DB connection active for 1 month and this 
connection do the operation that in my situation cause the -shm Mapped 
File grow up, Am I sure that sqlite do not waste application's memory 
even if I never close the DB connection? is just a question, and anyway 
I got my application running to test this behavior.

Il 10/09/2010 15.20, Max Vlasov ha scritto:
> On Fri, Sep 10, 2010 at 5:07 PM, Michele Pradella<
> michele.prade...@selea.com>  wrote:
>
>> what I worry about is that the Addressed space of
>> sqlite (during an operation of DELETE or UPDATE a lot of data in WAL
>> mode use case described before) could grow up till 2GB. This cause the
>> application crash because it can allocate no more monitor.
>>
>
> Are you talking about real tests or just a possibility? From what I see the
> actual size of wal file has nothing to do with the memory available, it's
> just structurally the same database pages saved separately from the main db
> file and shm file is the way to find those pages for readers and for pager
> to commit them. The shm file could be hungry for memory, but it's not a big
> deal since it's always wants 128 times less then the size of the data
> changed.
>
> Max
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>


-- 
Selea s.r.l.


Michele Pradella R


SELEA s.r.l.

Via Aldo Moro 69
Italy - 46019 Cicognara (MN)
Tel +39 0375 889091
Fax +39 0375 889080
*michele.prade...@selea.com* 
*http://www.selea.com*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory wasted shm mapped file (3.7.2)

[Max Vlasov]

On Fri, Sep 10, 2010 at 5:07 PM, Michele Pradella <
michele.prade...@selea.com> wrote:

>
> what I worry about is that the Addressed space of
> sqlite (during an operation of DELETE or UPDATE a lot of data in WAL
> mode use case described before) could grow up till 2GB. This cause the
> application crash because it can allocate no more monitor.
>


Are you talking about real tests or just a possibility? From what I see the
actual size of wal file has nothing to do with the memory available, it's
just structurally the same database pages saved separately from the main db
file and shm file is the way to find those pages for readers and for pager
to commit them. The shm file could be hungry for memory, but it's not a big
deal since it's always wants 128 times less then the size of the data
changed.

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory wasted shm mapped file (3.7.2)

[Michele Pradella]

  Ok I understand that it's only address space and not allocated: anyway 
if the addressed, and so the reserved, memory grow up you will see the 
Virutal Bytes in the windows performance monitor grow up. This is ok in 
the normal function: what I worry about is that the Addressed space of 
sqlite (during an operation of DELETE or UPDATE a lot of data in WAL 
mode use case described before) could grow up till 2GB. This cause the 
application crash because it can allocate no more monitor.
So is there something in the code of sqlite to control and limit the 
maximum amount of memory Adressed by WAL mechanism?


Il 10/09/2010 14.51, Max Vlasov ha scritto:
> On Fri, Sep 10, 2010 at 3:52 PM, Michele Pradella<
> michele.prade...@selea.com>  wrote:
>
>>   After some tests, with the new sqlite3.c source, seams that the
>> behavior is better than before. So I see the -shm and -wal file grow up,
>> and in VMMap I can see the Mapped File of -shm growing up, but not so
>> much as before.
>>
> Great to hear.
>
>
>> The only thing I'm thinking about is the behavior that I obtain if I
>> have 1 connection that is kept active from the beginning of the
>> application till the end. In this situation (with the DB connection
>> always active) I can see only Mapped File grow up, till 5MBis there
>> a limit that make impossible to the Mapped File to waste all the memory
>> even if I leave the connection open?
>>
>
> It seems that now you're concerned sqlite doesn't use memory enough :)
> Actually memory mapping here is not the thing that should grow significantly
> (let's not take the bug into account). Every 8 bytes in shm (and in memory
> mapping) is dedicated to a single page in the database file, in theory you
> should divide total affected (changed/updated) database bytes by 128 (=
> 1024/8 when page_size = 1024) to see how much memory mapping is going to
> occupy. I suppose since the latest fix also takes alignment into account, we
> see 64k and 32k jumping, so you can safely take shm size, multiply it by 2
> and this will be an estimate for the mapping address space sqlite will need.
>
>
> And have in mind, it's address space, not memory used, there are much
> confusion about memory in windows (and possibly in any other modern OS).
> When I investigated this thing, I wished Task manager had a value called
> "Address space" and also Windows had an error "Out of Address space".
> Currently I wrote a simple program that emulates the bug, it increases the
> file by 32k and maps the whole current file leaving the handles open.
> Imagine, this program occupies 2G and gives an error in a fraction of a
> second. So there are no actual memory allocation, just reserving pages (this
> time Intel processor architecture pages) in 386-adress space. The actual
> error appears after the MapViewOfFile call and the text is 'Not enough
> storage is available to process this command' (Code: 9)
>
> Max
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>


-- 
Selea s.r.l.


Michele Pradella R


SELEA s.r.l.

Via Aldo Moro 69
Italy - 46019 Cicognara (MN)
Tel +39 0375 889091
Fax +39 0375 889080
*michele.prade...@selea.com* 
*http://www.selea.com*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory wasted shm mapped file (3.7.2)

[Max Vlasov]

On Fri, Sep 10, 2010 at 3:52 PM, Michele Pradella <
michele.prade...@selea.com> wrote:

>  After some tests, with the new sqlite3.c source, seams that the
> behavior is better than before. So I see the -shm and -wal file grow up,
> and in VMMap I can see the Mapped File of -shm growing up, but not so
> much as before.
>

Great to hear.


> The only thing I'm thinking about is the behavior that I obtain if I
> have 1 connection that is kept active from the beginning of the
> application till the end. In this situation (with the DB connection
> always active) I can see only Mapped File grow up, till 5MBis there
> a limit that make impossible to the Mapped File to waste all the memory
> even if I leave the connection open?
>


It seems that now you're concerned sqlite doesn't use memory enough :)
Actually memory mapping here is not the thing that should grow significantly
(let's not take the bug into account). Every 8 bytes in shm (and in memory
mapping) is dedicated to a single page in the database file, in theory you
should divide total affected (changed/updated) database bytes by 128 (=
1024/8 when page_size = 1024) to see how much memory mapping is going to
occupy. I suppose since the latest fix also takes alignment into account, we
see 64k and 32k jumping, so you can safely take shm size, multiply it by 2
and this will be an estimate for the mapping address space sqlite will need.


And have in mind, it's address space, not memory used, there are much
confusion about memory in windows (and possibly in any other modern OS).
When I investigated this thing, I wished Task manager had a value called
"Address space" and also Windows had an error "Out of Address space".
Currently I wrote a simple program that emulates the bug, it increases the
file by 32k and maps the whole current file leaving the handles open.
Imagine, this program occupies 2G and gives an error in a fraction of a
second. So there are no actual memory allocation, just reserving pages (this
time Intel processor architecture pages) in 386-adress space. The actual
error appears after the MapViewOfFile call and the text is 'Not enough
storage is available to process this command' (Code: 9)

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory wasted shm mapped file (3.7.2)

[Michele Pradella]

  After some tests, with the new sqlite3.c source, seams that the 
behavior is better than before. So I see the -shm and -wal file grow up, 
and in VMMap I can see the Mapped File of -shm growing up, but not so 
much as before.
With VMMap I can see only -shm mapped files of 32K or 64K and I'm able 
to reach about 5MB of mapped files with a -shm of 2MB.
When the connection with the DB is closed I can see -shm -wal back 
inside the DB and all the mapped file disappear.
The only thing I'm thinking about is the behavior that I obtain if I 
have 1 connection that is kept active from the beginning of the 
application till the end. In this situation (with the DB connection 
always active) I can see only Mapped File grow up, till 5MBis there 
a limit that make impossible to the Mapped File to waste all the memory 
even if I leave the connection open?
Anyway I leave my application running till tomorrow, so I can tell you 
more about this situation.

Il 10/09/2010 12.16, Michele Pradella ha scritto:
>ok, Dan already sent me a sqlite3.c source, and I'm doing some
> tests...I let you know the results
>
> Il 10/09/2010 12.12, Max Vlasov ha scritto:
>> On Fri, Sep 10, 2010 at 12:53 PM, Dan Kennedy   wrote:
>>
>>> The bug is fixed by Shane's patch linked earlier in the thread. With
>>> the patch, an 11MB -shm file is mapped into memory in around 350 chunks
>>> with an average size of 48KB. Total address space used is around
>>> (350*48KB).
>>> Instead of the (350*5.5MB) that 3.7.2 was using.
>>>
>>>
>> Dan, thanks
>> I tried the patched version, It seems now the sizes changes only between 32k
>> and 64k, I hope I will make additional test with large data insert today.
>>
>> Michele, you can download this file:
>> http://www.maxerist.net/tmp/sqlite3_sqlite3_f213e133f6.zip
>> I kind of injected modified os_win.c from the full package into 3_7_2
>> amalgamation.
>>
>> Max
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>


-- 
Selea s.r.l.


Michele Pradella R


SELEA s.r.l.

Via Aldo Moro 69
Italy - 46019 Cicognara (MN)
Tel +39 0375 889091
Fax +39 0375 889080
*michele.prade...@selea.com* 
*http://www.selea.com*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory wasted shm mapped file (3.7.2)

[Michele Pradella]

  ok, Dan already sent me a sqlite3.c source, and I'm doing some 
tests...I let you know the results

Il 10/09/2010 12.12, Max Vlasov ha scritto:
> On Fri, Sep 10, 2010 at 12:53 PM, Dan Kennedy  wrote:
>
>> The bug is fixed by Shane's patch linked earlier in the thread. With
>> the patch, an 11MB -shm file is mapped into memory in around 350 chunks
>> with an average size of 48KB. Total address space used is around
>> (350*48KB).
>> Instead of the (350*5.5MB) that 3.7.2 was using.
>>
>>
> Dan, thanks
> I tried the patched version, It seems now the sizes changes only between 32k
> and 64k, I hope I will make additional test with large data insert today.
>
> Michele, you can download this file:
> http://www.maxerist.net/tmp/sqlite3_sqlite3_f213e133f6.zip
> I kind of injected modified os_win.c from the full package into 3_7_2
> amalgamation.
>
> Max
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>


-- 
Selea s.r.l.


Michele Pradella R


SELEA s.r.l.

Via Aldo Moro 69
Italy - 46019 Cicognara (MN)
Tel +39 0375 889091
Fax +39 0375 889080
*michele.prade...@selea.com* 
*http://www.selea.com*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory wasted shm mapped file (3.7.2)

[Max Vlasov]

On Fri, Sep 10, 2010 at 12:53 PM, Dan Kennedy  wrote:

>
> The bug is fixed by Shane's patch linked earlier in the thread. With
> the patch, an 11MB -shm file is mapped into memory in around 350 chunks
> with an average size of 48KB. Total address space used is around
> (350*48KB).
> Instead of the (350*5.5MB) that 3.7.2 was using.
>
>
Dan, thanks
I tried the patched version, It seems now the sizes changes only between 32k
and 64k, I hope I will make additional test with large data insert today.

Michele, you can download this file:
http://www.maxerist.net/tmp/sqlite3_sqlite3_f213e133f6.zip
I kind of injected modified os_win.c from the full package into 3_7_2
amalgamation.

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory wasted shm mapped file (3.7.2)

[Michele Pradella]

  I tried yesterday to apply the patch  to my sqlite3.c and I think that 
the problem's not fixed: but perhaps I'm wrong or I miss something 
applying the patch.
Can you provide me a sqlite3.c source with this patch applied correctly 
so I can test if this issue is correct?

Il 10/09/2010 10.53, Dan Kennedy ha scritto:
>> Michele, thanks for pointing out to vmmap, sysinternals made them so
>> fast, I
>> can not track them all. This is an excellent utility.
>> I think that the development team already knows that is going on,
>> just my
>> speculation.
>>
>> As long as I see, every next file mapping wants to see not only
>> requested
>> 32k region, but also every prior, so every CreateFileMapping/
>> MapViewOfFile
>> wants more on every next step, 32k-64k-96k (this is visible in vmmap
>> and
>> corresponds to the code in winShmMap). And as long as I see, the
>> problem is
>> that Windows allocates separated ranges of memory space for every
>> region
>> even if they're intersecting, i.e every MapViewOfFile needs to find
>> a brand
>> new address space range for every new region request. So we have
>>
>> 2,000,000k = (32k*(X + 1)/2)*X
>>
>> where X - is the number of region requests when the memory address
>> space get
>> to 2g limit.
>> And the answer for this X here is about 350 that leads us to 350*32k
>> = 11M
>> shm file. So this size that I noticed in my tests confirms this theory
> This bug analysis is correct.
>
> The bug is fixed by Shane's patch linked earlier in the thread. With
> the patch, an 11MB -shm file is mapped into memory in around 350 chunks
> with an average size of 48KB. Total address space used is around
> (350*48KB).
> Instead of the (350*5.5MB) that 3.7.2 was using.
>
>> I don't know about the internal logic that requires this, but on
>> Windows 350
>> regions is a maximum in this case. Does it mean that linux share
>> address
>> space for superset/subset regions in contrary to Windows logic?
> It is separate. This bug was in the OS specific win32 layer
>
> Dan.
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>


-- 
Selea s.r.l.


Michele Pradella R


SELEA s.r.l.

Via Aldo Moro 69
Italy - 46019 Cicognara (MN)
Tel +39 0375 889091
Fax +39 0375 889080
*michele.prade...@selea.com* 
*http://www.selea.com*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory wasted shm mapped file (3.7.2)

[Dan Kennedy]

> Michele, thanks for pointing out to vmmap, sysinternals made them so  
> fast, I
> can not track them all. This is an excellent utility.
> I think that the development team already knows that is going on,  
> just my
> speculation.
>
> As long as I see, every next file mapping wants to see not only  
> requested
> 32k region, but also every prior, so every CreateFileMapping/ 
> MapViewOfFile
> wants more on every next step, 32k-64k-96k (this is visible in vmmap  
> and
> corresponds to the code in winShmMap). And as long as I see, the  
> problem is
> that Windows allocates separated ranges of memory space for every  
> region
> even if they're intersecting, i.e every MapViewOfFile needs to find  
> a brand
> new address space range for every new region request. So we have
>
> 2,000,000k = (32k*(X + 1)/2)*X
>
> where X - is the number of region requests when the memory address  
> space get
> to 2g limit.
> And the answer for this X here is about 350 that leads us to 350*32k  
> = 11M
> shm file. So this size that I noticed in my tests confirms this theory

This bug analysis is correct.

The bug is fixed by Shane's patch linked earlier in the thread. With
the patch, an 11MB -shm file is mapped into memory in around 350 chunks
with an average size of 48KB. Total address space used is around  
(350*48KB).
Instead of the (350*5.5MB) that 3.7.2 was using.

> I don't know about the internal logic that requires this, but on  
> Windows 350
> regions is a maximum in this case. Does it mean that linux share  
> address
> space for superset/subset regions in contrary to Windows logic?

It is separate. This bug was in the OS specific win32 layer

Dan.





___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory wasted shm mapped file (3.7.2)

[Max Vlasov]

On Fri, Sep 10, 2010 at 11:29 AM, Michele Pradella <
michele.prade...@selea.com> wrote:

>  I forgot to tell you something: in my situation the -shm file is
> always small in size (about 6-7MB) even when I got 2GB of Virtual Bytes.
> But it seams that is mapped too much times.
> I see something that you could obtain if you do this:
> for (int i=0;i<1000;i++)
> "Map -shm in memory"
> This is the strange behavior...because seams that the file is mapped in
> memory but never removed: probably it's removed only when you close the
> connection, but if the during the query you wast too much memory you got
> "Disk I/O error" and the application reach an critical state.
>
>
Michele, thanks for pointing out to vmmap, sysinternals made them so fast, I
can not track them all. This is an excellent utility.
I think that the development team already knows that is going on, just my
speculation.

As long as I see, every next file mapping wants to see not only requested
32k region, but also every prior, so every CreateFileMapping/MapViewOfFile
wants more on every next step, 32k-64k-96k (this is visible in vmmap and
corresponds to the code in winShmMap). And as long as I see, the problem is
that Windows allocates separated ranges of memory space for every region
even if they're intersecting, i.e every MapViewOfFile needs to find a brand
new address space range for every new region request. So we have

2,000,000k = (32k*(X + 1)/2)*X

where X - is the number of region requests when the memory address space get
to 2g limit.
And the answer for this X here is about 350 that leads us to 350*32k = 11M
shm file. So this size that I noticed in my tests confirms this theory

I don't know about the internal logic that requires this, but on Windows 350
regions is a maximum in this case. Does it mean that linux share address
space for superset/subset regions in contrary to Windows logic?

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory wasted shm mapped file (3.7.2)

[Michele Pradella]

  I forgot to tell you something: in my situation the -shm file is 
always small in size (about 6-7MB) even when I got 2GB of Virtual Bytes. 
But it seams that is mapped too much times.
I see something that you could obtain if you do this:
for (int i=0;i<1000;i++)
 "Map -shm in memory"
This is the strange behavior...because seams that the file is mapped in 
memory but never removed: probably it's removed only when you close the 
connection, but if the during the query you wast too much memory you got 
"Disk I/O error" and the application reach an critical state.


Il 10/09/2010 8.27, Michele Pradella ha scritto:
>Even in my use case I got "Disk I/O error" after I reached 2GB of
> virtual memory.
> Max tell us the size of the memory mapped file in VMMap tool when you
> got "Disk I/O error", and check the value of Virtual Bytes in windows
> performance counter
>
> Il 10/09/2010 6.54, Max Vlasov ha scritto:
>>> But as a side effect I got not expected result in other area, when I tried
>>> to append 1,500,000 records to this 1.7G file having 5G of free space on the
>>> disk, I got the error a user reported recently about win7 64bit, "Disk I/O
>>> error". (http://www.mail-archive.com/sqlite-users@sqlite.org/msg54935.html,
>>> but this seems was not related to WAL)
>>>
>>>
>> Now I the problem is fully reproducible. A modified versions of the steps:
>>
>> Windows 7 64bit Home Premium, sqlite 3.7.2 in dll
>>
>> 1. Create db with the table
>> CREATE TABLE [TestTable] (
>> [Id] INTEGER PRIMARY KEY AUTOINCREMENT,
>> [Text] VARCHAR(200)
>> )
>>
>> 2. Open the db that should currently be in journal_mode=delete
>>
>> 3. Change journal_mode=WAL;
>>
>> 4. BEGIN TRANSACTION
>>
>> 4. Make 1,300,000 repeated queries
>> INSERT INTO TestTable (Text) VALUES ("12345678912345 (the exact length
>> of the string = 1152)
>>
>> 5. While the queries are executed, when the shm file grows to 11M
>> (0xAC), the failure occurs with Disk I/O error (both result and extended
>> are 10 (SQLITE_IOERR)).
>>
>> There's a change that there's something wrong with my program, can someone
>> do a similar test on another Windows 64bit system?
>>
>> Thanks
>>
>> Max
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>


-- 
Selea s.r.l.


Michele Pradella R


SELEA s.r.l.

Via Aldo Moro 69
Italy - 46019 Cicognara (MN)
Tel +39 0375 889091
Fax +39 0375 889080
*michele.prade...@selea.com* 
*http://www.selea.com*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory wasted shm mapped file (3.7.2)

[Michele Pradella]

  Even in my use case I got "Disk I/O error" after I reached 2GB of 
virtual memory.
Max tell us the size of the memory mapped file in VMMap tool when you 
got "Disk I/O error", and check the value of Virtual Bytes in windows 
performance counter

Il 10/09/2010 6.54, Max Vlasov ha scritto:
>> But as a side effect I got not expected result in other area, when I tried
>> to append 1,500,000 records to this 1.7G file having 5G of free space on the
>> disk, I got the error a user reported recently about win7 64bit, "Disk I/O
>> error". (http://www.mail-archive.com/sqlite-users@sqlite.org/msg54935.html,
>> but this seems was not related to WAL)
>>
>>
> Now I the problem is fully reproducible. A modified versions of the steps:
>
> Windows 7 64bit Home Premium, sqlite 3.7.2 in dll
>
> 1. Create db with the table
> CREATE TABLE [TestTable] (
> [Id] INTEGER PRIMARY KEY AUTOINCREMENT,
> [Text] VARCHAR(200)
> )
>
> 2. Open the db that should currently be in journal_mode=delete
>
> 3. Change journal_mode=WAL;
>
> 4. BEGIN TRANSACTION
>
> 4. Make 1,300,000 repeated queries
> INSERT INTO TestTable (Text) VALUES ("12345678912345 (the exact length
> of the string = 1152)
>
> 5. While the queries are executed, when the shm file grows to 11M
> (0xAC), the failure occurs with Disk I/O error (both result and extended
> are 10 (SQLITE_IOERR)).
>
> There's a change that there's something wrong with my program, can someone
> do a similar test on another Windows 64bit system?
>
> Thanks
>
> Max
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>


-- 
Selea s.r.l.


Michele Pradella R


SELEA s.r.l.

Via Aldo Moro 69
Italy - 46019 Cicognara (MN)
Tel +39 0375 889091
Fax +39 0375 889080
*michele.prade...@selea.com* 
*http://www.selea.com*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory wasted shm mapped file (3.7.2)

[Michele Pradella]

  Hi Shane, have you look at the Virtual Bytes in the performance 
monitor? in my situation is that counter that grow up to 2GB and than 
application crash because virtual space finish.
I'm using Windows XP professional SP3.
I can reproduce the problem in my DB with this SQL statement:

DELETE FROM metadata INDEXED BY metadata_datetime_sourcename WHERE 
((DateTime<=timestamp_value) AND (Source='source_name'))

Usually timestamp_value identify 1 week records. So I Delete 1 week of 
records at a time.
The DELETE statement is inside a  BEGIN;COMMIT; statement and I can have 
a lot of delete, like this:
BEGIN;

DELETE FROM metadata INDEXED BY metadata_datetime_sourcename WHERE 
((DateTime<=timestamp_value1) AND (Source='source_name1'))
DELETE FROM metadata INDEXED BY metadata_datetime_sourcename WHERE 
((DateTime<=timestamp_value2) AND (Source='source_name2'))
DELETE FROM metadata INDEXED BY metadata_datetime_sourcename WHERE 
((DateTime<=timestamp_value3) AND (Source='source_name3'))
DELETE FROM metadata INDEXED BY metadata_datetime_sourcename WHERE 
((DateTime<=timestamp_value4) AND (Source='source_name4'))
DELETE FROM metadata INDEXED BY metadata_datetime_sourcename WHERE 
((DateTime<=timestamp_value5) AND (Source='source_name5'))

..

COMMIT;

could be event 50 DELETE inside BEGIN; COMMIT;

My table have 3 indexes and have the size I told you yesterday.
We are using the default page size, because I do not change it.

I create the table like this:
CREATE TABLE IF NOT EXISTS metadata (Id INTEGER PRIMARY KEY 
AUTOINCREMENT,DateTime INTEGER,Source TEXT,SensorName TEXT,SensorValue TEXT)


Il 09/09/2010 23.25, Shane Harrelson ha scritto:
> Michele-
>
> I've looked at trying to reproduce your issue on an 32-bit Windows XP
> system using the latest code, and could not.
>
> Even assuming the "worst case" of a 512 byte page size, starting with
> a 1.2gb DB file, deleting all the records would result in a WAL file
> of roughly the same size containing 2mil+ pages.  Each of the WAL
> pages has an 8 byte entry in the SHM file.  The SHM file uses 32k
> regions which can each hold 4k entries.   For 2mil+ entries, we would
> need around 500 regions.  The SHM file is what is memory mapped, with
> each 32k region being mapped into memory.  The Windows implementation
> uses an average 48k for each region, so at most we would use around
> 24mb of memory.
>
> I attempted to reproduce this by creating a 1.5gb DB, containing 17mil
> records, each 65 bytes long, using a page size of 512 bytes.  Starting
> with this DB in WAL mode, I deleted all records.   Maximum shared
> memory usage did not exceed 24mb.
>
> If you're sure you added the fix Dan indicated correctly into your
> build, then we're going to need more info on exactly what you're
> doing.  What Windows version are you using specifically?  What page
> size are you using?   What SQL queries are you executing?  Can you
> provide a short series of statements with the CLI to reproduce this?
>
> HTH.
> -Shane
>
>
>
>
> On Thu, Sep 9, 2010 at 11:36 AM, Michele Pradella
>   wrote:
>>   Hi Max, I got the problem in both situations:
>>
>>1. I have a journal_mode=DELETE database and I convert it to WAL.
>>2. I create a new database with WAL mode.
>>
>> I never check the handles in the task manager, but I always see the
>> mapped files in vmmap growing up. I think it's the same.
>> Anyway I have the memory wasted especially when I have a quite big
>> DB(about 1.2GB with  about 17milions of records) and I try to Delete a
>> lot of records: in this situation I see mapped files growing up and
>> waste a lot of memory (I reached the maximum 32bit windows memory limit
>> so my application crash).
>>
>> ps.With this DB the "SELECT count(ID) FROM table_name" it's very
>> slow...it take  minutes(with the sqlite shell)!
>>
>> Il 09/09/2010 17.04, Max Vlasov ha scritto:
>>> On Thu, Sep 9, 2010 at 11:37 AM, Dan Kennedy
>>> wrote:
>>>
 On Sep 9, 2010, at 1:12 PM, Michele Pradella wrote:

>Hi, do you have some news about the wasted memory? have you found the
> reason for the windows backend?
 Fixed here:

 http://www.sqlite.org/src/ci/f213e133f6


>>> Dan, don't know whether it is related, but I detected memory leak in 3.7.2
>>> related to handle count increasing.
>>>
>>> Steps to reproduce
>>> (Windows XP SP3, sqlite3.7.2.dll compiled with bcc)
>>>
>>> 1. Create or use previous db with the table
>>> CREATE TABLE [TestTable] (
>>> [Id] INTEGER PRIMARY KEY AUTOINCREMENT,
>>> [Text] VARCHAR(200)
>>> )
>>>
>>> 2. Open the db that currently in journal_mode=delete
>>>
>>> 3. Change journal_mode=WAL;
>>>
>>> 4. BEGIN TRANSACTION
>>>
>>> 4. Make 50,000 repeated queries
>>> INSERT INTO TestTable (Text) VALUES ("12345678912345 (in my case the
>>> lengh of this string was about 1100 bytes)
>>>
>>> 5. See while the queries are processing how handles in Task manager
>>> increasing (total 

Re: [sqlite] memory wasted shm mapped file (3.7.2)

[Michele Pradella]

  Hi Max, I'm sure that is a leak related to sqlite WAL because if I 
disable WAL with journal_mode=DELETE and I do not have the leak. More 
over if I use vmmap tool I see the size of the mapped files growing up 
and vmmap show me that all the memory is used by -shm files mapped a lot 
of times. I'm using Windows XP 32bit, but even with win7 64bit I got the 
same result.

Il 09/09/2010 22.21, Max Vlasov ha scritto:
> On Thu, Sep 9, 2010 at 7:16 PM, Dan Kennedy  wrote:
>
>>> Dan, don't know whether it is related, but I detected memory leak in
>>> 3.7.2
>>> related to handle count increasing.
>> I think there will be one open handle for each 32KB of
>> shared-memory space in use. Or put another way, one open
>> handle for every 4000 pages in the WAL file. They should
>> all be closed when all connections to the database within
>> the process are closed.
>>
>>
>>
> Dan,
> thank you for your explanation, it makes perfect sense if we calculate the
> sizes and everything.
>
> I tried to test till the sizes Michele mentioned, this time on Win7 64 bit,
> I had almost perfect results, non-stopped appending till 1.7G and memory
> size was never more than 50MB, so I'd suggest to Michele to check the code,
> maybe there's a leak there not related to sqlite.
>
> But as a side effect I got not expected result in other area, when I tried
> to append 1,500,000 records to this 1.7G file having 5G of free space on the
> disk, I got the error a user reported recently about win7 64bit, "Disk I/O
> error". (http://www.mail-archive.com/sqlite-users@sqlite.org/msg54935.html,
> but this seems was not related to WAL)
>
> While I wrote this e-mail, I tried to perform another test, 1,200,000
> appends worked perfectly, the next series (without prior commit) failed
> almost immediately with the same Disk I/O Error.Free size is still 3,7G, so
> this was not related to the absence of free space. The size of shm file is
> 0xAC, and the section starting 0xAB8000 till the end filled with zeros.
> Please let me know if anything would be helpful to know from these files, I
> keep it in the state they were after the failure, I even did not perform
> Commit and Close.
>
> Max
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>


-- 
Selea s.r.l.


Michele Pradella R


SELEA s.r.l.

Via Aldo Moro 69
Italy - 46019 Cicognara (MN)
Tel +39 0375 889091
Fax +39 0375 889080
*michele.prade...@selea.com* 
*http://www.selea.com*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory wasted shm mapped file (3.7.2)

[Max Vlasov]

> But as a side effect I got not expected result in other area, when I tried
> to append 1,500,000 records to this 1.7G file having 5G of free space on the
> disk, I got the error a user reported recently about win7 64bit, "Disk I/O
> error". (http://www.mail-archive.com/sqlite-users@sqlite.org/msg54935.html,
> but this seems was not related to WAL)
>
>

Now I the problem is fully reproducible. A modified versions of the steps:

Windows 7 64bit Home Premium, sqlite 3.7.2 in dll

1. Create db with the table
CREATE TABLE [TestTable] (
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Text] VARCHAR(200)
)

2. Open the db that should currently be in journal_mode=delete

3. Change journal_mode=WAL;

4. BEGIN TRANSACTION

4. Make 1,300,000 repeated queries
INSERT INTO TestTable (Text) VALUES ("12345678912345 (the exact length
of the string = 1152)

5. While the queries are executed, when the shm file grows to 11M
(0xAC), the failure occurs with Disk I/O error (both result and extended
are 10 (SQLITE_IOERR)).

There's a change that there's something wrong with my program, can someone
do a similar test on another Windows 64bit system?

Thanks

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory wasted shm mapped file (3.7.2)

[Shane Harrelson]

Michele-

I've looked at trying to reproduce your issue on an 32-bit Windows XP
system using the latest code, and could not.

Even assuming the "worst case" of a 512 byte page size, starting with
a 1.2gb DB file, deleting all the records would result in a WAL file
of roughly the same size containing 2mil+ pages.  Each of the WAL
pages has an 8 byte entry in the SHM file.  The SHM file uses 32k
regions which can each hold 4k entries.   For 2mil+ entries, we would
need around 500 regions.  The SHM file is what is memory mapped, with
each 32k region being mapped into memory.  The Windows implementation
uses an average 48k for each region, so at most we would use around
24mb of memory.

I attempted to reproduce this by creating a 1.5gb DB, containing 17mil
records, each 65 bytes long, using a page size of 512 bytes.  Starting
with this DB in WAL mode, I deleted all records.   Maximum shared
memory usage did not exceed 24mb.

If you're sure you added the fix Dan indicated correctly into your
build, then we're going to need more info on exactly what you're
doing.  What Windows version are you using specifically?  What page
size are you using?   What SQL queries are you executing?  Can you
provide a short series of statements with the CLI to reproduce this?

HTH.
-Shane




On Thu, Sep 9, 2010 at 11:36 AM, Michele Pradella
 wrote:
>  Hi Max, I got the problem in both situations:
>
>   1. I have a journal_mode=DELETE database and I convert it to WAL.
>   2. I create a new database with WAL mode.
>
> I never check the handles in the task manager, but I always see the
> mapped files in vmmap growing up. I think it's the same.
> Anyway I have the memory wasted especially when I have a quite big
> DB(about 1.2GB with  about 17milions of records) and I try to Delete a
> lot of records: in this situation I see mapped files growing up and
> waste a lot of memory (I reached the maximum 32bit windows memory limit
> so my application crash).
>
> ps.With this DB the "SELECT count(ID) FROM table_name" it's very
> slow...it take  minutes(with the sqlite shell)!
>
> Il 09/09/2010 17.04, Max Vlasov ha scritto:
>> On Thu, Sep 9, 2010 at 11:37 AM, Dan Kennedy  wrote:
>>
>>> On Sep 9, 2010, at 1:12 PM, Michele Pradella wrote:
>>>
   Hi, do you have some news about the wasted memory? have you found the
 reason for the windows backend?
>>> Fixed here:
>>>
>>>    http://www.sqlite.org/src/ci/f213e133f6
>>>
>>>
>> Dan, don't know whether it is related, but I detected memory leak in 3.7.2
>> related to handle count increasing.
>>
>> Steps to reproduce
>> (Windows XP SP3, sqlite3.7.2.dll compiled with bcc)
>>
>> 1. Create or use previous db with the table
>> CREATE TABLE [TestTable] (
>> [Id] INTEGER PRIMARY KEY AUTOINCREMENT,
>> [Text] VARCHAR(200)
>> )
>>
>> 2. Open the db that currently in journal_mode=delete
>>
>> 3. Change journal_mode=WAL;
>>
>> 4. BEGIN TRANSACTION
>>
>> 4. Make 50,000 repeated queries
>> INSERT INTO TestTable (Text) VALUES ("12345678912345 (in my case the
>> lengh of this string was about 1100 bytes)
>>
>> 5. See while the queries are processing how handles in Task manager
>> increasing (total about 14). The followiing commit does not help in
>> decreasing the number to the start value.
>>
>> I tried to look with Process explorer, it seems there are many handles
>> titled "section".
>> Also I could not reproduce this when the db is already in WAL mode when
>> opened. Michele, can you tell us what is the mode when you initially open
>> db?
>>
>> Max
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>
>
> --
> Selea s.r.l.
>
>
>        Michele Pradella R
>
>
>        SELEA s.r.l.
>
> Via Aldo Moro 69
> Italy - 46019 Cicognara (MN)
> Tel +39 0375 889091
> Fax +39 0375 889080
> *michele.prade...@selea.com* 
> *http://www.selea.com*
> ___
> 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] memory wasted shm mapped file (3.7.2)

[Max Vlasov]

On Thu, Sep 9, 2010 at 7:16 PM, Dan Kennedy  wrote:

>
> >
> > Dan, don't know whether it is related, but I detected memory leak in
> > 3.7.2
> > related to handle count increasing.
>
> I think there will be one open handle for each 32KB of
> shared-memory space in use. Or put another way, one open
> handle for every 4000 pages in the WAL file. They should
> all be closed when all connections to the database within
> the process are closed.
>
>
>
Dan,
thank you for your explanation, it makes perfect sense if we calculate the
sizes and everything.

I tried to test till the sizes Michele mentioned, this time on Win7 64 bit,
I had almost perfect results, non-stopped appending till 1.7G and memory
size was never more than 50MB, so I'd suggest to Michele to check the code,
maybe there's a leak there not related to sqlite.

But as a side effect I got not expected result in other area, when I tried
to append 1,500,000 records to this 1.7G file having 5G of free space on the
disk, I got the error a user reported recently about win7 64bit, "Disk I/O
error". (http://www.mail-archive.com/sqlite-users@sqlite.org/msg54935.html,
but this seems was not related to WAL)

While I wrote this e-mail, I tried to perform another test, 1,200,000
appends worked perfectly, the next series (without prior commit) failed
almost immediately with the same Disk I/O Error.Free size is still 3,7G, so
this was not related to the absence of free space. The size of shm file is
0xAC, and the section starting 0xAB8000 till the end filled with zeros.
Please let me know if anything would be helpful to know from these files, I
keep it in the state they were after the failure, I even did not perform
Commit and Close.

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory wasted shm mapped file (3.7.2)

[Michele Pradella]

  Hi Max, I got the problem in both situations:

   1. I have a journal_mode=DELETE database and I convert it to WAL.
   2. I create a new database with WAL mode.

I never check the handles in the task manager, but I always see the 
mapped files in vmmap growing up. I think it's the same.
Anyway I have the memory wasted especially when I have a quite big 
DB(about 1.2GB with  about 17milions of records) and I try to Delete a 
lot of records: in this situation I see mapped files growing up and 
waste a lot of memory (I reached the maximum 32bit windows memory limit 
so my application crash).

ps.With this DB the "SELECT count(ID) FROM table_name" it's very 
slow...it take  minutes(with the sqlite shell)!

Il 09/09/2010 17.04, Max Vlasov ha scritto:
> On Thu, Sep 9, 2010 at 11:37 AM, Dan Kennedy  wrote:
>
>> On Sep 9, 2010, at 1:12 PM, Michele Pradella wrote:
>>
>>>   Hi, do you have some news about the wasted memory? have you found the
>>> reason for the windows backend?
>> Fixed here:
>>
>>http://www.sqlite.org/src/ci/f213e133f6
>>
>>
> Dan, don't know whether it is related, but I detected memory leak in 3.7.2
> related to handle count increasing.
>
> Steps to reproduce
> (Windows XP SP3, sqlite3.7.2.dll compiled with bcc)
>
> 1. Create or use previous db with the table
> CREATE TABLE [TestTable] (
> [Id] INTEGER PRIMARY KEY AUTOINCREMENT,
> [Text] VARCHAR(200)
> )
>
> 2. Open the db that currently in journal_mode=delete
>
> 3. Change journal_mode=WAL;
>
> 4. BEGIN TRANSACTION
>
> 4. Make 50,000 repeated queries
> INSERT INTO TestTable (Text) VALUES ("12345678912345 (in my case the
> lengh of this string was about 1100 bytes)
>
> 5. See while the queries are processing how handles in Task manager
> increasing (total about 14). The followiing commit does not help in
> decreasing the number to the start value.
>
> I tried to look with Process explorer, it seems there are many handles
> titled "section".
> Also I could not reproduce this when the db is already in WAL mode when
> opened. Michele, can you tell us what is the mode when you initially open
> db?
>
> Max
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>


-- 
Selea s.r.l.


Michele Pradella R


SELEA s.r.l.

Via Aldo Moro 69
Italy - 46019 Cicognara (MN)
Tel +39 0375 889091
Fax +39 0375 889080
*michele.prade...@selea.com* 
*http://www.selea.com*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory wasted shm mapped file (3.7.2)

[Dan Kennedy]

On Sep 9, 2010, at 10:04 PM, Max Vlasov wrote:

> On Thu, Sep 9, 2010 at 11:37 AM, Dan Kennedy   
> wrote:
>
>>
>> On Sep 9, 2010, at 1:12 PM, Michele Pradella wrote:
>>
>>> Hi, do you have some news about the wasted memory? have you found  
>>> the
>>> reason for the windows backend?
>>
>> Fixed here:
>>
>>  http://www.sqlite.org/src/ci/f213e133f6
>>
>>
>
> Dan, don't know whether it is related, but I detected memory leak in  
> 3.7.2
> related to handle count increasing.

I think there will be one open handle for each 32KB of
shared-memory space in use. Or put another way, one open
handle for every 4000 pages in the WAL file. They should
all be closed when all connections to the database within
the process are closed.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory wasted shm mapped file (3.7.2)

[Max Vlasov]

On Thu, Sep 9, 2010 at 11:37 AM, Dan Kennedy  wrote:

>
> On Sep 9, 2010, at 1:12 PM, Michele Pradella wrote:
>
> >  Hi, do you have some news about the wasted memory? have you found the
> > reason for the windows backend?
>
> Fixed here:
>
>   http://www.sqlite.org/src/ci/f213e133f6
>
>

Dan, don't know whether it is related, but I detected memory leak in 3.7.2
related to handle count increasing.

Steps to reproduce
(Windows XP SP3, sqlite3.7.2.dll compiled with bcc)

1. Create or use previous db with the table
CREATE TABLE [TestTable] (
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Text] VARCHAR(200)
)

2. Open the db that currently in journal_mode=delete

3. Change journal_mode=WAL;

4. BEGIN TRANSACTION

4. Make 50,000 repeated queries
INSERT INTO TestTable (Text) VALUES ("12345678912345 (in my case the
lengh of this string was about 1100 bytes)

5. See while the queries are processing how handles in Task manager
increasing (total about 14). The followiing commit does not help in
decreasing the number to the start value.

I tried to look with Process explorer, it seems there are many handles
titled "section".
Also I could not reproduce this when the db is already in WAL mode when
opened. Michele, can you tell us what is the mode when you initially open
db?

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory wasted shm mapped file (3.7.2)

[Michele Pradella]

  I have ported the changes of the fix 
http://www.sqlite.org/src/ci/f213e133f6 to my code (with some difficult 
because I have ported directly in my sqlite3.c source).
Anyway I think that the problem still exist, after few test I'll see 
with vmmap mapped file growing up like before the fix.
I think I ported the fix in the right way, and the problem still exist. 
I'll do some other tests.
After this fix did you never see the problem?

Il 09/09/2010 9.46, Michele Pradella ha scritto:
>ok thank you, today I'm going to port the difference to my source code
> and I'm going to try if the memory it's ok
>
> Il 09/09/2010 9.37, Dan Kennedy ha scritto:
>> On Sep 9, 2010, at 1:12 PM, Michele Pradella wrote:
>>
>>>Hi, do you have some news about the wasted memory? have you found the
>>> reason for the windows backend?
>> Fixed here:
>>
>>  http://www.sqlite.org/src/ci/f213e133f6
>>
>> Does the problem still show up for you using fossil tip?
>>
>>
>>
>>
>>
>>
>>> do you think it could be due to the windows implementation of the
>>> mmap?
>>>
>>> Il 02/09/2010 16.46, Richard Hipp ha scritto:
 On Thu, Sep 2, 2010 at 9:59 AM, Marcus Grimmwrote:

> Michele Pradella wrote:
>> ok, I'll wait for the walk around.
>> I always use a BEGIN; COMMIT; transaction but often, after a
>> COMMIT; the
>> -wal file does not change in size, it seams it's not checkponted.
>> Anyway do you think that with WAL journal mode I should continue
>> to use
>> BEGIN; COMMIT; statement? or not?
> as Richard mentioned, the wal mode is not intended to work well
> for bulk-insert kind of actions. You may try to split your insert
> cycles into smaller pieces.
>
> However, that might not help if you do sql statements which involve
> a huge implicit transaction, for example "CREATE INDEX .." on a
> huge table.
> At least on windows it can fail with IO error on a GB sized db.
>
 We are working on that problem.  In the meantime, your workaround
 is to
 switch to journal_mode=DELETE before creating large indices.


> Btw, I think the wal file doesn't shrink because sqlite doesn't
> truncate
> that file after completing the checkpoint. That's by design I guess.
>
 Correct.  The -wal file is deleted when the last connection to the
 database
 is closed.  But prior to that, the WAL file is kept open and is not
 truncated.  This is a performance optimization.  Most filesystems
 are faster
 at overwriting an existing file than they are at appending to the
 end of a
 file.  (Note the qualifier "Most" in the previous sentence.  There
 are
 exceptions to the rule.  We try to optimize for the common case.)


> Marcus
>
>
>
>> Il 02/09/2010 14.43, Richard Hipp ha scritto:
>>> On Thu, Sep 2, 2010 at 8:34 AM, Michele Pradella<
> michele.prade...@selea.com
 wrote:
 Hi,
 I found a strange behavior of the sqlite 3.7.2 with WAL journal
 mode.
 Yesterday I found my application DB with a -wal file of 1,5GB
 and a
> -shm
 file of few MB (about 9MB) with a DB file of 1,2GB: in this
 situation I got the process memory wasted by "mapped file" of
 the -shm
 file. It seams that the file is mapped a lot of times in memory
 so the
 process memory become 2GB and it can't allocate more memory. In
 that
 situation operation made on the DB cause I/O disk errors
 probably due
> to
 the wasted memory.

>>> By coincidence, the SQLite developers were just discussing this
>>> problem
>>> earlier this morning.  There are technical issues with windows
>>> that make
> a
>>> solution difficult.  We are trying to come up with a work-
>>> around.  (The
>>> problem you describe is specific to the windows backend and does
>>> not
> come up
>>> in unix.)
>>>
>>>
 I'm doing some other test to reproduce the problem, but I think
 that
 could be when I got a lot of operation between a BEGIN; COMMIT;
 So is it ok to use the BEGIN; COMMIT; with the WAL journal
 activated?
 is there some kind of limit in the number of operation between
 a BEGIN;
 COMMIT; statement?

>>> SQLite will not checkpoint the journal until you commit your
> transaction.
>>> So if you leave the transaction open too long, the WAL file and
>>> the -shm
>>> file will grow excessively large.  WAL works best with many
>>> smaller
>>> transactions.  If you have one or two big transactions, then
>>> using a
>>> traditional rollback-journal mode works better.
>>>
>>>
>>>
 I try to use the PRAGMA wal_checkpoint; to try resolve this
 situation,
 but seams that command was 

Re: [sqlite] memory wasted shm mapped file (3.7.2)

[Michele Pradella]

  ok thank you, today I'm going to port the difference to my source code 
and I'm going to try if the memory it's ok

Il 09/09/2010 9.37, Dan Kennedy ha scritto:
> On Sep 9, 2010, at 1:12 PM, Michele Pradella wrote:
>
>>   Hi, do you have some news about the wasted memory? have you found the
>> reason for the windows backend?
> Fixed here:
>
> http://www.sqlite.org/src/ci/f213e133f6
>
> Does the problem still show up for you using fossil tip?
>
>
>
>
>
>
>> do you think it could be due to the windows implementation of the
>> mmap?
>>
>> Il 02/09/2010 16.46, Richard Hipp ha scritto:
>>> On Thu, Sep 2, 2010 at 9:59 AM, Marcus Grimm>> online.de>wrote:
>>>
 Michele Pradella wrote:
>ok, I'll wait for the walk around.
> I always use a BEGIN; COMMIT; transaction but often, after a
> COMMIT; the
> -wal file does not change in size, it seams it's not checkponted.
> Anyway do you think that with WAL journal mode I should continue
> to use
> BEGIN; COMMIT; statement? or not?
 as Richard mentioned, the wal mode is not intended to work well
 for bulk-insert kind of actions. You may try to split your insert
 cycles into smaller pieces.

 However, that might not help if you do sql statements which involve
 a huge implicit transaction, for example "CREATE INDEX .." on a
 huge table.
 At least on windows it can fail with IO error on a GB sized db.

>>> We are working on that problem.  In the meantime, your workaround
>>> is to
>>> switch to journal_mode=DELETE before creating large indices.
>>>
>>>
 Btw, I think the wal file doesn't shrink because sqlite doesn't
 truncate
 that file after completing the checkpoint. That's by design I guess.

>>> Correct.  The -wal file is deleted when the last connection to the
>>> database
>>> is closed.  But prior to that, the WAL file is kept open and is not
>>> truncated.  This is a performance optimization.  Most filesystems
>>> are faster
>>> at overwriting an existing file than they are at appending to the
>>> end of a
>>> file.  (Note the qualifier "Most" in the previous sentence.  There
>>> are
>>> exceptions to the rule.  We try to optimize for the common case.)
>>>
>>>
 Marcus



> Il 02/09/2010 14.43, Richard Hipp ha scritto:
>> On Thu, Sep 2, 2010 at 8:34 AM, Michele Pradella<
 michele.prade...@selea.com
>>> wrote:
>>>Hi,
>>> I found a strange behavior of the sqlite 3.7.2 with WAL journal
>>> mode.
>>> Yesterday I found my application DB with a -wal file of 1,5GB
>>> and a
 -shm
>>> file of few MB (about 9MB) with a DB file of 1,2GB: in this
>>> situation I got the process memory wasted by "mapped file" of
>>> the -shm
>>> file. It seams that the file is mapped a lot of times in memory
>>> so the
>>> process memory become 2GB and it can't allocate more memory. In
>>> that
>>> situation operation made on the DB cause I/O disk errors
>>> probably due
 to
>>> the wasted memory.
>>>
>> By coincidence, the SQLite developers were just discussing this
>> problem
>> earlier this morning.  There are technical issues with windows
>> that make
 a
>> solution difficult.  We are trying to come up with a work-
>> around.  (The
>> problem you describe is specific to the windows backend and does
>> not
 come up
>> in unix.)
>>
>>
>>> I'm doing some other test to reproduce the problem, but I think
>>> that
>>> could be when I got a lot of operation between a BEGIN; COMMIT;
>>> So is it ok to use the BEGIN; COMMIT; with the WAL journal
>>> activated?
>>> is there some kind of limit in the number of operation between
>>> a BEGIN;
>>> COMMIT; statement?
>>>
>> SQLite will not checkpoint the journal until you commit your
 transaction.
>> So if you leave the transaction open too long, the WAL file and
>> the -shm
>> file will grow excessively large.  WAL works best with many
>> smaller
>> transactions.  If you have one or two big transactions, then
>> using a
>> traditional rollback-journal mode works better.
>>
>>
>>
>>> I try to use the PRAGMA wal_checkpoint; to try resolve this
>>> situation,
>>> but seams that command was ignored by sqlite because the -wal
>>> file does
>>> not change in size, even the DB file.
>>> ___
>>> 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

>>>
>>
>> -- 
>> Selea s.r.l.
>>
>>
>> Michele Pradella R
>>
>>
>> SELEA s.r.l.
>>
>> Via Aldo Moro 69
>> Italy - 46019 

Re: [sqlite] memory wasted shm mapped file (3.7.2)

[Dan Kennedy]

On Sep 9, 2010, at 1:12 PM, Michele Pradella wrote:

>  Hi, do you have some news about the wasted memory? have you found the
> reason for the windows backend?

Fixed here:

   http://www.sqlite.org/src/ci/f213e133f6

Does the problem still show up for you using fossil tip?






> do you think it could be due to the windows implementation of the  
> mmap?
>
> Il 02/09/2010 16.46, Richard Hipp ha scritto:
>> On Thu, Sep 2, 2010 at 9:59 AM, Marcus Grimm> online.de>wrote:
>>
>>> Michele Pradella wrote:
   ok, I'll wait for the walk around.
 I always use a BEGIN; COMMIT; transaction but often, after a  
 COMMIT; the
 -wal file does not change in size, it seams it's not checkponted.
 Anyway do you think that with WAL journal mode I should continue  
 to use
 BEGIN; COMMIT; statement? or not?
>>> as Richard mentioned, the wal mode is not intended to work well
>>> for bulk-insert kind of actions. You may try to split your insert
>>> cycles into smaller pieces.
>>>
>>> However, that might not help if you do sql statements which involve
>>> a huge implicit transaction, for example "CREATE INDEX .." on a  
>>> huge table.
>>> At least on windows it can fail with IO error on a GB sized db.
>>>
>> We are working on that problem.  In the meantime, your workaround  
>> is to
>> switch to journal_mode=DELETE before creating large indices.
>>
>>
>>> Btw, I think the wal file doesn't shrink because sqlite doesn't  
>>> truncate
>>> that file after completing the checkpoint. That's by design I guess.
>>>
>> Correct.  The -wal file is deleted when the last connection to the  
>> database
>> is closed.  But prior to that, the WAL file is kept open and is not
>> truncated.  This is a performance optimization.  Most filesystems  
>> are faster
>> at overwriting an existing file than they are at appending to the  
>> end of a
>> file.  (Note the qualifier "Most" in the previous sentence.  There  
>> are
>> exceptions to the rule.  We try to optimize for the common case.)
>>
>>
>>> Marcus
>>>
>>>
>>>

 Il 02/09/2010 14.43, Richard Hipp ha scritto:
> On Thu, Sep 2, 2010 at 8:34 AM, Michele Pradella<
>>> michele.prade...@selea.com
>> wrote:
>>   Hi,
>> I found a strange behavior of the sqlite 3.7.2 with WAL journal  
>> mode.
>> Yesterday I found my application DB with a -wal file of 1,5GB  
>> and a
>>> -shm
>> file of few MB (about 9MB) with a DB file of 1,2GB: in this
>> situation I got the process memory wasted by "mapped file" of  
>> the -shm
>> file. It seams that the file is mapped a lot of times in memory  
>> so the
>> process memory become 2GB and it can't allocate more memory. In  
>> that
>> situation operation made on the DB cause I/O disk errors  
>> probably due
>>> to
>> the wasted memory.
>>
> By coincidence, the SQLite developers were just discussing this  
> problem
> earlier this morning.  There are technical issues with windows  
> that make
>>> a
> solution difficult.  We are trying to come up with a work- 
> around.  (The
> problem you describe is specific to the windows backend and does  
> not
>>> come up
> in unix.)
>
>
>> I'm doing some other test to reproduce the problem, but I think  
>> that
>> could be when I got a lot of operation between a BEGIN; COMMIT;
>> So is it ok to use the BEGIN; COMMIT; with the WAL journal  
>> activated?
>> is there some kind of limit in the number of operation between  
>> a BEGIN;
>> COMMIT; statement?
>>
> SQLite will not checkpoint the journal until you commit your
>>> transaction.
> So if you leave the transaction open too long, the WAL file and  
> the -shm
> file will grow excessively large.  WAL works best with many  
> smaller
> transactions.  If you have one or two big transactions, then  
> using a
> traditional rollback-journal mode works better.
>
>
>
>> I try to use the PRAGMA wal_checkpoint; to try resolve this  
>> situation,
>> but seams that command was ignored by sqlite because the -wal  
>> file does
>> not change in size, even the DB file.
>> ___
>> 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
>>>
>>
>>
>
>
> -- 
> Selea s.r.l.
>
>
>Michele Pradella R
>
>
>SELEA s.r.l.
>
> Via Aldo Moro 69
> Italy - 46019 Cicognara (MN)
> Tel +39 0375 889091
> Fax +39 0375 889080
> *michele.prade...@selea.com* 
> *http://www.selea.com*
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org

Re: [sqlite] memory wasted shm mapped file (3.7.2)

[Michele Pradella]

  Hi, do you have some news about the wasted memory? have you found the 
reason for the windows backend?
do you think it could be due to the windows implementation of the mmap?

Il 02/09/2010 16.46, Richard Hipp ha scritto:
> On Thu, Sep 2, 2010 at 9:59 AM, Marcus Grimmwrote:
>
>> Michele Pradella wrote:
>>>ok, I'll wait for the walk around.
>>> I always use a BEGIN; COMMIT; transaction but often, after a COMMIT; the
>>> -wal file does not change in size, it seams it's not checkponted.
>>> Anyway do you think that with WAL journal mode I should continue to use
>>> BEGIN; COMMIT; statement? or not?
>> as Richard mentioned, the wal mode is not intended to work well
>> for bulk-insert kind of actions. You may try to split your insert
>> cycles into smaller pieces.
>>
>> However, that might not help if you do sql statements which involve
>> a huge implicit transaction, for example "CREATE INDEX .." on a huge table.
>> At least on windows it can fail with IO error on a GB sized db.
>>
> We are working on that problem.  In the meantime, your workaround is to
> switch to journal_mode=DELETE before creating large indices.
>
>
>> Btw, I think the wal file doesn't shrink because sqlite doesn't truncate
>> that file after completing the checkpoint. That's by design I guess.
>>
> Correct.  The -wal file is deleted when the last connection to the database
> is closed.  But prior to that, the WAL file is kept open and is not
> truncated.  This is a performance optimization.  Most filesystems are faster
> at overwriting an existing file than they are at appending to the end of a
> file.  (Note the qualifier "Most" in the previous sentence.  There are
> exceptions to the rule.  We try to optimize for the common case.)
>
>
>> Marcus
>>
>>
>>
>>>
>>> Il 02/09/2010 14.43, Richard Hipp ha scritto:
 On Thu, Sep 2, 2010 at 8:34 AM, Michele Pradella<
>> michele.prade...@selea.com
> wrote:
>Hi,
> I found a strange behavior of the sqlite 3.7.2 with WAL journal mode.
> Yesterday I found my application DB with a -wal file of 1,5GB and a
>> -shm
> file of few MB (about 9MB) with a DB file of 1,2GB: in this
> situation I got the process memory wasted by "mapped file" of the -shm
> file. It seams that the file is mapped a lot of times in memory so the
> process memory become 2GB and it can't allocate more memory. In that
> situation operation made on the DB cause I/O disk errors probably due
>> to
> the wasted memory.
>
 By coincidence, the SQLite developers were just discussing this problem
 earlier this morning.  There are technical issues with windows that make
>> a
 solution difficult.  We are trying to come up with a work-around.  (The
 problem you describe is specific to the windows backend and does not
>> come up
 in unix.)


> I'm doing some other test to reproduce the problem, but I think that
> could be when I got a lot of operation between a BEGIN; COMMIT;
> So is it ok to use the BEGIN; COMMIT; with the WAL journal activated?
> is there some kind of limit in the number of operation between a BEGIN;
> COMMIT; statement?
>
 SQLite will not checkpoint the journal until you commit your
>> transaction.
 So if you leave the transaction open too long, the WAL file and the -shm
 file will grow excessively large.  WAL works best with many smaller
 transactions.  If you have one or two big transactions, then using a
 traditional rollback-journal mode works better.



> I try to use the PRAGMA wal_checkpoint; to try resolve this situation,
> but seams that command was ignored by sqlite because the -wal file does
> not change in size, even the DB file.
> ___
> 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
>>
>
>


-- 
Selea s.r.l.


Michele Pradella R


SELEA s.r.l.

Via Aldo Moro 69
Italy - 46019 Cicognara (MN)
Tel +39 0375 889091
Fax +39 0375 889080
*michele.prade...@selea.com* 
*http://www.selea.com*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory wasted shm mapped file (3.7.2)

[Richard Hipp]

On Thu, Sep 2, 2010 at 9:59 AM, Marcus Grimm wrote:

>
> Michele Pradella wrote:
> >   ok, I'll wait for the walk around.
> > I always use a BEGIN; COMMIT; transaction but often, after a COMMIT; the
> > -wal file does not change in size, it seams it's not checkponted.
> > Anyway do you think that with WAL journal mode I should continue to use
> > BEGIN; COMMIT; statement? or not?
>
> as Richard mentioned, the wal mode is not intended to work well
> for bulk-insert kind of actions. You may try to split your insert
> cycles into smaller pieces.
>
> However, that might not help if you do sql statements which involve
> a huge implicit transaction, for example "CREATE INDEX .." on a huge table.
> At least on windows it can fail with IO error on a GB sized db.
>

We are working on that problem.  In the meantime, your workaround is to
switch to journal_mode=DELETE before creating large indices.


>
> Btw, I think the wal file doesn't shrink because sqlite doesn't truncate
> that file after completing the checkpoint. That's by design I guess.
>

Correct.  The -wal file is deleted when the last connection to the database
is closed.  But prior to that, the WAL file is kept open and is not
truncated.  This is a performance optimization.  Most filesystems are faster
at overwriting an existing file than they are at appending to the end of a
file.  (Note the qualifier "Most" in the previous sentence.  There are
exceptions to the rule.  We try to optimize for the common case.)


>
> Marcus
>
>
>
> >
> >
> > Il 02/09/2010 14.43, Richard Hipp ha scritto:
> >> On Thu, Sep 2, 2010 at 8:34 AM, Michele Pradella<
> michele.prade...@selea.com
> >>> wrote:
> >>>   Hi,
> >>> I found a strange behavior of the sqlite 3.7.2 with WAL journal mode.
> >>> Yesterday I found my application DB with a -wal file of 1,5GB and a
> -shm
> >>> file of few MB (about 9MB) with a DB file of 1,2GB: in this
> >>> situation I got the process memory wasted by "mapped file" of the -shm
> >>> file. It seams that the file is mapped a lot of times in memory so the
> >>> process memory become 2GB and it can't allocate more memory. In that
> >>> situation operation made on the DB cause I/O disk errors probably due
> to
> >>> the wasted memory.
> >>>
> >> By coincidence, the SQLite developers were just discussing this problem
> >> earlier this morning.  There are technical issues with windows that make
> a
> >> solution difficult.  We are trying to come up with a work-around.  (The
> >> problem you describe is specific to the windows backend and does not
> come up
> >> in unix.)
> >>
> >>
> >>> I'm doing some other test to reproduce the problem, but I think that
> >>> could be when I got a lot of operation between a BEGIN; COMMIT;
> >>> So is it ok to use the BEGIN; COMMIT; with the WAL journal activated?
> >>> is there some kind of limit in the number of operation between a BEGIN;
> >>> COMMIT; statement?
> >>>
> >> SQLite will not checkpoint the journal until you commit your
> transaction.
> >> So if you leave the transaction open too long, the WAL file and the -shm
> >> file will grow excessively large.  WAL works best with many smaller
> >> transactions.  If you have one or two big transactions, then using a
> >> traditional rollback-journal mode works better.
> >>
> >>
> >>
> >>> I try to use the PRAGMA wal_checkpoint; to try resolve this situation,
> >>> but seams that command was ignored by sqlite because the -wal file does
> >>> not change in size, even the DB file.
> >>> ___
> >>> 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
>



-- 
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] memory wasted shm mapped file (3.7.2)

[Marcus Grimm]

Michele Pradella wrote:
>   ok, I'll wait for the walk around.
> I always use a BEGIN; COMMIT; transaction but often, after a COMMIT; the 
> -wal file does not change in size, it seams it's not checkponted.
> Anyway do you think that with WAL journal mode I should continue to use 
> BEGIN; COMMIT; statement? or not?

as Richard mentioned, the wal mode is not intended to work well
for bulk-insert kind of actions. You may try to split your insert
cycles into smaller pieces.

However, that might not help if you do sql statements which involve
a huge implicit transaction, for example "CREATE INDEX .." on a huge table.
At least on windows it can fail with IO error on a GB sized db.

Btw, I think the wal file doesn't shrink because sqlite doesn't truncate
that file after completing the checkpoint. That's by design I guess.

Marcus



> 
> 
> Il 02/09/2010 14.43, Richard Hipp ha scritto:
>> On Thu, Sep 2, 2010 at 8:34 AM, Michele Pradella>> wrote:
>>>   Hi,
>>> I found a strange behavior of the sqlite 3.7.2 with WAL journal mode.
>>> Yesterday I found my application DB with a -wal file of 1,5GB and a -shm
>>> file of few MB (about 9MB) with a DB file of 1,2GB: in this
>>> situation I got the process memory wasted by "mapped file" of the -shm
>>> file. It seams that the file is mapped a lot of times in memory so the
>>> process memory become 2GB and it can't allocate more memory. In that
>>> situation operation made on the DB cause I/O disk errors probably due to
>>> the wasted memory.
>>>
>> By coincidence, the SQLite developers were just discussing this problem
>> earlier this morning.  There are technical issues with windows that make a
>> solution difficult.  We are trying to come up with a work-around.  (The
>> problem you describe is specific to the windows backend and does not come up
>> in unix.)
>>
>>
>>> I'm doing some other test to reproduce the problem, but I think that
>>> could be when I got a lot of operation between a BEGIN; COMMIT;
>>> So is it ok to use the BEGIN; COMMIT; with the WAL journal activated?
>>> is there some kind of limit in the number of operation between a BEGIN;
>>> COMMIT; statement?
>>>
>> SQLite will not checkpoint the journal until you commit your transaction.
>> So if you leave the transaction open too long, the WAL file and the -shm
>> file will grow excessively large.  WAL works best with many smaller
>> transactions.  If you have one or two big transactions, then using a
>> traditional rollback-journal mode works better.
>>
>>
>>
>>> I try to use the PRAGMA wal_checkpoint; to try resolve this situation,
>>> but seams that command was ignored by sqlite because the -wal file does
>>> not change in size, even the DB file.
>>> ___
>>> 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] memory wasted shm mapped file (3.7.2)

[Michele Pradella]

  ok, I'll wait for the walk around.
I always use a BEGIN; COMMIT; transaction but often, after a COMMIT; the 
-wal file does not change in size, it seams it's not checkponted.
Anyway do you think that with WAL journal mode I should continue to use 
BEGIN; COMMIT; statement? or not?


Il 02/09/2010 14.43, Richard Hipp ha scritto:
> On Thu, Sep 2, 2010 at 8:34 AM, Michele Pradella> wrote:
>>   Hi,
>> I found a strange behavior of the sqlite 3.7.2 with WAL journal mode.
>> Yesterday I found my application DB with a -wal file of 1,5GB and a -shm
>> file of few MB (about 9MB) with a DB file of 1,2GB: in this
>> situation I got the process memory wasted by "mapped file" of the -shm
>> file. It seams that the file is mapped a lot of times in memory so the
>> process memory become 2GB and it can't allocate more memory. In that
>> situation operation made on the DB cause I/O disk errors probably due to
>> the wasted memory.
>>
> By coincidence, the SQLite developers were just discussing this problem
> earlier this morning.  There are technical issues with windows that make a
> solution difficult.  We are trying to come up with a work-around.  (The
> problem you describe is specific to the windows backend and does not come up
> in unix.)
>
>
>> I'm doing some other test to reproduce the problem, but I think that
>> could be when I got a lot of operation between a BEGIN; COMMIT;
>> So is it ok to use the BEGIN; COMMIT; with the WAL journal activated?
>> is there some kind of limit in the number of operation between a BEGIN;
>> COMMIT; statement?
>>
> SQLite will not checkpoint the journal until you commit your transaction.
> So if you leave the transaction open too long, the WAL file and the -shm
> file will grow excessively large.  WAL works best with many smaller
> transactions.  If you have one or two big transactions, then using a
> traditional rollback-journal mode works better.
>
>
>
>> I try to use the PRAGMA wal_checkpoint; to try resolve this situation,
>> but seams that command was ignored by sqlite because the -wal file does
>> not change in size, even the DB file.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>


-- 
Selea s.r.l.


Michele Pradella R


SELEA s.r.l.

Via Aldo Moro 69
Italy - 46019 Cicognara (MN)
Tel +39 0375 889091
Fax +39 0375 889080
*michele.prade...@selea.com* 
*http://www.selea.com*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory wasted shm mapped file (3.7.2)

[Richard Hipp]

On Thu, Sep 2, 2010 at 8:34 AM, Michele Pradella  wrote:

>  Hi,
> I found a strange behavior of the sqlite 3.7.2 with WAL journal mode.
> Yesterday I found my application DB with a -wal file of 1,5GB and a -shm
> file of few MB (about 9MB) with a DB file of 1,2GB: in this
> situation I got the process memory wasted by "mapped file" of the -shm
> file. It seams that the file is mapped a lot of times in memory so the
> process memory become 2GB and it can't allocate more memory. In that
> situation operation made on the DB cause I/O disk errors probably due to
> the wasted memory.
>

By coincidence, the SQLite developers were just discussing this problem
earlier this morning.  There are technical issues with windows that make a
solution difficult.  We are trying to come up with a work-around.  (The
problem you describe is specific to the windows backend and does not come up
in unix.)


> I'm doing some other test to reproduce the problem, but I think that
> could be when I got a lot of operation between a BEGIN; COMMIT;
> So is it ok to use the BEGIN; COMMIT; with the WAL journal activated?
> is there some kind of limit in the number of operation between a BEGIN;
> COMMIT; statement?
>

SQLite will not checkpoint the journal until you commit your transaction.
So if you leave the transaction open too long, the WAL file and the -shm
file will grow excessively large.  WAL works best with many smaller
transactions.  If you have one or two big transactions, then using a
traditional rollback-journal mode works better.



> I try to use the PRAGMA wal_checkpoint; to try resolve this situation,
> but seams that command was ignored by sqlite because the -wal file does
> not change in size, even the DB file.
> ___
> 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] memory wasted shm mapped file (3.7.2)

[Michele Pradella]

  Hi,
I found a strange behavior of the sqlite 3.7.2 with WAL journal mode.
Yesterday I found my application DB with a -wal file of 1,5GB and a -shm 
file of few MB (about 9MB) with a DB file of 1,2GB: in this
situation I got the process memory wasted by "mapped file" of the -shm 
file. It seams that the file is mapped a lot of times in memory so the 
process memory become 2GB and it can't allocate more memory. In that 
situation operation made on the DB cause I/O disk errors probably due to 
the wasted memory.
I'm doing some other test to reproduce the problem, but I think that 
could be when I got a lot of operation between a BEGIN; COMMIT;
So is it ok to use the BEGIN; COMMIT; with the WAL journal activated?
is there some kind of limit in the number of operation between a BEGIN; 
COMMIT; statement?
I try to use the PRAGMA wal_checkpoint; to try resolve this situation, 
but seams that command was ignored by sqlite because the -wal file does 
not change in size, even the DB file.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users