 |
lua-l archive |
|
|
||
|
On 01/29/2014 07:42 PM, Gilles Ganault wrote:
On Wed, 29 Jan 2014 19:07:24 -0500, Nathan Angelacos <nangel@nothome.org> wrote:try using tmps (from your post, /run/shm would work) and see if it improves. If it does, then you know its the SD card: local db = sqlite3.open('/run/shm/test.db')Yes, it runs in a fraction of a second. I'll read up on SD and ext4.
Ext4 has little or nothing to do with it [1], but sqlite and flash storage has a lot.
If you need your database to be preserved across power loss, don't put it on a tmpfs; if you don't, you might want to consider a different database.
As I mentioned before, sqlite syncs to disk after every transaction. Writing to cheap flash (especially cheap SD cards) is _very_ slow.
Typically, when using sqlite the best idea is to wrap operations that are all executed at once into transactions (this is often desirable for other reasons as well, such as atomicity and serializability); you did 4 operations and when you wrapped it you got a reduction by about a factor of 4, etc. I only showed you the first exec() wrapped in the xaction because that's what you complained was slow. You can also put the next SELECT inside the transaction and you can generate your mg.write()s without delay. If the DB is accessed by only one process you may be able to create some long-running transactions but generally this is not a good idea.
In your case, you can disable the sync of the write-ahead log by executing this commands after you open your database [2]:
PRAGMA synchronous = OFF;This should eliminate the delay, at the cost of leaving you open to lost data if e.g. your appliance loses power after a write. But that's basically what you are asking for anyhow: the only way to guarantee that the data is physically written is to physically write it, and your storage is slow for writes.
-- David[1]: the filesystem's metadata journal (which may or may not be present on either an ext3 or ext4 FS, but is not on ext2) might make the delay slightly longer, but even if you removed it you would likely not notice the difference.
[2]: http://www.sqlite.org/pragma.html#pragma_synchronous