2015/06/16

本日、社内のアプリ開発者向けにインフラ観点のデータベースチューニングについてプレゼンしました。

アプリの人もインフラの知識があるとチューニングのアプローチ方法が増えるよという話なので深い内容ではなくざっくりとした説明です。

資料とともに話した内容もあわせて公開しますので利用したいかたがいればご自由にコピーしてお使い下さい。

この資料の目的

日々システムの運用をやっていると急にアクセスが増えてオンラインのレスポンスが低下したとか夜間バッチが遅くなったというような性能問題に出くわすことがあります。

このような場合、データベースまわりのチューニングが必要になることがよくありますが、そんな時にアプリ側からのアプローチだけでなくそれを取り巻くインフラの知識もあるとより多角的なチューニングができますよというお話をしたいと思います。

今回はOracleを題材にしていくつかチューニングの例をお話しますが他のデータベースでも基本的な考え方は同じですのでお使いのデータベースにあわせて適宜読み替えて下さい。

チューニングの考え方

チューニングとひと口で言ってもコンピュータにはボトルネックになる部分がたくさんあって、ロジックを見直すだけだと手数が限られてしまいます。

Oracleの場合はAWRという分析専用のデータがあって、データベースの処理に対してCPUやメモリなどのリソースがどのように使われているかを詳細に調べることができます。

ただこれを活用するには専任のデータベースエンジニアが必要になるので、それよりもまずSQLが実際にコンピュータ上でどのような流れで実行されるかを知っておけばもっとシンプルにいろいろな観点でチューニングができるようになります。

SQLの動作原理

プログラムやコマンドラインから投入されたSQLはまず構文解析プログラムというものによって実際にデータベースからどうやって効率的にデータを取り出すかを考えて「実行計画」というものを作成します。

この実行計画は共有メモリ上に用意されたSGAという領域に保存され、同じSQLがまた実行された時に使いまわせるようにキャッシュされます。

データはこの実行計画に従ってデータベースから取り出されます。

バインド変数化

このSQLは一字一句同じものでないと使いまわせないので例えばこの2つのSQLは条件の部分が違うので別物とみなされます。

すると本来不要な2回目の実行計画作成のためにCPUやメモリなどのリソースが使われてしまいます。

この動きをすると当然ながらパフォーマンスが悪くなりますし、他の処理も足を引っ張られてマシン全体の性能が落ちてしまいます。

これを防ぐためには条件の部分を変数化してあげれば2つのSQLは同じになり2回目の実行計画の作成が行われずにリソースが節約できてパフォーマンスの低下が防げます。

統計情報

ところでこの実行計画は本来ならデータベースの中をフルスキャンしないと作れませんが、毎回これをやっていると時間がかかって仕方ないのであらかじめ深夜などのデータベースが使われていない時間帯に「統計情報」と呼ばれる旅行のガイドブックのようなものを作っておいてこれをもとに実行計画を立てます。

しかしこのガイドブックは元のデータが変わるとだんだん役に立たなくなってくるので定期的に作り直す必要があります。

3年前のガイドブックを見て旅行に行く人がいないのと同じですね。

とはいえガイドブックを作るのもリソースをかなり使うので作るタイミングを適切に調整することが重要です。

再編成

また、統計情報がしっかり作られていても実際のデータの配置があちこちに散らばっていると結局アクセスに時間がかかってしまうので大元のデータベースそのものも再編成してあげる必要があります。

Windowsでいうとデフラグのようなものですが、これもやはり処理にはリソースを大量に使うので週末に実行するなど本業務に影響のないタイミングで実行する必要があります。

インデックス

最後にご紹介するのがみなさんもよく知っているインデックスです。

一般的にインデックスを張れば処理が早くなるのは知られていますが、何も考えずにインデックスを張っても高速化するわけではありません。

特に大きなテーブルの場合、インデックス内の検索までは早いものの実際のデータが物理的に離れた場所にあるためそこにアクセスするためのハードディスクのシークに時間がかかり、トータルでの処理速度はあまり上がらないことがあります。

パーティショニング

ここで「パーティショニング」という考え方が出てきます。

これはインフラの世界では「細かく区切る」というような意味ですが、例えば毎月時系列にデータが増えていくようなテーブルの場合、テーブルを月毎に内部で分割することによって検索対象の領域を一ヶ所に集めることができ物理的なデータの並びもある程度揃えることができるのでインデックスを張るより効果的な場合があります。

まとめ

このようにチューニング一つとってもインフラの知識があると広い視野から多角的なアプローチができるようになりますのでみなさんのスキルセットに加えていただくとエンジニアとしての価値がより高くなるのではないかと思います。