Re: high buffer wait ratio
"miyaki" <lcib@yahoo.com> wrote in message
news:c5vvo3$pco$1@terabinaries.xmission.com...
>
> 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?
>
What is the output of onstat -g spi sorted by num loops?
> 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
>
>
>
>
> _________________