今回は、ORACLEデータベースのパフォーマンスについての話です。
ちょっと専門用語も出てきます。
ちょっと専門用語も出てきます。
■データベースにも反抗期がある
人の一生と、データベースの一生が似ている気がします。
人は、生まれた日が誕生日です。
データベースの誕生日は、本番運用開始日がそれにあたります。
それぞれ、世に出た日が誕生日なのです。
人は、赤ん坊のころは、泣き叫ぶことで、自分の要求を伝えます。
データベースも同様。
運用開始したころは、想定以上の負荷に対して、泣き叫ぶようにエラーを吐き続けます。
時には、ハングして、親であるデータベースエンジニアをも泣かせます。
運用開始後1年経つと、障害発生数は少なくなり、あまり手がかからなくなります。
人だったら、小学生から中学生くらいでしょうか。
もう色んなことが一人でできるようになり、自立への道を進んでいる時期です。
自分の意見や主張も出てくる頃です。
やがて、親に反発して、何もしゃべってくれなかったり、自分で決めたことを勝手にやるようになります。
大人が嫌いになり、突然不機嫌になるということもあります。
データベースも、同じように、データベースエンジニアを困らすような、分けのわからない不機嫌さを見せるようになります。
人は、生まれた日が誕生日です。
データベースの誕生日は、本番運用開始日がそれにあたります。
それぞれ、世に出た日が誕生日なのです。
人は、赤ん坊のころは、泣き叫ぶことで、自分の要求を伝えます。
データベースも同様。
運用開始したころは、想定以上の負荷に対して、泣き叫ぶようにエラーを吐き続けます。
時には、ハングして、親であるデータベースエンジニアをも泣かせます。
運用開始後1年経つと、障害発生数は少なくなり、あまり手がかからなくなります。
人だったら、小学生から中学生くらいでしょうか。
もう色んなことが一人でできるようになり、自立への道を進んでいる時期です。
自分の意見や主張も出てくる頃です。
やがて、親に反発して、何もしゃべってくれなかったり、自分で決めたことを勝手にやるようになります。
大人が嫌いになり、突然不機嫌になるということもあります。
データベースも、同じように、データベースエンジニアを困らすような、分けのわからない不機嫌さを見せるようになります。
■わがままデータベース
エラーは出さないのですが、突然パフォーマンスが落ちたり不安定になったり。
運用開始直後の、分かりやすい障害ではなく、徐々に何かが悪くなっていくような、、、、分かり辛い障害。
はっきりした兆候がログに記録されていればいいのですが、それも無くて、原因がつかみづらい、、、
運用開始直後の、分かりやすい障害ではなく、徐々に何かが悪くなっていくような、、、、分かり辛い障害。
はっきりした兆候がログに記録されていればいいのですが、それも無くて、原因がつかみづらい、、、
親：データベースエンジニア
「何か、言いたいことあるのか！？」
子：データベース
「親父に言うことなんかねえよ！」
「何か、言いたいことあるのか！？」
子：データベース
「親父に言うことなんかねえよ！」
ここで、人の子ならほっといて様子を見ることもありなのでしょうが、データベースの場合、機嫌よく動いてもらわないと
いけないので、親父であるデータベースエンジニアが、意地でも何とかします。
いけないので、親父であるデータベースエンジニアが、意地でも何とかします。
■息子よ、おまえは何か隠しているだろう！？
昨日まで、パフォーマンスが良かった機能が、今日は、パフォーマンスが悪くなり、何もしなくても次の日良くなった
ということがありました。
ということがありました。
1)突然、不機嫌に
お客様「何か、この機能、いつもより遅いけど調べてくれない？」
という依頼を受けて、以下のようなことを調査しました。
お客様「何か、この機能、いつもより遅いけど調べてくれない？」
という依頼を受けて、以下のようなことを調査しました。
・アプリケーションが変わってないか
・データベースのインデックスや、テーブル構成が変わっていないか
・テーブルの統計情報は毎日取得されているか
・テーブルのデータ件数は大きく変化していないか
・データベースのインデックスや、テーブル構成が変わっていないか
・テーブルの統計情報は毎日取得されているか
・テーブルのデータ件数は大きく変化していないか
上記については、特に問題ありませんでした。
データベースに対しては何も手を加えていないし、データも大きく変わっていないので、何で急にパフォーマンスが
落ちたのか原因を特定することが出来ませんでした。
特定の機能だけが遅いだけで、システム全体としては動作するので、とりあえず様子を見ることにしました。
2)翌日、上機嫌に
お客様「昨日、遅かった機能、パフォーマンス戻ったよ。」
との連絡を受けました。
こちらとしては、何もしていないのにパフォーマンスが戻って、良かったと言いたいところですが、腑に落ちない部分が
ありました。
とりあえず、再現待ちとして、また何か起これば、そのときはサポートにも問い合わせて調査しましょうということで
落ち着きました。
3)翌日、また不機嫌に
お客様「何で、また遅くなってるんだ？ちゃんと調査してくれ！」
今度は、データベースの内部情報を取得して調査することにしました。
・パフォーマンスが良かった昨日の状態
・パフォーマンスが悪い今日の状態
2つの状態を比較するため、昨日と今日(問題となった機能が使用された時間帯に絞って)のAWRレポート(※1)を取得、
比較しました。
比較の結果、昨日は同じテーブルに対してインデックススキャンをしていたのに、今日はフルスキャンをしていました。
統計情報を毎日同じ時間に取得しているのに、何で、毎日実行計画が変わるのか不思議でなりません。
(※1)AWRレポートとは、特定の時間帯に絞って、データベースにどういった待機イベントが発生していたか、
どんなSQLの負荷が高かったかを確認できるHTMLまたは、txt形式のファイル。
データベースに対しては何も手を加えていないし、データも大きく変わっていないので、何で急にパフォーマンスが
落ちたのか原因を特定することが出来ませんでした。
特定の機能だけが遅いだけで、システム全体としては動作するので、とりあえず様子を見ることにしました。
2)翌日、上機嫌に
お客様「昨日、遅かった機能、パフォーマンス戻ったよ。」
との連絡を受けました。
こちらとしては、何もしていないのにパフォーマンスが戻って、良かったと言いたいところですが、腑に落ちない部分が
ありました。
とりあえず、再現待ちとして、また何か起これば、そのときはサポートにも問い合わせて調査しましょうということで
落ち着きました。
3)翌日、また不機嫌に
お客様「何で、また遅くなってるんだ？ちゃんと調査してくれ！」
今度は、データベースの内部情報を取得して調査することにしました。
・パフォーマンスが良かった昨日の状態
・パフォーマンスが悪い今日の状態
2つの状態を比較するため、昨日と今日(問題となった機能が使用された時間帯に絞って)のAWRレポート(※1)を取得、
比較しました。
比較の結果、昨日は同じテーブルに対してインデックススキャンをしていたのに、今日はフルスキャンをしていました。
統計情報を毎日同じ時間に取得しているのに、何で、毎日実行計画が変わるのか不思議でなりません。
(※1)AWRレポートとは、特定の時間帯に絞って、データベースにどういった待機イベントが発生していたか、
どんなSQLの負荷が高かったかを確認できるHTMLまたは、txt形式のファイル。
■SQLはどう解釈されるのか？
統計情報とか、アクセスパスとか、色々と専門用語が出てきたので、ちょっと解説します。
以下の「ORACLEが受け取ったSQLを解釈する」図を見てください。
以下の「ORACLEが受け取ったSQLを解釈する」図を見てください。
①端末から投げられたSQLは、ORACLEに受け渡されます。
②ORACLEは、受け取ったSQLをオプティマイザという機能で解析します。
オプティマイザとは、ORACLEの脳みたいなものです。
投げられたSQLを解析して、テーブルの統計情報(※2)から、最適な実行計画(※3)を決定します。
オプティマイザとは、ORACLEの脳みたいなものです。
投げられたSQLを解析して、テーブルの統計情報(※2)から、最適な実行計画(※3)を決定します。
③実行計画を元にディスクからデータを取得して、端末に結果を返します。
(※2)統計情報とは、テーブルデータのばらつきや、件数などの情報のことです。
テーブルごとに存在します。
(※3)実行計画とは、投げられたSQLと統計情報を元に、どういう風にテーブルからデータを取得するかといったORACLEの
動作についての計画書です。
テーブルごとに存在します。
(※3)実行計画とは、投げられたSQLと統計情報を元に、どういう風にテーブルからデータを取得するかといったORACLEの
動作についての計画書です。
■データベースが思っていること
統計情報が、実際のデータと一致していない状態は、パフォーマンス劣化を引き起こす原因になります。
オプティマイザが、統計情報を参考に実行計画を作成するからです。
例として、インデックスを作成しているテーブルに対して統計情報を取得したとします。
このテーブルが0件の時、統計情報を取得したら、ORACLEのオプティマイザはこのテーブルが100万件になっても、
「0件のまんまだな」
と、ずっと思っています。
このテーブルに対して、インデックスを使用したSQLを実行しても、応答が遅いかもしれません。
なぜなら、ORACLEのオプティマイザは
「0件のまんまだな」
と、今も思っているからです。
0件だったら、インデックス使う必要無いと思い、フルスキャンでアクセスする可能性が高いです。
オプティマイザが、統計情報を参考に実行計画を作成するからです。
例として、インデックスを作成しているテーブルに対して統計情報を取得したとします。
このテーブルが0件の時、統計情報を取得したら、ORACLEのオプティマイザはこのテーブルが100万件になっても、
「0件のまんまだな」
と、ずっと思っています。
このテーブルに対して、インデックスを使用したSQLを実行しても、応答が遅いかもしれません。
なぜなら、ORACLEのオプティマイザは
「0件のまんまだな」
と、今も思っているからです。
0件だったら、インデックス使う必要無いと思い、フルスキャンでアクセスする可能性が高いです。
■統計情報取得時のデータ件数が問題だった
問題となった機能で使用されているテーブルAは、ワークのような使い方をするテーブルでした。
多いときは100万件、少ないときは0件という状態が不定期に発生します。
パフォーマンスが悪かった日、テーブルAが統計状態を取得される時のデータ件数は0件でした。
ちなみに、統計情報は毎日夜中2時に取得をします。
0件の時に統計が取得されたテーブルAは、その日1日、日中に100万件のデータが入った状態でも0件の統計情報を使用して、
実行計画を作成します。
この場合、インデックスの項目をwhere句に指定したSQLは、有無を言わさず、100万件のデータに対してフルスキャンを行う
ような動きをしてしまいます。
多いときは100万件、少ないときは0件という状態が不定期に発生します。
パフォーマンスが悪かった日、テーブルAが統計状態を取得される時のデータ件数は0件でした。
ちなみに、統計情報は毎日夜中2時に取得をします。
0件の時に統計が取得されたテーブルAは、その日1日、日中に100万件のデータが入った状態でも0件の統計情報を使用して、
実行計画を作成します。
この場合、インデックスの項目をwhere句に指定したSQLは、有無を言わさず、100万件のデータに対してフルスキャンを行う
ような動きをしてしまいます。
そして、次の日、テーブルAは100万件のデータの状態で、統計情報の取得が行われました。
その日一日パフォーマンスが回復しました。
この場合、インデックスの項目をwhere句に指定したSQLは、ちゃんとインデックスが使用されたからです。
ただ、その日は良くても次の日どうなるか分かりません。
0件の状態で統計情報を取得される可能性があるからです。
その日一日パフォーマンスが回復しました。
この場合、インデックスの項目をwhere句に指定したSQLは、ちゃんとインデックスが使用されたからです。
ただ、その日は良くても次の日どうなるか分かりません。
0件の状態で統計情報を取得される可能性があるからです。
■統計情報を取らないことにしました
テーブルAのようなテーブル件数の増減が激しいワークのようなテーブルは、統計情報を取得しないことになりました。
取得しないというよりは、一度、100万件の状態で取得して、その後は取得しないようにする設定を入れました。
こうすることで、統計情報はずっと同じものを使い続けるので、毎日パフォーマンスが変化することがありません。
毎日インデックスが使用されるはずです。
本当なら、設計の段階でワークのようなテーブルを洗い出し、本番運用前にこういった統計情報の固定化対策を入れておくべき
でした。
取得しないというよりは、一度、100万件の状態で取得して、その後は取得しないようにする設定を入れました。
こうすることで、統計情報はずっと同じものを使い続けるので、毎日パフォーマンスが変化することがありません。
毎日インデックスが使用されるはずです。
本当なら、設計の段階でワークのようなテーブルを洗い出し、本番運用前にこういった統計情報の固定化対策を入れておくべき
でした。
