Random problems with performance in IDS 10 TC6

Hi, At my company we recently upgraded to IDS 10 TC6 and have been running it since march this year. Lately we've been experiencing some major problems with performance running certain stored procedures. The problem does not occur every time we run the SP. It does some quite randomly, at least we haven't been able to distinguish a pattern for the performance loss. For example; A SP that almost every day take around 5 - 10 minutes some days run for 2-3 hours. The SP does basically the same thing everyday. It scans through an indexed table for changed values and if so updates or inserts rows in another table. Each table is in a separate DBSpace and these in turn on separate disks. We've of course been running update statistics regularly, we actually run it every night. Buffered writes and reads are > 98% before and after running the SP. There are no sequential scans. There's not a lot bufwaits and normally no other processes are running on the server (Windows 2003 server). We've run basically every oncheck command there is to check the consistency of the tables in the database and all seems fine. I should probably also mention that the B-Tree Scanner is turned off during execution of the SP. Has anyone had similar problems? Any ideas on what to look for or check?

kemahe@gmail.com wrote: > Has anyone had similar problems? Any ideas on what to look for or > check? > Post the output from: onstat -C onstat -C hot onstat -C all If the last one is too big, email it to me. Also: onstat -c onstat -p onstat -D Does a slow SP run slow for an entire day and then the next day run fine? Or does the speed vary within a day? -- Cheers, Obnoxio the Clown http://obotheclown.blogspot.com

kemahe@gmail.com wrote: > Hi, > > At my company we recently upgraded to IDS 10 TC6 and have been running > it since march this year. Lately we've been experiencing some major > problems with performance running certain stored procedures. The > problem does not occur every time we run the SP. It does some quite > randomly, at least we haven't been able to distinguish a pattern for > the performance loss. > > For example; A SP that almost every day take around 5 - 10 minutes > some days run for 2-3 hours. The SP does basically the same thing > everyday. It scans through an indexed table for changed values and if > so updates or inserts rows in another table. Each table is in a > separate DBSpace and these in turn on separate disks. > > We've of course been running update statistics regularly, we actually > run it every night. Buffered writes and reads are > 98% before and > after running the SP. There are no sequential scans. There's not a lot > bufwaits and normally no other processes are running on the server > (Windows 2003 server). We've run basically every oncheck command there > is to check the consistency of the tables in the database and all > seems fine. I should probably also mention that the B-Tree Scanner is > turned off during execution of the SP. > > Has anyone had similar problems? Any ideas on what to look for or > check? Lock waiting? How is the server load when you have issues? Can you alter the procedures or in any other way collect info about the work done? (maybe the data changes?) Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

On 3 Sep, 21:35, kem...@gmail.com wrote: > Hi, > > At my company we recently upgraded to IDS 10 TC6 and have been running > it since march this year. Lately we've been experiencing some major > problems with performance running certain stored procedures. The > problem does not occur every time we run the SP. It does some quite > randomly, at least we haven't been able to distinguish a pattern for > the performance loss. > > For example; A SP that almost every day take around 5 - 10 minutes > some days run for 2-3 hours. The SP does basically the same thing > everyday. It scans through an indexed table for changed values and if > so updates or inserts rows in another table. Each table is in a > separate DBSpace and these in turn on separate disks. > > We've of course been running update statistics regularly, we actually > run it every night. Buffered writes and reads are > 98% before and > after running the SP. There are no sequential scans. There's not a lot > bufwaits and normally no other processes are running on the server > (Windows 2003 server). We've run basically every oncheck command there > is to check the consistency of the tables in the database and all > seems fine. I should probably also mention that the B-Tree Scanner is > turned off during execution of the SP. > > Has anyone had similar problems? Any ideas on what to look for or > check? Well query sysmaster:syssessions. Check i/o response times on the disks. Check what else is using CPU. Check if the machine is swapping/paging. Are you a using a SAN and is a bottleneck on the SAN or particular disks/arrays/controllers/SAN switches..

On 3 Sep, 22:48, Obnoxio The Clown <obno...@serendipita.com> wrote: > kem...@gmail.com wrote: > > Has anyone had similar problems? Any ideas on what to look for or > > check? > > Post the output from: > > onstat -C > onstat -C hot > onstat -C all> > If the last one is too big, email it to me. > > Also: > onstat -c > onstat -p > onstat -D> > Does a slow SP run slow for an entire day and then the next day run > fine? Or does the speed vary within a day? > > -- > Cheers, > Obnoxio the Clown > > http://obotheclown.blogspot.com Hey! Outputs below. I'll email onstat -C all and onstat -c all to you. Please note that we run the SP with B-Tree turned off (it's run in a nightly batch and not turned back on until the end of the batch). The speed varys between days (batches), I guess this could be the answer to both questions, we only run this SP once a day. However, we've not yet been able to recreate the problems on any of our test servers after they are restored from a backup taken just before the SP was run. onstat -C ======= IBM Informix Dynamic Server Version 10.00.TC6X2 -- On-Line (Prim) -- Up 2 days 21:28:22 -- 1802432 Kbytes Btree Cleaner Info BT scanner profile Information ============================== Active Threads 1 Global Commands 2000000 Building hot list Number of partition scans 349 Main Block 0x50B9D1D0 BTC Admin 0x506B6870 BTS info id Prio Partnum Key Cmd 0x508CD718 0 High 0x00000000 0 40 Yield N Number of leaves pages scanned 5947252 Number of leaves with deleted items 75164 Time spent cleaning (sec) 1270 Number of index compresses 5137 Number of deleted items 934384 Number of index range scans 8 Number of index leaf scans 18 Number of index alice scans 0 onstat -C hot ========== IBM Informix Dynamic Server Version 10.00.TC6X2 -- On-Line (Prim) -- Up 2 days 21:31:38 -- 1802432 Kbytes Btree Cleaner Info Index Hot List ============== Current Item 14 List Created 07:43:37 List Size 13 List expires in 0 sec Hit Threshold 50000 Range Scan Threshold 5000 Partnum Key Hits 0x00500007 5 46106 * 0x004000D0 2 44789 * 0x004000D4 4 44498 * 0x0040014A 3 43002 * 0x0040014A 1 40012 * 0x00500007 1 37058 * 0x004000D0 6 32976 * 0x004000D0 4 29483 * 0x004000D4 3 28180 * 0x00500007 4 27884 * 0x00500007 3 26994 * 0x00600006 4 26263 * 0x004000D0 3 25515 * onstat -D ======= IBM Informix Dynamic Server Version 10.00.TC6X2 -- On-Line (Prim) -- Up 2 days 21:40:11 -- 1802432 Kbytes Dbspaces address number flags fchunk nchunks pgsize flags owner name 5054B7E8 1 0x40001 1 1 4096 N B informix rootdbs 50BA5198 2 0x40001 2 1 4096 N B informix physdbs 50BA52F8 3 0x40001 3 1 4096 N B informix logdbs 50BA5458 4 0x41001 4 2 4096 N B informix primedbs1 50BA55B8 5 0x40001 5 2 4096 N B informix primedbs2 50BA5718 6 0x40001 6 2 4096 N B informix primedbs3 50BA5878 7 0x40001 7 2 4096 N B informix primedbs4 50BA59D8 8 0x40001 8 1 4096 N B informix archdbs1 50BA5B38 9 0x40001 9 2 4096 N B informix primedbs5 50BA5C98 10 0x42001 10 1 4096 N TB informix tempdbs1 50BA5DF8 11 0x42001 11 1 4096 N TB informix tempdbs2 11 active, 2047 maximum Chunks address chunk/dbs offset page Rd page Wr pathname 5054B948 1 1 0 20364 1507 F:\\IFMXDATA\\prime \\rootdbs_dat.000 50B8BB28 2 2 0 6 729718 f:\\ifmxdata\\prime \\physlogdbs_dat.000 50B8BCA8 3 3 0 124530 125000 g:\\ifmxdata\\prime \\logdbs_dat.000 50B8BE28 4 4 0 16290743 253921 h:\\ifmxdata\\prime \\primedbs1_dat.000 50B8AE28 5 5 0 16600525 411022 i:\\ifmxdata\\prime \\primedbs2_dat.000 50BA4018 6 6 0 11590170 7306 j:\\ifmxdata\\prime \\primedbs3_dat.000 50BA4198 7 7 0 32840154 15452 k:\\ifmxdata\\prime \\primedbs4_dat.000 50BA4318 8 8 0 6 0 m:\\ifmxdata\\prime \\archdbs1_dat.000 50BA4498 9 9 0 5384482 12591 n:\\ifmxdata\\prime \\primedbs5_dat.000 50BA4618 10 10 0 7732471 1835877 o:\\ifmxdata\\prime \\tempdbs1_dat.000 50BA4798 11 11 0 6813855 1781142 p:\\ifmxdata\\prime \\tempdbs2_dat.000 50BA4918 12 5 0 1326958 30212 i:\\ifmxdata\\prime \\primedbs2_dat.001 50BA4A98 13 4 0 2 0 h:\\ifmxdata\\prime \\primedbs1_dat.001 50BA4C18 14 6 0 2 0 j:\\ifmxdata\\prime \\primedbs3_dat.001 50BA4D98 15 7 0 2193540 3432 k:\\ifmxdata\\prime \\primedbs4_dat.001 50BA5018 16 9 0 2 0 n:\\ifmxdata\\prime \\primedbs5_dat.001 16 active, 32766 maximum NOTE: The values in the "page Rd" and "page Wr" columns for DBspace chunks are displayed in terms of system base page size. Expanded chunk capacity mode: always onstat -p ======= IBM Informix Dynamic Server Version 10.00.TC6X2 -- On-Line (Prim) -- Up 2 days 21:40:02 -- 1802432 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 49808235 100916890 3784405791 98.69 1777896 5207108 18938689 94.06 isamtot open start read write rewrite delete commit rollbk 3514631658 11435379 57040158 3320968071 10310344 2563206 97468 290528 21 gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs 0 0 0 0 0 0 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 17880.52 4242.35 116 232 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 3046751 70 599997001 0 0 37 186282 177351 ixda-RA idx-RA da-RA RA-pgsused lchwaits 3832565 5807564 20262952 29700485 69997

What does partnum 0x00700008 refer to? For TBP: 0x00700008 1 18146 91468 -1211678335 4475 33708.9 Also, can you "onmode -C threshold 500000"? -- Cheers, Obnoxio the Clown http://obotheclown.blogspot.com

On Sep 4, 9:29 am, Obnoxio The Clown <obno...@serendipita.com> wrote: > What does partnum 0x00700008 refer to? For TBP: > > 0x00700008  1           18146       91468 -1211678335        4475   33708.9 > > Also, can you "onmode -C threshold 500000"? > > -- > Cheers, > Obnoxio the Clown > > http://obotheclown.blogspot.com This is an index on one of our larger tables. 362194729 rows. TBLspace Report for Unknown:Unknown.700008 ISAM error: no record found. Physical Address 7:11 Creation date 03/15/2008 17:47:23 TBLspace Flags 802 Row Locking TBLspace use 4 bit bit- maps Maximum row size 586 Number of special columns 0 Number of keys 1 Number of extents 10 Current serial value 1 Pagesize (k) 4 First extent size 116579 Next extent size 11023 Number of pages allocated 557499 Number of pages used 555474 Number of data pages 0 Number of rows 0 Partition partnum 7340040 Partition lockid 7340036 Extents Logical Page Physical Page Size Physical Pages 0 7:9556710 458292 458292 458292 7:11633453 11023 11023 469315 7:12161315 11023 11023 480338 7:12704583 11023 11023 491361 7:12755096 11023 11023 502384 7:13405609 11023 11023 513407 7:13433492 11023 11023 524430 15:8433 11023 11023 535453 15:101991 11023 11023 546476 15:641479 11023 11023

Obnoxio The Clown wrote: > What does partnum 0x00700008 refer to? For TBP: > > 0x00700008 1 18146 91468 -1211678335 4475 33708.9 > > Also, can you "onmode -C threshold 500000"? > Errr ... yes, suggest onmode -C threshold 500000 :o)

On Sep 4, 10:13 am, TBP <th...@Usenet-News.Net> wrote: > Obnoxio The Clown wrote: > > What does partnum 0x00700008 refer to? For TBP: > > > 0x00700008  1           18146       91468 -1211678335        4475   33708.9 > > > Also, can you "onmode -C threshold 500000"? > > Errr ... yes, suggest onmode -C threshold 500000 :o) Yeah, that would seem like a good idea but can this really be a big factor in the problems we're having since the scanners isn't being run during execution of the SP?

TBP wrote: > Obnoxio The Clown wrote: > >> What does partnum 0x00700008 refer to? For TBP: >> >> 0x00700008 1 18146 91468 -1211678335 4475 33708.9 >> >> Also, can you "onmode -C threshold 500000"? >> >> > > Errr ... yes, suggest onmode -C threshold 500000 :o) > I do listen, you know! Sometimes. :o) -- Cheers, Obnoxio the Clown http://obotheclown.blogspot.com

kemahe@gmail.com wrote: > On Sep 4, 10:13 am, TBP <th...@Usenet-News.Net> wrote: > >> Obnoxio The Clown wrote: >> >>> What does partnum 0x00700008 refer to? For TBP: >>> >>> 0x00700008 1 18146 91468 -1211678335 4475 33708.9 >>> >>> Also, can you "onmode -C threshold 500000"? >>> >> Errr ... yes, suggest onmode -C threshold 500000 :o) >> > > Yeah, that would seem like a good idea but can this really be a big > factor in the problems we're having since the scanners isn't being run > during execution of the SP? > > You have, in the course of less than three days, scanned 3 billion pages (not sure of the math here, but it's a forking lot) by the B-Tree scanner. You tell me. :o) -- Cheers, Obnoxio the Clown http://obotheclown.blogspot.com