Re: AW: [sqlite] Memory mapped db
Trevor Talbot wrote: Michael is referring to a direct map from disk pages to memory pages (OS notion of pages, not sqlite's), using something like mmap() on unix or MapViewOfFile() on Windows. This way memory is directly backed by the file it refers to, instead of copying the data to entirely new pages (possibly backed by swap). It removes one level of (redundant) cache. The complications tend to come in when considering I/O control and locking. OS pages don't necessarily map to sqlite pages, so there can be some "odd" boundaries there. This would be most noticable when trying to flush data to disk. (The typical mmap abstraction requires you force dirty OS pages to disk. Interactions between file maps (often copy-on-write) and underlying OS caches can be weird.). You're also bounded by VM space when trying to map large files. Most mapping abstractions use "windows" intended to map several sequential OS pages, and since sqlite randomly accesses pages, it would probably be too much overhead when trying to handle files larger than the VM space you're willing to sacrifice to the map. In the general case I don't see it paying off, but in some specific cases it could be a win. I'd be interested to see experiments. - To unsubscribe, send email to [EMAIL PROTECTED] - A well reasoned explanation. We use mmap'ing with great success, but not for very large files or databases, for the reasons described above. You definitely get a performance lift with mmap'd I/O largely because a level of buffer shadowing is removed. On a B-Tree index access I measured a speed improvement of about 40% by changing from reads and local cacheing to mmap'd access. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: AW: [sqlite] Memory mapped db
Michael is referring to a direct map from disk pages to memory pages (OS notion of pages, not sqlite's), using something like mmap() on unix or MapViewOfFile() on Windows. This way memory is directly backed by the file it refers to, instead of copying the data to entirely new pages (possibly backed by swap). It removes one level of (redundant) cache. The complications tend to come in when considering I/O control and locking. OS pages don't necessarily map to sqlite pages, so there can be some "odd" boundaries there. This would be most noticable when trying to flush data to disk. (The typical mmap abstraction requires you force dirty OS pages to disk. Interactions between file maps (often copy-on-write) and underlying OS caches can be weird.). You're also bounded by VM space when trying to map large files. Most mapping abstractions use "windows" intended to map several sequential OS pages, and since sqlite randomly accesses pages, it would probably be too much overhead when trying to handle files larger than the VM space you're willing to sacrifice to the map. In the general case I don't see it paying off, but in some specific cases it could be a win. I'd be interested to see experiments. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: AW: [sqlite] Memory mapped db
On Thu, 28 Sep 2006 15:45:54 +0200, you wrote: Hi Michael >-Ursprüngliche Nachricht- >Von: Jay Sprenkle [mailto:[EMAIL PROTECTED] >Gesendet: Donnerstag, 28. September 2006 15:37 >An: sqlite-users@sqlite.org >Betreff: Re: [sqlite] Memory mapped db >That's not really the same. I would have to copy the db into ram after >opening it and since the db is too big to fit into the memory I would have >to recreate it dependend on my selects. >If sqlite maps the db into memory, the operating system manages the mapping, >sqlite "just" has to move it's view over the file. I'm working with some "small" SQLite databases, with less than 150.000 Records (Extractions of a bigger CS-DB). The records a stored in several Tables. One Table with 4000-5000 Records are bigger, the rest up to limit are smaller. I'm using SQlite to do some quickly jobs with the data on a local machine. I read and load the whole Result, opened by a SQL-Query, as a virtual table into a RAM's Vector-List. If this done, I can read Record by Record, forward, backward, skip any direction, alter, append new record, delete records. I can all do, what I want It seems to me, I do that, who you ask. But... I don't know, which developer-System you are using. I am working with VC++ .Net 2003. If that wrong to you, excuse the disturbance ;-) Is it OK, spend some time at my HP. Best Regards Thomas www.thlu.de - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Memory mapped db
On Thu, 28 Sep 2006 15:32:03 +0200, you wrote: > >Has anyone tested an sqlite which memory-maps the db-file into ram? Is this >an old (maybe bad idea :-) ? I've looked over the source and it seems that >read and write operations are used through a singled interface, so it maybe >possible to implement it without too much trouble... > >Any comments are welcome :-) > > Michael SQLite already does that, it is called caching, and you can set the both the page size and the number of pages in cache with the PRAGMA statement. http://www.sqlite.org/pragma.html#modify Read about SQLite internals for a more detailed explanation. http://www.sqlite.org/arch.html The page cache can be shared by mutiple daatabase connections: http://www.sqlite.org/sharedcache.html -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Memory mapped db
Michael Wohlwend wrote: Has anyone tested an sqlite which memory-maps the db-file into ram? Is this an old (maybe bad idea :-) ? I've looked over the source and it seems that read and write operations are used through a singled interface, so it maybe possible to implement it without too much trouble... Any comments are welcome :-) Michael It is not a bad idea, but would impose some limitations. In general memory mapping removes one layer of buffer shadowing and is beneficial. - To unsubscribe, send email to [EMAIL PROTECTED] -
AW: [sqlite] Memory mapped db
-Ursprüngliche Nachricht- Von: Jay Sprenkle [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 28. September 2006 15:37 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Memory mapped db >use the database named:memory: >for a ram database. In a lot of cases it will be cached by >the operating system so it ends up being that way anyway! That's not really the same. I would have to copy the db into ram after opening it and since the db is too big to fit into the memory I would have to recreate it dependend on my selects. If sqlite maps the db into memory, the operating system manages the mapping, sqlite "just" has to move it's view over the file. Michael
Re: [sqlite] Memory mapped db
use the database named:memory: for a ram database. In a lot of cases it will be cached by the operating system so it ends up being that way anyway! On 9/28/06, Michael Wohlwend <[EMAIL PROTECTED]> wrote: Has anyone tested an sqlite which memory-maps the db-file into ram? Is this an old (maybe bad idea :-) ? I've looked over the source and it seems that read and write operations are used through a singled interface, so it maybe possible to implement it without too much trouble... -- SqliteImporter and SqliteReplicator: Command line utilities for Sqlite http://www.reddawn.net/~jsprenkl/Sqlite Cthulhu Bucks! http://www.cthulhubucks.com - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Memory mapped db
Has anyone tested an sqlite which memory-maps the db-file into ram? Is this an old (maybe bad idea :-) ? I've looked over the source and it seems that read and write operations are used through a singled interface, so it maybe possible to implement it without too much trouble... Any comments are welcome :-) Michael