On Sat, 29 May 2004, Carlos Justiniano wrote: >I'm working on an open source messaging server (MsgSrv) which is >currently using SQLite. MsgSrv'ers are a network of P2P applications, >which communicate with one another. Each communication is a single >message, which is archived in a local SQLite database. > >When messaging traffic increases there are a lot of disk accesses. One >way to address the disk access issue is to use transactions... but that >would involve caching record data until a point in time when that data >can be flushed to the disk DB encased in a transaction block. >{this, I suspect might be the best approach...}
Is the disk access currently a bottleneck? If not, then just keep things as they currently are, otherwise... > >Another approach might be to use two SQLite databases. One db would be >a normal disk database and the other would be a memory only database. >The idea is to write to the memory database and to periodically flush >the memory database to the file database. Whether you keep the memory in a hash map, or an in memory database, you may lose data in a crash for anything not committed to main disk. If data loss is a no-no, then one option may be to queue pending updates to a logically append only queue on disk, which would act something like a write ahead log. Another thread can take items of the head of the queue and commit them to the database in batches. However, maintaining this queue on persistent storage would also involve synchronous IO for data integrity. However, it may be quicker than SQLite synchronous IO due to lack of meta-data updates and append only nature. YMMV. > >Clearly the latter approach requires more processing power but has >considerably more benefits. For one, my MsgSrv application is free to >use SQL queries throughout, while the movement from memory DB to disk DB >happens transparently to the application. I presume that messages have some way of being handled internally at the moment, in which case the latter approach doesn't buy you much. And you'll still have to code the movement from the memory DB to the disk DB, and while that's going on, the memory DB will be inaccessible to the rest of the application, so you'll be limited to the update speed of the disk DB anyway. I'd stick to the forme solution, perhaps with the persistent queue discussed above for integrity. > >With the former approach (managing memory structures in code), I end up >with a more tightly coupled solution ? however, using hash maps would be >wickly fast! > >I'm wondering if anyone has advice or concepts I should be considering - >or perhaps point out something I've missed? If you can't hold data for batching for integrity reasons, and the current solution is too slow, try investigating other databases that may allow cheaper single inserts. It's not a crime to not use SQLite:) > >Carlos > Christian -- /"\ \ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]