I have a problem where I need both a high throughput (10% write/delete, 90% read) and durability. My transactions are really simple, usually just a single write, delete or read, but it is essential that I know when a transaction is commited to disk, so that it would be durable after a crash.
I can see that sqlite does an fsync() after each COMMIT, so a naive implementation give *very* bad performance. I could severeal operations into one transaction, reducing the amout of time waiting for fsync() to finish, but I am not sure whether that is the most efficient solution. Is it possible to delay the fsync(), so that it only occurs after 10 or 100 transactions? The reason I ask is that I certainly don't want to roll back, if one operation fails, because the operations are basically independent of each other. And it may be more efficient if the transaction size stays small. Ideas? And is there a way to automatically replicate the database to a second system? Thomas
