A couple of weeks ago we reported an issue to Dr. Hipp about sqlite 3.7.4, the problem we experiencing with the upcoming server was that when there are some concurrent readers (even two or three) over the same table at the same time and the query is a complicated one (a query that required a lot of reads inside the table in order to be able to determine a row that satisfy the SELECT statement) then the time increase exponentially and most of the time the server just hangs.

We analyzed the problem and we discovered that most of the CPU time was spent in System space and not in User space and all the threads seemed blocked inside a semaphore_wait_signal_trap function inside the pcache module.

Dr. Hipp and the sqlite team analyzed the issue and his reply was “As best I can tell, the three threads seem to be colliding in the page-cache module (pcache1.c).  The default page-cache is designed for use on low-memory cellphones and gadgets.  A common poll of available pages it kept and a mutex is required to manage that pool.  That is the design that works best if your goal is to minimize the amount of memory used.  But this seems suboptimal on a workstation with lots of memory.  On a workstation it seems like giving each connection its own pool so that it does not have to mutex in order to fetch or release a page might be a big win.”

Few hours later I received an experimental code to test and things was a lot better as you can see from the following CPU time graph where most of the time is now spent in User space instead of in System space:

It was just a preliminary test code and yesterday I received the official code that will be part of the upcoming sqlite 3.7.5 with the following advice: “Mutexes are avoided as long as you omit SQLITE_ENABLE_MEMORY_MANAGEMENT. If you do include SQLITE_ENABLE_MEMORY_MANAGEMENT then the mutexes will be engaged again.”

I re-run my tests and I was happily surprised when I watched at the CPU time graph:

Read collisions are now completely avoided and all the bottleneck has been eliminated by design now… queries are now much more fasters and CPU time is all spent in User space. Very good job sqlite team, the upcoming server is now much more faster.

Tags: ,