weblog of key_amb

主にIT関連の技術メモ

MyNA(日本MySQLユーザ会) 2015年4月 に行ってきた #mysql_jp

MySQL 勉強会

f:id:key_amb:20150423001513j:plain

最近ブログが勉強会参加レポートばかりになっている感がありますが、今日(4/22)は MyNA (日本MySQLユーザ会) に行ってきました。

ここのところ話題になっていた '🍣' = '🍺' 問題や、パフォーマンス・チューニング Tips, MySQL 5.7 の性能 / 新機能と、盛り沢山の話が聞けて、大変勉強になりました。

当日の Tweet が togetter にまとめられています。

発表内容と照らし合わせて見ると、参考になる情報もあるかもしれません。

以下、発表内容のノートになります。
資料はまだネット上に上がっていないものもあるようですが、捕捉したらこちらの記事も更新します。
(愚直にメモを取っていたので、かなりの分量になりました ^^; )

🍣=🍺

とみたまさひろさん

  • 自己紹介
    • 日本MySQLユーザ会代表
  • MySQL的には 🍣 = 🍺
  • CHARSET と COLLATION
    • utf8mb4
      • 🍣 = F0 9F 8D A3
      • 🍺 = F0 9F 8D BA
    • COLLATION = 文字の照合規則
      • show collation; で確認できる。200 以上。
      • CHARSET が prefix についてる
    • Charset ごとに Collation がある
  • utf8mb4 の Collation 16個ある
    • utf8mb4_general_ci
      • utf8mb4 charset のデフォルト collation
      • 大文字小文字区別しない
      • 絵文字区別しない
    • utf8mb4_bin
      • 全文字を区別する
    • utf8mb4_unicode_ci
      • Unicode Collation Algorithm 4.0.0
      • ASCII大文字小文字区別しない
      • 絵文字区別しない
      • ひらがな、カタカナ、濁点有無、全角、半角を区別しない
    • utf8mb4_unicode_520_ci
      • 絵文字を区別する
      • 後は utf8mb4_unicode_ci と一緒
    • 誰か utf8mb4_japanese_ci 作って！
  • weight_string 関数で同じ文字列かどうか確かめられる
  • 「パ」と「ﾊﾟ」問題
    • utf8mb4_unicode_ci
    • LIKE では区別される
      • 「パ」は1文字、「ﾊﾟ」は2文字
    • でも、= では同じになる
  • MySQL にバグレポート上がってる

MySQLの全文検索に関するあれやこれや

@yoku0825 さん

  • 5.7.7-rc リリース
  • 人はなぜ MySQL で全文検索するのか
    • レプリケーションに載せちゃえばレプリカ作るのが簡単
    • SPIDER 使えばシャーディングも超簡単
    • マスターは InnoDB, スレーブは Mroonga で全文検索と簡単
      • 文書保管と全文検索と2箇所まじめにやるの大変
    • 理想図 - シンプル
    • 現実 - 複雑
      • 全文検索用の mroonga master がいる
      • どうしてこうなった。。
  • MySQL 全文検索の歴史
    • 古来より伝わる MyISAMの秘法
    • Tritonn
      • MyISAM FTS のインデックス部分だけを Senna にすり替えるパッチで分かち書きで省略
      • もうバージョンが上がることはなさそう
    • Mroonga = Tritonn の後継
      • FTSはパーサだけを使い、完全なストレージエンジンとして実装
    • InnoDB FTS
      • MyISAM でできることはすべて InnoDB でもできるように
    • MeCab Full-Text Parser Plugin
      • 5.1から FullText Parser 用の API はあった
      • これでメイン系と全文検索系で謎の重複スキーマで謎のXAもどきをしなくて済む？
      • RDS にも入る？(期待)
  • 全文検索するために絶対に必要な知識
    • MySQL のバグの登録方法 bugs.mysql.com
    • Advansed Search
      • Boolean mode にチェックがつけれる
        • "+" が and 検索
      • Status は All を選ぶべし
      • オススメ Sort は ID の DESC
      • 似たようなのがなければレポートする
    • 踏むものは何度でも踏む。バグ報告しないと解決しない
    • InnoDB FTS テストケースが割とざる
  • InnoDB FTS vs Mroonga Tokenizer
    • 本番投入用のDBにデータ入れて比較
    • 2文字 * 1単語
      • InnoDB 1gram がトップで遅い
    • 1文字 * 1単語
      • Bigram 1文字の単語検索できないのでは？ => Mroonga はできる
    • 2文字 * 2単語
      • InnoDB 1gram は ERROR 188 で固まる
    • 1文字 * 2単語
    • 結果発表
      • InnoDB => buffer pool に載れば速い
    • 結果まとめ
      • Mroonga
        • Bigram でも1文字検索させてくれてやさしい
        • しかも速い
        • ノーマライザーが素敵
      • InnoDB メモリにはやさしい
        • Mroonga はあるだけメモリ使う
      • InnoDB FTS Ngram は使い物にならないかも
      • InnoDB 使うなら MeCab 必須
    • みんなでバグと戦いましょう

MySQL 都市伝説 Part 2

横道さん

  • 自己紹介
    • 漢(奥野さん)と一緒にサポート
    • MySQL の中の人
    • MySQL 歴2年
  • wait_timeout の都市伝説
  • wait_timeout の都市伝説 2
    • wait_timeoutの制限は累積
    • たぶんコネクションプールを使っていたのでは？
  • wait_timeout の推奨設定
    • 予期せぬ切断時に残ったセッションを自動切断するためのもの
    • インタラクティブクライアントは極力排除
    • 運用に支障を来さないなかでできるかぎり短く設定するべき
    • コネクションプールの設定と整合性をもたせる
      • コネクションプールのアイドルクリーンナップよりちょっと長くする
  • FLUSH TABLES WITH READ LOCK の都市伝説
    • FLUSH TABLES WITH READ LOCK では READ は止まらない
    • FLUSH TABLES WITH READ LOCK 実行前に走っていたクエリが終わるまで、FLUSH TABLES WITH READ LOCK 自体が終わらない。そして元のクエリが使用しているテーブルに対するクエリがすべて止まる
    • 長いクエリが走ってないことを確かめること
  • メモリ使用量の都市伝説
    • ( sort_buffer_size + myisam_sort_buffer_size + read_buffer_size + ... + innodb_buffer_size ) * max_connections
    • sort_buffer は filesort が行われないとアロケートされない
    • join_buffer は Index なしの JOIN が行われないとアロケートされない
    • ちゃんと Index 設定していればどちらも起こらない
    • テンポラリテーブルのサイズが計算式に入ってない
      • tmp_table_size はテーブルごと
      • 1つのクエリで複数のテンポラリテーブルが作られることもある
      • 巨大な tmp_table_size は危険！
      • tmp_table_size に入らなかったら MyISAM に変換する。その変換がとても遅い
      • tmp_talbe_size 小さめにして速めに MyISAM にした方がいい
  • メモリ関係のパラメータの定石
    • 基本デフォルト
    • テスト、テスト、テスト
      • 本番に近い接続数、クエリパターン
    • 監視
      • Created_tmp_disk_tables
      • Sort_merge_passes
      • Binlog_cache_disk_use
    • パラメータでチューニングせず、クエリをチューニングせよ
    • 理想は No filesort, No Join buffer, No temporary table
    • むやみにパラメータ上限値を上げない
  • メモリの都市伝説
    • kernel.shmall
    • kernel.shmmax
    • いくつに設定すればいいのか？
    • 変更する必要はない
    • MySQL はシングルプロセスなので変える必要ない
  • O_DIRECT の都市伝説
    • Innodb_flush_method=O_DIRECT は遅い
    • バッファプールに載ってなくて、ページキャッシュに載ってる場合だけ速い
    • ページキャッシュ余ってるならバッファプールに割り当てるべき
  • 推奨設定
    • Innodb_flush_method=O_DIRECT
    • vm.swappiness = 0
    • OOM Killer に殺されないように
  • パーティショニングの都市伝説
    • パーティショニングを使うと速くなる
    • MySQL では並列に実行しない
    • どんなときパーティショニング使うか
      • アクセスに局所性があり、刈込みができる
      • 定期的に大量DELETE
    • 複数パーティションあると、それだけテーブルインスタンスが増えるので、よりメモリを食う
  • TRUNCATE の都市伝説
    • TRUNCATE は速いので DELETE ではなく TRUNCATE を使うべき => △
    • 間違いではないが、TRUNCATEではバッファプールのクリーンナップが必要
      • でかいバッファプールだと遅い
      • 別のセッションも止まる orz
        • dictionary lock => テーブルキャッシュ増やす
      • DEMO
  • TRUNCATE 対応
    • バッファプールインスタンスを増やす
    • テーブルキャッシュを増やす
    • 小さいテーブルならDELETEするべき
    • DROP => ReCreate でも可能
      • テーブル一瞬消えちゃってもいいなら
      • (Q&Aより) RENAME もアリかも
  • Server-side Prepared statement の都市伝説
    • SQLインジェクション防止のためにServer-side Prepared Statement 使うべし => △
    • Connector/J 5.1.8 以前にはバグが有った。
    • 今はバグがないのでどっちでもいい
    • Server-side Prepared statement は速い
      • ほとんど変わらない
      • MySQL にはプランキャッシュがないので、毎回オプティマイザが実行される
    • バグがある
      • しばらく使っているとコケる
      • Statement ID のロールオーバー。後勝ち。深刻
        • 上限超えると先頭に戻る
        • 実行できちゃうので結果おかしくなる
      • 遅くなることがある
      • 1サーバ内のStatement数に制限がある
        • デフォルト 16382
    • 使わない方がいい
      • 特に Java
      • コネクションプールで Statement ID がリセットされない
    • どうしても使いたい場合は cachePrepStmts=true と一緒に
      • Statement 保持されるので増えなくなる(？)
  • Load Average の都市伝説
    • Load Average 監視してれば安心
    • Load Average = 実行中のタスク + Run Queue のタスク + Uninterruptible Sleep
    • Uninterruptible Sleep
      • read, write の同期IO
      • 非同期IO ... AIO
        • io_submit ... 実行後すぐリターン
        • Callback か event で終了通知
        • Uninterruptible Sleep にならない
      • MySQL 5.5 から積極的に使用
        • バッファプールのフラッシュに使用
  • Flush 対応
    • InnoDB log ファイルの容量に注意せよ
    • MySQL 5.6, 5.7 の Adaptive Flush を使え
      • 5.5 だとあまりよくなかった
    • Load Average だけに頼るな
      • 体感に近いテストクエリを実行

MySQL 5.6, 5.7 性能比較

いとうひろゆき(@i_rethi)さん

※上は当日の発表資料から、追記・修正して 5/6 にアップされました*1。

  • 比較内容
    • 秒間接続数
    • sysbench の point select と oltp
    • tpcc-mysql
    • ほか
  • 環境
  • 秒間接続数
    • sysbench 0.4.12
    • ソースいじって接続・切断のみ行う
    • 結果
      • connection/sec
        • performance_schema ON でも性能低下が小さくなった
        • 全体的に 1.29 倍
  • sysbench の point select, oltp
    • sysbench 0.5
    • 結果
      • point select/sec
        • 全部メモリに載る状態だと 5.6 の方がちょっと速い
      • oltp read only
        • 5.6 の方がちょっと速い
      • oltp read write
        • これも同様
      • まとめ
        • 5.7より5.6の方がよい結果に
        • 5.7の方がよいという結果も見かけるのでCPUコア数で変わる？
        • performance schema の性能影響は 5.6, 5.7 とも軽微
        • AHI (Adaptive Hash Index) は有効な方がいい
  • tpcc-mysql
    • 使用ツール = tpcc mysql
    • 結果
      • 5.7 の方が 1.12倍ほど速い
      • AHI OFF の方が速い => 5.6, 5.7 とも
      • 5.6 の設定そのままなので、調整すると 5.7 もっとよくなるかも
  • まとめ
  • 今後
    • 2CPU 12Core24Thread でやってみたい
    • Facebook のやつ試したい
  • コメント
    • 5.7 では NUMA の処理が改善している

参考

State of The Dolphin

@RKajiyama さん

  • 閑話休題
    • MySQL Workbench 6.3 の新機能いくつか
      • Tale Data Export
        • JSON 選べるようになった
        • 気をつけないとCSVに戻るので注意
      • コード補完サジェストのハイライトがちょっと派手に
      • 証明書作る機能 - openssl 入れてれば使える
  • Percona Live に合わせて重要な発表をいくつか
  • 今年の1月から4月までの重要な発表
  • 5 of the 5 Top Websites are powered by MySQL
    • 7位か8位ぐらいに SQL Server のサイトがあるっぽい
  • MySQL Cluster 7.4 GA
    • 2月末に 7.4 リリースされた
    • 3つの改良
      • Performance 改善
        • 秒間2億件の NoSQL qps ... KVS として利用
        • 秒間2.5M SQL ベースの qps
      • Active-Active 冗長構成
      • Management
        • 再起動時間短くなった
  • MySQL Community 版の歴史
    • 5.6
    • 5.7 RC
      • Performance & Scalability
        • Sysbench Benchmark
          • 2x Faster than MySQL 5.6
        • Optimizer - New Cost Model
          • 設定可能になった。今までハードコードされてた。
      • Manageability
        • MySQL SYS Schema
          • 5.6 からあった。別途インストール必要だった
          • デフォルトになった
  • MySQL Community 5.7 RC
    • クエリ・リライト・プラグイン
    • MySQL 5.7 GIS - Boost.Geometry との統合
      • 独自コードの置き換え
    • InnoDB の改善
      • General Tablespace support
      • Resize the InnoDB Buffer Pool online
      • Additional Online ALTER TABLE support
      • 今まで再起動必要だったパラメータのいくつかがオンラインで変更可能になった
    • レプリケーション関連の改善
      • GTID 強化
        • On-line で GTID 有効化可能に
        • 5.6 であったクリティカルなタイミングで書き込みが失われ得る問題への対処
      • 準同期レプリの強化
      • 8-10x Faster slave throughput
        • マルチスレッドスレーブ
    • Multi-Source Replication
  • MySQL Labs
    • グループレプリケーション
      • シェアード・ナッシング型擬似同期レプリケーション
      • 更新はマルチ・マスタ型でどこでも可能
      • グループメンバーの管理と障害検知を自動化
        • サーバのフェールオーバ不要
    • Early Access Feature (EAF): Data Dictionary
    • EAF: InnoDB Compression
    • HTTP Plugin for MySQL
    • MySQL Binlog Events
      • 現時点でドキュメントなし
    • JSON データ型
      • JSON に適した SQL 関数
        • 今までも json_* という関数があった
        • jsn_* という関数にリネームした
          • jsn_array, jsn_search, ...
      • Index もつけれる
      • INSERT 時は JSON のバリデーションしてくれる
        • UPDATE のときはしてくれない => 壊れ得る
  • 告知
    • 今後のイベント等

*1:https://twitter.com/i_rethi/status/595877734343028736