Informix Health
Can anybody let me know about the health of my Database. I am sharing the output. Please let me know if any parameter I have to modify? INFORMIX-OnLine Version 7.13.UC6 -- On-Line -- Up 1 days 17:59:09 -- 953176 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 6216430 7833056 90628895 93.14 245819 888510 1643383 85.04 isamtot open start read write rewrite delete commit rollbk 24473422 306487 927927 19052473 475400 55072 68134 10579 163 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 8063.87 2224.23 27 54 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 825452 6 54648707 0 0 96 144162 152647 ixda-RA idx-RA da-RA RA-pgsused lchwaits 749964 6104 4936900 5690235 108785
2009/9/22 DEEPAK JOSHI <djoshih@hotmail.com>: > Can anybody let me know about the health of my Database. > I am sharing the output. Please let me know if any parameter I have to modify? > > INFORMIX-OnLine Version 7.13.UC6 -- On-Line -- Up 1 days 17:59:09 -- 953176 > Kbytes > > Profile > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > 6216430 7833056 90628895 93.14 245819 888510 1643383 85.04 > > isamtot open start read write rewrite delete commit rollbk > 24473422 306487 927927 19052473 475400 55072 68134 10579 163 > > ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes > 0 0 0 8063.87 2224.23 27 54 > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans > 825452 6 54648707 0 0 96 144162 152647 > > ixda-RA idx-RA da-RA RA-pgsused lchwaits > 749964 6104 4936900 5690235 108785 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > You are desparately short of buffers. Read % should be better than 99%, write % greater than 90% ovbuf zero and bufwaits as low as possible. Also seq scans seems a bit high. Have you run the recommended update stats? Are there any queries that are not supported by an index that could use an index? Do you have memory spare to add buffers? Keith
The only thing that I can tell from just this output is that your BR (Bufwaits Ratio) is high at over 11 ( values > 7 indicate occasional slow performance, >10 means many users are likely complaining about responsiveness and performance most of the time). This usually means contention for LRU queues among multiple users, but it can also indicate not enough buffers. Start by increasing the number of LRU's to the maximum of 128. It looks like you need a minimum of about 50,000 buffers to maintain a good Buffer Turnover Rate (BTR). Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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, Sep 22, 2009 at 8:17 AM, DEEPAK JOSHI <djoshih@hotmail.com> wrote: > Can anybody let me know about the health of my Database. > I am sharing the output. Please let me know if any parameter I have to > modify? > > INFORMIX-OnLine Version 7.13.UC6 -- On-Line -- Up 1 days 17:59:09 -- 953176 > Kbytes > > Profile > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > 6216430 7833056 90628895 93.14 245819 888510 1643383 85.04 > > isamtot open start read write rewrite delete commit rollbk > 24473422 306487 927927 19052473 475400 55072 68134 10579 163 > > ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes > 0 0 0 8063.87 2224.23 27 54 > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans > 825452 6 54648707 0 0 96 144162 152647 > > ixda-RA idx-RA da-RA RA-pgsused lchwaits > 749964 6104 4936900 5690235 108785 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0015174c41744fff0b04742bf9ee