This page discusses some of the problems with DB2 performance on a z/OS mainframe, from a Storage perspective. It is split into
In any performance tuning exercise, the only important result, is faster access at the customers terminal. This sounds obvious, but lack of understanding about DB2 often means that storage people make unreasonable demands of their DBAs, and the reverse is often true too. Some of the myths are,
DB2 performance is always poor, and affects the rest of the storage subsystem.
This is not true, DB2 does a lot of its IO asynchronously, and does it in bulk. This means that you can see long response times on DB2 volumes, but these are not a problem, they do not affect the customer. I've heard Storage people demand that their DBAs detune the DB2 systems, to shorten response times. The section on buffering should explain why this is not a good idea.
RAID DASD does not perform as well as non-RAID, so databases are best kept on SLED disks.
Not true. Some vendors will only supply RAID disks. Other vendors will allow their subsystems to be configured as non-RAID, but this is not a good idea as a lot of data could be lost. It is hard to buy a physical disk smaller than 146GB these days, and some of them are 2TB. Also, they are commodity disks, they fail. A failure rate of 2-3 per year is not uncommon. If you define these as JBOD, and they fail, just imagine recovering a terabytes worth of databases. In general, RAID1 performs as well, or better than non-RAID. RAID5 can be worse for cache read misses, or for very large sequential writes, but on average, RAID5 with sufficient cache performs as well as non-RAID. For me, there is no contest, I would always put all data on RAID disks. Also, nobody makes true CKD 3390 disks anymore. They emulate CKD on FBA disks. This means that the old DASD performance tuning mantras based around tracks, cylinders and file extents don't have any real meaning anymore.
Smaller, very active databases can be placed on Solid State disks to get really good I/O performance. An even better option is to use storage dynamic tiering, where data access is evaluated at block level and very busy portions of databases are automatically moved to Solid State disks, then moved off again if they become less active.
Don't put your DB2 data under SMS, you lose all control and can't performance tune it.
Not true and even less relevant with DB2 v10 as it insists that certain databases must be SMS managed. However you must design your SMS pools correctly, with input from your DBAs. You may need to define some small 'high performance' pools, and you may have to give your DBAs selective use of a Guaranteed Space storage class. See the Converting DB2 to SMS for details. DBAs tend to dislike SMS because they are unable to manually place and relocate their databases to avoid performance hotspots. If your DASD supports PAV, and especially if you use HyperPAV, hotspots should not be an issue. There is a brief PAV explanation in the mainframe disk section.
DB2 is optimised to run under older storage controllers like the 3990-6, so it issues commands to switch off cache if it thinks it will not get benefit.
If you have modern DASD controllers installed, then they will have large amounts of cache, and usually ignore the switch off commands. You need to alter the following parameters
|Parameter||Default setting||Modern DASD setting|
The DNZPARM SEQCACHE BYPASS used to give DBAs the option to bypass cache and go direct to disk. This does not apply anymore as all disk subystems cache by default. The BYPASS parameter now means that DB2 will use Sequential Detection, while SEQCACHE SEQ means that DB2 will explicitly do Sequential prefetch, which is faster than BYPASS.
SEQPRES does the same function, but is used by utilities for loading or reorganising databases. If it is set to YES then it will try to keep pages in cache for longer, so IBM recommends it be set to YES.
This section is intended to give Storage people a flavour of how DB2 buffering works. As such, it is too simplistic for DBAs, who will need a lot more detail. In particular, note how the buffers introduce asynchronous IO. This buffers up IO and processes them in bulk, so they appear as long response times. This can appear to be a performance problem, but in fact this is very efficient. The bottom line is that the prefetch buffering gets data into the CEC memory before the customer needs it. That makes the applications run a lot faster
DB2 uses four kinds of buffer processing
The amount of data transferred in a single IO depends on the buffer pool size and the page size, and will be between 2 and 32 pages. DB2 has several thresholds which control buffering. Two of them are Data Management (DMTH) and Sequential Prefetch (SPTH). If SPTH is exceeded, then prefetch will be cancelled. This is usually a symptom that the bufferpools are too small. Ask your DBAs the check out their DB2 PM reports to see if this is happening. A good benchmark to aim for is to make the buffer pool big enough to hold randomly read pages for five minutes.
It is probable that once a page is prefetched into a buffer, then it is unlikely to be accessed again by a random request. This means that it is best to keep random pages in the buffer for longer than prefetched pages. Prefetched page buffer usage is controlled by the VPSEQT parameter, which defaults to 80%. This means that up to 80 percent of the virtual pool can be used to cache prefetched pages. In general, a value of 20% to 30% is enough to hold prefetched pages for a few seconds, as this is plenty of time for the pages to be used before they are swapped out, and it prevents the buffer pool from being swamped by prefetched pages. You can calculate prefetch page rates from standard buffer pool statistics records. If you are using a hiperpool, HPSEQT should be set to zero so that no prefetched pages are moved to the hiperpool, but VPSEQT may need to be higher than 20-30%. If the buffer pool just contains the work database DSNDB07, VPSEQT should be set to 90% or more, as nearly all of the read activity is prefetch.
Remember that bigger is not necessarily better. If the buffer pools are too big, the z/OS system may start paging, which will adversely affect response times.
DB2 does asynchronous writes unless it needs to free up buffer space, or if is doing a Checkpoint, log switch or system shutdown. The Immediate Write Threshold (IWTH) is set to 97.5% of buffer pool space. if the IWTH is exceeded, DB2 does Synchronous writes and the application waits until they are complete. DB2 will now do a write for every table update until the buffer pool usage drops again. This means that if the IWTH is exceeded, write IOs will be excessive.
Asynchronous writes processes 4-32 pages at once, which is up to 128K. If the buffer pools are large, then DB2 does a lot of write IO at checkpoint time, and this can cause problems. IBM recommends that databases are tuned at table space level. Busy table spaces should be kept in the buffer pool, while table spaces with light usage should not be kept, to reduce the amount of write IO at checkpoint time. Also, make sure the LOGLOAD parameter is not set too high, as this determines frequency of checkpoints.
Just to complicate matters a little more, log handling is also controlled within the application program. If a write is specified as NO WAIT then the log record is written to buffers. If FORCE is specified, then the record is committed, and buffer flushed. In the latter case, the application waits until disk write complete, in the former, the log records write out asynchronously when buffer thresholds are met. Ideally, keep the log buffers as big as possible, and keep the WRITE THRESHOLD at less than 20% of the no. of buffers.
A good place to start, if you have DB2 performance problems, is to collect SMS records. Three SMF types specifically apply to DB2. The SMF section describes how to collect SMF data, and also details general SMF Storage records.
SMF 100 records have minimal overhead, and in general, all classes can be turned on. They contain DB2 system-wide information statistics.
SMF 101 records contain individual DB2 thread-related information. They are a good place to start to investigate DB2 performance. You will usually need sub types 1, 2, and 3, and also 7 and 8 if you're would like DB2 package level information.
SMF 102 records contain detailed performance-related information. These records collect lots of data, use lots of resource, and can seriously affect DB2 performance. Use with caution. It could be worth investing in a product like Spectrum SMF Writer that reads and simplifies SMF 102 records.