MySQL5.7でInnoDBのTransparent Page Compressionを試してみる Part1

MySQL

このエントリはMySQL Casual Advent Calendar 2015の6日目のエントリです。
Part1としているのは12/21にPart2を公開予定のためです。

InnoDBのTransparent Page Compression

MySQL 5.7からInnoDBにTransparent Page Compressionという圧縮機能が追加されました。InnoDBの圧縮機能はMySQL 5.1のInnoDB Pluginから使用可能でしたが、これまでのInnoDB圧縮と違い、Transparent Page Compressionは使用環境の制限があります。具体的にはFileSystemがhole punchingをサポートしている必要があります。
MySQL Server Blogからの抜粋ですが以下のようにKernelのバージョン制限があります。
※InnoDB Transparent Page Compression

よく使われているであろうCentOSでは7系であれば標準のKernelで利用可能です。
2016/01/06追記: 5.7.8-rc以降、CentOS6の2.6.32-504.12.2.el6.x86_64では使えることを確認しました。なおLinux genericだとダメなもよう。RHEL/Oracle Linux用のrpmだと使用可のようです。
利用可能な場合はMySQL起動時に以下のようなログが出力されます。

[Note] InnoDB: PUNCH HOLE support available

サポートされている圧縮形式はzlibとlz4の2つです。

注意点

特徴として*.ibdファイルはsparseファイルになるのでコールドバックアップを行っている場合は注意が必要です。
またxfsでのsparseファイルの挙動がやや怪しいので現時点ではext4か可能ならnvmfsでの使用が推奨になるかと思います。
InnoDB deadlock, thread stuck on kernel calls from transparent page compression

利用方法

サポートされているKernel環境であれば使用する際はこれまでのInnoDBの圧縮機能とほとんど変わらず、CREATE TABLE文に記載する形になります。

CREATE TABLE `t1` (
 id int,
 primary key (id)
) ENGINE=InnoDB COMPRESSION="zlib";

これまでのInnoDB圧縮より構文はシンプルかもしれません。
COMPRESSIONで指定可能な項目は zlib, lz4, noneの3つです。これまでのInnoDB圧縮との大きな違いは途中で圧縮を無効にしたい、有効にしたい、といった場合は以下のようにALTER文を実行しますがメタデータの変更しか発生しないためデータ量に関わらず即座に応答が返ってきます。

mysql> ALTER TABLE counttable COMPRESSION="none";
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> ALTER TABLE counttable COMPRESSION="lz4";
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

対象ページへの次の書き込みから圧縮・解凍されたデータが書き込まれる形となるので多少好みが分かれるかもしれません。
強制的に全ページを変更後の内容(zlib, lz4, none)に書き換えたい場合はOPTIMIZE TABLEを使う必要があります。

圧縮率

今回はLinkBenchのデータ(maxid1 = 20000001)のデータで確認しました。innodb_page_sizeは4k, 8k, 16k, 32k, 64kと全て試してみました(16kがデフォルト)。なお先にも記載した通りsparseファイルとなるためサイズの確認にはduコマンドに--apparent-sizeオプションの有無で確認しています。innodb_page_size 16kの場合の詳細な値は後述します。

compression innodb_page_size du -sch du -sch --apparent-size
lz4 4k 21GB 26GB
lz4 8k 17GB 25GB
lz4 16k 13GB 24GB
lz4 32k 11GB 25GB
lz4 64k 9.8GB 26GB
zlib 4k 21GB 26GB
zlib 8k 13GB 25GB
zlib 16k 11GB 24GB
zlib 32k 8.7GB 25GB
zlib 64k 8.0GB 26GB

各テーブルの圧縮率をMySQLから確認する場合は以下のようになります。

innodb_page_size=16kでzlibの場合
mysql> select name, ((file_size-allocated_size)*100)/(file_size+1) as compressed_pct, allocated_size/(1024*1024) as allocated_size_in_mb, file_size/(1024*1024) as file_size_in_mb from information_schema.INNODB_SYS_TABLESPACES WHERE name like 'linkdb%';
+------------------------+----------------+----------------------+-----------------+
| name                   | compressed_pct | allocated_size_in_mb | file_size_in_mb |
+------------------------+----------------+----------------------+-----------------+
| linkdb/linktable#P#p0  |        62.5583 |             353.4492 |        944.0000 |
| linkdb/linktable#P#p1  |        63.5606 |             230.2969 |        632.0000 |
| linkdb/linktable#P#p2  |        61.3289 |             580.0664 |       1500.0000 |
| linkdb/linktable#P#p3  |        62.3612 |             414.0273 |       1100.0000 |
| linkdb/linktable#P#p4  |        61.7483 |             368.7461 |        964.0000 |
| linkdb/linktable#P#p5  |        62.1545 |             534.3789 |       1412.0000 |
| linkdb/linktable#P#p6  |        62.0091 |             784.1328 |       2064.0000 |
| linkdb/linktable#P#p7  |        63.0929 |             329.2109 |        892.0000 |
| linkdb/linktable#P#p8  |        62.6877 |             343.2734 |        920.0000 |
| linkdb/linktable#P#p9  |        63.6906 |             219.3086 |        604.0000 |
| linkdb/linktable#P#p10 |        61.4523 |             488.7852 |       1268.0000 |
| linkdb/linktable#P#p11 |        63.9216 |             202.0391 |        560.0000 |
| linkdb/linktable#P#p12 |        62.2676 |             250.5430 |        664.0000 |
| linkdb/linktable#P#p13 |        62.1705 |             550.7969 |       1456.0000 |
| linkdb/linktable#P#p14 |        61.9548 |             590.4609 |       1552.0000 |
| linkdb/linktable#P#p15 |        63.2757 |             308.4844 |        840.0000 |
| linkdb/counttable      |        61.5928 |             434.7695 |       1132.0000 |
| linkdb/nodetable       |        29.3900 |            3951.3359 |       5596.0000 |
+------------------------+----------------+----------------------+-----------------+
18 rows in set (0.00 sec)

mysql> 

innodb_page_size=16kでlz4の場合
mysql> select name, ((file_size-allocated_size)*100)/(file_size+1) as compressed_pct, allocated_size/(1024*1024) as allocated_size_in_mb, file_size/(1024*1024) as file_size_in_mb from information_schema.INNODB_SYS_TABLESPACES WHERE name like 'linkdb%';
+------------------------+----------------+----------------------+-----------------+
| name                   | compressed_pct | allocated_size_in_mb | file_size_in_mb |
+------------------------+----------------+----------------------+-----------------+
| linkdb/linktable#P#p0  |        55.4050 |             420.9766 |        944.0000 |
| linkdb/linktable#P#p1  |        56.7123 |             273.5781 |        632.0000 |
| linkdb/linktable#P#p2  |        53.2847 |             702.5977 |       1504.0000 |
| linkdb/linktable#P#p3  |        54.7643 |             499.4023 |       1104.0000 |
| linkdb/linktable#P#p4  |        53.4013 |             447.3477 |        960.0000 |
| linkdb/linktable#P#p5  |        53.9754 |             649.8672 |       1412.0000 |
| linkdb/linktable#P#p6  |        54.0529 |             948.3477 |       2064.0000 |
| linkdb/linktable#P#p7  |        55.7346 |             394.8477 |        892.0000 |
| linkdb/linktable#P#p8  |        55.5176 |             409.2383 |        920.0000 |
| linkdb/linktable#P#p9  |        56.7004 |             263.2617 |        608.0000 |
| linkdb/linktable#P#p10 |        53.5631 |             588.8203 |       1268.0000 |
| linkdb/linktable#P#p11 |        56.8499 |             241.6406 |        560.0000 |
| linkdb/linktable#P#p12 |        54.0886 |             304.8516 |        664.0000 |
| linkdb/linktable#P#p13 |        54.4187 |             661.8398 |       1452.0000 |
| linkdb/linktable#P#p14 |        54.2518 |             710.0117 |       1552.0000 |
| linkdb/linktable#P#p15 |        55.7080 |             370.2813 |        836.0000 |
| linkdb/counttable      |        59.3272 |             458.7891 |       1128.0000 |
| linkdb/nodetable       |        28.2260 |            3999.2461 |       5572.0000 |
+------------------------+----------------+----------------------+-----------------+
18 rows in set (0.00 sec)

mysql>

圧縮率だけ見るとzlibの方が良さそうです。なおALTER文でCOMPRESSION=noneに全てのテーブルを変更したあとにLinkBenchを1時間実行した場合、以下のようにcompressed_pctが当然のことながら減少しました。

innodb_page_size=16kでzlib->noneのベンチマーク実行後
mysql> select name, ((file_size-allocated_size)*100)/(file_size+1) as compressed_pct, allocated_size/(1024*1024) as allocated_size_in_mb, file_size/(1024*1024) as file_size_in_mb from inf
ormation_schema.INNODB_SYS_TABLESPACES WHERE name like 'linkdb%';
+------------------------+----------------+----------------------+-----------------+
| name                   | compressed_pct | allocated_size_in_mb | file_size_in_mb |
+------------------------+----------------+----------------------+-----------------+
| linkdb/linktable#P#p0  |        20.6476 |             831.6133 |       1048.0000 |
| linkdb/linktable#P#p1  |        22.5837 |             529.5273 |        684.0000 |
| linkdb/linktable#P#p2  |        17.8877 |            1468.1680 |       1788.0000 |
| linkdb/linktable#P#p3  |        18.6294 |            1025.2695 |       1260.0000 |
| linkdb/linktable#P#p4  |        17.1622 |             947.6641 |       1144.0000 |
| linkdb/linktable#P#p5  |        17.2568 |            1366.9180 |       1652.0000 |
| linkdb/linktable#P#p6  |        18.7089 |            1937.9805 |       2384.0000 |
| linkdb/linktable#P#p7  |        20.2312 |             781.7344 |        980.0000 |
| linkdb/linktable#P#p8  |        20.9004 |             806.8164 |       1020.0000 |
| linkdb/linktable#P#p9  |        21.3016 |             513.1133 |        652.0000 |
| linkdb/linktable#P#p10 |        18.2654 |            1219.4805 |       1492.0000 |
| linkdb/linktable#P#p11 |        20.0674 |             489.1875 |        612.0000 |
| linkdb/linktable#P#p12 |        15.6215 |             664.9023 |        788.0000 |
| linkdb/linktable#P#p13 |        19.8355 |            1333.9375 |       1664.0000 |
| linkdb/linktable#P#p14 |        20.3784 |            1410.8945 |       1772.0000 |
| linkdb/linktable#P#p15 |        19.2842 |             749.0430 |        928.0000 |
| linkdb/counttable      |         0.1590 |            2168.5469 |       2172.0000 |
| linkdb/nodetable       |         0.2112 |            7771.5547 |       7788.0000 |
+------------------------+----------------+----------------------+-----------------+
18 rows in set (0.01 sec)

mysql>

innodb_page_size=16kでlz4->noneのベンチマーク実行後
mysql> select name, ((file_size-allocated_size)*100)/(file_size+1) as compressed_pct, allocated_size/(1024*1024) as allocated_size_in_mb, file_size/(1024*1024) as file_size_in_mb from information_schema.INNODB_SYS_TABLESPACES WHERE name like 'linkdb%';
+------------------------+----------------+----------------------+-----------------+
| name                   | compressed_pct | allocated_size_in_mb | file_size_in_mb |
+------------------------+----------------+----------------------+-----------------+
| linkdb/linktable#P#p0  |        19.1485 |             847.3242 |       1048.0000 |
| linkdb/linktable#P#p1  |        21.0874 |             536.6055 |        680.0000 |
| linkdb/linktable#P#p2  |        16.2208 |            1494.6211 |       1784.0000 |
| linkdb/linktable#P#p3  |        17.1224 |            1044.2578 |       1260.0000 |
| linkdb/linktable#P#p4  |        14.9215 |             969.8945 |       1140.0000 |
| linkdb/linktable#P#p5  |        15.5323 |            1395.4063 |       1652.0000 |
| linkdb/linktable#P#p6  |        17.0397 |            1977.7734 |       2384.0000 |
| linkdb/linktable#P#p7  |        18.5834 |             797.8828 |        980.0000 |
| linkdb/linktable#P#p8  |        19.3214 |             822.9219 |       1020.0000 |
| linkdb/linktable#P#p9  |        20.0094 |             524.7383 |        656.0000 |
| linkdb/linktable#P#p10 |        16.7346 |            1242.3203 |       1492.0000 |
| linkdb/linktable#P#p11 |        18.5290 |             495.3438 |        608.0000 |
| linkdb/linktable#P#p12 |        14.0730 |             673.6680 |        784.0000 |
| linkdb/linktable#P#p13 |        18.2792 |            1353.2969 |       1656.0000 |
| linkdb/linktable#P#p14 |        18.2571 |            1451.7539 |       1776.0000 |
| linkdb/linktable#P#p15 |        17.4944 |             765.6523 |        928.0000 |
| linkdb/counttable      |         0.1593 |            2168.5391 |       2172.0000 |
| linkdb/nodetable       |         0.2220 |            7750.7578 |       7768.0000 |
+------------------------+----------------+----------------------+-----------------+
18 rows in set (0.00 sec)

mysql>

LinkBenchの実行

今回はinnodb_page_size=16kでzlib, lz4, noneで行いました。しかし、先に記載した通り、今回のデータ量ではinnodb_buffer_pool_sizeにデータ・INDEXが全て入ってしまう状態であったため明確な差が出ませんでした。これはwriteの時のみ圧縮すれば良い状態になってしまったためと考えられます。
DBの圧縮ではreadの展開の方が頻度が多く、writeのように1回だけ行えば良い訳では無いからです。そのためPart2ではバッファに載り切らない状態での測定を予定しています。

  • ベンチマーク環境
    • CentOS 7.1
    • 3.10.0-229.14.1.el7.x86_64
    • Intel(R) Xeon(R) CPU E5-2630 0 @ 2.30GHz x 2(HT有効)
    • DDR3 1333 MHz ECC 8GB x 8
    • Intel SSD 910 Series 400GB(200GB x 2, RAID0(md)) ext4を使用
    • NIC / Intel Corporation I350 Gigabit Network Connection(SupermicroのMB(X9DAE)のオンボード)
  • 実行コマンド
    • ./bin/linkbench -c config/MyConfig.properties -D maxtime=3600 -D requests=10000000 -D requesters=64 -r

innodb_page_cleanerを24に設定し、その他の設定は先日のはてダと同一です。

以下ベンチマーク結果です。

compress スコア
lz4 35316
zlib 33371
none 34673

なんかlz4が一番高くなりました。。。圧縮でwrite量が減ったのかな？、という謎。CPU使用率は各コアidleが30%ぐらいあったのでlz4のように高速性が売りなものだとこんな結果になるのかもしれません。

またlz4, zlibでデータを作成し、ALTERでCOMPRESSION=noneとした場合は以下のようになりました。

compress スコア
lz4->none 33848
zlib->none 34576

なんか不思議な感じです。

以下ベンチマーク中のグラフです。順番はzlib, lz4, zlib->none, lz4->none, noneの計5パターンです。








まとめ

使用についてはこれまでのInnoDBの圧縮よりは気軽に使える感じですが、Kernelが比較的新しいバージョンでないと使えないのが難点かと思います。
データ量がinnodb_buffer_pool_sizeに全て入るうちは明確な差が出ませんでした。Part2では100GB以上のデータを用意して傾向がどう変わるか見ていく予定です。

MySQL Casual Talks Vol.8 でMySQL 5.7とMariaDB 10.1の性能比較について発表しました

MySQL

はじめに

遅くなりましたが11/20(金)に開催されたMySQL Casual Talks Vol.8で以下スライドで発表しました。その内容の補足とかを載せておきます。

最近MariaDB 10.1がGAになり、MySQL 5.7がGAになり、MariaDBってどうなん？、とか聞かれる事が増えたためとりあえずベンチマークで性能比較してみよう、が発端でした。MariaDBの10系はMySQL 5.5をベースにしているということで正直あまり期待していなかったのですが、起動時のログを見るとInnoDBはPerconaのXtraDB5.6ベースだったのでPercona5.6と同じ傾向かなぁという想像で実施しましたが概ね同じ傾向となりました。

まず、私はMariaDB10系はまともに触ったことがなかったので変だなと思った所を色々書いていきます。

MariaDBの変だと思った所
  1. innodb_page_size=4096にするとやな感じのログが出る(mysql_install_dbの際)
  2. 一通りデータ・INDEXがバッファに載った後も謎のreadが発生し続ける

まずは1.の変なログです。

InnoDB: Warning: innodb_page_size has been changed from default value 16384 to 4096d. (###EXPERIMENTAL### operation)
2015-11-30 01:58:57 2ab81a63f2a0 InnoDB: innodb-page-size has been changed from the default value 16384 to 4096
(snip)
2015-11-30  1:59:03 46976268028224 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1146: Table 'mysql.gtid_slave_pos' doesn't exist
InnoDB: Note: File system for file ./mysql/innodb_table_stats.ibd has file block size 4096 not supported for page_size 4096
InnoDB: Note: Using file block size 2048 for file ./mysql/innodb_table_stats.ibd
InnoDB: Note: File system for file ./mysql/innodb_index_stats.ibd has file block size 4096 not supported for page_size 4096
InnoDB: Note: Using file block size 2048 for file ./mysql/innodb_index_stats.ibd
InnoDB: Note: File system for file ./mysql/gtid_slave_pos.ibd has file block size 4096 not supported for page_size 4096
InnoDB: Note: Using file block size 2048 for file ./mysql/gtid_slave_pos.ibd
(snip)

一応これでも動くようですが凄く気持ち悪いです。少なくもとMySQL5.6, 5.7では出力されません。
ですので、今回発表の全てのベンチマークではデフォルトのinnodb_page_size=16384(16k)で実施しています。


続いて2.について。ディスクI/O状況のグラフです。左側がMySQL5.7、右側がMariaDB10.1となります。

MySQL5.7ではほぼほぼreadが発生しない状態になるのですが、MariaDBについては絶えず8〜9MB/secの読み取り(bio)が発生しています。pt-ioprofile等で見ましたが読み取り対象のファイルが分かりませんでした。dstat使うと間違いなくMariaDBのプロセスが読み取りを行っているのですが対象ファイルが完全に謎。知っている方がいましたら教えて下さい。

あと、MySQLとの互換性を頻繁に主張しているMariaDBですが、パラメータで見るとMySQL5.6にあったパラメータでもMariaDB10.1には存在しなかったりします。例えば以下のもの。

master_info_repository
relay_log_info_repository

InnoDBはXtraDB5.6系なのでInnoDB関連パラメータは割りと同じのあるかむしろ多いかなのですがInnoDB以外の所は5.5系ベースのためこういった存在しないパラメータが出てくるのだと思います。SQLベースだったら互換性保たれてるんですかね？

sysbenchの補足

今回select.luaとoltp.luaを使っていますがやっていることはほぼほぼ一緒です。以下luaファイルの中身の抜粋です。

select.luaの抜粋
function event(thread_id)
   local table_name
   table_name = "sbtest".. sb_rand_uniform(1, oltp_tables_count)
   rs = db_query("SELECT pad FROM ".. table_name .." WHERE id=" .. sb_rand(1, oltp_table_size))
end

oltp.luaの抜粋
   for i=1, oltp_point_selects do
      rs = db_query("SELECT c FROM ".. table_name .." WHERE id=" .. sb_rand(1, oltp_table_size))
   end

oltp.luaを見ると他にもクエリ発行する処理が書かれていますが今回実行したオプションでは上記のクエリのみの実行となります。Dimitriさんのブログ、MariaDBのブログではoltp.luaで実行してるようでしたので比較のために両方取った形になります。
oltp.luaは見たとおりforでぐるぐる回すので速くなったようです。

LinkBench実行についての補足

LinkBench実行前にmysqldプロセスを停止し、ファイルシステムキャッシュを開放しています。

echo 3 > /proc/sys/vm/drop_caches

ib_buffer_poolファイルも削除し、mysqld起動時のバッファの暖気は行わないようしています。

その他ベンチマーク時にはまった点

はまった点というかCentOSでは7系から採用されたsystemdに慣れていないせいではあるのですが、systemd経由で起動した場合、MySQL 5.7もMariaDB 10.1もmysqld_safeを経由しないため、これまでmysqld_safeで設定されていた項目を全てsystemdの起動設定ファイルに記載する必要があります。

今回行ったのは以下2点です。

  1. jemallocの使用
  2. numactlの使用

jemallocについて。mysqld_safeを経由する場合は [mysqld_safe]セクションにmalloc-lib=/lib64/libjemalloc.so.1のように書くことで指定出来たのですが、これを/etc/systemd/system/mysqld.service.d/service.conf等に書く必要があります。
またopen_files_limitパラメータについてもsystemdの起動設定ファイルにLimitNOFILEが記載されていて、LimitNOFILE以下に制限されてしまうので大きな値をopen_files_limitで指定している場合は変更する必要があります。以下記載例です。

[Service]
LimitNOFILE=65535
Environment="LD_PRELOAD=/lib64/libjemalloc.so.1"

systemdを使う場合の設定については本家のマニュアル(2.5.10 Managing MySQL Server with systemd)を参照して下さい。

numactlについて。NUMA環境ではSwap Insanity予防のためnumactl --interleave=allで起動する事を個人的に推奨しており、おそらくその他の方も行っていると思います。
MySQL 5.6、5.7の最新版ではinnodb_numa_interleaveというパラメータが増えているのでこれを有効にすればnumactl --interleave=allで起動したのとほぼ同様の効果が得られるため本家のMySQLではnumactlはあまり必要では無くなっています(InnoDBバッファプールのみinterleave=allになる)。

mysqld_safeを経由していた場合、Perconaでは[mysqld_safe]セクションにnuma-interleave=1としておくとnumactl --interleave=allで起動したのと同様の事を内部的に行ってくれるのですが、先にも記載した通りsystemd経由の場合はmysqld_safeは使われません。
そのためMariaDBでは/usr/lib/systemd/system/mariadb.serviceを以下のように書き換えて起動していました。変更箇所のみ抜粋したのが以下です。

#ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS
ExecStart=/usr/bin/numactl --interleave=all /usr/sbin/mysqld $MYSQLD_OPTS
公開されているベンチマーク結果について

OracleやPercona、MariaDBが公開しているベンチマーク結果がありますが、細かいパラメータやサーバ環境が載っていないものについては話半分というかそういう事もあるんだね、という程度でしか見ていません。実際に同じようなベンチマークを行い、同じ傾向になれば少なくとも自分が取得したH/W環境において、公開されているベンチマークの傾向については信用しても良いな、と判断しています。
MariaDBのブログの公式としてのベンチマーク結果(と捉えて問題無いと思いますが)は、正直あまり信用していません。何故かというと環境に関する公開情報がほとんどのケースで少なすぎるからです。

ぶっちゃけ人の取ったベンチマークなんて鵜呑みにするもんじゃない、と思っています(私の公開しているのもその程度の見方でOKです。信用出来ると思った場合は信用して下さい)。基本疑って掛かって下さい(特にベンダーが対抗して出したようなやつ)。そもそもベンチマークツールで取得した結果・傾向がそのままそれぞれのサービス環境で同じ傾向になる事なんてマレだと思います。

それでもMySQL5.7のreadwrite時の性能改善については特筆すべきものがあると感じています。performance_schemaからの傾向と実際のベンチマークからもそう感じています。ちょっと上手く取れてなかったようですが、以下のようになっています。今回も左(すごい狭い)がMySQL5.7、右がMariaDB10.1です。


取る際にしくったのかMySQLが狭くなってますが上のグラフがrwlockの待ち時間、下が待ちに入った回数です。とにかくrwlockの待ち時間減少が著しいです。MySQL5.7がsxlockと名前が変わっているのは5.7からS-lock、X-lockに加えてSX-lockが増えた結果、変更されたためのようです。
※この辺は前回のMyNA会のdimstatについてのスライドを見て頂いた方が良いかもしれません。但しMySQL 5.6と5.7の比較です。

その他としては秒間接続数なんかは環境問わずそれだけで速くなる改善かと思います。
MySQL Performance: Improved Connect/sec Rate in MySQL 5.7
MySQL 5.6, 5.7の比較でsysbenchのソースコードをいじってクエリ発行しないようにした比較ですがこちらも以前のMyNA会で計測しているので参考までに載せておきます。

その他ベンチマーク中にぶつぶつTwitterに書いていた内容を@yoku0825さんがまとめてくれてますので参考までに見ていただければと思います。
MySQLベンチマークおじさんがMySQL 5.7とMariaDB 10.1の比較をしていると聞いて

最後に

MariaDBのブログとDimitriさんのブログ、Perconaのブログで5.7との比較したベンチマークが載っていて、Dimitriさんのベンチマークの大半がCPUコア数が増えた際のスケーラビリティについて言及されています(物理72コアでもスケールする)。
そんなブログを掲載したらMariaDBはスライドに載せたコモディティハードウェア(4コア 64GBメモリ!?)でMariaDBの方がreadonlyだと速いと載せてきました。実際MariaDBの方が4コアだと速いようでしたがwhere id = Nのような主キーオンリーで速くても大半のサービスでは嬉しくないわけで。2倍とか3倍差があれば考慮しても良いかなと思いますが10%前後の差です。
※極論、主キー探査しかしないならKVSとか使えば良いわけで(データ量にもよるけど)。RDBの必要無いし。

最近はPCI-E SSD(iodriveとか)を使っている環境ではSlaveサーバはあくまでフェールオーバー用や集計用で、サービスの全てのクエリをMasterで行うケースも結構増えてきてるように感じています。その場合、当然readonlyな状況は滅多に発生しないためreadwriteで速い方を推奨されるかと思います。

Perconaは5.7でMySQL5.7の改善点がほぼ取り込まれると考えられるのでPerconaはスケーラビリティについても追随してくるだろうし、更に改善してくる可能性もあると思います。しかしMariaDBは10.1をMySQL 5.7がGAになる前に出したため、XtraDB5.7(？)を使用すると思われる次のバージョンまではそれなりのreadwriteが発生する環境でMySQL5.7に勝つことは無いと思います。
(GTIDのフォーマットも本家MySQLと違うしどうするんでしょうね？)

最後に主要な設定を載せておきます。

max_connections = 4096
query_cache_type = 0
query_cache_size = 0
performance_schema = ON
performance_schema_instrument='%sync%=on'

innodb_monitor_enable = 'all'

back_log = 1024

sync_binlog = 1
log-bin=mysql-bin
master_info_repository = TABLE
relay_log_info_repository = TABLE

innodb_strict_mode
innodb_file_format = Barracuda
innodb_buffer_pool_instances = 20
innodb_buffer_pool_size = 40G
innodb_log_buffer_size = 64M
innodb_log_file_size = 1G
innodb_log_files_in_group = 16
innodb_max_dirty_pages_pct = 90
innodb_max_dirty_pages_pct_lwm = 10
innodb_flush_method = O_DIRECT
innodb_thread_concurrency = 0
innodb_purge_threads = 4
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_flush_neighbors = 0 / 1
innodb_io_capacity = 5000 / 12000 / 18000
innodb_io_capacity_max = 8000 / 15000 / 23000
innodb_lru_scan_depth = 4000
innodb_open_files = 3000

innodb_print_all_deadlocks = 1

innodb_change_buffer_max_size = 10

innodb_checksums = 1
innodb_checksum_algorithm = crc32

innodb_adaptive_flushing = 1
innodb_adaptive_flushing_lwm = 10
innodb_adaptive_hash_index = 0 / 1
innodb_read_ahead_threshold = 0 / 56
innodb_sync_array_size = 24 / 32
innodb_autoextend_increment = 32
metadata_locks_hash_instances = 48

innodb_max_purge_lag_delay=1000000
innodb_max_purge_lag=100000

innodb_page_size=16k

loose-log_timestamps = SYSTEM
loose-innodb_buffer_pool_dump_pct = 100
loose-innodb_page_cleaners = 8
loose-innodb_numa_interleave = ON

skip-name-resolve

MyNA会でdimSTATについて話してきました

MySQL

お題の通り話してきました。
@kamipoさんOracle Ace認定おめでとうございますの会でした。

ハッシュタグ(#mysql_jp)見てると自分の話は誰得だったのか良く分からんなぁと。
こんなツールがあってこんなこと見れるんだよ、というのを知ってもらえたのなら十分かと思います。

dimSTATのインストール方法とかは以前のはてダに書いてあるので興味をもたれた方は参考にしてもらえると幸いです。

スライドの補足

5.7.8でパラメータの挙動の違いについて見てみた、の環境はLinkbenchのスコアから推測出切るとは思いますがxfs使っています。
本当はもうちょっと突き詰めたかったんですが時間が足りませんでした。

またデモの時に流したLinkbenchは
innodb_io_capacity = 55000
innodb_io_capacity_max = 60000
でどうなるか試した内容でした。結果のスコアは34905なのでやはり上げすぎない方が性能は出るようです。

その際にgeneral_log有効にしてもあまり遅くならなかったよ、と
話した部分がありますが、それはあくまでもバッファプールに
データが乗り切らなくなり、データに満遍なくアクセスがある
ケースです(ioが頻繁に発生する状況)。

データ・INDEXがバッファプールに全部載っている状態、または
アクセスされるデータに局所性がある場合はgeneral_logの待ちにより
全く性能がスケールしなくなります。
この辺についてはMySQL calualのSlackチャンネルの過去ログを見て
頂ければと思います。

innodb_monitor, performance_schemaの細かい部分はドキュメントもなく、
意味するところはソースのコメント見ないといけなかったりするのが
まだ残念な状態です。項目名から意味を類推しつつソース覗いてみて、
あとはベンチマーク実行して挙動をみてみる感じになっています。
※ソースをがっつり読める人はもうちょっと楽だと思います。

ext4の方がどうして遅いのかな、とfio + perf + Flame Graphsで載せましたが、
MySQL + perf + Flame Graphsじゃないのはベンチマーク中にperfをMySQLに対して
実行すると8割以上Kernel panicやら何も出力なく固まるやらしたためでした。
運良く取れたのもあるんですが、これが原因、というのが見やすい感じじゃ
なかったので載せてません。

writeonlyだとext4が遅いですが、readの場合はmutex_lock取らないので
readonlyならfioで計測されるiopsはext4もxfsとほぼ同じになります。
readwriteだと当然xfsが優勢です。



会場提供のGMOさん、主催のyoku0825さん、ありがとうございました。

dimSTATのインストール

MySQL

MySQLに関するブログを書かれている(というか中の人？)Dimitriさん作成のdimSTATのインストール手順についてまとめます。確認した環境は全てCentOS6(64bit)です。
URLはこちら(Dimitri (dim) Tools HOMEPAGE)。
Dimitriさんのブログはこちら(DimitriK's (dim) Weblog)

dimSTATはDimitriさんのブログに登場するグラフを生成してくれるリソースモニタです。
Performance Schemaのデータも割りと簡単にグラフ化出来るので性能測定の際には非常に便利なツールとなっています。

事前準備

compressコマンドのインストールとアカウント作成

インストール時にcompressコマンドが入っているかチェックされ、入っていない場合はパッケージに含まれるcompressコマンドがインストールされるようでしたので予めyumでインストールしておきます。

また、dim_STAT用のアカウントであるdimを作成しておきます。変更は可能ですので、違うアカウント名にしたい場合はインストール時に変更して下さい。

$ sudo yum install ncompress
$ sudo useradd -s /bin/bash -d /home/dim dim
$ sudo passwd dim

32bitな以下も必要なのでインストールされていない場合はインストール。32bit環境の場合は既に入っているかもしれません。

$ sudo yum install zlib.i686 libgcc.i686 libstdc++.i686 libX11.i686 ncurses-libs.i686

インストール後に何か上手く動いてないようでしたら/apps/httpd/etc/logs/error_logを見ると足りないライブラリが書かれていたりするの適宜追加しましょう。

出力例
/apps/mysql/bin/mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
どのパッケージに含まれるか調べる場合は以下のように。
$ sudo yum whatprovides */libncurses.so.5

dim_STATのインストール

dim_STATのダウンロードと展開
$ wget 'http://dimitrik.free.fr/dim_STAT-v90-linux-x86.tar'
$ tar xf dim_STAT-v90-linux-x86.tar
インストールの実行と起動

INSTALL.shでインストールを行います。以下の例ではHTTPのポート番号のみ8080へ変更しています。

$ cd dim_STAT-INSTALL
$ sudo ./INSTALL.sh
compress: ok


===========================================
** Starting dim_STAT Server INSTALLATION **
===========================================

HOSTNAME: [ホスト名]
      IP: [IPアドレス]
 DOMAINE: (none)

Is it correct? (y/n): y
**
** ATTENTION!
**
** On your host You have to assign a USER/GROUP pair as owner
** of all dim_STAT modules (default: dim/dim)

 User: dim
Group: dim

Is it correct? (y/n): y


**
** WARNING!!!
**
** On your host You have already created USER: dim
** This user will be the owner of all
** dim_STAT modules...
**
== /etc/passwd: =========================================================

dim:x:10002:10002::/home/dim:/bin/bash

=========================================================================

Is it correct? (y/n): y


======================================
** dim_STAT Directory Configuration **
======================================

** WebX root directory (5MB):
 => /WebX
 => /apps/WebX
 => /opt/WebX
 => /etc/WebX

 [/opt/WebX]:


** HOME directory for dim_STAT pkgs        [/apps]:
** TEMP directory                          [/tmp]:

** HTTP Server Port                        [80]: 8080
** DataBase Server Port                    [3306]:
** Default STAT-service Port               [5000]:


==================================================
** Process...
==================================================
=> Host                        : [ホスト名]
=> IP address                  : [IPアドレス]
=> Domain                      : (none)
=> User                        : dim
=> Group                       : dim
=> WebX root directory         : /opt/WebX
=> HOME directory              : /apps
=> TEMP directory              : /tmp
=> HTTP Server Port            : 8080
=> DataBase Server Port        : 3306
=> Default STAT-service Port   : 5000

Is it correct? (y/n): y


** !!
** !! !!! WARNING !!!
** !! ---------------
** !!
** !! ALL DATA will be DELETED!!! in: /apps/* !!!
** !! as well /WebX, /apps/WebX, /etc/WebX, /opt/WebX !!!
** !!
Delete all data? (y/n): y




** Cleanup /apps
** WebX Setup...
** dim_STAT Server extract...
** HTTP Server Setup...
** Database Server Setup...
** ADMIN/Tools Setup...
** TEMP directory...
** Permissions...
** Crontab Setup...


**
** INSTALLATION is finished!!!
**
May I create now a dim_STAT-Server start/stop script in /etc/rc*.d? (y/n): y
ln: creating symbolic link `/etc/rcS.d/K99dimstat' to `/apps/ADMIN/dim_STAT-Server': No such file or directory
============================================================================
 NOTE:
 =>
 => Please, set a password to the user dim
 => to enable crontab "clean up" procedure!..
 =>


** =========================================================================
**
**   You can start dim_STAT-Server now from /apps/ADMIN:
**
**   # cd /apps/ADMIN
**   # ./dim_STAT-Server start
**
**   and access homepage via Web browser - http://4a-p01-b5:8080
**
**   To collect stats from any Solaris, Linux or MacOSX machines just
**   install & start on them a correcponding [STAT-service] package...
**
** Enjoy! ;-)
**
** -Dimitri
** =========================================================================


$

$ ls -dl /etc/rc?.d/*dimstat
lrwxrwxrwx 1 root root 27 May 23 09:02 /etc/rc0.d/K99dimstat -> /apps/ADMIN/dim_STAT-Server
lrwxrwxrwx 1 root root 27 May 23 09:02 /etc/rc1.d/K99dimstat -> /apps/ADMIN/dim_STAT-Server
lrwxrwxrwx 1 root root 27 May 23 09:02 /etc/rc2.d/S99dimstat -> /apps/ADMIN/dim_STAT-Server
$
$ /etc/rc2.d/S99dimstat start
================[ dim_STAT-Server: start ]================

*
* MySQL Database Server
*
  => Log output  : /apps/mysql/data/mysqld.log
  => Local socket: /apps/mysql/data/mysql.sock
  => Admin Access: root# /apps/mysql/bin/mysql -S /apps/mysql/data/mysql.sock

Starting...
150523 09:07:36 mysqld_safe Logging to '/apps/mysql/data/mysqld.log'.
150523 09:07:36 mysqld_safe Starting mysqld daemon with databases from /apps/mysql/data
Done.
Starting HTTP server from: /apps/httpd
Done.
================[ dim_STAT-Server: start -- done. ]================
$

アップデートファイルの適用(2015/08/08追加)

ここまでの作業でCoreとなるパッケージはインストールが完了しました。続いてアップデートファイルを適用します。
ダウンロードファイル一覧

$ cd /opt/WebX/
$ wget 'http://dimitrik.free.fr/Core_Updates/WebX_apps-v90-u14.tgz'
$ tar zxf WebX_apps-v90-u14.tgz

以上でアップデートファイルの適用は完了です。

STAT-service

dim_STATでリソース取得を行う場合、取得対象となる全てのサーバでSTAT-serviceを起動する必要があります。

STAT-serviceのダウンロード、インストールと設定

MySQLのアドオンも含まれてるSTATsrvをインストールします。

$ wget http://dimitrik.free.fr/STATsrv-Linux-x86-mysql.tgz
$ tar zxf STATsrv-Linux-x86-mysql.tgz
$ sudo mv -i STATsrv /etc/.

MySQLのリソースも取得したい場合は取得したいサーバで以下の設定を行います。

$ cd /etc/STATsrv/bin
$ sudo cp -pi .env-mysql .env-mysql.ORG
$ sudo vi .env-mysql
$ cat .env-mysql
# Set connection params:
User=[ユーザ名]
Passwd=[パスワード]
Host=[IPアドレス]:[ポート番号]

export User Passwd Host

$

.env-mysqlの設定例としては以下のようになります。

# Set connection params:
User=dim
Passwd=dim-pw
Host=192.168.1.100:3306

export User Passwd Host

リソース取得を行いたいMySQLで.env-mysqlで設定したアカウントを作成しておきます。
権限はなんとなくPercona Templateと合わせています。見てる感じ足りてそうですが不足してそうなら足して下さい。

GRANT SUPER,PROCESS ON *.* to dim@'192.168.1.0/255.255.255.0' IDENTIFIED BY 'dim-pw';

リソース取得を行いたい各サーバでプロセスを起動し、準備完了です。

$ cd /etc/STATsrv/
$ sudo ./STAT-service start
Start STAT-service...
   port: 5000
   access log: /etc/STATsrv/log/access.log
$

簡単な初期設定例

dimSTATをインストールしたマシンのIPアドレスを指定する形で http://[インストールIPアドレス]:8080 でアクセスすると以下画面が表示されます。

まずはWelcome!をクリックして以下の画面へ移動します。

先にリソースを取得するサーバを追加する前にアドオンを追加しておきます。

画面下部にMySQL関連のものが3つあるので全部追加します。

終わったら画面右上の[Home]をクリックしてTOPに戻り、モニタするサーバを追加します。[Start New Collect]をクリックします。
NewにIPアドレスなりホスト名を入力して[Continue...]をクリックします。

取得したい対象をチェックし、[Start STAT(S) Collect NOW!!!]をクリックしてモニタリングを開始します。

再び[Home]をクリックしてTOPに戻り、[Analyze]をクリックします。
以下画面になるので特に弄らないで[Analyze]をクリック。

追加したサーバを選択して、分かりやすいのでまずは[Bookmark(s)]をクリックします。

以下画面が表示されるのでとりあえず[InnoDB_HistLen]をクリックします。

すると以下のようにグラフが表示されます。MySQL起動後何もしていないのでグラフはまっすぐです。

割とちゃんと描画しているサンプルは以下のようになります。

おわりに

細かい解説すると切りが無いのですが、とりあえず使うのであれば以上で十分かと思います。
my.cnfに記載するPerformance Schemaの設定は以下で良いかと思います。

performance_schema = ON
performance_schema_instrument='%sync%=on'

dimSTATで起動するApacheがかなり古いので、iptables等で適切にアクセス制御を行う、または127.0.0.1にバインドして起動させ、nginxとかでProxyさせた方が良いと思います。完全にプライベートな環境であればそこまで神経質にならなくても良いかもしれませんが、不特定多数からアクセスされる可能性のあるサーバの場合は気をつけた方が良さそうです。

個人的にはPerformance Schema関連をお手軽にグラフに出来るのでLinkBenchと合わせて使い始めています。ガチサービス中のマシンでは使ってないのでもし使う場合は十分に確認の上、ご利用下さい。

LinkBenchのインストールと使用について

MySQL

気づいたら1年以上書いてなかった。。。

最近は色々なPCI-E SSDのベンチマークを取る機会が多くなっていて、tpcc-mysqlだと負荷不足な感じなのと、異なる傾向のI/O負荷の高いベンチマークを取ってみたかったので以前から気になっていたFacebook製のLinkBenchを使い始めています(LinkBenchのgithub)。
今回はベンチマーククライアントに使っているOSがCentOS5系だったのでCentOS5系でのインストール方法について記載します。基本的にはCentOS6系でも変わらないと思います。

またgithubに書かれている内容を元に、実際にやってみた内容をまとめています。

事前準備

Javaとmavenインストール及び設定

この辺はお好みでインストール場所とかは変更して下さい。
Javaは7以上が必要なようなのでCentOS5系ではyumからインストールしています。

$ sudo yum install java-1.7.0-openjdk java-1.7.0-openjdk-devel
$ wget http://ftp.riken.jp/net/apache/maven/maven-3/3.3.3/binaries/apache-maven-3.3.3-bin.tar.gz
$ tar zxf apache-maven-3.3.3-bin.tar.gz
$ sudo mv -i apache-maven-3.3.3 /usr/local/.
JAVA_HOMEの設定とmavenへのPATH登録

LinkBenchのインストールの際に必要になるので以下のように環境変数を設定してください。適宜.bash_profileとかに書いておく事が推奨です。

$ export JAVA_HOME=/usr/lib/jvm/jre-1.7.0-openjdk.x86_64
$ export PATH=/usr/local/apache-maven-3.3.3/bin:$PATH

LinkBenchのインストール

例によってgitコマンドで取得してmvnコマンドでインストール(ビルド?)します。すみませんがmavenよく知りません。。。

$ git clone https://github.com/facebook/linkbench.git
$ cd linkbench
$ mvn clean package

試してないですが、テストを限定する場合

$ mvn clean package -P fast-test

テストをスキップする場合

$ mvn clean package -DskipTests

とするようです。

以下が出ればインストール成功みたいです。

[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 07:29 min
[INFO] Finished at: 2015-05-13T01:59:05+09:00
[INFO] Final Memory: 80M/1758M
[INFO] ------------------------------------------------------------------------
実行確認
$ ./bin/linkbench
Using java at: /usr/lib/jvm/jre-1.7.0-openjdk.x86_64/bin/java
Did not select benchmark mode
usage: linkbench [-c <file>] [-csvstats <file>] [-csvstream <file>] [-D
       <property=value>] [-L <file>] [-l] [-r]
 -c <file>                       Linkbench config file
 -csvstats,--csvstats <file>     CSV stats output
 -csvstream,--csvstream <file>   CSV streaming stats output
 -D <property=value>             Override a config setting
 -L <file>                       Log to this file
 -l                              Execute loading stage of benchmark
 -r                              Execute request stage of benchmark
$

MySQLの準備

ベンチマークを行うハードウェアで動作するMySQLで以下のようにデータベース作成等、準備を行います。まずは以下を実行します。

create database linkdb;
use linkdb;

CREATE TABLE `linktable` (
  `id1` bigint(20) unsigned NOT NULL DEFAULT '0',
  `id2` bigint(20) unsigned NOT NULL DEFAULT '0',
  `link_type` bigint(20) unsigned NOT NULL DEFAULT '0',
  `visibility` tinyint(3) NOT NULL DEFAULT '0',
  `data` varchar(255) NOT NULL DEFAULT '',
  `time` bigint(20) unsigned NOT NULL DEFAULT '0',
  `version` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (link_type, `id1`,`id2`),
  KEY `id1_type` (`id1`,`link_type`,`visibility`,`time`,`id2`,`version`,`data`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 PARTITION BY key(id1) PARTITIONS 16;

CREATE TABLE `counttable` (
  `id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `link_type` bigint(20) unsigned NOT NULL DEFAULT '0',
  `count` int(10) unsigned NOT NULL DEFAULT '0',
  `time` bigint(20) unsigned NOT NULL DEFAULT '0',
  `version` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,`link_type`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `nodetable` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `type` int(10) unsigned NOT NULL,
  `version` bigint(20) unsigned NOT NULL,
  `time` int(10) unsigned NOT NULL,
  `data` mediumtext NOT NULL,
  PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

続いてベンチマーク用のMySQLアカウントを作成します。リモートからベンチマークを実行する場合は適宜localhostを192.168.1.0/255.255.255.0とかに変更して下さい。

CREATE USER 'linkbench'@'localhost' IDENTIFIED BY 'パスワード';
GRANT ALL ON linkdb.* TO 'linkbench'@'localhost';

最小限の設定

config/FBWorkload.propertiesファイルで最小限の設定(接続情報)を記載します 。その他、負荷に関する設定も記述されています。
またconfig/FBWorkload.propertiesにはデータ量に関する設定も記述されています。

$ cp config/LinkConfigMysql.properties config/MyConfig.properties
$ vi config/MyConfig.properties
$ diff -U3 config/LinkConfigMysql.properties config/MyConfig.properties
--- config/LinkConfigMysql.properties   2015-05-13 01:42:23.000000000 +0900
+++ config/MyConfig.properties  2015-05-13 05:17:50.000000000 +0900
@@ -27,9 +27,9 @@
 nodestore = com.facebook.LinkBench.LinkStoreMysql

 # MySQL connection information
-host = yourhostname.here
-user = MySQLuser
-password = MySQLpass
+host = [IPアドレスorホストネーム]
+user = linkbench
+password = [パスワード]
 port = 3306
 # dbid: the database name to use
 dbid = linkdb
$

ベンチマーク用データのロード

以下コマンドでベンチマーク用のデータをロードします。
innodb_page_size=4kの場合、約12GBのデータが作成されます。

$ ./bin/linkbench -c config/MyConfig.properties -l

事前にMySQLに接続して以下を実行する事でロード時間の短縮が行えます。

use linkdb;
alter table linktable drop key `id1_type`;
set global innodb_flush_log_at_trx_commit = 2;
set global sync_binlog = 0;

ロードが終わったら以下コマンドを実行して環境を元に戻すのを忘れないで下さい。

set global innodb_flush_log_at_trx_commit = 1;
set global sync_binlog = 1;
alter table linktable add key `id1_type`
  (`id1`,`link_type`,`visibility`,`time`,`id2`,`version`,`data`);

INDEXも貼り直した状態で以下のファイルサイズで作成されました。

$ ls -lh /var/lib/mysql/linkdb/
total 12G
-rw-rw---- 1 mysql mysql 8.5K May 13 05:12 counttable.frm
-rw-rw---- 1 mysql mysql 584M May 13 05:30 counttable.ibd
-rw-rw---- 1 mysql mysql   61 May 13 05:12 db.opt
-rw-rw---- 1 mysql mysql 8.6K May 13 05:33 linktable.frm
-rw-rw---- 1 mysql mysql   88 May 13 05:33 linktable.par
-rw-rw---- 1 mysql mysql 400M May 13 05:33 linktable#P#p0.ibd
-rw-rw---- 1 mysql mysql 548M May 13 05:35 linktable#P#p10.ibd
-rw-rw---- 1 mysql mysql 300M May 13 05:35 linktable#P#p11.ibd
-rw-rw---- 1 mysql mysql 440M May 13 05:35 linktable#P#p12.ibd
-rw-rw---- 1 mysql mysql 364M May 13 05:35 linktable#P#p13.ibd
-rw-rw---- 1 mysql mysql 668M May 13 05:35 linktable#P#p14.ibd
-rw-rw---- 1 mysql mysql 376M May 13 05:36 linktable#P#p15.ibd
-rw-rw---- 1 mysql mysql 380M May 13 05:33 linktable#P#p1.ibd
-rw-rw---- 1 mysql mysql 676M May 13 05:33 linktable#P#p2.ibd
-rw-rw---- 1 mysql mysql 324M May 13 05:33 linktable#P#p3.ibd
-rw-rw---- 1 mysql mysql 416M May 13 05:33 linktable#P#p4.ibd
-rw-rw---- 1 mysql mysql 296M May 13 05:34 linktable#P#p5.ibd
-rw-rw---- 1 mysql mysql 832M May 13 05:34 linktable#P#p6.ibd
-rw-rw---- 1 mysql mysql 344M May 13 05:34 linktable#P#p7.ibd
-rw-rw---- 1 mysql mysql 432M May 13 05:34 linktable#P#p8.ibd
-rw-rw---- 1 mysql mysql 352M May 13 05:34 linktable#P#p9.ibd
-rw-rw---- 1 mysql mysql 8.5K May 13 05:26 nodetable.frm
-rw-rw---- 1 mysql mysql 3.6G May 13 05:31 nodetable.ibd
$

config/FBWorkload.properties の maxid1 = 200000001 にすると218GBのデータが作成されました。

$ du -sch /var/lib/mysql/linkdb/
218G    /var/lib/mysql/linkdb/
218G    total
$
$ ls -lh /var/lib/mysql/linkdb/
total 218G
-rw-rw---- 1 mysql mysql 8.5K May 13 05:43 counttable.frm
-rw-rw---- 1 mysql mysql  12G May 13 06:20 counttable.ibd
-rw-rw---- 1 mysql mysql   61 May 13 05:43 db.opt
-rw-rw---- 1 mysql mysql 8.6K May 13 16:35 linktable.frm
-rw-rw---- 1 mysql mysql   88 May 13 16:35 linktable.par
-rw-rw---- 1 mysql mysql 6.6G May 13 16:43 linktable#P#p0.ibd
-rw-rw---- 1 mysql mysql 6.0G May 13 18:17 linktable#P#p10.ibd
-rw-rw---- 1 mysql mysql  12G May 13 18:31 linktable#P#p11.ibd
-rw-rw---- 1 mysql mysql 5.4G May 13 18:37 linktable#P#p12.ibd
-rw-rw---- 1 mysql mysql 9.4G May 13 18:48 linktable#P#p13.ibd
-rw-rw---- 1 mysql mysql 5.9G May 13 18:54 linktable#P#p14.ibd
-rw-rw---- 1 mysql mysql  14G May 13 19:11 linktable#P#p15.ibd
-rw-rw---- 1 mysql mysql 6.0G May 13 16:50 linktable#P#p1.ibd
-rw-rw---- 1 mysql mysql 5.7G May 13 16:56 linktable#P#p2.ibd
-rw-rw---- 1 mysql mysql  18G May 13 17:17 linktable#P#p3.ibd
-rw-rw---- 1 mysql mysql 5.6G May 13 17:23 linktable#P#p4.ibd
-rw-rw---- 1 mysql mysql 9.9G May 13 17:34 linktable#P#p5.ibd
-rw-rw---- 1 mysql mysql 5.7G May 13 17:40 linktable#P#p6.ibd
-rw-rw---- 1 mysql mysql  13G May 13 17:55 linktable#P#p7.ibd
-rw-rw---- 1 mysql mysql 6.4G May 13 18:02 linktable#P#p8.ibd
-rw-rw---- 1 mysql mysql 8.0G May 13 18:11 linktable#P#p9.ibd
-rw-rw---- 1 mysql mysql 8.5K May 13 05:44 nodetable.frm
-rw-rw---- 1 mysql mysql  71G May 13 07:31 nodetable.ibd
$

LinkBenchの実行

以下コマンドでベンチマークを実行します。デフォルトでは200接続で負荷を掛ける事になります(requesters = 100だけど接続数は200のようでした)。
config/MyConfig.propertiesの requesters = 100 を編集する事で接続数は変更可能です。実行時にオプションとして渡す事も出来ます。

$ ./bin/linkbench -c config/MyConfig.properties -r

requestersを50として実行する場合は以下のようになります。

$ ./bin/linkbench -c config/MyConfig.properties -D requesters=50 -r

また、10分間のwarmupを行う場合は以下のようになります。設定ファイルではwarmup_time = 0でデフォルト0秒です。

$ ./bin/linkbench -c config/MyConfig.properties -D warmup_time=600 -r

スレッド毎にもレポートは出ますが、サマリーとしては以下のような出力になります。それぞれのレイテンシーについての出力もあります(時間は削除しています)。

[main]: ADD_NODE count = 1286123  p25 = [2,3]ms  p50 = [4,5]ms  p75 = [8,9]ms  p95 = [25,26]ms  p99 = [47,48]ms  max = 350.06ms  mean = 7.336ms
[main]: UPDATE_NODE count = 3680962  p25 = [2,3]ms  p50 = [4,5]ms  p75 = [9,10]ms  p95 = [27,28]ms  p99 = [48,49]ms  max = 419.252ms  mean = 8.255ms
[main]: DELETE_NODE count = 505534  p25 = [2,3]ms  p50 = [4,5]ms  p75 = [8,9]ms  p95 = [25,26]ms  p99 = [46,47]ms  max = 360.033ms  mean = 7.705ms
[main]: GET_NODE count = 6468958  p25 = [0.4,0.5]ms  p50 = [1,2]ms  p75 = [3,4]ms  p95 = [10,11]ms  p99 = [18,19]ms  max = 143.47ms  mean = 2.753ms
[main]: ADD_LINK count = 4493508  p25 = [4,5]ms  p50 = [7,8]ms  p75 = [20,21]ms  p95 = [55,56]ms  p99 = [90,91]ms  max = 896.368ms  mean = 15.965ms
[main]: DELETE_LINK count = 1495732  p25 = [0.5,0.6]ms  p50 = [1,2]ms  p75 = [6,7]ms  p95 = [39,40]ms  p99 = [79,80]ms  max = 609.526ms  mean = 7.77ms
[main]: UPDATE_LINK count = 4004540  p25 = [4,5]ms  p50 = [7,8]ms  p75 = [20,21]ms  p95 = [53,54]ms  p99 = [89,90]ms  max = 902.957ms  mean = 15.641ms
[main]: COUNT_LINK count = 2440215  p25 = [0.2,0.3]ms  p50 = [0.4,0.5]ms  p75 = [1,2]ms  p95 = [6,7]ms  p99 = [13,14]ms  max = 113.011ms  mean = 1.335ms
[main]: MULTIGET_LINK count = 263447  p25 = [0.2,0.3]ms  p50 = [0.3,0.4]ms  p75 = [0.7,0.8]ms  p95 = [4,5]ms  p99 = [12,13]ms  max = 442.304ms  mean = 1.144ms
[main]: GET_LINKS_LIST count = 25360981  p25 = [0.2,0.3]ms  p50 = [0.4,0.5]ms  p75 = [0.7,0.8]ms  p95 = [4,5]ms  p99 = [13,14]ms  max = 2795.329ms  mean = 1.21ms
[main]: REQUEST PHASE COMPLETED. 50000000 requests done in 2461 seconds. Requests/second = 20311

ベンチマーク後のファイルサイズは以下のようになりました。

$ du -sch /var/lib/mysql/linkdb/
246G    /var/lib/mysql/linkdb/
$
$ ls -lh /var/lib/mysql/linkdb/
total 246G
-rw-rw---- 1 mysql mysql 8.5K May 13 05:43 counttable.frm
-rw-rw---- 1 mysql mysql  13G May 14 03:36 counttable.ibd
-rw-rw---- 1 mysql mysql   61 May 13 05:43 db.opt
-rw-rw---- 1 mysql mysql 8.6K May 13 16:35 linktable.frm
-rw-rw---- 1 mysql mysql   88 May 13 16:35 linktable.par
-rw-rw---- 1 mysql mysql 7.7G May 14 03:36 linktable#P#p0.ibd
-rw-rw---- 1 mysql mysql 7.1G May 14 03:36 linktable#P#p10.ibd
-rw-rw---- 1 mysql mysql  14G May 14 03:36 linktable#P#p11.ibd
-rw-rw---- 1 mysql mysql 6.4G May 14 03:36 linktable#P#p12.ibd
-rw-rw---- 1 mysql mysql  12G May 14 03:36 linktable#P#p13.ibd
-rw-rw---- 1 mysql mysql 7.0G May 14 03:36 linktable#P#p14.ibd
-rw-rw---- 1 mysql mysql  16G May 14 03:36 linktable#P#p15.ibd
-rw-rw---- 1 mysql mysql 7.0G May 14 03:36 linktable#P#p1.ibd
-rw-rw---- 1 mysql mysql 6.8G May 14 03:36 linktable#P#p2.ibd
-rw-rw---- 1 mysql mysql  21G May 14 03:36 linktable#P#p3.ibd
-rw-rw---- 1 mysql mysql 6.7G May 14 03:36 linktable#P#p4.ibd
-rw-rw---- 1 mysql mysql  12G May 14 03:36 linktable#P#p5.ibd
-rw-rw---- 1 mysql mysql 6.8G May 14 03:36 linktable#P#p6.ibd
-rw-rw---- 1 mysql mysql  16G May 14 03:36 linktable#P#p7.ibd
-rw-rw---- 1 mysql mysql 7.5G May 14 03:36 linktable#P#p8.ibd
-rw-rw---- 1 mysql mysql 9.5G May 14 03:36 linktable#P#p9.ibd
-rw-rw---- 1 mysql mysql 8.5K May 13 05:44 nodetable.frm
-rw-rw---- 1 mysql mysql  73G May 14 03:36 nodetable.ibd
$

終わりに

その他色々細かい設定もあるみたいです。今の所はほぼ初期の設定で使用していますがデフォルトのrequesters = 100だと相当の負荷がかかります。HDDでは5とかに減らす事をお勧めします(SATAのHDDとか100で負荷かけたらすぐ壊れそう)。感覚的にはSSDなら32ぐらいから試すと良いと思います。

ベンチマークの結果は所詮目安ですが、チューニングの勉強にもなるので色々やってみると面白いですよ。

performance_schemaによる秒間接続数の性能差

MySQL

前回はperformance_schema(以下p_s)の無効、有効、有効でsetup_instrumentsテーブルのENABLED列を全てNOにした場合の性能測定を行いました。こちらは結果としてsetup_instrumentsテーブルのENABLED列を全てNOにするとp_sをOFFにした場合とあまり性能が変わらない所まで改善する事が確認出来ました。

p_sが有効にすると性能が下がるというのは松信さんのブログでも書かれているのでご存知の方も多いかと思います。今回はsetup_instrumentsテーブルの設定のより秒間接続数に差が出るか確認を行いました。

ベンチマーク環境は基本前回と同じです。但し、リモートからTCPにより接続する一般的なWEB・DBサーバの環境となるようにしています。今回はNICの情報等も追記しました。

今回も同様に設定ファイルのperformance_schema=ON/OFFを切り替えてテストしています。sysbenchのバージョンは松信さんが実行しているであろうものと同じ0.4.12を使用し、オプションも基本同じ設定としています。oltp-table-sizeのみ変更していますがoltp-dist-type=specialなのであまり意味は無いかもしれません。

ベンチマークのオプション

今回は1パターンしか行っていないので以下内容を設定変更後、MySQLを再起動し、特にバッファに載せる処理は行わず、即時実行としています。

$ /usr/local/sysbench-0.4.12/bin/sysbench \
  --test=oltp \
  --oltp-table-size=4500000 \
  --max-requests=1000000 \
  --mysql-table-engine=innodb \
  --db-ps-mode=disable \
  --mysql-engine-trx=yes \
  --oltp-read-only \
  --oltp-skip-trx \
  --oltp-dist-type=special \
  --oltp-connect-delay=0 \
  --oltp-reconnect-mode=query \
  --db-driver=mysql \
  --mysql-user=sbtest \
  --mysql-password=パスワード \
  --mysql-db=sbtest \
  --mysql-host=サーバIPアドレス \
  --mysql-port=3306 \
  --num-threads=100 \
  run

ベンチマークパターン

今回は以下4パターンで取得しています。接続数関連なのでthread_cache_size=0にしたパターンも行っています。

  • ケース1: p_s OFF
  • ケース2: p_s OFF(thread_cache_size=0)
  • ケース3: p_s ON (setup_instrumentsテーブルのENABLEDが全てOFF)
  • ケース4: p_s ON (setup_instrumentsテーブルのENABLEDが全てOFF, thread_cache_size=0)

ベンチマーク結果

秒間接続数
ケース1 19428
ケース2 15971
ケース3 16575
ケース4 14718

まとめ

残念ながら秒間接続数はsetup_instrumentsテーブルに関わらず減少することが分かりました。thread_cache_sizeが十分に大きければthread_cache_size=0でp_s OFFよりは速いようです。

上記のスコアはtpsにすると1000〜1300程度になります。使用しているサーバに対してsysbench 0.5でUNIX ドメインソケット経由でかつ接続を維持する場合は5600〜6000tpsぐらい出るのでネットワーク越しだとだいぶ接続の負荷により性能が落ちる事が分かります(persistentは可能なら使うべき)。

TCP経由でリモートから、接続維持(--oltp-reconnect-modeがデフォ)だとどのぐらい出るのか気になる方もいるかもしれませんが、1GbpsのNICだと帯域を先に使い切ってしまい正確な値は分かっていません。帯域を使い切っている状態であっても4000tpsぐらいは出ていたと記憶しています。

秒間で10kを超える接続/切断が行われる環境はそう多くないとは思いますがこういった所まで気を配る必要がある環境であればp_sはOFFにした方が良いでしょう。

おまけ

ベンチマーク中にmpstat -P ALLすると以下のようになります。

サーバ側
03:46:03 AM  CPU    %usr   %nice    %sys %iowait    %irq   %soft  %steal  %guest   %idle
03:46:06 AM  all   28.67    0.00    8.97    0.08    0.00    6.19    0.00    0.00   56.09
03:46:06 AM    0   52.20    0.00   13.56    0.00    0.00   19.32    0.00    0.00   14.92
03:46:06 AM    1   52.38    0.00   13.27    0.34    0.00   17.69    0.00    0.00   16.33
03:46:06 AM    2   50.17    0.00   13.22    0.00    0.00   18.98    0.00    0.00   17.63
03:46:06 AM    3   48.79    0.00   14.88    0.00    0.00    0.00    0.00    0.00   36.33
03:46:06 AM    4   51.19    0.00   11.86    0.00    0.00   20.00    0.00    0.00   16.95
03:46:06 AM    5   50.51    0.00   11.95    0.00    0.00   18.43    0.00    0.00   19.11
03:46:06 AM    6   29.11    0.00   12.67    0.00    0.00    0.00    0.00    0.00   58.22
03:46:06 AM    7   23.21    0.00   10.58    0.00    0.00    0.00    0.00    0.00   66.21
03:46:06 AM    8   17.75    0.00    6.48    0.00    0.00    0.00    0.00    0.00   75.77
03:46:06 AM    9   11.74    0.00    4.70    0.00    0.00    0.00    0.00    0.00   83.56
03:46:06 AM   10    7.43    0.00    3.72    0.00    0.00    0.00    0.00    0.00   88.85
03:46:06 AM   11    4.71    0.00    2.69    0.00    0.00    0.00    0.00    0.00   92.59
03:46:06 AM   12   41.55    0.00   12.16    0.34    0.00    0.00    0.00    0.00   45.95
03:46:06 AM   13   37.97    0.00   11.19    0.00    0.00    0.00    0.00    0.00   50.85
03:46:06 AM   14   35.27    0.00    9.59    0.34    0.00    0.00    0.00    0.00   54.79
03:46:06 AM   15   45.08    0.00   10.51    0.34    0.00   18.64    0.00    0.00   25.42
03:46:06 AM   16   50.17    0.00   10.51    0.00    0.00   17.29    0.00    0.00   22.03
03:46:06 AM   17   47.62    0.00   11.22    0.00    0.00   18.71    0.00    0.00   22.45
03:46:06 AM   18   10.51    0.00    8.81    0.00    0.00    0.00    0.00    0.00   80.68
03:46:06 AM   19   11.19    0.00   11.86    0.00    0.00    0.00    0.00    0.00   76.95
03:46:06 AM   20    1.34    0.00    0.67    0.00    0.00    0.00    0.00    0.00   97.99
03:46:06 AM   21    5.32    0.00    4.98    0.00    0.00    0.00    0.00    0.00   89.70
03:46:06 AM   22    4.70    0.00    5.37    0.00    0.00    0.00    0.00    0.00   89.93
03:46:06 AM   23    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00  100.00

クライアント側
03:45:57 AM  CPU    %usr   %nice    %sys %iowait    %irq   %soft  %steal  %guest   %idle
03:46:00 AM  all   16.18    0.00   31.05    0.04    0.00   20.08    0.00    0.59   32.06
03:46:00 AM    0   13.04    0.00   24.41    0.00    0.00   42.47    0.00    1.00   19.06
03:46:00 AM    1   15.59    0.00   30.17    0.00    0.00   27.46    0.00    0.34   26.44
03:46:00 AM    2   14.63    0.00   28.91    0.00    0.00   26.53    0.00    1.36   28.57
03:46:00 AM    3   16.39    0.00   29.43    0.00    0.00   25.08    0.00    0.00   29.10
03:46:00 AM    4   18.58    0.00   36.49    0.00    0.00    8.78    0.00    0.68   35.47
03:46:00 AM    5   17.73    0.00   35.79    0.00    0.00    7.36    0.00    0.33   38.80
03:46:00 AM    6   17.51    0.00   33.00    0.00    0.00    6.40    0.00    0.67   42.42
03:46:00 AM    7   15.67    0.00   29.67    0.33    0.00   17.00    0.00    0.67   36.67

performance_schemaのsetup_instrumentsテーブルによる性能差

MySQL

先日のMyNA会(2013年7月)の際に@sheeriさんがperformance_schema(以下p_s)について発表されていた際に取得項目がsetup_instrumentsテーブルで制御出来るようなお話をしていました。

MySQL5.6ではp_sが有効だと性能が1割程度落ちたりするというのは最近では割と良く知られてきている話しだと思います。p_sが有効な状態でsetup_instrumentsテーブルで全部取得しないようにした場合にどの程度の性能差が出るか試してみました。

設定ファイルのmysqldセクションでperformance_schema=ON, OFFを切り替えてテストしました。
また今回はread-onlyなケースしか行っていないのでバイナリログ周りの設定は結構ザルです。

ベンチマークについて

設定を変えつつsysbenchを以下の内容で実行しています。

#!/bin/sh

threads='8 16 32 64 128 256 512 1024'
DIR=ケース名

[ ! -d $DIR ] && mkdir $DIR
cp -pi ./mysql/etc/my.cnf $DIR/.

for thread in $threads; do
  echo "==== $thread ===="
  /usr/local/sysbench/bin/sysbench \
    --test=/usr/local/src/sysbench/sysbench/tests/db/oltp.lua \
    --init-rng=1 \
    --db-driver=mysql \
    --db-ps-mode=disable \
    --oltp-table-size=3750000 \
    --oltp-dist-type=uniform \
    --oltp-read-only=on \
    --oltp-tables-count=12 \
    --mysql-db=sbtest5 \
    --mysql-user=sbtest \
    --mysql-password=パスワード\
    --mysql-socket=/tmp/mysql-5.6.13.sock \
    --max-time=180 \
    --max-requests=0 \
    --num-threads=$thread \
    run | tee $DIR/ro-$thread
    sleep 30
done

ベンチマークパターン

以下3パターンで取得しました。

  • ケース1: p_s OFF
  • ケース2: p_s ON (setup_instrumentsテーブルはデフォルト)
  • ケース3: p_s ON (setup_instrumentsテーブルのENABLEDが全てOFF)
    • UPDATE performance_schema.setup_instruments SET enabled = 'NO'; を実行

ベンチマーク結果

上記スクリプト実行前に512スレッドで1分、ベンチマークを実行し、データ/インデックスをバッファに載せる作業をしています。

それほどケースが多くないのでグラフにはしていません。上記のスクリプトによる一発取りです。また、単位はtpsになります。

スレッド数 8 16 32 64 128 256 512 1024
ケース1 2596.08 4413.11 5721.59 5644.72 5619.35 5600.32 5597.74 5697.52
ケース2 2487.02 4333.26 5363.82 5298.40 5278.34 5258.75 5250.21 5333.69
ケース3 2727.37 4673.24 5751.39 5667.72 5671.37 5621.66 5611.42 5609.69

今回に関してはp_sがONでもsetup_instrumentsテーブルのENABLEDが全てNOだとp_sがOFFより性能出てますが所詮一発取りなので誤差と考えられます。とはいえ、p_sがONでも誤差程度の差になったのが割と驚きでした。

ではp_sがONでsetup_instrumentsテーブル変更して運用すれば良いんじゃ、と思うかもしれませんがメモリ使用率がp_sがONと比較して多くなってしまいます。今回の環境ではMySQL再起動後、512スレッドで10分間、sysbenchを実行した場合、psコマンドで表示されるmysqldのRSSは以下のようになりました。

RSS
ケース1 8762784
ケース2 9605508
ケース3 9615032

900MB程度多くメモリを使用する結果となりました。ということで一応デメリットはあります。

まとめ

p_sを有効にしても無効と遜色の無い性能がread-onlyでは出る事が分かりました。ただ、ベンチマークは所詮ベンチマークなので実環境では異なる結果になる可能性がありますので必ず実環境に沿ったベンチマークを取得する事を推奨します。
その結果、記載したベンチマークの結果と同じ傾向になり、メモリ使用率の増加が許容出来るのであればp_sが有効で、必要になるまではsetup_instrumentsを変更(ENABLEDをNOに)して運用するのも有りだと思います。

なお、setup_instrumentsの設定値はMySQLを再起動するとデフォルトになるため、本番運用に組み込む場合は起動スクリプト等でENABLEDをNOに変更する処理を入れる必要があります。 追記参照。

以下の通り設定によりp_sで使うメモリ量は設定値により固定されるようなので極端に使用率が増えることは無いと考えられます。

@yoku8025さん、情報ありがとうございました。

追記

my.cnfのmysqldセクションに以下内容を記載すれば起動時からsetup_instrumentsテーブルのENABLEDをNOに出来ました

performance-schema-instrument='%=off'