Help us understand the problem. What is going on with this article?

MySQL最低限のメモリ設定

More than 3 years have passed since last update.

環境にもよりますが、MySQLのデフォルトのメモリ関連の設定は控えめな感じになっています(私のCentOS環境では、key_buffer_sizeが8M,innodb_buffer_pool_sizeも8Mとなっています)ので、適切な状態への変更が必要です。

MySQLのメモリについては、いろいろな情報がありますが、MySQLのsupport-filesの中にあるサンプルを見ればいいでしょう(ちょっと、サイズの感覚が古いですが)。多くの場合、my-huge.cnfかmy-innodb-heavy-4G.cnfを参考にすればいいでしょう。

後は、本家Oracleさんの資料も参考になります。

グローバルバッファとスレッドバッファ

MySQLでは、mysqldが共通に使うグローバルバッファと、スレッド(コネクション)毎に割り振られるスレッドバッファがあります。MySQLのメモリ使用量は、

メモリ使用量 = グローバルバッファ + (スレッドバッファ x スレッド数)

の関係で表すことができるとされています。

MyISAMとInnoDB

MySQLの代表的なストレージエンジンはMyISAMとInnoDBですが、利用するエンジンによりメモリ(グローバルバッファ)の使い方は異なります(スレッドバッファは共通)。5.5からは、デフォルトのエンジンがInnoDBになったので、基本、InnoDBのことだけ考えておけばいいのですが、システムDBはMyISAMということなので、MyISAMのことも最低限考えておく必要があります。

InnoDB(グローバル)

InnoDBは、乱暴に言うと、全てのデータをinnodb_buffer_pool_sizeで指定されたメモリ内に保存するので、innnodb_buffer_pool_sizeの値を適切に設定する必要があります。DB専用のサーバであれば搭載メモリの80%程度とも言われています。

  • innodb_buffer_pool_size
  • innodb_log_file_size

log_file_sizeは、innodb_buffer_pool_sizeの25%程度すればよいらしい。が、大きくても64MB程度とも。

innodb_buffer_pool_size = 256M
innodb_log_file_size = 64M

MyISAM(グローバル)

MyISAMでは、一部のデータのみをkey_buffer_sizeで指定されたメモリに保存し、それ以外は、OSのメモリに保存するため、key_buffer_sizeを搭載メモリの25%程度に留めて設定し、OSのメモリにデータ等が保存されるよう(OSのメモリを圧迫しないよう)設定します。

key_buffer_sizeを大きくし過ぎると、OSのメモリを圧迫し、むしろパフォーマンスが低下することがあるようです。

  • key_buffer_size
key_buffer_size = 256M

スレッドバッファ

スレッドバッファは、メモリ割りて自体のオーバーヘッドやスレッド(コネクション)毎に利用されるため、あまり大きくし過ぎないことが重要なようです(128K ~ 1M程度)。

  • read_buffer_size(index無しの全体スキャン時に利用される)
  • sort_buffer_size(ソート処理に利用される)
  • read_rnd_buffer_size(indexを利用したソート処理に利用される)
read_buffer_size = 1M
sort_buffer_size = 1M
read_rnd_buffer_size = 4M

設定情報の確認

MySQL

各種パラメータ値

MySQLの現状がどのような設定なのかを確認するには、以下のコマンドを利用します。

mysql> show variables;

like等で絞り込む事もできます。

そもそもどのエンジンを利用してるか?

デフォルトのエンジンや、DBがどのエンジンを利用しているかの確認。

デフォルトエンジンの確認。

mysql> show engines;

DBの各テーブルがどのエンジンを利用しているか?

show table status from db_name;

エンジンの変更。

alter table table_name engine=InnoDB;

なお、多くの場合、エンジンの変更は問題が無いが、下記の場合には気をつける。

  • INSERT IGNOREを利用している場合
  • auto_incrementの挙動(InnoDBでは、値が詰まる)

OS

そもそもOSのメモリの搭載量、使用量を確認するには、

$ free

が一般的です。Linuxに置いて注意するのは、-/+ buffers/cache: 行のfreeの箇所を見ることです(ここが実際の空きメモリに該当)。

パラメータの設定

MySQL関連の設定は、(私は)my.cnfで行いますが、SET GLOBALコマンドでも設定可能です。

msyql> set global key_buffer_size = 128 * 1024 * 1024;

などとします。

Why do not you register as a user and use Qiita more conveniently?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away