このサイトの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にしても遅くなる結果しか出ていないため実装 & 記事化はしばらくお預けになりそうです。
