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

[Jamie]

> 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=10): ~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  
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  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 t

Re: [sqlite] problem with special letters

[jose isaias cabrera]

Make sure your CSV file is save as UTF-8 with encoding.  If it is, then the 
import piece of Lazarus does not pick up UTF8 characters correctly.  If any 
text file has an encoding or Byte Order Mark (BOM) at the beginning of the 
file, a good program should be able to interpret the correct encoding and 
provide the correct display of the various characters.



-Original Message- 
From: hfiandor

Sent: Friday, May 26, 2017 5:18 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] problem with special letters

Dear collegues:



i have an application with two ways to introduce data in the associate
table: by keyboard o thru a bottom named "Insert".



My language is Spanish, and some letters can have a special sign: a and á,
for example.

If i introduce a word as Biología by the keyboard, no problem. The table
write in the corresponding field "Biología".

If i prepare a file.csv with the data, no problem with the .csv file. The
file has "Biología"-

if i use the Insert bottom, the program read the file.csv and then follow
the instruction "insert into ..", and here is a problem.

The SQLite table don´t write "Biología" as I suppose, the program write
"Biolog¿a".

When i try to prepare a Report, copy exactly what the table has: "Biolog¿a".

I think: Lazarus work well when the data is introduced by keyboard, but
something fail when read a .csv (Biología) and translate to the SQLite´table
"Biolog¿a" with the insert into... command.

I will appreciate very much if somebody can help me.

Thanks in advance,

yours

Ing. Héctor F. Fiandor Rosario



___
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] problem with special letters

[Stephen Chrzanowski]

Joseph;

FYI, Lazarus is a programming IDE for the Pascal language.  It comes built
with Free Pascal.  Laz comes with built in SQLite3 support, but I've never
played with that part of its functionality as I use other wrappers from
Delphi.

On Fri, May 26, 2017 at 5:33 PM, Joseph L. Casale  wrote:

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On
> Behalf Of hfiandor
> Sent: Friday, May 26, 2017 3:18 PM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] problem with special letters
>
> > I think: Lazarus work well when the data is introduced by keyboard, but
> > something fail when read a .csv (Biología) and translate to the
> SQLite´table
> > "Biolog¿a" with the insert into... command.
>
> So your program has an encoding error, I don't know what Lazarus is or if
> that is the program? Can you elaborate a bit?
> ___
> 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

[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  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 
> 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

Re: [sqlite] problem with special letters

[hfiandor]

Dear Mr. Simon:

Thanks for your rapid answer. I just am using your suggestion of insert into 
and it work fine.

I will try to read the files you suggest. (and study)

Thanks again

hfiandor

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

Re: [sqlite] problem with special letters

[Simon Slavin]

On 26 May 2017, at 10:18pm, hfiandor  wrote:

> I think: Lazarus work well when the data is introduced by keyboard, but
> something fail when read a .csv (Biología) and translate to the SQLite´table
> "Biolog¿a" with the insert into... command.

Dear Hector,

Which operating system are you using ?  Please include version name like 
"Windows 8" or "macOS 10.11".

Are you using the Lazarus Pascal tool to read your CSV file ?  If you are not 
already using it please try the tool written by the SQLite team:



See section 8 for reading a CSV file.

You can download it under "Precompiled Binaries" from here:



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

Re: [sqlite] problem with special letters

[Joseph L. Casale]

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of hfiandor
Sent: Friday, May 26, 2017 3:18 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] problem with special letters
 
> I think: Lazarus work well when the data is introduced by keyboard, but
> something fail when read a .csv (Biología) and translate to the SQLite´table
> "Biolog¿a" with the insert into... command.

So your program has an encoding error, I don't know what Lazarus is or if
that is the program? Can you elaborate a bit?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] problem with special letters

[hfiandor]

Dear collegues:

 

i have an application with two ways to introduce data in the associate
table: by keyboard o thru a bottom named "Insert".

 

My language is Spanish, and some letters can have a special sign: a and á,
for example.

If i introduce a word as Biología by the keyboard, no problem. The table
write in the corresponding field "Biología".

If i prepare a file.csv with the data, no problem with the .csv file. The
file has "Biología"-

if i use the Insert bottom, the program read the file.csv and then follow
the instruction "insert into ..", and here is a problem.

The SQLite table don´t write "Biología" as I suppose, the program write
"Biolog¿a".

When i try to prepare a Report, copy exactly what the table has: "Biolog¿a".

I think: Lazarus work well when the data is introduced by keyboard, but
something fail when read a .csv (Biología) and translate to the SQLite´table
"Biolog¿a" with the insert into... command.

I will appreciate very much if somebody can help me.

Thanks in advance,

yours

Ing. Héctor F. Fiandor Rosario

 

___
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]

> 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  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.ne

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 
> wrote:
> 
> 
>  On Friday, 26 May, 2017 08:27, Jamie  said:
> 
> >>  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.
> 
> 
>

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] NOT NULL integer primary key

[James K. Lowden]

On Fri, 26 May 2017 18:04:14 +0200 (CEST)
Eric  wrote:

> Why should the INSERT return an error? It is quite OK to, when
> inserting a row, not specify a value for a NOT NULL column - as long
> as the DDL has specified some way of constructing a value. 

The SQL in question is

>>> insert into test values (null, 'row1');

There's a difference between not supplying a value, and specifying
NULL: 

insert into test values (null, 'row1');
insert into test (data) values ('row1');

Specifying NULL for a non-null column is an error in every SQL DBMS with
which I'm familar, whether or not a default is defined.  

> > It's a documented feature, so it's not a bug.  But it is decidedly
> > odd.  
> 
> The DDL specifies, in SQLite's own unique way, that a value will be
> provided, and the application author knows that a value will be
> provided, so where on earth is the problem? I don't think it's
> peculiar at all

It's peculiar because it doesn't say what it does.  It's nonstandard,
and nothing in the text of the DDL suggests a default exists.  SQL-92
includes syntax to define a default; it uses the word DEFAULT.  

It's a problem because the behavior is in exception to what the
standard specifies, the opposite of what is plainly expressed, and
contrary to what any normal SQL DBMS does. The SQLite user has to be
aware that non-NULL primary key "integer" columns have a special,
implicit property: that NULL is accepted on INSERT, and converted to an
indeterminate value.  

Put simply, how does the syntax 

id integer not null primary key

suggest that an inserted NULL will be converted to a value?  

If a magic value generated silently for a primary key isn't peculiar
enough, consider that it behaves differently on INSERT and UPDATE.
Try to set the same column to NULL with INSERT, get a value; with
UPDATE, it's an error. How many datatypes have that property? One.  

> the application author knows

As I said, it's not a bug because it's well documented.  But if you
haven't studied the SQLite documentation in detail, you might be
forgiven for thinking that INT PRIMARY KEY and INTEGER PRIMARY KEY mean
the same thing.  I'm not so sure every application author knows, but
it's a safe bet many find out the hard way.  

--jkl




___
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] C API: which calls have the biggest chance of latency?

[Jens Alfke]

> On May 25, 2017, at 10:00 PM, Wout Mertens  wrote:
> 
> I would like to make it partially asynchronous, still doing most of the
> work on the main thread, but waiting in a helper thread. I was thinking
> that the longest delays will be from disk access, so sqlite_step().

SQLite has a cache, so accessing recently-used pages of the database doesn’t 
hit the disk at all. (Unless the OS is low on memory and parts of the cache 
have been paged out…)
And the OS has a disk cache, so even if SQLite has to hit the filesystem, that 
request might be nearly instantaneous if the page is cached by the OS.
If actual disk access is required, the time can vary wildly — it depends on on 
how many other I/O requests by other processes are in the queue, what type of 
disk it is, how far the head has to seek if it’s a hard disk…

What I’m saying is that it’s pretty hard to predict. In the worst case I’ve 
seen queries that normally take a millisecond last for several seconds, when 
the OS is heavily bogged down with I/O.

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

Re: [sqlite] Two minor issues reported by with MSVC 2017

[Dan Kennedy]

On 05/26/2017 10:13 PM, Olivier Mascia wrote:

This is on amalgamation code for version 3.19.2. Compiling for 64 bits.
Those were not reported with 3.18 as far as I remember.


Thanks for reporting these. Looks like Joe already fixed them yesterday:

  http://www.sqlite.org/src/info/57a60e959c198b87

Dan.






(1) sqlite3.c(189575): warning C4267: 'function': conversion from 'size_t' to 
'int', possible loss of data

Line:
   iHash = fts5HashKey(nNew, (u8*)fts5EntryKey(p), strlen(fts5EntryKey(p)));

Changed to:
   iHash = fts5HashKey(nNew, (u8*)fts5EntryKey(p), 
(int)strlen(fts5EntryKey(p)));


(2) sqlite3.c(189888): error C4703: potentially uninitialized local pointer 
variable 'zKey' used

 *ppDoclist = (const u8*)&zKey[nTerm+1];

Line:
   char *zKey;

Changed to:
   char *zKey = 0;

Though it looks like zKey indeed is never null when p!=0. That would account 
for a false detection by the compiler.



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

Re: [sqlite] NOT NULL integer primary key

[Eric]

On Thu, 25 May 2017 21:47:20 -0400, "James K. Lowden" 
 wrote:
> On Fri, 19 May 2017 12:47:32 -0600
> "Keith Medcalf"  wrote:
>>On Thursday, 18 May, 2017 10:17, Paul Sanderson 
>> wrote:
>>> Create table test (id integer not null primary key, data text);
>>> insert into test values (null, 'row1');
>>> select * from test;
>>> 1, row1
> 
>> Specifying NOT NULL on an INTEGER PRIMARY KEY (which is an alias for
>> the RowID) is a redundant redundancy.  The RowID cannot be null and
>> must have a value.  

What on earth is a "redundant redundancy"? Never mind, it's irrelevant.

> Maybe, but despite saying it twice, the INSERT succeeded, when it
> should return an error.  

"INTEGER PRIMARY KEY NOT NULL" is valid standard SQL syntax, and it is OK
to specify "NOT NULL", if only because the primary key specification might
be lower down the statement, as a table constraint or even (though not in
SQLite) in a subsequent separate statement. This gives the DBMS a chance
to do a consistency check for us, in case we said something equivalent to
"INTEGER PRIMARY KEY NULL" (actually SQLite lets us get away with that).

Why should the INSERT return an error? It is quite OK to, when inserting
a row, not specify a value for a NOT NULL column - as long as the DDL has
specified some way of constructing a value. The usual thing is the DEFAULT
clause, SQL Server has IDENTITY, PostgreSQL has SERIAL ... . The point
is that the DBMS will provide a value, and the NOT NULL will thereby
be obeyed.

> 
>> but should the not null constraint be obeyed?
> 
>> Trying to insert a null value is how you get one generated for you.  
> 
> I just want to point out how peculiar that is.  This is the test:
> 
> 1.  The datatype must be integer
> 2.  The column must be in the primary key
> 3.  The DBMS must be SQLite
> 
> If the above are all true, then, and only then, the INSERT "succeeds"
> in the sense that the data inserted into the database are neither what
> the application supplied, nor what is expressed in the DDL.  
> 
> It's a documented feature, so it's not a bug.  But it is decidedly
> odd.  

The DDL specifies, in SQLite's own unique way, that a value will be
provided, and the application author knows that a value will be provided,
so where on earth is the problem? I don't think it's peculiar at all, not
even SQLite's choice of syntax, which is just a duck choice - the RowID
(an IMPLEMENTATION detail) looks just like an auto-increment integer, and
behaves like an integer primary key, so if you want an auto-increment PK,
overload the syntax to declare it and overload the implementation detail
to make it work.

Eric
-- 
ms fnd in a lbry
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Two minor issues reported by with MSVC 2017

[Olivier Mascia]

This is on amalgamation code for version 3.19.2. Compiling for 64 bits.
Those were not reported with 3.18 as far as I remember.

(1) sqlite3.c(189575): warning C4267: 'function': conversion from 'size_t' to 
'int', possible loss of data

Line:
  iHash = fts5HashKey(nNew, (u8*)fts5EntryKey(p), strlen(fts5EntryKey(p)));

Changed to:
  iHash = fts5HashKey(nNew, (u8*)fts5EntryKey(p), 
(int)strlen(fts5EntryKey(p)));


(2) sqlite3.c(189888): error C4703: potentially uninitialized local pointer 
variable 'zKey' used

*ppDoclist = (const u8*)&zKey[nTerm+1];

Line:
  char *zKey;

Changed to:
  char *zKey = 0;

Though it looks like zKey indeed is never null when p!=0. That would account 
for a false detection by the compiler.

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software


___
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] C API: which calls have the biggest chance of latency?

[Richard Hipp]

On 5/26/17, Wout Mertens  wrote:
>
> Are the above assumptions correct? Any other calls (besides opening the db)
> that can take a long time?

Most of the work associated with opening the database connection
(which is to say, parsing the schema) is deferred until the first time
you call sqlite3_prepare_v2().  Note also that if another process
modifies the schema (for example by running CREATE TABLE or CREATE
INDEX) then SQLite will automatically reparse the whole schema the
next time you run sqlite3_step() for previously prepared statements.

Note that parsing the schema involves calling sqlite3_exec() to run an
SQL statement that reads the schema - something that is possible
because SQLite is reentrant.  See
https://www.sqlite.org/src/artifact/b1140c3d0?ln=298 for the recursive
call to sqlite3_exec() and
https://www.sqlite.org/src/artifact/b1140c3d0?ln=289-291 for the
SELECT statement it uses for this.  Then for each CREATE statement in
the schema, SQLite recurses yet one more time to parse that statement
as well.  See https://www.sqlite.org/src/artifact/b1140c3d0?ln=84 for
the second level recursion.  Prior to the second recursion, SQLite
sets flags that tell the parser not to actually create the tables and
indexes, but just build up its internal symbol table.  Hence, the
statement that gets prepared does not actually get stepped.  The
side-effect of building the symbol tables, which is what we want,
happens during the preparation.

All of this is to say there is a lot going on under the hood, and all
that mechanism is deferred until the last possible moment, which means
it can happen just about anytime.  It is not as simple as saying "all
the hard work is done during sqlite3_step()".

Normally a schema parse takes microseconds - SQLite's parser will
normally churn though 100K or more SQL statements per second -  but it
can be longer depending on how big the schema is.

-- 
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] C API: which calls have the biggest chance of latency?

[R Smith]

On 2017/05/26 7:33 AM, Simon Slavin wrote:

On 26 May 2017, at 6:00am, Wout Mertens  wrote:


Ideally there'd be some way to know if a _step() call will be served from
buffer…

There are (simplified) three possibilities: quick quick, slow slow, and slow 
quick.

A) SQLite finds a good index for the search/sort and can easily locate all the 
rows it needs to execute the call.  In this case, both the initial _step() and 
all the others will execute quickly.

B) SQLite can’t find an ideal index for the query but finds one which will 
allow it to execute the query acceptably, just skipping down the table 
identifying which rows should be processed.  In this case, both the initial 
_step() and all the others will execute slowly.  But if the table is short, or 
if your command needs to execute a large proportion of the rows in the table 
that might not be very slowly.

C) SQLite can’t find any helpful indexes and decides that the most efficient 
way to execute the command involves making a temporary index.  In this case, 
the initial _step() can take a long time but subsequent _step()s can be fast.


Just to add, there could be a great amount of processing involved in 
even starting the query, such as using WITH clauses or SELECTing from a 
sub-query or perhaps using an IN operator on another query and the like. 
In these cases, my experience is (on all DB systems I use) that the 
initial STEP can take several magnitudes more time than the subsequent 
ones. It's hard to pin down a rule, it is not a consistent thing.




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

Re: [sqlite] C API: which calls have the biggest chance of latency?

[Wout Mertens]

On Fri, May 26, 2017 at 7:33 AM Simon Slavin  wrote:

>
> On 26 May 2017, at 6:00am, Wout Mertens  wrote:
>
> > Ideally there'd be some way to know if a _step() call will be served from
> > buffer…
>
> There are (simplified) three possibilities: quick quick, slow slow, and
> slow quick.
>
[...]
Aha, good to know, thanks!

I can’t speak to how useful it will be to handle _step() synchronously in
> real life applications.  Most of my applications are web-facing systems and
> handle their SQLite databases asynchronously because that’s how server
> access works in JS.  They make HTTP calls to a PHP application running on a
> server.  But for SELECT commands there’s really little they can do until
> they’ve got the first line back and know whether there are any rows.  Maybe
> prepare a few DOM structures for the results, but that doesn’t take long.
>

The reason better-sqlite3 calls sqlite in the main thread is that there is
a substantial cost to running the DB query in a different thread, involving
double copying and mutexes. For small databases, that overhead can really
add up, especially if the whole db fits in cache.

In my case however, my app runs web services as well as biggish database
access in a single process, so I can't block for potentially hundreds of
ms. I do like the simplicity of the library, so I'd like it to be minimally
asynchronous.
___
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