Re: [sqlite] creating a sqlite db on raw disk ?
On Fri, Nov 19, 2010 at 10:09 AM, Jay A. Kreibich wrote: > On Thu, Nov 18, 2010 at 11:04:02PM -0800, Yang scratched on the wall: > > > innodb allows creating a db on a raw disk partition, can we do the > > same on sqlite? > > Not out of the box, but it could be done by writing a VFS driver. > It is an idea I've toyed with, but I don't really have the low-level > raw device skills, nor enough motivation to learn. It would be an > interesting project, however. > Much of the work has already been done. See http://www.sqlite.org/src/artifact?name=40cf9e212a377a6511469384a64b01e6e34b2eec > > -j > > -- > Jay A. Kreibich < J A Y @ K R E I B I.C H > > > "Intelligence is like underwear: it is important that you have it, > but showing it to the wrong people has the tendency to make them > feel uncomfortable." -- Angela Johnson > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] creating a sqlite db on raw disk ?
On Thu, Nov 18, 2010 at 11:04:02PM -0800, Yang scratched on the wall: > innodb allows creating a db on a raw disk partition, can we do the > same on sqlite? Not out of the box, but it could be done by writing a VFS driver. It is an idea I've toyed with, but I don't really have the low-level raw device skills, nor enough motivation to learn. It would be an interesting project, however. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] creating a sqlite db on raw disk ?
Thanks for your detailed explanation. for your first question: I mean creating a new database file. since you asserted " >> innodb allows creating a db on a raw disk partition, can we do the >> same on sqlite? > > No. You need a file system of some kind. Or, at least, your operating > system needs to be able to address your storage using file-system calls, not > storage-structure calls. ", that answers my question. On Fri, Nov 19, 2010 at 5:38 AM, Simon Slavin wrote: > > On 19 Nov 2010, at 7:04am, Yang wrote: > >> when I create a db on a file system, I guess a query process > > Wait ... are you talking here about creating a new database file or querying > one what already exists ? > >> has to go >> through 2 levels of seeks ? >> first sqlite finds the B-tree node that stores the index to the file >> offset of my desired record, then sqlite uses that offset to make >> syscall seek(offset), >> then Kernel consults the FS implementation to find from its OWN >> B-tree (for example in ext3 fs ) the block location of that offset. > > Assuming that you're using a FS that uses B-trees, nodes, and indices (many > don't), that's a fair summary of what happens. But SQLite runs fine on many > embedded systems that use linked lists instead of B-trees, or don't use > nodes, or have databases stored in non-writable sequential memory. > >> innodb allows creating a db on a raw disk partition, can we do the >> same on sqlite? > > No. You need a file system of some kind. Or, at least, your operating > system needs to be able to address your storage using file-system calls, not > storage-structure calls. > >> I tried directly creating a db on ramdisk, failed: >> >> javasqlite-20100727# sqlite3 /dev/ram0 > > You didn't tell it what to call the database, just where you wanted it. I > think you want something like > > # sqlite3 /dev/ram0/myDatabase.sqlite > > There's no reason this shouldn't work if your drivers and your version of > *n*x /fully/ support the use of /deb/ram0 for file storage, including support > for locking calls. Works fine under Mac OS X, by the way. > > But SQLite does provide its own way of creating a database in memory. See > > http://www.sqlite.org/inmemorydb.html > > So you'd use something like > > # sqlite3 :memory: > > Of course, that database will be visible only to the process that creates it. > Which means that if you use the command-line tool to create it it will > effectively disappear as you quit the command-line tool. > > If you're using Linux or a Unix that supports it, you can also look into the > shared memory filesystem 'tmpfs'. I haven't tried it, but this should also > support SQLite without any problems. > > Simon. > ___ > 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
Re: [sqlite] creating a sqlite db on raw disk ?
On 19 Nov 2010, at 7:04am, Yang wrote: > when I create a db on a file system, I guess a query process Wait ... are you talking here about creating a new database file or querying one what already exists ? > has to go > through 2 levels of seeks ? > first sqlite finds the B-tree node that stores the index to the file > offset of my desired record, then sqlite uses that offset to make > syscall seek(offset), > then Kernel consults the FS implementation to find from its OWN > B-tree (for example in ext3 fs ) the block location of that offset. Assuming that you're using a FS that uses B-trees, nodes, and indices (many don't), that's a fair summary of what happens. But SQLite runs fine on many embedded systems that use linked lists instead of B-trees, or don't use nodes, or have databases stored in non-writable sequential memory. > innodb allows creating a db on a raw disk partition, can we do the > same on sqlite? No. You need a file system of some kind. Or, at least, your operating system needs to be able to address your storage using file-system calls, not storage-structure calls. > I tried directly creating a db on ramdisk, failed: > > javasqlite-20100727# sqlite3 /dev/ram0 You didn't tell it what to call the database, just where you wanted it. I think you want something like # sqlite3 /dev/ram0/myDatabase.sqlite There's no reason this shouldn't work if your drivers and your version of *n*x /fully/ support the use of /deb/ram0 for file storage, including support for locking calls. Works fine under Mac OS X, by the way. But SQLite does provide its own way of creating a database in memory. See http://www.sqlite.org/inmemorydb.html So you'd use something like # sqlite3 :memory: Of course, that database will be visible only to the process that creates it. Which means that if you use the command-line tool to create it it will effectively disappear as you quit the command-line tool. If you're using Linux or a Unix that supports it, you can also look into the shared memory filesystem 'tmpfs'. I haven't tried it, but this should also support SQLite without any problems. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] creating a sqlite db on raw disk ?
when I create a db on a file system, I guess a query process has to go through 2 levels of seeks ? first sqlite finds the B-tree node that stores the index to the file offset of my desired record, then sqlite uses that offset to make syscall seek(offset), then Kernel consults the FS implementation to find from its OWN B-tree (for example in ext3 fs ) the block location of that offset. innodb allows creating a db on a raw disk partition, can we do the same on sqlite? I tried directly creating a db on ramdisk, failed: javasqlite-20100727# sqlite3 /dev/ram0 SQLite version 3.7.3 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table blah ( x int ); Error: disk I/O error note that I was able to create an e2fs on the same ramdisk Thanks Yang ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
