#SQLアンチパターン ファントムファイルに立ち向かう

SQLアンチパターン

SQLアンチパターン

SQLアンチパターン

ファントムファイル(幻のファイル)

  • 画像などのバイナリファイルをストレージにおいて、そのパスをDBに格納するのをアンチパターンとして紹介している
  • 本書でも絶対にストレージに置いてはダメだとは書いては無い、常に2つの設計を検討すると書いてある
  • 私はDBよりも安価になっていくS3などのストレージに保存した方がいいと思っていて、その場合にはどのようなテーブル構成がいいのかを記録しておこうと思って書く

まずはファントムファイルとは何かをざっとまとめとく

画像のファイルパスを保存する場合

下記のようなテーブルを作成してファイルパスを保存する

CREATE TABLE Screenshots (
  bug_id BIGINT UNSIGNED NOT NULL,
  image_id BIGINT UNSIGNED NOT NULL,
  screenshot_path VARCHAR(100),
  caption VARCHAR(100),
  PRIMARY KEY (bug_id, image_id),
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id)
);
  • screenshot_path VARCHAR(100)で画像のファイルパスを保存
  • この設計がアンチパターンと紹介されている

DBではなくストレージにファイルを格納する場合のリスク

  • DELETEしても実画像ファイルが一緒に削除されることが保証されていない(「孤児」となったファイルが蓄積されていく恐れ)
  • DBとファイルのトランザクションの問題
    • ファイルの内容を変更するとDBでコミットされる前に、他のクライアントがその変更されたファイルを参照する可能性がある
    • 削除する場合は、ファイルを削除してDBでコミットに失敗した場合にファイルは元に戻らない、あるいは先にコミットしてファイルの削除に失敗したら孤児ファイルになる
  • バックアップが難しい
    • DBのバックアップツールでファイルパスだとその時点のデータをexportすることが保証されない
    • ファイルのバックアップも別途実施する必要がある
    • 2つのバックアップを仮に同時に行ったとしてもトランザクション付きでバックアップツールを実行できる訳でもないので厳密にその時のスナップショットを取るのが難しい
  • RDBにあるアクセス権限制御が使えない(GRANT/REVOKE)
  • ファイルパスはSQLデータ型ではないのでDBで検証できない
    • 外部ファイルにはFKを付けられない
    • カラムをNot Nullにしても実データは本当にそのパスにあるかわからない

本に書いてあるアンチパターンを用いてもよい場合

  • データベースの容量を減らしたい
  • データベースのバックアップを短時間で終了したい
  • バックアップファイルの容量を抑えたい
    • 画像をデータベースに格納しないことで巨大なサイズのデータベースをバックアップするよりも管理がしやすくなる
  • 画像ファイルの加工が容易になる

画像のバイナリデータをBLOBに保存する

下記のようなDDLを用いてバイナリデータを保存することで上述したリスクを軽減できる

CREATE TABLE Screenshots (
  bug_id BIGINT UNSIGNED NOT NULL,
  image_id SERIAL NOT NULL,
  screenshot_image BLOB,
  caption VARCHAR(100),
  PRIMARY KEY (bug_id, image_id),
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id)
);
  • screenshot_image BLOBで画像を保存

ここまでが本に記載してある内容

それでもファイルをストレージサーバに置く理由

ストレージと言ってもクラウドストレージを使う、大体の理由はクラウドストレージの利点を受けたいからですね

  • DBサーバは比較的高価でS3などのファイルストレージは安い、これからも安くなっていく事が見込まれる
  • サーバのファイル容量を気にしなくていい
  • 期限付きURLの発行などがクラウドストレージの機能で可能
  • Webサーバを経由せずに配信が可能になる
  • 認証をかけたい時はWebサーバで受けて、X-Reproxy-URL, X-Accel-Redirectヘッダーを付けてNginx経由で配信させる手段を取れる
    • (クラウドストレージの認証機能も要件にあえば使える)

ファイルパスを保存するときのテーブル設計

前振りが長かったのですが、ここからが本題です。

上記にあげたリスクの中で下記のDBとファイルのトランザクションの問題が一番嫌だなーと思っていて、その対応のためのテーブル設計案です。

  • ファイルの内容を変更するとDBでコミットされる前に、他のクライアントがその変更されたファイルを参照する可能性がある
  • 削除する場合は、ファイルを削除してDBでコミットに失敗した場合にファイルは元に戻らない、あるいは先にコミットしてファイルの削除に失敗したら孤児ファイルになる

例としてGmailの用にメールを送信するアプリで添付ファイルが一つしか付けられないという事にしましょう。問題をシンプルにするために添付ファイルは1対Nではなく1対1にします。ストレージはS3を例にします。

メールのリソースに対してパスを持つテーブル

単純に作ろうとすると下記のようになりますが、これをやってしまうとトランザクションの問題が顕著に影響を受けて困ります。

CREATE TABLE Emails (
  id BIGINT UNSIGNED NOT NULL,
  subject VARCHAR(255),
  body VARCHAR(255),
  to_address VARCHAR(255),
  file_path VARCHAR(255),
  PRIMARY KEY (id)
)
  • ブラウザでメールの削除ボタンを押したとして、削除しようとした時に一緒にS3のファイルも消す必要がありますが、S3のAPIを使って削除もしようとするとレスポンスが遅くなりますし、ファイルを削除してDBでコミットに失敗した場合にファイルは元に戻らないみたいなことが発生する可能性があります。

ファイルはファイル専用のテーブルを作る

CREATE TABLE Emails (
  id BIGINT UNSIGNED NOT NULL,
  subject VARCHAR(255),
  body VARCHAR(255),
  to_address VARCHAR(255),
  cloud_storage_id VARCHAR(255),
  PRIMARY KEY (id)
)
CREATE TABLE CloudStorages (
  id BIGINT UNSIGNED NOT NULL,
  file_path    VARCHAR(255),
  delete_flag SMALLINT(1) NOT NULL,
  PRIMARY KEY (id)
)

各機能のテーブルに直接FilePathを保存しないようにして、削除ボタンをクリックされた場合はEmailsレコードは単純にDELETEして、CloudStorageレコードはdelete_flagを更新するようにし、非同期で別途削除するようにします。

こうすることでファイルを削除してDBでコミットに失敗した場合にファイルは元に戻らないみたいなことが発生しないようになります。

ファイルの内容を変更するとDBでコミットされる前に、他のクライアントがその変更されたファイルを参照する可能性がある

この問題については、ファイルを更新する際にCloudStoragesテーブルにINSERTしてEmailsのcloud_storage_idをINSERTしたIDに変更し、元のCloudStoragesレコードはdelete_flagを更新します。

別解としてCloudStorageテーブルを作成しなくても、Emailsを削除するときに削除されたという削除イベントのテーブルを作成し、そこにfile_pathを持たせることで単純にパスを持つテーブルでも対応しようと思えばできます。

※論理削除フラグは〜って言いたくなったらstatusカラムに変更するかw CloudStorageテーブルを作成した場合でも削除イベントのCloudStorageテーブルを作って下さい。今回の話題とは直接関係ないので割愛します。

ブログを更新する

ずっとブログを書いて無かったのですが、最近幼なじみがブログを始めてコツコツとコンスタントに更新しているんですよ。業界も全然違うので内容は全然異なるのですが、なんか刺激を受けて私もまたブログを更新していきたいなーと思ってます。

せっかくなので、日記的なメモを

テニスはじめました

やっぱり歳を重ねる度に健康不安を感じるんですよ。運動してないよなー運動しないとなーと。そこで、スポーツジムにでも行こうかなーと思ってたんです。スポーツジムって基本ストイックに自分と向き合って頑張れみたいな印象があって続くのかなーって不安があったんです。

そこからなんでテニスになったかと言うと、家の近くに公園があって子供と良く遊びにいくんですけど、そこにテニスコートがあるんですよ。そこで家族みんなでテニスやってるのとか見るたびに家族で共通の趣味/スポーツっていいなぁと数年前から思ってて、テニスの方がいいかーと思って始めました。

テニスは全くやったことなかったんですけど、テニスは敷居が低くて楽しいですね。いつか家族でテニスできる夢見て頑張ります。

Macでもタイル型ウィンドウマネージャを使う

awesome wmを使いたい人生だった

http://awesome.naquadah.org/

一緒に開発してるメンバーでawesomeってウインドウマネージャを使っていて、便利そうだなーとずっと思ってたんですけど、Macには無いよなーと思って諦めてたらMacでも同様のものがありました

MacならAmethyst

インストールはbrew-caskになってて、brew-cask入れてない人は

brew install caskroom/cask/brew-cask

してから下記

brew cask install amethyst

インストール後はOSの再起動が必要でした。

ひとこと

やっほー。これはライフチェンジングやーって感じです。

Amazon RDS for MySQL リードレプリカを使ったテーブル変更

RDBでデータ件数が多いテーブルに対するカラム追加を行うと時間が数時間かかって困るんですけど、RDSだったらリードレプリカを使えば比較的容易にできるってのを最近知りました。

MySQL5.6からはオンラインDDLとかPercona-Toolkitのonline-schema-changeとか別のソリューションもあるのですが、リードレプリカも便利です。

リードレプリカを使ったテーブル変更

DDLオペレーションの実行

カラムを追加したり、インデックスを張ったりするようなテーブルレベルのDDLオペレーションは時間がかかり、マスターデータベースインスタンスのパフォーマンスに影響を与えてしますことがあります。リードレプリカの昇格機能を使えば次のような方法がとれます。

  • 指定されたリードレプリカ上でオペレーションを実行し、それが完了するまで待ちます。
  • リードレプリカの同期がマスターデータベースインスタンスに追いつくまで待ちます。
  • リードレプリカをマスターに昇格します。
  • 新しく昇格したマスターに全てのデータベーストラフィックを向けます。
  • 必要に応じてパフォーマンス向上のために追加のリードレプリカを作成します。
  • 元のマスターとそれに関連づけられている残りのリードレプリカをを終了します。

Amazon Web Services ブログ: 【AWS発表】Amazon RDS for MySQL - リードレプリカのマスター昇格機能を追加!

注意点

この方法を実施する際に気を付けないといけないことがあって、リードレプリカって要は別のDBインスタンスにマスターで実行されているSQLをじゃんじゃん非同期で流すだけなので、対象が不定になる更新系のクエリがあると問題になる。

以下のようなクエリは更新対象が不定になるので、 UNIQUE制約違反を引き起こしレプリケーションの停止を招く可能性があります。 UNIQUE制約違反がその場で発生せず、 後日たまたま重複するINSERTを行ったらレプリケーションが止まったり、 レプリケーションは動いているのに実はデータが一致していないということになりうるので注意が必要です。

  • UPDATE table SET col1 = 'foo' LIMIT 1;
    • LIMITで抽出される行がマスタとスレーブとで同一とは限りません。 LIMITが必要な場合は、UNIQUEなカラムでORDER BYを使って同順となるようにします。
  • REPLACE ... SELECTもしくはINSERT ... SELECT
    • これらのSQLで、 更新対象のテーブルのプライマリキーがAUTO_INCREMENTで、かつ、 SELECT文にORDER BYを使っていない場合に、 REPLACEもしくはINSERTされる順が不定となるため、AUTO_INCREMENTで発番されるプライマリキーがマスタとスレーブとで異なってしまいます。

現場指向のレプリケーション詳説

  • レプリケーションエラーが発生したかはAWSの管理コンソールで確認できますが、データの不整合は検知できない。
  • データ不整合が起きていることを気付かずにマスターに昇格するなどしてマスターと異なる状態になると怖いですね。

ひとこと

RDSのリードレプリカ便利ですね。リードレプリカは読み込み負荷を下げるためのモノだという意識が強かったので、リードレプリカを使ってテーブル変更したりという発想がなかったけど、これはいい使い方ですね。多少気を付けないといけないことがあるにしてもこれは便利。今後活用していきたい。

ISUCON4 予選に参加した

予選に参加して惨敗した

9/27の一日目に参加して最終スコアはworkload 8で33,000ぐらい。

惨敗してかなり悔しい。根拠もない自信で予選くらいは思いつく実装を全部できればなんとかなるやろと甘く見てた。。 本戦出場の最終的な結果は10/6辺りに発表されるらしいけど、まー無理ですね。

予選終わったときの敗北感とか疲労が半端無かった。でも、最高に楽しかった。来年リベンジする。 この企画マジで素晴らしい。F1もそうだけどコンテストによって人類は進化するのでよい企画だと思う。 運営のみなさんには本当にありがとうと言いたい。

メンバーと選択した言語

参加メンバーは @bluerabbit777jp, @najeira, @Jxck_ で言語はGoにした。

二年前のISUCONで@najeiraと出ようって話をしてたけど、その年は都合が悪くなって出られず、やっと今年出れた。

出ようって事は2年も前に決まってたけど、実は言語が決まってなかった。 @najeiraはGo, Pythonが得意で私はJava, C#, ruby, nodejsが普段使いの言語だったので実はミスマッチしてた。。 Goは素晴らしい言語だーと思ってた事もあってISUCON駆動でGo覚えられる!と前向きに考えてGoにした。

Goを使えて二人とも知っていて、一緒に参加して欲しいなーと思ったのは @Jxck_ だった。 しかし、GoCartというイベントとぶつかっていて可能ならリモートで参加してもらうという事にした。

なので、予選は実質ほぼ @bluerabbit777jp, @najeiraの二人で戦った。 本線はフルで @Jxck_ も参加する予定だったのに出番がなくなってしまい申し訳ない。

事前にやっておいたこと

初のISUCONだったので@najeiraとISUCON夏期講習を事前にした。 そこで、下記はやらないといけないなと把握した。

  • 不変なデータは全部インメモリに入れる
  • 可能な限りstatic filesに書き出してnginxで静的ファイルとしてできるだけ捌く
  • 初期データ投入用のshellがあり、そこでDBのスキーマ変更だけでなく、事前にできることをする
  • できればMySQL捨ててRedisに載せ替える
  • MySQLを使うなら検索は検索結果テーブルを作成してSELECTはシンプルにしてINSERTを多くする

GoでRedisを使ったことがなかったので事前にライブラリを調べるなどした。

下記も事前に用意した。

振り返ってみるとこれだけじゃ全然足りなくて

  • nginxやmysqlなどの設定ファイル
  • ack, tmux, emacs, shellの各種設定ファイル
  • golang/nginxのプロファイリングのためのツール

なども事前に用意しておくべきだった。 他のチームは秘伝のタレを用意しててchef流すだけにしてた、とか賢い。

予選当日

仕様把握と設計

最初にgithubリポジトリにソースをpushして@najeiraに各ハンドラ毎のSQLを全部Issueに書いてもらって 仕様を把握できるようにしてもらってる間に私はMySQLの初期データ量/設定値/テーブル構成をIssueにメモしていった。 予習でベンチマークツールでどのテーブルにどれだけINSERTするのか、INSERT/UPDATEが無いテーブルであれば オンメモリに載せられると思っていたので、ベンチマークツールを実行する前後のデータを記録するなどした。

記録はこんな感じ

f:id:bluerabbit:20140929232700p:plain

f:id:bluerabbit:20140929231117p:plain

f:id:bluerabbit:20140929231125p:plain

f:id:bluerabbit:20140929231138p:plain

11:00頃にはだいたい初期の準備と設計は終わって初期実装は下記のようにしようと決めた。

  • ロック情報は別テーブルに保持
  • RDBでカウントしたら負けなのはわかりきってたので、カウントはRedisのINCRを使う
  • /mypageの成功直近2件の情報はidでソートして2件取得する
    • この時点では最終的にはMySQL捨てるかも知れないしとりあえずこれでいいやと思ってた
  • init.shで成功レコードをテーブルにINSERTしておく
  • init.shでRedisに過去login_logの状態を復元しておく

f:id:bluerabbit:20140929231306p:plain

f:id:bluerabbit:20140929231645p:plain

f:id:bluerabbit:20140929231656p:plain

/login

f:id:bluerabbit:20140929231849p:plain

f:id:bluerabbit:20140929231947p:plain

実装

担当を分けて私はアプリケーションのコードを修正して、@najeiraにnginxで静的ファイルを捌くように設定変更などをしてもらいつつinit.shでRedisの初期化を行ってもらった。

ココまでの第一実装が終わったのがコミットログを見ると14:20頃。ココからまだ残っている改善点を対応していけばいいかなーと思っていたが、思うようにスコアが伸びない。ベンチマークを実行するとfailしていて全然スコアが出ない。

静的ファイルをnginxで配信するようにしたが、直接URL叩くとcssは取得できるのにcssが効かないことがわかってアタフタし StackOverflow先生に助けられてinclude /etc/nginx/mime.types;だと気付いてやっとスコアが出たのが約3万点。

スコアやっと出たと思ったが、ポータルにスコアが記録されない。運営に確認すると/reportがバグっていることに気付くがバグが中々見つからず、次の改善に着手できない。バグをつぶしたのが17:00過ぎでもう他の対応をする時間がなくなり、nginxやmysqlの設定を変更してworkload数がいくつくらいがいいのかを何度も実行している内に時間になって終了した。無念。

結局、最初の一次実装までしか改善できなかった。最初にusersは不変なテーブルだから全部オンメモリに置いとくとか、全部Redisにのせようとか、ベンチマーク実行時のログを元にベンチマークの癖とどこを改善しないといけないのか見ようとか、思ってたのも時間も集中力もなくなってしまい、やりたかった事が全然できずで終わってしまった。

競技終了後

他にGoを選択した人のブログで知ったがGOGC、MARTINI_ENVの設定があると知る。 実際にGOGC=off MARTINI_ENV=production付きでベンチマークを実行したら41000を超えた。MARTINI_ENVを設定しない場合は開発モードになって毎回htmlのテンプレートファイルをパースしに行ってしまうようだ。GOGCでoffすると早くなるとかunicornrspecGC止めるテクニックぽくて面白い。

競技終了後も各参加者のブログでどんな改善をしたとか、どんなツールを使ったなどを知れるのも面白い。参加すると競技中だけでなく競技後も楽しめるので一度で二度美味しい感じですね。

やっぱり二人より三人の方が時間にも精神的にも余裕が出ていいので、来年は三人で出てリベンジしたい。

Google App Engine for Goでデプロイしてみる

SDK Download

https://developers.google.com/appengine/downloads#Google_App_Engine_SDK_for_Go

path設定

export PATH=/path/to/go_appengine:$PATH

サンプルアプリケーションをダウンロード

https://github.com/GoogleCloudPlatform/appengine-guestbook-go/archive/part1-helloworld.zip

試しに動かしてみる

downloadしたファイルがある場所でgoapp serve

%goapp serve
INFO     2014-08-09 04:42:25,059 devappserver2.py:725] Skipping SDK update check.
INFO     2014-08-09 04:42:25,071 api_server.py:171] Starting API server at: http://localhost:52335
INFO     2014-08-09 04:42:25,074 dispatcher.py:183] Starting module "default" running at: http://localhost:8080
INFO     2014-08-09 04:42:25,077 admin_server.py:117] Starting admin server at: http://localhost:8000

http://localhost:8080にアクセスするとHello, world!と表示される

Google App Engineにdeployする

Google App EngineのAPP-ID作成

https://appengine.google.com/start/createapp?

Deploy

app.yamlapplication:の値を作成したAPP-IDを指定する

appcfg.py --oauth2 update .

http://{APP-ID}.appspot.com/にデプロイされる

golangのプログラムをbuildしてwindowsのexeを作る

golnagをセットアップする

今回はgvmを使った。OSはMac OS X

https://github.com/moovweb/gvm

gvmをインストール

% bash < <(curl -s -S -L https://raw.githubusercontent.com/moovweb/gvm/master/binscripts/gvm-installer)

go1.3をインストール

% gvm install go1.3

windowsもビルドできるようにする

% gvm cross windows 386

コード書く

cat hello.go

package main

import "fmt"

func main() {
    fmt.Println("Hello World")
}

フォーマットする

% gofmt -w

実行してみる

%go run hello.go
Hello World

windows向けにビルドする

%GOOS=windows GOARCH=386 go build -o hello.exe hello.go

hello.exeができました