What's throttling the database?

Posted by Troels Arvin on Server Fault See other posts from Server Fault or by Troels Arvin
Published on 2010-03-04T22:58:29Z Indexed on 2010/04/04 11:03 UTC
Read the original article Hit count: 234

Filed under:

profiling

|

db2

|

database-performance

Hardware: Intel x86_64 with 192GB of RAM.
OS: CentOS 5.4 x86_64.
DBMS: DB2 v. 9.7.1 64 bit.

During certain special workloads (e.g. parallel REORGs/RUNSTATs), I've seen the server transporting 450MB/s with 25000IO/s (yes, there is probably some storage system caching happening here) while all CPU cores were happily working in an even mix of usermode/wait. And disk benchmark tools can also bring some very satisfying bandwith and IO/s numbers to the table.

On the other hand, we also have another scenario: A single rather complex query with at least one large table scan. db2's "list applications" reports that the query is Executing (not locked). IO: At most 10MB/s, 500 IO/s; CPU: two cores in 99.9% wait state, all other cores 100% idle. The tables which the query reads from have been altered to have LOCKSIZE=TABLE, so I would think that lock list work is zero.

What's going on in such a situation? What tools/snapshots/... can I use to gain better insight in such a case?

© Server Fault or respective owner

Related posts about profiling

Any recommended java profiling tutorial?

as seen on Stack Overflow - Search for 'Stack Overflow'
Is there any recommended java application profiling tutorial? I am now using jProfiler and eclipse TPTP with my profiling. However, although equipped with wonderful weapons, as a newbie in java profiling, I am still missing the general theory and skill in pinpointing the bottleneck. So would you please… >>> More
Servlet Profiling

as seen on Stack Overflow - Search for 'Stack Overflow'
What is the best way to undertake Servlet profiling? In particular I am looking for a solution that can show the execution times of the method calls within the servlet. I am running the Servlet within a Tomcat environment. I have tried VisualVM but it only allows me to see the method calls from… >>> More
SQL Server: Profiling statements inside a User-Defined Function

as seen on Stack Overflow - Search for 'Stack Overflow'
I'm trying to use SQL Server Profiler (2005) to track down some application performance problems. One of the calls being made is to a table-valued user-defined function. This function wraps a select that joins several tables together. In SQL Server Profiler, the call to the UDF is logged. However… >>> More
DiscountASP.NET Launches SQL Server Profiling as a Service

as seen on ASP.net Weblogs - Search for 'ASP.net Weblogs'
DiscountASP.NET announces enhancing our SQL Server hosting with the launch of SQL Server Profiling as a service. SQL Profiler is a powerful tool that allows the application and database developer to troubleshoot general SQL locking problems, performance issues, and perform database tuning. With our… >>> More
DiscountASP.NET Launches SQL Server Profiling as a Service

as seen on ASP.net Weblogs - Search for 'ASP.net Weblogs'
DiscountASP.NET announces enhancing our SQL Server hosting with the launch of SQL Server Profiling as a service. SQL Profiler is a powerful tool that allows the application and database developer to troubleshoot general SQL locking problems, performance issues, and perform database tuning. With our… >>> More

Related posts about db2

link DB2 AS/400 (system i) and DB2 AIX

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi, everybody. I have two different database in DB2, a in DB2 AS400 and other in DB2 AIX, is possible link this database, similar to link by Oracle? Thank you in advance. erva. >>> More
Speed-start your Linux App: Using DB2 and the DB2 Control Center

as seen on Internet.com - Search for 'Internet.com'
This article guides you through setting up and using IBM DB2 7.2 with the Command Line Processor. You'll also learn to use the graphical Control Center, which helps you explore and control your databases, and the graphical Command Center, which helps you generate SQL queries. Other topics covered… >>> More
DB2 LUW Security - The DB2 DBA as a Locksmith

as seen on Internet.com - Search for 'Internet.com'
Database security should have multiple locks at multiple layers with multiple keys (and perhaps some barbed wire, an electric fence, a moat and a mean junkyard dog as well). With all these locks and keys, some locksmith skills are certainly useful. Rebecca Bond introduces some of her favorite DB2… >>> More
Why can't Perl's DBD::DB2 find dbivport.h during installation?

as seen on Stack Overflow - Search for 'Stack Overflow'
We are using a Perl utility to dump data from DB2 database. We installed DBI package and it is asking for DBD package also. We dont have root access and when we try to install DBD package we are getting the following error: ERROR BUILDING DB2.pm [lijumathew@intblade03 DBD-DB2-1.78]$ make make[1]:… >>> More
DBI::DBD package not getting installed for Perl?

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi, We are using a Perl utility to dump data from DB2 database. We installed DBI package and it is asking for DBD package also. We dont have root access and when we try to install DBD package we are getting the following error. ERROR BUILDING DB2.pm [lijumathew@intblade03 DBD-DB2-1.78]$ make make[1]:… >>> More