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
