> Have you tried the same queries on the SQLite3 client to see if you get the
> same blow up of memory?
Thank you for the suggestion and your other questions are answered further
down. I didn't think to try this, since I didn't know how to handle BLOBs in
the client, but looks like I can use a very simple writefile:
select writefile('I:\test.jpg', Image) from Thumbnail;
http://i.imgur.com/QuHfDBG.png
(console is actively scrolling)
It did not seem to blow up the memory used by the Active Mapped File even after
a long time, unlike the test application:
http://i.imgur.com/5zCnRtf.png
So unless what I'm doing in the client isn't equivalent enough to my test
application (see below), maybe it's a problem tied to System.Data.SQLite and
not a problem with sqlite?
Support for System.Data.SQLite links to this mailing list as well, which is how
I got here.
If anyone can think of anything I'm doing wrong in my test application
(http://i.imgur.com/mod5ISX.png), please let me know. Since it's either that or
a System.Data.SQLite problem?
> 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?
I wrote a test program which you can see here, which might help explain the
context quickly:
http://i.imgur.com/mod5ISX.png
The program simply reads the thumbnail/BLOB from the the database as a byte
array and just throws it away. The program when running uses the expected
amount of RAM, but the Active Mapped File for the database seems to skyrocket
in size. A more verbose explanation can be found here:
https://www.mail-archive.com/sqlite-users%40mailinglists.sqlite.org/msg103701.html
The problem seems to persist as long as the connection persists.
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.
Why do I have the test program? It was to help narrow down an issue I was
having for another complex application, and the problem was only occurring on a
specific database file and seems to be related to BLOBs.
Lastly, average GLOB length (n=100000): ~22621 bytes
****
Also if it helps anyone, you can see the entire thread here:
https://www.mail-archive.com/sqlite-users%40mailinglists.sqlite.org/
https://www.mail-archive.com/sqlite-users%40mailinglists.sqlite.org/msg103701.html
Hopefully mail doesn't break my links and new lines, since everything looks
okay right now.
Thank you for reading and for any suggestions!
On Friday, May 26, 2017 6:37 PM, Stephen Chrzanowski <pontia...@gmail.com>
wrote:
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 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 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
>
>
>
> _______________________________________________
> 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
>
_______________________________________________
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
