Re: I/O bottleneck, bufwaits too high
"Hal Maner" <hmaner@msystemsintl.com> wrote in message news:8ma9dc$eve1@www.informix.com... > Just out of curiosity ::) > > How many rows are there in the t_prm table? Around 526,000. > What are the indexes (and key columns) in the t_prm table? No indexes except for the PK column, which is emp_id. Same deal on evs_tape. > > Hal Maner > M Systems International, Inc. > www.msystemsintl.com > > Jeff Glenn <jglenn@nospam.ep-services.com> wrote in message > news:Vf%h5.34$mD6.38062@news.pacbell.net... > > First the environment stuff: > > IBM NUMA-Q (Sequent), 8 processors > > DYNIX/ptx 4.4.7 > > 8GB RAM > > IDS 9.20.UC1 <snip>
Jeff, Is the box running Informix a dedicated db server? If so, wouldn't it be better to use ALL the processors for Informix (increasing CPUVP). Or maybe, just add a few more. I wouldn't worry about the ever-increasing buffers not flushed. That's the whole point of the fuzzy checkpoint, to avoid flushing a buffer multiple times. One other item that you might try (using explain to see if the path is any better) is to replace your where IN clause with an EXISTS clause, as follows: WHERE EXISTS (SELECT * FROM t_prm where t_prm.emp_id = evs_tape.emp_id). Also wondering if OPTCOMPIND should be 0. Again, only explain can tell you. If you leave it at 2, I'm wondering if you need to increase SHMVIRT so that there is enough memory to adequately handle a hash join that might be being implemented. Just some off the wall, early morning guesses. Doug "Jeff Glenn" <jglenn@nospam.ep-services.com> wrote in message news:xK3i5.69$mD6.77072@news.pacbell.net... > > "Hal Maner" <hmaner@msystemsintl.com> wrote in message > news:8ma9dc$eve1@www.informix.com... > > Just out of curiosity ::) > > > > How many rows are there in the t_prm table? > > Around 526,000. > > > > What are the indexes (and key columns) in the t_prm table? > > No indexes except for the PK column, which is emp_id. Same deal on evs_tape. > > > > > Hal Maner > > M Systems International, Inc. > > www.msystemsintl.com > > > > Jeff Glenn <jglenn@nospam.ep-services.com> wrote in message > > news:Vf%h5.34$mD6.38062@news.pacbell.net... > > > First the environment stuff: > > > IBM NUMA-Q (Sequent), 8 processors > > > DYNIX/ptx 4.4.7 > > > 8GB RAM > > > IDS 9.20.UC1 > > <snip> > > >