ããŒã¿ãŒã»ã¶ã€ãã§ãïŒPerconaïŒ
ä»æ¥ã¯ããã©ãŒãã³ã¹ã«ã€ããŠã話ããŸãã
MySQLã®æé©åã«æ£ããã¢ãããŒãããæ¹æ³ãšããã®åé¡ã«å¯Ÿããããã€ãã®å®çšçãªã¢ãããŒããæ€èšããŸãã ãªããããéèŠã ãšæãã®ã§ããïŒ å®éãç¹å®ã®åé¡ãçºçããå Žåãããšãã°ããMySQLã§ãã£ãã·ã¥ãµã€ãºãã©ã®ããã«èšå®ããå¿ èŠããããŸããïŒã ãã®ãããªè³ªåã¯ãã€ã§ãGoogleãŸãã¯Yandexã«å ¥åããŠãããã«å¯ŸããåççãªåçãåŸãããšãã§ããŸãã ããããMySQLã®äžè¬çãåæããã³æé©åã¹ããŒã ã«ã€ããŠã®åçãåŸãã«ã¯ã©ãããã°ããã§ããããïŒ ããã¯ã¯ããã«è€éã§ãã
æåã«ç¥ã£ãŠããã¹ãããšã¯ãMySQLã®ããã©ãŒãã³ã¹ã ããæ¬åœã«éèŠã§ã¯ãªããšããããšã§ãã ãããŠãç§ã¯ãç¬é¡ã®åŸãããè¥ãç·ãèŠãŸãã ããã¯éèŠãªããšã§ã ãããéèŠã§ãªãå Žåãç§ãã¡ã¯äœã«ã€ããŠè©±ããŠããã®ã§ããïŒã
å®éããŠãŒã¶ãŒãäžåžã«ãšã£ãŠããŸã第äžã«ãMySQLã䜿çšããä»ã®ã·ã¹ãã ã䜿çšããå¯èœæ§ã®ããã¢ããªã±ãŒã·ã§ã³ã®ããã©ãŒãã³ã¹ãéèŠã§ãã ãããŠãããã¯ãã¢ããªã±ãŒã·ã§ã³ã®èŠ³ç¹ããåé¡ãèŠããšãä»ã®ããåºç¯ãªã¢ãããŒããçºèŠãããããçŠç¹ãåœãŠãã®ãçã«ããªã£ãŠããŸãã å Žåã«ãã£ãŠã¯MySQLã䜿çšãããŠããªãããé©åã«äœ¿çšãããŠããªãå¯èœæ§ããããŸãã
ãŸããã¢ããªã±ãŒã·ã§ã³ã®ããã©ãŒãã³ã¹ãåžžã«éèŠã§ããããšãéèŠã§ãã å Žåã«ãã£ãŠã¯ãç§ã¯äººã ãšè©±ãã圌ãã¯ç§ã«èšãïŒããŸããããã¯ç§ãã¡ã®ã¬ããŒãã§ãã ããã®ããã©ãŒãã³ã¹ã¯ç§ãã¡ã«ãšã£ãŠéèŠã§ã¯ãããŸããã1ã2ã3åããããŸãããã¹ãŠãç§ãã¡ã«åã£ãŠããŸããã ããããããªãã圌ãã«å°ãããªãã°ïŒãã¿ããªããããã®å ±åæžãããªãã«1ã¶æããããªããããã¯ããªãã«åã£ãŠããŸããïŒã 圌ãã¯èšãïŒããã¡ãããããã¯æ©èœããŸãããã ãããã£ãŠãçç£æ§ã¯éèŠã§ããäžéšã®äººã ã®é ã®äžã§ã¯ãéèŠãªããã©ãŒãã³ã¹ãšã¯ãããçš®ã®èŠæ±ãããªç§ãŸãã¯ç§åäœã§å®è¡ã§ããããšãæå³ããŸãã å€ãã®å Žåãæ°åãæ°æéãããããã»ã¹ã§ãããã©ãŒãã³ã¹ãéèŠã«ãªããšã¯èããŠããŸããã ãªããããéèŠãªã®ã§ããïŒ å€ãã®å Žåãäœããã®çš®é¡ã®ããŒã¿ããŒã¹ã¢ã«ãŽãªãºã ãå®è¡ããäœããã®çš®é¡ã®ã¯ãšãªãäœæãããšããã®è€éãã¯ããŒã¿éã«å¯ŸããŠéç·åœ¢ã«è¿ãããå¯èœæ§ãããããã§ãã ã€ãŸã ããŒã¿éã10åããå¢ããŠããããã¯ãšãªã®å®è¡é床ã100åã«ãªã£ãŠããå¯èœæ§ããããŸãã ãã®ãããªãã¬ãŒãã¯ãŸã£ããæåŸ ããŠããªãã£ããšããé©ããšé©ããçã¿åºããŸããã
ä»ã«äœãéèŠã§ããïŒ ãã®ããã©ãŒãã³ã¹ãšã¢ããªã±ãŒã·ã§ã³ã¯ãå¿ ãããMySQLã«é¢é£ãããšã¯éããŸããã ããã«ãããèå³æ·±ãã±ãŒã¹ããããŸãã ã¯ã©ã€ã¢ã³ãã«æ¥ãŠãMySQLãµãŒããŒãèŠãŠããã®ãèŠããŠããŸãã ãããŠããã¹ãŠãããŸããã£ãŠããããã§ãé·ãã¯ãšãªã¯ãªãããµãŒããŒã®è² è·ã¯10ïŒ æªæºã§ãããã¿ããªããªããããäžè¬ã«MySQLã§ãããšæããŸããïŒãç§ãã¡ã¯åžžã«ãã¹ãŠã®ããã©ãŒãã³ã¹ã®åé¡ã«å¯ŸåŠããŠããŸã-ããã¯åžžã«MySQLã«é¢ä¿ããŠãããããããã§æãäžããŸããã æã ããã§ãããå Žåã«ãã£ãŠã¯ããã§ã¯ãããŸããã
ãŸããMySQLã«åé¡ãããå Žåã§ããå€ãã®å Žåããœãªã¥ãŒã·ã§ã³ãMySQLã«é¢é£ããŠããªãå¯èœæ§ãããããšãéèŠã§ãã äŸãã°ãããªãã®å€ãã¯ã¹ãã£ã³ã¯ã¹ã®ã¢ã³ãã¬ã€ã»ã¢ã¯ã»ãããç¥ã£ãŠããŸãã ç¹ã«åæã®åœŒã®ããžãã¹ã®å€§éšåã¯ãMySQLå šææ€çŽ¢ã䜿çšããã®ã«é·ãèŠåŽããMySQLãŠãŒã¶ãŒãæãããšã§ããã ãããããã®è±¡ã¯é£ã¶ããšã¯ãããŸããã圌ãšäžç·ã«è©ŠããŠã¯ãããŸããã圌ãåãããããšããªãã§ãã ããã圌ã¯ã¹ã±ãŒãªã³ã°ããŸããã ãã®å Žåãå šææ€çŽ¢ãªã©ã®ã¿ã¹ã¯ã§ã¯ãSphinxã䜿çšããæ¹ãããåççãªãœãªã¥ãŒã·ã§ã³ã§ãã
ãããŠå®éã«ã¯ãMySQLãããŸãããŸã解決ã§ããªãããŸããŸãªåé¡ã解決ã§ãããããªããŒã«ããããããããŸãã ã€ãŸã å šææ€çŽ¢ãSphinx Elastic Search-å¥ã®ãªãã·ã§ã³ããããŸãã äžéšã®ã¿ã¹ã¯ã§ã¯ãRedisãŸãã¯Tarantoolã®æ¹ãã¯ããã«åªããŠããŸãã ããŒã¿åæã«ã€ããŠè©±ããŠããå Žåãããã§ã¯HadoopãŸãã¯VerticaããŸãã¯éåžžããŒã¿ã䞊ååŠçããäžéšã®ã·ã¹ãã ãé©ããŠããŸãã MySQLã¯1ã€ã®ãµãŒããŒäžã®1ã€ã®ã¹ã¬ããã§ã®ã¿ã¯ãšãªãå®è¡ããããããã¡ãã倧èŠæš¡ãªããªã¥ãŒã ã«ã¯ãŸã£ãã察å¿ããŠããŸããã
çç£æ§ã«ã€ããŠè©±ããšããäœãèããŠããŸããïŒ ãŸããå¿çæéã«ã€ããŠèããŸãã ãªãã§ïŒ å©å·±çãªãŠãŒã¶ãŒãèŠããšãããã圌ãæåã«èããããšã ããã§ãã ã€ãŸã ãŠã§ããµã€ãã«ã¢ã¯ã»ã¹ããããã¢ããªã±ãŒã·ã§ã³ã䜿çšããŠããŠããã®ã¢ããªã±ãŒã·ã§ã³ãããã«çããŠãããããããã ãã§èå³ããããŸãã 1ç§ãããã®ã¯ãšãªã1ç§ãããã®ãªã¯ãšã¹ãæ°ãããã³ãŠãŒã¶ãŒã®ãã®ä»ã®ãŽãã¯å°ãèå³ããããŸãã
ã€ãŸã ãŸããã¢ããªã±ãŒã·ã§ã³ã調ã¹ãŠãã¢ããªã±ãŒã·ã§ã³ããšã«ç°ãªãã¿ã¹ã¯ããšã«ç°ãªãå¿çæéã§ãŠãŒã¶ãŒã«å¿çããããã«ããŸãã 0.5ç§ã®å¿çæéãè¯å¥œã§ãããšããæ確ãªæ°åã¯ãªããããããã倧ãããã®ã¯ãã¹ãŠäžè¯ã§ãã
ããããå®éã®ã¢ããªã±ãŒã·ã§ã³ã«ã€ããŠè©±ããŠããå Žåãå€ãã®å Žåãå¿çæéã ãã§ãªãä»ã®ããšãèæ ®ããŸãã æåã®è³ªåã¯ããã®å¿çæéãå®å®ãããããšã§ãã ã€ãŸã éåžžã«å€ãã®å Žåãåé¡ã¯å¿çæéãåžžã«æªããšããããšã§ã¯ãªããã·ã¹ãã ãŸãã¯å€éšã§äœããçºçãããããæªåããããšããããšããããšã§ãã ããšãã°ãå€ãã®å Žåããã¹ãŠãç§ã«ãšã£ãŠããŸããããšäžæºãèšã人ãããŸãããããã¯ã¢ãããéå§ãããšããã¹ãŠãç§ã«ãšã£ãŠæªããã®ã«ãªãå§ããŸãã ãã®ãããªããšã«åŸãå¿ èŠããããŸãã ãŸãã¯ãããŠã³ããŒãã®æ¥å¢ãªã©ã®ããã€ãã®èŠçŽ ãšæ¥ç¶ããããšãã§ããŸããã€ãŸãããŠã§ããµã€ããæåãªãã¬ãçªçµã§è¡šç€ºãããããä»ã®èª°ãããããèšåããããããã©ãã£ãã¯ãæµãããããã·ã¹ãã ã¯å¯ŸåŠã§ããŸããã
ããã©ãŒãã³ã¹ã«ã€ããŠããå°ãã次ã®è³ªåã¯ãã¹ã±ãŒã©ããªãã£ã§ãã å€ãã®ã¢ããªã±ãŒã·ã§ã³ã¯ãããªãå°ããªã·ã¹ãã ããå§ãŸããŸãã å°éã®ããŒã¿ãå°éã®ãŠãŒã¶ãŒã圌ãã¯æé·ããèšç»ãæã£ãŠããŸãã ãããã®ã¢ã«ãŽãªãºã ãèŠæ±ãèšèšã䜿çšããã¹ããŒã ãçŸåšæ©èœããŠããã ãã§ãªããå°æ¥ã®ã¢ããªã±ãŒã·ã§ã³ã§ãæ©èœããŠããããšãéèŠã§ãã ãŸããçŸåšã®ããŒã¿éã«é¢ããŠãçŸåšæãæé©ãªã¢ãããŒãã¯ããã€ããããŸããããããã¯ã¹ã±ãŒãªã³ã°ãããŸããã
3çªç®ã®ãã€ã³ãã¯å¹çæ§ã§ããããããéèŠã§ãã ãªãœãŒã¹ã®äœ¿çšãšãã芳ç¹ãšããã®ã¢ãŒããã¯ãã£ã®äœæã«å¿ èŠãªäººã ã®èŠ³ç¹ã®äž¡æ¹ã®å¹çã ããšãã°ãHadoopã®èå³æ·±ããªãã·ã§ã³ã§ãã çŸåšãHadoopã¯ãã§ã«åªããŠããŸãããäŒçµ±çã«ãHadoopãå§ãŸã£ãã°ããã®ãšãã¯ãHadoopã¯äœåãã®ããŒãã§ã¹ã±ã«ãã³ã§ããã ããããããšãã°MySQLãšHadoopã®ããã©ãŒãã³ã¹ãæ¯èŒãããšã1ã€ã®ããŒãã®MySQLã¯10ã®Hadoopãšåãããšãã§ããŸãã ãŸããããŒã¿éãååã«å°ãªãå ŽåãMySQLã䜿çšããæ¹ãã¯ããã«å¹ççã§ããããšã¯æããã§ãã ãã ãã1ã€ã®ãµãŒããŒã«å¹²æžããªããã®ãå¿ èŠãªå Žåã¯ãHadoopã®èŠ³ç¹ããã1,000ãŸãã¯10,000ããŒããèµ·åã§ããŸãã MySQLã§ãããè¡ãããšã¯ã§ããŸããã
ã¹ã±ãŒã©ããªãã£ã®èŠ³ç¹ãããè² è·ãããŒã¿ã®ãµã€ãºã«ã€ããŠã¯ãã§ã«èª¬æããŸããããã€ã³ãã©ã¹ãã©ã¯ãã£ã®èŠ³ç¹ããã®ã¹ã±ãŒã©ããªãã£ãéèŠã§ãã ããã§èšåãã䟡å€ããããã®ã¯äœã§ããïŒ ããã¯ãäœæ¥ã¢ãŒãã«å¿ããŠã1ã€ãŸãã¯å¥ã®ã€ã³ãã©ã¹ãã©ã¯ãã£ã䜿çšã§ããå ŽåããããŸãã ããšãã°ãå€ãã®ã¯ã©ãŠããããã¯ãŒã¯ã§ã¯ãããã»ã©ç°¡åã«ã¹ã±ãŒã«ã¢ããã§ããŸããã ã64ã³ã¢ã1 TBã®ã¡ã¢ãªã4ã€ã®éåžžã«é«éãªãã©ãã·ã¥ã«ãŒããæèŒãããµãŒããŒãèšçœ®ããŠã10 TBã®ããŒã«ã«ãã©ãã·ã¥ãåä¿¡ã§ããããã«ãããã ããŒã¿ã»ã³ã¿ãŒã§åããŠããŠãã€ã³ãã©ã¹ãã©ã¯ãã£ãéžæã§ããå Žåã¯ããããå ¥æã§ããŸãããã®ãããªãµãŒããŒã40 GBã€ãŒãµããããŸãã¯è€æ°ã®ã€ãŒãµãããã§æ¥ç¶ããããšãã§ããŸãã ã¯ã©ãŠãã§äœæ¥ããå Žåã¯ãäœæ¥ããå¿ èŠãããå®å šã«ç°ãªãå¶éãããå ŽåããããŸãã
ããééãã次ã®ãã€ã³ãã å®éã®ã¢ããªã±ãŒã·ã§ã³ãéçºãããšããçç£æ§ãéèŠã«ãªãããšããããããŸããããããç§ãã¡ã泚æããªããã°ãªããªãå¯äžã®ãã®ã§ã¯ãããŸããã ããšãã°ãã»ãã¥ãªãã£ã éåžžãã»ãã¥ãªãã£ããã©ãã£ãã¯æå·åã¯ããã©ãŒãã³ã¹ãæ¹åããŸããããããã©ãããããããã¯ææ ¢ããªããã°ãªããªãããã©ãŒãã³ã¹ãäœäžãããå¯èœæ§ããããŸãã
管çæ§ãå¥ã®ãã€ã³ãã§ãã ã€ãŸã å€ãã®å Žåãæé©åãããã³ãŒããšæ§é -ããªãã¯åœŒã®ã€ããžã¯ãåŸã§ç解ããã§ãããã å€ãã®å Žåãããã«ããã³ãŒããè€éã«ãªãããããšãããã©ãŒãã³ã¹ãæé©åããããªããšããããšãããããŸãã ãŸããä»ã«ãèæ ®ãã¹ãããšããããŸãã ããšãã°ãå€ãã®å ŽåãããŒã¿ã®å€æŽããã°ã«èšé²ããå¿ èŠãããç£æ»ã®ããŒãºãããå ŽåããããŸããããã«ãããããã©ãŒãã³ã¹ã®ç¹ã§äœããç ç²ã«ããè¿œå æ©èœãè¿œå ãããŸãã
èšåãã䟡å€ããã次ã®ãã€ã³ãã ããã¯çç£æ§ãç¡éã«æ¹åã§ããããšã ãšããããšã§ãã å¯èœãªéãæé©åãããã·ã¹ãã ã¯ãªããå€ãã®ãéãšæéãè²»ãããŠãçç£æ§ãå°ãªããšãæ°ããŒã»ã³ãåäžãããããšã¯ãŸã äžå¯èœã§ãã ããã¯ããã€åæ¢ããããåžžã«ç¥ãå¿ èŠãããããšãæå³ããŸããããããªããšããªãœãŒã¹ãå®å šã«ç¡é§ã«ããŸãã ã€ãŸããå¿ èŠãªå¹çã®çš®é¡ãã·ã¹ãã ããã©ãŒãã³ã¹ãªã©ãäœããã®æ¹æ³ã§æ±ºå®ããå¿ èŠããããŸãã ãããŠãããã«éãããããããããšãã§ããŸãã
次ã®ç¬éã ãã§ã«MySQLã«æž¡ããŠããŸãã ã·ã¹ãã ã®å¿çæéãéèŠã§ãããšè¿°ã¹ãŸããã 次ã«ãäžè¬ã«MySQLããŠãŒã¶ãŒã«å¯ŸããŠè¡ãããšãèŠãŠã¿ãŸãããã ãŸããéåžžã«ç°¡åã«ãªããŸããMySQLã®ãªã¯ãšã¹ããå®è¡ããéžæãæŽæ°ãæ¿å ¥ãããã°ããããŠããå¿çããŸãã ãŸããMySQLã®ããã©ãŒãã³ã¹ã«é¢é£ãããã¹ãŠã®ããšã¯ãã¯ãšãªã«å¯Ÿãããã®å¿çã«ãã£ãŠç¹åŸŽä»ããããŸãã
åœç¶ã®ããšãªãããããŸããŸãªè² è·ãããŸããŸãªããŒã¿éãªã©ããããŸãã ãããããããã®èŠæ±ãèŠãŠãå¿çæéãèŠãŠãæé©åã®èŠ³ç¹ããããã«çŠç¹ãåœãŠããšãååŸã§ããçµæãåŸãããŸãã
MySQLã®æé©åã«ã€ããŠè©±ããŠããã®ã¯ãã€ã§ããïŒ ãã®ã¹ã©ã€ãã«èŒã£ãŠããªãããšã1ã€èšã£ãŠãã䟡å€ãããã§ãããã ããã¯ãæé©ãªè³ªåãäœããæé©åããããšã§ãããšããäºå®ã«é¢ãããã®ã§ã-ããã¯ãããè¡ãããšã§ã¯ãããŸããã æããã«ãããã¯èª°ãããããèãããŸãããMySQLã®èŠ³ç¹ããèå³æ·±ãã®ã¯ãã»ãšãã©ã®äººã«æ¥ãããã¢ããªã±ãŒã·ã§ã³ãååŸããMySQLãã°ããªã³ã«ããŠããŒãžãããŒãããããšã§ãã ã»ãšãã©ã®å Žåãç¹°ãè¿ãããããã€ãã®ã¯ãšãªãèŠã€ãããŸãã 2çªç®ã®èŠæ±ã¯åœ¹ã«ç«ããªããããç°¡åã«åé€ã§ããããšãç¥ã£ãŠããŸãã å€ãã®å ŽåãããŒã¿ããŒã¹ããããŒã¿ãèŠæ±ãããã¢ããªã±ãŒã·ã§ã³ã§äœ¿çšãããŠããªãããšãããããŸãã ãããããªãã§ããïŒ -ããŸããããã§ããã®ã¯ã©ã¹ã¯ããã§åæåãããŸããäžãäžã®ããã«ããŒã¿ããŒã¹ãããã¹ãŠãæã¡äžããŸãããå®éã«ã¯äœ¿çšããŸãããã å€ãã®å Žåãã¢ããªã±ãŒã·ã§ã³ã§ã¯ãäžèŠãªããŒãããŒããäœæãããŸãã ããããæé©åã«çŠç¹ãåœãŠããšããç§ãã¡ã¯ãã°ãã°äœã«åãçµãã§ããŸããïŒ æåã®éåžžã«ç°¡åãªç¬éã質åãããéãå®è¡ãããå Žåãä»ã®ç¬éãããå°ãªããªãœãŒã¹ãããšãã°ããå°ãªãã¡ã¢ãªã䜿çšãããå ŽåããŸãã¯å§çž®ã®å©ããåããŠãã·ã¹ãã ãæé©åããŠãã£ã¹ã¯é åãããå°ãªããããå Žåã ãŸãã¯ãããŒããŠã§ã¢ãããŒã¿ãµã€ãºãªã©ã®ç¹ã§ã¹ã±ãŒã©ããªãã£ãåäžããŠããŸãã
æé©åãšäžè¬çãªã¢ãããŒãã«ã€ããŠè©±ãå Žåãå€ãã®å Žå2ã€ã®ããšãæ€èšããŸãã 1ã€ã¯ãã©ã³ã¶ã¯ã·ã§ã³ã®æé©åã§ãã ããšãã°ãã¢ããªã±ãŒã·ã§ã³ãããã誰ãã次ã®ããã«èšã£ãŠããŸããããåç¥ã®ããã«ãæ€çŽ¢æ©èœãŸãã¯ã¢ãŒã«ã€ãã®é床ãäœäžããŸããå¿ èŠä»¥äžã«åäœãé ããªããŸããã ãã®é¢æ°ããããMySQLã§è¡ãã¯ãšãªãå ·äœçã«èŠãŠãæé©åããããšãã§ããŸãã å¥ã®éžæè¢ã¯ãã¢ããªã±ãŒã·ã§ã³å šäœãæé©åãããšããããšã§ãã 次ã«ãMySQLã«å°éãããã¹ãŠã®ã¯ãšãªã調ã¹ãã©ã®ã¯ãšãªãæãé ããã©ã®ãªãœãŒã¹ãæãå€ã䜿çšãããã確èªããããããæé©åãããšãã·ã¹ãã ãäžè¬çã«éãåäœããããšã¯æããã§ãã ãããåæã«ã2çªç®ã®ã¢ãããŒãã䜿çšããå Žåããã¬ãŒãã³ã°ããã»ã¹ãã©ãã ãæé©åããããã«ã€ããŠã®ä¿èšŒã¯ãããŸããããªããªããããã¯å€ãã®è² è·ãäœæããªãå¯èœæ§ãããããã§ããããã¯ãããããäžåžãèŠãŠããäœããã®åæã§ã1æ¥ã«1åãè² è·ã®0.01ïŒ ã§ãããããã§ãéåžžã«éèŠã§ãã
ãŸããäžè¬çãªæé©åã®èŠ³ç¹ããããªã¯ãšã¹ãã確èªãããããã«åªå é äœãä»ããããã«æé©åããããšã¯æããã§ãã
ã¯ãšãªã«é¢ããŠã ç§ãèšã£ãããã«ãæåã®è³ªåã¯ã¯ãšãªã®èŠ³ç¹ããã§ãïŒãããã®ã¯ãšãªãé¿ããããšãã§ããŸããïŒ å€ãã®å Žåãäžéšã®ã¯ãšãªã¯åœ¹ã«ç«ããªãããã§ãã 2çªç®ã®è³ªåã¯ããããã®ã¯ãšãªãå€æŽããŠãäœæ¥éãæžããããšãã§ãããã©ããã§ãã less workã¯ã©ãããæå³ã§ããïŒ ããã¯ãã¹ãã£ã³ããè¡æ°ãå°ãªãããšããã£ã¹ã¯ããèªã¿åãããŒã¿ãå°ãªãããšãªã©ãæå³ããŸãã -ããããã¹ãŠããåçŽãªåèªããäœæ¥ããšåŒã³ãŸãã
ã©ã®ãããªäŸããããŸããïŒ ã€ã³ããã¯ã¹ãªãã§å®è¡ããã¯ãšãªããããå€ãã®äœæ¥ãè¡ããŸãã ã€ã³ããã¯ã¹ãè¿œå ããŸãã-ã¯ãšãªã®åäœãå°ãªããªããŸãã ã€ã³ããã¯ã¹ãè¿œå ãããããMySQLã®èšå®ãå€æŽãããã»ã©åçŽãªå Žåã ãã以å€ã®å Žåãããè€éãªãã®ã«ãªãå¯èœæ§ããããŸããããšãã°ãã¯ãšãªãè¿ éã«å®è¡ãããããã«ã€ã³ããã¯ã¹ãè¿œå ã§ããªã貧ããããŒã¿ã¹ããŒã ãããå ŽåããããŸãã 次ã«ãã¯ãšãªã€ã³ããã¯ã¹ã ãã§ãªããããŒã¿ã¹ããŒã ãå€æŽããå¿ èŠããããŸãã ãã£ãã·ã³ã°ãªã©ãä»ã®ã¢ãããŒããé©çšããå¿ èŠãããå ŽåããããŸãã ããŒã¿ããã£ãã·ã¥ããããšãã§ããããŒã¿ãæ¯ç§äœåãã·ã£ãã«ãã代ããã«ãäœããã®ãµããªãŒããŒãã«ãã€ãŸããã¹ãŠã®çµæãçæããããŒãã«ãäœæã§ããŸãã
次ã®æé©åã®è³ªåïŒäœãéèŠã§ããïŒ ãŸããã¯ãšãªã®å¹³åå®è¡æéã ãã調ã¹ãã¹ãã§ã¯ãããŸããã ãªã¯ãšã¹ãã®å¹³åå®è¡æéãåé¡ã«ãªãããšã¯ãã£ãã«ãªãã®ã§ããŸã第äžã«ãåé¡ã¯ããã€ãããå§ãŸããŸãã極端ãªããšã§ã¯ãªãããã®æ¡ä»¶ã«è¿ããšèšãããã§ãã ããšãã°ãFlickrïŒæ¯èŒçå€ãããåçã®å€§èŠæš¡ãªãã¹ãã£ã³ã°äŒç€ŸïŒã®äººã ããAPIãä»ããŠå人ã100äžæã®åçãã¢ããããŒããããšãã«åé¡ãçºçãããšèšã£ãããšãèŠããŠããŸãã 1,000ã100æã®ç»åã®ã«ããŽãªã§ã¯ã100äžæã®ç»åãæ©èœããªããªã£ããããéåžžã®ãŠãŒã¶ãŒã§æ©èœãããã®ãæ£åžžã§ããããšã¯æããã§ãã å¹³åçãªãªã¯ãšã¹ããå¹³åçãªãŠãŒã¶ãŒã®èŠ³ç¹ããã¯ãããã¯æãããªãã§ãããã
次ã®ãã€ã³ãã¯ãããã€ãã®åé¡ãåšæçã«çºçãããããæéã®çµéã«äŒŽãåŸåã確èªããããšããããšã§ããããã¯ãæ¯æã®è«æ±ãåå ã§ããããé±ã«äžåºŠå€ãããŒã¿ãåé€ããããšãåå ã§ãããåé¡ã
ç¹°ãè¿ãã«ãªããŸããããªã¯ãšã¹ããèŠããšãã¯ãå°æ¥ã«ã€ããŠèããå¿ èŠããããŸãã ç¹ã«éçºã·ã¹ãã ã§ã éçºè ã¯ããã¹ãã®ããã«æ§æå ã®ããã€ãã®å°ããªããŒã¿ããŒã¹ã§ã¯ãšãªããã¹ãããMySQLã§èª¬æãããŸãèªã¿ãŸããã ãã®å Žåããªã¯ãšã¹ããæ¬çªçšã«ã¬ã€ã¢ãŠããããããå°ãé ããŠãããŒã¿ãæ¥æ¿ã«å¢å ãå§ãããšãããããã¹ãŠãå£ããŸãã 説æãèªãã§ããããã®ã¯ãšãªãå°æ¥ã©ã®ããã«æ©èœãããã«ã€ããŠèããããšãåŠã¶å¿ èŠããããŸãã
次ã®ç¬éã 䜿çšããã¯ãšãªãæžããããããšã¯æããã§ãã åãæ°ã®è¡ãåæããŠãã䜿çšã§ããã¯ãšãªãå°ãªãã»ã©è¯ãã§ãã ãªã¯ãšã¹ãããšã«ãããã¯ãŒã¯å¿çæéãè¿œå ããããããªã¯ãšã¹ãã®è§£æãæš©éã®ãã§ãã¯ãªã©ã®ããã®ãªãŒããŒããããè¿œå ãããŸãã ãŸããã§ããã ãå°ãªãããŒã¿ãèªã¿åããå€æŽããããšèããŠããŸãã ã€ãŸã ããšãã°ãå¶é10ã®èŠæ±ãæºããããšãã§ããå Žåãããã¯ãã¹ãŠã®ããŒã¿ãèªã¿åããã¢ããªã±ãŒã·ã§ã³ã®æåã®æ°è¡ãèªã¿åããæ®ããç Žæ£ãããããã¯ããã«åªããŠããŸãã
åãããšã¯ãããŒã¿ããŒã¹å ã®åãã¢ããªã±ãŒã·ã§ã³ãå®éã«äœ¿çšããåã®ã¿ãèªã¿åãæ¹ãè¯ããšããããšã§ãã ãããã®å ŽåãMySQLã¯ä»ã®è¿œå ã®æé©åã䜿çšããŠãã¯ãšãªãããå¹ççã«å®è¡ã§ããããã§ãã
ããã«ã ã¯ãšãªã®å®è¡äžã«åŠçã§ããããŒã¿ãå°ãªãã»ã©åªããŠããŸãã ç¹°ãè¿ããŸãããåãŠãŒã¶ãŒã«å¯ŸããŠæ¯åäœããã®è€éãªéèšãè¡ãéãã¯ãšãªã¯ã倧èŠæš¡ãªéèšã«ã¯ããŸãé©ããŠããŸããããã£ãã·ã¥ä»ãã®ããŒãã«ãšããŠäœæããæ¹æ³ãªã©ã確èªããããšããå§ãããŸãã
äœãèŠãããšãã§ããŸããïŒ ã©ã®ãããªèå³æ·±ãææšããããŸããïŒ 1ã€ç®ã¯ãã¯ã©ã€ã¢ã³ãã«éä¿¡ããéã«é¢ããŠMySQLãåæããããŒã¿ã®éã確èªã§ããããšã§ãã ãã®ã¡ããªãã¯ã¯ãMySQLããç°¡åã«ååŸã§ããŸãã ããšãã°ãã¹ããŒã¯ãšãªãã°ã®åã¯ãšãªã§ã¯ãããŒã¿ã¯Rows_sentããã³Rows_examinedã§ãã ãããã®éã®é¢ä¿ã¯ãç§ãã¡ãèŠãããã®ã§ãã æ¯çã1ã10ã®å Žåãã¢ããªã±ãŒã·ã§ã³å šäœã¯éåžžæé©åãããŠããŸãã MySQLããéä¿¡ããåè¡ã§100ãããã«ã¯1,000ãåŠçããå Žåãããããæé©åã®æ©äŒãå€ããããŸãã
次ã®ãã€ã³ãã¯ãã¢ããªã±ãŒã·ã§ã³ã䜿çšããéã«é¢ããŠãMySQLããå®éã«éä¿¡ããããŒã¿éã確èªããããšã§ãã ããã§ãæ®å¿µãªãããMySQLãããŒã¿ãæäŸãããããMySQLãæäŸããã¡ããªãã¯ã¯ãããŸããã ããããå€ãã®å Žåãå°ãªããšãå€ãã®ããŒã¿ãè¿ãæãæ·±å»ãªã¯ãšãªã«ã€ããŠã¯ãèªåã§ç¢ºèªã§ããŸãã ã€ãŸã ã¢ããªã±ãŒã·ã§ã³ããšã«100äžè¡ãèªãå Žåãæ éã«æ€èšããå¿ èŠããããŸããããã¹ãŠããã§äœ¿çšããŠããŸããïŒ 1ããŒãžã«100äžåã®èŠçŽ ã衚瀺ãããããšã¯ã»ãšãã©ãããŸããã ãã¶ããããã¯äœããã®åœ¢ã§éçŽãããŠããããã®å ŽåãããŒã¿ããŒã¹ã§ãã®äœæ¥ãè¡ãæ¹ãè¯ãå ŽåããããããŸãã
次ã®ç¬éã æ¢ã«è¿°ã¹ãããã«ãåã«ã€ã³ããã¯ã¹ãè¿œå ããã ãã§ã¯ãšãªãæé©åã§ãããšã¯éããªããããå€ãã®å Žåãã¹ããŒããããåçã«å€æŽããå¿ èŠããããããåè·¯ã®æé©åãšã¯ãšãªã®æé©åãäžç·ã«èŠãã®ãçã«ããªã£ãŠããŸãã ãããŠã2ã€ã®ããšããããŸãã
- ãããã¯å°ããªã¹ããŒã ã®æé©åã§ã-ã€ã³ããã¯ã¹ãè¿œå ããåã®ã¿ã€ããªã©ãå€æŽããMySQLã®ã¹ãã¬ãŒãžãå€æŽããŸãã
- èšèšãšã¢ãŒããã¯ãã£å šäœã®å€§ããªå€æŽãå¿ èŠã«ãªãããšããããŸãããéåžžã¯ã¯ããã«æ·±å»ãªèšç»ãå¿ èŠã§ãã ã€ãŸã åè·¯ã®å®å šãªãªãšã³ãžãã¢ãªã³ã°ãè¡ãå Žåãããªãã®æéãããããŸãã
. , . .
. , , . , , IR , «» , , , . , . , , . , Partitioning & Sharding, , .. Google .
, â , , , MySQL-, MySQL-. .
, , , ? -, , , . MySQL, , , - , , - â , .
, , - , MySQL. , .
, , , , . , , , , , , «, , MySQL » ..
, , .
. , , - , , , , , .
? :
. , â Intel. C MySQL , . ? MySQL , , , , . . turboboost, , , -, . , turboboost, , â , .
. , . , , . â , . - . , . , , , , , , , , , , - . , , , , .
:
. ã€ãŸã â RAID, , â FusionIO. ? , , .. , 5 . , . : working set , . - , , , . , , , .
? , . , , , MySQL OLT , ??, .. , , .
MySQL- , , , MySQL . , . , , , , , MySQL- application- . (network hops), . , , application- , , , â , , « «» - ». â , , .
. MySQL Linux. - MySQL - ? FreeBSD, Windows server. MySQL - Linux. , , , . , , , , , , â , , .
Linux. , MySQL- . , - Google, Facebook â 3-5% , , , . â , , EXT4 XFS. ,
, . .
â MySQL. ? MySQL . , .. , , MySQL , , , MySQL. MySQL 5.7, . , , , .. MySQL, , , Percona Server 5.7, .
MySQL-. , MySQL- . ã€ãŸã MySQL . , : «-! 400 ». , , 5-10-20, ,
. -, .
, , Google, , .
â .
:
- -, , , , , . ã€ãŸã , , -, , , , . .
- , , , , , , - . , - . , - , , MySQL , , , . - , , , , , , , , , - , , Linux - . , , .
. , , â , , MySQL â - . , - , , , - .
, , .
, . MySQL performance Schema Graphite.
â http://bit.ly/1KQSNWC
, , MySQL, , - , , , , , , 10 .
, . . , .
â http://www.meetup.com/moscowmysql/
â http://bit.ly/PL16Call
» pz@percona.com
â HighLoad++ .
" - " . 16 , 9. , :
- Postgres vs Mongo / (PostgreSQL Professional);
- , , : / (Percona);
- MySQL: / (Percona);
- highload / .
!