MySQLのメモリ使用量の調整

mysqldがメモリ不足により落ちる事があります。
正確には、OOM Killerというのがメモリ不足を検知して、
メモリを多く使ってるプロセスをkillしてしまうようです。

OOM Killer とは

Linuxの安定稼働の為、メモリを大量に消費しようとしているプロセスを
スコアリングして、自動でkillする仕組み。

OOM Killerにkillされたプロセスは、/var/log/messages にログが出力される。

$ grep Kill /var/log/messages
Feb  22 14:32:00 host1 kernel: Out of memory: Kill process 25968 (mysqld) score 78 or sacrifice child
Feb 22 14:32:00 host1 kernel: Killed process 25968, UID 497, (mysqld) total-vm:13896624kB, anon-rss:2082152kB, file-rss:196kB

こんな感じで、OOMキラーによって、
プロセスがkillされた場合、当時のメモリ使用量が記録される。

total-vm 割当メモリ範囲
rss 実利用メモリ
vss 仮想メモリ

rssが実際の使用メモリになる。

innodb_buffer_pool_size を設定する。

mysqldの利用メモリ量で効果があるのが、
innodb_buffer_pool_sizeを調整するということ。

mysqldがセレクトや、インサートなどでメモリ上に展開したデータ(インデックスも含まれる)をそのままキャッシュしている。
キャッシュすることで、処理の高速化がされる一方でその分メモリを専有する。

指定したメモリサイズ内で、キャッシュされるので、
pool_sizeが足りないときは、利用頻度の少ないデータがキャッシュから破棄される。

処理速度優先にしたい場合は、
全データがここにのるくらいのpool_sizeにするといいみたいだが、

メモリ量に限度がある場合は、
適切なサイズに設定しておくほうがよい。

現在の設定値を確認する

現在の使用状況を調べるには、DB接続して以下のSQLを流すとレポートが確認できる。
SHOW ENGINE INNODB STATUS

出力結果には改行が含まれている為、垂直表示にしてやると見やすい。
SQL文の末尾に \G をつけることによって、出力結果が垂直表示になる。

$ mysql -u root -e "SHOW ENGINE INNODB STATUS\G;" | less
...
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 510820352; in additional pool allocated 0
Total memory allocated by read views 20632
Internal hash tables (constant factor + variable factor)
    Adaptive hash index 13182656    (7902808 + 5279848)
    Page hash           494696 (buffer pool 0 only)
    Dictionary cache    11234265    (1977296 + 9256969)
    File system         24353272    (812272 + 23541000)
    Lock system         1443032     (1235672 + 207360)
    Recovery system     0   (0 + 0)
Dictionary memory allocated 9256969
Buffer pool size        30463
Buffer pool size, bytes 499105792
Free buffers            1024
Database pages          29117
Old database pages      10728
Modified db pages       10
Percent of dirty pages(LRU & free pages): 0.033
Max dirty pages percent: 90.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1740, not young 301789
0.00 youngs/s, 7.83 non-youngs/s
Pages read 67127, created 48, written 3333
1.78 reads/s, 0.00 creates/s, 0.03 writes/s
Buffer pool hit rate 981 / 1000, young-making rate 0 / 1000 not 83 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 29117, unzip_LRU len: 0
I/O sum[182]:cur[2], unzip sum[0]:cur[0]
...

レポートが大量に出力されるので、"MEMORY"などで検索すると良い。

  • Total memory allocated
    innodb_buffer_pool_size設定をもとに割当可能になるメモリサイズ

  • Buffer pool size
    ページという単位で表現されているが、buffer可能な全ページサイズ

  • Free buffers
    Buffer pool sizeのうち、フリーとしてまだ利用されていないページサイズ

my.cnf で innodb_buffer_pool_sizeを設定する

/etc/my.cnf もしくは、!includedir /etc/my.cnf.d などと書かれていれば、
/etc/my.cnf.d 配下にあるファイルに記載すれば良い。

# 単位はバイト
innodb_buffer_pool_size = 500000000

↑は500MBに設定している。

mysqldの再起動

mysqldの再起動で設定が反映される。
設定の書き方に問題があれば、起動に失敗するで
その場合は、設定ファイルを修正して再度やってみよう。

# mysqld status
sudo service mysql status

# mysqld restart
sudo service mysql restart

DB毎のデータサイズ集計

DBに保存されているデータサイズってどのくらいあるの?

結構時間かかるが、下記SQLで集計可能

SELECT table_schema, sum( data_length + index_length ) /1024 /1024 AS MB
FROM information_schema.tables
GROUP BY table_schema
ORDER BY MB DESC;

e.g. 集計結果

table_schema    MB
db1 528.53125000
db2 389.82812500
db3 309.07812500
mysql   120.77089787
information_schema  0.15625000
performance_schema  0.00000000

コメントを残す