MyISAM と InnoDB の更新によるパフォーマンスの違い

とある、20万 PV/日 の WordPress サイトの運用を手伝っているのだが DB の一部に MyISAM が使われている。具体的には、WordPress Popular Posts のテーブルで、多分一番負荷が高いのは popularpostssummary だと思われる。

よく、参照が多いなら MyISAM で、更新が多いなら InnoDB とかいうが、MySQL の slave で、しかも、ほとんど SQL アクセスがこないようにしているサーバは、InnoDB にしたところ顕著に負荷が増えた。

バッファプールが小さすぎるのだろうか…？増やしてみて、様子を見たい。

> SHOW STATUS LIKE 'key%';
+------------------------+-----------+
| Variable_name          | Value     |
+------------------------+-----------+
| Key_blocks_not_flushed | 0         |
| Key_blocks_unused      | 13383     |
| Key_blocks_used        | 13396     |
| Key_read_requests      | 262237806 |
| Key_reads              | 143254    |
| Key_write_requests     | 26889447  |
| Key_writes             | 18958314  |
+------------------------+-----------+

SHOW ENGINE INNODB STATUS;
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 2367377
Buffer pool size 8191
Free buffers 1
Database pages 8113
Old database pages 2974
Modified db pages 32
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 34678, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 18398, created 16387, written 2271625
0.00 reads/s, 0.00 creates/s, 10.58 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 8113, unzip_LRU len: 0
I/O sum[493]:cur[0], unzip sum[0]:cur[0]

という感じである…。

[MySQL] InnoDBを積極的に使わないケース によれば「データ＞バッファ　になるとアウト！」だそうだが…

mysql> select sum( index_length ) from information_schema.tables where engine = 'myisam';
+---------------------+
| sum( index_length ) |
+---------------------+
| 1301504 |
+---------------------+
1 row in set (3.54 sec)

mysql> show variables like 'key_buffer_size';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| key_buffer_size | 16777216 |
+-----------------+----------+
1 row in set (0.00 sec)

mysql> select sum( data_length ) from information_schema.tables where engine = 'innodb';
+--------------------+
| sum( data_length ) |
+--------------------+
| 290717696 |
+--------------------+
1 row in set (1.80 sec)

mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)

MySQL tuner すると

[!!] Table cache hit rate: 15% (400 open / 2K opened)
[!!] InnoDB buffer pool / data size: 128.0M/443.1M
[OK] InnoDB Read buffer efficiency: 99.95% (37917204 hits/ 37937035 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)

が気になる所だろうか…
innodb_buffer_pool_size を 384M にあげて、様子を見てから、table_open_cache を 800 くらいにしてみよう。

あとは、http://takahashikzn.root42.jp/entry/2015/02/08/212840 と innodb_log_file_size か。

ログファイルの更新が結構頻繁…なので
http://d.hatena.ne.jp/koziy/20070911/1189472682
http://dev.mysql.com/doc/refman/5.6/ja/innodb-data-log-reconfiguration.html
に従って innodb_log_file_size を変える。256M にしてみよう。

misc

Posted by tako