A DB2 Performance Tuning Roadmap_PHP Tutorial

A DB2 Performance Tuning Roadmap Wherever he walks, where his knees stand, his sword is straight, his sword is straight, and his voice is in the middle. The dance that combines with "Sanglin" is the meeting of "Sutra". Wen Huijun said: "Hey, that's great! Is this the level of technology?

Speaking of DB2, everyone may be unfamiliar with it. Most of them are familiar with Oracle, SQLserver, MYSQL and the popular NOSQL such as MongoDB and REDIS. I will learn more about it. The author has a lot of contact with DB2 due to work, so I will talk about my understanding here. Due to the author's own limitations, the description of many problems may be accurate. Everyone knows that MYSQL is a single database. Process multi-threading, ORACLE behaves differently on Windows and Linux. Under Windows, it provides services in a single process and in a multi-process manner. DB2 also provides services in a similar manner to multiple address spaces (similar to processes).

Original text

This process processing method of DB2, OVERHEAD is inter-process communication, which introduces many sub-thread classifications, such as CICS THREAD, ALLIED ADDRESS SPACE, DATABASE ACCESS THREAD, These concepts may be easier to understand through inter-process communication. This also introduces the so-called different concepts of CALL ATTACHEMENT FACILITY, which are actually due to the different APIs that DB2 opens to the outside world. For more information about DB2 DATA SHARING, please refer to

1. A brief analysis of DBMS CLUSTER technology in DB2
2. Initial exploration of DBMS-DSG - XCF
3. DSG CF cache talk
4. DSG CF cache talk 1
5. DSG CF cache talk 2
6. Introduction to the principle of DB2 Buffer
7. Theoretical basis of database design

OUTLOOK OF PERFORMANCE

The content of this article is sorted out after writing the basic skills of system tuning, and at the same time, the application tuning part is added. The learning of knowledge itself is also a step-by-step process. First of all, we need to clarify the concept of performance. What is performance? Its corresponding English word is performance. The explanation given by Wikipedia is the time and resources consumed by the computer to complete a useful task WIKI. So good high performance means using fewer resources and completing work faster. The goal of performance is to have no cavities, haha

1. realistic, that is, the performance can be achieved under the current technology, such as the average transaction response time of 0.1 is achievable, 0.000001, NOT REALISTIC
2. reasonable Reasonable
3. quantifiable, such as RATIO, PERCENTAGES, NUMBERS, rather than good, perfect, this kind of empty description
4. measurable, measurable

According to national conditions, all of these are premised on meeting the needs of the leader. After specifying the above goals, it is the monitor to see if the current system meets the requirements and then perform optimization. There are three types of monitors based on the frequency of execution:

1. routine monitoring
2. online/realtime event monitoring
3. exception monitoring

In fact, DBAs face more of the problems discovered in 2 and 3. Time is tight and the tasks are heavy. If it can be avoided, it is fine. If it cannot be avoided, emergency changes need to be implemented. But if monitor 1 has a good foundation, it can detect many problems in advance and nip them in the bud. Monitor 1 is more about collecting performance information and the overall operation of the system.

总体指导思想

1. TOP-DOWN TUNEING
2. DEVIDE AND CONQUER
3. 二八原则。花80%的时间解决20%的问题，带来80%的收益，即最佳性价比
4. 一个前提：当系统运行或是应用出现问题的时候，我们总是假定这是一个由量变到质变的过程[变更除外，这种情况下需要紧急回退]，即我们总是假设系统或是应用在以前是正常的，我们需要一个benchmark.这就给我提供了一个思路，当你没有思路时，你可以和历史数据进行比较，从而发现问题
5. 关于定性和定量的问题,相对来说，定性容易，定量有时候还是比较困难的
6. 从管理的角度，调优是一个持续改进的过程,应该是一个闭环，即监控发现问题，分析问题，解决问题，而后继续监控

7. 一点体会： 其实调优本身也是一个资源配置的问题，在特定的场景之下，如何把有限的资源进行有效的配置，从而达到组织的目的。 整个组织目前拥有的资源，这里只对计算机系统调优而言：

   1. CPU
   2. IO
   3. LOCKING
   4. STORAGE5. human resource 当然就是你了
   5. NETWORKING 可忽略
   6. SYSTEM HARDWARE & OTHER SOFTWARE SUCH AS CICS/ZOS/CFCC

影响这些resource的方式不外乎：

1. got enough 2. not enouth 3. too much 4. inefficient 5. what are the available controls? (fixes) 

两大方向

系统调优 应用调优

系统调优

关于系统调优前面已经介绍过了--系统调优的基本功,这里的任务就是如何在总结提炼.那篇文章介绍的更多内容其实对应的是routine monitor，

CICS性能数据收集

交易性能数据对应的SMF 类型为110,对应的分析工具CICS PA

SMF Type 110 (subtype0) — CICS Journal Record SMF Type 110 (subtype1) — CICS Monitoring Record SMF Type 110 (subtype2) — CICS Statistics Record 

DB2 性能数据收集

DB2 SMF 对应的SMF TYPE 为100,101,102,其中

SMF TYPE=100 DB2 SUBSYSTEM STATISTICSSMF TYPE=101 DB2 ACCOUNTING SMF TYPE=102 ALL OTHERE PERFORMANCE 

SMF TYPE=100 的表格如下

CLASS	DATA COLLECTED	IFCID
1	Statistics data	1, 2, 105,106, 202, 225
2	Installation-defined statistics record	152
3	Deadlock, lock escalation, group buffer pool, data set,extension information, indications of long-running URs, and active log space shortages	172, 196, 250, 258, 261, 262, 313, 330, 335, 337
4	DB2 exceptional conditions	173,191-195, 203-210, 235, 236, 238, 267, 268, 343, 402
5	DB2 data sharing statistics record	254
6	Storage usage details	225
7	DRDA location statistics	365
8	Data set I/O statistics	199

SMF 本身的结构也是一个树形层级结构,如果打算收取某一类型的trace，你需要关注三个方面，

1. TRACE TYPE
2. CLASS
3. IFCID

这样对应的收取trace的命令就很好理解了

START TRACE(S) CLASS() IFCID(172) DEST(SMF) ---TNODIS TRACESTOP TRACE(S) TNO(XX) recommand defualt trace:start trace(s) c(1,3,5,6,7,8) 

解读stat

这里首先介绍SMF TYPE=100,由上面的表格，我们可以了解到stat报表包括的大体内容，下面我们逐一介绍，让你对报表有一个大体的了解，有时候自下而上解决不了问题的时候，stat就是一个关键的突破口。 STATISTICS 性能数据收取的时间颗粒度granularity为1分钟，相比较SMF TYPE101，102，它的量是很少。 考虑解读性能数据的重要性，后续单独写一篇来介绍，你放心，绝对值得写一章。 在结束准备工作之前，在向你介绍一个性能数据在一个颗粒度内是如何计数的，主要分为3类：

1. SNOPSHOT VALUE--current value 即性能数据收取时间时对应的实时值
2. HWK --HIGH WATER MARK 对应的时间颗粒度内的最高水位值
3. ACCUMULATE VALUE--累加值，时间颗粒度内一个逐渐累加计数值作者注明确这一点对性能数据解读很重要。

16年第一篇，希望有一个好彩头

http://www.bkjia.com/PHPjc/1095030.htmlwww.bkjia.comtruehttp://www.bkjia.com/PHPjc/1095030.htmlTechArticleA DB2 Performance Tuning Roadmap 庖丁为文惠君解牛，手之所触，肩之所倚，足之所履，膝之所踦，砉然向然，奏刀騞然，莫不中音。合于《桑林》...