第11回　MySQLの高度な管理とチューニングテクニック

本連載もついに最終回。今回はMySQLサーバの運用・管理に必要な状態監視、チューニング、バックアップ、セキュリティについて解説する。以下のテクニックを駆使すれば、MySQLをさらに安定稼働させられるだろう。

鶴長 鎮一
MySQLユーザ会
2001/7/24

　前回までは、さまざまな言語やアプリケーション、とりわけWebアプリケーションからMySQLを利用することを主眼に説明してきました。皆さんの中には、それらを応用した実用的なプログラムを考えている方もいるかと思います。そこで、今回は運用上必要となるテクニックをいくつかのトピックに分けてご紹介します。「まだまだ実用は先だ」と思われている方も、いつかくる日のためにこんなこともできるということだけでも知っておいてください。

ロギング機能と動作ステータスの取得

　障害発生を防ぐための一番の方策は日々の運用方法にあります。サーバからの警告をいち早く察知して対処することで、障害を未然に防ぐことが可能になります。MySQLは、起動時に下記のようなオプションを指定することでロギング機能を有効にできます。

safe_mysqld --log=$logfile

　$logfileはログを出力するファイルの絶対パス（/usr/local/var/mysqld.logなど）です。$logfileを省略して、

safe_mysqld --log

とすることも可能です。その場合、ログファイルはデータディレクトリに“サーバ名.log”という名前で作成されます。

　MySQLをソースからインストールし、ひな型のスタートアップスクリプトを使って起動しているなら、ロギング機能はすでに組み込まれています。しかし、RPMファイルでインストールした場合やOSのインストール時にインストールパッケージの1つとして組み入れている場合は、ロギング機能が外されていることがあります。/etc/rc.d/init.dディレクトリにあるmysqlスタートアップスクリプトファイルを編集し、startセクションの該当個所に上記のオプションを追加します。設定を有効にするためにMySQLを再起動し、試しに

$ mysqlshow

＊mysqlshowはMySQLのデータベースやそのテーブル構造を表示するコマンド

と入力してMySQLへの問い合わせを行ってみましょう。ログファイルには、

010719  2:11:19      16 Connect    ユーザー名@接続ホスト on                      16 Query      show databases                      16 Quit

と追加されているはずです。これで、どこからだれが接続したか、どんな問い合わせを行ったかを調べることができます。もちろん、不正なアクセスに対する調査も可能です。

010719  2:17:00      17 Connect    Access denied for user: 'ユーザー名@接続ホスト' (Using password: YES)

という行に覚えがない場合は、何らかの不正アクセスが行われた可能性があります。

　ログファイルはサーバの運用ばかりでなく、アプリケーション構築の際にも有益です。アプリケーションで何らかのエラーが発生した場合、その過程がログファイルに記録されているかどうかで、MySQLへの問い合わせがどこで失敗しているかめどを付けることができます。ログファイルに記録されていれば、問い合わせSQL文そのものにエラーがある場合が考えられますが、ログファイルに記録がなければネットワークの問題やMySQLサーバが起動されているのかを疑う必要があります。しかし、多くのエラーは前出の“Access denied”に起因する場合がほとんどのようです。その場合は本連載第3回に立ち返り、正しく参照権を設定してください。

■サーバ稼働状況の調査

　これで、強力なロギング機能で正常なSQL問い合わせが行われているかを確認できるようになりました。しかし、サーバの稼働状況を把握するにはまだ十分とはいえません。MySQLには、サーバの稼働状況をタイムリーに調べる方法が備わっています。一番簡単な調査は、サーバそのものが稼働しているかの確認です。もちろん、

ps -aux | grep mysql

でも調べることは可能ですが、もっと簡単に

# mysqladmin ping mysqld is alive

とする方法もあります。さらに突き詰めて、MySQLの稼働総時間や開かれているテーブルの数を調べる場合は

# mysqladmin status

とします。すると、例えば下記のようなメッセージが返ってきます。

Uptime:  377976 Threads: 1  Questions: 91  Slow queries: 0  Opens: 23  Flush tables: 1  Open tables: 17 Queries per second avg: 0.000

　例として挙げたメッセージには、以下のような情報が含まれています。

	Uptime: 377976	起動からの総稼働秒数
	Threads: 1	MySQLが使用しているスレッドの数。ただし、このほかに表面上表示されない管理用スレッドが3つ動作している
	Questions: 91	起動時からの総問い合わせ数
	Slow queries: 0	ある想定時間よりも時間を要した問い合わせの数（ある想定時間は設定可能）
	Opens: 23	起動してから開かれたテーブルの数
	Flush tables: 1	flush、refresh、reloadの実行回数
	Open tables: 17	現時点で開かれているテーブルの数
	Queries per second avg: 0.000	問い合わせ平均応答秒数

　さらに詳しい稼働状況を知りたい場合は、

# mysqladmin extended-status

を用います。これらの情報は後述の「最適化」の過程で必要になってくるものです。

データファイルのメンテナンスとチューニング

　サーバ稼働状況の把握とは別に、もう1つ必要なのがデータファイルの検査です。MySQLは指定されたデータディレクトリにデータファイルを作成します（コラム「MySQLのデータファイル構造」参照）。不意なサーバ停止やHDトラブルによるデータファイル破損など、予期せぬトラブルは日ごろのメンテナンスを慎重に重ねても完全に防ぐことは不可能です。まず、データファイルが壊れていないかを確認します。

# myisamchk データファイル.MYI

# myisamchk /usr/local/var/ATMARKIT/list.MYI

＊実行例

　myisamchkコマンドを実行して下記のようなエラーや修復を促すメッセージが表示された場合は、早急に対処する必要があります。

Data records:      42   Deleted blocks:       0 myisamchk: warning: 1 clients is using or hasn't closed the table properly - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 - check data record references index: 2 MyISAM-table '/var/lib/mysql/ATMARKIT/list.MYI' is usable but should be fixed

■データファイルの修復と最適化

　myisamchkでエラーや修復を促すメッセージが表示されたら、下記のように対処します。

# myisamchk -r 修復の必要なデータファイル.MYI - recovering (with sort) MyISAM-table '/..XXX../XXX.MYI' Data records: 42 - Fixing index 1 - Fixing index 2

　-rオプションでも解決できない場合は-oオプションを用います。

# myisamchk -o 修復の必要なデータファイル.MYI

　-rあるいは-oオプションのどちらかで、ほとんどのエラーから回復できます。ただし、これらのオプションを指定する場合は、修復中にファイルが更新されないように注意する必要があります。具体的には、MySQLサーバのプロセスであるmysqldを停止させた状態で修復作業を行うようにします。

　また、myisamchkコマンドはデータファイルの修復以外にデータの最適化が可能です。まず、下記のようにファイル自体のコンディションを表示します。

#myisamchk -d データファイル.MYI MyISAM file:         /..XXX../XXX.MYI Record format:       Fixed length Character set:       ujis (12) Data records:                    2  Deleted blocks:              0 Recordlength:                   41 table description: Key Start Len Index Type 1   2     20  unique  char packed stripped 2   22    20  multip. char packed stripped

　Deleted blocksの値が大きい場合は、無駄に占有されているディスク領域を開放する必要があります。これは、修復の際に用いた-rオプションで解決できます。この場合も必ずmysqldプロセスを停止させることを忘れないでください。

■サーバプロセスのチューニング

　普段の運用方法をサーバのプロセスとデータファイルという2つの側面からとらえたように、最適化もそれぞれで行う必要があります。データファイルの最適化はお分かりいただけたと思います。では、サーバのプロセスを最適化するにはどうすればよいでしょうか。

　サーバプロセスは一度起動してしまうと、途中でパラメータを変更して用途に合わせて調整することはできません。起動パラメータの多くは--logオプションと同様、起動時にのみ指定可能です。しかし、そのすべてをsafe_mysqldのオプションとして記述してしまっては煩雑になってしまいます。

　そこでmy.cnfファイルを使用します。ソースからインストールした場合もRPMなどのパッケージを使ってインストールした場合も、/etc/my.cnfとして保存されているはずです。my.cnfの主要なパラメータには下記のようなものがあります。

[mysqld] port         = 3306 #起動ポート番号。セキュリティ面から変更しておいた方がいい場合もある set-variable = key_buffer=256M #メモリに余裕がある場合に指定。検索に使われるインデックスをバッファに保存する際のメモリサイズ set-variable = max_allowed_packet=1M #入力データ保持のための最大バッファサイズ。画像など、大きなデータ挿入でこの制限に引っ掛かる可能性がある set-variable = table_cache=256 #頻繁なアクセスに対し、データのキャッシュでディスクのI/O負荷を減らす場合に使用 set-variable = sort_buffer=1M #値を大きくすることでORDER BYやGROUP BYをクエリーに用いた場合に速度を上げる set-variable = record_buffer=1M #値を大きくすることでインデックスを含まないクエリーの実行速度を上げる set-variable = long_query_time=1 #mysqladmin statusコマンドで表示されるSlow queriesにカウントされる場合のしきい値。単位は秒

　my.cnfファイルを更新した場合、変更内容を有効にするためにはプロセスの再起動が必要です。

# /etc/rc.d/init.d/mysql stop # /etc/rc.d/init.d/mysql start

例：スタートアップスクリプトがある場合

コラム　MySQLのデータファイル構造 　MySQL 3.23.XXより前まではデータファイルの形式に「ISAM」と呼ばれるものが使われていました。そのため、MySQLに用意されているコマンドの中には「ism」で始まるコマンドがいくつかあります。3.23.XX以降、データファイルは「MyISAM」に変更されており、データファイルの互換性や最大サイズが改善されています。少し前ならISAMからMyISAMへの変換方法も紹介する必要がありましたが、ほとんどの方は無意識のうちにMyISAMを使用していると思います。 　MyISAMはデータディレクトリ（/usr/local/varや/va/lib/mysqlなど）に次のようなファイルを作ります。 データベース名/     テーブル名.frm　テーブル構造     テーブル名.MYD　データファイル     テーブル名.MYI　インデックスデータファイル 　データベース中にテーブルを1つ作るたびに、上記の3ファイルが1組になって増えていきます。

1/2

Index
快速MySQLでデータベースアプリ！
	ロギング機能と動作ステータスの取得 　サーバ稼働状況の調査
	データファイルのメンテナンスとチューニング 　データファイルの修復と最適化 　サーバプロセスのチューニング
	いざというときのためのバックアップ 　ダンプを使ったフルバックアップ 　差分バックアップの設定 　レプリケーションバックアップ環境の構築
	セキュリティの向上
	最後に

連載 快速MySQLでデータベースアプリ！

---

Linux Squareフォーラム データベース関連記事

	連載：快速MySQLでデータベースアプリ！（全11回） 軽快な動作で知られるRDBMS、MySQLでDBアプリの構築を行う。MySQLのインストールに始まり、PerlやRubyなどのスクリプトでデータベースを操作する方法までを完全解説
	連載：今から始める MySQL入門（連載中） 定番のLAMP（Linux＋Apache＋MySQL＋PHP）構成でWebアプリケーション開発に挑戦！ サンプルアプリの構築を進めながら、基礎知識や操作方法について詳しく解説する
	連載：Oracleマイスター養成講座（全6回） 本連載では、Oracleの管理・チューニング方法を紹介していく。これからOracleを始める人、そしてOracleをより深く理解したい人のための、一歩踏み込んだ実用講座
	連載：DB2マイスター養成講座（全7回） 本連載では、DB2 UDBの実践的な運用・管理方法を紹介していく。DB2を利用するうえで必要な知識を、実運用を前提にDB2のプロが解説
	特集：エンタープライズ市場に向かうMySQL 5.0［前編］ MySQL 5.0の新機能をアルファ版でチェック 1月に公開された5.0アルファ版は大幅に拡張されており、エンタープライズ市場への進出を予感させる
	特集：Linuxで動くリレーショナルデータベース・カタログ データベースサーバのOSとしてLinuxを採用するケースが増えている。Linuxで動作する7つの主なリレーショナルデータベースを紹介する。製品導入の際の参考にしてほしい
	more

MONOist組み込み開発フォーラムの中から、Linux関連記事を紹介します

＠ITメールマガジン　新着情報やスタッフのコラムがメールで届きます（無料）