2012年11月5日月曜日

MySQLをFusion-io ioDriveで使うためのチューニング

これは、こちらの記事の下書きです
http://www.e-agency.co.jp/column/20121107.html


使用したサーバ

http://server.sakura.ad.jp/dedicated/expressg2.html

さくらの専用サーバ エクスプレスG2シリーズ
Fujitsu RX100 S7 Xeon 4Core SATA + ioDrive 320GB Memory 32GB


ioDriveの接続とライブラリの確認

ioDriveの接続を確認
# lspci | grep -i fusion
01:00.0 Mass storage controller: Fusion-io ioDimm3 (rev 01)

ioDriveのRPMパッケージを確認
# rpm -qa | grep "iomemory"
iomemory-vsl-config-2.6.32-220.el6.x86_64-2.3.10.110-1.0.el6.x86_64
iomemory-vsl-source-2.3.10.110-1.0.el6.x86_64
iomemory-vsl-config-2.6.32-279.9.1.el6.x86_64-2.3.10.110-1.0.el6.x86_64
iomemory-vsl-2.6.32-220.el6.x86_64-2.3.10.110-1.0.el6.x86_64
iomemory-vsl-2.6.32-279.9.1.el6.x86_64-2.3.10.110-1.0.el6.x86_64
# rpm -qa | grep "fio"
fio-util-2.3.10.110-1.0.el6.x86_64
fio-firmware-107053-1.0.noarch
fio-common-2.3.10.110-1.0.el6.x86_64
libfio-2.3.10.110-1.0.el6.x86_64
fio-sysvinit-2.3.10.110-1.0.el6.x86_64

ioDriveのステータスを確認
# fio-status -a

Found 1 ioDrive in this system
Fusion-io driver version: 2.3.10 build 110

Adapter: ioDrive
        HP 320GB MLC PCIe ioDrive for ProLiant Servers, Product Number:600279-B21 SN:484653
        Low-Profile ioDIMM Adapter, PN:00119200008, Mfr:003, Date:20120507
        External Power: NOT connected
        Powerloss protection: available
        PCIE Bus voltage: avg 12.10V, min 12.06V, max 12.11V
        PCIE Bus current: avg 0.43A, max 0.99A
        PCIE Bus power: avg 5.26W, max 11.96W
        PCIE Power limit threshold: 24.75W
        PCIE slot available power: 75.00W
        Sufficient power available: Unknown
        PCIE negotiated link: 4 lanes at 2.50 Gbits/sec each, 1000 MBytes/sec total
        Connected ioDimm module:
          fct0: HP 320GB MLC PCIe ioDrive for ProLiant Servers, Product Number:600279-B21 SN:488443

fct0    Attached as 'fioa' (block device)
        HP 320GB MLC PCIe ioDrive for ProLiant Servers, Product Number:600279-B21 SN:488443
        HP ioDIMM 320GB, PN:00309800503, Mfr:003, Date:20120507
        Powerloss protection: protected
        PCI:01:00.0
        Vendor:1aed, Device:1005, Sub vendor:103c, Sub device:178c
        Firmware v5.0.7, rev 107053
        320.00 GBytes block device size, 406 GBytes physical device size
        Format: block, v300, 625,001,920 sectors, 512 bytes per sector
        Error correction: 39 bits per 960 bytes
        FPGA ID:0 Format UID:0000000773fb013303bb0049dcb8dc00
        PCIE slot available power: 75.00W
        Sufficient power available: Unknown
        PCIE negotiated link: 4 lanes at 2.50 Gbits/sec each, 1000 MBytes/sec total
        Internal temperature: 66.0 degC, max 67.9 degC
        Board temperature: 54 degC
        Internal voltage: avg 0.993V, max 0.996V
        Aux voltage: avg 2.470V, max 2.473V
        Media status: Healthy; Reserves: 100.00%, warn at 10.00%
        Lifetime data volumes:
           Physical bytes written: 1,382,385,168
           Physical bytes read   : 7,432,989,232
        RAM usage:
           Current: 108,025,856 bytes
           Peak   : 108,025,856 bytes


ioDriveのパーティションを設定

パーティションを確認。
# /sbin/fdisk -l

Disk /dev/fioa: 320.0 GB, 320000983040 bytes
255 heads, 63 sectors/track, 38904 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 32768 bytes
Disk identifier: 0x00000000


Disk /dev/sda: 999.7 GB, 999653638144 bytes
255 heads, 63 sectors/track, 121534 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x0003365b

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          32      256000   83  Linux
Partition 1 does not end on cylinder boundary.
/dev/sda2              32        1052     8192000   82  Linux swap / Solaris
/dev/sda3            1052      121535   967775232   83  Linux

ディスクのパーティションを設定します。
# fdisk /dev/fioa
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0x2ee10d3c.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
switch off the mode (command 'c') and change display units to
sectors (command 'u').

Command (m for help): [n]

Command action
e extended
p primary partition (1-4)
[p]
Partition number (1-4): [1]
First cylinder (1-38904, default 1): [Enter]
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-38904, default 38904): [Enter]
Using default value 38904

Command (m for help): [p]

Disk /dev/fioa: 320.0 GB, 320000983040 bytes
255 heads, 63 sectors/track, 38904 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 32768 bytes
Disk identifier: 0x2ee10d3c

Device Boot Start End Blocks Id System
/dev/fioa1 1 38904 312496348+ 83 Linux

Command (m for help): [w]
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

パーティションブロックの割り当て情報を確認。
# cat /proc/partitions
major minor  #blocks  name

 252        0  312500960 fioa
 252        1  312496348 fioa1
   8        0  976224256 sda
   8        1     256000 sda1
   8        2    8192000 sda2
   8        3  967775232 sda3


XFSファイルシステムの設定

ioDriveをXFSファイルシステムでフォーマットします。

ファイルシステムの違い
ext3は同一ファイルへの書き込みが、同時に1スレッドしかできない(ext4も同様)
xfsはrawデバイスに近い並列性(複数スレッドでの同時書き込み可)
Fusion-ioで使うならXFS
DeNA松信さんの「MySQL環境におけるFusion-io検証結果とDeNAにおける活用価値」セッションメモ(http://d.hatena.ne.jp/rx7/20101015/p1

xfsフォーマットをするのに必要なライブラリをインストール。
# yum install xfsprogs

xfsprogsがインストールされた事を確認。
# yum list | grep xfs
xfsprogs.x86_64                         3.1.1-7.el6               @base      
xfsdump.x86_64                          3.0.4-2.el6               base        
xfsprogs.i686                           3.1.1-7.el6               base        
xfsprogs-devel.i686                     3.1.1-7.el6               base        
xfsprogs-devel.x86_64                   3.1.1-7.el6               base        
xfsprogs-qa-devel.i686                  3.1.1-7.el6               base        
xfsprogs-qa-devel.x86_64                3.1.1-7.el6               base        


ファイルシステムxfsでフォーマット。
ブロックサイズを4KBとします。


MySQL+ioDrive
XFSは bs=4K 、InnoDBは bs=16K、トランザクションログは 512K とポイントは色々ありますが、MySQL+ioDriveとしては 4Kbyteが最も良好な性能を記録しているため、4Kbyteでフォーマットすることがよさそうです。
Fusion-io ioDriveの検討資料~運用設定編~(http://blog.father.gedow.net/2012/08/06/fusion-io-iodrive-operaton-configuration/


# /sbin/mkfs.xfs -s size=4096 -b size=4096 /dev/fioa1 -f
meta-data=/dev/fioa1             isize=256    agcount=16, agsize=4882756 blks
         =                       sectsz=4096  attr=2
data     =                       bsize=4096   blocks=78124087, imaxpct=25
         =                       sunit=0      swidth=0 blks
naming   =version 2              bsize=4096   ascii-ci=0
log      =internal log           bsize=4096   blocks=38146, version=2
         =                       sectsz=4096  sunit=1 blks, lazy-count=1
realtime =none                   extsz=4096   blocks=0, rtextents=0


ioDriveをマウント

# mkdir /fioa
# mount /dev/fioa1 /fioa

ioDriveがXFSファイルシステムでマウントされています。
# df -T
Filesystem    Type   1K-blocks      Used Available Use% Mounted on
/dev/sda3     ext4   952589244   3222600 900977884   1% /
tmpfs        tmpfs    16490208         0  16490208   0% /dev/shm
/dev/sda1     ext4      247919     73226    161893  32% /boot
/dev/fioa1     xfs   312343764     33504 312310260   1% /fioa

サーバ起動時に自動でマウントするように、fstabファイルに追記。
# vi /etc/fstab
/dev/fioa1 /fioa xfs defaults 1 2


I/Oスケジューラの設定

ioDriveドライバのI/Oスケジューラを設定します。
MySQLはI/Oスケジューラをバイパスしてデバイスに直接アクセスする事で、パフォーマンスの向上が見込めます。


I/O Schedulers
- Default with Fusion-io is NOOP
- Merges requests but does not optimize for rotational devices
- For MySQL, submitting requests directly to the device have shown performance improvements. use_workqueue=0
Tuning For Speed: Percona Server and Fusion-io(http://www.percona.com/files/presentations/percona-live/nyc-2011/PerconaLiveNYC2011-Tuning-For-Speed-Percona-Server-and-Fusion-io.pdf

Linuxカーネルのモジュールを設定します。
# modprobe iomemory-vsl use_workqueue=0

設定を確認。
# modprobe -c | grep use_workqueue
options  iomemory-vsl use_workqueue=0

iomemory-vsl.confに書き込みしておく。
# vi /etc/modprobe.d/iomemory-vsl.conf
options  iomemory-vsl use_workqueue=0

参考)
http://bizsupport1.austin.hp.com/bc/docs/support/SupportManual/c02737596/c02737596.pdf

The following table describes the module parameters you can set by editing the
/usr/modprobe.d/iomemory-vsl.conf file and changing the values.
/etc/modprobe.d/iomemory-vsl.conf
use_workqueue 3 (1 or 3) Linux only:
3 = Use standard OS I/O elevators
0 = bypass

One-time configuration
The IO Accelerator driver options can be set when the driver is installed on the command line of either
insmod or modprobe. For example, set the auto_attach driver option to 0:
$ modprobe iomemory-vsl auto-attach=0
This option takes effect only for this load of this driver. This option is not set for subsequent calls to modprobe
or insmod.
Persistent configuration
To maintain a persistent setting for an option, add the option to the
/etc/modprobe.d/iomemory-vsl.conf file or a similar file. To prevent the IO Accelerator from
auto-attaching, add the following line to the iomemory-vsl.conf file:
options iomemory-vsl auto_attach=0
The driver option then takes effect for every subsequent driver load, as well as on autoload of the driver
during boot time.


MySQL5.5(Percona XtraDB)のインストール

MySQL5.5(Percona XtraDB)の64bit versionを使います。
Percona XtraDB(http://www.percona.com/software/percona-server)は、MySQLコンサルティング会社であるPercona社(http://www.percona.com/)が提供しています。
MySQL Community Serverをフォークしたプロダクトです。
通常のMySQLより、マルチコアプロセッサ・I/Oに配慮した設計になっています。
ライセンスはGPLv2で、無料で使えます。

先に、MySQLがインストールされていたら、予めアンインストールしておきましょう。
# yum remove mysql mysql-server mysql-libs php-mysql

Percona公式リポジトリをインストールしたあと、yumコマンドでインストールできます。
# rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
# yum install Percona-Server-devel-55 Percona-Server-server-55 Percona-Server-client-55
# /usr/bin/mysql_install_db
# /etc/init.d/mysql start
Starting MySQL (Percona Server).. SUCCESS!
# /usr/bin/mysqladmin -u root password 'new-password' #パスワードは便宜設定しましょう
# /usr/bin/mysql -uroot -pnew-password

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.28-29.1 Percona Server (GPL), Release rel29.1, Revision 335

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

MySQLが立ち上がりました。
InnoDBエンジンのコメントがPercona-XtraDBとなっていることを確認します。

mysql> SHOW ENGINES \G
*************************** 9. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Percona-XtraDB, Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
9 rows in set (0.00 sec)

起動ログも確認しておきましょう。
# cat /var/log/mysqld.log
121029 14:42:55 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
121029 14:42:56 [Note] Plugin 'FEDERATED' is disabled.
121029 14:42:56 InnoDB: The InnoDB memory heap is disabled
121029 14:42:56 InnoDB: Mutexes and rw_locks use GCC atomic builtins
121029 14:42:56 InnoDB: Compressed tables use zlib 1.2.3
121029 14:42:56 InnoDB: Using Linux native AIO
121029 14:42:56 InnoDB: Initializing buffer pool, size = 128.0M
121029 14:42:56 InnoDB: Completed initialization of buffer pool
121029 14:42:56 InnoDB: highest supported file format is Barracuda.
121029 14:42:56  InnoDB: Waiting for the background threads to start
121029 14:42:57 Percona XtraDB (http://www.percona.com) 1.1.8-rel29.1 started; log sequence number 1595675
121029 14:42:57 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
121029 14:42:57 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
121029 14:42:57 [Note] Server socket created on IP: '0.0.0.0'.
121029 14:42:57 [Note] Event Scheduler: Loaded 0 events
121029 14:42:57 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.28-29.1'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Percona Server (GPL), Release rel29.1, Revision 335


phpをインストールするときには、remiレポジトリを使います。
# wget http://rpms.famillecollet.com/enterprise/remi-release-5.rpm
# rpm -Uvh remi-release-5.rpm
# yum --enablerepo=remi install php php-mysql
# php -v
PHP 5.3.18 (cli) (built: Oct 18 2012 08:50:17)
Copyright (c) 1997-2012 The PHP Group
Zend Engine v2.3.0, Copyright (c) 1998-2012 Zend Technologies


my.cnfの設定

ioDrive上にMySQLのデータディレクトリを移動します。
# /etc/init.d/mysql stop
# mv /var/lib/mysql /fioa/

シンボリックリンクを張ります
# ln -s /fioa/mysql  /var/lib/mysql

https://github.com/kazeburo/mysetup/blob/master/mysql/my55.cnf
kazeburoさんがgithubで公開されているmy.cnfファイルをベースに設定してきます。

# git clone https://github.com/kazeburo/mysetup.git
# cp /etc/my.cnf /etc/my.cnf.org
# cp mysetup/mysql/my55.cnf /etc/my.cnf

# vi /etc/my.cnf

変更した項目

# サーバID
server-id       = 1

# 控えめに搭載メモリ32GBの約70%とします
innodb_buffer_pool_size = 20G

# DiskのIO/secを指定します
innodb_io_capacity = 10000

# CPUのスレッド数を無制限にします
# 参考 innodb_thread_concurrencyとか計測してみました http://www.inter-office.co.jp/contents/122/
innodb_thread_concurrency = 0

# データのflushのタイミングを改善して、I/Oバーストを解消します
# 参考 MySQL Conference&Expo 2010に行ってきました http://engineer.dena.jp/2010/06/mysql-conferenceexpo-2010.html
innodb_adaptive_flushing = 1

# I/Oの速度を0.1秒間隔に保ちます
# 参考 variable innodb_adaptive_flushing_method http://www.percona.com/doc/percona-server/5.5/scalability/innodb_io_55.html#innodb_adaptive_flushing_method
innodb_adaptive_flushing_method = keep_average

# ioDraiveはランダムアクセスのコストがないため、バッファプール内に保留されたダーティページを纏めてテーブルスペースへ書き込む必要がありません。
# 設定をオフにしてパフォーマンスを発揮させます。
innodb_flush_neighbor_pages = 0

MySQLをリスタートします。
innodb_log_file_sizeの値が変わっていますので、ib_logfile0とib_logfile1を退避しておきます。
# /etc/init.d/mysql stop
# mv /var/lib/mysql/ib_logfile0 /tmp
# mv /var/lib/mysql/ib_logfile1 /tmp
# /etc/init.d/mysql start

ログの確認
# tail -f /var/lib/mysql/*.err


MySQLのベンチマーク

MySQLベンチマークツールのtpcc-mysqlを使ってテストデータを挿入します。

tpcc-mysqlをインストール。
# cd /usr/share/
# rpm -Uvh  http://dl.fedoraproject.org/pub/epel/5/x86_64/epel-release-5-4.noarch.rpm
# yum --enablerepo=epel install bzr
# bzr branch lp:~percona-dev/perconatools/tpcc-mysql
# cd tpcc-mysql/src
# make all
# cd ..; ls -l

データベースの設定をインポート。
# cd /usr/share/tpcc-mysql
# mysqladmin -uroot -pnew-password create tpcc
# mysql -uroot -pnew-password tpcc < create_table.sql
# mysql -uroot -pnew-password tpcc < add_fkey_idx.sql

テストデータを挿入。
tpcc_loadにはwarehouse(倉庫)というパラメータで挿入するデータ量を決めます。
1 warehouse = 約75MBです。

# ./tpcc_load -h
*************************************
*** ###easy### TPC-C Data Loader  ***
*************************************

 usage: tpcc_load [server] [DB] [user] [pass] [warehouse]
      OR
        tpcc_load [server] [DB] [user] [pass] [warehouse] [part] [min_wh] [max_wh]

           * [part]: 1=ITEMS 2=WAREHOUSE 3=CUSTOMER 4=ORDERS
# time ./tpcc_load localhost tpcc root 'new-password' 400


挿入完了まで5時間ほどかかりました。
データベースのサイズを見てみましょう。
mysql> SELECT SUM(data_length)/1024/1024 AS total_db_data_in_MB FROM information_schema.tables WHERE table_schema = 'tpcc';
+---------------------+
| total_db_data_in_MB |
+---------------------+
|      30628.68750000 |
+---------------------+
1 row in set (0.02 sec)

30GBのテストデータが準備できました。
このサーバのメモリ量と比べるときついサイズです。

ベンチマークを走らせる前に、テストデータのバックアップをしておきます。
ベンチマークスクリプトはテストデータを更新するためです。
今回は、MySQLバックアップツールのXtraBackup(http://www.submit.ne.jp/1211#1)を使いました。

バックアップ
# time /usr/bin/innobackupex --user root --password pnew-password --slave-info /tmp/xtrabackup/
# time /usr/bin/innobackupex --user root --password pnew-password --apply-log /tmp/xtrabackup/2012-11-02_16-33-27

リストア
# time /usr/bin/innobackupex --copy-back /tmp/xtrabackup/2012-11-02_16-33-27


それでは、ベンチマークを開始しましょう。
warehouseの数を100、同時接続数を10、測定を開始するまでの助走時間を60秒、測定時間を600秒とします。

# ./tpcc_start -h
***************************************
*** ###easy### TPC-C Load Generator ***
***************************************
./tpcc_start: option requires an argument -- 'h'
Usage: tpcc_start -h server_host -P port -d database_name -u mysql_user -p mysql_password -w warehouses -c connections -r warmup_time -l running_time -i report_interval -f report_file -t trx_file

# time ./tpcc_start -h localhost -d tpcc -u root -p 'new-password' -w 400 -c 10 -r 60 -l 600

測定終了まで
topやvmstat 1を眺めて待ちましょう。

TpmCは1分間に処理できるトランザクションの数です。
比較して数値が大きいほど、性能が高い事を示します。

・ファイルシステム XFS フル設定
----------------------------------------------
<TpmC>
                 29288.500 TpmC

real    11m0.032s
user    3m18.081s
sys     2m50.399s
----------------------------------------------

・ファイルシステム XFS kazeburoさんのmy.cnfに、server-idとinnodb_buffer_pool_size = 20Gを設定したのみのmy.cnf
----------------------------------------------
<TpmC>
                 25305.900 TpmC

real    11m0.083s
user    2m50.603s
sys     2m25.939s
----------------------------------------------

・ファイルシステム ext4 フル設定
----------------------------------------------
<TpmC>
                 27609.500 TpmC

real    11m0.052s
user    3m3.894s
sys     2m34.845s
----------------------------------------------


・ファイルシステム ext4  kazeburoさんのmy.cnfに、server-idとinnodb_buffer_pool_size = 20Gを設定したのみのmy.cnf
----------------------------------------------
<TpmC>
                 27358.000 TpmC

real    11m0.276s
user    3m5.723s
sys     2m37.755s
----------------------------------------------


まとめ

試行回数やパラメータ設定が少なくて、ちょっとベンチマークとしてはいけていないのですが、、、、
それぞれ、大きな差は有りませんでした。
my.cnfの設定はkazeburoさんのmy.cnfにserver-idとinnodb_buffer_pool_sizeを設定したのみの物でも十分にいけると思います。
勿論、実運用へ向けたチューニングとして、実際の挙動にできるだけ近いベンチマークを行う事が望ましいです。
次回は、もう少し細かくベンチマークを取っていきたいですね。



参考資料

http://www.percona.com/files/presentations/percona-live/nyc-2011/PerconaLiveNYC2011-Optimizing-MySQL-for-Solid-State-Storage.pdf
Optimizing MySQL for Solid State Storage

http://blog.father.gedow.net/2012/08/28/iodrive-mysql-event/
第2回 ioDrive+MySQL勉強会 発表資料

http://d.hatena.ne.jp/rx7/20101015/p1
DeNA松信さんの「MySQL環境におけるFusion-io検証結果とDeNAにおける活用価値」セッションメモ

http://www.percona.com/about-us/mysql-white-paper/virident-flashmax-m1400-mysql-tpcc-mysql-report/
Virident FlashMAX M1400 MySQL - tpcc-mysql report

http://www.percona.com/live/london-2011/session/tuning-for-speed-percona-server-and-fusion-io/
Tuning For Speed - Percona Server and Fusion-io

http://www.percona.com/files/white-papers/virident-mlc-tpcc.pdf
Virident FlashMAX M1400 MySQL - Tpcc-Mysql Report

http://blog.nomadscafe.jp/2012/10/mysql-mycnf-github.html
MySQLの設定ファイル my.cnf をgithubにて公開しました & チューニングポイントの紹介

http://www.fusionio.com/blog/mysql-acceleration-through-new-flash-storage-api-primitives/
MySQL Acceleration Through New Flash Storage API Primitives

http://d.hatena.ne.jp/sh2/20090212
tpcc-mysqlによるMySQLのベンチマーク