Re: Concurrency and It's Cost

A couple of issues. Obviously, this is an OLTP environment w/ OPTCOMPIND set to '0'. Have you tried a similar query w/ OPTCOMPIND set to 2 (to invoke light scans)?. This will bypass the user work space entirely. Of course, this works best with fragmented tables. Another issue here might be memory. From the numbers you reported, it appears that you might be paging??? How much shared memory does Informix allocate at start up? Are any additional segments allocated during the run? My experience suggests that for generic application that you describe, 128-256 Mb RAM is a minimum for optimizing throughput. I would be very interested in seeing more of the details of your test. Could you include the output from onstat -cd as well as onstat -g ses XXXX, and onstat -g seg? -d dsamson@ndc.co.il wrote in message <67as40$n7n@cssun.mathcs.emory.edu>... > I posted something earlier in the week asking to understand how the > fetch buffer works. I've since done work a) to figure out how it all > works, and b) to assess the costs of alternatives. If you're > interested and still reading, my summary follows. > > The fetch buffer is filled with a number of records at one shot. > This is obviously a big performance booster. However, you must > understand how this works with concurrency control. If you're using > "committed read", OnLine checks to make sure that the records are > committed WHEN THE BUFFER IS FILLED. If, between filling the buffer > and your application executing the fetch, the record gets deleted, you > will still retrieve that record (because it fetches from that buffer, > not the DB itself). > > If that's not good enough, you can switch to "cursor stability". > This makes sure that the record exists when you fetch it. There are 2 > flavors to this: > > a) Within a transaction, every record you fetch is guaranteed to be > good. > b) If you're not within a transaction, fetching any record will be > accurate UNLESS the next one to be fetched is deleted/updated. > If so, you'll get the old one. Otherwise, your results are > good. (Based on DB size, this caveat is quite rare.) > > Timing: > We have a system which continually reads records at a extremely > high rate (and then sends them up to a satellite). We need very high > throughput here. The following are times to fetch 375,000 records on > an HP-UX 10.20 workstation with 64Mb of RAM: > > Method Time(Sec) > Committed Read, No Transactions 143 > Repeatable Read, No Transactions 466 (226% longer) > Repeatable Read, with Transactions 440 (208% longer) > > I hope somebody finds this useful. > > > David > > David Samson, Senior Database Researcher dsamson@ndsisrael.com > NDS Technologies Israel Ltd. +972 2-589-4529 > PO Box 23012 Fax: +972 2-589-4578 > Jerusalem, Israel >