トップ > IT > MySQL で使用するメモリサイズの見積もり

MySQL で使用するメモリサイズの見積もり

IT

最近、MySQLのパラメータの調整をする機会があったのですが、特定のパラメータを変更した際に、メモリの消費量にどう影響するのか、というのを調査する際に、インターネッツを彷徨ったところ、サイトによって書いてあることにバラつきがあったので、自分でもまとめてみることにした。

結論から書くと、参考にしたのは以下のオライリーの書籍「MySQLトラブルシューティング」で、記述が一番わかりやすく書かれていた。
このエントリは、この書籍の 「3.9.3 オプションの安全値を計算する」 にて記載がある内容をまとめたものになる。

MySQLトラブルシューティング

MySQLトラブルシューティング

Amazon

著者について
Sveta Smirnova(スヴェータ・スミルノヴァ): Oracle社MySQLサポートグループ・バグ検証グループの主席テクニカルサポートエンジニアとして毎日MySQLのバグ修正とサポートを担当する。

発売は2012年と少し古い本で、本書によると対象としているMySQLのバージョンは5.5の模様。なので、少し古い記述ではあると思う。

尚、MySQL 5.7 や 8.0 については、以下の公式ドキュメントに近しい内容がかかれている（と思う）。

メモリ使用量のざっくり計算式

メモリ使用量 = グローバルバッファの使用量 + max_connections * スレッドバッファの使用量

上記に加えて、OSのメモリ使用量等を加えたものが、サーバの物理メモリ量を超えないように調整する必要があります。

グローバルバッファ

このオプションはグローバルで、すべての接続とクエリに影響。
サーバの起動時に割り当てられるものもあれば、使用しながら増えていくオプション (クエリキャッシュなど) もある。

  • query_cache_size
  • innodb_additional_mem_pool_size
  • innodb_buffer_pool_size
  • innodb_log_buffer_size
  • key_buffer_size

(参考) グローバルで省略できるパラメータ

サーバには、ファイル記述子の数を制限するオプションとキャッシュされるスレッドの数を制限するオプションもあるが、モダンなシステムでは無視できるほど小さいので、省略できるとのこと。以下がそのリスト。

  • thread_cache_size
  • table_definition_cache
  • table_open_cache
  • innodb_open_files

スレッドバッファ

基本的にスレッドごとに割り当てられるバッファ

  • net_buffer_length
  • thread_stack
  • query_prealloc_size
  • binlog_cache_size
  • binlog_stmt_cache_size

特定処理に割り当てられるバッファ

これらのバッファは、サーバが特定の処理を実行しなければいけない時に、必要に応じて割り当てられる。
割り当てられるメモリ量を正確に計算するのは難しく、クエリを分析して、どれが大量のリソースを要求しているかを調べて、以下のような計算を行う。

(バッファサイズ) * (特定のクエリに割り当てられたバッファ数) * (並列して実行されるクエリの数)

これを全ての変数で実行し、結果の合計を計算する。

これらのオプションは、ほとんどのクエリで小さな量に抑えておくのが良い。もし、特定のクエリでより多くのメモリが必要な場合は、その変数の値をセッションごとに増やすのが良い。(ものによってはグローバルに設定しても意味がないので、クエリを実行する直前に設定する。)

スレッドごとに1回割り当てられるオプション
  • read_rnd_buffer_size
  • sort_buffer_size
  • myisam_mmap_size
  • myisam_sort_buffer_size
  • bulk_insert_buffer_size
  • preload_buffer_size
スレッドごとに複数回割り当てられるオプション
  • join_buffer_size
  • read_buffer_size
  • tmp_table_size

MySQL が消費するメモリの最大量を計算する公式 (MB表記)

Set @join_tables = 1スレッドあたりでJOINされるテーブル数の見積;
Set @scan_tables = 1スレッドあたりのテーブルスキャン数の見積;
Set @tmp_tables = 1スレッドあたりで作成される一時テーブル数の見積;

SELECT(@@query_cache_size + @@innodb_additional_mem_pool_size + 
@@innodb_buffer_pool_size + @@innodb_log_buffer_size + @@key_buffer_size + 
@@max_connections * (@@global.net_buffer_length + @@thread_stack + 
@@global.query_prealloc_size + @@global.read_rnd_buffer_size + 
@@global.sort_buffer_size + @@myisam_mmap_size + 
@@global.myisam_sort_buffer_size + @@global.bulk_insert_buffer_size + 
@@global.preload_buffer_size + @@binlog_cache_size + 
@@binlog_stmt_cache_size + @@global.join_buffer_size * IFNULL(@join_tables, 1) + 
@@global.read_buffer_size * IFNULL(@scan_tables, 1)+
@@global.tmp_table_size * IFNULL(@tmp_tables, 1)))/ (1024*1024);

(参考) MySQLの各オプションの設定値を確認する例

mysql> SHOW GLOBAL VARIABLES LIKE '%buffer%';

(追記) 集まったご意見

yokuさんとか、nippondanjiさんとか、僕からするとMySQL神クラスの方が、フィードバックをくれてありがたい。集合知。

参考にさせていただいた書籍・リンク

MySQLトラブルシューティング

MySQLトラブルシューティング

Amazon
実践ハイパフォーマンスMySQL 第3版

実践ハイパフォーマンスMySQL 第3版

Amazon


rx7 読者になる

関連記事
コメントを書く