IBAnalyst: 了解您的数据库

Dmitri Kuzmenko, [email protected]、最終更新日：2014年3月31日

私は1994年からInterBaseに携わってきました。当時、ほとんどのデータベースは小さく、チューニングを必要としませんでした。もちろん、サーバー上でibconfigを変更したり、ハードウェアやOSを再構成したりする場面もありましたが、パフォーマンスをチューニングするために私ができることはほぼそれだけでした。

4年前、私たちの会社はInterBaseユーザーへのテクニカルサポートとトレーニングの提供を開始しました。多くの本番データベースを扱うことで、さまざまなことを学びました。しかし、私が学んだことのほとんどはアプリケーションに関するものでした。トランザクションパラメータの使用法、クエリと結果セットの最適化などです。

もちろん、データベースの統計情報を提供するツールであるgstatについて、かなり前から知っていました。gstatの出力を見たことがある方や、opguide.pdfでそれについて読んだことがある方は、統計出力がただの数字の羅列にしか見えないことをご存じでしょう。確かに、特定のテーブルやインデックスの断片化情報を見つけることはできますが、他にどのような有用な情報が得られるでしょうか？

幸いなことに、InterBaseに携わる前から、私はさまざまなデータ構造、その格納方法、そしてそれらが使用するアルゴリズムに興味を持っていました。これにより、gstatの出力を解釈するのに役立ちました。その時、私はgstatの出力を分析してデータベースのチューニングに役立てる、あるいは少なくともパフォーマンス問題の原因を特定するツールを書くことを決意しました。

長い話ですが、その結果としてIBAnalystが作成されました。私の経験にもかかわらず、それは今でもさまざまなデータベースで非常に興味深い発見やパフォーマンス問題を見つけることを可能にしてくれます。

実際のシステムでは、実行時のパフォーマンスは波のように変動します。そのような「波」の振幅は低い場合も高い場合もあり、パフォーマンスが日々（または時間ごとに）どのように異なるかを見ることができます。実際のパフォーマンスは、アプリケーションの設計、サーバー構成、トランザクションの並行性、データベース内のバージョンガベージなど、多くの要因に依存します。データベースで何が起こっているか（パフォーマンスの良い面と悪い面の両方）を把握するには、少なくとも時々データベース統計を覗いてみるべきです。

実際のシステムでは、実行時のパフォーマンスは波のように変動します。そのような「波」の振幅は低い場合も高い場合もあり、パフォーマンスが日々（または時間ごとに）どのように異なるかを見ることができます。実際のパフォーマンスは、アプリケーションの設計、サーバー構成、トランザクションの並行性、データベース内のバージョンガベージなど、多くの要因に依存します。データベースで何が起こっているか（パフォーマンスの良い面と悪い面の両方）を把握するには、少なくとも時々データベース統計を覗いてみるべきです。

IBAnalystの機能を見てみましょう。IBAnalystはgstatまたはServices APIから統計を取得し、データベース、そのテーブル、インデックスに関する完全な情報を提供するレポートにまとめることができます。統計の閲覧中に利用可能なその場での警告があり、ヒントコメントや推奨レポートも含まれています。

データベース情報

図1 データベース統計の概要

図1に示す概要は、データベースに関する一般的な情報を提供します。表示される警告やコメントは、多数の実世界の本番データベースから得られた慎重に収集された知識に基づいています。

注：この記事のすべての図には、実世界の本番データベース（所有者の許可を得て）から取得したgstat統計が含まれています。

前述のとおり、生のデータベース統計は難解で解釈が難しいものです。IBAnalystは潜在的な問題を黄色または赤で明確に強調表示し、問題の詳細は、関連するエントリにカーソルを置いて表示されるヒントを読むだけで確認できます。上の図から何がわかるでしょうか？これは方言3のデータベースで、ページサイズは4096バイトです。6〜8年前、開発者はデフォルトのページサイズとして1024バイトを使用していましたが、最近ではそのような小さなページサイズは多くのパフォーマンス問題を引き起こす可能性があります。このデータベースはページサイズが4kであるため、このページサイズは問題ないとして、警告は表示されていません。

次に、Forced WriteパラメータがOFFに設定され、赤でマークされていることがわかります。InterBase 4.xおよび5.xでは、デフォルトでこのパラメータはONでした。Forced Writes自体は書き込みキャッシュ方式です。ONの場合、変更されたデータは即座にディスクに書き込まれますが、OFFの場合、書き込みはオペレーティングシステムのファイルキャッシュに無期限に保存されます。InterBase 6はForced Writes OFFでデータベースを作成します。

なぜこれがIBAnalystレポートで赤でマークされているのでしょうか？答えは簡単です。非同期書き込みを使用すると、電源障害、OS障害、サーバー障害が発生した場合にデータベースの破損を引き起こす可能性があるからです。

ヒント：最新のHDDインターフェース（ATA、SATA、SCSI）では、Forced WriteをONまたはOFFに設定しても、パフォーマンスに大きな違いは見られないことは興味深いことです(1)。

レポートの次は、謎めいた「スイープ間隔」です。正の場合、エンジンが自動ガベージコレクションの開始を警告される、最も古い(2)トランザクションと最も古いスナップショットトランザクションの間のギャップのサイズを設定します。一部のシステムでは、このしきい値に達すると「突然のパフォーマンス低下」効果が発生し、その結果、スイープ間隔を0に設定する（自動スイープを完全に無効にする）ことが推奨されることがあります。ここでは、スイープ間隔が黄色でマークされています。これは、スイープギャップの値が負であるためです。これはInterBase 6.0、Firebird、Yaffilの統計では発生しますが、InterBase 7.xでは発生しません。スイープギャップの値がスイープ間隔より大きい場合（スイープ間隔が0でない場合）、スイープ間隔のレポートエントリは適切なヒントとともに赤でマークされます。

次の8行はグループとして検討します。これらはすべてデータベースのトランザクション状態の側面を表示しているためです。

• 最も古いトランザクションは、最も古い未コミットトランザクションです。これより低いトランザクション番号はコミット済みトランザクション用であり、そのようなトランザクションにはレコードバージョンはありません。最も古いトランザクションより高いトランザクション番号は、任意の状態にある可能性のあるトランザクション用です。これは「最も古い関心のあるトランザクション」とも呼ばれます。トランザクションがロールバックで終了すると凍結され、サーバーはその時点でその変更を元に戻すことができないためです。
• 最も古いスナップショット - 現在最も古い「関心のある」トランザクションであるトランザクションの開始時に存在した、最も古いアクティブ（つまり、まだコミットされていない）トランザクション。レコードバージョンに関心のある最も低いスナップショットトランザクション番号を示します。
• 最も古いアクティブ - 現在アクティブな最も古いトランザクション(3)。
• 次のトランザクション - 新しいトランザクションに割り当てられるトランザクション番号。
• アクティブなトランザクション - 最も古いアクティブなトランザクション番号が1日のトランザクション数の30%低い場合、IBAnalystは警告を出します。統計では、最も古いアクティブと次のトランザクションの間に他のアクティブなトランザクションがあるかどうかはわかりませんが、そのようなトランザクションが存在する可能性があります。通常、最も古いアクティブが動かなくなった場合、考えられる原因は2つあります：a）一部のトランザクションが長時間アクティブである、またはb）アプリケーション設計によりトランザクションが長時間実行される。どちらの原因もガベージコレクションを妨げ、サーバーリソースを消費します。
• 1日あたりのトランザクション数 - これは次のトランザクションを、データベースの作成から統計が取得された時点までの経過日数で割って計算されます。これは本番データベース、またはバックアップから定期的に復元されトランザクション番号がリセットされるデータベースにのみ正しく適用できます。

すでにご存じのとおり、警告がある場合は色付きの行として表示され、問題を修正または防止する方法について明確で説明的なヒントが示されます。

データベース統計が常に有用であるとは限らないことに注意してください。作業中やハウスキーピング操作中に収集された統計は意味をなさない場合があります。

以下の場合は統計を収集しないでください：

• データベースを復元した直後

• 执行了备份（gbak -b db.gdb），但未使用 -g 开关

• 最近执行了手动清理（gfix -sweep）

在这种情况下获得的统计数据实际上毫无用处。同样正确的是，在正常工作期间，数据库有时会处于完美状态，例如，当应用程序对数据库的负载低于平时（用户正在午餐或业务日处于低谷期）时。

如何判断数据库何时出现问题？

您的应用程序可能设计得非常好，以至于它们始终能正确处理事务和数据，不会产生清理间隙，不会积累大量活动事务，不会保持长时间运行的快照等。但通常情况并非如此（抱歉，各位同行）。

最常见的原因是开发人员在测试应用程序时只运行两三个并发用户。当应用程序随后在具有十五个或更多并发用户的生产环境中使用时，数据库可能会表现出不可预测的行为。当然，多用户模式可以正常工作，因为大多数多用户冲突可以通过两三个并发运行的应用程序进行测试。然而，随着用户数量的增加，垃圾回收问题可能会出现。如果您在正确的时机收集数据库统计信息，就可以捕获这些潜在问题。

表信息

让我们看看 IBAnalyst 的另一个示例输出。

.jpg)

图 2 表统计信息

IBAnalyst 的表统计信息视图也非常有用。它可以显示哪些表有大量记录版本、哪些表进行了大量更新/删除操作、哪些表存在碎片（由更新/删除或 BLOB 引起的碎片）等。您可以看到哪些表被频繁更新，以及表的大小（以兆字节为单位）。这些警告中的大多数都是可自定义的。

在此数据库示例中存在几个问题。首先，VerLen 列中的黄色警告表明记录版本占用的空间大于记录本身占用的空间。这可能是由于更新记录中的大量字段或批量删除导致的。请查看 MaxVers 列以蓝色标记的行。这表明每个记录只存储一个版本，因此问题是由批量删除引起的。Versions 列中的值显示了删除了多少条记录。

长期存在的活动事务阻止垃圾回收是性能下降的主要原因。对于某些表，可能仍有许多版本“正在使用中”。服务器无法确定它们是否真的在使用中，因为活动事务可能需要这些版本中的任何一个或全部。因此，服务器不会将这些版本视为垃圾，并且每当事务读取记录时，从大量版本中构建正确记录所需的时间会越来越长。在图 2 中，您可以看到两个表的版本数比记录数高出三倍。利用此信息，您还可以检查应用程序频繁更新这些表是设计使然还是由于错误导致的。

索引视图

索引被数据库引擎用于强制执行主键、外键和唯一约束。它们还加速了数据的检索。唯一索引最适合数据检索，但非唯一索引的收益程度取决于索引数据的多样性。

例如，查看 ADDR_ADDRESS_IDX6。首先，索引名称本身表明它是手动创建的。如果通过 Services API 获取了包含元数据信息的统计信息，您可以看到哪些列被索引（在 IBAnalyst 1.83 及更高版本中）。对于所检查的索引，您可以看到它有 34999 个键，TotalDup 为 34995，MaxDup 为 25056。两个重复列均以红色标记。这是因为该索引中所有键中只有 4 个唯一键值，如 Uniques 列所示。此外，最大的重复链（指向具有相同列值的记录的键）为 25056–即几乎所有键都存储了四个唯一值之一。因此，此索引可能：

• 降低恢复过程的速度。好吧，三万五千个键对于现代数据库和硬件来说不算什么，但仍应注意到其影响。
• 减慢垃圾回收速度。唯一值数量较少的索引与完全唯一的索引相比，可能会使垃圾回收速度降低多达十倍。此问题已在 InterBase 7.1/7.5 和 Firebird 2.0 中得到解决。
• 当优化器读取索引时产生不必要的页面读取。这取决于特定查询中搜索的值–通过 MaxDup 值较大的索引进行搜索会较慢。通过重复值较少的列进行搜索会更快，但只有您知道该列已被索引。

这就是为什么 IBAnalyst 会提醒您注意此类索引，将其标记为红色和黄色，并将其包含在“建议”报告中。不幸的是，大多数“不良”索引是为了强制执行外键约束而自动创建的。在某些情况下，可以通过使用触发器阻止对查找表中主键的删除或更新来解决此问题。但如果无法实施此类更改，IBAnalyst 将在您每次查看统计信息时显示外键上的“不良”索引。

报告

无需每次查看整个报告，识别单元格颜色并阅读新警告的提示。使用 IBAnalyst 的“建议”功能可以获得更直接、更详细的信息。只需加载统计信息并转到“报告/查看建议”菜单。此报告提供逐步分析，包括关于强制写入、清理间隔、数据库活动、事务状态、数据库页面大小、清理、事务清单页、碎片表、具有大量记录版本的表、大规模删除/更新、深度索引、不利于优化器的索引、无用索引甚至空表的更详细描述性警告。所有这些信息及随附建议都是根据加载的统计信息动态生成的。

作为报告输出的示例，让我们看看为本文前面看到的数据库统计信息生成的报告：

“事务清单页（TIP）的总体大小较大–94 千字节或 23 页。Read_committed 事务使用全局 TIP，但快照事务在内存中制作自己的 TIP 副本。较大的 TIP 大小可能会降低性能。尝试手动运行清理（gfix -sweep）以减小 TIP 大小。”

以下是报告表/索引部分的另一段引用：

“版本化表数量：8。大量记录版本通常会降低性能。如果表中有大量记录版本，则垃圾回收无法正常工作，或者记录未被任何 select 语句读取。您可以尝试在这些表上执行 select count(*) 以强制执行垃圾回收，但这可能需要很长时间（如果存在大量版本和非唯一索引），并且如果至少有一个事务对这些版本感兴趣，则可能不成功。

以下是版本/记录比率大于 3 的表列表：

表名	记录数	版本数	记录/版本大小
CLIENTS_PR	3388	10944	92%
DICT_PRICE	30	1992	45%
DOCS	9	2225	64%
N_PART	13835	72594	83%
REGISTR_NC	241	4085	56%
SKL_NC	1640	7736	170%
STAT_QUICK	17649	85062	110%
UO_LOCK	283	8490	144%

总结

IBAnalyst 是一款非常宝贵的工具，可帮助用户对 Firebird 或 InterBase 数据库统计信息进行详细分析，并识别数据库在性能、维护以及应用程序与数据库交互方面可能存在的问题。它将晦涩难懂的数据库统计信息以易于理解的图形方式显示出来，并自动提出关于提高数据库性能和简化数据库维护的合理建议。

1 InterBase 7.5 和 Firebird 1.5 具有特殊功能，可以在强制写入（Forced Writes）关闭时定期刷新未保存的页面。

2 最旧事务与各处提到的“最旧的有趣事务”是同一个概念。Gstat 输出不会将此事务显示为“有趣”。

3 Ann Harrison 表示，最旧活动事务是指当前最旧活动事务启动时处于活动状态的最旧事务。对于应用程序来说，这里没有太大区别。