サイトを再開して１週間ちょっとたち、以前の日記等も復活させつつ、だいぶコンテンツも増えてきました。ぼちぼち知り合いも見てくれているようですが、

「日記をクリックしたら、表示されるまでに時間がすごくかかる！」

というコメントをいただきました。

 

実はこのサイトを立ち上げる前、同じサーバで動かしている別のサイトを構築しているときから気にはなっていたのですが、訪問者もそれほど多くないサイトだったのであまり問題になってなかったのでそのままにしていました。

 

いや、正直をいえば、多少のパラメータチューニングを試みたのですが、あまり応答性が改善されなかったので、それ以上追求せずそのままにしていました。

 

それで今回は良いタイミングだと思い、改めてMySQLの運用環境の整備をはじめとしてチューニングに再挑戦することにしました。

 

まずはmysqlをいじるための管理のツールの準備です。

 

mysqlはコマンドラインからmysqlコマンドを使って操作をするのが基本ですが、コマンドやパラメータをいろいろ覚えないと行けない事と、数多くのパラメータ類や動作状況を一目で把握するにはちょっと不便です。MySQLはシェアも大きいので、MySQLを管理する為の様々なツール類がフリーや商用で出ています。

 

今回は以下の２つを使います。

phpMyAdmin
phpMyAdminはweb経由でMySQLの殆ど全ての操作をGUIベースで行える、非常にすぐれたプログラムで、これを入れておけば、ローカルでの作業のみならず、外出時に遠隔でこのwebページにアクセスすることでサーバの運用状況とか、パラメータの調整とか、あるいは各テーブルの操作ができます。
MySQL Administrator
MySQL AdministratorはWindows, MacOS X(10.4), Linuxのバイナリ、およびソースで提供されているスタンドアローンの管理アプリケーションで、動作状況をグラフで見たりすることもでき、MySQLのパフォーマンスを視覚的に把握するのに便利です。もちろん各テーブルの操作も可能です。

phpMyAdminやMySQL Administratorのインストール等に関しては省略します。

 

さて、動作状況を見る為にMySQL Administratorを動かしてサーバに接続します。ところがMySQLの管理ユーザ／パスワードで接続をしようとしても、該当のホストは接続許可されていません、というエラーが出ます。ちなみにこの作業は、MySQLやブログサイトを動かしているサーバとは別のマシンから作業をしようとしています。確か以前MySQL Administratorを使っていた記憶があるんですが、なぜかつながらない！

 

基本に立ち戻って、このエラーメッセージの内容を信じてmysqldの管理テーブルに、いま作業しているホストからの接続を受け付けるよう登録されているかどうか確認します。

 

すると、あれ？localhostしか登録されていない！

 

どうやら以前動作させた記憶というのは、MySQLが動いているマシンのローカルからだったようで、あらためてmysqlデータベースのuserテーブルに、ホスト、ユーザ名、パスワードを登録します。ちなみにホストの指定はローカルアドレスからは全て接続を許可するようにします。仮にMySQLのサーバのIPアドレスを192.168.1.1、作業中のマシンを192.168.1.128、サブネットマスクを255.255.255.0とします。記述の方法には以下の２つが可能です。

192.168.1.128
これだと、192.168.1.128というホストからのみ接続を受け付けます。
192.168.1.0/255.255.255.0
192.168.1.0のサブネットに属するホストからは全て接続を受け付けます。
192.168.1.%
上記と同じく、192.168.1.0のサブネットから全て接続を受け付けます。

今回は簡単なほうで、192.168.1.%とすることにしました。

 

さて、あらためてMySQL Administratorから接続しようとすると。。。
ありゃ？まだつながらない。

 

ごく一般的に考えて、テーブルの設定を変更してからすぐにサーバ側に反映されないけーすがあるので、おそらくmysqldにテーブルの更新を何らかの方法で通知する必要がありそうです。mysqldを再起動するのが確実そうですが、phpMyAdminからログインし、トップのページを見てみると、

MySQLのリロード

という項目があります。まずはこれを試してみます。するとmysqlデータベースのテーブルで更新された項目がmysqlに読み込まれた旨メッセージが表示されました。さて、あらためてMySQL Administratorからログインすると、お！うまくいきました。

 

さて、本来の作業としてMySQLのチューニングに入ります。

 

アプリケーション上部の"Health"ボタンをクリックして、"Memory Health"タブを選択します。すると、Query Cache Hitrateと、Keybufferの利用状況がグラフで時々刻々と表示されます。このグラフを表示させておいて、該当のブログサイトの画面からいろいろなリンクを押して、画面切り替えをすると、その度に発生するSQLコマンドの処理状況に応じてグラフが上下します。ためしにある日記へのリンクをクリックするとKey bufferのグラフが跳ね上がりました。現在のkey bufferの設定値は16kByteになっています。ん？16kByte?　なんか値が異常に小さい気がします。それでターミナルを開いてサーバにログインし、mysqldの設定ファイルを確認することにしました。

 

mysqlの設定は大きく分けて以下の４つのところで行います。mysqldを起動した際、この順番で設定が読み込まれ、同じ設定項目があった場合、あとから読み込まれた方のパラメータが有効になります。

/etc/my.cnf　　　グローバルオプション（このマシン全部で共通）
<data_dir>/my.cnf　　サーバ固有オプション
defaults-extra-file　　mysqldへの–defaults-extra-file=<path>で指定されたファイル
~/.my.cnf　　ユーザ固有オプション

ここで<data_dir>はひとつのMySQLインスタンスがインストールされている場所です。一般的にひとつのマシンで複数のMySQLを動かすケースがあり、その場合は全部のMySQLについては/etc/my.cnfが、そして個々のMySQLインスタンスについてはそれぞれがインストールされ、データが格納される場所にある<data_dir>/my.cnfが適用されます。
ちなみに僕のマシンはMacOS X 10.4で、MySQLの標準インストールパスは

 

　　/usr/local/mysql

 

なので、<data_dir>=/usr/local/mysql/dataです。

 

以前インストールして動作の調整をした際に/etc/my.cnfをいじった覚えがあり、たしかkey bufferのサイズは16kbから1Mに拡張していたはず。。。
サーバ固有オプションの方を眺めてみると、ありゃ、こちらは16kbのままです。他のパラメータも同様にかなり小さい値が設定されています。

 

MySQLをインストールすると、サーバのスペックに応じて標準的なパラメータを設定したファイルがいくつか用意されており、MySQLのインストールディレクトリの./support-files以下に入ってます。

my-small.cnf　　　小規模システム用。メモリサイズが64M以下用で応答遅い
my-medium.cnf　　MySQL主体で動かすメモリ32M〜64M程度のマシンもしくは128M程度で他のプログラムも動く
my-large.cnf　　MySQL主体で動かすメモリ512M程度のマシン
my-huge.cnf　　MySQL主体で動かすメモリ1G〜2G程度の大規模システム
my-innodb-heavy-4G.cnf　MySQL主体で動かすメモリ4G以上の大規模システムで、データベースにinnoDBを使っており、接続数がそれほど多くない複雑なQueryを処理するシステム（つまりトランザクションシステムではない、ということでしょうか）

僕も今回初めてきちんとこの辺に目を通している所で、/etc/my.cnfと<data_dir>/my.cnfのヘッダのコメントを見て、上記のどの設定ファイルが標準で使われているかを確認しました。現状のmysqlが使っているのは<data_dir>/my.cnfで、こいつが最終的にmysqldのパラメータを決定しています。<data_dir>/my.cnfを見てみると、my-small.cnfを使っているようです（汗）
現状のマシンはMacOS X 10.4.9でPowerPC 500MHz、メモリを900MB弱搭載しています。このスペックにしてはmy-small.cnfは小規模すぎ、またインターネットから（将来的に）多くのトラフィックが予想されるMySQLの設定としては小さすぎる事が分かりました。メモリサイズから考えると、my-large.cnfかmy-huge.cnfか悩む所ではありますが、MySQL専用マシンではないのと、他にもサーバアプリケーションが常駐していることを考え、少し小さめのmy-large.cnfを選ぶ事にしました。my-large.cnfを/etcと<data_dir>以下にコピーし、名前をmy.cnfへ変更します。

 

さて改めて、phpMyAdminでMySQLのリロードをしてからサーバパラメータの設定を確認してmy-large.cnfのものに変更されているかどうかを見ます。先ほどkey bufferが16kBだったので、これが256MBになっていればOKなわけですが、16KBのままです。
どうやらmysqldを起動したままmyslqテーブルの値を読み込むリロードではだめのようで、mysqldの再起動が必要な様子です。

 

mysqldの起動・停止については、MySQL Administratorからもできるようですが、勉強のためコマンドラインから実行します。
mysqldを正しく停止／起動させる方法は下記にあります。

 

　2.4.3. MySQL を自動的に起動および停止する

 

手動で停止，起動させるときはmysql.serverスクリプトを使うと良いようです。mysql.serverはインストールディレクトリのsupport-filesにあります。僕のマシンでは

 

　/usr/local/mysql/support-files/mysql.server

 

です。さて、あらためてターミナルから

% /usr/local/mysql/support-files/mysql.server stop
% /usr/local/mysql/support-files/mysql.server start

を実行した後に、あらためてphpMyAdminでkey buffer他の値を確認すると、今度はきちんと変更されていました。

 

さて、ブログの方へもどってブラウザからブログサイトの日記等をクリックして画面更新のスピードを確認してみます。うん、少し早くなったようです。

 

サイトの表示が遅い場合、今回は気になっていたMySQLのパラメータ調整を試みましたが，実際には様々なプロセスが絡んでいるため、他にも速度低下の要因が考えられます。Webサーバであるapache、ブログサイトで読み込んでいるECサイトの応答、ネットワークインタフェースカード、ルータ、ハブ、そしてサーバがつながっている回線そのものの輻輳（混雑）などなど。。。

 

ま、これでとりあえず様子を見てみようと思います。場合によってはメモリを増やしてmy-huge.cnf設定ファイルに切り替える事も検討します。

 

ちなみに僕の環境はMacOS X 10.4(.9)ですが、下記のような本を参考にしています。（あとはインターネットですね）

 

 

↓↓↓　「いいね！」と感じたら、クリックしてBlogRanking投票お願いします(^o^)/