system configuration

I have a dilemma. we are runnig informix IDS on a Sun E3000 with 6 200Mhz processors and 1 gig of ram. our performance is very poor. We have spent most of the last year tweaking the engine but our traffic increases and performance remains poor. We are now looking to upgrade our server and are at a loss for what sort of horsepowere we shuld be shopping for. I am attaching the end of day stats for one of our busiest days over the summer at the bottom of this post and would appreciate it if someone could give me some idea what sort of system we should be shopping for. I was thinking a Sun 6000 or 6500. I look forward to any input. If you would like more info please let me know. email address is mhdevlin@nycap.rr.com or mdevlin@reserveamerica.com. thanks again. INFORMIX-OnLine Version 7.24.UC6 -- On-Line -- Up 21:38:49 -- 304512 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 14772607 18002755 2254304397 99.34 1891087 2680138 8456961 77.64 isamtot open start read write rewrite delete commit rollbk 1409519981 10520849 19769422 844321135 2142142 674864 149469 477957 14583 2 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 837 0 0 104601.92 10303.19 183 366 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 1211977 71635 1968591814 12 0 3994 99375 501393 ixda-RA idx-RA da-RA RA-pgsused lchwaits 3016390 500803 9831594 13137760 9555401 Sent via Deja.com http://www.deja.com/ Before you buy.

You may also consider looking into bug # 115327 . I have cut and pasted an earlier posting with responses from Art which may help you make an over all best decision. the_griffon@my-deja.com wrote: > I have a dilemma. we are runnig informix IDS on a Sun E3000 > with 6 200Mhz processors and 1 gig of ram. our performance is very > poor. .......... Here is the earlier posting. Best of luck. > Thanks so much for your insight into this problem, Art. > > After getting your analysis, I contacted our local Informix SE, who has > sent > us a query (see below) to run against sysmaster to determine which > indexes > need rebuilding. Until it finishes, we are preparing a game plan to > rebuild > all our indexes (since all of our indexes were built either with 7.2x or > 7.30). > > To check for bad indexes: > > To see if bug 115327 is a factor, run the following on each instance > (this > will > take awhile - see the diagnostic notes*** I've included in the bug > submitters > synopsis below.): > > DATABASE Sysmaster;> > SELECT trim(dbsname)||":"||trim(tabname) > FROM syspaghdr P, systabnames T > WHERE P.pg_flags = 208 > AND P.pg_partnum > 1048570 > AND P.pg_partnum = T.partnum; > > - if no rows found, then you are not experiencing the bug. > - if you find rows, then you are running into the bug. > > If you are finding rows, the work around is to drop and recreate the > index(es) > for the tables in question. Also, in previous testing, I found another > workaround to be disabling then enabling the index. That got rid of the > mismarked index pages the same as dropping and recreating the index. > Neither > techniques guarantee that the bad page flags won't be propagated again, > so > continue to monitor using the select statement until you have upgraded > past > the > bug. > > Joe Vidrine > Garden Ridge, Inc. > Houston, TX > > Art S. Kagel wrote in message <38306E62.E77DC469@bloomberg.net>... > > > > > >uunet wrote: > >> > >> Hi, all > >> > >> Our production OLTP server is 7.31.UC4 on Solaris 2.6 (Sun E-4500, 6 > cpus, 4 > >> gig memory). Our batch processing went at a snail's pace all weekend, > and > it > >> occurred to me only this morning to check the output of "onstat -P". > Here > is > >> what I found: > >> > >> Informix Dynamic Server Version 7.31.UC4 -- On-Line -- Up 3 days > >> 20:34:16 -- 1311296 Kbytes > >> partnum total btree data other resident dirty > >> . > >> . > >> . > >> Totals: 120000 118099 1376 525 0 1836 > >> > >> Percentages: > >> Data 1.15 > >> Btree 98.42 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< > >> Other 0.44 > > > >Stop right there. You arebeing eaten by bug 115327, though I thought > that > >7.31UC4 has the fix for that one. Hmmmm, have you rebuilt your indexes > >since upgrading? Since the bug causes the index pages to get flagged as > > >BOTH leaf and node it confuses the buffer priority code and all index > >pages get classed MED-HIGH and quickly starve out the data pages from > the > >buffer cache as data pages are only MEDIUM (except for RESIDENT tables). > > >The fix, besides getting a version which will continue the problem, is > >to rebuild ALL indexes from which any rows may have been deleted and any > > >indexes created by 7.2x (since 7.2x did not maintain the flags properly > >at all). You should not have to rebuild indexes built by 7.3x on tables > > >which are not deleted from since it is the BTREE cleaner threads which > >mess up the flags, in the buggy versions, upon compressing sparse index > >pages emtied by deletes. > > > >Art S. Kagel > > > -- Compliments of QueriX -------------------------------------------------------------------------------------------------- QueriX 4GL Compilers are Informix 4GL Compatible. Some features are: True Windows GUI Clients. ActiveX support. HTML Report Generation. More rigorous error handling. Connection to other RDBMS such as Oracle. Connection to all versions of Informix 4GL with no need to change compiler. For more details visit: http://www.querix.com/ ---------------------------------------------------------------------------------------------------

I was the one that originally posted the query to test whether the database has been bitten by the subject bug (the query was given to me by our Informix SE). I now question whether the query returns the correct results (I question the "208" value of pg_flags, since some of the tables whose indexes we just rebuilt have flag values of "208" Informix, is this bug REALLY fixed?). A better method to determine which tables are affected by the subject bug might be to monitor "onstat -P" This will show the Data/Btree split of buffer pages by table, and will show a summary at the bottom. Since, in the output, the tables are listed by partnum rather than by name, you can load the output into a temp table and join with the sysmaster:systabnames table by partnum. Those tables whose buffer pages are skewed heavily toward "Btree" rather than "Data" are affected, and the Informix line is to drop and re-create the indexes. HTH Joe Vidrine Garden Ridge, Inc. Houston, TX QueriX 4GL / Mehdi wrote in message <38313B02.8BB218D1@querix.com>... >You may also consider looking into bug # 115327 . > >I have cut and pasted an earlier posting with responses from Art which may >help >you make an over all best decision. > >the_griffon@my-deja.com wrote: > >> I have a dilemma. we are runnig informix IDS on a Sun E3000 >> with 6 200Mhz processors and 1 gig of ram. our performance is very >> poor. .......... > >Here is the earlier posting. Best of luck. > <snip> > >Compliments of QueriX >--------------------------------------------------------------------------- ----------------------- > >QueriX 4GL Compilers are Informix 4GL Compatible. Some features are: > >True Windows GUI Clients. ActiveX support. HTML Report Generation. >More rigorous error handling. Connection to other RDBMS such as Oracle. >Connection to all versions of Informix 4GL with no need to change compiler. > >For more details visit: http://www.querix.com/ >--------------------------------------------------------------------------- ------------------------ > > >

Interesting, how long will this query run? it has been running 5 hours so far. Also how seriously could this be affecting performance? In article <38313B02.8BB218D1@querix.com>, QueriX 4GL / Mehdi <mehdi@querix.com> wrote: > You may also consider looking into bug # 115327 . > > I have cut and pasted an earlier posting with responses from Art which may > help > you make an over all best decision. > > the_griffon@my-deja.com wrote: > > > I have a dilemma. we are runnig informix IDS on a Sun E3000 > > with 6 200Mhz processors and 1 gig of ram. our performance is very > > poor. .......... > > Here is the earlier posting. Best of luck. > > > Thanks so much for your insight into this problem, Art. > > > > After getting your analysis, I contacted our local Informix SE, who has > > sent > > us a query (see below) to run against sysmaster to determine which > > indexes > > need rebuilding. Until it finishes, we are preparing a game plan to > > rebuild > > all our indexes (since all of our indexes were built either with 7.2x or > > 7.30). > > > > To check for bad indexes: > > > > To see if bug 115327 is a factor, run the following on each instance > > (this > > will > > take awhile - see the diagnostic notes*** I've included in the bug > > submitters > > synopsis below.): > > > > DATABASE Sysmaster;> > > > SELECT trim(dbsname)||":"||trim(tabname) > > FROM syspaghdr P, systabnames T > > WHERE P.pg_flags = 208 > > AND P.pg_partnum > 1048570 > > AND P.pg_partnum = T.partnum; > > > > - if no rows found, then you are not experiencing the bug. > > - if you find rows, then you are running into the bug. > > > > If you are finding rows, the work around is to drop and recreate the > > index(es) > > for the tables in question. Also, in previous testing, I found another > > workaround to be disabling then enabling the index. That got rid of the > > mismarked index pages the same as dropping and recreating the index. > > Neither > > techniques guarantee that the bad page flags won't be propagated again, > > so > > continue to monitor using the select statement until you have upgraded > > past > > the > > bug. > > > > Joe Vidrine > > Garden Ridge, Inc. > > Houston, TX > > > > Art S. Kagel wrote in message <38306E62.E77DC469@bloomberg.net>... > > > > > > > > >uunet wrote: > > >> > > >> Hi, all > > >> > > >> Our production OLTP server is 7.31.UC4 on Solaris 2.6 (Sun E-4500, 6 > > cpus, 4 > > >> gig memory). Our batch processing went at a snail's pace all weekend, > > and > > it > > >> occurred to me only this morning to check the output of "onstat -P". > > Here > > is > > >> what I found: > > >> > > >> Informix Dynamic Server Version 7.31.UC4 -- On-Line -- Up 3 days > > >> 20:34:16 -- 1311296 Kbytes > > >> partnum total btree data other resident dirty > > >> . > > >> . > > >> . > > >> Totals: 120000 118099 1376 525 0 1836 > > >> > > >> Percentages: > > >> Data 1.15 > > >> Btree 98.42 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< > > >> Other 0.44 > > > > > >Stop right there. You arebeing eaten by bug 115327, though I thought > > that > > >7.31UC4 has the fix for that one. Hmmmm, have you rebuilt your indexes > > >since upgrading? Since the bug causes the index pages to get flagged as > > > > >BOTH leaf and node it confuses the buffer priority code and all index > > >pages get classed MED-HIGH and quickly starve out the data pages from > > the > > >buffer cache as data pages are only MEDIUM (except for RESIDENT tables). > > > > >The fix, besides getting a version which will continue the problem, is > > >to rebuild ALL indexes from which any rows may have been deleted and any > > > > >indexes created by 7.2x (since 7.2x did not maintain the flags properly > > >at all). You should not have to rebuild indexes built by 7.3x on tables > > > > >which are not deleted from since it is the BTREE cleaner threads which > > >mess up the flags, in the buggy versions, upon compressing sparse index > > >pages emtied by deletes. > > > > > >Art S. Kagel > > > > > > -- > > Compliments of QueriX > -------------------------------------------------------------------------------------------------- > > QueriX 4GL Compilers are Informix 4GL Compatible. Some features are: > > True Windows GUI Clients. ActiveX support. HTML Report Generation. > More rigorous error handling. Connection to other RDBMS such as Oracle. > Connection to all versions of Informix 4GL with no need to change compiler. > > For more details visit: http://www.querix.com/ > --------------------------------------------------------------------------------------------------- > > Sent via Deja.com http://www.deja.com/ Before you buy.

The query finally finished with no returns. I will be pursuing the post by uunet however due to other facts (heavily loaded cpu, memory, disks) I am still leaning toward the ned for additional hardware. Even if this Bug is found , due to impending restructuring we will be shopping for upgrades. I would be very interested in any insight into what sort system we chould be shopping for. I guess the easiest answer is to buy tho most you can afford but i need some solid reasons to spend money for the company whether is $100 or $1,000,000.00. once again, I appreciate any input. In article <80s4ok$phj$1@ffx2nh4.news.uu.net>, "uunet" <jvidrine@gardenridge.com> wrote: > I was the one that originally posted the query to test whether the database > has been bitten by the subject bug (the query was given to me by our > Informix SE). I now question whether the query returns the correct results > (I question the "208" value of pg_flags, since some of the tables whose > indexes we just rebuilt have flag values of "208" Informix, is this bug > REALLY fixed?). > > A better method to determine which tables are affected by the subject bug > might be to monitor "onstat -P" This will show the Data/Btree split of > buffer pages by table, and will show a summary at the bottom. Since, in the > output, the tables are listed by partnum rather than by name, you can load > the output into a temp table and join with the sysmaster:systabnames table > by partnum. Those tables whose buffer pages are skewed heavily toward > "Btree" rather than "Data" are affected, and the Informix line is to drop > and re-create the indexes. > > HTH > > Joe Vidrine > Garden Ridge, Inc. > Houston, TX > > QueriX 4GL / Mehdi wrote in message <38313B02.8BB218D1@querix.com>... > >You may also consider looking into bug # 115327 . > > > >I have cut and pasted an earlier posting with responses from Art which may > >help > >you make an over all best decision. > > > >the_griffon@my-deja.com wrote: > > > >> I have a dilemma. we are runnig informix IDS on a Sun E3000 > >> with 6 200Mhz processors and 1 gig of ram. our performance is very > >> poor. .......... > > > >Here is the earlier posting. Best of luck. > > > <snip> > > > >Compliments of QueriX > >--------------------------------------------------------------------------- > ----------------------- > > > >QueriX 4GL Compilers are Informix 4GL Compatible. Some features are: > > > >True Windows GUI Clients. ActiveX support. HTML Report Generation. > >More rigorous error handling. Connection to other RDBMS such as Oracle. > >Connection to all versions of Informix 4GL with no need to change compiler. > > > >For more details visit: http://www.querix.com/ > >--------------------------------------------------------------------------- > ------------------------ > > > > > > > > Sent via Deja.com http://www.deja.com/ Before you buy.