469,613 Members | 1,365 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,613 developers. It's quick & easy.

unexplained performance drop

Hi All,

Out of nowhere my udb system (v8) performance has went terrible. Its gotten
about 10x worse, (some tests that used to take 2 seconds to run now take
20)... I'm not sure what happened. I did reorg/runstat/rebind on
everything, no luck... I'm not sure what to do next...? Any recommendations
on something to try to start narrowing down the possible problem.

Things I've tried:

db2stop/start
reorg/stats/rebind
The slowness seems to be system wide and not related to a specific table or
stored procedure.

Thanks,


Nov 12 '05 #1
6 1943
What os platform? What version/release/patch level? What fixpack level of db2?
What changes have been made to db2 and to the os or the hw since you noticed the
problem? Are any other applications on the same server having performance
problems? What does VMSTAT show?

Larry

AC Slater wrote:
Hi All,

Out of nowhere my udb system (v8) performance has went terrible. Its gotten
about 10x worse, (some tests that used to take 2 seconds to run now take
20)... I'm not sure what happened. I did reorg/runstat/rebind on
everything, no luck... I'm not sure what to do next...? Any recommendations
on something to try to start narrowing down the possible problem.

Things I've tried:

db2stop/start
reorg/stats/rebind

The slowness seems to be system wide and not related to a specific table or
stored procedure.

Thanks,


Nov 12 '05 #2
SunOS 5.8. UDB V8.1 Fixpack 3.

No other noticable slow applications.

Changes were being made to optimization levels right before performance
issue occured. I *think* all those changes were rolled back, but I could be
wrong.

VMstat:
$ vmstat
procs memory page disk faults cpu
r b w swap free re mf pi po fr de sr s0 s1 s6 -- in sy cs us sy
id
0 0 0 713392 24272 41 172 866 218 603 0 1437 54 18 0 0 642 318 198 3 3
94

One other note:

One of my test programs uses CLI to : connect, run stored proc 20 times,
disconnect. This used to take 2 seconds now takes 20 or so... almost all of
the 20 seconds is split between the connect and the first stored proc
execution...intersting; not sure what to make of that.

Thanks,
Frank
arry" <no****@nospam.com> wrote in message
news:3F***************@nospam.com...
What os platform? What version/release/patch level? What fixpack level of db2? What changes have been made to db2 and to the os or the hw since you noticed the problem? Are any other applications on the same server having performance
problems? What does VMSTAT show?

Larry

AC Slater wrote:
Hi All,

Out of nowhere my udb system (v8) performance has went terrible. Its gotten about 10x worse, (some tests that used to take 2 seconds to run now take
20)... I'm not sure what happened. I did reorg/runstat/rebind on
everything, no luck... I'm not sure what to do next...? Any recommendations on something to try to start narrowing down the possible problem.

Things I've tried:

db2stop/start
reorg/stats/rebind

The slowness seems to be system wide and not related to a specific table or stored procedure.

Thanks,

Nov 12 '05 #3
I am not a performance expert ... nor an expert at interpreting VMSTATs, but
this looks to me like the machine is spending a lot of time in idle. I believe
there is also some paging going on ... but whether it is excessive or not I'm
not sure.

Perhaps someone else can make some more helpful observations.

Larry Edelstein

AC Slater wrote:
SunOS 5.8. UDB V8.1 Fixpack 3.

No other noticable slow applications.

Changes were being made to optimization levels right before performance
issue occured. I *think* all those changes were rolled back, but I could be
wrong.

VMstat:
$ vmstat
procs memory page disk faults cpu
r b w swap free re mf pi po fr de sr s0 s1 s6 -- in sy cs us sy
id
0 0 0 713392 24272 41 172 866 218 603 0 1437 54 18 0 0 642 318 198 3 3
94

One other note:

One of my test programs uses CLI to : connect, run stored proc 20 times,
disconnect. This used to take 2 seconds now takes 20 or so... almost all of
the 20 seconds is split between the connect and the first stored proc
execution...intersting; not sure what to make of that.

Thanks,
Frank

arry" <no****@nospam.com> wrote in message
news:3F***************@nospam.com...
What os platform? What version/release/patch level? What fixpack level of

db2?
What changes have been made to db2 and to the os or the hw since you

noticed the
problem? Are any other applications on the same server having performance
problems? What does VMSTAT show?

Larry

AC Slater wrote:
Hi All,

Out of nowhere my udb system (v8) performance has went terrible. Its gotten about 10x worse, (some tests that used to take 2 seconds to run now take
20)... I'm not sure what happened. I did reorg/runstat/rebind on
everything, no luck... I'm not sure what to do next...? Any recommendations on something to try to start narrowing down the possible problem.

Things I've tried:

db2stop/start
reorg/stats/rebind

The slowness seems to be system wide and not related to a specific table or stored procedure.

Thanks,


Nov 12 '05 #4
Whats the easiest way to tell that bufferpools are working properly for a
tablespace?

For some reason I have a feeling that might be it...
"Larry" <no****@nospam.com> wrote in message
news:3F***************@nospam.com...
I am not a performance expert ... nor an expert at interpreting VMSTATs, but this looks to me like the machine is spending a lot of time in idle. I believe there is also some paging going on ... but whether it is excessive or not I'm not sure.

Perhaps someone else can make some more helpful observations.

Larry Edelstein

AC Slater wrote:
SunOS 5.8. UDB V8.1 Fixpack 3.

No other noticable slow applications.

Changes were being made to optimization levels right before performance
issue occured. I *think* all those changes were rolled back, but I could be wrong.

VMstat:
$ vmstat
procs memory page disk faults cpu r b w swap free re mf pi po fr de sr s0 s1 s6 -- in sy cs us sy id
0 0 0 713392 24272 41 172 866 218 603 0 1437 54 18 0 0 642 318 198 3 3 94

One other note:

One of my test programs uses CLI to : connect, run stored proc 20 times,
disconnect. This used to take 2 seconds now takes 20 or so... almost all of the 20 seconds is split between the connect and the first stored proc
execution...intersting; not sure what to make of that.

Thanks,
Frank

arry" <no****@nospam.com> wrote in message
news:3F***************@nospam.com...
What os platform? What version/release/patch level? What fixpack level of
db2?
What changes have been made to db2 and to the os or the hw since you

noticed the
problem? Are any other applications on the same server having
performance problems? What does VMSTAT show?

Larry

AC Slater wrote:

> Hi All,
>
> Out of nowhere my udb system (v8) performance has went terrible. Its gotten
> about 10x worse, (some tests that used to take 2 seconds to run now
take > 20)... I'm not sure what happened. I did reorg/runstat/rebind on
> everything, no luck... I'm not sure what to do next...? Any

recommendations
> on something to try to start narrowing down the possible problem.
>
> Things I've tried:
>
> db2stop/start
> reorg/stats/rebind
>
> The slowness seems to be system wide and not related to a specific

table or
> stored procedure.
>
> Thanks,

Nov 12 '05 #5
You can search on "Buffer pool activity" at this site to see the monitor
elements that can help you:

http://publib.boulder.ibm.com/infoce...help/index.jsp

A little late now, but you mentioned changing "optimizations" and trying
to ensure they were changed back to original values. If your system
turns out to be sensitive to such changes, there are things you can do
before making a change to ensure you can track down what went wrong if
the changes does degrade performance:

1. backup the database before making a change - this will save the
database configuration parameters.

2. there are some primitive scripts here to save database and database
manager configuration parameters in tables (tested with v7, not v8) with
a timestamp of when they were last changed:
http://www-106.ibm.com/developerwork...4adamache.html

3. Save the settings of all registry varviable (db2set>out)

4. If you want to be really ambitious, you can export the contents of
the catalog tables in IXF before anything major gets changed.

AC Slater wrote:
Whats the easiest way to tell that bufferpools are working properly for a
tablespace?

For some reason I have a feeling that might be it...
"Larry" <no****@nospam.com> wrote in message
news:3F***************@nospam.com...
I am not a performance expert ... nor an expert at interpreting VMSTATs,


but
this looks to me like the machine is spending a lot of time in idle. I


believe
there is also some paging going on ... but whether it is excessive or not


I'm
not sure.

Perhaps someone else can make some more helpful observations.

Larry Edelstein

AC Slater wrote:

SunOS 5.8. UDB V8.1 Fixpack 3.

No other noticable slow applications.

Changes were being made to optimization levels right before performance
issue occured. I *think* all those changes were rolled back, but I
could be
wrong.

VMstat:
$ vmstat
procs memory page disk faults
cpu
r b w swap free re mf pi po fr de sr s0 s1 s6 -- in sy cs us
sy
id
0 0 0 713392 24272 41 172 866 218 603 0 1437 54 18 0 0 642 318 198 3
3
94

One other note:

One of my test programs uses CLI to : connect, run stored proc 20 times,
disconnect. This used to take 2 seconds now takes 20 or so... almost
all of
the 20 seconds is split between the connect and the first stored proc
execution...intersting; not sure what to make of that.

Thanks,
Frank

arry" <no****@nospam.com> wrote in message
news:3F***************@nospam.com...

What os platform? What version/release/patch level? What fixpack level
of
db2?

What changes have been made to db2 and to the os or the hw since you

noticed the

problem? Are any other applications on the same server having
performance
problems? What does VMSTAT show?

Larry

AC Slater wrote:
>Hi All,
>
>Out of nowhere my udb system (v8) performance has went terrible.
Its
gotten

>about 10x worse, (some tests that used to take 2 seconds to run now
take
20)... I'm not sure what happened. I did reorg/runstat/rebind on
>everything, no luck... I'm not sure what to do next...? Any

recommendations

>on something to try to start narrowing down the possible problem.
>
>Things I've tried:
>
>db2stop/start
>reorg/stats/rebind
>
>The slowness seems to be system wide and not related to a specific
table
or

>stored procedure.
>
>Thanks,



Nov 12 '05 #6
> One other note:

One of my test programs uses CLI to : connect, run stored proc 20 times,
disconnect. This used to take 2 seconds now takes 20 or so... almost all of
the 20 seconds is split between the connect and the first stored proc
execution...intersting; not sure what to make of that.

Thanks,
Frank

Is your test program the only application connected to the database at
the time? If it is, it could be that the database is not "activated"
and that each connect forces all the resources to be allocated at
connect time (a slow process). Issue an "ACTIVATE DATABASE XXXX"
command to solve this.

Evan
Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by puzzlecracker | last post: by
1 post views Thread by Stephanie | last post: by
reply views Thread by Clay Luther | last post: by
4 posts views Thread by Aaron | last post: by
16 posts views Thread by David W. Fenton | last post: by
6 posts views Thread by Nathan Sokalski | last post: by
reply views Thread by =?Utf-8?B?THluZGE=?= | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.