473,321 Members | 1,916 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

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 2083
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: puzzlecracker | last post by:
Fixed font - Proportional font string initilization WHY? All 2 messages in topic - view as tree puzzlecracker Jan 29, 10:47 pm show options Newsgroups: comp.lang.c From:...
1
by: Stephanie | last post by:
I am trying to setup a Performance Condition Alert and on this one SQL 2000 Server the 'Type:' drop down does not give me the option to choose "SQL Server performance condition alert". "SQL Server...
0
by: Clay Luther | last post by:
I am doing to large dataset performance tests with 7.3.4b2 today and I noticed an interesting phenomenon. My shared memory buffers are set at 128MB. Peak postmaster usage appears to be around 90MB....
4
by: Aaron | last post by:
In a test environment we have dropped over 100 RI restrictions which showed a huge instant performance gain where some jobs were running with 2 or 3 times the performance than previously. The...
16
by: David W. Fenton | last post by:
http://www.granite.ab.ca/access/performancefaq.htm I hope Tony doesn't mind my opening a discussion of some issues on his performance FAQ page here in the newsgroup. This is not meant as...
4
by: Bob Alston | last post by:
Some more, rather specific Access performance questions. IN a split front-end & back-end Access/Jet ONLY LAN situation, and with all query criteria fields and join fields indexed: 1. Is is...
6
by: Nathan Sokalski | last post by:
I am using a DataSet as the DataSource of a DataList in my code. The SQL used to get the data from the database begins with: SELECT...
0
by: =?Utf-8?B?THluZGE=?= | last post by:
Using VB dot net web application, a page uses the drop down list web control. When running on my XP Pro from within Visual Studio or when running from http://localhost, the dropdown list does not...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.