Data Bulk in Database Write

[Oralloy]

Folks,

Pardon my question here, but I am trying to get a handle on I/O levels when writing to a SQLite database.

My requirement is to limit the amount of write to a JFFS file-system, so that we do not suffer catestrophic failure before end of system life.

Two models are held available - keep the database in memory and copy to flash periodically, or just keep the database on the flash.

Do any of you have a way that I might instrument, or otherwise measure the amount of disk change when writing to the database? Either by interrogating the device driver, or through device inspection?

Thank You,
Oralloy

[weaknessforcats]

What do you mean by "writes"? I know the C/C++ write function can write a block of memory in one call but that does not tell you the number of writes to a file system. Do you mean the number of disc sector writes?

I would keep this database on a mirrored server so you run no risk of data loss. Then at prescribed intervals you can copy this database to your JFFS system. Like during a scheduled system maintenance down time. You can't really make the copy with the system up since the data is not stable over the period of the copy.

Post your thoughts. I may not understand your situation.

[Oralloy]

weaknessforcats,

You are the recognised expert, so I will try to explain in an intelligible way....

I am working on an embedded system running Linux that has both a hard-disk and flash memory. The flash memory is accessed as a hard disk using the JFFS2 file-system. In addition on hard-disk fail, the system will restart and use the flash in place of the hard-drive, but with a reduced level of writes.

The database is used for systems data collection and periodic reporting. At every report interval the database is emptied. Maximum size that I've seen is about 50 kibyte, so it is rather small. The database is a SQLite database.

There is no option of a mirrored server, nor is the idea worth following at this point.

So, there are two solution options:

1. Frequent backup of the database file to flash (the JFFS2 file-system).
2. Run the database directly on the flash.

Both require I/O activity. What I do not know is how best to instrument the options, so that I can demonstrate the number of writes to the flash.

Ignoring compression, I can estimate the writes using the periodic-copy solution as <size-of-file> times <backup-frequency>.

Running the database directly on the flash, I do not know how to estimate the number of bytes written based on SQL writes executed. Although a database write will only modify a portion of the file, there is no easy measure.

So, questions:

1. How can I watch the file-system and determine the cost of database writes?
2. What are your thoughs on how to proceed?
3. Do you have suggestions as to alternate solutions?

I hope that helps you understand my problem a bit better.

Thank You,
Oralloy

[weaknessforcats]

I found this:

http://unix.stackexchange.com/questions/225095/how-to-get-total-read-and-write-iops-in-linux

Does this make any sense to you?

How would you know if your writes were too expensive?

Keep in mind that cost of writes is relevant only if a)your code is running bug free (otherwise bug fixes take priority), b)your processor speed cannot be increased by adding virtual machines to the processor chip, c) the number of your calls to the file system is already minimized (count your calls to write), d) you are not using someone else's database handler (you are stuck with that handler's operation), e) etc.,etc,...

Why would your database be on flash instead of RAM? You could allocate a large area of RAM and operate it as a private heap. Then at maintenance time you copy the heap to disc.

I have implemented the private heap approach and it has worked great. If all of your code allocates from thus heap, then by deleting the heap all the allocations disappear. This means all the memory leaks disappeared when the heap was deleted.

I'm still not sure why this embedded system cannot report directly to a cloud like an RFID. Most likely there is a reason. Here you would poll the device and receive the data. Like utility billing.

[Oralloy]

Lady WeaknessForCats,

Thank you for getting back to me. I appreciate your deep and thoughtful response. If I was rude, please accept my apologies as that was not my intent.

I had looked at some of those types of articles, and couldn't decide if it was going to give me useful information, or if it would provide a false (or useless) statistic for what I am trying to measure. Also, I was trying to avoid reinvention of wheel, by asking if other folks had solid information or knowledge.

TOO EXPENSIVE: estimate a 10,000 write-cycle life-span for each bit of flash; the computed aggregate write rate should not cause failure over an operational lifetime of ten years. If 10,000 is exceeded, what is the estimate for write-count, and what is the expected system life-span? So, I do not have an exact number, but I have a trivially expressed goal (10,000 write-cycles). Basic threshold is likely a factor of ten low (100,000 write-cycles), but I don't know the exact chip being used (yet). As the flash is accessed with a JFFS2 file-system, there may be error control mechanisms that significantly extend the lifespan.

Seeing how people are pulling that from

/proc

is quite helpful, as I got lost reading through code and reports without labelled information.

Obviously I need to perform some more interesting and detailed measurements, and see if there is useful information which I can capture.

Your observations are germane:

1. Bug free is never guaranteed, but I do try my best. Instrumentation which reports incorrect values, or analysis of the wrong sort, can cause incredible long-term problems.
2. Virtual machines cannot be faster than the host, period end.
3. Yes, I do minimise calls, although there are other processes that I do not have control over while performing experiments, so I will have to control for them.
4. I am currently stuck with SQLite, unless I determine that there is a disaster waiting to happen (e.g. destruction of flash).

The current model is that a specific group of transactions will cause an implicit database backup. Other transactions, which do not write critical information, do not cause the backup.

As to running the database from the flash, instead of periodic backups, is that it may actually write less data than copying the (aprox.) 50 kiByte file for critical transactions. This is a solution that I am considering, as it removes a great deal of (unnecessary) system complexity.

I have also considered a backup process that monitors a flag bit for backup-required, and watches the file to determine if it is active, or no. There are some ugly complexity issues involved with this sort of solution, and I will discuss them with you if I am unable to figure out the necessary IOCTL and locking.

As to the database use, I'm on an embedded system with millions of customer units, so power-off, unintended reboots, and other fails are a fact of life. I do not leave customers in an unrecoverable state.

If you'd like, we can discuss in detail the data collection and retention, as I am not adverse to good suggestions.

I will look into your suggested statistics collection and get back to you tomorrow, once I have a chance to work with them.

Many Thanks,
Oralloy

[donbock]

Do you have the option of using shadow ram for the database? This is a 2-ply RAM device consisting of RAM and nonvolatile storage (typically EEPROM). The device automatically copies EEPROM-to-RAM at powerup and RAM-to-EEPROM at power down. We haven't used this kind of memory in many years -- maybe they don't make it any more.