Monitoring an ERP Application in DB2 for z/OSFebruary 15, 2011 Monitoring an ERP application suite involves using several basic measurement techniques. Since it probably co-exists with other applications within a DB2 subsystem or data sharing group, a means must be found to separate ERP package performance measures from those of other applications. Additionally, when measuring subsystem performance you must isolate the effects of the package from those of other applications. Monitoring an ERP Application in DB2 for z/OSCreating Measurement ProcessesThere are three things to remember when creating measurement processes:
What to Monitor; What to MeasureThere are two general categories of measurements: system-related and application-related. These categories overlap somewhat, but generally speaking system-related measurements are at a high, or global level. Applications demand work and resources from the DB2 subsystem (such as data storage and retrieval) and the subsystem then performs work on the applications' behalf. Some common system-related measurements are: Virtual pool threshold attainment
Sortpool, EDM Pool, Rid Pool filling or failure
Logging rate and Log Write performance
Global buffer pool usage
Subsystem address space CPU usage
Application-related measurements are more detailed and specific. They deal with suites of SQL statements (either separately or taken in sets). Here, the most common measure deal with I/O: Buffer Pool hit rate (or page residency times)
Waits for synchronous I/O
Prefetch rates
Hardware Cache usage
Index efficiency
Data access paths
Measurement ClassesThere are also two classes of measurements that you will need: measurements of resources (which may be overused, underutilized, or constrained) and measurements of behaviors (which may be symptoms of underlying problems). Resources Memory
DBM1 Address Space (Virtual Storage constraints)
EDM Pool, Sort Pool, RID Pool
Virtual Pools (Page Residency)
CPU
I/O Subsystem
Network
Behaviors Locking
Compression
Work File Extents
Deadlocks, Timeouts
The difficulty in dealing with behaviors is that they are symptoms only; they are not the problems themselves. It is easy to fall into the trap of thinking that you have isolated a problem to a few SQL statements. True, making changes (adding OPTIMIZE FOR n ROWS, re-designing indexes, running multi-column RunStats) may increase statement execution speed; however, the net result may be that you have obscured or totally missed the underlying problem(s). This is why you need to gather measurements from all classes: resources and behaviors, system and application. In order to better understand what you are measuring, here's an example from one installation in the early stages of an ERP package tuning effort. Table 1 shows a cross-reference of their measurement categories and classes with specific measurements. Table 2 shows some typical tactics and tuning efforts for each quadrant. Table 1: Measurement Categories and Classes for an ERP Installation
Table 2: Typical Tuning Efforts by Measurement Category and Class
So, What Should I Measure?For ERP systems there are three initial areas to emphasize, depending upon data and transaction volumes. These relate to the package's use of memory and the I/O subsystem:
The DBM1 Address SpaceThe DBM1 address space contains many areas of memory called pools essential to proper ERP functioning. Among these are the Virtual Pools, EDM Pool, Sort Pool, RID Pool, and others. You should construct a spreadsheet that lists your various storage allocations. You can estimate usage based on ZParm settings for most of these pools; however, in a memory-constrained environment you should use whatever tools you have available to get real-time storage measurements. For example, it is possible to use DSNWDMP (described briefly in the IBM DB2 Diagnosis Guide) to dump the DBM1 address space and get actual pool size measurements. Other third-party tools may have summary or detail statistics you can use. The important things to do (as mentioned at the beginning of this article) are to:
So, you can begin with a simple daily (or hourly) measurement of total DBM1 address space real storage usage. Store the measurements over time, and graph the result. You will probably find that memory usage increases sharply on DB2 subsystem startup. This should be no surprise; many of the pools are allocated only on first use; also, some control blocks stored in DBM1 are related to datasets that are not open yet. Ensure that ZParms and other changes are controlled (SRTPOOL, MAXRBLK, MAXKEEPD, EDMBSPAC, EDMPOOL, DSMAX, CTHREAD, MAXDBAT, buffer pools, etc.). The settings for these and other ZParms will affect memory allocation in the DBM1 address space. As the production week rolls on your DBM1 real storage graph should stabilize, particularly as ERP package functions are executed. For example, if your package includes a payroll function you should see real storage allocations jump as payroll begins running. Over time if you do not see real storage usage stabilize then it is possible that there is a DB2 subsystem problem. Check your maintenance level and refer to IBMLink for APARs related to storage issues. The EDM PoolThe EDM Pool contains database objects such as database descriptors (DBDs) and, if you have implemented dynamic SQL caching, the dynamic SQL cache. Most third-party tools have a process for measuring the EDM pool contents, including the percentage of the pool taken up by each object category. Begin with this and (as above) map your EDM Pool usage over time. Pay particular attention to the ratio of Request for Pages to Page Already in Pool for each object category. If these ratios are all above 99% it indicates that, in general, your EDM Pool is functioning well. Whenever DB2 needed a database object or a prepared SQL statement, 99% of the time the object was already in the EDM pool and no I/O to disk was required to fetch it. The dynamic SQL cache needs to be treated a bit differently. ERP packages typically implement much of their functionality with dynamic SQL. Good performance will necessitate that you cache the prepared versions of these statements. The Global Dynamic Cache is allocated when ZParm CACHEDYN is set to YES. Based on your DBM1 storage allocation report you may consider your environment to be virtual storage constrained. In that case, consider setting ZParm EDMDSPAC to YES. This will place the global cache in a Data Space, thus outside of the DBM1 address space. (Note that you must also set additional ZParms that control the size of this data space. These ZParms all begin with "EDM".) One final note on the EDM Pool. There seems to be some difference of opinion on the Pages in Use statistic, especially when it is near 100%. Many experts recommend sizing the EDM Pool to allow a certain percentage (say 10%) of the EDM Pool to be unused. Theoretically, this allows for a cushion in case a long-running (and not committing) transaction is responsible for holding object definitions in the EDM Pool for an extended period. Continue to measure your EDM Pool usage over time. As the maximum size stabilizes, note the high-water mark. If the pool is near 100% used, increase its size gradually to allow for about 10% non-used. Continue to monitor over time. A sudden jump to 100% used may signal the appearance of a rogue application or process. The Virtual PoolsWith most ERP package processes being I/O-constrained, the virtual pools deserve special attention First, it is assumed that you have assigned objects to the virtual pools based on activity (random access, sequential access, etc.). (You can verify these assignments by a simple query against SYSTABLESPACE and SYSINDEXSPACE.) Now you will need to collect measurements of virtual pool use. You can begin with the -DISPLAY BPOOL . . . DETAIL command if you wish; alternatively, there are several IBM and third-party tools available for measuring and tuning buffer pools. Your initial measurements should concentrate on when and how often thresholds were exceeded. After that, you can continue with hit ratios. Threshold settings are described in the Administration Guide, including how best to determine them for random and sequential workloads. A complete discussion of virtual pool measurements and tuning is beyond the scope of this article. I recommend you begin with the materials on virtual pool tuning in the Administration Guide. In the next article, I'll cover tuning an ERP application. Wikipedia - Enterprise Resource Planning - Jan 01, 2011 - IBM - IBM InfoCenter - Jan 13, 2011 - IBM - System z Parallel Sysplex Best Practices - Jan 01, 2011 IBM - Infrastructure Solutions: SAP IS Automotive on an IBM Platform - Dec 01, 2008 |
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
