It takes time (cycles) to do the mutex operations and time (cycles) to perform I/O. These actions are serialized -- only one can happen at a time. So, if you use a single thread, then your "application thread" only progresses when it is not blocked waiting for these operations to complete. For example, when sqlite needs to read a page from disk, your application stops until the OS completes the read.
If you have multiple threads, then when the OS has blocked progress on one thread because it is waiting for a disk read to complete (which takes an eon in comparison to straight CPU operations), other threads that are ready-to-run will run. These types of blocking operations can occur at non-obvious places -- when allocating memory and diddling the MMU page map, any type of I/O operation, or anything else that is carried out by hardware rather than by continuous stream of instructions executed on the CPU. The same applies to multiple processes (heavy threads), except that in that case you also introduce overhead of context switching since each process is executing within its own virtual machine (Windows, for example, runs each process in a separate virtual machine (they call it a memory space though it is more akin to a virual machine) and the Operating System itself is mapped into the virtual machine address space through a Discontiguous Saved Segment -- quite similar to CP/CMS under VM/370). The concurrency limit is therefore lower with processes than threads, but only because PC's do not have the necessary VM Assist hardware to perform context switching independent of the main CPU. The big difference between PC hardware and a Mainframe is in the opportunity for processor offloading. In a PC, almost everything executes on the "main" (user) CPU, and very little is carried out in independent hardware. For example, old ATA and earlier disks I/O was carried out entirely by the main CPU, so I/O provided no opportunity for concurrency. SCSI (and modern equivalents such as SATA) provide hardware offloading of operations so that they occur without CPU supervision. Since the CPU is no longer tied up waiting for the eon of time it takes to read a disk sector, it can process other useful work during this time. This is where you find that "multiple threads" progress faster than a "single thread". CPU cycles that would otherwise be unused (or consumed spinning waiting for a completion notification) are used to progress useful work. This is especially true if you have "user I/O" (click a mouse, type, etc). If you take a PC, install co-processed I/O, and add CPU's to handle "supervisor" and "I/O" co-ordination leaving the "main" CPU to run only ready-to-run straight "user" processing, you will have a mainframe. In the ancient days you used to be able to buy a 370-on-a-card. You plugged it into the PC and it turned the PC into a mainframe. The card contained a mainframe (370) "main" (user) processor, and the rest of the PC was turned into the "supervisor", "I/O" and "VM Assist" hardware. Modern HPC (high-performance computing) and mainframe systems work the same way. The CPU that does the meaningful application processing is dedicated to only that task, and everything else is handed off to separate processors and coprocessed hardware. Most HPC platorms on commodity PC-type hardware add extra CPU's to dedicate to "main" processing, and turn the PC hardware into the support system to allow this additional CPU to actually run unrestrained. In typical PC systems the CPU never runs more than about 50% duty even when 100% utilized simply because there is too much administrative crap going on to allow it to run at full speed except in very short bursts. If PC systems were designed with enough cache (L1 & L2) and hardware coprocessing to allow the CPU to run with a 100% duty cycle, they would burst into flames. Modern CPU and PC design requires massively inefficient design in order to work. It is interesting that the faster the CPU's peak processing capability, the lower the duty cycle. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Sebastian Krysmanski > Sent: Friday, 21 September, 2012 01:32 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Store error messages in thread local memory > > Ok - could you elaborate on the first "this is what one would expect". What > difference does it make whether I use two threads or 20 threads with one > connection when all operations are serialized? Shouldn't both cases have the > same throughput? > > > On Friday, 21. September 2012 at 03:52, Keith Medcalf wrote: > > > > With two threads, using only one connection (87.8 s) is actually slower > than > > > using two connections (66.7 s). The performance of using only one > connection > > > drastically increased until a thread count somewhere between 10 and 20 > where > > > it settles at about 11 seconds. Using one connection per thread reduces > the > > > elapsed time only from 66 to 55 seconds. > > > > > > > > > This is what one would expect. > > > > > * The virtual machine has 2 CPU cores assigned. When using only one > > > connection, only one core seems to be used. When using one connection per > > > thread, both cores are used. > > > > > > > > > This is also to be expected. > > > > Because thread access to the sqlite engine is serialized by a mutex, the OS > ought to set thread affinity for all operations running through the same > mutex to the same CPU. If you repeated the test using two connections only, > and spreading your threads amongst connections, you ought to be able to get > about 5 or 6 seconds with 10 to 20 threads per connection. > > > > > > > > > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org (mailto: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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users