Re: Locking problem in INFORMIX ONLINE 7.20. UC2 -Reply

Thuc Nguyen wrote: > > What are the difference between lock mode row and lock mode page? As the name implies, lock mode row locks individual rows while lock mode page locks an entire page containing the row. Mode page uses fewer locks and tends to run faster. However, for high transaction rate, mulitiuser, databases lock mode page causes more lock contention and can actually slow total throughput as each user waits for some other user to release a page lock. > Should I create the tables with lock mode row? I am going to create If the application is interactive and multiuser favor lock mode row, if batch and single or few processes favor lock mode page. > database and build new schema on Online 7.20 in a few days. > > Since I upgraded to Online 7.20 from 5.03, Do I need to create the tables > with extent size next size in order to keep less than 8 extents? Does > Online 7.20 handle it automatically? On 5.03, I created each table with > extent size next size in the past. You know, that warning about >8 extents is only an informational message and 8 is not a magic number. The engineers wanted to warn you about tables becoming fragmented and had to pick a threshhold at which to begin warnings. I have tables that are 30GB in size and have 16 extents because the dbspace has 16 chunks and extents cannot span chunk boundaries. These perform just fine because a sequential scan can still scan 1 million pages before having to calculate an extent jump. On the other hand I just defragged a table with 6 fragments because 2 fragments were thousands of pages while the other four were only 100 pages each because performance on the table stank. The point is to limit the number of extents, yes, but only such that there are a relatively small number and each contains a significant number of rows. Online does not handle extents automatically except that contiguous extents in the same chunk are 'compressed' into a single extent. If you are reformatting a table you should declare 'extent size' larger enough to hold the entire current data set by running onstat/tbstat -pe and using an awk or perl script to summarize the extents (unless the table is bigger than a chunk in which case make an extent = chunksize - 80 pages [chunk overhead]) and 'next size' to ~8-12% of total space for small-medium tables expected to grow and ~1 months growth for large tables expected to grow. > infodsh@emirates.net.ae ("Infodata Ltd. (Sharjah) U.A.E.") writes: > >Dear friends, [SNIP] Art S. Kagel