Results 1 to 12 of 12
  1. #1
    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!

  2. #2
    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

  3. #3
    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

  4. #4
    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.

  5. #5
    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

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,734
    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

  7. #7
    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.

  8. #8
    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.

  9. #9
    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?

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,734
    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

  11. #11
    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.

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,734
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •