[sqlite] caching sqlite3_db_mutex()

[Baruch Burstein]

Hi,

Is it safe to cache the mutex pointer returned by `sqlite3_db_mutex(sqlite3*)`?
Can/does the mutex pointer change thought the life of the `sqlite3` object?

Thanks,
Baruch

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] caching sqlite3_db_mutex()

[Richard Hipp]

On 5/25/17, Baruch Burstein  wrote:
>
> Is it safe to cache the mutex pointer returned by
> `sqlite3_db_mutex(sqlite3*)`?
> Can/does the mutex pointer change thought the life of the `sqlite3` object?
>

I think so, yes.  (Warning:  This answer given in the early morning,
before coffee!)

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

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

> 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

[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] auntondex with unique and integer primary key

[Simon Slavin]

On 26 May 2017, at 2:47am, James K. Lowden  wrote:

> Nothing about any SQL statement implies anything about the
> implementation.  Thus, as you know, a unique constraint is not an
> instruction to build an index, much less a requirement to build a
> redundant one. It's a rule. Carry it out as ye may. 

The other side of this is that the SQL engine can make up indexes if it wants 
to.  And it can delete those indexes (though not indexes you asked for) if it 
wants to.  Automatic indexes are a detail of how the SQL engine does its job 
and not the programmer's responsibility.

So yes, OP spotted an unnecessary index in the cited case.  Whether it’s worth 
spotting the combination of things that lead to it is not settled.  Checking 
for the redundancy of "PRIMARY KEY UNIQUE" may slow down every CREATE TABLE 
command whereas this redundent index is created only when the programmer asks 
for it.  It may not be worth changing the way SQLite works now.

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

Re: [sqlite] auntondex with unique and integer primary key

[James K. Lowden]

On Fri, 19 May 2017 13:06:23 -0600
"Keith Medcalf"  wrote:

> You asked for the extra index to be created in the table
> specification.  It is not the job of the database engine to correct
> your errors (it is not even possible to know if it is an error).  

He didn't ask.  It's not an error.  And it is within the purview of the
DBMS to make any implementation decision it wishes to effect the
described outcome.  Other than that, we agree!  

Nothing about any SQL statement implies anything about the
implementation.  Thus, as you know, a unique constraint is not an
instruction to build an index, much less a requirement to build a
redundant one. It's a rule. Carry it out as ye may.  

Redundant constraints are not errors.  They're not wrong in any sense.
They're not minimal, that's all.  The logical redundancy can be
ascertained by the system, and there the redundancy can be excised.
Nothing forces the DBMS to check twice something that need be checked
only once.  

I appreciate that there are other constraints on the system, such as
backwards compatibility and feasibilty.  That's fine.  But let's not
gussy up pragmatic choices or problematic history as logically
mandated.  If we do, we're apt to miss opportunities to make
improvements, and teach users the wrong lessons about what to do.  

--jkl


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

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

[Wout Mertens]

I am liking the simplicity of the better-sqlite3 Nodejs library, but it is
synchronous (for some good reasons), so it will hang the main thread until
sqlite is done.

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().

The idea is to call sqlite_step() in a thread and then wait for the main
thread to copy the result into JS values (JS memory is not threadsafe),
then wait again and so on.

I wonder, is it only the first _step() that takes a really long time, doing
the query planning and index reads etc, and the rest is reading mostly from
buffered data? That would allow reading all the values in the main thread,
after the initial wait, or maybe per 10 rows or so.

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

Are the above assumptions correct? Any other calls (besides opening the db)
that can take a long time?
___
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?

[Simon Slavin]

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.

Here’s the thing.  An experienced SQLite programmer creates indexes suited to 
all WHERE and ORDER BY clauses they use, and then executes ANALYZE.  If you do 
this, SQLite can always do (A), and because everything executes quickly there’s 
little advantage to asynchronous queries.  The only time you really need to 
worry about (B) and (C) is when you provide facilities to your user allowing 
them to make up their own selection or sort order.  Of course, your particular 
needs may include this.

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.

Hope this helps.

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