Re: [sqlite] Memory Issue - GLOBs and Windows Active Mapped File

[Jamie]

> So I am guessing that this is probably expected behavior for .NET. You may be 
> able to tune some runtime settings, to tell it to limit heap growth. Or it 
> may be possible via the .NET SQLite API to read the blobs into existing byte 
> array objects, which would mostly eliminate the garbage production.
I am very familiar with how the .Net Framework Garbage Collection works, and 
this isn't really that type of problem. The memory usage of the 
application/process shows completely normal and the massively Large Active 
Mapped File remains as long as the connection remains, even if the program 
doing nothing for hours, days, or weeks. The improper state of the Large Active 
Mapped File isn't caused by the .Net Framework Garbage Collector.

My guess is there's something in the System.Data.SQLite library for reading 
byte data (or BLOBs) that is unintentionally opening the database file in the 
way where it'll remain as an Active Mapped File until the connection closes. 
Maybe it's a typo; maybe the wrong file flags are used, maybe there is some 
improperly used unsafe code? I don't really know. The other alternative there's 
still something wrong in my test application.
I appreciated your insights on it though, and I'm still open to any suggestions 
or comments, or anything I can change in my test application that may resolve 
the issue (http://i.imgur.com/mod5ISX.png).

Thank you everyone!
 

On Saturday, May 27, 2017 3:22 PM, Jens Alfke  wrote:
 

 
> On May 26, 2017, at 5:46 PM, Jamie  wrote:
> 
> Without going into too much detail of explaining C#, everything is discarded 
> appropriately. All disposable objects are wrapped by using statements. And 
> byte arrays vanish once they're out of scope.

They vanish from _your_ perspective, but they do remain in the heap until the 
garbage collector finds and removes them. So from the garbage collector’s POV, 
your program is generating garbage byte arrays as fast as they can be read from 
SQLite.

All garbage collectors  have a dilemma when garbage generation outpaces 
collection, and the current heap fills up. They can either block the allocator 
until they’ve cleared enough space to satisfy the request, or they can grow the 
heap by requesting more space from the OS. Usually they have heuristics for 
doing one or the other in different circumstances. If they go too far one way, 
the process thrashes in the garbage collector, but too far the other way and 
its memory usage blows up (and eventually the entire OS goes into page-swap 
thrash.)

I don’t use .NET so I don’t know its garbage collector's behavior. But my 
experience with other systems (most recently Go) is that they tend to lean more 
toward allocating more memory, so it’s more common to see a runaway garbage 
generator blow up its address space.

So I am guessing that this is probably expected behavior for .NET. You may be 
able to tune some runtime settings, to tell it to limit heap growth. Or it may 
be possible via the .NET SQLite API to read the blobs into existing byte array 
objects, which would mostly eliminate the garbage production.

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


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

Re: [sqlite] Memory Issue - GLOBs and Windows Active Mapped File

[Jens Alfke]

> On May 26, 2017, at 5:46 PM, Jamie  wrote:
> 
> Without going into too much detail of explaining C#, everything is discarded 
> appropriately. All disposable objects are wrapped by using statements. And 
> byte arrays vanish once they're out of scope.

They vanish from _your_ perspective, but they do remain in the heap until the 
garbage collector finds and removes them. So from the garbage collector’s POV, 
your program is generating garbage byte arrays as fast as they can be read from 
SQLite.

All garbage collectors  have a dilemma when garbage generation outpaces 
collection, and the current heap fills up. They can either block the allocator 
until they’ve cleared enough space to satisfy the request, or they can grow the 
heap by requesting more space from the OS. Usually they have heuristics for 
doing one or the other in different circumstances. If they go too far one way, 
the process thrashes in the garbage collector, but too far the other way and 
its memory usage blows up (and eventually the entire OS goes into page-swap 
thrash.)

I don’t use .NET so I don’t know its garbage collector's behavior. But my 
experience with other systems (most recently Go) is that they tend to lean more 
toward allocating more memory, so it’s more common to see a runaway garbage 
generator blow up its address space.

So I am guessing that this is probably expected behavior for .NET. You may be 
able to tune some runtime settings, to tell it to limit heap growth. Or it may 
be possible via the .NET SQLite API to read the blobs into existing byte array 
objects, which would mostly eliminate the garbage production.

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

Re: [sqlite] Memory Issue - GLOBs and Windows Active Mapped File

[Jamie]

. A lot of us are already very familiar with how Windows disk cache
> works. Even in my original post I said it was not Disk Caching. I also
> already provided supporting evidence that it was not the normal Windows
> Disk Cache that would be under Standby Disk Caching (which can also be seen
> under RamMap, and included in one of my images).
>
> You can find more basic information about RamMap here:
> https://blogs.technet.microsoft.com/askperf/2010/08/
> 13/introduction-to-the-new-sysinternals-tool-rammap/
>
> If for some reason you think it's still Windows Disk Cache problem, please
> provide supporting evidence, as I did here against it:
> https://www.mail-archive.com/sqlite-users
>
>    On Friday, May 26, 2017 2:59 PM, Keith Medcalf <kmedc...@dessus.com>
> wrote:
>
>
>
> Do you have the LargeSystemCache set to 0 or to 1?
>
> HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management
>
> If the machine is *not* running anything and is "just a file server" the
> you want LargeSystemCache=1 and DisablePagingExecutive=1
>
> If it runs any applications at all (that is, anything that did not come
> with the default install of the OS) then you want LargeSystemCache=0 and
> DisablePagingExecutive set depending on whether or not you want the OS
> itself to be swappable (which depends on how much memory you can afford --
> if you can afford more than 4 GB, set DisablePagingExecutive=1 to increase
> performance by forcing the OS kernel to remain in V:R memory (the parts in
> V=R always remain resident)).
>
> You should also make sure all the "I can't afford more than 256 KB of RAM"
> features that do nothing other than slow the system down when you have
> suffient resources such as SuperFetch, Prefetch, etc. are disabled.  They
> really adversely affect performance unless disabled.
>
> So, you have not yet said what the *real* issue is.
>
> Is this causing a preformance issue (which would include VirtAlloc
> failures or Commit Failures -- also known as Out of Memory errors), or are
> you just upset that the RAM for which money was paid is actually being used
> for something?
>
> --
> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
>
> > -Original Message-
> > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > On Behalf Of Jamie
> > Sent: Friday, 26 May, 2017 12:41
> > To: SQLite mailing list
> > Subject: Re: [sqlite] Memory Issue - GLOBs and Windows Active Mapped File
> >
> > > https://support.microsoft.com/en-us/help/2549369/performance-degrades-
> > when-accessing-large-files-with-file-flag-random-access
> > >
> > > Although the optimization is good, it sounds like it is the typical
> > Microsoft designed-by-flock-of-idiots software.  There is absolutely no
> > way that this should *ever* happen unless the cache was designed by
> > complete utter morons.  Of course, knowing the history of this the code
> > that "works properly" was probably patented IBM technology that had to be
> > removed and re-written (defectively) by Microsoft after they stole OS/2
> to
> > develop Windows NT ...
> > >
> > > And I know that this "bug" is present still in Windows 10 1607.  Don't
> > know if they have fixed it in 1703, but I kind of doubt it.  Instead they
> > added "page compression" (that you cannot disable) to create even more
> > problems.
> > While there is a lot of controversy with how Windows handles Disk Cache,
> I
> > don't think the problem I'm experiencing is related to that. I don't
> > believe Windows will ever set an Active File Mapping for Disk Cache,
> since
> > these are often tied to running processes/applications.
> > I think the problem may be related to reading byte data (BLOBs) from a
> > Database, since the Active File Map only seems to occur after reading
> > BLOBs from a table, which is why I considered sqlite or the sqlite.net
> > library may be reading from the disk with the wrong parameters or not
> > properly freeing the resources after they've been used for reading BLOBs.
> > I created that simple program (the one that just reads BLOBs over and
> > over) just to help narrow down the problem.
> >
> > To reiterate, the Active File Maps only seem to appear and persist for
> > files/tables that I have read byte data (BLOBs) from. Which is why I
> think
> > the problem is related to sqlite and reading byte data (BLOBs).
> >
> >
> >    On Friday, May 26, 2017 1:04 PM, Keith Medcalf <kmedc...@dessus.com>
> > wrote:
> >
> >
> >  On Friday, 26 May, 2017 08:27,

Re: [sqlite] Memory Issue - GLOBs and Windows Active Mapped File

[Stephen Chrzanowski]

Out of curiosity, how big is the data and GLOBs, and how long does the
connection persist, and where are you putting the information once read?

From previous recent posts (Not necessarily from this thread) its been
mentioned that SQLite reads one row at a time, then discards the data on
the next step.  Have you verified that what you're reading in is being
disposed after use and not just sticking around?

Have you tried the same queries on the SQLite3 client to see if you get the
same blow up of memory?

On Fri, May 26, 2017 at 5:00 PM, Jamie <eqrecov...@yahoo.com> wrote:

> > So, you have not yet said what the *real* issue is.
>
> My original message described the issue very plainly (
> https://www.mail-archive.com/sqlite-users%40mailinglists.
> sqlite.org/msg103701.html):>> When I'm performing a large amount of
> selects of GLOBs/thumbnails from an ongoing SQLiteConnection, I'm having a
> problem where the Windows Active Mapped File will constantly grow out of
> control in size (memory leak?).>>
> >> If anyone knows how I can prevent the Active Mapped File from
> continuously growing larger, please let me know.
>
> > Is this causing a preformance issue
> Yes, since it continues to consume more memory as the connection remains,
> basically a memory leak. I've seen it as high as 4GB, before I realized
> there was a problem after I was noticing performance issues on the machine.
> I'm assuming it would continue to grow until everything else got paged to
> disk. This problem does not seem to occur with any of other database files,
> so I'm pretty sure it's related to table selects reading BLOBs or byte data.
>
> > or are you just upset that the RAM for which money was paid is actually
> being used for something?
> Please try to be productive here and please read the messages. I already
> responded to your very verbose and incorrect message staying it was Disk
> Cache. A lot of us are already very familiar with how Windows disk cache
> works. Even in my original post I said it was not Disk Caching. I also
> already provided supporting evidence that it was not the normal Windows
> Disk Cache that would be under Standby Disk Caching (which can also be seen
> under RamMap, and included in one of my images).
>
> You can find more basic information about RamMap here:
> https://blogs.technet.microsoft.com/askperf/2010/08/
> 13/introduction-to-the-new-sysinternals-tool-rammap/
>
> If for some reason you think it's still Windows Disk Cache problem, please
> provide supporting evidence, as I did here against it:
> https://www.mail-archive.com/sqlite-users
>
> On Friday, May 26, 2017 2:59 PM, Keith Medcalf <kmedc...@dessus.com>
> wrote:
>
>
>
> Do you have the LargeSystemCache set to 0 or to 1?
>
> HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management
>
> If the machine is *not* running anything and is "just a file server" the
> you want LargeSystemCache=1 and DisablePagingExecutive=1
>
> If it runs any applications at all (that is, anything that did not come
> with the default install of the OS) then you want LargeSystemCache=0 and
> DisablePagingExecutive set depending on whether or not you want the OS
> itself to be swappable (which depends on how much memory you can afford --
> if you can afford more than 4 GB, set DisablePagingExecutive=1 to increase
> performance by forcing the OS kernel to remain in V:R memory (the parts in
> V=R always remain resident)).
>
> You should also make sure all the "I can't afford more than 256 KB of RAM"
> features that do nothing other than slow the system down when you have
> suffient resources such as SuperFetch, Prefetch, etc. are disabled.  They
> really adversely affect performance unless disabled.
>
> So, you have not yet said what the *real* issue is.
>
> Is this causing a preformance issue (which would include VirtAlloc
> failures or Commit Failures -- also known as Out of Memory errors), or are
> you just upset that the RAM for which money was paid is actually being used
> for something?
>
> --
> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
>
> > -Original Message-
> > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > On Behalf Of Jamie
> > Sent: Friday, 26 May, 2017 12:41
> > To: SQLite mailing list
> > Subject: Re: [sqlite] Memory Issue - GLOBs and Windows Active Mapped File
> >
> > > https://support.microsoft.com/en-us/help/2549369/performance-degrades-
> > when-accessing-large-files-with-file-flag-random-access
> > >
> > > Although the optimization is good, it sounds like it is the typical
> > Microsoft designed-by-flock-of-idiots softwar

Re: [sqlite] Memory Issue - GLOBs and Windows Active Mapped File

[Jamie]

> So, you have not yet said what the *real* issue is. 

My original message described the issue very plainly 
(https://www.mail-archive.com/sqlite-users%40mailinglists.sqlite.org/msg103701.html):>>
 When I'm performing a large amount of selects of GLOBs/thumbnails from an 
ongoing SQLiteConnection, I'm having a problem where the Windows Active Mapped 
File will constantly grow out of control in size (memory leak?).>>
>> If anyone knows how I can prevent the Active Mapped File from continuously 
>> growing larger, please let me know.

> Is this causing a preformance issue
Yes, since it continues to consume more memory as the connection remains, 
basically a memory leak. I've seen it as high as 4GB, before I realized there 
was a problem after I was noticing performance issues on the machine. I'm 
assuming it would continue to grow until everything else got paged to disk. 
This problem does not seem to occur with any of other database files, so I'm 
pretty sure it's related to table selects reading BLOBs or byte data.

> or are you just upset that the RAM for which money was paid is actually being 
> used for something?
Please try to be productive here and please read the messages. I already 
responded to your very verbose and incorrect message staying it was Disk Cache. 
A lot of us are already very familiar with how Windows disk cache works. Even 
in my original post I said it was not Disk Caching. I also already provided 
supporting evidence that it was not the normal Windows Disk Cache that would be 
under Standby Disk Caching (which can also be seen under RamMap, and included 
in one of my images).

You can find more basic information about RamMap here: 
https://blogs.technet.microsoft.com/askperf/2010/08/13/introduction-to-the-new-sysinternals-tool-rammap/

If for some reason you think it's still Windows Disk Cache problem, please 
provide supporting evidence, as I did here against it: 
https://www.mail-archive.com/sqlite-users

On Friday, May 26, 2017 2:59 PM, Keith Medcalf <kmedc...@dessus.com> wrote:
 

 
Do you have the LargeSystemCache set to 0 or to 1?

HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management

If the machine is *not* running anything and is "just a file server" the you 
want LargeSystemCache=1 and DisablePagingExecutive=1

If it runs any applications at all (that is, anything that did not come with 
the default install of the OS) then you want LargeSystemCache=0 and 
DisablePagingExecutive set depending on whether or not you want the OS itself 
to be swappable (which depends on how much memory you can afford -- if you can 
afford more than 4 GB, set DisablePagingExecutive=1 to increase performance by 
forcing the OS kernel to remain in V:R memory (the parts in V=R always remain 
resident)).

You should also make sure all the "I can't afford more than 256 KB of RAM" 
features that do nothing other than slow the system down when you have suffient 
resources such as SuperFetch, Prefetch, etc. are disabled.  They really 
adversely affect performance unless disabled.

So, you have not yet said what the *real* issue is.  

Is this causing a preformance issue (which would include VirtAlloc failures or 
Commit Failures -- also known as Out of Memory errors), or are you just upset 
that the RAM for which money was paid is actually being used for something?

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Jamie
> Sent: Friday, 26 May, 2017 12:41
> To: SQLite mailing list
> Subject: Re: [sqlite] Memory Issue - GLOBs and Windows Active Mapped File
> 
> > https://support.microsoft.com/en-us/help/2549369/performance-degrades-
> when-accessing-large-files-with-file-flag-random-access
> >
> > Although the optimization is good, it sounds like it is the typical
> Microsoft designed-by-flock-of-idiots software.  There is absolutely no
> way that this should *ever* happen unless the cache was designed by
> complete utter morons.  Of course, knowing the history of this the code
> that "works properly" was probably patented IBM technology that had to be
> removed and re-written (defectively) by Microsoft after they stole OS/2 to
> develop Windows NT ...
> >
> > And I know that this "bug" is present still in Windows 10 1607.  Don't
> know if they have fixed it in 1703, but I kind of doubt it.  Instead they
> added "page compression" (that you cannot disable) to create even more
> problems.
> While there is a lot of controversy with how Windows handles Disk Cache, I
> don't think the problem I'm experiencing is related to that. I don't
> believe Windows will ever set an Active File Mapping for Disk Cache, since
> these are often tied to running processes/applications.
> I 

Re: [sqlite] Memory Issue - GLOBs and Windows Active Mapped File

[Keith Medcalf]

Do you have the LargeSystemCache set to 0 or to 1?

HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management

If the machine is *not* running anything and is "just a file server" the you 
want LargeSystemCache=1 and DisablePagingExecutive=1

If it runs any applications at all (that is, anything that did not come with 
the default install of the OS) then you want LargeSystemCache=0 and 
DisablePagingExecutive set depending on whether or not you want the OS itself 
to be swappable (which depends on how much memory you can afford -- if you can 
afford more than 4 GB, set DisablePagingExecutive=1 to increase performance by 
forcing the OS kernel to remain in V:R memory (the parts in V=R always remain 
resident)).

You should also make sure all the "I can't afford more than 256 KB of RAM" 
features that do nothing other than slow the system down when you have suffient 
resources such as SuperFetch, Prefetch, etc. are disabled.  They really 
adversely affect performance unless disabled.

So, you have not yet said what the *real* issue is.  

Is this causing a preformance issue (which would include VirtAlloc failures or 
Commit Failures -- also known as Out of Memory errors), or are you just upset 
that the RAM for which money was paid is actually being used for something?

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Jamie
> Sent: Friday, 26 May, 2017 12:41
> To: SQLite mailing list
> Subject: Re: [sqlite] Memory Issue - GLOBs and Windows Active Mapped File
> 
> > https://support.microsoft.com/en-us/help/2549369/performance-degrades-
> when-accessing-large-files-with-file-flag-random-access
> >
> > Although the optimization is good, it sounds like it is the typical
> Microsoft designed-by-flock-of-idiots software.  There is absolutely no
> way that this should *ever* happen unless the cache was designed by
> complete utter morons.  Of course, knowing the history of this the code
> that "works properly" was probably patented IBM technology that had to be
> removed and re-written (defectively) by Microsoft after they stole OS/2 to
> develop Windows NT ...
> >
> > And I know that this "bug" is present still in Windows 10 1607.  Don't
> know if they have fixed it in 1703, but I kind of doubt it.  Instead they
> added "page compression" (that you cannot disable) to create even more
> problems.
> While there is a lot of controversy with how Windows handles Disk Cache, I
> don't think the problem I'm experiencing is related to that. I don't
> believe Windows will ever set an Active File Mapping for Disk Cache, since
> these are often tied to running processes/applications.
> I think the problem may be related to reading byte data (BLOBs) from a
> Database, since the Active File Map only seems to occur after reading
> BLOBs from a table, which is why I considered sqlite or the sqlite.net
> library may be reading from the disk with the wrong parameters or not
> properly freeing the resources after they've been used for reading BLOBs.
> I created that simple program (the one that just reads BLOBs over and
> over) just to help narrow down the problem.
> 
> To reiterate, the Active File Maps only seem to appear and persist for
> files/tables that I have read byte data (BLOBs) from. Which is why I think
> the problem is related to sqlite and reading byte data (BLOBs).
> 
> 
> On Friday, May 26, 2017 1:04 PM, Keith Medcalf <kmedc...@dessus.com>
> wrote:
> 
> 
>  On Friday, 26 May, 2017 08:27, Jamie <eqrecov...@yahoo.com> said:
> 
> >> <https://msdn.microsoft.com/en-
> us/library/windows/hardware/dn567645.aspx>
> >> says that there is a different kind of file cache for a random-access
> >> file, and that it shows up as active mapped pages.
> >> https://support.microsoft.com/en-us/help/976618/you-experience-
> performance-issues-in-applications-and-services-when-the-system-file->
> cache-consumes-most-of-the-physical-ram
> 
> > These pages are describing an unrelated problem with a Windows
> Service(s),
> > as those active pages under the category for METAFILE, and not under
> > Mapped File.
> 
> https://support.microsoft.com/en-us/help/2549369/performance-degrades-
> when-accessing-large-files-with-file-flag-random-access
> 
> Although the optimization is good, it sounds like it is the typical
> Microsoft designed-by-flock-of-idiots software.  There is absolutely no
> way that this should *ever* happen unless the cache was designed by
> complete utter morons.  Of course, knowing the history of this the code
> that "works properly" was probably patented 

Re: [sqlite] Memory Issue - GLOBs and Windows Active Mapped File

[Jamie]

> https://support.microsoft.com/en-us/help/2549369/performance-degrades-when-accessing-large-files-with-file-flag-random-access
> 
> Although the optimization is good, it sounds like it is the typical Microsoft 
> designed-by-flock-of-idiots software.  There is absolutely no way that this 
> should *ever* happen unless the cache was designed by complete utter morons.  
> Of course, knowing the history of this the code that "works properly" was 
> probably patented IBM technology that had to be removed and re-written 
> (defectively) by Microsoft after they stole OS/2 to develop Windows NT ...
> 
> And I know that this "bug" is present still in Windows 10 1607.  Don't know 
> if they have fixed it in 1703, but I kind of doubt it.  Instead they added 
> "page compression" (that you cannot disable) to create even more problems.
While there is a lot of controversy with how Windows handles Disk Cache, I 
don't think the problem I'm experiencing is related to that. I don't believe 
Windows will ever set an Active File Mapping for Disk Cache, since these are 
often tied to running processes/applications.
I think the problem may be related to reading byte data (BLOBs) from a 
Database, since the Active File Map only seems to occur after reading BLOBs 
from a table, which is why I considered sqlite or the sqlite.net library may be 
reading from the disk with the wrong parameters or not properly freeing the 
resources after they've been used for reading BLOBs. I created that simple 
program (the one that just reads BLOBs over and over) just to help narrow down 
the problem.

To reiterate, the Active File Maps only seem to appear and persist for 
files/tables that I have read byte data (BLOBs) from. Which is why I think the 
problem is related to sqlite and reading byte data (BLOBs).
 

On Friday, May 26, 2017 1:04 PM, Keith Medcalf  wrote:
 

 On Friday, 26 May, 2017 08:27, Jamie  said:

>> 
>> says that there is a different kind of file cache for a random-access
>> file, and that it shows up as active mapped pages.
>> https://support.microsoft.com/en-us/help/976618/you-experience-performance-issues-in-applications-and-services-when-the-system-file->
>>  cache-consumes-most-of-the-physical-ram

> These pages are describing an unrelated problem with a Windows Service(s),
> as those active pages under the category for METAFILE, and not under
> Mapped File.

https://support.microsoft.com/en-us/help/2549369/performance-degrades-when-accessing-large-files-with-file-flag-random-access

Although the optimization is good, it sounds like it is the typical Microsoft 
designed-by-flock-of-idiots software.  There is absolutely no way that this 
should *ever* happen unless the cache was designed by complete utter morons.  
Of course, knowing the history of this the code that "works properly" was 
probably patented IBM technology that had to be removed and re-written 
(defectively) by Microsoft after they stole OS/2 to develop Windows NT ...

And I know that this "bug" is present still in Windows 10 1607.  Don't know if 
they have fixed it in 1703, but I kind of doubt it.  Instead they added "page 
compression" (that you cannot disable) to create even more problems.




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


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

Re: [sqlite] Memory Issue - GLOBs and Windows Active Mapped File

[Keith Medcalf]

On Friday, 26 May, 2017 08:27, Jamie  said:

>> 
>> says that there is a different kind of file cache for a random-access
>> file, and that it shows up as active mapped pages.
>> https://support.microsoft.com/en-us/help/976618/you-experience-performance-issues-in-applications-and-services-when-the-system-file->
>>  cache-consumes-most-of-the-physical-ram

> These pages are describing an unrelated problem with a Windows Service(s),
> as those active pages under the category for METAFILE, and not under
> Mapped File.

https://support.microsoft.com/en-us/help/2549369/performance-degrades-when-accessing-large-files-with-file-flag-random-access

Although the optimization is good, it sounds like it is the typical Microsoft 
designed-by-flock-of-idiots software.  There is absolutely no way that this 
should *ever* happen unless the cache was designed by complete utter morons.  
Of course, knowing the history of this the code that "works properly" was 
probably patented IBM technology that had to be removed and re-written 
(defectively) by Microsoft after they stole OS/2 to develop Windows NT ...

And I know that this "bug" is present still in Windows 10 1607.  Don't know if 
they have fixed it in 1703, but I kind of doubt it.  Instead they added "page 
compression" (that you cannot disable) to create even more problems.




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

Re: [sqlite] Memory Issue - GLOBs and Windows Active Mapped File

[Jamie]

> 
> says that there is a different kind of file cache for a random-access
> file, and that it shows up as active mapped pages.
> https://support.microsoft.com/en-us/help/976618/you-experience-performance-issues-in-applications-and-services-when-the-system-file-cache-consumes-most-of-the-physical-ram
These pages are describing an unrelated problem with a Windows Service(s), as 
those active pages under the category for METAFILE, and not under Mapped File. 
 

On Friday, May 26, 2017 4:43 AM, Clemens Ladisch  wrote:
 

 Jamie wrote:
> this is not the normal Windows File Caching that you would typically
> see. File Caching would be under STANDBY Mapped File

To rule out SQLite's mmap, execute "PRAGMA mmap_size = 0", and then
confirm with the output of "PRAGMA mmap_size".



says that there is a different kind of file cache for a random-access
file, and that it shows up as active mapped pages.

SQLite does not use FILE_FLAG_RANDOM_ACCESS (except on Windows CE), but
it's possible that Windows is estimating that the accesses are random
(because the actually are).


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] Memory Issue - GLOBs and Windows Active Mapped File

[Jamie]

Thank you for your suggestion Clemens. I had responded about the pragma 
mmap_size to Mr. Mistachkin, but it ended up in the wrong place, probably 
because of a mistake I made.
Here is the contents of that message below and sorry about the inconvenience:
I tried changing it to several different values for that pragma, but it did not 
seem to have any affect on how large the Active Mapped File could grow. The 
pragma was set before anything else was done on the connection. Although 
futile; I also tried attempting to use the pragma on the connection string, but 
that did not seem to change the value for the pragma when it was queried 
immediately after.

The default value was 0 when I queried it. The values I tried changing it to 
were: 1048576, 10485760, 1024, 1, -1, and resetting it to 0.

Thank you for your suggestion though, I greatly appreciate it!



> Have you tried using the command "PRAGMA mmap_size=0;" on the connection?
>
> https://www.sqlite.org/pragma.html#pragma_mmap_size
> 
> --
> Joe Mistachkin @ https://urn.to/r/mistachkin 

On Friday, May 26, 2017 4:43 AM, Clemens Ladisch  wrote:
 

 Jamie wrote:
> this is not the normal Windows File Caching that you would typically
> see. File Caching would be under STANDBY Mapped File

To rule out SQLite's mmap, execute "PRAGMA mmap_size = 0", and then
confirm with the output of "PRAGMA mmap_size".



says that there is a different kind of file cache for a random-access
file, and that it shows up as active mapped pages.

SQLite does not use FILE_FLAG_RANDOM_ACCESS (except on Windows CE), but
it's possible that Windows is estimating that the accesses are random
(because the actually are).


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] Memory Issue - GLOBs and Windows Active Mapped File

[Richard Hipp]

On 5/26/17, Clemens Ladisch  wrote:
>
> SQLite does not use FILE_FLAG_RANDOM_ACCESS (except on Windows CE), but
> it's possible that Windows is estimating that the accesses are random
> (because the actually are).
>

IIRC, we experimented with using FILE_FLAG_RANDOM_ACCESS but found
that it made things run slower in our tests.  That would have been
years ago though - things might have changed since then.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory Issue - GLOBs and Windows Active Mapped File

[Clemens Ladisch]

Jamie wrote:
> this is not the normal Windows File Caching that you would typically
> see. File Caching would be under STANDBY Mapped File

To rule out SQLite's mmap, execute "PRAGMA mmap_size = 0", and then
confirm with the output of "PRAGMA mmap_size".



says that there is a different kind of file cache for a random-access
file, and that it shows up as active mapped pages.

SQLite does not use FILE_FLAG_RANDOM_ACCESS (except on Windows CE), but
it's possible that Windows is estimating that the accesses are random
(because the actually are).


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory Issue - GLOBs and Windows Active Mapped File

[Jamie]

> What you are observing is the Windows System File Cache.

I already explained this is not the normal Windows File Caching that you would 
typically see. File Caching would be under STANDBY Mapped File and can be 
easily removed from RAM when memory is needed for something else. You can even 
confirm this yourself very easily, but just running RamMap. Or you can even 
confirm Disk Cache is named "Standby" by just launching Resource Monitor.

Since it's marked as Active, it consumes RAM as if it was a running process. 
Hence the name, active.

From the various screenshots I included you can even confirm it's not normal 
disk cache. Even see after I close the process in Task Manager, the extra RAM 
is immediately freed up and increases the "Cached' and "Available" rows by 
several GBs. If it's not clear, "Cached" is Disk Cache.



On Thursday, May 25, 2017 8:02 PM, Keith Medcalf  
wrote:
 

 On Wednesday, 24 May, 2017 18:48, Jamie  wrote:

> When I'm performing a large amount of selects of GLOBs/thumbnails from an
> ongoing SQLiteConnection, I'm having a problem where the Windows Active
> Mapped File will constantly grow out of control in size (memory leak?).
> This is under Windows 7, using a .Net Framework 4.0 C# application with
> System.Data.SQLite 1.0.105.1. This is not the optional disk cache that can
> be removed from RAM, but rather a Windows Mapped File marked as 'active'
> that will eat up precious RAM, and I've seen as high as 4GB when I let it
> go on.

What you are observing is the Windows System File Cache.

Memory that is "free" (as in not being used for anything at all) is memory that 
you simply should not have bought.  A properly tuned computer system should 
consume exactly 100% of all available resources at all times with no 
over-commitment (that is, no bottlenecks appearing).  On bitty-boxen this is 
usually very hard to achieve because they are designed for limited utility and 
performance.

What is the "issue" you are seeing?  

If it is "only" that what would otherwise be wasted resources being used for 
something that may be useful (this is Windows after all, so utility by design 
is bound to be sum-optimal) then there is really no issue.

If you are encountering performance issues due to excessive system cache 
forcing out more useful purposes (such as process working set), and you have 
already set Windows into "Rational Behaviour" mode (prefer Application over 
Cache, as opposed to the highly irrational windows default to prefer useless 
crap over usefulness -- just like the default used to be to optimize for 
maximum CPU usage -- at least now Windows just sits around consuming no 
resources and doing nothing asking you to please wait for it to get on with 
doing something useful) then you are running into a known Microsoft Bug that 
has existed in all versions of Windows since, well, forever.

https://support.microsoft.com/en-us/help/976618/you-experience-performance-issues-in-applications-and-services-when-the-system-file-cache-consumes-most-of-the-physical-ram

If you are merely upset that the RAM you paid good money for is actually being 
used for something, then simply stop worrying.  As I said, unused RAM is a 
waste of money.  You can reduce the amount of RAM used by physically unplugging 
some from the computer.  As I said though, in "normal" operation all resources 
(including RAM) should be 100% in use.
 
> Active Mapped Files cannot be seen under Windows Task Manager, and won't
> count against the memory usage for a process, rather one will need a
> Microsoft utility called RamMap to see the problem:
> https://technet.microsoft.com/en-us/sysinternals/rammap.aspx

Of course not.  The memory does not belong to and is not associated with the 
process.  It belongs to the system.

> Here is a small test application I created to help isolate the issue. All
> the application does is read BLOBs/thumbnails from a table in an infinte
> loop and throws them away. The application itself uses very little RAM.
> http://i.imgur.com/mod5ISX.png
> 
> Here's an example of The Active Mapped File growing to insane sizes while
> my test application is running and the connection is still open. The
> active column is the one of concern:
> http://i.imgur.com/lVV97m7.png
> 
> Here are another two images of the change in RAM usage in Task Manager,
> before and after closing the process, even though the process itself shows
> very small RAM usage under all memory columns:
> http://i.imgur.com/LRzzUFc.png
> http://i.imgur.com/k5zNkLj.png
> 
> If anyone knows how I can prevent the Active Mapped File from continuously
> growing larger, please let me know. I've tried changing the Connection
> String, calling different methods on the SQLiteConnection (reset and
> clear), and ensured I was disposing everything appropriately, but I
> haven't managed to find how to reduce the the Active Mapped File size
> while the connection remains.
> 
> Thank you for 

Re: [sqlite] Memory Issue - GLOBs and Windows Active Mapped File

[Keith Medcalf]

On Wednesday, 24 May, 2017 18:48, Jamie  wrote:

> When I'm performing a large amount of selects of GLOBs/thumbnails from an
> ongoing SQLiteConnection, I'm having a problem where the Windows Active
> Mapped File will constantly grow out of control in size (memory leak?).
> This is under Windows 7, using a .Net Framework 4.0 C# application with
> System.Data.SQLite 1.0.105.1. This is not the optional disk cache that can
> be removed from RAM, but rather a Windows Mapped File marked as 'active'
> that will eat up precious RAM, and I've seen as high as 4GB when I let it
> go on.

What you are observing is the Windows System File Cache.

Memory that is "free" (as in not being used for anything at all) is memory that 
you simply should not have bought.  A properly tuned computer system should 
consume exactly 100% of all available resources at all times with no 
over-commitment (that is, no bottlenecks appearing).  On bitty-boxen this is 
usually very hard to achieve because they are designed for limited utility and 
performance.

What is the "issue" you are seeing?  

If it is "only" that what would otherwise be wasted resources being used for 
something that may be useful (this is Windows after all, so utility by design 
is bound to be sum-optimal) then there is really no issue.

If you are encountering performance issues due to excessive system cache 
forcing out more useful purposes (such as process working set), and you have 
already set Windows into "Rational Behaviour" mode (prefer Application over 
Cache, as opposed to the highly irrational windows default to prefer useless 
crap over usefulness -- just like the default used to be to optimize for 
maximum CPU usage -- at least now Windows just sits around consuming no 
resources and doing nothing asking you to please wait for it to get on with 
doing something useful) then you are running into a known Microsoft Bug that 
has existed in all versions of Windows since, well, forever.

https://support.microsoft.com/en-us/help/976618/you-experience-performance-issues-in-applications-and-services-when-the-system-file-cache-consumes-most-of-the-physical-ram

If you are merely upset that the RAM you paid good money for is actually being 
used for something, then simply stop worrying.  As I said, unused RAM is a 
waste of money.  You can reduce the amount of RAM used by physically unplugging 
some from the computer.  As I said though, in "normal" operation all resources 
(including RAM) should be 100% in use.
 
> Active Mapped Files cannot be seen under Windows Task Manager, and won't
> count against the memory usage for a process, rather one will need a
> Microsoft utility called RamMap to see the problem:
> https://technet.microsoft.com/en-us/sysinternals/rammap.aspx

Of course not.  The memory does not belong to and is not associated with the 
process.  It belongs to the system.

> Here is a small test application I created to help isolate the issue. All
> the application does is read BLOBs/thumbnails from a table in an infinte
> loop and throws them away. The application itself uses very little RAM.
> http://i.imgur.com/mod5ISX.png
> 
> Here's an example of The Active Mapped File growing to insane sizes while
> my test application is running and the connection is still open. The
> active column is the one of concern:
> http://i.imgur.com/lVV97m7.png
> 
> Here are another two images of the change in RAM usage in Task Manager,
> before and after closing the process, even though the process itself shows
> very small RAM usage under all memory columns:
> http://i.imgur.com/LRzzUFc.png
> http://i.imgur.com/k5zNkLj.png
> 
> If anyone knows how I can prevent the Active Mapped File from continuously
> growing larger, please let me know. I've tried changing the Connection
> String, calling different methods on the SQLiteConnection (reset and
> clear), and ensured I was disposing everything appropriately, but I
> haven't managed to find how to reduce the the Active Mapped File size
> while the connection remains.
> 
> Thank you for any help on the matter, and sorry for the long explanation.

Like I said, you can remove RAM from the computer.  If you currently have 8 GB 
and you remove 4 GB, then only 4 GB of RAM will be used.  Other than that there 
is no point whatsoever in doing anything at all unless you have run into the 
known Windows Bug where the system cache forces useful stuff out of memory and 
causes working set thrashing.
 



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

Re: [sqlite] Memory Issue - GLOBs and Windows Active Mapped File

[Jamie]

I tried changing it to several different values for that pragma, but it did not 
seem to have any affect on how large the Active Mapped File could grow. The 
pragma was set before anything else was done on the connection. Although 
futile; I also tried attempting to use the pragma on the connection string, but 
that did not seem to change the value for the pragma when it was queried 
immediately after.

The default value was 0 when I queried it. The values I tried changing it to 
were: 1048576, 10485760, 1024, 1, -1, and resetting it to 0.

Thank you for your suggestion though, I greatly appreciate it!



> Have you tried using the command "PRAGMA mmap_size=0;" on the connection?
>
> https://www.sqlite.org/pragma.html#pragma_mmap_size
> 
> --
> Joe Mistachkin @ https://urn.to/r/mistachkin
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory Issue - GLOBs and Windows Active Mapped File

[Joe Mistachkin]

Have you tried using the command "PRAGMA mmap_size=0;" on the connection?

https://www.sqlite.org/pragma.html#pragma_mmap_size 

--
Joe Mistachkin @ https://urn.to/r/mistachkin

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