IDS Forum

Re: Performance Issues

Posted By: Madison Pruet
Date: Monday, 1 August 2011, at 10:37 a.m.

In Response To: Re: Performance Issues (VIKAS HIVARKAR)

just post the output of onstat -g dic. Also the onconfig file for the
primary.

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

To: ids@iiug.org

Date: 08/01/2011 12:13 AM

Subject: Re: Performance Issues [24484]

Sent by: ids-bounces@iiug.org

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

*******************************************************************************

Forum Note: Use "Reply" to post a response in the discussion forum.

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.