Adding to my own comment, the mechanical properties of the disc. If you're reading from a solid state device this does not apply, however with magnetic spinning discs there's a read head that has to move around and if you're reading from four different locations in the same file and the files big enough, then you'll be generating a lot of motion with the read head. Your OS will attempt to collect the reads into as little motion as possible, however you also have to wait for the sector the come under the head once the read head is in position.
If throughput is the concern and not latency, then I would suggest you queue all of your queries, otherwise you'll just have to except that with reduced latency there is reduced performance. -----Original message----- Sent: Tuesday, 03 November 2015 at 09:28:33 From: [email protected] To: "Stephen Chrzanowski" <pontiac76 at gmail.com>,"SQLite mailing list" <sqlite-users at mailinglists.sqlite.org> Subject: Re: [sqlite] Why SQLite take lower performanceinmulti-threadSELECTing? To add to what other people are saying here, it is very likely in the additional threads result in reduced overall performance because rather than having one thread have the areas of disk cache where it's reading, you know have multiple threads each at their own locations in the file such that your disk cache is divided among all of your threads so you actually increase resource contention. As a result your performance is not just divided by the number of friends but is lower than total performance / number of threads. It is also true that in most cases the general optimization of read ahead caches at the OS level is exactly the opposite behavior was desired for a database. When you're doing table stands having A most recently used buffer until it fills the buffers with useless data. Ideally you would one day most recent in/first out buffer. -----Original message----- Sent: Monday, 02 November 2015 at 05:48:47 From: "Stephen Chrzanowski" <[email protected]> To: "SQLite mailing list" <sqlite-users at mailinglists.sqlite.org> Subject: Re: [sqlite] Why SQLite take lower performanceinmulti-threadSELECTing? @sanhua> If you can, just for testing, use the SQLite backup mechanism to copy the database to memory, then run your transactions off the memory version. This will get rid of disk IO with the exclusion of the OS swapping to memory. This will tell you if you're running into some kind of thread locking or if you're running into a disk IO issue. I have absolutely no clue about the back end of iOS (Other than it is linux based) so I don't know what it offers for threaded operations, or if the library you're using has threaded capabilities. On Sun, Nov 1, 2015 at 11:25 PM, Simon Slavin <slavins at bigfraud.org> wrote: > > On 2 Nov 2015, at 3:48am, sanhua.zh <sanhua.zh at foxmail.com> wrote: > > > I thought it might be storage contention, too. > > BUT, as the documentation of SQLite said, in ?DELETE? mode, SELECTing do > read for disk only. > > Even reading needs the attention of the disk. You tell the disk what you > want to read and it has to find that piece of disk, read it, and give the > result to you. It cannot answer four requests at once so while it's > answering one, the other threads have to wait. > > (Yes disk is cached. But that just moves the problem from the hard disk > to the piece of software which handles the cache. It cannot answer four > questions at once.) > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
