![Percona Server + log_slow_verbosity = query_plan
# Time: 130601 8:01:06.058915
# User@Host: root[root] @ localhost [] Id: 42
# Schema: imdb Last_errno: 0 Killed: 0
# Query_time: 7.725616 Lock_time: 0.000328 Rows_sent: 4 Rows_e
xamined: 1543720 Rows_affected: 0
# Bytes_sent: 272 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_s
izes: 0
# QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: No Tmp_t
able_on_disk: No
# Filesort: No Filesort_on_disk: No Merge_passes: 0
85/141](https://image.slidesharecdn.com/mysql-160125081954/85/MySQL-86-320.jpg)
雑なMySQLパフォーマンスチューニング
- 2. ＼こんにちは／ yoku0825＠とある企業のDBA の中の⼈ オラクれない- ポスグれない- マイエスキューエる- Twitter: @yoku0825 Blog: ⽇々の覚書 Oracle ACE Details MySQL 5.7 Community Contributor Award 1/141
- 4. ちなみに 3/141
- 5. というわけで安⼼ して 地雷友達 5.7 使っていいです よ :) 4/141
- 6. クエリーのライフサイクル client connection_handling parser optimizer executor handler storage_engine application mysqld 5/141
- 15. Executor オプティマイザーから渡された実⾏計画の通りにHandlerを 叩く Handlerから戻ってきた結果を使って実⾏計画の残りを実⾏ Using where; Using filesort; Using temporary; はコ イツが頑張ってる証拠 - スローログ, バイナリーログ 14/141
- 20. クエリーのライフサイクル client connection_handling parser optimizer executor handler storage_engine application mysqld 19/141
- 21. どこが 遅 い︖ 20/141
- 22. どこが遅い︖ connection_handling parser optimizer executor handler storage_engine mysqld だいたいExecutor たまにStorage Engine 21/141
- 26. EXPLAIN mysql> EXPLAIN SELECT * FROM table_1 a JOIN `table_2` s ON a.user_id=s.`user_id` AND s.site_i d=120 WHERE app_id=8250G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: a type: ref possible_keys: PRIMARY,ix_table_1,ix2_table_2,ix3_table_1,idx_table_1_06,idx_table_1_07,idx_t able_1_09 key: idx_table_1_06 key_len: 4 ref: const rows: 13496 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: s type: ref possible_keys: idx_table_2_02,idx_table_2_03 key: idx_table_2_02 key_len: 4 ref: xxx.a.user_id rows: 1 Extra: Using where 2 rows in set (0.00 sec) 25/141
- 28. type ALLがダメなんて良く⾔う ALL .. テーブルスキャン- ref .. 非ユニークキーによる選択- range .. レンジスキャン- 27/141
- 29. type どの⽅法でアクセスさせるのが最速だと オプティマイザー が ⾒積もった結果 特にInnoDBはクラスターインデックスなので、下⼿にセ カンダリーインデックスを使うよりも本当に速いケース もある - MyISAMもスキャンに強いので、本当に速いケースがあ る - ダメなのはALLじゃなく、本当の最適なアクセスと違う時 rows, key_lenが判断の助けになる- 28/141
- 31. InnoDBの統計情報 InnoDBの統計情報は テーブル全体から⼀部の情報をランダ ムに選び出し、全体を推測する サンプリング統計 5.5とそれ以前では1インデックスあたり8ページ（= 16kB * 8ページで128kB）でハードコード - 5.6とそれ以降では1インデックスあたりデフォルト20ペ ージ（innodb_stats_persistent_sample_pages） CREATE TABLE, ALTER TABLEでテーブル単位でページ数 を指定することも可能 - 30/141
- 33. LIMIT句のハンドリング LIMIT句はORDER BY句の処理が終わってから実⾏される ORDER BY句の処理は最終的にExecutorで終端するので- つまりLIMIT句もExecutorの処理の範疇- オプティマイザーは⾃分より後のExecutorの動作を正しく ⾒積もれない なので、ORDER BY .. LIMIT ..で効率的にORDER BY狙い のキーが使える場合でも、選んでくれないケースがある - 5.7でLIMITありの場合のオプティマイズが効くようにな っている - 32/141
- 34. Extra: Using where; Using filesort; ExecutorがWHEREをフィルターしている、ExecutorがORDER BYのソートをしている インデックスを使って⾼速化できるのは WHERE, ORDER BY, GROUP BY, select_list どうすればいいかと⾔われると、「インデックス使う」とし か⾔いようがない 33/141
- 36. Query As A Card 100枚のトランプが裏返して積まれた⼭があります 何組かのトランプを混ぜたもので何が何枚⼊ってるかはわか りません 35/141
- 39. これがtype: ALLかつExtra: Using where トランプをめくる .. ⾏をフェッチする スートと番号を⾒る .. Executorでのフィルター処理 38/141
- 41. どうするかというと 右⼿で ⼭の⼀番上のトランプをめくる1. スートと番号を⾒る2. スペードだったらそのカードを 左⼿に 残しておく3. スペードじゃなかったらどっかに置いておく4. ⼭の全てのカードに対して2, 3, 4を繰り返す5. ⼭から全てのスペードが出揃ったら、⼿元のカードをK〜A まで順番に並べる 6. 40/141
- 42. これがtype: ALLかつExtra: Using where; Using filesort トランプをめくる .. ⾏をフェッチする トランプをめくった 右⼿ .. リードバッファー スートを⾒る .. Executorでのフィルター処理 スートが⼀致した時にカードを⼊れた 左⼿ .. ソートバッフ ァー ⼿元のカードを並べ替える .. Executorによるクイックソー ト 41/141
- 43. KEY(suit) suit 上から club 3,5,13,14,16,18,19,20,37,44,45,55,57, 63,66,81,86,87,91,92,98,99,100 diamond 1,4,15,17,21,22,25,27,31,34,39,50,56, 58,60,65,68,70,72,73,78,97 heart 6,7,10,12,24,26,32,36,38,40,41,43,48, 51,52,61,62,67,74,75,84,85,88,89,90,9 3,95 spade 2,8,9,11,23,28,29,30,33,35,42,46,47,4 9,53,54,59,64,69,71,76,77,79,80,82,8 3,94,96 42/141
- 44. ここからスペードのAを探す suit 上から club 3,5,13,14,16,18,19,20,37,44,45,55,57, 63,66,81,86,87,91,92,98,99,100 diamond 1,4,15,17,21,22,25,27,31,34,39,50,56, 58,60,65,68,70,72,73,78,97 heart 6,7,10,12,24,26,32,36,38,40,41,43,48, 51,52,61,62,67,74,75,84,85,88,89,90,9 3,95 spade 2,8,9,11,23,28,29,30,33,35,42,46,47,4 9,53,54,59,64,69,71,76,77,79,80,82,8 3,94,96 43/141
- 45. スペードのAを探す トランプの上から2, 8, 9, .. 枚目を順番にめくり1. 番号を⾒る（スペードのものだけめくってるんだから、スー トは⾒る必要がない） 2. Aだったらそのカードを残しておく3. Aじゃなかったらどっかに置いておく4. 44/141
- 46. これがtype: ref, ref: const, Extra: Using where インデックス上からスペードを探して「上から2番目」の情 報を得る 1. 上から2番目のカードをめくる .. ここまでha̲index̲read2. Aかどうかを判定する .. Executorによるフィルター3. インデックス上の全てのリーフをさらうまで繰り返す .. ha̲index̲nextがHA̲ERR̲END̲OF̲FILEを返すまでルー プ 4. 45/141
- 47. ここからスペードを全て探し出して、K〜Aの順で並べる suit 上から club 3,5,13,14,16,18,19,20,37,44,45,55,57, 63,66,81,86,87,91,92,98,99,100 diamond 1,4,15,17,21,22,25,27,31,34,39,50,56, 58,60,65,68,70,72,73,78,97 heart 6,7,10,12,24,26,32,36,38,40,41,43,48, 51,52,61,62,67,74,75,84,85,88,89,90,9 3,95 spade 2,8,9,11,23,28,29,30,33,35,42,46,47,4 9,53,54,59,64,69,71,76,77,79,80,82,8 3,94,96 46/141
- 48. スペードを全て探し出して、K〜Aの順で並べる トランプの上から2, 8, 9, .. 枚目を順番にめくり 左⼿ に取 る 1. 番号を⾒て、並べ替える2. 47/141
- 49. type: ref, ref: const, Extra: Using filesort インデックス上からスペードを探して「上から2番目」の情 報を得る 1. 上から2番目のカードを 左⼿ に .. ha̲index̲readしてソー トバッファーに詰める 2. 番号を⾒て並べ替える .. Executorによるクイックソート3. 48/141
- 50. KEY(suit, number) suit number 上から club 2 13,20 club 3 18,45 .. .. .. heart 13 85 spade 1 2,23,47,76 spade 2 77 spade 3 83 .. .. .. spade 11 11,33,35,96 spade 12 49,59,80 49/141
- 51. ここからスペードのAを探す suit number 上から club 2 13,20 club 3 18,45 .. .. .. heart 13 85 spade 1 2,23,47,76 spade 2 77 spade 3 83 .. .. .. spade 11 11,33,35,96 spade 12 49,59,80 50/141
- 52. スペードのAを探す トランプの上から2, 23, 47, 76枚目を順番にめくる（おし まい︕） インデックスの情報だけで完結するため、⼭を全て⼿繰 る（不要なものをフェッチする）必要がない 1. 51/141
- 53. type: ref, ref: const, const インデックス上から(スペード, 1)を探して「上から2番目」 の情報を得る 1. 上から2番目のカードを取る .. ha̲index̲read2. インデックス上の全てのリーフをさらうまで繰り返す .. ha̲index̲nextがHA̲ERR̲END̲OF̲FILEを返すまでルー プ 3. 52/141
- 54. スペードを全て探し出して、K〜Aの順で並べる suit number 上から club 2 13,20 club 3 18,45 .. .. .. heart 13 85 spade 1 2,23,47,76 spade 2 77 spade 3 83 .. .. .. spade 11 11,33,35,96 spade 12 49,59,80 Kなかった。。 53/141
- 55. スペードを全て探し出して、K〜Aの順で並べる トランプの上から49, 59, 80枚目を⼿に取る1. トランプの上から11, 33, 35, 96枚目を⼿に取る2. 全てのスペードのカードぶん1, 2を繰り返す3. 54/141
- 56. type: ref, ref: const, const, Extra: Using index インデックス上から(スペード, 最後のノード)を探して「上 から49番目」の情報を得る 1. 上から49番目のカードを取る .. ha̲index̲read2. インデックス上の全てのリーフをさらうまで繰り返す .. ha̲index̲prevがHA̲ERR̲END̲OF̲FILEを返すまでルー プ 3. 55/141
- 57. :(;ﾞﾟʼωﾟʼ): Extra: Using whereが消えなかった たまに消えない たぶんオプティマイザーのバグ（ORDER BYと混じると Executorが仕事してなくてもUsing whereが消えない） 56/141
- 58. KEY(number) number 上から 1 2,22,23,26,47,76,93 2 4,13,20,25,75,77 .. .. 12 16,31,41,49,59,68,73,80,91 13 19,58,85 57/141
- 59. ここからスペードのAを探す インデックスからAを探して、「2, 22, 23, ..枚目」の情報 を得て 1. ひっくり返してスペードかどうかを判定する2. Aのリーフが全部終わったら終わり3. type: ref, ref: const, Extra: Using where 58/141
- 60. スペードを全て探し出して、K〜Aの順で並べる インデックスからKを探して19, 58, 85枚目を⼿に取る1. スペードなら⼿元に、そうでなければよける2. カードの⼭全てに対して1, 2を繰り返す3. 59/141
- 61. type: index, Extra: Using where ただしこれはLIMITがない場合はテーブルスキャンを選んで た 5.6の場合、LIMITがあってもテーブルスキャンを選ぶ- そりゃ、結局全部のカードを裏返してるから、頭から全部ひ っくり返した⽅が速い LIMITがある= 「枚数が揃ったらそこでそれ以降の結果セ ットは考慮する必要がない」 - この条件にマッチした場合、フェッチする⾏数を⼤幅に 減らせる - 60/141
- 62. インデックスのイメージ インデックスは ソート済みのデータの部分複製 （異論は認め る） root club spade 2 3 2 13 20 18 45 77 B+Treeじゃないけど取り敢えずそこまで気にしない 61/141
- 64. InnoDBのクラスターインデックスイメージ root club spade 3 5 13 14 2 23 root p3 p5 p13 p14 p2 p23 Club-A Club-4 Club-2 Club-5 Spade-A Spade-A Secondary Index Clustered Index 63/141
- 65. InnoDBでWHERE狙いのキー SELECT * FROM card USING(idx_suit) WHERE suit= 'club' ORDER BY number LIMIT 1; root club spade 3 5 13 14 2 23 root p3 p5 p13 p14 p2 p23 Club-A Club-4 Club-2 Club-5 Spade-A Spade-A Secondary Index Clustered Index 64/141
- 66. InnoDBでWHERE狙いのキー SELECT * FROM card USING(idx_suit) WHERE suit= 'club' ORDER BY number LIMIT 1; root club spade 3 5 13 14 2 23 root p3 p5 p13 p14 p2 p23 Club-A Club-4 Club-2 Club-5 Spade-A Spade-A Secondary Index Clustered Index 65/141
- 67. InnoDBでWHERE狙いのキー SELECT * FROM card USING(idx_suit) WHERE suit= 'club' ORDER BY number LIMIT 1; root club spade 3 5 13 14 2 23 root p3 p5 p13 p14 p2 p23 Club-A 1 Club-4 Club-2 Club-5 Spade-A Spade-A Secondary Index Clustered Index 66/141
- 68. InnoDBでWHERE狙いのキー SELECT * FROM card USING(idx_suit) WHERE suit= 'club' ORDER BY number LIMIT 1; root club spade 3 5 13 14 2 23 root p3 p5 p13 p14 p2 p23 Club-A 1 Club-4 Club-2 Club-5 Spade-A Spade-A Secondary Index Clustered Index 67/141
- 69. InnoDBでWHERE狙いのキー SELECT * FROM card USING(idx_suit) WHERE suit= 'club' ORDER BY number LIMIT 1; root club spade 3 5 13 14 2 23 root p3 p5 p13 p14 p2 p23 Club-A 1 Club-4 2 Club-2 Club-5 Spade-A Spade-A Secondary Index Clustered Index 68/141
- 70. InnoDBでWHERE狙いのキー SELECT * FROM card USING(idx_suit) WHERE suit= 'club' ORDER BY number LIMIT 1; root club spade 3 5 13 14 2 23 root p3 p5 p2 p23 p13 p14 Club-A 1 Club-4 2 Club-2 3 Club-5 4 Spade-A Spade-A Secondary Index Clustered Index フェッチした⾏をクイックソートしてからLIMITを評価 69/141
- 71. InnoDBでORDER BY狙いのキー SELECT * FROM card USING(idx_number) WHERE suit= 'club' ORDER BY number LIMIT 1; root A 2 2 22 23 26 4 13 root p2 p22 p23 p26 p4 p13 Spade-A Diamond-A Spade-A Heart-A Diamond-2 Club-2 Secondary Index Clustered Index 70/141
- 72. InnoDBでORDER BY狙いのキー SELECT * FROM card USING(idx_number) WHERE suit= 'club' ORDER BY number LIMIT 1; root A 2 2 22 23 26 4 13 root p2 p22 p23 p26 p4 p13 Spade-A 1 Diamond-A 2 Spade-A 3 Heart-A 4 Diamond-2 5 Club-2 6 Secondary Index Clustered Index LIMIT件数が早めに⾒つかれば勝ち、そうでなければ負け。 ところでこれ、 何かに似てると思いません︖ 71/141
- 73. InnoDBでcovering index SELECT * FROM card USING(idx_number) WHERE suit= 'club' ORDER BY number LIMIT 1; root club spade 2 3 A 13 20 18 45 2 23 root p13 p20 p18 p45 p2 p23 Club-2 1 Club-2 Club-3 Club-3 Spade-A Spade-A Secondary Index Clustered Index 72/141
- 74. WHERE狙いのキー, ORDER BY狙いのキー WHEREとORDER BY .. LIMITを同時にカバーできるインデック スが作成できない時に WHEREをストレージエンジンに任せてフェッチする⾏を減 らしてファイルソートをExecutorにやらせるか - ORDER BY .. LIMITをストレージエンジンに任せて ExecutorにフィルターさせつつLIMITでループを抜けるの を期待するか - 73/141
- 75. WHERE狙いのキー, ORDER BY狙いのキー 「JOINが遅い」はJOINが遅いんじゃなくてJOINの参照表で ソートしようとしてExecutorがソートしちゃってるケース あとはオプティマイザーにバグがあって、駆動表を Executorがフィルターして参照表の参照回数が減る可能 性を考慮していない。 - See also, http://www.slideshare.net/yoku0825/ whereorder-by 74/141
- 77. Executorが必要以上の仕事をしていないか GROUP BYのケースを除いて、Rows_sent= Rows_examinedが⼀ 番良い MySQLにはヒストグラム統計がないので、WHERE狙いと ORDER BY狙いのどちらが本当に速いのかMySQLは知らない （憶えてない） 76/141
- 78. Executorが必要以上の仕事をしていないか ⼈間は得てして 正しい 統計情報を知っているケースがある サービスの特性（⼥性は少ないからWHERE gender= 'F' ORDER BY birthdayはWHEREで狙った⽅がいいだろうし、 WHERE gender= 'M' ORDER BY birthdayはORDER BY狙いの ⽅が良いだろう、とか） - サンプリングでない実際の分布（InnoDBの統計情報は飽 くまでサンプリングなのでたまに踏み外す） - LIMITの考慮（5.6とそれ以前のMySQLはORDER BY .. LIMITの時でも積極的にWHEREを狙う） - 77/141
- 79. オプティマイザーの強化 MySQL 5.6 Multi-Range Read, Batched Key Access(デフォルトOFF), Semi-JOIN, Materialized MySQL 5.7 ORDER BY .. LIMIT Optimization, Configurable cost Model, JOIN optimization 78/141
- 81. クエリーの最適化(5.5) mysql55> EXPLAIN SELECT * FROM Country WHERE Continent = 'Asia' AND Code IN io n / 2); +----+--------------------+---------+----------------+---------------+------ -------+---------+------+------+-------------+ e y | key_len | ref | rows | Extra | +----+--------------------+---------+----------------+---------------+------ -------+---------+------+------+-------------+ | 1 | PRIMARY | Country | ALL | NULL | NUL L | NULL | NULL | 226 | Using where | | 2 | DEPENDENT SUBQUERY | City | index_subquery | CountryCode | Count ryCode | 3 | func | 7 | Using where | +----+--------------------+---------+----------------+---------------+------ -------+---------+------+------+-------------+ 2 rows in set (0.02 sec) 80/141
- 82. クエリーの最適化(5.7) mysql57> EXPLAIN SELECT * FROM Country WHERE Continent = 'Asia' AND Code IN -> (SELECT CountryCode FROM City WHERE Population > Country.Population / 2); +----+-------------+---------+------------+------+---------------+-------------+ ---------+-----------------+------+----------+---------------------------------- + | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extr a | +----+-------------+---------+------------+------+---------------+-------------+ ---------+-----------------+------+----------+---------------------------------- + | 1 | SIMPLE | Country | NULL | ALL | PRIMARY | NULL | NULL | NULL | 239 | 14.29 | Using wher e | | 1 | SIMPLE | City | NULL | ref | CountryCode | CountryCode | 3 | d1.Country.Code | 18 | 33.33 | Using where; FirstMatch(Country ) | +----+-------------+---------+------------+------+---------------+-------------+ ---------+-----------------+------+----------+---------------------------------- + 2 rows in set, 2 warnings (0.00 sec) 81/141
- 83. 古いMySQLに慣れていると、⼾惑うことも 「MySQLはこう 間違うだろう」という予測と 違う間違え⽅ をする EXPLAIN EXTENDED, SHOW WARNINGSでオプティマイザーがクエ リーをどう書き換えたか表⽰できる mysql57> show warningsG *************************** 2. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select `d1`.`Country`.`Code` AS `Code`,`d1`.`Country`.`Name` AS `Name `,`d1`.`Country`.`Continent` AS `Continent`,`d1`.`Country`.`Region` AS `Region`,`d1`.`Country `.`SurfaceArea` AS `SurfaceArea`,`d1`.`Country`.`IndepYear` AS `IndepYear`,`d1`.`Country`.`Po pulation` AS `Population`,`d1`.`Country`.`LifeExpectancy` AS `LifeExpectancy`,`d1`.`Country`. `GNP` AS `GNP`,`d1`.`Country`.`GNPOld` AS `GNPOld`,`d1`.`Country`.`LocalName` AS `LocalName`, `d1`.`Country`.`GovernmentForm` AS `GovernmentForm`,`d1`.`Country`.`HeadOfState` AS `HeadOfSt ate`,`d1`.`Country`.`Capital` AS `Capital`,`d1`.`Country`.`Code2` AS `Code2` from `d1`.`Count ry` semi join (`d1`.`City`) where ((`d1`.`City`.`CountryCode` = `d1`.`Country`.`Code`) and (` d1`.`Country`.`Continent` = 'Asia') and (`d1`.`City`.`Population` > (`d1`.`Country`.`Populati on` / 2))) 2 rows in set (0.00 sec) 82/141
- 84. sysスキーマ performance̲schema, information̲schemaのビューと、 performance̲schema関連の設定をゴニョるプロシージャ の詰め合わせ なのでperformance_schema= ONが⼤前提- 合わせて使いたいinnodb_monitor_enable= all- 5.6の場合はgitリポジトリーからcloneしてsqlスクリプト 実⾏ - 83/141
- 85. sys.statement̲analysis query full̲scanしてれば* exec̲count err̲count, warn̲count *̲latency rows̲sent̲avg, rows̲examined̲avg tmp̲tables, sort̲merge̲passes 84/141
- 86. Percona Server + log_slow_verbosity = query_plan # Time: 130601 8:01:06.058915 # User@Host: root[root] @ localhost [] Id: 42 # Schema: imdb Last_errno: 0 Killed: 0 # Query_time: 7.725616 Lock_time: 0.000328 Rows_sent: 4 Rows_e xamined: 1543720 Rows_affected: 0 # Bytes_sent: 272 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_s izes: 0 # QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: No Tmp_t able_on_disk: No # Filesort: No Filesort_on_disk: No Merge_passes: 0 85/141
- 89. シンプルな話、外側のクエリーに1⾏マッチするたびに内 側のクエリーを実⾏してしまう ⾏のフェッチのみならず、クエリーまるごと実⾏する。 外側テーブルのサイズが⼤きくなると加速度的に遅くな るのはこのせい。 - mysql> SHOW PROFILE; +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000023 | | .. | .. | | executing | 0.000006 | | Sending data | 0.000006 | | .. | .. | | executing | 0.000005 | | Sending data | 0.000014 | | executing | 0.000005 | | Sending data | 0.000047 | | end | 0.000010 | | .. | .. | +--------------------------------+----------+ 2000025 rows in set (4.78 sec) 88/141
- 90. 相関サブクエリーの書き換え⽅ 7割⽅JOINに書き換えられる JOINに落とし込んでUSE INDEXまでつければ5.5とそれ以 前でも戦える - どうしてもロジックとしてJOINに書き換えられないケース もある CREATE TEMPORARY TABLEを使って落とし込むのがいい テンポラリーテーブルはクラッシュアンセーフ（ 更新 ステートメントには使わない︕ ） そもそもクラッシュアンセーフだから迷わずMyISAM を選んでいい ⼤した量でなくてもインデックス張る。劇的に違う - のがいい、というか、SQLだけでやるにはそれしかない- 89/141
- 92. ひとやす み 91/141
- 95. パラメーターが適切でない innodb_buffer_pool_size innodb_log_file_size * innodb_log_files_in_group innodb_flush_method innodb_log_buffer_size innodb_flush_log_at_trx_commit innodb_io_capacity, innodb_io_capacity_max 94/141
- 96. innodb̲buffer̲pool̲size InnoDBの動作の要 個⼈的にはキャッシュじゃなくて データの本体- ⾜りてないとSELECTだけじゃなく全ての動作が遅い ただしDROP TABLEとTRUNCATE TABLEはバッファプールが⼤き ければ⼤きいほど遅くなる 95/141
- 98. InnoDBの動作の要 INSERT, UPDATE, DELETEのとき UNDOとログはちょっと省略 - UPDATE, DELETEの時にPRIMARY KEY以外の属性で絞り込 む場合は、SELECTと同様にバッファプールを使って検索 する - 97/141
- 99. InnoDBの動作の要 INSERT, UPDATE, DELETEのとき データそのものの追加, 更新, 削除は最終的にクラスター インデックスの値から、書き込むページ番号を特定する バッファプールはCOMMIT前に更新される（COMMIT されてるかどうかのフラグがある） READ-UNCOMMITTEDはバッファプールから読んで COMMITされてるかどうかのフラグを⾒ない COMMITされてるかどうかに関わらず、古いファイル はUNDOセグメントに移されて、そのページ番号がペ ージに書き込まれる - 98/141
- 101. 対象のページがバッファプールに載っていなければ テーブルスペースファイルを読んでバッファプールに載せて から書く バッファプールに載せてからINSERT- バッファプールに載せてからUPDATE- バッファプールに載せてからDELETE- Replication Booster for MySQLやinnodb̲fake̲changes はこの仕組みを利⽤したもの 100/141
- 102. DROP TABLE, TRUNCATEのとき TRUNCATEは内部的にDROP TABLE, CREATE TABLE ディクショナリーから参照を切る バッファプール上の全てのページをチェックし、そのテーブ ルのページがあれば破棄する 「バッファプールに載せてdelete mark」はしない- 101/141
- 103. ROW̲FORMAT= Compressed InnoDB圧縮 圧縮はページ単位、圧縮したままでは読むことも書くことも できない バッファプールに 圧縮状態のまま 読み込むa. バッファプール上で 解凍してコピーするb. 解凍済みのページに対する操作をするc. 解凍済みのページがフラッシュされる時に再圧縮されて 圧縮状態のページ が更新される（再圧縮） d. 圧縮状態のページはバッファプールにもう少し残るe. ただでさえ重いディスクアクセスのタイミングで圧縮/解凍 処理が加わる。 102/141
- 108. innodb̲log̲file̲size * innodb̲log̲files̲in̲group ⾜りているか , ⾜りていないか の⼆軸なので、⾜りていれ ば⼤きすぎる必要もないし、⾜りていなければどんどん⼤き くしないといけない ⼤きすぎるとクラッシュリカバリー終わらないって話が あるけど、MySQL 5.5以降でログファイルサイズによる クラッシュリカバリーが終わらないって話はそうそうな い（⻑くて数分程度） - innodb_flush_method= O_DIRECTでもページキャッシュさ れる（O̲DIRECTで開くのはテーブルスペースファイル だけ）ので、ページキャッシュと相談 - 107/141
- 109. innodb̲log̲file̲size * innodb̲log̲files̲in̲group InnoDBログの性能は innodb_log_file_size と innodb_log_files_in_group の積にほぼ⽐例 64M * 3 と96M * 2 はほぼ同等の性能- ⾜りてるか⾜りてないかだから- 5.5とそれ以前はinnodb_log_files_in_groupのみを増やす場 合のみ再作成不要 5.6とそれ以降は増やす時でも減らす時でもmy.cnfを書 き換えて再起動するだけでいい。楽ちん。 - 108/141
- 110. InnoDBのデータ永続化 InnoDBはテーブルスペースファイル + ログファイルで初め て完全なデータ バッファプールは (テーブルスペースファイル + ログフ ァイル) の部分集合 - ある意味「フルバックアップ」と「バイナリーログ」か らポイントインタイムリカバリーをするのに似ている - バッファプール上にあってテーブルスペースファイルに ないデータ(ダーティページ)が⼀定割合を超えると強制 チェックポイント - 109/141
- 113. innodb̲flush̲log̲at̲trx̲commit InnoDBログをsyncする頻度 デフォルトは1。RDBMSとしては1であるべき。 innodb̲flush̲log̲at̲trx̲co mmit write fsync 0 InnoDBまかせ InnoDBまかせ 1 COMMITごと COMMITごと 2 COMMITごと 1秒に1回 112/141
- 114. InnoDBログとクラッシュ problem commit write sync detail mysqldクラッ シュ OK OK OK データの⽋損な し OSクラッシュ OK OK OK データの⽋損な し mysqldクラッ シュ OK OK Not yet データの⽋損な し OSクラッシュ OK OK Not yet データ⽋損 mysqldクラッ シュ OK Not yet Not yet データ⽋損 OSクラッシュ OK Not yet Not yet データ⽋損 mysqldクラッ シュ Not yet Not yet Not yet データの⽋損な し OSクラッシュ Not yet Not yet Not yet データの⽋損な し 113/141
- 115. innodb̲flush̲log̲at̲trx̲commit= 1 problem commit write sync detail mysqldクラッ シュ OK OK OK データの⽋損な し OSクラッシュ OK OK OK データの⽋損な し mysqldクラッ シュ OK OK Not yet データの⽋損な し OSクラッシュ OK OK Not yet データ⽋損 mysqldクラッ シュ OK Not yet Not yet データ⽋損 OSクラッシュ OK Not yet Not yet データ⽋損 mysqldクラッ シュ Not yet Not yet Not yet データの⽋損な し OSクラッシュ Not yet Not yet Not yet データの⽋損な し 114/141
- 116. innodb̲flush̲log̲at̲trx̲commit= 2 problem commit write sync detail mysqldクラッ シュ OK OK OK データの⽋損な し OSクラッシュ OK OK OK データの⽋損な し mysqldクラッ シュ OK OK Not yet データの⽋損な し OSクラッシュ OK OK Not yet データ⽋損 mysqldクラッ シュ OK Not yet Not yet データ⽋損 OSクラッシュ OK Not yet Not yet データ⽋損 mysqldクラッ シュ Not yet Not yet Not yet データの⽋損な し OSクラッシュ Not yet Not yet Not yet データの⽋損な し 115/141
- 117. innodb̲flush̲log̲at̲trx̲commit= 0 problem commit write sync detail mysqldクラッ シュ OK OK OK データの⽋損な し OSクラッシュ OK OK OK データの⽋損な し mysqldクラッ シュ OK OK Not yet データの⽋損な し OSクラッシュ OK OK Not yet データ⽋損 mysqldクラッ シュ OK Not yet Not yet データ⽋損 OSクラッシュ OK Not yet Not yet データ⽋損 mysqldクラッ シュ Not yet Not yet Not yet データの⽋損な し OSクラッシュ Not yet Not yet Not yet データの⽋損な し 116/141
- 118. 独断と偏⾒ MySQLが単体でダウンするよりはH/W障害で軒並みごっそ りいかれる⽅が回数が多い気がする ということはinnodb_flush_log_at_trx_commitは1か0（ ク ラッシュしたら必ず毎回作り直す ）が効率がいいと思う 毎回作り直す ことを前提に設計すれば、 innodb_log_group_home_dir= /dev/shm も可能 /dev/shmだとページキャッシュ⾷わないし 更にはskip_innodb_doublewriteも使える - 117/141
- 121. ロックの競合（並列度が⾼いだけとは限らない） あるいは単にInnoDBが⾏ロックだと 思い込んで ロック粒 度が適切でない InnoDBのロックはネクストキーロック- つまり インデックスロック- ネクストキーロックは インデックス間のギャップもロッ ク ギャップロックが避けたい場合はREAD-COMMITTED にするとまあまあ良くなる - sys.innodb̲lock̲waitsがロック解析を助けてくれる InnoDBは基本的に参照ロックフリー 120/141
- 122. Too many connectionsは⼤概並列度⾼すぎではない 単にクエリーが遅い 単にロックが競合してる 単に性能限界を超えたmax_connectionsを設定してしまって る Connection Handlerをcloneして認証までしてから”Too many connections”の判定をするから、cloneの負荷はか かる - 稼げる時間は⼗数分、それもエラーを減らすために他の クエリーの速度を犠牲にする - ただしホントにmutexが変に刺さってもこれは出る（けど、 頻度は少ない） mutexが刺さったならmysqldの再起動⼀択- 121/141
- 123. mutexの競合（並列度が⾼すぎる） ガチでmutexが詰まってる場合は%sysが跳ねあがることが 多い InnoDBのmutexの詰まりはSHOW ENGINE INNODB STATUSで SEMAPHORESセクションにいっぱい吐く こうなると正直⼿の打ちようがほぼない- 多少IO処理を軽くしたりスレッドの数減らして再起動し ても焼け⽯に⽔ - スケールアウト検討どき- ⾼負荷状況下で新機能を使うと刺さって残ることがたまにあ る というか先週InnoDB Online DDLで変に刺さって再起動 した - 122/141
- 126. バルク操作 新しいインスタンスをスレーブで⽴てて、そっち側でバルク 更新 skip_innodb_doublewrite, innodb_flush_log_at_trx_commit= 0, skip_log_bin, skip_slow_query_log - バルク操作を分割して並列化- 切り替え時に ショートメンテが必要 （テーブルごとデー タの転送） 他のスレーブがあった場合、そっちにも転送しないと いけない 5.6とそれ以降ならFLUSH TABLE FOR EXPORTを使ってテ ーブル単位でファイルコピーできる - 125/141
- 131. 容量との戦い DELETEは⼀度拡張したテーブルスペースを縮⼩させない 空き領域が再利⽤されるので、テーブルスペースのサイ ズがそこで頭打ちになる、程度。 - テーブルスペースを⼩さくするにはOPTIMIZE TABLEが必 要。 ただしInnoDBでinnodb_file_per_table= 0の場合は OPTIMIZE TABLEでも⼩さくならない。 この設定だとDROP TABLEやTRUNCATEでも決して⼩さく ならないので結構⾯倒。 - 130/141
- 133. 容量との戦い タイトになるのがわかってる時はパーティショニングしてる 削除条件が設計段階できれいに決まっているなら、それ に合わせてパーティショニングすることでALTER TABLE TRUNCATE PARTITIONが使える。 - 12か⽉保存のためにPARTITION BY LIST ((YEAR (register_date) MOD 2)* 100 + MONTH(register_date)) で24パーティション確保して13か⽉目にTRUNCATE PARTITIONするとか - 132/141
- 139. Question Using whereとUsing filesort、どっちが悪いっていう指標はあ るの︖ フェッチするのが2⾏だったらUsing whereでもUsing filesort でも構わないけどフェッチするのが1000万⾏だったらダメだ よね、という感じなので、Rows_sentとRows_examinedの⽐率で ⽐べるのがいいんじゃ。 138/141
- 141. Question MyISAMならALTER TABLE .. ORDER BY ..で降順のインデック スを作れる︖ インデックス上は必ず昇順。ALTER TABLE .. ORDER BY ..で並 べ替えられるのはデータファイル上の並び順。 MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.7 ALTER TABLE 構⽂ 140/141