Nikolai Samokhvalov æ°ã®ã¬ããŒããPostgreSQL ã®ãã¥ãŒãã³ã°ã«å¯Ÿããæ¥çã®ã¢ãããŒã: ããŒã¿ããŒã¹ã®å®éšãã®è»¢åãèªãããšããå§ãããŸãã
Shared_buffers = 25% â ããã¯å€ãã§ããããããšãå°ãªãã§ãã? ãããšãã¡ããã©ããã§ããïŒ ãã® (ããªãæ代é ãã®) æšå¥šäºé ãããªãã®ç¹å®ã®ã±ãŒã¹ã«é©åãã©ãããã©ããã£ãŠå€æããã®ã§ãããã?
postgresql.conf ãã©ã¡ãŒã¿ãã倧人ã®ããã«ãéžæããåé¡ã«åãçµãæãæ¥ãŸããã ç²ç®çãªãèªåãã¥ãŒããŒããèšäºãããã°ããã®æ代é ãã®ã¢ããã€ã¹ã®å©ããåããã«ã次ã®ããšã«åºã¥ããŠããŸãã
- ããŒã¿ããŒã¹äžã§å³å¯ã«æ€èšŒãããå®éšã¯ããæŠéãå®éšã«ã§ããã ãè¿ãæ¡ä»¶äžã§èªåçã«å€§éã«å®è¡ããã
- DBMSãOSã®æ©èœãæ·±ãç解ããã
Nancy CLI ã®äœ¿çš (
ããŒã¿ããŒã¹ã䜿ã£ãå®éšã«ã€ããŠã話ããŸãã ããã¯å幎ã»ã©ç¶ãç©èªã§ãã
ç§ã®ããšãå°ãã Postgres ã 14 幎以äžäœ¿çšããçµéšã å€ãã®ãœãŒã·ã£ã«ãããã¯ãŒãã³ã°äŒæ¥ãèšç«ãããŸããã Postgres ã¯ä»ãæãããããå Žæã§äœ¿çšãããŠããŸãã
Meetup ã® RuPostgres ã°ã«ãŒããäžç 2 äœã§ãã åŸã ã«2人ã«è¿ã¥ããŠããŸããã RuPostgres.orgã
ãããŠãHighload ãå«ãããŸããŸãªã«ã³ãã¡ã¬ã³ã¹ã® PC ã§ã¯ãæåããããŒã¿ããŒã¹ãç¹ã« Postgres ãæ åœããŠããŸãã
ãããŠããæ°å¹Žãç§ã¯ãããã 11 ã®ã¿ã€ã ãŸãŒã³ã§ Postgres ã³ã³ãµã«ãã£ã³ã°ã®å®è·µãåéããŸããã
ãããŠãæ°å¹Žåã«ãããè¡ã£ããšãããããã 2010 幎以æ¥ãPostgres ã䜿çšããã¢ã¯ãã£ããªæåäœæ¥ãããã°ããäžæããŠããŸããã DBA ã®äœæ¥ã«ãŒãã³ãã»ãšãã©å€ãã£ãŠããªãããšããããŠäŸç¶ãšããŠå€ãã®æäœæ¥ãå¿ èŠã§ããããšã«é©ããŸããã ãããŠããã«ãããã§äœããééã£ãŠããããã¹ãŠããã£ãšèªååããå¿ èŠããããšæããŸããã
ãŸãããã¹ãŠãªã¢ãŒãã§ãã£ããããã¯ã©ã€ã¢ã³ãã®ã»ãšãã©ã¯ã¯ã©ãŠãäžã«ãããŸããã ãããŠæããã«ãå€ãã®ããšã¯ãã§ã«èªååãããŠããŸãã ããã«ã€ããŠã¯åŸã§è©³ãã説æããŸãã ã€ãŸããããããã¹ãŠã®çµæãšããŠãå€æ°ã®ããŒã«ãã€ãŸããå€æ°ã®ããŒã¿ããŒã¹ã管çã§ããããã«ãã»ãšãã©ãã¹ãŠã® DBA ã¢ã¯ã·ã§ã³ãèªååããããçš®ã®ãã©ãããã©ãŒã ãå¿ èŠã§ãããšããèããçãŸããŸããã
ãã®ã¬ããŒãã«ã¯ä»¥äžã¯å«ãŸããŸããã
- ãç¹å¹è¬ããšæ¬¡ã®ãããªã¹ããŒãã¡ã³ã - 8 GB ãŸã㯠25% ã®shared_buffers ãèšå®ããã°åé¡ãããŸããã å ±æãããã¡ã«ã€ããŠã¯ããŸã説æããŸããã
- ããŒãã³ã¢ã®ãå èãã
ã©ããªãã®ïŒ
- ç§ãã¡ãé©çšãéçºããæé©åååããããŸãã ãã®éçšã§ããããçš®é¡ã®ã¢ã€ãã¢ãçãŸããããŸããŸãªããŒã«ãã»ãšãã©ãªãŒãã³ãœãŒã¹ã§äœæãããŸããã€ãŸãããªãŒãã³ãœãŒã¹ã§åºç€ãäœããŸãã ããã«ããã±ãããããããã¹ãŠã®ã³ãã¥ãã±ãŒã·ã§ã³ã¯äºå®äžãªãŒãã³ãœãŒã¹ã§ãã ç§ãã¡ãçŸåšè¡ã£ãŠããããšã次ã®ãªãªãŒã¹ã§äœãè¡ãããããªã©ã確èªã§ããŸãã
- ãŸããå°èŠæš¡ãªæ°èäŒæ¥ãã倧äŒæ¥ãŸã§ãå€ãã®äŒæ¥ã§ãããã®ååãããŒã«ã䜿çšããçµéšãå¿ èŠã§ãã
ããã¯ã©ã®ããã«çºå±ããŠããã®ã§ããããïŒ
ãŸããDBA ã®äž»ãªã¿ã¹ã¯ã¯ãã€ã³ã¹ã¿ã³ã¹ã®äœæãããã¯ã¢ããã®å±éãªã©ã確å®ã«è¡ãããšã«å ããŠãããã«ããã¯ãèŠã€ããŠããã©ãŒãã³ã¹ãæé©åããããšã§ãã
ä»ã¯ãããªæãã§èšçœ®ãããŠããŸãã ã¢ãã¿ãªã³ã°ãèŠããšäœããèŠããŸããã詳现ãããã€ãæ¬ ããŠããŸãã ç§ãã¡ã¯ãéåžžã¯æã§ããæ éã«æãå§ããäœããã®æ¹æ³ã§ãããã©ãããã°ããããç解ããŸãã
ãããŠãã¢ãããŒãã¯XNUMXã€ãããŸãã Pg_stat_statements ã¯ãé ãã¯ãšãªãèå¥ããããã®ããã©ã«ãã®ãœãªã¥ãŒã·ã§ã³ã§ãã ãããŠãpgBadgerã䜿çšããPostgresãã°ã®åæã
ã©ã®ã¢ãããŒãã«ãé倧ãªæ¬ ç¹ããããŸãã æåã®ã¢ãããŒãã§ã¯ããã¹ãŠã®ãã©ã¡ãŒã¿ãç Žæ£ããŸããã ãããŠãåãã?ãã«çããã°ã«ãŒã SELECT * FROM ããŒãã«ãèŠããšã Postgres 10 以éã¯ã$ãã§ãããããã€ã³ããã¯ã¹ ã¹ãã£ã³ãªã®ãã·ãŒã±ã³ã¹ ã¹ãã£ã³ãªã®ãã¯ããããŸããã ããã¯ãã©ã¡ãŒã¿ã«å€§ããäŸåããŸãã ããã«ãŸãã«åºçŸããå€ãä»£å ¥ãããšãã€ã³ããã¯ã¹ ã¹ãã£ã³ã«ãªããŸãã ããã«ããŒãã«ã® 90% ãå ããå€ãä»£å ¥ãããšãPostgres ã¯çµ±èšæ å ±ãèªèããŠãããããã·ãŒã±ã³ã¹ ã¹ãã£ã³ãæããã«ãªããŸãã ããã€ãã®äœæ¥ãé²è¡äžã§ããããã㯠pg_stat_statements ã®å€§ããªæ¬ ç¹ã§ãã
ãã°åæã®æ倧ã®æ¬ ç¹ã¯ãååãšããŠãlog_min_duration_statement = 0ãã蚱容ã§ããªãããšã§ãã ãããŠãããã«ã€ããŠã話ããŸãã ãããã£ãŠãå šäœåã¯èŠããŸããã ãŸããéåžžã«é«éãªäžéšã®ã¯ãšãªã¯å€§éã®ãªãœãŒã¹ãæ¶è²»ããå¯èœæ§ããããŸããããããå€ãäžåã£ãŠãããã衚瀺ãããŸããã
DBA ã¯èŠã€ããåé¡ãã©ã®ããã«è§£æ±ºããŸãã?
ããšãã°ãããåé¡ãèŠã€ãããŸããã éåžžã¯äœãè¡ãããŸãã? ããªããéçºè ã§ããã°ãåããµã€ãºã§ã¯ãªãã€ã³ã¹ã¿ã³ã¹ã§äœããè¡ãããšã«ãªãã§ãããã DBA ã§ããã°ãã¹ããŒãžã³ã°ããããŸãã ãããŠããã㯠XNUMX ã€ã ãã§ãã ãããŠåœŒã¯XNUMXãæé ããŠããŸããã ãããŠæ¬çªã«é²ããšæããŸãã ããã«ãçµéšè±å¯ãª DBA ã§ãã£ãŠããæ¬çªç°å¢ã®ã¬ããªã«ã§ãã§ãã¯ã€ã³ããŸãã ãããŠãäžæçãªã€ã³ããã¯ã¹ãäœæããããã圹ç«ã€ããšã確èªããŠåé€ãã移è¡ãã¡ã€ã«ã«å ¥ããããšãã§ããããã«éçºè ã«æž¡ãããšãèµ·ãããŸãã ããã¯ä»èµ·ãã£ãŠããäžçš®ã®ãã³ã»ã³ã¹ã§ãã ãããŠãããã¯åé¡ã§ãã
- æ§æã調æŽããŸãã
- ã€ã³ããã¯ã¹ã®ã»ãããæé©åããŸãã
- SQL ã¯ãšãªèªäœãå€æŽããŸã (ããã¯æãå°é£ãªæ¹æ³ã§ã)ã
- 容éãè¿œå ããŸã (ã»ãšãã©ã®å Žåãæãç°¡åãªæ¹æ³)ã
ãããã«ã€ããŠã¯å€ãã®ããšãèµ·ãã£ãŠããŸãã Postgres ã«ã¯ãã³ãã«ããããããããŸãã ç¥ãã¹ãããšããããããããŸãã ãã®ã«ã³ãã¡ã¬ã³ã¹ã®äž»å¬è ã®ãããã§ããããPostgres ã«ã¯å€ãã®ã€ã³ããã¯ã¹ããããŸãã ãããŠãããããã¹ãŠãç¥ã£ãŠããå¿ èŠãããããããé DBA ãé»éè¡ãå®è·µããŠããããã«æããåå ã§ãã ã€ãŸããããããã¹ãŠãæ®éã«ç解ã§ããããã«ãªãã«ã¯ã10幎éå匷ããå¿ èŠããããŸãã
ãããŠç§ã¯ãã®é»éè¡ãšæŠãæŠå£«ã§ãã ç§ã¯ãã¯ãããžãŒãååšããããã«ãã¹ãŠããããããšæã£ãŠããŸãããããããã¹ãŠã«çŽæã¯ãããŸããã
人çã®äŸ
ç§èªèº«ã®ãããžã§ã¯ããå«ããå°ãªããšã 1 ã€ã®ãããžã§ã¯ãã§ããã芳å¯ããŸããã å¥ã®ããã°æçš¿ã§ã¯ãdefault_statistict_target ã®å€ã¯ 000 ãé©åã§ãããšè¿°ã¹ãŠããŸãã ããŠãæ¬çªç°å¢ã§è©ŠããŠã¿ãŸãããã
ãããŠãXNUMX 幎åŸãç§ãã¡ã¯ä»æ¥è©±ããŠããããŒã¿ããŒã¹ã®å®éšã®å©ããåããŠãç§ãã¡ã®ããŒã«ã䜿çšããŠããã€ãŠã®ãã®ãšçŸåšã©ããªã£ãããæ¯èŒã§ããããã«ãªããŸããã
ãã®ããã«ã¯å®éšãäœæããå¿ èŠããããŸãã XNUMX ã€ã®éšåããæ§æãããŸãã
- XNUMXã€ç®ã¯ç°å¢ã§ãã ããŒããŠã§ã¢ãå¿ èŠã§ãã ãããŠãç§ãã©ããã®äŒç€Ÿã«æ¥ãŠå¥çŽãçµã¶ãšãã¯ãæ¬çªç°å¢ãšåãããŒããŠã§ã¢ãæäŸããããã«èšããŸãã ããªãã®ãã¹ã¿ãŒããšã«ããã®ãããªããŒããŠã§ã¢ãå°ãªããšã XNUMX ã€å¿ èŠã§ãã ãã㯠Amazon ãŸã㯠Google ã®ã€ã³ã¹ã¿ã³ã¹ä»®æ³ãã·ã³ã§ãããããŸã£ããåãããŒããŠã§ã¢ãå¿ èŠã§ãã ã€ãŸããç°å¢ãåæ§ç¯ãããã®ã§ãã ãããŠãç°å¢ã®æŠå¿µã«ã¯ãPostgres ã®ã¡ãžã£ãŒ ããŒãžã§ã³ãå«ãŸããŸãã
- XNUMX çªç®ã®éšåãç§ãã¡ã®ç 究ã®å¯Ÿè±¡ã§ãã ããã¯ããŒã¿ããŒã¹ã§ãã ããã€ãã®æ¹æ³ã§äœæã§ããŸãã ãã®æ¹æ³ã説æããŸãã
- XNUMX çªç®ã®éšåã¯è² è·ã§ãã ãããæãé£ããç¬éã§ãã
- ãã㊠XNUMX çªç®ã®éšåã¯äœããã§ãã¯ããããã€ãŸãäœãäœãšæ¯èŒãããã§ãã æ§æå ã® XNUMX ã€ä»¥äžã®ãã©ã¡ãŒã¿ãå€æŽããããã€ã³ããã¯ã¹ãäœæãããã§ãããšããŸãã
ç§ãã¡ã¯å®éšãéå§ããŠããŸãã ããã pg_stat_statements ã§ãã å·ŠåŽã¯äœãèµ·ãã£ãã®ãã§ãã å³åŽ - äœãèµ·ãã£ãã®ãã
å·ŠåŽã§ã¯ãdefault_statistics_target = 100ãå³åŽã§ã¯ =1 ããã圹ã«ç«ã£ãããšãããããŸãã å šäœãšããŠããã¹ãŠã 000% æ¹åãããŸããã
ããããäžã«ã¹ã¯ããŒã«ãããšãpgBadger ãŸã㯠pg_stat_statements ããã®ãªã¯ãšã¹ãã®ã°ã«ãŒãã衚瀺ãããŸãã éžæè¢ã¯ 88 ã€ãããŸãã äžéšã®ãªã¯ãšã¹ãã XNUMX% æžå°ããããšãããããŸãã ããã§ãšã³ãžãã¢ãªã³ã°ã®ã¢ãããŒããç»å ŽããŸãã ãªãæ²ãã ã®ãçåã«æãã®ã§ãããã«å éšãæãäžããããšãã§ããŸãã çµ±èšã§äœãèµ·ãã£ãã®ããç解ããå¿ èŠããããŸãã çµ±èšã®ãã±ãããå¢ãããšçµæãæªããªãçç±ã
ãŸãã¯ãæãããšã¯ã§ããŸãããããALTER TABLE ... ALTER COLUMNããå®è¡ããŠããã®åã®çµ±èšã« 100 ãã±ãããè¿ããŸãã ãããŠãå¥ã®å®éšãè¡ã£ãŠããã®ãããã圹ã«ç«ã£ãããšã確èªããŸãã å šãŠã ããã¯ãçŽæã§ã¯ãªãããŒã¿ã«åºã¥ããŠå šäœåãææ¡ããææ決å®ãè¡ãã®ã«åœ¹ç«ã€ãšã³ãžãã¢ãªã³ã° ã¢ãããŒãã§ãã
ä»ã®åéã®äŸãããã€ãã ãã¹ãã§ã¯é·å¹Žã«ãããCIãã¹ããè¡ââãããŠããŸããã ãããŠãæ£ããèããæã£ãŠãããããžã§ã¯ãã¯ãèªåãã¹ããªãã§ã¯åç¶ããŸããã
ä»ã®æ¥çãã€ãŸãèªç©ºæ¥çãèªåè»æ¥çã§ã¯ã空æ°ååŠããã¹ããããšãã«å®éšãè¡ãæ©äŒããããŸãã ç§ãã¡ã¯ãå³é¢ããäœããçŽæ¥å®å®ã«æã¡äžããããããã«è»ããã©ãã¯ã«ä¹ãããããŸããã ããšãã°ã颚æŽããããŸãã
ä»ã®æ¥çã®èŠ³å¯ããçµè«ãå°ãåºãããšãã§ããŸãã
ãŸããç§ãã¡ã«ã¯ç¹å¥ãªç°å¢ããããŸãã æ¬çªã«è¿ã¥ããŠããŸãããããã«è¿ãããã§ã¯ãããŸããã ãã®äž»ãªç¹åŸŽã¯ãå®äŸ¡ã§åçŸæ§ããããå¯èœãªéãèªååãããŠããå¿ èŠãããããšã§ãã ãŸãã詳现ãªåæãè¡ãããã®ç¹å¥ãªããŒã«ãå¿ èŠã§ãã
ãããããé£è¡æ©ãæã¡äžããŠé£è¡ãããšããç¿Œã®è¡šé¢ã XNUMX ããªã¡ãŒãã«åäœã§ç 究ããæ©äŒã¯ã颚æŽå®éšãããå°ãªãã§ãããã ããã«å€ãã®èšºæããŒã«ããããŸãã ç§ãã¡ã¯ç©ºã®é£è¡æ©ã«ä¹ããããšãã§ããªããããªãããéãè·ç©ãéã¶äœè£ããããŸãã Postgresãåæ§ã§ãã å Žåã«ãã£ãŠã¯ãå®éšäžã«å®å šãªã¯ãšãª ãã°ãæå¹ã«ããããšããããŸãã ãããŠãæ¬çªç°å¢ã§ã¯ãããè¡ããããããŸããã auto_explain ã䜿çšããŠãããæå¹ã«ããããšãèšç»ããŠãããããããŸããã
å ã»ã©ãè¿°ã¹ãããã«ãé«åºŠãªèªååãšã¯ããã¿ã³ãæŒããŠç¹°ãè¿ãããšãæå³ããŸãã ããã¯ãå€ãã®å®éšãè¡ãããé ä¿¡ãããããã«ãªãããã§ãã
Nancy CLI - ãããŒã¿ããŒã¹ç 究æãã®åºç€
ããã§ç§ãã¡ã¯ãã®ããšãè¡ããŸããã ã€ãŸããç§ã¯ã»ãŒ XNUMX 幎åã® XNUMX æã«ãããã®ã¢ã€ãã¢ã«ã€ããŠè©±ããŸããã ãããŠããããã Nancy CLI ããã§ã«ãªãŒãã³ãœãŒã¹ã§æäŸãããŠããŸãã ããã¯ããŒã¿ããŒã¹ã©ããæ§ç¯ããããã®åºç€ã§ãã
ãã¡ããããŸã éçºäžã®ãã®ããããããããŸãã ããã«ã¯ããããã®ã¢ã€ãã¢ããããŸãã ããããããã¯ç§ãã¡ãã»ãŒæ¯æ¥äœ¿çšããŠãããã®ã§ãã ãããŠãã¢ã€ãã¢ãããã°ã40 è¡ãåé€ãããšããã¹ãŠã IO ã«åž°çããã®ã¯ãªãã§ããããããã®åŸãå®éšãå®æœããŠè©³çŽ°ã調ã¹ãŠäœãèµ·ãã£ãŠããã®ããç解ããå€åºå ã§ä¿®æ£ãè©Šã¿ãããšãã§ããŸãã ã€ãŸããå®éšãè¡ã£ãŠããã®ã§ãã ããšãã°ãäœãã埮調æŽããŠãæçµçã«äœãèµ·ãããã確èªããŸãã ãããŠãæ¬çªç°å¢ã§ã¯ãããè¡ããŸããã ãããã¢ã€ãã¢ã®æ¬è³ªã§ãã
ããã¯ã©ãã§æ©èœããŸãã? ããã¯ããŒã«ã«ã§åäœããŸããã€ãŸããã©ãã§ãå®è¡ã§ããMacBook äžã§ãå®è¡ã§ããŸãã 枯湟åŽåè ãå¿ èŠã§ããè¡ããŸãããã ããã ãã§ãã ããŒããŠã§ã¢äžã®äžéšã®ã€ã³ã¹ã¿ã³ã¹ãŸãã¯ä»®æ³ãã·ã³äžã§ãã©ãã«ã§ãå®è¡ã§ããŸãã
ãŸããEC2 ã€ã³ã¹ã¿ã³ã¹ã® Amazon ã§ã¹ãããçã«ãªã¢ãŒãã§å®è¡ããæ©äŒããããŸãã ãããŠãããã¯ãšãŠãçŽ æŽãããæ©äŒã§ãã ããšãã°ãæšæ¥ãç§ãã¡ã¯ i500 ã€ã³ã¹ã¿ã³ã¹ã§æãè¥ããã®ããå§ã㊠i3-3-xlarge ãŸã§ã® 16 以äžã®å®éšãå®æœããŸããã 500 åã®å®éšã«ã¯ 64 ãã«ããããŸããã ãããã15åéç¶ããŸããã ã€ãŸããããã§ã¯ã¹ãããã䜿çšãããŠãããããéåžžã«å®ããªããŸã - 70% å²åŒãAmazon ã®ç§åäœã®è«æ±ã§ãã ããããã®ããšãã§ããŸãã æ¬æ Œçãªç 究ãã§ããŸãã
ãŸããPostgres ã® 12 ã€ã®ã¡ãžã£ãŒ ããŒãžã§ã³ããµããŒããããŠããŸãã ããã€ãã®å€ãããŒãžã§ã³ãšæ°ãã第 XNUMX ããŒãžã§ã³ãå®æãããã®ã¯ããã»ã©é£ãããããŸããã
ãªããžã§ã¯ã㯠XNUMX ã€ã®æ¹æ³ã§å®çŸ©ã§ããŸãã ããïŒ
- ãã³ã/SQL ãã¡ã€ã«ã
- äž»ãªæ¹æ³ã¯ãPGDATA ãã£ã¬ã¯ããªã®ã¯ããŒã³ãäœæããããšã§ãã ååãšããŠãããã¯ã¢ãããµãŒããŒããååŸãããŸãã éåžžã®ãã€ã㪠ããã¯ã¢ãããããå Žåã¯ãããããã¯ããŒã³ãäœæã§ããŸãã ã¯ã©ãŠãã䜿çšããŠããå Žåã¯ãAmazon ã Google ãªã©ã®ã¯ã©ãŠã ãªãã£ã¹ããããè¡ã£ãŠãããŸãã ããã¯ãå®éã®ãããã¯ã·ã§ã³ã®ã¯ããŒã³ãäœæããæãéèŠãªæ¹æ³ã§ãã ãã®ããã«ããŠå±éããŠãããŸãã
- æåŸã®æ¹æ³ã¯ãPostgres ã§äœããã©ã®ããã«åäœããããç解ãããå Žåã®ç 究ã«é©ããŠããŸãã ããã¯pgãã³ãã§ãã pgbenchã䜿çšããŠçæã§ããŸãã ããã¯ãdb-pgbenchããªãã·ã§ã³ã® XNUMX ã€ã§ãã ããªãã¯åœŒã«ã¹ã±ãŒã«ãäŒããŸãã ãããŠãåè¿°ããããã«ããã¹ãŠã¯ã¯ã©ãŠãã§çæãããŸãã
ãããŠããŒãããŸã:
- ããŒã㯠XNUMX ã€ã® SQL ã¹ã¬ããã§å®è¡ã§ããŸãã ããã¯æãåå§çãªæ¹æ³ã§ãã
- ãããŠãè² è·ããšãã¥ã¬ãŒãããããšãã§ããŸãã ãããŠããŸã次ã®æ¹æ³ã§ããããšãã¥ã¬ãŒãã§ããŸãã ãã¹ãŠã®ãã°ãåéããå¿ èŠããããŸãã ãããŠãããã¯çãã§ãã ãã®çç±ã説æããŸãã ãããŠãNancy ã«çµã¿èŸŒãŸããŠãã pgreplay ã䜿çšããŠãã¬ã€ããŸãã
- ãŸãã¯å¥ã®ãªãã·ã§ã³ã ããããã¯ã©ããããŒãã§ãããçšåºŠã®åŽåããããŠè¡ããŸãã æŠéã·ã¹ãã ã®çŸåšã®è² è·ãåæãããªã¯ãšã¹ãã®äžäœã°ã«ãŒããæœåºããŸãã ãããŠãpgbench ã䜿çšãããšãå®éšå®€ã§ãã®è² è·ããšãã¥ã¬ãŒãã§ããŸãã
- äœããã® SQL ãå®è¡ããå¿ èŠããããŸããã€ãŸããããçš®ã®ç§»è¡ããã§ãã¯ããããã«ã€ã³ããã¯ã¹ãäœæãããã㧠ANALAZE ãå®è¡ããŸãã ãããŠãç空åã®åãšåŸã«äœãèµ·ãã£ãã®ããèŠãŠã¿ãŸãããã äžè¬ã«ããããã SQLã
- èšå®å ã® 100 ã€ä»¥äžã®ãã©ã¡ãŒã¿ãå€æŽããŸãã ããšãã°ãAmazon ã§ãã©ãã€ã ããŒã¿ããŒã¹ã® XNUMX åã®å€ããã§ãã¯ããããã«æ瀺ã§ããŸãã ãããŠæ°æé以å ã«çµæãåŸãããŸãã éåžžããã©ãã€ãèŠæš¡ã®ããŒã¿ããŒã¹ãå±éããã«ã¯æ°æéããããŸãã ãã ããéçºäžã®ãããããããã·ãªãŒãºåãå¯èœã§ããã€ãŸããåããµãŒããŒäžã§åã pgdata ãäžè²«ããŠäœ¿çšããŠç¢ºèªã§ããŸãã Postgres ãåèµ·åããããã£ãã·ã¥ããªã»ãããããŸãã ãããŠãè² è·ãé§åããããšãã§ããŸãã
- pg ã¹ãããã·ã§ããããå§ãŸãããŸããŸãªãã¡ã€ã«ã®æããã£ã¬ã¯ããªã«å°çããŸããSTAT***ã ãããŠæãèå³æ·±ãã®ã¯ pg_stat_statements ãš pg_stat_kcacke ã§ãã ãããã¯ãªã¯ãšã¹ããåæãã XNUMX ã€ã®æ¡åŒµæ©èœã§ãã ãŸããpg_stat_bgwriter ã«ã¯ãpgwriter ã®çµ±èšã ãã§ãªãããã§ãã¯ãã€ã³ãããããã¯ãšã³ãèªäœãããŒã㣠ãããã¡ã眮ãæããæ¹æ³ãå«ãŸããŠããŸãã ãããŠããããèŠãã®ã¯ã©ããèå³æ·±ãã§ãã ããšãã°ãshared_buffers ãèšå®ãããšãã«ãå šå¡ãã©ãã ã眮æããããèŠãã®ã¯éåžžã«èå³æ·±ãã§ãã
- Postgres ãã°ãå°çããŠããŸãã æºåãã°ãšããŒãåçãã°ã® XNUMX ã€ã®ãã°ã
- æ¯èŒçæ°ããæ©èœã¯ FlameGraphs ã§ãã
- ãŸããããŒãã®åçã« pgreplay ãŸã㯠pgbench ãªãã·ã§ã³ã䜿çšããå Žåããããã®åºåã¯ãã€ãã£ãã«ãªããŸãã ãããŠãã¬ã€ãã³ã·ãŒãšTPSã衚瀺ãããŸãã 圌ãããããã©ã®ããã«èŠãã®ããç解ããããšãã§ããã§ãããã
- ã·ã¹ãã ã€ã³ãã©ã¡ãŒã·ã§ã³ã
- åºæ¬ç㪠CPU ãš IO ãã§ãã¯ã ãã㯠Amazon ã® EC2 ã€ã³ã¹ã¿ã³ã¹ã«åœãŠã¯ãŸããŸãã100 ã€ã®ã¹ã¬ãã㧠100 åã®åäžã®ã€ã³ã¹ã¿ã³ã¹ãèµ·åãããã㧠10 åã®ç°ãªãå®è¡ãå®è¡ãããå Žåã000 åã®å®éšãå¿ èŠã«ãªããŸãã ãããŠããã§ã«èª°ãã«ãã£ãŠæå§ãããŠããæ¬ é¥ã®ããã€ã³ã¹ã¿ã³ã¹ã«ééããªãããã«ããå¿ èŠããããŸãã ä»ã®ããŒããŠã§ã¢ããã®ããŒããŠã§ã¢äžã§ã¢ã¯ãã£ãã«ãªã£ãŠããããªãœãŒã¹ãã»ãšãã©æ®ã£ãŠããªãã ãã®ãããªçµæã¯æšãŠãã»ããããã§ãããã ãŸããAlexey Kopytov ã® sysbench ã®å©ããåããŠãä»ã®ãã®ãšæ¯èŒã§ããããã€ãã®çããã§ãã¯ãå®è¡ããŸããã€ãŸããCPU ãã©ã®ããã«åäœããIO ãã©ã®ããã«åäœããããç解ã§ããŸãã
ããŸããŸãªäŒæ¥ã®äŸã«åºã¥ãæè¡çãªå°é£ã¯äœã§ãã?
ãã°ã䜿çšããŠå®éã®ããŒããç¹°ãè¿ããããšããŸãã ãªãŒãã³ãœãŒã¹ã® pgreplay ã§æžãããŠããã°çŽ æŽãããã¢ã€ãã¢ã§ãã ç§ãã¡ã¯ããã䜿ã£ãŠããŸãã ãã ããæ£åžžã«æ©èœããã«ã¯ããã©ã¡ãŒã¿ãŒãšã¿ã€ãã³ã°ã䜿çšããŠå®å šãªã¯ãšãª ãã°ãæå¹ã«ããå¿ èŠããããŸãã
æéãšã¿ã€ã ã¹ã¿ã³ãã«ã¯ããã€ãã®è€éãªåé¡ããããŸãã ãã®ãããã³å šäœã空ã«ããŸãã äžçªã®åé¡ã¯ããããè²·ãäœè£ããããã©ããã§ãã
åé¡ã¯ããããå©çšã§ããªãå¯èœæ§ãããããšã§ãã ãŸããã©ã®ãããªã¹ããªãŒã ããã°ã«æžã蟌ãŸããããç解ããå¿ èŠããããŸãã pg_stat_statements ãããå Žåã¯ããã®ã¯ãšãª (ãªã³ã¯ã¯ã¹ã©ã€ãå ã«ãããŸã) ã䜿çšããŠãXNUMX ç§ãããã«æžã蟌ãŸãããã€ãæ°ãããããææ¡ã§ããŸãã
ãªã¯ãšã¹ãã®é·ããèŠãŠã¿ãŸãããã ãã©ã¡ãŒã¿ããªããšããäºå®ã¯ç¡èŠããŠããŸããããªã¯ãšã¹ãã®é·ããšããªã¯ãšã¹ãã XNUMX ç§ãããã«äœåå®è¡ããããã¯ããã£ãŠããŸãã ãã®ããã«ããŠãXNUMX ç§ãããã®ããããã®ãã€ãæ°ãæšå®ã§ããŸãã äºåºŠééãããããããŸããããããããã°å¿ ãé çªãããããŸãã
ãã®ãªã¯ãšã¹ã㯠802 ç§ããã 300 åå®è¡ãããããšãããããŸãã ãããŠãbytes_per sec â XNUMX KB/s ããã©ã¹ãŸãã¯ãã€ãã¹ã§æžã蟌ãŸããããšãããããŸãã ãããŠãååãšããŠãç§ãã¡ã¯ãã®ãããªæµããåãå ¥ããããšãã§ããŸãã
ãããïŒ å®éã«ã¯ãããŸããŸãªãã° ã·ã¹ãã ãååšããŸãã ãããŠã人ã ã®ããã©ã«ãã¯éåžžãsyslogãã§ãã
syslog ãããå Žåã¯ã次ã®ãããªç»åã衚瀺ãããå¯èœæ§ããããŸãã pgbench ã䜿çšããã¯ãšãª ãã°ãæå¹ã«ããŠãäœãèµ·ããããèŠãŠã¿ãŸãããã
ãã°ãªã - ããã¯å·ŠåŽã®åã§ãã 161 TPS ãç²åŸããŸããã syslog ã䜿çšãããšããã㯠Amazon ã® Ubuntu 000 ã§ã16.04 TPS ãåŸãããŸãã ãããŠãä»ã® 37 ã€ã®ãã®ã³ã°æ¹æ³ã«å€æŽãããšãç¶æ³ã¯ã¯ããã«è¯ããªããŸãã ã€ãŸããäœäžãããšäºæ³ããŠããŸããããåçšåºŠã§ã¯ãããŸããã§ããã
ãŸããCentOS 7 ã§ã¯ãjournald ãåå ããŠãããæ€çŽ¢ããããããã«ãã°ããã€ããªåœ¢åŒã«å€æããŸãããTPS 㧠44 åãäœäžãããšããæªå€¢ããããŸããã
ãããŠãããã人ã ãçããŠããããšãªã®ã§ãã ãããŠå€ãã®å ŽåãäŒæ¥ãç¹ã«å€§äŒæ¥ã§ã¯ããããå€ããã®ã¯éåžžã«å°é£ã§ãã syslog ããé¢ããããå Žåã¯ãsyslog ããé¢ããŠãã ããã
- IOPS ãšæžã蟌ã¿ãããŒãè©äŸ¡ããŸãã
- ãã° ã·ã¹ãã ã確èªããŠãã ããã
- äºæž¬ãããè² è·ãé床ã«å€§ããå Žåã¯ããµã³ããªã³ã°ãæ€èšããŠãã ããã
pg_stat_statements ããããŸãã ç§ãèšã£ãããã«ãããã¯ããã«ããã«éããããŸããã ãããŠããªã¯ãšã¹ãã®åã°ã«ãŒããç¹å¥ãªæ¹æ³ã§ãã¡ã€ã«ã«åã蟌ãã§èšè¿°ããããšãã§ããŸãã ãããŠãpgbench ã®éåžžã«äŸ¿å©ãªæ©èœã䜿çšã§ããŸããããã¯ãã-fããªãã·ã§ã³ã䜿çšããŠè€æ°ã®ãã¡ã€ã«ãæ¿å ¥ããæ©èœã§ãã
å€ãã®ã-fããç解ããŸãã ãŸããæ«å°Ÿã®ã@ãã䜿çšãããšãåãã¡ã€ã«ã«ã©ã®ãããªå ±æãå¿ èŠããç¥ãããšãã§ããŸãã ã€ãŸãã10% ã®å Žåã¯ãããå®è¡ãã20% ã®å Žåã¯ãããå®è¡ãããšèšããŸãã ããã«ãããæ¬çªç°å¢ã§èŠããããã®ã«ããã«è¿ã¥ãããšãã§ããŸãã
æ¬çªç°å¢ã«ãããã®ãã©ããã£ãŠç解ããã®ã§ãããã? ã©ã®ãããªã·ã§ã¢ãã©ã®ããã«ããŠ? ããã¯ã¡ãã£ãšããäœè«ã§ãã ããXNUMXã€ã®è£œåããããŸã
圌ã¯å°ãç°ãªãçç±ã§çãŸããŸããã ç£èŠã ãã§ã¯ååã§ã¯ãªããšããçç±ããã§ãã ã€ãŸããããªãã¯æ¥ãŠãæ ¹æ¬ãèŠãŠãååšããåé¡ãèŠãŠãã ããã ãããŠãååãšããŠãhealth_check ãå®è¡ããŸãã çµéšè±å¯ãª DBA ã§ããã°ãhealth_check ãå®è¡ããŸãã ã€ã³ããã¯ã¹ã®äœ¿çšæ³ãªã©ãæ€èšããŸãããOKmeter ããæã¡ã§ããã°ãããã¯çŽ æŽãããããšã§ãã ãã㯠Postgres ã®åªããç£èŠã§ãã OKmeter.io â ã€ã³ã¹ããŒã«ããŠãã ããããã¹ãŠãããŸãè¡ãããŠããŸãã ææã§ãã
æã£ãŠããªãå Žåã¯ãéåžžã¯ããŸãæã£ãŠããŸããã ç£èŠã«ã¯éåžžãCPUãIOããããŠäºçŽããããããã ãã§ãã ãããŠãããã«å¿ èŠããããŸãã èªåããã¥ãŒã ãã©ã®ããã«æ©èœãããããã§ãã¯ãã€ã³ããã©ã®ããã«æ©èœãããã確èªããå¿ èŠããããŸããIO ã§ã¯ããã§ãã¯ãã€ã³ãã bgwriter ãããã¯ãšã³ããªã©ããåé¢ããå¿ èŠããããŸãã
åé¡ã¯ã倧äŒæ¥ãæ¯æŽããå Žåãäœããããã«å®è¡ã§ããªãããšã§ãã OKmeterãããã«è²·ãããšã¯ã§ããŸããã ããããããå幎以å ã«è²·ããããããªãã äžéšã®è·ç©ã¯ããã«é éã§ããŸããã
ãããŠãäœãã€ã³ã¹ããŒã«ããå¿ èŠã®ãªãç¹å¥ãªããŒã«ãå¿ èŠã§ãããã€ãŸãæ¬çªç°å¢ã§ã¯äœãã€ã³ã¹ããŒã«ããå¿ èŠããªããšããèãã«è³ããŸããã ãããã©ãããããããŸãã¯ãããå®è¡ããç£èŠãµãŒããŒã«ã€ã³ã¹ããŒã«ããŸãã ãããŠããªãã¬ãŒãã£ã³ã° ã·ã¹ãã ããã¡ã€ã« ã·ã¹ãã ãPostgres èªäœãªã©ãå€ãã®ããšãåæããå®çšŒåç°å¢ã§çŽæ¥å®è¡ã§ããããã€ãã®è»œãã¯ãšãªãäœæããäœã倱æããŸããã
ç§ãã¡ã¯ããã Postgres ãã§ãã¯ã¢ãããšåŒã³ãŸããã å»åŠçã«èšãã°å®æå¥åº·èšºæã§ãã èªåè»ãããŒãã«ãããã®ã§ããã°ãã¡ã³ããã³ã¹ã®ãããªãã®ã§ãã ãã©ã³ãã«ããããŸãããè»ã®ã¡ã³ããã³ã¹ã¯å幎ãŸãã¯XNUMX幎ããšã«è¡ããŸãã æ ç¹ã®ã¡ã³ããã³ã¹ã¯è¡ã£ãŠããŸããïŒ ã€ãŸããå®æçã«æ·±ã調æ»ãè¡ã£ãŠããŸãã? ããã¯å¿ ãè¡ãããªããã°ãªããŸããã ããã¯ã¢ãããäœæããŠããæ€æ»ãè¡ãå Žåããããåæ§ã«éèŠã§ãã
ãããŠãç§ãã¡ã¯ãã®ãããªããŒã«ãæã£ãŠããŸãã 掻çºã«åºçŸãå§ããã®ã¯ããã XNUMX ãæã»ã©åã§ãã 圌ã¯ãŸã è¥ãã§ãããããããã®ããšããããŸãã
æãã圱é¿åã®ãããã¯ãšãª ã°ã«ãŒãã®åé - Postgres-checkup ã®ã¬ããŒã K003
ãããŠãå ±åã°ã«ãŒã K ããããŸãããããŸã§ã« 003 ã€ã®å ±åããããŸãã ãããŠããã®ãããªã¬ããŒãKXNUMXããããŸãã pg_stat_statements ã total_time ã§ãœãŒããããã®ããããã§ãã
ãªã¯ãšã¹ã ã°ã«ãŒãã total_time ã§äžŠã¹æ¿ãããšãã·ã¹ãã ã«æãè² è·ããããŠãããã€ãŸãããå€ãã®ãªãœãŒã¹ãæ¶è²»ããŠããã°ã«ãŒããäžçªäžã«è¡šç€ºãããŸãã ã¯ãšãªã°ã«ãŒãã«ååãä»ããã®ã¯ãªãã§ãã? ãã©ã¡ãŒã¿ãŒãç Žæ£ããããã§ãã ãããã¯ãã¯ããªã¯ãšã¹ãã§ã¯ãªãããªã¯ãšã¹ãã®ã°ã«ãŒãã§ããã€ãŸããæœè±¡åãããŠããŸãã
ãŸããäžããäžãŸã§æé©åãããšããªãœãŒã¹ã軜æžãããã¢ããã°ã¬ãŒããå¿ èŠã«ãªãææãé ããããšã«ãªããŸãã ããã¯ãéãç¯çŽããã®ã«ãšãŠãè¯ãæ¹æ³ã§ãã
ããããããã¯ããŠãŒã¶ãŒãã±ã¢ããã«ã¯ããŸãè¯ãæ¹æ³ã§ã¯ãããŸããããŠãŒã¶ãŒã 15 ç§åŸ ã€ãšããããŸãã§ã¯ãããã®ã®éåžžã«è¿·æãªã±ãŒã¹ã¯èŠãããªããããããªãããã§ãã å šäœãšããŠããããã¯éåžžã«ãŸãã§ããããç®ã«ããããšã¯ãããŸããããç§ãã¡ã¯è³æºãæ±ã£ãŠããŸãã
ãã®ããŒãã«ã§äœãèµ·ãã£ãã®ã§ãããã? ã¹ãããã·ã§ããã1ææ®ããŸããã Postgres_checkup ã¯ãåèšæéãã³ãŒã«æ°ãè¡æ°ãshared_blks_read ãªã©ã®åã¡ããªã¯ã¹ã®ãã«ã¿ãæäŸããŸããããã§ããã«ã¿ã®èšç®ã¯å®äºã§ãã pg_stat_statements ã®å€§ããªåé¡ã¯ããã€ãªã»ãããããããèŠããŠããªãããšã§ãã pg_stat_database ãèšæ¶ããŠããå Žåãpg_stat_statements ã¯èšæ¶ããŸããã 000 ãšããæ°åãããããšãããããŸãããã©ãããæ°ããã®ãããããŸããã
ãããŠãããã« 56 ã€ã®ã¹ãããã·ã§ãããããããšãããããŸãã ãã®å Žåã®å·®å㯠XNUMX ç§ã§ããããšãããããŸãã éåžžã«çãã®ã£ããã total_time ã§äžŠã¹æ¿ããããŸãã ãããŠã埮åããããšãã§ããŸããã€ãŸãããã¹ãŠã®ã¡ããªã¯ã¹ãæéã§åå²ããŸãã åã¡ããªã¯ã¹ãæéã§å²ããšãXNUMX ç§ãããã®åŒã³åºãæ°ãåŸãããŸãã
次ã«ãXNUMX ç§ãããã® total_time ãç§ã®ãæ°ã«å ¥ãã®ææšã§ãã ããã¯ãXNUMX ç§ãããã®ç§æ°ãã€ãŸãã·ã¹ãã ã XNUMX ç§ããããã®ãªã¯ãšã¹ãã®ã°ã«ãŒããå®è¡ããã®ã«äœç§ããã£ããã枬å®ããŸãã XNUMX ç§ããã XNUMX ç§ãè¶ ããå Žåã¯ãè€æ°ã®ã³ã¢ãæäŸããå¿ èŠããã£ãããšãæå³ããŸãã ããã¯éåžžã«åªããææšã§ãã ããšãã°ããã®å人ã«ã¯å°ãªããšã XNUMX ã€ã®ã³ã¢ãå¿ èŠã§ããããšãããããŸãã
ããã¯ç§ãã¡ã®ããŠããŠã§ããããã®ãããªãã®ã¯ã©ãã«ãèŠãããšããããŸããã 泚æããŠãã ãã - ããã¯éåžžã«åçŽãªããšã§ã - æ¯ç§ã å Žåã«ãã£ãŠã¯ãCPU ã 100% ã®å ŽåãXNUMX ç§ããã XNUMX åãã€ãŸãããã®ãªã¯ãšã¹ãã ããè¡ãã®ã« XNUMX åãè²»ãããããšã«ãªããŸãã
次ã«ãXNUMX ç§ãããã®è¡æ°ã確èªããŸãã XNUMX ç§ãããã«è¿ãããè¡æ°ãããããŸãã
ãããããé¢çœãããšããããŸãã å ±æãããã¡èªäœãã XNUMX ç§ãããã«èªã¿åãå ±æãããã¡ã®æ°ã ãããã¯ãã§ã«ååšããŠããã®ã§ããªãã¬ãŒãã£ã³ã° ã·ã¹ãã ã®ãã£ãã·ã¥ãŸãã¯ãã£ã¹ã¯ããè¡ãååŸããŸããã æåã®ãªãã·ã§ã³ã¯é«éã§ãããXNUMX çªç®ã®ãªãã·ã§ã³ã¯ç¶æ³ã«å¿ããŠé«éã§ããå Žåãšããã§ãªãå ŽåããããŸãã
101 çªç®ã®å·®å¥åæ¹æ³ã¯ããã®ã°ã«ãŒãå ã®ãªã¯ãšã¹ãã®æ°ãåå²ããããšã§ãã XNUMX çªç®ã®åã«ã¯ãåžžã« XNUMX ã€ã®ã¯ãšãªãã¯ãšãªããšã«åå²ãããŸãã ãããŠèå³æ·±ãã®ã¯ããã®ãªã¯ãšã¹ãã«äœããªç§ãå«ãŸããŠããããšããããšã§ãã ãã®ã¯ãšãªãå¹³åããŠã©ã®ããã«åäœãããã¯ããã£ãŠããŸãã åãªã¯ãšã¹ãã«ã¯ XNUMX ããªç§ãå¿ èŠã§ããã ããã¯ç§ãã¡ãç解ããå¿ èŠãããäŒçµ±çãªææšã§ãã
åã¯ãšãªã¯å¹³åããŠäœè¡ãè¿ããŸããã? ãã®ã°ã«ãŒããã㯠8 人ãæ»ã£ãŠããããšãããããŸãã å¹³åããŠããã£ãã·ã¥ããååŸãããŠèªã¿åãããéã ãã¹ãŠãé©åã«ãã£ãã·ã¥ãããŠããããšãããããŸãã æåã®ã°ã«ãŒãã§ã¯å å®ãªãããã
åè¡ã® 1 çªç®ã®éšåæååã¯å šäœã®äœããŒã»ã³ãããè¡šããŸãã é»è©±ããããŸãã 000 ãšãããšããã®ã°ã«ãŒããã©ã®ãããªè²¢ç®ãããŠããããããããŸãã ãã®å Žåãæåã®ã°ã«ãŒãã®å¯äžã¯ 000% æªæºã§ããããšãããããŸãã ã€ãŸããå šäœåãèŠããªãã»ã©é ããšããããšã§ãã ãã㊠0,01 çªç®ã®ã°ã«ãŒãã¯é話㧠5% ã§ãã ã€ãŸãããã¹ãŠã®ã³ãŒã«ã® 5% ã XNUMX çªç®ã®ã°ã«ãŒãã§ãã
Total_time ãèå³æ·±ãã§ãã æåã®ã°ã«ãŒãã®ãªã¯ãšã¹ãã«ç·äœæ¥æéã® 14% ãè²»ãããŸããã ãããŠ11çªç®ã«ã€ããŠã¯XNUMXïŒ ãªã©ã§ãã
詳现ã«ã¯è§ŠããŸãããã埮åŠãªç¹ããããŸãã äžéšã«ãšã©ãŒã衚瀺ããŸããããã¯ãæ¯èŒãããšã¹ãããã·ã§ããããããŒãã£ã³ã°ã«ãªãå¯èœæ§ãããããã§ããã€ãŸããäžéšã®ãªã¯ãšã¹ããæãèœã¡ãŠ 0 çªç®ã®ãªã¯ãšã¹ãã«ååšã§ããªããªãäžæ¹ã§ãæ°ãããªã¯ãšã¹ããããã€ã衚瀺ãããå¯èœæ§ããããŸãã ãããŠããã§èª€å·®ãèšç®ããŸãã 20 ã衚瀺ãããŠããã°åé¡ãããŸããã ééãã¯ãããŸããã ãšã©ãŒçã XNUMX% ãŸã§ã§ããã°åé¡ãããŸããã
ããããæ¬é¡ã«æ»ããŸãã ã¯ãŒã¯ããŒããäœæããå¿ èŠããããŸãã äžããäžãŸã§é²ããŠã80% ãŸã㯠90% ã«éãããŸã§é²ããŸãã éåžžããã㯠10 ïœ 20 ã®ã°ã«ãŒãã§ãã ãããŠãpgbench çšã®ãã¡ã€ã«ãäœæããŸãã ããã§ã©ã³ãã ã䜿çšããŸãã æ®å¿µãªããããããããŸããããªãå ŽåããããŸãã Postgres 12 ã§ã¯ããã®ã¢ãããŒãã䜿çšããæ©äŒãããã«å¢ããã§ãããã
ãããŠããã®ããã«ã㊠total_time ã 80 ïœ 90% å¢å ããŸãã ã@ãã®æ¬¡ã«äœãå ¥ããã°ããã§ããããïŒ ç§ãã¡ã¯é»è©±ãèŠãŠãã©ãã ãã®é¢å¿ãããããèŠãŠãããã§éåžžã«å€ãã®é¢å¿ãè² ã£ãŠããããšãç解ããŸãã ãããã®ããŒã»ã³ããŒãžãããåãã¡ã€ã«ã®ãã©ã³ã¹ããšãæ¹æ³ãããããŸãã ãã®åŸãpgbench ã䜿çšããŠäœæ¥ã«é²ã¿ãŸãã
K001ãK002ãããããŸãã
K001 㯠75 ã€ã®éšåæååãæ〠10 ã€ã®å€§ããªæååã§ãã ããã¯è² è·å šäœã®ç¹åŸŽã§ãã XNUMX çªç®ã®åãš XNUMX çªç®ã®äžäœè¡ãåç §ããŠãã ããã XNUMX ç§ãããçŽ XNUMX ç§ã§ããããšãããããŸããã€ãŸããã³ã¢ã XNUMX ã€ããå Žåã¯è¯å¥œã§ãã 容éã¯çŽ XNUMX% ã«ãªããŸãã ãããŠãããã¯ãã®ããã«æ©èœããŸãã ã³ã¢ã XNUMX åããã°ãéåžžã¯èœã¡çããŠããŸãã ãã®ããã«ããŠãªãœãŒã¹ãè©äŸ¡ã§ããŸãã
K002 ã¯ãç§ãã¯ãšãª ã¯ã©ã¹ãšåŒãã§ãããã®ã§ããã€ãŸããSELECTãINSERTãUPDATEãDELETE ã§ãã ããã¯ããã¯ãªã®ã§ãå¥é SELECT FOR UPDATE ãå®è¡ããŸãã
ãããŠããã§ãSELECT ã¯éåžžã®ãªãŒã㌠(å šåŒã³åºãã® 82%) ã§ããããåæã« total_time ã® 74% ã§ãããšçµè«ä»ããããšãã§ããŸãã ã€ãŸããé »ç¹ã«åŒã³åºãããŸãããæ¶è²»ãããªãœãŒã¹ã¯å°ãªããªããŸãã
ãããŠããæ£ããå ±æãããã¡ãéžæããã«ã¯ã©ãããã°ããã§ãããã?ããšãã質åã«æ»ããŸãã ã»ãšãã©ã®ãã³ãããŒã¯ã¯ãã¹ã«ãŒããããã©ã®ãããã«ãªãããèŠãŠã¿ãŸããããã€ãŸãã¹ã«ãŒããããã©ã®ãããã«ãªãããèŠãŠã¿ãŸããããšããèãã«åºã¥ããŠããããšãããããŸãã éåžžãTPS ãŸã㯠QPS ã§æž¬å®ãããŸãã
ãããŠããã¥ãŒãã³ã° ãã©ã¡ãŒã¿ãŒã䜿çšããŠãè»äž¡ãã 311 ç§ãããã®ãã©ã³ã¶ã¯ã·ã§ã³ãã§ããã ãå€ãçµãåºãããšããŸãã ããã§ã¯ãéžæã®å Žåã¯ã¡ããã© XNUMX ç§ããã XNUMX ã§ãã
ããããå šéåã§éå€ããŠåž°å® ãã人ã¯ããŸããã ããã¯ã°ãããŠããŸãã ããŒã¿ããŒã¹ãåæ§ã§ãã å šéåã§é転ããå¿ èŠã¯ãããŸãããããããªäººã¯ããŸããã CPU ã 100% ããæ¬çªç°å¢ã«ã¯èª°ãäœãã§ããŸããã ãã ãã誰ããäœãã§ããå¯èœæ§ããããŸãããããã¯è¯ããããŸããã
éåžžã¯å®¹éã® 20%ãã§ããã° 50% 以äžã§é転ãããšããèãã§ãã ãããŠãç§ãã¡ã¯äœããããŠãŒã¶ãŒã®å¿çæéãæé©åããããåªããŠããŸãã ã€ãŸããæ¡ä»¶ä»ã㧠20% ã®é床ã§ã®é 延ãæå°ã«ãªãããã«ãããåãå¿ èŠããããŸãã ããã¯ç§ãã¡ã®å®éšã§ã䜿çšããããšããŠããã¢ã€ãã¢ã§ãã
ãããŠæåŸã«ãæšå¥šäºé :
- ããŒã¿ããŒã¹ã©ãã¯å¿ ãè¡ã£ãŠãã ããã
- å¯èœã§ããã°ããªã³ããã³ãã§å®è¡ããŠããã°ããå±éããŠãéãã§æšãŠãŠãã ããã é²ãããå Žåã¯ãèšããŸã§ããªããã€ãŸããç«ã£ãŠããæéããããããããŸãã
- 奜å¥å¿ãæã£ãŠãã ããã ãããŠãäœããééã£ãŠããå Žåã¯ããããã©ã®ããã«åäœããããå®éšã§ç¢ºèªããŸãã ãã³ã·ãŒã¯ãåºå°ãã©ã®ããã«æ©èœãããã確èªããããã«èªåèªèº«ãèšç·Žããããã«äœ¿çšã§ããŸãã
- ãããŠãå¿çæéãæå°éã«ããããšãç®æããŸãã
- Postgres ãœãŒã¹ãæããå¿ èŠã¯ãããŸããã æ å ±æºãæ±ããšãã¯ãè±èªãç解ããå¿ èŠããããŸãã ããã«ã¯ããããã®ã³ã¡ã³ããããããã¹ãŠãããã§èª¬æãããŠããŸãã
- ãŸããããŒã¿ããŒã¹ã®å¥å šæ§ãå®æçã« (å°ãªããšã XNUMX ãæã« XNUMX å)ãæåãŸã㯠Postgres ãã§ãã¯ã¢ããã§ãã§ãã¯ããŠãã ããã
質å
ã©ããããããšãïŒ ãšãŠãèå³æ·±ãããšã§ãã
äºæã
ã¯ããXNUMXåã§ãã ãã ç§ã«ã¯ããåãããŸããã§ããã ãã³ã·ãŒãšç§ãäœæ¥ãããšããXNUMX ã€ã®ãã©ã¡ãŒã¿ã ãã埮調æŽããããšã¯ã§ããŸããããããšãã°ã«ãŒãå šäœã埮調æŽããããšã¯ã§ããŸãã?
ãã«ã¿èšå®ãã©ã¡ãŒã¿ããããŸãã äžåºŠã«å¥œããªã ããããæ²ããããšãã§ããŸãã ãã ããå€ãã®ããšãå€æŽãããšã誀ã£ãçµè«ãå°ãåºãããå¯èœæ§ãããããšãç解ããå¿ èŠããããŸãã
ã¯ãã ãªãç§ãå°ããã®ã§ããããïŒ ãã©ã¡ãŒã¿ãŒã XNUMX ã€ãããªãå Žåãå®éšãè¡ãã®ã¯é£ããããã§ãã ç· ããŠãã©ã®ããã«æ©èœããããèŠãŠãã ããã ç§ã¯åœŒãå€ã«åºããŸããã ãããã次ã®ããšãå§ããŸãã
åæã«ç· ããããšãã§ããŸããããã¡ããç¶æ³ã«ãããŸãã ããããXNUMX ã€ã®ã¢ã€ãã¢ãè©ŠããŠã¿ãæ¹ãè¯ãã§ãããã ç§ãã¡ã¯æšæ¥ã¢ã€ãã¢ãæãã€ããŸããã éåžžã«è¿ãç¶æ³ã§ããã æ§æ㯠XNUMX ã€ãããŸããã ãããŠããªã倧ããªéããããã®ãââç解ã§ããŸããã§ããã ãããŠãéããäœã§ããããäžè²«ããŠç解ãèŠã€ããããã«ã¯ãäºåæ³ã䜿çšããå¿ èŠããããšããèããçãŸããŸããã ãã©ã¡ãŒã¿ã®ååãããã«åãã«ãã次㫠XNUMX åã® XNUMX ã«ãããªã©ããã¹ãŠãæè»ã§ãã
ãããŠãããäžã€è³ªåããããŸãã ãã®ãããžã§ã¯ãã¯ãŸã è¥ããçºå±éäžã§ãã ããã¥ã¡ã³ãã¯ãã§ã«æºåãããŠããŸããã詳现ãªèª¬æã¯ãããŸãã?
ç¹ã«ãã©ã¡ãŒã¿ã®èª¬æãžã®ãªã³ã¯ãããã«äœæããŸããã ããã¯ããã«ãããŸãã ããããå€ãã®ããšã¯ãŸã ããã«ã¯ãããŸããã ç§ã¯åãèãã®äººãæ¢ããŠããŸãã ãããŠãæŒå¥ãããšãã«ããããèŠã€ããŸãã ããã¯ãšãŠãã¯ãŒã«ã§ãã 誰ãããã§ã«ç§ãšäžç·ã«åããŠããã誰ããããã§å©ããŠäœããããŠãããŸããã ãã®ãããã¯ã«èå³ãããå Žåã¯ãäœãæ¬ ããŠãããã«ã€ããŠãã£ãŒãããã¯ãéã£ãŠãã ããã
ç 究宀ã建ãŠããããã£ãŒãããã¯ããããããããŸããã èŠãŠã¿ãŸãããã ããããšãïŒ
ããã«ã¡ã¯ïŒ ãå ±åããããšãããããŸãïŒ Amazonã®ãµããŒããããããšãããããŸããã GSP ããµããŒãããäºå®ã¯ãããŸãã?
è¯ã質åã ãããããå§ãããã§ãã ãéãç¯çŽãããã®ã§ãä»ã®ãšããåçµããŸããã ã€ãŸããlocalhost ã§ã®å®è¡ã®äœ¿çšããµããŒããããŠããŸãã èªåã§ã€ã³ã¹ã¿ã³ã¹ãäœæããããŒã«ã«ã§äœæ¥ããããšãã§ããŸãã ã¡ãªã¿ã«ããããç§ãã¡ã®ãã£ãŠããããšã§ãã ç§ã¯ããã Getlab ãš GSP ã§è¡ã£ãŠããŸãã ããããGoogle ã«ã¯å®äŸ¡ãªã¹ãããããªãããããã®ãããªãªãŒã±ã¹ãã¬ãŒã·ã§ã³ãè¡ãæå³ã¯ãŸã ããããŸããã ããã ïŒïŒïŒ ã€ã³ã¹ã¿ã³ã¹ããããŸãããå¶éããããŸãã ãŸããåžžã« 70% ã®å²åŒãããªãããã®äŸ¡æ Œã§éã¶ããšã¯ã§ããŸããã ã¹ãããã®å Žåã¯ãããã¯ãããå¯èœæ§ãæžããããã«äŸ¡æ Œã 5 ïœ 10% åŒãäžããŸãã ã€ãŸããã¹ãããã¯ä¿åãããŸããããã€ã§ã奪ãããå¯èœæ§ããããŸãã ä»ã®äººããå°ãã§ãé«ãå ¥æãããšãåŸã§æ®ºãããããšã«ãªããŸãã Google ã¯ãŸã£ããç°ãªãä»æ§ãæã£ãŠããŸãã ãããŠããã 24 ã€ã®éåžžã«æªãå¶éããããŸãã圌ã㯠5 æéããçããããŸããã ãŸããXNUMX æ¥éå®éšãå®è¡ãããå ŽåããããŸãã ãã ããããã¯ã¹ãããçã«è¡ãããšãã§ããã¹ãããã¯æ°ãæç¶ãããšããããŸãã
ããã«ã¡ã¯ïŒ ãå ±åããããšãããããŸãïŒ æ€æ»ã«ã€ããŠèšåããŸãããã stat_statements ãšã©ãŒã¯ã©ã®ããã«èšç®ããŸãã?
ãšãŠãè¯ã質åã§ãã ãšãŠã詳ããèŠããŠãäŒãããããšãã§ããŸãã ã€ãŸããäžé£ã®ãªã¯ãšã¹ã ã°ã«ãŒããã©ã®ããã«å€åããããã€ãŸããããã€ãè±èœããããã€ãæ°ãããªã¯ãšã¹ã ã°ã«ãŒããåºçŸãããã調ã¹ãŸãã 次ã«ãtotal_time ãš calls ãšãã XNUMX ã€ã®ã¡ããªã¯ã¹ã調ã¹ãŸãããã®ãããXNUMX ã€ã®ãšã©ãŒãååšããŸãã ãããŠãæµ®åã°ã«ãŒãã®è²¢ç®ã«æ³šç®ããŸãã åºçºè ãšå°çè ãšãã XNUMX ã€ã®ãµãã°ã«ãŒãããããŸãã å šäœåã«å¯Ÿãã圌ãã®è²¢ç®ãèŠãŠã¿ãŸãããã
æ®åœ±ã®åéã«XNUMXãXNUMXåæ²ãã£ãŠããŸãã®ã§ã¯ãªãããšå¿é ã§ã¯ãããŸãããïŒ
ã€ãŸãã圌ãã¯å床ç»é²ããã®ãããããšãäœãªã®ãïŒ
ããšãã°ããã®ãªã¯ãšã¹ãã¯ãã§ã« XNUMX åããªãšã³ãããããŠããããã®åŸéä¿¡ãããŠåã³ããªãšã³ãããããããã«å床éä¿¡ãããŠå床ããªãšã³ãããããŸããã ããã§äœããèšç®ããŸããããããã¯ã©ãã«ããã®ã§ãããã?
è¯ã質åã§ããã調ã¹ãŠã¿ãŸãã
ç§ãåããããªããšãããŸããã ãã¡ãããç§ãäžäººã§ãã£ãã»ããç°¡åã§ããã ããããstat_statements ããªã»ãããããªã»ããããã¹ãããã·ã§ããã®æç¹ã§ stat_statements ãèç©ã§ããäžéã«éããŠããªãäžå®ã®å²åæªæºã§ããããšã確èªããå¿ èŠããããŸããã ãããŠç§ã®ç解ã§ã¯ãããããäœã眮ãæããããªãã£ããšæããŸãã
ã¯ããã¯ãã
ããããä»ã«ç¢ºå®ã«å®è¡ããæ¹æ³ãããããŸããã
æ®å¿µãªãããããã§ã¯ãšãªããã¹ãã䜿çšããã®ããpg_stat_statements ã§ã¯ãšãª ID ã䜿çšããŠããã«çŠç¹ãåœãŠãã®ããæ£ç¢ºã«ã¯èŠããŠããŸããã queryid ã«æ³šç®ãããšãçè«äžã¯åçã®ãã®ãæ¯èŒããããšã«ãªããŸãã
ããããã¹ãããã·ã§ããã®éã«æ°å匷å¶çã«éå ŽããããããŸãæ»ã£ãŠããå¯èœæ§ããããŸãã
åãIDã§ïŒ
ã¯ãã
ãããå匷ããŠã¿ãŸãã è¯ã質åã ç§ãã¡ã¯ãããå匷ããå¿ èŠããããŸãã ãããä»ã®ãšãããç§ãã¡ãèŠãŠãããã®ã¯ 0 ãšæžãããŠããŸã...
ãã¡ãããããã¯ãŸããªã±ãŒã¹ã§ãããstat_statemetns ãããã§çœ®ãæããããå¯èœæ§ãããããšãç¥ã£ããšãã¯ã·ã§ãã¯ãåããŸããã
Pg_stat_statements ã«ã¯å€ãã®ãã®ãå«ããããšãã§ããŸãã track_utility = on ã«ãããšãã»ããã远跡ããããšããäºå®ã«ééããŸããã
ã¯ãããã¡ããã§ãã
Java Hibernate ã䜿çšããŠããå Žå (ããã¯ã©ã³ãã ã§ã)ãããã·ã¥ ããŒãã«ãããã«é 眮ããå§ããŸãã ãããŠãéåžžã«è² è·ã®é«ãã¢ããªã±ãŒã·ã§ã³ããªãã«ãããšãããã« 50 ïœ 100 ã®ã°ã«ãŒããã§ããŠããŸããŸãã ãããŠãããã§ã¯ãã¹ãŠãå€ããå°ãªããå®å®ããŠããŸãã ããã«å¯ŸåŠãã XNUMX ã€ã®æ¹æ³ã¯ãpg_stat_statements.max ãå¢ããããšã§ãã
ã¯ãããããããã®éãç¥ãå¿ èŠããããŸãã ãããŠãã©ããããããç§ãã¡ã¯åœŒããç®ãé¢ããªãå¿ èŠããããŸãã ãããç§ããã£ãŠããããšã§ãã ã€ãŸããpg_stat_statements.max ããããŸãã ãããŠãã¹ãããã·ã§ããã®æç¹ã§ã¯ 70% ã«éããŠããªãã£ãããšãããããŸãã ãããäœã倱ã£ãŠããªãã ãªã»ããããŸãããã ãããŠãŸãä¿åããŸãã 次ã®ã¹ãããã·ã§ããã 70 æªæºã®å Žåã¯ãåã³äœã倱ãããŠããªãå¯èœæ§ãé«ããªããŸãã
ã¯ãã çŸåšã®ããã©ã«ã㯠5 ã§ãããå€ãã®äººã«ãšã£ãŠã¯ããã§ååã§ãã
éåžžã¯ããã§ãã
ãããªïŒ
PS ç§èªèº«ã®ä»£ããã«ãPostgres ã«æ©å¯ããŒã¿ãå«ãŸããŠãããããããã¹ãç°å¢ã«å«ããããšãã§ããªãå Žåã¯ã次ã®ã³ãã³ãã䜿çšã§ããããšãä»ãå ããŠãããŸãã
åºæïŒ habr.com