MySQLの最先端を行く現場人が集う MySQL Casual Talks #6に行ってきた！
MySQLなカジュアルなトークが聞けるということでOracle総本山の青山センターへ行ってまいりました。
mysql-casual.org
1 Pocket
mysql-casual.org is Expired or Suspended.
ブログに書いてくださいと最後に書いてあったので、取り急ぎメモを残しておきます。
順番は発表順。
注意事項
@oranie さんがbeer飲んで、MySQL最高や〜って言ってる。 #mysqlcasual
— ジャック・ニコルソン (@kakerukaeru) 2014, 7月 11
おっさんがのんでるけど勉強会じゃないじゃん？
-> カジュアルはカジュアルに。勉強会ではないです。
@yoku0825さん TokuDB試してみる
TokuDB試してみる from yoku0825
- TokuDBとはTokutekが作ったFractalTree(R)Indexを実装したストレージエンジン
- 特徴
- オンラインALTER TABLE(ADD INDEX, ADD COLUMN)に対応
- 圧縮がイケてる
- 断片化しない
- クラスターインデックスを複数作れる
- infromation_schema, show engine tokuDB で情報みれるがなれるのに時間かかる
- foreign keyは対応してない
- How TokuDB Fractal TreeTM Indexes Work
- ランダムライトなインデックスの構築が早い
- InsertのTPSはいつまでもスループットが変わらない
- テストケース
- twitterみたいなことをやろうとした
- テーブルをクロスしてtimelineテーブルを作る
- timelineテーブルは6億件
- ファイルサイズ図ったらinnodb compactは60G弱、 InnoDB compressedは25G、 tokku db zlibは7Gくらいに収まった
- 所管
- innodbはスレッド数が超えるとwaitが出始めてスループット頭打ち
- tokudbはCPUをぶんまわす
- InnoDB compressedと比べると3倍圧縮がきくので容量欲しいときは嬉しい
- 結構lock wait timeoutを食らう
- 公式MLは過疎ってる
- パーティショニングしてるものを開くのがかなりオーバーヘッド
- count(DISTINCT)が遅い
- mysqlコマンドラインクライアントからクエリーを叩くとクラッシュすることがある
- まだ安心して使えなそう
- Q&A
- ロックが・・・
- インデックスロックという割にはなぜか空のところにINSERTするのにロックするのがよくわからない
- ロックが・・・
@songmuさん MySQLゆるふわ運用のためのアグレッシブ開発 ～ データを増やさないための設計と運用方針について(主にパーティション活用の話)
- スライド：http://songmu.github.io/slides/mysql-casual-6/#0
- 開発するときにパーティションをどういうふうにやってるかの開発者向けのお話
- ゆるふわMySQL運用
- スケールアップで頑張りたい
- シャーディングしたくない
- スレーブ参照もしたくない
- データをメモリにのせたい！
- 最近のHW事情
- ちゃんとインデックスの効いたクエリを投げてる分には数万QPSとかいける
- マスタ1台+ホットバックアップ用Slaveでかなりいける
- ダウンタイムを許容できるならRDSでも。
パーティションの活用
- 論理削除を避ける
- データを増やさない
- データを効率よく消す仕組みを作る
- パーティションの活用
- MySQLのDELETEは遅いので一度の大量データを消すのに時間が掛かるがパーティションでやれば瞬時に削除できる
- パーティションの制限
- パーティション対象カラムは全てのユニークキー制約に含まれてないとだめ
- 外部キー制約を使えない
- パーティション個数制限 (MySQL5.5 : 1024 / MySQL5.6 : 8192)
- パーティションをきっていない領域にデータを格納できない
- hoge_idに対して1~10までしかパーティションを設定していない状況で11とかいれるとエラー
- よく使うパーティションの種類
- RANGEパーティション
- 時間でパーティション区切る
- id, created_dateでprimary key
- 保持期間を設定して期間が過ぎたデータを消す
- RANGE COLUMNSはMySQL5.5以降
- LISTパーティション
- 期間データをもってるマスタ系のデータのevent_idなどで区切る
- eventが終わればマスタデータのend_atを元に削除
- RANGEパーティション
- created_dateでRANGEパーティションを区切ることの功罪
- idとcreated_dateでユニーク制約をしていると、idのユニーク制約が担保されない
- idだけでロックをかけると大変なことに
- 対策1 created_dateをDATETIMEじゃなくてDATEにする
- WHERE条件にcreated_dateを含めればユニーク制がある程度含まれる
- 複数日分をWHERE INで取ってきたい時とかに破綻する
- 一日未満のパーティションは切れない
- 対策2 idでRANGE PARTITIONを切る
- idのユニーク制約は保たれる
- パーティションを余裕を見て作っておかないとエラーになる
- autoincrementの値を監視しながら自動でつくる仕組みが必要
- 厳密に1日単位でパーティションを区切れない
- パーティション使えばカジュアルにINSERTできます
- 一時セッション情報とかもカジュアルにINSERTしてあとで簡単に消せると思えばINSERTも抵抗無い
- パーティション運用
- RANGEは毎日未明に翌日分のパーティションを作成するバッチをcronで回す
- Listパーティションはマスターデータ投入時に関連テーブルにパーティションを設定する
データを増やさない仕組み
- 論理削除を避ける
- DELETEは遅い
- 数百行くらいまでなら大丈夫
- DELETEして履歴テーブルに(BULK)INSERTする
- 履歴テーブルはパーティションを切って消していく
- 削除フラグのカラムを意識して変なインデックスをはらずに済む
- クエリもアプリもシンプルに
- DELETEは遅い
- そのバッチ処理は必要か
- 例えばユーザー全員プレゼントをする場合
- X : 全員に付与
- 死にレコードがふえるし、時間がかかる
- O : ユーザーアクセスのタイミングで付与する
- giftテーブルのようなマスターテーブルを作成して付与期間を設定する
- user_gift_histroryのようなテーブルで受け取り履歴管理
- gift_idでLISTパーティション設定してidで削除
- データ型に気をつける
- マスタデータなどでできるだけSMALLINTにする
- データ量が変わる
- マスタデータなどでできるだけSMALLINTにする
- 古いデータを簡単に削除できない
- 5.6以降ならEXCHANGE PARTITIONてのがあり、パーティションをごそっと別テーブルに移せる
- blackholeストレージとかにしてしまう
- 削除データをEXCHANGEするテーブル作って、プロダクション用はBLACKHOLEにしておいて、データ保全用のSLAVEには保存されるようにするとかすると良さそう！
@yappoさん Better Groonga Replication
- スライド：http://yappo.github.io/talks/20140711-mysqlcasual6/#/title
- Groongaでreplication構成を作った理由と実装について
Seena + MySQLを最初使っていた
- 使うのにコツがあった
- fulltext indexを頻繁に更新すると壊れた
- 通常のMySQLの用途としては使わない
- OFFSET 1000000 LIMIT 10とかで10行しかfetchするとMySQLでは1~100000行目まで綺麗に舐めてしまう -> バッチを作って回避
- 安定運用方法
- クローラを回して検索用データをインサートする
- URL別のスコアを計算する
- スコアを基準に検索用のテーブルをALTER TABLE serch_data ORDER BY scoreとかする
- 検索させたいテキストが入ったカラムにfulltext indexを貼る
- データ差し替えたいときはまたデータベース作り直しで、UPDATE/DELETEしない
Groonga
- 全文検索が必要になった
- JVMの運用に自信がないので消去法でGroongaを使おうと思った
- MySQLのストレージエンジン使ってよ
- with MySQLのブラジル製品怖い(経験者談)
- Groonga + MySQLの利点
- replicationが楽
- matsunobuさんのMHAが使える
- DBIとかで検索クエリ投げれる
- 欠点
- 闇が深いのでハマると大変
- Groonga単独の欠点 -> レプリケーションどうするのか
- ないのでGroonga Proxy Serverを作った
- 更新クエリだけを抽出してGroonga Serverにクエリを中継してくれる
- 障害のノードの対処やスケールアウト戦略をどうするか？
- クエリをproxyしてるだけなので実際運用が不可能だった
- こまったのでbinlogみたいな仕組みを入れたらどうといわれてひらめいた
- 特徴
- サーバ増設が簡単
- 設定はわずか
- 追加で必要なdaemonはない
- GroongaのクラスにMaster-Slaveという概念はない(全員がスレーブみたいな)
- システム概略
- 適当なMySQLのdatabaseにbinlog tableを作る
- Groonga側にslave_status tableを作る
- binlog tableのidとslave_status tableのposを比較して処理してないbinlog tableのdataを元にしてupdate_typeで指定したクエリをGroongaに投げる cron script
- 各GroongaSeverにcronscriptを仕込んで、binlogの更新内容を適用していく
- 簡単なサーバ増設フロー
- Groonga ServerはLBなどでバランシングする
- 増設したいときはバランサから外す
- cronやgroongaなどを止めて、新規サーバにコピーして起動するだけ
- 同期とかを気にせず増設して行けて便利(だが、実運用でやったことはない)
- なぜDroongaを使わなかったの？
- NodeJSやFluentd等色々使っててなんか大きい…
- まとめ
- 自前でdaemonかくと怖いので既存の仕組みでHA確保できる
- 障害対応などのドキュメントはちゃんと書く
- 困ったら優秀な人に泣きつけ
@do_akiさん N:1 Replication meets MHA
- About N:1 Replication
- Multi Master to Single Slave Replication
- Multi-source Replication
- N:1 ReplicationはMySQL5.0でも動く
- 2つのスレーブから1つのスレーブを作れます
- 違うマスターDBのテーブルをjoinしたかったので作った
- 3年間動き続けました
- マスターのサーバが変わった時にrestartが必要だった
- 再度つくろう！https://github.com/do-aki/N1Repl
- 一時的にマスター切り替えできるもの作ってます
- マスターサーバが変わった時に切り替えられるようなもの作ってます
- MHA for MySQL
- Master High Availability Manager and tools for MySQL
- failoverの仕組みだが、メンテナンスのためにmasterを外すという機能がある(マスタースイッチであってフェイルオーバーではない)
- N:1 replicationと仕組みが似てる！
- 残念ながらMHA対応はできませんでした
- マスターが死んでると動きません
- MHAはbinlog file/positionなしで動くfunctionをhookしてるとか
- JIKAISAKUにご期待ください
@kamipoさん ふつうのWeb開発者のためのクエリチューニング
- スライド：http://kamipo.github.io/talks/20140711-mysqlcasual6/#/title
- DBIx::QueryLog
- useするだけで全部のクエリをSTDERRにはいてくれる
- アプリケーションを一切変更することなく使えて便利
- しかし、クエリや実行計画から問題を読み取るには経験が必要
- 明らかにやばそうなクエリは機械的に判断できそう
- 明らかにやばそうなクエリ
- select_type
- DEPENDENT UNION
- DEPENDENT SUBQUERY
- UNCACHEABLE UNION
- UNCACHEABLE SUBQUERY
- type
- index
- ALL
- possible_keys
- NULL
- key
- NULL インデックス使ってない
- Extra
- Using filesort
- Using temporary
- select_type
- MySQLCasualLog作った！
- やばそうなクエリに色つけてくれる
- さっきのヤバイやつに色がついてくれる
- やばそうなクエリ？
- rows
- 小さいときは問題ない
- 大きいときに問題ないか判断するには経験が必要
- rows
- 参考
@monryさん 続・Amazon RDS Casual Talks でも、お高いんでしょう…?
- Amazon RDSって安いんですか？
- 好きなモノはお金だけど、カミさんが一番でした。てへぺろ
- 前回の構成
- Master (Small single-AZ)の下にmicro Slaveが5台
- ディスられました
-
- 小さいインスタンスタイプのRDSだせぇｗ
- Slaveはlargeくらいにしろよｗ
- multi-AZ使わないとかNE-YOｗ
- Reserved Instanceかってないのかｗ
- リア充爆発しろ
- 移行した
- Master m2.2xlarge(Multi-AZ)の1台のみにした
- 月々1,165.47$でしたが移行後、1,093.06$になりました
- スペック上がって安くなった！
- でも実は2014/04の料金改定がおおきい
@karupaneruraさん いまさらネクストキーロックを理解した話
- Transaction分離レベル
- Read Uncomitted
- Read Commited
- Serialize
- Repeatabe Read
- ファントムリードが発生する
- ファントムリード
- さっきまで無かったはずの行がTransactionの途中に存在でてきてしまう
- MySQLのロック
- record lock
- 単一のindex record ロック
- gap lock
- index recordの間、先頭、末尾のロック
- record lock
- next-key locking
- Repatable readで一貫性を保証するためのlock
- record lockとその前のgap lockを取得する
- gap lockによって行の挿入を防ぐ
- 勘所
- index record lockである
- covering indexの場合などはlock範囲が異なる
- cf : InnoDBのロックの範囲とネクストキーロックの話
- クエリによってロック範囲が細かく異なる
- index record lockである
@dblmktさん MySQL Fablic使いたいんですけどどうなんすか
- アメブロのDBのは昔はMySQLだが、Oracleだった
- マスター分割が激しい
- 独自ルールでシャーディング
- ひとつのテーブルに対してt1,t2,t3,,,と大量にテーブルをノード毎に分散して作っている
- show slave hosts; -> 65 rows!!
- 大変
- スレーブの作り直しが面倒
- シャーディングルール変更難しい
- アクセスの方より対策困る
- my.conf書き換え厳しい
- Replicate-do-tableをいちいち書いている
- いろいろ検証してみた
- Jetpants
- Tumblr謹製シャーディング管理ツール
- rubyベース
- サービスに影響を与えないようにシャーディングするのが売り
- MySQL Utilities
- mysqldbcopy
- mysqluserclone
- mysqlfailober
- Jetpants
MySQL Fablic
- MySQLサーバ群を管理する統合フレームワーク
- マスタ障害時、スレーブの自動昇格
- シャーディング、クエリのロードバランシング
- 更新はマスタ、参照はスレーブ
- スレーブの複製
- 構成
- シャーディングを使わないと普通のWebサーバとDBの構成になる
- Web/APIサーバからFabric対応ConnectorがFabricサーバに聞いてどこに接続するかをききにいく
- シャーディング構成
- 実際のデータが入るのがグループ1,グループ2
- GlobalGroupとしてのマスタが一台いる
- 前提条件
- Fabric対応Connector必須
- MySQL5.6.10以上+GTIDレプリケーション必須!!
- setupは簡単
- MySQL Serverインストール
- MysQL Utilitiesをインストールするとfabricも入ります
- MySQL Serverインストール
- グループ作成
- mysqlfabric group create cluester01
- グループにサーバ追加
- グループのサーバをマスタに昇格
- マスタ障害検知
- 障害検知を有効にするとサーバ状態を調べると検知できる
mysqlfabric group lookup_servers cluster01
FAULTYと出ます - 数秒で切り替わりできます
- アプリ側で自動的にマスターには向いてくれないので再接続する必要がある
- 障害検知を有効にするとサーバ状態を調べると検知できる
- シャード作成
- Global Groupのグループ作成した後に、グループを作っていく
- 分割や移動もコマンド一発で出来ます
- global groupにはスキーマ情報だけが入る
- 気になるところ
- ドキュメントがまだまだ
- コマンドの結果がわかりにくい
- Fabricサーバはの冗長化は不可能
- Pacemaker使ってやればとかあったけどそれはどうなの・・・
- ボトルネックになる可能性
- Fabricサーバのスケールアウト不可
- シャード分割、移動時にマスタにmysqldumpが走る
- マスタとスレーブでDELETEが走る
- すごい件数を分割させたりすると死ぬ
- 使いドコロ
- GTIDレプリ使う新規プロジェクトなら入れとけ
- 既存DBに入れるのは辛い？
- 現行シャード構成の変更
- Connector部分の書き換え
- MySQL5.6へのアップデート
- GTIDアプリケーションへの対応
- まとめ
- もういっぽだけどいい感じ
- デモ風景
@studio3104さん LTしたい
- スロークエリの話
- 解析どうしてる？
- 日次バッチによるメールで収集
- サーバにログインしてファイルを見る
- mysqldumpslow
- pt-query-digest
- グラフツールで件数の推移を可視化
- mysqldumpslow
- ファイル単位で解析
- ファイルがでかいと遅い
- ファイル単位で解析
- Nata2というのをつくった
- スロークエリログ一覧など出る
- クエリをおすと細かい詳細が見える
- 合計クエリ順などにソートできる
- スロークエリログの登録はAPIになげる
- nata2-client
- SSH+mysql client
- リモートでMySQLサーバからログ取得
- fluent-plugin-nata2開発中
- メモリをめいいっぱい使っているホストでFluentdを泳がすのは厳しい
- MySQLサーバにFluentdとかいれるのがめんどくさい
- use databases;をつかうとどのDBなのかがわからない問題
- 以上の理由から作ってます
- SSH接続できないサーバがあってスロークエリログを集めたい
- 今後追加したい機能
- ドキュメント
- テスト
- スロークエリ取得のAPI
- EXPLAIN
- show create tableてきな
- neta2-client local mode
- 今後修正したい機能
- 現在から24時間、1週間、1ヶ月、1年の期間指定しかできないので細かく指定したい
- 個別のクエリビュー
@sgwr_dtsさん MHA on AWS + Rails
弊社のMHA設定
- 基本的にMySQL on EC2
- VPC Route Tableによる仮想IP
- Railes 3.2/4.x
- フェイルオーバー周りのツールは自作
- master_ip_failoverなどはPerlからポート+VPCまわりの処理を追加
- サンプルプロジェクト
- ManagerはUpstartでデーモン化
- 複数のクラスタ監視
- 冗長化はしてない
- ノード/マスターサーバはPuppetにしてる
導入に至るまで
- 検証環境を作ってテストした
- 仮想IP切り替わり、スレーブもかわったがエラーが出た
- Automatic Reconnectionを使えば自動再接続が有効になるが、再試行は1回だけ
- Access denied forがでると再接続できない
- activerecord-mysql-reconnectを作りました
- 実装
- フェイルオーバー時にエラーの出る下位レイヤのメソッドをフック
- エラーメッセージを見てリトライ
- セッションを切って大丈夫か？->だいじょばないかも
- 再接続モードを用意しました
- Lost connection to server during query
- 検証環境で再現しない
- 今のところうまく動いている…
- とあるサービスでオンライン切り替えしたら問題なかった
- デモ
@rkajiyamaさん N:1 Replication meets Python
- 循環レプリケーション
- MySQL Utilities since 2010
- mysqlrplmsが加わりました！
- 複数のマスターから1台のスレーブに集める(どっかで聞いたような)
- gtidを使います(!!)
- switchover-interval で切り替え間隔指定できる
- intervalは画面表示のリフレッシュするだけ
- 宣伝
- 2014/7/17にMySQL組み込みセミナーをします
- 2014/9/28-29 MySQL Centralをやります
- 2014/10/10 なんかやる
- 2014/11/24 なんかやる
- 一番広い部屋でできますよ
- 2014/12/12 なんかやる
- MySQLでNoSQLやりたいな
まとめ
- 5.6を使ってる人はあまりいないみたい(うちもあまり使えてないけど)
- @songmuさんの発表は実践的に使えそうだった。Oracleのプラチナ試験の時にいじったりしたけど実践で使ったことはないのでそろそろパーティション童貞を卒業したい
- @kamipoさんはid:shin_bashiに似てた(カラダの細さとか見た目とか)
- 前にMulti-AZ使ってるとフェイルオーバーしてる最中、結構な時間接続きれるけど最近はどうなんだろう。
- MySQL Fabricはなんかｗｋｔｋさせられるが、冗長性が気になるところ
- @kamipoさんと@studio3104さんのものはお金かければMySQL Enterprise Monitor使えば賄えそうだが気軽に使うのならいいかも。
- GTIDが時期早々感があるが、今後スタンダードになっていったら便利にはなっていきそう
- 初めて参加したが、みなさんのMySQLレベルが高すぎて圧倒されました
- こういうIT系の勉強会って男性が多いのですが、そのなかで際立ってきれいな女性がいてびっくりした(そういえばLINEのDBAでもきれいな人がいた)
- 次回も絶対参加！
- 発表おつかれさまでした！
