We have a couple of databases under the same instance, one for production and one for test. We want to make sure that the production system always has the resources it needs regardless of what is going on in the other database. Any guidance on how this can be done?
Here is a general overview of what is involved in handling this scenario... note the new options available as of Db2 126.96.36.199!
When multiple databases are created under the same DB2 instance, they become potential competitors for both the system resources and for the DB2 instance level resources.
The system resources are the traditional physical resources: CPU, memory, I/O, and disk.
The DB2 instance level resources at risk include:
- Memory allocated from the DBMS memory set (e.g. the audit and monitor heaps, FCM communication memory for DB2 DPF)
- Agents available for database processing.
Whether or not real competition for these resources occurs depends on the overall capacity of the environment, the amount of resource needed by each database when it is active, and the number of databases that are active at the same time. Also, thanks to the wonders of modern operating systems ;), many of these resources can be over-utilized and still provide sufficient throughput for competing workloads. Deciding which resources actually need to be controlled is therefore usually the result of actual experience with active monitoring of resource consumption on a per database level.
The primary resource that most customers start with in configurations where more than one database is defined under the same instance is CPU. This is often sufficient for most implementations.
The mechanisms available for system resource control in this environment are:
- operating system workload management (on AIX or Linux) and the DB2 WLM dispatcher (available as of DB2 10.1)
- database_memory configuration parameter for each database (https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.config.doc/doc/r0006017.html)
- I/O and disk
- Separate filesystems
- Independent disk controllers
There are two ways to directly control CPU consumption by a DB2 database: integration with operation system workload management (OS WLM) offerings on Linux or AIX; DB2 WLM dispatcher.
The easiest way to allocate CPU between databases is using the DB2 WLM dispatcher (https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.wlm.doc/doc/c0058602.html). It is effective and provides the option of a fixed or variable approach to allocation.
The basic approach to implementing database level CPU allocation using the DB2 WLM dispatcher is described in the DB2 documentation in the section entitled “Scenario 2: Workload management dispatcher operates at instance level; service classes operate at database level” (https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.wlm.doc/doc/c0058620.html).
New in DB2 188.8.131.52, are the wlm_cpu_shares, wlm_cpu_share_mode, and wlm_cpu_limit database configuration parameters which introduce the DB2 WLM Dispatcher concepts at the database level and allow for an even simpler configuration. With these new options, you can explicitly set shares or limits at the database level.
The major caveat to using DB2 WLM dispatcher occurs when the CPU to be controlled is being consumed by work not within its scope (refer to the “Effects of work outside of workload management dispatcher control“ section in the DB2 documentation). In such cases, the best way to address the scenario is to leverage whatever workload management is offered by the operating system and put the db2sysc process servicing each database into a different resource group.