常駐先で、ORACLEデータベースの管理やってます。ORACLE Platinum10g、データベーススペシャリスト保有してます。データベースの話をメインにしたいです

データベースはお年頃

»

今回は、ORACLEデータベースのパフォーマンスについての話です。
ちょっと専門用語も出てきます。

■データベースにも反抗期がある

 人の一生と、データベースの一生が似ている気がします。
 
 人は、生まれた日が誕生日です。
 データベースの誕生日は、本番運用開始日がそれにあたります。
 それぞれ、世に出た日が誕生日なのです。
 人は、赤ん坊のころは、泣き叫ぶことで、自分の要求を伝えます。
 データベースも同様。
 運用開始したころは、想定以上の負荷に対して、泣き叫ぶようにエラーを吐き続けます。
 時には、ハングして、親であるデータベースエンジニアをも泣かせます。
 運用開始後1年経つと、障害発生数は少なくなり、あまり手がかからなくなります。
 人だったら、小学生から中学生くらいでしょうか。
 もう色んなことが一人でできるようになり、自立への道を進んでいる時期です。
 自分の意見や主張も出てくる頃です。
 やがて、親に反発して、何もしゃべってくれなかったり、自分で決めたことを勝手にやるようになります。
 大人が嫌いになり、突然不機嫌になるということもあります。
 データベースも、同じように、データベースエンジニアを困らすような、分けのわからない不機嫌さを見せるようになります。

■わがままデータベース

 エラーは出さないのですが、突然パフォーマンスが落ちたり不安定になったり。
 運用開始直後の、分かりやすい障害ではなく、徐々に何かが悪くなっていくような、、、、分かり辛い障害。
 はっきりした兆候がログに記録されていればいいのですが、それも無くて、原因がつかみづらい、、、

  親:データベースエンジニア
   「何か、言いたいことあるのか!?」
  子:データベース
   「親父に言うことなんかねえよ!」

 ここで、人の子ならほっといて様子を見ることもありなのでしょうが、データベースの場合、機嫌よく動いてもらわないと
 いけないので、親父であるデータベースエンジニアが、意地でも何とかします。

■息子よ、おまえは何か隠しているだろう!?

 昨日まで、パフォーマンスが良かった機能が、今日は、パフォーマンスが悪くなり、何もしなくても次の日良くなった
 ということがありました。

 1)突然、不機嫌に
  お客様「何か、この機能、いつもより遅いけど調べてくれない?」
  という依頼を受けて、以下のようなことを調査しました。

   ・アプリケーションが変わってないか
   ・データベースのインデックスや、テーブル構成が変わっていないか
   ・テーブルの統計情報は毎日取得されているか
   ・テーブルのデータ件数は大きく変化していないか

  上記については、特に問題ありませんでした。
  データベースに対しては何も手を加えていないし、データも大きく変わっていないので、何で急にパフォーマンスが
  落ちたのか原因を特定することが出来ませんでした。
  特定の機能だけが遅いだけで、システム全体としては動作するので、とりあえず様子を見ることにしました。
  
 2)翌日、上機嫌に
  お客様「昨日、遅かった機能、パフォーマンス戻ったよ。」
  との連絡を受けました。
  こちらとしては、何もしていないのにパフォーマンスが戻って、良かったと言いたいところですが、腑に落ちない部分が
  ありました。
  とりあえず、再現待ちとして、また何か起これば、そのときはサポートにも問い合わせて調査しましょうということで
  落ち着きました。
  
 3)翌日、また不機嫌に
  お客様「何で、また遅くなってるんだ?ちゃんと調査してくれ!」
  今度は、データベースの内部情報を取得して調査することにしました。
  ・パフォーマンスが良かった昨日の状態
  ・パフォーマンスが悪い今日の状態
  2つの状態を比較するため、昨日と今日(問題となった機能が使用された時間帯に絞って)のAWRレポート(※1)を取得、
  比較しました。
  
  比較の結果、昨日は同じテーブルに対してインデックススキャンをしていたのに、今日はフルスキャンをしていました。
  統計情報を毎日同じ時間に取得しているのに、何で、毎日実行計画が変わるのか不思議でなりません。
  
  (※1)AWRレポートとは、特定の時間帯に絞って、データベースにどういった待機イベントが発生していたか、
    どんなSQLの負荷が高かったかを確認できるHTMLまたは、txt形式のファイル。

■SQLはどう解釈されるのか?

 統計情報とか、アクセスパスとか、色々と専門用語が出てきたので、ちょっと解説します。
 以下の「ORACLEが受け取ったSQLを解釈する」図を見てください。

Oraclesql_3

 ①端末から投げられたSQLは、ORACLEに受け渡されます。

 ②ORACLEは、受け取ったSQLをオプティマイザという機能で解析します。
  オプティマイザとは、ORACLEの脳みたいなものです。
  投げられたSQLを解析して、テーブルの統計情報(※2)から、最適な実行計画(※3)を決定します。

 ③実行計画を元にディスクからデータを取得して、端末に結果を返します。

  (※2)統計情報とは、テーブルデータのばらつきや、件数などの情報のことです。
     テーブルごとに存在します。
  (※3)実行計画とは、投げられたSQLと統計情報を元に、どういう風にテーブルからデータを取得するかといったORACLEの
    動作についての計画書です。

■データベースが思っていること

 統計情報が、実際のデータと一致していない状態は、パフォーマンス劣化を引き起こす原因になります。
 オプティマイザが、統計情報を参考に実行計画を作成するからです。
 例として、インデックスを作成しているテーブルに対して統計情報を取得したとします。
 このテーブルが0件の時、統計情報を取得したら、ORACLEのオプティマイザはこのテーブルが100万件になっても、
 「0件のまんまだな
 と、ずっと思っています。
 このテーブルに対して、インデックスを使用したSQLを実行しても、応答が遅いかもしれません。
 なぜなら、ORACLEのオプティマイザは
 「0件のまんまだな
 と、今も思っているからです。
 0件だったら、インデックス使う必要無いと思い、フルスキャンでアクセスする可能性が高いです。

■統計情報取得時のデータ件数が問題だった

 問題となった機能で使用されているテーブルAは、ワークのような使い方をするテーブルでした。
 多いときは100万件、少ないときは0件という状態が不定期に発生します。
 パフォーマンスが悪かった日、テーブルAが統計状態を取得される時のデータ件数は0件でした。
 ちなみに、統計情報は毎日夜中2時に取得をします。
 0件の時に統計が取得されたテーブルAは、その日1日、日中に100万件のデータが入った状態でも0件の統計情報を使用して、
 実行計画を作成します。
 この場合、インデックスの項目をwhere句に指定したSQLは、有無を言わさず、100万件のデータに対してフルスキャンを行う
 ような動きをしてしまいます。

 そして、次の日、テーブルAは100万件のデータの状態で、統計情報の取得が行われました。
 その日一日パフォーマンスが回復しました。
 この場合、インデックスの項目をwhere句に指定したSQLは、ちゃんとインデックスが使用されたからです。
 ただ、その日は良くても次の日どうなるか分かりません。
 0件の状態で統計情報を取得される可能性があるからです。

■統計情報を取らないことにしました

 テーブルAのようなテーブル件数の増減が激しいワークのようなテーブルは、統計情報を取得しないことになりました。
 取得しないというよりは、一度、100万件の状態で取得して、その後は取得しないようにする設定を入れました。
 こうすることで、統計情報はずっと同じものを使い続けるので、毎日パフォーマンスが変化することがありません。
 毎日インデックスが使用されるはずです。
 本当なら、設計の段階でワークのようなテーブルを洗い出し、本番運用前にこういった統計情報の固定化対策を入れておくべき
 でした。

■息子よ、立派になったな!

 中学2年生の頃、私は両親を自室に入れることを拒んでいました。
 「はいんじゃねーよ!」と。
 理由は、押し入れに隠した自作の漫画を読まれることが恥ずかしかったからです。
 両親にすれば、小学生の頃まで素直だった息子が、、、分けが分からなかったと思います。
 データベースも安定してくると、一人で好きにやらせてくれよっていう時期があるのかもしれません。
 ORACLEは特に、好きにやらせてあげられるだけの各種自動化機能が増えてきています。
 ただ、あんまり好きにやらせると、今回みたいに間違った統計情報で、正しいと思って動いてしまうのです。
 そこを、見抜いて誤った方向に行かないようにしてあげるのが、親(データベースエンジニア)の仕事なんでしょう。

Comment(1)

コメント

話の前提がまちがってる。
正しい統計情報で間違った動きをすることもあるという認識が抜けている。

コメントを投稿する