How to check informix DB bottlenecks

Hi All, How to check the informix tables for bottlenecks? i am having problem our database and the user are explaining that it is a DB problem. I verified the logs and there's no valid error on the log file... I've used onstat -g sql, onstat -g ses, onstat -g lmx, onstat -k for locks. Any suggestion please?

Hi, please provide a little bit more information: Informix version, OS type/version, size of hardware (cpu/memory/disk), size and type of application (OLTP or DWH ? number of concurrent users, size of largest used tables). First you should have a look at operating system indicators (CPU usage, runqueue, disk io, memory usage/swap usage) . On DB side first overview: onstat -p You might further look into sysmaster:syssesprof for sessions with much buffer reads (cpu) or page reads (io) . and into sysmaster:sysptprof for tables/indexes with high usage. Another may (if not to many users) run some onstat -g sql 0And look for long running SQLs. onstat -u can provide information about waiting sessions and wait reasons (locks). Regards, Andreas Kutsche > ------------------------------------------- SPAR Österreichische Warenhandels-AG Hauptzentrale A - 5015 Salzburg, Europastrasse 3 FN 34170 a Tel: +43 662 4470 84923 Fax: Mobile: +43 664 6259575 E-Mail: Andreas.KUTSCHE@spar.at Internet: http://www.spar.at Wichtiger Hinweis: Der Inhalt dieser E-Mail kann vertrauliche und rechtlich geschützte Informationen, insbesondere Betriebs- oder Geschäftsgeheimnisse, enthalten, zu deren Geheimhaltung der Empfänger verpflichtet ist. Die Informationen in dieser E-Mail sind ausschließlich für den Adressaten bestimmt. Sollten Sie die E-Mail irrtümlich erhalten haben so ersuchen wir Sie, die Nachricht von Ihrem System zu löschen und sich mit uns in Verbindung zu setzen. Über das Internet versandte E-Mails können leicht manipuliert oder unter fremdem Namen erstellt werden. Daher schließen wir die rechtliche Verbindlichkeit der in dieser Nachricht enthaltenen Informationen aus. Der Inhalt der E-Mail ist nur rechtsverbindlich, wenn er von uns schriftlich bestätigt und gezeichnet wird. Sollte trotz der von uns verwendeten Virus-Schutzprogramme durch die Zusendung von E-Mails ein Virus in Ihre Systeme gelangen, haften wir nicht für evtl. hieraus entstehende Schäden. Wir danken für Ihr Verständnis. Important notice: The contents of this e-mail may contain confidential and legally protected information that is in particular related to operational and trade secrets, which the recipient is obliged to treat as confidential. The information in this e-mail is made available exclusively for use by the addressee. In the event that the e-mail may have been sent to you in error, we would ask you to kindly delete this communication from your system and to contact us. E-mails sent via the Internet can be easily manipulated or sent out under someone else's name. We therefore do not accept legal liability for the information contained in this communication. The contents of the e-mail are only legally binding if they have been confirmed and signed by us in writing. If, in spite of our using Antivirus protection software, a virus may have penetrated your system through the sending of this e-mail, we do not accept liability for any damage that may possibly arise as a result of this. We trust that you appreciate our position. ------------------------------------------- -----Ursprüngliche Nachricht----- > Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Im Auftrag von > JACK PAPA > Gesendet: Dienstag, 2. März 2010 08:22 > An: ids@iiug.org > Betreff: How to check informix DB bottlenecks [19148] > > Hi All, > > How to check the informix tables for bottlenecks? i am having problem our > database and the user are explaining that it is a DB problem. I verified > the > logs and there's no valid error on the log file... > > I've used onstat -g sql, onstat -g ses, onstat -g lmx, onstat -k for > locks. > Any suggestion please? > > > ************************************************************************** > ***** > Forum Note: Use "Reply" to post a response in the discussion forum.

I am using IDS 9.4, AIX 5.4. onstat -p shows => onstat -p IBM Informix Dynamic Server Version 9.40.UC6 -- On-Line (Prim) -- Up 23 days 15:06:08 -- 2251504 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 1220612817 3198664750 2251555606 45.79 34109654 100095460 2062133360 98.35 isamtot open start read write rewrite delete commit rollbk 2936740675 208936934 1093068317 2477580096 1270145450 25498127 26444677 3868723 1868 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 723695.11 86056.10 8699 17400 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 51876197 7247 1280409663 57 0 12432 6247517 15715161 ixda-RA idx-RA da-RA RA-pgsused lchwaits 85766244 103708265 208434932 366985387 3557149 ------------------------- This is the onstat -u output. 90291c30 ---P--- 12 informix - 0 0 0 0 34 90292248 ---P--B 13 informix - 0 0 0 392183594 777296 90292860 Y--P--D 15 informix - 908b4540 0 0 30027 8701 90292e78 ---P--D 35290 informix - 0 0 0 0 0 90293490 ---P--D 35289 informix - 0 0 0 0 0 902946d8 ---P--D 20 informix - 0 0 0 0 8 90295308 Y--P--- 25 patrol - 913b0a18 0 16 54299002 1142837 90295920 Y--P--D 26 informix - 4008f12c 0 0 0 0 90296550 Y-AP--M 106804 informix - 93b6a888 0 0 29 24 90297180 Y--P--- 106500 user1 0 93968f18 0 1 6 79 902983c8 Y--P--- 105021 user1 Server01 984162a0 0 1 396 285 90299c28 Y--P--- 102768 user1 Server01 936296f8 0 1 0 151 9029a240 Y--P--- 76613 user001 - 92c7ba18 0 2 808003 0 9029eb60 Y--P--- 76570 user001 - 93b51d38 0 1 1717 588 902a1c20 Y--P--- 103621 user1 Server01 931bb798 0 1 0 12 902a2e68 Y--P--- 106557 user001 - 9242afb8 0 1 51 0 902a6540 Y-AP--M 106805 informix - 96892ba8 0 0 26 24 902a7da0 Y--P--- 2788 user1 0 94821068 0 1 7706567 1542817 902a89d0 Y--P--- 2789 user1 0 9397b158 0 1 1552 57902 902a9600 Y--P--- 2776 user1 Server04 9398e068 0 1 11094 125827 93302018 Y--P--- 2781 user1 Server04 9155d9e0 0 1 0 0 933044a8 Y--P--- 20454 user1 0 931a5fc8 0 2 39585 2843 93304ac0 Y--P--- 105783 user1 Server01 937422e8 0 1 1262 674 93306320 Y--P--- 106798 user1 0 93bb4b58 0 1 2 95 93307568 Y--P--- 103615 user1 Server01 93a55fb8 0 1 299 217 93307b80 Y--P--- 106131 user1 Server01 9364ed88 0 1 69 1482 9330a010 Y--P--- 105251 user1 0 936ad608 0 1 1 1 9330b870 Y--P--- 106481 user1 Server01 9c11d658 0 1 0 40 9330d6e8 Y--P--- 106384 user1 Server01 92cb7ce0 0 1 2811 2612 9330f560 Y--P--- 106748 user1 Server01 93aa03d8 0 1 0 34 9330fb78 Y--P--- 106795 user1 0 930b1bf8 0 4 10345 809 93312620 Y-AP--M 106803 informix - 92139d38 0 0 27 24 93313e80 Y--P--- 106540 user001 - 939b39c8 0 1 67 0 933150c8 Y--P--- 99281 user1 0 9efd4478 0 1 1 17 933156e0 Y--P--- 99279 user1 0 9c9158d8 0 2 1 1 93315cf8 Y--P--- 106547 user001 - 91c43428 0 1 623 588 93316928 Y--P--- 104952 user1 Server01 9c012108 0 1 808 670 93316f40 Y--P--- 21824 user1 Server02 93943928 0 1 0 0 93317b70 Y--P--- 70141 user002 - 935cbf68 0 1 61964 4102 933193d0 Y--P--- 104992 user1 0 9378f798 0 1 0 1 9331ac30 Y--P--- 101838 user1 Server01 91271338 0 1 26147 7592 9331c490 ------- 106803 informix - 0 0 0 0 0 9331caa8 Y--P--- 101997 user1 Server01 92de41f8 0 1 2836 2608 9331e920 Y--P--- 41847 user1 0 93876e28 0 4 46627 3466 9331ef38 ------- 106807 informix - 0 0 0 298 0 933219e0 Y--P--- 106639 user1 0 9377a1a8 0 1 5 3 93321ff8 ------- 106812 informix - 0 0 0 298 0 93322610 Y--P--- 104990 user1 0 937a94c8 0 2 5 1 93322c28 Y--P--- 102590 user1 Server01 93c14298 0 1 0 0 93323e70 Y--P--- 105250 user1 Server68 938be978 0 1 0 0 93324aa0 Y--P--- 103041 user1 0 93884608 0 1 304442 75051 933250b8 Y--P--- 106136 user001 - 92de4b58 0 2 44 0 93326300 Y--P--- 104816 user1 Server01 9afb0428 0 1 829 205 9332c480 ------- 106811 informix - 0 0 0 0 0 9332d0b0 Y--P--- 76578 user001 - 9f9f7338 0 1 785 588 93330788 Y--P--- 106711 user1 0 9369a518 0 1 9 26 97e90e90 Y--P--- 106556 user001 - 93b51338 0 1 629 588 97e92d08 Y--P--- 103382 user1 0 939d8338 0 3 6956 226 97e93938 Y-AP--M 106812 informix - 93b37658 0 0 26 24 97e94568 Y--P--- 106546 user001 - 98496568 0 1 6 0 97e95dc8 Y--P--- 106210 user1 Server01 9c333ab8 0 1 0 43 97e98e88 Y--P--- 101889 user1 Server01 93a235b8 0 1 137 178 97e9b930 Y--P--- 100041 user1 0 937d4d88 0 1 5691 109 97e9cb78 Y--P--- 105010 user001 - 93848248 0 1 3934 1708 97e9d190 Y--P--- 104993 user1 Server69 93943978 0 1 0 0 97e9d7a8 Y--P--- 105244 user1 0 93867518 0 2 3 1 97e9e3d8 Y--P--- 76573 user001 - 936f7e78 0 1 76 0 97e9e9f0 Y--P--- 106582 user1 0 9f124a68 0 1 6 4 97ea0868 Y-AP--M 106808 informix - 93ab33d8 0 0 27 24 97ea26e0 ------- 106804 informix - 0 0 0 5 0 97ea3310 Y--P--- 70711 user003 Server12 93593dd8 0 1 18318 0 97ea3928 Y--P--- 106548 user001 - 93c6d838 0 1 12 0 97ea3f40 Y-AP--M 106807 informix - 930e0018 0 0 27 24 97ea5db8 Y--P--- 106209 user1 Server01 93c01068 0 1 181 185 97ea63d0 Y-AP--M 106811 informix - 9242a158 0 0 27 24 97eaa0c0 Y--P--- 20457 user1 0 93a11888 0 1 259723 1105 97eab308 Y-AP--M 106810 informix - 9b9b3298 0 0 27 24 97eab920 Y--P--- 104953 user1 Server01 937e3978 0 1 0 51 97eac550 Y--P--- 106560 user1 0 9171be28 0 2 247 137 97eae3c8 Y--P--- 103642 user1 Server01 9d62dc98 0 1 669 408 97eb0e70 Y--P--- 106387 user1 Server01 93c5fbf8 0 1 0 54 97eb1488 ------- 106806 informix - 0 0 0 304 0 97eb2ce8 ------- 106804 informix - 0 0 0 0 0 97eb3300 Y--P--- 103643 user1 Server01 93a11518 0 1 0 2 97eb7608 ------- 106808 informix - 0 0 0 298 0 97eba6c8 Y--P--- 101797 user1 Server01 939a0ce8 0 1 0 296 97ebbf28 Y--P--- 106545 user001 - 935de608 0 1 624 588 97ebd788 Y--P--- 76610 user001 - 93c27478 0 1 8037 2268 97ebefe8 ------- 106811 informix - 0 0 0 298 0 99b19c48 Y--P--- 106552 user001 - 9f40e928 0 1 620 588 99b1ae90 Y--P--- 101839 user1 Server01 9381d798 0 1 0 192 99b1b4a8 Y--P--- 71661 user003 Server12 9f4459c8 0 1 27579 0 99b1d938 Y--P--- 106553 user001 - 93c396a8 0 1 63 0 99b1fdc8 ------- 106806 informix - 0 0 0 0 96 99b21628 Y--P--- 104918 user001 - 936adce8 0 1 3964 1148 99b21c40 Y--P--- 102962 user1 0 93857928 0 1 61 3672 99b25318 Y--P--- 99276 user1 Server96 937b8388 0 1 0 0 99b25930 Y--P--- 86450 user1 0 9398e478 0 1 68882 251 99b26b78 Y--P--- 106369 user1 Server01 93aa0e78 0 1 0 0 99b277a8 Y--P--- 101892 user1 Server01 933980b8 0 1 0 0 99b27dc0 Y--P--- 82213 user001 - 9f545018 0 2 6172 0 99b283d8 Y--P--- 73755 user003 Server10 938393d8 0 1 16090 0 99b2a868 Y--P--- 105011 user001 - 9379b658 0 2 82569 0 99b2c0c8 Y--P--- 106791 user1 Server01 936e5338 0 1 48 122 99b2c6e0 ------- 106805 informix - 0 0 0 3 0 99b2e558 Y--P--- 103724 user1 Server01 9afb0928 0 1 56 27 99b2eb70 Y--P--- 105826 user1 0 930e0738 0 3 2059 198 99b2f188 Y--P--- 85998 user002 - 937c6bf8 0 1 25882 2218 99b309e8 Y--P--- 86447 user1 0 939d8658 0 2 26812 601 99b31000 Y--P--- 106561 user1 0 9377a478 0 1 6 4 99b32248 Y--P--- 102872 user1 Server05 936adb08 0 1 0 0 99b34cf0 Y--P--- 103725 user1 Server01 9c74ea18 0 1 0 6 99b35308 Y--P--- 106135 user001 - 9379bab8 0 1 2882 1148 99b35920 Y--P--- 106792 user1 Server01 9314c8d8 0 1 0 6 99b36b68 Y--P--- 102767 user1 Server01 935cb658 0 1 1789 990 99b37180 Y--P--- 104666 user1 Server01 9f8c04c

Hi, your onstat -p shows a buffer cache hit rate (%cached) of 45.79 . An OLTP system should show values above 90%. Maybe your buffer cache is way too small ? How many memory does your machine have ? How many is free ? What is the value of onconfig parameter BUFFERS ? You may have a look at the user sessions with high nread values: onstat -u | grep user | sort -n -k 9After that do indiviual Onstat -g sql <sid> or onstat -g ses <sid> with the session sid from column 3 for the last sessions. If the sessions are activ for a long time (and you don't reset statistics with onstat -z regularly) they show accumulated values. Bye Andreas > ------------------------------------------- SPAR Österreichische Warenhandels-AG Hauptzentrale A - 5015 Salzburg, Europastrasse 3 FN 34170 a Tel: +43 662 4470 84923 Fax: Mobile: +43 664 6259575 E-Mail: Andreas.KUTSCHE@spar.at Internet: http://www.spar.at Wichtiger Hinweis: Der Inhalt dieser E-Mail kann vertrauliche und rechtlich geschützte Informationen, insbesondere Betriebs- oder Geschäftsgeheimnisse, enthalten, zu deren Geheimhaltung der Empfänger verpflichtet ist. Die Informationen in dieser E-Mail sind ausschließlich für den Adressaten bestimmt. Sollten Sie die E-Mail irrtümlich erhalten haben so ersuchen wir Sie, die Nachricht von Ihrem System zu löschen und sich mit uns in Verbindung zu setzen. Über das Internet versandte E-Mails können leicht manipuliert oder unter fremdem Namen erstellt werden. Daher schließen wir die rechtliche Verbindlichkeit der in dieser Nachricht enthaltenen Informationen aus. Der Inhalt der E-Mail ist nur rechtsverbindlich, wenn er von uns schriftlich bestätigt und gezeichnet wird. Sollte trotz der von uns verwendeten Virus-Schutzprogramme durch die Zusendung von E-Mails ein Virus in Ihre Systeme gelangen, haften wir nicht für evtl. hieraus entstehende Schäden. Wir danken für Ihr Verständnis. Important notice: The contents of this e-mail may contain confidential and legally protected information that is in particular related to operational and trade secrets, which the recipient is obliged to treat as confidential. The information in this e-mail is made available exclusively for use by the addressee. In the event that the e-mail may have been sent to you in error, we would ask you to kindly delete this communication from your system and to contact us. E-mails sent via the Internet can be easily manipulated or sent out under someone else's name. We therefore do not accept legal liability for the information contained in this communication. The contents of the e-mail are only legally binding if they have been confirmed and signed by us in writing. If, in spite of our using Antivirus protection software, a virus may have penetrated your system through the sending of this e-mail, we do not accept liability for any damage that may possibly arise as a result of this. We trust that you appreciate our position. ------------------------------------------- -----Ursprüngliche Nachricht----- > Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Im Auftrag von > JACK PAPA > Gesendet: Dienstag, 2. März 2010 10:37 > An: ids@iiug.org > Betreff: Re: AW: How to check informix DB bottlenecks [19151] > > I am using IDS 9.4, AIX 5.4. > > onstat -p shows> > => onstat -p > > IBM Informix Dynamic Server Version 9.40.UC6 -- On-Line (Prim) -- Up 23> days > 15:06:08 -- 2251504 Kbytes > > Profile > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > 1220612817 3198664750 2251555606 45.79 34109654 100095460 2062133360 98.35 > > isamtot open start read write rewrite delete commit rollbk > 2936740675 208936934 1093068317 2477580096 1270145450 25498127 26444677 > 3868723 1868 > > 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 723695.11 86056.10 8699 17400 > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans > 51876197 7247 1280409663 57 0 12432 6247517 15715161 > > ixda-RA idx-RA da-RA RA-pgsused lchwaits > 85766244 103708265 208434932 366985387 3557149 > > ------------------------- > This is the onstat -u output. > > 90291c30 ---P--- 12 informix - 0 0 0 0 34 > 90292248 ---P--B 13 informix - 0 0 0 392183594 777296 > 90292860 Y--P--D 15 informix - 908b4540 0 0 30027 8701 > 90292e78 ---P--D 35290 informix - 0 0 0 0 0 > 90293490 ---P--D 35289 informix - 0 0 0 0 0 > 902946d8 ---P--D 20 informix - 0 0 0 0 8 > 90295308 Y--P--- 25 patrol - 913b0a18 0 16 54299002 1142837 > 90295920 Y--P--D 26 informix - 4008f12c 0 0 0 0 > 90296550 Y-AP--M 106804 informix - 93b6a888 0 0 29 24 > 90297180 Y--P--- 106500 user1 0 93968f18 0 1 6 79 > 902983c8 Y--P--- 105021 user1 Server01 984162a0 0 1 396 285 > 90299c28 Y--P--- 102768 user1 Server01 936296f8 0 1 0 151 > 9029a240 Y--P--- 76613 user001 - 92c7ba18 0 2 808003 0 > 9029eb60 Y--P--- 76570 user001 - 93b51d38 0 1 1717 588 > 902a1c20 Y--P--- 103621 user1 Server01 931bb798 0 1 0 12 > 902a2e68 Y--P--- 106557 user001 - 9242afb8 0 1 51 0 > 902a6540 Y-AP--M 106805 informix - 96892ba8 0 0 26 24 > 902a7da0 Y--P--- 2788 user1 0 94821068 0 1 7706567 1542817 > 902a89d0 Y--P--- 2789 user1 0 9397b158 0 1 1552 57902 > 902a9600 Y--P--- 2776 user1 Server04 9398e068 0 1 11094 125827 > 93302018 Y--P--- 2781 user1 Server04 9155d9e0 0 1 0 0 > 933044a8 Y--P--- 20454 user1 0 931a5fc8 0 2 39585 2843 > 93304ac0 Y--P--- 105783 user1 Server01 937422e8 0 1 1262 674 > 93306320 Y--P--- 106798 user1 0 93bb4b58 0 1 2 95 > 93307568 Y--P--- 103615 user1 Server01 93a55fb8 0 1 299 217 > 93307b80 Y--P--- 106131 user1 Server01 9364ed88 0 1 69 1482 > 9330a010 Y--P--- 105251 user1 0 936ad608 0 1 1 1 > 9330b870 Y--P--- 106481 user1 Server01 9c11d658 0 1 0 40 > 9330d6e8 Y--P--- 106384 user1 Server01 92cb7ce0 0 1 2811 2612 > 9330f560 Y--P--- 106748 user1 Server01 93aa03d8 0 1 0 34 > 9330fb78 Y--P--- 106795 user1 0 930b1bf8 0 4 10345 809 > 93312620 Y-AP--M 106803 informix - 92139d38 0 0 27 24 > 93313e80 Y--P--- 106540 user001 - 939b39c8 0 1 67 0 > 933150c8 Y--P--- 99281 user1 0 9efd4478 0 1 1 17 > 933156e0 Y--P--- 99279 user1 0 9c9158d8 0 2 1 1 > 93315cf8 Y--P--- 106547 user001 - 91c43428 0 1 623 588 > 93316928 Y--P--- 104952 user1 Server01 9c012108 0 1 808 670 > 93316f40 Y--P--- 21824 user1 Server02 93943928 0 1 0 0 > 93317b70 Y--P--- 70141 user002 - 935cbf68 0 1 61964 4102 > 933193d0 Y--P--- 104992 user1 0 9378f798 0 1 0 1 > 9331ac30 Y--P--- 101838 user1 Server01 91271338 0 1 26147 7592 > 9331c490 ------- 106803 informix - 0 0 0 0 0 > 9331caa8 Y--P--- 101997 user1 Server01 92de41f8 0 1 2836 2608 > 9331e920 Y--P--- 41847 user1 0 93876e28 0 4 46627 3466 > 9331ef38 ------- 106807 informix - 0 0 0 298 0 > 933219e0 Y--P--- 106639 user1 0 9377a1a8 0 1 5 3 > 93321ff8 ------- 106812 informix - 0 0 0 298 0 > 93322610 Y--P--- 104990 user1 0 937a94c8 0 2 5 1 > 93322c28 Y--P--- 102590 user1 Server01 93c14298 0 1 0 0 > 93323e70 Y--P--- 105250 user1 Server68 938be978 0 1 0 0 > 93324aa0 Y--P--- 103041 user1 0 93884608 0 1 304442 75051 > 933250b8 Y--P--- 106136 user001 - 92de4b58 0 2 44 0 > 93326300 Y--P--- 104816 us

A bit more specificity if you please. What behavior are they complaining about? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Mar 2, 2010 at 2:22 AM, JACK PAPA <informix2009@gmail.com> wrote: > Hi All, > > How to check the informix tables for bottlenecks? i am having problem our > database and the user are explaining that it is a DB problem. I verified > the > logs and there's no valid error on the log file... > > I've used onstat -g sql, onstat -g ses, onstat -g lmx, onstat -k for locks. > Any suggestion please? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001517479628b6bb330480cfdd7f

Haven't done the calculations formally, just eyeballing them, but your RAU is a bit low ~90% and you are seeing about 7% of your queries running sequential scans. The latter is a bit high and may indicate missing indexes or stale data distributions (or may just be that your queries hit lots of small lookup tables). What are your typical checkpoint times when someone's complaining? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Mar 2, 2010 at 4:37 AM, JACK PAPA <informix2009@gmail.com> wrote: > I am using IDS 9.4, AIX 5.4. > > onstat -p shows> > => onstat -p > > IBM Informix Dynamic Server Version 9.40.UC6 -- On-Line (Prim) -- Up 23> days > 15:06:08 -- 2251504 Kbytes > > Profile > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > 1220612817 3198664750 2251555606 45.79 34109654 100095460 2062133360 98.35 > > isamtot open start read write rewrite delete commit rollbk > 2936740675 208936934 1093068317 2477580096 1270145450 25498127 26444677 > 3868723 1868 > > 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 723695.11 86056.10 8699 17400 > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans > 51876197 7247 1280409663 57 0 12432 6247517 15715161 > > ixda-RA idx-RA da-RA RA-pgsused lchwaits > 85766244 103708265 208434932 366985387 3557149 > > ------------------------- > This is the onstat -u output. > > 90291c30 ---P--- 12 informix - 0 0 0 0 34 > 90292248 ---P--B 13 informix - 0 0 0 392183594 777296 > 90292860 Y--P--D 15 informix - 908b4540 0 0 30027 8701 > 90292e78 ---P--D 35290 informix - 0 0 0 0 0 > 90293490 ---P--D 35289 informix - 0 0 0 0 0 > 902946d8 ---P--D 20 informix - 0 0 0 0 8 > 90295308 Y--P--- 25 patrol - 913b0a18 0 16 54299002 1142837 > 90295920 Y--P--D 26 informix - 4008f12c 0 0 0 0 > 90296550 Y-AP--M 106804 informix - 93b6a888 0 0 29 24 > 90297180 Y--P--- 106500 user1 0 93968f18 0 1 6 79 > 902983c8 Y--P--- 105021 user1 Server01 984162a0 0 1 396 285 > 90299c28 Y--P--- 102768 user1 Server01 936296f8 0 1 0 151 > 9029a240 Y--P--- 76613 user001 - 92c7ba18 0 2 808003 0 > 9029eb60 Y--P--- 76570 user001 - 93b51d38 0 1 1717 588 > 902a1c20 Y--P--- 103621 user1 Server01 931bb798 0 1 0 12 > 902a2e68 Y--P--- 106557 user001 - 9242afb8 0 1 51 0 > 902a6540 Y-AP--M 106805 informix - 96892ba8 0 0 26 24 > 902a7da0 Y--P--- 2788 user1 0 94821068 0 1 7706567 1542817 > 902a89d0 Y--P--- 2789 user1 0 9397b158 0 1 1552 57902 > 902a9600 Y--P--- 2776 user1 Server04 9398e068 0 1 11094 125827 > 93302018 Y--P--- 2781 user1 Server04 9155d9e0 0 1 0 0 > 933044a8 Y--P--- 20454 user1 0 931a5fc8 0 2 39585 2843 > 93304ac0 Y--P--- 105783 user1 Server01 937422e8 0 1 1262 674 > 93306320 Y--P--- 106798 user1 0 93bb4b58 0 1 2 95 > 93307568 Y--P--- 103615 user1 Server01 93a55fb8 0 1 299 217 > 93307b80 Y--P--- 106131 user1 Server01 9364ed88 0 1 69 1482 > 9330a010 Y--P--- 105251 user1 0 936ad608 0 1 1 1 > 9330b870 Y--P--- 106481 user1 Server01 9c11d658 0 1 0 40 > 9330d6e8 Y--P--- 106384 user1 Server01 92cb7ce0 0 1 2811 2612 > 9330f560 Y--P--- 106748 user1 Server01 93aa03d8 0 1 0 34 > 9330fb78 Y--P--- 106795 user1 0 930b1bf8 0 4 10345 809 > 93312620 Y-AP--M 106803 informix - 92139d38 0 0 27 24 > 93313e80 Y--P--- 106540 user001 - 939b39c8 0 1 67 0 > 933150c8 Y--P--- 99281 user1 0 9efd4478 0 1 1 17 > 933156e0 Y--P--- 99279 user1 0 9c9158d8 0 2 1 1 > 93315cf8 Y--P--- 106547 user001 - 91c43428 0 1 623 588 > 93316928 Y--P--- 104952 user1 Server01 9c012108 0 1 808 670 > 93316f40 Y--P--- 21824 user1 Server02 93943928 0 1 0 0 > 93317b70 Y--P--- 70141 user002 - 935cbf68 0 1 61964 4102 > 933193d0 Y--P--- 104992 user1 0 9378f798 0 1 0 1 > 9331ac30 Y--P--- 101838 user1 Server01 91271338 0 1 26147 7592 > 9331c490 ------- 106803 informix - 0 0 0 0 0 > 9331caa8 Y--P--- 101997 user1 Server01 92de41f8 0 1 2836 2608 > 9331e920 Y--P--- 41847 user1 0 93876e28 0 4 46627 3466 > 9331ef38 ------- 106807 informix - 0 0 0 298 0 > 933219e0 Y--P--- 106639 user1 0 9377a1a8 0 1 5 3 > 93321ff8 ------- 106812 informix - 0 0 0 298 0 > 93322610 Y--P--- 104990 user1 0 937a94c8 0 2 5 1 > 93322c28 Y--P--- 102590 user1 Server01 93c14298 0 1 0 0 > 93323e70 Y--P--- 105250 user1 Server68 938be978 0 1 0 0 > 93324aa0 Y--P--- 103041 user1 0 93884608 0 1 304442 75051 > 933250b8 Y--P--- 106136 user001 - 92de4b58 0 2 44 0 > 93326300 Y--P--- 104816 user1 Server01 9afb0428 0 1 829 205 > 9332c480 ------- 106811 informix - 0 0 0 0 0 > 9332d0b0 Y--P--- 76578 user001 - 9f9f7338 0 1 785 588 > 93330788 Y--P--- 106711 user1 0 9369a518 0 1 9 26 > 97e90e90 Y--P--- 106556 user001 - 93b51338 0 1 629 588 > 97e92d08 Y--P--- 103382 user1 0 939d8338 0 3 6956 226 > 97e93938 Y-AP--M 106812 informix - 93b37658 0 0 26 24 > 97e94568 Y--P--- 106546 user001 - 98496568 0 1 6 0 > 97e95dc8 Y--P--- 106210 user1 Server01 9c333ab8 0 1 0 43 > 97e98e88 Y--P--- 101889 user1 Server01 93a235b8 0 1 137 178 > 97e9b930 Y--P--- 100041 user1 0 937d4d88 0 1 5691 109 > 97e9cb78 Y--P--- 105010 user001 - 93848248 0 1 3934 1708 > 97e9d190 Y--P--- 104993 user1 Server69 93943978 0 1 0 0 > 97e9d7a8 Y--P--- 105244 user1 0 93867518 0 2 3 1 > 97e9e3d8 Y--P--- 76573 user001 - 936f7e78 0 1 76 0 > 97e9e9f0 Y--P--- 106582 user1 0 9f124a68 0 1 6 4 > 97ea0868 Y-AP--M 106808 informix - 93ab33d8 0 0 27 24 > 97ea26e0 ------- 106804 informix - 0 0 0 5 0 > 97ea3310 Y--P--- 70711 user003 Server12 93593dd8 0 1 18318 0 > 97ea3928 Y--P--- 106548 user001 - 93c6d838 0 1 12 0 > 97ea3f40 Y-AP--M 106807 informix - 930e0018 0 0 27 24 > 97ea5db8 Y--P--- 106209 user1 Server01 93c01068 0 1 181 185 > 97ea63d0 Y-AP--M 106811 informix - 9242a158 0 0 27 24 > 97eaa0c0 Y--P--- 20457 user1 0 93a11888 0 1 259723 1105 > 97eab308 Y-AP--M 106810 informix - 9b9b3298 0 0 27 24 > 97eab920 Y--P--- 104953 user1 Server01 937e3978 0 1 0 51 > 97eac550 Y--P--- 106560 user1 0 9171be28 0 2 247 137 > 97eae3c8 Y--P--- 103642 user1 Server01 9d62dc98 0 1 669 408 > 97eb0e70 Y--P--- 106387 user1 Server01 93c5fbf8 0 1 0 54 > 97eb1488 ------- 106806 informix - 0 0 0 304 0 > 97eb2ce8 ------- 106804 informix - 0 0 0 0 0 > 97eb3300 Y--P--- 103643 user1 Server01 93a11518 0 1 0 2 > 97eb7608 ------- 106808 informix - 0 0 0 298 0 > 97eba6c8 Y--P--- 101797 user1 Server01 939a0ce8 0 1 0 296 > 97ebbf28 Y--P--- 106545 user001 - 935de608 0 1 624 588 > 97ebd788 Y--P--- 76610 user001 - 93c27478 0 1 8037 2268 > 97ebefe8 ------- 106811 informix - 0 0 0 298 0 > 99b19c48 Y--P--- 106552 user001 - 9f40e928 0 1 620 588 > 99b1ae90 Y--P--- 101839 user1 Server01 9381d798 0 1 0 192 > 99b1b4a8 Y--P--- 71661 user003 Serve

Lots of things in there. Andreas correclty points our your read cache hit rate which is poor. I find the 5th line interesting, lots of waiting - sequential scans, 1.2 billion lock requests. Compresses. 15 million Sequential scans - are those ok? Are they reading small tables or big ones that way? How up to date are your statistics? 6.2 million Compresses - rows are being deleted and then the page is later compressed - that would be a design issue, why so mamy deletes? deadlocks 0 - nice. 1.2 Billion lock requests - versus 1.2 billion disk reads - every read is locked? What is the isolation level of each of these users? Why are they locking everything they read? cheers j. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of JACK PAPA [deletia] bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 51876197 7247 1280409663 57 0 12432 6247517 15715161 [deletia]

First let me thank all of you guys for the nice inputs, really appreciated. The real problem actually is the user is complaining that they are experiencing too much slowness when performing a select, even just it is a simple select. The thing i noticed on the table they are complaining is this "extent size 256000 next size 32000 lock mode row";, the table they are complaining is locked on a per row basis. The thing is, on the original box, all are working normally, until it was migrated to a new box. database version, OS version are the same, the application is the same. The only changes are the network configuration and the hardware. Does the network plays a big part? on the user who is testing the application, when he tried to issue "ping hostname", the latency is quite high "160 average". from my pc, it's just "45". To Andrea, onstat -g ses pid|grep user|sort -n -k 9,shows these are the biggest pids.. 93324aa0 Y--P--- 103041 user1 0 93884608 0 1 616345 158815 9ccf36e8 Y--P--- 102960 user1 0 9fbb8a18 0 1 743498 168588 9029f178 ---PR-- 110770 user1 0 0 0 2 3054975 1683 902a7da0 Y--P--- 2788 user1 0 94821068 0 1 8010009 1613611 ---- for pid 2788 which consumes the biggest mem. => onstat -g ses 2788 IBM Informix Dynamic Server Version 9.40.UC6 -- On-Line (Prim) -- Up 2 days 07:57:56 -- 2251504 Kbytes session #RSAM total used dynamic id user tty pid hostname threads memory memory explain 2788 user1 0 385090 Server1 1 1236992 1166864 off tid name rstcb flags curstk status 3457 sqlexec 902a7da0 Y--P--- 2264 cond wait(netnorm) Memory pools count 1 name class addr totalsize freesize #allocfrag #freefrag 2788 V 9110e020 1236992 70128 2557 92 name free used name free used overhead 0 1648 mtmisc 0 864 resident 0 1248 scb 0 1400 opentable 0 101104 filetable 0 8864 ru 0 464 log 0 4208 temprec 0 16200 keys 0 36504 ralloc 0 868368 gentcb 0 1208 ostcb 0 2736 sort 0 56 sqscb 0 72144 sql 0 40 rdahead 0 448 hashfiletab 0 280 osenv 0 1792 buft_buffer 0 8352 sqtcb 0 11728 fragman 0 17792 udr 0 8152 sqscb info scb sqscb optofc pdqpriority sqlstats optcompind directives 9127e2a8 91186018 0 1 0 0 1 ........................................... .... NOTE: The "update statistics high" is executed on a weekly basis. Before, it was 3x a week but it was stopped due to conflict with other running jobs.

The change in the frequency of running UPDATE STATISTICS could have an impact on performance of certain queries, so that is an issue. The comms delays are also a serious issue for this user. You can try turning on OPTOFC for that session and see if it helps. That option reduces the number of communications packets send between the session and the server both ways and can help when communications are slow. Have your network folk take a look at the router or switch that's between this particular user and the server to see if there are errors being seen there and perhaps swap it out for a new one. FYI, a full UPDATE STATISTICS HIGH on the full database isn't necessary and takes much longer than running through the recommended suite of commands. You can use my dostats utility to automatically implement this protocol for you. Dostats is contained in the package utils2_ak which you can download from the IIUG Software Repository. Note that the current dostats source file, dostats_ng.ec for Next Generation, does not support IDS 9.40 so you'll have to modify the makefile to compile the older source file dostats.ecwhich is also included in the package. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Mar 2, 2010 at 9:26 PM, JACK PAPA <informix2009@gmail.com> wrote: > First let me thank all of you guys for the nice inputs, really appreciated. > The real problem actually is the user is complaining that they are > experiencing too much slowness when performing a select, even just it is a > simple select. > > The thing i noticed on the table they are complaining is this > > "extent size 256000 next size 32000 lock mode row";, the table they are > complaining is locked on a per row basis. The thing is, on the original > box, > all are working normally, until it was migrated to a new box. database > version, OS version are the same, the application is the same. The only > changes are the network configuration and the hardware. Does the network > plays > a big part? on the user who is testing the application, when he tried to > issue > "ping hostname", the latency is quite high "160 average". from my pc, it's > just "45". > > To Andrea, onstat -g ses pid|grep user|sort -n -k 9,shows these are the > biggest pids.. > > 93324aa0 Y--P--- 103041 user1 0 93884608 0 1 616345 158815 > 9ccf36e8 Y--P--- 102960 user1 0 9fbb8a18 0 1 743498 168588 > 9029f178 ---PR-- 110770 user1 0 0 0 2 3054975 1683 > 902a7da0 Y--P--- 2788 user1 0 94821068 0 1 8010009 1613611 > > ---- > for pid 2788 which consumes the biggest mem. > > => onstat -g ses 2788 > > IBM Informix Dynamic Server Version 9.40.UC6 -- On-Line (Prim) -- Up 2 days > 07:57:56 -- 2251504 Kbytes> > session #RSAM total used dynamic > id user tty pid hostname threads memory memory explain > 2788 user1 0 385090 Server1 1 1236992 1166864 off > > tid name rstcb flags curstk status > 3457 sqlexec 902a7da0 Y--P--- 2264 cond wait(netnorm) > > Memory pools count 1 > name class addr totalsize freesize #allocfrag #freefrag > 2788 V 9110e020 1236992 70128 2557 92 > > name free used name free used > overhead 0 1648 mtmisc 0 864 > resident 0 1248 scb 0 1400 > opentable 0 101104 filetable 0 8864 > ru 0 464 log 0 4208 > temprec 0 16200 keys 0 36504 > ralloc 0 868368 gentcb 0 1208 > ostcb 0 2736 sort 0 56 > sqscb 0 72144 sql 0 40 > rdahead 0 448 hashfiletab 0 280 > osenv 0 1792 buft_buffer 0 8352 > sqtcb 0 11728 fragman 0 17792 > udr 0 8152 > > sqscb info > scb sqscb optofc pdqpriority sqlstats optcompind directives > 9127e2a8 91186018 0 1 0 0 1 > ............................................ > ..... > > NOTE: > > The "update statistics high" is executed on a weekly basis. Before, it was > 3x > a week but it was stopped due to conflict with other running jobs. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001517477f86daed650480dc9492

... I have a little script ... ah well. Network latency of 160 does not sound like an issue of this order. So if this was just migrated over to new hardware - how was that migration performed? -- regardless, that's just how it might have happened, not WHAT has happened. If you have pinpointed it to a single table, have a look at that table in particular - From systables get the partnum (it will be 0 if it is a fragmented table - in which case, follow the tabid to sysfragments and get all of the partnumbers for each fragment from there). Typically, you might exclude the indexes from the list (from sysfragments) but that might be interesting as well. Then, in the sysmaster database. select pt.dbsname, pt.tabname, pt.partnum, ti_flags, ti_rowsize, ti_ncols, ti_nkeys, ti_nextns, ti_pagesize, date('01/01/1970') + trunc(ti_created/86400), ti_serialv, ti_fextsiz, ti_nextsiz, ti_nptotal, ti_npused, ti_npdata, ti_nrows, abs(lockreqs), abs(lockwts), abs(deadlks), abs(lktouts), abs(isreads), abs(iswrites), abs(isrewrites), abs(isdeletes), abs(bufreads), abs(bufwrites), abs(seqscans), abs(pagreads), abs(pagwrites) from sysptprof pt, systabinfo ti where pt.partnum in (LIST OF PART NUMBERS) and pt.partnum=ti.ti_partnum; This will get you all kinds of data on the table and it's activity. The answer may shout at you from that, if not, send it along. cheers j. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of JACK PAPA Sent: Tuesday, March 02, 2010 9:27 PM To: ids@iiug.org Subject: Re: How to check informix DB bottlenecks [19162] First let me thank all of you guys for the nice inputs, really appreciated. The real problem actually is the user is complaining that they are experiencing too much slowness when performing a select, even just it is a simple select. The thing i noticed on the table they are complaining is this "extent size 256000 next size 32000 lock mode row";, the table they are complaining is locked on a per row basis. The thing is, on the original box, all are working normally, until it was migrated to a new box. database version, OS version are the same, the application is the same. The only changes are the network configuration and the hardware. Does the network plays a big part? on the user who is testing the application, when he tried to issue "ping hostname", the latency is quite high "160 average". from my pc, it's just "45". To Andrea, onstat -g ses pid|grep user|sort -n -k 9,shows these are the biggest pids.. 93324aa0 Y--P--- 103041 user1 0 93884608 0 1 616345 158815 9ccf36e8 Y--P--- 102960 user1 0 9fbb8a18 0 1 743498 168588 9029f178 ---PR-- 110770 user1 0 0 0 2 3054975 1683 902a7da0 Y--P--- 2788 user1 0 94821068 0 1 8010009 1613611 ---- for pid 2788 which consumes the biggest mem. => onstat -g ses 2788 IBM Informix Dynamic Server Version 9.40.UC6 -- On-Line (Prim) -- Up 2 days 07:57:56 -- 2251504 Kbytes session #RSAM total used dynamic id user tty pid hostname threads memory memory explain 2788 user1 0 385090 Server1 1 1236992 1166864 off tid name rstcb flags curstk status 3457 sqlexec 902a7da0 Y--P--- 2264 cond wait(netnorm) Memory pools count 1 name class addr totalsize freesize #allocfrag #freefrag 2788 V 9110e020 1236992 70128 2557 92 name free used name free used overhead 0 1648 mtmisc 0 864 resident 0 1248 scb 0 1400 opentable 0 101104 filetable 0 8864 ru 0 464 log 0 4208 temprec 0 16200 keys 0 36504 ralloc 0 868368 gentcb 0 1208 ostcb 0 2736 sort 0 56 sqscb 0 72144 sql 0 40 rdahead 0 448 hashfiletab 0 280 osenv 0 1792 buft_buffer 0 8352 sqtcb 0 11728 fragman 0 17792 udr 0 8152 sqscb info scb sqscb optofc pdqpriority sqlstats optcompind directives 9127e2a8 91186018 0 1 0 0 1 ............................................ ..... NOTE: The "update statistics high" is executed on a weekly basis. Before, it was 3x a week but it was stopped due to conflict with other running jobs. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Hi, you got a lof of help from other people, too. They are all worth to check and implement. To follow my suggestions (with focus on single SQL statements): If the SQL the user is complaining about takes a few seconds, you could try to find the specific SQL statement: Depending on the kind of application you could try to identify the sessions belonging to that user (onstat -g ses will give you client addresses and process IDs ). Then you can ask the user to run the SQL and do a few onstat -g sql <SessionID>or onstat -g sql 0 (if you could not get the session id in advance). Afterwards compare the where-clause of the SQL and the dbschema -d <database> -t <table> , look for missing indices. If nothing obvious is found, you can run the SQL in dbaccess with set explain on - to get the execution plan. For the last rows of the onstat -u | sort : You already did a onstat -g ses (or onstat -g sql) - look at the SQL statement if you see an obviously missing index or something like that. Regards, Andreas > ------------------------------------------- SPAR Österreichische Warenhandels-AG Hauptzentrale A - 5015 Salzburg, Europastrasse 3 FN 34170 a Tel: +43 662 4470 84923 Fax: Mobile: +43 664 6259575 E-Mail: Andreas.KUTSCHE@spar.at Internet: http://www.spar.at Wichtiger Hinweis: Der Inhalt dieser E-Mail kann vertrauliche und rechtlich geschützte Informationen, insbesondere Betriebs- oder Geschäftsgeheimnisse, enthalten, zu deren Geheimhaltung der Empfänger verpflichtet ist. Die Informationen in dieser E-Mail sind ausschließlich für den Adressaten bestimmt. Sollten Sie die E-Mail irrtümlich erhalten haben so ersuchen wir Sie, die Nachricht von Ihrem System zu löschen und sich mit uns in Verbindung zu setzen. Über das Internet versandte E-Mails können leicht manipuliert oder unter fremdem Namen erstellt werden. Daher schließen wir die rechtliche Verbindlichkeit der in dieser Nachricht enthaltenen Informationen aus. Der Inhalt der E-Mail ist nur rechtsverbindlich, wenn er von uns schriftlich bestätigt und gezeichnet wird. Sollte trotz der von uns verwendeten Virus-Schutzprogramme durch die Zusendung von E-Mails ein Virus in Ihre Systeme gelangen, haften wir nicht für evtl. hieraus entstehende Schäden. Wir danken für Ihr Verständnis. Important notice: The contents of this e-mail may contain confidential and legally protected information that is in particular related to operational and trade secrets, which the recipient is obliged to treat as confidential. The information in this e-mail is made available exclusively for use by the addressee. In the event that the e-mail may have been sent to you in error, we would ask you to kindly delete this communication from your system and to contact us. E-mails sent via the Internet can be easily manipulated or sent out under someone else's name. We therefore do not accept legal liability for the information contained in this communication. The contents of the e-mail are only legally binding if they have been confirmed and signed by us in writing. If, in spite of our using Antivirus protection software, a virus may have penetrated your system through the sending of this e-mail, we do not accept liability for any damage that may possibly arise as a result of this. We trust that you appreciate our position. ------------------------------------------- -----Ursprüngliche Nachricht----- > Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Im Auftrag von > JACK PAPA > Gesendet: Mittwoch, 3. März 2010 03:27 > An: ids@iiug.org > Betreff: Re: How to check informix DB bottlenecks [19162] > > First let me thank all of you guys for the nice inputs, really > appreciated. > The real problem actually is the user is complaining that they are > experiencing too much slowness when performing a select, even just it is a > simple select. > > The thing i noticed on the table they are complaining is this > > "extent size 256000 next size 32000 lock mode row";, the table they are > complaining is locked on a per row basis. The thing is, on the original > box, > all are working normally, until it was migrated to a new box. database > version, OS version are the same, the application is the same. The only > changes are the network configuration and the hardware. Does the network > plays > a big part? on the user who is testing the application, when he tried to > issue > "ping hostname", the latency is quite high "160 average". from my pc, it's > just "45". > > To Andrea, onstat -g ses pid|grep user|sort -n -k 9,shows these are the > biggest pids.. > > 93324aa0 Y--P--- 103041 user1 0 93884608 0 1 616345 158815 > 9ccf36e8 Y--P--- 102960 user1 0 9fbb8a18 0 1 743498 168588 > 9029f178 ---PR-- 110770 user1 0 0 0 2 3054975 1683 > 902a7da0 Y--P--- 2788 user1 0 94821068 0 1 8010009 1613611 > > ---- > for pid 2788 which consumes the biggest mem. > > => onstat -g ses 2788 > > IBM Informix Dynamic Server Version 9.40.UC6 -- On-Line (Prim) -- Up 2> days > 07:57:56 -- 2251504 Kbytes > > session #RSAM total used dynamic > id user tty pid hostname threads memory memory explain > 2788 user1 0 385090 Server1 1 1236992 1166864 off > > tid name rstcb flags curstk status > 3457 sqlexec 902a7da0 Y--P--- 2264 cond wait(netnorm) > > Memory pools count 1 > name class addr totalsize freesize #allocfrag #freefrag > 2788 V 9110e020 1236992 70128 2557 92 > > name free used name free used > overhead 0 1648 mtmisc 0 864 > resident 0 1248 scb 0 1400 > opentable 0 101104 filetable 0 8864 > ru 0 464 log 0 4208 > temprec 0 16200 keys 0 36504 > ralloc 0 868368 gentcb 0 1208 > ostcb 0 2736 sort 0 56 > sqscb 0 72144 sql 0 40 > rdahead 0 448 hashfiletab 0 280 > osenv 0 1792 buft_buffer 0 8352 > sqtcb 0 11728 fragman 0 17792 > udr 0 8152 > > sqscb info > scb sqscb optofc pdqpriority sqlstats optcompind directives > 9127e2a8 91186018 0 1 0 0 1 > ............................................ > ..... > > NOTE: > > The "update statistics high" is executed on a weekly basis. Before, it was > 3x > a week but it was stopped due to conflict with other running jobs. > > > ************************************************************************** > ***** > Forum Note: Use "Reply" to post a response in the discussion forum.

Hello, network latency might have quite an impact, if the application is reading a lot of rows one row after the other (without array fetch), or even worse, if it is reading a lot of rows through a lot of small select statements. But from the other performance indicators given (sequential scans, cache rate) I would focus on the single table / SQL on that table. Regards, Andreas > ------------------------------------------- SPAR Österreichische Warenhandels-AG Hauptzentrale A - 5015 Salzburg, Europastrasse 3 FN 34170 a Tel: +43 662 4470 84923 Fax: Mobile: +43 664 6259575 E-Mail: Andreas.KUTSCHE@spar.at Internet: http://www.spar.at Wichtiger Hinweis: Der Inhalt dieser E-Mail kann vertrauliche und rechtlich geschützte Informationen, insbesondere Betriebs- oder Geschäftsgeheimnisse, enthalten, zu deren Geheimhaltung der Empfänger verpflichtet ist. Die Informationen in dieser E-Mail sind ausschließlich für den Adressaten bestimmt. Sollten Sie die E-Mail irrtümlich erhalten haben so ersuchen wir Sie, die Nachricht von Ihrem System zu löschen und sich mit uns in Verbindung zu setzen. Über das Internet versandte E-Mails können leicht manipuliert oder unter fremdem Namen erstellt werden. Daher schließen wir die rechtliche Verbindlichkeit der in dieser Nachricht enthaltenen Informationen aus. Der Inhalt der E-Mail ist nur rechtsverbindlich, wenn er von uns schriftlich bestätigt und gezeichnet wird. Sollte trotz der von uns verwendeten Virus-Schutzprogramme durch die Zusendung von E-Mails ein Virus in Ihre Systeme gelangen, haften wir nicht für evtl. hieraus entstehende Schäden. Wir danken für Ihr Verständnis. Important notice: The contents of this e-mail may contain confidential and legally protected information that is in particular related to operational and trade secrets, which the recipient is obliged to treat as confidential. The information in this e-mail is made available exclusively for use by the addressee. In the event that the e-mail may have been sent to you in error, we would ask you to kindly delete this communication from your system and to contact us. E-mails sent via the Internet can be easily manipulated or sent out under someone else's name. We therefore do not accept legal liability for the information contained in this communication. The contents of the e-mail are only legally binding if they have been confirmed and signed by us in writing. If, in spite of our using Antivirus protection software, a virus may have penetrated your system through the sending of this e-mail, we do not accept liability for any damage that may possibly arise as a result of this. We trust that you appreciate our position. ------------------------------------------- -----Ursprüngliche Nachricht----- > Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Im Auftrag von > Jack Parker > Gesendet: Mittwoch, 3. März 2010 03:55 > An: ids@iiug.org > Betreff: RE: How to check informix DB bottlenecks [19165] > > .... I have a little script ... ah well. > > Network latency of 160 does not sound like an issue of this order. > > So if this was just migrated over to new hardware - how was that migration > performed? -- regardless, that's just how it might have happened, not WHAT > has happened. > > If you have pinpointed it to a single table, have a look at that table in > particular - > > >From systables get the partnum (it will be 0 if it is a fragmented table > - > in which case, follow the tabid to sysfragments and get all of the > partnumbers for each fragment from there). Typically, you might exclude > the > indexes from the list (from sysfragments) but that might be interesting as > well. > > Then, in the sysmaster database. > > select pt.dbsname, pt.tabname, pt.partnum,> > ti_flags, ti_rowsize, ti_ncols, ti_nkeys, ti_nextns, > > ti_pagesize, date('01/01/1970') + trunc(ti_created/86400), > > ti_serialv, ti_fextsiz, ti_nextsiz, > > ti_nptotal, ti_npused, ti_npdata, ti_nrows, > > abs(lockreqs), > > abs(lockwts), > > abs(deadlks), > > abs(lktouts), > > abs(isreads), > > abs(iswrites), > > abs(isrewrites), > > abs(isdeletes), > > abs(bufreads), > > abs(bufwrites), > > abs(seqscans), > > abs(pagreads), > > abs(pagwrites) > from sysptprof pt, systabinfo ti > where pt.partnum in (LIST OF PART NUMBERS) > > and pt.partnum=ti.ti_partnum; > > This will get you all kinds of data on the table and it's activity. The > answer may shout at you from that, if not, send it along. > > cheers > j. > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of > JACK PAPA > Sent: Tuesday, March 02, 2010 9:27 PM > To: ids@iiug.org > Subject: Re: How to check informix DB bottlenecks [19162] > > First let me thank all of you guys for the nice inputs, really > appreciated. > The real problem actually is the user is complaining that they are > experiencing too much slowness when performing a select, even just it is a > simple select. > > The thing i noticed on the table they are complaining is this > > "extent size 256000 next size 32000 lock mode row";, the table they are > complaining is locked on a per row basis. The thing is, on the original > box, > all are working normally, until it was migrated to a new box. database > version, OS version are the same, the application is the same. The only > changes are the network configuration and the hardware. Does the network > plays > a big part? on the user who is testing the application, when he tried to > issue > "ping hostname", the latency is quite high "160 average". from my pc, it's > just "45". > > To Andrea, onstat -g ses pid|grep user|sort -n -k 9,shows these are the > biggest pids.. > > 93324aa0 Y--P--- 103041 user1 0 93884608 0 1 616345 158815 > 9ccf36e8 Y--P--- 102960 user1 0 9fbb8a18 0 1 743498 168588 > 9029f178 ---PR-- 110770 user1 0 0 0 2 3054975 1683 > 902a7da0 Y--P--- 2788 user1 0 94821068 0 1 8010009 1613611 > > ---- > for pid 2788 which consumes the biggest mem. > > => onstat -g ses 2788 > > IBM Informix Dynamic Server Version 9.40.UC6 -- On-Line (Prim) -- Up 2> days > 07:57:56 -- 2251504 Kbytes > > session #RSAM total used dynamic > id user tty pid hostname threads memory memory explain > 2788 user1 0 385090 Server1 1 1236992 1166864 off > > tid name rstcb flags curstk status > 3457 sqlexec 902a7da0 Y--P--- 2264 cond wait(netnorm) > > Memory pools count 1 > name class addr totalsize freesize #allocfrag #freefrag > 2788 V 9110e020 1236992 70128 2557 92 > > name free used name free used > overhead 0 1648 mtmisc 0 864 > resident 0 1248 scb 0 1400 > opentable 0 101104 filetable 0 8864 > ru 0 464 log 0 4208 > temprec 0 16200 keys 0 36504 > ralloc 0 868368 gentcb 0 1208 > ostcb 0 2736 sort 0 56 > sqscb 0 72144 sql 0 40 > rdahead 0 448 hashfiletab 0 280 > osenv 0 1792 buft_buffer 0 8352 > sqtcb 0 11728 fragman 0 17792 > udr 0 8152 > > sqscb info

Pings of 45, or even 160 seem obscenely high. On our network (with 3 switches between me and the core and then from the core to the server) my ping to the server is .15ms. (yes that is less than 1ms) Pings like the ones you are referring to are something I'd expect if you were using VPN over the internet. Jonathon Wyza CX & CBORD System Administrator CX Programmer/Analyst Administrative Computing Bethel College (574)-257-3381 AIM: Iamwyza jonathon.wyza@bethelcollege.edu ========================== SLES 10 SP2 & IDS 10.0 HC9 " I would love to change the world, but they won't give me the source code." -- Unknown -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of JACK PAPA Sent: Tuesday, March 02, 2010 9:27 PM To: ids@iiug.org Subject: Re: How to check informix DB bottlenecks [19162] First let me thank all of you guys for the nice inputs, really appreciated. The real problem actually is the user is complaining that they are experiencing too much slowness when performing a select, even just it is a simple select. The thing i noticed on the table they are complaining is this "extent size 256000 next size 32000 lock mode row";, the table they are complaining is locked on a per row basis. The thing is, on the original box, all are working normally, until it was migrated to a new box. database version, OS version are the same, the application is the same. The only changes are the network configuration and the hardware. Does the network plays a big part? on the user who is testing the application, when he tried to issue "ping hostname", the latency is quite high "160 average". from my pc, it's just "45". To Andrea, onstat -g ses pid|grep user|sort -n -k 9,shows these are the biggest pids.. 93324aa0 Y--P--- 103041 user1 0 93884608 0 1 616345 158815 9ccf36e8 Y--P--- 102960 user1 0 9fbb8a18 0 1 743498 168588 9029f178 ---PR-- 110770 user1 0 0 0 2 3054975 1683 902a7da0 Y--P--- 2788 user1 0 94821068 0 1 8010009 1613611 ---- for pid 2788 which consumes the biggest mem. => onstat -g ses 2788 IBM Informix Dynamic Server Version 9.40.UC6 -- On-Line (Prim) -- Up 2 days 07:57:56 -- 2251504 Kbytes session #RSAM total used dynamic id user tty pid hostname threads memory memory explain 2788 user1 0 385090 Server1 1 1236992 1166864 off tid name rstcb flags curstk status 3457 sqlexec 902a7da0 Y--P--- 2264 cond wait(netnorm) Memory pools count 1 name class addr totalsize freesize #allocfrag #freefrag 2788 V 9110e020 1236992 70128 2557 92 name free used name free used overhead 0 1648 mtmisc 0 864 resident 0 1248 scb 0 1400 opentable 0 101104 filetable 0 8864 ru 0 464 log 0 4208 temprec 0 16200 keys 0 36504 ralloc 0 868368 gentcb 0 1208 ostcb 0 2736 sort 0 56 sqscb 0 72144 sql 0 40 rdahead 0 448 hashfiletab 0 280 osenv 0 1792 buft_buffer 0 8352 sqtcb 0 11728 fragman 0 17792 udr 0 8152 sqscb info scb sqscb optofc pdqpriority sqlstats optcompind directives 9127e2a8 91186018 0 1 0 0 1 ............................................ ..... NOTE: The "update statistics high" is executed on a weekly basis. Before, it was 3x a week but it was stopped due to conflict with other running jobs. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.