Re: [sqlite] :memory: performance in sqlite 3.6

[Seebs]

On 22 Feb 2013, at 8:31, Eduardo Morras wrote:

The only thing i can think that explain it is compilation options, 
specifically SQLITE_TEMP_STORE=0. If you do pragma temp_store=2, does 
it work better?


Nope.

I did check all this stuff out, got ideas from people in the #sqlite IRC 
channel, and so on. I am pretty sure there is an actual problem, which 
may well be fixed in 3.7, but I am sort of suspecting that there may be 
remnants, and that :memory: performance might have significant room for 
improvement.


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

Re: [sqlite] :memory: performance in sqlite 3.6

[Seebs]

On 22 Feb 2013, at 8:07, Stephen Chrzanowski wrote:


I think I missed something;

For clarification, you mention that you're running the tests and are
monitoring memory use but (And here's what I'm missing) you don't see 
a

memory load against the application?


I do see a memory load against the application. It goes from about 1MB 
to about 10MB as the database gets populated. It's definitely doing what 
I expect in terms of storing things in memory, it's not swapping.


With SQLite 3.6, this makes it dramatically slower. With 3.7, speed is 
roughly the same.


You mention indexes.  Are these just basic Unique constraints, or, 
standard
indexes?  How many records are in the database, say on medium to heavy 
load?


A couple of actual indexes. Most of my tests ended up with 28,000 
records, but I did some with 84,000 or so (3x the size), whereupon 
memory usage went up to 20-something MB. Disk performance was unchanged, 
memory performance was about a factor of 2-3 worse.


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

Re: [sqlite] :memory: performance in sqlite 3.6

[Eduardo Morras]

On Wed, 20 Feb 2013 12:06:17 -0600
Seebs  wrote:

> On 20 Feb 2013, at 11:47, Simon Slavin wrote:
> 
> > On 20 Feb 2013, at 5:32pm, Seebs  wrote:
> >
> >> First: The SQL is completely trivial.
> >> Second: I am not having performance problems with sqlite, I am having 
> >> performance problems with :memory:. Performance on files is lovely.
> >
> > This normally means that your computer has very little actual memory 
> > to devote to 'memory' uses.  It is using a lot of swap space (or 
> > paging, or virtual memory, whatever you want to call it).  So when you 
> > think you are doing calls which reference ':memory:' it actually has 
> > to fetch and write those parts of memory to disk.
> 
> I am pretty sure that's not it.
> 
> I know I'm a newbie on the list, but please imagine for the sake of 
> argument that I am a basically competent programmer with enough sysadmin 
> background to be aware of the obvious problems.
> 
> In this case, I tested this pretty carefully over a period of about six 
> hours of testing across multiple machines. I was watching CPU load, 
> memory load, and all that stuff. And this is a machine with >4GB of 
> *free* memory -- that's over and above even the couple GB of disk cache 
> being used.
> 
> Process memory size is not appreciably different between sqlite 3.6 and 
> 3.7, or between page size of 1024 or 8192. Runtime is massively 
> different. I am pretty sure this is an actual computation-time issue, 
> and my intuition is that it's quite possible there's at least some other 
> performance issues lurking, because it appears that :memory: *used to 
> be* dramatically faster than disk, but something changed in the last 
> couple of years.


The only thing i can think that explain it is compilation options, specifically 
SQLITE_TEMP_STORE=0. If you do pragma temp_store=2, does it work better?


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

Re: [sqlite] :memory: performance in sqlite 3.6

[Stephen Chrzanowski]

I think I missed something;

For clarification, you mention that you're running the tests and are
monitoring memory use but (And here's what I'm missing) you don't see a
memory load against the application?  If you're using the :MEMORY:
database, throwing anything at it should bump up the count and NOT release
memory until you vacuum, and if its not, something isn't right.  If the
machine has a +4gb of available memory, I'd suggest maybe creating an
additional table and throwing a gig worth of a record blob and see if
memory does get bumped.

One of my apps toggles between the entire DB in either disk or memory, and
the database itself is close to 300meg in size.  No other schema changes
happen at the database level.  At app launch, if the user has opted to use
memory, the backup API is invoked, and I note that task manager shows
memory filling up accordingly for the application.  During development, one
of my tests was to throw 4gig of data at it, and memory bumped
accordingly.  When the app is configured to run off the disk, memory
utilization hangs at around the 1meg mark and stays consistent, at least
until I start actively using it.  (Undos are stored in memory via SQLite).

My daily/dev/game rig is a first gen Intel I7 @ 3.3ghz, 12gig of memory,
with twin Sata3 256gig SDDs @ RAID-0.  When I toggle between disk and
memory use, I notice a change in performance. The program just feels more
responsive with memory usage instead of 'platter' usage, considering EVERY
time I release the mouse button, the change is immediately written to the
database  (Each change amounts to about 6k of a blob).  I also wrote some
of the code on a lower end I5 with a platter and I still notice a
difference between the two modes as well, and the i5 runs off a 7200rpm
platter SATA3 and only 4gig of memory.  Both are Win7x64 machines.  Mind
you, I've not done any timing bench marks, and what I'm noticing is purely
on just the feel of the app.

You mention indexes.  Are these just basic Unique constraints, or, standard
indexes?  How many records are in the database, say on medium to heavy load?

On Wed, Feb 20, 2013 at 1:06 PM, Seebs  wrote:

> On 20 Feb 2013, at 11:47, Simon Slavin wrote:
>
>  On 20 Feb 2013, at 5:32pm, Seebs  wrote:
>>
>>  First: The SQL is completely trivial.
>>> Second: I am not having performance problems with sqlite, I am having
>>> performance problems with :memory:. Performance on files is lovely.
>>>
>>
>> This normally means that your computer has very little actual memory to
>> devote to 'memory' uses.  It is using a lot of swap space (or paging, or
>> virtual memory, whatever you want to call it).  So when you think you are
>> doing calls which reference ':memory:' it actually has to fetch and write
>> those parts of memory to disk.
>>
>
> I am pretty sure that's not it.
>
> I know I'm a newbie on the list, but please imagine for the sake of
> argument that I am a basically competent programmer with enough sysadmin
> background to be aware of the obvious problems.
>
> In this case, I tested this pretty carefully over a period of about six
> hours of testing across multiple machines. I was watching CPU load, memory
> load, and all that stuff. And this is a machine with >4GB of *free* memory
> -- that's over and above even the couple GB of disk cache being used.
>
> Process memory size is not appreciably different between sqlite 3.6 and
> 3.7, or between page size of 1024 or 8192. Runtime is massively different.
> I am pretty sure this is an actual computation-time issue, and my intuition
> is that it's quite possible there's at least some other performance issues
> lurking, because it appears that :memory: *used to be* dramatically faster
> than disk, but something changed in the last couple of years.
>
> -s
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] :memory: performance in sqlite 3.6

[Seebs]

On 20 Feb 2013, at 11:47, Simon Slavin wrote:


On 20 Feb 2013, at 5:32pm, Seebs  wrote:


First: The SQL is completely trivial.
Second: I am not having performance problems with sqlite, I am having 
performance problems with :memory:. Performance on files is lovely.


This normally means that your computer has very little actual memory 
to devote to 'memory' uses.  It is using a lot of swap space (or 
paging, or virtual memory, whatever you want to call it).  So when you 
think you are doing calls which reference ':memory:' it actually has 
to fetch and write those parts of memory to disk.


I am pretty sure that's not it.

I know I'm a newbie on the list, but please imagine for the sake of 
argument that I am a basically competent programmer with enough sysadmin 
background to be aware of the obvious problems.


In this case, I tested this pretty carefully over a period of about six 
hours of testing across multiple machines. I was watching CPU load, 
memory load, and all that stuff. And this is a machine with >4GB of 
*free* memory -- that's over and above even the couple GB of disk cache 
being used.


Process memory size is not appreciably different between sqlite 3.6 and 
3.7, or between page size of 1024 or 8192. Runtime is massively 
different. I am pretty sure this is an actual computation-time issue, 
and my intuition is that it's quite possible there's at least some other 
performance issues lurking, because it appears that :memory: *used to 
be* dramatically faster than disk, but something changed in the last 
couple of years.


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

Re: [sqlite] :memory: performance in sqlite 3.6

[Simon Slavin]

On 20 Feb 2013, at 5:32pm, Seebs  wrote:

> First: The SQL is completely trivial.
> Second: I am not having performance problems with sqlite, I am having 
> performance problems with :memory:. Performance on files is lovely.

This normally means that your computer has very little actual memory to devote 
to 'memory' uses.  It is using a lot of swap space (or paging, or virtual 
memory, whatever you want to call it).  So when you think you are doing calls 
which reference ':memory:' it actually has to fetch and write those parts of 
memory to disk.

One way to spot this is to use a utility which shows how much of your memory is 
used for what.  So use nmap or Task Manager or Activity Monitor or whatever to 
show memory usage on your computer, then open a database in ':memory:' and 
write 50 Meg of data to it.  If one of the figures went up by 50 Meg then all 
that data is actually being kept in RAM.  But I bet it's actually being written 
to disk.  Because your OS is devoting lots of your RAM to other tasks and 
doesn't have enough room free.  And since ':memory:' is actually being done by 
writing to disk, it's not faster than opening a database on disk.

Simon.

[Warning: I have omitted many distinctions in the above.  If you want a 
sophisticated discussion of the difference between swapspace and virtual memory 
you know where to find it.]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] :memory: performance in sqlite 3.6

[Seebs]

On 20 Feb 2013, at 5:20, Eduardo Morras wrote:

Execution time doing what?, Waiting for I/O? How do you get execution 
time? What sql are you doing?


I'm using sqlite3_profile and summing the reported times in nanoseconds.

Don't run with synchronous off, it's only calms the symptom, don't 
cure/repair the problem and can mask the real problem.


We cannot afford to have any fsyncs ocurring. Host system limitation; if 
sqlite asks for its database to be flushed to disk, that means two dozen 
other disk-intensive processes suddenly have to wait for all their 
writes to be flushed too.



Are you using a join?


No.

I don't use :memory: db, when need to do so, i use a normal db with 
10-20% more page cache than file size. In rare use cases, i use a ram 
memory disk and backup().


I don't have any way to know in advance what the filesize might be.


The test case I was using was pseudo version 1.4.5, on Linux hosts,
using the pseudo wrapper to untar a 28,000 file tarball.


Surely I'm misinterpreted it but, Is the sqlite db in a directory with 
28000 files? Each time a journal or temporal file is created, modified 
and deleted, and the main db file is modified, the directory entry 
must be updated and with 28000 files it's a very slow process.


There are no journal files, journaling is also off.

"pseudo" is a program in which all file-related syscalls in a client 
program are routed through to a server. The server is using sqlite to 
maintain a database of files. The database stores virtualized 
permissions and ownership. It's used to allow a build system to create 
root filesystems without root privileges. There are no rollbacks, and 
data persistence is mostly short-term; most databases last under ten 
minutes.


Please, post an example of your sql, perhaps it can be tuned for 
sqlite.


First: The SQL is completely trivial.
Second: I am not having performance problems with sqlite, I am having 
performance problems with :memory:. Performance on files is lovely.


My claim is that, at a bare minimum, a :memory: database should not be 
SLOWER than a file on disk. It probably ought to be faster, but slower 
implies that something has gone horribly wrong.


No joins, only one table, only a couple of things used as keys, they 
have indexes. Performance is adequate without :memory:.


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

Re: [sqlite] :memory: performance in sqlite 3.6

[Seebs]

On 20 Feb 2013, at 3:59, Clemens Ladisch wrote:


Seebs wrote:

I tried ... an in-memory database.
What I observed was a very, very, large slowdown.
We're talking 10MB of database



That database would be in the OS's file cache anyway.


It should. Problem is, there's a nearby program that was doing fsync() 
operations on something else, and the filesystem's implementation is 
such that an fsync on ANY file is a complete disk flush for everybody. 
So avoiding any writes to the filesystem is really important even with 
synchronous = OFF.



3. It is dramatically reduced in degree by pragma page_size = 8192.



The default cache size is measured in pages.
So maybe your cache size is too small?


Well, that's sort of the question: Should cache size be relevant for a 
:memory: db?



Could you show some example query?
What is the EXPLAIN QUERY PLAN output?


All the queries are completely trivial. "SELECT * FROM files WHERE path 
= '...';" type stuff. They match indexes. And again, this is all 
performance problems *compared to writing to a disk*.


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

Re: [sqlite] :memory: performance in sqlite 3.6

[Eduardo Morras]

On Tue, 19 Feb 2013 16:05:14 -0600
Seebs  wrote:

> I'm afraid I haven't got a nicely isolated reproducer for this.
> 
> I maintain a package, called pseudo, which includes a server built 
> around an sqlite database. In the process of debugging some performance 
> issues, I did some casual benchmarking. One of the first things I tried 
> was an in-memory database. Now, as has been observed, this is not 
> necessarily expected to be dramatically faster than an on-disk database.
> 
> What I observed was a very, very, large slowdown. Time for an overall 
> task relying on the database was increased by a factor of 2-3 -- and the 
> database code is not that significant a part of the runtime, usually. I 
> used the sqlite3_profile() and observed that the sum of reported 
> processing time from that was within a few percent of the total increase 
> in execution time, which is at least suspicious.

Execution time doing what?, Waiting for I/O? How do you get execution time? 
What sql are you doing?

> I did a bunch of testing trying to figure out more about this (and many 
> thanks to the friendly folks in #sqlite IRC who helped suggest some).
> 
> First: No, not swapping or paging. We're talking 10MB of database 
> against 12GB of RAM with several GB free. The database on disk was 
> running synchronous = OFF, so I wasn't necessarily expecting huge 
> improvements.

Don't run with synchronous off, it's only calms the symptom, don't cure/repair 
the problem and can mask the real problem.

> In all cases, I was running against brand-new freshly created databases, 
> whether in memory or on disk.
> 
> What I found:

> 4. It scales roughly with database size; at 28,000 rows, it's quite 
> noticeable, and at 84,000 it's painful.

Are you using a join? 

> I did find one thing that made me at least a little suspicious even in 
> 3.7 (specifically 3.7.15.2). In sqlite3PagerMovepage, there's a comment 
> right up at the top about journaling the page we're moving from, so 
> there's a call to sqlite3PagerWrite() if MEMDB. There's no check for 
> journaling mode, and it seems to me that if journaling is off, this 
> shouldn't be needed.

I remember a previous discussion about this topic.

> But that's not nearly enough to explain this.
> 
> Admittedly, a performance issue which seems mostly fixed in 3.7 is 
> probably a lowish priority. What concerns me is that it seems to me that 
> the performance of :memory: may have taken a severe hit at some point, 
> leading to a flood of internet forum posts, stackoverflow questions, and 
> the like about poor performance of :memory:. Since sqlite is so fast to 
> begin with, this may not have gotten noticed.

I don't use :memory: db, when need to do so, i use a normal db with 10-20% more 
page cache than file size. In rare use cases, i use a ram memory disk and 
backup().

> The test case I was using was pseudo version 1.4.5, on Linux hosts, 
> using the pseudo wrapper to untar a 28,000 file tarball. 

Surely I'm misinterpreted it but, Is the sqlite db in a directory with 28000 
files? Each time a journal or temporal file is created, modified and deleted, 
and the main db file is modified, the directory entry must be updated and with 
28000 files it's a very slow process.

Please, post an example of your sql, perhaps it can be tuned for sqlite.

> I am not sure 
> how easy or hard it would be to duplicate this with a simpler test case, 
> and won't have time to look more closely for a while, if ever. I'm 
> passing this on in case this rings a bell for someone, and to have it in 
> the archives if someone else comes looking.
> 
> -s


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

Re: [sqlite] :memory: performance in sqlite 3.6

[Clemens Ladisch]

Seebs wrote:
> I tried ... an in-memory database.
> What I observed was a very, very, large slowdown.
> We're talking 10MB of database

That database would be in the OS's file cache anyway.

> 3. It is dramatically reduced in degree by pragma page_size = 8192.

The default cache size is measured in pages.
So maybe your cache size is too small?

> 4. It scales roughly with database size; at 28,000 rows, it's quite
> noticeable, and at 84,000 it's painful.

Sounds like some inefficent O(n²) algorithm.

Could you show some example query?
What is the EXPLAIN QUERY PLAN output?


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

[sqlite] :memory: performance in sqlite 3.6

[Seebs]

I'm afraid I haven't got a nicely isolated reproducer for this.

I maintain a package, called pseudo, which includes a server built 
around an sqlite database. In the process of debugging some performance 
issues, I did some casual benchmarking. One of the first things I tried 
was an in-memory database. Now, as has been observed, this is not 
necessarily expected to be dramatically faster than an on-disk database.


What I observed was a very, very, large slowdown. Time for an overall 
task relying on the database was increased by a factor of 2-3 -- and the 
database code is not that significant a part of the runtime, usually. I 
used the sqlite3_profile() and observed that the sum of reported 
processing time from that was within a few percent of the total increase 
in execution time, which is at least suspicious.


I did a bunch of testing trying to figure out more about this (and many 
thanks to the friendly folks in #sqlite IRC who helped suggest some).


First: No, not swapping or paging. We're talking 10MB of database 
against 12GB of RAM with several GB free. The database on disk was 
running synchronous = OFF, so I wasn't necessarily expecting huge 
improvements.


In all cases, I was running against brand-new freshly created databases, 
whether in memory or on disk.


What I found:
1. This problem appears to occur with sqlite 3.6.20, or 3.6.22.
2. It does not appear to occur with sqlite 3.7, or at least not to 
nearly such a degree.

3. It is dramatically reduced in degree by pragma page_size = 8192.
4. It scales roughly with database size; at 28,000 rows, it's quite 
noticeable, and at 84,000 it's painful.
5. Times reported by sqlite3_profile callbacks were alternating 0 and 
43000 ns with a file database, and with a tiny (couple thousand item) 
in-memory database, and more like 215,000ns by the time the database got 
large.


Looking around, I found a 2003-era thing listing sqlite performance 
tips, which listed the in-memory DB as a huge and dramatic performance 
increase. My own experimentation in the past had suggested that I ought 
to see dramatic increases in performance for at least some workloads.


I did find one thing that made me at least a little suspicious even in 
3.7 (specifically 3.7.15.2). In sqlite3PagerMovepage, there's a comment 
right up at the top about journaling the page we're moving from, so 
there's a call to sqlite3PagerWrite() if MEMDB. There's no check for 
journaling mode, and it seems to me that if journaling is off, this 
shouldn't be needed.


But that's not nearly enough to explain this.

Admittedly, a performance issue which seems mostly fixed in 3.7 is 
probably a lowish priority. What concerns me is that it seems to me that 
the performance of :memory: may have taken a severe hit at some point, 
leading to a flood of internet forum posts, stackoverflow questions, and 
the like about poor performance of :memory:. Since sqlite is so fast to 
begin with, this may not have gotten noticed.


Unfortunately, I haven't got a test case I can easily use to test this. 
pseudo doesn't work with versions prior to 3.6 (except maybe it would, I 
think we imposed that check because of a specific failure on some broken 
version of 3.3.6 we encountered on some host).


The test case I was using was pseudo version 1.4.5, on Linux hosts, 
using the pseudo wrapper to untar a 28,000 file tarball. I am not sure 
how easy or hard it would be to duplicate this with a simpler test case, 
and won't have time to look more closely for a while, if ever. I'm 
passing this on in case this rings a bell for someone, and to have it in 
the archives if someone else comes looking.


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