ヤフー社内でやってるMySQLチューニングセミナー大公開

ヤフー社内でやってる MySQLチューニングセミナー大公開 2016/02/20 MyNA , JPUG 合同勉強会

ヤフーはどんなRDB使ってるの？ 2

Yahoo! JAPANのRDB環境 Oracle Database MySQL Percona • 11g RAC Enterprise Edition • MySQL 5.1 (RR,Mixed) Percona 5.5 (RR,Mixed) Percona 5.6 (RC,RBR,GTID) • 約200DB • 約500DB • サーバ 200台, Exadata もあるよ • サーバ 300台

4.

4

5.

6.

7.

8.

9.

10.

インデックスについて

11.

なぜ、インデックスで高速化するのか？ • インデックスはツリー構造 • データはソートされている • リーフノードに値と行のPKを格納 SELECT * FROM t WHERE col1 = ‘f’ インデックス対象 a-p r-z a d f p r t pk=1 pk=3 pk=2 pk=9 pk=4 pk=5 PK col1 time 1 a 11:15 2 f 01:10 3 d 03:01 ：：：

12.

13.

14.

なぜ効果が少ないのか？ SELECT * FROM t WHERE is_pk_even_num = 1 AND time <= 12:00 0 1 pk=1 pk=3 pk=5 ：：： pk=2 pk=4 pk=6 ：：： × 50万回インデックス対象 PK is_pk_even_num （PKが偶数か？） time 1 0 11:15 2 1 12:31 3 0 13:44 4 1 14:01 ：：： 1000000 1 20:01 • 半分までしか絞り込めない • インデックスを経由することによるオーバーヘッドが無視できない

15.

例外 SELECT * FROM t WHERE is_pk_1000multi = 1 AND time <= 12:00 インデックス対象 0 1 pk=1 pk=2 pk=3 ：：： pk=1000 pk=2000 pk=3000 ： PK is_pk_1000multi (PKが1000の倍数?) time 1 0 11:15 ： 0 12:31 1000 1 13:44 ：： 14:01 ：：： 1 20:01 × 1000000 1000回 • 分布が偏っていれば効果が大きくなる • 条件によって効果が異なる Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

16.

Left-Most-Index 以下のインデックスは同じ？インデックスA. CREATE INDEX idxA1 ON mytable (col1); CREATE INDEX idxA2 ON mytable (col2); インデックスB. CREATE INDEX idxB1 ON mytable (col1, col2); インデックスC. CREATE INDEX idxC1 ON mytable (col2, col1); 違います Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

17.

複合インデックスでは指定順が重要インデックスB. CREATE INDEX idxB1 ON mytable (col1, col2); a col1 a b～c col2 9 a pk=1 8 b pk=2 7 b pk=3 6 c pk=4 5 pk=5 インデックスC. CREATE INDEX idxC1 ON mytable (col2, col1); 7～9 5～6 col2 5 col1 c pk=1 6 b pk=4 7 b pk=2 8 a pk=3 9 a pk=5

18.

複合インデックスでは指定順が重要インデックスA. CREATE INDEX idxA1 ON mytable (col1); CREATE INDEX idxA2 ON mytable (col2); インデックスB. CREATE INDEX idxB1 ON mytable (col1, col2); インデックスC. CREATE INDEX idxC1 ON mytable (col2, col1); 絞り込み対象カラム → 1.WHRER col1 = ‘x’ 2.WHERE col2 = ‘x’ 3.WHERE col1 = ‘x’ AND col2 = ‘x’ インデックスA idxA1 が利用される idxA2 が利用される idxA1, idxA2 どちらかが利用される。 ※ インデックスB idxB1 が利用されるインデックスは利用されない idxB1 が利用される。 idxA1, idxA2より高速。インデックスC インデックスは利用されない idxC1 が利用される idxC1が利用される。 idxA1, idxA2より高速。 ※ 場合によってはインデックスマージ

19.

20.

SQLチューニング手順

21.

22.

23.

重いSQLの洗い出し方 STEP1 スロークエリーログに全SQLを記録 $ sudo rm mysqld-slow.log $ mysql –u user_name –p mysql> SET GLOBAL long_query_time = 0; mysql> SET GLOBAL log_slow_verbosity=full; mysql> FLUSH LOGS; スキャンした行数実行時間 # User@Host: user[user] @ [1.2.3.4] # Query_time: 0.067361 Lock_time: 0.000030 Rows_sent: 16 SET timestamp=1346772142; SELECT zpam.zip_cd FROM zip_area_mast zpam WHERE zpam.point_cd = 11276 AND zpam.del_flag = 0; : テーブルロックした時間行ロックした時間はカウントされていので注意！ Rows_examined: 121031 結果の行数

24.

重いSQLの洗い出し方 STEP2 • Percona Tookit の pt-query-digest で集計 $ sudo pt-query-digest --group-by fingerprint¥ --order-by Query_time:sum ¥ --since '2015-06-14 00:00:00' --until '2015-06-14 23:59:59' ¥ mysqld-slow.log 合計実行時間でソートオプションソート条件 Query_time:avg 平均実行時間 Query_time:sum 合計実行時間 InnoDB_IO_r_bytes:sum バッファプールにヒットせず、DISKから読み取ったデータ量 Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

25.

集計結果 # Query 5: 39.92 QPS, 229.19kx concurrency, ID 0x72BFE142E32896BD at byte 88442 # Time range: 2015-10-29 23:00:00 to 23:59:59 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= =======平均実行時間 ======= ======= 合計実行回数 # Count 31 143676 （1クエリあたり） # Exec time 11 660s 150us 623ms 5ms 18ms 10ms 490us # Lock time 20 13s 32us 5ms 91us 113us 30us 89us レスポンス行数（1クエリあたり） # Rows sent 0 264.02k 0 4 1.88 2.90 0.69 1.96 # Rows examine 0 264.02k 0 4 1.88 2.90 0.69 1.96 処理過程で読み込んだ行数 # Rows affecte 0 0 0 0 0 0 0 0 （1クエリあたり） # Rows read 0 264.02k 0 4 1.88 2.90 0.69 1.96 # Bytes sent 0 16.80M 83 171 122.61 143.84 17.33 124.25 # Merge passes 0 0 0 0 0 0 0 0 # Tmp tables 0 0 0 0 0 0 0 0 # Tmp disk tbl 0 0 0 0 0 0 0 0 # Tmp tbl size 0 0 0 0 0 0 0 0 # Query size 18 26.14M 148 271 190.81 246.02 51.01 143.84 キャッシュヒットせずに # InnoDB: DISKから読んだバイト数 # IO r bytes 9 786.66M 0 80.00k 5.61k 15.96k 9.17k 0 # IO r ops 9 49.17k 0 5 0.35 0.99 0.57 0 # IO r wait 14 569s 0 620ms 4ms 17ms 10ms クエリ0 ＜省略＞ SELECT topic_id FROM weather WHERE (consumeruri = ‘abc') AND (consumeruri_type = 'android')¥G

26.

27.

実行計画の確認 • 実行計画＝クエリの処理の流れ • 「EXPLAIN」をクエリの先頭に付ける • 更新系クエリはSELECTに書き換え UPDATE t SET col = newvalue WHERE condition = ‘x’; EXPLAIN SELECT col FROM t WHERE condition = ‘x’; Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

28.

29.

30.

チューニング例

31.

代表的なチューニング例 1. type=ALL または type=index で rows が大きい 2. Extra に Using temporary; Using filesort でrowsが大きい 3. select_type が DEPENDENT SUBQUERY 4. JOINにおいて2つ目以降のExtraにUsing whereが出力さていて rows が大きい 5. 大量更新 6. データ削除 Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

32.

代表的なチューニング例 1. type=ALL または type=index で rows が大きい 2. Extra に Using temporary; Using filesort でrowsが大きい 3. select_type が DEPENDENT SUBQUERY 4. JOINにおいて2つ目以降のExtraにUsing whereが出力さていて rows が大きい 5. 大量更新 6. データ削除 Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

33.

[beta]

【1.フルスキャン】 TYPE=ALL
mysql> EXPLAIN SELECT push_id FROM push_info
111万行に対し
WHERE deleted=1 AND datediff(now(), mod_date) > '180';
て絞り込み
+--+-----------+---------+----+-------------+----+-------+----+-------+--------------------------+
|id|select_type|table
|type|possible_keys|key |key_len|ref |rows |Extra
|
+--+-----------+---------+----+-------------+----+-------+----+-------+--------------------------+
|1 |SIMPLE
|push_info|ALL |NULL
|NULL|NULL |NULL|1115182|Using where;
|
+--+-----------+---------+----+-------------+----+-------+----+-------+--------------------------+

全部読み込み

行数多い

mysql> SHOW INDEX FROM push_info;
+-----------+------------+--------------------+--+----------+--------+------+------------+
| Table
| Non_unique | Key_name
|～| Sub_part | Packed | Null | Index_type |
+-----------+------------+--------------------+--+----------+--------+------+------------+
| push_info |
0 | PRIMARY
|～|
NULL | NULL |
| BTREE
|
+-----------+------------+--------------------+--+----------+--------+------+------------+

•
•
•

mod_dateとdeletedにインデックスを貼るべき
関数を適用した場合インデックスは効かない
すべての行に対してdatediffを実行してるのも×
• mod_date < date_sub(NOW(), INTERVAL 180 DAY) と書くべき

34.

インデックスが効かない条件 Extra 関数式否定構文 WHERE datediff(now(), mod_date) > '180' WHERE col1 / 2 = 0 WHERE col1 != 3 LIKE検索 ※ 前方一致除く WHERE col1 LIKE ‘%string%’ WHERE col1 LIKE ‘%string’ Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

35.

[beta]

【1.フルスキャン】チューニング結果
mysql> CREATE INDEX idx01 ON push_info(deleted, mod_date);

mysql> EXPLAIN SELECT push_id FROM push_info
WHERE deleted=1 AND mod_date < date_sub(NOW(), INTERVAL 180 DAY);
+----+-------------+-----------+-------+-------+------+------+--------------------------+
| id | select_type | table
| type | key | ref | rows | Extra
|
+----+-------------+-----------+-------+-------+------+------+--------------------------+
| 1 | SIMPLE
| push_info | range | idx01 | NULL | 10 |
Using index |
+----+-------------+-----------+-------+-------+------+------+--------------------------+

インデックスの
範囲読み込み

行数が10行

インデックスだ
けで解決でき
るクエリ

• Before: 380msec → After: 0msec

Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

36.

代表的なチューニング例 1. type=ALL または type=index で rows が大きい 2. Extra に Using temporary; Using filesort でrowsが大きい 3. select_type が DEPENDENT SUBQUERY 4. JOINにおいて2つ目以降のExtraにUsing whereが出力さていて rows が大きい 5. 大量更新 6. データ削除 Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

37.

38.

[beta]

【2.ソート】チューニング結果
mysql> CREATE INDEX OM_OTM ON order_master (order_time);

mysql> EXPLAIN SELECT order_time,seller_id,image_id,item_id,＜省略＞
FROM order_master WHERE is_hidden_page = '0'
ORDER BY order_time DESC LIMIT 20;
+--+-------------+--------------+-----+---------------+--------+------+-------------+
|id| select_type | table
|type | possible_keys | key
| rows | Extra
|
+--+-------------+--------------+-----+---------------+--------+------+-------------+
| 1| SIMPLE
| order_master |index| NULL
| OM_OTM |
20 | Using where |
+--+-------------+--------------+-----+---------------+--------+------+-------------+

インデックス利用

20個だけ取る

ソートがなくなる

• インデックスはソート済みのため、ソートが不要
• 上位20件だけ取ったら、処理を終了
• Before: 4300sec → After: 0sec
Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

39.

40.

41.

代表的なチューニング例 1. type=ALL または type=index で rows が大きい 2. Extra に Using temporary; Using filesort でrowsが大きい 3. select_type が DEPENDENT SUBQUERY 4. JOINにおいて2つ目以降のExtraにUsing whereが出力さていて rows が大きい 5. 大量更新 6. データ削除 Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

42.

43.

【3.相関サブクエリ】期待する動作最初に実行される mysql> EXPLAIN SELECT team_name ことを期待 FROM team WHERE team_id IN (SELECT team_id FROM member WHERE skill IN ("C")); ①skill=Cを探す ②team_id=1を探す team_id team_name team_id member_name skill 1 Team1 1 Yahoo Taro C 2 Team2 1 Yahoo Jiro Python 3 Team3 2 Yahoo Hanako Ruby 2 Yahoo Saburo PHP 3 Yahoo Sirou Perl • memberテーブルを1回読み込む • teamテーブルを1回読み込む • 合計＝5 + 3 = 8行の読み込みを期待する

44.

【3.相関サブクエリ】実際の動き mysql> EXPLAIN SELECT team_name FROM team WHERE team_id IN (SELECT team_id FROM member WHERE skill IN ("C")); team_id =1 AND skill=C のレコードを探す team_id member_name skill team_id team_name 1 Yahoo Taro C 1 Team1 1 Yahoo Jiro Python 2 Team2 2 Yahoo Hanako Ruby 3 Team3 2 Yahoo Saburo PHP 3 Yahoo Sirou Perl • 合計＝ 3 + 3 * 5 = 18行の読み込み • 実行計画の「PRIMARY」は外部表（親）を示す • なぜ、期待どうりに動かないのか？ • MySQLの仕様です。。。（5.6で改善されます）

45.

46.

47.

代表的なチューニング例 1. type=ALL または type=index で rows が大きい 2. Extra に Using temporary; Using filesort でrowsが大きい 3. select_type が DEPENDENT SUBQUERY 4. JOINにおいて2つ目以降のExtraにUsing whereが出力さていて rows が大きい 5. 大量更新 6. データ削除 Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

48.

49.

50.

51.

【4.JOIN】チューニング • ループ回数を少なくする • 結合条件以外の条件にマッチする件数をテーブルごとにカウント • 件数が少ないほうを外部表にする mysql> EXPLAIN SELECT prefecture FROM prefecture INNER JOIN people USING(pref_id) WHERE people.age >= 120; mysql> SELECT COUNT(*) FROM prefecture; +----------+ | COUNT(*) | +----------+ | 47 | +----------+ 1 row in set (0.00 sec) mysql> SELECT COUNT(*) FROM people where age >=120; +----------+ | COUNT(*) | +----------+ | 3 | +----------+ 1 row in set (0.70 sec) Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

52.

【4.JOIN】チューニング・狙うべき動作 ■ people - age_idx pref_id prefecture 1 Hokkaido 2 ～～～ Aomori ：～～～ 41 Saga ■ people pref_id name age 1 Tokugawa 121 2 Toyotomi 125 ～～～～～～～～～～～～ 41 Akechi 130 3 Inoue 90 ※ MySQLはクラスタインデックスのため主キー＝テーブルで実際はツリーです。

53.

54.

代表的なチューニング例 1. type=ALL または type=index で rows が大きい 2. Extra に Using temporary; Using filesort でrowsが大きい 3. select_type が DEPENDENT SUBQUERY 4. JOINにおいて2つ目以降のExtraにUsing whereが出力さていて rows が大きい 5. 大量更新 6. データ削除

55.

【6.大量の更新】 INSERT INTO quote_real_info (ask10_flag, ask10_order, ask10_price, … ＜170カラムぐらい＞ VALUES (NULL, NULL, '1150', '2012-06-13 15:10:00', '0', '900', …）; INSERT INTO quote_real_info (ask10_flag, ask10_order, ask10_price, … ＜170カラムぐらい＞ VALUES (NULL, NULL, '1150', '2012-06-13 15:10:00', '0', '900', …）; ：：バルクインサートにより性能向上 INSERT INTO quote_real_info (ask10_flag, ask10_order, ask10_price, … ＜170カラムぐらい＞ VALUES (NULL, NULL, '1150’, '2012-06-13 15:10:00’, '0’, '900’, …）, (NULL, NULL, '1152’, '2012-06-13 15:10:01’, '0’, '300', …）, (NULL, NULL, '1153', '2012-06-13 15:10:00', '0', '100', …）, (NULL, NULL, '1250', '2012-06-13 15:10:01', '0', '920', …）, (NULL, NULL, '1151', '2012-06-13 15:10:00', '0', '500', …）, 5万レコード (NULL, NULL, '1155', '2012-06-13 15:10:01', '0', '150', …）); /sec

56.

【6.大量の更新】チューニング方法 • RDBは通常コミット毎にDiskに書き込む •× Auto-Commit • バルクインサートはサーバ・クライアント間の通信回数を減らせるため、より高速高速 • 1件1件コミット • 複数件コミット • バルクインサート Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

57.

代表的なチューニング例 1. type=ALL または type=index で rows が大きい 2. Extra に Using temporary; Using filesort でrowsが大きい 3. select_type が DEPENDENT SUBQUERY 4. JOINにおいて2つ目以降のExtraにUsing whereが出力さていて rows が大きい 5. 大量更新 6. データ削除 Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

58.

【7.テーブルの削除】 DELETE FROM push_message； TRUNCATE TABLE push_message ； • TRUNCATE = DROP + CREATE • 注意：AUTO_INCREMENTがリセット • 条件を指定したい • パーティション化＋ [DROP|TRUNCATE] PARTITION Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

59.

チューニングの難しいクエリ

60.

必要なデータが大きいもの SELECT COUNT(*) FROM access_log; SELECT SUM(a) FROM access_log WHERE data BETWEEN ‘1995-01-01’ AND NOW(); 必要なデータが大きいものは遅い • • • MySQLは1セッション＝1スレッド＝１CPU 作りから見直す必要がある例）集計テーブルを作り更新時に同時にカウントアップする Copyright (C) 2016 Yahoo Japan Corporation. All Rights Reserved. 無断引用・転載禁止

61.

まとめ

62.

63.

おしらせ

64.

65.

ありがとうございました

ヤフー社内でやってるMySQLチューニングセミナー大公開

Yahoo!デベロッパーネットワーク

関連スライド

深層学習による自然言語処理入門: word2vecからBERT, GPT-3まで

ゼロから始める転移学習

ヤフーにおける WebAuthn と Passkey の UX の紹介と考察 #idcon #fidcon

OpenID Connectとネイティブアプリを取り巻く仕様と動向 Yahoo! JAPANの取り組み #openid #openid_tokyo

運用業務とスクラムは本当に組み合わせにくいのか︖運用業務が大半を占めるプロダクト開発での試行錯誤

ヤフーのオンプレ機械学習基盤AIPFについて #ml_kubernetes

各ページのテキスト