Hatena::ブログ(Diary)

SH2の日記 RSSフィード

2010-04-15 ServersMan@VPSでMySQL InnoDB Pluginをあきらめない このエントリーを含むブックマーク

DTIの仮想専用サーバServersMan@VPSを借りてみました。

Entryプランはメモリが256MBでまあ足りるだろうと思っていたのですが、ServersMan@VPSではOpenVZという仮想化ソフトウェアを使っていて、なんとスワップの利用が禁止されているのだそうです。つまりなにがなんでも総メモリ使用量を256MB以下に抑える必要があります。ちょっと難しそうです。

とりあえずMySQL 5.1.45をインストールして、すべてデフォルトで起動するとこんな感じです。

# free -m
             total       used       free     shared    buffers     cached
Mem:           256         23        232          0          0          0
-/+ buffers/cache:         23        232
Swap:            0          0          0

# service mysql start
Starting MySQL. SUCCESS!

# free -m
             total       used       free     shared    buffers     cached
Mem:           256        142        113          0          0          0
-/+ buffers/cache:        142        113
Swap:            0          0          0

(# top)
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
11692 mysql     20   0  124m  13m 3216 S  0.0  5.2   0:00.02 mysqld

このように、MySQLにメモリを半分ほど持っていかれてしまいます。これは厳しい。

InnoDB Pluginを有効にすると事態はさらに悪化します。InnoDB Pluginではinnodb_buffer_pool_sizeのデフォルト値が128MBになっているのでそのままでは起動すらできず、設定を絞ってみても…

[mysqld]
ignore-builtin-innodb
plugin-load = innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_l
ocks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_inn
odb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugi
n.so;innodb_cmpmem_reset=ha_innodb_plugin.so
(plugin-loadは実際には1行)

innodb_buffer_pool_size = 8M
# free -m
             total       used       free     shared    buffers     cached
Mem:           256        189         66          0          0          0
-/+ buffers/cache:        189         66
Swap:            0          0          0

(# top)
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 7457 mysql     20   0  172m  20m 3200 S  0.0  8.1   0:00.03 mysqld

と、かなりひどいことになります。これで運用するのはまず無理ですね。

ちなみにInnoDBを無効にするとほぼ問題がなくなります。Virtuozzo/OpenVZ環境ではこの構成で運用しているところが多いようです。

[mysqld]
skip-innodb
# free -m
             total       used       free     shared    buffers     cached
Mem:           256         45        210          0          0          0
-/+ buffers/cache:         45        210
Swap:            0          0          0

(# top)
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
18331 mysql     20   0 28780 4388 2748 S  0.0  1.7   0:00.00 mysqld

Virtuozzo/OpenVZでInnoDB Pluginをあきらめない設定例

というわけで限界までダイエットしてみました。

[mysqld]
character_set_server = utf8
collation_server = utf8_general_ci

transaction_isolation = READ-COMMITTED

ignore-builtin-innodb
plugin-load = innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_l
ocks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_inn
odb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugi
n.so;innodb_cmpmem_reset=ha_innodb_plugin.so
(plugin-loadは実際には1行)

default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 1
innodb_buffer_pool_size = 4M
innodb_log_buffer_size = 1M
innodb_log_file_size = 128MB

#server_id = 1
#log_bin = mysql-bin
#binlog_format = MIXED
#binlog_cache_size = 128K
#sync_binlog = 1

slow_query_log = 1
long_query_time = 0.1

query_cache_type = 1
query_cache_size = 1M

key_buffer_size = 512K
sort_buffer_size = 512K
read_buffer_size = 128K

max_allowed_packet = 16M
max_connections = 64
thread_cache_size = 8
table_open_cache = 4096

[client]
default_character_set = utf8

内訳は以下の通りです。

  • サーバあたりで確保されるメモリ設定
    • innodb_buffer_pool_size = 4M
    • innodb_log_buffer_size = 1M
    • key_buffer_size = 512K (MyISAMは使わないので必要最低限)
    • query_cache_size = 1M
  • コネクションあたりで確保されるメモリ設定
    • sort_buffer_size = 512K
    • read_buffer_size = 128K
    • binlog_cache_size = 128K

innodb_log_file_sizeを128MBにしていますが、このパラメータにinnodb_buffer_pool_sizeより大きな値を指定しても基本的には意味がありません。将来ServersMan@VPSの上位プランに移行したときにdatadir以下をそのまま持っていけるように、あえて大きめの値にしています。

もう一つ、/etc/init.d/mysqlの以下の場所にulimit -s 192を書き加えて、スレッドごとのスタックサイズを192KBに設定します。

case "$mode" in
  'start')
    # Start daemon
    ulimit -s 192

    # Safeguard (relative paths, core dumps..)
    cd $basedir

    manager=$bindir/mysqlmanager

本来MySQLではthread_stackというパラメータでスレッドごとのスタックサイズを設定できるのですが、どうもInnoDBスレッドにはこの設定が効かないようです。バグかもしれませんのであとで調べてOracleに報告しておきます。

CentOS 5におけるデフォルトスタックサイズは10MBですので、ulimitで強制的に絞ることによって約9.8MB×InnoDBスレッド数のメモリを節約できることになります。InnoDB Pluginで初期状態におけるメモリ使用量が多かったのは、ファイルI/Oスレッド数がオリジナルのInnoDBよりも増えているためです。

# free -m
             total       used       free     shared    buffers     cached
Mem:           256         41        214          0          0          0
-/+ buffers/cache:         41        214
Swap:            0          0          0

(# top)
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
26364 mysql     18   0 25148 9552 3212 S  0.0  3.6   0:01.52 mysqld

ここまでの設定を行った結果、InnoDB Pluginを有効にした状態でメモリ使用量を25MBまで抑えることができました。他のソフトウェアを動かしてもまだ余裕があるようであれば、innodb_buffer_pool_sizeを16〜32MB程度に増やしてもよいと思います。