MySQL Adminが見た Devs の常識、DBAは非常識

• DBA からのマサカリ集
• MySQL 4.0 から 5.6 への移行
  • レプリケーションを使ったバージョンアップ
    • 旧バージョンのスレーブがある場合
  • オンラインバージョンアップ
    • データファイルに互換性がある場合
  • データファイルの互換性がなければ、mysqldumpでダンプして新しい mysqlに
  • レプリケーション、オフラインバージョンアップができるのは基本的に一個上のメジャーバージョンまで
  • MariaDB 5.5, 10.0 は MySQL 5.5 ベース。5.6のコードとは互換性がない。
• レコードが3000万件あり、パーティショニングしても遅いのでスケールアウトしたい
  • スケールアウトのしどき
    • 物理的にメモリー、ストレージが足りない時
      • アプリケーションで水平シャーディングしてやるのが多い
      • 参照局所性が高い場合は、メモリが尽きてるように見えても意外に大丈夫なときもある
    • INSERT, DELETE が遅い時
      • PRIMERYKEY 直うちのケースは SELECT は早くならない
    • 高 IOPS な感じにするのであれば innodb_{read|write}_thread とか上げるんだけどあまり情報ない。
    • 残念ながら CPU や NIC がネックになるほど綺麗にスキーマ作ってあってクエリーが綺麗な DB は今のところ見たことがない
  • buffer pool hitrate も見よう
• 新規のテーブルをつくるときはレビューしているが、何をレビューしているのかケチがついた
  • SQL レビューって？
    • 文化な話もあるけど
    • 「今」「想定どおりのインデックスで」「想定どおりの結果がかえってくる」のは当たり前
    • 今よりレコード件数が増えても「想定通りのレスポンスで」結果が帰ってくることも勘案してください
    • EXPLAINの select_type が dependent subquery、type が all、Extraがusing temporary table や using file sort の場合はだいたい件数が伸びてくると重くなってきます。
      • 行けてないクエリは件数が多くなってくるほど重くなる場合が多い
• 最近親テーブルと子テーブルの間でデータ不整合が
  • 外部キー制約
    • 嫌いな人多いですよね
    • 嫌いな理由ベスト３
      • テスト目的などのテキトーなデータを突っ込もうとするとエラーになる
      • そもそもデータの整合性が狂っているので外部キー制約を追加する ALTER TABLE がコケる
      • 重くなりそう
        • tpcc_mysql で試した時には重くなかった
        • 外部キー　＞　セカンダリキー　＞　キーなし
    • これだけじゃなく、適切なトランザクション単位も大事
• このクエリなんとかならないの
  • 相関サブクエリー使ってる
    • サブクエリーの内側の WHERE 句で外側クエリーのテーブルのカラム参照してるやつ
    • 相関サブクエリーは外側クエリーからフェッチした行数だけサブクエリーを実行するので外側クエリーのテーブルが大きくなればなるほど一気に重くなる
    • CPU 使用率を跳ね上げることが多い。というか CPU しようりつが跳ね上がったら大量アクセス
  • インデックスが足りない
    • MyISAM はテーブルスキャンでもわりと速いけど
    • MySQL が１つのリレーションで使えるインデックスは基本的に１つだけなので重いクエリーには複合インデックスをつくる
      • WHERE 句の AND 条件でつながれているものを順番にならべる
      • ORDER BY で使われているカラムを追加する
      • 不等号とか OR 演算子使ったりすると ORDER BY まで波及しないとか、LIMIT 使ってるなら WHERE よりも OERDER BY を狙ってインデックス創ると速い
  • パラメータがイケけてない
    • 体感ではすくない
    • innodb_buffer_pool_size はデータ格納量か搭載メモリの７５％くらい
    • SSDの高速ストレージの場合は innodb_flush_method や innodb_io_capacity でかなり変わる
    • マウントオプションに noatime 足すのも結構効く
    • query_cache_type = 1
      • CPUの並列性が下がる
      • 5.4 からデフォルトでOffになった
    • MySQL はアホの子なのでなんとかとハサミは使いようだということを思い出せ
  • イケてないクエリが特定できない場合
    • そのままではスロークエリログに乗らないクエリー
      • set global long_query_time
      • log_queryies_not_using_indexes = 1
      • pt-query-digest, mysqldumpslow
    • pager egrep -v "sleep|handlersocket|binlog dump" からの show full process list を連打
      • おすすめ
      • 5.6 に deprecated に
    • まずはEXPLAIN
      • Keyで想定しているキーが使われているか、Key Lengthが想定している長さで使われているか
        • int, datetime, vachar(32) のキーなのに key length が 4 だと int しか使われていない
      • Using file sort はおそい
    • set profiling = 1; からクエリー実行、show profile;
      • 5.6 で deprecated に