Each thread has its own data. I.e. a thread only ever needs to read data that it wrote.
I considered using the asynch io module described at http://www.sqlite.org/asyncvfs.html so that I could have a worker thread, but was dissuaded by the author, who suggested the asynch pragma instead. The worker thread that decides to move an order from memory to db might in the very next instance find it needs to restore the order to memory. There would need to be some interlock to ensure that the data was not deleted till it was stored, or that it could be retrieved from the db thread's work queue. I believe the asynch io moduel supported that. At this point, I am relatively happy with the performance I get when I have both asynch and exclusive selected. In a simple (non-multithreaded) test, I was able to write 10,000 records of 1k length each in 1 second. My main concern is to make sure that I code my multithreaded application to work correctly with sqlite in the asynch+exclusive mode, and that I don't do anything to degrade the performance from what I saw in my simple test. Ray -----Original Message----- From: Ken [mailto:kennethinbox-sql...@yahoo.com] Sent: Thursday, July 09, 2009 10:33 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] multi-thread access to a db Additional considerations: 1. Does the re-reading of data occur cross thread? If so you'll need some way to identify the DB that contains the data. 2. Consider using either a disk array or multiple disk drives, one for each db file. You probably should do some load testing at volume to determine optimal configurations for you write patterns. 3. You indicate that the I/O is minimal. Why not create one thread that handles the Database work load. All the other threads could pass the work to the DbWorker thread. This would also eliminate DB contention, but would cause contention at the OS Mutex/Semaphore layer, which should be faster than disk based contention. Implementation of course could be done via a Shared Memory segment (if unix based) and some locking mechanisms. For a really slick high performance LL implemenation consider using an Unrolled Linked List. They are incredibly fast and provide 3-5 times faster performance than a simple LL, especially on multicore cpu's with large L1,L2 cache lines. HTH --- On Thu, 7/9/09, Rizzuto, Raymond <raymond.rizz...@sig.com> wrote: > From: Rizzuto, Raymond <raymond.rizz...@sig.com> > Subject: Re: [sqlite] multi-thread access to a db > To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> > Date: Thursday, July 9, 2009, 8:58 AM > I have 4 servers, all with 4 > cores. This is to handle a volume of 10-20 million > orders per day. > > Most of the work load (~90%) is unrelated to the > database. In fact, I added the database just to allow > me to offload orders out of memory when they look done so > that the app doesn't run out of memory. It is a 32-bit > app, so it typically dies when it hits ~2.5 gig. > Moving to a 64-bit app was not an option. > > In approx .2% of the orders, I guess wrong, and have to > read the order back in from the db to process additional > activity. I could remove indices to improve write > performance, however the hit on read performance might > outweigh the gain. Since the processing is supposed to > be in near-realtime, the delay in that case might be > excessive. Still, it may be worth trying that. > > I am using begin/commit around the writes since each order > can require between 1 and 5 writes. > > Ken made a suggestion that I create separate db's for each > thread. Since the threads don't share data, that > approach would work for me, and eliminate all > contention. Each db would have the same schema, of > course. > > Ray > > -----Original Message----- > From: Simon Slavin [mailto:slav...@hearsay.demon.co.uk] > Sent: Wednesday, July 08, 2009 5:12 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] multi-thread access to a db > > > On 8 Jul 2009, at 9:28pm, Rizzuto, Raymond wrote: > > > If I remove the locking_mode=exclusive, I don't get > those errors. > > > > I'd appreciate any advice on how I can get the best > performance > > using multiple threads in my application, given that: > > > > > > 1. I need maximum performance. > > Spend at least five grand on a fast water-cooled box. > Use a version > of Unix/Linux compiled without support for anything you > don't need, > like printing. Create your database file on a RAM > disk. Write your > application as a command-line app, and don't run the GUI. > > > That is also why I need multiple threads > > May not help if they're all constantly accessing the > database. In > fact contention for access can slow the process down: you > have seven > threads, five of which are perpetually blocked. > There's no one-size- > fits-all solution to fast database access, it depends on > the patterns > of when data is available for writing, and how important > the order the > data was available is when you read. Sometimes you > pile up all your > data to be written into a text file, and another process > (on a > different computer ?!) works through the text file and does > the writing. > > > 2. All threads need to write to the same db > > 3. No other application needs access to the db > > 4. I don't care about durability, just fast > insert times since > > reads are much less frequent. > > Use BEGIN TRANSACTION and COMMIT properly. This may > be more important > than multi-threading. It has a huge result. > > If reads are /really/ rare, it might be worth removing all > indices on > your database, and only creating an index just before you > need to > read, or even just executing the SELECT without any > indices. > > Simon. > > > IMPORTANT: The information contained in this email and/or > its attachments is confidential. If you are not the intended > recipient, please notify the sender immediately by reply and > immediately delete this message and all its attachments. Any > review, use, reproduction, disclosure or dissemination of > this message or any attachment by an unintended recipient is > strictly prohibited. Neither this message nor any attachment > is intended as or should be construed as an offer, > solicitation or recommendation to buy or sell any security > or other financial instrument. Neither the sender, his or > her employer nor any of their respective affiliates makes > any warranties as to the completeness or accuracy of any of > the information contained herein or that this message or any > of its attachments is free of viruses. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
