Checklist To Tune DB2 Performance

DBA suggests few of the recommended database performance tuning tips for IBM DB2 database engine that is running on Linux server.

• Linux kernel parameters tuning that applicable to DB2 version 8.1 and 8.2.
  
  Executing command sysctl -A to print out current kernel parameters setting. Some of the notable are
  
  
  • kernel.sem (Semaphore setting)
    Recommended Value : 250 256000 32 1024
    
    
  • kernel.msgmni (Maximum system-wide queues)
    Recommended Value : 1024
    
    
  • kernel.msgmax (Maximum size of messages in byte)
    Recommended Value : 65536
    
    
  • kernel.msgmnb (Default size of queue in byte)
    Recommended Value : 65536
    
    
  In order to retain these changes of kernel parameters on every system reboot, add the updated kernel parameters setting to /etc/sysctl.conf system file to do the great job.
  
  
• DB2 database configurable parameters tuning.
  
  After connecting to database called my_test_db by executing db2 connect to my_test_db, running another DB2 command db2 autoconfigure apply none to get DB2 database engine calculate the best recommended value of DB2 database configurable parameters. Some of the notable are
  
  
  • LOGPRIMARY / LOGFILSIZ
    Larger log buffer required for OLTP workloads with high transaction rate.
    
    
  • CHNGPGS_THRESH
    For databases with heavy update transaction workloads, make sure there are enough clean pages in the buffer pool by setting the parameter value equal to or less than the best recommended value calculated by DB2 database engine.
    
    
  • LOCKLIST
    The amount of storage that is allocated to the lock list. Increase this value if lock escalations causing performance concerns, that logged as warnings in the db2diag.log file.
    
    
  • DBHEAP
    Database heap per database. Needs to be increased for larger buffer pools.
    
    
  • NUM_IOCLEANERS
    Large buffer pools require a higher number of asynchronous page cleaners.
    
    
• Alternate page cleaning algorithm tuning.
  
  DB2 UDB ESE v8.2 introduces a new buffer pool page cleaning algorithm which is not turned on by default. It is necessary to test this new page cleaning algorithm with the database workload. To turn on this alternate page cleaning algorithm, executing DB2 command
  
  db2set DB2_USE_ALTERNATE_PAGE_CLEANING=YES

Related information:

• Search more related info with Google Search engine built-in