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
