IDS Forum

Re: Performance Issues

Posted By: VIKAS HIVARKAR
Date: Thursday, 4 August 2011, at 1:17 a.m.

In Response To: Re: Performance Issues (Madison Pruet)

Hello All,

"onstat -g sch" (We now have the vpuvps = cpu cores ) but

VP Scheduler Statistics:
vp pid class semops busy waits spins/wait
1 20655 cpu 0 0 0
2 20795 adm 0 0 0
3 20796 cpu 0 0 0
4 20797 cpu 0 0 0
5 20798 cpu 0 0 0
6 20799 cpu 0 0 0
7 20800 cpu 0 0 0
8 20801 cpu 0 0 0
9 20802 cpu 0 0 0
10 20803 cpu 0 0 0
11 20804 cpu 0 0 0
12 20809 cpu 33767045 34198356 997
13 20810 cpu 32838062 33260684 997
14 20812 lio 0 0 0
15 20813 pio 0 0 0
16 20814 aio 794912 0 0
17 20815 msc 1219209 0 0
18 20816 fifo 0 0 0
19 20817 aio 19127 0 0
20 20818 soc 1704 14257 698
21 20819 soc 1213 6589 568
22 20820 soc 1450 6469 679
23 20821 soc 1166 6740 674
24 20822 soc 1290 6546 631
25 20823 soc 883 3925 671
26 20824 soc 1701 8528 660
27 20825 soc 2223 11920 607
28 20826 soc 1484 6617 642
29 20827 soc 1572 8573 649
30 20828 soc 10311 14680 845
31 20829 soc 10929 24908 821

I do not understand why most of the initial cpuvps not spining or doing any work?

We will be adding more buffers this weekend and currently looking at the hot spots on the disc.

Regards,
===============================================================================
Hello All,

Yes was have an HDR and SDS (primary + 1node)

Database running with UNBUFFERED log mode

increased the no of cores to 12, now suits 12 cpuvps and considering to increase the buffers too

"onstat -l" output:

Physical Logging
Buffer bufused bufsize numpages numwrits pages/io
P-2 72 512 15225310 104294 145.98

phybegin physize phypos phyused %used

62:53 19995000 3137280 582604 2.91

Logical Logging
Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io
L-1 0 512 69423775 2990157 719686 23.2 4.2

Subsystem numrecs Log Space used

OLDRSAM 69391925 5290671676

HA 31850 1410892

Buffer Waiting
Buffer ioproc flags
L-1 0 0x1 0
L-3 0 0x1 0

I will be checking for seqential scans on big tables with the below query through the day:

"onstat -g dic"
I am trying to check the tables with high refcnt, if that is what I should be checking?

Dictionary Cache: Number of lists: 503, Maximum list size: 10
Total number of dictionary entries: 158

Any more checks or suggesstions?

Regards,
Vikas

==============================================================================
Are your main databases using BUFFERED LOG? That might explain the
contention for the logical log buffers. The BTR indicates that you could
use a significant increase in the number of buffers. Also over 9% of your
queries seem to include a sequential scan [(seqscans / (isam starts)) *
100]. See if those are being performed against large tables.

Art

==============================================================================
1) Are you using HDR?

2) Are you doing unbuffered logging? What is the output of onstat -l?
Just because you have a large log buffer does not mean that you are goi=
ng
to use the full buffer. That would be the case with unbuffered logging=
.

3) I'm not sure that I would be setting the number of cpuvps greater t=
han
the number of cores. That can cause lock inversion.

4) To find the sequential scans...

select dbsname, tabname , pf_seqscans , npdata, npused
from sysptntab, systabnames, sysptnhdr
where pf_seqscans > 0
and sysptnhdr.npdata > 16
and sysptnhdr.npused > 16
and sysptnhdr.partnum =3D systabnames.partnum =

and systabnames.partnum =3D sysptntab.partnum =

order by pf_seqscans DESC

Also - what is the output of onstat -g dic.
============================================================================
Do you use HR?
Can you paste the top 20 lines of "onstat -l"?

============================================================================
=

From: "VIKAS HIVARKAR" <vikas.hivarkar@gmail.com> =

=

To: ids@iiug.org =

=

Date: 07/30/2011 10:38 AM =

=

Subject: Performance Issues [24479] =

=

Sent by: ids-bounces@iiug.org =

=

Hello All,

IDS 11.50.FC8W2 on HP-UX 11.23, 8 cores

Facing performance issue and hence drilling through the following

NETTYPE ipcshm,10,30,CPU
NETTYPE soctcp,12,150,NET
VPCLASS cpu,num=3D10,noage

"onstat -g sch" shows all the cpuvps spinning equally & 4-5 busy waits =
for
all
cpuvps except the first one having 194 busy waits

BUFFERPOOL
size=3D2K,buffers=3D6000000,lrus=3D512,lru_min_dirty=3D50.000000,lru_ma=
x_dirty=3D60.000000

BUFFERPOOL
size=3D4K,buffers=3D2500000,lrus=3D512,lru_min_dirty=3D50.000000,lru_ma=
x_dirty=3D60.000000

BUFFERPOOL
size=3D16K,buffers=3D600000,lrus=3D512,lru_min_dirty=3D50.000000,lru_ma=
x_dirty=3D60.000000

"onstat -u" I see lot many sessions waiting for Log buffer G-BPX--
and some sessions doing rollbacks frequently :(

We do have good amount of log buffer set LOGBUFF 1024 and huge amount o=
f
logical logs

Some calculations:
BR: 0.3456
RAU: 99.94
BTR: 61.00/hrs, 146/hrs, 610/hrs (calculated for 3 bufferpools differen=
tly)

BTR: 40/hrs (combined for all 3 buffers)

Also pasting the output from "onstat -p"
Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
178371295 5841005690 6876949471 97.41 29669851 123813498 290614611 89.7=
9

isamtot open start read write rewrite delete commit rollbk
7040485791 950978742 312005761 921931088 75467491 7475945 9111695 43716=
88
4869

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 99930.89 49477.95 179 460935

bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
21899422 44236 1696677622 0 0 12779 4298263 30103468

ixda-RA idx-RA da-RA RA-pgsused lchwaits
39575656 5892 179906 39742052 9803879

Any suggessions to check why the sessions are waiting so much for buffe=
rs
and
logbufers?

Application check:
The below formula shows atleast 20% of the sql's doing sequential scan,=

calculated as
SSR =3D (seqscans / isam starts) * 100.00
How do I capture or find these SQL's doing sequential scan and or missi=
ng
indexs
How do I find out aged out Indexes if any

Messages In This Thread

• Performance Issues
  VIKAS HIVARKAR -- Saturday, 30 July 2011, at 11:37 a.m.
  • Re: Performance Issues
    Jack Parker -- Saturday, 30 July 2011, at 12:19 p.m.
  • Re: Performance Issues
    Fernando Nunes -- Saturday, 30 July 2011, at 1:22 p.m.
  • Re: Performance Issues
    Madison Pruet -- Saturday, 30 July 2011, at 4:01 p.m.
  • Re: Performance Issues
    Art Kagel -- Saturday, 30 July 2011, at 9:29 p.m.
    • Re: Performance Issues
      VIKAS HIVARKAR -- Monday, 1 August 2011, at 1:06 a.m.
      • Re: Performance Issues
        Madison Pruet -- Monday, 1 August 2011, at 10:37 a.m.
        • Re: Performance Issues
          VIKAS HIVARKAR -- Thursday, 4 August 2011, at 1:17 a.m.
          • Re: Performance Issues
            John Miller iii -- Thursday, 4 August 2011, at 4:15 a.m.
            • Re: Performance Issues
              VIKAS HIVARKAR -- Monday, 8 August 2011, at 1:59 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.