On May 5, 2008, at 1:59 PM, John Passaniti wrote: > I am trying to decide if sqlite is appropriate for my system. > > The target is an embedded system. It's an ARM9 processor running > under Linux 2.6. The system's primary storage is NAND flash, using > jffs2. The system is essentially a datalogger, where at regular > intervals (typically once per minute) sensor data is written. The > table schema would be very simple-- each row would have a timestamp, > and one column for each sensor. The table would be indexed on the > timestamp. Also, as time marches forward, old data (more than a year) > will be periodically deleted. > > My concern is this: I gather sqlite keeps data in an indexed tree > structure. As I write new rows to the table, I would imagine the data > has the potential to move around in the tree. That is, as I add > entries, the tree will rebalance, possibly shuffling data around. > > I guess my question is in the situation I just described, how often > will data be shuffled around? I'm not looking for some absolute > number here, but rather a sense of if a sqlite database on a jffs2 > filesystem would be spending a lot of time rebalancing trees and thus, > wearing out the flash faster. Or if such is just inherent with > sqlite, are there programming pointers people can provide that would > minimize this?
Use transactions if you did not already plan to. SQLite uses the b-tree structure (search for "b-tree comer" for a good paper describing the data structure), so shuffling the tree around is done incrementally during insert/delete operations. The algorithms try hard to minimize the number of pages written by this shuffling and are usually pretty good, but the theoretical worst performance is terrible. If you are using automatically generated rowids for your table (you are unless you have declared a column as INTEGER PRIMARY KEY) and the indexed timestamps are always increasing (I guess they are, right?) then your overhead will be mimimal. You should try to measure it first to make sure though... Dan. > _______________________________________________ > 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
