Transactd 2.0 その3 データベーススケーリング
ほとんどのNoSQLは、容易なスケーリングと、アクセス頻度の高い処理の高速化を目的として使われます。今回はTransactd 2.0について、スケーリングがどのように実現されるか書きたいと思います。
1. なぜNoSQLか?(SQLの欠点)
2. SQLでのボトルネックは何か?
サーバーボトルネック
データベースへのアクセスが増え、負荷が増大していった場合に、具体的に何がボトルネックになるのでしょうか?仮にすべてのデータがキャッシュに載っているとしたら、ボトルネックになるのは、ネットワークでしょうか?それともCPUでしょうか?
まず、一般的にDBサーバーが送受信するパケットは比較的小さなものが多く、巨大な結果受け取るような処理をしない限り、現在の標準的な1GBのネットワーク帯域を使い果たすことはほとんどありません。これは、NICとドライバーによって処理できるPPS(packet/sec)の上限の方がボトルネックになるからです。PPSは理論上、帯域をパケットサイズで割った数ですが、実際には、特に小さなパケットで理論値よりだいぶ少なくなります。(正確なデータではありませんが、1GBpsのNICでも256Byte以下のパケットでは頑張っても往復で30万pps位な感じがあります。理論上は256Byteで96万ppsです。 (1Gbps x 2 /(256 x 8))
)
実際に当社で負荷テストを行った結果ですが、複数台のクライアントから、スレッドを増やしながら連続したリクエストを送信しつづけると、リクエスト数とともにサーバーのCPU使用率が増え、やがてほぼ100%に達します。このテストはSQLの中でもCPU負荷の小さな1レコードのreadです。
下図はその時の結果のグラフです。
横軸はリクエストを送り続けるクライアント数で、縦軸は1秒間に処理できたリクエスト数です。
ともに、CPU使用率がほぼ100%に達し、リクエストを送るクライアントを増やしても、1秒あたりの処理数がほとんどが増加しなくなりました。
TransactdとMySQLの比較では絶対的な処理能力の差が1.4倍程になっています。このテストはSQLとTransactdでのCPU負荷の差が最も小さい単純なreadでの比較です。JoinやOrderByといった処理が加わるとさらに開きは大きくなっていきます。
少し横道にそれますが、グラフでリクエストに応じて処理数が直線的に増えている間は、処理の遅延はありません。徐々に遅延が始まり、傾きが急に水平に近くなったところから極端に遅くなり始めます。(リクエスト増に対して一定数の応答しか返せないので順番待ちになるから。)
クライアントボトルネック
例えば、ネットワーク越しの1台のクライアントから、1レコードのreadを連続して行って、MySQLサーバーの処理限界に到達させることができるでしょうか?それともクライアント側の何かがボトルネックとなって頭打ちになるのでしょうか? (クライアントとMySQLサーバーは1本のネットワークケーブルでつながれています。)
当社の実験では、クライアント側で非常に多くのスレッドを立ててアクセスしましたが、クライアントもサーバーもCPUを使い果たすことはないにも関わらず、処理数はある程度で止まってしまいました。クライアントではCPU全体を使い果たしていませんが、最初のコアのみ100%近くになっています。サーバー側ではRSS(Receive Side Scaling)によって受信とその応答送信が複数のコアに分散されますが、接続を開始する側では分散されず、送受信が1コアに集中してボトルネックになっているようです。
しかし、実際の運用では連続してDBサーバーにアクセスし続けることはなく、その他の処理も行いますので、これが問題になることはあまりないように思います。
Transactdで垂直スケーリング
垂直スケーリングは、1台のサーバーでの処理能力を高める「スケールアップ」です。
Transactdでのサーバー処理は、SQLのようなCPUを必要とする構文解析はありません。また、データアクセス以外のJoinやOrderByといった処理はクライアント側で行われるため、CPU負荷がクライアントに移動します。これらによって、サーバーのCPU負荷を大幅に削減できます。
SQLからTransactdに置き換えることで、確実により多くの処理を行えるようになります。
Transactdで水平スケーリング
水平スケーリングは、データを別サーバーへ移動させる「スケールアウト」です。
スケールアウトは、CPU負荷の削減とデータサイズの削減によって、よりキャッシュ溢れを防止できます。移動させるデータの分割方法は色々あります。ここでは、一番単純なテーブル単位のスケールアウトとidによる水平分割の方法を説明します。
TransactdのJoinはクライアント側で行うため、テーブルのロケーションはどこでも同じようにできます。これを容易にするためIDatabaseManager
インターフェースが導入されています。
テーブルの移動とコード変更
IDatabaseManager
は内部に複数のデータベースを保持し、use
で事前にデータベースのURIを指定します。使い終わったら unUse
を呼び出して返却します。
また同時に、IDatabaseManager
インターフェース自体がDatabase
オブジェクトと同様のインタフェースを持つことで、既存のAPIに対して1つのデータベースであるかのように振る舞います。JoinはActiveTable
を使って行いますが、その使い方を示します。
$cp1 = new bzs\connectParams('tdap://localhost/querytest?dbfile=test.bdf'); $db->c_use($cp1); $at1 = new bzs\activeTable($db, 'user'); ... $db->unUse();
上記のtdap://localhost/querytest?dbfile=test.bdf
がサーバーを指定しているURIです。ここを別のものに書き換えるだけでテーブルを移動しても全く同じように動作します。(PHPではuse
は予約語のためc_use
メソッドになっています。)
分散クエリーを予定しているのであれば、下記のようにURIを返す関数を1つ作れば集中管理できます。
function getDatabaseUriParam($tableName) { $dbhost = '192.168.0.15'; if ($tableName === 'user') $dbhost = '192.168.0.16'; return new bzs\connectParams('tdap://' + $dbhost + '/querytest?dbfile=test.bdf'); } ... $tableName = 'user'; $db->c_use(getDatabaseUriParam($tableName));//<-- 関数によりURIを取得 $at1 = new bzs\activeTable($db, $tableName); ... $db->unUse();
idによるテーブルの水平分割
テーブルの水平分割も基本はテーブルの移動と同様ですが、どのようなルールで分割するか基準を決めURIを返す関数にその情報も与えます。例えばidの範囲が1~100万のデータがサーバーA、それ以上がBにあるとすると、先ほどのgetDatabaseUriParam
関数を少し変更し、呼び出しにidの値を加え以下のようにできます。
function getDatabaseUriParam($tableName, $id) { $dbhost = '192.168.0.15'; if ($tableName === 'user') { if ($id <= 1000000) $dbhost = '192.168.0.16'; else $dbhost = '192.168.0.17'; } return 'tdap://' + $dbhost + '/querytest?dbfile=test.bdf'; } ... $tableName = 'user'; $db->c_use(getDatabaseUriParam($tableName, $id));//<-- 関数によりURIを取得 $at1 = new bzs\activeTable($db, $tableName); ... $db->unUse();
これはとても簡単な例です。Joinを伴った場合は少し複雑になりますので、また別の機会にしたいと思います。
分散トランザクション
前述の use
は同時に2つ以上を指定することができます。この状態でトランザクションを開始すると、利用中のすべてのデータベースに対してトランザクション開始の呼び出しが行われます。2つ以上のデータベースにまたがる更新処理でXAトランザクションのような処理が可能です。
(現在のバージョンでは完全なXAではありません。コミット処理で、先頭以外のデータベースで失敗した場合、最初にコミットできたトランザクションはロールバックされません。)
コード上では、データベースが1つでも2つでも全く変わりありません。
$db->beginTrn();//<-- 使用中のすべてのデータベースで開始 ... $db->endTrn();//<-- 使用中のすべてのトランザクションをコミット $db->unUse();//<-- すべてのデータベースを解放
第2回 MariaDB/MySQL コミュニティ イベント in Tokyo に行ってきました
2014/02/18に 第2回 MariaDB/MySQL コミュニティ イベント in Tokyo に行ってきました。
懇親会ではMontyさんと写真を撮ったり、色々なプラグインの開発者の方とお会いしたりでとても有意義な会でした。
MariaDBカンファレンスの感想
Montyさんがお見えになっていたこともあり、主題はMariaDBの紹介でした。簡単に言うと「MySQL5.6は出来が悪いよ。」「MariaDB10.0は品質、機能ともにMySQL5.6よりいいよ!」という感じでした。具体的には、
- MySQL5.6のGTIDは設計がダメなので使えない。MariaDB10では設計を変更し完全に書き直している。
- MariaDBは10.0より前からオプティマイザを改良していて、MySQLより5~ 20%位は速い、場合によっては10倍といったこともある。
- バグが少なく、コミュニティからのフィードバックを積極的に受け入れサポート熱心。
一方の主張だけなので話半分くらいに聞いたとしても、MariaDBの方がいいのかな?といった印象を受けました。あと、多くの大口ユーザーやLinuxディストリビューションがMariaDBに移行したり、移行の予定を宣言している事例があげられました。ただ、その多くは実際の移行はまだこれからという感じでした。
MariaDB FEEDBACK PLUGIN
今まで知らなかったのですが、MariaDBの動作環境や構成をMariaDB Foundationに送信するための「MariaDB FEEDBACK PLUGIN」というプラグインがあり、集まった統計情報はWebで誰でも見られるようになっているそうです。
すべてのMariaDBインスタンスでこのプラグインが有効になっているわけではないですが、興味深い情報が見られます。
HandlerSocketの樋口さんとお話ししてきました!
いろいろな話をさせていただいたのですが、「SQLは得意ではない」は面白かったです。自分も樋口さんと同様、SQLは苦手で、「オプティマイザに依存すると検索パフォーマンスが読みづらい」という点で全く同じ意見でした。
HandlerSocket 2.0 と Transactd 狙いの違い
樋口さんは、HandlerSocketの検索条件の指定内容の強化とJoinを進められるとのことでした。
対してTransactdはと言いますと、1つのテーブルに対する検索条件はほぼ完全なのでこのままです。サーバー側でJoinする予定はいまのところありません(クライアント側でします)。
サーバー側でJoinをしてしまうと、テーブル単位でのシンプルなサーバー分割ができなくなってしまいます。ではなぜ樋口さんはJoinの実装を進められるかというと、NoSQLのメリットよりも、高速なSQL代替を狙っておられるようです。実際、ユニークインデックスでのJoinはSQLよりもかなり高速にできるようです。NoSQLのメリットを重視しているTransactdとは狙っているものがやや異なっていますが、Transactdでもやれば同じように出来るな、と思いました。
MroongaやSpiderの斯波さんと少しだけお話ししてきました
SpiderがHandler Interfaceに対応されたとのことで、Transactdも大丈夫かな?と思いお聞きしたとことろ、主にHandler Interfaceの下で動作しているので「たぶん大丈夫ですよ」とのこと。thdからの情報もかなり利用されるとのことで、うまくいかない場合はTransactdからthdへの情報提供を確認すれば良さそうです。今後確認していきたいと思います。
オプティマイザの神話
いろいろな方とお話しする中で、「テーブルアクセスは検索対象(範囲)のインデックスがあるか、無いかしかない。無い場合は、フルスキャンまたは範囲スキャンしかない」ということはあまり知られていないと感じました。ISAMライクなテーブルにインデックスを使ってナビゲートするプログラムを開発した経験のない方は、SQLしか使ったことが無い場合が多いでしょうから、やむを得ないのかも知れません。
中には「オプティマイザは想像を絶するミラクルなアクセスをしてくれる!」「まさかフルスキャンなんてしないだろう」と考えている方もいらっしゃるようです。
また、「少し複雑なSQL文になると、NoSQLだけでは自分(のプログラムコード)で同じ結果を得ることはできない」とも思われているようで(できないものは無いのですが)、TransactdなどNoSQLの利用の「カベ」はそのような誤解にあるとも思いました。
そしてTransactdは
今回のイベントの参加で、Transactdの今後の方向性もより明確に出来たように思っています。
アプリケーションの多くは テーブルの1レコード ≒ オブジェクトの1インスタンス という関連を持たせています。ところがテーブルをJoinすると、そのデータは「どのオブジェクトなのか」?どれでもない中途半端なものになります。このため、Transactdが提供する「テーブル単位のみのアクセス」は、O/Rマッピングとはとても相性の良いものになります。
また、Transactdを使うにあたり多くの方にとって障害になるのは、JoinやOrderByといった処理をどうするかということです。そのような点を踏まえて今後は、
といったことを中心に進めていきたいと思います。
これだけでは抽象的なので、C++の一例をあげます。
まずはパフォーマンスを読めるようなAPI。「idが10000から10100且つstatusが1のユーザー」をusersオブジェクトに読み取ります。usersはuserのコレクションです。
std::vector<user> users; query.where("id", "<", 10100).and("status", "=", 1).reject(0); userTable.index(key_id).keyValue(10000).read(users, query);
次はJoin相当の例です。userクラスにはそのユーザーが属するグループのオブジェクトを包含しています。user::grp()
でグループオブジェクトが返るとします。例ではグループオブジェクトのデータを全ては読み取らず、nameのみ読み取っています。
query.select(_T("id"), _T("name")) groupTable.index(key_id).readEach(users, &user::grp, query);
これでuser情報と所属するgroup名を読み取りました。
こんな感じのAPIを、C++だけでなくPHPなどにも用意していきたいと思います。
あ、それと、MariaDB-10.0.8用Trnasactdバイナリをダウンロードできるようにしておきました。
皆さんも是非mariaDB-10.0.8でTransactd 1.2を試してみてください。
MySQL パフォーマンスとtransactd その3
その3は、select * from tablename where fieldname in(a,b,c...)
です。
IN
句による検索ですが、前回までと同じようにMySQLの処理とtransactdでの処理を見ていきましょう。
使用するインデックス解析
MySQLはまずSQL文を解析し、fieldnameフィールドをキーセグメントの先頭に持つインデックスが存在するか調べます。存在すれば、そのインデックスを使用したオペレーションhandler::ha_index_read_map(HA_READ_KEY_EXACT)
を使い操作を組み立てます。無ければ、handler::ha_rnd_next()
かhandler::ha_index_next()
を使ったレコードスキャンをします。
どちらの方法であっても、a b cをfieldnameで示されたフィールドの型に従って昇順にソートした上で処理していきます。
MySQLでfieldnameフィールドのインデックスがある場合
handler::ha_index_read_map(HA_READ_KEY_EXACT)
でa b cのソート結果順に検索し結果を返します。インデックスを使っての検索なので、重複を許可しない1セグメントのキーの場合は、1つあたりの検索時間は一定で、条件の個数分で検索が終了します。重複を許可するもしくはマルチセグメントのキーの場合は、handler::ha_index_next_same
を使って同じ値のレコードをすべて返します。
もし、見つからない値があってもエラーにはならず、その値の結果が返らないだけです。
MySQLでfieldnameフィールドのインデックスが無い場合
インデックスがないと、目的のレコードがどこにあるか不明なので、handler::ha_rnd_next()
かhandler::ha_index_next()
を使って先頭からすべてのレコードを検索します。当然複数レコードが存在する場合もあるので、最後のレコードまで完全にスキャンします。100万レコードあれば100万行すべてをスキャンすることになります。
transactdでfieldnameフィールドのインデックスがある場合
transactdでは、table::setKeyNum(keyNumber)
でユニークなインデックスを指定し、table::setFilter("in a,b,c")
で、値を列挙します。フィールド名を指定しないのは、インデックスを指定しているため、そのインデックスのフィールド値を列挙することが前提となるからです。もし、setKeyNum()
でユニークなキーを指定しなかった場合は、setFilter()
でstat()
がSTATUS_FILTERSTRING_ERROR
を返します。すなわち、in句ではユニークなキーでしか検索できません。ですので、意図せず全レコードをスキャンしてしまうことはありません。
インデックスが無い場合でのinに相当する処理は"fieldname = a or fieldname = b or fieldname = c"
のフィルターを使えば実現できます。
また、複数セグメントでユニークなキーの場合は、セグメントの数だけ値を並べます。たとえば、2つのセグメントでキーを構成していて、3つのレコードを得たい場合は、"in a1,a2,b1,b2,c1,c2"
のように各レコード2つづつ値を指定します。
検索の開始はtable::find()
を呼び出します。サーバー側の処理は、クライアントから送られてきたキー番号をセットしhandler::ha_index_read_map(HA_READ_KEY_EXACT)
をキー値セットの数だけ繰り返し呼び出して結果を返します。
クライアントでは順次読み出しますが、次のレコードはfindNext()
で読み取れます。もし、サーバー側で2番目の値が見つからなかった場合は、findNext()
で2番目に来るとstat()
がSTATUS_NOT_FOUND_TI
を返し、見つからなかったことを示します。もう一度findNext()
を呼び出すと3番目の検索結果を得ることができます。SQLと違うのは、どの値が見つからなかったかを容易に知ることができる点です。また、結果の順は自動でソートされることはなく、in句で指定した順に返ります。
このような振る舞いは、in句を使ってJOINに相当する処理をするのにとても便利です。取得したがソートされていたり飛ばされていたりしていた場合、JOIN元と今取得した結果を結合する際に、どちらかをソートしたり検索するといった余計な処理が必要になります。transactdはテーブル単位での処理しかありませんので、このJOINに相当する処理がやりやすいかはとても重要なことです。
transactdでfieldnameフィールドのインデックスがない場合
これはこの前のインデックスがある場合で説明したとおり、in句では検索することはできません。代わりに"fieldname = a or fieldname = b or fieldname = c"
のようなフィルターで処理します。インデックスの無いフィールドの場合、どこにそのレコードがあるのかわからないので、全レコードフルスキャンするしかありません。フルスキャンするにしてもどれかインデックスを指定します。innodbの場合、プライマリーキーはレコードのデータも含んでいて他のキーより高速ですから、通常はそれを指定します。
まとめ
MySQLでin句で指定したfieldnameにインデックスがない場合は、全レコードスキャンします。将来も含めたレコード数を想定してパフォーマンスに問題がでないかどうかよく検討して使いましょう。ユニークなインデックスがあれば、高速に検索して結果が返ります。ただし、見つからない値があっても、どれが見つからなかったかは自分で確認が必要です。
transactdでは、fieldnameにユニークなインデックスがある場合しかin句は使用できません。検索結果はfindNext()
で値の列挙順にアクセスでき、見つからならなかったことも知ることができます。これによりJOINに相当する後処理がやりやすくなります。インデックスが無い場合は、"fieldname = a or fieldname = b or fieldname = c"
のフィルターを使います。この場合の、パフォーマンスはSQLと同様ですが、可能であれば検索の開始位置と、rejectCount
などで検索範囲をコントロールしてパフォーマンスを改善しましょう。
MySQL パフォーマンスとtransactd その2の2
前回はselect * from tablename where fieldname = xxx
のfieldnameをキーセグメントの先頭に持つインデックスがない場合を書きました。今回は、インデックスがある場合です。
MySQLでfieldnameフィールドのインデックスがある場合
今回の例は条件式が一つですので簡単です。MySQLはまず、レコードバッファ内のfieldnameフィールドの位置にキー値(xxx)をセットしてhandler::ha_index_read_map(HA_READ_KEY_EXACT)
を呼び出します。
レコードがなければ(エラーならば)検索終了です。レコードが存在する場合、uniqueキーでなければhandler::ha_index_next_same()
をエラーになるまで繰り返し呼び出し、順次レコードを取得します。
handlerでアクセスしたレコード数は、fieldname=xxx
の重複レコードの数だけです。インデックスがあれば、実際に存在するレコード数しかアクセスしないので、パフォーマンスは最大です。(正確には、innodbの場合このキーがprimaryかそうでないかで若干パフォーマンスは異なりますが、重複レコードが少なければ大差ありません。)
アクセスするレコード数が少ないので、仮にinnodbのキャッシュに乗っていなくても、それなりに高速です。また、勝手にキャッシュされるレコード数も最少で、キャッシュメモリを温存できます。インデックスがあると色々お得ですね。
transactdでfieldnameフィールドのインデックスがある場合
クライアントフィルターを使う場合
最初にtable::setKeyNum()
でインデックス番号を指定し、table::setFV(fieldname, xxxx)
でキー値をセットします。その後、uniqueキーならtable::seekEqual()
で、そうでなければtable::seekGreater(orEqual=true)
でレコードにアクセスします。
レコードが見つかればtable::stat()
にゼロが返ります、uniqueキーでない時は、table::seekNext()
で次のレコードに移動してtable::stat()
とフィールドの値を調べます。値が異なるかtable::stat()
がゼロ以外になるまでtable::seekNext()
を繰り返し呼び出します。
最初にインデックスを指定しseekEqual
やseekGreater
といった名前の関数を呼び出すので、コードからパフォーマンスを容易に想像できます。
サーバーフィルターを使う場合
通常はクライアントフィルターで十分ですが、重複値がたくさんあるようなら、サーバーフィルターを使って一気に取得し、通信のオーバヘッドを削減しましょう。
table::setKeyNum()
でインデックス番号を指定し、table::setFV(fieldname, xxxx)
でキー値をセットするまでは同じです。その後、table::setFiletr(fieldname=xxx, rejectCount=1, maxRecords=0)
でサーバーフィルターをセットしてからtable::find()
を呼び出します。
サーバー側では、handler::ha_index_read_map(HA_READ_KEY_OR_NEXT)
を使ってスキャンの先頭に移動します。そして、handler::ha_index_next()
で次のレコードに移動しながら、条件にマッチするレコードかを調べていきます。
今回は対象フィールドのインデックスを指定しているので、レコードはそのインデックスでソートされていることが保証されています。そのため、マッチしないレコードが見つかればすぐにループを抜けるように、rejectCount=1
と指定することがポイントです。ここでもやはり、transactdは、スキャンの開始位置と終了条件を明確にコントロールできます。
まとめ
検索対象のフィールドがインデックスの先頭セグメントにあれば、検索パフォーマンスは最大です。ただし、重複レコードを許可するキーの場合は、重複レコードがどれくらいあるか想定し、パフォーマンスを読みましょう。これはMySQLでもtransactdでも同じです。
transactdでは、重複レコードの量に応じて、クライントフィルターかサーバーフィルターを選択しましょう。
このテーマの最後に、transactdを使った検索のひな形コード(C++)を書いておきます。(他の言語、Ruby ActiveX C# PHP などでも同じ名前のメソッドがあるので、ほとんど同じです。)
次は select * from tablename where fieldname in(a,b,c...)
を取り上げたいと思います。
Transactd インデックスを使った検索(クライアントフィルター)
static const char keynum = 0; tb->clearBuffer(); tb->setKeyNum(keynum); tb->setFV("fieldname", "xxxx"); tb->seekGreater(true/*orEqual*/); while (tb->stat() == 0) { if (strcmp(tb->getFVstr("fieldname"),"xxxx")!=0) break; //ここに見つかった場合の処理を記述します。 tb->seekNext(); }
Transactd インデックスを使った検索(サーバーフィルター)
static const char keynum = 0; tb->clearBuffer(); tb->setKeyNum(keynum); tb->setFilter("fieldname = xxxx", 1/*rejectCount*/, 0/*maxRecords*/); tb->setFV("fieldname", "xxxx"); tb->find(tbale::findForword); while (tb->stat() == 0) { //ここに見つかった場合の処理を記述します。 tb->findNext(); }
MySQL パフォーマンスとtransactd その2の1
その2はselect * from tablename where fieldname = xxx
です。長くなるのでまずは2の1から。
なんとも簡単なSQL文ですが、テーブルの定義やデータの状況によって全くパフォーマンスが異なってきます。
使用するインデックス解析
MySQLはまずSQL文を解析し、fieldname
フィールドをキーセグメントの先頭に持つインデックスが存在するか調べます。存在すれば、そのインデックスを使用したオペレーションhandler::ha_index_read_map(HA_READ_KEY_EXACT)
を使い操作を組み立てます。無ければ、hanndler::ha_rnd_next()かhandler::ha_index_next()
を使ったレコードスキャンをします。
実は、このインデックスの選択と、handler::ha_index_read_map()
オペレーションが使えるかどうかが最も重要なのです。handler::ha_index_read_map()
は、インデックスを使って一発で目的のレコードを取得します。そうでなければ、目的のレコードがテーブルのどこにあるのかわからないので、フルスキャンすることになります。
もし、MySQLのオプティマイザがうまくインデックスを見つけられないとき(今回の例のような単純なものの場合はありえませんが)は、USE INDEX (index_list)
構文を使ってどのインデックスを使うのか指定します。(どのインデックスが使われているかは、EXPLAIN
コマンドのkeyで確認できます。)
transactdでインデックスを指定する
transactdでは、インデックスを使ってテーブルアクセスする際には、必ずどのインデックスを使うかをプログラマがtable::setKeyNum()
で指定します。インデックスを使わないアクセスメソッドもありますが、それを使うということは「テーブルをスキャンすることをプログラマが選択している」ということになりますので、予想外にテーブルスキャンしてしまうことはありません。
そのため、当然ですが、インデックスがうまく選択されているかの心配や確認は必要なくなります。書いた通りです。
MySQLでfieldnameフィールドのインデックスがない場合
インデックスがないフィールドから目的の値をもつレコードを探すには、全レコードスキャンします。スキャンの範囲は、先頭レコードから最後のレコードです。
インデックスが付いていないと重複値の許可の有無もないので、1つ見つかってもやめることなく全レコードアタックして調べていきます。100万行もあったら、前回のcount(*)
と同じように恐ろしい結果が待っています。レコード数が少ないうちは瞬時に結果が返りますが、多くなるとレコード数に比例して遅くなります。
SQL文でこれ以上遅い例はないでしょう。遅い理由のほとんどはこのようなフルレコードスキャンです。(MySQLのhandlerを使ったスキャンの操作はMySQL パフォーマンスとtransactd その1をご覧ください。)
Lmit句を追加すると、スキャンの範囲を少し狭くすることができるかも知れません。たとえば、Limit 1
とすれば、該当する行が1個見つかったときに検索を中止できます。しかし、それが最後のレコードだった場合は、やはり全レコードスキャンなので、範囲を狭くすることができる「かも知れない」です。
transactdでfieldnameフィールドのインデックスがない場合(クライントフィルター)
transactdでは、インデックスを使うメソッドと使わないメソッドが分かれています。使わない場合はtable::stepFirst()
で最初から始めて、table::stepNext()
で次のレコードを順次取得し、条件に合った値を持つかどうかをクライント(プログラマ)が自分で調べます。(このようにクライントで値をチェックし、フィルタリングすることを、「クライアントフィルター」と呼んでいます。)
サーバー側では、クライアントのstepFirst()
とstepNext()
の呼び出しごとにhandler::ha_rnd_next()
が呼ばれ、先頭から順次カーソルを移動しながらレコードを返します。
インデックスがない場合、コーディングでは先頭から最後までのループを書かざるを得ません。そのため、コードからパフォーマンスを想像することが容易になります。また、重複があるかどうかを事前にプログラマが知っているなら、最初に対象レコードが見つかった時点でループをやめることもできます。さらに、検索レコード数が多くなリ過ぎたら、パフォーマンスを優先し検索を諦めるといった追加条件を加えてフルスキャンを防止することも容易です。ユーザーインタフェースでのキャンセルなどもその一例です。
このクライントフィルターはプログラムとしてはごく自然で解りやすいものです。しかしstepNext()
の度にサーバーと通信するため、通信のオーバーヘッドが発生します。このオーバーヘッドはループが多くなればなるほど無視できないものなります。そこでSQLのように通信が少なくて済む方法が次に紹介する「サーバーフィルター」です。
transactdでfieldnameフィールドのインデックスがない場合(サーバーフィルター)
transactdではもう一つ、「サーバーフィルター」という検索方法があります。SQLと同様にサーバー側でフィルタリングするので、通信回数を大幅に削減できます。
実際の手順は、①まずtable::setFilter("fieldname = xxxx", rejectCount, maxRecords)
のようにフィルターを指定して、②table::seekFirst()
で検索開始レコードに移動し、③table::find()
で検索を開始します。サーバー側では、handler::ha_index_next()
を使って1レコードずつ、レコードが検索対象か調べます。
スキャンの開始位置はプログラマがseek
オペレーションで指定できます。今回の例は全レコードですので、seekFirst()
で先頭に移動します。スキャンの終了はsetFilter
のrejectCount
とmaxRecords
で決まります。
maxRecords
はSQLのlimit
とほぼ同じです。指定した数のレコードが見つかると検索を中止します。
面白いのはrejectCount
です。マッチしなかったレコードがrejectCount
に達すると検索を中止します。たとえば1000とすると、マッチしないレコードを合計1000レコードスキャンしたところで検索を中止します。もし、全レコードを最後まで検索したいのなら、rejectCount
にゼロを指定します。
transactdのサーバーフィルターでは、プログラマが「検索の開始位置」と「1回の検索でのおおよそのスキャン数」をフィルター指定時に決めることができます。また、続きから検索を再開することもできますので、フルスキャンするにしても、定期的にユーザーのキャンセルを確認するといったことも可能です。
まとめ (select * from tablename where fieldname = xxx)
MySQLは、インデックスを使えるかどうかを最初に判断します。それによって、その後のスキャン操作が異なってきます。
インデックスがない場合は、全レコードをスキャンします。インデックスを用意したのなら、それが使われているかEXPLAIN
で確認しましょう。
インデックスが無いことを承知でそのフィールドだけのwhere文を組み立てるのなら、将来も含めたレコード数を想定して使いましょう。具体的には、数十レコード程度なら気にせずこのままでも良いでしょう。数百より多い場合は、ハードウェア(ディスク、メモリなど)や同時アクセス数とパフォーマンスの要求レベルに応じてインデックスの追加やスキャンするレコードを削減するための条件追加も検討しましょう。
transactdのインデックスを使わない検索
transactdを利用したアクセスでも、SQLと同様に全レコードアクセスするしかありません。
クライアントフィルターによるアクセスは、コード上でループを書くことになるので、コードからパフォーマンスを想像できます。また、クライアントサイドでフィルターするので、その他の条件によって自由に途中でやめることができます。
さらに、通信回数を減らしてより高速にしたいときは、サーバーフィルターを使います。サーバーフィルターによるアクセスは、通信回数を大幅に少なくできます。また、検索の開始位置と、rejectCount
とmaxRecords
によって1回の検索でのスキャンレコード数を制限することで終了位置をコントロールできます。パフォーマンスは、プログラマが組み立てられます。
MySQL SQLパフォーマンスとtransactd その1
よく、SQLが遅いといった話を耳にしますが、サーバー側がどう処理して遅いのかまで書いたものがあまり見当たらないので、Transactdの開発経験を生かし、その使い方と合わせて書いてみたいと思います。
MySQLは、プラガブルデータベースエンジンという仕組みでさまざまなデータベースエンジンを利用できるようになっています。その内部は、データベースエンジンの操作インターフェースを定義してエンジンごとに実装をするというC++のポリモーフィズムを利用したものとなっています。インターフェースはclass handler
で定義され、SQLの解析結果からhandlerインターフェースのメソッドを組み合わせて実行し結果を得ます。
これからサーバー側の内部の説明をしてゆきますが、各エンジンごとの詳細ではなく、主にhandlerインターフェースの操作レベルの話になります。SQLのパフォーマンスで最も重要なのは内部でアクセスするレコード数です。少なければ少ないほどパフォーマンスは良くなります。handlerインターフェースを使ってどのようにレコードアクセスするかに注目してください。
select count(*) from tablename
まずはselect count(*)
から。このSQL文はtablenameのテーブルのレコード数を調べますが、handlerにはrecords()
というレコード数を返してくれる、そのもののような関数があります。
「ような」としたのは、innodbは正確な値を返してくれないからです。驚いたことに「およそ」の数しか返って来ないのです。理由は、リピータブルリードや、リードコミッテッドなどトランザクション分離レベルのために正確な値がわからないからだそうです。(コミット済数を返してくれればそれでいいように思いますが)
それでMySQLはどうするかというと、全レコードスキャンします。100万レコード(行)があれば、先頭から最後までアクセスして数を返します。恐ろしい話です。レコードが少ないうちは瞬時に値が返りますが、レコードが多くなるとディスクアクセス、CPUとも最大パワーを使います。スキャンはhandler::ha_index_init()
にてprimaryキーの初期化をしてhandler::ha_index_first()
で先頭レコードに移動し、handler::ha_index_next()
がエラーを返すまでループします。そのテーブル全体(キー全体)がinnodbキャッシュにあれば、ディスクアクセスしなくて済みますが、なければディスクアクセスを伴います。その際、最適化のためにキーリードオンリーをinnodbに指示して、キーのみを読み取っています。
transactdでテーブルのレコード数を調べる
transactdでは、テーブルのレコード数を調べる関数としてtable::recordCount(bool estimate=true)
というメソッドを用意しています。この引数bool estimate
は、おおよそでよければtrue
を指定します。この結果はinnodbのhandler::records()
とほぼ同じ値です。
false
ならMySQLと同じように、全レコードスキャンします。先ほど「ほぼ同じ」と書いたのは、innodbのhandler::records()
はレコードがあるのにゼロを返してきたりするので、少し変えているからです。レコード数を取得した直後に削除や追加によって変わってしまうことがあるにせよ(すぐには変化しないことの方が多い)、有るか無いかは重要ですので、1以下の時はもう少し正確な値を得るために、実際にテーブルをスキャンしてレコードを数えて返します。そのため、estimate=true
でも数レコードをスキャンすることがあります。estimate=true
のときの精度は、innodbの実装に依存しますが、20%程度の誤差はよくあります。
まとめ (select count(*) from tablename)
MySQL(innodb)の場合、大きなテーブルで高速に現在のレコード数を調べるのは困難です。MySQL(innodb)のselect count(*) from tablename
は全レコードをスキャンします。将来も含めたレコード数を想定して使いましょう。具体的には、数十レコード程度なら気にせず使えば良いでしょう。数百より多い場合になると、ハードウェア(ディスク、メモリなど)や同時アクセス数とパフォーマンスの要求レベルに応じて使う使わないを判断しましょう。
transactdのtable::recordCount(estimate = true)
のアクセスパフォーマンスは、テーブルのレコード数に関係なくほぼ一定です。対してestimate = false
ではMySQL(innodb)の場合と同様です。transactdはestimate
引数にて、プログラマがコードでパフォーマンスと精度を選択できます。
余談ですが、transactdクライアントは Actian社の PSQL(Btrieve)というデータベースにもそのまま使えます。PSQLの場合、recordCount()
は、常にコミット済のレコード数を瞬時に返してくれます。
次のその2では、select * from tablename where fieldname = xxx
を取り上げたいと思います。
TokuDB 7.0.1のCommunity EditionをUbuntuでビルド
(以下の記事は、旧ブログの2013/04/26の記事です。)
今回は、開発系の話です。社長に代わり、Kが担当させていただきます。
タイトルの通り、Tokutek社のTokuDB 7.0.1 for MySQL 5.5.30のCommunity EditionをUbuntu 12.10(とUbuntu Server12.04)でビルドしてみました。なかなか苦戦しましたので、メモしておきます。
確認事項
- 64bit版でないとダメなようです。
- gcc-4.7以上が必要です。
- cmakeは2.8.8以上が必要と言われましたが、2.8.7でもビルドできました。問題があるのかはわからないです…。
ダウンロード
まず、Tokutek社のサイトから、ソースコードをダウンロードします。→ TokuDB Community Edition Downloads | Tokutek
ダウンロード時にログインや登録を促されますが、一番下の「Just take me to the download」をクリックすれば登録しなくてもOKです。Fractal Tree Libraryについては、pre-builtを使えばいいと思うのですが、存在に気付かず無駄な手間をかけてしまいました…(後述)。
- MySQL 5.5.30 sources (patched for TokuDB) mysql-5.5.30-tokudb-7.0.1.tar.gz
- Fractal Tree Library (source) から ft-index-master.zip と jemalloc-3.3.1.zip(Githubから)
Fractal Tree Library のビルド
(最初からpre-built版をダウンロードした人は飛ばしてください。)
ビルド方法や注意点は Tokutek/ft-index · GitHub にあります。
まず、依存関係のインストール。cmakeやmake、gcc-4.7も用意しておいてください。
aptitude install valgrind zlib1g-dev
ファイルを解凍し、jemallocのコードをftのthird_party/jemallocにコピーします。
unzip -q ft-index-master.zip
unzip -q jemalloc-3.3.1.zip
mkdir ft-index-master/third_party/jemalloc
cp -r jemalloc-3.3.1/* ft-index-master/third_party/jemalloc/
ビルドディレクトリを作ってビルド。
mkdir ft-index-master/bld
cd ft-index-master/bld
cmake .. -DBUILD_TESTING=OFF -DCMAKE_BUILD_TYPE=Debug \
-DUSE_BDB=OFF -DUSE_VALGRIND=OFF \
-DCMAKE_INSTALL_PREFIX=toku
make install
ここで、「-DCMAKE_INSTALL_PREFIX=toku」と指定しておくと、ft-index-master/bld/tokuディレクトリにインストールされます。うっかり指定を忘れると/usr/localの直下にバラバラと配置されます。(一回やってしまいました…。)
Ubuntu12.04ではcmakeが2.8.7だったのですが、ft-index-master/CMakeList.txtの1行目を修正したらできました。
cmake_minimum_required(VERSION 2.8.7 FATAL_ERROR)
しかし
これで必要なライブラリが用意できた…と思ったら、ビルド済みライブラリを見ると、libtokuportabilityはあるけど、libtokufractaltreeindexがありません。Githubにも以下のような記述がありました。
This will build libtokudb.so and libtokuportability.so and install it
結局libtokufractaltreeindexのソースは見つけられませんでした…その過程でpre-built版に気付き、そちらをダウンロードして使用しました。
tar -xzf tokufractaltreeindex-7.0.1-linux-x86_64.tar.gz
TokuDB入りMySQLのビルド
まず、依存関係のインストール。
aptitude install libaio1 bison
ファイルを解凍し、ビルドディレクトリを作ります。
tar -xzf mysql-5.5.30-tokudb-7.0.1.tar.gz
cd mysql-5.5.30-tokudb-7.0.1
mkdir bld
cd bld
cmakeの際、-DTOKUFRACTALTREE_RELEASE_DIRに解凍したディレクトリ、-DTOKUFRACTALTREE_LIBと-DTOKUPORTABILITY_LIBにそれぞれのファイル名までを指定します。(他に楽な指定方法があるのかな?)
cmake .. -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.5.30-tokudb \
-DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci \
-DTOKUFRACTALTREE_RELEASE_DIR=/path/to/tokufractaltreeindex-7.0.1-linux-x86_64 \
-DTOKUFRACTALTREE_LIB=/path/to/tokufractaltreeindex-7.0.1-linux-x86_64/lib/libtokufractaltreeindex-7.0.1_static.a \
-DTOKUPORTABILITY_LIB=/path/to/tokufractaltreeindex-7.0.1-linux-x86_64/lib/libtokuportability-7.0.1_static.a
しかし
cmakeが完了して、そのままmakeすると…
make
(中略)
[100%] Building CXX object sql/CMakeFiles/mysqld.dir/main.cc.o
Linking CXX executable mysqld
libsql.a(sql_backup.cc.o): In function `sql_backups(char const*, char const*, THD*)':
/path/to/mysql-5.5.30-tokudb-7.0.1/sql/sql_backup.cc:36: undefined reference to `tokubackup_create_backup'
libsql.a(sql_backup.cc.o): In function `sql_backup_throttle(unsigned long)':
/path/to/mysql-5.5.30-tokudb-7.0.1/sql/sql_backup.cc:41: undefined reference to `tokubackup_throttle_backup'
libsql.a(sql_backup.cc.o): In function `__static_initialization_and_destruction_0':
/path/to/mysql-5.5.30-tokudb-7.0.1/sql/sql_backup.cc:44: undefined reference to `tokubackup_version_string'
collect2: error: ld returned 1 exit status
make[2]: *** [sql/mysqld] Error 1
make[1]: *** [sql/CMakeFiles/mysqld.dir/all] Error 2
make: *** [all] Error 2
sql/CMakeLists.txtの161行目を以下のように書き換えると問題が発生しなくなりました。
# Toku: NOTE: Hot Backup *MUST* be linked to mysqld first! # <-「順番重要!」と言ってるようだけど…
- TARGET_LINK_LIBRARIES(mysqld ${HOT_BACKUP_LIBS} "${OPTIONAL_JEMALLOC_LIBRARY}" sql mysys)
+ TARGET_LINK_LIBRARIES(mysqld sql ${HOT_BACKUP_LIBS} "${OPTIONAL_JEMALLOC_LIBRARY}" mysys)
libsql.aがlibHotBackupCommunity.soより前でないと前述のエラーになるようです。CMakeLists.txtのコメントが気になるのですが…とりあえずcmakeとmakeをやり直します。
rm -r CMake*
cmake .. -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.5.30-tokudb \
-DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci \
-DTOKUFRACTALTREE_RELEASE_DIR=/path/to/tokufractaltreeindex-7.0.1-linux-x86_64 \
-DTOKUFRACTALTREE_LIB=/path/to/tokufractaltreeindex-7.0.1-linux-x86_64/lib/libtokufractaltreeindex-7.0.1_static.a \
-DTOKUPORTABILITY_LIB=/path/to/tokufractaltreeindex-7.0.1-linux-x86_64/lib/libtokuportability-7.0.1_static.a
make install
今度は上手くいきました。あとはMySQLのマニュアル通りに初期設定をすれば完了です。
mysql -uroot -p
# 確認してみる
mysql> show plugins;
| TokuDB | ACTIVE | STORAGE ENGINE | ha_tokudb.so | PROPRIETARY |
| TokuDB_user_data | ACTIVE | INFORMATION SCHEMA | ha_tokudb.so | PROPRIETARY |
| TokuDB_user_data_exact | ACTIVE | INFORMATION SCHEMA | ha_tokudb.so | PROPRIETARY |
| TokuDB_file_map | ACTIVE | INFORMATION SCHEMA | ha_tokudb.so | PROPRIETARY |
| TokuDB_fractal_tree_info | ACTIVE | INFORMATION SCHEMA | ha_tokudb.so | PROPRIETARY |
| TokuDB_fractal_tree_block_map | ACTIVE | INFORMATION SCHEMA | ha_tokudb.so | PROPRIETARY |
# ライセンスのところがPROPRIETARYのままになってしまったがとりあえずインストールはできている
# テーブルを作成してみる
mysql> use test;
mysql> CREATE TABLE user ( id int(11) NOT NULL, name varchar(33) NOT NULL ) ENGINE=TokuDB;
mysql> insert into user(id, name) values(1,'user1');
mysql> select * from user;
+----+-----------------------------------+
| id | name |
+----+-----------------------------------+
| 1 | user1 |
+----+-----------------------------------+
まとめ
ビルド方法について(特にcmakeまわり)の説明が見つからなかったので、苦労しました。結局libtokufractaltreeindexのソースはどこにあるのでしょうか…。今後の改善に期待します。
今回は性能調査はできませんでしたが、また機会があれば触れるかもしれません。それでは。(担当:K)