IDS Forum

Re: Parameter tuning?

Posted By: Art Kagel
Date: Thursday, 5 June 2008, at 9:33 a.m.

In Response To: Parameter tuning? (WH MAX)

Bufwaits is not your problem:

Bufwaits Ratio (BR) = 4.88 ( <7.0 is OK)
Buffer Turnover Rate (BTR) = 8.17/hour (< 10.0 is usually OK)
Readahead Utilization (RAU) = 73.01% (Should be > 99.0!)

Your biggest problem is that you are thrashing the buffer pool with vastly
excessive read ahead! You didn't post them, but either you have a very high
setting for RA_PAGES or RA_THRESHOLD is very close to RA_PAGES. My
recommended setting for most OLTP type installations (don't know your system
load type, but based on the onstat -p output I'd say it's mostly OLTP-like)
would be:

RA_PAGES 8
RA_THRESHOLD 2

This is different from the manuals, but impirical experience puts it about
there. Most modern systems do so much readahead for you at the drive,
controller, disk array, and OS levels that Informix's readhead is redundant
and more harmful than helpful. Minimize it. I don't see any evidence of
it, but I suspect that 80,000 buffers is not enough. Monitor onstat -P and
see if a small number of tables are dominating the buffer cache with many
smaller table and index presences swapping in and out. In that case
increasing the cache to say 100,000 will also help.

Other factors: about 2% of your queries involve sequential scans, this is a
bit high. You have over a million rollbacks versus 26 million commited
transactions or about 5%, this is also rather high. The last indicates that
your applications may be experiencing clashes caused by multiple users
trying to update the same data and having to rollback when the app discovers
that the data has been changed by someone else. This is a work-flow problem
rather than a database one, but it affects server performance.

Art

On Thu, Jun 5, 2008 at 8:16 AM, WH MAX <wen-hong.hua@st.com> wrote:

> Pls see the instance profile and shared memory parameters below.
> The problems I noticed are
> 1. low read-cache-hit 35%
> 2. low write-cache-hit 79%
> 3. a lots of bufwaits and seqscans
>
> What do you advice?
>
> Informix Dynamic Server Version 7.31.UD3 -- On-Line -- Up 158 days 02:30:01
> --
> 299808 Kbytes
>
> Profile
> dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
> 709636519 2287473345 1101860441 35.60 40085133 254130402 194042736 79.34
>
> isamtot open start read write rewrite delete commit rollbk
> 2242399144 105086802 1485406942 2487445966 80059226 3708851 26378446
> 1274819
> 1641
>
> 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 412217.32 221862.72 22376 45458
>
> bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
> 121163016 1289 1674500070 148 0 1827 671025 23792119
>
> ixda-RA idx-RA da-RA RA-pgsused lchwaits
> 556415450 15605821 12133051 426502790 10069114
>
> # Shared Memory Parameters
> LOCKS 500000 # Maximum number of locks
> BUFFERS 80000 # Maximum number of shared memory buffers
> NUMAIOVPS 16 # Number of IO vps
> PHYSBUFF 64 # Physical log buffer size (Kbytes)
> LOGBUFF 32 # Logical log buffer size (Kbytes)
> LOGSMAX 25 # Maximum number of logical log files
> CLEANERS 127 # Number of buffer cleaner processes
> SHMBASE 0x0 # Shared memory base address
> SHMVIRTSIZE 100000 # initial virtual shared memory segment size
> SHMADD 90000 # Size of new shared memory segments (Kbytes)
> SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited
> CKPTINTVL 600 # Check point interval (in sec)
> LRUS 127 # Number of LRU queues
> LRU_MAX_DIRTY 5 # LRU modified begin-cleaning limit (percent)
> LRU_MIN_DIRTY 3 # LRU modified end-cleaning limit (percent)
> LTXHWM 50 # Long TX high-water mark (percent)
> LTXEHWM 60 # Long TX exclusive high-water mark (percent)
> TXTIMEOUT 0x12c # Transaction timeout (in sec)
> STACKSIZE 32 # Stack size (Kbytes)
>

--
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.

Messages In This Thread

• Parameter tuning?
  WH MAX -- Thursday, 5 June 2008, at 8:16 a.m.
  • Re: Parameter tuning?
    Obnoxio The Clown -- Thursday, 5 June 2008, at 9:12 a.m.
    • Re: Parameter tuning?
      Eric Rowell -- Thursday, 5 June 2008, at 9:17 a.m.
  • Re: Parameter tuning?
    Art Kagel -- Thursday, 5 June 2008, at 9:33 a.m.
    • Re: Re: Parameter tuning?
      vze2qjg5@verizon.net -- Thursday, 5 June 2008, at 10:44 a.m.
      • Re: Re: Parameter tuning?
        Art Kagel -- Thursday, 5 June 2008, at 11:49 a.m.
    • Re: Parameter tuning?
      VIKAS HIVARKAR -- Thursday, 5 June 2008, at 10:47 a.m.
      • Re: Parameter tuning?
        Art Kagel -- Thursday, 5 June 2008, at 11:34 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.