Re: [sqlite] light weight write barriers

[David Lang]

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

[rui]

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

[Howard Chu]

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  

Re: [sqlite] light weight write barriers

[Howard Chu]

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

[Chris Friesen]

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

[Joe Mistachkin]

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

[LMHmedchem]

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

[Dan Freundel]

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