Right now I index off the order id. I can look into options for indexing - you are correct that it is more likely that I'll need to read an order I recently wrote than one that is older. However, since reading is ~.2% of the accesses to the db, all db work accounts for 2% of the cpu usage, it may not be worth optimizing in that area.
In my system, order codes are unique, and orders go to a specific thread based on a hash of that id. Therefore, the only thing I need to do is create a unique db file, probably based on the thread id, and each thread's logic for writing/reading is unchanged - just which db is different. Currently I archive orders individually, at the time I determine the order shouldn't be needed. I could add logic to do that in a batch in the future, but that would require some extensive changes elsewhere in the logic, so I'll try some of the other suggestions first. -----Original Message----- From: Simon Slavin [mailto:slav...@hearsay.demon.co.uk] Sent: Thursday, July 09, 2009 1:08 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] multi-thread access to a db On 9 Jul 2009, at 2:58pm, Rizzuto, Raymond wrote: > 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. Okay, you're way ahead of most of my recommendations and obviously understand what you're doing. > 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. Since it takes very little extra coding to test the effect on speed it might be worth experimenting with unusual index methods. If you read the database only for two operations, both of which need all records to do with a particular order, it might be worth experimenting with having no primary key, just one index on the 'order number' column. One DBMS I used to use was famously faster if you used 'DESC' in indices, since records you were normally looking for were more likely to be recent records than extremely old ones, and the search algorithm worked faster that way. > I am using begin/commit around the writes since each order can > require between 1 and 5 writes. If you're writing orders in big batches, and if your previous statements about crash-recovery are true, then it might be worth putting begin/commit just around each batch of orders instead of each individual order. You might want to take it even further: by analogy with a non-SQL DBMS, I once wrote a logging program which did a COMMIT only just before a SELECT was needed, or when the logging program was quit. However, I don't know how SQLite acts if you have thousands of uncommitted changes: it might get slower if you have that many. > 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. If you don't share data, that might be good. You'd need to work out a a system for order codes, of course, so that you can work out which dataset a particular order is in. 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
