MySQLのスロークエリを解消するには EXPLAIN でクエリが利用するインデックスを見たり、using filesort が出ていないかをチェックしたりして、テーブルにインデックスを追加したりSQLを見直したりするのが通例です。

しかしそれでも解消しないスロークエリがあったので、メモリ使用量の設定を変更することにしました。

「チューニング – データベース ( MySQL ) – 自宅サーバーの構築 – 自宅サーバーでやってみよう！！」によると、

/usr/lib/mysql

の下に、サーバーが搭載するメモリ量に応じたmy.cnfのサンプルファイルがある模様。

利用しているサーバー(VPS)は1GBまでメモリを使えるみたいなので、512MB以内のメモリ用のmy-large.cnfを参考にすることに。MySQLだけでメモリを食いつぶすわけにはいかないので一段階下の設定を選択しました。

my-large.cnfのものそのままですが、 [mysqld] の項に追加した設定は以下。

key_buffer = 256M
max_allowed_packet = 1M
table_cache = 256
read_buffer_size = 1M
thread_cache_size = 8
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
query_cache_size= 16M

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

ここで気をつけないといけないのは、innodb_log_file_sizeを変更したら ib_logfile0 と ib_logfile1 を削除して mysqld を再起動しないといけないという点。

これをやらないと、

100730 05:42:44 [ERROR] /usr/libexec/mysqld: Incorrect information in file: ...

のようなエラーが出てデータが取得できなくなってしまいます。一時的に MySQL を停止して作業することになるので、影響が少ない時間帯などにやる必要がありそうです。

設定項目の詳細は「DSAS開発者の部屋:5分でできる、MySQLのメモリ関係のチューニング！」が参考になります。

ひとまずこれでスロークエリが出なくなったので一安心。

• 
• ツイートで共有

著者について

F.Ko-Ji

Webエンジニアやってます。最近は ドットインストール の開発がお仕事です。その傍ら、個人で Meity や 電車遅延なう、梅酒.in、 #グラドル自画撮り部 の部室といったネットサービスを開発・運営してます。梅酒と草野球とリアル脱出ゲームが好きです。

» 詳しいプロフィールや運営サービスの一覧など