Re: Dirty READ problem, with several readers.

In article <CHMFIo.6II@cbnewsh.cb.att.com>, ijk@cbnewsh.cb.att.com (ihor.j.kinal) writes: [That's me - I found the problem!!!] ... > > Here are the symptoms: > 1). The backends start accumulating CPU time at a higher rate > than normal - yet they do NOT consume all the available CPU. > > 2). The front-ends are stuck with the following back-trace > [confirmed both by core-dumping the processes involved, and by > actively attaching with xdb the running proc]. > > 0 _sqpread + 0x00000084 (0, 0x40011344, 0x2, 0x2) > 1 _sqgetint + 0x00000060 (0, 0, 0x2, 0x2) > 2 _sqgetreturn + 0x000000c8 (0x400016b8, 0x68faa814, 0x40041e30, 0xffffffeb) > 3 _iqopen + 0x0000045c (0x4000a740, 0x4, 0x1def3, 0x40011308) > > Of course, no CPU time is being consumed by the front-ends, even though > they have work to do. > > > VERSIONS: HP 9000/870 on HP-UX 8.0; Informix 4.11.UC1 Online, with > 2.10 ESQL. > After more investigation, I brought the system down, and checked the indices for possible corruption. I brought the system back up, and discovered another server having this problem, with the front-end doing VERY little work [note the difference], but the backend doing LOTS up reads. Not being able to determine what the backend was doing [sidenote to Informix - this begs for an enhancement - see end], I said to myself: what's the optimizer doing? The indices are good, so could the statistics be out of whack? We run an update statistics every morning. To make a long story short, this weekend we had done updates to the db, with some MINOR alters to several tables [inserting a couple fields here & there]. As a side-effect, the update statistics did not run. Once I realized that, I ran update statistics manually, and everything then went back to normal. **************************************************************** * THE MORAL: If you alter you tables in any way, it might * * be prudent to update statistics on that table before you * * bring your system back up. If this was in the documentation * * then I certainly missed it [what documentation???] * **************************************************************** ~WARM FUZZY: I certainly am NOT happy with the way the performance can change with the statistics. There should be some way to lock queries in. ENHANCEMENT REQUEST: There should be a way to request the backend to tell us what query it's pursuing, what transaction consistency it's in, etc [i.e.. the output of set explain, for a start]. Say, use SIGUSR1. That sure would have made this problem a LOT easier to resolve. Standard disclaimers apply - I hope this can save other people some undergoing some pain. Ihor Kinal att!trumpet!ijk