Improvements in the upcoming sqlite 3.7.5

January 20th, 2011 | 3 Comments »

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.

3 Responses to “Improvements in the upcoming sqlite 3.7.5”

  • scott says:

    Dr Hipp is awesome. if you ever get a chance to listen to him talk, please do. I heard him talk at Atlanta Perl Mongers about databases (not just SQLite, but all databases) and it was very informative and he really really knows his stuff.

  • marco says:

    Oh yes sure Scott I’ll do. I talked to him by phone and he is really a very kind person too.

  • Benoit says:

    “Read collisions are now completely avoided (…)
    the upcoming server is now much more faster.”

    It’s a very good news !

Leave a Reply

This field is required

This field is required