Capacity planning
This topic contains capacity recommendations for setting up IBM® Db2® Data Management Console in your environment.
The following information is based on the result of IBM Relational Warehouse Workload
(IRWW) with Unique SQL Rate=50/s and monitoring profile settings with default values. The criteria
for calculation includes:
- Retain data for package cache in repository for 7 days
- Retain data for other settings data in repository for 28 days
| Monitor DB# | CPU (Cores) | Memory (GB) | Disk for console (GB) | JVM (Max Heap size) | Network (GB) | |
|---|---|---|---|---|---|---|
| Console | 0-20 | 4 | 8 | 15 | -Xmx 2048m | 1 GB |
| 20-100 | 8 | 36 | 15 | -Xmx 24576m | 1 GB | |
| 100-300 | 24 | 64 | 15 | -Xmx 49152m | 1 GB | |
| Repository database | Monitor DB# | CPU (Cores) | Memory (GB) | Disk for repository (GB) - 4 weeks | JVM (Max Heap size) | Network (GB) |
| 0-20 | 4 | 4 | 95 | |||
| 20-100 | 8 | 16 | 950 | |||
| 100-300 | 16 | 32 | 2800 | |||
| Repository database (CFG) | logfilsiz = 8192， logprimary = 25， logsecond = 200 | |||||
Description
Monitor DB #: The maximum number of monitored databases for which the sizing is suggested.
CPU: The number of cores required by Db2 Data Management Console and Db2 repository server.
Memory: The amount of RAM (in GB) required by Db2 Data Management Console and Db2 repository server.
Repository: The amount of disk space required to store product files, work files and monitoring data.
JVM parameter: Indicates the maximum heap size value that needs to be set on the Db2 Data Management Console.
Network: Indicates the recommended network interface card speed.
Note: The sizing information is based on the monitoring
configuration provided in the following table.
| Collection | Collect data every(minutes) | 5 | ||
| Number of SQL statements to capture (package cache) | 20 | |||
| Lock wait threshold(ms) | 30000 | |||
| Limit the number of data read every time | 5000 | |||
| Normalize captured SQL statements | YES | |||
| Persistence | Persistence | ON | ||
| Monigoring data | Keep data for(weeks) | 4 | ||
| Package cache | Keep data for(days) | 7 | ||
| number of fata read for each collection | 5000 | |||
| Alerts Monitoring | Alerts Monitoring | ON | ||
| Availability | HADR database disconnected | YES | Default Value | |
| HADR database not ready for role switch | YES | Default Value | ||
| HADR primary database blocked from writing to the log file | YES | Default Value | ||
| HADR standby database in replay-only window | YES | Default Value | ||
| HADR standby log receiving falling behind log shipping from primary | YES | Default Value | ||
| Standby database log receive buffer full | YES | Default Value | ||
| Log space | YES | Default Value | ||
| Physical memory in use | YES | Default Value | ||
| Virtual memory in use | YES | Default Value | ||
| Cluster caching facility status | YES | Default Value | ||
| Cluster host status | YES | Default Value | ||
| PureScale member status | YES | Default Value | ||
| Incomplete recovery | YES | Default Value | ||
| Invalid log path | YES | Default Value | ||
| Table space backup pending | YES | Default Value | ||
| Table space drop pending | YES | Default Value | ||
| Log archive failed | YES | Default Value | ||
| Database availability | YES | Default Value | ||
| Database partition availability | YES | Default Value | ||
| File system utilization | YES | Default Value | ||
| Table space container state | YES | Default Value | ||
| Table space container utilization | YES | Default Value | ||
| Table space quiesced | YES | Default Value | ||
| Table space state | YES | Default Value | ||
| Table space utilization | YES | Default Value | ||
| Performance | Connections | YES | Default Value | |
| Buffer pool asynchronous read ratio | YES | Default Value | ||
| Buffer pool asynchronous write ratio | YES | Default Value | ||
| Buffer pool hit ratio | YES | Default Value | ||
| Buffer pool hit ratio degrades | YES | Default Value | ||
| Catalog cache hit ratio | YES | Default Value | ||
| Application connections with excessive lock waits over request time | YES | Default Value | ||
| Increased table lock wait and lock timeout event hit rate | YES | Default Value | ||
| Table deadlock occurred | YES | Default Value | ||
| Degree of secondary log allocation | YES | Default Value | ||
| Log space used by dirty pages | YES | Default Value | ||
| Package cache hit ratio | YES | Default Value | ||
| Group buffer pool hit ratio | YES | Default Value | ||
| Total page reclaims per min | YES | Default Value | ||
| Virtual memory in use in CF system | YES | Default Value | ||
| Maximum log space exceeded | YES | Default Value | ||
| Post threshold sorts | YES | Default Value | ||
| Sort overflows | YES | Default Value | ||
| CPU time | YES | Default Value | ||
| Rows read | YES | Default Value | ||
| Rows returned | YES | Default Value | ||
| Statement performance | YES | Default Value | ||
| Time waited on locks | YES | Default Value | ||
| Total activity time | YES | Default Value | ||
| Total activity wait time | YES | Default Value | ||
| Rollback transactions | YES | Default Value | ||
| Rows read per fetched row | YES | Default Value | ||
| Configuration | Diagnostic level | YES | Default Value | |
| Log size | YES | Default Value | ||
| Notify level configuration parameter | YES | Default Value | ||
| Changed pages threshold | YES | Default Value | ||
| Client I/O block size (rqrioblk) | YES | Default Value | ||
| Number of asynchronous page cleaners configuration | YES | Default Value | ||
| Number of I/O servers configuration parameter | YES | Default Value | ||
| Sequential detection (seqdetect) | YES | Default Value | ||
| Block on log disk full configuration | YES | Default Value | ||
| Log archmeth1 enable (logarchmeth1) | YES | Default Value | ||
| Log file size (logfilsiz) | YES | Default Value | ||
| Log space utilization | YES | Default Value | ||
| Package cache overflows | YES | Default Value | ||
| Package cache size | YES | Default Value | ||
| Sort heap threshold | YES | Default Value | ||
| Sort overflows | YES | Default Value | ||
| Automatic maintenance | YES | Default Value | ||
| Automatic runstats collection | YES | Default Value | ||
| Utility impact limit (util_impact_lim) | YES | Default Value | ||
| Workload management collection interval | YES | Default Value |
| Prerequisites settings | Table Space | EVMON_TBS created by conosle |
| Activity event monitor | Enable Activity event monitor | ON |
| Collect data every (minutes) | 5 | |
| keep data for (days) | 28 | |
| Capture in-progress query with event monitor | ON | |
| Capture system workload data | ON | |
| Use administrative task scheduler (ATS) | OFF | |
| Locking event monitor | Enable locking event monitor | |
| Collect data every (minutes) | 5 | |
| keep data for (days) | 28 | |
| Lock wait threshold (microseconds) | 30000000 | |
| Use administrative task scheduler (ATS) | OFF | |
| Utility event monitor | Enable Utility event monitor | ON |
| Collect data every (minutes) | 5 | |
| keep data for (days) | 28 | |
| Use administrative task scheduler (ATS) | OFF | |
| Statistics event monitor | Enable Statistics event monitor | ON |
| Collect data every (minutes) | 5 | |
| keep data for (days) | 28 | |
| Capture system workload data | ON | |
| Use administrative task scheduler (ATS) | OFF |