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

2012年11月7日 ｜ 八子 武司

 

「高嶺の花」の製品であったFusion-io ioDriveが、さくらインターネットの専用サーバプランにより月5万円でお手軽に扱えるようになりました。

今回は、このサーバを使ってMySQLのチューニングをしていきます。

使用したサーバ

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で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社が提供している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のデータを扱う為に、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 + my.cnfフル設定

----------------------------------------------
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

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

ファイルシステム ext4 + my.cnfフル設定

----------------------------------------------
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

----------------------------------------------
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のベンチマーク

PICK UP

お問い合わせ

サービスに関するご相談は
こちらよりお気軽にお問い合わせください。

Copyright © e-Agency Co., Ltd. All rights reserved.

e-Agencyの様々な情報をSNSでお届けします!