2014-01-28

MySQL パフォーマンスとtransactd その２の１

MySQL Transactd

その２はselect * from tablename where fieldname = xxxです。長くなるのでまずは２の１から。

なんとも簡単な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 その１をご覧ください。）

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回の検索でのスキャンレコード数を制限することで終了位置をコントロールできます。パフォーマンスは、プログラマが組み立てられます。

2014-01-27

MySQL SQLパフォーマンスとtransactd その１

MySQL Transactd

よく、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()は、常にコミット済のレコード数を瞬時に返してくれます。

次のその２では、select * from tablename where fieldname = xxxを取り上げたいと思います。

2013-12-02

TokuDB 7.0.1のCommunity EditionをUbuntuでビルド

MySQL

（以下の記事は、旧ブログの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）

2013-11-27

ブログを開設しました

お知らせ

こんにちは。

本日、ビズステーション株式会社の公式ブログを開設しました。

ソフトウェア開発に関するTipsから開発背景まで、様々な情報をお届けする予定です。

よろしくお願いいたします！

BizStationブログ

ビズステーション株式会社の公式ブログです。

MySQL パフォーマンスとtransactd その２の１

使用するインデックス解析

transactdでインデックスを指定する

MySQLでfieldnameフィールドのインデックスがない場合

transactdでfieldnameフィールドのインデックスがない場合(クライントフィルター)

transactdでfieldnameフィールドのインデックスがない場合(サーバーフィルター)

まとめ　(select * from tablename where fieldname = xxx)

transactdのインデックスを使わない検索

MySQL SQLパフォーマンスとtransactd その１

select count(*) from tablename

transactdでテーブルのレコード数を調べる

まとめ　(select count(*) from tablename)

TokuDB 7.0.1のCommunity EditionをUbuntuでビルド

確認事項

ダウンロード

Fractal Tree Library のビルド

しかし

TokuDB入りMySQLのビルド

しかし

まとめ

ブログを開設しました

使用するインデックス解析

transactdでインデックスを指定する

MySQLでfieldnameフィールドのインデックスがない場合

transactdでfieldnameフィールドのインデックスがない場合(クライントフィルター)

transactdでfieldnameフィールドのインデックスがない場合(サーバーフィルター)

まとめ (select * from tablename where fieldname = xxx)

transactdのインデックスを使わない検索

select count(*) from tablename

transactdでテーブルのレコード数を調べる

まとめ (select count(*) from tablename)

確認事項

ダウンロード

Fractal Tree Library のビルド

しかし

TokuDB入りMySQLのビルド

しかし

まとめ

まとめ　(select * from tablename where fieldname = xxx)

まとめ　(select count(*) from tablename)