Technical Deep Dive from AWS Support Series [1] Database Profile 編

■プロフィール

アマゾンウェブサービスジャパン合同会社 サポートエンジニアリング

Yokoo ： クラウドサポートエンジニア（Database Profile）

Tateno ： シニアクラウドサポートエンジニア（Database Profile）

はじめに

今回インタビュアーをつとめます、シニアクラウドサポートエンジニアの Tateno です。早速、Yokoo さんからお話を伺っていきたいと思います。

クラウドサポートエンジニアの Yokoo です。データベースプロファイルで2年ちょっとになります。データベースのサポートを担当しており、Tateno さんにはいろいろ教えてもらってます。よろしくお願いします。サポートエンジニアになる前は、20年近く開発の業務をやってまして、データベースはユーザーとして触るぐらいで、AWSに入社するまではデータベースにはそんなには詳しくありませんでした。開発の経験を活かすと、お問い合わせいただいてるお客さまの気持ちが想像しやすいので、エンジニアとして培った知見をサポートに活かせてるかなと思います。

今回の Deep Dive 事例 : Aurora MySQL における内部一時テーブルの挙動

ありがとうございます。それでは、今回 Deep Dive に関してお話を伺っていくシリーズということで、まず、そもそも問い合わせを受けたときの問題に関して、簡単に説明をお願いします。

Aurora MySQL バージョン 3 で、SELECT のクエリを発行するとエラーが発生したので、原因はなんですか、というお問い合わせでした。
具体的に、どういうクエリを実行した場合にどういうエラーが起きたのかをご説明していきますと、まず、クエリは単純な SELECT 文で、プライマリーキーを ORDER BY に指定しているケースでした。クライアントは Java のプログラムで、JDBC ドライバー経由で実行されていました。
次に、エラーは、一時テーブルがフルになった、つまり領域が枯渇したというエラーでした。メッセージとしては「SQL state [HY000]; error code [1114]; The table '/rdsdbdata/tmp/#xxxxxxxxxxx' is full」となります。

なるほど。それは、一時テーブルを使ってるなら場合によって普通に出そうだとは思うんですが、何かそういう単純じゃない条件があったっていうふうなことになりますか？

問い合わせの内容からは、このクエリ自体の結果セットがすごく大きいっていうことを頂いていました。なので、サイズが大きくてローカルストレージが枯渇してるのか、一時テーブルの設定がワークロードに沿ってなくてある程度小さいのか、っていうのが最初に感じた印象です。

一時テーブルの動きの難しさについて

となると、まず最初に、一時テーブルの枯渇という 領域の枯渇っていう問題ってどのように起きるのかというところから始めましょうか。Aurora MySQL バージョン 3 においては、一時領域の枯渇って、どういうときに発生するんでしょう。Aurora MySQL のストレージって単純な仕組みじゃないですよね。

Aurora MySQL は、実際にテーブルのデータを保存するためのクラスターストレージと、一時テーブルやログの情報が書かれるローカルストレージの、2種類のストレージがあります。クラスターストレージは自動的に拡張されるんですが、ローカルストレージはデータベースインスタンスのインスタンスクラスによって決まる、固定サイズのストレージになっています。

となると、今回はローカルストレージに関連する問題だったということになりますか。

そうですね、その可能性はありましたが、それだけではありませんでした。一時テーブルに関する領域の枯渇は、先ほど言ったとおり、結果セットがすごく大きくて、クラスターストレージではない、一時テーブルを置くローカルストレージ側が足りなくて起きるか、一時テーブルの設定の上限が設定できますので、それが足りなくていっぱいになったとかっていうのがあるんです。

そうなると、一時テーブルの領域の枯渇問題って色々な理由で起きるんですね。

はい。Aurora MySQL バージョン3 は、その一時テーブルの保存先がいっぱいになったらストレージの上にオーバーフローするっていう仕組みがあるんですけど、その仕組みが複雑なので、そこにフォーカスして調査しようかなと考えました。
まず、内部ツールを使って調査しまして、ローカルストレージは余裕があったのが分かったので、ローカルストレージの問題といった事象ではないことが分かりました。次に、一時テーブルの設定がデフォルトのままだったので、1GBしか使えないという設定になってました。そうすると1GB使用するとエラーが出ますので、この設定をもう少し大きくしてみてはどうか、という点のご案内が必要だと考えました。
また、リーダーインスタンスの場合はちょっとまた話が別になりまして、仮にこの設定を上げたとしても、ローカルストレージの上限いっぱいでフルになってしまうという制限があります。クラスターボリュームを一時領域には使えないという仕組みになっているので、その辺は注意して設定してくださいというご案内を最初にしました。

Aurora MySQL の場合、ライターとリーダーで一時領域のしくみが変わるのは分かりにくいところですよね。このあたりの、一時テーブルの動きってかなり複雑だと思うんですが、何か分かりやすく解説されてるものとかってあるんでしょうか。

AWSから提供しているブログにそれが詳しく書かれてる記事「Use the TempTable storage engine on Amazon RDS for MySQL and Amazon Aurora MySQL」がありまして、こういう条件だったらこっちのフローに行きますよってフローチャートもありますので、そちらも一緒にご案内しました。これを読むと、どういった場合に何が起きるかが具体的に解説されています。

お客さまの本当の疑問について

分かりやすくて素晴らしい記事ですよね。つまり、やはり一時領域がフルであるっていうような場合は、そのフローチャートに従ってちゃんと調べていくことで、通常は解決するっていうことですね。

はい。でも、今回に関しては、それではお問い合わせのほうは完結しませんでした。お客さまから共有いただいたクエリだと、単純にプライマリキーで ORDER BY をして SELECT してるだけのクエリだったので、結果自体は大きかったんですけども、お客さまとしては一時テーブルをいっぱい使っているというよりも、そもそもなんで一時テーブルを使ってるんだということが疑問だったようでした。

たしかに、MySQL はこの条件しかないのであれば、本来は一時テーブルを使わずに結果を返してくれますもんね。

なので、追加のお問い合わせで、このクエリはそもそもどのような理由で一時テーブルを使ってしまうんでしょうか、というご質問をいただきました。
そもそも、一時テーブルがどういうときに使われるか、に関しては、一時テーブルを使うための条件というのが MySQLのドキュメント「8.4.4 MySQL での内部一時テーブルの使用」にありますので、それに該当してるかどうかです。お客さまもそのドキュメントを確認されていて、12個ぐらい条件があるんです。
ですが、今回のクエリはその条件に合致してないように見える、というものが、お客様の疑問でした。私も改めて確認し、このクエリが ORDER BY を使用しているだけの単純なクエリだとすると、ここに列挙されているどの条件に合致していて、なんで一時テーブルを使うようになってるのか、と疑問を持ちました。

深掘りの基本は再現検証

そういう場合は、どのように調査されていきますか。

まず、再現させることが大事です。それに先だって、まず社内のデータベースプロファイルの同僚に、このクエリだと確かに一時テーブル使うように見えないんですけど、なんで使うんですかねっていうのをちょっと相談してみました。
それで、確かに一時テーブルを使うのは変だねっていうアドバイスもらって、実際の表定義をもらってるのであれば、検証してみるのが良いんじゃない、っていうアドバイスいただいたんで、検証してみました。

検証は大事ですね。やってみると分かることって多いですからね。検証って、どういうことに気をつけて検証されますか。

できるだけお客さまの環境に沿うというか、お客さまと同じような状況で再現するかどうかが第一です。
具体的には今回、お客さまから表定義と実行したクエリをもらいましたので、実際にそのテーブルを作ってみました。一方、実際のデータは頂いておらず、レコードは数億件と頂いていたんですが、まずはそこまでのサイズでなくても再現できるかなと想定して、数千万件といった小さめのサイズでテスト用のデータを用意しました。また、お客さまから JDBC ドライバー経由でこういう接続文字列で実行してますという情報もいただいていたんで、JDBC ドライバーも用意して同じように接続文字列でアクセスしようとしました。

実際のデータはセキュリティの問題もありますから頂くことが難しいですが、それ以外の条件で分かることも多いですもんね。

それから、実際のテストに先だって、まずはより単純な条件において、クエリを実行した場合にそもそも一時テーブルが使用されるのかといったふうに、ちょっと切り分けて考えたいと考えました。なので、まずは同様のテーブル定義と多くのレコード数、及び、同じクエリで、単純にJDBCドライバーを使ったJavaのクライアントから実行して再現するかっていうのを確認しました。
その結果としては、再現しなかったんですが、レコードが多すぎて OutOfMemoryError となる状況が分かりました。なので、単純なテストでは再現しませんでした。また、そもそもそのようなクエリは OutOfMemoryError となる事象があることが分かりました。

普通に実行すると、別のエラーになってしまうということですね。となると、また別の条件がありそうです。

次に、お客さまと同様に、同じような接続文字列で実行したらどうなるかっていうのを検証するようにしました。
そうすると、お客さまの接続文字列には useCursorFetch という設定があり、カーソルを使ってフェッチしてるっていう設定になってました。なので、この部分を同様の設定にすると、一時テーブルを使用するようになりました。その結果、一時テーブルの領域を消費するようになり、この設定が少ないときに冒頭でいただいたエラーが発生する、というお客様の事象を再現することができました。

再現した後、何が事象の原因になったかについては、どのように調べられたんでしょうか。

まず、一時テーブルが実際にどの程度使われているのかを Performance Schema を使って調べてみました。その結果、確かに一時テーブルを使ってる様子が観察できました。
具体的には、「SELECT * FROM performance_schema.memory_summary_global_by_event_name where event_name like 'memory/temptable/%';」というクエリで確認を行いました。その後、MySQL のナレッジを改めて確認してみると、カーソルを使っている場合には、カーソルがフェッチするため、結果を一旦、一時テーブルに保存するという挙動が分かりました。これは「13.6.6.5 サーバー側のカーソルの制約」にて説明されています。
なので、この検証とドキュメントの情報から、カーソルを使うこと自体によって一時テーブルを使うんだなっていう挙動が分かりました

本当の問題の解決に向けて

となると、最終的に、お客さまにどのようにご案内されたんでしょうか。

まず、カーソルによる挙動ですよ、という点をご案内して、お客さまの質問に回答しました。一時テーブルを使う量に関しては、Perforamce Schema からある程度見積もることが事前にできるので、それで見積もってもらうっていう点があります。また、まず何億行というレコードをカーソルを使って読み取るという処理は、一時テーブルを使ってしまうので、例えばアプリケーション設計次第になりますけども、全レコード取らないである程度範囲を絞って取るといったアプリケーション設計の変更が必要じゃないかということを、提案をさせていただきました。

この事象では useCursorFetch=true っていう接続文字列が問題だったようですけど、これって一般的に使われるものなんでしょうか。

今回、お客様も結果セットが大きい事は認識していて、そういった場合に OutOfMemoryError となることは確認されていたと思います。なので、この OutOfMemoryError 問題を解決するために、カーソルを使っていたと思います。これ自体は、MySQL の JDBC ドライバーである MySQL Connector/J のドキュメントにおける「6.4 JDBC API Implementation Notes」の部分に記載されている、一般的な方法だと言えます。
しかし、そうすると確かに OutOfMemoryError は起きませんが、その分の負荷は別のところにいくので、今回のケースでは一時テーブルの消費につながったということになります。同様に、一時テーブルの消費が大きいから、useCursorFetchの設定を外してカーソルの使用をやめると、OutOfMemoryErrorになってしまうということになります。

ありがとうございます。お客様に解決方法をご案内する上で、動きを深く理解し本質的な解決が何かを考えないと、あるエラーを解消しようとご案内した結果、別のエラーになるだけということもありますもんね。簡単なSQLであっても、状況や設定次第で問題となる事象になってしまうといった、一見分かりやすそうで実はちょっとこみいってる事象を、完全に再現・分析し、お客さまに価値を届けられた、というのがすごく良かったかなと感じました。
いうわけで、今回は Yokoo さんから Deep Dive にどう取り組むかという話をお届けいただきました。ご説明いただきありがとうございました。

ありがとうございました！

クラウドサポートエンジニア について

クラウドサポートエンジニアの仕事は「技術力でお客様のイノベーションをお手伝いすること」です。日々のお問合せ対応を通じて様々なお客様の様々なユースケースに携わることができるため自然と学び続けることができる環境です。また、お客様のお問合せから見えてくる AWS サービスの課題・改善点をサービス開発チームにフィードバックすることも重要な仕事です。他にも AWS re:Invent などのイベントでの登壇・発表などクラウドや AWS の啓蒙活動も行っています。
ご興味のある方はぜひこちらのウェブサイトにお越しください！

クラウドサポートエンジニアについて詳しくはこちら