Re: Optimizing Informix IO

In article <6grs9h$559$1@gte2.gte.net>, Patrick Dwyer <paddydd@gte.net> writes > >Folks, > Here's some info about this situation. > >Ray Garrison <apsnet@ix.netcom.com> wrote in article ><6gdq0q$58q@sjx-ixn6.ix.netcom.com>... >> In article <6gde5t$g4t$1@news.xmission.com>, >> "Narkinsky, Pat" <Pat.Narkinsky@rivhs.com> wrote: >> >> > >> >System Manifest: >> >INFORMIX-OnLine Version 7.14.UD1 > Hardware = CPUs : ?? Memory : 8Gb, is this all available for Online? What else is running on the machine? Disks : 40x2Gb. OS : HP-UX ?? Online : 7.14.UD1, what about 7.24? Onconfig: Can you send us it? Disk layout: How do chunks and dbspaces correspond to physical disks? What does onstat -D give? SQL Optimization: In Onconfig is OPTCOMPIND set to 0? Run onstat -u, for each user with reads > 1000 Run onstat -g sql several times to find a select statement which is likely to be slow i.e. not a select on a small table. Go into dbaccess and run SET EXPLAIN ON; < the sql statement> and break out of the sql after a few seconds. Look in the sqexplain.out file produced. Are the write indexes being used? Avoid sequential scans and Dynamic Hash Joins on large tables. Network optimisation: Does most users connect via client/server PC-based apps or 4gl and esql/c based shared memory connections? Finally what does onstat -g ath give? >> >Second question: I've heard mention on this list to the effect "Informix >> >doesn't like RAID". Could someone clue me in on the considerations >> >here? >> > >remember the cost of > RAID 5, between 15 and 20% on writes compared to RAID 0. > This is the big problem with RAID, RAID 5 and 15-20% seems low, remember the parity drive gets accessed with EVERY write and hence maximimum I/O rate depends upon the speed of this drive i.e. one drive becomes a bottleneck. >> >Third: I've got 8GB of memory, however I am only using approximately >> >1.5GB. Would it be possible/advisable to try to get the engine to use >> >more of that memory? Will this make my checkpoints last too long? > Yes and yes but hopefully not that slow. Keep LRU_MIN_DIRTY=1, LRU_MAX_DIRTY=2 (or even 0 and 1). Basically try increasing buffers. >> >Fourth: One of the busiest dbspaces on the system is "tempdb1" >> >(according to 'onperf'). I have the following line in my onconfig file: >> > DBSPACETEMP tempdb1:tempdb1:tempdb2 > > It shouldn't be listed twice. Informix is probably using it twice. It >might not hurt to have more > temp dbspaces. Another question to ask is why is this OLTP app using >temp space. Can we > help it out with an index? > Listed each dbspace only once. With 40 disks I would proably have 10 dbspaces. Make sure in onstat -d that they are listed with 'T' against them i.e. they were created with Temp=Y. >> >What is the significance of the double listing for tempdb1? Is this >> >probably intentional, or is it the result of a typo on the part of my >> >vendor? Would this account for the fact that tempdb1 is MUCH busier >> >than tempdb2? Should I change it? > Yes. Yes and Yes, damn idiots! >> Question the First -> under HP/UX when I bounce the engine with KAIO >> enabled, I get this written to the online log: >> >> DR: DRAUTO is 0 (Off) >> HPUX Version B.11.0 -> Using select based KAIO >> INFORMIX-OnLine Initialized -- Shared Memory Initialized >> Then KAIO is being used. >> Question the Second -> (again HP) we tried using an HP model 20 array >> with RAID 5. Performance was sooooo slooooow we yelled at HP so loudly >> they took back the array and credited full amount towards purchase of >> a bunch of small disks. Informix much happier with 40 2Gig disks than >> it was with one 80 Gig array. >> True. Independent disks are better from a performance standpoint. What happens if one disk fails though? Does the system keep going without any errors? >> Question the Third -> I've never been able to figure out how adding more >> memory speeds things up, as long as you're not swapping and have plenty >> of buffers. I see no difference on our box between 1 Gig RAM and 2 Gig >RAM. >> It doesn't. Most people either do not have enough buffers or are swapping. However with PDQPRIOIRTY >0, there is a complex undocumented calculation where I think memory is involved used to determine how many threads are created to satisfy a query. More memory = more threads per session. With 8Gb of RAM I suspect fragmentation and PDQPRIORITY=1 should be used but we'll come to that later after some basic tuning has been done. >> Question the Fourth -> Cool! Never thought of specifying the same temp >> space twice on the DBSPACETEMP line. Am guessing Informix treats this as >> it would three temp spaces, simply uses the same dbspace for the first >> two temps. May be way to maximize leverage when have some fast and some This is not documented. Informix expects different dpspaces to be listed. Anything else could have bad effects and may even cause problems..( BAD VENDOR :->>). >> slow disks - specify each fast disk twice, each slow disk once. Have to >> try this... >> Don't, keep them distinct, I'd hate to see you bitten by say a future bug where data corruption and/or engin crashes occur. Only follow documented paths... > >> Hope some of this is useful... >> >> Ray Garrison > > >PaddyDD An onstat -a would help, just remove the big section with lots of repetition, I think it is the part about buffers and their usage.. -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care