QNX & SQLite (On NAND flash drive)

[cpscdave]

Wondering if anyone knows anything about this.

It was mentioned to me in passing. That on QNX, with an ETFS file system when you append to a file, that it completely rewrites that entire file to another location on the Drive. This presenting an issue with extra NAND flash writes ultimately causing corruption. (if anyone has more info on this that would be great as well)

So my question is, when you do an insert into a SQLite DB, and SQLite writes that insert to the disk, is that going to do an append and a complete rewrite?
We want to ensure that we arent' going to corrupt drives 6 months down the road here :)

Thanks for your help!

 

[phinds]

I don't see why it would cause any corruption as long as the file allocation information is properly handled (which it certainly should be).

 

[Tom.G]

I don't see why it would cause any corruption as long as the file allocation information is properly handled (which it certainly should be).

Because of the finite number of write cycles to a flash drive before it wears out. On the order of 10⁴ - 10⁵ writes to a location.

 

[phinds]

Because of the finite number of write cycles to a flash drive before it wears out. On the order of 10⁴ - 10⁵ writes to a location.

Well, sure but that's a function of just WRITING (which obviously IS done more if you re-write entire files) not something that is caused directly by rewriting rather than appending. That is, writing lots of new files and then "erasing" them does the same thing. I put "erasing" in quotes because files are not actually erased, they just get their disk space taken out of the file allocation pointers and the space is used in rotation with other unused space so as to minimize writing to any given cell.

 

[cpscdave]

Why this was raised was the thought that appending to these files would cause the OS to copy the entire file to a new physical location and then add the little extra bit it.

Only reason I could think of why the OS would do this is to keep the drive non-fragmented to maintain performance.

 

[sysprog]

Why this was raised was the thought that appending to these files would cause the OS to copy the entire file to a new physical location and then add the little extra bit it.

Only reason I could think of why the OS would do this is to keep the drive non-fragmented to maintain performance.

With NAND flash device architecture, individual bytes are not addressable. Writes are for at least a page/block at a time. The number of writes is limited, so in order to avoid uneven wear, writes are preferentially targeted to areas in inverse order of of how many times the area has previously been written. As you surmised, in order to keep access speeds optimal, files are preferentially written to contiguous areas. To maintain contiguity, systems will, at least for a file composed of a small number of pages/blocks, write the entire file for an update, even if the update was to content within only one block of a multi-block file.

 

[cpscdave]

I looked into this a bit more and it looks as if you can compile SQLite to work with NAND drives
by setting -DSQLITE_TEMP_STORE=3

More info can be found here: https://www.sqlite.org/compile.html

But basically setting it to 3 SQLite will keep all temp files in memory as opposed to writing the temp files out to the disk.