If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > DB2 Server High CPU Utilization

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Apr 2002
Posts: 84
Question DB2 Server High CPU Utilization

Dear All,

We are running an application using DB2 in Unix. When the concurrency is high, there is always 100% CPU utilization. I suspect it could due to the setting in OS or DB2. Some parameters are:

Database page size = 4096
Table Space 1 page size = 4096
Table Space 2 page size = 16348
Temp Space page size = 4096
User Space page size = 4096
SysToolSpace page size = 4096
Number of frequent values retained (NUM_FREQVALUES) = 10
Number of quantiles retained (NUM_QUANTILES) = 20
Database heap (4KB) (DBHEAP) = 1200
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 128
Log buffer size (4KB) (LOGBUFSZ) = 1024
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 2048
Buffer pool size (pages) (BUFFPAGE) = 1000
Extended storage segments size (4KB) (ESTORE_SEG_SZ) = 16000
Number of extended storage segments (NUM_ESTORE_SEGS) = 0
Max storage for lock list (4KB) (LOCKLIST) = 10000
Max size of appl. group mem set (4KB) (APPGROUP_MEM_SZ) = 30000
Percent of mem for appl. group heap (GROUPHEAP_RATIO) = 70
Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 128
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = (SHEAPTHRES)
Sort list heap (4KB) (SORTHEAP) = 256
SQL statement heap (4KB) (STMTHEAP) = 4096
Default application heap (4KB) (APPLHEAPSZ) = 256
Package cache size (4KB) (PCKCACHESZ) = 1024
Statistics heap size (4KB) (STAT_HEAP_SZ) = 4384

Anyone could kindly help to provide some hints? Thanks!
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Oct 2007
Posts: 219
get snapshot of dynamic sql in the db
check the queries exec time, check in explain plan
whether tablescan is going, there could be lot of issue
high cpu utilization could be coz of tablescan
regds
Paul
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Apr 2002
Posts: 84
Could it due to some db2/os parameter settings? I'd like to start with the DB2 fine tuning first. Thanks.


Quote:
Originally Posted by Mathew_paul View Post
get snapshot of dynamic sql in the db
check the queries exec time, check in explain plan
whether tablescan is going, there could be lot of issue
high cpu utilization could be coz of tablescan
regds
Paul
Reply With Quote
  #4 (permalink)  
Old
∞∞∞∞∞∞
 
Join Date: Aug 2008
Location: Toronto, Canada
Posts: 2,361
You need to find out which processes are using the most CPU and check the snapshots to see what they're doing (as Paul suggested). If user cpu is high, it could be due to some bad plans.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Apr 2002
Posts: 84
Thanks!

Sorry that I am not DBA. Could you kindly explain how to check the dynamic sql and query exec time? Do you mean I need to extract all the queries from the application and run it in DB2?

Thanks.


Quote:
Originally Posted by Mathew_paul View Post
get snapshot of dynamic sql in the db
check the queries exec time, check in explain plan
whether tablescan is going, there could be lot of issue
high cpu utilization could be coz of tablescan
regds
Paul
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,713
I will tell you one thing. Your dbheap is way too low if you keep LOGBUFSZ = 1024. For most databases, LOGBUFSZ of 256 is fine, unless you are logging a lot of LOB columns. Try 2000 for dbheap.

But most likely you also have bufferpool problems or SQL access plan problems.
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Feb 2009
Posts: 114
Quote:
Originally Posted by qxz;
When the concurrency is high, there is always 100% CPU utilization. ... Anyone could kindly help to provide some hints?
I am not sure what the issue is here. What would you like hints about? Is there a problem with 100% CPU? Would you prefer to have I/O or network as bottleneck?

There are several primary bottleneck possibilities: CPU, I/O, memory and paging, network. CPU is the fastest resource of the ones listed. If CPU is your bottleneck then your application is running as fast as the fastest component of your system. That is always the objective of any database tuning that you do - make your application CPU bound. And in this case it looks like that is what you've achieved. Congratulations! Now call your friendly hardware vendor and buy more CPU, and do it before the end of the year - you will get it at a discount.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Apr 2002
Posts: 84
Thanks all for the advices. There are a few possibilities that cause high CPU utilization:

1. Application (in-efficient SQL statements)
2. DB2 (setting)
3. OS (paging)
4. I/O
5. Network (including firewall)

What I need is to isolate the problem. I suspected it was due to DB2 setting, but how to prove it? Or, how to make sure that the application is not the bottleneck?

Quote:
Originally Posted by db2dummy1 View Post
I am not sure what the issue is here. What would you like hints about? Is there a problem with 100% CPU? Would you prefer to have I/O or network as bottleneck?

There are several primary bottleneck possibilities: CPU, I/O, memory and paging, network. CPU is the fastest resource of the ones listed. If CPU is your bottleneck then your application is running as fast as the fastest component of your system. That is always the objective of any database tuning that you do - make your application CPU bound. And in this case it looks like that is what you've achieved. Congratulations! Now call your friendly hardware vendor and buy more CPU, and do it before the end of the year - you will get it at a discount.
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Apr 2002
Posts: 84
If the database tables are split into two tablespace, will it cause performance issue when executing query that needs to join tables from both tablespace?


Quote:
Originally Posted by qxz View Post
Thanks all for the advices. There are a few possibilities that cause high CPU utilization:

1. Application (in-efficient SQL statements)
2. DB2 (setting)
3. OS (paging)
4. I/O
5. Network (including firewall)

What I need is to isolate the problem. I suspected it was due to DB2 setting, but how to prove it? Or, how to make sure that the application is not the bottleneck?
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,713
Quote:
Originally Posted by qxz View Post
If the database tables are split into two tablespace, will it cause performance issue when executing query that needs to join tables from both tablespace?
No, it will not hurt performance. DB2 can walk and chew gum at the same time. In fact, it "could" help performance (but don't expect any miracles or even a measurable difference in performance with multiple tablespaces).

The actual join takes place in the bufferpools, not on disk, although DB2 might (or might not) need to spool the joined table to a system temporary tablespace in a work table while it is executing the query. But tables in the system temporary tablespaces always go through bufferpools also (except for LOB columns).

In DB2 for z/OS where you have set up a Simple tablespace with multiple tables existing on the same page in that one tablespace, there could be a theoretical advantage in that case. But that is a fairly obscure situation, and again, it would be difficult to measure any performance improvement in most cases.
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
Reply With Quote
  #11 (permalink)  
Old
Super Moderator
 
Join Date: Aug 2001
Location: UK
Posts: 4,649
Quote:
Originally Posted by qxz View Post
Sorry that I am not DBA.
Thanks.
Quote:
Originally Posted by qxz View Post
What I need is to isolate the problem. I suspected it was due to DB2 setting, but how to prove it? Or, how to make sure that the application is not the bottleneck?

Looks like you want to blame your DBA and looking for pointers to support the cliam
__________________
Visit the new-look IDUG Website , register to gain access to the excellent content.
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,713
If you are running on UNIX or Linux, then 100% CPU utilization will cause big problems for DB2. Make sure that all your monitor switches (except timestamp) are off in the dbm config. (db2 get dbm cfg).

The most important performance parameter in DB2 is usually the bufferpools:
select * from syscat.bufferpools (please post output). Also post output from vmstat (to list physical memory in the machine). vmstat 5 5

But extremely high CPU % usually means you have a lot table scans in memory, which is usually an SQL access plan problem (which includes the possibility of insufficient indexes for particular queries).
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On