AWSでRDS使いつつ全文検索もしたい！（CDP新提案）
本日はCDP Advent Calendar 2013 on Zusaarの7日目のエントリを書きます
毎年AWSは相も変わらず、新機能ラッシュですね。今年は個人的にRDS関連の新機能が目立った気がします。Read ReplicaのMaster昇格やリージョン間スナップショットコピー、PostgreSQL対応など盛りだくさんでした。
今日は、RDSを使ってCDPの新提案してみます。
ここからは、CDPのテンプレートに則って書いて行きます。
CDPの内容
解決したい課題
「世界でもっとも普及している、オープン ソース データベース」であるMySQLは、本来様々なストレージ・エンジンを使用でき、様々な検索特性に変更できる。しかし、クラウド環境でサービスとして提供している物では、ほとんどの場合MyISAMはInnnoDBしか使用できない。
好きなストレージエンジンを使用する場合は、自前でMySQLの運用など行わなければいけない。
クラウドでの解決/パターンの説明
MySQLでは、標準機能でMaster-Slaveのレプリケーション構成を構築できる。Masterにクラウドで提供しているDBサービスを使用し、バックアップ等の運用の手間を軽減し、Slaveを自前で構築し、自由にストレージエンジンを構成する。
構造
利点
- 好きなストレージエンジンで検索できるので、全文検索(mroonga)等を高速に行える。
- バックアップ等の運用管理の手間が軽減される。
注意点
その他
- 未検証ではあるが、DWH用ストレージエンジンを使用すれば集計処理を高速に行うことも可能になる。
- Attunity Replicate等のソフトウェアを使用すれば、同様の構成をRDS－>Redshiftで可能になる。
まとめ
実は、JAWS-UG 千葉團の第１回でやったLTネタ（みんな大好き“全文検索 on AWS”を試してみました！）をまとめた物です。
名付けて、"Complex Searcher パターン"！
これは、id:understeer さんが、RDS: MySQL5.6 のbinlogアクセス権限 - aws memo でRDSにMySQL 5.6が使えるようになったら、REPLICATION SLAVE権限が使えるようになってたと書いていたの覚えていたのが始まりでした。その後、ElasticSearchとか検索エンジンが盛り上がってきましたが、カジュアルに使うならmroongaだろ！と思ったので、このパターンを思いつきました。
SendGrid Nightに行ってきた
@nakansuke さんから強烈な「ブログ書けプレッシャー」を受けたので、SendGrid Nightの参加レポートを書きます。
SendGrid？
説明不要かもしれませんが、一言で言うと「クラウドメール送信サービス」です。何でも「クラウド」と呼ぶのは嫌いなんですが、SendGridはスケーラブルでありAPI経由で全ての操作が可能であるので本当の「クラウド」です。
SebdGrid Nightのセッション
SendGrid Nightは、SendGridのKen Apple氏が来日されるのに合わせて、Engine Yardさんの事務所で勉強会されました。
本編は「SebdGrid入門」ということで、構造計画研究所の中井さんとSendGridのKen AppleさんがSendGridの紹介をしてくれました。
発表の資料は、これでした。
- SendGridの日本語に関するサポート全般や日本円、請求書払いを構造計画研究所さんがやってくれる。
- SebdGridは、メール送信でややこしい処理(ISP等のSpam判定解除とかドメイン認証)を全てやってくれる。
- 以下のTipsでメール送信を効率的に行うことができる。
- 1つのSMTPコネクションで複数のメッセージを送信できる
- SendGridに同時に複数接続をしてメール送信送信できる
- 1接続で1000人の宛先まで送信できる
- RFC違反のメールアドレスへは送信できないけど、構造計画研究所さんと相談中。
- etc
といった感じです。
その後にLTがパソナテック 大久保さん、構造計画研究所 佐藤さんやフレクト 若葉さん、Wantedly 川崎さんと続きました。
詳しい内容は、中井さん(id:nakansuke)が書いているので割愛します。
懇親会！
勉強会の後の懇親会は、構造計画研究所さん、SendGridさんのオゴリでした！太っ腹です。
しかも、SendGridのノベルティのじゃんけん大会も開催しました。
SendGrid Tシャツやサングラス、ワンショルダーのバッグまでありました。
で、サングラスとワンショルダーのバッグをゲットしました。
まとめ
国産サービスだと、価格が高い分簡易的なWeb画面が作成できたりしますが、SendGridは強みに絞ってサービスをしている気がします。
必ずしもどちらかが良いとは言えませんが、Webアプリを作っている場合は、豊富な機能は必要ないのでSendGridが良い気がします。
自分が担当しているシステムは現在国産サービスを使用していますが、RFC違反のメールアドレスへ送信できる様になったら、SendGridへの移行を提案しようかなと思いました。
なんにせよ、SendGridさんはノベルティをくれるので、SendGridをみんな使いましょう！
よし、これでプレッシャーから解放される！
JAWS FESTA Kansai 2013 で一番長いセッションをやってきた
JAWS Festa Kansa 2013？
先日の9月28日にJAWS FESTA Kansai 2013に運営として参加してきました。
JAWS(日本AWSユーザーグループ)の全国規模のイベントを開催しました。今までJAWSのイベントは、いくつも開催してきたのですが本当の意味でのユーザーグループ主催のイベントはこれが初めてとなります。これまでは、良い意味でも悪い意味でもAWSの中の人の影響が大きい所があったのですが、ユーザーグループ自身のイベントを開こうとなって、開催しました。(ユーザーグループにはADSJの中の人が多数いらっしゃるのですが、運営主体はADSJ外の方が多数でした)
何を運営した？
私は「設計・移行ワークショップ（仮想RFPから提案書の作成）」という、仮想RFPを元に、参加者がグループで提案書を作ろうというセッションをやっていました。これは、JAWS横浜支部で2回実施した内容を全国イベントでもやってみよう、と言う事でやってみました。
このワークショップは、@maroon1st、@satotech さん、@ijin さん、@hashiva さん、あとリーダー(@iara)にも参加者のフォローをして頂きました。
どんな内容？
今回は、ECサイトに「WB〇」からの取材があり、1か月後に放映されるんだけど、どうやったら対応できるの？みんなで提案して、という仮想RFPをお題にしました。
内容はこんな感じです。
今回は、参加者が合計9名だったので、3人3チームとして提案書を策して頂きました。
提案書をみんな作成！
RFPの説明をして、参加者の方々に提案書を作成して頂きました。
提案書の内容は、テンプレートを提供していて、このテンプレートの内容に沿って作成頂きました。本来の提案書からするとまったく項目が足らないのですが、時間が限られているため、この内容でも多すぎるくらいです。
RFPの説明の時に、「実際に構築は行わないので、尖がった提案をして下さい！」と訴えたので、期待しながら皆さんの提案書作りを見回っていました。
横浜支部でやっている時より1チームの人数が少ないからなのか、全参加者が活発にディスカッションしている事が印象的でした。AWS未経験の方をベテランの方がうまくサポートしていたりしていました。
各チームのチームビルディングが良かったのかなあ、と思います。これは、「結果的に」なので、運営側はもっとファシリテーションをちゃんとやるべきでした。反省。
また、途中でRFPの最初の内容から要件追加をして見ました。内容は、ECサイトだから実は入会時とか購入時とかにメールをしているので、要件に追加をやりました。実は、やってみたら面白いんじゃないかと言ってその場で決めました。
各チームはどんな提案書を作成したの？
提案書の作成時間は2時間程度だったので、はっきり言って時間が足りません。そのため、最低限の
発表順は、速く提出したチームが最後とした結果、Cチーム→Bチーム→Aチームとなりました。
Cチームの発表
先ずはCチームです。こんな提案をして頂きました。
移行や監視をきっちり考えているところが、高ポイントですね！
Bチームの発表
続いてBチームの発表です。
移行時のアプリケーション変更の凍結まで言及して頂いてます。
Aチームの発表
最後はAチームの発表です。
AWS設計・移行のテンプレート - Google Drive
DBの負荷対策にDynamoDBを入れてきた意欲的な提案です。
「尖がった提案をして下さい！」と言ったので、それに応えて頂きました。「移行期間が短いけど大丈夫？」と聞いたら「我々に任せて頂ければ大丈夫です！」(笑)と言っていたので、まあ大丈夫でしょう！
各チームの発表に対して、お客に扮した運営者が質問をしていきます。
一番印象的な質問は@hashiva さんの
「AutoScalingを使うって本当に大丈夫ですか？」
でした。全チームがWebサーバをAutoScaling構成にしていたのですが、全チームに必ず聞いていました。
AutoScaling構成で運用を成り立たせるのは、難易度が高いんですよね～
結果は？
各チームのシステム構成、プレゼンテーション、チームワークに対して様々な項目に点数をつけて、採点を行いました。
チームワークに関しては、各チーム文句なしで差が付きませんでした。
システム構成はAチームとCチームがリードし、プレゼンテーションはBチームがリードしていました。
注目の優勝は
Cチーム！
でした。
運営側の回答例は？
こんな感じで回答例を作成しました。
これは、参加者と違って時間制限無しで作成したので、チートです。
ポイントは、以下の通りです。
・テレビを見てアクセスする人だから、ほとんどが参照処理で更新処理(商品の購入とか)が比較的少ないはず。
・多数のアクセスを裁くため、リクエストを別のサービス(CDNとかDNS)に分散させる。
・準備期間が短いので、簡略化のためAutoScalingはあえて使用しない。
・AWS以外の外部サービスもどんどん使っていく。
・DBアクセスの負荷低減のため、セッションストレージをElastiCacheのRedis Engineを使用し、RDBの負荷を下げる。
・RDSのMulti-AZ、RDSのRead Replicaの冗長化、セッションストレージのElastiCache Redis Engineの冗長化により、SPOFを排除。
・NATも冗長化する。
こんなところでしょうか。
このレベルの内容は、とても2時間では不可能ですね、、、
ちなみに、回答例についても質問を受け付けたら、参加者の質問より運営者のキッツい質問が飛びまくりました。@ijinさん@satotechさんとかガチのマサカリ怖い！
運営してみた感想/反省
このワークショップは、JAWS横浜で2回開催済みなので、大体の準備や当日の動きを把握していたので、準備不足でしたがなんとか運営は回りました。
しかし、このワークショップは4時間30分かかっているので、午後のセッションは他に全く参加できません。にも拘らず参加頂いた方々には感謝しています。ワークショップ後に「実際にAWSの仕事をやることになったので、このワークショップに参加しに来た」という方までいらっしゃいました。参加者の方も勉強になったと思いますが、運営側もとっても勉強になりました。
懇親会で、ワークショップの内容でLTをやったら、他の支部でもやりたいという声を頂きましたので、提供できるネタは提供致します。全国の支部で開催して頂けると良いですね。
最初、京セラドームでイベントをやろう！となった時は、本当に参加者が来るのかと不安になっていましたが、申込みで850人程度、実際の参加者で600人以上と大盛況でした。JAWSの関西メンバーの活躍のおかげですね。
最後に
翌日に大阪の新世界で串揚げをJAWSメンバーと食べたけど、これだけは忘れません。
串揚げは サソリ より コオロギ が旨い！
「AWS Game Day Tokyo 2013」に行ってきたら、AWS界隈でMost EVILになってました。
先日「AWS Game Day Tokyo 2013」に行ってきました。
「AWS Game Day Tokyo 2013」とは？
アメリカ大統領選挙のオバマ陣営「Obama for America」が堅牢なシステムを作り上げるために、「Game Day」という手法を用いました。「Game Day」とは、敵味方に分かれてシステムを攻撃、防御/修復を行い、脆弱性の発見とその修復方法を考えるものです。
これを、日本で初めて行おうというのが「AWS Game Day Tokyo 2013」です。世界でも、「Obama for America」、南アメリカに続きまだ3回目で、これまでで最大規模で開催されました。
Game Start !
朝10時に、会場に着くとAWS界隈の濃いメンバーが集まっており、効果的な攻撃方法を話し合ってました。
- Reserved Instanceを大量購入する破産攻撃
- 使用するアカウントでEnterprise Support(最低$15,000！)に入ってしまう
- etc
等々、効果的な作戦が上がってましたよ。
皆が集まるとルール説明がありました。
- 全体参加者で3チームに分かれ、別の部屋で行う。
- チーム毎に。さらに2、3人でチームに分かれて、別の部屋のチームと対戦する。
- まずシステムを構築する。対戦相手のシステムを攻撃しあう。攻撃された自分のシステムを修復する。
- 単に消すような攻撃はつまらない。分からないように壊して下さい。
- お金がかかる攻撃はNG！
ルール説明の後、チーム分けがされました。チームが発表されると、参加者に国家公務員の所属とかセキュリティ企業の人がおり、ザワザワしました。
自分のチームは、AWSに慣れている方は少なめで、セキュリティ関連の方の割合が多かったです。組んだ方は、#自宅ラック勉強会の@seafayさんでした。
Prepare to Game !
最初に守るシステムを構築します。簡単に説明するとQueueに入っている複数URLの画像を取り出して、画像を結合し、S3へ出力するというシステムです。実際に処理をするサーバはAuto Scaling構成になっています。システムの内容はnginksさんのブログが詳しいです。
手順書に従ってシステムを構築するのですが、対象のリージョンが決まってたり、既定の時間が足らなくなってしまい、最終的には用意されていたCloudFormationで一発でStackを構築してしまいました。今思うと、自分で構築した方がカスタマイズできて、より面白かったかなと思います。
周りを見渡すと、自分のシステムを守るためにnmapの実施確認をされている方もいましたが、AWSでは事前申請が必要なのでNGとなり、ションボリされていました。
自分のチームでは、時間がなかったため防御のためにAuto Scaling Groupにに仕込みを入れておきました。
Let's Attack !
昼食後に、「いつ攻撃するの？」(玉川さん)「今でしょ！」(Miles Ward)の掛け声で、攻撃を開始しました。
攻撃の内容、基本的に以下の方針を考えていました。意地が悪い内容ですね！
- なるべく、AWS Management Consoleからは見えない部分を叩く。
- 直したつもりになっても戻らない。
- 一度直しても、いつの間にか元に戻る。
その結果、以下の攻撃を行いました。
- Auto Scaling GroupをScheduled Actionで毎分max size、min sizeを0台に上書きする！
- Auto ScalingのLaunch Configurationを別の物に変更して、マシンが上がっても動作しないようにする。
- Launch Configurationの元の設定を削除して、元の名前でダミーの設定を用意しておく。
- S3の権限を全て消しておく。
- S3のBucket Policyでアクセスを拒否しておく。
- SQSをダミーで大量に作成しておく。
Auto Scaling GroupをScheduled Actionは、実は防御の仕込でも使ってました。
攻撃中に他のチームは、課題のアプリケーションの脆弱性を付いてSQSにコマンドを投げ込むコマンドインジェクション(名付けて"SQS Injection"？)等の高度な攻撃をされていました。一番驚いたのが、相手のIAMのPower Userしかないのに、KeyPair無しでrootの奪取をしたチームが有りました。
SQS経由でコマンドインジェクションでどうにかしたんでしょうか？私の拙いスキルでは、あまり想像つきません。
Repair
攻撃の次は、修復を行います。このシステムで使用している以下の主要サービスを見ていきました。
- Auto Scaling Group
- S3
- SQS
- EC2
最初はAuto Scaling Groupです。Auto Scaling Groupを確認しましたが、台数やAMIは正常でした。また、Launch ConfigurationもIAM Roleが設定されているので正常と判断しました(Power UserではIAM Roleが設定できないのです)。また、Scheduled Actionも設定されていませんでした。
次に、S3ですが、ユーザのパーミッションもBucket Policyを問題ありませんでした。
そして、SQS。キューのパーミッションはDefault Policyの設定で、Queueもからの状態でした。
最後にEC2です。Auto Scaling構成なので、とりあえずインスタンスをTerminateして、勝手にインスタンスが上がってくるのを待ちました。その後、SSHでログインが正常にできました。
どこに攻撃がされているのか分からないまま、Queueに画像のURLを投入すると、結果の画像がS3へ正常に出力されました。
何所に攻撃されたの？？？
攻撃された箇所が分からず、動作に関係が無いSecurity Groupの設定や別リージョンを確認しましたが何もわからずに、時間が過ぎていきタイムアップとなりました。
Discussion
最後は、対戦相手と攻撃の修復の内容を話し合います。相手に何所を攻撃したのか聞くと、SQS経由でコマンドを投入し、サービスをいじったりや巨大ファイルを作成したりと大胆な攻撃をしたそうです。しかし、こちらの修復方法がEC2をTerminateすると勝手にインスタンスが立ち上がるという事なので、きょっとショックを受けていたような。
そして、こちらの攻撃の説明をした後に修復方法を聞くと、結局CloudFormationで修復したそうです。どんなに頑張っても、最強のCloudFormationで一発で直りますよね。正しい修復方です。
他のチームの攻撃を聞くと、SQSに巨大なファイルを突っ込んだ上でタイムアウトを短くし、次に投入するQueueを消してしまうという凄い攻撃や、出力先S3のLifecycle Ruleを変更して1日後にGlacierへ移動してしまうというお洒落な攻撃がありました。
Evaluation
Miles氏からの講評あり、なんと我チームが「Most EVIL break!賞」を受賞しました。正直、SQS関連への攻撃で皆高度な攻撃を仕掛けていたので無理かなと思っていたのですが、Management Consoleから見えない部分を突いたのが評価されたみたいです。Milesには、これで「Most EVLI！」覚えてもらったようです。
あと、最も優れた修復を行った「Most AWESOME fix!賞」は@ijinさんが受賞されました。
Miles氏が言っていたことで最も印象的だったのが、「RepairよりRebuildの方が簡単にできるようになる」、「あるべき状態の振る舞いを定義を保存しておき、現状と比較し、自己治癒し続けていくというのが最高のシステム」という２つです。
最近では、Chef、PuppetとServerSpecでServer Provisioningが普及し始めていますが、今度はCloudFormationやOpsWorksでSystem Provisioningが進んでいくと言う事なのでしょう。System as Code(Not Application)とでもいうのでしょうか？
最後に
システムを攻撃すると言う事はあまり経験できることではないので、今回のGame Dayは非常に勉強になりました。
今後の改善点かなと思うところは、
- 攻撃、修復のターンを複数回のが良い。
- VPC環境のシステムにする(ネットワーク系の設定ができるので)。
- 修復する時の手段を制限する(CloudFormationは強力すぎるので、作り直せば何でも直せてしまう)。
- 攻撃の前に防御策を行う時間をもう少し取った方が良い。
次のre:InventでもGame Dayを行うようなので、東京リージョン代表として是非参加したいと思います。問題は渡航費をどうやって捻出するかだなあ。
Milesを始め、AWSのSAの皆様有難うございました。
あと同じチームになった@seafayさん、有難うございました。
温泉ハッカソンでCloudFormationの勉強してみました
さてさて、今日はAWSのCloudFormationを勉強してみました。
CloudFormation？
AWSのサイトには以下の様に書いています。
AWS CloudFormation は、関連する AWS リソースの集約を整った予測可能な方法でプロビジョニングおよび更新し、開発者やシステム管理者が容易にそれらを作成・管理できるようにします。
これだけ書かれても、あまり良く分かりませんね。
簡単に言うと、事前にjson形式で各コンポーネントの構成を定義すると、一気にその構成を作ってくれる機能です。
何勉強したの？
CloudFormationは、サンプルテンプレートがたくさんあるし、@suz_labさんが最近すごい勢いで書いているので、参考にするものが多いです。でも今回は、勉強のために公式ドキュメントだけを見てテンプレートを作ってみました。
公式ドキュメント：Welcome - AWS CloudFormation
＊どんな内容？
CloudFormation初心者がCloudFormationを勉強するので、構成は簡単にしました。
「EIP付きのEC2インスタンスを既存VPC内に作成しよう！」です。
なお、前提条件として、VPC、セキュリティグループ、IAM Role等は事前に作成済みと言う事にしました。
で、作成したテンプレートが以下の内容です。
{ "AWSTemplateFormatVersion" : "2010-09-09", "Description" : "Web Server Stack for maroon1st-Formation", "Parameters" : { "AMI" : { "Default": "ami-", "Description": "AMI ID", "Type": "String" }, "WebServerAZ" : { "Default": "AAZ", "Description": "AZ for Web Server ", "Type": "String", "AllowedValues" : [ "AAZ", "BAZ", "CAZ" ] }, "IAMRole" : { "Default": "", "Description": "IAM Role", "Type": "String" }, "InstanceType" : { "Default": "t1.micro", "Description": "Instance Type For Launching EC2", "Type": "String", "AllowedValues" : [ "t1.micro", "m1.small", "m1.medium", "m1.large", "m1.xlarge", "m2.xlarge", "m2.2xlarge", "m2.4xlarge", "c1.medium", "c1.xlarge", "cc1.4xlarge", "cc2.8xlarge", "cg1.4xlarge" ] }, "KeyPair" : { "Default": "", "Description": "Key Pair", "Type": "String" }, "SecurityGroup" : { "Default": "sg-", "Description": "This is SecurityGroup ID", "Type": "CommaDelimitedList" }, "InstanceName" : { "Default": "Maroon1st Web Server", "Description": "Instance Name for EC2 Tag", "Type": "String" }, "UserData" : { "Default": "", "Description": "UserData as Text", "Type": "String" } }, "Mappings" : { "SubnetMapPublic" : { "AAZ" : { "SubnetID" : "subnet-0f0f2066"}, "BAZ" : { "SubnetID" : "subnet-020f206b"}, "CAZ" : { "SubnetID" : "subnet-190f2070"} }, "TokyoRegionAZ" : { "AAZ" : { "AZName" : "ap-northeast-1a"}, "BAZ" : { "AZName" : "ap-northeast-1b"}, "CAZ" : { "AZName" : "ap-northeast-1c"} } }, "Resources" : { "WebServers" : { "Type" : "AWS::EC2::Instance", "Properties" : { "AvailabilityZone" : { "Fn::FindInMap" : [ "TokyoRegionAZ" , { "Ref" : "WebServerAZ"}, "AZName" ] }, "BlockDeviceMappings" : [], "DisableApiTermination" : true, "EbsOptimized" : false, "IamInstanceProfile" : { "Ref" : "IAMRole" }, "ImageId" : { "Ref" : "AMI" }, "InstanceType" : { "Ref" : "InstanceType" }, "KeyName" : { "Ref" : "KeyPair" }, "Monitoring" : false, "SecurityGroupIds" : { "Ref" : "SecurityGroup" }, "SourceDestCheck" : false, "SubnetId" : { "Fn::FindInMap" : [ "SubnetMapPublic", { "Ref" : "WebServerAZ" }, "SubnetID" ] }, "Tags" : [ { "Key" : "Name", "Value": { "Ref" : "InstanceName" } }, { "Key" : "Server Type", "Value" : "Public Web Server" } ], "Tenancy" : "default", "UserData" : {"Fn::Base64" : { "Ref" : "UserData" } } } }, "WebServerIP" : { "Type" : "AWS::EC2::EIP", "Properties" : { "InstanceId" : { "Ref" : "WebServers" }, "Domain" : "vpc" } } }, "Outputs" : { "InstanceID" : { "Description" : "ID for Launched Instance", "Value" : { "Ref" : "WebServers" } }, "PublicIP" : { "Description" : "Public IP for Launched Instance", "Value" : { "Ref" : "WebServerIP" } }, "AvailabilityZone" : { "Description" : "Launched AZ", "Value" : {"Fn::GetAtt" : [ "WebServers", "AvailabilityZone"]} }, "PrivateIPAddress" : { "Description" : "Private IP Address Launched Instance", "Value" : {"Fn::GetAtt" : [ "WebServers", "PrivateIp"]} } } }
でも、これってサンプルテンプレートに似てるのありました。orz
↓の名前でありました。
VPC_EC2_Instance_with_EIP_and_Security_Group.template
でもイイんです(川平栄治の感じで)
勉強だから！
作ってみて大変だった事
CloudFormationで一番は異変なのは、CloudFormationのファンクションを使う事だと分かりました。
"Fn::FindInMap"の中で、Fn:Joinの稀有t号した文字列は扱えなかったりするので嵌りました。
まとめると、、、
CloudFormationは、
まあ、RightScaleだとServer TemplateとかMacroがあるので同等のことができるんですが、CLOUDFORMATION*作ってみて大変だった事
CloudFormationはAWS謹製なので、すぐ新規機能がる使えるのが良いですね。
次は、Auto Scaling Group でEC2インスタンス出来るまで頑張るぞ！
qpstudyに参加してきた
先日qpstudyに参加してきました。
「DevOpsをぶち壊せ(仮) ～DevOps言うな(仮)～」
http://atnd.org/event/201301qpstudy
元々の「DevOps」のイメージ
DevOpsというのは、インフラエンジニアもコードも書こうよ！っていうムーブメントというイメージがありました。
具体的には、最近流行のChef/Puppetとか、Run book Automationを進めてアジャイルなインフラを進めるためにコードを書いて行こうという考え方だと考えていました。
まあ、ツールとかプロセスではないよなあ、とは思っていました。余り強いイメージは無かったですね。
ディスカッションを聞いてみて
最初はmizzyさんのプレゼンでした。
http://www.slideshare.net/mizzy/dev-ops-qpstudy
「DevとOpsの関係性に関するムーブメント」という言葉がDevOpsを端的に表しているだと納得しました。アジャイルインフラとかはDevOpsの結果でしかなくて、簡単にいうと「DevとOpsで対立しないで仲良くやろうよ！」なのかなという印象でした。
しかし、その後のディスカッションは途中から１人がDevとOpsの両方をやる必要があるか？という議論になっていきました。mizzyさんは関係性のカルチャーについて論じていたのに、ほかのパネリストは方法論やプロセスの話をしていたので、レイヤーが違っていて噛み合っていなかった様に思えました。
また、しょっさんが違う視点から掻き回してくれていましたが、顧客やマネージャの立場からは関係無くて効率的に進めば良いというのは、ある意味、的を射ていると思いましたね。
SQLの分析関数はちゃんと覚えよう！
本日はJPOUG Advent Calendar 2012のエントリを書きます。
他の日は錚々たる方々が担当しているので、今日は大したことがない内容です。
、、、とハードルを下げておく。
実は、分析関数を3年位前まで存在を知らなかったんですね。SQLの入門書に記載されていなかったり、分析関数/ウィンドウ関数という名称で何だか難しいイメージがあり、全然勉強をしてなかった、、、大体最初に触ったDB(Oracle 7.3.4)には未だ搭載されていなかったし。
そんな訳で、存在を知る3年以上前までは、とんでもなく大変なSQLを書いたこともありました。
そんなSQLと分析関数で書き直したSQLを比較してみます。
検証環境
マシン：Amazon Web Service RDS(db.m1.small)
Oracle：Oracle Database Standard Edition One 11.2.0.2.v5
サンプルデータ：JdbcRunner テストキット Tiny TPC-Cの tpcc_load.js、tpcc.js で作成。
@sh2ndさん有難うございます。
昔はこう書いてた
各顧客の最新の注文で、一番高い金額を取得してみます。
昔は、こんな感じで書いていました。
SELECT o.o_id, o.o_d_id, o.o_w_id, MAX(o.o_id) as latest_order_date, (SELECT MAX(i.i_price) FROM item i, order_line ol2 WHERE i.i_id = ol2.ol_i_id AND ol2.ol_o_id = o.o_id AND ol2.ol_d_id = o.o_d_id AND ol2.ol_w_id = o.o_w_id) as highest_price FROM orders o, order_line ol WHERE o.o_c_id = '1124' AND o.o_d_id = '2' AND o.o_w_id = '13' AND o.o_id = ol.ol_o_id AND o.o_d_id = ol.ol_d_id AND o.o_w_id = ol.ol_w_id GROUP BY o.o_id, o.o_d_id, o.o_w_id ORDER BY o.o_id, o.o_d_id, o.o_w_id
う～ん、、、 カラムの中にSELECT文があるのが、かなり嫌な感じがします。
書き直してみる
カラムの中にあるSELECT文が嫌なので、FIRST関数を使ってみます。
まず、itemテーブルを普通にFROM句に持ってきて、FIRST関数のORDER BY句でオーダーID順でソートします。
SELECT o.o_id, o.o_d_id, o.o_w_id, MAX(o.o_id) as latest_order_date, MAX(i.i_price)KEEP(DENSE_RANK FIRST ORDER BY o.o_id DESC) as highest_price FROM orders o, order_line ol, item i WHERE o.o_c_id = '1124' AND o.o_d_id = '2' AND o.o_w_id = '13' AND o.o_id = ol.ol_o_id AND o.o_d_id = ol.ol_d_id AND o.o_w_id = ol.ol_w_id AND ol.ol_i_id = i.i_id GROUP BY o.o_id, o.o_d_id, o.o_w_id ORDER BY o.o_id, o.o_d_id, o.o_w_id
こんな感じでしょうか。余計なSELECT文が無くなってスッキリしました。
実行コストは？
SELECT文はスッキリしましたが、実行コストはどの程度改善させるのでしょう？
比較します。
昔のSQLの実行計画
とりあえず、autotrace を設定します。
SQL> set autotrace traceonly explain
昔のSQLの実行計画を確認します。
Execution Plan
----------------------------------------------------------
Plan hash value: 4157488715
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 | 7 (15)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 24 | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 10 | 240 | 14 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | ORDER_LINE | 10 | 150 | 4 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | ORDER_LINE_PK | 10 | | 3 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | ITEM_PK | 1 | | 0 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | ITEM | 1 | 9 | 1 (0)| 00:00:01 |
| 8 | SORT GROUP BY | | 1 | 33 | 7 (15)| 00:00:01 |
| 9 | NESTED LOOPS | | 10 | 330 | 7 (15)| 00:00:01 |
| 10 | VIEW | VW_GBC_5 | 1 | 23 | 5 (20)| 00:00:01 |
| 11 | HASH GROUP BY | | 1 | 14 | 5 (20)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID| ORDERS | 1 | 14 | 4 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | ORDERS_IX1 | 1 | | 3 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | ORDER_LINE_PK | 10 | 100 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("OL2"."OL_W_ID"=:B1 AND "OL2"."OL_D_ID"=:B2 AND "OL2"."OL_O_ID"=:B3)
6 - access("I"."I_ID"="OL2"."OL_I_ID")
13 - access("O"."O_W_ID"=13 AND "O"."O_D_ID"=2 AND "O"."O_C_ID"=1124)
14 - access("ITEM_1"="OL"."OL_W_ID" AND "ITEM_2"="OL"."OL_D_ID" AND
"ITEM_3"="OL"."OL_O_ID")あんまり重いデータではないので、こんなもんでしょうか？
書き直したSQLの実行計画
こちらも実行計画を確認します。
Execution Plan
----------------------------------------------------------
Plan hash value: 1892011866
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 17 (6)| 00:00:01 |
| 1 | SORT GROUP BY | | 1 | 38 | 17 (6)| 00:00:01 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 9 | 342 | 16 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 9 | 261 | 7 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| ORDERS | 1 | 14 | 4 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | ORDERS_IX1 | 1 | | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| ORDER_LINE | 9 | 135 | 3 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | ORDER_LINE_PK | 9 | | 2 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | ITEM_PK | 1 | | 0 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | ITEM | 1 | 9 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("O"."O_W_ID"=13 AND "O"."O_D_ID"=2 AND "O"."O_C_ID"=1124)
8 - access("OL"."OL_W_ID"=13 AND "OL"."OL_D_ID"=2 AND "O"."O_ID"="OL"."OL_O_ID")
9 - access("OL"."OL_I_ID"="I"."I_ID")書き直したSQLの方が%CPUは低いけどCostは大きいですね。各々のコストが小さいので余り良く分からない、、、
もう少し重いSQLで比較する
WHERE句から、以下の顧客を特定する条件を抜かして比較してみます。
o.o_c_id = '1124' AND o.o_d_id = '2' AND o.o_w_id = '13'
昔のSQLの実行計画 その２
Execution Plan
----------------------------------------------------------
Plan hash value: 414388587
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 240K| 4687K| | 14470 (2)| 00:02:54 |
| 1 | SORT AGGREGATE | | 1 | 24 | | | |
| 2 | NESTED LOOPS | | | | | | |
| 3 | NESTED LOOPS | | 10 | 240 | | 14 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| ORDER_LINE | 10 | 150 | | 4 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | ORDER_LINE_PK | 10 | | | 3 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | ITEM_PK | 1 | | | 0 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | ITEM | 1 | 9 | | 1 (0)| 00:00:01 |
| 8 | SORT GROUP BY | | 240K| 4687K| 7544K| 14470 (2)| 00:02:54 |
|* 9 | HASH JOIN | | 240K| 4687K| 5160K| 13008 (2)| 00:02:37 |
| 10 | VIEW | VW_GBF_5 | 240K| 2343K| | 11887 (2)| 00:02:23 |
| 11 | HASH GROUP BY | | 240K| 2343K| 91M| 11887 (2)| 00:02:23 |
| 12 | INDEX FAST FULL SCAN | ORDER_LINE_PK | 4799K| 45M| | 4399 (1)| 00:00:53 |
| 13 | INDEX FAST FULL SCAN | ORDERS_PK | 480K| 4687K| | 365 (1)| 00:00:05 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("OL2"."OL_W_ID"=:B1 AND "OL2"."OL_D_ID"=:B2 AND "OL2"."OL_O_ID"=:B3)
6 - access("I"."I_ID"="OL2"."OL_I_ID")
9 - access("O"."O_ID"="ITEM_3" AND "O"."O_D_ID"="ITEM_2" AND "O"."O_W_ID"="ITEM_1")
書き直したSQLの実行計画 その２
Execution Plan
----------------------------------------------------------
Plan hash value: 1398604944
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 240K| 7968K| | 43683 (1)| 00:08:45 |
| 1 | SORT GROUP BY | | 240K| 7968K| 201M| 43683 (1)| 00:08:45 |
|* 2 | HASH JOIN | | 4760K| 154M| 10M| 26928 (1)| 00:05:24 |
| 3 | INDEX FAST FULL SCAN| ORDERS_PK | 480K| 4687K| | 365 (1)| 00:00:05 |
|* 4 | HASH JOIN | | 4760K| 108M| 2056K| 17923 (1)| 00:03:36 |
| 5 | TABLE ACCESS FULL | ITEM | 100K| 878K| | 308 (1)| 00:00:04 |
| 6 | TABLE ACCESS FULL | ORDER_LINE | 4799K| 68M| | 11352 (1)| 00:02:17 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("O"."O_ID"="OL"."OL_O_ID" AND "O"."O_D_ID"="OL"."OL_D_ID" AND
"O"."O_W_ID"="OL"."OL_W_ID")
4 - access("OL"."OL_I_ID"="I"."I_ID")ん？
改めて比較すると不思議なことに、不細工な昔のSQLの方が３倍程度低Cost!!
実行計画から類推するとFIRST関数の場合は、FROM一度全データを全て展開してから集約するので、処理対象行数が最後まで少なくならないようですね。
