ããã¯ãšã³ãéçºè ã¯ãSQL ã¯ãšãªããæ¬çªãã§é©åã«æ©èœããããšãã©ã®ããã«ç解ããŠããã®ã§ãããã? 倧äŒæ¥ãæ¥éã«æé·ããŠããäŒæ¥ã§ã¯ã誰ããã補åãã«ã¢ã¯ã»ã¹ã§ããããã§ã¯ãããŸããã ãŸããã¢ã¯ã»ã¹ããå Žåããã¹ãŠã®ãªã¯ãšã¹ããç°¡åã«ãã§ãã¯ã§ããããã§ã¯ãªããããŒã¿ããŒã¹ã®ã³ããŒã®äœæã«ã¯æ°æéãããããšããããããŸãã ãããã®åé¡ã解決ããããã«ãç§ãã¡ã¯äººé DBAããžã§ãŒãäœæããŸããã ãã§ã«ããã€ãã®äŒæ¥ã§å°å ¥ãæåããŠãããåæ°ç€Ÿã®éçºè ãå©ããŠããŸãã
ãããªïŒ
ããã«ã¡ã¯ãã¿ããªïŒ ç§ã®ååã¯ã¢ãããªãŒã»ã¹ã¿ã³ã¹ã©ãŒã§ãã ç§ã¯äŒç€Ÿã«å€ããŠããŸã
ç§ãã¡ã«ã¯çŽ æŽãããã¯ã©ã€ã¢ã³ããããŸããä»æ¥ã®ã¬ããŒãã®äžéšã¯ã圌ããšã®ä»äºäžã«åºäŒã£ãã±ãŒã¹ã«åœãŠãããŸãã éåžžã«æ·±å»ãªåé¡ã®è§£æ±ºãç§ãã¡ãã©ã®ããã«æ¯æŽãããã«ã€ããŠã話ããŸãã
è€éã§é«è² è·ãªç§»è¡ãéçºããã³å®è¡ãããšããç§ãã¡ã¯ããã®ç§»è¡ã¯ããŸãããã ããã?ããšãã質åãèªåããŸãã ç§ãã¡ã¯ã¬ãã¥ãŒãå©çšããããçµéšè±å¯ãªååã DBA å°é家ã®ç¥èãå©çšããŸãã ãããŠããããé£ã¶ãã©ãããç¥ãããšãã§ããŸãã
ãããããã«ãµã€ãºã®ã³ããŒã§èªåãã¡ã§ãã¹ãã§ããã°ãã£ãšè¯ããããããŸããã ãããŠä»æ¥ã¯ããã¹ãã«å¯ŸããçŸåšã®ã¢ãããŒããã©ã®ãããªãã®ã§ãããããŸããã¹ããããè¯ãè¡ãã«ã¯ã©ãããã°ãããããŸãã©ã®ãããªããŒã«ã䜿çšããã°ãããã«ã€ããŠã話ããŸãã ãŸãããã®ãããªã¢ãããŒãã®é·æãšçæãããã³ããã§ä¿®æ£ã§ããç¹ã«ã€ããŠã説æããŸãã
ãããŸã§ã«æ¬çªç°å¢ã§çŽæ¥ã€ã³ããã¯ã¹ãäœæããããå€æŽãå ããããã人ã¯èª°ã§ãã? ããªãã®éã§ãã ãããŠããããããŒã¿ã®æ倱ãããŠã³ã¿ã€ã ã®çºçã誰ã«ãããããã®ã§ãããã? ããããã°ããã®çã¿ãããããŸãã ãããããããšã«ããã¯ã¢ããããããŸãã
æåã®ã¢ãããŒãã¯ãæ¬çªç°å¢ã§ã®ãã¹ãã§ãã ãããã¯ãéçºè ãããŒã«ã« ãã·ã³äžã§ãã¹ã ããŒã¿ã䜿çšããŠããå Žåãããçš®ã®éãããéžæè¢ãååšããŸãã ãããŠæ¬çªç°å¢ã«ããŒã«ã¢ãŠããããšããã®ç¶æ³ãåŸãããŸãã
çãããé«ããã ããããããããªãã»ããããã§ãããã
ãããŠããããè¡ãããã®æè¯ã®æ¹æ³ã¯äœã§ãã?
ã¹ããŒãžã³ã°ãè¡ã£ãŠãããã§æ¬çªã®äžéšãéžæããŸãããã ãããããå®éã®è£œåããã¹ãŠã®ããŒã¿ãååŸããŸãããã ãããŠãããŒã«ã«ã§éçºããåŸãããã«ã¹ããŒãžã³ã°ã確èªããŸãã
ããã«ããããšã©ãŒã®äžéšãåé€ã§ããããã«ãªããŸããã€ãŸããæ¬çªç°å¢ã§ãšã©ãŒãçºçããã®ãé²ãããšãã§ããŸãã
åé¡ç¹ã¯äœã§ãã?
- åé¡ã¯ããã®æŒåºãååãšå ±æããŠããããšã§ãã ãããŠãããçš®ã®å€æŽãå ãããšãããŒã¿ããªããäœæ¥ãç¡é§ã«ãªãããšãéåžžã«ãããããŸãã ã¹ããŒãžã³ã°ã¯ââæ°ãã©ãã€ãã«éããŸããã ãããŠåã³äžæãããŸã§é·ãæéåŸ ããªããã°ãªããŸããã ãããŠææ¥æçµæ±ºå®ããããšã«ããŸããã 以äžãéçºãå®äºããŸããã
- ãããŠãã¡ãããããã§ã¯å€ãã®ååãå€ãã®ããŒã ãåããŠããŸãã ãããŠããã¯æåã§è¡ãå¿ èŠããããŸãã ãããŠãããã¯äžäŸ¿ã§ãã
ããŒã¿ããŒã¹ã«å€æŽãå ããããããŒã¿ã«è§Šããããæ§é ãå€æŽãããããå Žåãè©Šè¡ã¯ XNUMX åã ãã§ãããšããããšãéèŠã§ãã ãŸããäœãåé¡ãçºçããå Žåã移è¡äžã«ãšã©ãŒãçºçããå Žåãããã«ããŒã«ããã¯ããããšã¯ãããŸããã
ããã¯åã®ã¢ãããŒããããåªããŠããŸãããäŸç¶ãšããŠæ¬çªç°å¢ã§äœããã®ãšã©ãŒãçºçããå¯èœæ§ãé«ããªããŸãã
åéçºè ã«ãã¹ããã³ããã€ãŸããã«ãµã€ãºã®ã³ããŒãæäŸããããšã劚ãããã®ã¯äœã§ãããã? äœã劚ãã«ãªã£ãŠãããã¯æããã ãšæããŸãã
ãã©ãã€ããè¶ ããããŒã¿ããŒã¹ãæã£ãŠããã®ã¯èª°ã§ãã? éšå±ã®åå以äžã
ãããŠãããã»ã©å€§èŠæš¡ãªçç£ãããå Žåãåéçºè ã«ãã·ã³ãç¶æããã®ã¯éåžžã«é«äŸ¡ã§ãããããã«é·ãæéããããããšã¯æããã§ãã
ãã¹ãŠã®å€æŽããã«ãµã€ãºã®ã³ããŒã§ãã¹ãããããšãéåžžã«éèŠã§ããããšãèªèããŠããã¯ã©ã€ã¢ã³ããããŸãããããŒã¿ããŒã¹ã¯ XNUMX ãã©ãã€ãæªæºã§ãããåéçºè çšã®ãã¹ããã³ããç¶æãããªãœãŒã¹ããããŸããã ãããã£ãŠããã³ããããŒã«ã«ã®ãã·ã³ã«ããŠã³ããŒããããã®æ¹æ³ã§ãã¹ãããå¿ èŠããããŸãã ãšãŠãæéãããããŸãã
ã€ã³ãã©ã¹ãã©ã¯ãã£å ã§å®è¡ãããšããŠãã200 æéããã XNUMX ãã©ãã€ãã®ããŒã¿ãããŠã³ããŒãã§ããã®ã¯ããã§ã«éåžžã«åªããŠããŸãã ãã ããè«çãã³ãã䜿çšããã¯ã©ãŠãããããŒã«ã«ã«ããŠã³ããŒãããŸãã 圌ãã«ãšã£ãŠããã®é床㯠XNUMX æéãããçŽ XNUMX ã®ã¬ãã€ãã§ãã ãããŠãè«çãã³ãããã®æ¹å転æãã€ã³ããã¯ã¹ã®ããŒã«ã¢ãããªã©ã«ã¯äŸç¶ãšããŠæéãããããŸãã
ãããã補åã®ä¿¡é Œæ§ãç¶æã§ããããããã®ã¢ãããŒãã䜿çšããŠããŸãã
ããã§äœãã§ããã§ããããïŒ ãã¹ããã³ããå®äŸ¡ã«ããŠããã¹ãŠã®éçºè ã«ç¬èªã®ãã¹ããã³ããæäŸããŸãããã
ãããŠããã¯å¯èœã§ãã
ãããŠããã®ã¢ãããŒãã§ã¯ãéçºè ããšã«ã·ã³ ã¯ããŒã³ãäœæãããšãããã 10 å°ã®ãã·ã³ã§å ±æã§ããŸãã ããšãã°ã10 TB ã®ããŒã¿ããŒã¹ããããããã XNUMX 人ã®éçºè ã«æäŸãããå ŽåãXNUMX TB ã®ããŒã¿ããŒã¹ã XNUMX åçšæããå¿ èŠã¯ãããŸããã XNUMX å°ã®ãã·ã³ã䜿çšããŠéçºè ããšã«èãåé¢ã³ããŒãäœæããã«ã¯ãXNUMX å°ã®ãã·ã³ã®ã¿ãå¿ èŠã§ãã ä»çµã¿ã«ã€ããŠã¯å°ãåŸã»ã©èª¬æããŸãã
å®éã®äŸ:
-
DB - 4,5 ãã©ãã€ãã
-
ç¬ç«ããã³ããŒã 30 ç§ã§ååŸã§ããŸãã
ãã¹ãã¹ã¿ã³ããåŸ ã€å¿ èŠãããã®å€§ããã«äŸåããå¿ èŠããããŸããã æ°ç§ã§ååŸã§ããŸãã å®å šã«åé¢ãããç°å¢ã«ãªããŸããããããã®éã§ããŒã¿ãå ±æããŸãã
ããã¯çŽ æŽãããã ããã§ã¯éæ³ãšäžŠè¡äžçã«ã€ããŠè©±ããŸãã
ç§ãã¡ã®å Žåããã㯠OpenZFS ã·ã¹ãã ã䜿çšããŠæ©èœããŸãã
OpenZFS ã¯ãããã«äœ¿ããã¹ãããã·ã§ãããšã¯ããŒã³ããµããŒãããã³ããŒãªã³ã©ã€ã ãã¡ã€ã« ã·ã¹ãã ã§ãã ä¿¡é Œæ§ãšæ¡åŒµæ§ã«åªããŠããŸãã 圌女ã¯ãšãŠãæ±ããããã§ãã æåéã XNUMX ã€ã®ããŒã ã«å±éã§ããŸãã
ä»ã«ã次ã®ãªãã·ã§ã³ããããŸãã
-
LVMã
-
ã¹ãã¬ãŒãž (Pure Storage ãªã©)ã
ç§ã話ããŠããããŒã¿ããŒã¹ ã©ãã¯ã¢ãžã¥ãŒã«åŒã§ãã ãããã®ãªãã·ã§ã³ã䜿çšããŠå®è£ ã§ããŸãã ããããç¹ã« LVM ã«åé¡ããã£ããããçŸæç¹ã§ã¯ OpenZFS ã«çŠç¹ãåœãŠãŠããŸãã
䜿ãæ¹ïŒ ããŒã¿ãå€æŽãããã³ã«äžæžãããã®ã§ã¯ãªãããã®æ°ããããŒã¿ãæ°ããæç¹ãã€ãŸãæ°ããã¹ãããã·ã§ããããã®ãã®ã§ããããšãåã«ããŒã¯ããããšã«ãã£ãŠããŒã¿ãä¿åããŸãã
ãããŠå°æ¥ãããŒã«ããã¯ããããšãããŸãã¯å€ãããŒãžã§ã³ããæ°ããã¯ããŒã³ãäœæããããšãã¯ããããããŸããããã®ããã«ããŒã¯ãããããŒã¿ã®ãããã¯ããã ããããšèšãã ãã§ãã
ãããŠããã®ãŠãŒã¶ãŒã¯ãã®ãããªããŒã¿ã»ãããæäœããŸãã 圌ã¯åŸã ã«ããããå€æŽããç¬èªã®ã¹ãããã·ã§ãããäœæããŸãã
ãããŠåå²ããŠãããŸãã ãã®å Žåãåéçºè ã¯ç·šéããç¬èªã®ã¯ããŒã³ãæã€æ©äŒããããå ±æãããããŒã¿ã¯å šå¡ã§å ±æãããŸãã
ãã®ãããªã·ã¹ãã ãèªå® ã«å°å ¥ããã«ã¯ã次㮠XNUMX ã€ã®åé¡ã解決ããå¿ èŠããããŸãã
-
XNUMX ã€ç®ã¯ããŒã¿ã®ãœãŒã¹ã§ããããŒã¿ã®ååŸå ã§ãã æ¬çªç°å¢ã§ã¬ããªã±ãŒã·ã§ã³ãèšå®ã§ããŸãã èšå®ããããã¯ã¢ããã¯ãã§ã«äœ¿çšã§ãããšæããŸãã WAL-EãWAL-GããŸãã¯ããŒãã³ã ãŸããRDS ã Cloud SQL ãªã©ã®ã¯ã©ãŠã ãœãªã¥ãŒã·ã§ã³ã䜿çšããŠããå Žåã§ããè«çãã³ãã䜿çšã§ããŸãã ãã ããããã§ãããã¯ã¢ããã䜿çšããããšããå§ãããŸãããã®ã¢ãããŒãã䜿çšãããšããã¡ã€ã«ã®ç©çæ§é ãä¿æãããååšããåé¡ãæ€åºããããã«éçšç°å¢ã§ç¢ºèªãããã¡ããªã¯ã¹ã«ããã«è¿ã¥ããããšãã§ããããã§ãã
-
XNUMX ã€ç®ã¯ãããŒã¿ããŒã¹ ã©ãããã¹ãããå Žæã§ãã ããã¯ã¯ã©ãŠãã®å Žåãããã°ããªã³ãã¬ãã¹ã®å ŽåããããŸãã ããã§ãZFS ãããŒã¿å§çž®ããµããŒãããŠãããšèšãããšãéèŠã§ãã ãããŠããã¯éåžžã«ããŸããããŸãã
ãã®ãããªã¯ããŒã³ããšã«ãããŒã¹ã§è¡ãæäœã«å¿ããŠãããçš®ã®éçºãæé·ããããšãæ³åããŠãã ããã ãã®ããã«ã¯ãéçºè ã«ãã¹ããŒã¹ãå¿ èŠã§ãã ãã ããããŒã¹ã 4,5 ãã©ãã€ããšãããããZFS ã¯ããã 3,5 ãã©ãã€ãã«å§çž®ããŸãã ããã¯èšå®ã«ãã£ãŠç°ãªãå ŽåããããŸãã ãããŠç§ãã¡ã«ã¯ãŸã éçºã®äœå°ããããŸãã
ãã®ãããªã·ã¹ãã ã¯ããŸããŸãªå Žåã«äœ¿çšã§ããŸãã
-
ãããã¯éçºè ã§ãããã¯ãšãªæ€èšŒãæé©åãæ åœãã DBA ã§ãã
-
ããã¯ãæ¬çªç°å¢ã«å±éããåã«ç¹å®ã®ç§»è¡ããã¹ãããããã® QA ãã¹ãã§äœ¿çšã§ããŸãã ãŸããå®éã®ããŒã¿ã䜿çšã㊠QA çšã®ç¹å¥ãªç°å¢ãæ§ç¯ããæ°ããæ©èœããã¹ãã§ããããã«ããããšãã§ããŸãã ãŸããæ°æéåŸ ã€ã®ã§ã¯ãªãæ°ç§ã§å®äºããã·ã³ ã³ããŒã䜿çšãããªãå Žåã«ã¯æ°æ¥ãããå ŽåããããŸãã
-
ãããŠå¥ã®ã±ãŒã¹ã äŒç€Ÿã«åæã·ã¹ãã ãã»ããã¢ãããããŠããªãå Žåã¯ã補åããŒã¹ã®ã·ã³ ã¯ããŒã³ãåé¢ãããããåæã«äœ¿çšã§ããé·ãã¯ãšãªãŸãã¯ç¹å¥ãªã€ã³ããã¯ã¹ã«å²ãåœãŠãããšãã§ããŸãã
ãã®ã¢ãããŒãã§ã¯æ¬¡ã®ããã«ãªããŸãã
-
ãã¹ãŠã®å€æŽããã«ãµã€ãºã®ããŒã¿ââã§ãã¹ãããããããæ¬çªç°å¢ãã§ãšã©ãŒãçºçããå¯èœæ§ãäœããªããŸãã
-
ç§ãã¡ã«ã¯ãã¹ãããæåããããŸãããªããªããèªåã®ã¹ã¿ã³ããåŸãããã«äœæéãåŸ ã€å¿ èŠããªããªã£ãããã§ãã
-
ãŸãããã¹ãã®éã«éå£ãåŸ ã¡æéã¯ãããŸããã å®éã«è¡ã£ãŠç¢ºèªããããšãå¯èœã§ãã ãããŠãéçºãã¹ããŒãã¢ããããã«ã€ããŠããã®æ¹æ³ã¯ããè¯ããªãã§ãããã
-
ãªãã¡ã¯ã¿ãªã³ã°ãå°ãªããªããŸãã æçµçã«æ¬çªç°å¢ã«æ®ããã°ã¯å°ãªããªããŸãã åŸã§ãããã®ãªãã¡ã¯ã¿ãªã³ã°ãæžãããŸãã
-
äžå¯éçãªå€åãå ã«æ»ãããšãã§ããŸãã ããã¯æšæºçãªã¢ãããŒãã§ã¯ãããŸããã
- ãã¹ããã³ãã®ãªãœãŒã¹ãå ±æããã®ã§ãããã¯æçã§ãã
ãã§ã«åªããŠããŸãããä»ã«å éã§ãããã®ã¯äœã§ãããã?
ãã®ãããªã·ã¹ãã ã®ãããã§ããã®ãããªãã¹ãã«åå ããæ·å± ãå€§å¹ ã«äžããããšãã§ããŸãã
çŸåšãéçºè ãå®éã®ãã«ãµã€ãºã®ããŒã¿ââã«ã¢ã¯ã»ã¹ããã«ã¯å°é家ã«ãªããªããã°ãªããªããšããæªåŸªç°ãçããŠããŸãã 圌ã¯ãã®ãããªã¢ã¯ã»ã¹ãä¿¡é Œã§ããã«éããããŸããã
ããããããããªãå Žåã¯ã©ããã£ãŠæé·ããã®ãã ãããã䜿çšã§ãããã¹ã ããŒã¿ã®ã»ãããéåžžã«å°ãªãå Žåã¯ã©ããªãã§ãããã? ããã§ã¯æ¬åœã®çµéšã¯åŸãããŸããã
ãã®ãµãŒã¯ã«ããæãåºãã«ã¯ã©ãããã°ããã§ããããïŒ ããããã¬ãã«ã®éçºè ã«ãšã£ãŠäŸ¿å©ãªæåã®ã€ã³ã¿ãŒãã§ã€ã¹ãšããŠãSlack ããããéžæããŸããã ãã ããä»ã®ã€ã³ã¿ãŒãã§ã€ã¹ã§ãããŸããŸããã
ããã«ãã£ãŠäœãã§ããããã«ãªãã®ã§ããããïŒ ç¹å®ã®ã¯ãšãªãååŸããŠãããŒã¿ããŒã¹ã®ç¹å¥ãªãã£ãã«ã«éä¿¡ã§ããŸãã ã·ã³ ã¯ããŒã³ã¯æ°ç§ã§èªåçã«ãããã€ãããŸãã ãã®ãªã¯ãšã¹ããå®è¡ããŠã¿ãŸãããã ç§ãã¡ã¯ææšãšæšå¥šäºé ãåéããŸãã èŠèŠåã瀺ããŠã¿ãŸãããã ãããŠããã®ã¯ããŒã³ã¯æ®ãããã®ã¯ãšãªãäœããã®æ¹æ³ã§æé©åããããã€ã³ããã¯ã¹ãè¿œå ãããã§ããããã«ãªããŸãã
ãŸããSlack ã¯ãããã«ã³ã©ãã¬ãŒã·ã§ã³ã§ããæ©äŒãäžããŠãããŸãã ããã¯åãªããã£ãã«ã§ããããããã®ãããªãªã¯ãšã¹ãã®ã¹ã¬ããå ã§ãã®ãªã¯ãšã¹ãã«ã€ããŠã®è°è«ãéå§ãã瀟å ã®ååã DBA ã« ping ãéä¿¡ããããšãã§ããŸãã
ãããããã¡ããåé¡ããããŸãã ããã¯çŸå®ã®äžçã§ãããäžåºŠã«å€æ°ã®ã¯ããŒã³ããã¹ããããµãŒããŒã䜿çšããŠãããããã¯ããŒã³ã䜿çšã§ããã¡ã¢ãªãš CPU ãã¯ãŒã®éãå§çž®ããå¿ èŠããããŸãã
ãããããããã®ãã¹ãã劥åœã§ããããã«ã¯ããã®åé¡ãäœããã®æ¹æ³ã§è§£æ±ºããå¿ èŠããããŸãã
éèŠãªç¹ãåãããŒã¿ã§ããããšã¯æããã§ãã ããããç§ãã¡ã¯ãã§ã«ãããæã£ãŠããŸãã ãããŠãåãæ§æãå®çŸããããšèããŠããŸãã ãããŠããã®ãããªã»ãŒåäžã®æ§æãäžããããšãã§ããŸãã
å®çšŒåç°å¢ãšåãããŒããŠã§ã¢ã䜿çšã§ããã°çŽ æŽãããã§ãããç°ãªãå ŽåããããŸãã
Postgres ãã¡ã¢ãªãã©ã®ããã«æ±ãããæãåºããŠãã ããã ãã£ãã·ã¥ã¯ XNUMX ã€ãããŸãã XNUMX ã€ã¯ãã¡ã€ã« ã·ã¹ãã ããããã XNUMX ã€ã¯ãã€ãã£ã Postgresãã€ãŸãå ±æãããã¡ ãã£ãã·ã¥ããã§ãã
å ±æãããã¡ ãã£ãã·ã¥ã¯ãæ§æã§æå®ãããµã€ãºã«å¿ããŠãPostgres ã®èµ·åæã«å²ãåœãŠãããããšã«æ³šæããããšãéèŠã§ãã
ãããŠãXNUMX çªç®ã®ãã£ãã·ã¥ã¯å©çšå¯èœãªã¹ããŒã¹ããã¹ãŠäœ¿çšããŸãã
ãããŠã25 å°ã®ãã·ã³äžã§è€æ°ã®ã¯ããŒã³ãäœæãããšãã¡ã¢ãªãåŸã ã«ãã£ã±ãã«ãªãããšãããããŸãã ãããŠãè¯ãæå³ã§ãå ±æãããã¡ ãã£ãã·ã¥ã¯ãã·ã³ã§å©çšå¯èœãªã¡ã¢ãªã®ç·éã® XNUMX% ãå ããŸãã
ãããŠããã®ãã©ã¡ãŒã¿ãå€æŽããªãå Žåã4 å°ã®ãã·ã³äžã§å®è¡ã§ããã€ã³ã¹ã¿ã³ã¹ã¯ 4 ã€ã ããã€ãŸãããã®ãããªã·ã³ ã¯ããŒã³ãã¹ãŠã®ãã¡ã® XNUMX ã€ã ãã§ããããšãããããŸãã ãããŠãã¡ãããããã¯è¯ããããŸããããªããªããç§ãã¡ã¯ãã£ãšå€ãã®ãã®ãæã¡ããããã§ãã
ããããã®äžæ¹ã§ããããã¡ ãã£ãã·ã¥ã¯ã€ã³ããã¯ã¹ã®ã¯ãšãªãå®è¡ããããã«äœ¿çšãããŸããã€ãŸããèšç»ã¯ãã£ãã·ã¥ã®å€§ããã«äŸåããŸãã ãããŠããã®ãã©ã¡ãŒã¿ãåãåºããŠæžããã ãã§ãèšç»ã倧ããå€ããå¯èœæ§ããããŸãã
ããšãã°ãæ¬çªç°å¢ã«å€§èŠæš¡ãªãã£ãã·ã¥ãããå ŽåãPostgres ã¯ã€ã³ããã¯ã¹ã®äœ¿çšãåªå ããŸãã ããã§ãªãå Žåã¯ãSeqScan ããããŸãã ãããŠãããç§ãã¡ã®èšç»ãäžèŽããªãã£ãããäžäœäœã®æå³ãããã®ã§ããããïŒ
ãããããã§ãå®éã«ã¯ Postgres ã®ãã©ã³ã¯ãã©ã³å ã®å ±æãããã¡ãŒã«æå®ãããç¹å®ã®ãµã€ãºã«ã¯äŸåãããeffective_cache_size ã«äŸåãããšããçµè«ã«éããŸããã
Effective_cache_size ã¯ãå©çšå¯èœãªãã£ãã·ã¥ã®æšå®éãã€ãŸããããã¡ ãã£ãã·ã¥ãšãã¡ã€ã« ã·ã¹ãã ãã£ãã·ã¥ã®åèšã§ãã ããã¯æ§æã«ãã£ãŠèšå®ãããŸãã ãããŠããã®ã¡ã¢ãªã¯å²ãåœãŠãããŠããŸããã
ãããŠããã®ãã©ã¡ãŒã¿ã®ãããã§ãPostgres ãéšããŠãããšããã®ããŒã¿ããªããŠããå®éã«ã¯å©çšå¯èœãªããŒã¿ããããããããšèšãããšãã§ããŸãã ãããã£ãŠãèšç»ã¯çç£ãšå®å šã«äžèŽããŸãã
ããããããã¯ã¿ã€ãã³ã°ã«åœ±é¿ãäžããå¯èœæ§ããããŸãã ãŸããã¿ã€ãã³ã°ã«ãã£ãŠã¯ãšãªãæé©åããŸãããã¿ã€ãã³ã°ã¯å€ãã®èŠå ã«äŸåããããšãéèŠã§ãã
-
çŸåšæ¬çªç°å¢ã«ãããè² è·ã«ãã£ãŠç°ãªããŸãã
-
æ©æ¢°èªäœã®ç¹æ§ã«ãããŸãã
ããã¯éæ¥çãªãã©ã¡ãŒã¿ãŒã§ãããå®éã«ã¯ãçµæãååŸããããã«ãã®ã¯ãšãªãèªã¿åãããŒã¿ã®éã«ãã£ãŠæ£ç¢ºã«æé©åã§ããŸãã
ãããŠãæ¬çªç°å¢ã§èŠãããã¿ã€ãã³ã°ã«è¿ã¥ãããå Žåã¯ãæãé¡äŒŒããããŒããŠã§ã¢ã䜿çšããå¿ èŠãããããããããã¹ãŠã®ã¯ããŒã³ãé©åããããã«ããå¿ èŠããããŸãã ããããããã¯åŠ¥åã§ããã€ãŸããåããã©ã³ãåŸãããç¹å®ã®ã¯ãšãªãèªã¿åãããŒã¿ã®éããããããã®ã¯ãšãªãè¯ã (ãŸãã¯ç§»è¡) ãæªãããçµè«ä»ããããšãã§ããŸããããŸã æé©åããå¿ èŠããããŸãã ã
Joe ãå ·äœçã«ã©ã®ããã«æé©åãããŠããããèŠãŠã¿ãŸãããã
å®éã®ã·ã¹ãã ãããªã¯ãšã¹ããåãåã£ãŠã¿ãŸãããã ãã®å ŽåãããŒã¿ããŒã¹ã¯ 1 ãã©ãã€ãã§ãã ãããŠã10 件以äžã®ããããïŒããã€ããæ°ããæçš¿ã®æ°ãæ°ããããšæããŸãã
ç§ãã¡ã¯ãã£ãã«ã«ã¡ãã»ãŒãžãæžã蟌ãã§ããŸããã¯ããŒã³ããããã€ãããŸããã ãã®ãããªãªã¯ãšã¹ã㯠2,5 åã§å®äºããããšãããããŸãã ãããç§ãã¡ãæåã«æ°ã¥ãããšã§ãã
B Joe ã¯ããã©ã³ãšææšã«åºã¥ããŠèªåæšå¥šäºé ã衚瀺ããŸãã
ã¯ãšãªãåŠçããããŒã¿ãå€ãããŠãæ¯èŒçå°æ°ã®è¡ãååŸã§ããªãããšãããããŸãã ãŸããã¯ãšãªå ã«ãã£ã«ã¿ãªã³ã°ãããè¡ãå€ãããããšã«æ°ã¥ããã®ã§ãããçš®ã®ç¹æ®ãªã€ã³ããã¯ã¹ãå¿ èŠã§ãã
äœãèµ·ãã£ãã®ãã詳ããèŠãŠã¿ãŸãããã å®éããã¡ã€ã« ãã£ãã·ã¥ãŸãã¯ãã£ã¹ã¯ããã»ãŒ 142 ã®ã¬ãã€ãã®ããŒã¿ãèªã¿åã£ãŠããããšãããããŸãã XNUMX è¡ããåŸãããªãã£ãã®ã§ãããã¯è¯ããããŸããã
ãããŠãããã«ã¯ã€ã³ããã¯ã¹ ã¹ãã£ã³ããããããã«åŠçãããã¯ãã ã£ãã®ã§ããããã£ã«ã¿ãŒã§é€å€ããè¡æ°ãå€ããã (è¡æ°ãã«ãŠã³ãããå¿ èŠããã£ã) ããããªã¯ãšã¹ãã¯ãã£ãããšåŠçãããŸããã
ããã¯ãã¯ãšãªå ã®æ¡ä»¶ãšã€ã³ããã¯ã¹å ã®æ¡ä»¶ãéšåçã«äžèŽããªããšããäºå®ã«ããããã©ã³ã§çºçããŸããã
ã€ã³ããã¯ã¹ãããæ£ç¢ºã«ããŠããã®åŸã¯ãšãªã®å®è¡ãã©ã®ããã«å€åããããèŠãŠã¿ãŸãããã
ã€ã³ããã¯ã¹ã®äœæã«ã¯ããªãã®æéãããããŸããããã¯ãšãªã確èªãããšã2,5 åã§ã¯ãªã 156 ããªç§ãããããããååãªæéã§ããããšãããããŸãã ãããŠãèªã¿åã£ãããŒã¿ã¯ããã 6 ã¡ã¬ãã€ãã§ãã
ãããŠä»ã¯ã€ã³ããã¯ã¹ã®ã¿ã®ã¹ãã£ã³ã䜿çšããŠããŸãã
ããäžã€éèŠãªè©±ã¯ãèšç»ãããå°ããããããã圢ã§æ瀺ããããšããããšã§ãã Flame Graphã䜿çšããå¯èŠåãå®è£ ããŸããã
ããã¯å¥ã®ããã匷åãªãªã¯ãšã¹ãã§ãã ãããŠãXNUMX ã€ã®ãã©ã¡ãŒã¿ã«åŸã£ãŠãã¬ãŒã ã°ã©ããæ§ç¯ããŸããããã¯ãç¹å®ã®ããŒããèšç»ãšã¿ã€ãã³ã°ã§ã«ãŠã³ãããããŒã¿ã®éãã€ãŸãããŒãã®å®è¡æéã§ãã
ããã§ãç¹å®ã®ããŒããçžäºã«æ¯èŒã§ããŸãã ãããŠãã©ã¡ãã®åŠçã«ãããæéãå€ããå°ãªããã¯æããã§ãããä»ã®ã¬ã³ããªã³ã°æ¹æ³ã§ã¯éåžžãããè¡ãã®ãå°é£ã§ãã
ãã¡ãããexplain.depesz.com ã¯èª°ããç¥ã£ãŠããŸãã ãã®ããžã¥ã¢ã©ã€ãŒãŒã·ã§ã³ã®åªããæ©èœã¯ãããã¹ã ãã©ã³ãä¿åãããœãŒãã§ããããã«ããã€ãã®åºæ¬ãã©ã¡ãŒã¿ãŒãããŒãã«ã«è¿œå ããŠããããšã§ãã
ãŸãããã®ãããã¯ããŸã æ·±ãæãäžããŠããªãéçºè ã Explain.depesz.com ã䜿çšããŸããããã¯ãã©ã®ã¡ããªã¯ã¹ãéèŠã§ã©ã®ã¡ããªã¯ã¹ãããã§ãªãããç°¡åã«ææ¡ã§ããããã§ãã
èŠèŠåã«å¯Ÿããæ°ããã¢ãããŒãããããŸã - ãã㯠Explain.dalibo.com ã§ãã 圌ãã¯ããªãŒãèŠèŠåããŸãããããŒããçžäºã«æ¯èŒããã®ã¯éåžžã«å°é£ã§ãã ããã§æ§é ãããç解ã§ããŸããã倧ããªãªã¯ãšã¹ããããå Žåã¯ãååŸã«ã¹ã¯ããŒã«ããå¿ èŠããããŸããããªãã·ã§ã³ãå¿ èŠã§ãã
ÐПллабПÑаÑОÑ
ãããŠãå ã»ã©ãè¿°ã¹ãããã«ãSlack ã¯ç§ãã¡ã«ã³ã©ãã¬ãŒã·ã§ã³ã®æ©äŒãäžããŠãããŸãã ããšãã°ãæé©åæ¹æ³ãæ確ã§ã¯ãªãè€éãªã¯ãšãªã«ééããå Žåã¯ãSlack ã®ã¹ã¬ããã§ååãšãã®åé¡ãæ確ã«ããããšãã§ããŸãã
ãã«ãµã€ãºã®ããŒã¿ââã§ãã¹ãããããšãéèŠã§ããããã«æããŸãã ãããè¡ãããã«ããªãŒãã³ãœãŒã¹ã§å ¥æã§ãã Update Database Lab ããŒã«ãäœæããŸããã Joe ãããã䜿çšã§ããŸãã ä»ããå ¥æããŠãèªåã®å Žæã§å®è£ ã§ããŸãã ãã¹ãŠã®ã¬ã€ãã¯ããã§å ¥æã§ããŸãã
Delphix ãããããããœãªã¥ãŒã·ã§ã³èªäœã¯é©æ°çã§ã¯ãããŸãããããšã³ã¿ãŒãã©ã€ãº ãœãªã¥ãŒã·ã§ã³ã§ããããšã«æ³šæããããšãéèŠã§ãã å®å šã«ééãããŠããŠãéåžžã«é«äŸ¡ã§ãã ç§ãã¡ã¯ç¹ã« Postgres ã«ç¹åããŠããŸãã ãããã¯ãã¹ãŠãªãŒãã³ãœãŒã¹è£œåã§ãã åå ããŸãããïŒ
ããã§çµãããŸãã ããããšãïŒ
質å
ããã«ã¡ã¯ïŒ ãå ±åããããšãããããŸãïŒ å°ãåã«ã»ãŒåãåé¡ã解決ããã®ã§ãç¹ã«ç§ã«ãšã£ãŠã¯éåžžã«èå³æ·±ãã§ãã ããã§ãããã€ã質åããããŸãã å°ãªããšããã®äžéšãåŸãããšãã§ããã°å¹žãã§ãã
ãã®ç°å¢ã®å Žæã¯ã©ã®ããã«èšç®ããŠããã®ã§ããããïŒ ãã®ãã¯ãããžãŒã¯ãç¹å®ã®ç¶æ³äžã§ã¯ã¯ããŒã³ãæ倧ãµã€ãºãŸã§æé·ããå¯èœæ§ãããããšãæå³ããŸãã 倧ãŸãã«èšãã°ã10 ãã©ãã€ãã®ããŒã¿ããŒã¹ãš 10 åã®ã¯ããŒã³ãããå Žåãåã¯ããŒã³ã XNUMX åã®äžæã®ããŒã¿ãéã¿ä»ãããç¶æ³ãã·ãã¥ã¬ãŒãããã®ã¯ç°¡åã§ãã ãããã®ã¯ããŒã³ãäœãããšã«ãªããã®å Žæãã€ãŸãããªãã話ãããã«ã¿ãã©ã®ããã«èšç®ããŸãã?
è¯ã質åã ããã§ç¹å®ã®ã¯ããŒã³ã远跡ããããšãéèŠã§ãã ãŸããã¯ããŒã³ã«å€§ããããå€æŽãå ããããã¯ããŒã³ãæé·ãå§ããå Žåã¯ããŸããŠãŒã¶ãŒã«ããã«ã€ããŠèŠåãçºããããé害ãçºçããªãããã«ãã®ã¯ããŒã³ãçŽã¡ã«åæ¢ããããšãã§ããŸãã
ã¯ããå ¥ãåã®è³ªåããããŸãã ã€ãŸãããããã®ã¢ãžã¥ãŒã«ã®ã©ã€ããµã€ã¯ã«ãã©ã®ããã«ä¿èšŒããã®ã§ãããã? ãã®åé¡ãšããŸã£ããå¥ã®è©±ããããŸãã ããã¯ã©ãããŠèµ·ããã®ã§ãããã?
ã¯ããŒã³ããšã«ããã€ãã® ttl ããããŸãã åºæ¬çã«ãåºå®ttlããããŸãã
ç§å¯ã§ã¯ãªããšãããäœã§ããããïŒ
1 æéãã€ãŸãã¢ã€ãã«ç¶æ - 1 æéã 䜿ãããªãå Žåã¯å©ããŸãã ããããæ°ç§ã§ã¯ããŒã³ãäœæã§ãããããããã¯é©ãã¹ãããšã§ã¯ãããŸããã ãŸãå¿ èŠãªå Žåã¯ããé¡ãããŸãã
ããšãã°ãäœããã®çç±ã§è€æ°ã®æ¹æ³ã䞊è¡ããŠäœ¿çšããããããã¯ãããžãŒã®éžæã«ãèå³ããããŸãã ãªã ZFS ãªã®ã? ãªãLVMã䜿ããªãã£ãã®ã§ãã? LVM ã«åé¡ããããšãã£ããããŸããã äœãåé¡ã§ããã? ç§ã®æèŠã§ã¯ãããã©ãŒãã³ã¹ã®èŠ³ç¹ãããæãæé©ãªãªãã·ã§ã³ã¯ã¹ãã¬ãŒãžã䜿çšããããšã§ãã
ZFS ã®äž»ãªåé¡ã¯äœã§ãã? åããã¹ãäžã§å®è¡ããå¿ èŠããããã€ãŸããã¹ãŠã®ã€ã³ã¹ã¿ã³ã¹ãåã OS å ã«ååšããå¿ èŠããããšããäºå®ã ã¹ãã¬ãŒãžã®å Žåã¯ãããŸããŸãªæ©åšãæ¥ç¶ã§ããŸãã ãããŠãããã«ããã¯ãšãªãã®ã¯ã¹ãã¬ãŒãž ã·ã¹ãã äžã«ãããããã¯ã ãã§ãã ãããŠããã¯ãããžãŒã®éžæã®åé¡ã¯èå³æ·±ãã§ãã ãªãLVMã§ã¯ãªãã®ã§ãããã?
å ·äœçã«ã¯ãããŒãã¢ããã§LVMã«ã€ããŠè©±ãåãããšãã§ããŸãã ã¹ãã¬ãŒãžã«ã€ããŠã¯ããã é«äŸ¡ã§ãã ZFS ã·ã¹ãã ã¯ã©ãã«ã§ãå®è£ ã§ããŸãã ãã·ã³ã«ãããã€ã§ããŸãã ãªããžããªãããŠã³ããŒãããŠãããã€ããã ãã§ãã Linux ã«ã€ããŠèšãã°ãZFS ã¯ã»ãšãã©ã©ãã«ã§ãã€ã³ã¹ããŒã«ãããŸãã ã€ãŸããéåžžã«æè»ãªè§£æ±ºçãåŸãããŸãã ZFS èªäœã¯ãããã«äœ¿ããæ©èœããããããããŸãã 奜ããªã ãããŒã¿ãã¢ããããŒãã§ããå€æ°ã®ãã£ã¹ã¯ãæ¥ç¶ã§ããã¹ãããã·ã§ããããããŸãã ãããŠãå ã»ã©ãèšããŸãããã管çã¯ç°¡åã§ãã ã€ãŸãããšãŠã䜿ãå¿å°ãè¯ãããã§ãã 圌ã¯è©Šç·ŽãåããŠããã圌ã¯ããäœæ³ãããã 圌ã«ã¯éåžžã«å€§ããªã³ãã¥ããã£ããããæé·ãç¶ããŠããŸãã ZFS ã¯éåžžã«ä¿¡é Œæ§ã®é«ããœãªã¥ãŒã·ã§ã³ã§ãã
ãã³ã©ã€ã»ãµã¢ã¯ããã: ããã«ã³ã¡ã³ãããŠãããã§ãã? ç§ã®ååã¯ãã³ã©ã€ã§ããç§ãã¡ã¯ã¢ãããªãŒãšäžç·ã«åããŠããŸãã åçŽãçŽ æŽããããšããæèŠã«ã¯åæããŸãã ãŸããåœç€Ÿã®é¡§å®¢ã®äžã«ã¯ Pure Storage ãªã©ãå©çšããŠãã人ãããŸãã
ã¢ãããªãŒã¯ãç§ãã¡ãã¢ãžã¥ãŒã«æ§ã«éç¹ã眮ããŠãããšæ£ããææããŸããã ãããŠå°æ¥çã«ã¯ãã¹ãããã·ã§ããã®ååŸãã¯ããŒã³ã®äœæãã¯ããŒã³ã®ç Žæ£ãšãã XNUMX ã€ã®ã€ã³ã¿ãŒãã§ã€ã¹ãå®è£ ã§ããããã«ãªããŸãã ãã¹ãŠç°¡åã§ãã ãããããªããã¹ãã¬ãŒãžãã¯ãŒã«ã§ãã
ããããZFS ã¯èª°ã§ãå©çšã§ããŸãã DelPhix ã¯ãã§ã«ååã§ãããã¯ã©ã€ã¢ã³ã㯠300 瀟ãããŸãã ãã®ãã¡ããã©ãŒãã¥ã³ 100 ã«ã¯ 50 瀟ã®é¡§å®¢ãããŸããã€ãŸããNASA ãªã©ã察象ãšããŠããŸãã誰ãããã®ãã¯ãããžãŒãå ¥æã§ããæ代ãæ¥ãŠããŸãã ããããç§ãã¡ããªãŒãã³ãœãŒã¹ã®ã³ã¢ãæã£ãŠããçç±ã§ãã ãªãŒãã³ãœãŒã¹ã§ã¯ãªãã€ã³ã¿ãŒãã§ã€ã¹éšåããããŸãã ãããä»åã玹ä»ãããã©ãããã©ãŒã ã§ãã ããããç§ãã¡ã¯èª°ã§ãã¢ã¯ã»ã¹ã§ããããã«ããããšèããŠããŸãã ç§ãã¡ã¯ããã¹ãŠã®ãã¹ã¿ãŒãã©ãããããã§æšæž¬ããã®ãããããããªé©åœãèµ·ãããããšèããŠããŸãã SELECT ãæžããšããããé ãããšãããã«ããããŸãã DBA ããã®éç¥ãåŸ ã€ã®ã¯ãããŠãã ããã ãããäž»ãªç®æšã§ãã ãããŠãç§ãã¡å šå¡ãããã«å°éãããšæããŸãã ãããŠç§ãã¡ã¯ããã誰ããæã«ã§ãããã®ãäœããŸãã ãããã£ãŠãZFS ã¯ã©ãã§ãå©çšã§ããããã«ãªãããã§ãã åé¡ã解決ãããªãŒãã³ãœãŒã¹ ã©ã€ã»ã³ã¹ãååŸããã³ãã¥ããã£ã«æè¬ããŸã*
ããã«ã¡ã¯ïŒ ãå ±åããããšãããããŸãïŒ ç§ã®ååã¯ããã·ã ã§ãã ç§ãã¡ã¯åãåé¡ã«åãçµãã§ããŸããã 圌ãã¯èªåãã¡ã§æ±ºããã®ã§ãã ãããã®ã¯ããŒã³éã§ãªãœãŒã¹ãã©ã®ããã«å ±æããŸãã? åã¯ããŒã³ã¯ãã€ã§ãç¬èªã®äœæ¥ãè¡ãããšãã§ããŸããã€ãŸããããã¯ããŒã³ã¯ããããšããã¹ãããå¥ã®ã¯ããŒã³ã¯å¥ã®ããšããã¹ããã誰ããã€ã³ããã¯ã¹ãæ§ç¯ãã誰ããéãä»äºãããªããŸãã CPU ããšã«åå²ã§ããå ŽåãIO ããšã«åå²ããã«ã¯ã©ãããã°ããã§ãããã? ãããæåã®è³ªåã§ãã
ãããŠ4ã€ç®ã®è³ªåã¯ãã¹ã¿ã³ãã®éãã«ã€ããŠã§ãã ããã« ZFS ãããããã¹ãŠãããŸããã£ãŠãããšããŸãããã ããæ¬çªç°å¢ã®ã¯ã©ã€ã¢ã³ãã«ã¯ ZFS ããªããããšãã° extXNUMX ããããšããŸãã ãã®å Žåã¯ã©ãããã°ããã§ããããïŒ
質åã¯ãšãŠãè¯ãã§ãã ç§ã¯ãªãœãŒã¹ãå ±æããŠãããšããäºå®ã«ã€ããŠããã®åé¡ã«ã€ããŠå°ã觊ããŸããã ãããŠè§£æ±ºçã¯ããã§ãã ã¹ããŒãžã³ã°ã§ãã¹ãããŠãããšæ³åããŠãã ããã 誰ããXNUMXã€ã®è² è·ãäžãããšåæã«ãä»ã®èª°ãããã®ãããªç¶æ³ã«ãªãå¯èœæ§ããããŸãã ãã®çµæãç解ã§ããªãææšã衚瀺ãããããšã«ãªããŸãã åãåé¡ã補åã§ãçºçããå¯èœæ§ããããŸãã ãªã¯ãšã¹ãããã§ãã¯ããããšãã«ããªã¯ãšã¹ãã«äœããã®åé¡ãããããšãããããåäœãé ãå Žåãå®éã«ã¯åé¡ã¯ãªã¯ãšã¹ãã«ããã®ã§ã¯ãªããããçš®ã®äžŠåè² è·ããããšããäºå®ã«ãããŸãã
ãããã£ãŠãããã§ã¯ãèšç»ãã©ã®ãããªãã®ã«ãªãã®ããèšç»ã®äžã§ã©ã®ãããªã¹ããããèžãã®ãããã®ããã«ã©ãã ãã®ããŒã¿ãåéããã®ãã«çŠç¹ãåœãŠãããšãéèŠã§ãã ããšãã°ããã£ã¹ã¯ã«äœããããŒãããããšããäºå®ã¯ãç¹ã«ã¿ã€ãã³ã°ã«åœ±é¿ããŸãã ãã ãããã®ãªã¯ãšã¹ãã®è² è·ã¯ããŒã¿éã«ãã£ãŠæšå®ã§ããŸãã åæã«äœããã®å®è¡ãè¡ãããããšã¯ããã»ã©éèŠã§ã¯ãããŸããã
質åãXNUMXã€ãããŸãã ããã¯ãšãŠãã¯ãŒã«ãªãã®ã§ãã ã¯ã¬ãžãã ã«ãŒãçªå·ãªã©ãæ¬çªããŒã¿ãéèŠã«ãªãã±ãŒã¹ã¯ãããŸãã? ãã§ã«äœãæºåãã§ããŠããŸããããããšãå¥ã®ã¿ã¹ã¯ã§ãã? XNUMX çªç®ã®è³ªåã¯ãMySQL ã«ãã®ãããªãã®ã¯ãããŸãã?
ããŒã¿ã«ã€ããŠã ãããªããŸã§é£èªåãè¡ããŸãã ããããæ£ç¢ºã« Joe ããããã€ããå Žåãéçºè ã«ã¢ã¯ã»ã¹æš©ãäžããªããã°ãããŒã¿ã«ã¢ã¯ã»ã¹ããããšã¯ã§ããŸããã ãªãïŒ ãžã§ãŒã¯ããŒââã¿ã瀺ããªãããã§ãã 衚瀺ãããã®ã¯ææšãšèšç»ã ãã§ããããã ãã§ãã ããã¯ã¯ã©ã€ã¢ã³ãã®èŠä»¶ã® XNUMX ã€ã§ãããããããã¯æå³çã«è¡ãããŸããã 圌ãã¯ãå šå¡ã«ã¢ã¯ã»ã¹æš©ãäžããã«æé©åã§ããããã«ããããšèããŠããŸããã
MySQLã«ã€ããŠã ãã®ã·ã¹ãã ã¯ãç¶æ ããã£ã¹ã¯ã«ä¿åããããããçšéã«äœ¿çšã§ããŸãã ãããŠãç§ãã¡ã¯ Postgres ãå®è¡ããŠããã®ã§ããŸã Postgres ã®èªååããã¹ãŠå®è¡ããŠããŸãã ããã¯ã¢ããããã®ããŒã¿ã®ååŸãèªååããããšèããŠããŸãã Postgres ãæ£ããæ§æããŠããŸãã ç§ãã¡ã¯èšç»ãäžèŽãããæ¹æ³ãªã©ãç¥ã£ãŠããŸãã
ãã ããã·ã¹ãã ã¯æ¡åŒµå¯èœãªãããMySQL ã«ã䜿çšã§ããŸãã ãããŠãã®ãããªäŸããããŸãã Yandex ã«ãåæ§ã®ãã®ããããŸãããã©ãã«ãå ¬éããŠããŸããã 圌ãã¯ããã Yandex.Metrica å ã§äœ¿çšããŸãã ããšã¯ MySQL ã®è©±ã ãã§ãã ãã ãããã¯ãããžãŒã¯åã ZFS ã§ãã
ãå ±åããããšãããããŸãïŒ ç§ãããã€ã質åããããŸãã ã¯ããŒã³äœæã¯ãè¿œå ã®ã€ã³ããã¯ã¹ãæ§ç¯ãããªã©ãåæã«äœ¿çšã§ãããšè¿°ã¹ãŸããã ã©ã®ããã«æ©èœãããã«ã€ããŠããå°ã詳ããæããŠããã ããŸãã?
ããã§ã¯ãã¹ã¿ã³ãã®é¡äŒŒæ§ãèšç»ã®é¡äŒŒæ§ã«ã€ããŠãããã« XNUMX çªç®ã®è³ªåããããŠããã ããŸãã ãã®èšç»ã¯ãPostgres ã«ãã£ãŠåéãããçµ±èšã«ãäŸåããŸãã ãã®åé¡ãã©ããã£ãŠè§£æ±ºããŸãã?
åæã«ãããšããŸã 䜿çšããŠããªããããå ·äœçãªã±ãŒã¹ã¯ãããŸãããããã®ãããªæ©äŒã¯ãããŸãã ã€ã³ããã¯ã¹ã«ã€ããŠè©±ããŠããå Žåãã¯ãšãªãæ°åã®ã¬ã³ãŒããšéåžžæ¬çªç°å¢ã§ã€ã³ããã¯ã¹ä»ããããŠããªãåãå«ãããŒãã«ã远跡ããŠãããšæ³åããŠãã ããã ããã§ããã€ãã®ããŒã¿ãèšç®ããããšæããŸãã ãã®ãªã¯ãšã¹ããæ¬çªç°å¢ã«éä¿¡ãããå Žåããªã¯ãšã¹ãã¯ãã㧠XNUMX åéåŠçããããããæ¬çªç°å¢ã§ã¯åçŽã«ãªãå¯èœæ§ããããŸãã
OKãæ°åéåæ¢ããŠãåé¡ãªãã·ã³ ã¯ããŒã³ãäœæããŸãããã åæãèªã¿ãããããããã«ãããŒã¿ã«é¢å¿ã®ããåã«ã€ã³ããã¯ã¹ãè¿œå ããŸãã
ã€ã³ããã¯ã¹ã¯æ¯åäœæãããŸãã?
ããŒã¿ã«ã¢ã¯ã»ã¹ããŠã¹ãããã·ã§ãããäœæãããã®åŸãã®ã¹ãããã·ã§ããããå埩ããŠæ°ãããªã¯ãšã¹ããå®è¡ããããã«ããããšãã§ããŸãã ã€ãŸããæ¢ã«ã€ã³ããã¯ã¹ãä»ããããŠããæ°ããã¯ããŒã³ãçæã§ããããã«ããããšãã§ããŸãã
çµ±èšã«é¢ãã質åã«é¢ããŠã¯ãããã¯ã¢ãããã埩å ããå Žåãšã¬ããªã±ãŒã·ã§ã³ãè¡ã£ãå Žåãçµ±èšã¯ãŸã£ããåãã«ãªããŸãã ãªããªããç©ççãªããŒã¿æ§é å šäœãæã£ãŠããããã§ããã€ãŸãããã¹ãŠã®çµ±èšã¡ããªãã¯ãå«ããããŒã¿ããã®ãŸãŸã®ç¶æ ã§æã¡èŸŒãããšã«ãªããŸãã
ããã«å¥ã®åé¡ããããŸãã ã¯ã©ãŠã ãœãªã¥ãŒã·ã§ã³ã䜿çšããå ŽåãGoogle ã Amazon ã§ã¯ç©çã³ããŒã®ååŸãèš±å¯ããŠããªããããããã§ã¯è«çãã³ãã®ã¿ãå©çšå¯èœã§ãã åé¡ãçºçããŸãã
ãå ±åããããšãããããŸãã ããã«ã¯ãMySQL ãšãªãœãŒã¹å ±æã«é¢ãã XNUMX ã€ã®è¯ã質åããããŸããã ããããå®éã«ã¯ãããã¯ç¹å®ã® DBMS ã®ãããã¯ã§ã¯ãªãããã¡ã€ã« ã·ã¹ãã å šäœã®ãããã¯ã§ãããšããäºå®ã«ãã¹ãŠåž°çããŸãã ãããã£ãŠããªãœãŒã¹å ±æã®åé¡ããæçµçã« Postgres ã§ãããšããããšã§ã¯ãªãããã¡ã€ã« ã·ã¹ãã ããµãŒããŒãªã©ã§è§£æ±ºãããå¿ èŠããããŸãã
ç§ã®è³ªåã¯å°ãç°ãªããŸãã ããã¯ãè€æ°ã®å±€ãããå€å±€ããŒã¿ããŒã¹ã«è¿ããã®ã§ãã ããšãã°ã100 ãã©ãã€ãã®ã€ã¡ãŒãžæŽæ°ãèšå®ããè€è£œããŠããŸãã ãããŠãç§ãã¡ã¯ãã®ãœãªã¥ãŒã·ã§ã³ãç¹ã«ããŒã¿ããŒã¹ã«äœ¿çšããŸãã ã¬ããªã±ãŒã·ã§ã³ãé²è¡äžã§ãããŒã¿ãæŽæ°ãããŠããŸãã ããã§ã¯ XNUMX 人ã®åŸæ¥å¡ã䞊è¡ããŠåããŠãããåžžã«ããŸããŸãªã·ã§ãããçºå°ããŠããŸãã äœããã¹ããïŒ ç«¶åããªãããšã競åãèµ·åããåŸã«ãã¡ã€ã« ã·ã¹ãã ãå€æŽããããããã®åçããã¹ãŠæ¶ããŠããŸã£ãããšã確èªããã«ã¯ã©ãããã°ããã§ãããã?
ããã ZFS ã®ä»çµã¿ã§ããããããããã¯å®è¡ãããŸããã ã¬ããªã±ãŒã·ã§ã³ã«ãããã¡ã€ã« ã·ã¹ãã ã®å€æŽã XNUMX ã€ã®ã¹ã¬ããã«åå¥ã«ä¿æã§ããŸãã ãããŠãéçºè ãå€ãããŒãžã§ã³ã®ããŒã¿ã§äœ¿çšããã¯ããŒã³ãä¿æããŸãã ãããŠããã¯ç§ãã¡ã«ãšã£ãŠããŸããããããã§ãã¹ãŠãããŸããããŸãã
æŽæ°ã¯è¿œå ã®ã¬ã€ã€ãŒãšããŠè¡ããããã¹ãŠã®æ°ããåçã¯ãã®ã¬ã€ã€ãŒã«åºã¥ããŠãã§ã«ç§»åãããããšãããããŸããã
以åã®ã¬ããªã±ãŒã·ã§ã³ããã®ãã®ã§ãã以åã®ã¬ã€ã€ãŒããã
以åã®ã¬ã€ã€ãŒã¯åé€ãããŸãããå€ãã¬ã€ã€ãŒãåç §ããæŽæ°ã§åãåã£ãæåŸã®ã¬ã€ã€ãŒããæ°ããç»åãååŸããŸãã?
äžè¬çã«ãã¯ãã
ãã®çµæãæ倧 XNUMX ã€ã®å±€ãåŸãããŸãã ãããŠæéãçµã€ãšå§çž®ããå¿ èŠãåºãŠããã®ã§ããããïŒ
ã¯ãããã¹ãŠæ£ããã§ãã äœãã®çªããããŸãã ç§ãã¡ã¯æ¯é±ã®ã¹ãããã·ã§ãããä¿æããŸãã ããã¯ããªããæã£ãŠãããªãœãŒã¹ã«ãã£ãŠç°ãªããŸãã 倧éã®ããŒã¿ãä¿åã§ããå Žåã¯ãã¹ãããã·ã§ãããé·æéä¿åã§ããŸãã 圌ãã¯èªãç«ã¡å»ãããšã¯ãããŸããã ããŒã¿ã®ç Žæã¯çºçããŸããã ã¹ãããã·ã§ãããå€ããšæãããå Žåãã€ãŸãäŒç€Ÿã®ããªã·ãŒã«äŸåããŠããå Žåã¯ãåçŽã«ã¹ãããã·ã§ãããåé€ããŠã¹ããŒã¹ã空ããããšãã§ããŸãã
ããã«ã¡ã¯ãã¬ããŒãããããšãããããŸãïŒ ãžã§ãŒã«ã€ããŠè³ªåã§ãã 顧客ã¯å šå¡ã«ããŒã¿ãžã®ã¢ã¯ã»ã¹ãèš±å¯ããããªããšèšã£ãŠããŸããã å³å¯ã«èšãã°ãExplain Analyzeã®çµæãæã£ãŠãã人ã¯ããŒã¿ãèŠãããšãã§ããŸãã
ãã®ãããªãã®ã§ãã ããšãã°ããSELECT FROM WHERE email = to thatããšæžãããšãã§ããŸãã ã€ãŸããããŒã¿ãã®ãã®ã¯ããããŸããããéæ¥çãªå åã¯ããã€ã確èªã§ããŸãã ããã¯ç解ããªããã°ãªããŸããã ãããäžæ¹ã§ãããã¯ãã¹ãŠããã«ãããŸãã ç§ãã¡ã¯ãã°ç£æ»ãè¡ã£ãŠãããéçºè ãäœãããŠããããèŠãä»ã®ååã管çããŠããŸãã ãããŠã誰ãããããè¡ãããšãããšãã»ãã¥ãªãã£ãµãŒãã¹ã圌ãã®ãšããã«æ¥ãŠããã®åé¡ã«åãçµãã§ãããã
ããã«ã¡ã¯ãå ±åããããšãããããŸãïŒ çã質åããããŸãã äŒç€Ÿã Slack ã䜿çšããŠããªãå ŽåãçŸåš Slack ã«ãã€ã³ããããã®ã¯ãããŸãã? ãããšããéçºè ããã¹ã ã¢ããªã±ãŒã·ã§ã³ãããŒã¿ããŒã¹ã«æ¥ç¶ããããã«ã€ã³ã¹ã¿ã³ã¹ããããã€ããããšã¯å¯èœã§ãã?
çŸåšãSlack ãžã®ãªã³ã¯ããããŸããã€ãŸããä»ã®ã¡ãã»ã³ãžã£ãŒã¯ãããŸããããä»ã®ã¡ãã»ã³ãžã£ãŒããµããŒãããããšèããŠããŸãã äœãã§ããã§ããããïŒ Joe ãªã㧠DB Lab ããããã€ããããšããREST API ãŸãã¯åœç€Ÿã®ãã©ãããã©ãŒã ãå©çšããŠã¯ããŒã³ãäœæããPSQL ã«æ¥ç¶ããããšãã§ããŸãã ãã ããç»é¢ããªããªããããéçºè ã«ããŒã¿ãžã®ã¢ã¯ã»ã¹ãèš±å¯ããæºåãã§ããŠããã°ããããè¡ãããšãã§ããŸãã
ãã®å±€ã¯å¿ èŠãããŸãããããã®ãããªæ©äŒã¯å¿ èŠã§ãã
ãããªããã¯ããã§ããŸãã
åºæïŒ habr.com