初参戦のISUCONで予選敗退した話 (ヽ´ω`) < 22位
お久しぶりです。この度、はじめてISUCONに参加することが出来たので、ブログを書いてみました。
ISUCON8 !!
エントリーするまでの話
「アプリエンジニアもすなるISUCONなるものを、インフラエンジニアもしてみむとてするなり。」
巷でISUCONというものが流行ってて、優勝したらお金ももらえるし、参加してる人楽しそうだし参加したいなぁという気持ちをこじらせてたのですが、ISUCON8 で初参加を果たす事ができました。
今回は、会社つながりで アプリエンジニアの 私 @aeroastro と @abish 、そしてインフラエンジニアの @naoka と一緒に「ほうじ茶ラテ部」を結成し、参加してきました! 締切直前に声をかけたにも関わらず、集まっていただいて本当にありがとうございました。
言語はGo と Perl と Ruby で迷ったのですが、せっかくだし業務で使うことの多いRubyで書いてみようということで、Rubyで挑戦してみました。(Perlとかにしたら良かったですね。。。)
結果
じゃじゃーん、初参戦で決勝進出ー!とやりたいところですが、予選敗退してしまいました。
22位
最後に数時間分の作業をロールバックしたのがきつかった。。。
結果は出せませんでしたし、100万円もゲット出来なかったのですが、めっちゃ楽しかったのは幸いです!
やったこと
ISUCONの参加者が得られている情報を前提として書いているものがあるので、ご容赦ください。(公式に講評がでたら、そこへのリンクも張ります)
戦略と全体感
インフラ周りの構成変更やセットアップ、各種プロファイラの設定等については @naoka におまかせ。ローカルの環境構築や動作確認手法の確立、RubyのWebAppの下回りを自分が担当。 @abish がコードリーディングとチューニングを進める。 下回りのセットアップが完了次第、自分もコードリーディング&チューニングに移行するという戦略で進めました。
この戦略は割と良い感じでした。お互いの得意な部分をメインでやりつつ、最終的には合流していくスタイルです。
失敗したなぁと感じたのは、後半の戦いにおいて、凡ミス等が重なり failが継続しているにも関わらず、ロールバックせず、追加開発を継続する判断をしてしまったことです。最終的には、予選終了直前に git checkout
で、数時間前の状態に大幅ロールバックさせて、とりあえずベンチを走らせるということになってしまいました。
ロールバック判断をより細かく、より早くして、常に成功する状態を保っておけば、もっと良い成績を残せたのではないか?という反省があります。
インフラ構成変更
インフラの構成は、以下のように変更されました。
- h2o から nginx への換装
- MariaDB 5.5 から MySQL 5.7 への換装
- 1台でDB, Webの相乗り構成から3台構成への変更 (Web専用2台、DBとinitialize用Web相乗り1台)
ミドルウェア等のバージョンアップは、多くの先人達が何年もかけてチューニングしてきた巨人の肩に乗れるので、下方互換を保つ範囲において、副作用の少ないチューニングです。
nginx への換装は慣れていないものを利用してハマるより、慣れているもので最高のパフォーマンスを出したほうが良いという判断からすぐに換装しました。 MySQL は 5.7 にすると、全般的な性能が改善する為バージョンアップを行いました。8.0まで上げるのもありだったのですが、普段使い慣れていないので、ややリスクがあると判断して避けました。 また、カーネルのバージョンアップもしてみようか?(笑)という話になりましたが、再起動出来なかったら死が待っているので、リスク高すぎと判断して避けました。 予め検証しておけば問題ないはずなので、次回挑戦するときは、カーネルのバージョンアップも是非やっていきたい気持ちが強くなりました。
1台構成から3台構成への変更は、3台あるマシンのCPUとメモリをキチンと使い切る為です。(とはいえ、実際のベンチ実行中はDBのCPUは残っちゃいますが)
リクエストは、ベンチ対象のサーバのnginx からWebの2台に振り分けられます。また、/initialize
の初期化処理のみは、DB上に立てた アプリにルーティングします。
これは、初期化スクリプトがデフォではローカル向いてるというのもありますが、それ以上にデータベースの初期化が行われる際に、細い内部ネットワークを使わずに済むので効率的という話があります。
チューニングを進めると、インスタンス間のネットワークもサチって、path毎に向き先を調整する必要や、さらなる効率化も出てくるかもしれませんが、今回は残念ながらアプリのチューニングがその域に達しませんでした。。。 (ヽ´ω`)
また、nginx と alp の組み合わせによるアクセス解析や、 tcpump と pt-query-digest によるクエリ解析、vmstat等によるパフォーマンスの継続的な監視や、「〇〇して欲しい」という無茶振りへの対応もしていただきました。
各種下回りについて
CPU2コアかつ、メモリ1GBしか無い環境だったので、プロセス数2、それぞれのスレッド数8という雑な設定ではじめました。スレッドの再生成時のオーバーヘッドをなくすため、常に8スレッドにしています。 また、プロファイラとしては Stackprof を導入しました。 NYTProf 等の高機能プロファイラと比較すると、ちょっと扱いづらい部分はありますが、オーバーヘッド少なく色々と調べてくれる良いやつです。
そして、migration用の rake タスクや再起動スクリプトを追加していました。が、このあたりはコードリーディングが不十分なうちに、下回りを整備しようとした自分のミスが散見されます。今回は /initialize
でキックされるスクリプトや環境変数の分離がキチンと出来ていたので、下回りの整備は最小限で良かったと思われます。
また、curl
と jq
を用いて、コピペするだけで簡単な動作確認が出来るようなスクリプト群も作りました。以下のようなものです。
これにより、超絶雑にであれば、ローカルでサクッと動作確認が出来るようになります。 Cookie は -b
と -c
で使えるので、覚えておくと便利です。
# 初期化処理 curl -XGET localhost:9292/initialize # ユーザー系のリクエスト id=$(curl -XPOST localhost:9292/api/users --data '{ "nickname": "hoge", "login_name": "hoge", "password": "hoge"}' | jq '.id') # 新規作成 curl -XPOST -c cookie.txt localhost:9292/api/actions/login --data '{"login_name": "hoge", "password": "hoge"}' # ログイン curl -XGET -b cookie.txt localhost:9292/api/users/$id curl -XGET -b cookie.txt localhost:9292/api/events curl -XGET -b cookie.txt localhost:9292/api/events/11 sheet_num=$(curl -XPOST -b cookie.txt localhost:9292/api/events/11/actions/reserve --data '{"sheet_rank": "S"}' | jq '.sheet_num') curl -XDELETE -b cookie.txt "localhost:9292/api/events/11/sheets/S/$sheet_num/reservation"
active_reservations の追加
これはかなり効果が大きかった案件です。今回は座席の予約を行うアプリですが、キャンセルされた座席は座席予約時に意味の無い情報になります。
そのため、有効な座席管理を active_reservations
で、予約履歴を これまで通り reservations
で管理するようにしました。また、これをやるタイミングで get_events
における n + 1 問題を解消し、 reservations
のセカンダリインデックスに対して、 event_id
のみを指定する rangeアクセス一発で終わるような構造にしました。
変更行は10行前後でしたが、これを入れることによりパフォーマンスの大幅改善ができ、その当時は3位に一気に浮上しました。思い出の瞬間として、スクショ取っておけばよかったですw
n + 1 問題で忘れ去られやすいことですが、単純に 1 にすれば良いものではなく、 MySQL の InnoDB の B+木に対しての走査パターンまでキチンと考えられると良いです。 1 にしたとしても、そこに (sheet_id) を入れてたりすると、セカンダリインデックスを n 回走査することになるので。
sheets のキャッシュ
これは、ものは出来ていましたが、最後の先祖返りで無かったことにしました。
今回、 sheets
というテーブルは 座席を表すものでしたが、このテーブルは更新されません。
参照しか受けないのと、データ量としてもそこまで多くはなかったので、アプリのプロセスにプロセスキャッシュする戦略に出ました。
readonlyなので、スレッドで分ける必要も無いので、プロセスキャッシュしています。
sheets は生成頻度が高かったので、これが上手く実装されていれば、太りやすさも解消されつつ、クエリ削減によるパフォーマンス向上もあったのではないかと思います。
get_event の引数に event を追加
これは、ものは出来ていましたが、最後の先祖返りで無かったことにしました。
今回、 get_event
が呼ばれることが多く、1リクエストで数回呼ばれることもありましたが、この内部で event
を fetch するのではなく、外部で events
を fetch して、それを与えるようにすることで改善しました。キチンと実装が動いていれば、かなり大きい削減効果がありそうです。
セールスレポートの最適化
これは、ものは出来ていましたが、最後の先祖返りで無かったことにしました。
セールスレポート生成は不安定かつ時間もかかっていたので、多少のコード改善を行いました。
- 全イベントの取得時には、MySQLにソートをさせている無駄処理があったので、Ruby側でのソートに移行しました。
- 特定イベントの取得時には、MySQLでの無駄なJOINがあったので、RubyレイヤでのアプリケーションJOINへの変更を行いつつ、インデックスを聞かせました。
この辺は、 Using temporary; Using filesort
が出ていましたが、この組み合わせが非常に邪悪で、 Using temporary
については、生成されるテーブルサイズが 大きいので、Disk IO が発生していました。
これは、 My ISAM の中間テーブルが生成されているためですね。
銀だこ
お昼ご飯は銀だこを食べました。 @naoka が買い出しに行ってくれて、3人でホクホクのたこ焼きを食べました。 とても美味しかったです。
その他やったこと
- reservation テーブルに対してのindex貼り直し
- これは、そもそもベンチをキチンと通すためにやっていました。
- 全件取得してソートをかけるレポートにおいて、Rubyレイヤーでソートをかけ、DB負荷を下げる
困ったこととやっておけばよかったこと
- GCにかなりのCPUが使われたり、スワップが発生して、Disk IOがボトルネックになっていた部分もあったので、定期的にpumaのプロセスを殺すとかをすればよかった。
- 今回はRubyのプロセスが太りすぎたが、メモリネックになった場合にオブジェクトの生成箇所や生存箇所を高速で調べて解決する手段を、予め準備しておくべきだった。
- 参照実装が、全体的に破壊的だったので、ちょっとした改善を行おうとすると容易にバグるのが辛かった。(これは参考実装が、問題として非常に上手く作られていることの証です。)
- ロック周りとかは全般的にアーキテクチャ変更すべきだった。が、そこがネックになるところまで行けなかった。
次回に向けて
今回は、色々なミス等が重なり、当日アタフタしちゃった感がありましたが、例えば、以下のようなことを改善できれば、もっとキチンと上を目指せてたのではないかと思いました。
- 基本的なやることリストは明文化し、可能であればスクリプトレベルまで落とし込んでおく
- 最初期のコードリーディングにもっと時間をかけて、無駄な落とし穴にハマったり、不必要なことをしなくて済むようにする
- 動かなければすぐに切り戻し、常に動いている状態を保ちつつ、改善を図っていく
特に、常に動く状態をキープするのは、精神衛生上も絶対にやるべきだった感があるので、次回はキチンとやっていきたいです。
また、これらは、本番サービスにおけるパフォチューをやる上では、ごくごく当たり前にやるべきことの幾つかではあるので、ISUCONにおいても気を抜かないということが大切なのだということを思い知らされました。
謝辞
今回のISUCON予選を通して、運営の皆様には大変お世話になりました。 怪しげな飛び道具を使わずとも、キチンと地道に改善すれば予選突破できる問題。安定しているポータルサイトやベンチマーク。ローカルでも簡単に動かせるようなアプリ設計等、今回のISUCONの出題は非常にレベルが高かったし、スピードアップの本質ではない所で引っかかるリスクが少なくなっていたのではないかと思います。
特に、メモリが少なく、レコード数が多いというのも、非常に良い問題だと思っています。これにより、本番サービスではつかえないような、オンメモリに全部載せる作戦等が封じられたのではないかと思います。今後もこの傾向を続けて欲しい。
オンラインでの戦いとなったので、直接顔を合わせることは出来なかったのですが、Discord や Twitter でのふれあい等、戦って頂いた他チームの方もありがとうございました。いい試合でした。
そして、一緒に戦ってくれたチームメンバーの方々には、非常に感謝しています。決勝まで一緒に行くことは出来ず申し訳ない気持ちでいっぱいですが、初めてのISUCONで、ドタバタしつつ悩んだ8時間は、とても楽しかったです。
関わった皆様、本当にありがとうございました!
ElasticsearchのIndexを本当の意味で無停止再構築する手法
※ この記事は Elastic stack (Elasticsearch) Advent Calendar 2017 の2日目の記事になります。
Elasticsearchを利用したサービスを運用している場合、既存機能の改修や仕様変更、新規機能の実装に伴い、運用中であってもindexや検索クエリを変更していくことは日々発生します。この際に、インデックスを再構築する必要性も出てきますが、インデックスを再構築し、そこに整合性のあるデータセットを準備するのには時間がかかります。その都度メンテナンスを行うのは非現実的であり、特に大規模サービスであれば準備にかかる時間自体も非現実的なものになりかねません。
このような問題への対処方法として、 Index Aliases を利用することで、透過的に無停止再構築を行うことが出来るというのは複数のブログ記事で紹介されています。これらはアプリケーションから直接indexを利用するのではなく、aliasを経由して利用することで、無停止再構築を行おうという手法です。
たしかに、shard数の変更であったり、 Analyzer や Tokenizer レベルの修正であれば、 Index Aliasを利用した無停止再構築は利便性の高い手法です。Index Alias が持つ透過性により、アプリケーションロジックを変更せずに、インデックスを再構築することが出来ます。
しかしながら、新規fieldの追加や既存fieldの変更/破棄を行い、検索クエリも変更するといったレベルでの開発を行う場合、Index Aliases を利用した無停止再構築は最善の手法ではなくなります。この記事においては、エンドユーザーにスムーズな価値提供を行うために、高い開発効率を維持しつつも、インデックスを無停止で再構築する手法について提案したいと思います。
TL;DR
- 設計
- RDBMSなどの他レイヤーでマスターとしてのデータ永続化層を実現し、Elasticsearchはデータを検索するための永続化層として責務を分担させる
- アプリケーションでは、ModelやEntityに対して、Elasticsearchへのデータ保存・検索を担うRepositoryを定義する
- Modelに対して m : n で Repository が定義でき、Repositoryは保存用・検索用で別のものが利用できるようにしておく (最低限の開発であれば 1 : n で良い)
- Repositoryとindexは 1 : 1 の関係で利用できるようにしておく
- インデックス再構築オペレーション
- Elasticsearch上に新しいindexを構築し、新/旧2つのindexが利用可能な状態にする
- 保存用のRepositoryを、新/旧2つのindexに対応する2つのRepositoryにする。この時点以降に操作されたデータは新indexにも保存され始める
- データが充足されるまでは、検索用のRepositoryは旧indexのものを利用しつづける
- バッチなどからBulk API を利用して、未操作のデータの同期を行う
- この際、Operation Type を利用した楽観ロックを行うことにより、サービス経由で更新されるデータとの整合性を保ちつつ、未処理のデータについてのみ同期を行う
- データ同期完了後、検索ロジックを変えたいタイミングで、検索用Repositoryを新index用のものに変更する
- 任意のタイミングで旧indexへの保存を停止し、旧indexを破棄する
満たすべき無停止再構築の要件について
本記事で無停止再構築と言った場合に何を示しているかについて、以下に記述します。
- 旧インデックスを利用するアプリケーションは、完全にアプリケーションが切り替わるまで、コードの修正なく正常に動作すること
- 旧インデックスはインデックス再構築中も常に最新のデータで更新され続けること
- 旧から新へのアプリケーションの切り替えはプロセス単位でアトミックに行われ、旧インデックスを参照している旧プロセスも正常に動作すること
- 新インデックスはインデックス再構築中に更新されたデータも含めて最新の状態で投入されること
- 新・旧それぞれのインデックス上に存在するデータは必要十分な量に限定され、移行に伴うオーバーヘッドやリスクが最小になること
- 上記を満たした上で、開発・運用をより効率化すること
具体的な例について
抽象的な話ばかりでは分かりづらいということもあるため、以下のシチュエーションを簡単な例として取り上げます。
- ユーザー
user
をアカウント名account_name
で検索するためにElasticsearchを利用していた account_name
の検索だけでは、非アクティブなユーザーもヒットしてしまうため、最終ログイン日last_login
が1ヶ月以内のユーザーに絞る機能が必要になった
例に限って言えば、一般的な無停止再構築の手法ではなく、別の解決策が適しているという点もありますが、この記事では一般的な解決策を紹介致します。 また、実装サンプルとして Ruby on Rails + elasticsearch-rails の組み合わせを取り上げたいと思います。
どうして Index Aliases が最善ではない場合があるのか?
Index Aliases が最善の選択肢ではなくなるのは、その透過性が無意味となる場合があるときです。 アプリケーションから参照しているエイリアス名は同一だが、Elasticsearchの内部的にはAliasが指しているIndexは異なるという点がIndex Aliasesのもともとの利点です。 mappingの外部構造にほとんど変化がなければ、aliasを通して保存されたドキュメントは登録されている全てのindexに保存され、検索クエリは登録されている全てのindexから行われます。
逆に言えば、新・旧のindexでfieldの型が大きく異なる場合や、新・旧のindexで存在しないfieldがある場合はこれらの利点は失われます。aliasを通したもう一方へのindexへの保存は、余分なデータの生成、誤ったmappingの自動作成、エラーなどにつながります。また、index と 検索クエリが一致しなければ、想定しない結果(つまりバグ)につながります。
ユーザー検索の例の場合、新・旧のindex中のmappings中のpropertiesは以下のようになります。
旧 index
{ "user" : { "properties" : { "account_name" : { "type" : "keyword" } } } }
新 index
{ "user" : { "properties" : { "account_name" : { "type" : "keyword" }, "last_login" : { "type" : "date" } } } }
そして新たに検索クエリの filter に付け加える節は以下になります。
{ "range": { "last_login": { "gt": "2017/11/02" } } }
上記を見ていただくとわかるように、旧indexには last_login
は存在しません。
その為、旧indexに last_login
を保存しようとすれば、 dynamic の設定に依存しますが、性能上のオーバーヘッドやデータ不整合、エラーにつながります。
また、新 index に投げるはずのクエリが 旧index に投げられた場合は last_login
がそもそも存在しないため、検索結果が0件になってしまいます。
一般的に、Aliasの切り替えを同時並列で実行されているアプリケーションでアトミックにハンドリングすることは難しく、Index Aliases を使った無停止再構築は破綻してしまいます。
設計
そこで、アプリケーションレイヤーで対象のindexを明示的に指定して扱う手法を本記事では提案します。
責務分担について
Elasticsearch を用いた検索を行う場合、システムとしてのデータ永続化は RDBMS などのElasticsearch外で行い、Elasticsearchは検索のための永続化層として責務を持たせます。これはElasticsearchを検索用のシステムとして最適化し、破壊的な変更や本来のデータ構造を示していないmappingであっても容易に作成可能にするためです。
Repositoryの設計について
責務分担を行った上で、以下のようなRepositoryの導入を行います。
Repositoryはアプリケーションで利用するModelもしくはEntityをElasticsearchの特定のindexに保存/検索するという責務を持ちます。このようにすることによって、旧indexと新indexに関わるロジックを疎結合に分け、それぞれを高い凝集度で実装することが可能になります。
また、Model と Repository を m : n の関係にすることによって、一つのModel をどちらの index から取得するか、どちらに保存するかという選択がアプリケーションレイヤーで簡単に操作できるようになります。
ユーザー検索の例に対して、Ruby on Rails と Elasticsearch::Persistence の Repository Pattern を利用したコード例を示します。 Elasticsearch::Model もRailsとの相性は良く充実した機能を持っていますが、無停止再構築を行う上では Elasticsearch::Persistence の方がおすすめです。
簡単の為に 1 : n の関係にしていますが、Repositoryクラスにindexに関する操作が集約されていることがわかります。
# Model class class User < ActiveRecord::Base def self.repositories_to_save @repositories_to_save ||= [ ::ElasticRepository::UserV1.new, # ::ElasticRepository::UserV2.new, ] end after_save do self.class.repositories_to_save.each do |repository| repository.save(self) end end end # Repository Class module ElasticRepository # This unofficial inheritance usage increases flexibility especially when instance objects have their own customized settings class UserV1 < Elasticsearch::Persistence::Repository::Class def initialize client Elasticsearch::Client.new url: 'localhost:9200', log: true index 'user_v1' type 'user' settings number_of_shards: 1 do mapping do indexes :account_name, type: :keyword end end klass User end def deserialize(document) hash = document['_source'] klass.new(account_name: hash['account_name']).tap(&:readonly!) end def serialize(document) { id: document.id, account_name: document.account_name, } end def search_by_account_name(account_name) search( query: { bool: { filter: [ { term: { account_name: account_name } } ] } } ) end end end # Save (Save Record to DB and Index Documents to Elasticsearch) user = User.create!(account_name: 'foo') # When searching with old logic and old index ElasticRepository::UserV1.new.search_by_account_name('foo') # When searching with new logic and new index ElasticRepository::UserV2.new.search_by_account_name('foo', last_login: 1.month.ago)
このようにすることで、新/旧のロジックをアプリケーションレイヤーで適切に扱うことができるようになります。
インデックス再構築のオペレーション
上述したRepositoryを設計すれば、インデックスの再構築作業は容易かつアトミックに行えます。
新インデックスの作成
まずは新しいindexを作成し書き込みを可能な状態にします。Elasticsearchでのindex作成は非常に軽いオペレーションであるため、定義済みのものについては、deploy時などに自動的に反映されるようにする形が楽でしょう。
ユーザー検索の例ではCapistranoなどでのdeploy時に以下のコードを常に実行するようにしているだけでこのステップは完了です。
User.repositories_to_save.each(&:create_index!) # This internally checks if the index already exists or not.
新・旧両インデックスへの書き込み開始
つぎに、Modelクラスの保存時に利用されるRepositoryを新・旧の両方にします。
このアプリケーションコードがdeployされた後は、新indexについても最新のデータが保存されるようになります。
ユーザー検索の例では、 User.repositories_to_save
の中にあるコメントアウトを外すだけで完了します。
全データの同期
上記アプリケーションコードがdeployされた後に操作されたデータに関しては、基本的には最新の情報が新indexにも保存されるようになります。 一方、アプリケーションでアクセスされていない休眠レコードについては、バッチ処理などでの同期が必要になります。
この際には Elasticsearch の Bulk API を利用して複数レコードを同時に保存することで高い効率を得ることが可能になります。
しかし、ここで一つの問題を解決する必要があります。それは通常のアプリケーション側での更新とバッチによる更新が同時に行われた際に、最新のデータを整合性のある状態で保存しなければならないということです。幸いなことにElasticsearchには Operation Type の設定が可能です。バッチからの更新には
op_type=create
を与えることによって、アプリケーション側の操作で新しくなったデータを古いデータで誤って上書きするということが避けられます。実際の運用においては、全てのレコードに対してこの処理を行っていくことで簡単に準備することが可能です。
また、Bulkによる更新速度が早すぎる場合にはrejectされる場合もあるので、適宜調整を行ってください。
検索用インデックスの切り替え
全データ同期用のバッチ処理が完了すれば、新インデックスにも完全なデータデータセットが揃っているはずです。 アプリケーション中での検索部分を新Repositoryを利用したものに置き換えれば、それがdeployされた瞬間に、新しいindex, 新しいlogicの組み合わせで検索が行われるようになります。
Index Aliases と異なり、旧アプリケーションは旧indexと旧logicの組み合わせで動き続けているので、deploy時に問題が発生することもありません。
ユーザー検索の例においては、上述したコードの最後の部分にある新ロジック用のコードに置換していただくだけでこの作業は完了です。
旧インデックスへの保存停止および削除
完全に旧アプリケーションが停止した後は旧インデックスへの保存停止、および、インデックスの削除を行います。
自動的に削除されるような仕組みにしておくのが運用コストを削減するために良い手法だと思われます。
ユーザー検索の例においては、User.repositories_to_save
に該当しないものをdeploy時に削除してしまう程度のコードでも問題なく、より安全を期すなら、削除についてはしばらく放置しても良いでしょう。QAを行う前提であれば旧インデックスへの保存を停止するタイミングで、deploy時に削除してしまう方がバグの再現性が高いのでおすすめです。
以上の手順によって、ElasticsearchのIndexを本当の意味で無停止再構築することができました。
ユーザー検索の例で言えば、新indexへの保存を開始するタイミングと、新indexへ切り替えを行うタイミングでの都合2回のdeployが必要になりますが、ロジックとindexはそれぞれのRepository内部に閉じ込められ、開発効率をより高めていくことが可能だと思います。
まとめ
本記事では、Elasticsearchのインデックスを無停止再構築するための手法を紹介しました。 アプリケーションレイヤーやデプロイのフローまで絡めた手法にはなりますが、開発・運用のどちらの面からも適している手法だと考えています。
Elasticsearchはまだまだ勉強中なので、より適切な手法などがあれば是非教えてください。
ということで、 Elastic stack (Elasticsearch) Advent Calendar 2017 の2日目でした。
次は st_1tさん の 「Kibanaのハンズオン的なもの」になります!お楽しみに!
参考
MySQLのUsing Temporaryについて
Using Temporary とは
MySQLにおいて、EXPLAIN文を用いてクエリの実行計画を見るときに、この文字列が出る可能性があります。例えば、以下のような場合です。
mysql> CREATE TABLE `books` ( -> `id` int(10) unsigned NOT NULL AUTO_INCREMENT, -> `title` varchar(255) NOT NULL, -> `author` varchar(255) NOT NULL, -> `description` varchar(255) NOT NULL, -> `extra` TEXT, -> `released_at` datetime NOT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.01 sec) > EXPLAIN SELECT author, COUNT(title) FROM books GROUP BY author; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ | 1 | SIMPLE | books | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using temporary; Using filesort | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ 1 row in set, 1 warning (0.00 sec)
MySQL 5.7 Reference Manual を見ると以下のように書かれています。
MySQL :: MySQL 5.7 Reference Manual :: 9.8.2 EXPLAIN Output Format
To resolve the query, MySQL needs to create a temporary table to hold the result. This typically happens if the query contains GROUP BY and ORDER BY clauses that list columns differently.
Using TemporaryはMySQLのクエリ実行計画において、結果を保持するために一時テーブルを作成する必要があることを示しています。
Webアプリケーションなど、それなりに高いパフォーマンスが求められる場合、一般的にはUsing Temporaryが出た時点でクエリの改善が求められます。クエリそのものを、効率的なクエリに置き換える作業や、データを利用しているアプリケーション側におけるロジックを含めた改善が必要です。
この記事では、クエリ最適化の話は一旦おいておいて、一時テーブルの構造と仕組みについて簡単にまとめたいと思います。
テーブルの種類とデータの形式について
一時テーブルとして実際に作られるテーブルは2種類であり、一時テーブルとして利用される際に以下の特徴を持っています。
Memory Engine
- オンメモリで動作
- VARCHAR, VARBINARY等のカラムは最大長に合わせて、CHAR, BINARYなどの固定長として保存される。
- テーブルサイズが肥大しすぎた場合には、自動的にMyISAMに変換される。
MyISAM Engine
- ディスク上で動作
- VARCHAR, VARBINARY等のカラムは可変長で保存される。(MySQL 5.6.5以降のみ)
当然、Disk I/O の発生する MyISAM Engineの方がパフォーマンスが悪いのですが、それ以上に悪いのはMemory Engineとして確保されつつ、データサイズの閾値を超えてしまった為に、MyISAM Engineへと変換される場合です。では、この2つの使い分けはどのようになっているのでしょうか。
テーブルの使い分けについて
以下に、MyISAM Engineが利用される条件を示します。
クエリに起因するもの
- BLOB、または TEXT が含まれる
- 512 バイト、もしくは、512文字以上のカラムが、 GROUP BY もしくは、 DISTINCT に含まれる
- UNION、もしくは、UNION ALL が利用された際に、512バイト、もしくは、512文字以上のカラムが含まれる
- SHOW COLUMNS および DESCRIBE が利用されたとき
テーブルサイズに起因するもの
tmp_table_size
を超過したもの (デフォルト値は16MB)max_heap_table_size
を超過したもの (デフォルト値は16MB)
設定値に起因するもの
big_tables
オプションが ONであるとき
生成されるテーブルの構造を考えると、納得のいく条件から出来ています。
オンメモリで回したいのであれば、カラム長を一定以下に制限し、tmp_table_size
および max_heap_table_size
を増加させることが正攻法です。
逆に、メモリが足りないほど大規模なテーブルが生成されるのであれば big_tables
を ON
にして(これはセッション内部だけでも可能)、初めからDiskを利用したほうがパフォーマンスが僅かに向上します。
一時テーブルの生成状況を計測する手段
一時テーブルが生成された際には、サーバーステータス変数の Created_tmp_tables
および Created_tmp_disk_tables
から知ることができます。前者は生成された一時テーブルの総数を、後者はそのうちDisk上に生成されたものの数を示しています。
mysql> SHOW GLOBAL STATUS WHERE Variable_name IN ('Created_tmp_tables', 'Created_tmp_disk_tables'); +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 0 | | Created_tmp_tables | 27 | +-------------------------+-------+ 2 rows in set (0.00 sec)
この値が妙に増えるようであれば、何らかの原因究明や対策を打っておいたほうがいいかもしれません。
まとめと今後の課題
ということで、本記事では一時テーブルの中身についてザックリとまとめてみました。 一時テーブルといっても、特定のEngineが使われているだけで、大したことはありません。とはいえ、メモリ上にテーブルが作成されたり、Disk I/Oが発生するなど、パフォーマンスの観点からは、あまり望ましくないのは確かです。
パフォーマンスに配慮したクエリを作っている限りは、なかなかUsing Temporaryにお目にかかることは無いのですが、Webアプリケーションで利用しているORMなどは、一時テーブルを作成するようなクエリを生成していることがあります。
例えば、 Ruby on Rails の ActiveRecord がテーブルのカラムを取得するために利用している SHOW FULL FIELDS FROM @table_name
は典型的な例で、MyISAMの一時テーブルを生成しています。今回は一時テーブルの概要をまとめましたが、次の記事では、ORMが生成するクエリについての考察を書きたいと思います。