4 æãAvito ã®ãšã³ãžãã¢ã¯ãClickHouse ã®äž»èŠéçºè ã§ãã Alexey Milovidov æ°ãšãIntegros ã® Golang éçºè ã§ãã Kirill Shvakov æ°ãšã®ãªã³ã©ã€ã³äŒè°ã«éãŸããŸãããããŒã¿ããŒã¹ç®¡çã·ã¹ãã ãã©ã®ããã«äœ¿çšããããã©ã®ãããªåé¡ãçºçãããã«ã€ããŠè©±ãåããŸããã
äŒè°ã«åºã¥ããŠãããã¯ã¢ãããããŒã¿ã®åã·ã£ãŒãã£ã³ã°ãå€éšèŸæžãGolang ãã©ã€ããŒãClickHouse ããŒãžã§ã³ã®æŽæ°ã«é¢ããç§ãã¡ãšèŠèŽè ã®è³ªåã«å¯Ÿããå°é家ã®åçãèšäºã«ãŸãšããŸããããã§ã« Yandex DBMS ãç©æ¥µçã«äœ¿çšããŠããããã®çŸåšãšå°æ¥ã«èå³ãããéçºè ã«ãšã£ãŠã¯åœ¹ç«ã€ãããããŸãããããã©ã«ãã§ã¯ãç¹ã«èšèŒãããŠããªãéããåç㯠Alexey Milovidov ã«ãããã®ã§ãã
ã«ããã®äžã«ã¯å€§éã®ããã¹ããããã®ã§æ³šæããŠãã ããã質åãå«ãã³ã³ãã³ããããã²ãŒãã®äžå©ãšãªãã°å¹žãã§ãã
ããŒãžå 容
ClickHouse ã¯åžžã«æŽæ°ãããŠããŸãããããŒã¿ã¯æŽæ°ãããŠããŸãããããã«ã€ããŠã©ãããã°ããã§ããããïŒ ClickHouse ããããŒã¿ãããã¯ã¢ããããããã®çŸåšã®ãã¹ã ãã©ã¯ãã£ã¹ã¯äœã§ãã? ã·ã£ããå ã®ã¬ããªã«ã®é ããå¶åŸ¡ããããšã¯å¯èœã§ãããã? ããŒãã«æ§é ãå€æŽãããå Žåã¯ã©ããªãã§ãããã? ããŒã¿ã®åã·ã£ãŒãã£ã³ã°ã«ãããçŸåšã®ãã¹ã ãã©ã¯ãã£ã¹ã¯äœã§ãã? ClickHouse ã«ã¯ãã¯ãªãã¯ããŠã¹ ã³ã㌠ãŠãŒãã£ãªãã£ããããŸãã圌女ã«ã€ããŠæããŠããã ããŸããïŒ ãªã·ã£ãŒãã£ã³ã°ãšåŒã°ãããã€ãããçãªãã®ããããŸããã圌女ã¯ã©ãã§ããïŒ äœéãã£ã¹ã¯ã«ç§»åããåã«ããã¹ãŠã®ããŒã¿ãçµåããããšã¯ã§ããŸãã? äºåã«äºææ§ã確èªããæ¹æ³ããªãå ŽåãClickHouse ã®æ°ããããŒãžã§ã³ã«ç§»è¡ããã«ã¯ã©ãããã°ããã§ãã? Kill ã¯ãšãªã¯ã¯ãšãªã匷å¶çµäºããããšã«ãªã£ãŠããŸãããããã§ã¯ãããŸããããªãïŒ èªã¿åãè² è·æã®å¿çæéãèšç®ããã«ã¯ã©ãããã°ããã§ãã? ããå€ãã®ããŒã¿ããã£ãã·ã¥ã«å ¥ããã«ã¯ãClickHouse ã§äœã調æŽã§ããŸãã? RAM å ã®ã¹ãã¬ãŒãžçšã« storage_configuration ãæ§æããã«ã¯ã©ãããã°ããã§ãã? Low Cardinality ã¯äžæã®å€ã®æ°ãŸã§æå¹ã§ãã? 50 åè¡ã®ããŒãã«ãå šææ€çŽ¢ããããã®ãã¹ã ãã©ã¯ãã£ã¹ã¯äœã§ãã? å€æ°ã®ãŠãŒã¶ãŒã® ClickHouse ãžã®ã¢ã¯ã»ã¹ãæŽçããæåã®æ¹æ³ã¯äœã§ãã? 1 ã€ã®ã¯ãšãªã®çµæã 10 人ã®ã¯ã©ã€ã¢ã³ãã«æäŸããããšã¯å¯èœã§ãã? éåææäœããããªã¢ã©ã€ãºã ãã¥ãŒã«ã€ããŠã¯ã©ãã§ãã? ClickHouseã«ã¯ããããã®ãã°ããããŸããçŸåšãµãŒããŒã§èµ·ãã£ãŠããããšããã¹ãŠç¢ºèªããã«ã¯ã©ãããã°ããã§ãã? ãµãŒããŒã OOM ã«ã¯ã©ãã·ã¥ããªãããã«ããŒãžã«åœ±é¿ãäžããã«ã¯ã©ãããã°ããã§ãã? ClickHouse çšã® Golang ãã©ã€ããŒã¯ã©ã®ããã«éçºãããŸãã? Lazy_load èšå®ãæå¹ã«ããŠåèµ·åãããšãå€éšèŸæžãããŒããããŸãããäœããããïŒ å°ãªããšã 1 ã€ã®èŸæžããšã©ãŒã§ã¯ã©ãã·ã¥ããå Žåãã·ã¹ãã ã®ãªããŒãèŸæžãã©ããèªã¿èŸŒãŸãªããšããäºå®ãã©ãããã°ããã§ãããã? ClickHouse æ§æã§è©³çŽ°ãæ§æãããããšã©ãŒãçºçããå Žåã«ã¯è¡šç€ºããªãæ¹æ³ã¯ãããŸãã? ããŒãã¹: éäŒããã® Zoom ã®èæ¯
ããã¹ããèªã¿ãããªãå Žåã¯ãéäŒã®é²ç»ãèŠãããšãã§ããŸã
ç§ãã¡ã® YouTube ãã£ã³ãã«ã§ ãã¿ã€ã ã³ãŒãã¯ãããªã®äžã®æåã®ã³ã¡ã³ãã«ãããŸãã
ClickHouse ã¯åžžã«æŽæ°ãããŠããŸãããããŒã¿ã¯æŽæ°ãããŠããŸãããããã«ã€ããŠã©ãããã°ããã§ããããïŒ
ClickHouse ã¯åžžã«æŽæ°ãããŠãããæçµçã«æé©åãããããŒã¿ã¯æŽæ°ããããããã¯ã¢ãã ã³ããŒã«ä¿åãããŠããŸãã
äœããã®åé¡ãçºçããããŒã¿ã倱ããããšããŸãã埩å ããããšã«ããŸããããããã¯ã¢ãã ãµãŒããŒã«ä¿åãããŠããå€ãããŒãã£ã·ã§ã³ã¯ãçŸåšäœ¿çšãããŠããããŒãžã§ã³ã® ClickHouse ãšã¯å€§ããç°ãªãããšãå€æããŸããããã®ãããªç¶æ³ã§ã¯ã©ãããã°ããã§ããããããŸãããã¯å¯èœã§ãããã?
å€ã圢åŒã®ããã¯ã¢ããããããŒã¿ã埩å ããŠããæ°ããããŒãžã§ã³ã«æ¥ç¶ã§ããªããšããç¶æ³ã¯ããåŸãŸããã ClickHouse ã®ããŒã¿åœ¢åŒã«ã¯åžžã«äžäœäºææ§ãä¿ãããããã«ããŠããŸãããã£ãã«äœ¿çšãããªãé¢æ°ã®åäœãå€æŽãããå Žåãããã¯æ©èœã®äžäœäºææ§ãããã¯ããã«éèŠã§ãã ClickHouse ã®æ°ããããŒãžã§ã³ã¯ããã£ã¹ã¯ã«ä¿åãããŠããããŒã¿ãåžžã«èªã¿åãããšãã§ããå¿ èŠããããŸãããããæ³åŸã§ãã
ClickHouse ããããŒã¿ãããã¯ã¢ããããããã®çŸåšã®ãã¹ã ãã©ã¯ãã£ã¹ã¯äœã§ãã?
æçµçãªæäœããã©ãã€ãèŠæš¡ã®å·šå€§ãªããŒã¿ããŒã¹ãããšãã°éå» 3 æ¥éã«æŽæ°ãããããŒã¿ãæé©åããåŸãäœãæé ãå®è¡ãããªãããšãèæ ®ããŠãããã¯ã¢ãããäœæããã«ã¯ã©ãããã°ããã§ãããã?
ç¬èªã®ãœãªã¥ãŒã·ã§ã³ãäœæããŠãbash äžã«æžã蟌ãããšãã§ããŸãããããã®ããã¯ã¢ãã ã³ããŒãããŸããŸãªæ¹æ³ã§åéããŸãããããããäœãæŸèæãæ¡ãå¿ èŠã¯ãªããèªè»¢è»ã¯ãã£ãšæã«çºæãããã®ã§ããããïŒ
ãã¹ããã©ã¯ãã£ã¹ããå§ããŸããããç§ã®ååã¯ãããã¯ã¢ããã«é¢ãã質åã«å¯ŸããŠããã®åé¡ã¯ãã§ã«è§£æ±ºãããŠãã Yandex.Cloud ãµãŒãã¹ã«ã€ããŠæãåºãããããã«åžžã«ã¢ããã€ã¹ããŠããŸãããããã£ãŠãå¯èœã§ããã°ããã䜿çšããŠãã ããã
ããã¯ã¢ããã®ããã®å®å šãªãœãªã¥ãŒã·ã§ã³ã¯ãããŸããã100% ã ClickHouse ã«çµã¿èŸŒãŸããŠããŸãã䜿ãããã©ã³ã¯ãããããŸããå®å šãªãœãªã¥ãŒã·ã§ã³ãåŸãã«ã¯ãæåã§å°ããããããã¹ã¯ãªããã®åœ¢åŒã§ã©ãããŒãäœæããå¿ èŠããããŸãã
ããŒã¿ã®éãšã¯ã©ã¹ã¿ãŒã®ãµã€ãºã«å¿ããŠãæãåçŽãªãœãªã¥ãŒã·ã§ã³ããå§ããŠãæãæŽç·Žããããœãªã¥ãŒã·ã§ã³ã§çµãããŸããã¯ã©ã¹ã¿ãŒã倧ãããªãã»ã©ããœãªã¥ãŒã·ã§ã³ã¯ããè€éã«ãªããŸãã
ããŒã¿ãå«ãããŒãã«ãæ° GB ããå æããŠããªãå Žåã¯ã次ã®ããã«ããã¯ã¢ãããå®è¡ã§ããŸãã
- ããŒãã«å®çŸ©ãã€ãŸãã¡ã¿ããŒã¿ãä¿åããŸã- ããŒãã«ã®äœæã衚瀺.
- ClickHouse ã¯ã©ã€ã¢ã³ãã䜿çšããŠãã³ããäœæããŸã - select * ããŒãã«ãã ãã¡ã€ã«ãžãããã©ã«ãã§ã¯ãTabSeparated 圢åŒã§ãã¡ã€ã«ãåãåããŸããããå¹ççã«ãããå Žåã¯ããã€ãã£ã圢åŒã§å®è¡ã§ããŸãã
ããŒã¿ã®éãå€ãå Žåãããã¯ã¢ããã«ã¯ããå€ãã®æéãšå€ãã®ã¹ããŒã¹ãå¿ èŠã«ãªããŸããããã¯è«çããã¯ã¢ãããšåŒã°ããŸãããClickHouse ããŒã¿åœ¢åŒã«ã¯é¢é£ä»ããããŠããŸããããã®å Žåã¯ãæåŸã®æ段ãšããŠããã¯ã¢ãããäœæããããã MySQL ã«ã¢ããããŒãããŠãªã«ããªããããšãã§ããŸãã
ããé«åºŠãªã±ãŒã¹ã§ã¯ãClickHouse ã«ã¯ããŒã«ã« ãã¡ã€ã« ã·ã¹ãã ã«ããŒãã£ã·ã§ã³ã®ã¹ãããã·ã§ãããäœæããæ©èœãçµã¿èŸŒãŸããŠããŸãããã®æ©èœã¯ãªã¯ãšã¹ããšããŠå©çšã§ããŸã ããŒãã«ã®ããªãŒãºããŒãã£ã·ã§ã³ãå€æŽããããããã¯åã« ããŒãã«ã®ããªãŒãºãå€æŽãã - ããã¯ããŒãã«å šäœã®ã¹ãããã·ã§ããã§ãã
ã¹ãããã·ã§ãã㯠1 ã€ã®ã·ã£ãŒãäžã® 1 ã€ã®ããŒãã«ã«å¯ŸããŠäžè²«ããŠäœæãããŸããã€ãŸãããã®æ¹æ³ã§ã¯ã©ã¹ã¿ãŒå šäœã®äžè²«ããã¹ãããã·ã§ãããäœæããããšã¯äžå¯èœã§ããããããã»ãšãã©ã®ã¿ã¹ã¯ã§ã¯ãã®ãããªå¿ èŠã¯ãªããåã·ã£ãŒãã§ãªã¯ãšã¹ããå®è¡ããŠäžè²«æ§ã®ããã¹ãããã·ã§ãããååŸããã ãã§ååã§ããããã¯ããŒããªã³ã¯ã®åœ¢åŒã§äœæããããããè¿œå ã®ã¹ããŒã¹ãå æããŸããã次ã«ããã®ã¹ãããã·ã§ãããããã¯ã¢ãã ãµãŒããŒãŸãã¯ããã¯ã¢ããã«äœ¿çšããã¹ãã¬ãŒãžã«ã³ããŒããŸãã
ãã®ãããªããã¯ã¢ããã®åŸ©å ã¯éåžžã«ç°¡åã§ãããŸããæ¢åã®ããŒãã«å®çŸ©ã䜿çšããŠããŒãã«ãäœæããŸãã次ã«ãä¿åãããŠããããŒãã£ã·ã§ã³ã®ã¹ãããã·ã§ããããããã®ããŒãã«ã® Directory-Detached ã«ã³ããŒããã¯ãšãªãå®è¡ããŸãã ããŒãã£ã·ã§ã³ãæ¥ç¶ããããã®ãœãªã¥ãŒã·ã§ã³ã¯ãæã倧éã®ããŒã¿ã«éåžžã«é©ããŠããŸãã
åãµãŒããŒã«æ°åãããã«ã¯æ°çŸãã©ãã€ããããã«ã¯æ°çŸå°ã®ãµãŒããŒãããå Žåãããã«åªãããã®ãå¿ èŠã«ãªãå ŽåããããŸããããã«ã¯ãYandex.Metrica ã®ååããæŸã£ã解決çããããŸãããã¹ãŠã®äººã«ã¯ãå§ãããŸãããèªãã§ããããé©åãã©ãããèªåã§å€æããŠãã ããã
ãŸãã倧èŠæš¡ãªãã£ã¹ã¯ ã·ã§ã«ããåãããµãŒããŒãè€æ°äœæããå¿ èŠããããŸãã次ã«ããããã®ãµãŒããŒäžã§è€æ°ã® ClickHouse ãµãŒããŒãèµ·åããåãã·ã£ãŒãã®å¥ã®ã¬ããªã«ãšããŠæ©èœããããã«æ§æããŸãã次ã«ããããã®ãµãŒããŒäžã§ã¹ãããã·ã§ãããäœæã§ãããã¡ã€ã« ã·ã¹ãã ãŸãã¯ããŒã«ã䜿çšããŸããããã«ã¯ 2 ã€ã®ãªãã·ã§ã³ããããŸããæåã®ãªãã·ã§ã³ã¯ LVM ã¹ãããã·ã§ããã2 çªç®ã®ãªãã·ã§ã³ã¯ Linux äžã® ZFS ã§ãã
ãã®åŸãæ¯æ¥ã¹ãããã·ã§ãããäœæããå¿ èŠããããããã¹ãããã·ã§ããã暪ããã£ãŠã¹ããŒã¹ãå æããããšã«ãªããŸããåœç¶ã®ããšãªãããããŒã¿ãå€æŽããããšãæéã®çµéãšãšãã«ã¹ããŒã¹ã®éãå¢å ããŸãããã®ã¹ãããã·ã§ããã¯ãã€ã§ãåãåºããŠããŒã¿ã埩å ã§ãããšããããªããšãäžæè°ãªè§£æ±ºçã§ããããã«ããããã®ã¬ããªã«ããªãŒããŒã«ãªãããšããªãããã«ãæ§æå ã§ãããã®ã¬ããªã«ãå¶éããå¿ èŠããããŸãã
ã·ã£ããå
ã®ã¬ããªã«ã®é
ããå¶åŸ¡ããããšã¯å¯èœã§ãããã?
ä»å¹Žã¯ClickHouseã§ã·ã£ãããäœãäºå®ã§ããããããã®ã¬ããªã«ã®å¶åŸ¡ãããã©ã°ãçµç¹åããããšã¯å¯èœã§ãããã?å€æŽããã®ä»ã®å€æŽã«ãããã¬ãã£ããªã·ããªãªãã身ãå®ãããã«ããã䜿çšããããšèããŠããŸãã
å€æŽã«å¯ŸããŠäœããã®ããŒã«ããã¯ãè¡ãããšã¯å¯èœã§ãã?ããšãã°ãæ¢åã®ã·ã£ããã§ããã®ç¬éãŸã§ã¯å€æŽãé©çšãããã®ç¬éããå€æŽã®é©çšãåæ¢ãããšããŸãã?
ã³ãã³ããã¯ã©ã¹ã¿ãŒã«æ¥ãŠãããç Žå£ããå Žåã1 æéã®ã©ã°ãããæ¡ä»¶ä»ãã¬ããªã«ãã§ããŸããçŸæç¹ã§ã¯ããã䜿çšããŸãããããããæåŸã® 10 åéã¯ããã«å€æŽãé©çšããªããšèšããŸãã?
ãŸããã¬ããªã«ã®å¶åŸ¡ãããã©ã°ã«ã€ããŠã§ãããŠãŒã¶ãŒãããããªèŠæããã£ãã®ã§ãããããå¿ èŠãªäººãããããããããããŒããå ¥ããŠãã ããããšãããªã¯ãšã¹ããGithubã«IssueãšããŠäœæããŸããã誰ãé ä¿¡ãããåé¡ã¯çµäºããŸããããã ããClickHouse ãã»ããã¢ããããããšã§ããã®æ©äŒããã§ã«åŸãããšãã§ããŸãã確ãã«ãããŒãžã§ã³ 20.3 以éã®ã¿ã§ãã
ClickHouse ã¯ããã¯ã°ã©ãŠã³ãã§ããŒã¿ã®çµåãåžžã«å®è¡ããŸããããŒãžãå®äºãããšãç¹å®ã®ããŒã¿ã®ã»ããããã倧ããªããŒã¿ã®ã»ããã«çœ®ãæããããŸããåæã«ã以åã«ååšããŠããããŒã¿ã¯ãã°ãããã£ã¹ã¯äžã«æ®ãç¶ããŸãã
ãŸãããã³ããããã³ã°æäœãæäŸããããã«ããããã䜿çšããéžæã¯ãšãªãååšããéãããããã¯ä¿åããç¶ããŸããéžæã¯ãšãªã¯å€ããã£ã³ã¯ããç°¡åã«èªã¿åãããšãã§ããŸãã
次ã«ãæéã®ãããå€ããããŸããå€ãããŒã¿ã¯ 8 åéãã£ã¹ã¯äžã«æ®ããŸãããã® 8 åãã«ã¹ã¿ãã€ãºã㊠1 æ¥ã«å€ããããšãã§ããŸããããã«ã¯ãã£ã¹ã¯å®¹éãããããŸããããŒã¿ ãããŒã«ãã£ãŠã¯ãæçµæ¥ã®ããŒã¿ã¯ 2 åã©ããã 5 åã«ãªãå¯èœæ§ãããããšãããããŸãããã ããé倧ãªåé¡ãçºçããå Žåã¯ãClickHouse ãµãŒããŒãåæ¢ããŠãã¹ãŠã解決ã§ããŸãã
ããã§ããããå€æŽããã©ã®ããã«ä¿è·ãããããšããçåãçããŸãã ClickHouse ã®å€ãããŒãžã§ã³ã§ã¯ãå€æŽã¯éšåãçŽæ¥å€æŽããã ãã®æ¹æ³ã§æ©èœãããããããã§è©³ããèŠãŠã¿ã䟡å€ããããŸããããã€ãã®ãã¡ã€ã«ãå«ãããŒã¿ããããããšãã°æ¬¡ã®ããã«ããŸãã ããããåãå€æŽããããã®åŸããã®åã¯ãã¹ãŠã®ãã£ã³ã¯ããç©ççã«åé€ãããŸãã
ããããããŒãžã§ã³ 20.3 以éãå€æŽã¡ã«ããºã ãå®å šã«å€æŽãããããŒã¿ã¯åžžã«äžå€ã«ãªããŸããããããã¯ãŸã£ããå€æŽãããŸãããå€æŽã¯ããŒãžãšã»ãŒåãããã«æ©èœããããã«ãªããŸããããã®å Žã§éšåã亀æããã®ã§ã¯ãªããæ°ãããã®ãäœæããŸããæ°ãããã£ã³ã¯ã§ã¯ãå€æŽãããŠããªããã¡ã€ã«ã¯ããŒããªã³ã¯ã«ãªããåãåé€ãããšããã®åã¯æ°ãããã£ã³ã¯ã§æ¬ èœããã ãã«ãªããŸããå€ãéšåã¯ããã©ã«ã㧠XNUMX ååŸã«åé€ãããŸããããã§äžèšã®èšå®ã埮調æŽã§ããŸãã
çªç¶å€ç°ãªã©ã®å€æŽã«ã€ããŠãåæ§ã§ãããããããšã å€æŽåé€ ãŸã㯠æŽæ°ãå€æŽãããããŒã¹ãå€æŽããã®ã§ã¯ãªããæ°ããããŒã¹ãäœæããŸãããããŠãå€ããã®ãåé€ããŸãã
ããŒãã«æ§é ãå€æŽãããå Žåã¯ã©ããªãã§ãããã?
å€ãã¹ããŒã ã§äœæãããããã¯ã¢ããã埩å ããã«ã¯ã©ãããã°ããã§ãã? 2 çªç®ã®è³ªåã¯ãã¹ãããã·ã§ãããšãã¡ã€ã« ã·ã¹ãã ããŒã«ã®å Žåã«ã€ããŠã§ããããã§ã¯ãLinux LVM äžã® ZFS ã§ã¯ãªã Btrfs ãé©ããŠããŸãã?
ããããããªã ããŒãã£ã·ã§ã³ãæ¥ç¶ãã ç°ãªãæ§é ã®ããŒãã£ã·ã§ã³ã䜿çšãããšãClickHouse ã¯ãããäžå¯èœã§ããããšãéç¥ããŸããããã解決çã§ãã 1 ã€ç®ã¯ãå€ãæ§é 㧠MergeTree ã¿ã€ãã®äžæããŒãã«ãäœæããããã« Attach ã䜿çšããŠããŒã¿ãæ·»ä»ããå€æŽã¯ãšãªãäœæããããšã§ãããã®åŸããã®ããŒã¿ãã³ããŒãŸãã¯è»¢éããŠå床添ä»ãããããªã¯ãšã¹ãã䜿çšããããšãã§ããŸãã ããŒãã«ã®å€æŽ ããŒãã£ã·ã§ã³ã®ç§»å.
4 çªç®ã®è³ªåã¯ãBtrfs ã䜿çšã§ãããã©ããã§ãããŸããLVM ãããå Žåã¯ãLVM ã¹ãããã·ã§ããã§ååã§ããããã¡ã€ã« ã·ã¹ãã 㯠extXNUMX ã§ãã£ãŠãåé¡ãããŸããã Btrts ã§ã¯ããã¹ãŠã䜿çšçµéšã«äŸåããŸããããã¯æçãããã¡ã€ã« ã·ã¹ãã ã§ãããç¹å®ã®ã·ããªãªã§å®éã«ãã¹ãŠãã©ã®ããã«æ©èœãããã«ã€ããŠã¯ããŸã çåããããŸããéçšç°å¢ã« Btrfs ããªãéããããã䜿çšããããšã¯ãå§ãããŸããã
ããŒã¿ã®åã·ã£ãŒãã£ã³ã°ã«ãããçŸåšã®ãã¹ã ãã©ã¯ãã£ã¹ã¯äœã§ãã?
ãªã·ã£ãŒãã£ã³ã°ã®åé¡ã¯è€éãã€å€é¢çã§ããããã§èããããçãã¯ããã€ããããŸããäžæ¹ã®åŽé¢ããèšãã°ãClickHouse ã«ã¯ãªã·ã£ãŒãã£ã³ã°æ©èœãçµã¿èŸŒãŸããŠããŸããããããããã®çãã¯èª°ã«ãåœãŠã¯ãŸããªããšæããŸãããããã£ãŠãè£ãè¿ãã°ãClickHouse ã«ã¯ããŒã¿ããªã·ã£ãŒãã£ã³ã°ããããŸããŸãªæ¹æ³ããããšèšããŸãã
ã¯ã©ã¹ã¿ãŒã®ã¹ããŒã¹ãäžè¶³ããŠããå ŽåããŸãã¯ã¯ã©ã¹ã¿ãŒãè² è·ãåŠçã§ããªãå Žåã¯ãæ°ãããµãŒããŒãè¿œå ããŸãããããããããã®ãµãŒããŒã¯ããã©ã«ãã§ã¯ç©ºã§ããããµãŒããŒäžã«ã¯ããŒã¿ããªããè² è·ããããŸãããæ°ãã倧ããªã¯ã©ã¹ã¿ãŒå šäœã«ããŒã¿ãåçã«åæ£ãããããã«ãããŒã¿ãåé 眮ããå¿ èŠããããŸãã
ãããè¡ãæåã®æ¹æ³ã¯ããªã¯ãšã¹ãã䜿çšããŠããŒãã£ã·ã§ã³ã®äžéšãæ°ãããµãŒããŒã«ã³ããŒããããšã§ãã ããŒãã«ãã§ããããŒãã£ã·ã§ã³ã®å€æŽãããšãã°ãæããšã«ããŒãã£ã·ã§ã³ãããã2017 幎ã®æåã®æãæ°ãããµãŒããŒã«ã³ããŒããXNUMX çªç®ã®æãå¥ã®æ°ãããµãŒããŒã«ã³ããŒãããšããŸãããããŠãã»ãŒåäžã«ãªããŸã§ãããç¹°ãè¿ããŸãã
転éã¯ãèšé²äžã«å€æŽãããªãããŒãã£ã·ã§ã³ã«å¯ŸããŠã®ã¿å®è¡ã§ããŸããæ°ããããŒãã£ã·ã§ã³ã®å Žåã転éã¯ã¢ãããã¯ã§ã¯ãªããããèšé²ãç¡å¹ã«ããå¿ èŠããããŸããããããªããšãããŒã¿ã«éè€ãã®ã£ãããçããŠããŸããŸãããã ãããã®æ¹æ³ã¯å®çšçã§ãããéåžžã«å¹æçã§ããæ¢è£œã®å§çž®ããŒãã£ã·ã§ã³ã¯ãããã¯ãŒã¯çµç±ã§éä¿¡ãããŸããã€ãŸããããŒã¿ã¯å§çž®ãŸãã¯åãšã³ã³ãŒããããŸããã
ãã®æ¹æ³ã«ã¯ 1 ã€ã®æ¬ ç¹ããããããã¯ã·ã£ãŒãã£ã³ã° ã¹ããŒã ããã®ã·ã£ãŒãã£ã³ã° ã¹ããŒã ã«èªçŽãããã©ãããã©ã®ãããªã·ã£ãŒãã£ã³ã° ããŒãæã£ãŠãããã«ãã£ãŠç°ãªããŸããã¡ããªã¯ã¹ã®äŸã§ã¯ãã·ã£ãŒãã£ã³ã° ããŒã¯ãã¹ã®ããã·ã¥ã§ããåæ£ããŒãã«ãéžæãããšãã¯ã©ã¹ã¿ãŒå ã®ãã¹ãŠã®ã·ã£ãŒãã«äžåºŠã«ç§»åããããããããŒã¿ãååŸããŸãã
ããã¯ãã©ã®ããŒã¿ãã©ã®ã·ã£ãŒãã«é 眮ããããã¯å®éã«ã¯éèŠã§ã¯ãªãããšãæå³ããŸããéèŠãªããšã¯ã1 ã€ã®ãã¹ã«æ²¿ã£ãããŒã¿ã¯æçµçã« 1 ã€ã®ã·ã£ãŒãã«é 眮ããããšããããšã§ãããã©ã®ã·ã£ãŒããã©ãã§ãããã¯éèŠã§ã¯ãããŸããããã®å Žåãéžæã¯ãšãªã䜿çšãããšå®å šãªããŒã¿ãåä¿¡ã§ãããããæ¢è£œã®ããŒãã£ã·ã§ã³ã®è»¢éã¯å®ç§ã§ãããªã·ã£ãŒãã£ã³ã°ã®åãåŸãã«é¢ä¿ãªããã¹ããŒã ã¯ããŸãéèŠã§ã¯ãããŸããã
ããããããè€éãªã±ãŒã¹ããããŸããã¢ããªã±ãŒã·ã§ã³ ããžã㯠ã¬ãã«ã§ç¹å¥ãªã·ã£ãŒãã£ã³ã° ã¹ããŒã ã«äŸåããŠããå Žåããã®ã¯ã©ã€ã¢ã³ãã¯ããããã®ã·ã£ãŒãã«é 眮ãããŠããããªã¯ãšã¹ãã¯åæ£ããŒãã«ã§ã¯ãªãããã«çŽæ¥éä¿¡ã§ããŸãããŸãã¯ãããªãæ°ããããŒãžã§ã³ã® ClickHouse ã䜿çšããŠãããèšå®ãæå¹ã«ããŠããŸãã æªäœ¿çšã®ã·ã£ãŒããã¹ãããããŠæé©åããããã®å Žåãéžæã¯ãšãªäžã«ãwhere ã»ã¯ã·ã§ã³ã®åŒãåæãããã·ã£ãŒãã£ã³ã° ã¹ããŒã ã«åŸã£ãŠã©ã®ã·ã£ãŒãã䜿çšããå¿ èŠãããããèšç®ãããŸããããã¯ãããŒã¿ããã®ã·ã£ãŒãã£ã³ã° ã¹ããŒã ã«åŸã£ãŠæ£ç¢ºã«åå²ãããŠããå Žåã«æ©èœããŸããæåã§äžŠã¹æ¿ããå Žåã¯å¯Ÿå¿ãå€ããå¯èœæ§ããããŸãã
ããããã® 1 çªç®ã®æ¹æ³ã§ãããããŠããã®æ¹æ³ãé©åãããããšã次ã«é²ã¿ãŸãããããããªãã®çããåŸ ã£ãŠããŸãã
Avito ã®äž»ä»»ã·ã¹ãã 管çè ãVladimir Kolobaev æ°: ã¢ã¬ã¯ã»ã€ãããªããèšåããæ¹æ³ã¯ãèªæžãªã©ã®è² è·ãåæ£ããå¿ èŠãããå Žåã«ã¯ããŸãæ©èœããŸãããæããšã®ããŒãã£ã·ã§ã³ãååŸããããåæãå¥ã®ããŒãã«ååŸãããããããšãã§ããŸããããã®ããŒã¿ã«å¯Ÿãããªã¯ãšã¹ããæ¥ãå Žåã«ã¯ããã®ããŒã¿ãããŒãããã ãã§ãããã ããã¯ã©ã¹ã¿ãŒå šäœãããŒãããããšèããŠããŸããããããªããšããã°ããã®éãèªã¿åãããŒãå šäœã 2 ã€ã®ã·ã£ãŒãã§åŠçãããããšã«ãªãããã§ãã
ã¢ã¬ã¯ã»ã€ã»ãããŽã£ãã: ããã§ã®çãã¯å¥åŠã§ããã¯ããããã¯æªãã§ãããããŸãããå¯èœæ§ããããŸããå ·äœçã«ãã®æ¹æ³ã説æããŸããããŒã¿ã®èåŸã«ããè² è·ã·ããªãªã«æ³šç®ãã䟡å€ããããŸãããããããŒã¿ã®ç£èŠã§ããå Žåããªã¯ãšã¹ãã®å€§éšåã¯æ°ããããŒã¿ã«å¯Ÿãããã®ã§ãããšã»ãŒç¢ºå®ã«èšããŸãã
æ°ãããµãŒããŒãã€ã³ã¹ããŒã«ããå€ãããŒãã£ã·ã§ã³ã移è¡ããŸããããæ°ããããŒã¿ã®èšé²æ¹æ³ãå€æŽããŸããããããŠãæ°ããããŒã¿ãã¯ã©ã¹ã¿ãŒå šäœã«åæ£ãããŸãããããã£ãŠãããã 5 ååŸãæåŸã® 5 åéã®ãªã¯ãšã¹ãã¯ã¯ã©ã¹ã¿ãŒã«åçã«ããŒãããã1 æ¥åŸã24 æéã®ãªã¯ãšã¹ãã¯ã¯ã©ã¹ã¿ãŒã«åçã«ããŒããããŸãããããŠãæ®å¿µãªãããåæã®ãªã¯ãšã¹ãã¯äžéšã®ã¯ã©ã¹ã¿ãŒ ãµãŒããŒã«ã®ã¿éä¿¡ãããŸãã
ãã ãã2019 幎 2019 æã«ç¹åãããªã¯ãšã¹ãããªãå ŽåããããããŸããæãå¯èœæ§ãé«ãã®ã¯ããªã¯ãšã¹ãã 2019 幎ã«å ¥ã£ãå Žåããã®ãªã¯ãšã¹ã㯠XNUMX å¹Žå šäœãã€ãŸãçãç¯å²ã§ã¯ãªããé·æéã«ãããããšã«ãªãã§ãããããŸãããã®ãããªãªã¯ãšã¹ãã§ãã¯ã©ã¹ã¿ãŒã«åçã«ããŒãã§ããããã«ãªããŸããããããäžè¬çã«ãããã¯ããŒã¿ãå®å šã«åçã«åæ£ããªãã¢ãããã¯ãªãœãªã¥ãŒã·ã§ã³ã§ãããšããããªãã®ææã¯å®å šã«æ£ããã§ãã
質åã«çããã«ã¯ããå°ããã€ã³ãããããŸãããã® 1 ã€ã¯ãåã·ã£ãŒãã£ã³ã°ã«ããåé¡ã軜æžãããããã«æåã«ã·ã£ãŒãã£ã³ã° ã¹ããŒã ãèšèšããæ¹æ³ã«é¢ãããã®ã§ããããã¯åžžã«å¯èœãªããã§ã¯ãããŸããã
ããšãã°ãç£èŠããŒã¿ããããšããŸããã¢ãã¿ãªã³ã° ããŒã¿ãå¢å ããŠããçç±ã¯ 3 ã€ãããŸãã 1ã€ç®ã¯éå»ã®ããŒã¿ã®èç©ã§ãã 2 ã€ç®ã¯ãã©ãã£ãã¯ã®å¢å ã§ãããããŠïŒã€ç®ã¯ãç£èŠå¯Ÿè±¡ã®å¢å ã§ããä¿åããå¿ èŠãããæ°ãããã€ã¯ããµãŒãã¹ãšã¡ããªã¯ã¹ããããŸãã
ãã®ãã¡ãæ倧ã®å¢å 㯠3 çªç®ã®çç±ã§ããã¢ãã¿ãªã³ã°ã®äœ¿çšã®å¢å ã«é¢é£ããŠããå¯èœæ§ããããŸãããã®å Žåãè² è·ã®æ§è³ªãäž»ãªéžæã¯ãšãªãäœã§ããããæ€èšãã䟡å€ããããŸããåºæ¬çãªéžæã¯ãšãªã¯ãã¡ããªã¯ã¹ã®äžéšã®ãµãã»ããã«åºã¥ããŠããå¯èœæ§ãé«ããªããŸãã
ããšãã°ãäžéšã®ãµãŒãã¹ã«ããäžéšã®ãµãŒããŒã® CPU 䜿çšçãªã©ã§ãããã®ããŒã¿ãååŸããããŒã®ç¹å®ã®ãµãã»ãããååšããããšãããããŸããããããŠããã®ããŒã¿ã®ãªã¯ãšã¹ãèªäœã¯ããããéåžžã«åçŽã§ãæ°åããªç§ã§å®äºããŸãããµãŒãã¹ãšããã·ã¥ããŒãã®ç£èŠã«äœ¿çšãããŸãããããæ£ããç解ã§ããã°å¹žãã§ãã
ãŠã©ãžããŒã«ã»ã³ãããšã: å®éã®ãšãããç§ãã¡ã¯çŸåšã®ç¶æ³ãšéå»ã®ç¶æ³ããªã¢ã«ã¿ã€ã ã§æ¯èŒãããããéå»ã®ããŒã¿ãéèŠããããšãéåžžã«å€ãã§ãããããŠã倧éã®ããŒã¿ã«è¿ éã«ã¢ã¯ã»ã¹ã§ããããšã¯ç§ãã¡ã«ãšã£ãŠéèŠã§ãããClickHouse ã¯ããã«é¢ããŠåªããä»äºãããŸãã
ãŸã£ãããã®éãã§ããä»ã®ç£èŠã·ã¹ãã ãšåæ§ã«ãèªã¿åããªã¯ãšã¹ãã®ã»ãšãã©ã¯éå» 1 æ¥ã«çºçããŸãããããåæã«ãå±¥æŽããŒã¿ã®è² è·ãéåžžã«å€§ãããªããŸããããã¯åºæ¬çã«ã30 ç§ããšã«å·¡åããClickHouse ã«æ¬¡ã®ããã«èšãèŠåã·ã¹ãã ããã®ãã®ã§ãããéå» 6 é±éã®ããŒã¿ããã ããã次ã«ããããããããçš®ã®ç§»åå¹³åãäœæããçŸåšã®å€ãéå»ã®å€ãšæ¯èŒããŸããããã
ãã®ãããªããæè¿ã®ãªã¯ãšã¹ãã«å¯ŸããŠã¯ã2 æ¥åã®ããŒã¿ã®ã¿ãä¿åããå¥ã®å°ããªããŒãã«ããããäž»èŠãªãªã¯ãšã¹ãã¯ããã«é£ã³èŸŒããšèšãããã®ã§ãã倧èŠæš¡ãªå±¥æŽã¯ãšãªã®ã¿ã倧èŠæš¡ãªã·ã£ãŒã ããŒãã«ã«éä¿¡ããŸãã
ã¢ã¬ã¯ã»ã€ã»ãããŽã£ãã: æ®å¿µãªãããããã¯ããªãã®ã·ããªãªã«ã¯ããŸãé©çšã§ããªãããšãå€æããŸãããã䜿çšããå¿ èŠã¯ãªããã®ã®ãç§ã®å人ã®ãµãŒãã¹ã§äœ¿çšãããŠãã 2 ã€ã®æªè³ªã§è€éãªã·ã£ãŒãã£ã³ã° ã¹ããŒã ã«ã€ããŠèª¬æããŸãã
Yandex.Metrica ã€ãã³ããå«ãã¡ã€ã³ ã¯ã©ã¹ã¿ãŒããããŸããã€ãã³ããšã¯ãããŒãžãã¥ãŒãã¯ãªãã¯ãã³ã³ããŒãžã§ã³ã§ããã»ãšãã©ã®ãªã¯ãšã¹ãã¯ç¹å®ã® Web ãµã€ãã«éä¿¡ãããŸãã Yandex.Metrica ãµãŒãã¹ãéããWeb ãµã€ã (avito.ru) ãéããã¬ããŒãã«ç§»åãããšãWeb ãµã€ãã«å¯Ÿãããªã¯ãšã¹ããè¡ãããŸãã
ããããå éšã¢ããªã¹ãã«ãã£ãŠè¡ãããåæçããã³ã°ããŒãã«ãªèŠæ±ã¯ä»ã«ããããŸãã念ã®ãããå éšã¢ããªã¹ã㯠Yandex ãµãŒãã¹ã«å¯ŸããŠã®ã¿ãªã¯ãšã¹ããè¡ãããšã«æ³šæããŠãã ãããããããããã«ãããããããYandex ãµãŒãã¹ã§ãããã¹ãŠã®ããŒã¿ã®ããªãã®ã·ã§ã¢ãå ããŠããŸãããããã¯ç¹å®ã®ã«ãŠã³ã¿ã«å¯Ÿãããªã¯ãšã¹ãã§ã¯ãªããããåºç¯ãªãã£ã«ã¿ãªã³ã°ã«å¯Ÿãããªã¯ãšã¹ãã§ãã
1 ã€ã®ã«ãŠã³ã¿ãŒãã°ããŒãã« ã¯ãšãªã«å¯ŸããŠãã¹ãŠãå¹ççã«æ©èœããããã«ããŒã¿ãæŽçããã«ã¯ã©ãããã°ããã§ãããã?ãã 1 ã€ã®åé¡ã¯ãClickHouse ã® Metrics ã¯ã©ã¹ã¿ãŒã«å¯Ÿãããªã¯ãšã¹ãã®æ°ã 1 ç§ãããæ°åã§ããããšã§ããåæã«ã1 ã€ã® ClickHouse ãµãŒããŒã§ã¯ãããšãã° 1 ç§ãããæ°å件ãªã©ã®éèŠãªãªã¯ãšã¹ããåŠçã§ããŸããã
ã¯ã©ã¹ã¿ãŒã®ãµã€ãºã¯ 600 å°ã®ãµãŒããŒã§ãããã®ã¯ã©ã¹ã¿ãŒäžã«åæ£ããŒãã«ãåçŽã«ãã«ããããã«æ°åã®ãªã¯ãšã¹ããéä¿¡ãããšã1 ã€ã®ãµãŒããŒã«ãªã¯ãšã¹ããéä¿¡ãããããããã«æªãããšã«ãªããŸããäžæ¹ãããŒã¿ãåçã«åæ£ãããã¹ãŠã®ãµãŒããŒã«ã¢ã¯ã»ã¹ããŠãªã¯ãšã¹ããããšãããªãã·ã§ã³ã¯ããã«åŽäžãããŸãã
æ£å察ã®éžæè¢ããããŸããããŒã¿ããµã€ãéã§ã·ã£ãŒãã£ã³ã°ãã1 ã€ã®ãµã€ãã«å¯Ÿãããªã¯ãšã¹ãã 1 ã€ã®ã·ã£ãŒãã«éä¿¡ããããšæ³åããŠãã ãããããã§ãã¯ã©ã¹ã¿ãŒã¯ 1 ç§ããã 1 äžä»¶ã®ãªã¯ãšã¹ããåŠçã§ããããã«ãªããŸããã1 ã€ã®ã·ã£ãŒãã§ã¯ 1 ã€ã®ãªã¯ãšã¹ãã®åäœãé ãããŸããã¹ã«ãŒãããã®ç¹ã§æ¡åŒµã§ããªããªããŸããããããµã€ãavito.ruã®å Žåã¯ç¹ã«ããã§ãã Avito ã RuNet ã§æãã¢ã¯ã»ã¹æ°ã®å€ããµã€ãã® 1 ã€ã§ãããšèšã£ãŠãããã®ç§å¯ã¯æãããŸãããããã 1 ã€ã®ã·ã£ãŒãã§åŠçããã®ã¯çæ°ã®æ²æ±°ã§ãããã
ãããã£ãŠãã·ã£ãŒãã£ã³ã° ã¹ããŒã ã¯ããå·§åŠãªæ¹æ³ã§èšèšãããŠããŸããã¯ã©ã¹ã¿ãŒå šäœã¯ãã¬ã€ã€ãŒãšåŒã°ããããã€ãã®ã¯ã©ã¹ã¿ãŒã«åå²ãããŸããåã¯ã©ã¹ã¿ãŒã«ã¯ãåæ°ããæ°åã®ã·ã£ãŒããå«ãŸããŠããŸãããã®ãããªã¯ã©ã¹ã¿ãŒã¯åèš 39 åãããŸãã
ããã¯ã©ã®ããã«ã¹ã±ãŒã«ãããã®ã§ãããã?ã¯ã©ã¹ã¿ãŒã®æ°ã¯å€åãããæ°å¹Žå㯠39 ã§ããããä»ããã®ãŸãŸã§ãããã ããããŒã¿ãèç©ãããã«ã€ããŠãããããã®ã·ã£ãŒãã®æ°ãåŸã ã«å¢å ããŸããå šäœçãªã·ã£ãŒãã£ã³ã° ã¹ããŒã ã¯æ¬¡ã®ããã«ãªããŸãããããã®ã¯ã©ã¹ã¿ãŒã¯ Web ãµã€ãã«åå²ãããŠãããã©ã® Web ãµã€ããã©ã®ã¯ã©ã¹ã¿ãŒã«ããããç解ããããã«ãMySQL ã®å¥ã®ã¡ã¿ããŒã¹ã䜿çšãããŸãã 1 ã€ã®ãµã€ã - 1 ã€ã®ã¯ã©ã¹ã¿ãŒäžããããŠãã®å éšã§ã¯ã蚪åè ID ã«åŸã£ãŠã·ã£ãŒãã£ã³ã°ãçºçããŸãã
èšé²ããéã¯ã蚪åè ID ã®é€ç®ã®äœãã§é€ç®ããŸãããã ããæ°ããã·ã£ãŒããè¿œå ãããšãã·ã£ãŒãã£ã³ã° ã¹ããŒã ãå€æŽãããåå²ã¯ç¶è¡ãããŸãããé€ç®ã®äœãã¯å¥ã®æ°å€ã§è¡ãããŸããããã¯ã1 人ã®èšªåè ããã§ã«è€æ°ã®ãµãŒããŒã«ååšããŠããããšãæå³ãããããä¿¡é Œããããšã¯ã§ããŸãããããã¯ãããŒã¿ã®å§çž®çãé«ããããã ãã«è¡ãããŸãããããŠããªã¯ãšã¹ããè¡ããšãã¯ãã¯ã©ã¹ã¿ãŒã調ã¹ãŠæ°åã®ãµãŒããŒã«ã¢ã¯ã»ã¹ããåæ£ããŒãã«ã«ç§»åããŸããããã¯ãšãŠãæããªèšç»ã§ãã
ããããç§ãã¡ããã®èšç»ãæŸæ£ãããšèšããªããã°ãç§ã®è©±ã¯äžå®å šã«ãªããŸããæ°ããã¹ããŒã ã§ã¯ããã¹ãŠãå€æŽããclickhouse-copier ã䜿çšããŠãã¹ãŠã®ããŒã¿ãã³ããŒããŸããã
æ°ããã¹ããŒã ã§ã¯ããã¹ãŠã®ãµã€ãã倧èŠæš¡ãšå°èŠæš¡ã® 120 ã€ã®ã«ããŽãªã«åé¡ãããŸãããããå€ãã©ã®ããã«éžæããããã¯ããããŸãããããã®çµæã倧èŠæš¡ãªãµã€ãã 360 ã€ã®ã¯ã©ã¹ã¿ãŒã«èšé²ããããããã 120 ã€ã®ã¬ããªã«ãæ〠120 åã®ã·ã£ãŒããã€ãŸã XNUMX å°ã®ãµãŒããŒãååšããŸãããŸããã·ã£ãŒãã£ã³ã° ã¹ããŒã ã¯ããããããªã¯ãšã¹ããäžåºŠã«ãã¹ãŠã®ã·ã£ãŒãã«éä¿¡ãããããã«èšèšãããŠããŸãã Yandex.Metrica 㧠avito.ru ã®ã¬ããŒã ããŒãžãéããšããªã¯ãšã¹ã㯠XNUMX å°ã®ãµãŒããŒã«éä¿¡ãããŸãã RuNet ã«ã¯å€§èŠæš¡ãªãµã€ããã»ãšãã©ãããŸããããããŠããªã¯ãšã¹ã㯠XNUMX ç§ããã XNUMX 件ã§ã¯ãªããXNUMX 件æªæºã§ããããããã¹ãŠã¯åæ£ããŒãã«ã«ãã£ãŠéãã«åŠçãããããããã XNUMX å°ã®ãµãŒããŒã§åŠçãããŸãã
2 çªç®ã®ã¯ã©ã¹ã¿ãŒã¯å°èŠæš¡ãµã€ãçšã§ããããã¯ãµã€ã ID ã«åºã¥ãã·ã£ãŒãã£ã³ã° ã¹ããŒã ã§ãããåãªã¯ãšã¹ã㯠1 ã€ã®ã·ã£ãŒãã«ã®ã¿éä¿¡ãããŸãã
ClickHouse ã«ã¯ãã¯ãªãã¯ããŠã¹ ã³ã㌠ãŠãŒãã£ãªãã£ããããŸãã圌女ã«ã€ããŠæããŠããã ããŸããïŒ
ãã®ãœãªã¥ãŒã·ã§ã³ã¯ããç ©éã§ãçç£æ§ãå€å°å£ããšããã«èšããŸããå©ç¹ã¯ãæå®ãããã¿ãŒã³ã«åŸã£ãŠããŒã¿ãå®å šã«æ±ãããšã§ãããã ãããã®ãŠãŒãã£ãªãã£ã®æ¬ ç¹ã¯ããªã·ã£ãŒãããŸã£ããè¡ãããªãããšã§ããããã¯ã©ã¹ã¿ãŒ ã¹ããŒãããå¥ã®ã¯ã©ã¹ã¿ãŒ ã¹ããŒãã«ããŒã¿ãã³ããŒããŸãã
ããã¯ããããæ©èœããã«ã¯ 2 ã€ã®ã¯ã©ã¹ã¿ãŒãå¿ èŠã§ããããšãæå³ããŸãããããã¯åããµãŒããŒäžã«é 眮ã§ããŸãããããã§ãããŒã¿ã¯æ®µéçã«ç§»åããããã³ããŒãããŸãã
ããšãã°ã以å㯠4 å°ã®ãµãŒããŒããããŸããããçŸåšã¯ 8 å°ã«ãªã£ãŠããŸãããã¹ãŠã®ãµãŒããŒäžã«æ°ããåæ£ããŒãã«ãæ°ããããŒã«ã« ããŒãã«ãäœæããã¯ãªãã¯ããŠã¹ ã³ããŒæ©ãèµ·åããŠãããããèªã¿åããæ°ããã·ã£ãŒãã£ã³ã° ã¹ããŒã ãåãå ¥ããããã«ããŒã¿ã転éããå¿ èŠãããäœæ¥ã¹ããŒã ãæå®ããŸãããŸããå€ããµãŒããŒã§ã¯ãå€ãããŒã¿ãæ®ããŠããå¿ èŠããããåãå€ãããŒã¿ã®ååããã®äžã«å°çãããããçŸåšã®ãµãŒããŒã® 1.5 åã®ã¹ããŒã¹ãå¿ èŠã«ãªããŸããããŒã¿ãåã·ã£ãŒãã£ã³ã°ããå¿ èŠããããã¹ããŒã¹ããããšäºåã«èããããŠããå Žåã¯ããã®æ¹æ³ãé©ããŠããŸãã
ã¯ãªãã¯ããŠã¹ã³ããŒæ©ã¯å éšã§ã©ã®ããã«åäœããŸãã?ãã¹ãŠã®äœæ¥ãã1 ã€ã®ã·ã£ãŒãäžã® 1 ã€ã®ããŒãã«ã® 1 ã€ã®ããŒãã£ã·ã§ã³ãåŠçããäžé£ã®ã¿ã¹ã¯ã«åå²ããŸããããããã¹ãŠã®ã¿ã¹ã¯ã¯äžŠè¡ããŠå®è¡ã§ããclickhouse-copier ã¯è€æ°ã®ã€ã³ã¹ã¿ã³ã¹ã®ç°ãªããã·ã³äžã§å®è¡ã§ããŸããã1 ã€ã®ããŒãã£ã·ã§ã³ã«å¯ŸããŠè¡ãããšã¯æ¿å ¥éžæã«ãããŸãããããŒã¿ã¯èªã¿åããã解åãããååå²ããããã®åŸåã³å§çž®ãããã©ããã«æžã蟌ãŸããåãœãŒããããŸããããã¯ããé£ãã決æã§ãã
ãªã·ã£ãŒãã£ã³ã°ãšåŒã°ãããã€ãããçãªãã®ããããŸããã圌女ã¯ã©ãã§ããïŒ
2017 幎ã«ããªã·ã£ãŒãã£ã³ã°ãšåŒã°ãããã€ãããçãªãã®ããããŸããã ClickHouseã«ããªãã·ã§ã³ããããŸããç§ãç解ããŠããããã«ãããã¯é¢éžããŸããã§ããããªããããèµ·ãã£ãã®ãæããŠããããŸããïŒããã¯éåžžã«é¢é£æ§ãããããã§ãã
å šäœçãªåé¡ã¯ãããŒã¿ãé©åãªå Žæã«åã·ã£ãŒãã£ã³ã°ããå¿ èŠãããå Žåããããã¢ãããã¯ã«è¡ãããã«éåžžã«è€éãªåæãå¿ èŠã«ãªãããšã§ãããã®åæãã©ã®ããã«æ©èœãããã調ã¹å§ãããšãããæ ¹æ¬çãªåé¡ãããããšãæããã«ãªããŸããããããŠããããã®åºæ¬çãªåé¡ã¯çè«çãªãã®ã§ããã ãã§ãªããéåžžã«ç°¡åã«èª¬æã§ãããã®ã®åœ¢ã§ããã«å®éã«çŸãå§ããŸãã-äœãæ©èœããŸããã
äœéãã£ã¹ã¯ã«ç§»åããåã«ããã¹ãŠã®ããŒã¿ãçµåããããšã¯ã§ããŸãã?
ããŒãžã®ã³ã³ããã¹ãã«ãããäœéãã£ã¹ã¯ãžã®ç§»åãªãã·ã§ã³ã䜿çšãã TTL ã«ã€ããŠã®è³ªåã§ãã cron çµç±ä»¥å€ã«ãäœéãã£ã¹ã¯ã«ç§»åããåã«ãã¹ãŠã®éšåã 1 ã€ã«ããŒãžããæ¹æ³ã¯ãããŸãã?
質åã«å¯Ÿããçãã¯ã転éããåã«ããã¹ãŠã®éšåãäœããã®æ¹æ³ã§èªåçã« 1 ã€ã«æ¥çããããšã¯å¯èœã§ããããšããããšã§ãããããããã§ããããã¯å¿ èŠãªããšæããŸãããã¹ãŠã®éšåã 1 ã€ã«ããŒãžããå¿ èŠã¯ãããŸããããäœéãã£ã¹ã¯ã«èªåçã«è»¢éãããããšãæåŸ ããŠãã ããã
転éã«ãŒã«ã«ã¯ 2 ã€ã®åºæºããããŸãã 1æç®ã¯è©°ããç¶æ ã§ããçŸåšã®ã¹ãã¬ãŒãžå±€ã®ç©ºãé åãäžå®ã®å²åæªæºã®å Žåããã£ã³ã¯ã 1 ã€éžæãããããäœéã®ã¹ãã¬ãŒãžã«ç§»åããŸãããšããããé ãã®ã§ã¯ãªããèšå®ã©ããã«æ¬¡ã®é床ã«ãªããŸãã
2 çªç®ã®åºæºã¯ãµã€ãºã§ãã倧ããªéšåãåããããšã§ããé«éãã£ã¹ã¯ã®ç©ºã容éã«å¿ããŠãããå€ã調æŽã§ããããŒã¿ã¯èªåçã«è»¢éãããŸãã
äºåã«äºææ§ã確èªããæ¹æ³ããªãå ŽåãClickHouse ã®æ°ããããŒãžã§ã³ã«ç§»è¡ããã«ã¯ã©ãããã°ããã§ãã?
ãã®è©±é¡ã¯å®æçã«è°è«ãããŸã
ClickHouseé»å ±ãã£ããå ããŸããŸãªããŒãžã§ã³ãèæ ®ããŠãããã§ããããŒãžã§ã³ 19.11 ãã 19.16 ãžã®ã¢ããã°ã¬ãŒããããšãã° 19.16 ãã 20.3 ãžã®ã¢ããã°ã¬ãŒãã¯ã©ã®çšåºŠå®å šã§ããããµã³ãããã¯ã¹ã§äºææ§ãäºåã«ç¢ºèªã§ããªãç¶æ ã§æ°ããããŒãžã§ã³ã«ç§»è¡ããæåã®æ¹æ³ã¯äœã§ãã?
ããã«ã¯ããã€ãã®ãé»éã®ãã«ãŒã«ããããŸããåã -
次ã«ããµã³ãããã¯ã¹ã§äºææ§ã確èªããæ¹æ³ããªããéçšç°å¢ã§ããã«æŽæ°ãããå Žåã¯ããããè¡ãå¿ èŠã¯ãªããšããæšå¥šäºé ããããŸãããŸããµã³ãããã¯ã¹ãäœæããŠãã¹ãããŸãããã¹ãç°å¢ããªãå Žåã¯ã倧èŠæš¡ãªäŒç€Ÿã§ã¯ãªãå¯èœæ§ãé«ããããŒã¿ã®äžéšãã©ãããããã«ã³ããŒããŠããã¹ãŠãæ£ããåäœããããšã確èªã§ããŸãããã·ã³äžã§ããŒã«ã«ã«è€æ°ã®ã¬ããªã«ãäœæããããšãã§ããŸãããŸãã¯ãè¿ãã®ã©ããã«ããæ°ããããŒãžã§ã³ãå ¥æããŠãããã«ããŒã¿ã®äžéšãã¢ããããŒãããããšãã§ããŸããã€ãŸããå³åžã®ãã¹ãç°å¢ãäœæããŸãã
ãã 1 ã€ã®ã«ãŒã«ã¯ãæ¬çªç°å¢ã§ã®ãã°ã®çºèŠãšãã®åŸã®ã¯ã€ãã¯ä¿®æ£ã®ãããããŒãžã§ã³ã®ãªãªãŒã¹åŸ 1 é±éã¯æŽæ°ããªãããšã§ããæ··ä¹±ããªãããã«ãClickHouse ã®ããŒãžã§ã³ã®çªå·ãç解ããŸãããã
ããŒãžã§ã³20.3.4ããããŸããæ°åã® 20 ã¯è£œé 幎 - 2020 ã瀺ããŸããäžèº«ã®èŠ³ç¹ããã¯ãããã¯éèŠã§ã¯ãªãããã泚æãæããŸããã次㞠- 20.3ãäœããã®æ°æ©èœãåãããªãªãŒã¹ããªãªãŒã¹ãããã³ã«ã3 çªç®ã®æ°å€ (ãã®å Žå㯠20.4) ãå¢ãããŸãã ClickHouse ã«äœããã®æ©èœãè¿œå ãããå Žåã¯ããã®æ°ãå¢ããå¿ èŠããããŸããã€ãŸããããŒãžã§ã³ 20.3.4 ã§ã¯ãClickHouse ã®åäœãããã«åäžããŸãã 4 æ¡ç®ã¯ 4 ã§ãããã㧠XNUMX ã¯ãæ°ããæ©èœã¯è¿œå ãããŸããã§ããããããã€ãã®ãã°ãä¿®æ£ãããããã ãªãªãŒã¹ã®æ°ã§ãããã㊠XNUMX ã¯ããã XNUMX åè¡ã£ãããšãæå³ããŸãã
ãããäœãã²ã©ãããšã ãšã¯æããªãã§ãã ãããéåžžããŠãŒã¶ãŒã¯ææ°ããŒãžã§ã³ãã€ã³ã¹ããŒã«ã§ãã幎é皌åæéã«åé¡ãªãåäœããŸããããããäžåœäººã®åå¿ã«ãã£ãŠè¿œå ããããããããããåŠçããããã®é¢æ°ã§ãééã£ãåŒæ°ãæž¡ããšãµãŒããŒãã¯ã©ãã·ã¥ãããšæ³åããŠãã ãããç§ãã¡ã«ã¯ããã解決ãã責任ããããŸããæ°ããããã ããŒãžã§ã³ããªãªãŒã¹ãããClickHouse ã¯ããå®å®ããŸãã
ClickHouse ãéçšç°å¢ã§å®è¡ããŠããŠãè¿œå æ©èœãåãã ClickHouse ã®æ°ããããŒãžã§ã³ããªãªãŒã¹ãããå Žå (ããšãã°ã20.4.1 ãæåã®ããŒãžã§ã³ã§ã)ãåæ¥ã«æ¥ãã§éçšç°å¢ã«å°å ¥ããªãã§ãã ããããããããªããããå¿ èŠãªã®ã§ããããïŒ ClickHouse ããŸã 䜿çšããŠããªãå Žåã¯ãã€ã³ã¹ããŒã«ã§ããŸããã»ãšãã©ã®å Žåããã¹ãŠåé¡ãªãåäœããŸãããã ããClickHouse ããã§ã«å®å®ããŠåäœããŠããå Žåã¯ãããããšã¢ããããŒãã«æ³šç®ããŠãã©ã®ãããªåé¡ãä¿®æ£ãããŠãããã確èªããŠãã ããã
ããªã«ã»ã·ã¥ãŽã¡ã³ãïŒ ãã¹ãç°å¢ã«ã€ããŠå°ãè£è¶³ããããšæããŸãã誰ãããã¹ãç°å¢ãéåžžã«æããŠãããéåžžã«å€§èŠæš¡ãª ClickHouse ã¯ã©ã¹ã¿ãŒãããå Žåããã¹ãç°å¢ã¯å°ãªããšã 10 åã® 1 ããå°ãããªããã°ãªããªããšäœããã®çç±ã§ä¿¡ããŠããŸããå šç¶ãããªããšãªãã§ããã
ç§èªèº«ã®äŸããèšããŸããç§ã«ã¯ãããžã§ã¯ãããããClickHouse ããããŸããç§ãã¡ã®ãã¹ãç°å¢ã¯åœŒå°çšã§ãããã㯠Hetzner ã«ãã 20 ãŠãŒãã®å°ããªä»®æ³ãã·ã³ã§ãããã«ã¯ãã¹ãŠããããã€ãããŠããŸãããããè¡ãããã«ãAnsible ã§ã¯å®å šãªèªååãè¡ãããŠãããããååãšããŠãããŒããŠã§ã¢ ãµãŒããŒã«é 眮ããããåã«ä»®æ³ãã·ã³ã«ãããã€ããããªã©ãã©ãã«é 眮ããŠãéãã¯ãããŸããã
äœãã§ããã§ããããïŒ ClickHouse ã®ããã¥ã¡ã³ãã§ãèªå® ã«å°ããªã¯ã©ã¹ã¿ãŒããããã€ããæ¹æ³ã®äŸãæäŸãããšããã§ããããDocker ã LXC ã§ããããã Ansible ãã¬ã€ããã¯ãäœæããŸããããã¯ã人ã«ãã£ãŠãããã€ã¡ã³ããç°ãªãããã§ããããã«ããå€§å¹ ã«ç°¡çŽ åãããŸãã 5 åã§ã¯ã©ã¹ã¿ãŒãååŸããŠãããã€ã§ãããšãäœããç解ããã®ãã¯ããã«ç°¡åã«ãªããŸãããã¹ãããŠããªã補åããŒãžã§ã³ã«ç§»è¡ããã®ã¯çµããã®ãªãéã§ãããããããã¯éåžžã«äŸ¿å©ã§ããããŸãããããšãããã°ãããŸããããªãããšããããŸãããããã£ãŠãæåãæåŸ ããã®ã¯æªãããšã§ãã
Maxim Kotyakov æ°ãã·ã㢠ããã¯ãšã³ã ãšã³ãžã㢠Avito æ°: 倧äŒæ¥ãçŽé¢ããäžé£ã®èª²é¡ã®ãã¡ããã¹ãç°å¢ã«ã€ããŠå°ãè£è¶³ããŸããåœç€Ÿã«ã¯æ¬æ Œç㪠ClickHouse ã¢ã¯ã»ãã¿ã³ã¹ ã¯ã©ã¹ã¿ãŒããããããŒã¿ ã¹ããŒã ãšèšå®ã«é¢ããŠã¯ãéçšç°å¢ã®æ£ç¢ºãªã³ããŒã§ãããã®ã¯ã©ã¹ã¿ãŒã¯ãæå°éã®ãªãœãŒã¹ãåããããªãèå»ããã³ã³ãããŒã«ãããã€ãããŸããæ¬çªããŒã¿ã®äžå®ã®å²åãããã«æžã蟌ã¿ãŸãã幞ããªããšã«ãKafka ã§ã¹ããªãŒã ãè€è£œããããšãå¯èœã§ããããã«ãããã®ã¯ãã¹ãŠåæãããã¹ã±ãŒã«ãããŸãã容éãšãããŒã®äž¡æ¹ã®ç¹ã§ãçè«çã«ã¯ãä»ã®ãã¹ãŠã®æ¡ä»¶ãçããå Žåãã¡ããªã¯ã¹ã®ç¹ã§æ¬çªãšåæ§ã«åäœããã¯ãã§ããççºã®å¯èœæ§ããããã®ã¯ãã¹ãŠãæåã«ãã®ã¹ã¿ã³ãã«è»¢ããããæºåãæŽããŸã§æ°æ¥éããã«æŸçœ®ãããŸãããããåœç¶ã®ããšãªããããã®ãœãªã¥ãŒã·ã§ã³ã¯é«äŸ¡ã§é£ããããµããŒã ã³ã¹ãããŒãã§ã¯ãããŸããã
ã¢ã¬ã¯ã»ã€ã»ãããŽã£ãã: Yandex.Metrica ã®å人ãã¡ã®ãã¹ãç°å¢ãã©ã®ãããªãã®ãã説æããŸãã 600 ã€ã®ã¯ã©ã¹ã¿ãŒã«ã¯ 360 å°ã®ãµãŒããŒããããå¥ã®ã¯ã©ã¹ã¿ãŒã«ã¯ XNUMX ã®ãµãŒããŒããããããã« XNUMX çªç®ãšããã€ãã®ã¯ã©ã¹ã¿ãŒããããŸãããã®ãã¡ã® XNUMX ã€ã®ãã¹ãç°å¢ã¯ãããããã« XNUMX ã€ã®ã¬ããªã«ãæã€åçŽãª XNUMX ã€ã®ã·ã£ãŒãã§ãããªã XNUMX ã€ã®ã·ã£ãŒããããã®ã§ãããã?ããªããäžäººã§ã¯ãããŸããããã«ããããŠã¬ããªã«ãããã¯ãã§ããæ¯æããæäœéã®éé¡ã ãã§ãã
ãã®ãã¹ãç°å¢ã§ã¯ãã¯ãšãªãæ©èœããŠãããã©ããããŸãé倧ãªåé¡ããªããã©ããã確èªã§ããŸãããããããã¹ãŠãæ©èœããŠããã«ãããããããè² è·ã«å°ããªå€åãããå ŽåããŸã£ããç°ãªãæ§è³ªã®åé¡ãçºçããããšããããããŸãã
äŸãæããŠã¿ãŸãããã ClickHouse ã®æ°ããããŒãžã§ã³ãã€ã³ã¹ããŒã«ããããšã«ããŸãããããã¯ãã¹ãç°å¢ã«æçš¿ãããŠãããèªåãã¹ã㯠Yandex.Metrica èªäœã§å®äºããŠãããå€ãããŒãžã§ã³ãšæ°ããããŒãžã§ã³ã®ããŒã¿ãæ¯èŒãããã€ãã©ã€ã³å šäœãå®è¡ããŸãããããŠãã¡ãããCI ã®ã°ãªãŒã³ ãã¹ããè¡ããŸããããã§ãªããã°ããã®ããŒãžã§ã³ãææ¡ããããšãããªãã£ãã§ãããã
ãã¹ãŠé 調ãããããæ¬çªã«åããŠåãå§ããŸããã°ã©ãã®è² è·ãæ°åã«å¢å ãããšããã¡ãã»ãŒãžã衚瀺ãããŸããããŒãžã§ã³ãããŒã«ããã¯ããŠããŸããã°ã©ããèŠããšãè² è·ã¯å®éã«ããŒã«ã¢ãŠãäžã«æ°åå¢å ããããŒã«ã¢ãŠãæã«æžå°ããŠããããšãããããŸãããã®åŸãããŒãžã§ã³ã®ããŒã«ããã¯ãéå§ããŸããããããŠè² è·ã¯åãããã«å¢å ããåãããã«æžå°ããŸãããçµè«ã¯æ¬¡ã®ãšããã§ããã¬ã€ã¢ãŠãã®ããã§è² è·ãå¢å ããŸããããé©ãã¹ãããšã§ã¯ãããŸããã
ãã®åŸãååã«æ°ããããŒãžã§ã³ãã€ã³ã¹ããŒã«ãããã説åŸããã®ã¯å°é£ã§ãããç§ã¯èšããŸãïŒã倧äžå€«ãåºçºããŠãã ãããé 匵ã£ãŠãã ããããã¹ãŠããŸããããŸããã°ã©ãã®è² è·ãå¢å ããŸãããããã¹ãŠåé¡ãããŸãããé 匵ãã"äžè¬çã«ããããå®è¡ããã ãã§ãããŒãžã§ã³ã¯å®çšŒåçšã«ãªãªãŒã¹ãããŸãããããããã»ãŒãã¹ãŠã®ã¬ã€ã¢ãŠãã§åæ§ã®åé¡ãçºçããŸãã
Kill ã¯ãšãªã¯ã¯ãšãªã匷å¶çµäºããããšã«ãªã£ãŠããŸãããããã§ã¯ãããŸããããªãïŒ
ããçš®ã®ã¢ããªã¹ãã§ãããŠãŒã¶ãŒãç§ã®ãšããã«æ¥ãŠãç§ã® ClickHouse ã¯ã©ã¹ã¿ãŒãé 眮ãããªã¯ãšã¹ããäœæããŸããããªã¯ãšã¹ããéä¿¡ãããã¬ããªã«ãŸãã¯ã·ã£ãŒãã«å¿ããŠãäžéšã®ããŒããŸãã¯ã¯ã©ã¹ã¿ãŒå šäœããã®ãµãŒããŒäžã®ãã¹ãŠã® CPU ãªãœãŒã¹ãã·ã§ã«ãå ã«ããããã¹ãŠãèµ€è²ã«ãªã£ãŠããããšãããããŸããåæã«ãClickHouse èªäœããªã¯ãšã¹ãã«å¿çããŸãããããŠç§ã¯ããæžããŸãããããã»ã¹ãªã¹ããèŠããŠãã ãããã©ã®ãããªãªã¯ãšã¹ãããã®çæ°ãåŒãèµ·ãããã®ããæããŠãã ãããã
ãã®ãªã¯ãšã¹ããèŠã€ããŠãããã« kill ãæžã蟌ã¿ãŸãããããŠãäœãèµ·ãã£ãŠããªãããšãããããŸããç§ã®ãµãŒããŒã¯æ£ã®äžã«ãããClickHouse ãããã€ãã®ã³ãã³ããäžãããµãŒããŒãçããŠããããšã瀺ãããã¹ãŠãé 調ã§ãããããããã¹ãŠã®ãŠãŒã¶ãŒãªã¯ãšã¹ãã§å£åããããå£åã¯ClickHouseã®ã¬ã³ãŒãããå§ãŸããkillã¯ãšãªã¯æ©èœããŸããããªãïŒ kill ã¯ãšãªã¯ã¯ãšãªã匷å¶çµäºãããã®ã ãšæã£ãŠããŸããããããã§ã¯ãããŸããã
ããŠãããªãå¥åŠãªçããåŸãããŸããéèŠãªã®ã¯ãkill ã¯ãšãªã§ã¯ã¯ãšãªã kill ãããªããšããããšã§ãã
Kill query ã§ã¯ããI want this query to be killããšããå°ããªããã¯ã¹ã«ãã§ãã¯ãå ¥ããŸãããããŠããªã¯ãšã¹ãèªäœã¯ãåãããã¯ãåŠçãããšãã«ãã®ãã©ã°ã確èªããŸããèšå®ãããŠããå Žåããªã¯ãšã¹ãã¯åäœãåæ¢ããŸãã誰ããªã¯ãšã¹ãã殺ããªãããšãå€æããŸããã圌èªèº«ããã¹ãŠããã§ãã¯ããŠåæ¢ããå¿ èŠããããŸãããããŠãããã¯ããªã¯ãšã¹ããããŒã¿ã®ãããã¯ãåŠçããŠããç¶æ ã«ãããã¹ãŠã®ã±ãŒã¹ã§æ©èœããã¯ãã§ãã次ã®ããŒã¿ãããã¯ãåŠçãããã©ã°ããã§ãã¯ããŠåæ¢ããŸãã
ããã¯ããªã¯ãšã¹ããäžéšã®æäœã§ãããã¯ãããŠããå Žåã«ã¯æ©èœããŸããã確ãã«ãããããããã¯ããªãã®ã±ãŒã¹ã§ã¯ãããŸããããªããªããããªãã«ãããšã倧éã®ãµãŒããŒãªãœãŒã¹ã䜿çšããããã§ããå€éšãœãŒãããã®ä»ã®è©³çŽ°ã§ã¯ããããæ©èœããªãå¯èœæ§ããããŸããããããäžè¬çã«ããã¯èµ·ããã¹ãã§ã¯ãªãããã°ã§ãããããŠãç§ããå§ãã§ããå¯äžã®ããšã¯ãClickHouseãæŽæ°ããããšã§ãã
èªã¿åãè² è·æã®å¿çæéãèšç®ããã«ã¯ã©ãããã°ããã§ãã?
ã¢ã€ãã ã®éåäœ (ããŸããŸãªã«ãŠã³ã¿ãŒ) ãæ ŒçŽããããŒãã«ããããŸããè¡æ°ã¯çŽ1åè¡ã§ãã 1 åã®ã¢ã€ãã ã« XNUMX åã® RPS ã泚ãå Žåãäºæž¬å¯èœãªå¿çæéãåœãŠã«ããããšã¯å¯èœã§ãã?
æèããå€æãããšãæžã蟌ã¿ã«ã¯åé¡ããªããããèªã¿åãè² è·ã«ã€ããŠè©±ããŠããããšãããããŸãã1,000 è¡ã10 äžè¡ãå Žåã«ãã£ãŠã¯æ°çŸäžè¡ãæ¿å ¥ããããšãã§ããŸãã
èªæžãªã¯ãšã¹ãã¯éåžžã«ç°ãªããŸããéžæ 1 ã§ã¯ãClickHouse 㯠8192 ç§ãããçŽæ°äžä»¶ã®ãªã¯ãšã¹ããå®è¡ã§ããããã64 ã€ã®ããŒã«å¯Ÿãããªã¯ãšã¹ãã§ããã§ã«ããçšåºŠã®ãªãœãŒã¹ãå¿ èŠã«ãªããŸãããŸãããã®ãããªãã€ã³ã ã¯ãšãªã¯ãèªã¿åãããšã«ã€ã³ããã¯ã¹ã«ãã£ãŠããŒã¿ ãããã¯ãèªã¿åãå¿ èŠããããããäžéšã®ããŒãšå€ã®ããŒã¿ããŒã¹ãããå°é£ã«ãªããŸããç§ãã¡ã®ã€ã³ããã¯ã¹ã¯åã¬ã³ãŒãã§ã¯ãªããåç¯å²ã«å¯Ÿå¿ããŸããã€ãŸããç¯å²å šäœãèªã¿åãå¿ èŠããããŸããããã©ã«ãã§ã¯ 1 è¡ã§ãããŸããå§çž®ãããããŒã¿ ãããã¯ã XNUMX KB ãã XNUMX MB ã«è§£åããå¿ èŠããããŸããéåžžããã®ãããªå¯Ÿè±¡ãçµã£ãã¯ãšãªã¯å®äºãããŸã§ã«æ°ããªç§ããããŸãããããããããæãç°¡åãªãªãã·ã§ã³ã§ãã
ç°¡åãªç®è¡ãè©ŠããŠã¿ãŸããããæ°ããªç§ã 1000 ã§ä¹ç®ãããšãæ°ç§ã«ãªããŸãã XNUMX ç§ããã XNUMX ã®ãªã¯ãšã¹ãã«å¯Ÿå¿ããã®ã¯äžå¯èœã§ãããã®ããã«èŠããŸãããå®éã«ã¯ãè€æ°ã®ããã»ããµ ã³ã¢ããããããããã¯å¯èœã§ãããã®ãããååãšããŠãClickHouse 㯠XNUMX RPS ãä¿æã§ããå ŽåããããŸãããçããªã¯ãšã¹ããç¹ã«å¯Ÿè±¡ãçµã£ããªã¯ãšã¹ãã®å Žåã¯å¯èœã§ãã
åçŽãªãªã¯ãšã¹ãã®æ°ã«å¿ã㊠ClickHouse ã¯ã©ã¹ã¿ãŒãæ¡åŒµããå¿ èŠãããå Žåã¯ãã¬ããªã«ã®æ°ãå¢ãããã©ã³ãã ãªã¬ããªã«ã«ãªã¯ãšã¹ããéä¿¡ãããšããæãåçŽãªæ¹æ³ããå§ãããŸãã 1 ã€ã®ã¬ããªã«ã 1 ç§ããã 500 ã®ãªã¯ãšã¹ããä¿æããå Žåãããã¯å®å šã«çŸå®çã§ããã3 ã€ã®ã¬ããªã«ã¯ 15,000 件ã®ãªã¯ãšã¹ããåŠçããããšã«ãªããŸãã
ãã¡ããããã€ã³ãèªã¿åãã®æ倧æ°ã«åãã㊠ClickHouse ãæ§æã§ããå ŽåããããŸãããã®ããã«ã¯äœãå¿ èŠãªã®ã§ããããïŒ 64 ã€ç®ã¯ãã€ã³ããã¯ã¹ã®ç²åºŠãäžããããšã§ãããã®å Žåãã€ã³ããã¯ã¹å ã®ãšã³ããªæ°ããµãŒããŒããšã«æ°çŸäžãŸãã¯æ°åäžã«ãªãããšãåæãšããŠãXNUMX ã€ã«æžããå¿ èŠã¯ãããŸãããããŒãã«ã« XNUMX åè¡ãããå Žåãç²åºŠã¯ XNUMX ã«èšå®ã§ããŸãã
å§çž®ããããããã¯ã®ãµã€ãºãå°ããããããšãã§ããŸããããã«ã¯èšå®ããããŸã æå°å§çž®ããã㯠ãµã€ãº, æ倧å§çž®ããã㯠ãµã€ãºãããããåæžããããŒã¿ãè£å ãããšãã¿ãŒã²ãããçµã£ãã¯ãšãªãé«éåãããŸãããã ããClickHouse ã¯ããŒãšå€ã®ããŒã¿ããŒã¹ã§ã¯ãããŸãããå€æ°ã®å°ããªãªã¯ãšã¹ãã¯è² è·ã®ã¢ã³ããã¿ãŒã³ã§ãã
ããªã«ã»ã·ã¥ãŽã¡ã³ãïŒ æ®éå£åº§ãããå Žåã®ã¢ããã€ã¹ããããŠããã ããŸããããã¯ãClickHouse ãããçš®ã®ã«ãŠã³ã¿ãŒãä¿åããå Žåã®ããªãæšæºçãªç¶æ³ã§ããç§ã«ã¯ãŠãŒã¶ãŒãããŠã圌ã¯ã©ãã®åœåºèº«ã§ã第äžã®åéã®åºèº«ã§ãããäœãã段éçã«å¢ããå¿ èŠããããŸãã MySQL ã®å Žåãäžæã®ããŒãäœæã (MySQL ã§ã¯éè€ããŒãPostgreSQL ã§ã¯ç«¶å)ããã©ã¹èšå·ãè¿œå ããŸããããã¯ãã£ãšããŸããããŸãã
ããŒã¿ãããŸããªãå ŽåãClickHouse ã䜿çšããæå³ã¯ããŸããããŸãããéåžžã®ããŒã¿ããŒã¹ãããããããããŸãå®è¡ããŸãã
ããå€ãã®ããŒã¿ããã£ãã·ã¥ã«å
¥ããã«ã¯ãClickHouse ã§äœã調æŽã§ããŸãã?
ç¶æ³ãæ³åããŠã¿ãŸããã - ãµãŒããŒã«ã¯ 256 GB ã® RAM ããããClickHouse ã¯æ¥åžžçã«çŽ 60 ïœ 80 GBãããŒã¯æã«ã¯æ倧 130 GB ã䜿çšããŸããããå€ãã®ããŒã¿ããã£ãã·ã¥ã«ä¿åãããããã«ãäœãæå¹ã«ããŠèª¿æŽã§ããããããã«å¿ããŠããã£ã¹ã¯ãžã®ããªãããæžã£ãã®ã§ããããïŒ
éåžžããªãã¬ãŒãã£ã³ã° ã·ã¹ãã ã®ããŒãž ãã£ãã·ã¥ãããã«é©åã«å¯Ÿå¿ããŸããäžéšãéããŠãããã£ãã·ã¥æžã¿ããŸãã¯ã空ããã確èªãããšããã£ãã·ã¥ãããŠããéã衚瀺ããããã¹ãŠã®ç©ºãã¡ã¢ãªããã£ãã·ã¥ã«äœ¿çšãããŠããããšãããããŸãããã®ããŒã¿ãèªã¿åããšãã¯ããã£ã¹ã¯ããã§ã¯ãªã RAM ããèªã¿åãããŸããåæã«ãå§çž®ãããããŒã¿ããã£ãã·ã¥ãããããããã£ãã·ã¥ãæå¹ã«æŽ»çšãããŠãããšãèšããŸãã
ãã ããããã€ãã®åçŽãªã¯ãšãªãããã«é«éåãããå Žåã¯ãClickHouse å ã®è§£åãããããŒã¿ã®ãã£ãã·ã¥ãæå¹ã«ããããšãã§ããŸãããããã éå§çž®ãã£ãã·ã¥ã config.xml æ§æãã¡ã€ã«ã§ãéå§çž®ãã£ãã·ã¥ ãµã€ãºãå¿ èŠãªå€ã«èšå®ããŸããæ®ãã¯ããŒãž ãã£ãã·ã¥ã«çœ®ãããããã空ã RAM ã®åå以äžã«ããããšããå§ãããŸãã
ããã«ãèŠæ±ã¬ãã«ã®èšå®ã¯ 2 ã€ãããŸããæåã®èšå® - éå§çž®ãã£ãã·ã¥ã䜿çšãã - ãã®äœ¿çšãå«ã¿ãŸãããã¹ãŠã®ããŒã¿ãèªã¿åã£ãŠãã£ãã·ã¥ããã©ãã·ã¥ã§ããéããªã¯ãšã¹ããé€ãããã¹ãŠã®ãªã¯ãšã¹ãã«å¯ŸããŠãããæå¹ã«ããããšããå§ãããŸãã 2 çªç®ã®èšå®ã¯ããã£ãã·ã¥ã䜿çšããæ倧è¡æ°ã®ãããªãã®ã§ãã倧èŠæš¡ãªã¯ãšãªããã£ãã·ã¥ããã€ãã¹ããããã«èªåçã«å¶éãããŸãã
RAM å
ã®ã¹ãã¬ãŒãžçšã« storage_configuration ãæ§æããã«ã¯ã©ãããã°ããã§ãã?
æ°ãã ClickHouse ããã¥ã¡ã³ãã§ãé¢é£ããã»ã¯ã·ã§ã³ãèªã¿ãŸããã
ããŒã¿ã¹ãã¬ãŒãžä»ã ã説æã«ã¯é«é SSD ã䜿çšããäŸãå«ãŸããŠããŸããåãããšãããªã¥ãŒã ãããã¡ã¢ãªã§ã©ã®ããã«æ§æã§ããã®ãçåã«æããŸãããããŠããäžã€è³ªåã§ãã select ã¯ãã®ããŒã¿æ§æã§ã©ã®ããã«æ©èœããã®ããã»ããå šäœãèªã¿åãã®ãããããšããã£ã¹ã¯äžã® 1 ã€ã ããèªã¿åãã®ãããã®ããŒã¿ã¯ã¡ã¢ãªå ã§å§çž®ãããŠããã®ãããªã©ã§ãããããŠãprewhere ã»ã¯ã·ã§ã³ã¯ãã®ãããªããŒã¿çµç¹ãšã©ã®ããã«é£æºããã®ã§ãããã?
ãã®èšå®ã¯ããŒã¿ ãã£ã³ã¯ã®ã¹ãã¬ãŒãžã«åœ±é¿ãããã®åœ¢åŒã¯ãŸã£ããå€æŽãããŸããã
詳ããèŠãŠã¿ãŸãããã
RAM ãžã®ããŒã¿ ã¹ãã¬ãŒãžãæ§æã§ããŸãããã£ã¹ã¯ã«å¯ŸããŠæ§æãããŠããã®ã¯ãã®ãã¹ã ãã§ãããã¡ã€ã« ã·ã¹ãã å ã®ãã¹ã«ããŠã³ãããã tmpfs ããŒãã£ã·ã§ã³ãäœæããŸãããã®ãã¹ãæãããããªããŒãã£ã·ã§ã³ã®ããŒã¿ãä¿åãããã¹ãšããŠæå®ãããšãããŒã¿ã®äžéšãå°çããããã«æžã蟌ãŸãå§ããŸãããã¹ãŠåé¡ãããŸããã
ãã ããä¿¡é Œæ§ãäœãããããããè¡ãããšã¯ãå§ãããŸããããã ããç°ãªãããŒã¿ã»ã³ã¿ãŒã«å°ãªããšã 3 ã€ã®ã¬ããªã«ãããå Žåã¯ãå¯èœã§ããäœããã£ãå Žåã§ãããŒã¿ã¯åŸ©å ãããŸãããµãŒããŒãçªç¶ãªãã«ãªããåã³ãªã³ã«ãªã£ããšæ³åããŠã¿ãŸããããããŒãã£ã·ã§ã³ãå床ããŠã³ããããŸããããããã«ã¯äœããããŸããã§ããã ClickHouse ãµãŒããŒãèµ·åãããšããããã®èŠçŽ ãååšããªãããšãããããŸãããZooKeeper ã¡ã¿ããŒã¿ã«ããã°ããããã¯ååšããã¯ãã§ãã圌ã¯ãã©ã®ã¬ããªã«ãããããæã£ãŠãããã調ã¹ãããããèŠæ±ããããŠã³ããŒãããŸãããã®ããã«ããŠããŒã¿ã埩å ãããŸãã
ãã®æå³ã§ãããŒã¿ã RAM ã«ä¿åããããšã¯ãããŒã¿ããã£ã¹ã¯ã«ä¿åããããšãšåºæ¬çã«å€ãããŸãããããŒã¿ããã£ã¹ã¯ã«æžã蟌ãŸãããšããããŒã¿ã¯æåã«ããŒãž ãã£ãã·ã¥ã«ä¿åãããåŸã§ç©ççã«æžã蟌ãŸããããã§ããããã¯ããã¡ã€ã« ã·ã¹ãã ã®ããŠã³ã ãªãã·ã§ã³ã«ãã£ãŠç°ãªããŸãããã ãã念ã®ããã«èšã£ãŠãããŸãããClickHouse ã¯æ¿å ¥æã« fsync ãè¡ããŸããã
ãã®å ŽåãRAM å ã®ããŒã¿ã¯ãã£ã¹ã¯äžã®ããŒã¿ãšãŸã£ããåã圢åŒã§ä¿åãããŸããéžæã¯ãšãªãåæ§ã«ãèªã¿èŸŒãå¿ èŠãããããŒã¹ãéžæãããã®ããŒã¹ã®äžããå¿ èŠãªããŒã¿ç¯å²ãéžæããŠèªã¿èŸŒã¿ãŸããããŒã¿ã RAM äžã«ããããã£ã¹ã¯äžã«ãããã«é¢ä¿ãªããprewhere ã¯ãŸã£ããåãããã«æ©èœããŸãã
Low Cardinality ã¯äžæã®å€ã®æ°ãŸã§æå¹ã§ãã?
äœã«ãŒãã£ããªãã£ã¯å·§åŠã«èšèšãããŠããŸããããŒã¿ ãã£ã¯ã·ã§ããªãã³ã³ãã€ã«ããŸããããããã¯ããŒã«ã«ã§ãã第äžã«ãäœåããšã«èŸæžãç°ãªãããšã第äºã«ãåãäœåã®äžã§ãç¯å²ããšã«èŸæžãç°ãªãããšã§ããäžæã®å€ã®æ°ããããå€ (ãããã 100 äž) ã«éãããšããã®èŸæžã¯åã«æ£äžããããæ°ããèŸæžãäœæãããŸãã
çãã¯äžè¬çã«ãããŒã«ã«ç¯å²ããšã«ãããšãã°æ¯æ¥ããšã«ãæ倧 100 äžåã®äžæã®å€ãããã°ãã«ãŒãã£ããªãã£ãäœãããšãå¹æçã§ãããã®åŸã¯åçŽãªãã©ãŒã«ããã¯ãè¡ããã1 ã€ã ãã§ã¯ãªããå€ãã®ç°ãªãèŸæžã䜿çšãããŸããéåžžã®æåååãšã»ãŒåãããã«åäœããŸãããå¹çã¯è¥å¹²å£ããããããŸããããé倧ãªããã©ãŒãã³ã¹ã®äœäžã¯ãããŸããã
50 åè¡ã®ããŒãã«ãå
šææ€çŽ¢ããããã®ãã¹ã ãã©ã¯ãã£ã¹ã¯äœã§ãã?
ããŸããŸãªçãããããŸãã 1 ã€ç®ã¯ãClickHouse ã¯å
šææ€çŽ¢ãšã³ãžã³ã§ã¯ãªããšããããšã§ããããã«ã¯ç¹å¥ãªã·ã¹ãã ããããŸããããšãã°ã
ãªããã®ãããªããšãèµ·ããã®ã§ãããã?圌ãã¯ãElasticsearch ãã€ã³ããã¯ã¹ã®æ§ç¯ãå§ããšããŠãäžéšã®ããªã¥ãŒã ã®è² è·ã«å¯ŸåŠã§ããªããªããšããäºå®ã«ãã£ãŠããã説æããŠããŸããã€ã³ããã¯ã¹ã¯éåžžã«ç ©éã«ãªããããåçŽã«ããŒã¿ã ClickHouse ã«è»¢éãããšã容éã®ç¹ã§æ°åå¹ççã«ä¿åãããããšãããããŸããåæã«ãæ€çŽ¢ã¯ãšãªã¯ã圢æ åŠãèæ ®ããŠããŒã¿å šäœããäœããã®ãã¬ãŒãºãæ€çŽ¢ããå¿ èŠããããããªãã®ã§ã¯ãªãããŸã£ããç°ãªããã®ã§ããããšããããããŸãããããšãã°ãéå»æ°æéã®ãã°å ã§ãã€ãã®äžéšãèŠã€ããŸãã
ãã®å ŽåãClickHouse ã§ã€ã³ããã¯ã¹ãäœæããŸãããã®æåã®ãã£ãŒã«ãã¯æ¥ä»ãšæå»ã«ãªããŸãããŸããæ倧ã®ããŒã¿ã«ãããªãã¯æ¥ä»ç¯å²ã«åºã¥ããŸããéžæããæ¥ä»ç¯å²å ã§ã¯ãååãšããŠãlike ã䜿çšãããã«ãŒã ãã©ãŒã¹ææ³ã䜿çšããå Žåã§ããå šææ€çŽ¢ãå®è¡ããããšããã§ã«å¯èœã§ãã ClickHouse ã® like æŒç®åã¯ãèŠã€ããããäžã§æãå¹çç㪠like æŒç®åã§ãããã£ãšè¯ããã®ãèŠã€ããããæããŠãã ããã
ãã ãããã¯ããã«ã¹ãã£ã³ãšåãã§ãããŸãããã« ã¹ãã£ã³ã¯ CPU ã ãã§ãªããã£ã¹ã¯ã§ãé ããªãå¯èœæ§ããããŸããçªç¶ 1 æ¥ããã 1 ãã©ãã€ãã®ããŒã¿ãçºçããæ¥äžã«åèªãæ€çŽ¢ãããšããã®ãã©ãã€ããã¹ãã£ã³ããå¿ èŠããããŸãããããŠããã¯ããããéåžžã®ããŒããã©ã€ãäžã«ãããæçµçã«ã¯ SSH çµç±ã§ãã®ãµãŒããŒã«ã¢ã¯ã»ã¹ã§ããªããããªæ¹æ³ã§ããŒããããããšã«ãªããŸãã
ãã®å Žåããã 1 ã€å°ããªããªãã¯ãæäŸããæºåãã§ããŠããŸããããã¯å®éšçãªãã®ã§ãããŸããããããããªãããããŸããããªããããããŸããã ClickHouse ã«ã¯ãããªã°ã©ã ãã«ãŒã ãã£ã«ã¿ãŒã®åœ¢åŒã§å šæã€ã³ããã¯ã¹ããããŸãã Arenadata ã®ååã¯ãã§ã«ãããã®ã€ã³ããã¯ã¹ãè©ŠããŠãããå€ãã®å Žåãæå³ãããšããã«æ©èœããŸãã
ããããæ£ãã䜿çšããã«ã¯ãããªã°ã©ã ãã«ãŒã ãã£ã«ã¿ãŒãšã¯äœãããã®ãµã€ãºãéžæããæ¹æ³ãªã©ããã£ã«ã¿ãŒãã©ã®ããã«æ©èœããããæ£ç¢ºã«ç解ããå¿ èŠããããŸãããããã¯ãããŒã¿å ã§ãã£ãã«èŠã€ãããªãããŸããªãã¬ãŒãºãéšåæååã«å¯Ÿããã¯ãšãªã«åœ¹ç«ã€ãšèšããŸãããã®å Žåããµãç¯å²ã¯ã€ã³ããã¯ã¹ã«ãã£ãŠéžæãããèªã¿åãããããŒã¿ã¯å°ãªããªããŸãã
æè¿ãClickHouse ã«ã¯å šææ€çŽ¢ã®ããã®ããã«é«åºŠãªæ©èœãè¿œå ãããŸãããããã¯ããŸãã8 ã€ã®ãã¹ã§äžåºŠã«å€æ°ã®éšåæååãæ€çŽ¢ããŸããããã«ã¯ã倧æåãšå°æåãåºå¥ãããªãã·ã§ã³ã倧æåãšå°æåãåºå¥ããªããªãã·ã§ã³ãUTF-XNUMX ãŸã㯠ASCII ã®ã¿ããµããŒããããªãã·ã§ã³ãå«ãŸããŸããå¿ èŠãªæãå¹æçãªãã®ãéžæããŠãã ããã
1 ãã¹ã§è€æ°ã®æ£èŠè¡šçŸãæ€çŽ¢ããæ©èœãç»å ŽããŸããã X ã 1 ã€ã®éšåæååã®ããã«æžããããX ãå¥ã®éšåæååã®ããã«æžãå¿ èŠã¯ãããŸãããããã«æžãããšãã§ãããã¹ãŠãå¯èœãªéãå¹ççã«è¡ãããŸãã
3 çªç®ã«ãæ£èŠè¡šçŸã®è¿äŒŒæ€çŽ¢ãšéšåæååã®è¿äŒŒæ€çŽ¢ãè¡ãããããã«ãªããŸããã誰ããåèªã®ã¹ãã«ãééããå Žåãæ倧äžèŽããåèªãæ€çŽ¢ãããŸãã
å€æ°ã®ãŠãŒã¶ãŒã® ClickHouse ãžã®ã¢ã¯ã»ã¹ãæŽçããæåã®æ¹æ³ã¯äœã§ãã?
å€æ°ã®æ¶è²»è ãã¢ããªã¹ãã®ã¢ã¯ã»ã¹ãæŽçããæé©ãªæ¹æ³ãæããŠãã ããããã¥ãŒã圢æããæ倧åæã¯ãšãªã®åªå é äœãä»ããæ¹æ³ãšãã©ã®ãããªããŒã«ã䜿çšããã?
ã¯ã©ã¹ã¿ãŒãååã«å€§ããå Žåã¯ãããã« 2 å°ã®ãµãŒããŒãæ§ç¯ããã®ãè¯ã解決çã§ãããããã¢ããªã¹ãã®ãšã³ã㪠ãã€ã³ãã«ãªããŸããã€ãŸããã¢ããªã¹ãã«ã¯ã©ã¹ã¿ãŒå ã®ç¹å®ã®ã·ã£ãŒããžã®ã¢ã¯ã»ã¹ãèš±å¯ãããããŒã¿ã®ãªã空ã®ãµãŒããŒã 2 ã€äœæãããããã®ãµãŒããŒã«ã¢ã¯ã»ã¹æš©ãæ§æããã ãã§ãããã®å Žåãåæ£ãªã¯ãšã¹ãã®ãŠãŒã¶ãŒèšå®ã¯ãªã¢ãŒã ãµãŒããŒã«è»¢éãããŸããã€ãŸããããã 2 ã€ã®ãµãŒããŒäžã§ãã¹ãŠãæ§æãããã®èšå®ã¯ã¯ã©ã¹ã¿ãŒå šäœã«åœ±é¿ããŸãã
ååãšããŠããããã®ãµãŒããŒã«ã¯ããŒã¿ã¯ãããŸãããããªã¯ãšã¹ããå®è¡ããã«ã¯ãµãŒããŒäžã® RAM ã®éãéåžžã«éèŠã§ããå€éšéçŽãŸãã¯å€éšäžŠã¹æ¿ããæå¹ã«ãªã£ãŠããå Žåããã£ã¹ã¯ã¯äžæããŒã¿ã«ã䜿çšã§ããŸãã
èãããããã¹ãŠã®å¶éã«é¢é£ããèšå®ã確èªããããšãéèŠã§ããããã§ãã¢ããªã¹ããšã㊠Yandex.Metrica ã¯ã©ã¹ã¿ãŒã«ã¢ã¯ã»ã¹ãããªã¯ãšã¹ããå°ãããšããŸãã ãããæ°ããã«ãŠã³ããéžæ, ãã®åŸãããã«ãªã¯ãšã¹ããå®è¡ã§ããªããšããäŸå€ãäžããããŸããã¹ãã£ã³ã§ããæ倧è¡æ°ã¯ 1,000 åè¡ã§ãã¯ã©ã¹ã¿ãŒäžã® 1 ã€ã®ããŒãã«ã«ã¯åèš 50 å è¡ããããŸãããããæåã®å¶éã§ãã
è¡å¶éãåé€ããŠã¯ãšãªãå床å®è¡ãããšããŸãã次ã«ã次ã®äŸå€ã衚瀺ãããŸã - èšå®ãæå¹ã«ãªã£ãŠããŸã æ¥ä»ã«ããã€ã³ããã¯ã¹ã匷å¶ãããæ¥ä»ç¯å²ãæå®ããªããšã¯ãšãªãå®äºã§ããŸãããã¢ããªã¹ãã«é Œã£ãŠæåã§æå®ããå¿ èŠã¯ãããŸãããå žåçãªã±ãŒã¹ã¯ãã€ãã³ãã®æ¥ä»ãé±ã®éã«æžãããæ¥ä»ç¯å²ã§ãããããŠãåã«ééã£ãå Žæã«æ¬åŒ§ãæå®ããã ãã§ãand ã®ä»£ããã« or - or URL ãäžèŽããããšãå€æããŸãããå¶éããªãå ŽåãURL åãã¯ããŒã«ããã倧éã®ãªãœãŒã¹ãç¡é§ã«ãªãã ãã§ãã
ããã«ãClickHouse ã«ã¯ 2 ã€ã®åªå é äœèšå®ããããŸããæ®å¿µãªããããããã¯éåžžã«åå§çã§ãã 1ã€ã¯åã«åŒã°ããŸã åªå é äœãåªå 床 â 0 ã§ãäœããã®åªå 床ãæã€ãªã¯ãšã¹ããå®è¡ãããŠããããåªå 床ã®å€ãããããå°ãããã€ãŸãåªå 床ãé«ããªã¯ãšã¹ããå®è¡ãããŠããå Žåãåªå 床ã®å€ããããã倧ãããã€ãŸãåªå 床ãäœããªã¯ãšã¹ããå®è¡ãããŸããã¯åã«äžæåæ¢ãããŠããã ãã§ããã®éã¯ãŸã£ããæ©èœããŸããã
ããã¯éåžžã«å€§ãŸããªèšå®ã§ãããã¯ã©ã¹ã¿ãŒã«äžå®ã®è² è·ããããå Žåã«ã¯é©ããŠããŸããããã ããéèŠãªçããŠããŒã¹ãçãªãªã¯ãšã¹ãããããã¯ã©ã¹ã¿ãŒãã»ãšãã©ã¢ã€ãã«ç¶æ ã§ããå Žåã«ã¯ããã®èšå®ãé©ããŠããŸãã
次ã®åªå é äœã®èšå®ãåŒã³åºãããŸãã OSã¹ã¬ããã®åªå é äœã Linux ã¹ã±ãžã¥ãŒã©ã®ãã¹ãŠã®ãªã¯ãšã¹ãå®è¡ã¹ã¬ããã« nice å€ãèšå®ããã ãã§ãããŸããŸãã®åäœã§ãããããã§ãåäœããŸãã nice ã®æå°å€ãèšå®ã (å€ãæ倧ã§ãããããåªå 床ãæãäœããªããŸã)ãåªå 床ã®é«ããªã¯ãšã¹ãã« -19 ãèšå®ãããšãCPU ãæ¶è²»ããåªå 床ã®äœããªã¯ãšã¹ãã¯ãåªå 床ã®é«ããªã¯ãšã¹ãã®çŽ XNUMX åã® XNUMX ã«ãªããŸãã
æ倧ãªã¯ãšã¹ãå®è¡æé (ããšãã° 5 å) ãæ§æããå¿ èŠããããŸããæãåªããŠããã®ã¯ãã¯ãšãªå®è¡ã®æäœé床ã§ãããã®èšå®ã¯é·ãéååšããŠãããClickHouse ã®é床ãäœäžããªãããšãä¿èšŒããã ãã§ãªãã匷å¶çã«èšå®ããå¿ èŠããããŸãã
æ³åããŠã¿ãŠãã ãããäžéšã®ã¯ãšãªã®åŠçã 1 ç§ããã 100 äžè¡æªæºã§ããå Žåãããã¯å®è¡ã§ããŸãããããã¯ç§ãã¡ã®è¯ãååãè¯ãããŒã¿ããŒã¹ã«æ¥ãããããããšã«ãªããŸããããã ãã¯çŠæ¢ããŸããããå®éã«ã¯ 2 ã€ã®èšå®ããããŸããäžäººã¯åŒã°ããŸã æå°å®è¡é床 - 1 ç§ãããã®è¡æ°ã§ãæå°å®è¡é床ããã§ãã¯ããåã®ç§ã¯ã¿ã€ã ã¢ãŠããšåŒã°ããŸã (ããã©ã«ãã§ã¯ 15 ç§)ãã€ãŸãã15 ç§ã¯å¯èœã§ããããããé ãå Žåã¯ãäŸå€ãã¹ããŒããŠãªã¯ãšã¹ããäžæ¢ããŸãã
ã¯ã©ãŒã¿ãèšå®ããå¿ èŠããããŸãã ClickHouse ã«ã¯ããªãœãŒã¹æ¶è²»ãã«ãŠã³ãããã¯ã©ãŒã¿æ©èœãçµã¿èŸŒãŸããŠããŸãããã ããæ®å¿µãªãããCPU ããã£ã¹ã¯ãªã©ã®ããŒããŠã§ã¢ ãªãœãŒã¹ã§ã¯ãªããè«çãªãœãŒã¹ãã€ãŸãåŠçããããªã¯ãšã¹ãã®æ°ãèªã¿åãããè¡æ°ããã€ãæ°ã察象ãšãªããŸãããŸããããšãã°ã5 å以å ã«æ倧 100 件ã®ãªã¯ãšã¹ãã1 æéãããæ倧 1000 件ã®ãªã¯ãšã¹ããæ§æã§ããŸãã
ã©ãããŠãããéèŠã§ããïŒäžéšã®åæã¯ãšãªã¯ ClickHouse ã¯ã©ã€ã¢ã³ãããçŽæ¥æåã§å®è¡ãããããã§ãããããŠãã¹ãŠãããŸãããã§ãããããããã瀟å ã«é«åºŠãªã¢ããªã¹ããããå Žåã¯ã圌ããã¹ã¯ãªãããäœæããããããã®ã¹ã¯ãªããã«ãšã©ãŒãçºçããå¯èœæ§ããããŸãããã®ãšã©ãŒã«ããããªã¯ãšã¹ãã¯ç¡éã«ãŒãã§å®è¡ãããŸããããã¯ç§ãã¡ã身ãå®ãå¿ èŠããããã®ã§ãã
1 ã€ã®ã¯ãšãªã®çµæã 10 人ã®ã¯ã©ã€ã¢ã³ãã«æäŸããããšã¯å¯èœã§ãã?
éåžžã«å€§ããªãªã¯ãšã¹ããåæã«éä¿¡ããããŠãŒã¶ãŒãäœäººãããŸãããªã¯ãšã¹ãã¯å€§ãããååãšããŠè¿ éã«å®è¡ãããŸããããã®ãããªãªã¯ãšã¹ããåæã«å€æ°ãããããéåžžã«èŠçã«ãªããŸãã 10åé£ç¶ã§å±ããåããªã¯ãšã¹ãã1åã ãå®è¡ãã10人ã®ã¯ã©ã€ã¢ã³ãã«çµæãäžããããšã¯å¯èœã§ãããã?
åé¡ã¯ããã£ãã·ã¥ã®çµæãäžéããŒã¿ã®ãã£ãã·ã¥ããªãããšã§ãããªãã¬ãŒãã£ã³ã° ã·ã¹ãã ã«ã¯ããŒãž ãã£ãã·ã¥ãããããã£ã¹ã¯ããããŒã¿ãå床èªã¿åãããšã¯ã§ããŸããããæ®å¿µãªããšã«ãããŒã¿ã¯åŒãç¶ã解åãéã·ãªã¢ã«åãããã³ååŠçãããŸãã
äžéããŒã¿ããã£ãã·ã¥ããããåæ§ã®ã¯ãšãªãããçš®ã®ãã¥ãŒã«äžŠã¹ãŠçµæãã£ãã·ã¥ãè¿œå ããããšã§ãããããªããšãåé¿ããããšèããŠããŸããçŸåšããªã¯ãšã¹ã ãã£ãã·ã¥ãè¿œå ããéçºäžã®ãã« ãªã¯ãšã¹ãã 1 ã€ãããŸããããã㯠in ã»ã¯ã·ã§ã³ãš join ã»ã¯ã·ã§ã³ã®ãµãã¯ãšãªã®ã¿ã察象ãšããŠããŸããã€ãŸãããœãªã¥ãŒã·ã§ã³ã¯äžå®å šã§ãã
ããããç§ãã¡ããã®ãããªç¶æ³ã«çŽé¢ããŠããŸããç¹ã«æšæºçãªäŸã¯ãããŒãžåå²ãããã¯ãšãªã§ããå ±åæžããããæ°ããŒãžãããå¶é 10 ã®èŠæ±ããããŸããããããåãããšã§ãããå¶é 10,10 ã§ãããããããŸã次ã®ããŒãžããããŠåé¡ã¯ããªãæ¯åããã ãã®ããšãæ°ããã®ããšããããšã§ããããããä»ã®ãšãã解決çã¯ãªããåé¿ããæ¹æ³ããããŸããã
ClickHouse ã®é£ã«ãµã€ãã«ãŒãšããŠé
眮ããã代æ¿ãœãªã¥ãŒã·ã§ã³ããããŸã -
ããªã«ã»ã·ã¥ãŽã¡ã³ãïŒ ClickHouse ãããã·ã«ã¯ãçµã¿èŸŒã¿ã®ã¬ãŒã ãªããã¿ãŒãšçµã¿èŸŒã¿ã®çµæãã£ãã·ã¥ããããŸããåæ§ã®åé¡ã解決ãããŠãããããããã§å€ãã®èšå®ãè¡ãããŸããããããã·ã䜿çšãããšããªã¯ãšã¹ãããã¥ãŒã«å ¥ããããšã§ãªã¯ãšã¹ããå¶éãããããªã¯ãšã¹ã ãã£ãã·ã¥ã®åç¶æéãæ§æãããã§ããŸãããªã¯ãšã¹ããæ¬åœã«åãã§ããå Žåããããã·ã¯ãªã¯ãšã¹ããäœåºŠãéä¿¡ããŸãããClickHouse ã«éä¿¡ãããã®ã¯ 1 åã ãã§ãã
Nginx ã«ã¯ç¡æçã§ããã£ãã·ã¥ãããããããæ©èœããŸãã Nginx ã«ã¯ããªã¯ãšã¹ããåæã«å°çããå Žåããªã¯ãšã¹ããå®äºãããŸã§ä»ã®ãªã¯ãšã¹ãã®é床ãäœäžãããšããèšå®ããããŸãããã ããClickHouse Proxy ã§ã¯ã»ããã¢ãããã¯ããã«é©åã«è¡ãããŸãã ClickHouse å°çšã«ãç¹ã«ãããã®èŠæ±ã«åãããŠäœæããããããããé©ããŠããŸãããŸããåãä»ãã¯ç°¡ââåã§ãã
éåææäœããããªã¢ã©ã€ãºã ãã¥ãŒã«ã€ããŠã¯ã©ãã§ãã?
åçãšã³ãžã³ã®æäœãéåæã§ãããšããåé¡ããããŸããæåã«ããŒã¿ãæžã蟌ãŸãããã®åŸããŒã¿ã厩å£ããŸããããã€ãã®éåäœãå«ãå®äœåãããã¿ãã¬ããããµã€ã³ã®äžã«ååšããå Žåãéè€ãããã«æžã蟌ãŸããŸãããŸããè€éãªããžãã¯ããªãå ŽåãããŒã¿ã¯è€è£œãããŸããããã«ã€ããŠäœãã§ããã§ããããïŒ
æãããªè§£æ±ºçã¯ãããŸããããã¯ãéåææãããã¿æäœäžã«ç¹å®ã®ã¯ã©ã¹ã® matview ã«ããªã¬ãŒãå®è£ ããããšã§ããç¹å¹è¬ãåæ§ã®æ©èœãå®è£ ããèšç»ã¯ãããŸãã?
éè€æé€ãã©ã®ããã«æ©èœããããç解ããããšã¯äŸ¡å€ããããŸããããããã話ãããããšã¯è³ªåãšã¯é¢ä¿ãããŸãããã念ã®ããèŠããŠãããŠãã ããã
ã¬ããªã±ãŒããããããŒãã«ã«æ¿å ¥ããå Žåãæ¿å ¥ããããããã¯å šäœã®éè€æé€ãè¡ãããŸããåãè¡ã®åãæ°ãå«ãåããããã¯ãåãé åºã§åæ¿å ¥ãããšãããŒã¿ã¯éè€æé€ãããŸããæ¿å ¥ãããšãOKããšè¡šç€ºãããŸãããå®éã«ã¯1ãã±ããåã®ããŒã¿ãæžã蟌ãŸããã ãã§ãããéè€ããããšã¯ãããŸããã
ããã¯ç¢ºå®æ§ãåŸãããã«å¿ èŠã§ããæ¿å ¥äžã«ãOKãã衚瀺ãããå ŽåãããŒã¿ã¯æ¿å ¥ãããŠããŸãã ClickHouse ãããšã©ãŒãåãåã£ãå Žåã¯ãæ¿å ¥ãããªãã£ãããšãæå³ãããããæ¿å ¥ãç¹°ãè¿ãå¿ èŠããããŸãããã ããæ¿å ¥äžã«æ¥ç¶ãåæãããå Žåã¯ãããŒã¿ãæ¿å ¥ããããã©ãããããããŸãããå¯äžã®ãªãã·ã§ã³ã¯ãæ¿å ¥ãå床繰ãè¿ãããšã§ããããŒã¿ãå®éã«æ¿å ¥ããããããåæ¿å ¥ããå Žåããããã¯éè€æé€ãè¡ãããŸããããã¯éè€ãé¿ããããã«å¿ èŠã§ãã
ãŸãããããªã¢ã©ã€ãºã ãã¥ãŒãã©ã®ããã«æ©èœããããéèŠã§ããããŒã¿ãã¡ã€ã³ããŒãã«ã«æ¿å ¥ããããšãã«éè€æé€ãããå ŽåãããŒã¿ã¯ãããªã¢ã©ã€ãºããã¥ãŒã«ãå ¥ããŸããã
ããŠã質åã«ã€ããŠã§ããåã ã®è¡ãéè€ããŠèšé²ããŠãããããç¶æ³ã¯ããã«è€éã«ãªããŸããã€ãŸããè€è£œãããã®ã¯ããã¯å šäœã§ã¯ãªããç¹å®ã®è¡ã§ããããããã¯ããã¯ã°ã©ãŠã³ãã§æããããŸããŸããå®éãããŒã¿ã¯ã¡ã€ã³ããŒãã«ã§æããããŸããŸãããæããããŸããŠããªãããŒã¿ã¯ãããªã¢ã©ã€ãºããã¥ãŒã«ç§»åããããŒãžäžã«ãããªã¢ã©ã€ãºããã¥ãŒã«ã¯äœãèµ·ãããŸããããããªã¢ã©ã€ãºã ãã¥ãŒã¯æ¿å ¥ããªã¬ãŒã«ãããªãããã§ããä»ã®æäœäžã¯ããã以äžäœãèµ·ãããŸããã
ãããŠãç§ã¯ããã§ããªãã幞ãã«ããããšã¯ã§ããŸããããã®å Žåã«å¿ èŠãªã®ã¯ãå ·äœçãªè§£æ±ºçãæ¢ãããšã ãã§ããããšãã°ããããªã¢ã©ã€ãºã ãã¥ãŒã§åçããããšã¯å¯èœã§ããããŸããéè€æé€æ¹æ³ãåæ§ã«æ©èœããå¯èœæ§ããããŸãããããæ®å¿µãªãããåžžã«ãããšã¯éããŸãããéçŽããŠããå Žåã¯æ©èœããŸããã
ããªã«ã»ã·ã¥ãŽã¡ã³ãïŒ æã¯æŸèæã®å·¥äºããããŸãããåºåã®ã€ã³ãã¬ãã·ã§ã³ãååšãããšããåé¡ãããããªã¢ã«ã¿ã€ã ã§è¡šç€ºã§ããããŒã¿ãããã€ããããŸããããããã¯åãªãã€ã³ãã¬ãã·ã§ã³ã§ããããããéè€ããããšã¯ãã£ãã«ãããŸããããéè€ããå Žåã¯ãããã«ããŠãåŸã§æããããããšã«ãªããŸãããããŠãã¯ãªãã¯ããã®ã¹ããŒãªãŒå šäœãªã©ãåçŸã§ããªããã®ããããŸãããããããããã«ã§ãèŠããããšãæããŸããã
ãããªã¢ã©ã€ãºã ãã¥ãŒã¯ã©ã®ããã«äœæãããŸããã?çŽæ¥æžã蟌ãŸãããã¥ãŒããããŸãããçããŒã¿ã«æžã蟌ãŸãããããã¥ãŒã«æžã蟌ãŸãããããŸãããããã§ã¯ãããæç¹ã§ããŒã¿ãããŸãæ£ãããªããªã£ãããéè€ãããããããšããããŸããããŒãã«ã® 2 çªç®ã®éšåã§ã¯ããããªã¢ã©ã€ãºã ãã¥ãŒãšãŸã£ããåãããã«èŠããŸããã€ãŸããæ§é ãå®å šã«åäžã§ããæã ãããŒã¿ãåèšç®ããéè€ã®ãªãããŒã¿ãã«ãŠã³ããããããã®ããŒãã«ã«æžã蟌ã¿ãŸãã
API ã䜿çšããŸããããã㯠ClickHouse ã§ã¯æåã§ã¯æ©èœããŸããããããŠãAPI ã¯æ¬¡ã®ããã«ãªããŸããããŒãã«ã«æåŸã«è¿œå ããæ¥ä»ãååŸãããšãæ£ããããŒã¿ããã§ã«èšç®ãããŠããããšãä¿èšŒãããããããŒãã«ãšå¥ã®ããŒãã«ã«ãªã¯ãšã¹ããäœæãããŸãããªã¯ãšã¹ãã¯äžæ¹ããäžå®ã®æéãéžæããããäžæ¹ããã¯ãŸã èšç®ãããŠããªãæéãååŸããŸããããã¯æ©èœããŸãããClickHouse ã ãã§ã¯æ©èœããŸããã
ã¢ããªã¹ãçšããŠãŒã¶ãŒçšãªã©ãäœããã® API ãããå ŽåãååãšããŠãããã¯ãªãã·ã§ã³ã§ããããªãã¯åžžã«æ°ããŠããŸããåžžã«æ°ããŠããŸããããã¯ã1 æ¥ã« 1 åãŸãã¯å¥ã®æéã«å®è¡ã§ããŸããå¿ èŠã®ãªããéèŠã§ã¯ãªãç¯å²ãèªåã§éžæããŸãã
ClickHouseã«ã¯ããããã®ãã°ããããŸãããµãŒããŒã§èµ·ãã£ãŠããããšããã¹ãŠäžç®ã§ç¢ºèªããã«ã¯ã©ãããã°ããã§ãã?
ClickHouse ã«ã¯éåžžã«å€æ°ã®ããŸããŸãªãã°ãããããã®æ°ã¯å¢ãç¶ããŠããŸããæ°ããããŒãžã§ã³ã§ã¯ãäžéšã®æ©èœã¯ããã©ã«ãã§æå¹ã«ãªã£ãŠããŸãããå€ãããŒãžã§ã³ã§ã¯ãæŽæ°æã«æå¹ã«ããå¿ èŠããããŸãããããããã®æ°ã¯ãŸããŸãå¢ããŠããŸããæçµçã«ã¯ããµãŒããŒã§çŸåšäœãèµ·ãã£ãŠãããããããããäœããã®æŠèŠããã·ã¥ããŒãã§ç¢ºèªããããšèããŠããŸãã
ãããã®ãã°ãå®æåãšããŠè¡šç€ºããæ¢è£œã®ããã·ã¥ããŒãã®æ©èœããµããŒãããŠãã ClickHouse ããŒã ãŸãã¯å人ã®ããŒã ã¯ãããŸãã?çµå±ã®ãšãããClickHouse ã§ãã°ãèŠãã ãã§ãååã§ããããããããããã§ã«ããã·ã¥ããŒãã®åœ¢ã§æºåãããŠããã°ãéåžžã«ã¯ãŒã«ã«ãªãã§ããããç§ã¯ããããããã¯ãåããã§ãããã
ããã·ã¥ããŒãã¯ãããŸãããæšæºåãããŠããŸãããåœç€Ÿã§ã¯ãçŽ 60 ããŒã ã ClickHouse ã䜿çšããŠããŸãããæãäžæè°ãªã®ã¯ããã®å€ããèªåçšã«äœæããããã·ã¥ããŒããšãå°ãç°ãªãããã·ã¥ããŒããæã£ãŠããããšã§ããäžéšã®ããŒã ã¯ãå éšã® Yandex.Cloud ã€ã³ã¹ããŒã«ã䜿çšããŠããŸããå¿ èŠãªãã®ããã¹ãŠã§ã¯ãããŸããããæ¢è£œã®ã¬ããŒããããã€ããããŸããä»ã®äººã¯ç¬èªã®ãã®ãæã£ãŠããŸãã
Metrica ã®åå㯠Grafana ã«ç¬èªã®ããã·ã¥ããŒããæã£ãŠãããç§ã圌ãã®ã¯ã©ã¹ã¿ãŒçšã«ç¬èªã®ããã·ã¥ããŒããæã£ãŠããŸããã»ãªããã£ãã·ã¥ã®ãã£ãã·ã¥ããããªã©ã調ã¹ãŠããŸãããããŠããã«é£ããã®ã¯ã䜿çšããããŒã«ãç°ãªãããšã§ããç§ã¯ Graphite-web ãšããéåžžã«å€ãããŒã«ã䜿çšããŠããã·ã¥ããŒããäœæããŸããã圌ã¯å®å šã«éãã§ããç§ã¯ä»ã§ããã®æ¹æ³ã§äœ¿ã£ãŠããŸããããããã Grafana ã®æ¹ã䟿å©ã§çŸããã§ãããã
ããã·ã¥ããŒãã®åºæ¬çãªãã®ã¯åãã§ãããããã¯ã¯ã©ã¹ã¿ãŒã®ã·ã¹ãã ã¡ããªãã¯ã§ã: CPUãã¡ã¢ãªããã£ã¹ã¯ããããã¯ãŒã¯ããã®ä» - åæãªã¯ãšã¹ãæ°ãåæããŒãžæ°ã1 ç§ãããã®ãªã¯ãšã¹ãæ°ãMergeTree ããŒãã« ããŒãã£ã·ã§ã³ã®æ倧ãã£ã³ã¯æ°ãã¬ããªã±ãŒã·ã§ã³ ã©ã°ãã¬ããªã±ãŒã·ã§ã³ ãã¥ãŒ ãµã€ãºã1 ç§ãããã®æ¿å ¥è¡æ°ã1 ç§ãããã®æ¿å ¥ãããã¯æ°ããã°ããã§ã¯ãªãã¡ããªã¯ã¹ããååŸã§ããã®ã¯ããã ãã§ãã
ãŠã©ãžããŒã«ã»ã³ãããšã: ã¢ã¬ã¯ã»ã€ãå°ãèšæ£ããããšæããŸããã°ã©ãã¡ããããã Grafana ã«ã¯ ClickHouse ãšããããŒã¿ãœãŒã¹ããããŸããã€ãŸããGrafana ãã ClickHouse ã«çŽæ¥ãªã¯ãšã¹ããè¡ãããšãã§ããŸãã ClickHouse ã«ã¯ãã°ãå«ãããŒãã«ããããããã¯èª°ã«ãšã£ãŠãåãã§ãããã®çµæãGrafana ã§ãã®ãã° ããŒãã«ã«ã¢ã¯ã»ã¹ããŠããµãŒããŒãè¡ããªã¯ãšã¹ãã確èªããããšèããŠããŸãããããªããã·ã¥ããŒããããã°æé«ã§ããã
èªåã§èªè»¢è»ã«ä¹ããŸããããããã質åããããŸãããã¹ãŠãæšæºåãããŠããŠãGrafana ã誰ãã䜿çšããŠããã®ã§ããã°ããªã Yandex ã«ã¯ãã®ãããªå ¬åŒããã·ã¥ããŒãããªãã®ã§ãããã?
ããªã«ã»ã·ã¥ãŽã¡ã³ãïŒ å®éãClickHouse ã«éãããããŒã¿ãœãŒã¹ã¯ Altinity ããµããŒãããããã«ãªããŸããããããŠãã©ããæãã誰ãããã·ã¥ãããã®ãã¯ãã«ãäžãããã ãã§ãã Yandex ã¯ãŸã ClickHouse ãäœã£ãŠãããããã«ãŸã€ãã話ã¯ç¥ããªãã®ã§ã圌ãã«å°ããããšãã§ããŸãã Altinity ã¯çŸåš ClickHouse ãæšé²ããŠããäž»èŠäŒæ¥ã§ãã圌ãã¯åœŒãèŠæšãŠãããšã¯ãªãã圌ããµããŒãããŸããååãšããŠãããã·ã¥ããŒãã Grafana Web ãµã€ãã«ã¢ããããŒãããã«ã¯ãç»é²ããŠã¢ããããŒãããã ãã§æžã¿ãç¹å¥ãªåé¡ã¯ãããŸããã
ã¢ã¬ã¯ã»ã€ã»ãããŽã£ãã: éå» 1 幎éã«ããããClickHouse ã«ã¯å€ãã®ã¯ãšãª ãããã¡ã€ãªã³ã°æ©èœãè¿œå ãããŸããããªãœãŒã¹äœ¿çšéã«é¢ããåãªã¯ãšã¹ãã®ã¡ããªã¯ã¹ããããŸãããããŠã€ãæè¿ãããªç§ããšã«ã¯ãšãªãã©ãã«è²»ãããŠãããã確èªããããã«ãããã«äžäœã¬ãã«ã®ã¯ãšãª ãããã¡ã€ã©ãŒãè¿œå ããŸããããããããã®æ©èœã䜿çšããã«ã¯ãã³ã³ãœãŒã« ã¯ã©ã€ã¢ã³ããéããŠãªã¯ãšã¹ããå ¥åããå¿ èŠããããŸãããããããã€ãå¿ããŠããŸããŸããã©ããã«ä¿åããã®ã§ãããæ£ç¢ºãªå Žæãå¿ããŠããŸããŸãã
ãããã«éãã¯ãšãªããããã¯ãšãª ã¯ã©ã¹ããšã«ã°ã«ãŒãåãããŠããŸãããšããããŒã«ãããã°ããã®ã«ãšæããŸããäžã€æŒããŠã¿ããšãã ããéãã®ã ãšèšãããŸãããçŸåšãã®ãããªè§£æ±ºçã¯ãããŸããããããŠãæ¬åœã«äžæè°ãªã®ã¯ã人ã ãç§ã«ãæããŠãã ãããGrafana çšã®æ¢è£œã®ããã·ã¥ããŒãã¯ãããŸãã?ããšå°ãããšããç§ãããèšãã®ã§ãããGrafana ã® Web ãµã€ãã«ã¢ã¯ã»ã¹ãããšããããã·ã¥ããŒããã³ãã¥ããã£ããããããã·ã¥ããŒãããããŸãã Dimka ããã¯ãKostyan ããã®ããã·ã¥ããŒãããããŸããèªåã§äœ¿ã£ãããšããªãã®ã§ãäœãªã®ãåãããŸãããã
ãµãŒããŒã OOM ã«ã¯ã©ãã·ã¥ããªãããã«ããŒãžã«åœ±é¿ãäžããã«ã¯ã©ãããã°ããã§ãã?
ããŒãã«ããããŸããããŒãã«ã«ã¯ããŒãã£ã·ã§ã³ã 1 ã€ã ãããããã㯠ReplacingMergeTree ã§ãã 4幎éããŒã¿ãæžã蟌ã¿ç¶ããŠããŸããããã«å€æŽãå ããäžéšã®ããŒã¿ãåé€ããå¿ èŠããããŸããã
ãããå®è¡ãããšããããã®ãªã¯ãšã¹ãã®åŠçäžã«ã¯ã©ã¹ã¿ãŒå ã®ãã¹ãŠã®ãµãŒããŒäžã®ãã¹ãŠã®ã¡ã¢ãªãæ¶è²»ãããã¯ã©ã¹ã¿ãŒå ã®ãã¹ãŠã®ãµãŒããŒã OOM ç¶æ ã«ãªããŸããããã®åŸãå šå¡ãäžç·ã«ç«ã¡äžãã£ãŠãåãæäœããã®ããŒã¿ ãããã¯ã®ããŒãžãéå§ããåã³ OOM ã«é¥ããŸããããããã圌ãã¯åã³èµ·ãäžãããŸãããããŸãåããŸããããããŠãã®äºã¯æ¢ãŸããªãã£ãã
ãã®åŸãããã¯å®éã«ã¯ãã°ã§ããã圌ããä¿®æ£ããããšãå€æããŸããããšãŠãçŽ æµã§ãããããããšãããããŸãããããæ®çç©ãæ®ã£ããããŠãããŒãã«å ã§äœããã®ããŒãžãè¡ãããšãèãããšãçåãçããŸãããªããããã®ããŒãžã«äœããã®åœ±é¿ãäžããããšãã§ããªãã®ã§ãããã?ããšãã°ãå¿ èŠãª RAM ã®éã«ãã£ãŠããŸãã¯ååãšããŠããã®ç¹å®ã®ããŒãã«ãåŠçããéã«ãã£ãŠå¶éããŸãã
ãMetricsããšããããŒãã«ãããã®ã§ã2 ã€ã®ã¹ã¬ããã§åŠçããŠãã ããã 10 åãŸã㯠5 åã®ããŒãžã䞊è¡ããŠäœæããå¿ èŠã¯ãªãã2 åã«åããŠå®è¡ããŸããã¡ã¢ãªã¯2ååã¯ãããšæããŸããã10ååŠçããã«ã¯è¶³ããªããããããŸããããªãææãæ®ãã®ã§ããããïŒãªããªããããŒãã«ã¯æé·ãç¶ããŠããããã€ããåççã«ã¯ãã°ã«ãããã®ã§ã¯ãªããããŒã¿ã倧éã«å€æŽããããããããŒãã«äžã®ååãªã¡ã¢ãªãäžè¶³ãããšããç¶æ³ã«çŽé¢ããããšã«ãªãããã§ãããµãŒãããããŠãããŒãžæã«ãµãŒããŒã OOM ã«ã¯ã©ãã·ã¥ããŸãããããå€ç°ã¯è§£é€ã§ããã®ã§ãããã¡ã«ãžãããããŸããã
ãåç¥ã®ãšãããããŒãžæã« RAM ã®é㯠1 ã€ã®çãç¯å²ã®ããŒã¿ã«ã®ã¿äœ¿çšãããããããµãŒããŒã¯ OOM ã«é¥ããŸããããããã£ãŠãããŒã¿éã«é¢ä¿ãªãããã¹ãŠãããŸããããŸãã
ãŠã©ãžããŒã«ã»ã³ãããšã: 倧äžå€«ãããã§ããã°ãä¿®æ£ãããåŸãèªåçšã«æ°ããããŒãžã§ã³ãããŠã³ããŒãããå€ãã®ããŒãã£ã·ã§ã³ãããå¥ã®å°ããªããŒãã«ã§åæ§ã®æäœãå®è¡ããŸããããŸããããŒãžäžã«ãµãŒããŒäžã§çŽ 100 GB ã® RAM ãæ¶è²»ãããŸããã 150 åãå æããã100 åãé£ã¹ããã50 GB ã®ãŠã£ã³ããŠãæ®ã£ãŠãããããOOM ã«ã¯é¥ããŸããã§ããã
å®éã« 100 GB ã® RAM ãæ¶è²»ããå Žåã« OOM ã«é¥ãã®ãé²ãã«ã¯ãçŸåšäœãå¿ èŠã§ãã?ããŒãžäžã® RAM ãçªç¶è¶³ããªããªã£ãå Žåã¯ã©ãããã°ããã§ãããã?
ã¢ã¬ã¯ã»ã€ã»ãããŽã£ãã: ããŒãžå°çšã®RAMã®æ¶è²»éãå¶éãããŠããªããšããåé¡ãããã 2 çªç®ã®åé¡ã¯ãäœããã®ããŒãžãå²ãåœãŠãããŠããå Žåããããã¬ããªã±ãŒã·ã§ã³ ãã°ã«èšé²ãããããããããå®è¡ããå¿ èŠãããããšã§ããã¬ããªã±ãŒã·ã§ã³ ãã°ã¯ãã¬ããªã«ãäžè²«ããç¶æ ã«ããããã«å¿ èŠãªã¢ã¯ã·ã§ã³ã§ãããã®ã¬ããªã±ãŒã·ã§ã³ ãã°ãããŒã«ããã¯ããæåæäœãè¡ããªãå Žåã¯ãäœããã®æ¹æ³ã§ããŒãžãå®è¡ããå¿ èŠããããŸãã
ãã¡ããããäžãäžã«åããŠãOOM ããä¿è·ããããã« RAM å¶éãèšããŠããããšã¯äžå¿ èŠã§ã¯ãããŸãããããã§ã¯ããŒãžã®å®äºã«ã¯åœ¹ç«ã¡ãŸãããããŒãžã¯åã³éå§ãããäœããã®ãããå€ã«éããäŸå€ãã¹ããŒããããã®åŸåã³éå§ãããŸããããã§ã¯äœãè¯ãããšã¯ãããŸãããããããååãšããŠããã®å¶éãå°å ¥ããããšã¯æçã§ãã
ClickHouse çšã® Golang ãã©ã€ããŒã¯ã©ã®ããã«éçºãããŸãã?
Kirill Shvakov ã«ãã£ãŠäœæããã Golang ãã©ã€ããŒã¯ãçŸåš ClickHouse ããŒã ã«ãã£ãŠæ£åŒã«ãµããŒããããŠããŸãã圌
ClickHouse ãªããžããªå ã圌ã¯ä»å€§ãããŠæ¬ç©ã§ããã¡ãã£ãšããã¡ã¢ãç¡éã®ç§©åºã®éåžžã®åœ¢åŒãåããçŽ æŽããããæãããŠãããªããžããªããããŸã - ããã Vertica ã§ãããŸããVertica éçºè ã«ãã£ãŠãµããŒããããŠããç¬èªã®å ¬åŒ Python ãã©ã€ããŒããããŸãããŸããã¹ãã¬ãŒãžã®ããŒãžã§ã³ãšãã©ã€ããŒã®ããŒãžã§ã³ãå€§å¹ ã«ç°ãªããããæç¹ã§ãã©ã€ããŒãåäœããªããªãããšãäœåºŠããããŸããããããŠ2ç¹ç®ããã®å ¬åŒãã©ã€ããŒã®ãµããŒãã¯ãããããã«ãã·ã¹ãã ã«ãã£ãŠå®è¡ãããŠããããã«æããŸããåé¡ãæžã蟌ããšãæ°žä¹ ã«ãã³ã°ã¢ããããŸãã
質åã2ã€ãããŸããçŸåšãKirill ã® Golang ãã©ã€ããŒã¯ãGolang ãã ClickHouse ãšéä¿¡ããããã®ã»ãŒããã©ã«ãã®æ¹æ³ã§ãã http ã€ã³ã¿ãŒãã§ãŒã¹ã奜ãã§ä»ã§ã http ã€ã³ã¿ãŒãã§ãŒã¹çµç±ã§éä¿¡ããŠãã人ã¯å¥ã§ããããã®ãã©ã€ããŒã®éçºã¯ã©ã®ããã«é²ããããã®ã§ããããïŒãªããžããªèªäœã®é倧ãªå€æŽãšåæãããŸãã?ãŸããåé¡ãæ€èšããæé ã¯äœã§ãã?
ããªã«ã»ã·ã¥ãŽã¡ã³ãïŒ 1 ã€ç®ã¯ããã¹ãŠãå®åçã«ã©ã®ããã«çµç¹ãããŠããããšããããšã§ãããã®ç¹ã«ã€ããŠã¯è°è«ãããŠããªãã®ã§ããçãããããšã¯ãããŸããã
ãã®åé¡ã«é¢ãã質åã«çããã«ã¯ããã©ã€ããŒã®ã¡ãã£ãšããå±¥æŽãå¿ èŠã§ããç§ã¯å€§éã®ããŒã¿ãæ±ãäŒç€Ÿã§åããŠããŸãããããã¯ãã©ããã«ä¿åããå¿ èŠãããèšå€§ãªæ°ã®ã€ãã³ããå«ãåºåã¹ãããŒã§ããããããŠããæç¹ã§ClickHouseãç»å ŽããŸãããããŒã¿ãå ¥åãããšãããæåã¯ãã¹ãŠåé¡ãããŸããã§ãããããã®åŸ ClickHouse ãã¯ã©ãã·ã¥ããŸããããã®ç¬éãç§ãã¡ã¯ãããå¿ èŠãªããšå€æããŸããã
1 幎åŸãç§ãã¡ã¯ ClickHouse ã䜿çšãããšããèãã«æ»ããäœããã®æ¹æ³ã§ããã«ããŒã¿ãæžã蟌ãå¿ èŠããããŸãããå°å ¥ã¡ãã»ãŒãžã¯æ¬¡ã®ãšããã§ãããããŒããŠã§ã¢ã¯éåžžã«åŒ±ãããªãœãŒã¹ã¯ã»ãšãã©ãããŸãããããããç§ãã¡ã¯åžžã«ãã®æ¹æ³ã§äœæ¥ããŠããããããã€ãã£ã ãããã³ã«ã«æ³šç®ããŸããã
ç§ãã¡ã¯ Go ã§äœæ¥ããŠããã®ã§ãGo ãã©ã€ããŒãå¿ èŠã§ããããšã¯æããã§ãããç§ã¯ã»ãŒãã«ã¿ã€ã ã§ãããè¡ããŸãã - ããã¯ç§ã®ä»äºã§ãããç§ãã¡ã¯ãããããæç¹ãŸã§æã£ãŠããŸããããåççã«ã¯ç§ãã¡ä»¥å€ã®èª°ããããã䜿çšããããšã誰ãæ³å®ããŠããŸããã§ããããã®åŸãCloudFlare ããŸã£ããåãåé¡ãæ±ããŠããŸãããã圌ãã¯åãã¿ã¹ã¯ãæ±ããŠããããããã°ããã®éã¯éåžžã«ã¹ã ãŒãºã«é£æºã§ããŸãããããã«ãããã ClickHouse èªèº«ãšãã©ã€ããŒã®äž¡æ¹ã§å®è¡ããŸããã
ClickHouse ãšä»äºã«é¢ããç§ã®æŽ»åãå°ãå€ãã£ããããããæç¹ã§ããããããŸããããããã£ãŠãåé¡ã¯è§£æ±ºãããŠããŸãããå®æçã«ãèªåã§äœããå¿ èŠãšãã人ããªããžããªã«ã³ãããããŸãããã®åŸããã« ãªã¯ãšã¹ãã確èªããå Žåã«ãã£ãŠã¯èªåã§ç·šéããããšããããŸãããããã¯ãã£ãã«èµ·ãããŸããã
ãã©ã€ããŒã«æ»ãããã§ããæ°å¹Žåããã®ãã¹ãŠãå§ãŸã£ããšããClickHouse ãç°ãªã£ãŠãããç°ãªãæ©èœãåããŠããŸãããããã§ããã©ã€ããŒãé©åã«åäœããããã«ãã©ã€ããŒãåäœæããæ¹æ³ãããããŸããããããªããšãèç©ãããæŸèæã®ããã§ãããŒãžã§ã³ 2 ã¯ãããã«ããŠãäºææ§ããªããªããŸãã
ãã®åé¡ãã©ãæŽçããŠãããããããŸãããç§èªèº«ã«ã¯ããŸãæéããããŸããããããã©ã€ããŒãçµãã人ãããããç§ã¯åœŒããå©ããäœããã¹ãããæããããšãã§ããŸãããããããããžã§ã¯ãã®éçºã«ãããYandexã®ç©æ¥µçãªåå ã«ã€ããŠã¯ãŸã è°è«ãããŠããŸããã
ã¢ã¬ã¯ã»ã€ã»ãããŽã£ãã: å®éããããã®ãã©ã€ããŒã«é¢ããå®åå¶åºŠã¯ãŸã ãããŸãããå¯äžã®ããšã¯ããã©ã€ããŒãå ¬åŒçµç¹ã«æåºãããŠãããšããããšã§ããã€ãŸãããã®ãã©ã€ããŒã¯ Go ã®å ¬åŒã®ããã©ã«ã ãœãªã¥ãŒã·ã§ã³ãšããŠèªèãããŠããŸããä»ã«ãããã€ãã®ãã©ã€ããŒããããŸããããããã¯åå¥ã«æäŸãããŸãã
ãããã®ãã©ã€ããŒã瀟å ã§éçºããããšã¯ãããŸãããåé¡ã¯ããã®ç¹å®ã®ãã©ã€ããŒã®ããã§ã¯ãªããå°åã®ãã¹ãŠã®ãã©ã€ããŒã®è²æã®ããã«å人ãéçšã§ãããã©ããããããšãå€éšãã誰ããèŠã€ããããšãã§ããããšããããšã§ãã
Lazy_load èšå®ãæå¹ã«ããŠåèµ·åãããšãå€éšèŸæžãããŒããããŸãããäœããããïŒ
Lazy_load èšå®ãæå¹ã«ãªã£ãŠããããµãŒããŒãåèµ·åãããåŸãèŸæžã¯èªåçã«ããŒããããŸããããŠãŒã¶ãŒããã®èŸæžã«ã¢ã¯ã»ã¹ããåŸã«ã®ã¿çºçããŸãããããŠãåããŠã¢ã¯ã»ã¹ãããšãšã©ãŒãçºçããŸãã ClickHouse ã䜿çšããŠäœããã®æ¹æ³ã§èŸæžãèªåçã«ããŒãããããšã¯å¯èœã§ããããããšããŠãŒã¶ãŒããšã©ãŒãåä¿¡ããªãããã«èŸæžã®æºåç¶æ³ãåžžã«èªåã§å¶åŸ¡ããå¿ èŠããããŸãã?
ãããããClickHouse ã®ããŒãžã§ã³ãå€ããããèŸæžãèªåçã«èªã¿èŸŒãŸããŸããã§ããããããªããšãããåŸãã§ããããïŒ
ãŸããã¯ãšãªã䜿çšããŠèŸæžã匷å¶çã«ããŒãã§ããŸãã ã·ã¹ãã ãªããŒãèŸæžã次ã«ããšã©ãŒã«é¢ããŠã§ãããèŸæžããã§ã«ããŒããããŠããå Žåãã¯ãšãªã¯ããŒããããããŒã¿ã«åºã¥ããŠæ©èœããŸããèŸæžããŸã ããŒããããŠããªãå Žåã¯ããªã¯ãšã¹ãäžã«çŽæ¥ããŒããããŸãã
ããã¯éãèŸæžã«ã¯ããŸã䟿å©ã§ã¯ãããŸãããããšãã°ãMySQL ãã 100 äžè¡ãååŸããå¿ èŠããããŸãã誰ããåçŽãªéžæãè¡ããŸããããã®éžæã¯åã 100 äžè¡ãåŸ æ©ããŸããããã«ã¯ 2 ã€ã®è§£æ±ºçããããŸãã 1 ã€ç®ã¯ãlazy_load ããªãã«ããããšã§ãã次ã«ããµãŒããŒãèµ·åãããããµãŒããŒã«è² è·ããããåã«ã次ã®ããšãè¡ããŸãã ã·ã¹ãã ãªããŒãèŸæž ãŸãã¯ãèŸæžã䜿çšããã¯ãšãªãå®è¡ããã ãã§ãããããšèŸæžãèªã¿èŸŒãŸããŸãã ClickHouse ã¯èŸæžãèªåçã«ããŒãããªããããlazy_load èšå®ãæå¹ã«ããŠèŸæžã®å¯çšæ§ãå¶åŸ¡ããå¿ èŠããããŸãã
æåŸã®è³ªåã«å¯Ÿããçãã¯ãããŒãžã§ã³ãå€ããããããã°ãå¿ èŠãã®ã©ã¡ããã§ãã
å°ãªããšã 1 ã€ã®èŸæžããšã©ãŒã§ã¯ã©ãã·ã¥ããå Žåãã·ã¹ãã ã®ãªããŒãèŸæžãã©ããèªã¿èŸŒãŸãªããšããäºå®ãã©ãããã°ããã§ãããã?
ã·ã¹ãã ã®ãªããŒãèŸæžã«ã€ããŠã¯å¥ã®è³ªåããããŸãã 2 ã€ã®èŸæžããããŸãã1 ã€ã¯ããŒããããŠãããã2 ã€ç®ã¯ããŒããããŠããŸãããã®å Žåãã·ã¹ãã ãªããŒãèŸæžã¯èŸæžãããŒãããªããããã·ã¹ãã ãªããŒãèŸæžã䜿çšããŠååã«ãã£ãŠç¹å®ã®èŸæžããã€ã³ããã€ãã€ã³ãã§ããŒãããå¿ èŠããããŸãããããClickHouseã®ããŒãžã§ã³ã«é¢ä¿ããã®ã§ããããïŒ
ããªãã幞ãã«ãããã§ãããã®è¡åã¯å€ããã€ã€ãããŸãããããã¯ãClickHouse ãæŽæ°ãããšããããå€æŽãããããšãæå³ããŸããçŸåšã®èªåã®è¡åã«æºè¶³ããŠããªãå Žå ã·ã¹ãã ãªããŒãèŸæžãã¢ããããŒãããŠãè¯ãæ¹åã«å€ããããšãç¥ããŸãããã
ClickHouse æ§æã§è©³çŽ°ãæ§æãããããšã©ãŒãçºçããå Žåã«ã¯è¡šç€ºããªãæ¹æ³ã¯ãããŸãã?
次ã®è³ªåã¯ãèŸæžã«é¢ãããšã©ãŒãã€ãŸã詳现ã«ã€ããŠã§ããèŸæžã® ClickHouse æ§æã§æ¥ç¶ã®è©³çŽ°ãæå®ããŸããããšã©ãŒãããå Žåã¯ãå¿çãšããŠãããã®è©³çŽ°ãšãã¹ã¯ãŒããåãåããŸãã
ODBC ãã©ã€ããŒèšå®ã«è©³çŽ°ãè¿œå ããããšã§ããã®ãšã©ãŒã解決ããŸããã ClickHouse æ§æã§è©³çŽ°ãæ§æãããšã©ãŒãçºçããå Žåã«ãããã®è©³çŽ°ã衚瀺ããªãæ¹æ³ã¯ãããŸãã?
ããã§ã®å®éã®è§£æ±ºçã¯ãodbc.ini ã§ãããã®è³æ Œæ å ±ãæå®ããClickHouse èªäœã§ã¯ ODBC ããŒã¿ ãœãŒã¹åã®ã¿ãæå®ããããšã§ããä»ã®ãã£ã¯ã·ã§ã㪠ãœãŒã¹ã§ã¯ããã¯èµ·ãããŸãããMySQL ã®ãã£ã¯ã·ã§ããªã§ãããã®ä»ã®ãã£ã¯ã·ã§ããªã§ãããšã©ãŒ ã¡ãã»ãŒãžãåãåã£ããšãã«ãã¹ã¯ãŒãã衚瀺ãããã¯ãã¯ãããŸããã ODBC ã«ã€ããŠã調ã¹ãŸããååšããå Žåã¯åé€ããã ãã§ãã
ããŒãã¹: éäŒããã® Zoom ã®èæ¯
ç»åãã¯ãªãã¯ãããšãç±å¿ãªèªè ã®ããã«éäŒã®ããŒãã¹èæ¯ãéããŸããç§ãã¡ã¯ Avito ãã¯ãããžãŒã®ãã¹ã³ãããã¡ãšäžç·ã«ç«ãæ¶ããã·ã¹ãã 管çè 宀ãæãªããã®ã³ã³ãã¥ãŒã¿ãŒã¯ã©ãã®ååãšè©±ãåããèœæžããèæ¯ã«æ©ã®äžã§æ¯æ¥ããŒãã£ã³ã°ãè¡ã£ãŠããŸãã
åºæïŒ habr.com