0

mySQLの制御フロー関数で嵌まる

node.jsでmySQLを使ってると非同期コールバックだらけでselectしてupdateしてまたselectしてといったように処理が煩雑になりがちです。
joinを避けてクエリーを発行していくとなおさら。

そういった事を極力避ける為、制御フロー関数(IF)なんかを使い倒して一つのクエリで処理させる事が多いのですが、このIFの挙動で嵌まってしまった。

やりたい事は、member_idが0だったら、更新したばかりのupdated_idの値でmember_idで更新してやる。それ以外だったら99を入れる。

元々のデータはこんな感じ。
mysql> select updated_id, member_id from sample where id = 5;
+------------+----------------+
| updated_id | member_id |
+------------+----------------+
|      10      |           0         |
+------------+----------------+

下記のようなIFを使ったクエリでUPDATEしてみると、member_idに10が入ると思いきや77が入った。
mysql> UPDATE sample SET updated_id = 77, member_id = IF(member_id=0, updated_id, 99) WHERE id = 5;

mysql> select updated_id, member_id from sample where id = 5;
+------------+----------------+
| updated_id | member_id |
+------------+----------------+
| 77 | 77 |
+------------+----------------+

なんでもう77入ってるの?
updated_id = 77としてるものが先に更新されてるような結果になった。
されてるようなでなくて更新されてた。

クエリを少し変えて、updated_id = 77を後にもってきたら想定してた通りの動きになった。
mysql> UPDATE sample SET member_id = IF(member_id=0, updated_id, 99), updated_id = 77 WHERE id = 4;

mysql> select updated_id, member_id from sample where id = 4;
+------------+----------------+
| updated_id | member_id |
+------------+----------------+
| 77 | 10 |
+------------+----------------+

同じクエリを続けて発行すると、すでに77が入ってるupdated_idの値がmember_idに入った。これは当然だし納得。
mysql> UPDATE sample SET member_id = IF(member_id=0, updated_id, 99), updated_id = 77 WHERE id = 4;

mysql> select updated_id, member_id from sample where id = 4;
+------------+----------------+
| updated_id | member_id |
+------------+----------------+
| 77 | 77 |
+------------+----------------+

クエリが実行される前に、値が更新されてるというのが理解できなかったけど、そういうものらしい。
やはり内部の動きまで知らないとこういうところで無駄な時間を食ってしまう。
phpの挙動がおかしいなと思ったらcのコード追って原因を究明するくらいにならないとダメと言われた事があったけど、今回の一件で納得した。

0

mySQLのJOINと個別取得のベンチマーク

以前はなんとしても一発のクエリでデータを取得してやるみたいなのがあったけど、ソーシャルゲームなんかを作ってるとmySQLの激しい結合のせいでパフォーマンスが落ちるって事がよくあります。
「結合が悪いというかそれは設計の問題だ!」という意見は今は無視。
確かに、経験上結合やめて高速になったことはあったけど、結合にして高速になったて事はなかったので、「結合の利用は控えめに」となってた。
そんな軽い認識でいたけど、気になったのでモヤモヤを晴らすためにもどれだけパフォーマンスに影響するのか気になったのでベンチマークをとってみました。

まず一般的な取得の方法は以下の3つ。
1対1の関係のメンバーとアイテムというテーブルからすべてのデータを取得したい場合
A、結合で取得
良い点:単純な結合なら楽チン。
悪い点:結合が激しくなるとインデックスがどうなってるかワケがわからなくなる。

B、メンバーテーブルをループで回す。
メンバーテーブルの外部キーを取得して都度アイテムテーブルにアクセスして取得
良い点:少ないレコードの場合手っ取り早い。
悪い点:明らかにクエリー数が増えるのでパフォーマンスに悪影響。

C、アイテムテーブルを予め配列にしておく。(プライマリーキーを配列のキーに設定)
メンバーテーブルをループで回して、用意しておいたアイテム配列から取得。
良い点:高速。クエリー数が少なくて済む。
悪い点:検索の時に面倒な事が多い。本来DBですべき処理をプログラム上で処理する必要がある。

実務で使うのはAでパフォーマンスが落ちたらCを考える。Bは書き捨てのコードを書く時くらい。

ベンチマークとったのもこのメンバーとアイテムの関係です。
■メンバーテーブル
CREATE TABLE IF NOT EXISTS `member` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`membername` varchar(255) NOT NULL,
`item_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `item_id` (`item_id`)
) ENGINE=MyISAM;

でレコードを10,000件登録。

■アイテムテーブル
CREATE TABLE IF NOT EXISTS `item` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`itemname` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;

でレコード数を10件登録。

使ったスクリプト

実行環境
– php:5.3.6
– mySQL:5.1.55
– memcache:1.4.5

以下を実行(memcacheも入れてみた)
// 単純にメンバー名を取得
getMember();
// itemテーブルをjoinさせて表示[A]
getMemberItemWithJoin();
// 1件づつitemテーブルからitemidを取得して表示[B]
getMemberItemWithOneByOneFromDB();
// itemテーブルを予め取得しておいて表示[C]
getMemberItemWithAllFromDB();
// memcacheにあるitemデータを取得しながら表示[B]
getMemberItemWithOneByOneFromMemcache($memcache);
// memcacheにあるitemデータを予め取得しておいて表示[C]
getMemberItemWithAllFromMemcache($memcache);

結果。
何度かやって2と3は稀に順位が変わったけどそれ以外は変わらず。
1 getMember: 0.026777982711792 seconds
2 getMemberItemWithAllFromDB: 0.028398036956787 seconds
3 getMemberItemWithAllFromMemcache: 0.036365985870361 seconds
4 getMemberItemWithJoin: 0.048348903656006 seconds
5 getMemberItemWithOneByOneFromMemcache: 0.6713719367981 seconds
6 getMemberItemWithOneByOneFromDB: 1.0738849639893 seconds

1は当たり前なので無視。
結合か纏めて取得かで単純に言えば纏めて取得の方が速かった。上の分類でいうとCのやり方が一番早い。
上記でも書いたように検索時に面倒だったり、コードが長くなったりにもするけどこの分母の少なさでこれだけ差が出るとパフォーマンス重視のサイトにはやっぱり結合は控えるべき。
KVSももちろん使うべき。
なんとも幼稚臭い内容だけど、実際ベンチマークを取ると理解が深まるのがいい。

0

mySQL5.1 on Mac

macOS X(10.6)にphp+apache+mySQLで開発環境を整えてみた。
ここ数年の自分の開発環境というのは、linuxサーバーにcodaでつなげてリモートで開発するような形でした。
ソーシャルアプリだとgadget.xmlを外部からアクセスできる場所に置いておく必要があるのでこれが一番良かったりしたのですが、最近は少しソーシャルアプリから離れつつあるということで開発環境を改めてみました。
といって単に普段使ってるmacを開発環境にしただけなんだけど、実はこのスタイルは初めて。
本番環境はcentOSかfedoraなので、それと違う環境で開発するというのが二度手間になる部分(ソフトのインストールとかpathの設定とか)があるのではないかと躊躇してたけど、実際インストールが完了して開発する事になってもそんな気にする事態は今の所発生していません。

phpとapacheは元々入ってるのでそれをそのまま使います。
mySQLはmac portsから入れました。昔いれたmysql5.0の残骸が残ってたのでこの通りやっても環境によってはうまくインストールされないかもしれません。
ログがなくなってしまったので覚えてる限りです。

ネット上によくあるインストールログのようにするとserverの方が入りません。
# sudo port install mysql5 +server

このコマンドでいれてと親切にでてきたのでそのまま実行します。
# sudo port install mysql5-server

以前5.0が入ってたせいかactivateしろみたいな事がでたのでactivateする。
# sudo port -f activate mysql5-server

面倒なのでmacごと再起動。
# mysql5 -u root
とりあえず動いた。たぶんmysql5.0の残骸のお陰。

# ps aux | grep mysql
_mysql 1463 0.0 0.5 2518252 19688 ?? S 5:57PM 0:00.08 /opt/local/libexec/mysqld --basedir=/opt/local --datadir=/opt/local/var/db/mysql5 --user=_mysql --log-error=/opt/local/var/db/mysql5/aoki-takashi-no-iMac.local.err --pid-file=/opt/local/var/db/mysql5/aoki-takashi-no-iMac.local.pid --socket=/opt/local/var/run/mysql5/mysqld.sock --port=3306
root 1365 0.0 0.0 2435548 784 ?? S 5:57PM 0:00.02 /bin/sh /opt/local/lib/mysql5/bin/mysqld_safe --datadir=/opt/local/var/db/mysql5 --pid-file=/opt/local/var/db/mysql5/aoki-takashi-no-iMac.local.pid
root 1356 0.0 0.0 2446916 1020 ?? Ss 5:57PM 0:00.00 /opt/local/bin/daemondo --label=mysql5 --start-cmd /opt/local/etc/LaunchDaemons/org.macports.mysql5/mysql5.wrapper start ; --stop-cmd /opt/local/etc/LaunchDaemons/org.macports.mysql5/mysql5.wrapper stop ; --restart-cmd /opt/local/etc/LaunchDaemons/org.macports.mysql5/mysql5.wrapper restart ; --pid=none

確かに動いた。親切にも引数の説明がされてる。
# sudo /opt/local/etc/LaunchDaemons/org.macports.mysql5/mysql5.wrapper restart
Shutting down MySQL
... SUCCESS!
Starting MySQL
. SUCCESS!

ちゃんと再起動される。

文字コードをUTF8に変えようと思ったけど、my.cnfがない。
mySQLのページで親切に説明されてる方がいたので参考にさせもらいました。
(このmySQLのページのローカライズはなんでこんな中途半端なんだろうか)

# cd /opt/local/var/macports/software/mysql5/5.1.53_0/opt/local/share/mysql5/mysql/
sudo cp my-medium.cnf /etc/my.cnf

my.cnfを編集する
[mysqld]
max_allowed_packet=16MB
default-character-set=utf8
skip-character-set-client-handshake
 
sudo /opt/local/etc/LaunchDaemons/org.macports.mysql5/mysql5.wrapper restart

これで無事UTF8で動くようになりました。

0

スロークエリーのログローテーション

第一前提として、スロークエリーのログを保存できるようにしておく。
log-slow-queries=/mnt/log/mysql-slow.log
long_query_time=5
log-queries-not-using-indexes

これは、自分の環境だけなのか mysql-slow.log を予め作っておく必要がある。
mySQLが勝手に生成してくれない。
今も謎。仕様?
# mkdir /mnt/log/mysql-slow.log
# chmod 666 /mnt/log/mysql-slow.log

ログローテーションで使うのでrootのアイパスをmy.cnfに追記しておく。
[mysqladmin]
password = "hogehoge"
user = root

そしてmysqldを再起動。

設定ファイルのテンプレートがyumでmySQLをいれたら入ってたのでそれを利用

# cp /usr/local/mysql/share/mysql/mysql-log-rotate /etc/logrotate.d/

/mnt/log/mysql-slow.log {
notifempty
daily
rotate 3
missingok
compress
postrotate
# just if mysqld is really running
touch /mnt/log/mysql-slow.log
chmod 666 /mnt/log/mysql-slow.log
if test -x /usr/local/mysql/bin/mysqladmin && \
/usr/local/mysql/bin/mysqladmin ping &>/dev/null
then
/usr/local/mysql/bin/mysqladmin flush-logs
fi
endscript
}

上記のようなログファイルが生成されないという問題があるので、無理やりファイルを作ってローテーションする。

そもそもローテーションしないといけないくらいログが溜まること自体問題なのだけど、1ファイルがでかくなり過ぎるのは精神衛生上もよくないので一応やっておく。

0

mmeasure

mmeasureはmySQLの負荷を監視、チューニングのアドバイスを提供してくれるツールです。
今まで知らなかったけど、けっこう古いツールです。
そのままだとmySQL5.1以上は動きません。

cactiでもお馴染のグラフ生成に必要なrrdtoolをインストール
# yum install rrdtool

mmeasureの最新版をダウンロード&展開
# cd /usr/local
# wget http://prdownloads.sourceforge.jp/mmeasure/18557/mmeasure-1.0.7.tar.gz
# tar xvzf mmeasure-1.0.7.tar.gz

mmeasure.confを修正する
# cp mmeasure_template.conf mmeasure.conf

このテンプレートがデフォルトでは設定項目が足りなくて動かないという頑固者。
生成する画像の設定
WEBDIR="$PATH_MMEASURE/web/images/graphs"
WIDTH="400"
WIDTH_SMALL="400"
WIDTH_LARGE="400"
HEIGHT="100"
HEIGHT_SMALL="100"
HEIGHT_LARGE="100"
IMAGEFORMAT="gif"

rrdtoolのパスを変更
PATH_RRDTOOL="/usr/bin/rrdtool"

mySQL関連の設定を変更
MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASSWORD=hogehoge
#MYSQL_PORT=3306
#MYSQL_SOCKET=/tmp/mysql.sock
MYSQL_SLOW_QUERY_LOG="/mnt/log/mysql-slow.log" # なければmy.iniで設定する

mySQL5.1だと起動はするものの数分したらコケるので下記のように男らしく修正
原因はtable_cacheがtable_open_cacheという名前に変わった為。
参考: mmeasuer 1.0.7を無理矢理mysql 5.1に対応させる
find /usr/local/mmeasure/ -type f -print0 | xargs -0 grep -l --null table_cache | xargs -0 perl -i.bak -p -e 's/table_cache/table_open_cache/g;'

同じような原因で、総クエリー回数以下の値を取得できないので、show statusをshow global statusにかえてやる。
find /usr/local/mmeasure/ -type f -print0 | xargs -0 grep -l --null table_cache | xargs -0 perl -i.bak -p -e 's/show status/show global status/g;'

これでmmeasure本体の設定は完了。

続いて、webで表示させるツールなのでhttpdも修正
/etc/httpd/conf.d/mmeasure.conf を作成

Alias /mmeasure/ "/usr/local/mmeasure/web/"
<Directory /usr/local/mmeasure/web/>
AuthUserFile /usr/local/mmeasure/web/.htpasspwd
AuthGroupFile /dev/null
AuthName "User Verification"
AuthType Basic
require valid-user
AddDefaultCharset euc-jp
</Directory>

mmeasure.shを起動
/usr/local/mmeasure/daemon/mmeasure.sh start

mmeasure.shを再起動
/etc/init.d/httpd restart

http://hogehoge.com/mmeasure/ にアクセスするとページが表示される。

注意点として、
「過去のインデックス未使用クエリー内訳」にページャーとかがついてるわけでないので、インデックス未使用クエリーが多いとずらーっと1ページに表示されしまう。

0

mySQLでレプリケーション

レプリケーションの設定のメモ。
まずマスターとなるサーバーで設定。
/etc/my.confを編集

[mysqld]
datadir=/mnt/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
log-bin
server-id=10
log-bin=mysql-bin
log-bin-index=mybin.index
expire_logs_days=14

log-binでバイナリログを保存しますよの宣言
server-idはスレーブと重複しないサーバーIDのようなもの。
log-bin=mysql-binとlog-bin-index=mybin.indexはバイナリログのファイル名。
mysql-bin.000001のようになります。
ここで指定したら/mnt/mysqlにバイナリログは蓄積されていきます。
指定しなかった場合、俺の環境では/var/run/mysqlに溜まりEC2のHDを圧迫してたので必須でした。
expire_logs_daysはログのローテーションの期間です。2週間サイクルにしています。

mySQLを再起動したらSQLを発行する度にバイナリログに書き込まれていきます。

スレーブの設定で使う読み取りバイナリログの読み取り開始位置を確認しておく。
mySQLにログイン

> SHOW MASTER STATUS;

Fileがlog-bin.000001
Positionが128
となってたのでそれをメモる。

ログインついでにスレーブからの接続専用のユーザーを作成する

> GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'replrepl';

スレーブと同期を取るために一旦mySQLを止める。
下記のようにしてスレーブにデータディレクトリをごっそり持っていきます。

rsync -avz -e ssh /mnt/mysql root@ec2-174-129-***-**.compute-1.amazonaws.com:/mnt

続けてスレーブのmy.confを設定
[mysqld]

datadir=/mnt/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
relay-log=relay-bin
server-id=14
master-host=ec2-174-129-**-**.compute-1.amazonaws.com
master-user=repl
master-password=replrepl
replicate-do-db=access
expire_logs_days=14

relay-log=relay-binでリレーされたらログがrelay-bin.000001という形で蓄積されていきます。
master-hostでマスターのサーバーのホスト名を
master-userとmaster-passwordで接続情報を登録しておきます。
replicate-do-dbで指定したDBだけリレーションするようにします。この場合accessのいうデータベースだけリレーションします。

次にmySQLにログイン。
バイナリログがまったくなければ

> start slave;

だけでもいけるかも。

>change master to master_host = 'ec2-174-129-**-**.compute-1.amazonaws.com', master_port = 3306, master_user = 'repl', master_password = 'replrepl', master_log_file = 'mysql-bin.00001', master_log_pos = 128;
start slave;

ここで先ほどメモった読み取り開始位置を入力する。

これでレプリケーションが始まる。
マスターで何かinsertしてスレーブに反映されていればOK。
レプリケーションされてなければmysqld.logを確認する。