DB2 Performance Tuning

This section discusses some of the problems with DB2 performance on a z/OS mainframe, from a Storage perspective. It is split into

• Storage Controllers, Cache, RAID and general IO response times
• DB2 buffering
• SMF records for DB2

DB2 and IO performance

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 36GB these days, and some of them are 500GB. 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 500GB 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.

Don't put your DB2 data under SMS, you lose all control and can't performance tune it.
Not true IF you 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 dynamic PAV, 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

You can also give preferential treatment to more critical databases by using the IO response time parameters in the Storage Class. MUST CACHE and MAY CACHE lose their original meaning with new controllers, as they are all HAVETO CACHE. However, MUST CACHE uses a better LRU algorithm than MAY CACHE, so there is more chance of MUST CACHE data staying in the cache. Allocate all your critical DB2 databases as MUST CACHE, (by setting a low response time in the SMS storageclass definition) and use a MAY CACHE storage class for less critical databases.

back to top

Use of buffer pools

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

Synchronous read

The application requests a page of data (between 4K and 32K) then waits for it to arrive in the buffer. IO response times should be 2-4 ms.

Sequential prefetch

The application 'knows' it is going to need a chunk of data, so it requests it in advance, then carries on doing its processing while the data is being transferred. Multiple pages are read in one IO operation, using IO chaining. IO response times can be 10-30 ms or so. Sequential prefetch is determined by DB2 at program bind time, so the DB2 optimiser decides then that it wants to do sequential prefetch, and always does it when that program piece is called.

Dynamic prefetch

Similar to sequential prefix, except that sequential processing is detected at runtime. If DB2 detects that 5 out of the last 8 IOs were sequential, it invokes dynamic prefetch. If the rate then drops to less than 5 from 8, prefetch is cancelled. This can be more efficient than Sequential Prefetch, but not always. It is possible for DB2 to invoke dynamic prefetch when the data is not being read sequentially, and this just wastes buffer space.

List sequential prefetch

Data which is not stored sequentially, but is read sequentially. Que? Think of it as an alternate index (its actually called a non-clustering index). The alternate index keys are held in sorted order, but if you read the data sequentially through the alternate index, you will not read it sequentially from disk.

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.

Write IOs

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.

back to top

SMF Records

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.

• Class 1 shows DB2 activity summary information. They are written out every few minutes as specified in DSNZPARM.
• Class 3 contains information about deadlock or timeout incidents.
• Class 4 contains diagnostic information for exceptional DB2 conditions.
• Class 5 records Data Sharing information.

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.

• Class 1 contains basic accounting information.
• Class 2 contains 'in-DB2' time. You need this to determine if the performance problem is in DB2 or in somewhere else.
• Class 3 wait time and would be used to check out storage, CPU or buffer pool problems.
• Class 7 contains Package/DBRM basic accounting information.
• Class 8 contains Package/DBRM level wait time 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.

back to top

By entering and using this site, you accept the conditions and limitations of use