My MySQL Best Practices

Slide 1

Slide 1 text

My MySQL Best Practices 2017/03/21 MyNA @mita2

Slide 2

Slide 2 text

2 Thank you, Matt ! Welcome MySQL Nippon Association

Slide 3

Slide 3 text

みんなBest Practice おさらい • @yoku0825 • MySQL パターン • https://www.slideshare.net/yoku0825/mysql-71661984 • アンリーダーブルSQL • RDBMS・ザ・オラクルマシン etc… • @sodai1025 • Web エンジニアに知ってほしいRDBアンチパターン • http://soudai.hatenablog.com/entry/2017/03/04/190000 3

Slide 4

Slide 4 text

自己紹介 4 • 三谷智史（Twitter: @mita2） • MySQLとの関わり 2002年～主に利用して開発 2010年～主に管理する立場

Slide 5

Slide 5 text

5 My MySQL Best Practice というかDBAからのお願いです

Slide 6

Slide 6 text

My MySQL Best Practice • DBを選ぶ • Use InnoDB 、no more MyISAM • エラーハンドリング • 定期的な再接続 • クエリとクエリの間に重い処理を入れない • DBAとのコミュニケーションのコツ 6

Slide 7

Slide 7 text

My MySQL Best Practice • DBを選ぶ • Use InnoDB 、no more MyISAM • エラーハンドリング • 定期的な再接続 • クエリとクエリの間に重い処理を入れない • DBAとのコミュニケーションのコツ 7

Slide 8

Slide 8 text

DBを選ぶ • みんな大好き MySQL • 苦手なこともあるよ 8

Slide 9

Slide 9 text

MySQL の得意分野 9 OLTP系ワークロード OLTP系ワークロード DWH系ワークロード DWH系ワークロードテーブルテーブル • Online Transaction Processing • オンライン処理 • 結果をすぐ返す • 多数の行のうち少数の行の複数のカラムに注目 • よくアクセスされる行とそうでない行がある • Data WareHouse • 集計・解析 • 多くはバッチ・非同期 • 多数の行のうち多数の行の少数のカラムに注目 • 比較的均一にアクセスされる

Slide 10

Slide 10 text

MySQL の得意分野 10 OLTP系ワークロード OLTP系ワークロード DWH系ワークロード DWH系ワークロードテーブルテーブル • Online Transaction Processing • オンライン処理 • 結果をすぐ返す • 多数の行のうち少数の行の複数のカラムに注目 • よくアクセスされる行とそうでない行がある • Data WareHouse • 集計・解析 • 多くはバッチ・非同期 • 多数の行のうち多数の行の少数のカラムに注目 • 比較的均一にアクセスされる

Slide 11

Slide 11 text

DBMSの使い分け • DB選定フェーズを確保する • 「違うもの」と比較する • × MySQL vs MySQL forks • ○ MySQL vs Hadoop • ○ MySQL vs Cassandra • ○ OSS vs 商用 • × どのDBが一番良いか • ○ どう組み合わせるのが良いか 11

Slide 12

Slide 12 text

My MySQL Best Practice • DBを選ぶ • Use InnoDB 、no more MyISAM • エラーハンドリング • 定期的な再接続 • クエリとクエリの間に重い処理を入れない • DBAとのコミュニケーションのコツ 12

Slide 13

Slide 13 text

Use InnoDB, No more MyISAM • MyISAM はトランザクション非対応 • BEGIN/COMMITが使えない・・・だけではない 13

Slide 14

Slide 14 text

Use InnoDB, No more MyISAM • MyISAM はディスクに同期書き込みしない • 障害時にデータがロストするリスクがある • 破損チェックや復旧に時間がかかる 14

Slide 15

Slide 15 text

Use InnoDB, No more MyISAM • MyISAM フェードアウトは確定的 • MySQL 8.0 • Dictionary Data in InnoDB • DDL が crash safe に • https://dev.mysql.com/doc/refman/8.0/en/data-dictionary.html 15

Slide 16

Slide 16 text

My MySQL Best Practice • DBを選ぶ • Use InnoDB 、no more MyISAM • エラーハンドリング • 定期的な再接続 • クエリとクエリの間に重い処理を入れない • DBAとのコミュニケーションのコツ 16

Slide 17

Slide 17 text

エラーハンドリング • DBのエラーはログに落とす • PHP PDOのERR_MODEをException にするの忘れがち • setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); • エラーハンドリングは「開発者の腕の見せ所」 17

Slide 18

Slide 18 text

リトライ • エラーはいつでもあり得る • DBの構成を工夫しても、リトライがなければ活かされない • リトライしてますか？ • そのバッチ再実行できますか？ 18

Slide 19

Slide 19 text

そしてテスト • 障害試験 • 負荷試験 • RDBはスケールできない（他と比較して • 近年、軽視されがち • ビジネスの加速 • CI/CD 等によるチェックポイントのない運用 • クラウドによるスケールアップ 19

Slide 20

Slide 20 text

My MySQL Best Practice • DBを選ぶ • Use InnoDB 、no more MyISAM • エラーハンドリング • 定期的な再接続 • クエリとクエリの間に重い処理を入れない • DBAとのコミュニケーションのコツ 20

Slide 21

Slide 21 text

定期的な再接続 • コネクションプーリングしているとき • 定期的に張りなおしが必要 • Apache なら MaxRequestsPerChild を設定 • ２つの理由 21

Slide 22

Slide 22 text

理由１設定変更の反映 • コネクションの設定は、「新規接続時」に適用される • SET GLOBAL → 再接続しないといつまでも古い設定のまま 22

Slide 23

Slide 23 text

理由2 コネクションの偏りを解消する • 障害等でコネクションが偏る • 偏っても問題ないのが、理想だが・・・実際は不安 • 復旧後、自動で偏りが解消されるように 23

Slide 24

Slide 24 text

My MySQL Best Practice • DBを選ぶ • Use InnoDB 、no more MyISAM • エラーハンドリング • 定期的な再接続 • クエリとクエリの間に重い処理を入れない • DBAとのコミュニケーションのコツ 24

Slide 25

Slide 25 text

クエリとクエリの間に処理をはさまない 25 Connect Connect Execute SQL Execute SQL Call WebAPI Call WebAPI Execute SQL Execute SQL Disconnect Disconnect Connect Connect Execute SQL Execute SQL Call WebAPI Call WebAPI Execute SQL Execute SQL Disconnect Disconnect APIが遅くなると・・・ TIME

Slide 26

Slide 26 text

クエリとクエリの間に処理をはさまない • API を呼び出している間、コネクションは Idle 状態 • コネクションの生存期間が延びて・・・ • Too many connections で新規接続不可 26

Slide 27

Slide 27 text

クエリとクエリの間に処理をはさまない • Idle コネクションはそのうち wait_timeoutで切られる • SQL 実行時には接続が失われている 27 Connect Connect Execute SQL Execute SQL Call WebAPI Call WebAPI Execute SQL Execute SQL Disconnect Disconnect MySQL server has gone away MySQL server has gone away

Slide 28

Slide 28 text

クエリとクエリの間に処理をはさまない • DBの処理はまとめて行う • まとめられないなら、「遅くなる可能性のある処理」にはタイムアウトを 28 Connect Connect Execute SQL Execute SQL Call Web API Call Web API Execute SQL Execute SQL Disconnect Disconnect

Slide 29

Slide 29 text

My MySQL Best Practice • DBを選ぶ • Use InnoDB 、no more MyISAM • エラーハンドリング • 定期的な再接続 • クエリとクエリの間に重い処理を入れない • DBAとのコミュニケーションのコツ 29

Slide 30

Slide 30 text

DBAとのコミュニケーションのコツ • 「やってほしい作業」ではなく「目的」を伝える • 開発者に見えているDBの状態はごく一部 • 経験豊富・情報量の多いDBAに「手段」はまかせる 30

Slide 31

Slide 31 text

31 Enjoy MySQL