Archive for the ‘MySQL’ Category.

トランザクションを理解する

さて、昨日書いた補足説明をしたいと思います。

まずレイドボスの場合は、倒したかどうか?という判断が必要になります。
読んでいる数値が保証されていなければどうなるか?
通常は討伐報酬が発生しますが、報酬の2重付与が発生してしまいます。
悲しい事に、ここまで書かないと何が悪いの?と理解できない人間が多いのがゲーム業界のサーバーエンジニアです。

ですから、プランナーがサーバーエンジニアをただのweb屋レベルのペチパーと勘違いするのも仕方ないと思います。
本当は数が少ないだけで、最も知識が必要で高いレベルが要求されるのがサーバーエンジニアです。
アプリ側は人間が余っていますが、マトモなサーバーエンジニア・インフラエンジニアは数が少ないのでどこの会社も人材を求めている所でしょう。

で、次に減らしてから増やすという話も説明しないと理解できない人が大勢いるでしょう。
そもそもA処理が終わってB処理が終わらない。
このシチュエーションが起こるケースは
1.トランザクションを利用していない。
2.B処理が別の保存先(例えばキャッシュ)
という事が考えられます。

もう1つ考えられる最もアホな発想についてまずは潰しておきます。
3.トランザクション中にコミットして片方が切り捨てられるという発想です。
はい、WALとか知らないんだったら勉強しましょうね。
insert文やupdate文を実行した時にそのデータはどこにいくとお考えですか?
メモリ上を更新するだけです。
そしてコミット処理を受け付けた時にmysqlが何をしているか知っていますか?
REDO(トランザクション)ログをシーケンシャルに書き出してメモリ上のデータが仮で無くなるだけです。
(設定されていればbinlogも)
binlogがredoログだと思っている人が多いですが、binlogはbinlogです。
多分レプリケーションの関係で後から付けた仕組みなんじゃないですか?>binlog
ib_logfile0とib_logfile1というのがmysqlのトランザクションログです。
チェックポイントに到達するとログファイルを切り替えて、実際にディスクに書き込むのでそれまではオンメモリなんですよ。
ですのでmysqlのリカバリのメカニズムは、ディスクに書き込まれていないデータはトランザクションログから復旧を試みます。
コミットに途中で?失敗する=トランザクションログの書き込みに途中で失敗する=mysqlサーバーのディスク逝ってるレベル。
2行目のクエリ書き込みタイミングでディスク逝く確率はゼロじゃないけど、このパターンの障害はファイルも救い出せないレベルじゃねーの?って事。
仕組みを知らずにケース3を想像してた人は、せいぜい意地になって超低確率のレアケースを想定してなさいってこった。

次にケース1
トランザクションを利用していない。>アホか、氏ね。

次にケース2
片方だけ更新しちゃう>アホか、氏ね
キャッシュだったとしても、ロールバック出来る形で実装していないのは設計が間違ってるだけ
キャッシュだったとしたら、キャッシュを消せば次回はDBから読み込んでキャッシュ化する作りになってなかったらアホ
ロールバックするの仕組みを実装するのが面倒だったら、DBへのコミット失敗したらキャッシュ消せ
つまり減らして増やすじゃなくて、DBコミット→キャッシュコミットという順番なら正しい
キャッシュコミットに失敗する=キャッシュサーバーがフェイルオーバーするから、DBから読み直して動くので全く問題なし
問題があるなら設計に問題がある、消えて困るデータをキャッシュするな

唯一筋が通りそうなのは、キャッシュじゃなくて
ケース4.垂直分割とかDBが分かれているケース
XA-TRANSACTION使えアホと言いたい所だが、Transactionマネージャーを自作するのが面倒くさいので自力Commitループしてるパターン。
このパターンだったら片方だけコミットされるというのはあり得るから、最終的にはエラーログからデータは戻すんだけど
それまでの間の問題にならないように減らす→増やすの順番でコミットせーよ!ってのは有りかもしれませんね。

SQLインジェクション対策で祭りがあったらしい

今更ながら2013年末に何やら盛り上がってたらしいのを知りましたw

「プリペアードクエリが基本だけど、動的に SQL を組み立てる場合もあるから、そういう場合に備えてエスケープも知っておいたほうがいいかも」
でググると関連するものが出てくるが、全く何を揉めてるのか分からなくて楽しかったです。

http://togetter.com/li/601462
異論を唱えてる誰かがいるらしくて、それがこの2人じゃないという構図なのでまったくもって意味不明なまとめです。
エスケープの知識はあって然るべきという当たり前の事を2人とも言ってるのに論点が噛み合ってないようで、これだけ読むと意味不明でした。

http://togetter.com/li/600908
次にこういうまとめにたどり着きました。
先程のまとめの題名だと、「プリペアードクエリが基本だけど」というタイトルだったので、そういう発言をしたのかと思っていましたが、このまとめではエスケープを使えと主張した事になってます。
一体大元の着火発言はどれだったんだ?と意味不明です。
多分全体の流れを分かってる人達は分かるんでしょうが、後から部外者が読むと話が噛み合ってないだけにしか見えませんから面白いですねw

http://archive.today/TGn8P
最後に辿り着いたのがこちらになります。
どうやらIPAの文書をディスったのが根底にあって、SQLインジェクションの話は全体から見ると枝葉の話題に過ぎないぽいですね。
なので2つ目のリンクの人みたいに感情的になって問答しているんだろうという結論に辿り着きました。

結論、まとめ(割愛具合)が悪いと後から部外者が見ると双方ともアホに見えるから可哀想。

クエリ発行回数の削減(SELECT)

MySQLのプロシージャは複数結果セットに対応しています。
これを利用しない手はありません。
昨日、関連テーブル一括削除のエントリーで1:nの親子関係テーブルを複数JOINするとねずみ算式に結果セットの行数が膨らむ話をしました。
そのようなケースでも利用できますし、JOIN出来ない関係ないデータを1クエリで同時に取得するという用途にも利用出来ます。

1:nの子テーブルがあるパターンです。
取得するカラム数も結果セット毎に違います。

スクリプトを準備します。
PDOには複数結果セットの為のPDOStatement::nextRowsetというメソッドが準備されています。

3回クエリを発行するのと同じ結果が1クエリで取得出来ます。

比較の為に普通に3回SELECTするテストスクリプトも用意します。

今回はループじゃなくてabでベンチマークを取ってみます。
ネットワークを介するデメリットが対象なのでDBサーバーとWEBサーバーはちゃんと別のVMになっています。
私のPCはシングルコアの糞PCにLubuntu入れて延命してる環境なので負荷は軽めにしています。

秒間38.35リクエストが64.57リクエストまで跳ね上がっています。
ネットワークを介するデメリットは想像以上ではないですか?
スレーブから取得するデータであれば、IF文等の制御ロジックを含むプロシージャも許容範囲となるかもしれません。

クエリ発行回数の削減(関連データ一括削除)3

手動で削除するテクニックを記載しましたが、MySQLに任せる事で1クエリで済ませる事も可能です。

DELETE CASCADE指定をしておけば親テーブルが削除された時に自動的に削除されます。
私は使用するなとの指示がありましたので使用していません。

使用して問題なければ、外部キー制約を付けるのが一番簡単で手っ取り早いですね。
MySQLが提供している機能なのですから性能面でも問題ないと思うんですけどねぇ。
「mysql 外部制約 問題」とかでググると比較的新しい日付でも問題点?が出てくるので、使わない方が良いという判断が下されたんだという気がします。

これに関しては要調査の課題としてメモしときたいと思います。

クエリ発行回数の削減(関連データ一括削除)2

先程のエントリーは注意点を書きたかったので書き記した物です。
これこそプロシージャ使えよ!って話ですねw

何も考えずに関連テーブルのdeleteを記述していけば良いだけです。
勿論hoge_idはindexが張られていますよね?
先程のように技術者のスキルによる心配をしなくても良い上に1クエリのみで削除出来ます。
プロシージャの場合は、内部で発行されたクエリがbinログに落ちるようです。
無理にJOINした場合、削除クエリが重くなってレプリ遅延を起こす可能性もありますしプロシージャ化はメリットしかありませんよね?
IF文とか何らかのプログラム制御させるならマスターDBに若干負荷をかけるデメリットがあるかもしれませんが、単純クエリの発行だけですからね。
tcpレイヤーでの処理が減る分プロシージャの方が有利な気もしますね。

この関連データはhoge_idで絞れる内容ですので、DELETEで大量のデータが対象となる事は想定していません。
大量データの削除は後日別エントリーで記述したいと思います。

クエリ発行回数の削減(関連データ一括削除)

MySQLにはJOINで複数レコードをDELETEする機能まであります。

DELETEとFROMの間に削除するテーブル名を指定する事で、両方のテーブルからレコードを1クエリで削除出来ます。

これもクエリ発行回数の削減にはマストと言えるのですが注意点があります。
要はSELECT文と同じなので、全てを1クエリで済まそうとせずに対象レコードに注意して下さい。
親子関係のテーブルを想定します。
親(1)
子1(1)
子2(1)
子3(1)
1:1のテーブルがn件紐付いても1件なので1クエリでやっちゃって下さい。

親(1)
子1(2)
子2(3)
子3(4)
1:nのテーブルがn件紐づくと、2*3*4で24件として展開されます。
孫テーブルがあったりすると、ねずみ算式に増えてしまいます。

1クエリで削除して問題ない単位を考慮できる人じゃないとちょっと怖い事になります。

クエリ発行回数の削減(同一テーブルinsert)

MySQLには標準でバルクインサートという機能があります。
業務系出身の私はMySQLはあまり使いませんでしたので、本格的にソーシャルゲーム開発に携わって初めて知りました。
なんだこのvaluesの羅列は!って感じですねw
高速性を求めるならあって当然の機能と思えるようになりました。

INSERT INTO hoge (hoge_value) VALUES (‘1’),(‘2’),(‘3’);
みたいな感じで同一テーブルに1クエリで複数件登録出来ます。

この機能を利用すれば、フレームワーク側のモデルにinsert予定データをプールする機能を持っていれば、最後に1クエリだけで済みますね。
よくあるソーシャルゲームのシチュエーションで考えると、プレゼントがクエストのクリアやミッション達成で複数件同時に発生する時などに使えますね。
ある時はギルドメンバー全員にプレゼントを送るというシチュエーションが混ざってきても、モデル側でプールする仕組みがあれば全て最後に1クエリで済みます。
さすがにギルドメンバーの人数が多くて1人あたりn件送った結果、例えば10,000件超のプレゼントを一括登録とかになると分割insertも出来る機能も持ちたい所です。
1クエリの実行時間が長いとレプリ遅延を引き起こしますからハードウェアスペックにも左右されますね。
つーか例が大げさすぎてアレですけど、10,000件もプールするとweb(ap)サーバー側がメモリ使いすぎで悲鳴上げるかもしれないですねw

クエリ発行回数の削減(複数テーブルinsert)

getLastInsertIdのエントリーで説明したシチュエーションの続きです。
親・子テーブルを一括で登録するシーンが前提となります。
mysqlのプロシージャを使うと一括で登録出来ます。
勿論レプリケーション構成でも問題ありません。
2014.4.15追記:私のテスト環境だと問題なく動きますしPDO::rollbackも効くのですが、procedureが別トランザクションになる環境もあるようです。
どこの設定の違いなのかは掴めていません。

登録された主テーブルのlast_insert_idは、プログラム側で必要になるという前提で考えています。
プロシージャの場合は、最後にSELECT文を投げることで結果も返せるというのがポイントです。

普通のinsertのテスト関数を用意します。

プロシージャ利用のinsertのテスト関数を用意します。

複数クエリ一括投げinsertのテスト関数を用意します。
これはlast_insert_idを取るためにクエリを2回投げてしまっています。

ローカルPC内のVMとは言え、ネットワークを介した通信誤差の影響を受けるので、ある程度の回数で試します。
1000回くらいループさせてみます。
batch_insert2はlast_idを取得しないデータ作成の1クエリのみです。

当然の結果ですが、通信回数の差が顕著に出ていますね。
1000回ループなので1回当たり約12.5msがプロシージャ化で約6.8msになる感じですね。
勿論insertするサブテーブル数が増えるともっと差が出ます。
php-extensionの項でも書きましたが、数msの積み重ねが処理速度の向上に繋がりますから馬鹿には出来ません。

last_insert_idが必要ない前提で考えた場合、バッチクエリのやり方でフレームワークとして処理の最後に1クエリだけで更新系を済ませるというのもアリかもしれないですね。

【朗報】PDOのgetLastInsertIdメソッドは通信なし

ソーシャルゲーム開発においてクエリの発行回数を削減するのは非常に有用です。
DBサーバーへのネットワークを介する回数自体が変わるのですから。
ネットワークを経由する事の問題点はApacheとnginxの項でも書きました、ハードウェアの物理構成まで関係してきます。
単純にローカルネットワークだったら早いとかの問題でもありませんので、そこら辺はインフラ屋さんが考えるんでしょうね。

ソーシャルゲーム開発は、テーブル設計にしてもあえて正規化しないといった手法が取られます。
なのでユーザー情報に紐づくサブテーブルがたくさんあったりすると思います。
user_table
user_hoge1
user_hoge2
みたいな感じですね。
ユーザー登録時に限らず、サブテーブルを含めて一括でデータを作成するシチュエーションを想定しています。

普通にphpで作成すると、3テーブル作成するなら3回もinsert文を発行する事になります。

ここでPDOのlastInsertIdメソッドが、SELECT LAST_INSERT_ID()を発行してたら4回になるなぁと気になって調べました。

mysqlのmysql_insert_idというAPIを利用しているだけなんですけど、通信が発生するかどうかはAPI側のソースを見ないと分からないですね。

なので手っ取り早くtcpdumpしてみました。

hogeのinsert後にgetLastInsertIdをしていますが、直後にhoge2のinsertが来ていますので通信は発生していないようです。
driver_data->serverしか引数に渡してないから通信してそうだなぁと思ったのですが、これは予想がハズれてラッキーでした。
何らかの理由でSELECT LAST_INSERT_ID()クエリを投げている人はPDOのメソッドを使ったほうが良さげです。

mysqldumpオプション

ソーシャルゲーム開発はレプリケーションしてるのが当たり前ですが、スレーブからのバックアップに関する話です。
ネットには嘘情報が転がってるので、mysqldumpのソースコードを見て整理しました。
ちなみに5.5系(2013年に調べたから5.5.35くらいのソース?)の話なんで、ネットに転がってる嘘情報はバージョン違いな可能性は否定しません。
記事を書くにあたって5.5.37のソースコードをダウンロードして再確認→引用しています。

master-dataオプション

SHOW MASTER STATUSを発行して
master-data=1
CHANGE MASTERコマンドとしてMASTER_LOG_FILEとMASTER_LOG_POSをダンプファイルに出力する
master-data=2
上記CHANGE MASTERコマンドをコメントとしてダンプファイルに出力する

5525行目でmaster-dataオプションが指定されている事により、do_show_master_statusが実行されます。
if (opt_master_data && do_show_master_status(mysql))

下記マーカー部分の4661-4662行目でCHANGE MASTERコマンドをコメント出力するか切り替えてるだけです。

スレーブDBからのバックアップでmaster-dataオプションは不要です。
1.スレーブDBでbinlogが有効でないとエラーになります。
(log-slave-updatesで多段レプリでもしてるならmaster-dataオプションは有効ですが普通はスレーブのbinlogは無効ですよね)

2.dump-slaveオプションとの重ねがけ
ネット上にはスレーブDBからのバックアップ方法として–master-dataと–dump-slaveの両オプションを付けてたりする嘘情報が転がってます。
スレーブDBのbinlogが無効でも、これが1で説明したエラーにならないのはdump-slaveを付けるとmaster-dataオプションは無効になるのでエラーにならないだけです。
941行目くらいからですね。
main→get_options

重ねがけを記述してるサイトは嘘情報として鵜呑みにしない方が良さげという事ですね。

整理すると、master-dataオプションはマスターDBからのバックアップで利用するオプションです。
master-data=1
マスターDBのダンプからスレーブDBを新規作成する際に、CHANGE MASTERコマンドも埋め込む
master-data=2
マスターDBからの日次バックアップ等に利用、binlogから差分リカバリ用にコメントでポジションを埋め込む

dump-slaveオプション

dump-slave=1
CHANGE MASTERコマンドでMASTER_LOG_FILEとMASTER_LOG_POSをダンプファイルに出力する
dump-slave=2
CHANGE MASTERコマンドをコメントでダンプファイルに出力する

このCHANGE MASTERに埋め込まれる値は、
SHOW SLAVE STATUSのRelay_Master_Log_FileとExcec_Master_Log_Pos
do_show_slave_statusの4747行目でSHOW SLAVE STATUSを実行して

結果の10番目(Relay_Master_Log_File)と22番目(Exec_Master_Log_Pos)を埋め込んでます。

つまりSQL THREADで反映済みのマスターDBのbinlog情報がCHANGE MASTERに埋め込まれる

整理すると、
dump-slave=1
CHANGE MASTERを発行するので、マスターDBが存在する前提の用途で利用する事になる
スレーブDBを追加する場合の用途に使える
dump-slave=2
スレーブからの日次バックアップ等に利用、binlogから差分リカバリ用にコメントでポジションを埋め込む
基本的にこっちを使っとけば、うっかりCHANGE MASTERが実行される事もない

いかがだろうか?
ネットには嘘情報がゴロゴロ転がっているのである
信じられるのは己のスキルのみ
嘘を嘘と見抜けない人はうんたらですよ。。。と