调优数据模型和应用程序
简介最近，我遇到了很多与性能相关的问题，因此，我想我应当针对这一问题做一些解释。由于懒于整理自己的基准，而且...
简介
最近，我遇到了很多与性能相关的问题，因此，我想我应当针对这一问题做一些解释。由于懒于整理自己的基准，而且我认为剖析广泛使用的基准更有意义，因此我们来研究一下 Lester Knutsen 与 Advanced DataTools 所采用的 “Fastest DBA 基准”。大多数读者对此可能并不太熟悉，这是一个由 Advanced DataTools 发起的定期辩论，他们提出了一个缓慢运行的应用程序，让挑战参与者来对其进行调优。我在 参考资料 部分提供了这一讨论相关细节的链接。
我也意识到类没有书面文字有用，因此，您现在所阅读的是对于如何基于 Fastest DBA 基准的剖析，来调优数据库的探索。我们将执行基准、注意性能问题，单独解决每个问题，并使用所有已解决的问题来尝试该基准。注意这是第二个基准；后续的文章将会对第三个（以及当前的）基准进行剖析。
本文不介绍有关基准的安装问题 — README 文件当中提供了全面的讲解。我在我的 MacBook 中提出了两个问题，第一个是关于引擎的 SHMBASE。我必须编写源文件 $ONCONFIG，并进行相应的设置（并针对每个 setup.sh 执行进行重新复制）。第二个问题是，我的 Informix 用户默认属于 “admin” 组，因此，我必须手动将 dbspaces 的所有权改为 Informix:Informix。
基准的执行与交付无法及时完成。基准本身很简单：它加载客户表，然后为这些客户生成账单。最后的操作是对账单表的更新，这需要花费一定时间。该工作并不复杂，但它提供了一个认识调优如何提高性能的机会。
从哪里开始？
通常，任何性能调优项目的首要问题是问题描述。如何知道何时已经解决了问题？如何衡量性能以及确定它是否已被提高，以及提高了多少？基于本文的目的，我想说，基准的运行时间过长，我想在一个小时内执行完毕。此外，我会捕获每个阶段的数据库指标来确定是否有所改善。
有关数据库调优的第二个问题是确定调优什么。我们将会从纠正中看到最佳结果：
- 数据库模型
- 应用程序
- 磁盘
- 最后是引擎。
运行基准
安装了基准以后，执行 onstat -z 来清除引擎指标，这样就能确保仅看到基准对引擎的作用。
您可能会注意到，基准的创建者以很友好的方式来进行计划解释，因此它被构建到当前目录中。在整个 run.sh 脚本上有一个计时器，但获取每一步的中间时间很有用，因此，我们将会编辑 benchmark_run.sql 并利用如下语句，在每个代码块后面插入时间戳：
select current hour to second from systables where tabid=1;
下一步，利用 run.sh 来启动基准。在单独窗口中启动 onstat -pr。这将会每隔 5 秒种输出一次引擎指标。接下来可以调出基准，对其进行检测，或者干脆回去休息。
首次对账单表的插入（在笔记本电脑中）耗时 1m16s（第二次耗时 1m17s）而且引擎配置文件内容如下：
清单 1. 引擎性能配置文件
IBM Informix Dynamic Server Version 11.50.FC6DE -- On-Line -- Up 1 days 02:35:27 -- 118812 KbytesProfiledskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached175586 178320 4720830 96.28 113398 237194 1002488 88.69 isamtot open start read write rewrite delete commit rollbk4130803 157 495827 1321784 330055 24 65 55 0gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs 0 0 0 0 0 0 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 112.78 13.67 2 1 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 449 0 2480376 0 0 1 7 28 ixda-RA idx-RA da-RA RA-pgsused lchwaits 28 0 165256 165232 1520
此输出类似于汽车油尺。在一个简短的页面中，它捕获了引擎的状态。
第一行（在 Profile 下面）提供了磁盘与缓存性能的相关信息。在此处，我们对读写缓存命中率感兴趣。读命中率应超过 99%，写缓存命中率最好达到 95% 左右。由于我们没有做很多工作，而且缓存数量还没有调整，所有这就可以了。虽然在第一行中，针对引擎的大小来对内存做了考虑（118,812 KB），但是，我们没有使用很多内存，我们为表保持缓存。
第二行（isamtot）指出实际已经完成了多少工作（例如 isamtot 是全部 isam 操作）。
第三行（gp_*）描述了如何利用 Generic Pages 或者非标准页面来管理引擎。由于我们在这一基准中没有采用这一点，所以所读取的都是 0。我会在后续的 dipstick 读取中移除该行。
第四行以超出锁的次数、用户线程或者缓存数来开始（通常所有的 0 会是这样）。后面的两个是 User CPU 与 System CPU 的数量、Checkpoints 的数量、以及 Flushes 的数量。
第五行是我们所关注的：
表 1. 引擎配置文件的第 5 行
参数描述
bufwaits
进程必须等待缓存的次数，是因为没有足够的缓存，还是因为缓存已被别人使用。
lokwaits
等待要解开的锁的次数。
lockreqs
所请求的锁的数量（已锁定的行数或页数）。
deadlks
检测到死锁的次数。
dltouts
死锁超时的次数。
ckpwaits
等待检查点的数量。
compress
压缩的数量（在删除后清理数据页）。
seqscans
顺序扫描的数量。
此处对缓存等待的边际数、以及顺序扫描的边际数感兴趣，而不是大量的锁请求。
第六行指出 read-ahead 如何工作，以及闭锁的数量。引擎消耗了所有索引与数据 read-aheads。同样，我们看到了大量的闭锁请求。
现在让基准运行时间更长一些，来再次进行检查。
在两天中，我的笔记本电脑休眠了多次，因此并没有真正运行 48 小时（虽然引擎认为它一直在运行）。现在再次访问 dipstick，来查看相关内容。
清单 2. 两天以后的性能情况
IBM Informix Dynamic Server Version 11.50.FC6DE -- On-Line -- Up 3 days 05:42:03 -- 127004 KbytesProfiledskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached19966695480 19989379994 60891586765 67.21 660217 892639 3121469 78.85 isamtot open start read write rewrite delete commit rollbk15495512 9115 1409626 6189708 759769 955295 1112 6097 0ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 221553.88 127876.85 1136 604 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 364286149 0 59940823034 0 0 51 1707 102128 ixda-RA idx-RA da-RA RA-pgsused lchwaits 224274 0 19966050547 19966274029 4146547
我们所要关注的事情已经膨胀失控。（注意：通常检查站等待数可能会暂停，但是，在本例中，基准正运行单个 SQL 进程，因此该进程正在等待自己，而检查点是向磁盘写入的更有效方式，因此不必对此太过乐观。）
读缓存率为 67%。写缓存率为 78%。而我们执行的逻辑 ISAM 指令的数量是 6 百万次读取，锁请求数是（就是所读取的物理指示器）是 590 亿。顺序扫描数、以及缓冲区等待数和闭锁等待数已经失控。我们正执行大量的 read-ahead 操作，其正在被消耗，它的数量很大，但是为什么要做这么多？
好像我们正在进行顺序扫描（对整个表）来满足 SQL 命令的需求，不管它是什么。我们来仔细看一下相关内容。
在 sysmaster 数据库当中有两个我们所关注的表，sysptprof（Partition Profile）与 syssesprof（Session Profile）。现在看一下它们为我们提供的内容：
select * from sysptprof order by abs(seqscans) desc;
注意：如果顺序扫描数超过 MAXINT（20 多亿），它将变成一个负数 — 因此，我们需要绝对值。以下是该 select 的结果：
清单 3. 表性能配置文件
dbsname benchmarktabname billspartnum 3145799lockreqs 136990885lockwts 0deadlks 0lktouts 0isreads 2419074iswrites 605280isrewrites 853404isdeletes 0bufreads 1085720339bufwrites 2071693seqscans 99566pagreads -1398716020pagwrites 534809dbsname benchmarktabname customerpartnum 3145795lockreqs 727188lockwts 0deadlks 0lktouts 0isreads 99566iswrites 0isrewrites 99562isdeletes 0bufreads 1898629bufwrites 99562seqscans 4pagreads 727202pagwrites 99562
要注意 pagreads（页读取）如何变成了负数。无法确定该情况在运行当中出现了多少次。同样，lockreqs（所请求）已经翻滚很多倍。
在 102,000 个顺序扫描当中（见上面的 onstat 输出），有 99,000 个是针对该表的。该表具有 ~600,000 行。我们已经针对它发出了 1 亿 3 千 6 百万个锁请求。（事实上，20 分钟后，该数字是 9 亿 3 千 4 百万，这是个问题）。如果采用 99,000*600,000，那么能得到 590 亿 4 千万，该数字，并非巧合，占去了所请求的大部分。换句话说，我们顺序地扫描了此表，锁定了每个行，操作进行了很多次。由于存在 100,000 个客户，所以必须确保已接近当前步骤的尾声。事实上，它正好已经结束：
Real 3113m27.925sUser 0m0.006sSys 23m45.717s
不幸的是，我们没有看到 Session Profile 表。这将为我们展示类似的信息，但是在会话级别，因此我们会看到会话做了过多的顺序扫描与锁定请求。
常见问题
我要单独提出第一个问题，因为这一问题出现的比较频繁。我们的第一个主要问题是，当我们想要一行的内容时，我们会扫描整个账单表，并锁定我们读取的每一行。
这一盲目的顺序扫描/锁定掩饰了真实的性能指标。由于我们一遍又一遍地顺序扫描一个巨大的表，这样我们就无法关注读缓存命中率。因此，在查看更加真实的性能指标之前，我们先关注这一问题。
需要花费两天时间来运行的语句是：
update customer set balance_due = balance_due + ( select sum ( total_bill ) from bills where bills.customer_number = customer.customer_number ) where customer_number in ( select customer_number from bills );
如果我们看一下解释计划（位于 sqexplain.out 中），我们就会开始了解该问题（见粗体的注意部分）：
清单 4. 解释计划
update customer set balance_due = balance_due + ( select sum ( total_bill ) from bills where bills.customer_number = customer.customer_number )where customer_number in ( select customer_number from bills )Estimated Cost: 3 -- The optimizer thought this would be cheapEstimated # of Rows Returned: 10 With only 10 rows affected 1) informix.customer: INDEX PATH Excellent, an index was used to read this table (1) Index Name: informix. 101_2 Index Keys: customer_number (Serial, fragments: ALL) Lower Index Filter: informix.customer.customer_number = ANY subquery Subquery: Estimated Cost: 2 Estimated # of Rows Returned: 1 1) informix.bills: SEQUENTIAL SCAN Here is our problem Filters: informix.bills.customer_number = informix.customer.customer_number Subquery: Estimated Cost: 2 The optimizer thinks this will be cheap. Why? Estimated # of Rows Returned: 1 1) informix.bills: SEQUENTIAL SCAN[deletia]
账单表出了什么问题，使得优化程序认为需要顺序扫描整个表？我们来看一下该表。
Select * from benchmark:systables where tabname = 'bills';tabname billsrowsize 1266nrows 0.00created 08/23/2010ustlowts
我已经略去了很多信息，只展示两条最重要的信息。根据系统目录的内容，账单表是空的。“ustlowts” 是空值，这说明了没有再次对该表进行这一统计。事实上，如果我们看一下基准代码，我们就会发现，该表已被创建、填充、并使用，但是统计信息没有被更新。这是 Database 101 填充内容。优化程序采用系统目录，说明那里有多少行，以及构造了较好的索引来计算所采用的查询路径。由于表是 “空” 的，那么很显然读取空表比采用索引更简单。
第二条关键信息是我们正尝试利用 customer_number 来读取账单表。在账单表中不存在 customer_number 上的索引。即使我们对该表更新了统计信息，优化程序也没有选择，只能对其进行顺序扫描。
在我们对基准的第一个改进中，我们向账单表增加索引，并为表更新统计信息。由于索引增加了写入表的成本，所以要在填充完账单表后立即创建索引。在索引创建后，更新统计信息。（注意，在 V11.x 中统计信息将作为索引创建流程的一部分，在索引创建时生成。我们对其进行测试。）
我们增加如下内容：
create index bills_idx1 on bills(customer_number);
在填充完账单表后，立即利用以下命令来清除状态计数器：onstat -z 并重启动基准。
现在已增加了索引。我们来查看一下表信息：
tabname billsrowsize 1266nrows 605280.0000000ustlowts 2010-08-25 20:52:22.00000
情况好了很多。统计信息已被更新，不但填充了 nrows，而且我们还看到统计信息最终被更新了。该运行执行情况如下：
清单 5. 更新完统计信息后
Real 36m5.033sUser 0m0.005sSys 0m1.479sdskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached1397183 1600342 17971316 92.23 656896 885752 3087149 78.72 isamtot open start read write rewrite delete commit rollbk14888305 284 1354389 6438035 747981 956259 30 170 0ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 2980.00 119.77 22 20 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 244886 0 10281980 0 0 10 12 71 ixda-RA idx-RA da-RA RA-pgsused lchwaits 479759 64 901978 1381601 12531
现在我们已经拥有合理数量指标，并且已经移除了噪音，但是想要调优引擎，仍然言之过早。
调优数据模型
对于调优性能，我们能够做的、最有效的就是解决任何数据模型问题。以下是我们当前的数据模型。
图 1. 用于基准数据库的数据模型
我们看到了一些问题。对于初学者，这一账单表中包含了各种不正常数据垃圾。所有的 Customer、State、以及 Product 信息已被丢弃在这里。
图 2. 突出显示多余数据列
所有粗体的项目都是可以很容易地从源表中检索出的多余垃圾。Sales_Tx 令人吃惊，因为在它可以使用的地方，读出了状态表。Product_Price 不属于这里，但是如果我们看到它是如何使用的，会发现它仅是名字起得很差。这实际上是有多少客户被针对该产品记账到这一账单中。它更应该叫做 Billed_Price。
如果我们研究的更深入一点，我们会发现静态数据与动态数据混在了一起。比如，客户名与地址很可能会变。然而，我们当然希望它的 balance_due 是这样的。此外，账单票据通常不会描述一个客户。它们应当描述客户与其账单之间的关系。此外，账单本身已被损坏。在本基准中，我们为每个客户生成了 6 个账单，而不是只有包含 6 行项目的单个账单。了解这一观点后，我们来再次访问这一数据模型。
这是一个修正的模型。它还有问题：Zipcode 已足够识别国家与城市，因此，这些属性应当被迁移到 City 表（而不是状态表），但是该数据是静态的，因此我们可以不必理会。
图 3. 修正数据模型
如果我们要重组架构，那需要一定的工作量，我们应当想要将其包含在基准时间中。注意，针对以上数据模型执行相同的应用程序花费了 16 秒（在数据被重组以后）。
在这一操作之前，我们应当规划好表的大小。然而由于具有大量的区片，Informix 不再具有性能问题，但每个区片的分配也需要一定的时间。最好是正确地规划表的大小，这样就不必在每次插入 20 行时，还要浪费时间来对其进行扩展。
规划表的大小应当：
表 2. 表大小表大小
Bill_Items
20,416
Billing_Notes
202,048
Bills
14,976
Customer
14,976
Customer_Balance
1,728
Product 与 state 不会改变，因此不必调整其大小。然而，我们将会为其更新统计数据。（注意：这是因为后面还会涉及不必的和无效的变更）。
关注 ibm软件技术联盟
微信扫一扫关注公众号
