遅くなった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」を利用するための下準備が出来たので良しとします。