Data Egret ã® Alexey Lesovsky æ°ã«ããã¬ããŒããPostgreSQL ã¢ãã¿ãªã³ã°ã®åºç€ãã®ãã©ã³ã¹ã¯ãªãããèªãããšããå§ãããŸãã
ãã®ã¬ããŒãã§ã¯ãAlexey Lesovsky ããpost-gress çµ±èšã®éèŠãªãã€ã³ãããã®æå³ãããã³ç£èŠã«çµ±èšãååšããå¿ èŠãããçç±ã«ã€ããŠèª¬æããŸãã ã¢ãã¿ãªã³ã°ã«ã©ã®ãããªã°ã©ããå«ããã¹ãããããããè¿œå ããæ¹æ³ãããã³ãããã解éããæ¹æ³ã«ã€ããŠã ãã®ã¬ããŒãã¯ãPostgres ã®ãã©ãã«ã·ã¥ãŒãã£ã³ã°ã«é¢å¿ã®ããããŒã¿ããŒã¹ç®¡çè ãã·ã¹ãã 管çè ãéçºè ã«ãšã£ãŠåœ¹ç«ã¡ãŸãã
ç§ã®åå㯠Alexey LesovskyãData Egret 瀟ã®ä»£è¡šã§ãã
ç§èªèº«ã«ã€ããŠå°ãã ç§ã¯ãã£ãšåã«ã·ã¹ãã 管çè ãšããŠåãå§ããŸããã
ç§ã¯ããããçš®é¡ã®ããŸããŸãª Linux ã·ã¹ãã ã管çããä»®æ³åãç£èŠããããã·ã®æäœãªã©ãLinux ã«é¢é£ããããŸããŸãªäœæ¥ã«åãçµã¿ãŸãããããããããæç¹ããããŒã¿ããŒã¹ãPostgreSQL ãããå€ã䜿çšãå§ããŸããã ç§ã¯åœŒã®ããšãæ¬åœã«å¥œãã§ããã ãããŠããæç¹ãããå€åæéã®ã»ãšãã©ã PostgreSQL ã§äœæ¥ããããã«ãªããŸããã ããããŠç§ã¯åŸã ã« PostgreSQL DBA ã«ãªããŸããã
ãããŠãç§ã®ãã£ãªã¢ãéããŠãç§ã¯åžžã«çµ±èšãç£èŠãé é枬å®ã®ãããã¯ã«èå³ãæã£ãŠããŸããã ç§ãã·ã¹ãã 管çè
ã ã£ããšããZabbix ãšéåžžã«ç·å¯ã«é£æºããŠããŸããã ãããŠã次ã®ãããªå°ããªã¹ã¯ãªããã»ãããæžããŸãã
ä»ã¯PostgreSQLã«åãçµãã§ããŸãã ç§ã¯ãã§ã«ãPostgreSQL çµ±èšãæäœã§ããããã«ããå¥ã®èšäºãæžããŠããŸãã ãããã
ã¡ãã£ãšãã玹ä»æã ç§ãã¡ã®é¡§å®¢ãã¯ã©ã€ã¢ã³ãã¯ã©ã®ãããªç¶æ³ã«ãããŸããïŒ ããŒã¿ããŒã¹ã«é¢é£ããŠäœããã®äºæ ãçºçããŸããã ãããŠãããŒã¿ããŒã¹ããã§ã«åŸ©å ãããŠãããšãéšéã®è²¬ä»»è ãŸãã¯éçºè²¬ä»»è ããã£ãŠæ¥ãŠããçãããç§ãã¡ã¯ããŒã¿ããŒã¹ãç£èŠããå¿ èŠããããŸããäœãæªãããšãèµ·ãã£ãã®ã§ãä»åŸãã®ãããªããšãèµ·ãããªãããã«ããå¿ èŠããããŸããããšèšããŸããã ãããŠãããããããŒã¿ããŒã¹ (PostgreSQLãMySQLããŸãã¯ãã®ä») ãç£èŠã§ããããã«ç£èŠã·ã¹ãã ãéžæããããæ¢åã®ç£èŠã·ã¹ãã ãé©å¿ããããšããèå³æ·±ãããã»ã¹ãå§ãŸããŸãã ãããŠååãã¡ã¯æ¬¡ã®ããã«ææ¡ãå§ããŸãããããããã®ããŒã¿ããŒã¹ããããšèããŸããã 䜿ã£ãŠã¿ãŸããããã ååå士ãå£è«ãå§ããŸãã ãããŠæçµçã«ã¯ãããçš®ã®ããŒã¿ããŒã¹ãéžæããããšã«ãªããŸããããã®äžã§ã® PostgreSQL ç£èŠã¯ããªã貧匱ã«è¡šç€ºãããåžžã«äœããè¿œå ããå¿ èŠããããŸãã GitHub ãããªããžããªãããã€ãååŸããã¯ããŒã³ãäœæããã¹ã¯ãªããã調æŽããŠãäœããã®æ¹æ³ã§ã«ã¹ã¿ãã€ãºããŸãã ãããŠæçµçã«ã¯ããçš®ã®æäœæ¥ã«ãªãã®ã§ãã
ãããã£ãŠããã®è¬æŒã§ã¯ãPostgreSQL ã ãã§ãªãããŒã¿ããŒã¹ã®ç£èŠãéžæããæ¹æ³ã«ã€ããŠã®ç¥èãæäŸããããšæããŸãã ãŸããç£èŠãå®äºããŠäœããã®å©çãåŸãããšãã§ããããã«ããããã®ç¥èãæäŸããŸããããã«ãããä»åŸçºçããå¯èœæ§ã®ããç·æ¥äºæ ãè¿ éã«é²ãããã«ãããŒã¿ããŒã¹ãæçã«ç£èŠã§ããããã«ãªããŸãã
ãããŠããã®ã¬ããŒãã«å«ãŸããã¢ã€ãã¢ã¯ãDBMS ã§ãã£ãŠã noSQL ã§ãã£ãŠããããããããŒã¿ããŒã¹ã«çŽæ¥é©çšã§ããŸãã ãããã£ãŠãPostgreSQL ã ãã§ãªããPostgreSQL ã§ãããè¡ãæ¹æ³ã«ã€ããŠã¯å€ãã®ã¬ã·ããååšããŸãã ã¯ãšãªã®äŸãPostgreSQL ãç£èŠã®ããã«æã€ãšã³ãã£ãã£ã®äŸããããŸãã ãŸããDBMS ã«ã¢ãã¿ãªã³ã°ãå¯èœã«ããåãæ©èœãããå Žåã¯ããããã調æŽããŠè¿œå ããããšãã§ããŸãã
ã¬ããŒãã«ã¯èŒã£ãŠãªãã
ã¡ããªã¯ã¹ãé
ä¿¡ããã³ä¿åããæ¹æ³ã«ã€ããŠèª¬æããŸãã ããŒã¿ã®åŸåŠçãšãŠãŒã¶ãŒãžã®æ瀺ã«ã€ããŠã¯äœãèšããŸããã èŠåã«ã€ããŠã¯äœãèšããŸããã
ããããã¹ããŒãªãŒãé²ãã«ã€ããŠãæ¢åã®ç£èŠã®ããŸããŸãªã¹ã¯ãªãŒã³ã·ã§ãããèŠããŠãäœããã®åœ¢ã§æ¹å€ããŸãã ãã ãããããã®è£œåã«å¯Ÿããåºåãã¢ã³ãåºåãäœæããªãããã«ãç§ã¯ãã©ã³ãã®ååãåºããªãããã«ããŸãã ãããã£ãŠããã¹ãŠã®å¶ç¶ã¯ã©ã³ãã ã§ãããããªãã®æ³ååã«ä»»ãããŠããŸãã
ãŸããã¢ãã¿ãªã³ã°ãšã¯äœããç解ããŸãããã ã¢ãã¿ãªã³ã°ã¯éåžžã«éèŠãªããšã§ãã 誰ãããããç解ããŠããŸãã ãããåæã«ãã¢ãã¿ãªã³ã°ã¯ããžãã¹è£œåã«é¢é£ããŠããããäŒç€Ÿã®å©çã«çŽæ¥åœ±é¿ãäžãããã®ã§ã¯ãªããããåžžã«æ®äœããŒã¹ã®ã¢ãã¿ãªã³ã°ã«æéãå²ãåœãŠãããŸãã æéãããã°ã¢ãã¿ãªã³ã°ãè¡ããŸãããæéããªããã°ããã¯ãã°ã«å
¥ããŠããã€ããããã®ã¿ã¹ã¯ã«æ»ããŸãã
ãããã£ãŠãç§ãã¡ã®å®åããèšãã°ãã¯ã©ã€ã¢ã³ãã蚪åãããšãã®ã¢ãã¿ãªã³ã°ã¯äžå®å šã§ããããšãå€ããããŒã¿ããŒã¹ãããé©åã«åŠçããã®ã«åœ¹ç«ã€ãããªèå³æ·±ãå 容ãå«ãŸããŠããªãããšããããããŸãã ãããã£ãŠãç£èŠã¯åžžã«å®äºããå¿ èŠããããŸãã
ããŒã¿ããŒã¹ã¯æ å ±ã®ãªããžããªã§ãããããããŒã¿ããŒã¹ã¯éåžžã«è€éã§ãããç£èŠããå¿ èŠããããŸãã ãŸããæ å ±ã¯äŒæ¥ã«ãšã£ãŠéåžžã«éèŠã§ããã決ããŠå€±ãããšã¯ã§ããŸããã ãããåæã«ãããŒã¿ããŒã¹ã¯éåžžã«è€éãªãœãããŠã§ã¢ã§ãã ãããã¯å€æ°ã®ã³ã³ããŒãã³ãã§æ§æãããŠããŸãã ãããŠããããã®ã³ã³ããŒãã³ãã®å€ãã¯ç£èŠããå¿ èŠããããŸãã
ç¹ã« PostgreSQL ã«ã€ããŠè©±ããŠããå Žåãå€æ°ã®ã³ã³ããŒãã³ãã§æ§æãããã¹ããŒã ã®åœ¢åŒã§è¡šãããšãã§ããŸãã ãããã®ã³ã³ããŒãã³ãã¯çžäºã«äœçšããŸãã åæã«ãPostgreSQL ã«ã¯ããããã Stats Collector ãµãã·ã¹ãã ããããããã䜿çšãããšããããã®ãµãã·ã¹ãã ã®åäœã«é¢ããçµ±èšãåéãã管çè ãŸãã¯ãŠãŒã¶ãŒããããã®çµ±èšã衚瀺ã§ããããã«ãããçš®ã®ã€ã³ã¿ãŒãã§ã€ã¹ãæäŸã§ããŸãã
ãããã®çµ±èšã¯ãç¹å®ã®é¢æ°ãšãã¥ãŒã®ã»ããã®åœ¢åŒã§è¡šç€ºãããŸãã ããŒãã«ãšåŒã¶ããšãã§ããŸãã ã€ãŸããéåžžã® psql ã¯ã©ã€ã¢ã³ãã䜿çšããŠããŒã¿ããŒã¹ã«æ¥ç¶ãããããã®é¢æ°ãšãã¥ãŒãéžæããŠãPostgreSQL ãµãã·ã¹ãã ã®åäœã«é¢ããç¹å®ã®æ°å€ãååŸã§ããŸãã
ãããã®æ°å€ããæ°ã«å ¥ãã®ç£èŠã·ã¹ãã ã«è¿œå ããã°ã©ããæç»ããæ©èœãè¿œå ããŠãé·æçãªåæãååŸã§ããŸãã
ãã ããäžžäžæ¥ãããå¯èœæ§ãããããããã®ã¬ããŒãã§ã¯ããããã¹ãŠã®æ©èœãå®å
šã«èª¬æããããšã¯ããŸããã æåéããXNUMX ã€ãXNUMX ã€ããŸã㯠XNUMX ã€ã®ããšã«ã€ããŠåãäžããããããç£èŠãããè¯ãããã®ã«ã©ã®ããã«åœ¹ç«ã€ãã説æããŸãã
ããŒã¿ããŒã¹ã®ç£èŠã«ã€ããŠè©±ãå Žåãäœãç£èŠããå¿
èŠãããã®ã§ãããã? ãŸã第äžã«ãå¯çšæ§ãç£èŠããå¿
èŠããããŸããããŒã¿ããŒã¹ã¯ã¯ã©ã€ã¢ã³ãã«ããŒã¿ãžã®ã¢ã¯ã»ã¹ãæäŸãããµãŒãã¹ã§ãããå¯çšæ§ãç£èŠãããã®å®æ§çããã³å®éçç¹æ§ã®äžéšãæäŸããå¿
èŠãããããã§ãã
ãŸããããŒã¿ããŒã¹ã«æ¥ç¶ããã¯ã©ã€ã¢ã³ãã¯éåžžã®ã¯ã©ã€ã¢ã³ãã§ããå Žåãããã°ãããŒã¿ããŒã¹ã«æ害ãäžããå¯èœæ§ãããæ害ãªã¯ã©ã€ã¢ã³ãã§ããå Žåããããããç£èŠããå¿ èŠããããŸãã ãŸãã圌ããç£èŠãããã®æŽ»åã远跡ããå¿ èŠããããŸãã
ã¯ã©ã€ã¢ã³ããããŒã¿ããŒã¹ã«æ¥ç¶ãããšãã¯ã©ã€ã¢ã³ããããŒã¿ã®æäœãéå§ããããšã¯æãããªã®ã§ãã¯ã©ã€ã¢ã³ããã©ã®ããã«ããŒã¿ãæäœããããã€ãŸãã©ã®ããŒãã«ãæäœãããããŸãçšåºŠã¯äœãã§ããã©ã®ã€ã³ããã¯ã¹ãæäœããããç£èŠããå¿ èŠããããŸãã ã€ãŸããã¯ã©ã€ã¢ã³ãã«ãã£ãŠäœæãããã¯ãŒã¯ããŒããè©äŸ¡ããå¿ èŠããããŸãã
ãã ããã¯ãŒã¯ããŒãã«ã¯ãåœç¶ã®ããšãªãããªã¯ãšã¹ããå«ãŸããŸãã ã¢ããªã±ãŒã·ã§ã³ã¯ããŒã¿ããŒã¹ã«æ¥ç¶ããã¯ãšãªã䜿çšããŠããŒã¿ã«ã¢ã¯ã»ã¹ããŸãããã®ãããããŒã¿ããŒã¹å ã«ã©ã®ãããªã¯ãšãªãããããè©äŸ¡ãããã®é©åæ§ãç£èŠããã¯ãšãªãäžæ£ã«èšè¿°ãããŠããªãããšãããé«éã«åäœããããã«ããã€ãã®ãªãã·ã§ã³ãæžãæããŠäœæããå¿ èŠãããããšãç£èŠããããšãéèŠã§ãããããŠããè¯ãããã©ãŒãã³ã¹ã§ã
ããã§è©±ããŠããã®ã¯ããŒã¿ããŒã¹ã§ãããããããŒã¿ããŒã¹ã¯åžžã«ããã¯ã°ã©ãŠã³ã ããã»ã¹ã§ãã ããã¯ã°ã©ãŠã³ã ããã»ã¹ã¯ããŒã¿ããŒã¹ã®ããã©ãŒãã³ã¹ãè¯å¥œãªã¬ãã«ã«ç¶æããã®ã«åœ¹ç«ã€ãããããã¯ã°ã©ãŠã³ã ããã»ã¹èªäœãåäœããã«ã¯äžå®éã®ãªãœãŒã¹ãå¿ èŠã§ãã åæã«ããããã¯ã¯ã©ã€ã¢ã³ãèŠæ±ã®ãªãœãŒã¹ãšéè€ããå¯èœæ§ãããããã貪欲ãªããã¯ã°ã©ãŠã³ã ããã»ã¹ãã¯ã©ã€ã¢ã³ãèŠæ±ã®ããã©ãŒãã³ã¹ã«çŽæ¥åœ±é¿ãäžããå¯èœæ§ããããŸãã ãããã£ãŠãããã¯ã°ã©ãŠã³ãããã»ã¹ã«é¢ããŠæªã¿ãçããªãããã«ãããããç£èŠããã³è¿œè·¡ããå¿ èŠããããŸãã
ããŒã¿ããŒã¹ç£èŠã«é¢ããããããã¹ãŠã¯ãã·ã¹ãã ã¡ããªãã¯ã«æ®ããŸãã ããããã€ã³ãã©ã¹ãã©ã¯ãã£ã®ã»ãšãã©ãã¯ã©ãŠãã«ç§»è¡ããŠããããšãèãããšãåã ã®ãã¹ãã®ã·ã¹ãã ã¡ããªã¯ã¹ã¯åžžã«èæ¯ã«æ¶ããŠããŸããŸãã ããããããŒã¿ããŒã¹ã§ã¯ãããã¯äŸç¶ãšããŠé¢é£ããŠãããåœç¶ãã·ã¹ãã ã¡ããªã¯ã¹ãç£èŠããããšãå¿ èŠã§ãã
ã·ã¹ãã ã¡ããªã¯ã¹ã«é¢ããŠã¯ãå€ããå°ãªãããã¹ãŠãåé¡ãããŸãããææ°ã®ç£èŠã·ã¹ãã ã¯ãã¹ãŠãããã®ã¡ããªã¯ã¹ããã§ã«ãµããŒãããŠããŸãããäžè¬ã«ãããã€ãã®ã³ã³ããŒãã³ãã¯ãŸã ååã§ã¯ãªããè¿œå ããå¿ èŠããããã®ããããŸãã ãããã«ã€ããŠãããã€ãã®ã¹ã©ã€ãã§èª¬æããŸãã®ã§ã觊ããŠãããŸãã
èšç»ã®ç¬¬äžã®ãã€ã³ãã¯ã¢ã¯ã»ã·ããªãã£ã§ãã ã¢ã¯ã»ã·ããªãã£ãšã¯äœã§ãã? ç§ã®ç解ã§ã¯ãå¯çšæ§ãšã¯ãããŒã¹ãããµãŒãã¹æ¥ç¶ãžã®èœåã§ããã€ãŸããããŒã¹ãåäžãããµãŒãã¹ãšããŠã¯ã©ã€ã¢ã³ãããã®æ¥ç¶ãåãå
¥ããŸãã ãããŠããã®ã¢ã¯ã»ã·ããªãã£ã¯ç¹å®ã®ç¹æ§ã«ãã£ãŠè©äŸ¡ã§ããŸãã ãããã®ç¹æ§ãããã·ã¥ââããŒãã«è¡šç€ºãããšéåžžã«äŸ¿å©ã§ãã
ããã·ã¥ããŒããäœã§ãããã¯èª°ããç¥ã£ãŠããŸãã å¿
èŠãªæ
å ±ããŸãšããããç»é¢ãäžç®èŠããšãã®ããšã§ãã ãŸããããŒã¿ããŒã¹ã«åé¡ããããã©ãããããã«å€æã§ããŸãã
ãããã£ãŠãããŒã¿ããŒã¹ã®å¯çšæ§ããã®ä»ã®éèŠãªç¹æ§ã¯åžžã«ããã·ã¥ããŒãã«è¡šç€ºããããã®æ
å ±ãæå
ã«ããããã€ã§ãå©çšã§ããããã«ããå¿
èŠããããŸãã ã€ã³ã·ãã³ãã®èª¿æ»ã«æ¢ã«åœ¹ç«ã€è¿œå ã®è©³çŽ°æ
å ±ã¯ãäžéšã®ç·æ¥äºæ
ã調æ»ããå Žåããã§ã«ã»ã«ã³ã㪠ããã·ã¥ããŒãã«é
眮ãããããµãŒãããŒãã£ã®ç£èŠã·ã¹ãã ã«ã€ãªããããªã«ããŠã³ ãªã³ã¯ã«é衚瀺ã«ããå¿
èŠããããŸãã
ããç¥ãããç£èŠã·ã¹ãã ã®äžäŸã ããã¯éåžžã«åªããç£èŠã·ã¹ãã ã§ãã 圌女ã¯å€ãã®ããŒã¿ãåéããŠããŸãããç§ããèŠããšã圌女ã¯ããã·ã¥ããŒããšããå¥åŠãªæŠå¿µãæã£ãŠããŸãã ãããã·ã¥ããŒããäœæããããšãããªã³ã¯ããããŸãã ãã ããããã·ã¥ããŒããäœæãããšãã¯ãXNUMX ã€ã®åã®ãªã¹ããã€ãŸãã°ã©ãã®ãªã¹ããäœæããŸãã ãããŠãäœããèŠãå¿ èŠããããšãã¯ãããŠã¹ãã¯ãªãã¯ããŠã¹ã¯ããŒã«ããç®çã®ãã£ãŒããæ¢ãå§ããŸãã ãããŠãããã«ã¯æéãããããŸããã€ãŸããããã·ã¥ããŒãèªäœããããŸããã ãã£ãŒãã®ãªã¹ãã®ã¿ããããŸãã
ãããã®ããã·ã¥ããŒãã«äœãè¿œå ããå¿ èŠããããŸãã? å¿çæéãªã©ã®ç¹æ§ããå§ããããšãã§ããŸãã PostgreSQL ã«ã¯ pg_stat_statements ãã¥ãŒããããŸãã ããã©ã«ãã§ã¯ç¡å¹ã«ãªã£ãŠããŸãããåžžã«æå¹ã«ããŠäœ¿çšããå¿ èŠãããéèŠãªã·ã¹ãã ãã¥ãŒã® XNUMX ã€ã§ãã ããŒã¿ããŒã¹å ã§å®è¡ããããã¹ãŠã®å®è¡ã¯ãšãªã«é¢ããæ å ±ãä¿åãããŸãã
ãããã£ãŠããã¹ãŠã®ãªã¯ãšã¹ãã®åèšå®è¡æéãååŸããäžèšã®ãã£ãŒã«ãã䜿çšããŠãªã¯ãšã¹ãã®æ°ã§å²ãããšãã§ãããšããäºå®ããå§ããããšãã§ããŸãã ããããããã¯ç é¢å ã®å¹³åäœæž©ã§ãã ä»ã®ãã£ãŒã«ã (æå°ã¯ãšãªå®è¡æéãæ倧å€ãäžå€®å€) ããéå§ããããšãã§ããŸãã ãŸããããŒã»ã³ã¿ã€ã«ãæ§ç¯ããããšãã§ããŸããPostgreSQL ã«ã¯ããã«å¯Ÿå¿ããé¢æ°ããããŸãã ãããŠããã§ã«å®äºãããªã¯ãšã¹ãã«å¯ŸããããŒã¿ããŒã¹ã®å¿çæéãç¹åŸŽä»ããããã€ãã®æ°å€ãååŸã§ããŸããã€ãŸããåœã®ãªã¯ãšã¹ããselect 1ããå®è¡ããŠå¿çæéã調ã¹ãã®ã§ã¯ãªãããã§ã«å®äºãããªã¯ãšã¹ãã®å¿çæéãåæããŠæç»ããŸããå¥ã®å³ãäœæããããããã«åºã¥ããŠã°ã©ããäœæããŸãã
çŸåšã·ã¹ãã ã«ãã£ãŠçæãããŠãããšã©ãŒã®æ°ãç£èŠããããšãéèŠã§ãã ãã®ããã«ãpg_stat_database ãã¥ãŒã䜿çšã§ããŸãã xact_rollback ãã£ãŒã«ãã«æ³šç®ããŸãã ãã®ãã£ãŒã«ãã«ã¯ãããŒã¿ããŒã¹å ã§çºçããããŒã«ããã¯ã®æ°ã ãã§ãªãããšã©ãŒã®æ°ãèæ ®ãããŸãã çžå¯Ÿçã«èšãã°ããã®æ°å€ãããã·ã¥ââããŒãã«è¡šç€ºããŠãçŸåšçºçããŠãããšã©ãŒã®æ°ã確èªã§ããŸãã ãšã©ãŒãå€æ°ããå Žåã¯ããã°ã調ã¹ãŠãšã©ãŒã®çš®é¡ãšçºççç±ã確èªãããããã解決ããããã«æè³ããã®ãè¯ãçç±ã«ãªããŸãã
ã¿ã³ã¡ãŒã¿ãŒãªã©ãè¿œå ããããšãå¯èœã§ãã ãããã¯ãXNUMX ç§ãããã®ãã©ã³ã¶ã¯ã·ã§ã³æ°ãš XNUMX ç§ãããã®ãªã¯ãšã¹ãæ°ã§ãã çžå¯Ÿçã«èšãã°ããããã®æ°å€ãããŒã¿ããŒã¹ã®çŸåšã®ããã©ãŒãã³ã¹ãšããŠäœ¿çšãããªã¯ãšã¹ãã«ããŒã¯ããããã©ããããã©ã³ã¶ã¯ã·ã§ã³ã«ããŒã¯ããããã©ããããããã¯éã«ãããã¯ãšã³ãã®é害ã«ããããŒã¿ããŒã¹ãéè² è·ã«ãªã£ãŠãããã©ããã芳å¯ã§ããŸãã åžžã«ãã®å³ãèŠãŠãç§ãã¡ã®ãããžã§ã¯ãã§ã¯ãã®çš®ã®ããã©ãŒãã³ã¹ã¯æ£åžžã§ãããäžãšäžã®å€ã¯ãã§ã«ããçš®ã®åé¡ããããç解ã§ããªããã®ã§ããããšãèŠããŠããããšãéèŠã§ããã€ãŸãããããã®æ°å€ããªããããªãã®ããæ€èšããå¿ èŠãããããšãæå³ããŸããéåžžã«é«ãã
ãã©ã³ã¶ã¯ã·ã§ã³æ°ãèŠç©ããããã«ãå床 pg_stat_database ãã¥ãŒãåç §ããŸãã ã³ãããæ°ãšããŒã«ããã¯æ°ãå ç®ããŠãXNUMX ç§ãããã®ãã©ã³ã¶ã¯ã·ã§ã³æ°ãååŸã§ããŸãã
è€æ°ã®ãªã¯ãšã¹ãã XNUMX ã€ã®ãã©ã³ã¶ã¯ã·ã§ã³ã«åãŸãããšãçããã¯ç解ããŠããŸãã? ãããã£ãŠãTPS ãš QPS ã¯è¥å¹²ç°ãªããŸãã
XNUMX ç§ãããã®ãªã¯ãšã¹ãæ°ã¯ pg_stat_statements ããååŸã§ããå®äºãããã¹ãŠã®ãªã¯ãšã¹ãã®åèšãåçŽã«èšç®ã§ããŸãã çŸåšã®å€ãåã®å€ãšæ¯èŒãããããæžç®ãããã«ã¿ãååŸããæ°éãååŸããããšã¯æããã§ãã
å¿ èŠã«å¿ããŠè¿œå ã®ã¡ããªã¯ã¹ãè¿œå ã§ããŸããããã¯ãããŒã¿ããŒã¹ã®å¯çšæ§ãè©äŸ¡ããããŠã³ã¿ã€ã ãçºçãããã©ãããç£èŠããã®ã«ã圹ç«ã¡ãŸãã
ãããã®ææšã® XNUMX ã€ã¯çšŒåæéã§ãã ãã ããPostgreSQL ã®çšŒåæéã¯å°ã泚æãå¿ èŠã§ãã ãã®çç±ãã話ããŸãã PostgreSQL ãèµ·åãããšã皌åæéã®ã¬ããŒããéå§ãããŸãã ããããããæç¹ã§ãããšãã°ãããã¿ã¹ã¯ãå€éã«å®è¡ãããŠããå ŽåãOOM ãã©ãŒãæ¥ãŠãPostgreSQL ã®åããã»ã¹ã匷å¶çã«çµäºããŸãããã®å ŽåãPostgreSQL ã¯ãã¹ãŠã®ã¯ã©ã€ã¢ã³ãã®æ¥ç¶ãçµäºããã·ã£ãŒãåãããã¡ã¢ãªé åããªã»ããããŠããªã«ããªãéå§ããŸããæåŸã®ãã§ãã¯ãã€ã³ãã ãããŠããã®ãã§ãã¯ãã€ã³ãããã®å埩ãç¶ãéãããŒã¿ããŒã¹ã¯æ¥ç¶ãåãå ¥ããŸãããã€ãŸãããã®ç¶æ³ã¯ããŠã³ã¿ã€ã ãšããŠè©äŸ¡ãããå¯èœæ§ããããŸãã ãã ãã皌åæéã«ãŠã³ã¿ã¯ãªã»ãããããŸãããããã¯ãæåã®ç¬éãããã¹ããã¹ã¿ãŒã®èµ·åæéãèæ ®ãããããã§ãã ãããã£ãŠããã®ãããªç¶æ³ã¯ã¹ãããã§ããŸãã
ãŸããããã¥ãŒã äœæ¥å¡ã®æ°ãç£èŠããå¿ èŠããããŸãã ã¿ãªããã¯PostgreSQLã®èªåããã¥ãŒã ãšã¯äœãç¥ã£ãŠããŸãã? ãã㯠PostgreSQL ã®èå³æ·±ããµãã·ã¹ãã ã§ãã 圌女ã«ã€ããŠã¯å€ãã®èšäºãæžãããå€ãã®å ±åããªãããŠããŸãã ç空ãšãããã©ã®ããã«æ©èœãããã«ã€ããŠã¯ãå€ãã®è°è«ããããŸãã å€ãã®äººã¯ãããå¿ èŠæªã ãšèããŠããŸãã ããããããããã®ããã§ãã ããã¯ããã©ã³ã¶ã¯ã·ã§ã³ã§å¿ èŠã®ãªãå€ãããŒãžã§ã³ã®è¡ãã¯ãªãŒã³ã¢ããããæ°ããè¡çšã«ããŒãã«ãšã€ã³ããã¯ã¹ã®ã¹ããŒã¹ã解æŸããã¬ããŒãž ã³ã¬ã¯ã¿ãŒã®äžçš®ã§ãã
ãªãç£èŠããå¿ èŠãããã®ã§ãããã? æé€æ©ã¯æã ãšãŠãçãããã§ãã 倧éã®ãªãœãŒã¹ãæ¶è²»ããããã®çµæãã¯ã©ã€ã¢ã³ãã®ãªã¯ãšã¹ãã«åœ±é¿ãçãå§ããŸãã
ãããŠããã㯠pg_stat_activity ãã¥ãŒãéããŠç£èŠããå¿ èŠããããŸããããã«ã€ããŠã¯æ¬¡ã®ã»ã¯ã·ã§ã³ã§èª¬æããŸãã ãã®ãã¥ãŒã«ã¯ãããŒã¿ããŒã¹å ã®çŸåšã®ã¢ã¯ãã£ããã£ã衚瀺ãããŸãã ãããŠããã®ã¢ã¯ãã£ããã£ãéããŠãçŸåšçšŒåããŠããæé€æ©ã®æ°ã远跡ããããšãã§ããŸãã ããã¥ãŒã ã远跡ããå¶éãè¶ ããŠããå Žåã¯ãPostgreSQL ã®èšå®ã調ã¹ãŠããã¥ãŒã ã®æäœãæé©åããå¿ èŠãããããšãããããŸãã
PostgreSQL ã«é¢ãããã XNUMX ã€ã®ç¹ã¯ãPostgreSQL ã¯é·ããã©ã³ã¶ã¯ã·ã§ã³ã«éåžžã«ããããããŠãããšããããšã§ãã ç¹ã«ãäœãããã«é·æéæ»çãããã©ã³ã¶ã¯ã·ã§ã³ã®å Žåã¯ããã§ãã ããã¯ãããããã©ã³ã¶ã¯ã·ã§ã³äžã®ã¢ã€ãã«ç¶æ ã®çµ±èšã§ãã ãã®ãããªãã©ã³ã¶ã¯ã·ã§ã³ã¯ããã¯ãä¿æããããã¥ãŒã ãæ©èœããªããªããŸãã ãã®çµæãããŒãã«ãèšåŒµããŠãµã€ãºã倧ãããªããŸãã ãŸããå€ãããŒãžã§ã³ã®è¡ããã¹ãŠã¡ã¢ãªãããã£ã¹ã¯ã«ç§»åãããŸãã¡ã¢ãªãããã£ã¹ã¯ã«æ»ãå¿ èŠãããããããããã®ããŒãã«ãæäœããã¯ãšãªã®åäœãé ããªããŸãã ãããã£ãŠãæé·ãã©ã³ã¶ã¯ã·ã§ã³ã®æéãæéãæé·ããã¥ãŒã èŠæ±ãç£èŠããå¿ èŠããããŸãã ãŸããéåžžã«é·æéå®è¡ãããŠããããã»ã¹ (OLTP ã®è² è·ã«ãã§ã« 10 ïœ 20 ïœ 30 åãè¶ ããŠãã) ãèŠã€ãã£ãå Žåã¯ããããã«æ³šæãæã£ãŠåŒ·å¶çã«çµäºããããã¢ããªã±ãŒã·ã§ã³ãæé©åããŠãã¯åŒã³åºããããããã»ã©é·ããã³ã°ããŸããã åæã¯ãŒã¯ããŒãã®å Žåã10ã20ã30 åã¯éåžžã§ãããããããé·ãå ŽåããããŸãã
次ã«ãæ¥ç¶ãããã¯ã©ã€ã¢ã³ãã䜿çšãããªãã·ã§ã³ããããŸãã ãã§ã«ããã·ã¥ããŒããäœæããããã«äž»èŠãªå¯çšæ§ã¡ããªãã¯ãæçš¿ããŠããå Žåã¯ãæ¥ç¶ãããŠããã¯ã©ã€ã¢ã³ãã«é¢ããè¿œå æ
å ±ãããã«è¿œå ããããšãã§ããŸãã
PostgreSQL ã®èŠ³ç¹ããèŠããšãã¯ã©ã€ã¢ã³ãã¯ç°ãªããããæ¥ç¶ãããã¯ã©ã€ã¢ã³ãã«é¢ããæ å ±ã¯éèŠã§ãã è¯ãã¯ã©ã€ã¢ã³ããããã°æªãã¯ã©ã€ã¢ã³ããããŸãã
ç°¡åãªäŸã§ãã ã¯ã©ã€ã¢ã³ãã«ããã¢ããªã±ãŒã·ã§ã³ã®ç解ã ã¢ããªã±ãŒã·ã§ã³ã¯ããŒã¿ããŒã¹ã«æ¥ç¶ããããã«ãªã¯ãšã¹ãã®éä¿¡ãéå§ããŸããããŒã¿ããŒã¹ã¯ãªã¯ãšã¹ããåŠçããŠå®è¡ããçµæãã¯ã©ã€ã¢ã³ãã«è¿ããŸãã ãããã¯åè¯ã§æ£ããã¯ã©ã€ã¢ã³ãã§ãã
ã¯ã©ã€ã¢ã³ããæ¥ç¶ããæ¥ç¶ã¯ä¿æããŠãããäœãããªãç¶æ³ããããŸãã ã¢ã€ãã«ç¶æ ã§ãã
ããããæªãã¯ã©ã€ã¢ã³ããããŸãã ããšãã°ãåãã¯ã©ã€ã¢ã³ããæ¥ç¶ãããã©ã³ã¶ã¯ã·ã§ã³ãéããããŒã¿ããŒã¹å ã§äœããå®è¡ããåŸãã³ãŒãã«å ¥ããããšãã°å€éšãœãŒã¹ã«ã¢ã¯ã»ã¹ããããããã§åä¿¡ããããŒã¿ãåŠçãããããŸãã ãããã圌ã¯ååŒãæç«ãããªãã£ãã ãããŠããã©ã³ã¶ã¯ã·ã§ã³ã¯ããŒã¿ããŒã¹å ã§ãã³ã°ããåç·äžã®ããã¯ã«ä¿æãããŸãã ããã¯æªãç¶æ ã§ãã ãŸããã¢ããªã±ãŒã·ã§ã³å éšã®ã©ããã§çªç¶äŸå€ãçºçããŠé害ãçºçããå Žåããã©ã³ã¶ã¯ã·ã§ã³ã¯éåžžã«é·ãééãããŸãŸã«ãªãå¯èœæ§ããããŸãã ãããŠããã㯠PostgreSQL ã®ããã©ãŒãã³ã¹ã«çŽæ¥åœ±é¿ããŸãã PostgreSQL ã¯é ããªããŸãã ãããã£ãŠããã®ãããªã¯ã©ã€ã¢ã³ããã¿ã€ã ãªãŒã«è¿œè·¡ããäœæ¥ã匷å¶çã«çµäºããããšãéèŠã§ãã ãããŠããã®ãããªç¶æ³ãèµ·ãããªãããã«ã¢ããªã±ãŒã·ã§ã³ãæé©åããå¿ èŠããããŸãã
ä»ã®æªãã¯ã©ã€ã¢ã³ãã¯åŸ æ©äžã®ã¯ã©ã€ã¢ã³ãã§ãã ãããããããã¯ç¶æ³ã«ãã£ãŠæªããªããŸãã ããšãã°ãåçŽãªã¢ã€ãã« ãã©ã³ã¶ã¯ã·ã§ã³ã§ãããã©ã³ã¶ã¯ã·ã§ã³ãéããŠããã€ãã®è¡ãããã¯ãããšãã³ãŒãã®ã©ããã§å€±æãããã©ã³ã¶ã¯ã·ã§ã³ããã³ã°ãããŸãŸã«ãªããŸãã å¥ã®ã¯ã©ã€ã¢ã³ããæ¥ãŠåãããŒã¿ãèŠæ±ããŸããããã®ãã³ã°ãããã©ã³ã¶ã¯ã·ã§ã³ããã§ã«å¿ èŠãªè¡ã®ããã¯ãä¿æããŠãããããããã¯ãçºçããŸãã ãã㊠XNUMX çªç®ã®ãã©ã³ã¶ã¯ã·ã§ã³ã¯ãæåã®ãã©ã³ã¶ã¯ã·ã§ã³ãå®äºãããŸã§åŸ æ©ãããã管çè ã«ãã£ãŠåŒ·å¶çã«éããããŸãã ãããã£ãŠãä¿çäžã®ãã©ã³ã¶ã¯ã·ã§ã³ãèç©ãããããŒã¿ããŒã¹æ¥ç¶å¶éãè¶ ããå¯èœæ§ããããŸãã ãããŠãå¶éããã£ã±ãã«ãªããšãã¢ããªã±ãŒã·ã§ã³ã¯ããŒã¿ããŒã¹ãæäœã§ããªããªããŸãã ããã¯ãããžã§ã¯ãã«ãšã£ãŠãã§ã«ç·æ¥äºæ ã§ãã ãããã£ãŠãæªè³ªãªã¯ã©ã€ã¢ã³ãã远跡ããã¿ã€ã ãªãŒã«å¯Ÿå¿ããå¿ èŠããããŸãã
ã¢ãã¿ãªã³ã°ã®å¥ã®äŸã ãããŠãããã«ã¯ãã§ã«ãŸãšããªããã·ã¥ããŒãããããŸãã äžèšã«æ¥ç¶ã«é¢ããæ
å ±ããããŸãã DBæ¥ç¶ â 8åã ãããŠããã ãã§ãã ã©ã®ã¯ã©ã€ã¢ã³ããã¢ã¯ãã£ãã§ãã©ã®ã¯ã©ã€ã¢ã³ããã¢ã€ãã«ç¶æ
ã§äœãããŠããªãã®ãã«ã€ããŠã®æ
å ±ã¯ãããŸããã ä¿çäžã®ãã©ã³ã¶ã¯ã·ã§ã³ãä¿çäžã®æ¥ç¶ã«é¢ããæ
å ±ã¯ãããŸãããã€ãŸããããã¯æ¥ç¶æ°ã瀺ãå³ã§ãããããã ãã§ãã ãããŠãèªåã§æšæž¬ããŠãã ããã
ãããã£ãŠããã®æ
å ±ãç£èŠã«è¿œå ããã«ã¯ãpg_stat_activity ã·ã¹ãã ãã¥ãŒã«ã¢ã¯ã»ã¹ããå¿
èŠããããŸãã PostgreSQL ã«å€ãã®æéãè²»ãããŠããå Žåãããã¯éåžžã«åªãããã¥ãŒã§ãããPostgreSQL ã§ã®çŸåšã®ã¢ã¯ãã£ããã£ãã€ãŸã PostgreSQL ã§äœãèµ·ãã£ãŠãããã衚瀺ããããããéåžžã«äŸ¿å©ã§ãã ããã»ã¹ããšã«ããã®ããã»ã¹ã«é¢ããæ
å ±ã瀺ãåå¥ã®è¡ããããŸããã€ãŸããæ¥ç¶ãè¡ããããã¹ãããŠãŒã¶ãŒãååããã©ã³ã¶ã¯ã·ã§ã³ã®éå§æ¥ãçŸåšå®è¡äžã®ãªã¯ãšã¹ããæåŸã«å®è¡ããããªã¯ãšã¹ãã§ãã ãããã£ãŠãstat ãã£ãŒã«ãã䜿çšããŠã¯ã©ã€ã¢ã³ãã®ç¶æ
ãè©äŸ¡ã§ããŸãã çžå¯Ÿçã«èšãã°ããã®ãã£ãŒã«ãã§ã°ã«ãŒãåããçŸåšããŒã¿ããŒã¹ã«ããçµ±èšãšãããŒã¿ããŒã¹ã«ãã®çµ±èšãããæ¥ç¶ã®æ°ãååŸã§ããŸãã ãããŠããã§ã«åä¿¡ããæ°å€ãã¢ãã¿ãªã³ã°ã«éä¿¡ãããããã«åºã¥ããŠã°ã©ããæç»ããããšãã§ããŸãã
ãã©ã³ã¶ã¯ã·ã§ã³ã®æéãè©äŸ¡ããããšãéèŠã§ãã ããã¥ãŒã ã®æéãè©äŸ¡ããããšãéèŠã§ãããšãã§ã«è¿°ã¹ãŸãããããã©ã³ã¶ã¯ã·ã§ã³ãåæ§ã«è©äŸ¡ãããŸãã xact_start ãã£ãŒã«ããš query_start ãã£ãŒã«ãããããŸãã ãããã¯ãçžå¯Ÿçã«ããã©ã³ã¶ã¯ã·ã§ã³ã®éå§æéãšãªã¯ãšã¹ãã®éå§æéã瀺ããŸãã çŸåšã®ã¿ã€ã ã¹ã¿ã³ãã衚瀺ãã now() é¢æ°ã䜿çšãããã©ã³ã¶ã¯ã·ã§ã³ãšãªã¯ãšã¹ãã®ã¿ã€ã ã¹ã¿ã³ããæžç®ããŸãã ãããŠããã©ã³ã¶ã¯ã·ã§ã³ã®æéãã€ãŸããªã¯ãšã¹ãã®æéãååŸããŸãã
é·ããã©ã³ã¶ã¯ã·ã§ã³ãèŠã€ãã£ãå Žåã¯ããã§ã«å®äºããŠããå¿ èŠããããŸãã OLTP ããŒãã®å Žåãé·ããã©ã³ã¶ã¯ã·ã§ã³ã¯ãã§ã« 1ã2ã3 åãè¶ ããŠããŸã. OLAP ã¯ãŒã¯ããŒãã®å Žåããã©ã³ã¶ã¯ã·ã§ã³ãé·ãã®ã¯æ£åžžã§ãããå®äºãŸã§ã« XNUMX æé以äžãããå Žåã¯ãã©ããã«ã¹ãã¥ãŒãããããšã瀺ããŠããŸãã
ã¯ã©ã€ã¢ã³ããããŒã¿ããŒã¹ã«æ¥ç¶ãããšãããŒã¿ã®æäœãéå§ãããŸãã 圌ãã¯ããŒãã«ã«ã¢ã¯ã»ã¹ããã€ã³ããã¯ã¹ã«ã¢ã¯ã»ã¹ããŠããŒãã«ããããŒã¿ãååŸããŸãã ãããŠãã¯ã©ã€ã¢ã³ãããã®ããŒã¿ãã©ã®ããã«æ±ãããè©äŸ¡ããããšãéèŠã§ãã
ããã¯ãã¯ãŒã¯ããŒããè©äŸ¡ããã©ã®ããŒãã«ãæããããããã§ãããã倧ãŸãã«ç解ããããã«å¿ èŠã§ãã ããšãã°ãããã¯ãããçš®ã®é«é SSD ã¹ãã¬ãŒãžã«ãããããããŒãã«ãé 眮ãããç¶æ³ã§å¿ èŠã«ãªããŸãã ããšãã°ãé·æé䜿çšããŠããªãã¢ãŒã«ã€ã ããŒãã«ããããçš®ã®ãã³ãŒã«ããã¢ãŒã«ã€ããã€ãŸã SATA ãã©ã€ãã«ç§»åããããã«åžžé§ãããŠãããšãå¿ èŠã«å¿ããŠã¢ã¯ã»ã¹ã§ããããã«ãªããŸãã
ããã¯ããªãªãŒã¹ãå±éåŸã®ç°åžžã®æ€åºã«ã圹ç«ã¡ãŸãã ãããžã§ã¯ããäœããã®æ°æ©èœããªãªãŒã¹ãããšããŸãã ããšãã°ãããŒã¿ããŒã¹ãæäœããããã®æ°ããæ©èœãè¿œå ããŸããã ãŸããããŒãã«ã®äœ¿çšç¶æ³ã°ã©ãããããããããšããããã®ã°ã©ãäžã®ç°åžžãç°¡åã«æ€åºã§ããŸãã ããšãã°ãããŒã¹ããæŽæ°ããããããŒã¹ããåé€ãããããŸãã ãšãŠãç®ç«ã€ã§ãããã
ãå€åãçµ±èšã®ç°åžžãæ€åºããããšãã§ããŸãã ããã¯ã©ãããæå³ã§ããïŒ PostgreSQL ã«ã¯ãéåžžã«åŒ·åã§åªããã¯ãšãª ãã©ã³ããŒãåãã£ãŠããŸãã ãããŠéçºè ã¯ãã®éçºã«å€ãã®æéãè²»ãããŠããŸãã 圌ã¯ã©ããã£ãŠåããŠããã®ã§ããïŒ é©åãªèšç»ãç«ãŠãããã«ãPostgreSQL ã¯ããŒãã«å ã®ããŒã¿ã®ååžã«é¢ããçµ±èšãç¹å®ã®æéééãšé »åºŠã§åéããŸãã ãããã¯æãäžè¬çãªå€ã§ããäžæã®å€ã®æ°ãããŒãã«å ã® NULL ã«é¢ããæ å ±ãå€ãã®æ å ±ã§ãã
ãããã®çµ±èšã«åºã¥ããŠããã©ã³ããŒã¯ããã€ãã®ã¯ãšãªãæ§ç¯ããæãæé©ãªãã®ãéžæãããã®ã¯ãšãª ãã©ã³ã䜿çšããŠã¯ãšãªèªäœãå®è¡ããããŒã¿ãè¿ããŸãã
ãããŠãçµ±èšããæµ®ããããšãèµ·ãããŸãã ããŒãã«å ã®è³ªãšéã®ããŒã¿ãäœããã®çç±ã§å€æŽãããŸããããçµ±èšã¯åéãããŸããã§ããã ãããŠãçå®ãããèšç»ãæé©ã§ã¯ãªãå¯èœæ§ããããŸãã ãããŠãããŒãã«ã«åºã¥ããŠåéãããã¢ãã¿ãªã³ã°ã«åºã¥ããŠèšç»ãæé©ã§ã¯ãªãããšãå€æããå Žåããããã®ç°åžžã確èªã§ããããã«ãªããŸãã ããšãã°ãã©ããã§ããŒã¿ã質çã«å€åããã€ã³ããã¯ã¹ã®ä»£ããã«ããŒãã«ãä»ããã·ãŒã±ã³ã·ã£ã« ãã¹ã䜿çšããå§ããŸããã ã¯ãšãªã 100 è¡ã®ã¿ãè¿ãå¿ èŠãããå Žå (100 è¡ãšããå¶éããããŸã)ããã®ã¯ãšãªã«å¯ŸããŠå®å šãªæ€çŽ¢ãå®è¡ãããŸãã ãããŠãããã¯åžžã«ããã©ãŒãã³ã¹ã«éåžžã«æªã圱é¿ãäžããŸãã
ããã¯ã¢ãã¿ãªã³ã°ã§ã確èªã§ããŸãã ãããŠããã§ã«ãã®ã¯ãšãªã確èªããExplain ãå®è¡ããçµ±èšãåéããæ°ããè¿œå ã®ã€ã³ããã¯ã¹ãæ§ç¯ããŸãã ãããŠãã§ã«ãã®åé¡ã«å¯Ÿå¿ããŠããŸãã ã ããããéèŠãªã®ã§ãã
ã¢ãã¿ãªã³ã°ã®å¥ã®äŸã ãšãŠã人æ°ãããã®ã§ç¥ã£ãŠãã人ãå€ããšæããŸãã ãããžã§ã¯ãã§ããã䜿çšãã人
ããã€ãã®ã°ã©ãããããŸãã ãããŠãã€ã㯠5 ãšããŠç€ºãããŸããã€ãŸããXNUMX ã€ã®ã°ã©ãããããŸãã ãããã¯ãããŒã¿ã®æ¿å ¥ãããŒã¿ã®æŽæ°ãããŒã¿ã®åé€ãããŒã¿ã®ãã§ãããããŒã¿ã®è¿åŽã§ãã åäœã¯ãã€ãã§ãã ããããåé¡ã¯ãPostgreSQL ã®çµ±èšã¯ããŒã¿ãã¿ãã« (è¡) ã§è¿ããšããããšã§ãã ãããã£ãŠãã¿ãã«ã¯ãã€ãã§ã¯ãªãæååã§ãããå€ãã®ãã€ãããããåžžã«å¯å€é·ã§ããããããããã®ã°ã©ãã¯ã¯ãŒã¯ããŒããæ°åãæ°ååéå°è©äŸ¡ããéåžžã«è¯ãæ¹æ³ãšãªããŸãã ã€ãŸããã¿ãã«ã䜿çšããŠã¯ãŒã¯ããŒãããã€ãåäœã§èšç®ããããšã¯éçŸå®çãªã¿ã¹ã¯ã§ããããéåžžã«å°é£ã§ãã ãããã£ãŠãããã·ã¥ããŒããŸãã¯çµã¿èŸŒã¿ã®ç£èŠã䜿çšããå Žåã¯ããããæ£ããåäœããæ£ããè©äŸ¡ãããããŒã¿ãè¿ãããããšãç解ããããšãåžžã«éèŠã§ãã
ãããã®ããŒãã«ã®çµ±èšãååŸããã«ã¯ã©ãããã°ããã§ãããã? ãã®ç®çã®ããã«ãPostgreSQL ã«ã¯ç¹å®ã®ãã¥ãŒã®ãã¡ããªãŒããããŸãã ãããŠäž»ãªèŠç¹ã¯ã
äžèšã®ãã£ãŒã«ãã䜿çšãããšãæ¿å ¥ãæŽæ°ãåé€ã®æ°ãæšå®ã§ããŸãã ç§ã䜿çšããããã·ã¥ããŒãã®äŸã§ã¯ããããã®ãã£ãŒã«ãã䜿çšããŠã¯ãŒã¯ããŒãã®ç¹æ§ãè©äŸ¡ããŠããŸãã ãããã£ãŠãããããåºã«ããŠæ§ç¯ããããšãã§ããŸãã ãã ãããããã¯ãã€ãã§ã¯ãªãã¿ãã«ã§ããããããã€ãåäœã§å®è¡ããããšã¯ã§ããªãããšãèŠããŠãã䟡å€ããããŸãã
ãã®ããŒã¿ã«åºã¥ããŠããããã TopN ããŒãã«ãæ§ç¯ã§ããŸãã ããšãã°ãããã 5ãããã 10 ãªã©ã§ãã ãŸããä»ã®ããŒãã«ãããå€ããªãµã€ã¯ã«ãããŠããããã ããŒãã«ã远跡ã§ããŸãã ããšãã°ã5 ã€ã®ãããããããŒãã«ãæ¿å ¥ããŸãã ãããã® TopN ããŒãã«ã䜿çšããŠã¯ãŒã¯ããŒããè©äŸ¡ãããªãªãŒã¹ãæŽæ°ãå±éåŸã®ã¯ãŒã¯ããŒãã®ããŒã¹ããè©äŸ¡ã§ããŸãã
ããŒãã«ã®ãµã€ãºãè©äŸ¡ããããšãéèŠã§ããéçºè ãæ°ããæ©èœãå±éãããšãè¿œå ã®ããŒã¿éãè¿œå ããããšã決å®ãããããããã©ã®ããã«ãªãããäºæž¬ããŠããªãã£ãããã«ãããŒãã«ã®ãµã€ãºã倧ãããªãå§ããããšãããããã§ããããŒã¿ããŒã¹ã®ãµã€ãºã«åœ±é¿ããŸãã ãã®ãããªã±ãŒã¹ã¯ç§ãã¡ã«ãšã£ãŠãé©ãã§ãã
ããã§ã¯ãã¡ãã£ãšãã質åã§ãã ããŒã¿ããŒã¹ ãµãŒããŒã®è² è·ã«æ°ã¥ãããšããã©ã®ãããªçåãçããŸãã? 次ã®è³ªåã¯äœã§ãã?
ãããå®éã«ã¯æ¬¡ã®ãããªçåãçããŸãã è² è·ã«ãã£ãŠã©ã®ãããªãªã¯ãšã¹ããçºçããŸãã? ã€ãŸããè² è·ã«ãã£ãŠåŒãèµ·ããããããã»ã¹ã芳å¯ããããšã¯é¢çœããããŸããã ãã¹ãã«ããŒã¿ããŒã¹ãããå Žåããã®ããŒã¿ããŒã¹ã¯ããã§å®è¡ãããŠãããããŒã¿ããŒã¹ã®ã¿ãããã§ç Žæ£ãããããšã¯æããã§ãã ããããããéããšãPostgreSQL ã§äœããå®è¡ããŠããããã»ã¹ã®ãªã¹ãã衚瀺ãããŸãã 圌ããäœãããŠããã®ãã¯ãããããã¯æããã§ã¯ãããŸããã
ãããã£ãŠãæãé«ãè² è·ãåŒãèµ·ããã¯ãšãªãèŠã€ããå¿ èŠããããŸããããã¯ãã¯ãšãªã®ãã¥ãŒãã³ã°ã¯ãäžè¬ã«ãPostgreSQL ããªãã¬ãŒãã£ã³ã° ã·ã¹ãã ã®æ§æããã¥ãŒãã³ã°ããããããŒããŠã§ã¢ããã¥ãŒãã³ã°ãããããããã倧ããªå©çãåŸãããããã§ãã ç§ã®æšå®ã«ãããšãããã¯çŽ 80-85-90% ã§ãã ãããŠãããã¯ã¯ããã«é«éã«å®è¡ãããŸãã ç¹ã«ããŒã¿ããŒã¹ãåèµ·åã§ããªãå Žåã¯ãæ§æãä¿®æ£ããããåèµ·åãã¹ã±ãžã¥ãŒã«ããããããŒããŠã§ã¢ãè¿œå ãããããããããèŠæ±ãä¿®æ£ããæ¹ãé«éã§ãã ãã®ã¯ãšãªããããè¯ãçµæãåŸãã«ã¯ãã¯ãšãªãã©ããã«æžãçŽãããã€ã³ããã¯ã¹ãè¿œå ããæ¹ãç°¡åã§ãã
ãããã£ãŠãèŠæ±ãšãã®é©åæ§ãç£èŠããå¿
èŠããããŸãã ã¢ãã¿ãªã³ã°ã®å¥ã®äŸãèŠãŠã¿ãŸãããã ãããŠããã§ãåªããç£èŠãè¡ãããŠããããã§ãã ã¬ããªã±ãŒã·ã§ã³ã«é¢ããæ
å ±ãã¹ã«ãŒããããããããã³ã°ããªãœãŒã¹äœ¿çšçã«é¢ããæ
å ±ããããŸãã ãã¹ãŠåé¡ãããŸãããããªã¯ãšã¹ãã«é¢ããæ
å ±ã¯ãããŸããã ããŒã¿ããŒã¹å
ã§ã©ã®ãããªã¯ãšãªãå®è¡ãããŠããã®ããå®è¡æéãã¯ãšãªã®æ°ã¯äžæã§ãã ã¢ãã¿ãªã³ã°ã§ã¯åžžã«ãã®æ
å ±ãå¿
èŠã§ãã
ãã®æ å ±ãååŸããã«ã¯ãpg_stat_statements ã¢ãžã¥ãŒã«ã䜿çšããŸãã ããã«åºã¥ããŠãããŸããŸãªã°ã©ããäœæã§ããŸãã ããšãã°ãæãé »ç¹ã«å®è¡ãããã¯ãšãªãã€ãŸãæãé »ç¹ã«å®è¡ãããã¯ãšãªã«é¢ããæ å ±ãååŸã§ããŸãã ã¯ãããããã€åŸã«ãããèŠãŠããªã¯ãšã¹ããæ¥å¢ããŠãããã©ãããç解ããããšãéåžžã«åœ¹ç«ã¡ãŸãã
æé·ã®ã¯ãšãªãã€ãŸãå®äºãŸã§ã«æãæéããããã¯ãšãªãç£èŠã§ããŸãã ãããã¯ããã»ããµäžã§å®è¡ãããI/O ãæ¶è²»ããŸãã total_timeãmean_timeãblk_write_timeãblk_read_time ãã£ãŒã«ãã䜿çšããŠãããè©äŸ¡ããããšãã§ããŸãã
ãªãœãŒã¹äœ¿çšéã®èŠ³ç¹ããæãéããªã¯ãšã¹ãããã£ã¹ã¯ããèªã¿åããªã¯ãšã¹ããã¡ã¢ãªãæäœãããªã¯ãšã¹ãããŸãã¯éã«äœããã®æžã蟌ã¿è² è·ãåŒãèµ·ãããªã¯ãšã¹ããè©äŸ¡ããã³ç£èŠã§ããŸãã
æãå¯å€§ãªãªã¯ãšã¹ããè©äŸ¡ããããšãã§ããŸãã ãããã¯ã倧éã®è¡ãè¿ãã¯ãšãªã§ãã ããšãã°ãå¶éã®èšå®ãå¿ãããªã¯ãšã¹ããèããããŸãã ãããŠãã¯ãšãªãããããŒãã«å šäœã§ããŒãã«ãŸãã¯ã¯ãšãªã®å å®¹å šäœãè¿ãã ãã§ãã
ãŸããäžæãã¡ã€ã«ãŸãã¯äžæããŒãã«ã䜿çšããã¯ãšãªãç£èŠããããšãã§ããŸãã
ãããŠããã¯ã°ã©ãŠã³ãããã»ã¹ããŸã ãããŸãã ããã¯ã°ã©ãŠã³ã ããã»ã¹ã¯äž»ã«ãã§ãã¯ãã€ã³ãããŸãã¯ãã§ãã¯ãã€ã³ããšãåŒã°ããèªåããã¥ãŒã ãšã¬ããªã±ãŒã·ã§ã³ã§ãã
ã¢ãã¿ãªã³ã°ã®å¥ã®äŸã å·ŠåŽã« [ã¡ã³ããã³ã¹] ã¿ããããã®ã§ãããã«ç§»åãããšã圹ç«ã€ãã®ã衚瀺ãããããšãæåŸ ããŸãã ããããããã§ã¯ããã¥ãŒã æäœãšçµ±èšåéã®æéã ããããããã以äžã®ããšã¯ãããŸããã ããã¯éåžžã«è²§åŒ±ãªæ å ±ãªã®ã§ãããã¯ã°ã©ãŠã³ã ããã»ã¹ãããŒã¿ããŒã¹å ã§ã©ã®ããã«åäœãããããŸããã®åäœã«ãã£ãŠåé¡ãçºçãããã©ããã«ã€ããŠã®æ å ±ãåžžã«å ¥æããå¿ èŠããããŸãã
ãã§ãã¯ãã€ã³ããèŠããšãã¯ããã§ãã¯ãã€ã³ããããŒã㣠ããŒãžãã·ã£ãŒã ã¡ã¢ãªé åãããã£ã¹ã¯ã«ãã©ãã·ã¥ããŠããããã§ãã¯ãã€ã³ããäœæããããšãèŠããŠããå¿ èŠããããŸãã ãããŠããã®ãã§ãã¯ãã€ã³ãã¯ãç·æ¥æã« PostgreSQL ãçªç¶çµäºããå Žåã®å埩ã®å ŽæãšããŠäœ¿çšã§ããŸãã
ãããã£ãŠããã¹ãŠã®ãããŒãã£ãããŒãžããã£ã¹ã¯ã«ãã©ãã·ã¥ããã«ã¯ãããçšåºŠã®éã®æžã蟌ã¿ãè¡ãå¿ èŠããããŸãã ãããŠãäžè¬ã«ã倧éã®ã¡ã¢ãªãæèŒããã·ã¹ãã ã§ã¯ãããã¯å€§éã«ãªããŸãã ãŸãããã§ãã¯ãã€ã³ããçãééã§é »ç¹ã«å®è¡ãããšããã£ã¹ã¯ã®ããã©ãŒãã³ã¹ãå€§å¹ ã«äœäžããŸãã ãããŠãã¯ã©ã€ã¢ã³ãã®ãªã¯ãšã¹ãã¯ãªãœãŒã¹äžè¶³ã«ãã£ãŠå°é£ã«ãªããŸãã 圌ãã¯ãªãœãŒã¹ãããã£ãŠç«¶äºããçç£æ§ãæ¬ åŠããŸãã
ãããã£ãŠãæå®ããããã£ãŒã«ãã䜿çšãã pg_stat_bgwriter ãéããŠãçºçãããã§ãã¯ãã€ã³ãã®æ°ãç£èŠã§ããŸãã ãŸããç¹å®ã®æé (10ã15ã20 åããŸã㯠3 å) ã«å€æ°ã®ãã§ãã¯ãã€ã³ããããå Žå (ããšãã°ã4-5-XNUMX)ãããã¯ãã§ã«åé¡ã«ãªãå¯èœæ§ããããŸãã ãããŠãããŒã¿ããŒã¹ãšæ§æã調ã¹ãŠãäœããã®ãããªå€§éã®ãã§ãã¯ãã€ã³ãã®åå ãšãªã£ãŠããã®ãã調ã¹ãå¿ èŠããããŸãã ãããããã倧èŠæš¡ãªã¬ã³ãŒãã£ã³ã°ãè¡ãããŠããã®ãããããªãã ãã§ã«ã¯ãŒã¯ããŒã ã°ã©ããè¿œå ããŠãããããã¯ãŒã¯ããŒããè©äŸ¡ããããšãã§ããŸãã ãã§ã«ãã§ãã¯ãã€ã³ã ãã©ã¡ãŒã¿ãŒã埮調æŽããŠãã¯ãšãªã®ããã©ãŒãã³ã¹ã«å€§ããªåœ±é¿ãäžããªãããšã確èªã§ããŸãã
èªåããã¥ãŒã ã«åã³æ»ããŸãããªããªããåè¿°ããããã«ãèªåããã¥ãŒã ã¯ãã£ã¹ã¯ãšã¯ãšãªã®äž¡æ¹ã®ããã©ãŒãã³ã¹ãç°¡åã«å ç®ã§ããããã§ãããã®ãããèªåããã¥ãŒã ã®éãèŠç©ããããšã¯åžžã«éèŠã§ãã
ããŒã¿ããŒã¹å
ã®èªåããã¥ãŒã ã¯ãŒã«ãŒã®æ°ã¯å¶éãããŠããŸãã ããã©ã«ãã§ã¯ XNUMX ã€ã®ã¯ãŒã«ãŒãååšãããããããŒã¿ããŒã¹å
ã§åžžã« XNUMX 人ã®ã¯ãŒã«ãŒãäœæ¥ããŠããå Žåãããã¯èªåããã¥ãŒã ãæ§æãããŠããªãããšãæå³ãããããå¶éãåŒãäžããèªåããã¥ãŒã èšå®ãä¿®æ£ããŠæ§æãéå§ããå¿
èŠããããŸãã
ã©ã®æé€æ©äœæ¥å¡ãããããè©äŸ¡ããããšãéèŠã§ãã ãŠãŒã¶ãŒãèµ·åããããDBA ãæ¥ãŠæåã§äœããã®ããã¥ãŒã ãèµ·åãããããè² è·ãçºçããŸããã äœããã®åé¡ãçºçããŠããŸãã ãŸãã¯ãããã¯ãã©ã³ã¶ã¯ã·ã§ã³ã«ãŠã³ã¿ãŒã®ããžãå€ãæé€æ©ã®æ°ã§ãã PostgreSQL ã®äžéšã®ããŒãžã§ã³ã§ã¯ããããã¯éåžžã«éãããã¥ãŒã ã«ãªããŸãã ãŸããããŒãã«å
šäœãèªã¿åãããã®ããŒãã«å
ã®ãã¹ãŠã®ãããã¯ãã¹ãã£ã³ãããããããã©ãŒãã³ã¹ãç°¡åã«å ç®ã§ããŸãã
ãããŠãã¡ãããæé€æ©ã®æç¶æéãã§ãã éåžžã«é·æé皌åããé·æã¡ããæé€æ©ãããå Žåãããã¯åã³æé€æ©ã®æ§æã«æ³šæãæãããããããã®èšå®ãåæ€èšããå¿ èŠãããããšãæå³ããŸãã ããŒãã«äžã§ããã¥ãŒã ãé·æé (3 ïœ 4 æé) å®è¡ããããšãç¶æ³ãçºçããå¯èœæ§ããããŸãããããã¥ãŒã ãåäœããŠããéã«ã倧éã®ãããè¡ãåã³ããŒãã«ã«èç©ãããŠããŸãããã§ãã ãããŠãæé€æ©ãå®äºãããããã«ãããäžåºŠãã®ããŒãã«ã«æé€æ©ããããå¿ èŠããããŸãã ãããŠç§ãã¡ã¯ãç¡éã®ç空ç¶æ ãšããç¶æ³ã«å°éããŸãã ãããŠãã®å Žåãããã¥ãŒã ã¯ãã®ä»äºã«å¯Ÿå¿ã§ãããããŒãã«å ã®æçšãªããŒã¿ã®éã¯åããŸãŸã§ãããããŒãã«ã®ãµã€ãºãåŸã ã«å€§ãããªãå§ããŸãã ãããã£ãŠãé·ãããã¥ãŒã äžã¯ãåžžã«æ§æã確èªããŠæé©åãè©Šã¿ãŸãããåæã«ãã¯ã©ã€ã¢ã³ãèŠæ±ã®ããã©ãŒãã³ã¹ãäœäžããªãããã«ããŸãã
çŸåšãã¹ããªãŒãã³ã° ã¬ããªã±ãŒã·ã§ã³ãåããŠããªã PostgreSQL ã€ã³ã¹ããŒã«ã¯äºå®äžãããŸããã ã¬ããªã±ãŒã·ã§ã³ã¯ããã¹ã¿ãŒããã¬ããªã«ã«ããŒã¿ã移åããããã»ã¹ã§ãã
PostgreSQL ã®ã¬ããªã±ãŒã·ã§ã³ã¯ãã©ã³ã¶ã¯ã·ã§ã³ ãã°ãéããŠè¡ãããŸãã ãŠã£ã¶ãŒãã¯ãã©ã³ã¶ã¯ã·ã§ã³ ãã°ãçæããŸãã ãã©ã³ã¶ã¯ã·ã§ã³ ãã°ã¯ãããã¯ãŒã¯æ¥ç¶ãä»ããŠã¬ããªã«ã«éä¿¡ãããã¬ããªã«äžã§è€è£œãããŸãã ããã¯ç°¡åã§ãã
ãããã£ãŠãã¬ããªã±ãŒã·ã§ã³ã©ã°ãç£èŠããã«ã¯ãpg_stat_replication ãã¥ãŒã䜿çšãããŸãã ãããã圌女ã«ãšã£ãŠãã¹ãŠãåçŽãªããã§ã¯ãããŸããã ããŒãžã§ã³ 10 ã§ã¯ããã¥ãŒã«ããã€ãã®å€æŽãå ããããŸããã ãŸããããã€ãã®ãã£ãŒã«ãã®ååãå€æŽãããŸããã ããã€ãã®ãã£ãŒã«ããè¿œå ãããŸããã ããŒãžã§ã³ 10 ã§ã¯ãã¬ããªã±ãŒã·ã§ã³ã®é 延ãç§åäœã§æšå®ã§ãããã£ãŒã«ãã衚瀺ãããŸããã ãšãŠãå¿«é©ã§ãã ããŒãžã§ã³ 10 ããåã§ã¯ãã¬ããªã±ãŒã·ã§ã³ ã©ã°ããã€ãåäœã§èŠç©ããããšãã§ããŸããã ãã®ãªãã·ã§ã³ã¯ããŒãžã§ã³ 10 ã«ãæ®ããŸããã€ãŸããã©ã°ããã€ãåäœã§æšå®ããããã©ã°ãç§åäœã§æšå®ããããéœåã®ããæ¹ãéžæã§ããŸãã å€ãã®äººã¯äž¡æ¹ãè¡ã£ãŠããŸãã
ãã ããã¬ããªã±ãŒã·ã§ã³ã®é 延ãè©äŸ¡ããã«ã¯ããã©ã³ã¶ã¯ã·ã§ã³å ã®ãã°ã®äœçœ®ãç¥ãå¿ èŠããããŸãã ãããã®ãã©ã³ã¶ã¯ã·ã§ã³ ãã°ã®äœçœ®ã¯ãæ£ç¢ºã« pg_stat_replication ãã¥ãŒå ã«ãããŸãã çžå¯Ÿçã«èšãã°ãpg_xlog_location_diff() é¢æ°ã䜿çšããŠããã©ã³ã¶ã¯ã·ã§ã³ ãã°å ã® XNUMX ã€ã®ãã€ã³ããååŸã§ããŸãã ãããã®éã®å·®åãèšç®ããã¬ããªã±ãŒã·ã§ã³ ã©ã°ããã€ãåäœã§ååŸããŸãã ãšãŠã䟿å©ã§ã·ã³ãã«ã§ãã
ããŒãžã§ã³ 10 ã§ã¯ããã®é¢æ°ã®ååã pg_wal_lsn_diff() ã«å€æŽãããŸããã äžè¬ã«ããxlogããšããåèªãåºçŸãããã¹ãŠã®é¢æ°ããã¥ãŒããŠãŒãã£ãªãã£ã§ã¯ãå€ãwalãã«çœ®ãæããããŸããã ããã¯ãã¥ãŒãšé¢æ°ã®äž¡æ¹ã«åœãŠã¯ãŸããŸãã ããã¯ãŸãã«ã€ãããŒã·ã§ã³ã§ãã
ããã«ãããŒãžã§ã³ 10 ã§ã¯ãã©ã°ãå ·äœçã«ç€ºãè¡ãè¿œå ãããŸããã ãããã¯ãæžã蟌ã¿ã©ã°ããã©ãã·ã¥ ã©ã°ããªãã¬ã€ ã©ã°ã§ãã ã€ãŸããããããç£èŠããããšãéèŠã§ãã ã¬ããªã±ãŒã·ã§ã³ã«é 延ãããããšãããã£ãå Žåã¯ããã®é 延ãçºçããçç±ãšçºçå Žæã調æ»ããåé¡ã解決ããå¿ èŠããããŸãã
ã·ã¹ãã ã¡ããªã¯ã¹ã«é¢ããŠã¯ãã»ãŒãã¹ãŠãæ£åžžã«è¡ãããŠããŸãã ç£èŠãéå§ããããšãã¯ãã·ã¹ãã ã¡ããªãã¯ããå§ãŸããŸãã ããã¯ãããã»ããµãã¡ã¢ãªãã¹ã¯ããããããã¯ãŒã¯ããã£ã¹ã¯ã®å»æ£ã§ãã ãã ããå€ãã®ãã©ã¡ãŒã¿ã¯ããã©ã«ãã§ã¯ååšããŸããã
ãªãµã€ã¯ã« ããã»ã¹ããã¹ãŠé 調ã§ããã°ããã£ã¹ã¯ã®ãªãµã€ã¯ã«ã«åé¡ããããŸãã éåžžãç£èŠéçºè ã¯ã¹ã«ãŒãããã«é¢ããæ å ±ãè¿œå ããŸãã iops ãŸãã¯ãã€ãåäœã§æå®ã§ããŸãã ãããã圌ãã¯ã¬ã€ãã³ã·ãŒãšãã£ã¹ã¯ããã€ã¹ã®äœ¿çšçãå¿ããŠããŸãã ãããã¯ããã£ã¹ã¯ã®è² è·ãšé床ãè©äŸ¡ã§ãããããéèŠãªãã©ã¡ãŒã¿ã§ãã é 延ãé·ãå Žåã¯ããã£ã¹ã¯ã«äœããã®åé¡ãããããšãæå³ããŸãã 䜿çšçãé«ãå Žåã¯ããã£ã¹ã¯ã察å¿ããŠããªãããšãæå³ããŸãã ãããã¯ã¹ã«ãŒããããããåªããç¹æ§ã§ãã
ããã«ããããã®çµ±èšã¯ãããã»ããµã®ãªãµã€ã¯ã«ãšåæ§ã«ã/proc ãã¡ã€ã« ã·ã¹ãã ãããååŸã§ããŸãã ãªããã®æ å ±ãç£èŠã«è¿œå ãããªãã®ãããããŸããã ããããããã«ããããããããããã¢ãã¿ãªã³ã°ã«å«ããããšã¯éèŠã§ãã
ãããã¯ãŒã¯ã€ã³ã¿ãŒãã§ãŒã¹ã«ãåãããšãåœãŠã¯ãŸããŸãã ãã±ããåäœããã€ãåäœã®ãããã¯ãŒã¯ ã¹ã«ãŒãããã«é¢ããæ å ±ã¯ãããŸãããåŸ ã¡æéã䜿çšçã«é¢ããæ å ±ã¯ãããŸããããããæçãªæ å ±ã§ã¯ãããŸããã
ã©ã®ãããªç£èŠã«ãæ¬ ç¹ã¯ãããŸãã ãããŠãã©ã®ãããªçš®é¡ã®ã¢ãã¿ãªã³ã°ãè¡ã£ãŠããåžžã«äœããã®åºæºãæºãããªãããšããããŸãã ããã§ããéçºã¯é²ãã§ãããæ°ããæ©èœãæ°ãããã®ãè¿œå ãããŠããã®ã§ãäœããéžæããŠå®äºããŠãã ããã
ãããŠãå®äºããã«ã¯ãæäŸãããçµ±èšãäœãæå³ããã®ãããããŠããããåââé¡è§£æ±ºã«ã©ã®ããã«äœ¿çšã§ããã®ããåžžã«ç解ããŠããå¿ èŠããããŸãã
ãããŠããã€ãã®éèŠãªãã€ã³ã:
- å¯çšæ§ãåžžã«ç£èŠããããŒã¿ããŒã¹ã§ãã¹ãŠãæ£åžžã§ããããšãããã«è©äŸ¡ã§ããããã«ããã·ã¥ããŒããçšæããå¿ èŠããããŸãã
- æªãã¯ã©ã€ã¢ã³ããæé€ããŠæå¢ããã«ã¯ãã©ã®ã¯ã©ã€ã¢ã³ããããŒã¿ããŒã¹ãæäœããŠããããåžžã«ææ¡ããå¿ èŠããããŸãã
- ãããã®ã¯ã©ã€ã¢ã³ããããŒã¿ãã©ã®ããã«æ±ãããè©äŸ¡ããããšãéèŠã§ãã èªåã®ã¯ãŒã¯ããŒãã«ã€ããŠã®ã¢ã€ãã¢ãæã£ãŠããå¿ èŠããããŸãã
- ã©ã®ãããªã¯ãšãªã䜿çšããŠããã®ã¯ãŒã¯ããŒããã©ã®ããã«åœ¢æãããããè©äŸ¡ããããšãéèŠã§ãã ã¯ãšãªãè©äŸ¡ããæé©åãããªãã¡ã¯ã¿ãªã³ã°ããã€ã³ããã¯ã¹ãæ§ç¯ããããšãã§ããŸãã ããã¯éåžžã«éèŠã§ãã
- ããã¯ã°ã©ãŠã³ã ããã»ã¹ã¯ã¯ã©ã€ã¢ã³ãã®ãªã¯ãšã¹ãã«æªåœ±é¿ãäžããå¯èœæ§ãããããããªãœãŒã¹ã®äœ¿çšéãå€ãããªãããšãç£èŠããããšãéèŠã§ãã
- ã·ã¹ãã ã¡ããªã¯ã¹ã䜿çšãããšããµãŒããŒã®ã¹ã±ãŒãªã³ã°ãšå®¹éã®å¢å ã«é¢ããèšç»ãç«ãŠãããšãã§ãããããã·ã¹ãã ã¡ããªã¯ã¹ã远跡ããŠè©äŸ¡ããããšãéèŠã§ãã
ãã®ãããã¯ã«èå³ãããå Žåã¯ã次ã®ãªã³ã¯ãåç
§ããŠãã ããã
ãªã¯ãšã¹ãã®äŸïŒ
ããã¯ç§ãã¡ã®äŒæ¥ãªããžããªã§ãããç§èªèº«ã®ãªããžããªã§ãã ã¯ãšãªã®äŸãå«ãŸããŠããŸãã ããã«ã¯ select* from ã·ãªãŒãºããã®ã¯ãšãªã¯ãããŸããã çµåãåããæ¢è£œã®ã¯ãšãªããã§ã«ååšããŠãããçã®æ°å€ãèªã¿åãå¯èœãªäŸ¿å©ãªå€ (ãã€ããæéãªã©) ã«å€æã§ããèå³æ·±ãé¢æ°ã䜿çšããŠããŸãã ããããæŸãäžãã芳å¯ããåæããã¢ãã¿ãªã³ã°ã«è¿œå ãããããã«åºã¥ããŠã¢ãã¿ãªã³ã°ãæ§ç¯ããããšãã§ããŸãã
質å
質å: ãã©ã³ãã®å®£äŒã¯ããªããšãã£ããããŸããããããã§ãæ°ã«ãªããŸãããããžã§ã¯ãã§ã¯ã©ã®ãããªããã·ã¥ããŒãã䜿çšããŠããŸãã?
åç: ããã¯ç°ãªããŸãã ç§ãã¡ã顧客ã®ãšããã«è¡ããšããã®é¡§å®¢ã¯ãã§ã«ç¬èªã®ã¢ãã¿ãªã³ã°ãè¡ã£ãŠããããšããããŸãã ãããŠãç£èŠã«äœãè¿œå ããå¿
èŠããããã«ã€ããŠã客æ§ã«ã¢ããã€ã¹ããŸãã ææªã®ç¶æ³ã¯Zabbixã®å Žåã§ãã TopN ã°ã©ããæ§ç¯ããæ©èœããªãããã§ãã ç§ãã¡èªèº«ã䜿ã£ãŠããŸã
質å: AWR ã¬ããŒããéèšã«é¡äŒŒãããã®ã¯ãããŸãã? ãã®ãããªããšã«ã€ããŠãåç¥ã§ããïŒ
åç: ã¯ããAWR ãäœã§ãããã¯ç¥ã£ãŠããŸããçŽ æŽããããã®ã§ãã çŸæç¹ã§ã¯ãã»ãŒæ¬¡ã®ã¢ãã«ãå®è£
ããããŸããŸãªèªè»¢è»ããããŸãã äžå®ã®ééã§ãããã€ãã®ããŒã¹ã©ã€ã³ãåã PostgreSQL ãŸãã¯å¥ã®ã¹ãã¬ãŒãžã«æžã蟌ãŸããŸãã ã€ã³ã¿ãŒãããã§ã°ãŒã°ã«ã§æ€çŽ¢ãããšãããã«ãããŸãã ãã®ãããªãã®ã®éçºè
ã® XNUMX 人ã¯ãPostgreSQL ã¹ã¬ããã® sql.ru ãã©ãŒã©ã ã«åå ããŠããŸãã ããã§åœŒãæãŸããããšãã§ããŸãã ã¯ãããããããã®ããããŸãã䜿ããŸãã ããã«ãã®äžã«
PS1 postgres_exporter ã䜿çšããŠããå Žåãã©ã®ããã·ã¥ããŒãã䜿çšããŠããŸãã? ãããã¯ããã€ããããŸãã ãããã¯ãã§ã«æ代é ãã§ãã ããããã³ãã¥ããã£ãæŽæ°ããããã³ãã¬ãŒããäœæããã§ãããã?
PS2 pganalyze ã¯ãããã©ãŒãã³ã¹ã®ç£èŠãšèªåãã¥ãŒãã³ã°ã®ææ¡ã«éç¹ã眮ããç¬èªã® SaaS 補åã§ãããããåé€ãããŸããã
ç»é²ãŠãŒã¶ãŒã®ã¿ãã¢ã³ã±ãŒãã«åå ã§ããŸãã
ã©ã®èªå·±ãã¹ãå postgresql ã¢ãã¿ãªã³ã° (ããã·ã¥ããŒãä»ã) ãæé©ã ãšæããŸãã?
-
èŠèŽè ã®ïŒïŒ%ãZabbix + Alexey Lesovsky ããã®è¿œå ãzabbix 4.4ããŸã㯠libzbxpgsql + zabbix libzbxpgsql + zabbix3
-
èŠèŽè ã®ïŒïŒ%ãhttps://github.com/lesovsky/pgcenter0
-
èŠèŽè ã®ïŒïŒ%ãhttps://github.com/pg-monz/pg_monz0
-
èŠèŽè ã®ïŒïŒ%ãhttps://github.com/cybertec-postgresql/pgwatch22
-
èŠèŽè ã®ïŒïŒ%ãhttps://github.com/postgrespro/mamonsu2
-
èŠèŽè ã®ïŒïŒ%ãhttps://www.percona.com/doc/percona-monitoring-and-management/conf-postgres.html0
-
èŠèŽè ã®ïŒïŒ%ãpganalyze ã¯ç¬èªã® SaaS - åé€ã§ããŸãã1
-
èŠèŽè ã®ïŒïŒ%ãhttps://github.com/powa-team/powa1
-
èŠèŽè ã®ïŒïŒ%ãhttps://github.com/darold/pgbadger0
-
èŠèŽè ã®ïŒïŒ%ãhttps://github.com/darold/pgcluu0
-
èŠèŽè ã®ïŒïŒ%ãhttps://github.com/zalando/PGObserver0
-
èŠèŽè ã®ïŒïŒ%ãhttps://github.com/spotify/postgresql-metrics1
10 人ã®ãŠãŒã¶ãŒãæ祚ããŸããã 26åã®ãŠãŒã¶ãŒãæ£æš©ããã
åºæïŒ habr.com