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 %cached dskwrits pagwrits
bufwrits %cached
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
sending to informix-list
miyaki wrote:
> 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%.
Don't jump in and attack LRU queues, because there's a few things here which
really stands out:
> RESIDENT 0Make sure you have enough memory on the machine to be able to set this to 1.
There's no point allowing a database engine to swap unless it's not used
very much.
> NETTYPE tlitcp,1,100,NET
> NETTYPE ipcshm,3,100,CPU
> MULTIPROCESSOR 1
> NUMCPUVPS 3
> SINGLE_CPU_VP 0
> NOAGE 1
> AFF_SPROC 1
> AFF_NPROCS 3
Are there 4 CPU's on this machine? If not, your numbers are scary; if you
have at least 4, this is fine.
> LOCKS 2000000
2 million locks? I'd be surprised....
> BUFFERS 30000Only 30000 buffers? How much memory on the machine? get this number
increased substantially.
> NUMAIOVPS 2Are you using KAIO? If not, this is extremely low and is probably
responsible for most of your problems - this and the very small number of
buffers. I do not see evidence of kaio in your message.
> CLEANERS 32
> LRUS 32A good start is to set CLEANERS to match the number of chunks you have.
Then, make LRU's = CLEANERS, so that when each LRU queue needs a flush,
there is a cleaner available. Finally, set NUMAIOVPS to = CLEANERS, plus a
few more for "good luck". Tune from there with measurements to back up any
changes.
> RA_PAGES 64
> RA_THRESHOLD 32Probably overkill with BUFFERS so low. If you turn BUFFERS upto say 100,000
at least, then make these
RA_PAGES 32
RA_THRESHOLD 28
which is a decent starting point.
> SHMVIRTSIZE 147456
> SHMADD 14336
How many memory segments are allocated? These numbers are "too different"
from each other. Look at the output from onstat -g seg add up the
allocated V segments, and put that into SHMVIRTSIZE. Then set a value that's
maybe 1/3 of that into SHMADD. Monitor the segments allocated after that.
How many users on the machine, and what sort of work are they doing? How
fast are the CPUs in this machine (I don't know Sun's in any detail) How
many disks?
What's the output of
onstat -g iov | head -7
Andrew Hamm wrote:
>
> What's the output of
> onstat -g iov | head -7
WRONG!!!
I mean, what's the output of:
onstat -g iof
onstat -g iov
onstat -F | head -7
Your buffers are very low, how much memory have you got???
miyaki wrote:
>
> 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 %cached dskwrits pagwrits
> bufwrits %cached
> 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'