MySQLの利用状況確認

まず、MySQLの利用状況を確認し、設定変更の目安とします。

MySQLコマンド

# MySQLサーバーへログイン
mysql -u ユーザー名 -p データベース名
Enter password:パスワードを入力

# MySQLの利用状況を確認
show status;

# ストレートエンジンを確認
show engines;

# パラメータ設定一覧を確認
show variables;

# MySQLサーバーからログアウト
exit

補足

●MySQLの利用状況を確認
例えば…
・Max_used_connections･･･同時接続数の最大値
・Threads_connected･･･現在接続しているMySQLクライアント数
・Threads_created･･･接続を処理するために生成されたスレッド数(接続総数)
・Threads_running･･･スリープ状態になっていないスレッド数(処理を実行中)
などが表示されます。

●ストレートエンジンを確認
MySQL Ver.5.5系以降は、デフォルトのストレートエンジンがInnoDBです。
MySQL Ver.5.5系未満は、デフォルトのストレートエンジンがMyISAMです。

InnoDB(イノディービィー)
・トランザクション機能をサポート
・行レベルでロックをかける
・クラッシュ時のリカバリに対応
・フルテキスト検索に非対応
・更新系クエリが多い場合向き

MyISAM(マイアイサム)
・トランザクション機能をサポートしていない
・テーブルレベルでロックをかける
・シンプルで高速に動作
・フルテキスト検索に対応
・参照系クエリが多い場合向き

●パラメータ設定一覧を確認
パラメータのデフォルト値や変更したパラメータの値を確認できます。

文字コード設定

my.cnf

# viコマンドでmy.cnfを開く
vi /etc/my.cnf 

[mysqld]
# default charset
character-set-server = utf8
skip-character-set-client-handshake

[mysql]
# default charset 
default-character-set = utf8

補足

●viコマンドでmy.cnfを開く
MySQLの設定を変更するため、my.cnfを開きます。

●character-set-server = utf8　
MySQL 5.5系以降のMySQLサーバーデフォルト文字コード設定。
※以前のバーションでは”default-character-set = utf8″と設定するので注意。

●skip-character-set-client-handshake
クライアントが指定した文字コードを無視し、サーバーの文字コードを設定するオプションです。これはMySQLが内部変換しないための記述で、11行目のクライアント側のデフォルト文字コードも設定してやることで、サーバーとクライアントの文字コードを同じにします。

“skip-character-set-client-handshake”に関しては、セキュリティ上の問題も指摘されていますが、こちらを記述してやらないと他のところでいろいろと不具合が出てしまいます。参考書籍等でも”skip-character-set-client-handshake”の追記が掲載されている場合が多いため、現状ではこのオプションを付けるようにしています。

パラメータ設定

MySQLのパラメータには、グローバルパラメータとスレッドごとのパラメータがあります。

グローバルパラメータ
MySQLサーバ全体で共有する値。
メモリ量や使用状況により、そこそこの数値を割り当てても構いません。

スレッドごとのパラメータ
スレッドごとに割り当てられる値。
グローバルパラメータと異なり、利用状況を適切に測定した上で設定します。
また、スレッドごとのパラメータにそれ程大きな値を割り当てる必要はないと思います。

以下は、グローバルパラメータとスレッドごとのパラメータの設定例です。
※サーバーのメモリを2GBとした場合の設定例。

my.cnf

[mysqld]
# global parameter
max_connections = 150
thread_cache_size = 50
query_cache_size = 32M
;InnoDB
innodb_buffer_pool_size = 1000M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 250M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
;MyISAM
key_buffer = 512M
table_open_cache = 512

# thread parameter
sort_buffer_size = 2M
read_buffer_size = 256K
join_buffer_size = 256K
read_rnd_buffer_size = 1M
max_allowed_packet = 2M
;MyISAM
myisam_sort_buffer_size = 8M

補足

[グローバルパラメータ]
●max_connections
同時接続最大数。
デフォルト値は100。

●thread_cache_size
生成されたコネクション用のスレッドをキャッシュする数。
デフォルト値は0。
max_connectionsの1/3程度が妥当なようです。

●query_cache_size
クエリ結果をキャッシュから読み出すことで、高速な動作が期待できます。
デフォルト値は0(無効)。
クエリキャッシュ設定に関しては、以前の記事でも紹介しています。
⇒ MySQLのクエリキャッシュ設定

●innodb_buffer_pool_size
InnoDB使用時のパラメータ。
データやインデックスをキャッシュするためのメモリ上の領域。
デフォルト値は8M。
メモリの50％～80％を割り当てるのが妥当なようです。

●innodb_additional_mem_pool_size
InnoDB使用時のパラメータ。
内部データ等を保持するための領域。
デフォルト値は1M。
多く割り当てる必要はなく、不足時には、エラーログに警告が表示されるので、その際に変更すればOKです。

●innodb_log_file_size
InnoDB使用時のパラメータ。
更新ログを記録するディスク上のファイル領域。
デフォルト値は5M。

●innodb_log_buffer_size
InnoDB使用時のパラメータ。
更新ログを記録するメモリ上の領域。
デフォルト値は1M。
殆どの場合8M位で問題なし。

●innodb_flush_log_at_trx_commit
InnoDB使用時のパラメータ。
ログファイルをディスクにフラッシュするタイミングを指定するパラメータ。
デフォルト値は1(有効)。
余程の理由がない限り、COMMITされたデータが確実にディスクに書き込まれる(フラッシュされる)設定1にする。
※デフォルト値のまま使用する場合、記述する必要はありません。

●innodb_lock_wait_timeout
InnoDB使用時のパラメータ。
行ロック待ち時間(秒)。
デフォルト値は50。
※デフォルト値のまま使用する場合、記述する必要はありません。

●key_buffer
MyISAM使用時のパラメータ。
インデックスをキャッシュするためのメモリ上の領域。
デフォルト値は8M。

●table_open_cache
MyISAM使用時のパラメータ。
一度開いたテーブルをキャッシュする数。
デフォルト値は64。
※MySQL 5.5系以降の書き方。以前のバージョンではtable_cache。

[スレッドごとのパラメータ]
●sort_buffer_size
ORDER BYやGROUP BYの時に使用されるメモリ上の領域。
デフォルト値は2M。

●read_buffer_size
インデックスを用いないテーブルスキャン時に使用されるメモリ上の領域。
デフォルト値は125k。

●join_buffer_size
インデックスを用いないテーブル結合時に使用されるメモリ上の領域。
デフォルト値は125k。

●read_rnd_buffer_size
ソート後、レコードを読み込む際に使用されるメモリ上の領域。
デフォルト値は256k。

●max_allowed_packet
通信時における１パケットの最大サイズ。
デフォルト値は1M。

●myisam_sort_buffer_size
MyISAM使用時のパラメータ。
REPAIR TABLE・CREATE INDEX・ALTER INDEX時のインデックスのソートに使われるメモリ上の領域。
デフォルト値は8M。

上記はあくまで参考例です。
※サーバー環境や使用状況により変更してください。
基本的なMySQLのチューニングは、まずグローバルパラメータを設定し、スレッドごとのパラメータは、MySQLの利用状況を適切に測定した上で設定するようにします。

ストレージエンジンの変更

MySQL 5.5系では、デフォルトのストレートエンジンはInnoDBです。
テーブルのストレートエンジンにMyISAMしか使っていない、またメモリ消費を抑えたい等、デフォルトのストレートエンジンをInnoDBからMyISAMに変更したい場合は以下のようにします(※例えば、WordPressでしかMySQLを使用していないような場合、一部の決済系プラグイン等を除いて、基本テーブルのストレートエンジンにはMyISAMしか使っていないと思いますし、トランザクション機能も使用することはない思います。また、InnoDBからMyISAMに変更することで、メモリの使用量を節約できます)。

my.cnf

# default storage engine
skip-innodb
default-storage-engine=MyISAM

# 設定を反映させるためMySQLを再起動
/etc/init.d/mysqld restart

# 既存のテーブルのストレートエンジンを変更する
alter table テーブル名 engine=MyISAM;

補足

●skip-innodb
InnoDBを使用しない場合に使います。
以前のバージョンでは”skip-innodb”だけでよかったのですが、MySQL 5.5系以降ではエラーが出力されるので、”default-storage-engine=MyISAM”でデフォルトのストレートエンジンも指定してやります(※MySQL 5.5系未満では、デフォルトのストレートエンジンはMyISAMであるため、”skip-innodb”とするとInnoDBを使用せずデフォルトのMyISAMになりましたが、MySQL 5.5系以降では、デフォルトのストレートエンジンはInnoDBであるため、”default-storage-engine=MyISAM”とデフォルトのストレートエンジンを指定しないとエラーが出力されるということです)。

●設定を反映させるためMySQLを再起動
全ての設定を反映させるため、MySQLを再起動します。

●既存のテーブルのストレートエンジンを変更する
使用しているテーブルに対して上記コマンドを繰り返します(※予め使用するストレートエンジンを指定してテーブルを作成している場合を除きます)。

phpMyAdminで変更することも可能です。
1.該当のDBを選択する
2.変更したいテーブルを選択する
3.[操作]タブをクリック
4.ストレートエンジンからMyISAMを選択
5.[実行]ボタンをクリック