> Just by having 2 programs access 2 > different files on the same disk give that much of a performance/scaling > gain causes me to believe that there exists a vital area of code that is > mutexed that is the bottleneck.
I hope you are talking about your OS kernel, because SQLite won't ever care whether 2 different files reside on a same hdd or on different ones. In both cases it executes the same code. > So I'll ask the question. Is there any compile time or pragma's engineered > towards read only performance as locking a table is no longer needed. To > save mutexing in cache we would also be ok if each thread would have it's > cache so it doesn't have to obtain a lock going into it. With default settings each connection has its own cache, they have very little interference with each other. And even if you have some custom settings different files have different caches anyway. Regarding optimization of read-only access: the problem is not that you only reading a file and don't need locks, the problem is to prevent any other process that could write into the same file from doing that while your reading transaction is active. So no, SQLite doesn't have any settings or pragmas to optimize such access. That said if you are absolutely sure that no other process will write to the database while you are reading you can create your own VFS and make xAccess and xCheckReservedLock methods a no-op. You can read more about VFS starting from here http://www.sqlite.org/c3ref/vfs_find.html (follow the links to read all related info). Pavel On Mon, Aug 15, 2011 at 9:00 AM, Drew Kozicki <drewkozi...@gmail.com> wrote: > The bottleneck appears to be mutex's locking access to the file and cache > inside the same program. I'm guessing that the reason why 2 programs on 2 > files on 2 hdd's did not perform as well as the one too me suggests that > both hdd's do not have the same performance and that it is not access to the > physical disk that is the problem. Just by having 2 programs access 2 > different files on the same disk give that much of a performance/scaling > gain causes me to believe that there exists a vital area of code that is > mutexed that is the bottleneck. > > The source code, unfortunately, I can't post as it is the property of the > company I work for. > > So I'll ask the question. Is there any compile time or pragma's engineered > towards read only performance as locking a table is no longer needed. To > save mutexing in cache we would also be ok if each thread would have it's > cache so it doesn't have to obtain a lock going into it. > > Thank you all once again for your help, > > Drew > > Message: 17 > Date: Fri, 12 Aug 2011 19:28:33 +0100 > From: Simon Slavin <slav...@bigfraud.org> > Subject: Re: [sqlite] Read only scaling optimization > To: General Discussion of SQLite Database <sqlite-users@sqlite.org> > Message-ID: <b4050f5b-3edb-4b79-a58c-3788d971c...@bigfraud.org> > Content-Type: text/plain; charset=us-ascii > > > On 12 Aug 2011, at 7:01pm, Drew Kozicki wrote: > >> I have a Driver doing this pulling in 32 queries aimed at randomness and >> different tables, much like that would be experienced in typical usage. > Best >> performance comes from having 2 separate programs running on 2 separate >> files. > > I'm no expert, but that suggests to me that your bottleneck is access to the > physical file on disk. So your greatest speed increases will come not from > more threads but from a very fast hard disk drive, lots of hard drive > caching, etc.. > > That's a great set of benchmarks, by the way. > > Simon. > > ------------------------------ > > Message: 18 > Date: Fri, 12 Aug 2011 14:35:29 -0400 > From: Pavel Ivanov <paiva...@gmail.com> > Subject: Re: [sqlite] Read only scaling optimization > To: General Discussion of SQLite Database <sqlite-users@sqlite.org> > Message-ID: > <cag1a4rt+pkj2ingy0jeym6h2vfikownlnx1bqgn-9hydput...@mail.gmail.com> > Content-Type: text/plain; charset=ISO-8859-1 > >>> I have a Driver doing this pulling in 32 queries aimed at randomness and >>> different tables, much like that would be experienced in typical usage. > Best >>> performance comes from having 2 separate programs running on 2 separate >>> files. >> >> I'm no expert, but that suggests to me that your bottleneck is access to > the physical file on disk. ?So your greatest speed increases will come not > from more threads but from a very fast hard disk drive, lots of hard drive > caching, etc.. > > It's a little surprising to me that with all the same conditions 2 > files residing on the same drive have better performance than the same > files residing on different drives. Theoretically that shouldn't > happen. > > > Pavel > > > Message: 2 > Date: Sun, 14 Aug 2011 17:53:26 +0400 > From: Alexey Pechnikov <pechni...@mobigroup.ru> > Subject: Re: [sqlite] Read only scaling optimization > To: General Discussion of SQLite Database <sqlite-users@sqlite.org> > Message-ID: > <CANMYFJ=7F87=f415337O+ > HuywUmjZtNfNL-V5m8Un=rg3wk...@mail.gmail.com> > Content-Type: text/plain; charset=ISO-8859-1 > > 2011/8/12 Pavel Ivanov <paiva...@gmail.com>: >> It's a little surprising to me that with all the same conditions 2 >> files residing on the same drive have better performance than the same >> files residing on different drives. Theoretically that shouldn't >> happen. > > Yes, it's not right behaviour. Is needed the sources of the test programm. > > -- > Best regards, Alexey Pechnikov. > http://pechnikov.tel/ > _______________________________________________ > 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
