先日設定したサーバでMuninをインストールして監視していたのですが、どうもMySQL関連のプラグイン(mysql_bytes, mysql_queries, mysql_threadsなど)でグラフが描画されず、どうしたものかと思ってぐぐってみたところ、このエントリを見つけました。まさにビンゴで /etc/munin/plugin-conf.d/munin-node に以下の行を足してmunin-nodeをrestartしたところ、うまくグラフが描画されるようになりました。
env.mysqladmin /usr/local/bin/mysqladmin
Muninの問題なのか何なのか、自分のケースでは mysql 関連のコマンドを /usr/local/bin/ 配下にインストールしてたので、ここにPATHが通っていなかったのが問題のようです。
$ sudo -umunin munin-run mysql_bytes
と実行してもうまく数値が出てきたのでどうしたものかと悩んでいたのですが、解決してすっきりしました。
以前MySQL 5.1の最新版のdebパッケージを作成する方法というエントリを書いたのですが、Ubuntuの最新の開発版パッケージではなく、MySQLが出している最新のMySQLのソースをビルドしてdebパッケージを作成する方法を見つけたのでメモしておきます。ちなみにMySQL以外にもperlとかのパッケージもこの方法で最新にすることが出来ると思います。
debを作成するための準備
まずはビルドする人間のメールアドレスと名前を環境変数で設定しておきます。適当に自分のものに置き換えて下さい。
$ export DEBEMAIL="hogehoge@foo.com"
$ export DEBFULLNAME="Your Name"
次にパッケージをビルドするために必要なものをインストールします。
$ sudo aptitude install dh-make devscripts debhelper fakeroot lintian sudo pbuilder piuparts dpatch build-essential
$ sudo aptitude build-dep mysql-server-5.1
ソースの取得
Ubuntuのdebを作成するのに使われているソースを取得します。ソースはカレントディレクトリに展開されるので、必要があれば適当なディレクトリに cd しておいてください。
$ apt-get source mysql-server-5.1
次に最新のMySQLのソースをmysql.comより取得します。”Select Platform”というプルダウンで”Source Code”を選択し、”Generic Linux (Architecture Independent), Compressed TAR Archive”をダウンロードして下さい。現在の最新版は5.1.42になるので、mysql-5.1.42.tar.gz としてダウンロードしています。
ビルド
ではビルドを始めます。uupdate というコマンドを使うと、自動的に引数で指定した最新のソースパッケージを展開しそこにDebianのパッケージを作成するためのファイルが生成されます。(つーか
uupdate便利過ぎ！)
$ cd mysql-dfsg-5.1-5.1.37
$ uupdate ../mysql-5.1.42.tar.gz
“cd ../mysql-dfsg-5.1-5.1.42 しろ”と言われるので、cd してパッケージをビルドします。(私はAthlon X2 5050eの環境で約2.5時間かかりました)
$ dpkg-buildpackage -uc -us -rfakeroot
インストール
ビルドが完了すると .deb パッケージがソースパッケージのディレクトリに作成されているので、必要なものをdpkg -iしてインストールします。ただし、依存関係が若干複雑なのでエラーが出たら依存しているものを先にインストールすると良いでしょう。
sudo dpkg -i \
libmysqlclient16_5.1.42-0ubuntu1_amd64.deb \
libmysqlclient-dev_5.1.42-0ubuntu1_amd64.deb \
libmysqlclient16-dev_5.1.42-0ubuntu1_all.deb \
mysql-common_5.1.42-0ubuntu1_all.deb \
mysql-client-5.1_5.1.42-0ubuntu1_amd64.deb \
mysql-client_5.1.42-0ubuntu1_all.deb \
mysql-server-5.1_5.1.42-0ubuntu1_amd64.deb \
mysql-server-core-5.1_5.1.42-0ubuntu1_amd64.deb \
mysql-server_5.1.42-0ubuntu1_all.deb \
libmysqld-dev_5.1.42-0ubuntu1_amd64.deb \
libmysqld-pic_5.1.42-0ubuntu1_amd64.deb
無事にインストールできれば、これでmysql-server-5.1が最新版になっています。mysqlコマンドでSQLを発行して動作を確認してみて下さい。
$ mysql -uroot -pxxxxx
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 72
Server version: 5.1.42-0ubuntu1-log (Ubuntu)
タイトル通りなんですが、今までは複数のテーブルを削除する時は
> drop table hoge;
> drop table fuga;
なんてやってましたが、
って書けるんですね。豆知識。
インフラエンジニアじゃないけどインフラエンジニア勉強会 hbstudy#5に参加してきました。もともとこのイベントには参加したいなぁと思っていて、参加登録したらいいタイミングで松信さんが講演することにw 貴重なMySQLのチューニングの話が生で聞けてとてもよかったよかった。
あと、最初にPostgreSQLの話をしていた永安さんのセッションもよかった。普通DBの入門の話ってあまりつっこんだ運用の話は出てこないと思うのですが、運用を意識した入門編でこういうのはすごい貴重だったのではないかと。
PostgreSQL安定運用のコツ2009
永安さんの話はスライドを見てもらえばほぼ全てわかります。スライドが充実し過ぎていてあまりメモを取っていなかったのですが、最近のポスグレはAuto Vacuumなんて仕組みがあって、あまりVacuumを意識しなくてよいのだなぁと。あとポスグレもチューニングはいかに共有バッファをうまく使うかっていうところで、あんまりMySQLと変わらないんだなって思いました。
Linux MySQLサーバーのパフォーマンスチューニング
資料(PDF)
MySQLのチューニングの基本はデータサイズを小さくしていかにメモリにのっけるか、という話。たとえば、日時を格納するカラムはDATETIME(8byte使用)じゃなくてTIMESTAMP(4byte)を使えとか。statusみたいな1/0しか入らないカラムは文字列型じゃなくてTINYINTかENUM使えとか。ちなみに日時は2038年問題が気にならないのであれば、UNIXTIME化してINT型のフィールドにしてしまうという荒技もありますよね。アプリケーション側でいちいち変換しなくてはいけないですが。
あと「巨大なTEXT/BLOBはクエリ効率を悪化させる」という話で、巨大なデータを格納するカラムは別テーブルにすると、それ以外のカラムのデータをSELECTするときに悪影響が出ないらしい。ちょっとどういう話か失念してしまったので、資料が公開されたら復習します。一定以上の大きさのテキストフィールドを別領域に保存するストレージエンジンとして、Falcon, PBXTがあるとのこと。ちなみに、HDDが一秒間に処理できるランダムI/Oはせいぜい数百ぐらいなので、とても遅いですと。
あとは実データを引かずにCovering Index(インデックスだけを読む検索)でうまく処理する方法もあるそうで、
- テーブルのレコードにアクセスする必要がなくなるので、高速になる
- Indexのサイズが大きくなるので、更新のコストが高くなる
- Limit句を使うときにも効果がある
というメリットデメリットがあるそうです。
- メモリを十分に確保してダイレクトI/Oを活用する
- オンライン処理のあとに、バッチ処理で巨大なテーブルに対してフルスキャンするのは問題がある
- バッチ処理によるバッファプールが占有され、オンラインのバッファプールが追い出されてしまうため
- OOM Killerに注意する
- ダイレクトI/Oを使うとプロセス内にデータが置かれるので、プロセスのサイズが大きくなる
- DBサーバとしてはファイルシステムキャッシュを縮小してほしい
- # echo 0 > /proc/sys/vm/swappiness = 0
- -> Direct I/Oとセットで使うことが多い
- cpで大きいファイルをコピー
- cpに対してファイルシステムキャッシュが使われる
- InnoDBのプロセスのデータがスワップに追い出される->これは避けたい
- ファイルシステムはext3
- もっとも使われていて安全
- dir_index, noatime(relatime)
- xfsはDirect I/Oだと並列で書き込める
- xfsは巨大なファイルのコピーがはやい
- でもxfs使っている人少なすぎなので、おすすめできないｗ
- 監視の方法
- iotop: プロセス単位でI/O量を取る kernel 2.6.20以降
- ネットワーク統計: MySQL Cluster使う人には必要かも
- mtstat: 一秒おきに受信/送信byte数を表示
- /proc/net/dev をみればわかる情報
- SSD
- ランダムリードはHDD: 200回にたいして、25000回のI/O (Intel X25E)
- 書き込み性能は製品による差が激しい
- write cache必須
- バッテリーで守れていることが重要。RAIDコントローラに任せるものとSSD自身で持つものがある(RAID Controllerの場合はそれがSSDに対応していることが重要)
- SSDは並列性が重要。Crystalなんとかのベンチはシングルプロセスの話なのであてにならない
- PCI-E型SSDにも注目 -> I/Fの速度が速い(300MB -> 2GB)
途中から殴り書きですが、TIMESTAMP型が4byteとDATETIMEの半分で済むことにこの日初めて知りました。その他Covering Indexなど、知らなかったテクニックなのでとても勉強になりました。あとSSDは本当もうすぐそこまで来ていて、これを入れるだけで数倍DBのI/Oが速くなることを考えるとすごいなぁと。しかし色々ベンチ取られていて、すごく説得力のあるお話でした。この人にコンサル頼んだらいくらかかるんだろう…
Ubuntu 9.04にはMySQL 5.1のパッケージが用意されているのですが、バージョンが5.1.31であり最新ではないので(現時点で最新は5.1.37)、最新版のdebパッケージを作成する方法を紹介します。debを作成するといっても、開発中のUbuntuに入っているmysql-server-5.1のソースを持ってきてビルドするだけなので、比較的お手軽にできます。
debを作成するための準備
まずはビルドする人間のメールアドレスと名前を環境変数で設定しておきます。
$ export DEBEMAIL="hogehoge@foo.com"
$ export DEBFULLNAME="Your Name"
次にパッケージをビルドするために必要なものをインストールします。
$ sudo aptitude install dh-make devscripts debhelper fakeroot lintian sudo pbuilder piuparts dpatch
ソースの取得
“mysql-dfsg-5.1” source package in Ubuntuよりdevelopmentのkarmicの方のリンクをクリックして、その先の画面で以下の3つのファイルをダウンロードします。
- mysql-dfsg-5.1_5.1.37.orig.tar.gz – ソースtarボール
- mysql-dfsg-5.1_5.1.37-1ubuntu2.diff.gz – debを作成するためのdiff
- mysql-dfsg-5.1_5.1.37-1ubuntu2.dsc – パッケージングのルールを記載したファイル
今回は ~/deb というディレクトリを作成してその中で作業するので、debディレクトリにダウンロードしておきます。
mysql-dfsg-5.1のビルド
mysql-dfsg-5.1のビルドに必要なパッケージをインストールしておきます。
$ sudo aptitude build-dep mysql-server-5.1
次にソースを展開します。dpkg-source -x を実行するとソースを展開するだけはなく、パッチも自動的に当ててくれます。
$ dpkg-source -x mysql-dfsg-5.1_5.1.37-1ubuntu2.dsc
dpkg-source: warning: extracting unsigned source package (mysql-dfsg-5.1_5.1.37-1ubuntu2.dsc)
dpkg-source: extracting mysql-dfsg-5.1 in mysql-dfsg-5.1-5.1.37
dpkg-source: info: unpacking mysql-dfsg-5.1_5.1.37.orig.tar.gz
dpkg-source: info: applying mysql-dfsg-5.1_5.1.37-1ubuntu2.diff.gz
そしていよいよビルドです。(私はAthlon X2 5050eの環境で約2時間かかりました)
$ cd mysql-dfsg-5.1-5.1.37
$ debuild -us -uc
場合によっては「”hardening-wrapper”というパッケージがないよ」というエラーになってビルドできないかもしれないので、このパッケージをaptitudeでインストールしておきます。
完了すると .deb パッケージが~/deb/配下に作成されているので、必要なものをdpkg -iしてインストールします。ただし、依存関係が若干複雑なのでエラーが出たら依存しているものを先にインストールすると良いでしょう。
$ sudo dpkg -i mysql-common_5.1.37-1ubuntu2_all.deb ¥
libmysqlclient16_5.1.37-1ubuntu2_amd64.deb ¥
mysql-client_5.1.37-1ubuntu2_all.deb ¥
mysql-client-5.1_5.1.37-1ubuntu2_amd64.deb
$ sudo dpkg -i mysql-server-core-5.1_5.1.37-1ubuntu2_amd64.deb ¥
mysql-server-5.1_5.1.37-1ubuntu2_amd64.deb ¥
mysql-server-5.1_5.1.37-1ubuntu2_amd64.deb
なお、5.0から5.1にアップグレードするような場合、my.cnfに使えなくなったオプションを書いていたりするとmysqldの起動に失敗するので、エラーログを見て適切に対処しましょう。例えば私の場合は –skip-bdb というオプションが使えなくなっていてエラーになっていました。
無事にインストールできれば、これでmysql-server-5.1が最新版になっています。mysqlコマンドでSQLを発行して動作を確認してみて下さい。
$ mysql -uroot -pxxxxx
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 68
Server version: 5.1.37-1ubuntu2-log (Ubuntu)
補足
今回はUbuntu 9.04で試しましたが、Debianでも同じ手順でパッケージを作成することができるはずです。また、debパッケージの作成にあたってDebianパッケージ60分クッキング(PDF)を参考にさせて頂きました。
MySQLのデータベースをバックアップする際にmysqldumpを使用しますが、個人的に「これはつけたらよさそう」と思っているオプションを紹介します。
–opt
–quick –add-drop-table –add-locks –extended-insert –lock-tables を指定するのと同じです。ダンプしたデータをMySQL サーバに読み込むための最速ダンプを提供します。(マニュアルそのまんま)
–single-transaction
ダンプする際に先頭にBEGINをつけるため、ダンプ時のデータのトランザクションの一貫性を保つことができます。ただしInnoDBなどのトランザクションが有効なストレージエンジンではないと意味がないです。
–flush-logs
ダンプを開始する前に、MySQL サーバ内のログファイルをフラッシュします。バイナリログを保存するような設定になっている場合はこれがフラッシュされます。ダンプしたデータとバイナリログを使用してデータを復元する場合に有用です。
–master-data
CHANGE MASTER TOコマンドをダンプの先頭に付加します。–master-data=2を指定するとCHANGE MASTER TOがコメントアウトされた状態になります。–maser-data=1と指定するとコメントアウトされずにダンプされます。
–default-character-set
ダンプする際の文字コードを指定します。
–hex-blob
バイナリ型のデータをエスケープ処理を行わずに実際に格納された値の16進表記でダンプします。 これを指定しないと default-character-set がSJIS系の場合エスケープ処理に失敗し、バイナリデータが壊れてしまう場合があります。
まとめ
自分はこんな感じでバックアップをとっています。
$ mysqldump -uroot -pxxxxxxxx --opt --flush-logs --single-transaction --master-data=2 --default-character-set=utf8 --hex-blob <database>
自分がMySQLをインストールしたあとに行う設定を備忘録がてら書いてみます。サーバのスペックによって若干変動するところもありますが、チューニングする項目というのは大体こんなもんでしょう。
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking
# ここまではわりとお決まりの設定。環境によってファイルのパスは適宜変えます。
# サーバ側で使用する文字コードの設定
default-character-set = utf8
# クライアント/サーバ側での文字コード変換を行わない
skip-character-set-client-handshake
# CREATE TABLE する際に ENGINE=InnoDB の指定をしなくてもInnoDBにする
default-table-type = InnoDB
# クライアント/サーバがやり取りすることのできるパケットサイズを指定
# 巨大なデータをやり取りする場合は指定した方がよい
max_allowed_packet = 16M
# MyISAMで使用するINDEXのキャッシュサイズ
key_buffer = 64M
# スレッドのスタック領域のサイズ
thread_stack = 128K
# 生成されたコネクション用のスレッドをキャッシュしておく数
thread_cache_size = 16
# 最大同時接続数。メモリ搭載量にもよるが100〜300ぐらいが良いらしい
max_connections = 150
# MyISAMで一度開いたテーブルをキャッシュしておく数
table_cache = 128
# 同時に実行できるスレッド数。CPUのコア数の2倍を設定
thread_concurrency = 2
# query cacheの設定
# 0: OFF, 1:ON, 2:DEMAND
query_cache_type = 1
# キャッシュするクエリ結果の最大サイズ
query_cache_limit = 1M
# クエリキャッシュに割り当てるメモリ量
query_cache_size = 16M
# slow logの設定
log_slow_queries = /var/log/mysql/mysql-slow.log
# 3秒以上かかったクエリは上記ログファイルに出力
long_query_time = 3
# ここからレプリケーションの設定
# MySQLサーバごとに固有のID
server-id = 1
# バイナリログ出力先
log_bin = /var/log/mysql/mysql-bin.log
# データ書き込み(トランザクションのコミット)を何回行ったらバイナリログを
# フラッシュするかの回数。0の場合はデータ書き込みがあってもバイナリログをフラッシュしない
sync_binlog = 0
# バイナリログを保持しておく日数
expire_logs_days = 60
# バイナリログの1ファイルあたりの最大サイズ
max_binlog_size = 1024M
# バイナリログを出力する対象のDB
binlog_do_db = hoge
# バイナリログの出力対象外とするDB
binlog_ignore_db = mysql information_schema
# Berkley DBは使わない
skip-bdb
# InnoDBのデータファイルの名前とどのぐらいで自動拡張するか
innodb_data_file_path = ibdata1:128M:autoextend
# テーブルごとに.ibdのデータファイルを作成するかどうか
innodb_file_per_table = 1
# InnoDBの内部データなどを保持するための領域
innodb_additional_mem_pool_size = 20M
# InnoDBのデータやインデックスをキャッシュするためのメモリ上の領域
# DBサーバ専用のマシンの場合はメモリの50%-80%を指定
innodb_buffer_pool_size = 256M
# InnoDBの更新ログを記録するメモリ上の領域
innodb_log_buffer_size = 8M
# InnoDBの更新ログを記録するディスク上のファイルのサイズ
# 大きくするとパフォーマンスはあがるがクラッシュ時のリカバリに時間がかかるようになる
innodb_log_file_size = 64M
# データをファイルに書き込む際のメソッド。
# Linuxの場合は O_DIRECTがパフォーマンス的にはよい
innodb_flush_method = O_DIRECT
# InnoDBのログバッファをInnoDBログファイルに書き込むタイミングを決める
# 基本的には1
innodb_flush_log_at_trx_commit = 1
# 2相コミットを行うかどうか
innodb_support_xa = OFF
# クラッシュ対策としてデータの二重書き込みを無効にする
skip-innodb_doublewrite
innodb_で始まるInnoDB関連の設定項目の詳細についてはMySQL5開拓団 ストレージエンジンの吟味 (2)にとても詳しく載っているので一読してみた方がよいです。あとはDSAS開発者の部屋:5分でできる、MySQLのメモリ関係のチューニング！も非常に参考になるでしょう。MySQLの設定はなかなか奥が深いですが、設定項目の意味を理解していればよいパフォーマンスを引き出せると思います。
コメント