Re: [sqlite] light weight write barriers
On Fri, 16 Nov 2012, Howard Chu wrote: David Lang wrote: barriers keep getting mentioned because they are a easy concept to understand. "do this set of stuff before doing any of this other set of stuff, but I don't care when any of this gets done" and they fit well with the requirements of the users. Users readily accept that if the system crashes, they will loose the most recent stuff that they did, *some* users may accept that. *None* should. when users are given a choice of having all their work be very slow, or have it be fast, but in the unlikely event of a crash they loose their mose recent changes, they are willing to loose their most recent changes. If you think about it, this is not much different from the fact that you loose all changes since the last time you saved the thing you are working on. Many programs save state periodically so that if the application crashes the user hasn't lost everything, but any application that tried to save after every single change would be so slow that nobody would use it. There is always going to be a window after a user hits 'save' where the data can be lost, because it's not yet on disk. There are a couple industry failures here: 1) the drive manufacturers sell drives that lie, and consumers accept it because they don't know better. We programmers, who know better, have failed to raise a stink and demand that this be fixed. A) Drives should not lose data on power failure. If a drive accepts a write request and says "OK, done" then that data should get written to stable storage, period. Whether it requires capacitors or some other onboard power supply, or whatever, they should just do it. Keep in mind that today, most of the difference between enterprise drives and consumer desktop drives is just a firmware change, that hardware is already identical. Nobody should accept a product that doesn't offer this guarantee. It's inexcusable. This is an option to you. However if you have enabled write caching and reordering, you have explicitly told the system to be faster at the expense of loosing data under some conditions. The fact that you then loose data under those conditions should not surprise you. The idea that you must have enough power to write all the pending data to disk is problematic as that then severely limits the amount of cache that you have. B) it should go without saying - drives should reliably report back to the host, when something goes wrong. E.g., if a write request has been accepted, cached, and reported complete, but then during the actual write an ECC failure is detected in the cacheline, the drive needs to tell the host "oh by the way, block XXX didn't actually make it to disk like I told you it did 10ms ago." The issue isn't a drive having a write error, it's the system shutting down (or crashing) before the data is written, no OS level tricks will help you here. The real problem here isn't the drive claiming the data has been written when it hasn't, the real problem is that the application has said 'write this data' to the OS, and the OS has not done so yet. The OS delays the writes for many legitimate reasons (the disk may be busy, it can get things done more efficently by combining and reordering the writes, etc) Unless the system crashes, this is not a problem, the data will eventually be written out, and on system shutdown everthing is good. But if the system crashes, some of this postphoned work doesn't get done, and that can be a problem. Applications can do fsync if they want to be sure that their data is safe on disk NOW, but they currently have no way of saying "I want to make sure that A happens before B, but I don't care if A happens now or 10 seconds from now" That is the gap that it would be useful to provide a mechanism to deal with, and it doesn't matter what your disk system does in terms of lieing ot not, there still isn't a way to deal with this today. David Lang ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] memory leak in transactions
Thanks for your reply. I am using version 1.0.79.0 of System.Data.sqlite Here is how i am using it, i only use executescalar and executenonquery, let me know if you want to see how i pass the parameters etc. RunBigtransaction() { using (IDbTransaction tran = Connection.BeginTransaction()) { foreach (Message msg in messages) { CreateMessage(msg); } tran.Commit(); } } void CreateMessage() { string tableName = gettablename(); // here i check if a table exists using a select query string query = select "name" from "sqlite_master" where "type=table", name=tablename"; string tablename = ExecuteQuery(query) if (tablename == null) { // here i do a query on another table in schema to read a script to create a new table, which uses the ExecuteGetTableQuery. string script = getscript(); Exceute(script); } // here i execute an insert and update // insert uses the same pattern like ExecuteGetTableQuery but with query changed to insert into with 15 parameters. RawInsertMessage(tableName, msg); // insert or update where update does just Execute function. SetMessageFolderId(msg.StoreId, msg.Id, msg.ParentId); } public void Execute(string script) { using (IDbCommand cmd = Connection.CreateCommand()) { cmd.CommandText = script; cmd.ExecuteNonQuery(); } } public string ExecuteGetTableQuery(string b) { using (IDbCommand cmd = Connection.CreateCommand()) { cmd.CommandText = b.ToString(); object r = cmd.ExecuteScalar(); if (r == null || r is DBNull) return default(T); if (!(r is T)) throw new InvalidCastException("cannot cast " + r.GetType() + " to " + typeof (T)); return (T) r; } } Regards, Raj On Thu, Nov 15, 2012 at 8:52 PM, Joe Mistachkin wrote: > > rui wrote: >> >> I am seeing explosive memory growth when i am using transactions using >> System.Data.SQLite. >> > > The best way to address this issue is to utilize "using" blocks for any > SQLiteCommand, SQLiteDataReader, and SQLiteTransaction objects used. That > way, you won't have to wait until they are garbage collected later on by the > CLR. > >> >> All the object are properly disposed from trasnsaction to command etc. >> > > Could you provide some example C# code that demonstrates the behavior you are > seeing? > >> >> The SQLiteConnection is kept open for the life time of the session, >> which could span hours. >> > > This means the memory associated with the connection will be kept around; > however, this should be OK and should not result in the memory usage numbers > you are seeing. > > -- > Joe Mistachkin > > > ___ > 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] light weight write barriers
Ric Wheeler wrote: On 11/16/2012 10:06 AM, Howard Chu wrote: David Lang wrote: barriers keep getting mentioned because they are a easy concept to understand. "do this set of stuff before doing any of this other set of stuff, but I don't care when any of this gets done" and they fit well with the requirements of the users. Users readily accept that if the system crashes, they will loose the most recent stuff that they did, *some* users may accept that. *None* should. but they get annoyed when things get corrupted to the point that they loose the entire file. this includes things like modifying one option and a crash resulting in the config file being blank. Yes, you can do the 'write to temp file, sync file, sync directory, rename file" dance, but the fact that to do so the user must sit and wait for the syncs to take place can be a problem. It would be far better to be able to say "write to temp file, and after it's on disk, rename the file" and not have the user wait. The user doesn't really care if the changes hit disk immediately, or several seconds (or even 10s of seconds) later, as long as there is not any possibility of the rename hitting disk before the file contents. The fact that this could be implemented in multiple ways in the existing hardware does not mean that there need to be multiple ways exposed to userspace, it just means that the cost of doing the operation will vary depending on the hardware that you have. This also means that if new hardware introduces a new way of implementing this, that improvement can be passed on to the users without needing application changes. There are a couple industry failures here: 1) the drive manufacturers sell drives that lie, and consumers accept it because they don't know better. We programmers, who know better, have failed to raise a stink and demand that this be fixed. A) Drives should not lose data on power failure. If a drive accepts a write request and says "OK, done" then that data should get written to stable storage, period. Whether it requires capacitors or some other onboard power supply, or whatever, they should just do it. Keep in mind that today, most of the difference between enterprise drives and consumer desktop drives is just a firmware change, that hardware is already identical. Nobody should accept a product that doesn't offer this guarantee. It's inexcusable. B) it should go without saying - drives should reliably report back to the host, when something goes wrong. E.g., if a write request has been accepted, cached, and reported complete, but then during the actual write an ECC failure is detected in the cacheline, the drive needs to tell the host "oh by the way, block XXX didn't actually make it to disk like I told you it did 10ms ago." If the entire software industry were to simply state "your shit stinks and we're not going to take it any more" the hard drive industry would have no choice but to fix it. And in most cases it would be a zero-cost fix for them. Once you have drives that are actually trustworthy, actually reliable (which doesn't mean they never fail, it only means they tell the truth about successes or failures), most of these other issues disappear. Most of the need for barriers disappear. I think that you are arguing a fairly silly point. Seems to me that you're arguing that we should accept inferior technology. Who's really being silly? If you want that behaviour, you have had it for more than a decade - simply disable the write cache on your drive and you are done. You seem to believe it's nonsensical for someone to want both fast and reliable writes, or that it's unreasonable for a storage device to offer the same, cheaply. And yet it is clearly trivial to provide all of the above. If you - as a user - want to run faster and use applications that are coded to handle data integrity properly (fsync, fdatasync, etc), leave the write cache enabled and use file system barriers. Applications aren't supposed to need to worry about such details, that's why we have operating systems. Drives should tell the truth. In event of an error detected after the fact, the drive should report the error back to the host. There's nothing nonsensical there. When a drive's cache is enabled, the host should maintain a queue of written pages, of a length equal to the size of the drive's cache. If a drive says "hey, block XXX failed" the OS can reissue the write from its own queue. No muss, no fuss, no performance bottlenecks. This is what Real Computers did before the age of VAX Unix. Everyone has to trade off cost versus something else and this is a very, very long standing trade off that drive manufacturers have made. With the cost of storage falling as rapidly as it has in recent years, this is a stupid tradeoff. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http://www.op
Re: [sqlite] light weight write barriers
David Lang wrote: barriers keep getting mentioned because they are a easy concept to understand. "do this set of stuff before doing any of this other set of stuff, but I don't care when any of this gets done" and they fit well with the requirements of the users. Users readily accept that if the system crashes, they will loose the most recent stuff that they did, *some* users may accept that. *None* should. but they get annoyed when things get corrupted to the point that they loose the entire file. this includes things like modifying one option and a crash resulting in the config file being blank. Yes, you can do the 'write to temp file, sync file, sync directory, rename file" dance, but the fact that to do so the user must sit and wait for the syncs to take place can be a problem. It would be far better to be able to say "write to temp file, and after it's on disk, rename the file" and not have the user wait. The user doesn't really care if the changes hit disk immediately, or several seconds (or even 10s of seconds) later, as long as there is not any possibility of the rename hitting disk before the file contents. The fact that this could be implemented in multiple ways in the existing hardware does not mean that there need to be multiple ways exposed to userspace, it just means that the cost of doing the operation will vary depending on the hardware that you have. This also means that if new hardware introduces a new way of implementing this, that improvement can be passed on to the users without needing application changes. There are a couple industry failures here: 1) the drive manufacturers sell drives that lie, and consumers accept it because they don't know better. We programmers, who know better, have failed to raise a stink and demand that this be fixed. A) Drives should not lose data on power failure. If a drive accepts a write request and says "OK, done" then that data should get written to stable storage, period. Whether it requires capacitors or some other onboard power supply, or whatever, they should just do it. Keep in mind that today, most of the difference between enterprise drives and consumer desktop drives is just a firmware change, that hardware is already identical. Nobody should accept a product that doesn't offer this guarantee. It's inexcusable. B) it should go without saying - drives should reliably report back to the host, when something goes wrong. E.g., if a write request has been accepted, cached, and reported complete, but then during the actual write an ECC failure is detected in the cacheline, the drive needs to tell the host "oh by the way, block XXX didn't actually make it to disk like I told you it did 10ms ago." If the entire software industry were to simply state "your shit stinks and we're not going to take it any more" the hard drive industry would have no choice but to fix it. And in most cases it would be a zero-cost fix for them. Once you have drives that are actually trustworthy, actually reliable (which doesn't mean they never fail, it only means they tell the truth about successes or failures), most of these other issues disappear. Most of the need for barriers disappear. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http://www.openldap.org/project/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] light weight write barriers
On 11/15/2012 11:06 AM, Ryan Johnson wrote: The easiest way to implement this fsync would involve three things: 1. Schedule writes for all dirty pages in the fs cache that belong to the affected file, wait for the device to report success, issue a cache flush to the device (or request ordering commands, if available) to make it tell the truth, and wait for the device to report success. AFAIK this already happens, but without taking advantage of any request ordering commands. 2. The requesting thread returns as soon as the kernel has identified all data that will be written back. This is new, but pretty similar to what AIO already does. 3. No write is allowed to enqueue any requests at the device that involve the same file, until all outstanding fsync complete [3]. This is new. This sounds interesting as a way to expose some useful semantics to userspace. I assume we'd need to come up with a new syscall or something since it doesn't match the behaviour of posix fsync(). Chris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] memory leak in transactions
rui wrote: > > I am seeing explosive memory growth when i am using transactions using > System.Data.SQLite. > The best way to address this issue is to utilize "using" blocks for any SQLiteCommand, SQLiteDataReader, and SQLiteTransaction objects used. That way, you won't have to wait until they are garbage collected later on by the CLR. > > All the object are properly disposed from trasnsaction to command etc. > Could you provide some example C# code that demonstrates the behavior you are seeing? > > The SQLiteConnection is kept open for the life time of the session, > which could span hours. > This means the memory associated with the connection will be kept around; however, this should be OK and should not result in the memory usage numbers you are seeing. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] inserting record datinto mutliple tables with a composite primary key
This looks like a more or less complete solution for creating the tables and doing inserts. Primary table: CREATE TABLE Structure( 'id' INTEGER PRIMARY KEY, 'name' TEXT NOT NULL, 'filePath' TEXT NOT NULL, 'iH1' INTEGER NOT NULL, 'iH2' INTEGER NOT NULL, 'iH3' INTEGER NOT NULL, 'iH4' INTEGER NOT NULL, 'formula' TEXT NOT NULL, 'fw' FLOAT NOT NULL, UNIQUE(iH1, iH2, iH3, iH4) ON CONFLICT FAIL ) Satellite table: CREATE TABLE Project1( 'Structure_id' INTEGER NOT NULL, 'class' STRING, 'status' STRING, 'RI17-1' FLOAT, FOREIGN KEY(Strucutre_id) REFERENCES Structure(id) ) There are three cases for doing inserts of the data for phosphoserine, which is distributed over both tables. record phosphoserine, data for Structure table: name = phosphoserine filePath = phosphoserine.mol iH1 = 185073 iH2 = 856147 iH3 = 73543 iH4 = 25338 formula= C3H8NO6P fw = 185.073 record phosphoserine, data for Project1 table: class = C0248 status = M RI17-1 = 15.0 these statements include some ruby pseudocode 1. Insert data to Structure table only, do not insert Project1 data @db.execute "INSERT INTO Structure(id, name, filePath, iH1, iH2, iH3, iH4, formula, fw) VALUES(null,'phosphoserine', 'phosphoserine.mol', 185073, 856147, 73543, 25338, 'C3H8NO6P', 185.073)" 2. Sequentially insert data to Structure and then Project1 @db.execute "INSERT INTO Structure(id, name, filePath, iH1, iH2, iH3, iH4, formula, fw) VALUES(null,'phosphoserine', 'phosphoserine.mol', 185073, 856147, 73543, 25338, 'C3H8NO6P', 185.073)" # capture the row number where phosphoserine was inserted to Structure @Structure_id = @db.last_insert_row_id # use the value of Structure_id to link phosphoserine data in Project1 to phosphoserine data in Structure # the implicit rowid will be the primary key for Project1 and so is not handled explicitly @db.execute "INSERT INTO Project1(Structure_id, class, status, RI17-1) VALUES(Structure_id, C0248, M, 15.0)" 3. Insert phosphoserine data to Project1 at some later time, meaning in a situation where there is already a record in Structure for phosphoserine # lookup the row number in Structure where the phosphoserine record is stored using the 4 int key values @Structure_id = @db.execute "SELECT id FROM Structure WHERE iH1 = 185073 AND iH2 = 856147 AND iH3 = 73543 AND iH4 = 25338;" # use the value of Structure_id to link phosphoserine data in Project1 to phosphoserine data in Structure # the implicit rowid will be the primary key for Project1 and so is not handled explicitly @db.execute "INSERT INTO Project1(Structure_id, class, status, RI17-1) VALUES(Structure_id, C0248, M, 15.0)" Excepting that the ruby is probably not quite right here and that the SQL instructions are using literals and not parameters, does this look like a reasonable setup? The Structure table will have more records than the satellite tables and data will be added to the satellite tables more frequently. I think this allows for addition of new tables of data related to previously stored structures (Project2, Project3, etc) as needed, and also allows for a quick method of looking up structures that might have been registered under a different name. Is ON CONFLICT FAIL what I want for the Structure table, or would ABORT/ROLLBACK make more sense? *LMHmedchem* -- View this message in context: http://sqlite.1065341.n5.nabble.com/inserting-record-data-into-mutliple-tables-with-a-composite-primary-key-tp64874p65567.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stricter parsing rules
If it was called "PRAGMA strict_mode" or even "PRAGMA disable_dbl_quot_lit" you could reverse the check and then the default behavior would remain the same. --- On Fri, 11/16/12, NSRT Mail account. wrote: From: NSRT Mail account. Subject: Re: [sqlite] Stricter parsing rules To: "General Discussion of SQLite Database" Date: Friday, November 16, 2012, 1:44 AM Hi Dan, I had some free time and looked into your request. Bear in mind I don't really know what I'm doing, but I managed to whip up this: http://paste.nachsoftware.com/SQLite3/BrksDfe9d421e8ed00d34f0fc50a59956af21byK Results: > ./sqlite3 SQLite version 3.7.14.1 2012-10-04 19:37:12 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> SELECT "cows"; Error: no such column: cows sqlite> PRAGMA dblquoted_str_lit=1; sqlite> SELECT "cows"; cows sqlite> PRAGMA dblquoted_str_lit=0; sqlite> SELECT "cows"; Error: no such column: cows I believe this is what you wanted. I imagine the naming would be improved if this code became mainline, and the ifdefs would be handled better, but I guess this is pretty much what you wanted. Note: This changes the default behavior of SQLite. A proper patch of course would keep the default behavior. Although I couldn't figure out the pragma system enough to see how to specify what default pragma settings are. From: Dan Freundel To: General Discussion of SQLite Database Sent: Thursday, November 15, 2012 7:35 PM Subject: Re: [sqlite] Stricter parsing rules I agree as well. I tested out the original poster's patch and it works great. Any way this can be made into a "PRAGMA strict_mode" so that it would be usage-specific rather than compile-specific? I asked about this on stackoverfolw as well:http://stackoverflow.com/questions/13383763/is-there-a-way-disable-lax-quoting-rules-in-sqlite/ --- On Thu, 11/15/12, John Gabriele wrote: From: John Gabriele Subject: Re: [sqlite] Stricter parsing rules To: "General Discussion of SQLite Database" Date: Thursday, November 15, 2012, 1:56 PM On Wed, Nov 14, 2012 at 5:22 PM, Simon Slavin wrote: > > On 14 Nov 2012, at 9:46pm, BareFeetWare wrote: > >> I agree. this tolerance by SQLite for misquoted identifiers allows a lot of >> errors. I'd really like to see the rules tightened to remove ambiguous input. > > Me three. Perhaps this and a couple of similar things will be fixed in > SQLite4. Me four. The OP's practice of always using double-quotes for identifiers seems like a sound one, and it would be useful if sqlite could tell me if the thing I *think* is an identifier is actually not. ---John ___ 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 ___ 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