DB2数据库管理最佳实践笔记-10日常运维.doc

10.1 日常运维工具概述Runstats是run statistics的缩写，意思是收集统计信息，目的是为DB2优化器提供最佳路径选择；Reorg是重组的意思，目的是减少表和索引在物理存储上的碎片，提供性能；Reorgchk是重组前的检查Rebind是对一些包、存储过程或静态程序进行重新绑定。几个工具的执行流程：首先通过Runstats收集表和索引的统计信息，然后执行Reorg重组，如果有必要则执行，然后再次收集统计信息。最后，对于静态语句、存储过程等，执行Rebind绑定。10.2 Runstats在系统运行一个查询的时候，优化器需要决定用某种方式来访问数据。只有当DB2对表中的数据有一个大概的了解，才能知道每一步操作大约需要处理多少数据，返回多少行。当优化器了解了这些信息后，就会根据一系列的运算，判定出各种访问途径所需要消耗的资源，然后从中选择一个消耗资源最少的方法。最普通的Runstats就是统计表和索引中有多少行数据，有多少不同的数值。Runstats命令使用DISTRIBUTION参数手机数据分布。数据分布分为两种，一种叫做频率采样（Frequency）,一种叫做百分比采样（Quantile）。当收集数据分布时，两种采样方式都会被收集。其中频率采样是手机表中拥有相同数量最多的几行，比如10000行数据中9000行为10，然后500行为9，然后100行为8，剩下的部分平均分布。如果我们制定Frequency为3的话，那么系统就会记录下来有9000行10,500行9，然后100行8，剩下的部分在估算时则假定平均分布。而百分比采样则是将整个10000行数据分成相等大小的若干段，然后记录每一段的段首和段尾的数值，当需要查询一个数据段时（比如C110 AND C115）,就可以根据每一个数据段的启始数值加上段落的大小，估算出符合查询条件的记录数量。理论上，数据分布收集的越细致越好。但是经过细致的数据分布信息可能会导致DB2在优化SQL时需要处理更多的信息，并占用更多的系统存储空间，可能会导致性能的下降。因此，一般情况下我们建议使用默认的数据分布采样设置，也就是频率采样为10，百分比采样为20。但是，有些情况下，则需要根据实际情况调整分布参数。Runstats的语法比较复杂，在实际应用中，最常用的几种使用方法如下：1）为表和索引收集统计信息，包括数据分布，代码如下：RUNSTATS ON TABLE . ON ALL COLUMNS WITH DISTRIBUTION AND DETAILED INDEXES ALL2)收集索引统计信息，如果表上没有统计信息，该选项会同时对表做统计。但该选项并不会收集数据分布信息，代码如下：RUNSTATS ON TABLE . FOR INDEXES ALL3)使用伯努利算法抽样统计。DB2会扫描每一行数据，但只对一定比例的抽样数据进行统计。这种方法一般用于数据仓库中的大表。如果收集全表数据统计，将需要很长时间，并占用CPU资源，应用性能会造成影响。下列采用了伯努利10%抽样统计，代码如下：runstats on table . tablesample bernoulli(10)如何查看一个表是否收集了统计信息？一个比较有效的办法是查看syscat.tables的stats_time字段，如果该字段值为空，则表示没有收集过统计信息，否则会显示统计信息的时间：db2 select char(tabname,20) as tabname,stats_time form syscat.tables where STATS_TIME is NULLDB2 runstats 命令只能针对单表执行，而无法对整个数据库做运行时统计（虽然可以使用reorgchk update statistics 对所有表收集统计信息，但reorgchk并不会收集分布统计）。可考虑将需要执行runstats的表写入一个脚本，以下是一个脚本范例：#!/bin/kshif $#createRunstats.txtgrep RUNSTATS createRunstats.txt runstats_detailed.sqldb2 -tvf runstats_detailed.sqlRunstats命令有allow write accesss 和 allow read access选项，allow write access选项是默认行为，表示runstats表时，可以读取修改表数据；allow write access选项，会在runstats的表上加IN锁，而指定allow read access时，会在runstats表上加S锁。当运行Runstats时，如果出现表和索引统计信息不一致，将会导致Runstats报警而影响优化器路径选择。出现这种情况，需要同时收集表和索引统计信息。db2 delete from t1db2 runstats on table db2inst1.t1SQL2314W Some statistics are in an inconsistent state SQLSTATE=01650db2 runstats on table db2inst1.t1 and detailed indexes all当执行大数据量得统计信息收集时，可能出现“sql2310N”使用程序不能生成统计信息，返回错误“-930”错误，这时可考虑采用抽样统计。Runstats统计结果存在系统表中，如SYSSTAT.TABLES，如SYSSTAT.TABLES保存了表的统计信息，SYSSTAT.INDEXES保存了索引统计信息，可以查看这些统计信息，但不建议手动修改。在生产环境中，当遇到性能问题时，通常的做法是在测试机上搭建环境，模拟实际场景，但如果生产环境数据量太大、太敏感时，就无法创建相同的数据环境了，这样统计信息就不会一致，从而无法保证两边的执行计划是一致的。这时，可以采用的做法是将生产数据库的统计信息抽取出来，在测试库上进行更新，以此来“欺骗”优化器。db2look提供了mimc选项用于保存统计数据。db2look -d sample -m db2look_stat.out以下是Runstats命令的最佳实践：1.当表的数据量发生了很大变化，如通过load加载了大量数据，或reorg后，或新增了索引等，建议为相应对象收集runstats信息，为优化器提供最准确的依据。2.为减小对应用的影响，应尽可能的在空闲时间执行runstats.3.当表很大、或运维窗口很小，或表数据频繁变动时，可考虑在某些关键字段上执行runstats，而不是在所有字段。4.当表很大、统计的时间很长时，可考虑采用抽样统计。5.为提高可用性，推荐使用allow write access选项，但使用该选项时不能有太多的增删改操作，否则可能会造成数据和索引统计的不一致。6.系统表也需要经常做runstats。7.为减少统计信息不一致的情况，考虑在表和索引上同时进行统计信息收集。8.当执行完runstats后，要发出commit命令以释放锁，对于静态语句，还需要对package重新绑定，以便生成新的访问计划。10.3 Reorg10.3.1 问什么需要Reorg在DB2中，如果经常对数据进行增 删 改操作，可能会造成表和索引数据的物理组织不连续，出现空页和溢出等情况。同磁盘碎片整理工具类似，DB2提供了Reorg工具进行表和索引重组，使数据在物理存储上连续，提高页使用效率，减少I/O次数，提高查询性能。Reorg是日常运维的重要工具。当出现以下问题时，Reorg表可能会大大提供性能：1.如果表中有许多行被删除，可能会导致某些数据页只包含一部分数据，甚至有些页变成空页，当空间并不会释放。通过Reorg后表占用的空间会大大降低。（释放空页）2.当发生行溢出时。所谓行溢出（overflow）,主要发生在表中包含varchar变长字段的情况，对变长字段值进行更新后，记录的长度比以前更长，使得原有的页空间不足以放下更新的数据，DB2就会将这行数据放到另外一页，而在原有位置通过指针指向新行的RID,这样对该行的访问需要两次I/O。如果行溢出的发生得很频繁，会导致大量不必要的I/O开销。这时可Reorg表数据，重新组织数据的存储顺序，减少不必要的I/O。3.按照某个索引重新组织表数据的物理顺序。前面我们讲过聚集索引（cluster index）的概念，就是表数据在物理上的存储顺序与索引的顺序相匹配，但随着数据的频繁操作，有些表数据的物理顺序可能无法匹配索引顺序，这时可通过Reorg操作重新按索引物理排序。4.当启用了表数据压缩功能时，可通过Reorg建立字典表，并对表数据进行压缩。当出现以下问题时，Reorg所以可能会大大提高性能1.当表数据删除后，可能会导致很多索引页变成空页，可通过Reorg索引减少索引页空间。2.减少索引的层次。索引是B+树结构，包含树根、树枝和叶子节点，根据索引数据的大小，索引可能会包含几层，层次越低，索引查询需要的I/O就越少。通过Reorg可能降低索引的层次。3.去除伪删除的行和页。当删除数据后，索引中指向这些行的指针被标记为pseudo deleted，而不是物理上删除。这时候可通过Reorg删除这些指针，减少索引叶子节点的数量。当然，Reorg并不总会降低数据页大小。有时候按照索引重组后数据页会增加，这是一种具有建设性的增长。在生产环境下，DBA比较关心的是Reorg的执行时间、执行频率和对应用的影响。对于大表来说，执行一次Reorg需要的时间可能会更长，对资源的占用率很高，对应用会有一定的影响，建议在业务空闲的时候执行，执行的频率可考虑每周，或每月做一次。如何判断一张表或索引是否需要Reorg，有两种方法可供选择：1.通过Reorgchk工具2.另外一个是通过sysibmadm.snaptab管理视图。Reorgchk工具利用8个公式（3个表公式、5个索引公式）判断表和索引是否需要重组。如果表统计结果F1 F2 F3标记为*，则该表需要重组；如果索引统计结果F4-F8有*标记，则需对索引重组。db2 reorgchk on schema db2admin当表很多时对Reorgchk结果的解析会比较麻烦，这是可考虑sysibmadm.snaptab管理视图，如果发现overflow_accesses与rows_read比例高于3%，则需要对表进行重组。注意：采用此方法需要将实例监控器打开开关（update dbm cfg using dft_mon_table on）:db2 SELECT substr(TABNAME,1,18) as TABNAME,ROWS_READ,OVERFLOW_ACCESSES from SYSIBMADM.SNAPTAB where (ROWS_READ 999) and (OVERFLOW_ACCESS * 100)/(ROWS_READ + 1) 3) 10.3.2 Reorg用法与离线Reorg相比，在线Reorg对资源的占用较少，对应用的影响也很小。在线Reorg也叫inplace reorg,可以保证在重组过程中，其他应用对数据的不间断访问，在线表重组并不会创建数据副本，而是在原空间中进行，表数据的重组是分批次的，每批次只处理一部分数据，因此它的速度比离线Reorg要慢得多。在线Reorg可随时启动和终止，为了保证可恢复性，在线Reorg会记录大量的日志，需要的日志空间依赖于要移动的行数、表上索引的个数和索引键大小，因此可能是表大小的几倍。在线Reorg命令举例：db2 reorg table db2inst1.employee inplace allow write access在线Reorg是在后台异步执行的，因此即使我们看到命令成功返回，实际上仍然在后台执行。如果要重组的表很多，通常的做法是写成脚本，需要注意的是，在线重组是异步过程，这可能会造成脚本里的多个reorg命令同时执行，导致I/O和CPU资源占用很多，并且会消耗所有的活动日志，影响应用系统正常工作。为解决该问题，在编写脚本时采用以下方法控制多个表的在线Reorg执行顺序：每个在线Reorg执行时，都有一个对应的db2reorg应用程序，通过db2 list applications show detail|grep -i db2reorg判断，如果有Reorg正在执行，则等待该Reorg正在执行，则等待该Reorg执行完毕，否则执行脚本里的下一个表重组。除此之外，还可以通过其他方法在线表重组，参考前面的离线表重组。3.Reorg索引当离线表重组结束后，会重建表上的所有索引。在线表重组仅仅维护索引，而不会重建索引（聚集索引除外），当在线重组结束后，如果需要进行索引单独重组，可通过reorg indexes all for 。db2 reorg indexes all for table t1对于在线索引重组的监控，可以通过list history reorg all 或查看 db2diag.log文件。db2 list history reorg all for sample10.3.3 Reorg最佳实践对于DBA来说，在reorg时有几个问题需要注意：1.离线重组还是在线重组一般来说，如果应用有一定的运维实践窗口，建议选择离线Reorg加快执行速度；对于要求7*24小时持续运行、具有很小运维窗口的应用，建议在业务相对空闲的时候选择在线Reorg,虽然速度较慢，但可以减小对应用的影响。在线重组在设计时考虑的更多的是如何减小对应用的影响，而不是速度。离线重组支持的访问模式：Allow No Access Allow Read Access(默认)优点：提供最快的表重组；原始数据在最终替换前是只读的一旦表重组结束后会进行索引重建缺点：占用数据空间大，大概是原表的2倍；访问受限，只读；如果重组工程中失败，则需要重新执行选择建议：如果有运维时间窗口，并且要求Reorg执行速度，则选择离线Reorg在线重组支持的访问模式：Allow Read Access Allow Write Access(默认)优点：执行重组时允许应用访问；可以随时终止和恢复；异步运行；因为增量处理，所以占用的空间小缺点：要求更多日志空间；比离线reorg速度慢很多，可能慢10-20倍；不能重组大对象；维护索引，但是不能重建索引，可能需要随后对索引重组选择建议：一般来说，对于7*24小时持续运行并具有很小运维窗口的应用，建议选用在线重组，确保高可用，当时建议在交易较小的时候使用2.如果在重组的过程中出现意外（比如断电）,对正在重组的表或索引有何影响？对于离线重组，如果断电时Reorg正在进行scan或build，当崩溃恢复时该表将恢复到初始状态；如果Reorg正在进行replace(copy),崩溃恢复将重新执行该阶段；如果正在执行index create,崩溃恢复阶段将把索引失效，并延迟索引的重建。对于在线重组，如果断电时Reorg正在执行，那么正在执行Reorg的那个事务将回滚（因为在线Reorg采用增量的方式进行数据处理，已完成的迁移并不会收到影响）。但reorg的状态是终止（paused）,可以重新恢复。对于在线索引重组，断电后，崩溃恢复将回滚索引创建，并延迟索引重建。以下是关于Reorg的几点最佳实践：1.如果表上有大量的增删改操作，产生碎片的几率会很大，建议重组。2.当Reorgchk命令提示需要重组时，建议重组。3.创建表压缩时，通过Reorg建立压缩字典表，并进行表压缩。4.对于离线重组，需要确保有足够的表空间存取影子拷贝，否则重组会失败。5.对于在线重组，由于每次数据迁移都需要记录日志，因此必须确保有足够的日志空间。同时，需要确保util_heap_sz数据库参数的值足够大。6.在同一时刻，同一张表上只能有一个重组。但只要资源允许，可以同时执行几个表重组。7.对于普通表来说，当执行在线索引重组时，必须为一张表的所有索引执行，而无法选择为某个索引执行。8.对于系统表也要经常做Reorg.9.重组最好不要和其他工具一起执行，因为可能会产生锁竞争，导致回滚。10.4 Rebind讲到Rebind，则不能不提bind。开发过SQLC的工程师对bind应该不会陌生，包含在C程序的DB2 SQL语句经过预编译（prep）后，会绑定（bind）到DB2的package中，package里包含了每条SQL语句的访问计划。以后执行这个C程序时，就会按照保存在package的访问计划执行SQL语句。Rebind工具会根据当前的统计信息为package里的SQL语句重新生成新的访问计划，对性能可能会有比较好的提升。Rebind一般用于嵌入式SQL，如嵌入SQL C、嵌入SQL Java、嵌入SQL Cobol等，这些嵌入式语言的sql如果是静态语句时，当表的统计信息发生重大变化，或新增索引等可能造成执行计划发生变化的时候，建议用Rebind命令重新绑定。对于存储过程，本质上也是存储到package中，Rebind同样适合。使用Rebind命令时，需要提供package名。对于SQC(嵌入C)等程序，在bind的时候会创建一个package，在DB2内部，每个存储过程对应一个package。可以通过db2 list packages for all(或schema xx)列出相应的package名。db2 rebind package P6045027如果针对所有的package重新绑定，可以考虑db2rbind命令db2rbind sample -l db2rbind.log all动态SQL是在执行时才编译，并存储到package cache中。如果更新了统计信息，可以通过flush package cache dynamic 更新 package cache10.5 获取数据库占用空间的大小从v9开始，DB2提供了SYSSPROC.GET_DBSIZE_INFO存储过程来计算当前数据库大小和最大容量大小：db2 call GET_DASIZE_INFO(?,?,?,)其中前三个参数为输出参数，第四个参数为输入参数，表示在该时间后进行数据库大小和容量大小的刷新，单位为分钟，默认为30分钟，如果为0，则会马上进行刷新。如果需要统计每天数据大小的增长情况，可考虑将此更新窗口设为24小时，即24*60=1440分钟。例如：db2 call GET_DBSIZE_INFO(?,?,?,0)参数名：DATABASESIZE参数值：180142080（Bytes）参数值：DATABASECAPACITY参数值：39617922048（Bytes）注意：1.如果存在多个SMS表空间的情况，db容量的计算结果不一定准确。2.在多分区环境下，此存储过程只能看dbsize大小，无法看db容量。10.6 获取某个表空间占用空间大小可以使用db2 list tablespaces show detail在9.1版本中，可通过SYSIBMADM.TBSP_UTILIZATION查看表空间使用大小。10.7 获取某个表/索引占用空间大小1.计算某个表占用空间有db2pd -tcbstats Admin_get_tab_info表函数和SYSIBMADM.ADMINTABINFO系统管理视图三种方法。1）db2pd的tcbstats可以查看表的TCB信息，其中D