Re: [sqlite] WAL on a separate filesystem?
Quoth Simon Slavin , on 2011-01-20 00:55:18 +: > Agreed. I was imagining that one file was on an external hard disk > and the other was on an internal disk. If the power to the external > hard disk goes off, one file will get updated but the other won't. > I don't know enough about the inner workings of SQLite to know if it > can cope. I suspect that for the files themselves it's okay so long as fsync is actually a hard sync (and not just a reordering barrier), which it's supposed to be anyway. I don't believe SQLite can be making any assumptions regarding the reordering of unsynchronized writes between files on a single device; if fsync is a hard sync then any ordering semantics are already propagated through the application to the other device in turn. However, if either file ever gets created or deleted during normal operation, then you have the problem of needing to sync the directory, and that probably won't work because SQLite won't hit the correct directory for whichever file is actually somewhere else. Also, if one file is a symlink and it gets deleted and recreated, then it will be recreated on the wrong filesystem. This suggests that actually the main database should be the symlink if one goes that route, and it should be precreated as an empty file (and synchronized to storage) before any operations begin. Either way, it's an unsupported configuration and should only be used with extreme caution, especially if the SQLite component may later be replaced with the expectation of backwards compatibility. > Simon. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL on a separate filesystem?
On 20 Jan 2011, at 12:49am, Scott Hess wrote: > The biggest concern I would have would be whether there are subtle > atomicity guarantees which are served by being on the same filesystem > which cannot be served by being split across multiple filesystems. Agreed. I was imagining that one file was on an external hard disk and the other was on an internal disk. If the power to the external hard disk goes off, one file will get updated but the other won't. I don't know enough about the inner workings of SQLite to know if it can cope. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL on a separate filesystem?
On Wed, Jan 19, 2011 at 12:15 PM, Dustin Sallings wrote: > This isn't a question so much about value judgment (I've already > argued that some, though mentioning maintenance tools is helpful > there, too). It comes down to whether reliability of SQLite itself > would be reduced if a WAL existed on a different partition -- whether > there are any assumptions WAL makes that would be invalid across > a filesystem boundary. You either would have to hack the filesystem namespace to look like you want it to look using mount tricks, or modify SQLite's VFS (or write your own) to accomplish what you want. Since you cannot trust the contents of the database itself, you would need to provide some means of specifying the alternate location in code. The biggest concern I would have would be whether there are subtle atomicity guarantees which are served by being on the same filesystem which cannot be served by being split across multiple filesystems. -scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL on a separate filesystem?
On 19 Jan 2011, at 8:15pm, Dustin Sallings wrote: > On Jan 19, 2011, at 11:01, Simon Slavin wrote: > >> If you do something special to keep your journal file in a different place, >> these other sqlite3 applications won't find it. So they'll just find a >> corrupt database file, and are less likely to be able to figure out how to >> restore to a COMMIT point or a SAVEPOINT. > > > I understand this concern and think it's a valid point. I can assume > for the purpose of this usage that only tools I provide will be used to > access the DB (I ship a sqlite3 binary since I'm using WAL and I've got users > on CentOS which ships sqlite 1.2 for all I know). This solution depends on what facilities your operating system has for creating an alias, soft link, shortcut, or whatever it calls them. If your operating system does these correctly, the sqlite3 open function should correctly understand it and use it. If CentOS is just another flavour of Linux you will probably want to use soft links. Experiment with soft links (or whatever is appropriate) first, to make sure you understand what they are and how they work. Given two different filespaces ... one you want the database file on (call it D) one you want the journal file on (call it J) Step 1 -- Set up your application to use J for both files. Start your application or whatever other tool you have to create the database file and put at least a little data in it. Quit the application. Step 2 -- Copy your database file from J to D. Delete (or rename) the original copy of the file in J. Create an alias/link/shortcut/whatever in J, linked to the file that is now in D. Step 3 -- Restart your application. Make sure it is correctly following the link and finding the data you have moved to D. Because your application thinks that the database file is on J, it should create the journal file on J. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL on a separate filesystem?
On Jan 19, 2011, at 11:01, Simon Slavin wrote: > If you do something special to keep your journal file in a different place, > these other sqlite3 applications won't find it. So they'll just find a > corrupt database file, and are less likely to be able to figure out how to > restore to a COMMIT point or a SAVEPOINT. I understand this concern and think it's a valid point. I can assume for the purpose of this usage that only tools I provide will be used to access the DB (I ship a sqlite3 binary since I'm using WAL and I've got users on CentOS which ships sqlite 1.2 for all I know). I'm a bit of a proxy of this question. I wrote software that uses SQLite under some pretty high volumes and I have a user wanting to split stuff up across multiple filesystems. I already have the ability to do data partitioning in the application, but the user is wanting to separate the WAL out as well. This isn't a question so much about value judgment (I've already argued that some, though mentioning maintenance tools is helpful there, too). It comes down to whether reliability of SQLite itself would be reduced if a WAL existed on a different partition -- whether there are any assumptions WAL makes that would be invalid across a filesystem boundary. -- dustin sallings ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL on a separate filesystem?
On 19 Jan 2011, at 6:43pm, Dustin Sallings wrote: > On Jan 19, 2011, at 2:35, Richard Hipp wrote: > >> No. The WAL has to be in the same directory as the original database. >> Otherwise, the process that tries to recover from a crash or power failure >> won't know where to find the WAL file. > > I understand how it's opening it. This is more about how the file is > used. > > If I could convince SQLite to open the WAL in a location other than in > the same location as the main db, would this cause reliability problems? The problem is not with the session which creates the WAL file, its with the one that has to clear up after any crash. With things as they stand, every application which uses sqlite3 looks for a journal file in the same place. So if your application (or the computer) crashes and someone uses a different application next to open the database file, it will definitely be able to find the journal file and restore the database to an uncorrupted state, right after a COMMIT, just where you'd want it. This happens a lot: after a crash instead of restarting the normal application, someone uses the sqlite3 command-line utility or some sort of database viewer to see what state the data is in. With the journal in a predictable location SQLite will handle this correctly no matter what application they use. If you do something special to keep your journal file in a different place, these other sqlite3 applications won't find it. So they'll just find a corrupt database file, and are less likely to be able to figure out how to restore to a COMMIT point or a SAVEPOINT. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL on a separate filesystem?
On Jan 19, 2011, at 2:35, Richard Hipp wrote: > No. The WAL has to be in the same directory as the original database. > Otherwise, the process that tries to recover from a crash or power failure > won't know where to find the WAL file. I understand how it's opening it. This is more about how the file is used. If I could convince SQLite to open the WAL in a location other than in the same location as the main db, would this cause reliability problems? -- dustin sallings ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL on a separate filesystem?
On 19 Jan 2011, at 11:53, Russell Leighton wrote: > Perhaps that could be the default and a pragma could be used to > override this default and specify the directory holding the WAL. > > This could be useful in cases that users want to put the WAL > someplace else (like an SSD). I think you're getting way beyond the 'Lite' world with that one. On modern Linux systems you can use mount --bind /path/to/existing/file /alternate/path/to/that/file Which makes any access of /alternate/path/to/that/file an access of /path/to/existing/file. Both must exist; I recommend using touch to create the file at the alternate path, as it's mostrously confusing if it contains any data when the mount has not been run. You could use this facility to make the SQLite database appear to be on your SSD. For safety's sake you should also make the WAL file appear to be colocated with the database, so that the WAL file can be found by an application which accesses the database with either path. This may or may not be safe depending on whether SQLite uses the path to the database to perform any locking. To be clear: I won't be doing this myself… Best Regards, Phil Willoughby -- Managing Director, StrawberryCat Limited StrawberryCat Limited is registered in England and Wales with Company No. 7234809. The registered office address of StrawberryCat Limited is: 107 Morgan Le Fay Drive Eastleigh SO53 4JH ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL on a separate filesystem?
On Jan 19, 2011, at 5:35 AM, Richard Hipp wrote: > On Wed, Jan 19, 2011 at 2:52 AM, Dustin Sallings > wrote: > >> >> Is it possible without violating any assumptions that would >> lead to >> reliability problems to have a DB's WAL exist on a separate >> filesystem? >> > > No. The WAL has to be in the same directory as the original database. > Otherwise, the process that tries to recover from a crash or power > failure > won't know where to find the WAL file. > Perhaps that could be the default and a pragma could be used to override this default and specify the directory holding the WAL. This could be useful in cases that users want to put the WAL someplace else (like an SSD). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL on a separate filesystem?
On Wed, Jan 19, 2011 at 2:52 AM, Dustin Sallings wrote: > >Is it possible without violating any assumptions that would lead to > reliability problems to have a DB's WAL exist on a separate filesystem? > No. The WAL has to be in the same directory as the original database. Otherwise, the process that tries to recover from a crash or power failure won't know where to find the WAL file. > > -- > dustin sallings > > ___ > 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
[sqlite] WAL on a separate filesystem?
Is it possible without violating any assumptions that would lead to reliability problems to have a DB's WAL exist on a separate filesystem? -- dustin sallings ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
