MySQL5.6 innodb_file_formatをAntelopeからBarracudaにオンラインで切り替える

遅くなったVPNサーバのレスポンスを少しでも改善すべく、MySQL5.6のパフォーマンス改善を目指してinnodb_file_formatをBarracudaに切り替えてみました。

1. my.cnfファイルにinnodb_file_formatの行を追加する

my.cnfはmysqldを再起動しないと反映されませんが、とりあえず先に追加しておきます。
innodb_file_per_tableはBarracudaに変更するための必須設定ですが、InnoDBへ変更した際に設定済なのでこの場での説明はパスします。

# vi /etc/my.cnf
[mysqld]
innodb_file_per_table = 1
+ innodb_file_format = Barracuda
+ innodb_file_format_max = Barracuda
2. オンラインで対応フォーマットをBarracuda対応に切り替える

起動中のmysqlをBarracudaに対応させます。

mysql> show variables like '%file_format%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_file_format       | Antelope |
| innodb_file_format_check | ON       |
| innodb_file_format_max   | Antelope |
+--------------------------+----------+

mysql> set global innodb_file_format = Barracuda ;
Query OK, 0 rows affected (0.02 sec)

mysql> set global innodb_file_format_max = Barracuda ;
Query OK, 0 rows affected (0.02 sec)

mysql> show variables like '%file_format%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
+--------------------------+-----------+
3 rows in set (0.00 sec)
3. Barracudaに切り替える前に各テーブルのRow_formatを確認

innodb_file_formatは対応フォーマットを切り替えるだけなのでDBの各テーブルも個別に切り替える必要があります。まずはテーブル一覧と各テーブルのRow_formatを確認します。

mysql> use wordpress_db;
mysql> show tables;
+------------------------+
| Tables_in_wordpress_db |
+------------------------+
| wp_cf_access           |
| wp_cf_number           |
| wp_commentmeta         |
| wp_comments            |
| wp_links               |
| wp_options             |
| wp_postmeta            |
| wp_posts               |
| wp_term_relationships  |
| wp_term_taxonomy       |
| wp_termmeta            |
| wp_terms               |
| wp_usermeta            |
| wp_users               |
+------------------------+

mysql> SHOW TABLE STATUS LIKE 'wp_cf_access'\G
*************************** 1. row ***************************
           Name: wp_cf_access
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 520
 Avg_row_length: 189
    Data_length: 98304
Max_data_length: 0
   Index_length: 114688
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2016-10-20 20:23:14
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

「Row_format: Compact」からCompactが使われていることがわかります。CompactはInnoDBのデフォルト値で性能と互換性のバランスが良いフォーマットとのこと。これを性能重視のDynamic(Barracuda)に切り替えます。

4. 各テーブルをBarracuda(Dynamic)に切り替える

ALTER TABLEはテーブルの定義を変更するコマンド。これに「ROW_FORMAT = DYNAMIC」と続けることで指定のフォーマットに切り替えることができます。なおALTER TABLEデータを全てコピーしなおすの挙動なので安全とのことですよ。

mysql> ALTER TABLE wp_commentmeta ROW_FORMAT = DYNAMIC;
mysql> ALTER TABLE wp_comments ROW_FORMAT = DYNAMIC;
mysql> ALTER TABLE wp_links ROW_FORMAT = DYNAMIC;
mysql> ALTER TABLE wp_media_file_manager_log ROW_FORMAT = DYNAMIC;
mysql> ALTER TABLE wp_options ROW_FORMAT = DYNAMIC;
mysql> ALTER TABLE wp_postmeta ROW_FORMAT = DYNAMIC;
mysql> ALTER TABLE wp_posts ROW_FORMAT = DYNAMIC;
mysql> ALTER TABLE wp_term_relationships ROW_FORMAT = DYNAMIC;
mysql> ALTER TABLE wp_term_taxonomy ROW_FORMAT = DYNAMIC;
mysql> ALTER TABLE wp_termmeta ROW_FORMAT = DYNAMIC;
mysql> ALTER TABLE wp_terms ROW_FORMAT = DYNAMIC;
mysql> ALTER TABLE wp_usermeta ROW_FORMAT = DYNAMIC;
mysql> ALTER TABLE wp_users ROW_FORMAT = DYNAMIC;
・・・
・・
・
mysql> ALTER TABLE  wp_cf_access ROW_FORMAT = DYNAMIC;
ALTER TABLE  wp_cf_number ROW_FORMAT = DYNAMIC;
Query OK, 0 rows affected (0.29 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE  wp_cf_number ROW_FORMAT = DYNAMIC;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0
5. Barracuda(Dynamic)に切り替わったことを確認

Row_formatとCreate_optionsの行を確認します。
なおCreate_optionsが「row_format=DYNAMIC」になっていても「innodb_file_format = Barracuda」を指定していないと「Row_format: Compact」から変わらないとのこと。

mysql> SHOW TABLE STATUS LIKE 'wp_cf_access'\G
*************************** 1. row ***************************
           Name: wp_cf_access
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 520
 Avg_row_length: 220
    Data_length: 114688
Max_data_length: 0
   Index_length: 114688
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2016-10-20 20:19:31
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: row_format=DYNAMIC
        Comment:
1 row in set (0.00 sec)
6. Barracudaの効果は？

月間14,000/PV程度のWordPressサイトでは高速化の効果を測定できませんでした。元々MySQLがボトルネックにはなっていないので当然ではあるのですが、何かしらの成果を期待していたので残念です。ですがBarracudaのデメリットはありませんし、将来的に「Row_format:COMPRESSED」を利用するための下準備が出来たので良しとします。

スポンサーリンク

,