473,554 Members | 5,359 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 2096
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...int ersting; 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...int ersting; 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...int ersting; 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 "optimizati ons" 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***** **********@nosp am.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

recommendati ons

>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...int ersting; 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
1165
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: "puzzlecracker" <ironsel2...@gmail.com> - Find messages by this author
1
2147
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 event alert" is the only item listed in the drop down. Is there some setting on the Server that I am missing that needs to be activated? I am...
0
387
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. My test app performs inserts across 4 related tables, each set of 4 inserts representing a single theoretical "device" object. I report how many...
4
1758
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 problem is that we have now dropped the same restrictions in the production environment but have seen absolutely no performance difference since the...
16
2640
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 criticism, at all, as I am not alleging error. I'm just asking about a couple of things to open up the discussion to see what people have to say about...
4
1768
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 good form to have a single query with base table with criteria joined to a related table - all in one query? Or should I do a two-step, first query...
6
2304
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 members.organization,artists.artist,artists.email,artists.website,members.email FROM members INNER JOIN artists ON members.memberid=artists.memberid WHERE Notice that both tables involved in the SELECT...
0
930
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 have any performance issues. The tester states that they have to click on the drop down list multiple times in order to see the drop down list. ...
0
7580
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7503
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7781
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8017
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7869
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6121
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5421
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5140
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3531
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?

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.