DB ディープダイブ part 6 : クエリプラン、中間結果、一時DBとストレージパフォーマンス

本ブログエントリーはPernixData社のテクノロジーエバンジェリストであるFrank Denneman氏のブログの翻訳版です。 Frank氏について、詳しくはこちらもご参照ください。

本記事の原文はDB Deepdive part 6: Query Plans, Intermediate Results, tempdb and Storage Performanceで閲覧可能です。

ネットワールドのPernixDataに関する情報はこちら。本ブログのPernixDataの記事のまとめはこちら。

データベースのワークロード特性シリーズのパート6へようこそ。データベースは仮想化データセンタ内では最も多くのI/Oを消費するものの一つであると考えられています。データベースの操作とデータベースの設計はそれ自身が研究に値するものですが、データベースの設計の世界を少しかいま見てみるだけでも非常に面白いものだと考えています。データベースのエキスパートで、同僚でもある PernixData社の製品担当VPの Bala Narasimhan 氏のもとへ赴き、データベースの設計とそのI/Oの特徴について教えてもらいました。

このシリーズの過去の回 :

Part 1 - データベースの構造

Part 2 - データパイプライン

Part 3 - データベースチューニングのための補助機構

Part 4 - NoSQLプラットフォーム

Part 5 - クエリ実行プラン

以前の記事の中で、データベースのクエリオプティマイザを取り上げ、それがどのように動作しているかを紹介したしました。その際には TPC-H のようなクエリとSQLサーバのデータベースを用いて、クエリオプティマイザを利用する場合のクエリのストレージの要件について解説を行っています。

今日の記事ではストレージのパフォーマンスに影響のあるクエリ実行について切り込んでいきます。そのひとつは中間結果処理です。今日の内容ではPostgreSQLデータベース内のクエリオプティマイザを使用します。その理由はこれらの問題が特定のデータベース固有のものではないということをお見せしたいからです。言い換えると、ご紹介するものはデータベースが動作しているすべての環境で発生しているストレージのパフォーマンスの問題だということです。この話題を通して、これらのストレージパフォーマンスの問題はインフラストラクチャレベルで解決するのがもっとも良く、プロプライエタリのインフラにおける裏ワザやデータベース内部を書き直すべきではないということをお伝えしたいと考えています。

PostgreSQLのオプティマイザのツアーのあとは、もう一度SQLサーバへと戻り、SQLサーバでの処理の中間結果における恒常的な問題について取り上げます。これは一時DBと呼ばれます。ここではこれまでにどのような一時DBのパフォーマンスの問題を解消しようとする試みがあったかと、更に良い方法をご紹介します。

中間結果とは?

データベースはソート、アグリゲーション、結合など様々な操作を行います。可能な限り、データベースはこれらの操作をメモリ(RAM)内で実行しようとします。しかし、多くの場合、データセットが大きすぎて、利用できるメモリ(RAM)の総量が限られているため、メモリ(RAM)内でこれらの操作が完了しないケースが有ります。これが起こると、これらの操作はディスクへとこぼれ落ちていくしかありません。ソートや結合、アグリゲーションなどの操作の実行中に一度ディスクに書き込まれ、その後にディスクから再度読み込まれるデータセットを中間結果と呼びます。

今回の記事では例としてソートを取り上げ、ストレージパフォーマンスが中間結果処理を管理する場合の重要な要件になるということをお伝えします。

ユースケース

今回の例では BANK という ACCTNUM と BALANCE という2つの列からなるテーブルを利用します。このテーブルは銀行の口座番号とそれぞれの口座の残高を記録するものです。テーブルの作成は以下のようになります :

Create Table BANK (AcctNum int, Balance int);

これから解析を行うクエリは残高の値のあるアカウントの数を計算し、この情報をもとに残高を照準で表示しようとするものです。このクエリはSQLでは以下のようになります:

Select count(AcctNum), Balance from BANK GROUP BY Balance ORDER BY Balance;

このクエリ内の ORDER BY という節がソート操作を引き起こします。厳密に言うと残高の列のソートが実行されます。PostgreSQLでこのクエリを実行してみました。

BANK テーブルには2億3千万の行が格納されています。残高の列の密度が非常に高くなるようにしています。以下はこのクエリについてのPostgreSQLのオプティマイザのスクリーンショットです。このクエリがディスクを利用してマージソートを行い、ソートの最中に4GBのディスク領域を利用したことがわかります。ほとんどのクエリの実行時間がソート操作に費やされていることがわかります。

ディスクベースのソートと他のデータベース操作が中間結果を生成しており、特徴としては大きな中間結果のWriteに続いて、この結果を更に処理するためのReadが行われています。つまり、IOPSが非常に重要な要件です。

ソート操作が特に苦痛を伴うのはこれが具体化のポイントだからです。これソートが完了するまでクエリは先を実行することができないということです。クエリ全体が、根本的にソートと中間結果の処理でボトルネックになってしまっているということです。これ以上のストレージのパフォーマンスがクエリの処理時間の足を引っ張っているという証明はないでしょう。

一時DBとは?

一時DBはSQLサーバ内部で中間結果を処理を含む様々な理由で利用するシステムデータベースです。つまり、上のクエリをSQLサーバに対して実行した場合、ソートの操作はその処理の一部を中間結果として一時DBはへと溢れさせるということです。

この一時DBのパフォーマンスが SQL サーバの環境において重要視されるということはおどろくべきことではありません。一時DBのについてはこちら(リンク先は英文)で詳しく読むことが可能です。

一時DBを含め、中間結果のストレージパフォーマンスを管理するにはどうしたらよいでしょうか?

この2年ほど、私は多くのSQL サーバのユーザーに対して一時DBのパフォーマンスについて話をしてきました。これは SQL サーバのパフォーマンスに関してのみの話です。これまでのお客様はストレージに高速なメディアを搭載することだけで一時DBのパフォーマンスを解消してきています。例えばフラッシュをハイブリッドストレージやオールフラッシュストレージ(AFA - all flash array)に搭載するという形です。考え方としては｢早いストレージ｣はとても効果なので、標準化はできない、だから一時DBだけをそこに移そうというものです。結局のところ｢早いストレージ｣を一時DBの問題のためだけのバンドエイドとして探しているわけです。

AFAやハイブリッドストレージは一時DBのパフォーマンスをブーストできますので表面上はこれは理にかなっています。しかしまだいくつかの問題が残されています。少し挙げてみましょう:

一時DBをSQLサーバの他のすべてのデータベースとは別に管理しなくてはならなくなりました。
一時DBへはこのストレージを提供できていますが、ストレージのデータサービスを提供することができていません。パフォーマンスのバンドエイドのためだけに使っています。これは紙に欠かれている以上に無駄な投資をしてしまっているということを意味します。
一時DBを利用しないクエリについては、このストレージは全く意味がありません。
データベース内のパフォーマンス問題は一時DBだけに限定されません。例えば、テーブルのフルスキャンなどを行うことがあるでしょうが、これはこのストレージからはなんの助けも得られません。
メディアの革新を活用することができません。例えば、RAMやPCMや今後一時DBのために世の中に登場する他のものは活用できないのです。

PernixData FVPはどのように役立つか?

私はPernixData FVPは中間結果全般、そして一時DBに関連するストレージパフォーマンス問題については特に理想的なソリューションだと考えています。一時DBを含む中間結果の処理は一時的なデータのローカリティ(訳注 : データがCPUの近くにあること)を示し、つまり FVPにとっては理想的な高速化の候補となります。以下がなぜFVPがこのシナリオで有効なのかという理由です :

PernixData FVPはあらゆるサーバーサイドのメディアを利用することができます。フラッシュ、メモリ(RAM)を利用して一時DBと中間結果の処理を高速化できます。
このために構成を分ける必要はありません。その代わりに仮想マシンレベルで運用を行い、データベースVMを高速化し、その結果として、中間結果の処理を含むすべてのI/O操作を向上させることができます。
一時DBの要件が変っていったとしても～例えば、もっとその管理に容量が必要になったりした場合でも～それは単にフラッシュのカードをもっと大きなものに変えるだけでFVPは対応することができます。その場合もダウンタイムはなく、アプリケーションへの影響はありません。これによって、フラッシュの価格/性能のカーブにそのまま乗っかることができます。

記事担当者: マーケティング本部三好哲生 (@pernixdata_netw)