> 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
