Transactd 2.1はさらに高速で低負荷に
まもなくリリースされるTransactd 2.1は、2.0に比べてクエリーの速度が大幅に高速になります。
SQLより高速なクエリー
2.1ではJoinなどに関する処理を徹底的に見直し、SQLを凌ぐクエリーレスポンスを実現しました。
TransactdのJoinを含むクエリーは、テーブルごとにクライアントからリクエストを送信するため、サーバー側ですべてを処理して結果を返すSQLに比べて通信回数が多くなり、レスポンスにおいては不利です。特に結果レコード数が少ない場合は、処理時間に比べて通信時間の占める割合が大きく、高速に処理しても差が出にくくなります。にもかかわらず、Transactd2.1はSQLより高速にJoin処理ができます。
下図は、Linux(Ubuntu 14.04.1)上のPHP5.6クライアントでのレスポンスを、SQL(MySQL 5.6.21, PDO)と比較したグラフです。Readは単純な1レコードの読み取り、Join_xRowはx行の結果を返すクエリーです。すべて、5クライアントで数千回の連続実行をして、1回あたりの平均を取っています。(ネットワークは1Gbps NIC)
図1 (縦軸は秒)
//クエリーのSQL文 select `user`.`id`,`user`.`名前`,`user`.`group`,`groups`.`name` as `group_name` from `user` left join `groups` on `user`.`group` = `groups`.`code` where `user`.`id` > ? and `user`.`id` <= ? order by `group_name`; //Transactdのコード $q->select('id', '名前', 'group')->where('id', '<', '?'); $rs = $activeTable1->keyValue($id)->read($q); $activeTable2->alias('name', 'group_name'); $q->reset()->select('group_name'); $activeTable2->join($rs, $q, 'group'); $rs->orderBy('group_name');
高速化のポイント
プリペアードクエリー
プリペアードクエリー機能が新しく追加されました。これによりクエリーの実行速度の向上、CPU負荷、ネットワーク消費の低減をしました。
TransactdのアクセスはAPIベースです。SQLのような言語構文解析はありません。しかし、クライアント側ではシリアライズ、サーバー側ではデシリアライズや比較順の決定などの負荷があります。
プリペアードクエリーは、(サーバー、クライアントともに)クエリー処理時に行われる準備処理を事前にキャッシュします。クエリー実行時は、プレースホルダーの値のみやり取りして処理を行います。
下図は、プリペアードクエリーを使用したときとそうでない時のレスポンス比較の図です。約12%レスポンス速度が向上しています。
15 Client同時実行 Joinを含む1行を返すクエリー実行速度の平均値(縦軸は秒)
recordsetクラスの実装見直し
図1のJoinを含むテストでは、Transactd、SQLともに結果セットを[行][列]の連想配列の形式で値を読み出せるようにするところまで実行しています。サーバーから受け取ったデータを如何に効率よく連想配列のように取り出せるかについて、メモリのアロケート回数、コピー回数の低減を徹底的に進め、実行速度の向上とメモリの利用効率を高めました。
SWIGバインディングコードの最適化
PHP、Rubyの言語バインディングはSWIGを使用して生成しています。このSWIGによって生成されるバインディングコードを見直し、PHPやRubyでの処理速度の向上と省メモリーを実現しました。
SWIGは関数のオーバーロードやデフォルト引数があると非常に冗長なコードを生成してしまいます。また、メソッドチェーンのためのthisを返す場合も新しいリーソースを確保してしまいます。2.1はこれらを取り除き最適なコードにすることで、バインディングのオーバーヘッドを大幅に小さくしています。
ガベージコレクタの影響を受けない結果セットメモリ管理
PHP、Ruby、ActiveXなどの言語バインディングでも、結果セット(recordset)のメモリ確保と管理はC++のTransactdクライアント内部で行われます。「recordsetクラスの実装見直し」と「SWIGバインディングコードの最適化」によって、ガベージコレクタによる「引っかかるような」もたつきがほとんどなくなりました。常にスムーズに処理が行われます。
下の動画はPHPにて3つのテーブルをJoinし、500行の結果を返すクエリーを2000回連続して実行したときの、PDOとTransactdの比較動画です。20回ごとに1つのプログレスが進んでいきます。上がPDO、下がTransactdです。ともに同じ結果を返すクエリーです。
Transactdはスムーズに処理が進んでいく様子をご覧ください。
Transactd 2.1 vs PDO(MySQL) - YouTube
このクエリーでは、MySQL+PDOが32.4秒、Transactdが9.4秒で3倍以上の速度差が出ています。
SQLの数分の1のサーバーCPU負荷
一連のbenchmark実行時のサーバーCPU負荷を計測しました。 図1のJoin 100レコードのReadテスト時のCPU負荷の変化を示します。
(図1では5クライアントの時のデータを載せていますが、実際の計測は1~50クライアントまで連続的に計測しています。)
図を見るとTransactdはCPU負荷が小さいことがわかります。(面積で約 1/2)さらにこのとき約1.6倍のスループットが出ていましたので、実質MySQLの約1/3のCPU負荷になっています。
データの転送量の少なさ、SQL文の解析、Joinの結合処理、OrderByのソート処理がないことが要因かと思います。サーバーは、SQLに比べ約3倍のスケーリングを実現できます。
まとめ
2.0のリリース以降、クエリーパフォーマンスの改善を図ってきました。この改善でサーバー、クライアントとも処理時間は限りなく少なくなりました。
ここで提示したデータはありのままのデータです。出来る限り実際の使用状況に近くするため、1GbpsのNICでケーブルで接続して計測しています。ローカルや同一ホスト内の仮想サーバー間通信ではありません。
今回示したデータは、SQLとの差が少し少な目に見えるかも知れません。これは、処理時間において、1Gbps NICによるTCP/IP通信のレイテンシの占める割合が大きく、この部分はSQLもTransactdもほぼ同様にかかるためです。他のNoSQL(Radisやmemcachedなど)でも全く同様です。
参考までに、ローカルでのサーバーとクライアントの通信に、共有メモリによるプロセス間通信とTCP/IPを使った場合の差を示します。共有メモリによる方法は通信時間は限りなくゼロに近いものです。点線で囲まれた部分が通信時間に相当します。通信時間の比率の高さがよくわかると思います。
処理時間に占める通信時間(縦軸はミリ秒)
サーバー間のネットワークもいよいよ10Gbpsの時代になりつつあります。サーバーとクライアントを10Gbps NICで接続すればSQLとの差はさらに大きなものになると思います。
先日、ブログのコメントに、更新処理における排他制御に関する質問をいただきましたので、詳しく回答させていただきました。その他、使い方など何か不明なことがありましたら是非コメントください。お待ちしています。
(いただいた質問は下記記事にあります。)
Transactd 2.0 その3 データベーススケーリング - BizStationブログ
MySQLからNoSQLへの移行に「ちょっと待った」
Transactdの開発過程でMySQLのパフォーマンス問題を調べてみたところ、ほとんどは「クエリーが遅い」といった問題のようでした。また、NoSQLデータベースへの移行理由としても「MySQLが遅いから」といった意見が結構あります。しかし、そのような理由でMySQLからNoSQLに移行しようとしているのなら、「ちょっと待った」です。(他の理由なら別ですが。)
NoSQLに移行すれば何もかもがバラ色というわけではありません。本当に移行する必要があるか、これを読んで考えてみてください。
TransactdはNoSQLですが、MySQLでもあります。同じMySQLでNoSQLを実現できるTransactdはこの話の中ではMySQLに入れて考えてください。
遅い理由
一口に遅いといっても、いくつかの意味があります。
応答速度が遅い
応答速度を見るには、マスターデータにselect * where id = x from xxx
といった1レコードを取得するクエリを投げて、どれくらいのスピードで返るか比較するのが適当でしょう。サーバーだけでなく、ネットワーク、クライアントライブラリも含めた計測で評価する必要があります。
データがメモリ上にあることを前提とすると、応答速度を決定する主な要因は、ネットワークのレイテンシです。ネットワークのレイテンシについては、TCPであればSQLでもNoSQLでも同じだけかかります。
SQLだと、このような簡単なクエリでも、構文解析を行うためNoSQLより遅くなります。しかし、Transactdには構文解析はありません。同じネットワークであれば他のNoSQLとほとんど同じ速度です。
これを確かめるために、実際にmemcached 1.4.5と比較してみました。1つ約100バイトのレコードを2万件用意して、1レコードずつ20,000回の読み取りにかかる時間を計測しました。同じサーバーマシン、同じクライアントマシンで、クライアントはすべてPHP5.5で比較しています。
[MYSQL(PDO)] 7.3037860393524 sec 2738.3058447004 records/sec [memcached] 5.2722289562225 sec 3793.4619619269 records/sec [Transactd] 5.6204497814178 sec 3558.4340716153 records/sec
[TEST Environment Spec] Server : HP Proliant DL165G7 AMD Opteron(TM) Processor 6212 / 10GB RAM Intel 1Gbps NIC Client : HP Elite book 9470 Core i5 8GB RAM Intel 1Gbps NIC MySQL : 5.6.14 64Bit Transactd : 2.0.0 64Bit memcached : 1.4.5 64Bit
memcachedが少し速い結果になりましたが、Transactdと大きな差はありません。innodbは十分に高速ですね。Transactdを使えば、他のNoSQLに変更しても応答速度は大きく変わりません。
(「ホントにmemcachedと変わんないの?」と思う方は多いでしょう。TranasctdをネットワークレイテンシのないWindowsローカルで動作させると上の条件で1秒くらいになります。ネットワークの往復2万回で4秒ほどかかっている計算です。これはmemcachedも同じですからほとんど差がないのです。)
クエリが遅い
クエリが遅いのは、サーバー側で大量のレコードを読み取ってしまったり、大量のデータを保持しソートするなどしているからです。適切なindexを使って限られた範囲を読み取れば、(クライアント数の増大など以外では)予想外のパフォーマンスになってしまうことはありません。もう一度クエリを見直してみましょう。
大切なのは、どのindexを使ってどこからどの範囲を読み取るのか、その範囲にはおよそどの程度のレコードがあるのかを、将来も含めて想定することです。
SQLだけで操作してきた場合は、これらを想定するのは難しいように思えるかもしれません。しかし、これが出来ないと、他のNoSQLを使用したプログラミングはもっと難しくなります。
NoSQLではkey/valueでデータを格納していきます。シンプルでいいのですが、たとえばvalueでの絞り込みや検索を行う際に、すべてのデータを取得して探したのでは、とても遅くなってしまいます。これを回避するにはIndexテーブルなどを作成して、事前にどこにデータがあるかkeyを保存する処理などが必要になります。
実はこの「Indexテーブルなどの作成」は、先ほど強調した大切な事項とほぼ同じことを言っています。遅いクエリを書いてしまうのは、indexを使ったアクセスと読み取り範囲を想定できていないからであり、たとえNoSQLでもそこから逃れることはできないのです。
よりよいクエリを書くための提案があります。TransactdのQueryBuilderを使ってクエリを作成してみてください。QueryBuilderでは、どのインデックスを使用してどの値から読み取るのかを最初に決めなくてはなりません。同時にconditionsで終わりの条件も指定します。もし、どちらも指定しなければ、そのテーブルの全レコードを検索します。
「縛りがきついな」と思われた方、オプティマイザの夢物語はそこまでにしましょう。MySQLのオプティマイザが決める実行計画も、何ひとつ変わらない全く同じ縛りの中で決定されています。
まとめ
いかがでしたでしょうか?NoSQLに移行したからといって「遅い」が簡単には改善しないことがお分りいただけたと思います。Transactdであれば、MySQLのまま部分的に置き換えて高速化することもできます。
また、クエリの見直しには是非QueryBuilderを使ってみてください。作りながら実行できるので、すぐにindexを使ったアクセスの仕方と大切さを実感できます。理解が進めばパフォーマンスの良いSQLが自然に書けるようになるかと思います。教育や学習ツールにもなりますね!
最後に、関連記事を貼っておきます。
Transactd 2.0 その2 QueryBuilder で簡単NoSQLクエリー - BizStationブログ
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();//<-- すべてのデータベースを解放
Transactd 2.0 その2 QueryBuilder で簡単NoSQLクエリー
Transactd 2.0 その1では、読み取りクエリーの使い方について説明しました。今回は、そのクエリーのコードをVisualに生成できるツール、QueryBuilderを紹介します。
QueryBuilderでは、GUIの画面上でデータベースやテーブル、キーの値や条件などを指定すると、Transactdのクエリーのソースコードを生成できます。生成されたコードをコピーアンドペーストするだけでデータベースへのアクセスコードが完成します。開発工程をとても簡単にしてくれること間違いなしです。生成可能ソースの言語は現在のところ、C++/PHP/Ruby/JScriptです。
また、実際にクエリーを実行して結果や実行速度を確認することもできます。
QueryBuilderのインストール
QueryBuilderは現在Beta版で、WindowsとMac OS X版があります。どちらも、インストールパッケージで簡単にインストールできます。アンインストールもクリーンにできますので是非試してみてください。
追記 (2016/11/15)
以前のバージョンはバグがありますので最新版(バージョン 3.6)のみリンクします。サーバーPluginも Version 3.6を使用してください。
- Mac OS X 10.9 : querybuilder.pkg (未署名のため、ダウンロード後Ctrlキーを押しながらクリックして開き、開発元未確認のダイアログで[開く]をクリックします。)
- Windows 32Bit : setupQueryBuilder_3_6.exe (デジタル署名済)
それでは、実際の使い方をご紹介します。(実際の操作を行うには、Transactdのサーバーが必要です。Pluginのインストールはこちら)
テーブルからレコードを読み取る
+ボタンをクリックしてウィザードを開始します。
データベースのホスト名、データベース名、スキーマテーブルの名前を指定して[Next]をクリックします。スキーマテーブルを特別に用意していない場合はtransactd_schema
と指定してください。自動で、そのデータベースのスキーマが生成されます。
[Table]にテーブルリストが表示されるので、読み取るテーブルと、インデックスを選択します。
ユニークなインデックスにはグリーンのマークがついています。[Key Values]には指定したインデックスのフィールド名タイプとサイズが表示されます。
[Value]列に読み取りを開始するインデックスの値を入力します。もし、テーブルの先頭なら、最も小さな値を指定します。[Next]をクリックします。
ここでは、SQLのselect
に相当する、列の選択を行います。
左側に選択したテーブルのフィールド一覧が表示されるので、必要なフィールドを→ボタンで右側のリストに加えていきます。この例では4つのフィールドすべてを追加しています。
また、フィールド名に別名を付ける場合は[Alias]に別名を入力します。例では「名前」フィールドを「name」としています。[Next]をクリックします。
最後に抽出条件を指定します。検索の始まりは最初に指定したので、終わりをid <= 100
とします。左側のフィールド一覧からidを、右側の Record match conditions に→ボタンで入れます。[Conditions]に<= 100
と入力します。 [Finish]をクリックして完了です。
ソースコードの表示
[PHP]タブをクリックするとクエリーの内容がPHPのソースコードで表示されます。
生成されたソース全体は、単独で動作するように書かれていますが、クエリーの部分だけが選択された状態で表示されます。選択された部分のコードは以下のようになっています。
$cp1 = new bzs\connectParams('tdap://localhost/querytest?dbfile=test.bdf'); $db->c_use($cp1); $at1 = new bzs\activeTable($db, 'user'); $at1->alias('名前', 'name'); $q = new bzs\query(); $q->select('id', 'name', 'group', 'tel'); $q->where('id', '<=', '100') ->reject(65535)->limit(0)->optimize(bzs\queryBase::none); $rs = $at1->index(0)->keyValue('0')->read($q);
最後の行の$rs
が結果のrecordset
です。
データの表示
[Run]をクリックするとクエリーを実行して、結果が[Data view]に表示されます。
右上の緑のステータスの横には実行時間が表示されます。このサンプルはWindows 32Bitで表示していますが、WindowsのGetTickCount
の計測の誤差が0.015程度あるため0と表示されています。100レコード程度だとほとんど0.01秒以下です。
Joinする
それでは次に上記の結果のgroup
フィールドからそのグループの名前をJoinしてみます。グループの名前はgroups
テーブルから参照します。+ボタンをクリックしてウィザードを開始します。
Joinを選択して[Next]をクリックします。
再びデータベースを選択する画面が出ます。すなわち異なるデータベースのテーブルもJoin可能です。同じデータベースなら、[Current database]を選択します。
ここでは、Joinするテーブルを選択し、レコードの検索に使うインデックスを指定します。
通常、マスタの参照であればユニークキーが存在します。groups
テーブルでは一意なフィールドがid
ではなくcode
というフィールドになっています。
次に、左側のレコードセット内のフィールドからJoinのキーとなるフィールドを選択し→ボタンで右側の[Join Key(s)]に入れます。これで、user.group
とgroups.code
でJoinするという意味になります。
[Next]をクリックします。
最後に、groups
テーブルから追加する列を選択します。ここではname
を選択しました。またgroup_name
という別名を付けました。[Finish]をクリックして完了です。
それでは生成されたソースコードを見てみまししょう。先程と同じ部分は省きます。
$cp2 = new bzs\connectParams('tdap://192.168.3.155/querytest?dbfile=test.bdf'); $db->c_use($cp2); $at2 = new bzs\activeTable($db, 'groups'); $at2->alias('name', 'group_name'); $q->reset()->select('name')->optimize(bzs\queryBase::none); $at2->index(0)->join($rs, $q, 'group');
最初の行で異なるサーバーが指定されています。分散クエリーが簡単に実行できます。
実行すると、group_name
フィールドが追加されているのがわかります。実行時間もJoinをしても0.01秒以下で実行できています。
GropuByをしてみる
次に、group
フィールドでグルーピングしてみましょう。各グループに属するユーザーの数をカウントしてみます。
先ほどまで同様、+ボタンをクリックしてウィザードを開始します。
[Operation]ページで[GroupBy]を選択して[Next]をクリックします。
[GroupBy]ページが表示されます。
左上にrecordset
内のフィールド一覧が表示されます。ここから、グルーピングのキーになるフィールドを→ボタンで[Grouping Key]に入れます。
次に、[Functions]からCount
を選択し、→ボタンをクリックします。画像がありませんが、ここでカウントした結果を入れる列名を聞かれます。ここではuser_count
としました。
[Registerd function]にはCount
のresultがuser_count
であると表示されています。[Finish]をクリックして完了です。
追加されたソースコードと実行結果を見てみましょう。
$q = new bzs\groupQuery(); $gq->keyField('group'); $fns = new bzs\fieldNames(); $fns->reset()->keyField(); $f0 = new bzs\count(fns, 'user_count'); $gq->addFunction($f0); $rs->groupBy(gq);
各グループ20Usersづつであることがわかります。これでもまだ実行時間は0.01秒以下です。上段の[Records]列を見てください。Read Join GroupByの後のレコード数が表示されています。それまで100レコードだったものが、 GroupBy
で5レコードになったことがわかります。
まとめ
QueryBuilderはいかがだったでしょうか? 是非皆さんもダウンロードして試してみてください。簡単にTransactdのクエリーを作成できること、分散クエリーが可能なこと、高速に実行できることをお分りいただけると思います。
そして何より、開発がとてもスピーディーで正確で簡単になります。
Transactd 2.0 その1 SQLライクなクエリーと結果セット
いよいよTransactd 2.0をリリースします。Transactd 2.0で新しくなったのは主に、
です。順番に詳しく紹介してゆきます。今回は「SQLライクなクエリーと結果セット」です。
Transactdの従来のAPIはどんな処理でも書けますが、SQLに慣れた方にとってはあまりフレンドリーではありませんでした。特に、JOINを含むレコードの読み取り・検索は、SQLと大きく異なっています。
そこで、SQLライクなAPIと結果を扱うrecordset
クラスが追加されました。
recordset
は連想配列によく似た結果セットで、rs[1]["name"]
のように行・列に添え字でアクセスできます。内部実装はC++であり、PHPでもRubyでも高速にSQLライクな処理ができます。
紹介のためのサンプルコードはPHPで書いてみます。メソッドの多くは、メソッドチェーンができるようにthisを返します。
OrderBy
まずはOrderBy
。recordset rs
の"group"
というフィールドで昇順にソートします。
$rs->orderBy("group");
逆順にするにはreverse
を使います。
$rs->reverse();
複数のフィールドで昇順、降順が混在するorderBy
は、sortFields
クラスにフィールド名とソート方向をセットして渡します。
"group"
で昇順、"tel"
で降順にするならば、以下のようになります。
$sort = new sortFields(); $sort->add('group', true).add('tel', false); $rs->orderBy($sort);
GroupBy
次に、GroupBy
。groupQuery
オブジェクトに情報をセットしてrecordset
のgroupBy
メソッドに渡します。
$gq = new groupQuery();
...
$rs->groupBy(gq);
groupQuery
オブジェクトには複数の計算関数を追加できます。計算関数には対象のフィールドと結果フィールド名を指定します。
例は"group"
でグルーピングして各グループに属するレコード数をカウントし"group_count"
という列に結果を格納します。
$gq = new groupQuery(); $gq->keyField("group"); //<---- グルーピングキーフィールドの指定 $func = new count("group_count"); //<---- 関数と結果列名の指定 $gq->addFunction($func); $rs->groupBy($gq);
計算関数にはcount
の他にもsum
avg
min
max
があります。
レコードごとに計算の対象とするかどうか指定したいときは、計算関数のオブジェクトにWhen
フィルタを設定できます。
"tel" != ""
の時だけカウントしたいときは
$func->when("tel", "<>" ,"");
でフィルタリングできます。さらに、
$func->when("tel", "<>" ,"")->and_("tel" , "<>", "090*");
のようにand_()
とor_()
条件をメソッドチェーンでつないでいくこともできます。
また、グルーピングのキーは複数のフィールドとすることもできます。
$gq->keyField("group", "name");
MatchBy
結果セットを得たあとでフィルタリングできます。マッチしない行はレコードセットから削除されます。
$rq = new recordsetQuery(); $rq->when("count", ">", "1")->and_("tel", "<>", ""); $rs->matchBy(rq);
途中結果をコピー
SQLでは完全な結果を得るのみで途中結果を得ることはできません。
たとえば、売上の明細とその金額の合計を得たい場合、明細のクエリーと合計を計算するクエリーの2つが必要です。(または合計を自分で計算します。)
Transactdでは、処理をクライアント側で順番に書くので、途中の結果セットをコピーしておくだけで両方の結果を得られ効率的です。
... $rs2 = $rs->clone(); ... $rs2->groupBy(gq); // rsは明細 // rs2は合計
レコードの読み取り
新しく読取専用クラスactiveTable
クラスが追加されました。
activeTable
には主に、read
, join
, outerJoin
の3つのメソッドしかありません。
まずは単純なread
です。データベースとテーブル名からactiveTable
を生成します。
次にクエリーを作成してactiveTable
のread
に渡すと、結果セット(recordset
)が返ってきます。
クエリーは、select
, where
, and_
, or_
メソッドでSQLライクに記述できます。
まず、activeTable
を生成します。
$at = new activeTable($db, "user");
検索には必ずindex
を番号で指定します。
$at->index(1);
検索を開始する先頭レコードの値を指定します。この値はindex
で指定したキーフィールドの値です。
$at->index(1)->keyValue("1");
次にquery
オブジェクトを使ってSQLライクに検索条件を指定します。
$q = new query(); $q->select("id", "名前", "group")->where("group", "<=", "2")
フィールドに別名を付けてみましょう。
$at->alias("名前", "name");
最後に読み取ります。
$rs = $at->read($q);
全部まとめて書くと (クラスの名前空間 BizStation\Transactdは省略)
$db = new database(); $uri = "tdap://localhost/test?dbfile=transactd_schema"; $db->open($uri, TYPE_BDF, OPEN_NORMAL); $at = new activeTable($db, "user"); $at->alias("名前", "name"); $q = new query(); $q->select("id", "name", "group")->where("group", "<=", "2") $rs = $at->index(1)->keyValue("1")->read($q);
すべてのフィールドを読み取りたいときは、select
メソッドでフィールドを選択しないようにします。そうするとデフォルトですべてのフィールドが返ります。
$q = new query(); $q->where("group", "<=", "2") $rs = $at->index(1)->keyValue("1")->read($q);
Join
さあいよいよ、核心のJoin
です。
TransactdのJoin
は、サーバーサイドでの結合ではなく、クライアントサイドで結合します。結合は、事前に取得したrecordset
とこれから読み取るテーブルのフィールドで行います。
1対1のJoin
例として$rs
は上記の結果セットとします。結果セットのgroup
フィールドにはグループ番号が入っています。この番号からgroups
テーブルのname
フィールドをJoinしてみましょう。
$at2 = new activeTable($db, "groups"); $q->select('name'); //<--- name 列だけを読み取る $at2->index(0)->join($rs, $q, "group"); //<--- 結果セットのgroup列をキーとしてJoin
これで、user
テーブルから得た結果セットにgroups
テーブルのname
フィールドをJoinしました。
内部では、recordset
のgroup
フィールドの値をgroups
のindex
で指定されたキーフィールドにセットして、順にレコードを取得しています。この場合、index(0)
はgroups
のプライマリーユニークキーなので、1対1のJoinです。
キーがマルチセグメントキーの場合は、それぞれのセグメントに対応するrecordset
のフィールドを指定します。
例えば、groups
テーブルのprimaryキーがtype + id
なら
$at2 = new activeTable($db, "groups"); $q->select('name'); $at2->index(0)->join($rs, $q, "type", "group");
のようにjoinメソッドで2つのフィールドを指定します。
1対多のJoin
recordset
内の1行に対して複数の相手レコードがある場合は1対多のJoinです。
この場合、指定するインデックスは、重複可能なキーか、マルチセグメントのユニークキーで下位セグメントが未指定、のどちらかになります。
呼び出しは、1対1の場合とほとんど同じです。
例えば、まず、1~100までのグループを読み取って、そこに属するユーザーをすべてJoinしてみましょう。
最初に1~100までのグループを読み取ります。
$at = new activeTable($db, "groups"); $at2->alias("id", "group_id"); $q = new query(); $q->where("group_id", "<=", "100") $rs = $at->index(0)->keyValue("1")->read($q);
次にそこに属するユーザーのid
とname
をJoinしてみます。
$at2 = new activeTable($db, "user"); $at2->alias("名前", "name"); $q->reset()->select("id", "name"); $at2->index(1)->join($rs, $q, "group_id"); //<--- index=1はgroupフィールドの重複可キー
これで、読み取ることができました。$q->reset()
はquery
オブジェクトの状態をリセットして、オブジェクトを使いまわすために呼び出しています。
OuterJoin
先ほどまでのJoinはInnerJoin
で、相手レコードが見つからなかった場合は、recordset
内の該当行は削除されます。
それに対して、outerJoin
メソッドを使うと、相手レコードが見つからなかった場合でも元の行はそのまま残ります。追加されたフィールドは数値0
、文字列では""
で初期化されます。
Joinの最適化オプション
TransactdのJoinには1つだけ最適化オプションがあります。
Joinメソッドは、テーブルにアクセスする前にrecordset
内の各行のJoinのキー値を調べ、同じものがないか走査し、同値であればそれを省いて読み取りを行い、読み取られるレコード数を削減する処理を行っています。
ところが、各行のJoinのキー値がユニークであることがわかっている場合、この処理はムダです。そこでこの処理を省くためのオプションが用意されています。
recordeset
の各行のJoinキー値がユニークであるとわかっている場合は、query
オブジェクトのoptimize
にjoinHasOneOrHasMany
を指定します。
$q->optimize(joinHasOneOrHasMany);
Union
2つのrecordset
オブジェクトを和結合できます。$rs
に$rs2
を結合します。
$rs->unionRecordset($rs2);
ただし、2つのレコードセットのフィールドは順序も含めてすべて同じでなければなりません。もし異なる場合は例外が投げられます。
recordsetのそのほかの主な機能
列情報
recordset
のfieldDefs
メソッドで、fielddef
オブジェクトのコレクションが返ります。
例は、すべてのフィールド名を表示しています。
$fds = $rs->fieldDefs(); foreach($fd in $fds) { echo $fd->name().PHP_EOL; }
Top
recordset
の先頭N行を返します。
$rsTop10 = new recordset(); $rs->top($rsTop10, 10)
もし、$rs
の行数がN行に満たない場合は、すべての行がコピーされます。
FirstとLast
先頭行および最終行を返します。
$firstRecord = $rs->first(); $lastRecord = $rs->last();
removeField
N番目(0Orign)の列を削除します。
$rs->removeField(3);
appendField
列を末尾に追加します。
$rs->appendField("mobile phone", ft_myvarchar/*type*/, 30/*length*/);
まとめ
ご覧いただいた通り、activeTable
とrecordset
の組み合わせを用いると、SQLライクな表現でSQLとほぼ同様な結果を簡単に得ることができます。処理の順序や使用するインデックスの指定など、プログラマの思い通りにとてもフレキシブルに処理できます。
なお、SQLのような四則演算や文字列操作関数は、各言語で可能な処理ですので用意されていません。SQLは独立した言語なのでそのような関数がありますが、TransactdはAPIですので、それぞれの言語に用意されたものを使用できます。
次回は、今回ご紹介したSQLライクなクエリーのソースコードををVisualな操作で簡単に生成してくれる、queryBuilder
をご紹介したいと思います。queryBuilder
は、WindowsとMac OS X 10.9 でC++/PHP/Ruby/JScriptのソースを生成できます。
C++ クロスプラットフォーム開発
しばらく日が空いてしまいましたが、相変わらず元気に Transactdに励んでいます。もうすぐTransactd 2.0 がリリースできそうですが、今回はC++コンパイラーのお話しです。
Transactdの基本ライブラリ開発はC++ですが、OSはWindwosとLinuxとMac OS X、コンパイラーはVisual Studio, C++Builder 32Bit 64Bit, Clang, GCC とマルチプラットフォーム、マルチコンパイラです。でもこれをワンソースでコンパイルできています。日々これらと格闘していると色々見えてきたのでその辺を書き留めておきたいと思います。
文字コード
Linux、Mac OS Xではutf8で書いていますが、定数に日本語文字列を使うときは、ソースファイルを必ずutf8 BOM付で保存します。LinuxではBOMなしが標準ですが、Visual Studio (VS)はBOMなしを理解してくれないのでBOM付にします。(4.3以前の古いGCCはBOMを読めないので注意)
WindowsではUNICODEが標準ですので、文字列は通常ワイド文字として扱います。WindowsとLinuxの両方に対応するために、文字列はすべて _T
_TCHAR
マクロを使い、Linux用のtchar.h
を作成して両方に対応させています。最初は少し面倒ですが、慣れればあまり大した問題ではないですね。
template
テンプレートへの対応は、C++Builder 32Bitが一番おバカで、これでコンパイルが通ればあとのコンパイラーは何とかなります。ですので、最初のコンパイルはほとんどC++Builder32です。このコンパイラーのテンプレート解析のクセはVSに通じるものがあって、Borlandの開発者はVSでコンパイルでできてC++Builderでできないものを潰していったのでは?と感じるものがあります。
GCCとClangは似ているようでそうでもなく、VSとも違い3つのコンパイラーはそれぞれ我が道を行っています。そうは言ってもC++ですので、それぞれに意味のあるエラーを吐いてくれます。あちらをとればこちらがダメといったことはかなり少なく、普通のテンプレートほとんど共通で使えます。
boost
boost様々です。プラットフォームに依存する部分をboostで書けばワンソースでマルチプラットフォームが簡単です。thread mutex socketやfile TESTなんかもそうです。ただ、C++Builder32はboost 1.39までしかサポートされていないので、それ以上の機能を使うときだけ、C++Builder32では少し異なる実装をする時があります。GCC VS Clangでは最新のboostが使えます。
デバッガ
Visualなデバッガに慣れてしまっていると Linuxでのgdbは悲惨です。Windowsでのデバッグの3倍くらいの時間がかかってしまいます。デバッグにはC++Builder32がお勧めです。理由は
何よりも、コンパイルが早いのがデバッグには最高です。直す -> コンパイル -> 実行 -> デバッグ のサイクルをとても早く回すことができます。機能も欲しいものはほとんど揃っています。唯一、条件付きのブレークポイントが設定できませんのでif文を入れてブレークしたりしています。
gdbはLinuxでの動作に問題があるときにたまに使うくらいであまり頻繁には使いません。Linuxではデバッグ用のコード埋め込みやダンプのほうがやり易い場合も多くあります。
C++Builder 64Bit Clangのデバッガはリモートデバッグに似ていて、IDEとの間に別プロセスのエージェントが入っています。今のところこれがハングしてしまうことが多く、一応使えますが、安定して快適にとうところにはなっていません。
コンパイラとプラットフォームの違いの吸収
コンパイラによって Cライブラリやpragmaなどが違っています。また、プラットフォームでパスのセパレータやスレッドローカルストレージなど様々なものが異なっています。
これらは、それぞれ、compiler.h
とcrosscompile.h
にまとめ、マクロでの切り替えや不足する関数を補って、通常のソースは共通のコードで書けるようにしています。あまりきれいなソースではありませんが違いを埋めるマクロが詰まっていて、苦労の逸品?です。(transactdのソースにありますのでGPLv2で使えます。)
最適化
C++Builder32を少しお勧めしましたが、最大の欠点は実行速度が遅いことです。VSの1.5倍くらい遅くなります。PHPやRubyからVSでコンパイルしたTransactdクライアントを使うと、C++Builder32のネイティブより高速にDB処理ができます。ただ実際はDB処理だけではありませんので、総合的にはC++Builderの方が早くはなると思いますが、糊(スクリプト言語)の実行速度は下で働くC/C++ライブラリの最適化と、どれだけライブラリ呼び出しだけで済ませるかにかかるかと思います。スクリプト言語でゴリゴリ書くとスローになってしまいます。(TransactdのPHP,Ruby,ActiveXはすべてVSかGCCですのでご心配なく)
64Bit Windows上のVSとClangではVSの方が数パーセント速そうで、やはり総合的に見てVSはベターなコンパイラです。
第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を試してみてください。