ä»å¹Žã®åãã«MySQLã®æ°ããããŒãžã§ã³ããªãªãŒã¹ãããåŸãå€ãã®äººãå€ãããŒãžã§ã³ããMySQLã«ç§»è¡ãã䟡å€ããããã©ãããèããŸããã ãã®è³ªåã«èªåã§çããã«ã¯ããŸãããã®ç§»è¡ããããããã®ãæ£ç¢ºã«çè§£ããå¿
èŠããããŸãã ãã®èšäºã§ã¯ãæ°ããããŒãžã§ã³ã®é
åžãããã©ãŒãã³ã¹ã®åæãæ°ããããŒãžã§ã³ã®æäœæ§ãããã³æžãçŽããããªããã£ãã€ã¶ãŒã«ããã³ãŒãã®å€æŽã®å¿
èŠæ§ã«å«ãŸããŠãããç§ã«ãšã£ãŠéèŠãªæ°ããæ©èœã匷調ããŸãã 倿Žã®éã¯æ¬åœã«éåžžã«å€§ãããããåé
ç®ã«ã€ããŠãããã©ãŒãã³ã¹ãã¹ãã«é¢ããå
ã®èšäºãžã®ãªã³ã¯ãæäŸãã説æããæ°Žãé€å€ããŸãã
æãè峿·±ããã®ããå§ããŸããã-ãªããã£ãã€ã¶ãŒæ¡åŒµæ©èœ
ã€ã³ããã¯ã¹æ¡ä»¶ããã·ã¥ããŠã³ïŒICPïŒ -è€åã€ã³ããã¯ã¹ã䜿çšããŠããŒãã«å
ã®ããŒã¿ã«ã¢ã¯ã»ã¹ããå Žåããã®æé©åãå®è¡ãããŸãã ããŒã®æåã®éšåã®æ¡ä»¶ãæç€ºçã«é©çšã§ããããŒã®æ®ãã®éšåã®æ¡ä»¶ãæç€ºçã«é©çšã§ããªãå Žåãããšãã°ïŒ
keypart1 = 1
è¿°èªã«ã¢ã¯ã»ã¹ãã keypart2 like '%value%'
è¿°èªã®ãã£ã«ã¿ãªã³ã°ã- ãŸãã¯ãç¯å²ããŒã®äž¡æ¹ã®éšåã«èª²ãã«ã¯ã
keypart1 between a and b
ã®keypart1 between a and b
æ¡ä»¶ã¯ã¢ã¯ã»ã¹è¿°èªã§keypart2 between c and d
ã¯ãã£ã«ã¿ãŒè¿°èªã§ãïŒã¢ã¯ã»ã¹è¿°èªãšããŠã®ãã®ã¿ã€ãã®ã¯ãšãªã®ã€ã³ããã¯ã¹ã¯ã MySQL
ãŸã å®è£
ãããŠããŸããïŒã
ãã®åŸã
MySQL
ãµãŒããŒã¯ã¢ã¯ã»ã¹è¿°èªãé©çšããåŸãã¹ãã¬ãŒãžãšã³ãžã³ã«åãæ¿ããããŒãã«è¡ãèªã¿åã£ãåŸã2çªç®ã®æ¡ä»¶ãé©çšããŸãã ãã®ãªãã·ã§ã³ã䜿çšãããšããã¡ãããã¹ãã¬ãŒãžãšã³ãžã³èªäœã
ICP
ãµããŒãããçŸæç¹ã§
MyISAM
ããã³
InnoDB
ã§ããå Žåããã®åãæ¿ãã¯è¡ãããããã£ã«ã¿ãŒè¿°èªã¯ã€ã³ããã¯ã¹ããŒã¿ã«åºã¥ããŠæä»çã«é©çšãããŸãã èšç»ã¯ã
Using index condition
ã
ãã¹ãã«ãããšããã£ãã·ã¥ããããŒã¿ãèŠæ±ãããšã
ã®ããã©ãŒãã³ã¹ã¯çŽ30ã50ïŒ
ã§ãããå
¥å/åºåãµãã·ã¹ãã ãæäœããŠããéãæå€§100åã®å éãå¯èœã§ãã
ãã«ãã¬ã³ãžãªãŒãïŒMRRïŒ -ã»ã«ã³ããªããŒã䜿çšããŠ
Range Scan
ãå®è¡ããå Žåãä»»æã®ãã£ã¹ã¯ãªãŒãã®åæ°ãæžããããã«ãæåã«ã€ã³ããã¯ã¹ããå¿
èŠãªããŒã¿ããã¹ãŠèªã¿åããæ¬¡ã«
ROWID
ã§ãœãŒãããŠããããã©ã€ããªããŒã§ã®ã¿ããŒã¿ãèªã¿åããŸãã ééããªãçŽ æŽããããšããç¹ã§ã¯ã
Using MRR
ã§ãã ãã®æäœã¯ããŒã ã§ãã ã€ãŸã äºæ¬¡ããŒã«ãã£ãŠã¢ã¯ã»ã¹è¿°èªãé©çšããå©çšå¯èœãªãããã¡ãåãããã®åŸã®ã¿ããŒã¿ããœãŒãããŠèªã¿åããŸãã ããã§ã幞éãšããŒã¿ãäºãã«è¿ããã©ããã 忣ããŒãã«ã®å Žåãç¶æ³ã¯ããã«è¯ããªããŸãããããã¯ãã®èšäºã®ç¯å²å€ã§ãã çŽç²ãªåœ¢åŒã§ã¯ããã®ã¢ã«ãŽãªãºã ã¯1ã€ã®ããŒãã«ã§ã¯ãšãªãå®è¡ãããšãã«é©çšã§ããŸãã ãŸãã
ãããããŒã¢ã¯ã»ã¹ïŒBKAïŒãšçµã¿åãããããšã§
ãçµåæäœãé«éåããããšãã§ããŸãã ããã©ã«ãã§ã¯ã
BKA
ãã©ã°ã¯ãªãã«ãªã£ãŠããããã®å€ã¯ããªããã£ãã€ã¶ãŒãã©ã°ã®å€ãšåæ§ã«ã
SELECT @@optimizer_switch
èŠæ±ãå®è¡ããããšã§è¡šç€ºã§ããŸãã BKAãæå¹ã«ãªã£ãŠããå Žåã
join_buffer_size
ãã©ã¡ãŒã¿ãŒã®ç°ãªãå€ã§ãªã¯ãšã¹ãã確èªããå¿
èŠããããŸãããã®ãã©ã¡ãŒã¿ãŒã¯æå€§ãããã¡ãŒãµã€ãºã®èšç®ã«äœ¿çšãããããã§ãã ãã®çŽ æŽãããã¢ã«ãŽãªãºã ãã¯ãšãªã®èгç¹ããé©çšãããšã
Using join buffer (Batched Key Access)
ãŸãã ãããã¯ã®è©³çްã¯
ãã¡ããã芧ãã ãã ã
ãã¹ãçµæã«ãã
ãšããã®æé©åã¯ãå
¥åãåºåãããã£ã¹ã¯ã§åäœããå Žåã«ã®ã¿ã倧å¹
ãªå éãå®çŸã§ããŸãã ãã®å Žåã
MRR
ã®ã¿10åã®å éãåŸãããšãã§ããŸãã
MRR
ãš
BKA
䜵çšã«ãããä»ã®å€ãã®åæãã¹ãã§æå€§280åã®å éãåŸãããŸããã ã¡ã¢ãªã®ã¿ã§äœæ¥ããå Žåããã®æäœã¯å
¥åãšåºåãæä»çã«æé©åããããã«èšèšãããŠãããããéåžžã«è«ççãªå éã¯åŸãããŸããã
ãã¡ã€ã«ã®äžŠã¹æ¿ãã®æé©å -ã€ã³ããã¯ã¹åãããŠããªãåã«ãã
äžŠã¹æ¿ãã¯ãç¹å®ã®æ¡ä»¶ã®ããšã§ã¯ããã«çç£çã«ãªããŸããã 以åã¯ããã®ã¿ã€ãã®ãœãŒããå®è¡ããå Žåãå¯èœãªå¯äžã®ãªãã·ã§ã³ã¯ããŒãžãœãŒãã§ããã 倧ãŸãã«èšããšããªã¯ãšã¹ãã®çµæãšããŠååŸãããããŒã¿ã¯äžæããŒãã«ã«ä¿åãããŠããŸããã ãã®åŸããã®ããŒãã«ã«å¯ŸããŠãœãŒããå®è¡ããã
LIMIT
æ¡ä»¶ãæºããã¬ã³ãŒããè¿ãããŸããã ã芧ã®ãšããããã®ã¢ã«ãŽãªãºã ã¯ããŸãçç£çã§ã¯ãããŸãããç¹ã«ãå°æ°ã®è¡ãè¿ãã¯ãšãªã倿°ããå Žåã¯ããã§ãã å
¥åãæé©åããããã«ãåºåã¢ã«ãŽãªãºã ãæ¬¡ã®ããã«å€æŽãããŸããã ã¯ãšãªã®çµæãšããŠåä¿¡ããã¬ã³ãŒãã®æ°ã
sort_buffer_size
ã«å®å
šã«é
眮ãããŠããå ŽåãããŒãã«ã®ã¹ãã£ã³æã«ãã¥ãŒãäœæãããŸãã ãã®ãã¥ãŒã¯é åºä»ããããã¯ãšãªçµæã®æ¬¡ã®è¡ãåä¿¡ããããšããã«ãã£ã±ãã«ãªããŸãã ãã¥ãŒãµã€ãºã¯
N
ïŒãŸãã¯
LIMIT M, N
æ§æã䜿çšãããå Žåã¯
M + N
ïŒã§ãã ãã¥ãŒããã£ã±ãã«ãªããšãæåŸããäœåãªããŒã¿ãã¹ããŒãããŸãã ãããã£ãŠãäžŠã¹æ¿ãã®çµäºã¯ã¯ãšãªçµæã®çµäºãšåæã«å®è¡ãããäžæããŒãã«ãžã®åŒã³åºãã¯ãããŸããã ãªããã£ãã€ã¶ãŒã¯ããœãŒãã®æŠç¥ãåå¥ã«éžæããŸãã ãã¥ãŒã¯ãããã»ããµãããã«ããŒãããå¿
èŠããããšãã«äœ¿çšãããå
¥å/åºåã·ã¹ãã ãå©çšå¯èœãªãšãã«ãœãŒããããŒãžããŸãã
æš¡æ¬è©Šéšã宿œãããšã4åã®å éãåŸãããŸããã ãã ãããã®æé©åã«ãããåºåå
¥åãµãã·ã¹ãã ã®è² è·ã軜æžã§ããããšãçè§£ããŠããå¿
èŠããããŸããããã«ãããã€ã³ã¹ã¿ã³ã¹å
šäœã®äœæ¥ã«ãã©ã¹ã®åœ±é¿ããããŸãã
ãµãã¯ãšãªã®æé©åpostpone materialization
åã®
postpone materialization
-äž»ã«ã¯ãšãªãã©ã³ã®æ§ç¯ã®é«éåã«é¢é£ããŸãã 以åã¯ã
EXPLAIN
ã³ãã³ããå®è¡ãããšã
FROM
ã»ã¯ã·ã§ã³ã§äœ¿çšããããµãã¯ãšãªãå
·äœåãããŠããããã®çµ±èšãååŸãããŠããŸããã ã€ãŸã å®éããããã®ã¯ãšãªã¯ããŒã¿ããŒã¹ã§å®è¡ãããŸããã çŸåšããã®æäœã¯å®è¡ããããDBã€ã³ã¹ã¿ã³ã¹ãããŒãããã«ã¯ãšãªãã©ã³ãååŸããããšãå¯èœã«ãªããŸããã ãã ããããšãã°ã
FROM
ã»ã¯ã·ã§ã³ã«2ã€ã®å®äœåå¯èœãªè¡šçŸããããæåã®ã¬ã³ãŒããåäžã®ã¬ã³ãŒããè¿ããªãå Žåã2çªç®ã®ã¬ã³ãŒãã¯å®è¡ãããªããªã©ãã¯ãšãªã®å®è¡æéãççž®ã§ããŸãã ãŸããã¯ãšãªã®å®è¡äžã«ãMySQLã¯å¿
èŠã«å¿ããŠãçµæã®ãããªã¢ã©ã€ãºããŒãã«ã«åå¥ã«ã€ã³ããã¯ã¹ãä»ããããšãã§ããŸãã
semi-join transformation
-
semi-join transformation
ã¯ããã¹ãŠã®ããŒã¿ããŒã¹ã®å¥ã®ããŒãã«ã®ããŒã¿ã«åºã¥ããŠã1ã€ã®ããŒãã«ã®ã¿ããããŒã¿ãååŸããããã«äœ¿çšã§ããŸããå
žåçãªäŸã¯
EXISTS
ã³ã³ã¹ãã©ã¯ãã§ãã ãã®æé©åã®äœ¿çšã¯ã以åã¯éåžžã«ããŸãæ©èœããªãã£ã
IN
ãã¶ã€ã³ã§å¯èœã«ãªããŸããã ãã®æé©åãå¯èœã«ããããã«ã¯ããµãã¯ãšãªã以äžã®æ¡ä»¶ãæºããå¿
èŠããããŸãïŒ
UNION
ã
GROUP BY
ããã³
HAVING
ã
LIMIT
å¶çŽä»ãã®
ORDER BY
ãªãïŒãããã®æ§é ã¯å¥ã
ã«äœ¿çšã§ããŸãïŒã ãŸãããµãã¯ãšãªã®æ°ã
JOIN
ã«èš±å¯ãããããŒãã«ã®æå€§æ°ãè¶
ããªãããã«ããå¿
èŠããããŸããããããªããšã
MySQL
ã¯ãã®ã¯ãšãªãæžãæããããšãã§ããŸããã æé©åã¯ã
SEMI JOIN
ãªã¯ãšã¹ããæžãçŽããã
FROM
æ§æã§ãµãã¯ãšãªã
VIEW
ãšããŠæç€ºããäžèšã®
postpone materialization
åã®æé©åã䜿çšããããšã§
postpone materialization
ãããŸãã æçµçµæããã®éè€ãšã³ããªã¯ãæ¬¡ã®æ¹æ³ã§åé€ãããŸãã
- æçµã¯ãšãªã®äžæã®çµæãäžæããŒãã«ã«é
眮ããããšã«ããïŒ
Start temporary
Extra
èšç»ãåã®Start temporary
ããã³Start temporary
ã«End temporary
ãïŒ - ãµãã¯ãšãªããããŒãã«ãã¹ãã£ã³ããæ®µéã§ãæåã«èŠã€ãã£ããåºæºã䜿çšãã
FirstMatch(tbl_name)
Extra
planåã®FirstMatch(tbl_name)
ïŒ - æé©åã䜿çšããLoose Index Scan ïŒ
LooseScan(m..n)
ããã§ãmããã³nã¯ãã®æé©åã«äœ¿çšãããéèŠãªéšåã§ãïŒ
ãµãã¯ãšãªãå®äœåããŠããã€ã³ããã¯ã¹ãäœæãããšãã
MATERIALIZED
ã¯ã¯ãšãªãã©ã³ã®
select_type
åã«ãããŸãã èŠæ±ãã©ã®ããã«æžãæããããããæ£ç¢ºã«çè§£ããã«ã¯ãæšæºã®
EXPLAIN EXTENDED
ã³ãã³ãã䜿çšã§ããŸãã ããã«ããããªã¯ãšã¹ããã©ã®ããã«æžãæããããããããã³æé©åãé©çšããããã©ãããåžžã«çè§£ã§ããŸãã
ãåç¥ã®ããã«ã
æ²ãã£ãŠæžãããã¯ãšãªãæžãçŽããš ïŒãã®ãããªã¯ãšãªã«æãåºããªãã£ãã®ã§ãä»ã§ã¯èš±ãããªããªã£ãŠããŸãïŒãããã©ãŒãã³ã¹ã倧å¹
ã«åäžããå¯èœæ§ãããããã
table pullout
ãã¹ããå®è¡ããããšã¯ã»ãšãã©æå³ããããŸããïŒ
FROM
WHERE
ãããµãã¯ãšãªãåé€ã
table pullout
ïŒãçç£æ§ã1000åã«å¢å ããŠãå¶éã¯ãããŸãããããã¹ãŠã®ã¯ãšãªãæ£ããèšè¿°ãããŠããã°ããã®ã¿ã€ãã®æé©åããç°åžžãªäœããåŸ
ã€ããšã¯ã§ããŸããã
InnoDB
éççµ±èšæåŸã«ãããã¯èµ·ãããŸããã
Google
ã
Percona
ãŸãã¯
Oracle
èªäœã®ã©ããããã¯ããããŸãããã
MySQL
ãã¯ãšãªãã©ã³ã®æ§ç¯ããåçãµã³ããªã³ã°ãé€å€ãã
MySQL
ãå¯èœã«ãªããŸããã çŸåšãããŒãã«ãšã€ã³ããã¯ã¹ã®çµ±èšã¯æ°žç¶ããŒãã«ã«ä¿åãããŸãã ãã®çµ±èšåéæ¹æ³ã¯ããã©ã«ãã§æå¹ã«ãªã£ãŠããŸãã ããŒãã«å
ã®ããŒã¿ã®10ïŒ
以äžãæŽæ°ãããšããã®çµ±èšã¯èªåçã«åæ§ç¯ãããŸãïŒãã¡ãããããã¯å€æŽã§ããŸãïŒã ãŸããçµ±èšã®åéã¯ã
ANALYSE
ã³ãã³ãã䜿çšããŠåŒ·å¶çã«éå§ã§ããŸãã ãªããã£ãã€ã¶ãŒãåäŸã®ãªãã»ã©æãã§ãããšãã«çµ±èšã匷å¶çã«ãªã»ããããã«ã¯ã
FLUSH TABLE
ã³ãã³ããåŒã³åºããŸãã æ£ç¢ºã«äœãè¡ããããã¯ãæ°ããããŒãã«
mysql.innodb_index_stats
ã
mysql.innodb_table_stats
ã§æç¢ºã«èŠãããšãã§ããŸãã ããã¯ç¢ºãã«Oracleã®é«åºŠãªãã¹ãã°ã©ã ã§ã¯ãªããå®éã®é²æç¶æ³ã§ãã çŸåšãèšç»ã¯ããå®å®ããŠãããäžæ¹ã§ãDBAã«ã¯æ°ãããšã³ã¿ãŒãã€ã¡ã³ãããããŸãïŒããŒãã«ã®çµ±èšãç¡é¢ä¿ã«ãªã£ããšãã«éä¿¡ããããŠã³ã¿ã€ã ãèŠã€ãããµã³ããªã³ã°ã®éãæšæž¬ããŠåèšç®ããŸããç¹ã«éçºè
ããã°ããã®ãšãããçµ±èšã¯ãã³ãã«ã«ãã£ãŠå€æŽã§ããŸããçŽæ¥æŽæ°ã«ããã ãŸãããã¹ãã®å®è¡æã«ãããã¯ã°ã©ãŠã³ãã¹ã¬ããã§åäœããŠãããšæãããçµ±èšæ
å ±ã³ã¬ã¯ã¿ãŒãããŒã¿ãåŠçã§ããªãã£ãããšã«ã泚æããŠãã ããã åæãæåã§éå§ãããŸã§ãé·ãéçµ±èšã¯ç©ºã®ãŸãŸã§ããã ç¹å®ã®ããŒãã£ã·ã§ã³ã«å¯ŸããŠçµ±èšåæãå®è¡ããããšã¯äžå¯èœã§ãããããããŒãã£ã·ã§ã³åãããããŒãã«å
šäœãåæããå¿
èŠããããŸãããããã¡ããããŸã䟿å©ã§ã¯ãããŸããã ã¢ã¯ãã£ããªDMLãå®è¡ããŠããŒã¹ã忢ããå Žåããåãç¶æ³ãçºçããŸãã ããŒã¿ã¯-çµ±èšãªãã«ãªããŸãã ãããããããã¯äŸå€çãªç¶æ³ã§ãããããŒã¿ããŒã¹ã®éåžžã®æäœã劚ããããšã¯ãªããšæããŸãã
ãªããã£ãã€ã¶ãŒã«é¢ãã
çµè«ãšããŠãMaria DB 5.5ãªããã£ãã€ã¶ãŒã¯å°éå®¶ã«ããã°ããæŽç·ŽãããŠããããMySQL 5.6ã§ã¯åæ§ã®æé©åã倿°è¡ãããŠãããããããã©ãŒãã³ã¹ãåäžããŠããããšã«æ³šç®ãã䟡å€ããããŸãã
ããè¯ããã¥ãŒããã¯ã¹ãšç°ãªããã¥ãŒããã¯ã¹
誰ããããç¥ã£ãŠããããã«ãMySQLã¯ãåæã«å®è¡ããã倿°ã®ãã©ã³ã¶ã¯ã·ã§ã³ãåãã倿°ã®ããã»ããµã«å¯ŸããŠååã«æ¡åŒµã§ããŸããã ãã®çç±ã¯ãå
éšããã¯ãç¹ã«
kernel mutex
ãããã³ãã«ãã³ã¢ã¢ãŒããã¯ãã£ã§ã¡ã¢ãªãæäœããéã®åºæ¬çãªåé¡ã§ãã ã¢ã¯ãã£ããªãã©ã³ã¶ã¯ã·ã§ã³ã®ãªã¹ããã³ããŒããããã«ä¿æããã
Kernel mutex
ãéããããã³ã°ããã³ããããã³ã°ãã©ã³ã¶ã¯ã·ã§ã³ãããã¯ãããã¯åŸ
æ©ãªã©ã®ããã«ããã€ãã®ãã¥ãŒããã¯ã¹ã«åå²ãããŸããã
false sharing
ã®åé¡ã解決ãããŸãã.1ã€ã®ã³ã¢ãå¿
èŠãªäžå€ããŒã¿ãããŒããããã®ããã«å¿
èŠãªãã1ã€ã®ã³ã¢ã1ã€ã®
cacheline
ã§å€æŽããããããæåã®ã³ã¢ã®ããŒã¿ã¯åžžã«ãã£ãã·ã¥ããæŽãæµãããŸããã çŸåšãå€ãã®éèŠãªãªããžã§ã¯ãã«64ãã€ãã®
ã¢ã©ã€ã¡ã³ããå°å
¥ãããŠ
ããŸãã
éçºè
ã®ããã°ã«ãããšã
MySQL
ã¯
read only
ãã©ã³ã¶ã¯ã·ã§ã³ã§æå€§50ïŒ
åäžããŠã
read only
ã ãŸããã¢ã¯ãã£ãã»ãã·ã§ã³æ°ã®å¢å ã«ããããã©ãŒãã³ã¹ã®åäžã¯ã以åã®ããŒãžã§ã³ãšæ¯èŒããŠæå€§600ïŒ
ã§ããã ç¬ç«ããè² è·ãã¹ããå®è¡ããå Žåãæå€§16ã®åæã»ãã·ã§ã³-é床ã¯å€ããããããé«é-æ··åèªã¿åã/æžã蟌ã¿ãã©ã³ã¶ã¯ã·ã§ã³ã§æå€§100ïŒ
ãèªã¿åãå°çšãã©ã³ã¶ã¯ã·ã§ã³ã§æå€§300ïŒ
ã
UNDOãªã»ããã®æé©å
UNDOã䞊è¡ããŠãªã»ããã§ããããã«ãªããŸããã ãããè¡ãã«ã¯ã
innodb_purge_threads
ãã©ã¡ãŒã¿ãŒã1ãã倧ããå€ã«èšå®ããŸããç£æ¥çšããŒã¿ããŒã¹ã§ãã®ãã©ã¡ãŒã¿ãŒã
innodb_purge_threads
ã¯ãããŒãã£ã·ã§ã³ããŒãã«ãã倧éã®ããŒã¿ã䞊åã§åé€ããå Žåã«ã®ã¿åŠ¥åœã§ãã ããŒã¿ããªã»ãããããšãã«
dict_index_t::lock
ãã³ã°ãããããããŒãã£ã·ã§ã³ã䜿çšããªã人ããŸãã¯ãã©ã¬ã«DML以å€ã®çç±ã§ããŒãã£ã·ã§ã³ã䜿çšãã人ïŒããšãã°ãDMLæäœã1ã€ã®ããŒãã£ã·ã§ã³ã®ã¿ã§å®è¡ãããã¢ãŒã«ã€ãïŒã®ããã©ãŒãã³ã¹ã®æ¹åã¯è¡ãããŸããã ãããã®å Žåã以åãšåæ§ã«ãã¡ã€ã³ã¹ããªãŒã ãšã¯å¥ã®ããŒã¿ãã³ããåçŽã«å²ãåœãŠãããšããå§ãããŸãã
ããŒãã£ãããã¯ã®æé©å
ãåãã®ããã«ãã¡ã¢ãªããããŒãã£ãããã¯ããã³ãããããšã¯ãããŒãžã§ã³ç®¡çãããããŒã¿ããŒã¹ã«ãšã£ãŠæãåé¡ã®ããå Žæã®1ã€ã§ãã ãããã¯ã®å¿
èŠãªéšåããã£ã¹ã¯ã«ãªã»ããããã«ã¯ãã¡ã€ã³ã®
InnoDB Master Thread
ã§è¡ããå
šå¡ãåŸ
æ©ããããåºæ¬çã«ããã¯ã°ã©ãŠã³ãã¹ã¬ãããšããŠç¹å®ã®ã»ãã·ã§ã³ããªã»ããã³ãã³ããéä¿¡ãããã®ã»ãã·ã§ã³ãç¡æéã«ãã³ã°ããæ®ãã¯ã»ãã·ã§ã³ã¯ãããã¯ãããŸããã æåãš2çªç®ã®äž¡æ¹ã®åé¡ãåé¿ããããã«ã
page_cleaner
ãšåŒã°ããå¥ã®ã¹ã¬ãããäœæãããŸããã ãã®ã¹ããªãŒã ã®ããžãŒç¶æ
ã®è©³çްã«ã€ããŠã¯ã
ãã¡ããã芧ãã ããã select name, comment from information_schema.innodb_metrics where name like 'buffer_flush_%';
ããã§ããããã¯ãªã»ãããå®éã«éåæçã«å®è¡ããå§ããŸããã ãããã¯ã®ãªã»ãããã©ã¡ãŒã¿ãŒã䜿çšããããšã«ããå Žåã¯ãæ°ãã
LRU flush
æé©åãã©ã¡ãŒã¿ãŒã«ã泚æãæãããšããå§ãããŸãããããã®ãã©ã¡ãŒã¿ãŒ
ã¯ã MySQL
éçºè
ã®
çµè«ã«ãããšãäºãã«éæ¥çã«åœ±é¿ãäžããå¯èœæ§ãããããã§ãã
ãããã¯ãªã»ããã¯SSDãã©ã€ãã«é©åããŸããã ãã®ãããŸããªãã¬ãŒãºã¯ã次ã®ããšã瀺ããŠããŸãã ç¥ã£ãŠããããã«ããããã¯ãã£ãã·ã¥ã¯ããŒãžã§çºçããŸãã ãšã¯ã¹ãã³ãã¯64ããŒãžã§ãã åããšã¯ã¹ãã³ãå
ã§ãããã®åãé£ç¶ããŒãžãåŒã³åºããã€ããŒãããã 以åã¯ãããŒãžã倿Žããéã
InnoDB
ã¯ãšã¯ã¹ãã³ãå
šäœãåéããŠããŒã¿ããã£ã¹ã¯ã«ãã©ãã·ã¥ããŠåºåãæé©åããããšããŸãããHDDã®å Žåã1ã¡ã¬ãã€ããæé©ãªãµã€ãºã§ããããã£ã¹ã¯ãžã®1åã®åŒã³åºãã§æäœãå®è¡ã§ããããã§ãã SSDã®å Žåããã³ããµã€ãºã¯4ãããã€ããããªããããäœããå
¥åããŠãæå³ããããŸããã ããã«ã倿ŽãããŠããªãããŒãžããã³ãããããšãç¡æå³ã§ãã ã ãããnewfangledéã®ææè
ã¯ã
ãã©ã¡ãŒã¿ãŒã§éã¶ããšãã§ã
ãŸã ã
çµè«ãšããŠã
ãã®ãã¹ãã®çµæã«æ°ä»ãã®ã¯å Žéãã§ã¯ãããŸããã


ã°ã©ããããããããã«ããã£ãã·ã¥ãæäœãããšããæé©åããããªã»ããã䜿çšããæ°ããããŒãžã§ã³ã¯ãå°æ°ã®ã»ãã·ã§ã³ã§
MySQL 4.0.30
倱ã
MySQL 4.0.30
ïŒéåžžã«å€ãã®å€ä»£ããŒãžã§ã³ãéžæããçç±ã¯äžæã§ãïŒããã¹ã±ãŒãªã³ã°ãããš1æ¡åªããçµæã瀺ããŸãã


ãã¡ã€ã«ã·ã¹ãã ã§äœæ¥ããå Žåãçµæã¯ããã»ã©å°è±¡çã§ã¯ãªããäž¡æ¹ã®ããŒãžã§ã³ãçã£åããåãããMySQL 5.6ã§ãå Žæã倱ããŸãã ããããèè
ã®çµè«ã«ãããšããªãªãŒã¹
5.6.12
ããã®æ¬ ç¹ã¯è§£æ¶ãããçç£æ§ã¯3åã«è·³ãäžãããŸãã ã€ãŸã å€§èŠæš¡ãªå
¥åºåãšãããã¡ã®ãã£ã¹ã¯ãžã®ã¢ã¯ãã£ããªãã©ãã·ã¥ã«åé¡ãããå Žåã¯ã次ã®ããŒãžã§ã³ããªãªãŒã¹ãããã®ãåŸ
ã€äŸ¡å€ããããŸãã
InnoDBïŒALTER TABLE ...ãªã³ã©ã€ã³
ãã®æè¡ã®ãã¹ãŠã®ç§°è³ã¯ã2ã€ã®èšèã§
å¹³æã«å®£äŒã§ããŸãã éçºè
ã«ãããšããã®ããŒã
ã¯å®å
šã«æžãçŽãããŸãããã
InnoDB-engine
ããŒã ã¯
InnoDB-engine
倧ããªå©ç¹ãåŸãããŸããã§ããã
online
ã§ã¯ãéãããæ°ã®éåžžã«ãŸããªæäœã®ã¿ãå®è¡ãããå¶éããããŸãã æäœã
online
ããšãçè§£ããã®
online
éåžžã«ç°¡åã§ãã ãªã¯ãšã¹ãã®çµæãšããŠãããªãã¯åãåãã§ããã
Query OK, 0 rows affected
ãããŠãäœãã§ããã®ãïŒ
ãŸããããããã¹ãŠã§ãã
å¯èœæ§ã®èª¬æãããããããã«ã
ç§ãã¡ã«æäŸãããæ©äŒã¯éåžžã«å°ãªã ãå°æ¥çã«ç¶æ³ãæ¹åããã¿ã³ããªã³ãšèžãããšãªã倧ããªããŒãã«ãžã®å€æŽãå®è¡ã§ããããšãé¡ã£ãŠããŸããããã¯ãå®è¡äžã®ããŒãã«ã®å®å
šãªã³ããŒã«é¢é£ä»ããããŠããŸãïŒ
MySQL 5.6
5 ïŒ
DDL
ã³ãã³ãã
ããŒãã£ã·ã§ãã³ã°
ããŒãã£ã·ã§ã³ãšã³ãžã³ã¯å€§å¹
ã«åèšèšãããŸããã çŸåšãåŠçã®ããã®ããŒãã£ã·ã§ã³ã®éžæã¯ãããŒãã«ãéããŠããã¯ãèšå®ãããããæ©ãå®è¡ãããŸãã ããŒãã£ã·ã§ã³ããŒã«å¯ŸããåçŽãªã¯ãšãªã®å
¥åå
¥åã®åæã次ã«ç€ºããŸãã
select count(1) from part_table where partition_key = 190110; +
å
¥åºåãšãããã¡ããŒã«
select count(distinct file_name) file_name_count, sum(sum_number_of_bytes_read) sum_number_of_bytes_read, min(substring_index(file_name, '/', -1)) min_file_name, max(substring_index(file_name, '/', -1)) max_file_name from performance_schema.file_summary_by_instance where file_name like '%part_table%.ibd' and count_read + count_write > 0 order by 1;
ã芧ã®ãšãããããŒãžã§ã³5.5ãšã¯å¯Ÿç
§çã«ãæå®ãããããŒãã«ã®ãã¹ãŠã®ããŒãã£ã·ã§ã³ã§ã¯ãªããæ¡ä»¶ã«å¯Ÿå¿ããããŒãã£ã·ã§ã³ã®çµ±èšã®ã¿ãåæãããŸããæ®ãã®ããŒãã£ã·ã§ã³ããããã¡ããŒã«ã«ããŒãããããšãé€å€ããŸããããã¯ã«ã€ããŠã¯ãäœããŸã æç¢ºã§ã¯ãããŸãããè§£æããŠãªã¯ãšã¹ãã®å®äºãåŸ
ã€ã¢ã«ãŽãªãºã ã倧å¹
ã«å€æŽãããŠãããããwait/synch/mutex/mysys/THR_LOCK::mutex
以åã¯åããŒãã£ã·ã§ã³ã§ãããã¯ãããŠããæãããããªãã¥ãŒããã¯ã¹ã¯ããŸãäžè¬çã§ã¯ãªããªããŸããã 1ã€ã®ããŒãã£ã·ã§ã³ãç
§äŒããå Žåããã®ããŒãã«å
ã®åèšããŒãã£ã·ã§ã³æ°ã«2ãæããåæ°ã¯ããã»ã©å€ããããã¯ãããã1ã€ã ãããããã¯ãããŸããããã¯ééããªã倧ããªãã©ã¹ã§ããç§ã¯ãåæ§ã®æ»æçãªæ¯ãèããæã€ä»ã®ããã¯ããŸã èŠã€ããããšãã§ããŸããã§ãããã©ããããã¹ãã«ç€ºãããŠãããšã³ãžã³ã®åé¡åé€ãããŸããïŒç¬ç«ããŠç¶æããã«ããŒãã£ã·ã§ã³ã®ãªã¹ããååŸããã«ã¯ãããŒãã«ã䜿çšã§ããŸããmysql.innodb_table_stats
ããŒãã£ã·ã§ã³ãäœæããããšãã«ãšã³ããªã远å ãããå®éã®çµ±èšãåŸã§è¡šç€ºãããŸãïŒããŸããå¿«é©ãªããšããã1ã€ã®ããŒãã«ã®ããŒãã£ã·ã§ã³ã®æå€§å¯èœæ°ã®å¶éã®å¢å ã«æ³šæããããšãã§ããŸããç§ã¯å人çã«ãšã³ãžã³ã«é¢ããèŠæ
ã¯ãªãã®ã§ãããŒãã£ã·ã§ã³ã䞊ååŠççšã«äœæãããããšã«æ³šæãã䟡å€ãããhint parallel(n)
ãŸããããŒãã£ã·ã§ã³ããŒãã«ãåŸ
ã£ãŠããŸããããã¯ã¢ãããšåŸ©å
æ°ããããŒãžã§ã³ã§ã¯ãInnoDBã®2ã€ã®éåžžã«æåŸ
ãããæ©èœãäžåºŠã«è¿œå ãããŸãããEXCHANGE PARTITION
ALTER TABLE part_table EXCHANGE PARTITION p1 WITH TABLE non_part_table;
ãã¹ãŠãæšæºã§ããããŒãã«ã¯ãã€ã³ããã¯ã¹ãå«ãå®å
šã«åäžã®æ§é ãæã£ãŠããå¿
èŠããããŸãããŸããstorage engine
ããŒãã£ã·ã§ã³åãããŠããªãããŒãã«ã®ããŒã¿ã¯ã亀æãããããŒãã£ã·ã§ã³ã®å¢çãè¶
ããªãããã«ããå¿
èŠããããŸããTransportable Tablespaces
ãåç¥ã®ããã«ã.ibd
ãã¡ã€ã«ã倿ŽããŠããŒã¿ãããããšãæåŸ
ããã ãã§ã¯æ©èœããŸãããREDO
ãã°ãããŒã¿ãã£ã¯ã·ã§ããªãã·ã¹ãã ã«ã©ã ãããŒãã«ã¹ããŒã¹ã¡ã¿ããŒã¿ãªã©ãèæ
®ããå¿
èŠããããŸãã ïŒãã®ãããªæäœã¯ãããäžé£ã®ã³ãã³ãã«ãã£ãŠæäŸãããããããšã¯ã¹ããŒãDB- FLUSH TABLES table_one, table_two FOR EXPORT;
-ã³ããŒ.ibd
ããŒã¿ããŒã¹ãã£ã¬ã¯ããªããçæãããèšå®ãã¡ã€ã«ããŒã¿ããŒã¹ãã€ã³ããŒããã-é¡äŒŒããæ§é ã®ç©ºã®ããŒãã«ãäœæãã- ALTER TABLE table_one DISCARD TABLESPACE;
-ã³ããŒ.ibd
ããŒã¿ããŒã¹ãã£ã¬ã¯ããªã«çæãããæ§æãã¡ã€ã«- ALTER TABLE table_one IMPORT TABLESPACE;
æäœã¯ããŒã¿ãã£ã¯ã·ã§ããªãéããŠå®è¡ãããŸããããã©ãŒãã³ã¹ã¹ããŒã
ããã©ãŒãã³ã¹èšºæåè·¯ã倧å¹
ã«æ¹åãããŸããããŸãã圌女ã®ä»äºã¯3åéããªããŸãããçŸåšãªã³ã«ãªã£ãŠãããšãã®ããã©ãŒãã³ã¹ã®äœäžã¯10ïŒ
ã§ã¯ãªãã3.5ïŒ
ã«éããªããããããã©ã«ãã§ãªã³ã«ã§ããŸããããã«å ããŠãå³ã«ã¯ä»¥åã®ããã«17åã®ããŒãã«ã¯ãããŸãããã52åãããŸããçè§£ã§ããããã«å€ãã®å€æŽããããŸããããããã¹ãŠã説æããå¿
èŠãããå Žåã¯ãå¥ã®èšäºãå¿
èŠã§ãã- ãªã¯ãšã¹ããçŽæ¥ãã¬ãŒã¹ã§ããããã«ãªããŸãã
show tables like '%statements%'; +
- (
object_type= 'TEMPORARY TABLE'
, - , )
select digest_text, sum_rows_affected, sum_rows_sent, sum_rows_examined from events_statements_summary_by_digest; +
show tables like '%by_thread%'; +
- , , ,
select digest_text, sum_created_tmp_disk_tables, sum_created_tmp_tables from events_statements_summary_by_digest; +
- , ,
show tables like 'table%waits%sum%'; +
ç§ããå§ãããããšãã§ããŸãèå³ã®ãã人ã®ããã«ããã«ãããŠãç§ã¯ãŠã§ãããŒã¯åå¿è
ã®ããã«ããŠããããéåžžã«å€ãã®å€æŽã®ããŒãžã§ã³5.5ã§ããã®ã¹ããŒã ã«ç²Ÿéããè
ãã®äž¡æ¹ã«æçšã§ããããšæããŸããã¹ããŒã ã¯ããã©ã«ãã§æå¹ã«ãªã£ãŠãããããèšå®ã¯éåžžã«æ§ããã§ããããšãã°ã5.5ã®ã¡ã€ã³ããŒãã«ã«ã¯events_waits_history_long
100ãšã³ããªãããªãããããããã®ãã©ã¡ãŒã¿ãŒã¯ãã¹ãŠéçã§ããã倿Žããã«ã¯ã€ã³ã¹ã¿ã³ã¹ã®åèµ·åãå¿
èŠãªã®ã§ãäºåã«èšå®ã決å®ããããšããå§ãããŸãããããã«
çµè«ãšããŠãç§ã¯åœŒã®æè¿ã®èšäºã§è¡šæãããããŒã¿ãŒã»ã¶ã€ãã§ãã®çµè«ãåŒçšããããšãã§ããŸããæš¡æ¬ãã¹ãã®çµæã«ãããšã1ã¹ã¬ããã®è² è·ã§MySQL 5.6
ã¯ä»¥åã®ããŒãžã§ã³ããMySQL 5.5
7.5ã11ïŒ
é
ãã64ã¹ã¬ããã®è² è·ã§ã¯11ã26ããŒã»ã³ãé
ããªããŸããããã¯å
¬åŒããŒãžã§ã³ãšã¯æ ¹æ¬çã«ç°ãªããŸããã€ãŸã
誰ããæé©ã«æžãçŽããŸããããããã©ãŒãã³ã¹ãåäžãããããšã¯ã§ããŸããã§ãããäžèšã§è¿°ã¹ããã¹ãŠã®æé©åã«ãããçµè«ã¯åçŽã«é©ãã¹ããã®ã«ãªããŸããããã¡ããèªåã§ç°å¢ã確èªããæ¹ãããã§ããããããšãã°ãç§ãã¡ã«ãšã£ãŠéèŠãªå Žæã¯æç¶ãã®åŒã³åºãã§ãããã®ããã«ãç§ãã¡ã¯èªå·±èšè¿°åã®ãªãŒãã³ãã¬ãŒã ã¯ãŒã¯ã䜿çšããŸã; ããã®éçºè
ã«ã¯esinevãjbdc-procããããŸãããªãŠã ã®æž¬å®ã¯æ¬¡ã®ãšããã§ãã
ã芧ã®ããã«ããµãŒããŒã®ããã©ãŒãã³ã¹ã¯åäžããŸããããéã«ããªãŠã ã¯20ïŒ
ã«äœäžããŸãããæ°ããããŒãžã§ã³ãã€ã³ã¹ããŒã«ãããšãããã«ããã¯ãããç°¡åã«èŠã€ããããšãã§ãã倿°ã®èšºæããŒã«ãå
¥æã§ããŸããããã«å ããŠãMySQLãæ£ããæ¹åã«é²ãã§ããããšãæããã«ãªããŸãããå®å®æ§ã確èªããããã«ããã€ãã®ããŒãžã§ã³ãåŸ
ã£ãŠãããããŒãžã§ã³ãã¢ããã°ã¬ãŒãããäºå®ã§ãã