Re: [sqlite] In-memory DB slower than disk-based?

[Jensen, Vern]

Yes Stephen, your description of the locking when writing is pretty much 
exactly what I'm sure is happening.

A couple clarifications though: I have only 20 or 30 threads on the server 
side. It's the client that has 100 or more threads. And I'm opening all 
connections to the DB when the server first starts up (one per server thread), 
not per connection.

My thinking was that, in the exact scenario you described, an in-memory 
database should in theory be faster. Because a thread that locks due to writing 
should finish its write and be 'done' much faster. In theory. I guess not in 
practice.

-Vern

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Stephen Chrzanowski
Sent: Thursday, August 07, 2014 5:30 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] In-memory DB slower than disk-based?

It "smells" like that you're running into general timing issues, especially if 
you're consistent with how long it takes to put info into the database.

If we were to take your scenario of 100 threads of writing to memory, bashing 
the hell out of the database for writes, the first thread is going to lock the 
database, the other 99 threads are going to get put on hold for a period of 
time.  The first thread is going to release, then *when* the waiting threads 
start making write requests, one is going to get the lock, the others are going 
to get put on hold again.  Since hardware is fast enough, you may find yourself 
in a situation where all 100 threads are actually counting down and waiting to 
write to the database.

If we start writing to the platter/ssd, you might actually see a shorter time 
out since the delays between the waiting periods become spread out.

I'm not sure if there is anything that broadcasts to other threads that writing 
can resume.  Transactions may also cause problems as well for timing since it 
can take a long time to write data out.  The other thing I just thought about 
is that if the database is locked while trying to open a new connection, you 
may run into a 1-2 second delay before you can start doing ANYTHING with the 
database since it is technically in a read-only state.  Are you opening/closing 
connections to the database at every iteration in every thread?  (Meaning you 
start the testing process, threads are launched to open a connection, you do 
your work, you close the connection, destroy the thread, then repeat as needed)


On Thu, Aug 7, 2014 at 2:53 PM, Jensen, Vern <vern.jen...@cshs.org> wrote:

> Real hardware. 27” iMac (native BootCamp running Win 7) with 3.4 GHz
> Intel Core i7 quad-core processor. And my mistake: 24 GB of RAM, not
> 20. Anytime I’ve looked at the app while it’s running, it’ll take 5 to
> 15 MB at most, depending on how many threads I give it.
>
> -Vern
>
> 
> Vern Jensen
> Software Engineer
> Artificial Intelligence in Medicine (AIM) Program
> jens...@cshs.org<mailto:jens...@cshs.org>
> Office: 310-423-8148 :: Fax: 310-423-0173
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
IMPORTANT WARNING: This message is intended for the use of the person or entity 
to which it is addressed and may contain information that is privileged and 
confidential, the disclosure of which is governed by applicable law. If the 
reader of this message is not the intended recipient, or the employee or agent 
responsible for delivering it to the intended recipient, you are hereby 
notified that any dissemination, distribution or copying of this information is 
STRICTLY PROHIBITED. If you have received this message in error, please notify 
us immediately by calling (310) 423-6428 and destroy the related message. Thank 
You for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In-memory DB slower than disk-based?

[Stephen Chrzanowski]

It "smells" like that you're running into general timing issues, especially
if you're consistent with how long it takes to put info into the database.

If we were to take your scenario of 100 threads of writing to memory,
bashing the hell out of the database for writes, the first thread is going
to lock the database, the other 99 threads are going to get put on hold for
a period of time.  The first thread is going to release, then *when* the
waiting threads start making write requests, one is going to get the lock,
the others are going to get put on hold again.  Since hardware is fast
enough, you may find yourself in a situation where all 100 threads are
actually counting down and waiting to write to the database.

If we start writing to the platter/ssd, you might actually see a shorter
time out since the delays between the waiting periods become spread out.

I'm not sure if there is anything that broadcasts to other threads that
writing can resume.  Transactions may also cause problems as well for
timing since it can take a long time to write data out.  The other thing I
just thought about is that if the database is locked while trying to open a
new connection, you may run into a 1-2 second delay before you can start
doing ANYTHING with the database since it is technically in a read-only
state.  Are you opening/closing connections to the database at every
iteration in every thread?  (Meaning you start the testing process, threads
are launched to open a connection, you do your work, you close the
connection, destroy the thread, then repeat as needed)


On Thu, Aug 7, 2014 at 2:53 PM, Jensen, Vern  wrote:

> Real hardware. 27” iMac (native BootCamp running Win 7) with 3.4 GHz Intel
> Core i7 quad-core processor. And my mistake: 24 GB of RAM, not 20. Anytime
> I’ve looked at the app while it’s running, it’ll take 5 to 15 MB at most,
> depending on how many threads I give it.
>
> -Vern
>
> 
> Vern Jensen
> Software Engineer
> Artificial Intelligence in Medicine (AIM) Program
> jens...@cshs.org
> Office: 310-423-8148 :: Fax: 310-423-0173
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In-memory DB slower than disk-based?

[Jensen, Vern]

Real hardware. 27” iMac (native BootCamp running Win 7) with 3.4 GHz Intel Core 
i7 quad-core processor. And my mistake: 24 GB of RAM, not 20. Anytime I’ve 
looked at the app while it’s running, it’ll take 5 to 15 MB at most, depending 
on how many threads I give it.

-Vern


Vern Jensen
Software Engineer
Artificial Intelligence in Medicine (AIM) Program
jens...@cshs.org
Office: 310-423-8148 :: Fax: 310-423-0173

On Aug 7, 2014, at 5:40 AM, Simon Slavin 
> wrote:


On 7 Aug 2014, at 3:47am, Jim Callahan 
> wrote:
[various questions]

In addition, is this real hardware, or is it a simulated or virtual machine ?

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

IMPORTANT WARNING: This message is intended for the use of the person or entity 
to which it is addressed and may contain information that is privileged and 
confidential, the disclosure of which is governed by applicable law. If the 
reader of this message is not the intended recipient, or the employee or agent 
responsible for delivering it to the intended recipient, you are hereby 
notified that any dissemination, distribution or copying of this information is 
STRICTLY PROHIBITED. If you have received this message in error, please notify 
us immediately by calling (310) 423-6428 and destroy the related message. Thank 
You for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In-memory DB slower than disk-based?

[Jensen, Vern]

Jim,

The primary table in the database has 5 columns, and has at most about 4,000 
entries during stress-testing. (Although in tests involving even only about 600 
entries, the results were the same.) There are a few other tables that would be 
much smaller. Nothing that should stress any resources at all. System RAM is 20 
GB.

-Vern


Vern Jensen
Software Engineer
Artificial Intelligence in Medicine (AIM) Program
jens...@cshs.org
Office: 310-423-8148 :: Fax: 310-423-0173

On Aug 6, 2014, at 7:47 PM, Jim Callahan 
> wrote:

What is the system memory utilization?
If the in-memory database overloads the real memory
the frantic os virtual memory paging (thrashing) could slow
everything down.

>From your use scenario it sounds like your database shouldn't be that large,
but how large is the database? and how large is the system ram?
What percent of the ram is being used?

Jim Callahan


IMPORTANT WARNING: This message is intended for the use of the person or entity 
to which it is addressed and may contain information that is privileged and 
confidential, the disclosure of which is governed by applicable law. If the 
reader of this message is not the intended recipient, or the employee or agent 
responsible for delivering it to the intended recipient, you are hereby 
notified that any dissemination, distribution or copying of this information is 
STRICTLY PROHIBITED. If you have received this message in error, please notify 
us immediately by calling (310) 423-6428 and destroy the related message. Thank 
You for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In-memory DB slower than disk-based?

[Simon Slavin]

On 7 Aug 2014, at 3:47am, Jim Callahan  wrote:
> [various questions]

In addition, is this real hardware, or is it a simulated or virtual machine ?

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

Re: [sqlite] In-memory DB slower than disk-based?

[Jim Callahan]

What is the system memory utilization?
If the in-memory database overloads the real memory
the frantic os virtual memory paging (thrashing) could slow
everything down.

>From your use scenario it sounds like your database shouldn't be that large,
but how large is the database? and how large is the system ram?
What percent of the ram is being used?

Jim Callahan


On Wed, Aug 6, 2014 at 3:39 PM, Fabian Giesen 
wrote:

> On 8/6/2014 11:51 AM, Jensen, Vern wrote:
>
>> *bump*
>>
>> Anyone?
>>
>
> Not sure if this is your problem, but this kind of load puts a lot of
> stress on SQLite's page cache, which might well perform worse than your
> OS'es page cache (used for "real" disk IO) does, especially under high
> contention.
>
> Also, SQLite's defaults wrt. threading are set up to be safe, which means
> that when in doubt about what your code might be doing, SQLite will
> serialize.
>
> I did a bit of SQLite perf tuning recently and here's two things you might
> want to try:
>
> 1. What's your value for "flags"? SQLite by default (pessimistically)
> assumes that you might be sharing a DB connection between multiple threads,
> and thus wraps essentially all API functions in locks to make sure this is
> safe. In a heavily multi-threaded scenario, I would recommend that you
> manually ensure that each connection is only used by one thread at a time,
> and then add SQLITE_OPEN_NOMUTEX to your flags. This reduces lock traffic
> substantially. (If your connection was per-thread already, doing so will
> not reduce contention or have any impact on your asymptotic perf or
> scalability, but locks aren't free.)
>
> 2. Before sqlite3_initialize, try
>
>   "sqlite3_config(SQLITE_CONFIG_MEMSTATUS, 0);"
>
> This disables some API functions that provide access to memory stats, but
> avoids a global lock on every memory allocation/free, and SQLite does a lot
> of them. If you don't need these memory statistics, turning this off can
> make a noticeable difference in scalability.
>
> I'm not sure if this will affect page cache throughput specifically, but
> these two things are both fairly easy to try.
>
> -Fabian
>
> ___
> 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] In-memory DB slower than disk-based?

[Simon Slavin]

On 6 Aug 2014, at 11:00pm, big stone  wrote:

> I too noticed disappointing improvements by going ":memory:".
> But, it seems also very dependent of the compilation options and the
> version of SQLite you use.
> 
> sometimes, gain is roughly 0%
> sometimes, gain can be 40%.

Pagesize for the disk version can make a surprisingly big difference.  There's 
not that much you can do about it since the majority of influences on SQLite 
speed are from things you have no control over, like the storage driver's cache 
strategy, and the OS's process scheduling.

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

Re: [sqlite] In-memory DB slower than disk-based?

[big stone]

Hi,

I too noticed disappointing improvements by going ":memory:".
But, it seems also very dependent of the compilation options and the
version of SQLite you use.

sometimes, gain is roughly 0%
sometimes, gain can be 40%.

==> You may try with a more recent version of SQLite than 3.7.15.2.


https://raw.githubusercontent.com/stonebig/ztest_donotuse/master/benchmark_test01_measures.GIF
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In-memory DB slower than disk-based?

[Jensen, Vern]

Thanks for the replies, Fabian and Richard.

It’s definitely ‘good enough’ performance as-is, I mainly wanted to see if I 
was doing something clearly wrong, or if my results were surprising. Given that 
they’re not, I’m happy to move on. Thanks again,

-Vern
IMPORTANT WARNING: This message is intended for the use of the person or entity 
to which it is addressed and may contain information that is privileged and 
confidential, the disclosure of which is governed by applicable law. If the 
reader of this message is not the intended recipient, or the employee or agent 
responsible for delivering it to the intended recipient, you are hereby 
notified that any dissemination, distribution or copying of this information is 
STRICTLY PROHIBITED. If you have received this message in error, please notify 
us immediately by calling (310) 423-6428 and destroy the related message. Thank 
You for your cooperation.

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

Re: [sqlite] In-memory DB slower than disk-based?

[Richard Hipp]

On Wed, Aug 6, 2014 at 2:51 PM, Jensen, Vern  wrote:

> *bump*
>
> Anyone?
>

I don't think anybody has any information for you Vern.  So much depends on
the details of your setup that it is hard to speculate what might be going
wrong.

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

Re: [sqlite] In-memory DB slower than disk-based?

[Fabian Giesen]

On 8/6/2014 11:51 AM, Jensen, Vern wrote:

*bump*

Anyone?


Not sure if this is your problem, but this kind of load puts a lot of 
stress on SQLite's page cache, which might well perform worse than your 
OS'es page cache (used for "real" disk IO) does, especially under high 
contention.


Also, SQLite's defaults wrt. threading are set up to be safe, which 
means that when in doubt about what your code might be doing, SQLite 
will serialize.


I did a bit of SQLite perf tuning recently and here's two things you 
might want to try:


1. What's your value for "flags"? SQLite by default (pessimistically) 
assumes that you might be sharing a DB connection between multiple 
threads, and thus wraps essentially all API functions in locks to make 
sure this is safe. In a heavily multi-threaded scenario, I would 
recommend that you manually ensure that each connection is only used by 
one thread at a time, and then add SQLITE_OPEN_NOMUTEX to your flags. 
This reduces lock traffic substantially. (If your connection was 
per-thread already, doing so will not reduce contention or have any 
impact on your asymptotic perf or scalability, but locks aren't free.)


2. Before sqlite3_initialize, try

  "sqlite3_config(SQLITE_CONFIG_MEMSTATUS, 0);"

This disables some API functions that provide access to memory stats, 
but avoids a global lock on every memory allocation/free, and SQLite 
does a lot of them. If you don't need these memory statistics, turning 
this off can make a noticeable difference in scalability.


I'm not sure if this will affect page cache throughput specifically, but 
these two things are both fairly easy to try.


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

Re: [sqlite] In-memory DB slower than disk-based?

[Jensen, Vern]

*bump*

Anyone?

-Vern


Vern Jensen
Software Engineer
Artificial Intelligence in Medicine (AIM) Program
jens...@cshs.org
Office: 310-423-8148 :: Fax: 310-423-0173

On Aug 4, 2014, at 12:02 PM, Jensen, Vern 
> wrote:

Hey all! We use SQLite (3.7.15.2) as the backend for a server that provides 
floating licenses for our software. I've recently written a stress-testing 
framework that starts up any number of threads, and hits the server with 
multiple requests per thread.
While being stress-tested in this way, I've found that if the testing framework 
is using 100 threads, a single call to sqlite3_exec() can take 8-15 seconds at 
times (assuming my timeout set with sqlite3_busy_timeout is big enough to allow 
this, otherwise is failed with error code 5 -- LOCKED). This is when doing DB 
writes, obviously. journal_mode is set to WAL.

I assumed that by switching from a DB file on disk to one in memory, we'd see 
this 'write' bottleneck nearly disappear. We don't have too many records in the 
tables being written to, so the only reasonable explanation for 8-15 seconds 
for some writes would be that the DB is on disk.

Yet when switching to an in-memory database (with a shared cache, since 
currently the server has 30 separate threads, each with their own separate 
SQLite connection), I found the performance actually got slightly *worse* than 
with a disk-based version. I re-ran the tests each way several times, and 
indeed, performance is worse.
I open the shared-cache in-memory DB like this:

int result = sqlite3_open_v2("file:memdb1?mode=memory=shared", &_db, 
flags, NULL);

I also tried using these for the in-memory version (versus WAL journaling mode):

sqlite3_exec(_db, "PRAGMA read_uncommitted=true", NULL, NULL, NULL);
sqlite3_exec(_db,"PRAGMA journal_mode=OFF",NULL,NULL,NULL);

but to no avail. Still worse performance than when the DB is on disk. Which 
makes little sense to me.

I realize SQLite was not designed to be accessed concurrently by 100 separate 
users, and this really is a stress-testing case that far exceeds our expected 
real-world usage. But at the same time, I'd like to get this running as 
optimally as possible, and it seems like 8-15 second writes is unreasonable for 
an in-memory database. (Not that 8-15 seconds is the average... but even the 
average is 3-5 seconds when 100 threads are stress-testing the server at once.)

-Vern
IMPORTANT WARNING: This message is intended for the use of the person or entity 
to which it is addressed and may contain information that is privileged and 
confidential, the disclosure of which is governed by applicable law. If the 
reader of this message is not the intended recipient, or the employee or agent 
responsible for delivering it to the intended recipient, you are hereby 
notified that any dissemination, distribution or copying of this information is 
STRICTLY PROHIBITED. If you have received this message in error, please notify 
us immediately by calling (310) 423-6428 and destroy the related message. Thank 
You for your cooperation.

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

IMPORTANT WARNING: This message is intended for the use of the person or entity 
to which it is addressed and may contain information that is privileged and 
confidential, the disclosure of which is governed by applicable law. If the 
reader of this message is not the intended recipient, or the employee or agent 
responsible for delivering it to the intended recipient, you are hereby 
notified that any dissemination, distribution or copying of this information is 
STRICTLY PROHIBITED. If you have received this message in error, please notify 
us immediately by calling (310) 423-6428 and destroy the related message. Thank 
You for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] In-memory DB slower than disk-based?

[Jensen, Vern]

Hey all! We use SQLite (3.7.15.2) as the backend for a server that provides 
floating licenses for our software. I've recently written a stress-testing 
framework that starts up any number of threads, and hits the server with 
multiple requests per thread.
While being stress-tested in this way, I've found that if the testing framework 
is using 100 threads, a single call to sqlite3_exec() can take 8-15 seconds at 
times (assuming my timeout set with sqlite3_busy_timeout is big enough to allow 
this, otherwise is failed with error code 5 -- LOCKED). This is when doing DB 
writes, obviously. journal_mode is set to WAL.

I assumed that by switching from a DB file on disk to one in memory, we'd see 
this 'write' bottleneck nearly disappear. We don't have too many records in the 
tables being written to, so the only reasonable explanation for 8-15 seconds 
for some writes would be that the DB is on disk.

Yet when switching to an in-memory database (with a shared cache, since 
currently the server has 30 separate threads, each with their own separate 
SQLite connection), I found the performance actually got slightly *worse* than 
with a disk-based version. I re-ran the tests each way several times, and 
indeed, performance is worse.
I open the shared-cache in-memory DB like this:

int result = sqlite3_open_v2("file:memdb1?mode=memory=shared", &_db, 
flags, NULL);

I also tried using these for the in-memory version (versus WAL journaling mode):

sqlite3_exec(_db, "PRAGMA read_uncommitted=true", NULL, NULL, NULL);
sqlite3_exec(_db,"PRAGMA journal_mode=OFF",NULL,NULL,NULL);

but to no avail. Still worse performance than when the DB is on disk. Which 
makes little sense to me.

I realize SQLite was not designed to be accessed concurrently by 100 separate 
users, and this really is a stress-testing case that far exceeds our expected 
real-world usage. But at the same time, I'd like to get this running as 
optimally as possible, and it seems like 8-15 second writes is unreasonable for 
an in-memory database. (Not that 8-15 seconds is the average... but even the 
average is 3-5 seconds when 100 threads are stress-testing the server at once.)

-Vern
IMPORTANT WARNING: This message is intended for the use of the person or entity 
to which it is addressed and may contain information that is privileged and 
confidential, the disclosure of which is governed by applicable law. If the 
reader of this message is not the intended recipient, or the employee or agent 
responsible for delivering it to the intended recipient, you are hereby 
notified that any dissemination, distribution or copying of this information is 
STRICTLY PROHIBITED. If you have received this message in error, please notify 
us immediately by calling (310) 423-6428 and destroy the related message. Thank 
You for your cooperation.

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