先日mysqldの調整をしました。
まぁ調整と言っても、システムのメモリサイズに応じて用意されている設定ファイルのテンプレートを変更しただけなのですが、それでもまだ遅い感じなので、個々のパラメータも若干弄ってみる事にしました。

 

最初に当方環境を書いておきます。

• MySQL : MySQL 4.0.25
• Apache: Apache 1.3.33

かなり古いですね（汗）
MySQLに関しては、xoopsの関係で4.1にあげずに使っています。またApacheに関しては、ほんとはApache2にしたいのですが、MacOS Xで標準インストールされているApacheと共存させるのにけっこうめんどくさいので、そのまま使っています。

 

まずmysqldです。
教科書的なことを言えば、調整に際し、以下のログ

/usr/local/mysql/data/slow.log

を見て、どのテーブルに対する、どのSQLコマンドが遅いのか？を確認してから適切なパラメータを調整する必要がある訳ですが、今回はそこまではせずに、直接webページを表示してみて、表示速度に変化があるかどうかで簡単に確認する事にしました。

 

まずmysqldの調整にあたって下記のサイトを参考に致しました。

 

　　blogのチューニング

 

上記サイトでは、my.cnfの下記のパラメータの調整に付いて説明してあります。

query_cache_limit=1M
query_cache_min_res_unit=4k
query_cache_size=32M
query_cache_type=1

このうち、当方のmysqldではquery_cache_min_res_unitのパラメータが存在しないふるいバージョンの様でエラーとなってしまいましたので、これは指定しませんでした。その他を下記のように設定しました。

query_cache_limit=4M　←1Mから変更
query_cache_size=64M　←32Mから変更
query_cache_type=1

さて、これでmysqldを再起動します。

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

うん、また少し早くなったようです。いったん描画を始めると早いのですが、一番最初に画面に書き始める所でまだもたつきます。xoopsの画面は、すべてMySQLに格納されたデータを毎回SQL文で検索して読み出してからhtmlを生成するので、MySQLへのSQL文実行に関わる部分を調整してみるのが効果の期待できるところなのですが、それ以外にもアフィリエイト関係でその他のサイトのテキストやバナーイメージを引っ張ってきているので，サイトのサーバの調整だけでは力の及ばない所があるというのが実際の所です。

 

その他に何かmysqlに対してできることはないか、と調べていると、mysqldを起動するときのパラメータとして

–skip-name-resolve

を指定するのが効果ありそうです。–skip-name-resolveは、mysqlがアクセスしてきたクライアントのIPアドレスからホスト名を引く処理をスキップするオプションで、当方立ち上げているxoopsのサイトでは、mysqldにアクセスしてくるクライアントは、基本的に同じマシン上で動いているwebサーバであるApacheとxoopsを構成しているphpのみです。従っていちいちmysqldへアクセスがあるたびに名前解決をする処理は必要ありません。従って、このオプションを

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

スクリプトの、起動時のパラメータ指定の所に追記しました。（赤字の所が追記部分）

case “$mode” in
’start’)

# Start daemon

if test -x $bindir/mysqld_safe then

# Give extra arguments to mysqld with the my.cnf file. This script may
# be overwritten at next upgrade.

$bindir/mysqld_safe –datadir=$datadir –pid-file=$pid_file –skip-name-resolve >/dev/null 2>&1 &

# Make lock for RedHat / SuSE

if test -w /var/lock/subsys then
touch /var/lock/subsys/mysql fi else echo “Can’t execute $bindir/mysqld_safe from dir $basedir” fi ;;

さて取り敢えずの所、MySQLでできる調整はこの辺までです。

 

さて、もう一つ関係しているのが、webサーバ"Apache"です。
Apacheに関しては、下記の@ITのサイトを参考にしました。

 

　　第15回　Apacheパフォーマンス・チューニングのポイント
　　最終回　Apacheパフォーマンス・チューニングの実践

 

Apacheの設定ファイルは下記にあります。

/etc/httpd/httpd.conf

上記のサイトの説明を参考に下記を変更しました。これらは主として、Apacheの子プロセスに関する設定です。

MaxClients 300　←150から変更
MinSpareServers 5　←1から変更
MaxSpareServers 10　←5から変更
MacRequestsPerChild 1000　←100000から変更

MaxClientsは同時アクセスできるクライアント数の上限です。すなわち、最大で300の子プロセスhttpdが起動する事になります。そしてこれらが個々にクライアントとセッションを張るので、イコールコネクションの最大数となります。

これは実際に仕事をするプロセスですが、これ以外に子プロセスが起動するのに生ずるオーバーヘッドを抑制するため、余分にプロセスを起動して待機しています。これらの数を指定するのがMinSpareServersとMaxSpareServersです。MinSpareServersは最小待機プロセス数で、MaxSpareServersは最大待機プロセス数です。通常は待機プロセスとしてMinSpareServers分のプロセスが待機していますが、この待機プロセスは稼働状況に応じて最大で10まで増えます。結果として、システムで起動するhttpdの数は

MaxClients + MaxSpareServers = 300 + 10 = 310

となります。これらの値はCPUの負荷とかメモリの利用状況をみながら調整する必要があります。

あと、MaxRequestsPerChildは、起動している子プロセスの生存期間です。あまり同じプロセスが起動しっぱなしになっていると、メモリリークなどが発生したりしてシステムに悪い影響を及ぼす可能性もあります。したがって、或る程度クライアントの処理をこなしたらプロセスに終了してもらい、新たに別の子プロセスを起動するようにするためのものです。当初100000という、かなりのセッションをこなせるようになっていましたが、これを1000回処理したら終わるように変更しています。

変更が終了したらApacheを再起動します。

% apachectl restart

さて、ここまで変更が終わったらサイトにアクセスしてみます。

 

うんうん、劇的といえるほど早くはなってませんが、それでもだいぶ改善されたように思います。まだ若干最初の表示開始までの時間がかかっているのが気になりますが、また改めて調整をして行きたいと思います。■

 

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

 

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

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

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

 

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

 

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

 

それで今回は良いタイミングだと思い、改めて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^)/