Hi, > Such application needs custom VFS designed specifically for its needs I Believe that a good library should be as robust as possible, and SQLite is very good at being robust. This is the reason you can find SQLite almost anywhere, from mobile devices, desktop applications and web sites. But not all of them have the same requirements, and SQLite should give its users the options to configure it differently. Some users will choose to use the traditional way, and some will choose to boost performance, and will pay the price of the ability to connect from multiple processes. Currently SQLite have good support for single process (single thread) applications, multiprocess applications, but does not have good enough support for single process multithreaded applications, as SQLite can perform better if use in memory index for WAL. Maybe we should add another option: PRAGMA locking_mode=proc_exclusive, which means exclusive for the same process.
> Of course such system will corrupt database immediately if > application exits/crashes with non-empty background queue I think another solution I suggested to a problem of WAL file size, will give you the good of all worlds (but with price). There is a solution which will be good for ALL WAL users (cyclic WAL file), and a simpler solution (keep last transaction in memory - this combine WAL and async), that is good for some applications (But will never corrupt the DB), and FMHO should be configurable, so if you use it, you know the limitations and accept them. Bottom line: Give the user an option. Yoni. On 10/12/2010 5:32 PM, Pavel Ivanov wrote: >> Perhaps using async VFS mode would better suit Yoni's application? >> >> http://www.sqlite.org/asyncvfs.html > > From my experience asyncVFS is not suitable for applications with high > throughput expecting high performance, because with big load and big > writeback queue asyncVFS consumes a lot of CPU for each reading from > file (it scans through the whole queue on each request to read, lock > or unlock database file) which I guess generally slows down each query > significantly (apart from causing a big CPU load). > > Such application needs custom VFS designed specifically for its needs > (e.g. you can eliminate actual locking/unlocking of database file - it > gives pretty significant benefit but again at the price of never be > able to connect to database while your application is running). Also > this custom VFS can be coupled with custom PCache to get some > additional perks: e.g. VFS can schedule every write to background > thread and tell PCache that whatever SQLite says it shouldn't evict > this page until it's written to disk. This way you'll be able to write > everything in background without causing additional pressure on > queries - all pages they need are either in the cache or were not > changed recently and are not in VFS background queue. Of course such > system will corrupt database immediately if application exits/crashes > with non-empty background queue. Also such system has danger of going > out of memory in case of too big throughput, so it needs to have > additional guards for that. > > > Pavel > > On Fri, Dec 10, 2010 at 10:05 AM, Christian Smith > <csm...@thewrongchristian.org.uk> wrote: >> On Fri, Dec 10, 2010 at 09:49:46AM -0500, Pavel Ivanov wrote: >>>> Given that the WAL index is mmap'ed, you're unlikely to see improvement >>>> in performance by storing it in heap memory. Reads/writes will go at >>>> main memory speeds once mapped into your address space, and under memory >>>> pressure, it will be no slower than if the heap was pushed to the swapfile. >>> >>> Still I think pushing actual memory to swap file has bigger memory >>> pressure threshold than pushing cache pages that are backed by actual >>> file data out of physical memory. Also writing to mmaped file will >>> still force OS to write it to disk from time to time and that brings >>> additional pressure on the system overall. >>> >> >> Once you're pushing working memory to disk, you've basically lost the >> performance battle either way. >> >> Given the OP problem, it doesn't sound like memory is the limiting >> factor anyway. >> >> From the past posts, it appears Yoni is after predictable performance >> with high throughput (logging system?) but without the durability >> gaurantees provided by SQLite by default. >> >> Perhaps using async VFS mode would better suit Yoni's application? >> >> http://www.sqlite.org/asyncvfs.html >> >> This way, the foreground thread handles writes to the SQLite IO queue, >> and the background SQLite IO thread handles any latencies that result >> from the commits. Yoni's already mentioned in other threads that >> durability is not the main priority. >> >> I'm not sure how this async VFS fits in with WAL. Might be that normal >> rollback journalling only is supported, but from a performance >> standpoint, that's probably not a problem. >> >> Christian >> _______________________________________________ >> 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
