MySQL 5.6 パラメータ検討会

7月29日にMyNA(日本MySQLユーザ会)会 2013年7月が行われ、Oracle ACE Directorの@sheeriさん、MyNA会長の@tmtmsさんに混ざって発表をしてきました。運営のみなさま、当日お越しいただいたみなさま、いつもありがとうございます。

今回は@yoku0825さん、@yyamasaki1さんがライトニングトークをされました。@yoku0825さんアイスごちそうさまでした。

私は発表内容について懇親会でいろいろ宿題をもらってしまい、しばらく復習をしていました。ようやく修正が終わりましたのでRevision 2として公開します。


復習のついでに、パラメータファイルの叩き台を作成しました。

このパラメータファイルは一部のディレクトリ名、hostname、server_id、server_uuidとssl_cipherを除き、Amazon RDS t1.smallの設定を再現したものです。これをベースにカスタマイズしていくと時間を節約できるのではないかと思います。インスタンスサイズが異なる場合はinnodb_buffer_pool_sizeとmax_connectionsを増減させます。オンプレミスの環境でしたらdefault_time_zoneはSYSTEMの方がよいかもしれません。

コメントアウトされているところは私が独断で追加したパラメータです。新機能を積極的に活用する、また極力詳細なログを出力するという方針で選んでいます。設定値はあくまでも一例ですが、ご参考になればと思います。リファレンスマニュアルにリンクしたものを以下に貼り付けておきます。

[mysqld]
## File Location
datadir                   = /rdsdbdata/db
innodb_data_home_dir      = /rdsdbdata/db/innodb
pid-file                  = /rdsdbdata/log/mysql-3306.pid
log_bin                   = /rdsdbdata/log/binlog/mysql-bin-changelog
log_error                 = /rdsdbdata/log/error/mysql-error.log
general_log_file          = /rdsdbdata/log/general/mysql-general.log
innodb_log_group_home_dir = /rdsdbdata/log/innodb
relay_log                 = /rdsdbdata/log/relaylog/relaylog
slow_query_log_file       = /rdsdbdata/log/slowquery/mysql-slowquery.log
slave_load_tmpdir         = /rdsdbdata/tmp
tmpdir                    = /rdsdbdata/tmp
secure_file_priv          = /tmp
socket                    = /tmp/mysql.sock

## SSL
ssl_ca   = /rdsdbdata/rds-metadata/ca-cert.pem
ssl_cert = /rdsdbdata/rds-metadata/server-cert.pem
ssl_key  = /rdsdbdata/rds-metadata/server-key.pem

## InnoDB
#innodb_buffer_pool_dump_at_shutdown = ON
#innodb_buffer_pool_load_at_startup  = ON
innodb_buffer_pool_size              = 1092M
#innodb_checksum_algorithm           = crc32
#innodb_file_format                  = Barracuda
#innodb_flush_method                 = O_DIRECT
#innodb_io_capacity                  = 2000
#innodb_io_capacity_max              = 4000
#innodb_large_prefix                 = ON
innodb_log_file_size                 = 128M
#innodb_online_alter_log_max_size    = 1G
#innodb_print_all_deadlocks          = ON

## MyISAM
key_buffer_size = 16M

## Memory
#max_heap_table_size = 64M
#tmp_table_size      = 64M

## Binary Log
binlog_format             = MIXED
#enforce_gtid_consistency = ON
#expire_logs_days         = 14
#gtid_mode                = ON
log_slave_updates         = ON
master_info_repository    = TABLE
#master_verify_checksum   = ON
max_binlog_size           = 128M
relay_log_info_repository = TABLE
relay_log_recovery        = ON
#report_host              = 
#report_port              = 3306
server_id                 = 12345
#slave_net_timeout        = 30
sync_binlog               = 1

## Slow Query Log
log_output                              = TABLE
#log_queries_not_using_indexes          = ON
#log_slow_slave_statements              = ON
#log_throttle_queries_not_using_indexes = 60
#long_query_time                        = 1.0
#slow_query_log                         = ON

## Connection Management
#log_warnings              = 2
#max_allowed_packet        = 16M
#max_connect_errors        = 999999999
max_connections            = 121
#table_open_cache          = 10000
table_open_cache_instances = 16

## Memory Allocation per Connection
read_buffer_size     = 256K
read_rnd_buffer_size = 512K
#sort_buffer_size    = 2M

## SQL Behavior
#character_set_server           = utf8mb4
#collation_server               = utf8mb4_general_ci
default_time_zone               = UTC
explicit_defaults_for_timestamp = ON
#sql_mode                       = TRADITIONAL
#transaction_isolation          = READ-COMMITTED

[client]
#loose_default_character_set = utf8mb4
socket                       = /tmp/mysql.sock

MySQL向けに書かれたアプリケーションをOracle Databaseに接続させる

先日Oracle Database 12c Release 1がリリースされまして、新機能ガイドを眺めていたところ気になる新機能を見つけました。

Oracle Database 12c Release 1にはliboramysql12という共有ライブラリが同梱されていて、これをlibmysqlclientと差し替えるとMySQL向けに書かれたアプリケーションがそのままOracle Databaseに対して動作するのだそうです。ドキュメントから図を引用します。

試してみましょう。MySQLにテスト用のテーブルを作ります。

mysql> CREATE TABLE myora (
    ->     id INT PRIMARY KEY,
    ->     data VARCHAR(100)
    -> ) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8mb4;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO myora (id, data) VALUES (1, 'マイエスキューエル');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM myora;
+----+-----------------------------+
| id | data                        |
+----+-----------------------------+
|  1 | マイエスキューエル          |
+----+-----------------------------+
1 row in set (0.00 sec)

Oracle Databaseにも同じものを作ります。

SQL> CREATE TABLE myora (
  2      id NUMBER PRIMARY KEY,
  3      data VARCHAR2(400)
  4  );
Table created.

SQL> INSERT INTO myora (id, data) VALUES (1, 'オラクル');
1 row created.

SQL> COMMIT;
Commit complete.

SQL> COL data FORMAT a40
SQL> SELECT * FROM myora

        ID DATA
---------- ----------------------------------------
         1 オラクル

しばらく試行錯誤していたのですが、PythonとMySQL-Pythonの組み合わせで動作確認がとれました。

#!/usr/bin/env python
# -*- coding: utf-8 -*-

from __future__ import print_function
from __future__ import unicode_literals

import MySQLdb

connection = None
cursor = None

try:
    connection = MySQLdb.connect(
            db='scott', user='scott', passwd='tiger', charset='utf8')
    cursor = connection.cursor()
    cursor.execute("SELECT data FROM myora WHERE id = 1")
    result = cursor.fetchall()

    for record in result:
        print(record[0])
except MySQLdb.Error as e:
    print(repr(e))
finally:
    if cursor:
        cursor.close()
    if connection:
        connection.close()

$ python db1.py
マイエスキューエル

$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib
$ export LD_PRELOAD=$ORACLE_HOME/lib/liboramysql12.so
$ python db1.py
オラクル

すごいですね。ただし、LD_PRELOADを設定して実行しただけでは以下のエラーが発生していました。

Traceback (most recent call last):
  File "db1.py", line 16, in <module>
    cursor.execute("SELECT data FROM myora WHERE id = 1")
  File "/usr/lib64/python2.6/site-packages/MySQLdb/cursors.py", line 156, in execute
    query = query.encode(charset)
LookupError: unknown encoding:

これはMySQL C APIのうちmysql_character_set_name()がliboramysql12には実装されておらず、空文字列を返すことによるものです。今回はMySQL-Pythonに以下のパッチをあててしのぎました。

--- cursors.py_orig     2013-07-01 00:57:30.422669501 +0900
+++ cursors.py  2013-06-30 23:01:36.461905484 +0900
@@ -152,6 +152,8 @@
         del self.messages[:]
         db = self._get_db()
         charset = db.character_set_name()
+        if charset == '':
+            charset = 'utf8'
         if isinstance(query, unicode):
             query = query.encode(charset)
         if args is not None:

それから例外ハンドリングの違いが気になりました。以下のスクリプトでわざと一意制約違反を起こしてみます。

#!/usr/bin/env python
# -*- coding: utf-8 -*-

from __future__ import print_function
from __future__ import unicode_literals

import MySQLdb

connection = None
cursor = None

try:
    connection = MySQLdb.connect(
            db='scott', user='scott', passwd='tiger', charset='utf8')
    cursor = connection.cursor()
    cursor.execute("INSERT INTO myora (id, data) VALUES (1, 'ポストグレス')")
    connection.commit()
except MySQLdb.Error as e:
    print(repr(e))
finally:
    if cursor:
        cursor.close()
    if connection:
        connection.close()

$ python db2.py
IntegrityError(1062, "Duplicate entry '1' for key 'PRIMARY'")

$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib
$ export LD_PRELOAD=$ORACLE_HOME/lib/liboramysql12.so
$ python db2.py
InternalError(1, 'ORA-00001: unique constraint (SCOTT.SYS_C0010064) violated')

このように、残念ながらエラー番号のマッピングはしてくれないようです。MySQL-Pythonはエラー番号を見て例外クラスのインスタンスを生成しているため、生成されたインスタンスも誤ったものとなっています。

  • 未実装APIやエラー番号の違いがあるため、MySQL-Pythonなどのドライバにかなりの修正が必要
  • SQLの方言を吸収しないため、アプリケーションにもかなりの修正が必要
  • MySQL用のアプリケーションをわざわざ高価なOracle Databaseに移行する動機がない

ということで現時点ではあまり実用性がない気もしますが、Oracle Database本体がMySQLに関連する機能を備えたのは初めてだと思いますので、ご紹介しました。

EBS Standard Volumeの性能特性

最近@namikawaさんと@con_mameさんがEBSの性能測定をされていて、少し気になったので追試をしました。

測定環境です。

  • ap-northeast-1c
  • m1.small (Spot Instance)
  • Amazon Linux AMI 2013.03.1
  • EBS Standard Volume 16GB
  • ext4 (relatime、nobarrier)

測定用スクリプトです。EBS Standard Volumeに対してランダムI/Oの測定のみを行います。

#/bin/bash

for SIZE in 1g 2g 4g 8g; do
    for IODEPTH in 1 2 4 8; do
        rstat/rstat_start.sh localhost

        fio -direct=1 -ioengine=libaio -readwrite=randread -blocksize=4k \
            -filename=/fio/fio.dat -loops=1000 -runtime=180 \
            -size=${SIZE} -iodepth=${IODEPTH} -name=fio_s${SIZE}_q${IODEPTH} | tee -a fio_s${SIZE}.log

        rstat/rstat_stop.sh
        sleep 30
    done

    mkdir fio_${SIZE}
    mv *.log *.csv fio_${SIZE}
done

ポイントは、測定時間を16秒から180秒に伸ばしたところです。ファイルサイズが8GBのときの結果を時系列のグラフにすると、以下のようになります。

数秒から数十秒経過したところで、性能がストンと落ちています。この挙動はドキュメントに記載されているとおりのものなのですが、なかなかダイナミックな制御が行われているようです。

スタンダードボリュームは、I/O 要件が中程度またはバースト性であるアプリケーション用のストレージに適しています。スタンダードボリュームの平均パフォーマンスは約 100 IOPS であり、ベストエフォートで数百 IOPS のバーストも可能です。スタンダードボリュームは、ブートボリュームとしての使用にも適しており、バースト可能であることからインスタンス起動時間が短縮されます。

ファイルサイズが8GB、非同期I/OのQueue Depthが8の場合はバースト時に14,000 IOPS、それが終わると300〜600 IOPSとなりました。Standard Volumeがコンスタントに1,000 IOPS以上出せてしまうとProvisioned IOPSの意味がなくなってしまいますから、1,000 IOPS未満に落ち込むことがあるという点は致し方ないのかもしれません。振れ幅が大きいのでデータベースの格納先として使用すると突然の性能低下に悩まされそうですね。ルートデバイスには適していると思います。

バースト制御のアルゴリズムがどうなっているのかはよく分からないのですが、今回の測定条件ではfioコマンドの終了後に30秒ほど休ませると再びバーストするようになりました。また読み取りはアクセス範囲が狭い場合にバーストが継続する傾向があり、書き込みについてはアクセス範囲が広くても180秒間ずっとバーストしたままとなっていました。他のパターンのグラフをいくつか載せておきます。


ファイルサイズが1GBの場合は、バーストが継続しています。


書き込みの場合は、ファイルサイズが8GBでもバーストが継続しています。

もっと詳しく調べたいところですが、お財布がどんどん軽くなるのでこのあたりにしておきます。個人的には、データベースの格納先としてはProvisioned IOPSをおすすめします。

チューニンガソン5の復習 MySQL 5.6 新機能編

というわけで、MyNA(日本MySQLユーザ会)会 2013年3月に参加して発表をしてきました。とてもリラックスして話をすることができました。司会進行の坂井さんをはじめ日本MySQLユーザ会のみなさま、日本オラクルのみなさま、当日お越しいただいたみなさま、どうもありがとうございました。
私のセッションでは前回のエントリの続きということで、MySQL 5.6の新機能Optimizer Traceを活用しながら正攻法でのチューニングを行っていきました。とはいえ途中から正攻法ではなくなっていた気もします。MySQL 5.6でRDBMSとしての土台はしっかりしてきたと思いますので、今後は高度な統計情報を使用したSQL実行計画の最適化といったところにも機能強化が施されていくのではないかと期待しています。

プレゼンテーション資料からリンクしているウェブサイトの一覧です。

もっとすてきなチューニング方法を見つけたら、ぜひ教えてください。

チューニンガソン5の復習 MySQL 5.5 チート編＆勉強会のお知らせ

ゼロスタートの@zakiさんを中心に企画、運営されているチューニンガソンというイベントがあります。指定された環境を用いてOSやサーバ周りのチューニングを行い、性能を競うというものです。2013年1月に行われた第5回ではMySQLがテーマだったということで、本日はこれの復習をしていこうと思います。

しばらく真面目にチューニングしていたのですが思いのほか難しかったので、今回はちょっとズルをして優勝タイムを出すところまでご紹介します。

計測条件

Dropboxでレギュレーションが公開されています。

また、イベントで用いられたものと同じAmazon Machine Imageが公開されています。

  • m1.xlarge (8ECU、4仮想コア、15GBメモリ)
  • Amazon Linux AMI 2012.09
  • MySQL 5.5.29

しかし従量課金のm1.xlargeで作業していると、正直私は焦りを隠せません。落ち着いて作業をするために、まずは自宅のPCへ引っ越しをしました。

計測プログラムのオリジナル版には、結果を集計サーバにPOSTする機能があります。しかしもう集計サーバはありませんので、改変版ではPOST機能を取り除いてあります。またSQLとログを少し見やすく整形しています。@zakiさんに改変版の頒布許可をいただきました。どうもありがとうございます。
データベースはMediaWikiのもので、Wikipedia日本語版が格納されています。

CREATE TABLE `page` (
  `page_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `page_namespace` int(11) NOT NULL,
  `page_title` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `page_restrictions` tinyblob NOT NULL,
  `page_counter` bigint(20) unsigned NOT NULL DEFAULT '0',
  `page_is_redirect` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `page_is_new` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `page_random` double unsigned NOT NULL,
  `page_touched` binary(14) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `page_latest` int(10) unsigned NOT NULL,
  `page_len` int(10) unsigned NOT NULL,
  PRIMARY KEY (`page_id`),
  UNIQUE KEY `name_title` (`page_namespace`,`page_title`),
  KEY `page_random` (`page_random`),
  KEY `page_len` (`page_len`),
  KEY `page_redirect_namespace_len` (`page_is_redirect`,`page_namespace`,`page_len`)
) ENGINE=InnoDB AUTO_INCREMENT=2704666 DEFAULT CHARSET=utf8;

CREATE TABLE `revision` (
  `rev_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `rev_page` int(10) unsigned NOT NULL,
  `rev_text_id` int(10) unsigned NOT NULL,
  `rev_comment` tinyblob NOT NULL,
  `rev_user` int(10) unsigned NOT NULL DEFAULT '0',
  `rev_user_text` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `rev_timestamp` binary(14) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `rev_minor_edit` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `rev_deleted` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `rev_len` int(10) unsigned DEFAULT NULL,
  `rev_parent_id` int(10) unsigned DEFAULT NULL,
  `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
  PRIMARY KEY (`rev_id`),
  UNIQUE KEY `rev_page_id` (`rev_page`,`rev_id`),
  KEY `rev_timestamp` (`rev_timestamp`),
  KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
  KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
  KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
  KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=45658130 DEFAULT CHARSET=utf8 MAX_ROWS=10000000 AVG_ROW_LENGTH=1024;

mysql> SELECT *
    -> FROM information_schema.tables
    -> WHERE table_schema = 'wikipedia'
    -> AND table_name IN ('page', 'revision')\G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: wikipedia
     TABLE_NAME: page
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Compact
     TABLE_ROWS: 1670757
 AVG_ROW_LENGTH: 98
    DATA_LENGTH: 164265984 (156.7MB)
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 207159296 (197.6MB)
      DATA_FREE: 85983232
 AUTO_INCREMENT: 2704666
    CREATE_TIME: 2013-03-03 20:54:20
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:
*************************** 2. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: wikipedia
     TABLE_NAME: revision
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Compact
     TABLE_ROWS: 1599526
 AVG_ROW_LENGTH: 136
    DATA_LENGTH: 217808896 (207.7MB)
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 431144960 (411.2MB)
      DATA_FREE: 85983232
 AUTO_INCREMENT: 45658130
    CREATE_TIME: 2013-03-03 20:55:53
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: max_rows=10000000 avg_row_length=1024
  TABLE_COMMENT:

MySQLの設定ファイルはRed Hat Enterprise Linuxバンドル版のmy.cnfを基本として、キャラクタセットのみ追加指定した状態です。チューニングはまったく施されていません。

[mysqld]
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
character-set-server=utf8

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
character-set-server=utf8

[mysql]
default-character-set=utf8

計測プログラムが実行するクエリは以下の6種類です。これらが1回ずつ順番に実行されます。

RESET /* Q0 */ QUERY CACHE;

  SELECT /* Q1 */ COUNT(*)
    FROM page p
    JOIN revision r
      ON p.page_id = r.rev_page
   WHERE p.page_is_redirect = 0
     AND p.page_namespace = 0
     AND r.rev_user = 0;

  SELECT /* Q2 */ COUNT(*)
    FROM page p
    JOIN revision r
      ON p.page_id = r.rev_page
   WHERE p.page_is_redirect = 0
     AND p.page_namespace = 0
     AND r.rev_user = 0
     AND p.page_touched > DATE_FORMAT(NOW() - INTERVAL 1 YEAR, '%Y0101000000');

  SELECT /* Q3 */ p.page_id
    FROM page p
    JOIN revision r
      ON p.page_id = r.rev_page
   WHERE p.page_is_redirect = 0
     AND p.page_namespace = 0
     AND r.rev_user = 0
ORDER BY p.page_touched DESC
   LIMIT 10;

  SELECT /* Q4 */ r.rev_user, COUNT(*) AS c
    FROM page p
    JOIN revision r
      ON p.page_id = r.rev_page
   WHERE p.page_is_redirect = 0
     AND p.page_namespace = 0
GROUP BY r.rev_user
ORDER BY c DESC;

  SELECT /* Q5 */ SUBSTRING(r.rev_timestamp, 1, 6), COUNT(*) AS c
    FROM page p
    JOIN revision r
      ON p.page_id = r.rev_page
   WHERE p.page_is_redirect = 0
     AND p.page_namespace = 0
GROUP BY SUBSTRING(r.rev_timestamp, 1, 6)
ORDER BY c DESC;

ベースライン

手元のPCを使用して、まずはそのまま実行してみます。

$ python tgbench_mysql_2.py 127.0.0.1
Q0:0.00018
Q1:5.97537
Q2:4.66229
Q3:4.59017
Q4:14.48054
Q5:14.30720
Total:43.97575


1回目は44.0秒、2回目は41.1秒となりました。2回目はOSのページキャッシュ、InnoDBのバッファプールにデータがキャッシュされるため性能が向上します。クエリの単体性能を計測する際は、2回ずつ実施することをおすすめします。
当日は140秒程度の結果が出ていたそうで、m1.xlargeに比べるとCore i5-2400Sの方が3倍程度速いということになります。m1.xlargeはXeon E5645でSPECint_base2006は36.4、一方Core i5-2400SのSPECint_base2006は36.0とCPUコアあたりの性能にはほとんど違いがないのですが、EC2の方で何らかの制限が設けられているのだと考えられます。

チューニングその1：InnoDBバッファプールの拡大

計測プログラムが実行するクエリはpageテーブルとrevisionテーブルのみにアクセスします。pageテーブルのサイズは354.2MB、revisionテーブルのサイズは618.9MBということで、まずはこれらのテーブルがすべて収まるだけのメモリを割り当てることがチューニングの第一歩になるかと思います。

[mysqld]
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
character-set-server=utf8
innodb_buffer_pool_size=1G


8.8秒になりました。m1.xlargeの場合は、これで17秒程度になったようです。

チューニングその2：RESET QUERY CACHEの回避

クエリ0番にRESET QUERY CACHEがあるのを見てしまうと、やはりこれを回避したいと思うのが人情ではないでしょうか。本番で成功した方はいなかったのですが、回避の可能性には数名の方が気づいていたようです。

やってみましょう。
MySQL Proxyとは、MySQLの通信プロトコルを理解しMySQLクライアントとMySQLサーバとの通信を横取りしてさまざまな操作を行うサーバプログラムです。Luaスクリプトによって処理内容を定義し、ロードバランサとして使用したりクエリのプロファイリングを行ったりすることが想定されています。ライセンスはGPLv2です。なお長い間アルファ版のまま開発が進んでいないため、本番環境で使用することはおすすめしません。

-- tuningathon5_q0.lua
-- Q0: Ignoring 'RESET QUERY CACHE'

function read_query(packet)
    if string.byte(packet) == proxy.COM_QUERY then
        local query = string.sub(packet, 2)
        if string.find(query, "^%s*RESET /%* Q0 %*/ QUERY CACHE%s*$") then
            print("[HIT Q0]")
            proxy.queries:reset()
            proxy.queries:append(1, string.char(proxy.COM_QUERY) .. "FLUSH /* Q0_rewrite */ QUERY CACHE")
            return proxy.PROXY_SEND_QUERY
        end
    end
end

このLuaスクリプトで、RESET QUERY CACHEをFLUSH QUERY CACHEに書き換えてしまいます。RESET QUERY CACHEはクエリキャッシュを削除するコマンド、一方FLUSH QUERY CACHEはクエリキャッシュをデフラグするだけのコマンドです。これら二つのコマンドは処理が成功した際の戻りのパケットが同一ですので、クエリを書き換えていることはそう簡単には発覚しないはずです。

[mysqld]
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
character-set-server=utf8
innodb_buffer_pool_size=1G
port=3307
query_cache_size=1M

MySQLサーバのTCP待ち受けポートを変更し、クエリキャッシュの設定を施しておきます。今回のクエリに対しては、クエリキャッシュは1MBあれば足ります。MySQL Proxyを以下のオプションで起動し、MySQLクライアントから送られてくるクエリをMySQL Proxyで中継してMySQLサーバに渡すようにします。

$ /PATH/TO/mysql-proxy --proxy-address=:3306 \
                       --proxy-backend-addresses=:3307 \
                       --proxy-lua-script=tuningathon5_q0.lua


1.0秒になりました。優勝タイムは6.2秒とのことで、環境の違いを加味してもこの時点で上回ることができたのではないかと思います。

チューニングその3：クエリ1番の修正

RESET QUERY CACHEを回避することでクエリ3〜5番は速くなったのですが、実はクエリ1番と2番が速くなっていません。

mysql> SHOW GLOBAL STATUS LIKE 'Qcache_hits';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 107   |
+---------------+-------+
1 row in set (0.00 sec)

(計測プログラム実行)

mysql> SHOW GLOBAL STATUS LIKE 'Qcache_hits';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 110   |
+---------------+-------+
1 row in set (0.00 sec)

計測プログラムの前後でクエリキャッシュの状態を確認すると、3回しかクエリキャッシュにヒットしていないことが分かります。これはクエリ3〜5番の分です。なぜクエリ1番と2番はクエリキャッシュにヒットしないのでしょうか。まずクエリ1番から確認していきましょう。

mysql> SHOW GLOBAL STATUS LIKE 'Qcache_hits';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 110   |
+---------------+-------+
1 row in set (0.00 sec)

mysql>   SELECT /* Q1 */ COUNT(*)
    ->     FROM page p
    ->     JOIN revision r
    ->       ON p.page_id = r.rev_page
    ->    WHERE p.page_is_redirect = 0
    ->      AND p.page_namespace = 0
    ->      AND r.rev_user = 0;
+----------+
| COUNT(*) |
+----------+
|   273537 |
+----------+
1 row in set (0.00 sec)

mysql>  SHOW GLOBAL STATUS LIKE 'Qcache_hits';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 111   |
+---------------+-------+
1 row in set (0.00 sec)

クエリ1番を手打ちすると、きちんとクエリキャッシュにヒットしていることが分かります。原因を突き止めることはなかなか難しいと思いますが、これは、残念ながらMySQLの不具合です。

AUTOCOMMITがOFFの場合に、トランザクション開始前の最初のクエリがクエリキャッシュにヒットしないというものです。今も直っていません。PythonのMySQLdbモジュールはPython Database API Specification v2.0にもとづきデフォルトでAUTOCOMMITがOFFになっており、この不具合を必ず踏みます。ワークアラウンドとして「START TRANSACTIONで明示的にトランザクションを開始しておく」という方法がありますので、先ほどのLuaスクリプトを以下のように修正します。

-- tuningathon5_q0q1.lua
-- Q0: Ignoring 'RESET QUERY CACHE'
-- Q1: Workaround for Bug#42197

function read_query(packet)
    if string.byte(packet) == proxy.COM_QUERY then
        local query = string.sub(packet, 2)
        if string.find(query, "^%s*RESET /%* Q0 %*/ QUERY CACHE%s*$") then
            print("[HIT Q0]")
            proxy.queries:reset()
            proxy.queries:append(1, string.char(proxy.COM_QUERY) .. "FLUSH /* Q0_rewrite */ QUERY CACHE")
            proxy.queries:append(2, string.char(proxy.COM_QUERY) .. "START /* Q1_workaround */ TRANSACTION", { resultset_is_needed = true })
            return proxy.PROXY_SEND_QUERY
        end
    end
end

function read_query_result(inj)
    if inj.id == 2 then
        return proxy.PROXY_IGNORE_RESULT
    end
end

クエリ0番を実行した直後にSTART TRANSACTIONを実行します。その際START TRANSACTIONの結果をそのままクライアントに返すと計測プログラムが誤動作してしまいますので、read_query_result()ファンクションで結果を握りつぶしています。

0.56秒になりました。グラフでオレンジ色の部分がなくなったことが分かると思います。

チューニングその4：クエリ2番の修正

クエリ2番がクエリキャッシュにヒットしない理由は明快で、NOW()ファンクションを使用しているためです。

  SELECT /* Q2 */ COUNT(*)
    FROM page p
    JOIN revision r
      ON p.page_id = r.rev_page
   WHERE p.page_is_redirect = 0
     AND p.page_namespace = 0
     AND r.rev_user = 0
     AND p.page_touched > DATE_FORMAT(NOW() - INTERVAL 1 YEAR, '%Y0101000000');

マニュアルに、クエリキャッシュにヒットしなくなるファンクションの一覧が記載されています。

このクエリをどうやって直すかですが、あらためてDATE_FORMAT()ファンクションのところをよく見ると、出力結果が年月日のうち年にしか依存していないことが分かると思います。ここの部分は2013年1月1日〜12月31日までは「20120101000000」という文字列を、2014年1月1日〜12月31日までは「20130101000000」という文字列を返します。このくらいでしたらLuaスクリプトで同じ文字列を返すようにできます。

-- tuningathon5_q0q1q2.lua
-- Q0: Ignoring 'RESET QUERY CACHE'
-- Q1: Workaround for Bug#42197
-- Q2: Avoiding NOW() function

function read_query(packet)
    if string.byte(packet) == proxy.COM_QUERY then
        local query = string.sub(packet, 2)
        if string.find(query, "^%s*RESET /%* Q0 %*/ QUERY CACHE%s*$") then
            print("[HIT Q0]")
            proxy.queries:reset()
            proxy.queries:append(1, string.char(proxy.COM_QUERY) .. "FLUSH /* Q0_rewrite */ QUERY CACHE")
            proxy.queries:append(2, string.char(proxy.COM_QUERY) .. "START /* Q1_workaround */ TRANSACTION", { resultset_is_needed = true })
            return proxy.PROXY_SEND_QUERY
         elseif string.find(query, "^%s*SELECT /%* Q2 %*/ COUNT%(%*%)%s*"
                    .. "FROM page p%s*"
                    .. "JOIN revision r%s*ON p%.page_id = r%.rev_page%s*"
                    .. "WHERE p%.page_is_redirect = 0%s*"
                    .. "AND p%.page_namespace = 0%s*"
                    .. "AND r%.rev_user = 0%s*"
                    .. "AND p%.page_touched > DATE_FORMAT%(NOW%(%) %- INTERVAL 1 YEAR, '%%Y0101000000'%)%s*$")
         then
            print("[HIT Q2]")
            proxy.queries:reset()
            proxy.queries:append(1, string.char(proxy.COM_QUERY)
                .. "SELECT /* Q2_rewrite */ COUNT(*) "
                .. "FROM page p "
                .. "JOIN revision r ON p.page_id = r.rev_page "
                .. "WHERE p.page_is_redirect = 0 "
                .. "AND p.page_namespace = 0 "
                .. "AND r.rev_user = 0 "
                .. "AND p.page_touched > '" .. os.date('%Y') - 1 .. "0101000000'")
            return proxy.PROXY_SEND_QUERY
        end
    end
end

function read_query_result(inj)
    if inj.id == 2 then
        return proxy.PROXY_IGNORE_RESULT
    end
end


0.067秒になりました。このグラフでは何も見えなくなりました。拡大してみましょう。

最終的にクエリ4番が処理時間の大半を占めるようになりました。これは、クエリ4番の結果セットが39,434レコードと最も大きいためです。

勉強会のお知らせ

MySQL Proxyを使用してクエリキャッシュを無理やり効かせるという今回のチューニング方針ですが、MySQL Proxyがアルファ版であること、クエリキャッシュはCPUスケーラビリティが悪く最近は使われない傾向であることを考慮すると、実際の業務に応用することは難しくあまり筋が良いチューニング方針だとは言えません。
本エントリ執筆時点でまだ上手くできていないのですが、クエリキャッシュをOFFに戻して、ついでにMySQLを5.6にバージョンアップしてどこまで改善できるのかを調べています。調査結果について、今月久しぶりに開催されるMySQLユーザ会のイベントでご報告したいと思います。

当日は@yoku0825さんもMySQL 5.6の新機能について発表されます。ぜひ、ご参加ください。

Provisioned IOPSの検討 - JPOUG Advent Calendar 2012

2012年9月にAmazon RDSでProvisioned IOPSというサービスが利用できるようになりました。本日はこれを題材にして、IOPSを保証するということについて勉強していきたいと思います。このエントリは、JPOUG Advent Calendar 2012の17日目となります。16日目は@maroon1stさんでした。
Amazon RDSについては、@horiuchiさんが15日目のエントリでまとめ記事を書いていらっしゃいます。Provisioned IOPSについては記事の反響で第3位ということで、なかなか注目度が高かったことが分かりますね。

fioによるIOPSの測定

さて、まずはIOPSを測定するツールを準備しましょう。I/O関連のベンチマークツールはいろいろありますが、今回はfio - Flexible I/O Testerを使用することにしました。これは私が希望する測定パターンをすべて再現できたこと、Red Hat Enterprise Linuxやそのクローン、およびUbuntu向けにバイナリパッケージが用意されておりインストールが簡単なことが主な理由です。RHEL系の場合はRepoForceからバイナリパッケージを入手、Ubuntuの場合はapt-getコマンドでインストールできます。
fioを使用した最近のベンチマーク結果として、@namikawaさんがAmazon EC2 High I/O Quadruple Extra Large Instanceの測定結果を、@kazeburoさんがIntel SSD 910 800GBの測定結果を公開されています。

これを参考にしつつ、以下のようにカスタマイズしておきます。

  • テストファイルサイズを16GBに拡大します。これはRAIDコントローラやストレージのキャッシュによって性能が高ぶれするのを防ぐためです。OSのページキャッシュについてはDirect I/Oを使用することであらかじめ影響を排除しておきます。ディスクに余裕があればファイルサイズはもう少し増やしておきたいところで、例えばさくらインターネットさんではSSDプランの提供にあたり100GBで測定されています。
  • ブロックサイズ4KBに加えて8KB、16KBの測定を行います。8KBはOracle Databaseのデフォルトブロックサイズ、およびPostgreSQLのブロックサイズです。16KBはMySQL InnoDBのページサイズです。
  • 読み書きをミックスさせた測定を行います。読み取り100%、書き込み100%のほかに、読み取り：書き込み＝90：10、50：50、10：90での測定を追加します。
  • 非同期I/Oを使用します。多重アクセスによる性能の伸びを測定する際、fioでは-numjobs=64 -group_reportingとしてマルチスレッド実行をさせることができますが、非同期I/Oを使用して-ioengine=libaio -iodepth=64と指定することもできます。基本的に同じ結果が得られます。MySQL 5.1＋InnoDB Pluginが同期I/Oのマルチスレッド実行、MySQL 5.5が非同期I/Oを行っているということをふまえ、今回は非同期I/Oを採用します。
  • 非同期I/OにおけるQueue Depthのバリエーションを増やします。64以外に、1から256までの測定パターンを用意しておきます。なおSerial ATAの場合はNCQ(Native Command Queuing)の上限が32なので、高速なSSDを持ってきたとしても多重アクセスによる性能の伸びは32で頭打ちとなります。

できあがった測定スクリプトは以下のようになりました。測定パターンはシーケンシャル読み取り、シーケンシャル書き込みの2パターンに加え、ランダム読み書きがブロックサイズ3パターン×読み書き比率5パターン×Queue Depth 9パターンで135パターンあり、合計で137パターンとなります。1回の測定を60秒に設定していますので、全パターンの測定には2時間強かかります。


手元のパソコンで測定した結果をご紹介します。fioのログからこの表を出力するスクリプトを用意していますので、ご利用ください。



Plextor PX-256M2Pはカタログスペックで読み取りIOPSが70,000、書き込みIOPSが65,000となっていますが、ほぼカタログスペック通りの値が出ていることが分かると思います。黄色のセルは、Windowsでよく使われているCrystalDiskMarkと同じ測定を行っている部分です。


ブロックサイズが大きくなるとIOPSは下がっていきます。また読み書きをミックスさせると読み取りのみ、書き込みのみの場合に比べてIOPSが下がります。この傾向はPlextor以外のSSDでも同じでした。

cgroupによるIOPSの制御

では、IOPSの制御を試してみましょう。調べたところ、IOPSを制御する方法は2つ見つかりました。

  • QEMUのDisk I/O Limits機能を使う方法。IBMが中心となって開発が進められている機能です。KVM Forum 2011のウェブサイトにIO Throttling in QEMU (PDF)というプレゼンテーション資料が公開されています。
  • Linuxのcgroup(Control Group)機能を使う方法。cgroupはLinux Kernel 2.6.24からある機能ですが、2.6.37からブロックデバイスに対するI/O帯域、IOPSを制御できるようになりました。RHEL 6はKernel 2.6.32ですが、6.1にこの機能がバックポートされています。@enakai00さんがブログで詳しい解説をされています。

QEMUのDisk I/O Limits機能はまだ開発中ということで、今回はcgroupを使用しました。ここから先は仮想環境で作業を行っていきます。cgroupによるIOPS制御には技術的な制限事項があって、物理環境では少し使いづらいためです。この件については後述します。

  • Storage：Plextor PX-256M2P
  • Host OS：Scientific Linux 6.3 x86_64
  • Host I/O Scheduler：deadline
  • Host Filesystem：ext4 (rw,discard,nobarrier), 4KB aligned
  • KVM Disk Bus：virtio
  • KVM Storage Format：raw
  • KVM Cache Model：none
  • KVM I/O Mode：native
  • Guest OS：Scientific Linux 6.3 x86_64
  • Guest I/O Scheduler：noop
  • Guest Filesystem：ext4 (rw,nobarrier), 4KB aligned

まずIOPSを制御しない状態での、仮想環境の素の性能です。ブロックサイズによる傾向の違いはなかったので、ここから先は16KBの結果のみ掲載します。

物理環境とあまり変わらない形のグラフですが、Queue Depthが小さいときの性能の落ち込みが目立ちます。これはI/Oリクエストが2つのOSを経由するため、レイテンシが長くなっていることが原因です。Queue Depth 1の性能はシングルスレッドで行う大量処理、例えばLOAD DATAやALTER TABLEに効いてくるので、仮想化のオーバーヘッドというものは今でもそれなりにあります。
一方、ピーク性能は物理環境と変わりないことが分かります。処理を詰め込めるだけ詰め込んでしまえば、アライメントが合っていないなどのミスがなければ最終的にはストレージの限界性能が出ます。もちろん、CPUなど他のコンポーネントがボトルネックになっていないことが前提です。
次は、読み取り、書き込みとも10,000 IOPSに制限した測定結果です。これはKVMホストから以下のコマンドを実行して設定します。

# ls -l /dev/sda
brw-rw---- 1 root disk 8, 0  9月 16 23:00 2012 /dev/sda

# echo '8:0 10000' > /cgroup/blkio/libvirt/qemu/k01sl6/blkio.throttle.read_iops_device
# echo '8:0 10000' > /cgroup/blkio/libvirt/qemu/k01sl6/blkio.throttle.write_iops_device


非常にきれいなグラフになっています。ここまでうまくいくとは予想していませんでした。読み取り100%のときはちょうど10,000 IOPSになっており、90%のときは読み取り10,000＋書き込み1,111で11,111 IOPSとなります。50%のときは上限20,000 IOPSですが今回使用したSSDはそこまで性能が出ないため、16,634 IOPSとなっています。
続いて、3,000 IOPS、1,000 IOPSに制限した測定結果です。


きちんと効いていますね。かなり使える機能だと思います。

cgroup使用時の注意点

RHEL 6.3の時点では、cgroupによるI/O帯域、IOPSの制御には技術的な制限事項があります。それはBuffered Writeに対して正常に動作しないというものです。例としてKVMゲストでOS全体に書き込み1,000 IOPSの制限をかけると、以下のようになります。

# echo '252:32 0' > /cgroup/blkio/blkio.throttle.write_iops_device

# dd if=/dev/zero of=test.dat bs=131072 count=8192 oflag=direct
8192+0 records in
8192+0 records out
1073741824 bytes (1.1 GB) copied, 8.20527 s, 131 MB/s

oflag=directを付けた場合は8,192回の書き込みに8.2秒かかるということで、想定通りです。

# dd if=/dev/zero of=test.dat bs=131072 count=8192
8192+0 records in
8192+0 records out
1073741824 bytes (1.1 GB) copied, 202.553 s, 5.3 MB/s

# dd if=/dev/zero of=test.dat bs=131072 count=8192 oflag=dsync
8192+0 records in
8192+0 records out
1073741824 bytes (1.1 GB) copied, 309.503 s, 3.5 MB/s

しかし、oflag=direct以外の設定では極端に性能が劣化してしまいます。OSのバッファに書き込んでいる間は速いのですが、そこから溢れて実際にブロックデバイスに書き込みに行くところで制御がうまくいっていないようです。設定が効かなくてフルスピードになってしまうならまだしも、こんなに遅くなってしまっては困ります。
現状cgroupによるI/O帯域、IOPSの制御が期待通りに動作するのは、Direct I/Oに対してのみです。RHELのマニュアルにも注意事項が記載されています。

Currently, the Block I/O subsystem does not work for buffered write operations. It is primarily targeted at direct I/O, although it works for buffered read operations.

Red Hat Enterprise Linux 6 Chapter 3. Subsystems and Tunable Parameters - Red Hat Customer Portal

Oracle Database、MySQLはDirect I/Oを使用していますが、すべてのI/OがDirect I/Oというわけではありません。また、PostgreSQLはDirect I/Oを使用していません。これらのソフトウェアに対して直接cgroupでI/O帯域、IOPSの制御を行うことは、まだ無理です。
ではなぜこの機能がRHEL 6.1にバックポートされたのかですが、おそらくKVMでの利用を狙ってのことだと考えられます。qemu-kvmは設定によって、KVMゲスト側のI/O方式に関わらずKVMホスト側でDirect I/Oを行わせることができます。仮想化によるシステム統合やプライベートクラウド基盤の構築を行う際、KVMホスト側でqemu-kvmプロセスにcgroupの設定を行うことで、KVMゲストごとのI/Oリソース消費量を制御することができるというわけです。
qemu-kvmにDirect I/Oをさせるには、仮想ディスクのキャッシュモデルにnoneを指定します。

デフォルトはwritethroughです。writethroughの場合はBuffered I/Oが行われるため、cgroupによるI/O帯域、IOPSの制御は先ほどと同じように誤動作してしまいます。ご注意ください。

# dd if=/dev/zero of=test.dat bs=131072 count=8192 oflag=direct
8192+0 records in
8192+0 records out
1073741824 bytes (1.1 GB) copied, 262.503 s, 4.1 MB/s

なおwritebackは設定としては存在しているのですが、障害発生時にデータを失う危険性があるためサポート対象外となっています。
ここまでの内容から、RHEL 6.3の時点でIOPSの制御を行うための前提条件は以下のようになります。

  • KVMによる仮想化を行う
  • 仮想ディスクのキャッシュモデルにnoneを指定する
  • KVMホスト側でqemu-kvmプロセスに対しcgroupの設定を行う

ここでcgroupによるI/O帯域、IOPSの制御はブロックデバイスを対象とした機能ですので、以下の条件も追加されます。

  • KVMゲストのイメージファイルが、NFS上ではなくブロックデバイス上に配置されている

イメージファイルがブロックデバイス上に配置されていない場合は、もう一つの方法であるQEMUのDisk I/O Limits機能が提供されるのを待つことになるかと思います。

IOPSを保証するということ

話をAmazon RDSに戻すと、Amazon Web Servicesは仮想化基盤としてKVMではなくXenを使用しているので、ここまで述べたものとは別の方法でProvisioned IOPSを提供しているということになります。XenでIOPSを制御する方法はすぐには見つかりませんでしたが、もしDom 0でtapdiskプロセスにcgroupが効くのであれば似たような方法で実現できるのかもしれません。あるいは、もっと低いレイヤで実装しているのかもしれません。
IOPSを保証するというのはAmazon RDSのようなパブリッククラウドサービスだけでなく、プライベートクラウド基盤や一般のシステム開発にとっても重要な概念です。近年一つのシステムが一つのストレージを占有できる構成は少なくなってきており、システム基盤の共通化という方針のもと複数システムで少数のサーバやストレージを共有するようになってきています。その際CPUやメモリについては現行を踏襲したハードウェアリソースを割り当ててもらえるのですが、ストレージについては容量しか考慮してもらえないことが多く、あとから深刻な性能問題を起こすことが増えています。
IOPSというものがストレージ製品のカタログに書いていないことも、サイジングの際に容量しか考慮してもらえない原因の一つだと考えています。IOPSは同じストレージ製品でも構成によって大きく変わるので、確かにカタログに書きづらい部分はあります。そこで今回簡単にIOPSを測定できるスクリプトを用意しましたので、これを用いてさまざまなストレージ製品の性能を事前に収集しておくことをおすすめします。
プライベートクラウド基盤を設計する際は、特にDBMSを稼動させる計画がある場合、CPUコア数やメモリ量だけでなくIOPSについても目標値を定め、適切な設備投資を行ってください。
システム統合を行う際は、まず現行システムのリソース消費状況を調査すると思います。その際CPU使用率だけ見るのではなく、必ずI/O状況も見てください。Oracle Databaseであれば、Statspack/AWRレポートのLoad ProfileセクションからPhysical readsとPhysical writes、MySQLであればSHOW ENGINE INNODB STATUSからFILE I/Oセクションのreads/sおよびwrites/sを確認してください。I/O状況の調査は、夜間バッチや業務繁忙期などシステム負荷が高い時間帯を必ず含むようにしてください。そして、各システムのIOPSを合計したよりも高い性能のストレージ製品を調達してください。
Provisioned IOPSはユーザから見ると希望するIOPSを得られるというサービスですが、クラウド基盤提供側やシステム統合の立場から見ると違った意味があります。この機能には、特定のユーザやシステムが必要以上にI/Oリソースを占有して他のシステムに影響を与えることを防ぐ意味があります。現状さまざま前提条件がありますが、RHEL系でKVMを使用している場合は検討する価値のある機能だと思います。最大の課題はOracle DatabaseがKVM上で動作保証されていないことですね。まあ、システム統合のついでにPostgreSQLにマイグレーションするというのもいいかもしれませんね。
Oracle Databaseについていえば、IOPSが足りないときの究極の解決策はOracle Exadataを導入することです。I/O性能が本当に一桁違うので、現行システムのI/O状況があまり分かっていなくてもそこがボトルネックになることはないでしょう。ただEngineered Systemに任せきりになって、いつの間にか自社の技術力がなくなっていたということのないようにしたいものです。
JPOUG Advent Calendar 2012、18日目は@mutatsuさんです。それではまた。

tpcc-mysqlにおける初期データロードのチューニング

ピンポイントチューニング講座です。tpcc-mysqlを使ったことのある方はご存知かと思いますが、最初にtpcc_loadコマンドで行う初期データのロードがかなり遅いです。実は簡単にチューニングする方法があるので、今回はこれをご紹介したいと思います。
tpcc-mysqlについて復習しておきたい方は、以下のエントリを先にご覧ください。

TPC-CのER図を見ると、ほとんどのテーブルに倉庫ID(Warehouse ID)を示すカラムがあることが分かります。

TPC-Cでは倉庫テーブルのレコード件数がデータベース全体の規模を決めるスケールファクタになっていて、これを増やすとそれぞれのテーブルに倉庫IDが2のデータ、倉庫IDが3のデータ、…とデータが積み重なっていく形になっています。ここでそれぞれの倉庫IDのデータは互いに独立しているので、並列化をかけることができます。

商品テーブルのみスケールファクタと関係なく10万レコードで固定されているため、まず最初に商品テーブルのデータロードを行います。そのあと論理CPUの数だけ並列にデータロードを実行します。tpcc_loadにはデータの一部分だけをロードするオプションが用意されていますので、これを利用することができます。


クアッドコアの環境でスケールファクタ1,000、およそ100GBのデータロードを行ってみました。


並列度が低い場合はTurbo Boostが効くのでクアッドコアで4倍とまではいきませんが、きちんと性能が伸びていることが分かると思います。100GBのデータロードに2時間20分なら、まあ待てるかなといったところです。
余談ですがデータ・ウェアハウス向け製品ではデータロードの性能は毎時10TBに達するそうで、MySQLなどとはまるで別世界の話になっています。すごいですね。