May 12, 2010.

ＤＢ高速化�@

あまり大規模なプロジェクトにかかわったことがなかったので、これまでのbaristaのDB設計への意識はおざなりなものだったように思う。SEとして高速なSQLを追求することはあっても、PM,PL的観点から長期にわたるパフォーマンスを見越したDB設計を行なったことはなかった。

最近になってパフォーマンスが問題となるシステムが増えたため、SQLレベルでの改善を試みていたのだが、もう限界に達していた。悩んだ結果、自分の端末内にDBのバックアップを展開し、空き時間に設計的観点からのパフォーマンスチューニングを検討した。その結果、目覚しい高速化を実現、本番環境にも導入することとなった。そんなわけで最近、ＤＢのチューニングが楽しい。鳥頭なので、考えをメモしておこうと思う。勉強しながらの事なので、間違いがあったらご容赦を＆ご指摘ください。

インデックス構築

インデックスを使うと速いなんてのは馬鹿でも知っている事実だが、実感できるほど重いデータを扱うことがなかった。しかし、今回ははっきりと効果がわかる事例があったのでちょっと紹介しておこうと思う。

現在担当しているあるシステムは７年ぐらい前に別の会社が開発したパッケージの権利を買い取り、顧客ごとに大幅なカスタマイズを行なって導入しているものだ。仕様として各テーブルにトリガーが設定され、insert/update時にさまざまな処理を自動で行うようになっていた。

ところ最近、導入当初に15分ほどで完了していたバッチ処理が、ピーク時には30分以上かかるようになって来た。履歴データは３ヶ月しか持たないので、単純に１日当たりのデータが増えたのが要因。パフォーマンス調査を行なったところ、トリガーが足を引っ張っていることが判明。トリガの処理中での検索が非常に遅かった。そのあたりの原因究明に前回の日記の内容が役に立った。

問題のトリガはupdateのたびにupdate対象を自分のテーブルの中から検索という処理を行なっていたのだが、これに異常な時間がかかっていた。10万件のテーブルだと、１データupdateするたびに10万件から1件を探すselectが走る。バッチ処理で1000行の処理を行なうと、のべ10万x1000件の検索が走ることになる。

以前にこれを改善するために、�@トリガをやめる。�Aトリガが行なっていた処理をプログラムで実装するという２手順による高速化計画を立て、見積もりを書いたのだが、云百万円になってしまった。

今回細かく調査を行なったところ、各テーブルにはPK以外のインデックスがほとんどなかった。改善のため、トリガで利用している検索項目や、さまざまな結合に用いられる項目をインデックス化したところ、なんと2〜30分程度の処理が3分程度で終わるようになってしまったのだ。サーバで20分の処理が、自分のパソコンでも5分かからない。ここまでの差が出るとは思っていなかったので驚いた。

インデックスは万能ではない。定期的な再構築が必要となるし、そもそもinsertやupadteが遅くなってしまう。しかし、今回の場合、insert & updateの際に走るトリガが遅いのが問題だったため、insertやupdateが遅くなるというインデックスの欠点は問題にならなかった。完全な成功例といえるだろう。

調査結果を上司に報告し、結果として今回の改善は云十万円で受注となり、お客さんの環境に提供することとなった。コーディングの10％のコストで90%程度の性能のパフォーマンスアップができた事になる。検討しない手はない。また、システムを新規作成する際には画面や機能を想像し、それに必要となるインデックスの構成を考えた上でテーブル設計をすると良いだろう。

長くなったので続きはまた明日。

Copyright 2010 barista. All rights reserved.