IBM红皮书《DB2 Performance Guide》学习笔记

?? 在书的第6章问题检侧的情景中，列出db2问题检测的先后次序

?? 1、Connection constraints

?? 2、Sorting constraints

?? 3、Locking constraints

???4、Buffer pool problems

?? 5、Cache size problems

?? 6、Miscellaneous problems

???此1-5有专门的指标来判断

?? 一、Connection constraints

???????? 涉及几个参数

???????? dbm参数max_connections、maxagents、maxcagents、max_coordagents

???????? db参数maxappls

???????? 可以通过db2 get snapshot for dbm 检查下面指标，

???????? 在下面中，前面为快照获取的数据，后面一项为配置参数

???????? 1) Remote connections to db manager + Local connections < max_connections

???????? 2) High water mark for agents registered < maxagents

???????? 3) High water mark for agents waiting for a token 越接近0越好

???????? 4) High water mark for coordinating agents < max_coordagents

???????? 检查数据库参数

???????? db2 get snapshot for all on sample

???????? 检查

???????? High water mark for connections < maxappls

?? 二、Sorting constraints

??????? 涉及dbm参数sheapthres?

????????????? db参数 sheapthres_shr、sortheap

??????? db2 get snapshot for dbm 检查dbm参数

??????? 1) Private Sort heap high water mark < sheapthres

????????2) Post threshold sorts 应该非常小

??????? 3) Piped sorts requested与Piped sorts accepted之差非常小，等于0最好

??????? 4) Hash joins after heap threshold exceeded应该非常小，0最好

??????? db2 get snapshot for all on sample 检查db参数

??????? 1) Shared Sort heap high water mark < sheapthres_shr

??????? 2) Sort overflows应该非常小

?? 三、Locking constraints

??????? 涉及db参数dlchktime、locklist、locktimeout、maxlocks

??????? db2 get snapshot for all on sample检查

??????? 1) Time waited on locks、Lock waits应该非常小 否则关注locktimeout参数

??????? 2) Lock escalations、Exclusive lock escalations应该比较小

??????? 3) Deadlocks detected、Lock timeouts应该比较小

???四、Buffer pool constraints

???五、Cache size constraints

??????? 四、五两项主要关注缓存的命中率，命中率一般希望能达到90%以上

?? 六、Miscellaneous constraints

???????? 关注dbm参数

????????????? intra_parallel、maxfilop、maxtotfilop、rqrioblk、num_poolagents

??????? db参数 chngpgs_thresh、logbufsz、num_iocleaners、num_ioservers

??????? 环境变量

??????? DB2_PARALLEL_IO、DB2MEMDISCLAIM、DB2MEMMAXFREE、DB2NTMEMSIZE、DB2_PINNED_BP????

? ?检查完上面的步骤之后，进行到最为艰难的sql语句跟踪

??????? 1) 创建事件监控器

???????????db2 create event monitor msql_mon for statements,connections write to table

?????????????? buffersize 4 blocked manualstart

?????????? db2 flush event monitor sql_mon

?????????? db2 set event monitor msql_mon state 1

????????2) 找出对性能影响最大的sql语句

????????? db2 "select substr(char(agent_id),1,4) agent_id,rows_read,rows_selected,

???????????????? total_sorts,sort_oveflows,(system_cpu_time + user_cpu_time) as?cpu from

???????????????? conn_msql_connection order by cpu desc"

????????? 从上面找出最耗cpu的agent_id，从中找出sql语句

????????? db2 "select substr(stmt_text,1,256) as statment from stmt_msql_statment where

????????????????? agent_id = 上面的id and stmt_operation = 6 "

?