XNUMX幎åãYandexã®ãŽã£ã¯ãã«ã»ã¿ã«ããã¹ããŒãšã¢ã¬ã¯ã»ã€ã»ãããŽã£ãããã¹ããŒãžã«ç»å Ž HighLoad ++
XNUMXå¹ŽåŸ ã¯ãªãã¯ããŠã¹ ã¯ããã«è¯ããªããŸãã - ãã®éã«ã¢ã¬ã¯ãµã³ããŒã¯ã人ã ã®ç§»åãæ¯æŽããã ãã§ãªããAltinity ãšããäŒç€Ÿãèšç«ããŸããã ã¯ãªãã¯ããŠã¹ æ°åã®ãããžã§ã¯ãã«åãçµãã§ããŸãããYandex ã®ååãšååããŠè£œåèªäœãæ¹åããŠããŸãã ä» ã¯ãªãã¯ããŠã¹ ãŸã æ°ãŸãŸãªæ£æ©ã§ã¯ãããŸãããããã¯ãå°é·åã§ã¯ãããŸããã
Alexander 㯠2003 幎ããåæ£ã·ã¹ãã ã«åãçµãã§ããã次ã®ãããªå€§èŠæš¡ãªãããžã§ã¯ããéçºããŠããŸãã MySQLãOracle О Verticaã æåŸã« HighLoad ++ 2019 ã¢ã¬ã¯ãµã³ããŒã䜿çšã®å é§è ã® XNUMX 人 ã¯ãªãã¯ããŠã¹ããã® DBMS ãçŸåšã©ã®ãããªãã®ã§ãããã説æããŸããã äž»ãªæ©èœã«ã€ããŠåŠã³ãŸã ã¯ãªãã¯ããŠã¹: ä»ã®ã·ã¹ãã ãšã©ãéãã®ããã©ã®ãããªå Žåã«äœ¿çšãããšããå¹æçã«ãªãã®ãã äŸã䜿çšããŠã以äžã«åºã¥ããŠã·ã¹ãã ãæ§ç¯ããããã®ããããžã§ã¯ãã§ãã¹ããããæè¿ã®å®è·µæ¹æ³ãèŠãŠãããŸãã ã¯ãªãã¯ããŠã¹.
æ¯ãè¿ãïŒ3幎åã«äœãèµ·ãã£ãã®ã
XNUMX幎åã«äŒç€Ÿãè²æž¡ããŸãã LifeStreet Ма ã¯ãªãã¯ããŠã¹ å¥ã®åæããŒã¿ããŒã¹ããã®åºåãããã¯ãŒã¯åæã®ç§»è¡ã¯æ¬¡ã®ããã«ãªããŸãã
- 2016 幎 XNUMX æã ãªãŒãã³ãœãŒã¹ ç»å Ž ã¯ãªãã¯ããŠã¹ ãããŠç§ãã¡ã®ãããžã§ã¯ããå§ãŸããŸããã
- 8æã ã³ã³ã»ããã®èšŒæ: 倧èŠæš¡ãªåºåãããã¯ãŒã¯ãã€ã³ãã©ã¹ãã©ã¯ãã£ãããã³ 200 ïœ 300 ãã©ãã€ãã®ããŒã¿ã
- XNUMXæã æåã®æ¬çªããŒã¿ã
- 10æã 補åã®å šè² è·ã¯ 50 æ¥ããã XNUMX ïœ XNUMX åã®ã€ãã³ãã§ãã
- 2017 幎 XNUMX æããŠãŒã¶ãŒã®ç§»è¡ã«æåã ã¯ãªãã¯ããŠã¹, 2,5 å°ã®ãµãŒããŒãããªãã¯ã©ã¹ã¿ãŒäžã® 60 ãã¿ãã€ãã®ããŒã¿ã
移è¡ããã»ã¹äžã«ã次ã®ãããªç解ãæ·±ãŸããŸããã ã¯ãªãã¯ããŠã¹ ã¯å¿«é©ã«äœæ¥ã§ããåªããã·ã¹ãã ã§ããããã㯠Yandex ã®å éšãããžã§ã¯ãã§ãã ãããã£ãŠã埮åŠãªéãããããŸããYandex ã¯æåã«èªç€Ÿã®å éšé¡§å®¢ã«å¯Ÿå¿ãããã®åŸã§ã³ãã¥ããã£ãšå€éšãŠãŒã¶ãŒã®ããŒãºã«å¯Ÿå¿ããŸãããClickHouse ã¯å€ãã®æ©èœåéã§ãšã³ã¿ãŒãã©ã€ãº ã¬ãã«ã«éããŠããŸããã§ããã ã ãããããç§ãã¡ã¯ 2017 幎 XNUMX æã« Altinity ãèšç«ããŸããã ã¯ãªãã¯ããŠã¹ Yandex ã ãã§ãªããä»ã®ãŠãŒã¶ãŒã«ãšã£ãŠãããã«é«éãã€äŸ¿å©ã«ãªããŸãã ãããŠä»ãç§ãã¡ã¯:
- ç§ãã¡ã¯ãã¬ãŒãã³ã°ãè¡ãã以äžã«åºã¥ãããœãªã¥ãŒã·ã§ã³ã®æ§ç¯ãæ¯æŽããŸãã ã¯ãªãã¯ããŠã¹ 顧客ããã©ãã«ã«å·»ã蟌ãŸããªãããã«ããããŠãœãªã¥ãŒã·ã§ã³ãæçµçã«æ©èœããããã«ã
- 24æé7æ¥ãµããŒããæäŸããŸã ã¯ãªãã¯ããŠã¹- èšçœ®;
- ç§ãã¡ã¯ç¬èªã®ãšã³ã·ã¹ãã ãããžã§ã¯ããéçºããŠããŸãã
- ç§ãã¡ã¯èªåèªèº«ã«ç©æ¥µçã«ã³ãããããŸã ã¯ãªãã¯ããŠã¹ãç¹å®ã®æ©èœã確èªããããŠãŒã¶ãŒããã®ãªã¯ãšã¹ãã«å¿ããŸãã
ãã¡ããåŒã£è¶ãããæäŒãããŸã ã¯ãªãã¯ããŠã¹ Ñ MySQL, Vertica, ãªã©ã¯ã«, ã°ãªãŒã³ãã©ã , ã¬ããã·ãã ããã³ãã®ä»ã®ã·ã¹ãã ã ç§ãã¡ã¯ããŸããŸãªåãçµã¿ã«åå ããŠããããããã¯ãã¹ãŠæåããŠããŸãã
ã«ç§»åããçç± ã¯ãªãã¯ããŠã¹
é床ãèœã¡ãªãïŒ ãããäž»ãªçç±ã§ãã ã¯ãªãã¯ããŠã¹ - ããŸããŸãªã·ããªãªã«å¯Ÿå¿ããéåžžã«é«éãªããŒã¿ããŒã¹:
é·ãé人ã ãšååããŠãã人ã ããã®ã©ã³ãã ãªåŒçš ã¯ãªãã¯ããŠã¹.
ã¹ã±ãŒã©ããªãã£ã ä»ã®ããŒã¿ããŒã¹ã§ã¯ãXNUMX ã€ã®ããŒããŠã§ã¢ã§è¯å¥œãªããã©ãŒãã³ã¹ãéæã§ããŸããã ã¯ãªãã¯ããŠã¹ ãµãŒããŒãè¿œå ããã ãã§ãåçŽæ¹åã ãã§ãªãæ°Žå¹³æ¹åã«ãæ¡åŒµã§ããŸãã ãã¹ãŠãæåŸ ã©ããã«ã¹ã ãŒãºã«æ©èœããããã§ã¯ãããŸããããããŸããããŸãã ããžãã¹ã®æé·ã«åãããŠã·ã¹ãã ãæ¡åŒµã§ããŸãã éèŠãªã®ã¯ãçŸåšã®ãœãªã¥ãŒã·ã§ã³ã«å¶éããããåžžã«éçºã®å¯èœæ§ãããããšã§ãã
æºåž¯æ§ã äžã€ã®ãã®ã«å¯Ÿããå·çã¯ãããŸããã ããšãã°ã Amazonã¬ããã·ãã ã©ããã«ç§»åããã®ã¯é£ããã§ãã ã ã¯ãªãã¯ããŠã¹ ã©ãããããããµãŒããŒã«ã€ã³ã¹ããŒã«ããããã¯ã©ãŠãã«å±éãããã§ããŸãã Kubernetes â ã€ã³ãã©ã¹ãã©ã¯ãã£ã®éçšã«å¶éã¯ãããŸããã ããã¯èª°ã«ãšã£ãŠã䟿å©ã§ãããä»ã®å€ãã®åæ§ã®ããŒã¿ããŒã¹ã«ã¯ãªã倧ããªå©ç¹ã§ãã
æè»æ§. ã¯ãªãã¯ããŠã¹ Yandex.Metrica ãªã©ãXNUMX ã€ã®ãã®ã«ãšã©ãŸããããŸããŸãå€ãã®ç°ãªããããžã§ã¯ããæ¥çã§éçºããã䜿çšãããŠããŸãã æ°ããåé¡ã解決ããããã«æ°ããæ©èœãè¿œå ããããšã§æ¡åŒµã§ããŸãã ããšãã°ããã°ãããŒã¿ããŒã¹ã«ä¿åããããšã¯ãããŒéåã§ãããšèããããŠããããã次ã®ãããªããšãèããããŸããã Elasticsearchã ã§ãæè»æ§ã®ããã㧠ã¯ãªãã¯ããŠã¹ããã°ãä¿åããããšãã§ããå€ãã®å Žåããã㯠ãããããã«åªããŠããŸãã Elasticsearch - 㧠ã¯ãªãã¯ããŠã¹ ããã«ã¯å¿ èŠãªéã®éã10åã®XNUMXã«ãªããŸãã
ããªãŒ ãªãŒãã³ãœãŒã¹ã äœãæ¯æãå¿ èŠã¯ãããŸããã ã©ããããããŸãã¯ãµãŒããŒã«ã·ã¹ãã ãã€ã³ã¹ããŒã«ããããã®èš±å¯ã亀æžããå¿ èŠã¯ãããŸããã é ããææ°æã¯ãããŸããã åæã«ãä»ã®ãªãŒãã³ãœãŒã¹ ããŒã¿ããŒã¹ ãã¯ãããžã¯é床ã«ãããŠç«¶åã§ããŸããã ã¯ãªãã¯ããŠã¹. MySQLãMariaDBãGreenplum - ãããã¯ãã¹ãŠã¯ããã«é ãã§ãã
ã³ãã¥ããã£ããã©ã€ãã 楜ããã§ãã 㧠ã¯ãªãã¯ããŠã¹ çŽ æŽãããã³ãã¥ããã£ïŒäº€æµäŒããã£ããããããŠãšãã«ã®ãŒãšæ¥œèŠ³äž»çŸ©ã§ç§ãã¡å šå¡ãå é»ããŠãããã¢ã¬ã¯ã»ã€ã»ãããŽã£ããã
ã¯ãªãã¯ããŠã¹ãžã®ç§»è¡
ã«è¡ãã«ã¯ ã¯ãªãã¯ããŠã¹ äœããã®çç±ã§ãå¿ èŠãªã®ã¯æ¬¡ã® XNUMX ã€ã ãã§ãã
- éçãç解ãã ã¯ãªãã¯ããŠã¹ ãããŠãããé©ããªããã®ã
- å©çšãã ãã¯ãããžãŒãšãã®æ倧ã®åŒ·ã¿ã
- å®éšã ä»çµã¿ãç解ããŠããŠã ã¯ãªãã¯ããŠã¹ããã€æ©ããªããããã€é ããªããããã€è¯ããªããããã€æªããªãããåžžã«äºæž¬ã§ããããã§ã¯ãããŸããã ããã§è©ŠããŠã¿ãŠãã ããã
åŒã£è¶ãåé¡
ãã§ãã㯠XNUMX ã€ã ãã§ãã ã¯ãªãã¯ããŠã¹ ä»ã®äœããåå ã§ãéåžžã¯äœãåé¡ãçºçããŸãã ç§ãã¡ã¯ããæ°ã«å ¥ãã®ããŒã¿ããŒã¹ã§æ©èœããããã€ãã®ãã©ã¯ãã£ã¹ãç©äºã«æ £ããŠããŸãã ããšãã°ãäžç·ã«åããŠãã人ã¯èª°ã§ãã SQL ããŒã¿ããŒã¹ã§ã¯ã次ã®äžé£ã®é¢æ°ãå¿ é ã§ãããšèŠãªãããŸãã
- ååŒ;
- å¶çŽã
- äžè²«æ§;
- ã€ã³ããã¯ã¹
- æŽæ°/åé€;
- NULL;
- ããªç§ã
- èªååãã£ã¹ãã
- è€æ°ã®çµåã
- ä»»æã®ããŒãã£ã·ã§ã³ã
- ã¯ã©ã¹ã¿ãŒç®¡çããŒã«ã
åéã¯å¿ é ã§ãããXNUMX幎åã« ã¯ãªãã¯ããŠã¹ ãããã®æ©èœã¯ã©ããå©çšã§ããŸããã§ããã çŸåšããã©ã³ã¶ã¯ã·ã§ã³ãå¶çŽãäžè²«æ§ãããªç§ãåãã£ã¹ããªã©ãå®è£ ãããŠããªããã®ã¯ååæªæºãæ®ã£ãŠããŸãã
ãããŠéèŠãªããšã¯ã ã¯ãªãã¯ããŠã¹ äžéšã®æšæºçãªãã©ã¯ãã£ã¹ãã¢ãããŒãã¯æ©èœããªãã£ããããããŸã§ãšç°ãªãæ¹æ³ã§æ©èœãããããŸãã ã«ç»å Žãããã®ãã¹ãŠ ã¯ãªãã¯ããŠã¹ã ã«å¯Ÿå¿ "ã¯ãªãã¯ããŠã¹ã®ããæ¹"ãã€ãŸãä»ã®ããŒã¿ããŒã¹ãšã¯æ©èœãç°ãªããŸãã äŸãã°ïŒ
- ã€ã³ããã¯ã¹ã¯éžæãããŸããããã¹ããããããŸãã
- æŽæ°/åé€ åæã§ã¯ãªããéåæã§ãã
- è€æ°ã®çµåããããŸãããã¯ãšãª ãã©ã³ããŒã¯ãããŸããã äžè¬ã«ãããããã©ã®ããã«å®è¡ããããã¯ãããŒã¿ããŒã¹ã®äžçã®äººã ã«ãšã£ãŠã¯ããŸãæ確ã§ã¯ãããŸããã
ã¯ãªãã¯ããŠã¹ã¹ã¯ãªãã
1960幎ããã³ã¬ãªãŒåºèº«ã®ã¢ã¡ãªã«ã®æ°åŠè ãŠã£ã°ã㌠EP èšäºãæžããŸãããèªç¶ç§åŠã«ãããæ°åŠã®äžåçãªæå¹æ§ãïŒãèªç¶ç§åŠã«ãããæ°åŠã®ç解ã§ããªãæå¹æ§ãïŒãç§ãã¡ã®åšãã®äžçã¯äœããã®çç±ã§æ°åŠçæ³åã«ãã£ãŠãã説æãããŠãããšããããšã§ãã æ°åŠã¯æœè±¡çãªç§åŠã§ãããæ°åŠçãªåœ¢åŒã§è¡šçŸãããç©çæ³åã¯èªæã§ã¯ãããŸããã ãŠã£ã°ã㌠EP ããã¯éåžžã«å¥åŠã§ãããšåŒ·èª¿ããã
ç§ã®èŠç¹ããã ã¯ãªãã¯ããŠã¹ -åãå¥åŠãã ãŠã£ã°ããŒã®èšèãèšãæãããšã次ã®ããã«èšããŸãããèããããªãã»ã©ã®å¹çã¯é©ãã¹ããã®ã§ãã ã¯ãªãã¯ããŠã¹ ããŸããŸãªåæçšéã«äœ¿çšã§ããŸãã
ããšãã°ã ãªã¢ã«ã¿ã€ã ããŒã¿ ãŠã§ã¢ããŠã¹ãããŒã¿ã¯ã»ãŒç¶ç¶çã«ããŒããããŸãã XNUMX ç§é
ãã§ãªã¯ãšã¹ããåãåããããšèããŠããŸãã ãé¡ãããŸã - 䜿ã£ãŠãã ãã ã¯ãªãã¯ããŠã¹ããããèšèšãããã·ããªãªã ããã§ãã ã¯ãªãã¯ããŠã¹ ããã¯ãŸãã«ãWeb ã ãã§ãªããããŒã±ãã£ã³ã°ã財ååæã§ã䜿çšãããæ¹æ³ã§ãã ã¢ãããã¯hãåæ§ã« äžæ£è¡çºã®æ€åºn. 㧠ãªã¢ã«ã¿ã€ã ããŒã¿ ãŠã§ã¢ããŠã¹ ãã¹ã¿ãŒãããã¹ããŒãã¬ãŒã¯ãã®ãããªè€éãªæ§é ã®ã¹ããŒã ã䜿çšãããå€ãã®ããŒãã«ã ç»é² (å Žåã«ãã£ãŠã¯è€æ°)ãããŒã¿ã¯éåžžãäžéšã®ã·ã¹ãã ã«ä¿åãããå€æŽãããŸãã
å¥ã®ã·ããªãªãèããŠã¿ãŸããã - æç³»å: ããã€ã¹ããããã¯ãŒã¯ã䜿çšçµ±èšãã¢ãââã®ã€ã³ã¿ãŒãããã®ç£èŠã ããã§ã¯ãæéã«åŸã£ãŠé åºä»ããããéåžžã«åçŽãªã€ãã³ãã«ééããŸãã ã¯ãªãã¯ããŠã¹ ã¯ããšããšãã®ããã«éçºããããã®ã§ã¯ãããŸããã§ããããããŸãæ©èœããããšãããã£ãã®ã§ã倧äŒæ¥ã¯ ã¯ãªãã¯ããŠã¹ ç£èŠæ
å ±ã®ãªããžããªãšããŠã é©åãã©ãããæ€èšãããã ã¯ãªãã¯ããŠã¹ æç³»åã«ã€ããŠã¯ãã¢ãããŒããšçµæã«åºã¥ããŠãã³ãããŒã¯ãäœæããŸãã æµå
¥DB О ã¿ã€ã ã¹ã±ãŒã«DB - å°éåããã æç³»å ããŒã¿ããŒã¹ã
Ð æç³»å éåžžã¯ãçãããŒãã« (ããã€ãã®å°ããªå) ã䜿çšãããŸãã ã¢ãã¿ãªã³ã°ããã¯å€§éã®ããŒã¿ (XNUMX ç§ãããæ°çŸäžã¬ã³ãŒã) ãååŸãããå¯èœæ§ãããããããã¯éåžžãå°ããªããŒã¹ãã§ååŸãããŸã (ãžã® ã¹ããªãŒãã³ã°ïŒã ãããã£ãŠãå¥ã®æ¿å
¥ã¹ã¯ãªãããå¿
èŠã§ãããã¯ãšãªèªäœã«ãç¬èªã®è©³çŽ°ããããŸãã
ãã°ç®¡çã ãã°ãããŒã¿ããŒã¹ã«åéããã®ã¯éåžžã¯å¥œãŸãããããŸãããã ã¯ãªãã¯ããŠã¹ ããã¯ãäžã§èª¬æããããã«ããã€ãã®ã³ã¡ã³ãã䜿çšããŠè¡ãããšãã§ããŸãã å€ãã®äŒæ¥ãå©çšããŠãã ã¯ãªãã¯ããŠã¹ ãŸãã«ãã®ç®çã®ããã«ã ãã®å Žåããã°å šäœãä¿åãããã©ããã¯ã€ãããŒãã«ã䜿çšããŸãïŒããšãã°ã次ã®åœ¢åŒïŒ JSONã®ïŒããŸãã¯çŽ°ããåããŸãã éåžžãããŒã¿ã¯å€§ããªããã (ãã¡ã€ã«) ã§ããŒããããäœããã®ãã£ãŒã«ãã§æ€çŽ¢ããŸãã
éåžžããããã®æ©èœããšã«å°çšã®ããŒã¿ããŒã¹ã䜿çšãããŸãã ã¯ãªãã¯ããŠã¹ 人ã¯ããããã¹ãŠããªãããšãã§ããããã圌ããäžåãã»ã©åªããŠããŸãã ã§ã¯ã詳ããèŠãŠã¿ãŸããã æç³»å ã·ããªãªãšæ£ãããæçãããæ¹æ³ ã¯ãªãã¯ããŠã¹ ãã®ã·ããªãªã®å Žåã
æç³»å
çŸåšããããã¡ã€ã³ã·ããªãªã§ãã ã¯ãªãã¯ããŠã¹ æšæºçãªè§£æ±ºçãšèããããŸãã æç³»å æéã®çµéã«äŒŽãäžé£ã®ã€ãã³ãã§ãããæéã®çµéã«äŒŽãäœããã®ããã»ã¹ã®å€åãè¡šããŸãã ããšãã°ããã㯠XNUMX æ¥ãããã®å¿ææ°ãã·ã¹ãã å ã®ããã»ã¹æ°ãªã©ã§ãã äœããã®æ¬¡å ã§æéãå»ããã®ã¯ãã¹ãŠ æç³»å:
ãã®çš®ã®ã€ãã³ãã®ã»ãšãã©ã¯ç£èŠã«ãã£ãŠçºçããŸãã ããã¯ãWeb ãç£èŠããã ãã§ãªããå®éã®ããã€ã¹ (èªåè»ãç£æ¥ã·ã¹ãã ã IoTãå·¥å Žãç¡äººã¿ã¯ã·ãŒãYandex ããã§ã«ãã©ã³ã¯ã«ç©ãã§ãã ã¯ãªãã¯ããŠã¹-ãµãŒãã
ããšãã°ãè¹è¶ããããŒã¿ãåéããäŒç€ŸããããŸãã ã³ã³ããè¹ã®ã»ã³ãµãŒã¯æ°ç§ããšã«ãæ°çŸãã®ç°ãªã枬å®å€ãéä¿¡ããŸãã ã³ã³ããè¹ã¯äžç§ãããšãã¢ã€ãã«ç¶æ ã§ãã£ãŠã¯ãããªãããããšã³ãžãã¢ã¯ããããç 究ããã¢ãã«ãæ§ç¯ããè¹ãããã«å¹ççã«äœ¿çšãããŠããããç解ããããšããŸãã ããŠã³ã¿ã€ã ã¯ééã®æ倱ã«ã€ãªãããããåæ¢ãæå°éã«æããããããã«ã«ãŒããäºæž¬ããããšãéèŠã§ãã
çŸåšã§ã¯ã æç³»åã çŸå Žã§ DBãšã³ãžã³ ããŸããŸãªããŒã¿ããŒã¹ã¯äœããã®åœ¢ã§ã©ã³ã¯ä»ããããŠãããã¿ã€ãå¥ã«è¡šç€ºã§ããŸãã
äžçªæé·ãæ©ãã¿ã€ãã¯ã æç³»ås. ã°ã©ãããŒã¿ããŒã¹ãæé·ããŠããŸããã æç³»åããæ°å¹Žã§æ¥éã«æé·ããŠããŸãã ãã®ãã¡ããªãŒã®ããŒã¿ããŒã¹ã®ä»£è¡šçãªãã®ã¯æ¬¡ã®ãšããã§ãã æµå
¥DB, ããã¡ããŠã¹, KDB, ã¿ã€ã ã¹ã±ãŒã«DB (äžã«æ§ç¯ããã PostgreSQL)ãããã®è§£æ±ºç Amazon. ã¯ãªãã¯ããŠã¹ ããã§ã䜿çšã§ãã䜿çšãããŠããŸãã ããã€ãã®å
¬çãªäŸã玹ä»ããŸãããã
å é§è ã®ã²ãšã€ãå瀟ã§ã CloudFlareã® (CDN-ãããã€ããŒïŒã 圌ãã¯åœŒããç£èŠããŠããŸã CDN ã¹ã«ãŒ ã¯ãªãã¯ããŠã¹ (DNS-ãªã¯ãšã¹ãã HTTP-ã¯ãšãªïŒãèšå€§ãªè² è· - 6 ç§ããã XNUMX äžã€ãã³ãã ãã¹ãŠãééããŸã ã«ãã«ã ã«è¡ã ã¯ãªãã¯ããŠã¹ãã·ã¹ãã å ã®ã€ãã³ãã®ããã·ã¥ããŒãããªã¢ã«ã¿ã€ã ã§è¡šç€ºããæ©äŒãæäŸããŸãã
ã³ã ãã£ã¹ã - ç±³åœã®é»æ°éä¿¡åéã®ãªãŒããŒã® XNUMX ã€: ã€ã³ã¿ãŒããããããžã¿ã« ãã¬ããé»è©±ã 圌ãã¯åæ§ã®å¶åŸ¡ã·ã¹ãã ãäœæããŸãã CDN å ã« ãªãŒãã³ãœãŒã¹ ãããžã§ã¯ã Apache ãã©ãã£ãã¯å¶åŸ¡ èšå€§ãªããŒã¿ãæ±ãããã«ã ã¯ãªãã¯ããŠã¹ åæã®ããã¯ãšã³ããšããŠäœ¿çšãããŸãã
ãã«ã³ã çµã¿èŸŒãŸããŠãã ã¯ãªãã¯ããŠã¹ ããªãã®äžã« PMMããŸããŸãªã¢ãã¿ãªã³ã°ãä¿åãã MySQL.
ç¹å®ã®èŠä»¶
æç³»åããŒã¿ããŒã¹ã«ã¯ç¬èªã®åºæã®èŠä»¶ããããŸãã
- å€ãã®ãšãŒãžã§ã³ãããã®è¿ éãªæ¿å ¥ã å€ãã®ã¹ããªãŒã ããã®ããŒã¿ãéåžžã«è¿ éã«æ¿å ¥ããå¿ èŠããããŸãã ã¯ãªãã¯ããŠã¹ ãã¹ãŠã®æ¿å ¥ããã³ããããã³ã°ã§ããããããããããŸãæ©èœããŸãã ã©ãã§ã insert ã¯ãã£ã¹ã¯äžã®æ°ãããã¡ã€ã«ã§ãããå°ããªæ¿å ¥ã¯äœããã®æ¹æ³ã§ãããã¡ãªã³ã°ã§ããŸãã 㧠ã¯ãªãã¯ããŠã¹ ããŒã¿ãäžåºŠã« XNUMX è¡ãã€æ¿å ¥ãããããã倧ããªãããã«åããŠæ¿å ¥ããããšããå§ãããŸãã
- æè»ãªã¹ããŒã ã 㧠æç³»å éåžžãç§ãã¡ã¯ããŒã¿æ§é ãå®å šã«ã¯ç¥ããŸããã ç¹å®ã®ã¢ããªã±ãŒã·ã§ã³åãã«ç£èŠã·ã¹ãã ãæ§ç¯ããããšã¯å¯èœã§ããããããå¥ã®ã¢ããªã±ãŒã·ã§ã³ã«äœ¿çšããã®ã¯å°é£ã§ãã ããã«ã¯ãããæè»ãªã¹ããŒã ãå¿ èŠã§ãã ã¯ãªãã¯ããŠã¹ã¯ã匷ãåä»ããããããŒã¹ã§ãã£ãŠãããããè¡ãããšãã§ããŸãã
- ããŒã¿ã®å¹ççãªä¿ç®¡ãšå¿ãã é垞㯠æç³»å èšå€§ãªéã®ããŒã¿ããããããã§ããã ãå¹ççã«ä¿åããå¿ èŠããããŸãã ããšãã°ã æµå ¥DB åªããå§çž®ãäž»ãªç¹åŸŽã§ãã ãã ããä¿åããã ãã§ãªããå€ãããŒã¿ããå¿ããŠããäœããã®åŠçãå®è¡ã§ããå¿ èŠããããŸãã ããŠã³ãµã³ããªã³ã° â åéäœã®èªåã«ãŠã³ãã
- éçŽããŒã¿ã«å¯Ÿããé«éã¯ãšãªã éå» 5 åéãããªç§åäœã®ç²ŸåºŠã§èŠããšèå³æ·±ãå ŽåããããŸãããæ次ããŒã¿ã§ã¯åãç§ã®ç²åºŠã¯å¿ èŠãªãå ŽåããããŸããäžè¬çãªçµ±èšã§ååã§ãã ãã®çš®ã®ãµããŒãã¯å¿ èŠã§ããããã§ãªããšã3 ãæã®ãªã¯ãšã¹ãã¯å®äºãããŸã§ã«éåžžã«é·ãæéãããããŸãã ã¯ãªãã¯ããŠã¹.
- ããã®ãããªãªã¯ãšã¹ãæåŸã®ãã€ã³ããçŸåšÂ»ã ãããã¯å žåçãªãã®ã§ã æç³»å ã¯ãšãª: ããæç¹ã§ã®ã·ã¹ãã ã®æåŸã®æž¬å®å€ãŸãã¯ç¶æ ã確èªããŸãã tã ãããã¯ããŒã¿ããŒã¹ã«ãšã£ãŠããŸãå¿«é©ãªã¯ãšãªã§ã¯ãããŸããããå®è¡ã§ããå¿ èŠããããŸãã
- ãæ¥çãæç³»å. æç³»å æç³»åã§ãã XNUMX ã€ã®æç³»åãããå Žåãå€ãã®å Žåãããããæ¥ç¶ããŠçžé¢ãããå¿ èŠããããŸãã ãã¹ãŠã®ããŒã¿ããŒã¹ãç¹ã«äœçœ®åãããããŠããªãæç³»åã§ãããè¡ãã®ã¯äŸ¿å©ã§ã¯ãããŸããã以äžã«ããã€ãã®æç¹ã瀺ããŸãããä»ã®æç¹ããããŸãã å¹³åçãšèããããšãã§ããŸãããçªç¶ç©Žãéãå¯èœæ§ããããããæ確ã§ã¯ãããŸããã
ãããã®èŠä»¶ãã©ã®ããã«æºããããããèŠãŠã¿ãŸããã ã¯ãªãã¯ããŠã¹.
ã¹ããŒã
Ð ã¯ãªãã¯ããŠã¹ ã®ããã®ã¹ããŒã æç³»å ããŒã¿ã®èŠåæ§ã®çšåºŠã«å¿ããŠãããŸããŸãªæ¹æ³ã§å®è¡ã§ããŸãã ãã¹ãŠã®ææšãäºåã«ããã£ãŠããã°ãéåžžã®ããŒã¿ã«åºã¥ããŠã·ã¹ãã ãæ§ç¯ããããšãå¯èœã§ãã ããšãã°ãç§ã¯ãããããŸãã CloudFlareã® ã¢ãã¿ãªã³ã°ä»ã CDN ååã«æé©åãããã·ã¹ãã ã§ãã ã€ã³ãã©å šäœãããŸããŸãªãµãŒãã¹ãç£èŠãããããæ±çšçãªã·ã¹ãã ãæ§ç¯ã§ããŸãã äžèŠåãªããŒã¿ã®å Žåãäœãç£èŠããŠããã®ããäºåã«ããããŸããããããããããæãäžè¬çãªã±ãŒã¹ã§ãã
éåžžã®ããŒã¿ã ã³ã©ã ã ã¹ããŒã ã¯ã·ã³ãã«ã§ãå¿ èŠãªã¿ã€ãã®åãå«ãŸããŠããŸãã
CREATE TABLE cpu (
created_date Date DEFAULT today(),
created_at DateTime DEFAULT now(),
time String,
tags_id UInt32, /* join to dim_tag */
usage_user Float64,
usage_system Float64,
usage_idle Float64,
usage_nice Float64,
usage_iowait Float64,
usage_irq Float64,
usage_softirq Float64,
usage_steal Float64,
usage_guest Float64,
usage_guest_nice Float64
) ENGINE = MergeTree(created_date, (tags_id, created_at), 8192);
ããã¯ãããçš®ã®ã·ã¹ãã èªã¿èŸŒã¿ã¢ã¯ãã£ããã£ãç£èŠããéåžžã®ããŒãã«ã§ã (user, ã, ã¢ã€ãã«, niceïŒã ã·ã³ãã«ã§äŸ¿å©ã§ãããæè»æ§ã¯ãããŸããã ããæè»ãªã¹ããŒã ãå¿ èŠãªå Žåã¯ãé åã䜿çšã§ããŸãã
äžèŠåãªããŒã¿ã é å:
CREATE TABLE cpu_alc (
created_date Date,
created_at DateTime,
time String,
tags_id UInt32,
metrics Nested(
name LowCardinality(String),
value Float64
)
) ENGINE = MergeTree(created_date, (tags_id, created_at), 8192);
SELECT max(metrics.value[indexOf(metrics.name,'usage_user')]) FROM ...
æ§é å ¥ãåã«ãªã£ã 㯠XNUMX ã€ã®é åã§ã: metrics.name О ã¡ããªã¯ã¹.å€ã ããã§ã¯ããã®ãããªä»»æã®ç£èŠããŒã¿ããã€ãã³ãããšã®ååã®é åããã³æž¬å®å€ã®é åãšããŠä¿åã§ããŸãã ããã«æé©åããã«ã¯ããã®ãããªæ§é ã XNUMX ã€ã§ã¯ãªããè€æ°äœæã§ããŸãã ããšãã°ãXNUMX ã€ã¯ ãããŒã-å€ãå¥ã® - ã® intå-ã€ãŸãããªããªã intå ãã£ãšå¹ççã«åçŽãããã
ãããããã®ãããªæ§é ã«ã¢ã¯ã»ã¹ããã®ã¯ããã«å°é£ã§ãã æåã«ã€ã³ããã¯ã¹ã次ã«é åã®å€ãåŒãåºãã«ã¯ãç¹å¥ãªé¢æ°ã䜿çšããŠç¹å¥ãªæ§é ã䜿çšããå¿ èŠããããŸãã
SELECT max(metrics.value[indexOf(metrics.name,'usage_user')]) FROM ...
ããããããã§ãéåžžã«éãåäœããŸãã äžèŠåãªããŒã¿ãæ ŒçŽãããã XNUMX ã€ã®æ¹æ³ã¯ãè¡ããšã§ãã
äžèŠåãªããŒã¿ã æååã ãã®åŸæ¥ã®æ¹æ³ã§ã¯ãé åã䜿çšãããååãšå€ãåæã«ä¿åãããŸãã 5 ã€ã®ããã€ã¹ããäžåºŠã« 000 ã®æž¬å®å€ãååŸãããå ŽåãããŒã¿ããŒã¹ã«ã¯ 5 è¡ãçæãããŸãã
CREATE TABLE cpu_rlc (
created_date Date,
created_at DateTime,
time String,
tags_id UInt32,
metric_name LowCardinality(String),
metric_value Float64
) ENGINE = MergeTree(created_date, (metric_name, tags_id, created_at), 8192);
SELECT
maxIf(metric_value, metric_name = 'usage_user'),
...
FROM cpu_r
WHERE metric_name IN ('usage_user', ...)
ã¯ãªãã¯ããŠã¹ ããã«å¯ŸåŠããŸã - ç¹å¥ãªæ¡åŒµæ©èœããããŸã ã¯ãªãã¯ããŠã¹ SQLã äŸãã°ã maxIf â äœããã®æ¡ä»¶ãæºããããå Žåã«ã¡ããªã¯ã¹ã«ãã£ãŠæ倧å€ãèšç®ããç¹å¥ãªé¢æ°ã XNUMX ã€ã®ãªã¯ãšã¹ãã§ãã®ãããªåŒãè€æ°èšè¿°ããè€æ°ã®ã¡ããªã¯ã¹ã®å€ãå³åº§ã«èšç®ã§ããŸãã
XNUMX ã€ã®ã¢ãããŒããæ¯èŒããŠã¿ãŸãããã
ããã§ã¯ãããã€ãã®ãã¹ã ããŒã¿ ã»ããã«ããã£ã¹ã¯ ããŒã¿ ãµã€ãºããè¿œå ããŸããã åã®å ŽåãããŒã¿ ãµã€ãºã¯æå°ãã€ãŸãæ倧ã®å§çž®ãšæ倧ã®ã¯ãšãªé床ãå®çŸã§ããŸããããã¹ãŠãäžåºŠã«èšé²ããå¿ èŠãããããã代åã䌎ããŸãã
é åã®å Žåã¯ããã¹ãŠãå°ãæªããªããŸãã ããŒã¿ã¯äŸç¶ãšããŠååã«å§çž®ãããŠãããäžèŠåãªãã¿ãŒã³ãä¿åã§ããŸãã ããã ã¯ãªãã¯ããŠã¹ - ååããŒã¿ããŒã¹ããã¹ãŠãé åã«ä¿åãå§ãããšãããã XNUMX è¡ç®ã«å€ãããå¹çæ§ãšæè»æ§ã察䟡ãšããŸãã ã©ã®ãããªæäœã§ããé åå šäœãã¡ã¢ãªã«èªã¿èŸŒãã§ããããã®äžã§ç®çã®èŠçŽ ãèŠã€ããå¿ èŠããããŸããé åã倧ãããªããšãé床ãäœäžããŸãã
ãã®ã¢ãããŒããæ¡çšããŠããäŒæ¥ã® XNUMX 〠(ããšãã°ã
æãç°¡åãªã¢ãããŒãã¯æååã䜿çšããããšã§ãã ããããããŒã¿ã®å§çž®ãäžååã§ããŒãã« ãµã€ãºã倧ãããã¯ãšãªãè€æ°ã®ã¡ããªã¯ã¹ã«åºã¥ããŠããå Žåã§ããClickHouse ã¯æé©ã«åäœããŸããã
ãã€ããªããæ¹åŒ
ã¢ã¬ã€åè·¯ãéžæãããšä»®å®ããŸãããã ãã ããã»ãšãã©ã®ããã·ã¥ããŒãã«ã¯ãŠãŒã¶ãŒãšã·ã¹ãã ã®ã¡ããªã¯ã¹ã®ã¿ã衚瀺ãããããšãããã£ãŠããå Žåã¯ã次ã®æ¹æ³ã§ãããã®ã¡ããªã¯ã¹ãããŒãã« ã¬ãã«ã§é åããåã«å ·äœåããããšãã§ããŸãã
CREATE TABLE cpu_alc (
created_date Date,
created_at DateTime,
time String,
tags_id UInt32,
metrics Nested(
name LowCardinality(String),
value Float64
),
usage_user Float64
MATERIALIZED metrics.value[indexOf(metrics.name,'usage_user')],
usage_system Float64
MATERIALIZED metrics.value[indexOf(metrics.name,'usage_system')]
) ENGINE = MergeTree(created_date, (tags_id, created_at), 8192);
æ¿å ¥æ ã¯ãªãã¯ããŠã¹ èªåçã«ã«ãŠã³ããããŸãã ãã®ããã«ããŠãããžãã¹ãšåã³ãçµã¿åãããããšãã§ããŸããã¹ããŒã ã¯æè»ã§äžè¬çã§ãããæãé »ç¹ã«äœ¿çšãããåãæç²ããŸããã ããã«ã¯ã€ã³ãµãŒããå€æŽããå¿ èŠããªãããšã«æ³šæããŠãã ããã ETLããã«ãããããŒãã«ãžã®é åã®æ¿å ¥ãç¶è¡ãããŸãã ãã£ãã°ããã§ã ä»ã®æºãããã€ãã®ã¹ããŒã«ãŒãè¿œå ããããã«äœ¿ãå§ããããšãã§ãããã€ããªããã§é«éãªã¹ããŒã ãå ¥æããŸããã
ã³ãŒããã¯ãšå§çž®
ã®ããã« æç³»å æ å ±éã¯éåžžã«å€§ãããªãå¯èœæ§ããããããããŒã¿ãã©ã®ããã«å§çž®ããããéèŠã§ãã 㧠ã¯ãªãã¯ããŠã¹ 1:10ã1:20ãå Žåã«ãã£ãŠã¯ãã以äžã®å§çž®å¹æãå®çŸããããŒã«ã®ã»ããããããŸãã ããã¯ããã£ã¹ã¯äžã® 1 TB ã®è§£åãããããŒã¿ã 50 ïœ 100 GB ãå æããããšãæå³ããŸãã ãµã€ãºãå°ããã»ã©ãããŒã¿ã®èªã¿åããšåŠçãé«éã«ãªããŸãã
é«ã¬ãã«ã®å§çž®ãå®çŸããã«ã¯ã ã¯ãªãã¯ããŠã¹ ã¯æ¬¡ã®ã³ãŒããã¯ããµããŒãããŠããŸãã
ããŒãã«ã®äŸ:
CREATE TABLE benchmark.cpu_codecs_lz4 (
created_date Date DEFAULT today(),
created_at DateTime DEFAULT now() Codec(DoubleDelta, LZ4),
tags_id UInt32,
usage_user Float64 Codec(Gorilla, LZ4),
usage_system Float64 Codec(Gorilla, LZ4),
usage_idle Float64 Codec(Gorilla, LZ4),
usage_nice Float64 Codec(Gorilla, LZ4),
usage_iowait Float64 Codec(Gorilla, LZ4),
usage_irq Float64 Codec(Gorilla, LZ4),
usage_softirq Float64 Codec(Gorilla, LZ4),
usage_steal Float64 Codec(Gorilla, LZ4),
usage_guest Float64 Codec(Gorilla, LZ4),
usage_guest_nice Float64 Codec(Gorilla, LZ4),
additional_tags String DEFAULT ''
)
ENGINE = MergeTree(created_date, (tags_id, created_at), 8192);
ããã§ã³ãŒããã¯ãå®çŸ©ããŸã ããã«ãã«ã¿ ããå Žåã«ã¯ãããäžã€ã®å Žåã«ã¯ - ãŽãªã©ããããŠç§ãã¡ã¯ééããªãããã«è¿œå ããŸã LZ4 å§çž®ã ãã®çµæããã£ã¹ã¯äžã®ããŒã¿ã®ãµã€ãºãå€§å¹ ã«æžå°ããŸãã
ããã¯ãç°ãªãã³ãŒããã¯ãšå§çž®ã䜿çšããå Žåã«ãåãããŒã¿ãã©ãã ãã®ã¹ããŒã¹ãå ãããã瀺ããŠããŸãã
- ãã£ã¹ã¯äžã® GZIP ãã¡ã€ã«å ã
- ClickHouse ã§ã¯ã³ãŒããã¯ã¯äœ¿çšãããZSTD å§çž®ã䜿çšããŸãã
- ClickHouse ã§ã¯ã³ãŒããã¯ãšå§çž® LZ4 ããã³ ZSTD ã䜿çšããŸãã
ã³ãŒããã¯ãå«ãããŒãã«ã®æ¹ãå æããã¹ããŒã¹ãã¯ããã«å°ãªãããšãããããŸãã
ãµã€ãºã®åé¡
ããã»ã©éèŠã§ã¯ãªã
äžèšã®ãã¹ãŠã®äŸã§äœ¿çšããã®ã¯ã ãããŒã64ã ã§ãããç§ãã¡ãéžãã ãªã ãããŒã32ããããªãããã«è¯ãã§ãããã ããã¯ãäžèšã®ãªã³ã¯å
ã®èšäºã§ãPerkona ã®ã¹ã¿ããã«ãã£ãŠããå®èšŒãããŠããŸãã ã¿ã¹ã¯ã«é©ããæãã³ã³ãã¯ããªã¿ã€ãã䜿çšããããšãéèŠã§ããã¯ãšãªé床ããããã£ã¹ã¯ ãµã€ãºãå°ããå Žåã§ãåæ§ã§ãã ã¯ãªãã¯ããŠã¹ ããã«ã¯éåžžã«ææã§ãã
ãå©çšããã ããã° intxnumx 代ããã« intxnumxãããããšãã»ãŒ XNUMX åã®ããã©ãŒãã³ã¹ã®åäžãæåŸ ã§ããŸãã ããŒã¿ãå æããã¡ã¢ãªãå°ãªããªãããã¹ãŠã®ãæŒç®ããããé«éã«å®è¡ãããŸãã ã¯ãªãã¯ããŠã¹ å éšçã«ã¯éåžžã«å³å¯ã«åæå®ãããã·ã¹ãã ã§ãããææ°ã®ã·ã¹ãã ãæäŸãããã¹ãŠã®å¯èœæ§ãæ倧éã«æŽ»çšããŸãã
éèšãš ãããªã¢ã©ã€ãºããã¥ãŒ
éèšãã¥ãŒãšãããªã¢ã©ã€ãºã ãã¥ãŒã䜿çšãããšãããŸããŸãªå Žåã«éèšãäœæã§ããŸãã
ããšãã°ãééèšã®ãœãŒã¹ ããŒã¿ãããå Žåãç¹å¥ãªãšã³ãžã³ã«ããèªåéèšã䜿çšããŠãããŸããŸãªãããªã¢ã©ã€ãºã ãã¥ãŒããããã®ããŒã¿ã«ã¢ã¿ããã§ããŸãã ãµãã³ã°ããŒãžããªãŒ (SMT). SMT éèšãèªåçã«èšç®ããç¹å¥ãªéèšããŒã¿æ§é ã§ãã çããŒã¿ãããŒã¿ããŒã¹ã«æ¿å
¥ãããèªåçã«éçŽããããã®äžã§ããã·ã¥ããŒããããã«äœ¿çšã§ããããã«ãªããŸãã
TTL - å€ãããŒã¿ããå¿ããã
äžèŠã«ãªã£ãããŒã¿ããå¿ãããã«ã¯ã©ãããã°ããã§ãããã? ã¯ãªãã¯ããŠã¹ ãããè¡ãæ¹æ³ãç¥ã£ãŠããŸãã ããŒãã«ãäœæãããšãã«æå®ã§ããã®ã¯ã TTL åŒ: ããšãã°ãååäœã®ããŒã¿ã¯ 30 æ¥åãæ¥åäœã®ããŒã¿ã¯ XNUMX æ¥éä¿åããé±åäœãŸãã¯æåäœã®ããŒã¿ã«ã¯äžå觊ããªãããã«ããŸãã
CREATE TABLE aggr_by_minute
âŠ
TTL time + interval 1 day
CREATE TABLE aggr_by_day
âŠ
TTL time + interval 30 day
CREATE TABLE aggr_by_week
âŠ
/* no TTL */
å€å±€ - ããŒã¿ããã£ã¹ã¯éã§åå²ãã
ãã®èãæ¹ãããã«é²ãããšãããŒã¿ã¯æ¬¡ã®ããã«ä¿åã§ããŸãã ã¯ãªãã¯ããŠã¹ ããŸããŸãªå Žæã§ã å é±ã®ããã ããŒã¿ãéåžžã«é«éãªããŒã«ã« ãµãŒããŒã«ä¿åããããšããŸãã SSDãããã«éå»ã®ããŒã¿ãå¥ã®å Žæã«çœ®ããŸãã 㧠ã¯ãªãã¯ããŠã¹ ãããå¯èœã«ãªããŸãã:
ã¹ãã¬ãŒãž ããªã·ãŒãæ§æã§ããŸã (ã¹ãã¬ãŒãžããªã·ãŒïŒ ãã㧠ã¯ãªãã¯ããŠã¹ ç¹å®ã®æ¡ä»¶ã«éãããšãããŒã¿ãå¥ã®ã¹ãã¬ãŒãžã«èªåçã«è»¢éããŸãã
ããããããã ãã§ã¯ãããŸããã ç¹å®ã®ããŒãã«ã®ã¬ãã«ã§ãããŒã¿ããã€ã³ãŒã«ã ã¹ãã¬ãŒãžã«ä¿åãããããæ£ç¢ºã«å®çŸ©ã§ããŸãã ããšãã°ãããŒã¿ã¯éåžžã«é«éãªãã£ã¹ã¯ã« 7 æ¥éä¿åãããå€ããã®ã¯ãã¹ãŠäœéãªãã£ã¹ã¯ã«è»¢éãããŸãã ããã¯ãã³ã¹ãã管çããã³ãŒã«ã ããŒã¿ã«ãéãç¡é§ã«è²»ãããã«ãã·ã¹ãã ãæ倧ã®ããã©ãŒãã³ã¹ã«ç¶æã§ãããããåªããŠããŸãã
CREATE TABLE
...
TTL date + INTERVAL 7 DAY TO VOLUME 'cold_volume',
date + INTERVAL 180 DAY DELETE
ãŠããŒã¯ãªæ©èœ ã¯ãªãã¯ããŠã¹
ã»ãŒãã¹ãŠã«ãã㊠ã¯ãªãã¯ããŠã¹ ãã®ãããªããã€ã©ã€ããã¯ãããŸãããä»ã®ããŒã¿ããŒã¹ã«ã¯ãªãç¬å æ§ã«ãã£ãŠçžæ®ºãããŸãã ããšãã°ã次ã®ãããªãŠããŒã¯ãªæ©èœããããŸãã ã¯ãªãã¯ããŠã¹:
- é åã 㧠ã¯ãªãã¯ããŠã¹ é åã«å¯Ÿããéåžžã«åªãããµããŒããšããããã«å¯ŸããŠè€éãªèšç®ãå®è¡ããæ©èœã
- ããŒã¿æ§é ã®éçŽã ããã¯ããã©ãŒæ©èœãã® XNUMX ã€ã§ãã ã¯ãªãã¯ããŠã¹ã Yandex ã®æ åœè ã¯ããŒã¿ãéçŽããããªããšèšã£ãŠããŸããããã¹ãŠãéçŽãããŠããŸãã ã¯ãªãã¯ããŠã¹ãéããŠäŸ¿å©ã ããã§ãã
- ãããªã¢ã©ã€ãºããã¥ãŒã ããŒã¿æ§é ã®éçŽãšäœµããŠããããªã¢ã©ã€ãºã ãã¥ãŒã䜿çšãããšã䟿å©ãªããŒã¿æ§é ãå®çŸã§ããŸãã ãžã® éçŽã
- ã¯ãªãã¯ããŠã¹SQLã ããã¯èšèªæ¡åŒµæ©èœã§ã SQL ã§ã®ã¿å©çšã§ããããã€ãã®è¿œå ããã³ç¬å æ©èœãåããŠããŸãã ã¯ãªãã¯ããŠã¹ã 以åã¯ãäžæ¹ã§ã¯æ¡å€§ãä»æ¹ã§ã¯äžå©ã®ãããªç¶æ³ã§ããã ãããŸã§ãšæ¯ã¹ãŠã»ãŒãã¹ãŠã®æ¬ ç¹ã解æ¶ãããŸããã SQL 92 ãããåé€ããŸãããä»ã¯åãªãæ¡åŒµæ©èœã§ãã
- ã©ã ãâåŒã ãããã¯ãŸã ããŒã¿ããŒã¹ã«ãããŸãã?
- ML-ãµããŒãã ããã¯ããŸããŸãªããŒã¿ããŒã¹ã§å©çšã§ããŸãããããåªããããŒã¿ããŒã¹ãããã°ãããå£ã£ãããŒã¿ããŒã¹ããããŸãã
- ãªãŒãã³ãœãŒã¹ã æ¡åŒµã§ãã ã¯ãªãã¯ããŠã¹ äžç·ã«ã ä» ã¯ãªãã¯ããŠã¹ å¯çš¿è ã¯çŽ 500 åã§ããã®æ°ã¯å¢ãç¶ããŠããŸãã
ããªãããŒãªã¯ãšãª
Ð ã¯ãªãã¯ããŠã¹ åãããšãè¡ãã®ã«ããŸããŸãªæ¹æ³ããããŸãã ããšãã°ã次㮠XNUMX ã€ã®ç°ãªãæ¹æ³ã§ããŒãã«ããæåŸã®å€ãè¿ãããšãã§ããŸãã CPU (XNUMX çªç®ããããŸãããããã«ãšããŸããã¯ã§ã)ã
æåã®ãã®ã¯ããããããã«äŸ¿å©ãã瀺ããŠããŸã ã¯ãªãã¯ããŠã¹ ããã確èªããããšãã®ã¯ãšãª ã¿ãã« ãµãã¯ãšãªã«å«ãŸããŸãã ããã¯ç§ãå人çã«ä»ã®ããŒã¿ããŒã¹ã§ã¯æ¬åœã«èŠéããŠãããã®ã§ãã äœãããµãã¯ãšãªãšæ¯èŒãããå Žåãä»ã®ããŒã¿ããŒã¹ã§ã¯ã¹ã«ã©ãŒã®ã¿ãæ¯èŒã§ããŸãããããã€ãã®åã«ã€ããŠã¯æ¬¡ã®ããã«èšè¿°ããå¿ èŠããããŸãã ç»é²ã 㧠ã¯ãªãã¯ããŠã¹ ã¿ãã«ã䜿çšã§ããŸã:
SELECT *
FROM cpu
WHERE (tags_id, created_at) IN
(SELECT tags_id, max(created_at)
FROM cpu
GROUP BY tags_id)
XNUMX çªç®ã®ã¡ãœããã¯åãããšãè¡ããŸãããéèšé¢æ°ã䜿çšããŸãã argMax:
SELECT
argMax(usage_user), created_at),
argMax(usage_system), created_at),
...
FROM cpu
Ð ã¯ãªãã¯ããŠã¹ éèšé¢æ°ã¯æ°åãããã³ã³ãããŒã¿ã䜿çšãããšãçµã¿åããã®æ³åã«ããã°ãçŽ XNUMX åã®éèšé¢æ°ãåŸãããŸãã ArgMax - æ倧å€ãèšç®ããé¢æ°ã® XNUMX ã€: ãªã¯ãšã¹ãã¯å€ãè¿ããŸãã 䜿çšç¶æ³_ãŠãŒã¶ãŒãæ倧å€ã«éããŸã created_at:
SELECT now() as created_at,
cpu.*
FROM (SELECT DISTINCT tags_id from cpu) base
ASOF LEFT JOIN cpu USING (tags_id, created_at)
ã¢ãœãçµå â ç°ãªãæéã®è¡ããæ¥çãããŸãã ããã¯ããŒã¿ããŒã¹åºæã®æ©èœã§ããã kdb +ã æéãç°ãªã XNUMX ã€ã®æç³»åãããå Žåã ã¢ãœãçµå XNUMX ã€ã®ãªã¯ãšã¹ãã§ãããã移åããã³ããŒãžã§ããŸãã äžæ¹ã®æç³»åã®åå€ã«ã€ããŠãããäžæ¹ã®æç³»åã§æãè¿ãå€ãæ€çŽ¢ãããããããåãè¡ã«è¿ãããŸãã
åæé¢æ°
æšæºã§ã¯ SQL-2003 次ã®ããã«æžãããšãã§ããŸã:
SELECT origin,
timestamp,
timestamp -LAG(timestamp, 1) OVER (PARTITION BY origin ORDER BY timestamp) AS duration,
timestamp -MIN(timestamp) OVER (PARTITION BY origin ORDER BY timestamp) AS startseq_duration,
ROW_NUMBER() OVER (PARTITION BY origin ORDER BY timestamp) AS sequence,
COUNT() OVER (PARTITION BY origin ORDER BY timestamp) AS nb
FROM mytable
ORDER BY origin, timestamp;
Ð ã¯ãªãã¯ããŠã¹ ããã¯ã§ããŸãã - æšæºããµããŒãããŠããŸãã SQL-2003 ãããŠããããããã¯æ±ºããŠããªãã ããã 代ããã«ã ã¯ãªãã¯ããŠã¹ 次ã®ããã«æžãã®ãäžè¬çã§ãã
ã©ã ããçŽæããŸãã - ããã«ãããŸã!
ããã¯ãæšæºã®åæã¯ãšãªã«äŒŒãŠããŸãã SQL-2003: 圌㯠XNUMX ã€ã®éããæ°ããŸã ã¿ã€ã ã¹ã¿ã³ããæéãåºæ° - ç§ãã¡ãéåžžåæé¢æ°ãèæ ®ãããã¹ãŠã®ãã®ã 㧠ã¯ãªãã¯ããŠã¹ é åãéããŠããããã«ãŠã³ãããŸããæåã«ããŒã¿ãé åã«æãããã¿ããã®åŸé åã«å¯ŸããŠå¿ èŠãªããšããã¹ãŠå®è¡ãããããå±éããŠæ»ããŸãã ããŸã䟿å©ã§ã¯ãªããå°ãªããšãé¢æ°åããã°ã©ãã³ã°ã®ç¥èãå¿ èŠã§ãããéåžžã«æè»ã§ãã
ç¹å¥ãªæ©èœ
ããã«ã ã¯ãªãã¯ããŠã¹ å€ãã®ç¹æ®ãªæ©èœã ããšãã°ãåæã«å®è¡ãããŠããã»ãã·ã§ã³ã®æ°ã確èªããã«ã¯ã©ãããã°ããã§ãããã? äžè¬çãªç£èŠã¿ã¹ã¯ã¯ãXNUMX ã€ã®ãªã¯ãšã¹ãã§ã®æå€§è² è·ã決å®ããããšã§ãã 㧠ã¯ãªãã¯ããŠã¹ ãã®ç®çã®ããã«ç¹å¥ãªé¢æ°ããããŸãã
äžè¬ã«ãClickHouse ã«ã¯ããŸããŸãªç®çã®ããã®ç¹å¥ãªæ©èœããããŸãã
- runningDifferenceãrunningAccumulateãneighbor;
- sumMap(ããŒ, å€);
- timeSeriesGroupSum(uid, ã¿ã€ã ã¹ã¿ã³ã, å€);
- timeSeriesGroupRateSum(uid, ã¿ã€ã ã¹ã¿ã³ã, å€);
- skewPopãskewSampãkurtPopãkurtSamp;
- äžç¶¿å ¥ã/ãã¯ã¿ã€ä»ãã
- simpleLinearRegressionãstochasticLinearRegressionã
ããã¯é¢æ°ã®å®å šãªãªã¹ãã§ã¯ãªããåèšã§ 500 ïœ 600 ãããŸãã ãã³ã: ã®ãã¹ãŠã®é¢æ° ã¯ãªãã¯ããŠã¹ ã·ã¹ãã ããŒãã«ã«ãããŸã (ãã¹ãŠãææžåãããŠããããã§ã¯ãããŸãããããã¹ãŠèå³æ·±ããã®ã§ã)ã
select * from system.functions order by name
ã¯ãªãã¯ããŠã¹ ããèªäœã«é¢ããå€ãã®æ å ±ãä¿åãããŠããŸãã ãã°ããŒãã«, ã¯ãšãªãã°ããã¬ãŒã¹ ãã°ãããŒã¿ ãããã¯ã䜿çšããæäœã®ãã° (ããŒããã°)ãã¡ããªã¯ã¹ ãã°ãããã³ã·ã¹ãã ãã°ãéåžžããããã¯ãã£ã¹ã¯ã«æžã蟌ãŸããŸãã ãã°ã¡ããªã¯ã¹ã¯ æç³»å в ã¯ãªãã¯ããŠã¹ å®éã«ã¯ ã¯ãªãã¯ããŠã¹: ããŒã¿ããŒã¹èªäœã圹å²ãæããããšãã§ããŸã æç³»å ããŒã¿ããŒã¹ããé£ãèãããããšã«ãªããŸãã
ããããŠããŒã¯ãªããšã§ã - ç§ãã¡ã¯è¯ãä»äºãããŠããã®ã§ã æç³»åããªãç§ãã¡ã¯å¿
èŠãªãã®ããã¹ãŠèªåã®äžã«ä¿ç®¡ã§ããªãã®ã§ããããïŒ ç§ãã¡ã¯å¿
èŠãšããŠããªã ããã¡ããŠã¹ãç§ãã¡ã¯ãã¹ãŠãèªåèªèº«ã«ä¿ã¡ãŸãã æ¥ç¶æžã¿ ã°ã©ãã¡ã ãããŠç§ãã¡ã¯èªåèªèº«ãç£èŠããŸãã ãã ãã ã¯ãªãã¯ããŠã¹ èœã¡ãŠãçç±ãââåãããªãã®ã§ãéåžžã¯ãããªããšã¯ããŸããã
倧ããªã¯ã©ã¹ã¿ãŒãŸãã¯å€æ°ã®å°ããªã¯ã©ã¹ã¿ãŒ ã¯ãªãã¯ããŠã¹
XNUMX ã€ã®å€§ããªã¯ã©ã¹ã¿ãŒãšå€æ°ã®å°ã㪠ClickHouse ã§ã¯ã©ã¡ããåªããŠããŸãã? åŸæ¥ã®ã¢ãããŒã DWH åè·¯ãã¢ããªã±ãŒã·ã§ã³ããšã«å²ãåœãŠããã倧èŠæš¡ãªã¯ã©ã¹ã¿ãŒã§ãã ç§ãã¡ã¯ããŒã¿ããŒã¹ç®¡çè ã«çžè«ããå³ãèŠããŠããããŸããããããšã次ã®å³ããããŸããã
Ð ã¯ãªãã¯ããŠã¹ å¥ã®æ¹æ³ã§è¡ãããšãã§ããŸãã åã¢ããªã±ãŒã·ã§ã³ãç¬èªã«äœæã§ããŸã ã¯ãªãã¯ããŠã¹:
巚倧ãªæªç©ã¯ããå¿
èŠãããŸãã DWH ãããŠæã«è² ããªã管çè
ã åã¢ããªã±ãŒã·ã§ã³ã«ç¬èªã®ãã®ãäžããããšãã§ããŸã ã¯ãªãã¯ããŠã¹éçºè
ã¯èªåã§ãããè¡ãããšãã§ããŸãã ã¯ãªãã¯ããŠã¹ ã€ã³ã¹ããŒã«ã¯éåžžã«ç°¡åã§ãè€éãªç®¡çã¯å¿
èŠãããŸããã
ã§ããããããæã£ãŠããã ã¯ãªãã¯ããŠã¹é »ç¹ã«ã€ã³ã¹ããŒã«ããå¿
èŠãããå Žåã¯ããã®ããã»ã¹ãèªååããããšèããŸãã ãã®ããã«ãããšãã°ã次ã®ããã«äœ¿çšã§ããŸãã Kubernetes О ã¯ãªãã¯ããŠã¹-ãªãã¬ãŒã¿ãŒã 㧠Kubernetes ã¯ãªãã¯ããŠã¹ ããªã³ã¯ãªãã¯ããšèšãæããããšãã§ããŸãããã¿ã³ãã¯ãªãã¯ããŠãããã§ã¹ããå®è¡ãããšãããŒã¿ããŒã¹ã®æºåãæŽããŸãã ããã«å³ãäœæããããã«ã¡ããªã¯ã¹ã®ã¢ããããŒããéå§ãããšã5 åã§ããã·ã¥ããŒããå®æããŸãã ã°ã©ãã¡ãã ãšãŠãç°¡åã§ãïŒ
ãã®çµæã¯ïŒ
ãã®ããã«ã ã¯ãªãã¯ããŠã¹ - ããïŒ
- ãã°ããã ããã¯èª°ããç¥ã£ãŠããŸãã
- ãã ã å°ãè°è«ã®äœå°ããããŸãããèšç·Žã§ã¯é£ãããæŠéã§ã¯ç°¡åã ãšç§ã¯èããŠããŸãã ããæ¹ããããã° ã¯ãªãã¯ããŠã¹ ãããæ©èœããã°ããã¹ãŠãéåžžã«ç°¡åã«ãªããŸãã
- æ®éçã«ã ããŸããŸãªã·ããªãªã«é©ããŠããŸãã DWHãæç³»åããã°ã¹ãã¬ãŒãžã ããã§ã¯ãããŸãã OLTP ãããã£ãŠãããã§çãæ¿å ¥ãèªã¿åããå®è¡ããªãã§ãã ããã
- ãããããããšã«ã ããããäžç·ã«åããŠãã人ã§ããã ã¯ãªãã¯ããŠã¹ãè¯ãæå³ã§ãæªãæå³ã§ãé¢çœãç¬éãããããçµéšãããŠããã ããŸããã ããšãã°ãæ°ãããªãªãŒã¹ããªãªãŒã¹ãããåŸããã¹ãŠãæ©èœããªããªã£ãŠããŸããŸããã ãããã¯ãããã¿ã¹ã¯ã« XNUMX æ¥éèŠåŽããŠããŸããããTelegram ãã£ããã§è³ªåããåŸããã®ã¿ã¹ã¯ã¯ XNUMX åã§è§£æ±ºãããšãããããŸãã ãããã¯ãã¬ã·ã£ã»ãããŽã£ããæ°ã®å ±åäŒèŠã§ã®ã¹ã¯ãªãŒã³ã·ã§ããã®ããã«ã ã¯ãªãã¯ããŠã¹ æŸéãäžæãã HighLoad ++ã ãã®ãããªããšã¯åžžã«èµ·ãããç§ãã¡ã®ç掻ãå°é£ã«ããŠããŸãã ã¯ãªãã¯ããŠã¹ æãããŠé¢çœãïŒ
ãã¬ãŒã³ããŒã·ã§ã³ãã芧ããã ããŸã
åŸ æã®é«è² è·ã·ã¹ãã éçºè äŒè°ãéå¬ãããŸããã
HighLoad ++ 9æ10æ¥ãšXNUMXæ¥ã«ã¹ã³ã«ã³ãã§éå¬ãããã æåŸã«ãHighLoad++ ã®ãšãã«ã®ãŒããªã³ã©ã€ã³ã§ããã±ãŒãžåããããšã¯ã§ããªããããããã¯ãªãã©ã€ã³ ã«ã³ãã¡ã¬ã³ã¹ã«ãªããŸã (ãã¹ãŠã®äºé²æªçœ®ãè¬ããäžã§)ãã«ã³ãã¡ã¬ã³ã¹ã§ã¯ããã¯ãããžãŒã®æ倧ã®æ©èœã«é¢ããäºäŸãèŠã€ããŠçŽ¹ä»ããŸããHighLoad++ ã¯ãFacebookãYandexãVKontakteãGoogleãAmazon ãã©ã®ããã«æ©èœãããã XNUMX æ¥éã§åŠã¹ãå¯äžã®å Žæã§ãã
2007幎ããç¶ç¶çã«éå¬ããŠãããä»å¹Žã§14åç®ãšãªããŸãã ãã®éãã«ã³ãã¡ã¬ã³ã¹ã¯ 10 åã«æé·ããæšå¹Žã®äž»èŠãªæ¥çã€ãã³ãã«ã¯ 3339 人ã®åå è ã165 人ã®è¬æŒè ãã¬ããŒããããŒãã¢ãããéãŸãã16 ã®ãã©ãã¯ãåæã«å®è¡ãããŸããã
æšå¹Žã¯ãã¹ã20å°ããè¶ãšã³ãŒããŒã5280ãªããã«ããã«ãŒãããªã³ã¯ã1650ãªããã«ãæ°Žã10200æ¬ãã£ãã ããã«2640ããã°ã©ã ã®é£æã16æã®ç¿ãš000åã®ã«ããã ã¡ãªã¿ã«ãåççŽããéãããéã§ã25æ¬ã®ãªãŒã¯ã®èæšãæ€ããŸãã:)ãã±ãããè³Œå ¥ã§ããŸã
ãã㧠ãã«ã³ãã¡ã¬ã³ã¹ã«é¢ãããã¥ãŒã¹ãå ¥æ -ãã㧠ããã¹ãŠã®ãœãŒã·ã£ã« ãããã¯ãŒã¯ã§è©±ããŸããTelegram ,Vkontakte О
åºæïŒ habr.com