My MySQL Best Practices

Transcript

1. My MySQL Best Practices 2017/03/21 MyNA @mita2
2. 2 Thank you, Matt ! Welcome MySQL Nippon Association
3. みんな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
4. 自己紹介 4 • 三谷 智史（Twitter: @mita2） • MySQLとの関わり 2002年～ 主に利用して開発 2010年～ 主に管理する立場
5. 5 My MySQL Best Practice というかDBAからのお願いです
6. My MySQL Best Practice • DBを選ぶ • Use InnoDB 、no more MyISAM • エラーハンドリング • 定期的な再接続 • クエリとクエリの間に重い処理を入れない • DBAとのコミュニケーションのコツ 6
7. My MySQL Best Practice • DBを選ぶ • Use InnoDB 、no more MyISAM • エラーハンドリング • 定期的な再接続 • クエリとクエリの間に重い処理を入れない • DBAとのコミュニケーションのコツ 7
8. DBを選ぶ • みんな 大好き MySQL • 苦手なこともあるよ 8
9. MySQL の得意分野 9 OLTP系 ワークロード OLTP系 ワークロード DWH系 ワークロード DWH系 ワークロード テーブル テーブル • Online Transaction Processing • オンライン処理 • 結果をすぐ返す • 多数の行のうち 少数の行の複数のカラムに注目 • よくアクセスされる行と そうでない行がある • Data WareHouse • 集計・解析 • 多くはバッチ・非同期 • 多数の行のうち 多数の行の少数のカラムに注目 • 比較的均一にアクセスされる
10. MySQL の得意分野 10 OLTP系 ワークロード OLTP系 ワークロード DWH系 ワークロード DWH系 ワークロード テーブル テーブル • Online Transaction Processing • オンライン処理 • 結果をすぐ返す • 多数の行のうち 少数の行の複数のカラムに注目 • よくアクセスされる行と そうでない行がある • Data WareHouse • 集計・解析 • 多くはバッチ・非同期 • 多数の行のうち 多数の行の少数のカラムに注目 • 比較的均一にアクセスされる
11. DBMSの使い分け • DB選定フェーズを確保する • 「違うもの」と比較する • × MySQL vs MySQL forks • ◦ MySQL vs Hadoop • ◦ MySQL vs Cassandra • ◦ OSS vs 商用 • × どのDBが一番良いか • ◦ どう組み合わせるのが良いか 11
12. My MySQL Best Practice • DBを選ぶ • Use InnoDB 、no more MyISAM • エラーハンドリング • 定期的な再接続 • クエリとクエリの間に重い処理を入れない • DBAとのコミュニケーションのコツ 12
13. Use InnoDB, No more MyISAM • MyISAM は トランザクション非対応 • BEGIN/COMMITが使えない・・・だけではない 13
14. Use InnoDB, No more MyISAM • MyISAM は ディスクに同期書き込みしない • 障害時にデータがロストするリスクがある • 破損チェックや復旧に時間がかかる 14
15. 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
16. My MySQL Best Practice • DBを選ぶ • Use InnoDB 、no more MyISAM • エラーハンドリング • 定期的な再接続 • クエリとクエリの間に重い処理を入れない • DBAとのコミュニケーションのコツ 16
17. エラーハンドリング • DBのエラーはログに落とす • PHP PDOのERR_MODEをException にするの忘れがち • setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); • エラーハンドリングは「開発者の腕の見せ所」 17
18. リトライ • エラーはいつでもあり得る • DBの構成を工夫しても、リトライがなければ活かされない • リトライしてますか？ • そのバッチ再実行できますか？ 18
19. そしてテスト • 障害試験 • 負荷試験 • RDBはスケールできない（他と比較して • 近年、軽視されがち • ビジネスの加速 • CI/CD 等によるチェックポイントのない運用 • クラウドによるスケールアップ 19
20. My MySQL Best Practice • DBを選ぶ • Use InnoDB 、no more MyISAM • エラーハンドリング • 定期的な再接続 • クエリとクエリの間に重い処理を入れない • DBAとのコミュニケーションのコツ 20
21. 定期的な再接続 • コネクションプーリングしているとき • 定期的に張りなおしが必要 • Apache なら MaxRequestsPerChild を設定 • ２つの理由 21
22. 理由１ 設定変更の反映 • コネクションの設定は、「新規接続時」に適用される • SET GLOBAL → 再接続しないといつまでも古い設定のまま 22
23. 理由2 コネクションの偏りを解消する • 障害等でコネクションが偏る • 偏っても問題ないのが、理想だが・・・実際は不安 • 復旧後、自動で偏りが解消されるように 23
24. My MySQL Best Practice • DBを選ぶ • Use InnoDB 、no more MyISAM • エラーハンドリング • 定期的な再接続 • クエリとクエリの間に重い処理を入れない • DBAとのコミュニケーションのコツ 24
25. クエリとクエリの間に処理をはさまない 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
26. クエリとクエリの間に処理をはさまない • API を呼び出している間、コネクションは Idle 状態 • コネクションの生存期間が延びて・・・ • Too many connections で新規接続不可 26
27. クエリとクエリの間に処理をはさまない • 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
28. クエリとクエリの間に処理をはさまない • DBの処理はまとめて行う • まとめられないなら、「遅くなる可能 性のある処理」にはタイムアウトを 28 Connect Connect Execute SQL Execute SQL Call Web API Call Web API Execute SQL Execute SQL Disconnect Disconnect
29. My MySQL Best Practice • DBを選ぶ • Use InnoDB 、no more MyISAM • エラーハンドリング • 定期的な再接続 • クエリとクエリの間に重い処理を入れない • DBAとのコミュニケーションのコツ 29
30. DBAとのコミュニケーションのコツ • 「やってほしい作業」ではなく「目的」を伝える • 開発者に見えているDBの状態はごく一部 • 経験豊富・情報量の多いDBAに「手段」はまかせる 30
31. 31 Enjoy MySQL