IDS Forum

Re: high buffer wait ratio

Posted By: Martin Fuerderer
Date: Monday, 19 April 2004, at 5:15 a.m.

In Response To: high buffer wait ratio (miyaki )

Hi,

you may:

- check "onstat -F" for
- "Fg Writes" : preferred is that this remains 0 (zero),
- "Chunk Writes" : preferred is that this is very low or even better 0
(zero),
- "LRU Writes" : most writes (at best all) should be done as LRU writes.

- you have 30000 BUFFERS configured, that's ~ 60 MB (with page size 2kB),
but you have SHMVIRTSIZE 147456 (kB) which is 144 MB.
This seems a bit "out-of-proportion".
- Are you sure that this amount of SHMVIRTSIZE really is necessary ?
- Have you ever seen that even more SHM is allocated (via SHMADD) ?
(Check the message log file or "onstat -g seg" output.)
- Can you set BUFFERS higher ?
- if you do not have more memory, you may try "shifting" some memory
from SHMVIRTSIZE to BUFFERS.

- do you see long checkpoints in message log file (i.e. checkpoint taking
longer
than 5 seconds) ?
- Compare that to the above "onstat -F" output. "Chunk Writes" is write
activity during checkpoints ...
- You may want to set LRU_MAX_DIRTY and LRU_MIN_DIRTY even lower.
If nothing else helps, try the "extremes", i.e. 1 and 0.
- Check that PHYSFILE is appropriate. Especially if you increase BUFFERS
you
may want to increase this as well.
- Maybe you can increase PHYSBUFF.
- I strongly assume you're using raw devices. Otherwise you would need
to
check NUMAIOVPS.

- there are other possibilities for tuning, but they require ever more
knowledge of "what the application" is doing. E.g. fragmentation of
tables
over dbspaces and then reaping the benefit of fragment elimination so
that
there will be less pages read into the buffer pool, etc.

Regards,
Martin
--
Martin Fuerderer
IBM Informix Development Munich
Data Management Solutions

forum.subscriber@iiug.org wrote on 19.04.2004 09:08:33:

> Hi all,
>
> My users are complaining the database slowness. I
> noticed that the buffer wait ratio at my database is
> quite high, around 58% and LRU contention is at 3.29%.
> How can i improve the database performance?
>
> Here's some info that might help for analysis.
>
> OS = Solaris 2.7
> IDS 7.31.UC2
>
> NETTYPE tlitcp,1,100,NET
> NETTYPE ipcshm,3,100,CPU
> DEADLOCK_TIMEOUT 10
> RESIDENT 0
> MULTIPROCESSOR 1
> NUMCPUVPS 3
> SINGLE_CPU_VP 0
> NOAGE 1
> AFF_SPROC 1
> AFF_NPROCS 3
> LOCKS 2000000
> BUFFERS 30000
> NUMAIOVPS 2
> PHYSBUFF 32
> LOGBUFF 32
> LOGSMAX 350
> CLEANERS 32
> SHMBASE 0x50000000
> SHMVIRTSIZE 147456
> SHMADD 14336
> SHMTOTAL 0
> CKPTINTVL 600
> LRUS 32
> LRU_MAX_DIRTY 2
> LRU_MIN_DIRTY 1
> LTXHWM 40
> LTXEHWM 50
> TXTIMEOUT 0x12c
> STACKSIZE 64
> RA_PAGES 64
> RA_THRESHOLD 32
> OPTCOMPIND 0
>
> Profile
> dskreads pagreads bufreads Êched dskwrits pagwrits
> bufwrits Êched
> 8240066 5913261 1107258877 99.26 778190 1102988
> 3511005 77.84
>
> isamtot open start read write rewrite
> delete commit rollbk
> 259438585 14166422 24177572 150718210 296141 308870
> 46026 144071 58
>
> 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 20309.42 1745.31 27
> 54
>
> bufwaits lokwaits lockreqs deadlks dltouts ckpwaits
> compress seqscans
> 5030714 1547 159474098 0 0 1052
> 44578 506896
>
> ixda-RA idx-RA da-RA RA-pgsused lchwaits
> 3983298 60547 1529713 5441731 3104843
>
> 32 buffer LRU queue pairs priority
> levels
> # f/m pair total % of length LOW MED_LOW
> MED_HIGH HIGH
> 0 f 933 98.3% 917 0 30
> 813 74
> 1 m 1.7% 16 0 16
> 0 0
> 2 f 923 98.5% 909 0 29
> 810 70
> 3 m 1.5% 14 0 14
> 0 0
> 4 f 931 98.7% 919 0 31
> 831 57
> 5 m 1.3% 12 0 12
> 0 0
> 6 f 930 98.4% 915 0 26
> 818 71
> 7 m 1.6% 15 0 15
> 0 0
> 8 f 923 98.9% 913 0 26
> 828 59
> 9 m 1.1% 10 0 8
> 2 0
> 10 f 922 98.6% 909 0 20
> 825 64
> 11 m 1.4% 13 0 12
> 1 0
> 12 f 934 98.6% 921 0 34
> 820 67
> 13 m 1.4% 13 0 13
> 0 0
> 14 f 924 98.6% 911 0 22
> 807 82
> 15 m 1.4% 13 0 12
> 1 0
> 16 f 929 98.5% 915 0 27
> 821 67
> 17 m 1.5% 14 0 13
> 1 0
> 18 f 928 98.8% 917 0 19
> 831 67
> 19 m 1.2% 11 0 11
> 0 0
> 20 f 925 98.2% 908 0 21
> 812 75
> 21 m 1.8% 17 0 16
> 1 0
> 22 f 925 98.5% 911 0 23
> 826 62
> 23 m 1.5% 14 0 13
> 1 0
> 24 f 935 98.5% 921 0 27
> 832 62
> 25 m 1.5% 14 0 12
> 2 0
> 26 f 923 97.9% 904 0 15
> 827 62
> 27 m 2.1% 19 0 18
> 1 0
> 28 f 928 98.2% 911 0 22
> 821 68
> 29 m 1.8% 17 0 17
> 0 0
> 30 f 922 97.9% 903 0 15
> 821 67
> 31 m 2.1% 19 0 18
> 1 0
> 32 f 902 97.5% 879 0 12
> 802 65
> 33 m 2.5% 23 0 22
> 1 0
> 34 f 947 98.1% 929 0 35
> 821 73
> 35 m 1.9% 18 0 18
> 0 0
> 36 f 926 98.6% 913 0 26
> 812 75
> 37 m 1.4% 13 0 12
> 1 0
> 38 f 931 98.4% 916 0 25
> 809 82
> 39 m 1.6% 15 0 13
> 2 0
> 40 F 917 98.8% 906 0 16
> 818 72
> 41 m 1.2% 11 0 11
> 0 0
> 42 f 924 98.4% 909 0 23
> 814 72
> 43 m 1.6% 15 0 14
> 1 0
> 44 f 932 98.2% 915 0 23
> 823 69
> 45 m 1.8% 17 0 17
> 0 0
> 46 f 919 98.3% 903 0 13
> 822 68
> 47 m 1.7% 16 2 13
> 1 0
> 48 f 920 98.5% 906 0 14
> 832 60
> 49 m 1.5% 14 0 14
> 0 0
> 50 f 925 97.8% 905 0 21
> 801 83
> 51 m 2.2% 20 0 20
> 0 0
> 52 f 924 98.7% 912 0 20
> 843 49
> 53 m 1.3% 12 0 12
> 0 0
> 54 f 922 98.7% 910 0 26
> 820 64
> 55 m 1.3% 12 0 12
> 0 0
> 56 f 927 98.2% 910 0 23
> 815 72
> 57 m 1.8% 17 0 16
> 1 0
> 58 f 927 98.3% 911 0 18
> 823 70
> 59 m 1.7% 16 0 15
> 1 0
> 60 f 919 98.9% 909 0 19
> 835 55
> 61 m 1.1% 10 0 10
> 0 0
> 62 f 931 98.8% 920 0 34
> 813 73
> 63 m 1.2% 11 0 11
> 0 0
> 471 dirty, 29628 queued, 30000 total, 32768 hash
> buckets, 2048 buffer size
> start clean at 2% (of pair total) dirty, or 18 buffs
> dirty, stop at 1%
> 0 priority downgrades, 0 priority upgrades
>
>
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Photos: High-quality 4x6 digital prints for 25¢
> http://photos.yahoo.com/ph/print_splash
>

Messages In This Thread

• high buffer wait ratio
  miyaki -- Monday, 19 April 2004, at 3:08 a.m.
  • Re: high buffer wait ratio
    Martin Fuerderer -- Monday, 19 April 2004, at 5:15 a.m.
  • Re: high buffer wait ratio
    ART KAGEL, BLOOMBERG/ 499 PARK -- Monday, 19 April 2004, at 9:10 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.