调优 Informix 数据库获取最佳性能,第 1 部分: 调优数据模型和应用程序

使用 “Fastest DBA” 基准进行 Informix 数据库性能案例研究

作者 Jack Parker 通过分析 “Fastest DBA 基准” 解释了如何调优 IBM® Informix® 数据库应用程序来获取最优性能的一些基本原则。在本系列文章的第 1 部分中,他介绍了数据模型以及应用程序如何影响性能,并为您提供了最新的统计数据。

Jack Parker, 工程师, Cisco Systems

Jack ParkerJack Parker是 Cisco 公司一名工程师。自 80 年代中期开始,他一直从事构建和管理基于 Informix 的解决方案。他偶尔也为 comp.databases.informix 撰写文章、发表演讲和投稿。



2011 年 7 月 11 日

免费下载:IBM® Informix® 11.7 试用版(包括 Ultimate Edition、Developer Edition 和 Innovator-C Edition)
下载更多的 IBM 软件试用版,并加入 IBM 软件下载与技术交流群组,参与在线交流。

简介

最近,我遇到了很多与性能相关的问题,因此,我想我应当针对这一问题做一些解释。由于懒于整理自己的基准,而且我认为剖析广泛使用的基准更有意义,因此我们来研究一下 Lester Knutsen 与 Advanced DataTools 所采用的 “Fastest DBA 基准”。大多数读者对此可能并不太熟悉,这是一个由 Advanced DataTools 发起的定期辩论,他们提出了一个缓慢运行的应用程序,让挑战参与者来对其进行调优。我在 参考资料 部分提供了这一讨论相关细节的链接。

我也意识到类没有书面文字有用,因此,您现在所阅读的是对于如何基于 Fastest DBA 基准的剖析,来调优数据库的探索。我们将执行基准、注意性能问题,单独解决每个问题,并使用所有已解决的问题来尝试该基准。注意这是第二个基准;后续的文章将会对第三个(以及当前的)基准进行剖析。

本文不介绍有关基准的安装问题 — README 文件当中提供了全面的讲解。我在我的 MacBook 中提出了两个问题,第一个是关于引擎的 SHMBASE。我必须编写源文件 $ONCONFIG,并进行相应的设置(并针对每个 setup.sh 执行进行重新复制)。第二个问题是,我的 Informix 用户默认属于 “admin” 组,因此,我必须手动将 dbspaces 的所有权改为 Informix:Informix。

基准的执行与交付无法及时完成。基准本身很简单:它加载客户表,然后为这些客户生成账单。最后的操作是对账单表的更新,这需要花费一定时间。该工作并不复杂,但它提供了一个认识调优如何提高性能的机会。

从哪里开始?

通常,任何性能调优项目的首要问题是问题描述。如何知道何时已经解决了问题?如何衡量性能以及确定它是否已被提高,以及提高了多少?基于本文的目的,我想说,基准的运行时间过长,我想在一个小时内执行完毕。此外,我会捕获每个阶段的数据库指标来确定是否有所改善。

有关数据库调优的第二个问题是确定调优什么。我们将会从纠正中看到最佳结果:

  1. 数据库模型
  2. 应用程序
  3. 磁盘
  4. 最后是引擎。

由于使用单磁盘的 MacBook Pro,所以我在磁盘调优方面可做的事情不多。那么需要关注 CPU、磁盘、以及内存使用情况。


运行基准

安装了基准以后,执行 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 Kbytes

Profile
dskreads pagreads  bufreads  %cached dskwrits  pagwrits  bufwrits  %cached
175586   178320    4720830   96.28   113398    237194    1002488   88.69  

isamtot   open      start     read      write     rewrite   delete   commit  rollbk
4130803   157       495827    1321784   330055    24        65       55      0

gp_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 Kbytes

Profile
dskreads   pagreads   bufreads   %cached dskwrits   pagwrits   bufwrits   %cached
19966695480 19989379994 60891586765 67.21   660217     892639     3121469    78.85  

isamtot   open     start     read      write    rewrite   delete   commit   rollbk
15495512  9115     1409626   6189708   759769   955295    1112     6097     0

ovlock     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     benchmark
tabname     bills
partnum     3145799
lockreqs    136990885
lockwts     0
deadlks     0
lktouts     0
isreads     2419074
iswrites    605280
isrewrites  853404
isdeletes   0
bufreads    1085720339
bufwrites   2071693
seqscans    99566
pagreads    -1398716020
pagwrites   534809

dbsname     benchmark
tabname     customer
partnum     3145795
lockreqs    727188
lockwts     0
deadlks     0
lktouts     0
isreads     99566
iswrites    0
isrewrites  99562
isdeletes   0
bufreads    1898629
bufwrites   99562
seqscans    4
pagreads    727202
pagwrites   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.925s
User  0m0.006s
Sys   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 cheap
Estimated # 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          bills
rowsize          1266
nrows            0.00
created          08/23/2010
ustlowts

我已经略去了很多信息,只展示两条最重要的信息。根据系统目录的内容,账单表是空的。“ustlowts” 是空值,这说明了没有再次对该表进行这一统计。事实上,如果我们看一下基准代码,我们就会发现,该表已被创建、填充、并使用,但是统计信息没有被更新。这是 Database 101 填充内容。优化程序采用系统目录,说明那里有多少行,以及构造了较好的索引来计算所采用的查询路径。由于表是 “空” 的,那么很显然读取空表比采用索引更简单。

第二条关键信息是我们正尝试利用 customer_number 来读取账单表。在账单表中不存在 customer_number 上的索引。即使我们对该表更新了统计信息,优化程序也没有选择,只能对其进行顺序扫描。

在我们对基准的第一个改进中,我们向账单表增加索引,并为表更新统计信息。由于索引增加了写入表的成本,所以要在填充完账单表后立即创建索引。在索引创建后,更新统计信息。(注意,在 V11.x 中统计信息将作为索引创建流程的一部分,在索引创建时生成。我们对其进行测试。)

我们增加如下内容:

create index bills_idx1 on bills(customer_number);

在填充完账单表后,立即利用以下命令来清除状态计数器:onstat -z 并重启动基准。

现在已增加了索引。我们来查看一下表信息:

tabname          bills
rowsize          1266
nrows            605280.0000000
ustlowts         2010-08-25 20:52:22.00000

情况好了很多。统计信息已被更新,不但填充了 nrows,而且我们还看到统计信息最终被更新了。该运行执行情况如下:

清单 5. 更新完统计信息后
Real    36m5.033s
User    0m0.005s
Sys     0m1.479s

dskreads   pagreads   bufreads   %cached dskwrits   pagwrits   bufwrits   %cached
1397183    1600342    17971316   92.23   656896     885752     3087149    78.72  

isamtot   open    start     read      write     rewrite   delete   commit   rollbk
14888305  284     1354389   6438035   747981    956259    30       170      0

ovlock     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. 突出显示多余数据列
图片展示了账单表中的额外列:Last_Name、First_Name、Address、City、State、State_Name、Zip、Start_Date、Product_Code、以及 Product_Name

所有粗体的项目都是可以很容易地从源表中检索出的多余垃圾。Sales_Tx 令人吃惊,因为在它可以使用的地方,读出了状态表。Product_Price 不属于这里,但是如果我们看到它是如何使用的,会发现它仅是名字起得很差。这实际上是有多少客户被针对该产品记账到这一账单中。它更应该叫做 Billed_Price。

如果我们研究的更深入一点,我们会发现静态数据与动态数据混在了一起。比如,客户名与地址很可能会变。然而,我们当然希望它的 balance_due 是这样的。此外,账单票据通常不会描述一个客户。它们应当描述客户与其账单之间的关系。此外,账单本身已被损坏。在本基准中,我们为每个客户生成了 6 个账单,而不是只有包含 6 行项目的单个账单。了解这一观点后,我们来再次访问这一数据模型。

这是一个修正的模型。它还有问题:Zipcode 已足够识别国家与城市,因此,这些属性应当被迁移到 City 表(而不是状态表),但是该数据是静态的,因此我们可以不必理会。

图 3. 修正数据模型
图片展示了 state、customer、customer_balance、bills、billing_notes、product、以及 bill_items 表

如果我们要重组架构,那需要一定的工作量,我们应当想要将其包含在基准时间中。注意,针对以上数据模型执行相同的应用程序花费了 16 秒(在数据被重组以后)。

在这一操作之前,我们应当规划好表的大小。然而由于具有大量的区片,Informix 不再具有性能问题,但每个区片的分配也需要一定的时间。最好是正确地规划表的大小,这样就不必在每次插入 20 行时,还要浪费时间来对其进行扩展。

规划表的大小应当:

表 2. 表大小
大小
Bill_Items20,416
Billing_Notes202,048
Bills14,976
Customer14,976
Customer_Balance1,728

Product 与 state 不会改变,因此不必调整其大小。然而,我们将会为其更新统计数据。(注意:这是因为后面还会涉及不必的和无效的变更)。


调优应用程序

下一个提高性能的有效方法就是调优应用程序。这符合 “了解数据,了解查询” 规则。我们在进行该操作时,需要关注一下数据模型,因为这在数据模型中有助于应用程序的使用。

应用程序首先做的是为客户生成三组账单,其中包含以 A 开头的 product_code。可能会基于客户的开户日期(>= 1/1/2000,>=1/1/2005,>=1/1/1985)来生成不同数量的账单。根据产品 1、2、4、7、9、10 而不同。看一下这些数据,所有的产品代码都以 A 开头,最晚的开户日期是 1/1/2007。换句话说,我们应当将这三个插入语句合并到一个插入语句中,并且仅需读取一次源表而不是三次。

接下来,应用程序更新 Bills 表,将一个产品打折为 10,其 start_date 为 <= 1/1/2009,其 balance_due 为 > 50000。WHERE 子句是对该条件的重复并增加红色 herring “product number in (1 through 10)”。由于这包括了所有产品,因此没有指向该指示器。大约有 40,000 个客户的 start_date 为 < 1/1/2009,并且其 start_date 为 > 50000。这意味着对表的顺序扫描比索引读取更快,我们无法遵循指示器标准。

接下来,再次更新账单,将总账单设置为价格减去折扣和销售税,这从 state 表读取。红色 herring 的产品号从 1 到 10(换句话说,是全部)。如果在 Bills 表中只剩下了 sales_tax,我们不必获取它,特别是因为我们现在必须通过 customer 表来导航到此处。因此,我们把它送回去。

最后,我们来更新 customer,依据来自 bills 表的 total_bill 值来递增其 balance_due。我们只需更新具有账单的客户。由于他们都有账单,这没有意义。目前我们必须将 Balance_due 放入其自己的表。如果它处于包含 Bills 的表中,我们就避免了附加的更新操作。可以说,每个客户只有一个账单,它应当在那里。

然后我们读取总值,来验证应用程序已正确运行:Record count、sum(product_price)、sum(product_discount)、sum(total_bill)。现在该信息已在 Bill_Items 表中。如果我们在 bill 表中为其生成摘要列,我们就能提高读取速度。当然,这将产生一个不正确的记录值,我们知道那是 #Customers*6,但这是错误的。

我们从 customers 读取了 Count(*) 与 sum(balance_due。由于我们当前在 bills 表中具有 Balance_Due,我们现在应当直接从此处读取。

我们的数据模型就是这样:

图 4. 修正后的数据模型 2
图片展示了 state、customer、bills、billing_notes、product、以及 bill_items 表

我们将要执行的操作是:

  • 扫描 customer 表(6 秒)
  • 扫描 State 表(0 秒)
  • 连接 Customer 表与 State 表(6 秒)
  • 插入到 Bills(7 秒)
  • 创建索引(1 秒)
  • 加载 bill_items 表(8 秒)
  • 在 bill_items 上创建索引(3 秒)
  • 更新 bill_items(58 秒);利用对 customer 的嵌套循环读取,来顺序扫描 bill_items
  • 更新 bills(5 秒);利用对 bill_items 的循环嵌套读取来顺序扫描
  • 验证(4 秒)
清单 6. 修正数据模型的结果
Profile
dskreads   pagreads   bufreads   %cached dskwrits   pagwrits   bufwrits   %cached
721155     734054     10036861   92.82   19724      60567      1450097    98.64  

isamtot   open    start     read      write    rewrite   delete    commit   rollbk
10295768  447     807589    4558564   706236   706197    65        45       0

ovlock     ovuserthread ovbuff     usercpu  syscpu   numckpts   flushes   
0          0            0          75.50    42.50    4          3         

bufwaits   lokwaits   lockreqs   deadlks    dltouts    ckpwaits   compress   seqscans  
459393     0          7468727    0          0          2          13         22        

ixda-RA    idx-RA     da-RA      RA-pgsused lchwaits  
605515     0          105695     711210     212     

real    1m38.397s
user    0m0.007s
sys     0m1.510s

如果我们看一下对 bill_items 的更新,我们所做的就是基于来自 customer 表的信息来更新 discount,这个我们在首次构建 bill_items 表时已经掌握了。我们可利用这个优势,可在首次创建表时设置 discount。以下 CASE 语句用于解决这个问题:

清单 7. CASE 语句
insert into bill_items
        (bill_number,
        customer_number,
        product_number,
        billed_price,
        product_discount)
 select bill_number,
        bills.customer_number,
        product_number,
        product_price,
        case when (customer.start_date<="01/01/2009" 
               and customer.balance_due>50000) then 10
        else 0
        end case
   from bills, product, customer
  where product.product_number in (1,2,4,7,9,10)
    and bills.customer_number=customer.customer_number;

那么如果再次运行呢?

清单 8. 再次运行 CASE 语句
Profile
dskreads   pagreads   bufreads   %cached dskwrits   pagwrits   bufwrits   %cached
209038     221593     8178955    97.44   25104      49481      844982     97.03  

isamtot   open    start    read      write     rewrite   delete   commit   rollbk
7271368   376     908477   4242538   706230    100913    65       32       0

ovlock     ovuserthread ovbuff     usercpu  syscpu   numckpts   flushes   
0          0            0          45.13    3.65     4          3         

bufwaits   lokwaits   lockreqs   deadlks    dltouts    ckpwaits   compress   seqscans  
1617       0          6056020    0          0          2          13         10        

ixda-RA    idx-RA     da-RA      RA-pgsused lchwaits  
1784       0          202678     204462     81      

real	0m34.160s
user	0m0.006s
sys	0m0.050s

loaded bills in 3 sec
loaded bill_items in 19 sec
updated bills in 6 sec
verifications 4 sec

我们来看一下所有这些锁。由于我们没有操控锁,因此引擎在需要一行(或者页)时会对其进行锁定。我们会以独占模式锁定表。注意,这需要采用事务来环绕锁。我们需要关注逻辑日志,并确保我们所掌握的足狗使用,这样我们就不必将其填入到一个长事务中。

在每次插入更新操字段之前,先对表进行独占锁定:

清单 9. 独占锁
78036 Kbytes
Profile
dskreads   pagreads   bufreads   %cached dskwrits   pagwrits   bufwrits   %cached
9943       217306     7344046    99.88   5757       38980      836059     99.31  

isamtot   open    start    read      write    rewrite   delete   commit   rollbk
7270986   496     908610   4241799   706213   100905    54       28       0

ovlock     ovuserthread ovbuff     usercpu  syscpu   numckpts   flushes   
0          0            0          35.80    3.90     1          0         

bufwaits   lokwaits   lockreqs   deadlks    dltouts    ckpwaits   compress   seqscans  
3          0          3252       0          0          0          11         11        

ixda-RA    idx-RA     da-RA      RA-pgsused lchwaits  
6          0          0          6          152     

real	0m31.829s
user	0m0.006s
sys     0m0.048s

我们还忘记了一些应用程序层问题:Informix 不擅长读取与写入大量数据。但是,通过日志数据库,我们正在记录所执行的每个操作。我们可以通过在加载表时,将其类型设置为 raw,来调优大批量插入,然后再将其设置为标准表,这样就可在之上构建索引。将表 Bills 与Bill_Items 变更为 raw,然后再加载 :

清单 10. 利用 raw 表来加载
Profile
dskreads   pagreads   bufreads   %cached dskwrits   pagwrits   bufwrits   %cached
849        208192     6732674    100.00  10031      13202      836054     98.80  

isamtot   open    start    read      write    rewrite   delete   commit   rollbk
7474373   370     807441   4042766   706210   100903    49       20       0

ovlock     ovuserthread ovbuff     usercpu  syscpu   numckpts   flushes   
0          0            0          31.98    2.32     6          6         

bufwaits   lokwaits   lockreqs   deadlks    dltouts    ckpwaits   compress   seqscans  
0          0          2664       0          0          3          11         7         

ixda-RA    idx-RA     da-RA      RA-pgsused lchwaits  
0          0          0          0          13      

real	0m21.686s
user	0m0.005s
sys	0m0.019s

结束语

我们利用具体例子对相关问题做了详细的说明,并会在后续的文章中继续对引擎相关问题进行深入探讨。我们在本例中所提及的例子介绍了基准的执行,而没有涉及引擎。我们 “更正” 了数据模型以及应用程序,并更新了统计信息。我们采用工具检测到,在对 Informix 的调优实践中,引擎所做的操作都适用。

附录

以下是初始基准 SQL 脚本,以及我们在本文中所做的修订。增加内容标记为粗体而删除内容标记为斜体

--  File:	benchmark_run.sql
--  Date:	4/20/2009
--  Author:	Lester Knutsen
--  Description:
--    There are 4 tables in this process to generate bills 
--    customer table with 100100 records, 
--    states table with sales tax info
--    product table with 10 products and the 
--    bills table where the results get written to.
--  Log:  Created 4/20/2009


set explain on;
update statistics high for table product(product_number);
update statistics high for table state(state);
update statistics high for table customer(customer_number);
drop table bill_items;
set isolation dirty read;


-- drop and re-create the bills table


drop table "informix".bills ;

-- Note: Type raw and not carrying useless columns around
-- Also got rid of Primary key since we are now a raw table.

create raw table "informix".bills 
	(
	bill_number serial not null ,
	customer_number integer,
	last_name char(30),
	first_name char(30),
	address char(50),
	city char(20),
	state char(2),
	state_name char(20),
	zip integer,
	start_date date,
	bill_date date,
	bill_notes char(1000),
	product_code char(4),
	product_number integer,
	product_name char(50),
	product_price decimal(16,2),
	product_discount decimal(16,2),
	sales_tx decimal(16,2),
	total_bill decimal(16,2),
	balance_due decimal(16,2),primary key (bill_number )
	);

-- Note: Bill_items was not part of original data model

create raw table bill_items
        (
        bill_number integer not null ,
        customer_number integer,
        product_number integer,
        billed_price decimal(16,2),
        product_discount decimal(16,2)
        ) ;

-- Note: Loading Bills in one single statement.

begin work;
lock table bills in exclusive mode;
insert into bills 
        (bill_number,
        customer_number,
        bill_date,
        sales_tax,
        total_bill,
        balance_due)
 select 0,
        customer_number,
        today, -- bill_date
        state.sales_tax,
        0, -- total bill
        balance_due
   from customer, state
  where customer.state=state.state;

alter table bills type(standard);

-- Note: And putting the index back on, effectively updates statistics

create index bill_idx1 on bills(customer_number);
commit;
	-- Note: Replaced the next 4 blocks of code with the single block above

-- Create bills for product numbers 1 and 2

insert into  bills 
	(
	customer_number,
	last_name,
	first_name,
	address,
	city,
	state,
	state_name,
	zip,
	start_date ,
	bill_date ,
	product_code ,
	product_number,
	product_name ,
	product_price ,
	product_discount ,
	sales_tx,
	total_bill
	)
select 
	customer.customer_number,
	customer.last_name,
	customer.first_name,
	customer.address,
	customer.city,
	customer.state,
	state.state_name,
	customer.zip,
	customer.start_date ,
	today, -- bill_date ,
	customer.product_code ,
	product.product_number,
	product.product_name ,
	product.product_price ,
	0, -- product_discount ,
	state.sales_tax,
	0 -- total_bill
from customer, state, product
where customer.state = state.state
and customer.product_code[1] = product.product_code[1]
and customer.start_date >= "01/01/2000"
and product.product_number in ( 1, 2 );


Create bills for product number 4 and 7

insert into  bills 
	(
	customer_number,
	last_name,
	first_name,
	address,
	city,
	state,
	state_name,
	zip,
	start_date,
	bill_date,
	product_code,
	product_number,
	product_name,
	product_price,
	product_discount,
	sales_tx,
	total_bill
	)
select 
	customer.customer_number,
	customer.last_name,
	customer.first_name,
	customer.address,
	customer.city,
	customer.state,
	state.state_name,
	customer.zip,
	customer.start_date,
	today, -- bill_date,
	customer.product_code,
	product.product_number,
	product.product_name,
	product.product_price,
	0, -- product_discount,
	state.sales_tax,
	0 -- total_bill
from customer, state, product
where customer.state = state.state
and customer.product_code[1] = product.product_code[1]
and customer.start_date >= "01/01/2005"
and product.product_number in ( 4, 7 );

-- Create bills for product number 9 and 10

insert into  bills 
	(
	customer_number,
	last_name,
	first_name,
	address,
	city,
	state,
	state_name,
	zip,
	start_date ,
	bill_date ,
	product_code ,
	product_number,
	product_name ,
	product_price ,
	product_discount ,
	sales_tx,
	total_bill
	)
select 
	customer.customer_number,
	customer.last_name,
	customer.first_name,
	customer.address,
	customer.city,
	customer.state,
	state.state_name,
	customer.zip,
	customer.start_date ,
	today, -- bill_date ,
	customer.product_code ,
	product.product_number,
	product.product_name ,
	product.product_price ,
	0, -- product_discount ,
	state.sales_tax,
	0 -- total_bill
from customer, state, product
where customer.state = state.state
and customer.product_code[1] = product.product_code[1]
and customer.start_date >= "01/01/1985"
and product.product_number in ( 9, 10 );
	Note: Loading Bill_Items with info that used to be in Bills

begin work;
lock table bill_items in exclusive mode;
insert into bill_items
        (bill_number,
        customer_number,
        product_number,
        billed_price,
        product_discount)
 select bill_number,
        bills.customer_number,
        product_number,
        product_price,
        case when (customer.start_date<="01/01/2009" and \
customer.balance_due>50000) then 10
        else 0
        end case
   from bills, product, customer
  where product.product_number in (1,2,4,7,9,10)
    and bills.customer_number=customer.customer_number;

alter table bill_items type(standard);

create index bill_items_idx2 on bill_items(bill_number);

commit;
	Note: We took care of this by loading the bill_items table.

-- Update bills - give the best customers a $10 discount

update bills
   set product_discount =  ( select 10 from customer 
 where customer.customer_number = bills.customer_number
   and customer.start_date <= "01/01/2009" 
   and customer.balance_due > 50000 )
 where bills.customer_number in  ( select customer_number 
  from customer where customer.start_date <= "01/01/2009" 
   and customer.balance_due > 50000 )
   and product_number in ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 );


-- Update bills - calculate the total with sales tax and minus the discount


-- Note: Similar, but taking advantage of our new data model. 
begin work;
lock table bills in exclusive mode;
update bills
	set total_bill = (( (select sum(bill_items.billed_price-product_discount) 
	from bill_items 
	where bills.bill_number=bill_items.bill_number))  * ( 1 +  sales_tax  )) ,
	balance_due=balance_due + 
	(( (select sum(bill_items.billed_price-product_discount)
	from bill_items 
	where bills.bill_number=bill_items.bill_number))  * ( 1 +  sales_tax  )) ;

commit;
	update bills
set total_bill = (( product_price - product_discount )  \
* ( 1 + ( select sales_tax  from state where bills.state = state.state ))) \
where product_number in ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 );

Note: Not needed since we have balance in the bills table.

-- Update the customer balance for customers who just got a bill

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 );


-- Calculate the totals  - This must match the expected results

-- Note: Modified to read values out of new structures.
select  count(*) record_count, 
        sum ( billed_price ) sum_product_price,  
        sum ( product_discount) sum_product_discount
from bill_items;

select  count(*) recourd_count, 
        sum ( product_price ) sum_product_price,  
        sum ( product_discount) sum_product_discount, 
        sum ( total_bill )  sum_total_bill
from bills;
Note: We had to move the total bill down to this section since 
that data is now at the bill level.

Note also: Because there are 4 states in the original customer table
which do not exist in the State table, the number of bills will not
match the number of customers (off by 120) and the balance_due as 
computed from bills will not include the 6044509.99 balance due from
these customers.  This could of course be corrected, but that is 
beyond the scope of this effort.

select count(*) customer_count,
       sum(total_bill) sum_total_bill,
       sum ( bills.balance_due )
from bills;


-- Perform a checkpoint to make sure all data is written to disk -
-- This is required ;)

execute function sysadmin:task ( 'onmode', 'c' );


-- End of SQL script

参考资料

学习

获得产品和技术

讨论

条评论

developerWorks: 登录

标有星(*)号的字段是必填字段。


需要一个 IBM ID?
忘记 IBM ID?


忘记密码?
更改您的密码

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件

 


在您首次登录 developerWorks 时,会为您创建一份个人概要。您的个人概要中的信息(您的姓名、国家/地区,以及公司名称)是公开显示的,而且会随着您发布的任何内容一起显示,除非您选择隐藏您的公司名称。您可以随时更新您的 IBM 帐户。

所有提交的信息确保安全。

选择您的昵称



当您初次登录到 developerWorks 时,将会为您创建一份概要信息,您需要指定一个昵称。您的昵称将和您在 developerWorks 发布的内容显示在一起。

昵称长度在 3 至 31 个字符之间。 您的昵称在 developerWorks 社区中必须是唯一的,并且出于隐私保护的原因,不能是您的电子邮件地址。

标有星(*)号的字段是必填字段。

(昵称长度在 3 至 31 个字符之间)

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件.

 


所有提交的信息确保安全。


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=Information Management
ArticleID=715424
ArticleTitle=调优 Informix 数据库获取最佳性能,第 1 部分: 调优数据模型和应用程序
publish-date=07112011