WordPressのDBをMyISAMからInnoDBに変更する

このサイトのMySQLストレージエンジンは「高速」という理由でMyISAMを使っています。
ですが適切なチューニングを行えばInnoDBは遅くないですし、何よりMyISAMよりも安全なはず。運用中のDBを弄るのはリスクもありますが、今後の為にInnoDB化します。

1.事前準備編

まずはmysqltunerで現状のパフォーマンスを確認します。

# mysqltuner
-------- Performance Metrics -------------------------------------------------
[--] Up for: 29d 16h 40m 2s (1M q [0.566 qps], 44K conn, TX: 5B, RX: 499M)
[--] Reads / Writes: 83% / 17%
[--] Total buffers: 60.0M global + 2.9M per thread (12 max threads)
[OK] Maximum possible memory usage: 94.5M (9% of installed RAM)
[OK] Slow queries: 0% (2/1M)
[OK] Highest usage of available connections: 83% (10/12)
[OK] Key buffer size / total MyISAM indexes: 8.0M/458.0K
[OK] Key buffer hit rate: 99.9% (11M cached / 12K reads)
[OK] Query cache efficiency: 52.9% (591K cached / 1M selects)
[OK] Query cache prunes per day: 11
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 100K sorts)
[!!] Temporary tables created on disk: 43% (68K on disk / 157K total)
[OK] Thread cache hit rate: 99% (10 created / 44K connections)
[!!] Table cache hit rate: 2% (34 open / 1K opened)
[OK] Open file limit used: 6% (67/1K)
[OK] Table locks acquired immediately: 99% (645K immediate / 646K locks)

apacheベンチも実行しときます。(※WP Super Cacheを無効化した検証環境の実施結果)

# ab -n 1000 -c 10 https://localhost/
Requests per second:    14.01 [#/sec] (mean)
Time per request:       713.914 [ms] (mean)
Time per request:       71.391 [ms] (mean, across all concurrent requests)
Transfer rate:          150.15 [Kbytes/sec] received

次にmysqlにログインしてWordPress用のテーブル名を確認します。

# mysql -uroot -p
mysql>use wp_wordpress;
mysql> select
table_name, engine,TABLE_COLLATION
from information_schema.tables 
where table_schema=database() 
order by (table_name) desc; 

+-------------------------+------+---------------+
|table_name               |engine|TABLE_COLLATION|
+-------------------------+------+---------------+
|wp_users                 |MyISAM|utf8_general_ci|
|wp_usermeta              |MyISAM|utf8_general_ci|
|wp_term_taxonomy         |MyISAM|utf8_general_ci|
|wp_term_relationships    |MyISAM|utf8_general_ci|
|wp_terms                 |MyISAM|utf8_general_ci|
|wp_termmeta              |MyISAM|utf8_general_ci|
|wp_site_cache            |MyISAM|utf8_general_ci|
|wp_posts                 |MyISAM|utf8_general_ci|
|wp_postmeta              |MyISAM|utf8_general_ci|
|wp_options               |MyISAM|utf8_general_ci|
|wp_media_file_manager_log|MyISAM|utf8_unicode_ci|
|wp_links                 |MyISAM|utf8_general_ci|
|wp_comments              |MyISAM|utf8_general_ci|
|wp_commentmeta           |MyISAM|utf8_general_ci|
+-------------------------+------+---------------+
14 rows in set (0.00 sec)

WordPressのストレージエンジンをMyISAMで行くと決めた時からメモリ節約のためInnoDBは無効化していました。InnoDBを再有効化+最適化するには、InnoDBログファイルを再作成する必要があるのでファイルを削除します。

# ll -h /var/lib/mysql/
total 21M
-rw-rw---- 1 mysql mysql  10M Jan 15 2015  ibdata1
-rw-rw---- 1 mysql mysql 5.0M Jan 15 2015  ib_logfile0
-rw-rw---- 1 mysql mysql 5.0M Jan  1 2015  ib_logfile1
drwx------ 2 mysql mysql 4.0K Nov  8 14:49 mysql
srwxrwxrwx 1 mysql mysql    0 Feb 11 18:37 mysql.sock
drwx------ 2 mysql mysql 4.0K Mar 12 03:52 wp_wordpress

# rm -f /var/lib/mysql/ib_*
# ll -h /var/lib/mysql/
total 10M
-rw-rw---- 1 mysql mysql  10M Jan 15 2015  ibdata1
drwx------ 2 mysql mysql 4.0K Nov  8 14:49 mysql
srwxrwxrwx 1 mysql mysql    0 Feb 11 18:37 mysql.sock
drwx------ 2 mysql mysql 4.0K Mar 12 03:52 wp_wordpress
2.InnoDBを有効化する

まずはmy.cnfのInnoDB無効化設定をコメントアウトしInnoDBの初期設定を追加します。

# vi /etc/my.cnf
- skip-innodb
+ #skip-innodb
+ innodb_data_file_path = ibdata1:64M:autoextend
+ innodb_buffer_pool_size = 20M
+ innodb_file_per_table
+ innodb_autoextend_increment = 32
+ innodb_additional_mem_pool_size = 4M
+ innodb_log_buffer_size = 8M
+ innodb_log_file_size = 16M
+ innodb_flush_log_at_trx_commit = 2

mysqlを再起動してInnoDBを有効に出来るか、起動時に変なエラーが無いか確認します。

# service mysqld restart
Stopping mysqld:                      [ OK ]
Starting mysqld:                      [ OK ]

# tail -n 30 /var/log/mysqld.log
Version: '5.1.73' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
160312 11:36:19 [Note] /usr/libexec/mysqld: Normal shutdown

160312 11:36:19 [Note] Event Scheduler: Purging the queue. 0 events
160312 11:36:19 InnoDB: Starting shutdown...
160312 11:36:24 InnoDB: Shutdown completed; log sequence number 0 44233
160312 11:36:24 [Note] /usr/libexec/mysqld: Shutdown complete

160312 11:36:24 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
160312 11:36:25 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
160312 11:36:25 InnoDB: Initializing buffer pool, size = 20.0M
160312 11:36:25 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
160312 11:36:25 InnoDB: Setting file ./ibdata1 size to 64 MB
InnoDB: Database physically writes the file full: wait...
160312 11:36:25 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 16 MB
InnoDB: Database physically writes the file full: wait...
160312 11:36:25 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 16 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
160312 11:36:25 InnoDB: Started; log sequence number 0 0
160312 11:36:25 [Note] Event Scheduler: Loaded 0 events
160312 11:36:25 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.73' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution

failの文字がないのでInnoDBは起動したっぽい。念のためmysqlのhave_innodbを参照して確認します。

# mysql -uroot -pmysql> show variables like "have_inno%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb   | YES   |
+---------------+-------+
1 row in set (0.00 sec)

Yesになっているのでバッチリです。

3.WordPressのテーブルをInnoDBに変換する

あとはmysqlにログインし「ALTER TABLE テーブル名 engine=InnoDB;」を実行するだけです。
DBの静止状態を作るためにservice httpd stopでhttpdを止めてから実行するつもりでしたが、ついうっかり停止せずやってしまいました。問題なかったので結果オーライです。

# mysql -uroot -puse wp_wordpress;

ALTER TABLE wp_users engine=InnoDB;
ALTER TABLE wp_usermeta engine=InnoDB;
ALTER TABLE wp_term_taxonomy engine=InnoDB;
ALTER TABLE wp_term_relationships engine=InnoDB;
ALTER TABLE wp_terms engine=InnoDB;
ALTER TABLE wp_termmeta engine=InnoDB;
ALTER TABLE wp_site_cache engine=InnoDB;
ALTER TABLE wp_posts engine=InnoDB;
ALTER TABLE wp_postmeta engine=InnoDB;
ALTER TABLE wp_options engine=InnoDB;
ALTER TABLE wp_media_file_manager_log engine=InnoDB;
ALTER TABLE wp_links engine=InnoDB;
ALTER TABLE wp_comments engine=InnoDB;
ALTER TABLE wp_commentmeta engine=InnoDB;

mysql> select
table_name, engine,TABLE_COLLATION
from information_schema.tables 
where table_schema=database() 
order by (table_name) desc; 

+-------------------------+------+---------------+
|table_name               |engine|TABLE_COLLATION|
+-------------------------+------+---------------+
|wp_users                 |InnoDB|utf8_general_ci|
|wp_usermeta              |InnoDB|utf8_general_ci|
|wp_term_taxonomy         |InnoDB|utf8_general_ci|
|wp_term_relationships    |InnoDB|utf8_general_ci|
|wp_terms                 |InnoDB|utf8_general_ci|
|wp_termmeta              |InnoDB|utf8_general_ci|
|wp_site_cache            |InnoDB|utf8_general_ci|
|wp_posts                 |InnoDB|utf8_general_ci|
|wp_postmeta              |InnoDB|utf8_general_ci|
|wp_options               |InnoDB|utf8_general_ci|
|wp_media_file_manager_log|InnoDB|utf8_unicode_ci|
|wp_links                 |InnoDB|utf8_general_ci|
|wp_comments              |InnoDB|utf8_general_ci|
|wp_commentmeta           |InnoDB|utf8_general_ci|
+-------------------------+------+---------------+
14 rows in set (0.00 sec)

InnoDB化できました。
早速ベンチを取ります。(※WP Super Cacheを無効化した検証環境の実施結果)

#  ab -n 1000 -c 10 https://localhost/
Requests per second:    14.15 [#/sec] (mean)
Time per request:       706.741 [ms] (mean)
Time per request:       70.674 [ms] (mean, across all concurrent requests)
Transfer rate:          151.45 [Kbytes/sec] received

MyISAMの時は14.01[#/sec]でしたので、少なくとも遅くなっていないようです。成功かな?

4.InnoDB用のパラメータチューニング

このままではMyISAMとInnoDB両方のキャッシュを確保していることになるので無駄なメモリがあります。1GBでやり繰りするためは少しでもメモリを節約したいのでMyISAM用の設定を削除します。取りあえず今のところ設定しているmy.cnfのパラメータはこんなところ。

# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
character-set-server=utf8
skip-character-set-client-handshake
default-storage-engine=InnoDB
skip-name-resolve
skip-external-locking

#innoDB turning
innodb_data_file_path = ibdata1:64M:autoextend
innodb_log_file_size = 16M
innodb_buffer_pool_size = 20M
innodb_file_per_table
innodb_autoextend_increment = 16
innodb_additional_mem_pool_size = 4M
innodb_log_buffer_size = 4M
innodb_flush_log_at_trx_commit = 2
skip_innodb_doublewrite

query_cache_size=20M
table_open_cache = 64
tmp_table_size=16M
max_heap_table_size=16M
max_connections = 12
thread_cache_size = 20
max_allowed_packet = 1M
sort_buffer_size = 512k
read_buffer_size = 512k
read_rnd_buffer_size = 1M
key_buffer_size = 2M
#myisam_sort_buffer_size = 1M
wait_timeout = 300

ついでにmysqltunerの結果も。

# mysqltuner
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in InnoDB tables: 3M (Tables: 14)
[!!] Total fragmented tables: 2

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 3m 16s (7K q [1.870 qps], 262 conn, TX: 26M, RX: 3M)
[--] Reads / Writes: 79% / 21%
[--] Total buffers: 66.0M global + 2.4M per thread (12 max threads)
[OK] Maximum possible memory usage: 94.5M (9% of installed RAM)
[OK] Slow queries: 0% (0/7K)
[OK] Highest usage of available connections: 25% (3/12)
[OK] Key buffer size / total MyISAM indexes: 2.0M/89.0K
[OK] Query cache efficiency: 40.2% (2K cached / 5K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 540 sorts)
[!!] Temporary tables created on disk: 45% (408 on disk / 904 total)
[OK] Thread cache hit rate: 98% (3 created / 262 connections)
[OK] Table cache hit rate: 84% (38 open / 45 opened)
[OK] Open file limit used: 4% (46/1K)
[OK] Table locks acquired immediately: 100% (3K immediate / 3K locks)
[OK] InnoDB data size / buffer pool: 3.5M/20.0M

結果はMyISAMの時と変わらず、概ね良好でした。何故かTotal fragmented tablesの値が2から減りませんが、InnoDB テーブルのデフラグメント化を試しても変わらなかったので諦めます。

InnoDB化は安全性の確保が目的でしたが、MySQL5.6へバージョンアップすることによる高速化の布石でもあったりします。既に検証環境で試していますが、MySQL5.6にしても遅くなる結果しか出ていないため実装 & 記事化はしばらくお預けになりそうです。

スポンサーリンク
レクタングル (大)

レクタングル (大)