high buffer wait ratio
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 32LOGSMAX 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
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
>
Several problems, see below:
----- Original Message -----
From: Miyaki <lcib@yahoo.com>
At: 4/19 3:55
> 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%.
Where are you getting the LRU contention figure?
> 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 0This really should be set to '1' or even better, -1.
> MULTIPROCESSOR 1
> NUMCPUVPS 3
> SINGLE_CPU_VP 0
> NOAGE 1
> AFF_SPROC 1
> AFF_NPROCS 3
> LOCKS 2000000
> BUFFERS 30000You probably need more buffers, I'd have to see onstat -P output and know how
long since onstat -z was run (or since startup) to be sure, but it's a safe
bet.
I'd start by doubling it and look to increase it more. Monitor the BTR and
#unused buffer pages to know when you have enough.
> NUMAIOVPS 2
If you are using COOKED devices or filesystem chunks this should be MUCH
higher,
but even if all chunks are RAW I'd make this 4-6. Monitor onstat -g iov
looking at the io/wup column. If all aio vps have a value >=1.0 you need more.
If more than one of them is <0.9 you can probably drop one or two safely
(though I'd evaluate over a peak period first).
> PHYSBUFF 32
> LOGBUFF 32> LOGSMAX 350
> CLEANERS 32
> SHMBASE 0x50000000
> SHMVIRTSIZE 147456
> SHMADD 14336
> SHMTOTAL 0
> CKPTINTVL 600
> LRUS 32
LRUS and CLEANERS are likely too low, and also 32 is a VERY bad value. Avoid
32, 64 & 96 they seem to trigger a bufwaits storm due to a quirk in the LRU
contention resolution algorithm that noone's ever been able to track down and
fix. Try 63 or 65 first and adjust from there monitoring the BR.
> LRU_MAX_DIRTY 2
> LRU_MIN_DIRTY 1
> LTXHWM 40
> LTXEHWM 50
> TXTIMEOUT 0x12c
> STACKSIZE 64
> RA_PAGES 64
> RA_THRESHOLD 32
You RA Utilization is low about 90%, it should be VERY close to 100%. Reduce
the RA parameters. Their rarely needed if your system has fast disks and good
caching drives and controllers all of which are performing read-ahead for you
anyway. The Informix readahead just servers to thrash the buffer cache without
buying you much unless your disks are slow or you are running a DW/DSS server
that performs lots of sequential scans.
Art S. Kagel
> 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 25c
> http://photos.yahoo.com/ph/print_splash