Menu
I use sqlite on Raspberry Pi to store sensor readings. The table structure is simple:
CREATE TABLE IF NOT EXISTS sensors(id INTEGER PRIMARY KEY AUTOINCREMENT, codename TEXT, name TEXT)
CREATE TABLE IF NOT EXISTS sensorvalues(id INTEGER PRIMARY KEY AUTOINCREMENT, sensorid INTEGER, value DECIMAL(10,2), time DATETIME, FOREIGN KEY(sensorid) REFERENCES sensors(id))
Each time I insert record to 'sensorvalues' table (about 20 bytes of data) iotop shows that about 30Kb is written do disk. In order to protect sd card from corruption I would like to minimize data written to permanent memory. I know that writing exactly 20 bytes is not possible because IO operations use page structures and buffers, but writing 30Kb on each commit seems too much. Is there any way to tune sqlite to minimize ammount of written data to neccessary minimum?
223
Try to zip it and use zipinput stream while unpacking your database from assets. Anyway, android will zip your 14mb database when you create your apk anyway (which will be unzipped during install), so I guess your apk will be around 5-6mb in size. Android will zip automatically when creating/exporting apk file?
The maximum size of a database file is 4294967294 pages. At the maximum page size of 65536 bytes, this translates into a maximum database size of approximately 1.4e+14 bytes (281 terabytes, or 256 tebibytes, or 281474 gigabytes or 256,000 gibibytes).
Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second. Transaction speed is limited by the rotational speed of your disk drive.
sqlite > dbinfo. sql will give you detail info on each table's size on disk.
Every valid SQLite database file begins with the following 16 bytes (in hex): 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00. This byte sequence corresponds to the UTF-8 string "SQLite format 3" including the nul terminator character at the end. 1.3.2. Page Size The two-byte value beginning at offset 16 determines the page size of the database.
If a version of SQLite coded to the current file format specification encounters a database file where the read version is 1 or 2 but the write version is greater than 2, then the database file must be treated as read-only. If a database file with a read version greater than 2 is encountered, then that database cannot be read or written.
The memory-mapped file is in the same directory as the database and has the same name as the database with a "-shm" suffix appended. Because the wal-index is shared memory, SQLite does not support journal_mode=WALon a network filesystem when clients are on different machines, as all clients of the database must be able to share the same memory.
Magic Header String Every valid SQLite database file begins with the following 16 bytes (in hex): 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00. This byte sequence corresponds to the UTF-8 string "SQLite format 3" including the nul terminator character at the end.
Inserting into these tables requires updating both the table and the index, and for reliability in the case of a crash, the database first writes the old data to a rollback journal, then updates the database file itself, then deletes the rollback journal.
You could disable the safety features with PRAGMA journal_mode = memory and/or PRAGMA synchronous = off, but
if the application using SQLite crashes in the middle of a transaction when the MEMORY journaling mode is set, then the database file will very likely go corrupt.
For many inserts, WAL mode is likely to be more efficient.
140
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
