Actually, my use of the db is much simpler, and I don't need the databases at all after the program completes.
The db is essentially a backing store for in-memory orders. I.e. I persist the order to the db and discard the in-memory order when it looks likely I won't need it (to avoid out of memory crash), and only very occasionally reload it back into memory from the db. The app runs from start of business to end of business. The db is discarded at end of day since its contents aren't the end product of the app. The app basically converts input order-related events from one wire format into another, and sends the output format over the network to another system. I suspect this is quite different from most uses of sqlite. -----Original Message----- From: Ken [mailto:kennethinbox-sql...@yahoo.com] Sent: Thursday, July 09, 2009 3:06 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] multi-thread access to a db Batching the orders and writing more data as one transaction will certainly yield better throughput, but at the risk of some data loss until the data is committed to disk. It sounds like you are building some type of OLTP/ Transaction logging system. Another good idea here is to also implement or at least think about some form of an archiving system. For instance not only would you have one db per thread (hashed) but maybe daily you switch to a brand new database file set. Maybe a naming convention such as MMDDYY_HashID.db would also be useful? --- 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, 12:28 PM > 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 > 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
