どうもこんにちは。北海道に旅行中でこの記事は札幌のホテルから書いている oinume です。MySQL Casual Advent Calendarも11日目になりました。「MySQL Casualが全然カジュアルじゃない！」という意見をよく耳にするので、今日はウルトラカジュアルなエントリーを書こうと思います。
はじめに
MySQLを使って開発をしていると、本番と開発用サーバの見分けがつかずに、間違って本番で重いクエリを発行しちゃったりします。そういうオペミスを少しでも防ぐために「本番環境ではmysqlコマンドのプロンプトの色を変える」という技を紹介したいと思います。
rlwrap
紹介といってもやり方は非常に簡単で、下記のように rlwrap というコマンドを使って mysql コマンドを実行します。
rlwrap -a -pRED mysql -uroot
するとあら不思議！MySQLのプロンプトが下記のように赤くなりました！
というわけで、rlwrapがインストールされているマシンであれば、下記のように .bashrc 的なファイルに書いておくことでmysqlのプロンプトを常に赤くすることができてオペミスも減らせますね！
if [ -x /usr/bin/rlwrap ]; then
alias mysql='/usr/bin/rlwrap -a -pRED mysql'
fiちなみに rlwrap は Debian(Ubuntu) であれば apt でインストールできます。
sudo aptitude install rlwrap
CentOSの場合はepelリポジトリが追加されていれば yum でインストールできます。
さいごに
このウルトラカジュアルっぷりいかがでしたでしょうか。Advent Calendar 12日目は ikasam_a さんです。よろしくお願いします！
MySQL Casual Talks Vol.2で20分枠をもらって話してきた！こんなに長い時間人前で話すのは初めてだったので緊張しまくりでした。話した内容は仕事(ピグ)で利用しているMySQL上で運用しているNoSQLのやり方について。MySQLの設定とかの話は一切せずJavaのコードがメインでした。
正直、myfinderさんに声をかけられたときはどうしようかと悩んだけど、こういう経験はなかなかできないので良かったです。場数を踏むっていうのも重要ですしね。懇親会では riywo さんとシステム運用のガチトークをできたし、DeNAの人とたくさん絡めてよかったですｗ riywo さんが「MySQL Formal Talksやっても来るメンバーって変わらないよね」って言ってたのがなんともこの勉強会を象徴していて妙に印象に残っていますｗ
Vol.3 があったら今度は弊社メンバーの誰かにFusion-IOの話をしてもらいたいなぁ。
5.5ってどうなんだろう？と思ったので試しにこのブログのMySQLを5.1 -> 5.5にしてみた。
tar.gz のバイナリを取ってきて /usr/local/mysql-5.5.13-linux-2.6-i686 に展開して /usr/local/mysql にsymlink (バージョンアップに失敗したときにすぐに戻せるように)。あとはstop start して起動するだけ。
/usr/local/mysql/bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
って言われるので、libaioをインストール(ubuntu)
$ sudo aptitude install libaio
$ sudo /sbin/ldconfig
もう1回起動。今度は
110604 1:16:45 [ERROR] /usr/local/mysql/bin/mysqld: unknown variable 'default-table-type=InnoDB'
って怒られる。どうやら default-table-type は使えなくなったらしい。
default-storage-engine = InnoDB
としたら無事起動。
最後に
# /usr/local/mysql/bin/mysql_upgrade
して完了。今のところ無問題。
先日設定したサーバで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が速くなることを考えるとすごいなぁと。しかし色々ベンチ取られていて、すごく説得力のあるお話でした。この人にコンサル頼んだらいくらかかるんだろう…
コメント