お名前.com VPS にMySQLをインストールしWordPress用にチューニング

2013年6月7日

MySQL

お名前.com VPS メモリ2G プランに Nginx で WordPress を構築するメモ、第６回目。前回はApacheをインストールしてSSL環境も整えるところまでやりました。

WordPress
お名前.com VPS にApacheをインストールしてSSL環境も整える

お名前.com VPS メモリ2G プランに Nginx で WordPress を構築するメモ、第５回目。前回は EP ...

続きを見る

今回は、お名前.com の VPS に MySQL をインストールし、WordPress 用にチューニングします。MySQL 各種ログを rotate する方法にも触れます。

目次

  1. MySQL をインストール
  2. MySQL 起動
  3. MySQL のセキュリティを高める
  4. WordPress 用にチューニング
  5. ログファイルの出力先を作成する
  6. innodb_log_file_size の値を変更してエラーが出たら
  7. logrotate を設定する
  8. MySQL の設定状況を確認する
  9. クエリキャッシュのヒット率を確認する

MySQL をインストール

CentOS 標準リポジトリの MySQL は 5.1 と少々古いので、remi リポジトリから 5.5 をインストールします。remi リポジトリのセットアップが済んでいない方は お名前.com VPS で EPEL RPMforge remi リポジトリを利用する をどうぞ。

# yum install mysql-server --enablerepo=remi

記事の執筆時点ではバージョン 5.5.31 がインストールされました。

MySQL 起動

MySQL を起動します。

# /etc/init.d/mysql start

サーバを起動時に MySQL が自動的に起動するようにします。

# chkconfig mysqld on
# chkconfig --list mysqld
mysqld         	0:off	1:off	2:on	3:on	4:on	5:on	6:off

MySQL のセキュリティを高める

mysql_secure_installation コマンドを実行し、セキュリティを高めます。

# mysql_secure_installation

対話形式で進みます。選択肢はすべてデフォルトの Yes でいいでしょう。以下が設定されます。

  • root ユーザのパスワードを設定
  • anonymous ユーザーの削除
  • root ユーザーのログインを localhost に制限
  • test データベースの削除
  • 権限変更の反映
Change the root password? [Y/n] y
Remove anonymous users? [Y/n] y
Disallow root login remotely? [Y/n] y
Remove test database and access to it? [Y/n] y
Reload privilege tables now? [Y/n] y

WordPress 用にチューニング

my.cnf を設定し、MySQL5.5 を WordPress 用にチューニングします。ポイントは以下の通り。

  • お名前.com VPS のメモリは 2GB。MySQL が利用するメモリは 平常時 200MB、Max で 1GB くらいに抑えたい
  • 同時接続数上限を 256 に
  • クエリキャッシュを有効に。よく実行される select 文はすべてクエリキャッシュに乗るのが理想
  • WordPress は MyISAM なので key_buffer_size を多めに
  • WordPress の裏で InnoDB メインのデータベースも動かすので innodb_buffer_pool_size もそれなりに割り当て

以下の設定で、MySQL の仮想メモリのサイズが 1.1GB 、実メモリのサイズが 100MB 〜 150MB くらいで稼働しています。Web サーバは Nginx でリバースプロキシのキャッシュを有効にしキャッシュ生存期間を長めにしているので、MySQL へのアクセスはそれほど多くない状態になっています。また DB のクエリをファイルにキャッシュする系のプラグインは使用していません。

[mysql]
default-character-set = utf8
show-warnings
#safe-updates
prompt = '[\d] mysql> '
[mysqld]
# basic
datadir = /var/lib/mysql
port = 3306
socket = /var/lib/mysql/mysql.sock
user = mysql
symbolic-links = 0
character-set-server = utf8
default-storage-engine = InnoDB
# max connections
max_connections = 256
# table_open_cache = max_connections * tables used in one transaction + 5
table_open_cache = 1024
# all tables + max_connections + 5
table_definition_cache = 400
# table_open_cache * 1.4
open_files_limit = 1450
# global buffer
key_buffer_size = 128M
tmp_table_size = 32M
max_heap_table_size = 32M
query_cache_size = 128M
query_cache_limit = 2M
query_cache_min_res_unit = 4k
query_cache_type = 1
innodb_buffer_pool_size = 128M
innodb_additional_mem_pool_size = 16M
# thread buffer
read_buffer_size = 256K
read_rnd_buffer_size = 512K
join_buffer_size = 256K
sort_buffer_size = 512K
# InnoDB
innodb_log_buffer_size = 16M
innodb_file_per_table
innodb_autoextend_increment = 64
innodb_log_files_in_group = 2
innodb_log_file_size = 32M
innodb_flush_log_at_trx_commit = 1
#innodb_flush_method = O_DIRECT
innodb_thread_concurrency = 3
innodb_commit_concurrency = 3
# log
general_log = 0
general_log_file = /var/log/mysql/general.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_queries_not_using_indexes
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
[mysqldump]
default-character-set = utf8
quick
set-charset
single-transaction
[mysqld_safe]
log-error = /var/log/mysql/error.log
pid-file = /var/run/mysqld/mysqld.pid

ログファイルの出力先を作成する

上記 my.cnf で slow_query_log や log-error を取るように設定したので、mysql ユーザが /var/log/mysql にログファイルを書き込めるようにしておきます。

$ mkdir /var/log/mysql
# chown -R mysql:mysql /var/log/mysql
# chmod -R 640 /var/log/mysql

ログファイルの出力先を作成したら MySQL を再起動します。

# /etc/init.d/mysql restart

innodb_log_file_size の値を変更してエラーが出たら

既に MySQL を稼働させている場合、innodb_log_file_size の値を変えると MySQL 起動時にエラーが出るかもしれません。そのときは一度 MySQL を止めて、InnoDB のログファイルを削除してから起動します。

# /etc/init.d/mysql stop
# rm /var/lib/mysql/ib_logfile[01]
# /etc/init.d/mysql start

logrotate を設定する

my.cnf で各種ログを出力するように設定しましたが、このままだとログが永遠に肥大化するので１日１回 rotate するように設定します。

/etc/logrotate.d/mysqld にひな形がありますので、これをベースにします。デフォルトではすべてコメントアウトされているはずです。中身を確認すると次のように書かれています。

/etc/logrotate.d/mysqld

...
# If the root user has a password you have to create a
# /root/.my.cnf configuration file with the following
# content:
# ATTENTION: This /root/.my.cnf should be readable ONLY
# for root !
...

コメントに書かれているように MySQL のログを rotate するには root ユーザで mysqladmin コマンドをパスワードなしで実行できるようにする必要があります。root ユーザのホームディレクトリに .my.cnf を作成し、mysqladmin コマンドのパスワードを記述します。

/root/.my.cnf

[mysqladmin]
user= root
password = MySQL root ユーザのパスワード

.my.cnf の権限を変更し、root のみ読み書き可能にします。

# chmod 600 /root/.my.cnf

mysqladmin コマンドの動作確認をします。パスワードを聞かれず、ping の結果を取得できれば OK です。

# mysqladmin ping
mysqld is alive

/etc/logrotate.d/mysqld を編集します。

/var/log/mysql/*.log {
        create 640 mysql mysql
        notifempty
    daily
        rotate 4
        missingok
        nocompress
    postrotate
    # just if mysqld is really running
    if test -x /usr/bin/mysqladmin  \
       /usr/bin/mysqladmin ping >/dev/null
    then
       /usr/bin/mysqladmin flush-logs
    fi
    endscript
}

logrotate コマンドをデバッグモードで実行して動作確認します。

# logrotate -dv /etc/logrotate.d/mysqld

Handling 1 logs
rotating pattern: /var/log/mysql/*.log  after 1 days (4 rotations)
empty log files are not rotated, old logs are removed
considering log /var/log/mysql/error.log
  log does not need rotating
considering log /var/log/mysql/slow.log
  log does not need rotating
not running postrotate script, since no logs were rotated
not running postrotate script, since no logs were rotated

特にエラーがでていなければ OK です。メッセージによると 1日後 にローテートされるようです。翌日、ログファイルが rotate されているか確認しましょう。

MySQL の設定状況を確認する

my.cnf で設定した MySQL の設定値を確認する場合は MySQL にログインして show variables を実行します。

mysql> show global variables like '%size%';
+---------------------------------------------------+----------------------+
| Variable_name                                     | Value                |
+---------------------------------------------------+----------------------+
| binlog_cache_size                                 | 32768                |
| binlog_stmt_cache_size                            | 32768                |
| bulk_insert_buffer_size                           | 8388608              |
| delayed_queue_size                                | 1000                 |
| innodb_additional_mem_pool_size                   | 16777216             |
| innodb_buffer_pool_size                           | 134217728            |
...

クエリキャッシュのヒット率を確認する

クエリキャッシュのヒット率は show status の結果から調べることができます。

mysql> show global status like 'QCache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 399       |
| Qcache_free_memory      | 124736472 |
| Qcache_hits             | 414926    |
| Qcache_inserts          | 59288     |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 14761     |
| Qcache_queries_in_cache | 1475      |
| Qcache_total_blocks     | 3461      |
+-------------------------+-----------+

キャッシュヒット率は以下の計算で算出できます。

Qcache_hits / (Qcache_hits + Qcache_inserts + Qcache_not_cached) * 100
414926 / (414926 + 59288 + 14761) * 100 = 84%

クエリキャッシュのヒット率は 84% でした。WordPress すばらしい。Qcache_free_memory の値を確認して、環境に応じて最適な値に調節してみてください。

終わりに

お名前.com VPS に MySQL をインストールし、WordPress 用にチューニングしたときのメモでした。チューニングをしたら、しばらくは毎日ログを見ましょう。my.cnf の設定が不適切で、MySQL がエラーを吐いていることがあります。次回は PHPをインストールし、APC、memcached などを設定します。

WordPress
お名前.com VPS にPHPをインストールし、APC、memcachedなど設定する

お名前.com VPS メモリ2G プランに Nginx で WordPress を構築するメモ、第７回目。前回はMyS ...

続きを見る

-技術ブログ
-,