ひげろぐ

最近管理しているDBサーバで継続的にスロークエリが出るようになったので、チューニングしてみたら気持ちの良い結果が出た。
結論から言うとカラム数が多いテーブルに対しては:selectで取得するカラムを絞るのがかなり有効かと思う。

現状把握

今回スロークエリの発生していたテーブルの状況を整理したのが以下。

• レコード件数は110万件くらい
• カラム数は30程度
• インデックスは効いている（explainで確認済み）
• 処理の性質的にキャッシュは使えない

スロークエリになっているのはもっぱら以下のクエリ。

select * from pages order by updated_at limit 100;

Railsのコードで見るとこんなかんじ。

Page.all(:order => 'updated_at', :limit => 100)

こんな単純なクエリが実行に2秒から10秒程度もかかってスロークエリとして記録されているのは切ない。
インデックスは効いているので問題解決には他のアプローチが必要になる。

考えるに対象は30以上カラムがあってレコードサイズもそこそこ大きいテーブル。
そこで取得するカラムを絞って余計なカラムを取得しないようにしてみたらどうかと思った。

というかクエリが単純すぎてまずはそれくらいしか浮かばなかったわけだけど。

ベンチマークとチューニング

計測なくしてチューニングなしということでベンチマークで使ったのはmybench。

• mybench – a simple benchmarking tool for MySQL

ベンチマークとチューニングは手元の開発環境で実行した。
こちらレコード件数は3万件程度。本番環境より大幅に少ないが十分だろう。たぶん。

全カラム取得とカラムを絞った結果の比較が以下。
10クライアントから100回ずつ、計1000回のリクエストを送るというのを試行回数3回ずつ行った結果。
serialは経過時間（秒）です。

まずは全部まるごと取得している現状のクエリ。

select * from pages order by updated_at limit 100;
# Page.all(:order => 'updated_at', :limit => 100)
  serial  : 29.173278
  serial  : 29.433684
  serial  : 30.258237

これを取得カラムを絞ったものにしてみると。

select id, updated_at from pages order by updated_at limit 100;
# Page.all(:select => 'id, updated_at', :order => 'updated_at', :limit => 100)

  serial  : 16.422306
  serial  : 17.562543
  serial  : 16.070013

倍近く速くなった。
うつくしい。

いやぁ、チューニングって本当に気持ちがいいものですね。
これを本番環境にアップしたらスロークエリもパッタリなくなり幸せになれました。

以下詳しく見たい人向け

select * from pages order by updated_at limit 100;
# Page.all(:order => 'updated_at', :limit => 100)

test: 1000 0.001631 0.060372 0.029173278 29.173278 342.779443571614
  clients : 10
  queries : 1000
  fastest : 0.001631
  slowest : 0.060372
  average : 0.029173278
  serial  : 29.173278
  q/sec   : 342.779443571614

test: 1000 0.001535 0.06981 0.029433684 29.433684 339.746801657584
  clients : 10
  queries : 1000
  fastest : 0.001535
  slowest : 0.06981
  average : 0.029433684
  serial  : 29.433684
  q/sec   : 339.746801657584

test: 1000 0.00298 0.065291 0.030258237 30.258237 330.488521191767
  clients : 10
  queries : 1000
  fastest : 0.00298
  slowest : 0.065291
  average : 0.030258237
  serial  : 30.258237
  q/sec   : 330.488521191767

select id, updated_at from pages order by updated_at limit 100;
# Page.all(:select => 'id, updated_at', :order => 'updated_at', :limit => 100)

test: 1000 0.000327 0.037233 0.016422306 16.422306 608.927881382797
  clients : 10
  queries : 1000
  fastest : 0.000327
  slowest : 0.037233
  average : 0.016422306
  serial  : 16.422306
  q/sec   : 608.927881382797

test: 1000 0.001182 0.050836 0.017562543 17.562543 569.393623691057
  clients : 10
  queries : 1000
  fastest : 0.001182
  slowest : 0.050836
  average : 0.017562543
  serial  : 17.562543
  q/sec   : 569.393623691057

test: 1000 0.000301 0.04706 0.016070013 16.070013 622.277032383234
  clients : 10
  queries : 1000
  fastest : 0.000301
  slowest : 0.04706
  average : 0.016070013
  serial  : 16.070013
  q/sec   : 622.277032383234

CentOSにてパッケージインストールのMySQLを元に。
割とやっつけ。

手順は以下の通り。

• /etc/my2.cnfを準備
• データディレクトリの準備
• 起動スクリプトのコピーと編集
• 自動起動の設定

/etc/my2.cnfを準備

テキトーに。
以下の項目は起動スクリプトで使われるので必須。

• datadir
• socket
• log-error
• pid-file

あとportとかもかぶらないように。

データディレクトリの準備

テキトーに。

起動スクリプトのコピーと編集

$ sudo cp /etc/init.d/mysqld /etc/init.d/mysqld_2
$ sudo vi /etc/init.d/mysqld_2

やっつけ感ただようファイル名。

ヘッダ行とかの書き換え

mysqlをmysql_2にするとかてけとうに。

# mysqld_2      This shell script takes care of starting and stopping
#               the MySQL subsystem (mysqld).
#
# chkconfig: - 64 36# description:  MySQL database server.
# processname: mysqld_2
# config: /etc/my2.cnf
# pidfile: /var/run/mysqld/mysqld_2.pid

prog="MySQL_2"

get_mysql_option関数の変更

my_print_defaultsコマンドに与える引数に設定ファイルを指定するオプションを追加する。

result=`/usr/bin/my_print_defaults -c /etc/my2.cnf "$1" | sed -n "s/^--$2=//p" | tail -n 1`

mysqld_safeの実行行

オプションは全部設定ファイルで与えることにして、他のオプションは全部削除。

/usr/bin/mysqld_safe --defaults-file=/etc/my2.cnf >/dev/null 2>&1 &

ここまでやると以下のようにして起動できるようになる。

$ sudo /etc/init.d/mysqld_2 start

psコマンドなどできちんと起動しているかどうか確認できればオッケー。

自動起動設定

仕上げ

$ sudo /sbin/chkconfig --add mysqld_2
$ sudo /sbin/chkconfig mysqld_2 on

こないだいくつかのサービスやブログのデータベース移行を行った。
DBMSはおなじみMySQL。

移行と行っても停止して全然問題のないデータベースなので単純にmysqldumpして流し込むだけなのでお手軽簡単。
ただ移行前のMySQLは文字コードが完全バカ設定だったが、移行後のMySQLは真面目状態に設定した状態になっているので、文字コードにちょっと気を遣ってやる必要がある。

移行前のデータ

移行前のMySQLには入っているデータに二つのケースがある。

• テーブルの文字コードがlatin1でUTF-8のデータが入っているケース
• テーブルの文字コードがUTF-8でUTF-8のデータが入っているケース

後者の場合は何も考えずダンプしたデータをそのまま流し込めばいいので苦労はないが、前者の場合は次の手順でダンプ内容をちょっと書き換える。

latin1からutf8へ

まずダンプする。
そのまま手を加えず取り出すためlatin1で。

 $ mysqldump -u hogeuser -p --default-character-set=latin1 hogedb > hogedb.dump

ダンプされたデータ内容はUTF-8だが、データベースやテーブルの文字コードがlatin1なのでこれを真面目MySQLに読ませると文字化ける。
そこで「latin1」を「utf8」に書き換える

ここでsedとか使いこなせるとかっこいいのかもしれないがぐぐらないと使い方が思い出せないのでファイルもそんなに大きくなかったしviで開いて置き換えてしまった。
でもダンプファイルが大きい場合はしんどいと思われるので一応ぐーぐる先生に聞いたやり方を参考までにメモっておく。

sed -e s/latin1/utf8/ hogedb.dump > hogedb_utf8.dump

文字コード指定以外に「latin1」という文字列があった場合はそれも巻き込んで変換してしまうので、そういう場合は適当に工夫していただきたい。

参考

ヽ( ・∀・)ノくまくまー(2006-10-11) [MySQL] 文字化け問題を本気で直す

「赤の MySQL さん、ここは “utf8″ になって頂きたかった。(児玉清)」のフレーズがいまだに心に響く。