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] -
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