Ilya Kosmodemyansky ã«ãã 2015 幎ã®ã¬ããŒããPostgreSQL ã®ããã©ãŒãã³ã¹ãåäžãããããã® Linux ãã¥ãŒãã³ã°ãã®è»¢å
å 責äºé : ãã®ã¬ããŒãã®æ¥ä»ã¯ 2015 幎 4 æã§ããããšã«æ³šæããŠãã ããã9.4 幎以äžãçµéããããªãã®æéãçµéããŠããŸãã ã¬ããŒãã§èª¬æãããŠããããŒãžã§ã³ 4 ã¯ãµããŒããããªããªããŸããã éå» 5 幎éã§ãPostgreSQL ã® 15 ã€ã®æ°ãããªãªãŒã¹ããªãªãŒã¹ãããLinux ã«ãŒãã«ã® XNUMX ããŒãžã§ã³ããªãªãŒã¹ãããŸããã ãããã®éšåãæžãçŽããšãå¥ã®ã¬ããŒããäœæãããããšã«ãªããŸãã ãã ããããã§ã¯ãä»æ¥ã§ãéèŠãª PostgreSQL çšã®åºæ¬ç㪠Linux ãã¥ãŒãã³ã°ã«ã€ããŠèããŸãã
ç§ã®ååã¯ã€ãªã€ã»ã³ã¹ã¢ããã£ã³ã¹ããŒã§ãã ç§ã¯ PostgreSQL ã³ã³ãµã«ãã£ã³ã°ã§åããŠããŸãã ããã§ãããŒã¿ããŒã¹å šè¬ãšç¹ã« PostgreSQL ã«é¢é£ã㊠Linux ãã©ããããã«ã€ããŠå°ã話ããŸããåçã¯éåžžã«äŒŒãŠããããã§ãã
äœã話ããŸããããïŒ PostgreSQL ãšéä¿¡ããå Žåã¯ãããçšåºŠã¯ UNIX 管çè ã§ããå¿ èŠããããŸãã ããã¯ã©ãããæå³ã§ããïŒ Oracle ãš PostgreSQL ãæ¯èŒãããšãOracle ã§ã¯ 80% ã DBA ããŒã¿ããŒã¹ç®¡çè ã20% ã Linux 管çè ã§ããå¿ èŠããããŸãã
PostgreSQL ã®å Žåã¯ããå°ãè€éã§ãã PostgreSQL ã䜿çšããã«ã¯ãLinux ãã©ã®ããã«åäœããããããæ·±ãç解ããå¿ èŠããããŸãã åæã«ãæ©é¢è»ã®å°ãåŸããèµ°ããŸããæè¿ã¯ãã¹ãŠãéåžžã«ããŸãæŽæ°ãããŠããããã§ãã ãããŠãæ°ããã«ãŒãã«ããªãªãŒã¹ãããæ°ããæ©èœãç»å Žããããã©ãŒãã³ã¹ãåäžããŸãã
ãªã Linux ã«ã€ããŠè©±ããŠããã®ã§ãããã? ããã¯ãç§ãã¡ã Linux ã«ã³ãã¡ã¬ã³ã¹ Peter ã«åå ããŠããããã§ã¯ãããŸãããçŸä»£ã®ç¶æ³ã«ãããŠãããŒã¿ããŒã¹å šè¬ãç¹ã« PostgreSQL ã䜿çšããã®ã«æãæ£åœãªãªãã¬ãŒãã£ã³ã° ã·ã¹ãã ã® XNUMX ã€ã Linux ã§ããããã§ãã æ®å¿µãªããšã«ãFreeBSD ã¯éåžžã«å¥åŠãªæ¹åã«çºå±ããŠããããã§ãã ãããŠãããã©ãŒãã³ã¹ãä»ã®å€ãã®ããšã®äž¡æ¹ã§åé¡ãçºçããã§ãããã Windows äžã® PostgreSQL ã®ããã©ãŒãã³ã¹ã¯ãéåžžãWindows ã«ã¯ UNIX ãšåãå ±æã¡ã¢ãªããªããšããäºå®ã«åºã¥ãå¥ã®æ·±å»ãªåé¡ã§ãããPostgreSQL ã¯ãã«ãããã»ã¹ ã·ã¹ãã ã§ããããããã¹ãŠããã«é¢ä¿ããŠããŸãã
ãããŠã誰ãããœã©ãªã¹ã®ãããªãšããŸããã¯ã«ã¯ããŸãèå³ããªããšæãã®ã§ãè¡ããŸãããã
ææ°ã® Linux ãã£ã¹ããªãã¥ãŒã·ã§ã³ã«ã¯ãã«ãŒãã«ã®æ§ç¯æ¹æ³ã«å¿ã㊠1 ãè¶ ãã syctl ãªãã·ã§ã³ããããŸãã åæã«ãããŸããŸãªãããã«æ³šç®ãããšãããŸããŸãªæ¹æ³ã§äœãã調æŽããããšãã§ããŸãã ããããããŠã³ãããæ¹æ³ã«ã€ããŠã¯ããã¡ã€ã« ã·ã¹ãã ãã©ã¡ãŒã¿ãŒããããŸãã BIOS ã§äœãæå¹ã«ããããããŒããŠã§ã¢ã®æ§ææ¹æ³ãªã©ãèµ·åæ¹æ³ã«é¢ãã質åãããå Žåã¯ã
ããã¯ãXNUMX ã€ã®çãã¬ããŒãã§ã¯ãªããæ°æ¥ãããŠè°è«ã§ããéåžžã«å€§ããªã¥ãŒã ã§ãããããã§ã¯éèŠãªããšã«çŠç¹ãåœãŠãŸããã€ãŸããLinux ã§ã®ããŒã¿ããŒã¹ã®é©åãªäœ¿çšã確å®ã«åŠšããã¬ãŒããåé¿ããæ¹æ³ã§ããä¿®æ£ããªãã§ãã ããã åæã«ãéèŠãªç¹ã¯ãå€ãã®ããã©ã«ã ãã©ã¡ãŒã¿ãŒãããŒã¿ããŒã¹ã«ãšã£ãŠæ£ããèšå®ã«å«ãŸããŠããªããšããããšã§ãã ã€ãŸããããã©ã«ãã§ã¯ãããŸãåäœããªããããŸã£ããåäœããŸããã
Linux ã«ã¯ã©ã®ãããªåŸæ¥ã®ãã¥ãŒãã³ã° ã¿ãŒã²ããããããŸãã? çãã㯠Linux ã®ç®¡çãæ±ã£ãŠããã®ã§ãã¿ãŒã²ãããäœãã«ã€ããŠã¯ç¹ã«èª¬æããå¿ èŠã¯ãªããšæããŸãã
以äžã調æŽã§ããŸãã
- CPUã
- ã¡ã¢ãªã
- ã¹ãã¬ãŒãžã
- ä»ã®ã ããã«ã€ããŠã¯æåŸã«è»œé£ããšããªããã話ããŸãã ããšãã°ãçãšã ããªã·ãŒãªã©ã®ãã©ã¡ãŒã¿ã§ãããéåžžã«äºæž¬äžå¯èœã§ãããŸãå¿«é©ãšã¯èšããªã圢ã§ããã©ãŒãã³ã¹ã«åœ±é¿ãäžããå¯èœæ§ããããŸãã
PostgreSQL ãšããŒã¿ããŒã¹å šè¬ã®è©³çŽ°ã¯äœã§ãã? åé¡ã¯ãåã ã®ãããã埮調æŽããŠãããã©ãŒãã³ã¹ãå€§å¹ ã«åäžããããšã確èªã§ããªãããšã§ãã
確ãã«ãã®ãããªã¬ãžã§ããã¯ãããŸãããããŒã¿ããŒã¹ã¯è€éã§ãã ãµãŒããŒãæã€ãã¹ãŠã®ãªãœãŒã¹ãšå¯Ÿè©±ããæ倧éã«å¯Ÿè©±ããããšã奜ã¿ãŸãã ãã¹ã OS ã®äœ¿çšæ¹æ³ã«é¢ãã Oracle ã®çŸåšã®æšå¥šäºé ãèŠããšãããã¯ã¢ã³ãŽã«ã®å®å®é£è¡å£«ã«ã€ããŠã®ãžã§ãŒã¯ã®ãããªãã®ã«ãªãã§ããã - ç¬ã«é€ãäžããŠäœã觊ããªãã§ãã ããã ããŒã¿ããŒã¹ã«ãã¹ãŠã®ãªãœãŒã¹ãäžããŸããããããŒã¿ããŒã¹èªäœããã¹ãŠãæŽçããŸãã
ååãšããŠãç¶æ³ã¯ããçšåºŠãŸã§ PostgreSQL ãšãŸã£ããåãã§ãã éãã¯ãããŒã¿ããŒã¹ããŸã ãã¹ãŠã®ãªãœãŒã¹ãèªåèªèº«ã§ååŸã§ããªãããšã§ããã€ãŸããLinux ã¬ãã«ã®ã©ããã§èªåã§ãã¹ãŠãæŽçããå¿ èŠããããŸãã
äž»ãªã¢ã€ãã¢ã¯ãåäžã®ã¿ãŒã²ãã (ã¡ã¢ãªãCPU ãªã©) ãéžæããŠãã¥ãŒãã³ã°ãéå§ããã®ã§ã¯ãªããã¯ãŒã¯ããŒããåæããåªããããã°ã©ããŒãäœæããè² è·ãã§ããã ã軜æžããŠã¹ã«ãŒããããåäžãããããšã§ãããŠãŒã¶ãŒãå«ãç§ãã¡ã«ãšã£ãŠãã
ãããäœã§ãããã説æããããã®åçã§ãã Linux OS ãããã¡ãå ±æã¡ã¢ãªãPostgreSQL å ±æãããã¡ããããŸãã Oracle ãšã¯ç°ãªããPostgreSQL ã¯ã«ãŒãã« ãããã¡ãä»ããŠã®ã¿çŽæ¥åäœããŸããã€ãŸãããã£ã¹ã¯ããã®ããŒãžãå ±æã¡ã¢ãªã«å ¥ãã«ã¯ããŸã£ããåãç¶æ³ã§ã«ãŒãã« ãããã¡ãééããŠæ»ãå¿ èŠããããŸãã
ãã£ã¹ã¯ã¯ãã®ã·ã¹ãã ã®äžã«ååšããŸãã ãããåç€ãšããŠæããŸããã å®éã«ã¯ãRAID ã³ã³ãããŒã©ãŒãªã©ãååšããå¯èœæ§ããããŸãã
ãããŠããã®å ¥åºåã¯äœããã®åœ¢ã§ãã®åé¡ãéããŠèµ·ãããŸãã
PostgreSQL ã¯å€å žçãªããŒã¿ããŒã¹ã§ãã äžã«ããŒãžããããŸãã ãã¹ãŠã®å ¥åºåã¯ããŒãžã䜿çšããŠè¡ãããŸãã ããŒãžã䜿çšããŠãããã¯ãã¡ã¢ãªã«äžããŠããŸãã ãããŠãäœãèµ·ãããªããã°ãåã«ããããèªã¿åãã ãã§ããã®åŸåŸã ã«ãã®ãã£ãã·ã¥ãå ±æãããã¡ãŒããæ¶ããŠãããæçµçã«ã¯ãã£ã¹ã¯ã«æ»ããŸãã
ã©ããã眮ãæãããšãããŒãžå šäœãããŒãã£ãšããŠããŒã¯ãããŸãã ããã§ã¯ããããéã§ããŒã¯ããŸããã ããã¯ããã®ããŒãžãããã㯠ã¹ãã¬ãŒãžãšåæããå¿ èŠãããããšãæå³ããŸãã ã€ãŸããããŒãã£ã«ãããšããWAL ã«ãšã³ããªãäœæãããŸããã ãããŠçŽ æŽãããç¬éã«ããã§ãã¯ãã€ã³ããšåŒã°ããçŸè±¡ãèµ·ãããŸããã ãããŠåœŒãå°çãããšããæ å ±ããã®ãã°ã«èšé²ãããŠããŸããã ããã¯ããã®æç¹ã§ãããã®å ±æãããã¡ãŒã«ãã£ããã¹ãŠã®ããŒã㣠ããŒãžããã«ãŒãã« ãããã¡ãŒãä»ã㊠fsync ã䜿çšããŠã¹ãã¬ãŒãž ãã£ã¹ã¯ãšåæãããããšãæå³ããŸãã
ãªããããè¡ãããã®ã§ãããã? é»å§ã倱ãããå Žåã§ãããã¹ãŠã®ããŒã¿ã倱ããããšããç¶æ³ã¯çºçããŸããã§ããã 誰ããç§ãã¡ã«è©±ããæ°žç¶èšæ¶ã¯ããããŸã§ã®ãšããããŒã¿ããŒã¹çè«ã®äžã«ãããŸããããã¯æããæªæ¥ã§ããããã¡ããç§ãã¡ã¯ãããç®æããŠããããããæ°ã«å ¥ã£ãŠããŸãããä»ã®ãšããããããã¯ãã€ãã¹20幎ã§çããŸãã ãããŠãã¡ãããããããã¹ãŠãç£èŠããå¿ èŠããããŸãã
ã¹ã«ãŒããããæ倧åããã¿ã¹ã¯ã¯ãããããã¹ãŠã®æ®µéã§åŸ®èª¿æŽããŠããã¹ãŠãè¿ éã«ååŸã«é²ãããã«ããããšã§ãã å ±æã¡ã¢ãªã¯åºæ¬çã«ããŒãž ãã£ãã·ã¥ã§ãã PostgreSQL ã§ã¯ãéžæã¯ãšãªãªã©ãéä¿¡ãããšããã®ããŒã¿ããã£ã¹ã¯ããååŸãããŸãã ãããã¯æçµçã«å ±æãããã¡ãŒã«çœ®ãããŸããã ãããã£ãŠããããããé©åã«åäœãããã«ã¯ã倧éã®ã¡ã¢ãªãå¿ èŠã§ãã
ããããã¹ãŠãé©åãã€è¿ éã«æ©èœããããã«ã¯ããã¹ãŠã®æ®µéã§ãªãã¬ãŒãã£ã³ã° ã·ã¹ãã ãæ£ããæ§æããå¿ èŠããããŸãã ãŸãããã©ã³ã¹ã®ãšããããŒããŠã§ã¢ãéžæããŠãã ãããã©ããã«äžåè¡¡ããããšã倧éã®ã¡ã¢ãªãäœæã§ããŸãããååãªé床ã§åŠçã§ããªããªãããã§ãã
ãããã®åãã€ã³ããèŠãŠã¿ãŸãããã
ãããã®ããŒãžã®è¡ãæ¥ãé«éåããã«ã¯ã次ã®ããšãéæããå¿ èŠããããŸãã
- ãŸããã¡ã¢ãªãããå¹ççã«äœ¿çšããå¿ èŠããããŸãã
- 第 XNUMX ã«ãã¡ã¢ãªããããŒãžããã£ã¹ã¯ã«ç§»åãããšãã®ãã®ç§»è¡ãããå¹ççã«ãªãã¯ãã§ãã
- ãããŠç¬¬äžã«ãè¯å¥œãªãã£ã¹ã¯ãååšããå¿ èŠããããŸãã
ãµãŒããŒã« 512 GB ã® RAM ãããããã®ãã¹ãŠããã£ãã·ã¥ãªãã® SATA ããŒã ãã©ã€ãã«çœ®ãããå ŽåãããŒã¿ããŒã¹ ãµãŒããŒå šäœãåãªãã«ããã£ã§ã¯ãªããSATA ã€ã³ã¿ãŒãã§ã€ã¹ãåããã«ããã£ã«ãªããŸãã çŽæ¥ééããããšã«ãªããŸãã ãããŠäœãããªããæã£ãŠã¯ãããŸããã
XNUMX ã€ç®ã®èšæ¶ã«é¢ããç¹ã§ããã人çãéåžžã«å°é£ã«ããèŠå ã XNUMX ã€ãããŸãã
ãã®æåã®ãã®ã¯NUMAã§ãã NUMA ã¯ããã©ãŒãã³ã¹ãåäžãããããã«äœããããã®ã§ãã ã¯ãŒã¯ããŒãã«å¿ããŠãããŸããŸãªããšãæé©åã§ããŸãã ãŸããçŸåšã®æ°ãã圢åŒã§ã¯ãããŒãž ãã£ãã·ã¥å ±æãããã¡ãéäžçã«äœ¿çšããããŒã¿ããŒã¹ãªã©ã®ã¢ããªã±ãŒã·ã§ã³ã«ã¯ããŸãé©ããŠããŸããã
äžèšã§èšãã°ã NUMA ã«åé¡ããããã©ããã¯ã©ãããã°ããããŸãã? ããçš®ã®äžå¿«ãªè¡æããããçªç¶äžéšã® CPU ãéè² è·ã«ãªããŸãã åæã«ãPostgreSQL ã®ã¯ãšãªãåæãããšãé¡äŒŒããã¯ãšãªãååšããªãããšãããããŸãã ãããã®ã¯ãšãªã¯ CPU ãããã»ã©éäžçã«äœ¿çšããã¹ãã§ã¯ãããŸããã ãããªãé·ãé£ããŸããã PostgreSQL çšã® NUMA ã®æ§ææ¹æ³ã«ã€ããŠã¯ãæåããæ£ããæšå¥šäºé ã䜿çšããæ¹ãç°¡åã§ãã
æ¬åœã«äœãèµ·ãã£ãŠããã®ã§ããããïŒ NUMA 㯠Non-Uniform Memory Access ã®ç¥ã§ãã ãã€ã³ãã¯äœã§ããïŒ CPU ãããããã®é£ã«ã¯ããŒã«ã« ã¡ã¢ãªããããŸãã ãããŠããã®ã¡ã¢ãªçžäºæ¥ç¶ã¯ä»ã® CPU ããã¡ã¢ãªããã«ã¢ããã§ããŸãã
èµ°ãã° numactl --hardware
ããããããšãã®ãããªå€§ããªã·ãŒããåŸãããŸãã ãšããããè·é¢ãã£ãŒã«ãããããŸãã 10ã20ã®ãããªæ°åã衚瀺ãããŸãã ãããã®æ°å€ã¯ããã®ãªã¢ãŒã ã¡ã¢ãªãååŸããŠããŒã«ã«ã§äœ¿çšããããã®ãããã®æ°ã«ãããŸããã ååçã«ã¯è¯ãã¢ã€ãã¢ã§ãã ããã«ãããããŸããŸãªã¯ãŒã¯ããŒãäžã§ããã©ãŒãã³ã¹ã倧å¹
ã«é«éåãããŸãã
ããã§ãXNUMX ã€ã® CPU ãæåã«ãã®ããŒã«ã« ã¡ã¢ãªã䜿çšããããšããŠããŠã次ã«äœãã®ããã«ã€ã³ã¿ãŒã³ãã¯ãçµç±ã§å¥ã®ã¡ã¢ãªãååŸããããšããŠãããšæ³åããŠãã ããã ãããŠããã® CPU 㯠PostgreSQL ããŒãž ãã£ãã·ã¥å šäœãååŸããŸããã€ãŸããæ°ã®ã¬ãã€ãã§ãã éåžžãCPU ã®ã¢ãžã¥ãŒã«èªäœã«ã¯ã¡ã¢ãªãã»ãšãã©ãªããããåžžã«ææªã®ã±ãŒã¹ãçºçããŸãã ãããŠããµãŒãã¹ããããã¹ãŠã®ã¡ã¢ãªã¯ããããã®çžäºæ¥ç¶ãçµç±ããŸãã ããã¯é ããŠæ²ããããšãããããŸãã ãããŠããã®ããŒãã«ãµãŒãã¹ãæäŸããããã»ããµã¯åžžã«éè² è·ç¶æ ã«ãªããŸãã ãããŠããã®ã¡ã¢ãªã®ã¢ã¯ã»ã¹æéã¯æªããé ãã§ãã ãããããŒã¿ããŒã¹ã«äœ¿çšããŠããå Žåãããã¯æãŸãããªãç¶æ³ã§ãã
ãããã£ãŠãããŒã¿ããŒã¹ã«ãšã£ãŠããæ£ãããªãã·ã§ã³ã¯ãLinux ãªãã¬ãŒãã£ã³ã° ã·ã¹ãã ãããã§äœãèµ·ãã£ãŠãããããŸã£ããèªèããªãããšã§ãã ãã®ãããã¡ã¢ãªã«ã¢ã¯ã»ã¹ããã®ãšåãããã«ãªããŸãã
äœæ ã§ããïŒ ããã¯éã§ããã¹ãã ãšæãããã§ãããã ãã㯠XNUMX ã€ã®åçŽãªçç±ã§çºçããŸããããã¯ãããŒãž ãã£ãã·ã¥ã«å€§éã®ã¡ã¢ãª (æ°åãæ°çŸã®ã¬ãã€ã) ãå¿ èŠã§ãããšããããšã§ãã
ãããŠãããããã¹ãŠãå²ãåœãŠãŠããã«ããŒã¿ããã£ãã·ã¥ããå Žåããã£ãã·ã¥ã䜿çšããããšã§åŸãããå©çã¯ãã¡ã¢ãªãžã®ãã®ãããªããªãããŒãªã¢ã¯ã»ã¹ã«ããå©çãããå€§å¹ ã«å€§ãããªããŸãã ãããã£ãŠãNUMA ã䜿çšããŠããå¹ççã«ã¡ã¢ãªã«ã¢ã¯ã»ã¹ããããšãšæ¯ã¹ãŠãæ¯èŒã«ãªããªãã»ã©ã®ã¡ãªãããåŸãããŸãã
ãããã£ãŠãæããæªæ¥ãå°æ¥ããããŒã¿ããŒã¹èªäœãã©ã® CPU ã§å®è¡ãããŠããã®ããã©ãããäœããååŸããå¿ èŠãããã®ãââãææ¡ã§ããªããŸã§ãçŸæç¹ã§ã¯ XNUMX ã€ã®ã¢ãããŒãããããŸãã
ãããã£ãŠãæ£ããã¢ãããŒãã¯ãNUMA ãå®å šã«ç¡å¹ã«ããããšã§ããããšãã°ãåèµ·åæãªã©ã§ãã ã»ãšãã©ã®å Žåãè³éã¯æ¡éãã«å€§ãããããã©ã¡ããè¯ãããšããåé¡ã¯ãŸã£ããçããŸããã
å¥ã®ãªãã·ã§ã³ããããŸãã ã¯ã©ã€ã¢ã³ãããµããŒããæ±ããŠç§ãã¡ã«æ¥ããšãããµãŒããŒãåèµ·åããã®ã¯ã¯ã©ã€ã¢ã³ãã«ãšã£ãŠå€§ããªåé¡ãšãªããããæåã®ã¯ã©ã€ã¢ã³ããããé »ç¹ã«ãã®ã¯ã©ã€ã¢ã³ãã䜿çšããŸãã 圌ã¯ããã§ããžãã¹ãããŠããŸãã ãããŠãNUMA ãåå ã§åé¡ãçºçããŸãã ãããã£ãŠãåèµ·åããã䟵襲æ§ã®äœãæ¹æ³ã§ç¡å¹ã«ããããšããŸãããç¡å¹ã«ãªã£ãŠããããšã確èªããããã«æ³šæããŠãã ããã çµéšãããããããã«ã芪 PostgreSQL ããã»ã¹ã§ NUMA ãç¡å¹ã«ããã®ã¯è¯ãããšã§ããããããæ©èœããå¿ èŠã¯ãŸã£ãããããŸããã 圌女ãæ¬åœã«ã¹ã€ãããåã£ãã®ãã©ããã確èªããå¿ èŠããããŸãã
Robert Haas ã«ããè¯ãæçš¿ããããŸãã ãã㯠PostgreSQL ã³ããã¿ãŒã® XNUMX 人ã§ãã ãã¹ãŠã®äœã¬ãã«ã®ã¢ãã®äž»èŠãªéçºè ã® XNUMX 人ã ãã®æçš¿ã®ãªã³ã¯ããã©ããšãNUMA ã人ã ã®ç掻ãããã«å°é£ã«ãããã«ã€ããŠãããã€ãã®å€åœ©ãªã¹ããŒãªãŒã説æãããŠããŸãã ããŒã¿ããŒã¹ãæ£åžžã«åäœããããã«ãµãŒããŒäžã§äœãæ§æããå¿ èŠããããã«ã€ããŠãã·ã¹ãã 管çè ã®ãã§ãã¯ãªã¹ãã調ã¹ãŠãã ããã ãããã®èšå®ã¯æžãçããŠç¢ºèªããå¿ èŠããããŸããããããªããšãããŸãè¯ãçµæãåŸãããŸããã
ããã¯ãããã説æãããã¹ãŠã®èšå®ã«é©çšãããããšã«æ³šæããŠãã ããã ãã ããéåžžãããŒã¿ããŒã¹ã¯ãã©ãŒã«ã ãã¬ã©ã³ã¹ã®ããã«ãã¹ã¿ãŒ/ã¹ã¬ãŒã ã¢ãŒãã§åéãããŸãã ãã€ãäºæ ã«éã£ãŠã¹ã¬ãŒãã«åãæ¿ãããã¹ã¬ãŒãããã¹ã¿ãŒã«ãªã£ãŠããŸãã®ã§ãã¹ã¬ãŒãåŽã§ãããã®èšå®ãè¡ãããšãå¿ããªãã§ãã ããã
ç·æ¥äºæ ãçºçãããšãããã¹ãŠãéåžžã«æªããé»è©±ã鳎ãç¶ããäžåžã倧ããªæ£ãæã£ãŠèµ°ã£ãŠãããšãã確èªããããšãèããæéã¯ãããŸããã ãããŠããã®çµæã¯éåžžã«æ²æšãªãã®ã«ãªãå¯èœæ§ããããŸãã
次ã®ãã€ã³ãã¯å·šå€§ããŒãžã§ãã 巚倧ãªããŒãžãåå¥ã«ãã¹ãããã®ã¯å°é£ã§ããããããå®è¡ã§ãããã³ãããŒã¯ã¯ãããŸããããã¹ãããããšã«æå³ã¯ãããŸããã Google ã«ç°¡åã«ã¢ã¯ã»ã¹ã§ããŸãã
ãã€ã³ãã¯äœã§ããïŒ ããšãã°ã30 GB 以äžã®å€§éã® RAM ãæèŒãããããã»ã©é«äŸ¡ã§ã¯ãªããµãŒããŒã䜿çšããŠãããšããŸãã 巚倧ãªããŒãžã¯äœ¿çšããŸããã ããã¯ãã¡ã¢ãªäœ¿çšéã«é¢ããŠç¢ºå®ã«ãªãŒããŒããããããããšãæå³ããŸãã ãããŠããã®ãªãŒããŒãããã¯æ±ºããŠå¿«é©ãªãã®ã§ã¯ãããŸããã
äœæ ã§ããïŒ ã©ãããã®ïŒ ãªãã¬ãŒãã£ã³ã° ã·ã¹ãã ã¯ã¡ã¢ãªã现ããåå²ããŠå²ãåœãŠãŸãã ããã¯ãšãŠã䟿å©ã§ããæŽå²çã«ã¯ãããªã£ãã®ã§ãã ããã«è©³ãã説æãããšãOS ã¯ä»®æ³ã¢ãã¬ã¹ãç©çã¢ãã¬ã¹ã«å€æããå¿ èŠããããŸãã ãŸãããã®ããã»ã¹ã¯æãåçŽã§ã¯ãªããããOS ã¯ãã®æäœã®çµæã Translation Lookaside Buffer (TLB) ã«ãã£ãã·ã¥ããŸãã
TLB ã¯ãã£ãã·ã¥ã§ããããããã®ç¶æ³ã§ã¯ãã£ãã·ã¥ã«åºæã®åé¡ããã¹ãŠçºçããŸãã ãŸãã倧éã® RAM ãããããã¹ãŠãå°ããªãã£ã³ã¯ã«å²ãåœãŠãããŠããå Žåããã®ãããã¡ã¯éåžžã«å€§ãããªããŸãã ãŸãããã£ãã·ã¥ã倧ããå Žåããã£ãã·ã¥å ã®æ€çŽ¢ã¯é ããªããŸãã ãªãŒããŒãããã¯æ£åžžã§ãããããèªäœãã¹ããŒã¹ãå æããŸããã€ãŸããRAM ãäœãééã£ããã®ã«ãã£ãŠæ¶è²»ãããŠããŸãã ãã®æã
XNUMX ã€ç®ã¯ããã®ãããªç¶æ³ã§ãã£ãã·ã¥ãå¢å€§ããã»ã©ããã£ãã·ã¥ ãã¹ãçºçããå¯èœæ§ãé«ããªããŸãã ãããŠããã®ãã£ãã·ã¥ã®å¹çã¯ããµã€ãºã倧ãããªãã«ã€ããŠæ¥éã«äœäžããŸãã ããã§ããªãã¬ãŒãã£ã³ã°ã·ã¹ãã ã¯ã·ã³ãã«ãªã¢ãããŒããèæ¡ããŸããã Linuxã§ã¯å€ããã䜿ãããŠããŸããã ããã¯å°ãåã« FreeBSD ã«ç»å ŽããŸããã ããããç§ãã¡ã¯ Linux ã«ã€ããŠè©±ããŠããŸãã ãããã¯å·šå€§ãªããŒãžã§ãã
ããã§æ³šç®ãã¹ãã¯ãã¢ã€ãã¢ãšããŠã®ãã¥ãŒãž ããŒãžã¯åœåãOracle ã IBM ãå«ãã³ãã¥ããã£ã«ãã£ãŠæšé²ããããã®ã§ãããã€ãŸããããŒã¿ããŒã¹ ã¡ãŒã«ãŒã¯ããããããŒã¿ããŒã¹ã«ã圹ç«ã€ãšåŒ·ãèããŠããŸããã
ãããŠãããã PostgreSQL ãšã©ã®ããã«é£æºãããããšãã§ããã§ãããã? ãŸããLinux ã«ãŒãã«ã§ãã¥ãŒãž ããŒãžãæå¹ã«ããå¿ èŠããããŸãã
次ã«ãsysctl ãã©ã¡ãŒã¿ã§ãããã®æ°ãæ瀺çã«æå®ããå¿ èŠããããŸãã ããã®æ°åã¯å€ããµãŒããŒããã®ãã®ã§ãã 巚倧ãªããŒãžãããã«åãŸãå ±æãããã¡ãŒã®æ°ãèšç®ã§ããŸãã
ãŸãããµãŒããŒå šäœã PostgreSQL å°çšã§ããå Žåã¯ãRAM ã® 25% ãå ±æãããã¡ã«å²ãåœãŠãããããŒã¿ããŒã¹ããã® 75% ã«ç¢ºå®ã«åãŸããšç¢ºä¿¡ããŠããå Žå㯠75% ãå²ãåœãŠãã®ãè¯ãéå§ç¹ã§ãã åºçºç¹ 256ã 64 GB ã® RAM ãããå Žåãããã«å¿ã㊠XNUMX GB ã®å€§å®¹éãããã¡ãããããšãèããŠãã ããã ãã®æ°å€ãã©ã®çšåºŠã«èšå®ãããã«ã€ããŠãããçšåºŠã®äœè£ãæã£ãŠæŠç®ããŠãã ããã
ããŒãžã§ã³ 9.2 ããå (ç§ã®èšæ¶ãééã£ãŠããªããã°ãããŒãžã§ã³ 8.2 以é) ã¯ããµãŒãããŒãã£ã®ã©ã€ãã©ãªã䜿çšã㊠PostgreSQL ã巚倧ãªããŒãžã«æ¥ç¶ããããšãã§ããŸããã ãããŠãããã¯åžžã«è¡ãããã¹ãã§ãã ãŸããã«ãŒãã«ã巚倧ãªããŒãžãæ£ããå²ãåœãŠãããšãã§ããå¿ èŠããããŸãã ãã㊠5 çªç®ã«ãããããšé£æºããã¢ããªã±ãŒã·ã§ã³ããããã䜿çšã§ããããã«ããããã§ãã ãã ãã®ããã«äœ¿ãããããã§ã¯ãããŸããã PostgreSQL ã¯ã·ã¹ãã XNUMX ã¹ã¿ã€ã«ã§ã¡ã¢ãªãå²ãåœãŠãããããã㯠libhugetlbfs ã䜿çšããŠå®è¡ã§ããŸããããã¯ã©ã€ãã©ãªã®å®å šåã§ãã
9.3 ã§ã¯ãã¡ã¢ãªãæäœããéã® PostgreSQL ã®ããã©ãŒãã³ã¹ãåäžããã·ã¹ãã 5 ã®ã¡ã¢ãªå²ãåœãŠæ¹æ³ãå»æ¢ãããŸããã 誰ãããšãŠãæºè¶³ããŠããŸãããããããªããšã9.3 å°ã®ãã·ã³ã§ XNUMX ã€ã® PostgreSQL ã€ã³ã¹ã¿ã³ã¹ãå®è¡ããããšããŠããŸããå ±æã¡ã¢ãªã足ããªããšèšãããŸããã ãããŠãsysctlãä¿®æ£ããå¿ èŠããããšåœŒã¯èšããŸãã ãããŠããŸã åèµ·åããå¿ èŠããããããªsysctlããããŸããäžè¬ã«ã誰ããæºè¶³ããŠããŸããã ããããmmap ã®ã¡ã¢ãªå²ãåœãŠã«ããã巚倧ããŒãžã®äœ¿çšãäžæãããŸããã ã»ãšãã©ã®ã¯ã©ã€ã¢ã³ãã¯å€§èŠæš¡ãªå ±æãããã¡ã䜿çšããŠããŸãã ãŸããXNUMX ã§ã¯ãªãŒããŒããããé©åãªå²åã§èšç®ãããããã«ãªã£ããããXNUMX ã«åãæ¿ããªãããšã匷ããå§ãããŸãã
ããããã³ãã¥ããã£ã¯ãã®åé¡ã«æ³šç®ãã9.4 ã§ã¯ãã®ã€ãã³ããéåžžã«ããŸãäœãçŽããŸããã 9.4 ã§ã¯ãtry ããªã³ãŸãã¯ãªãã«ã§ãããã©ã¡ãŒã¿ã postgresql.conf ã«è¿œå ãããŸããã
è©ŠããŠã¿ãã®ãæãå®å šãªãªãã·ã§ã³ã§ãã PostgreSQL ãèµ·åãããšãå ±æã¡ã¢ãªãå²ãåœãŠããããšãã«ããã®ã¡ã¢ãªã巚倧ããŒãžããååŸããããšããŸãã ãããæ©èœããªãå Žåã¯ãéåžžã®éžæã«æ»ããŸãã FreeBSD ãŸã㯠Solaris ããæã¡ã®å Žåã¯ããã€ã§ãå®å šã«è©ŠããŠã¿ãããšãã§ããŸãã
ãªã³ã®å Žåã巚倧ãªããŒãžããéžæã§ããªãå Žåã¯åã«èµ·åããŸããã ããã§ã¯ã誰ããäœãããåªããŠãããã«ã€ããŠã¯ãã§ã«è©±ãããŠããŸãã ãã ããè©ŠããŠã¿ãããééãã®äœå°ããããããããããæ¬åœã«å¿ èŠãªãã®ã匷調衚瀺ãããŠãããã©ããã確èªããŠãã ããã çŸåšããã®æ©èœã¯ Linux ã§ã®ã¿åäœããŸãã
次ã«é²ãåã«ããã 32 ã€å°ããªæ³šæäºé ãèšèŒããŸãã ééçãªå·šå€§ããŒãžã¯ãŸã PostgreSQL ã«é¢ãããã®ã§ã¯ãããŸããã 圌ã¯ããããæ£åžžã«äœ¿çšããããšãã§ããŸããã ãŸãããã®ãããªã¯ãŒã¯ããŒãã«ééçãã¥ãŒãž ããŒãžã䜿çšãããšã倧èŠæš¡ãªå ±æã¡ã¢ãªãå¿ èŠãªå Žåãéåžžã«å€§å®¹éã®å Žåã«ã®ã¿å©ç¹ãåŸãããŸãã ãã©ãã€ãèŠæš¡ã®ã¡ã¢ãªãããå Žåã¯ããããæå¹ã«ãªãå¯èœæ§ããããŸãã ãã£ãšæ¥åžžçãªã¢ããªã±ãŒã·ã§ã³ã«ã€ããŠè©±ããŠããå Žåããã·ã³ã« 64ã128ã256ãXNUMX GB ã®ã¡ã¢ãªãããå Žåãéåžžã®å·šå€§ããŒãžã¯åé¡ãªããåã«ééãç¡å¹ã«ããŸãã
ãããŠæåŸã®èšæ¶ã«ã€ããŠã¯ãæç©ãšã¯çŽæ¥é¢ä¿ããããŸãããããã¯æ¬åœã«ããªãã®äººçãå°ç¡ãã«ããå¯èœæ§ããããŸãã ãã¹ãŠã®ã¹ã«ãŒãããã¯ããµãŒããŒãåžžã«ã¹ã¯ããããŠãããšããäºå®ã«ãã£ãŠå€§ãã圱é¿ãããŸãã
ãããŠãããã¯ããŸããŸãªæå³ã§éåžžã«äžå¿«ãªãã®ãšãªãã§ãããã ãããŠäž»ãªåé¡ã¯ãææ°ã®ã«ãŒãã«ã®åäœãå€ã Linux ã«ãŒãã«ãšã¯è¥å¹²ç°ãªãããšã§ãã ãããŠããããèžãã®ã¯éåžžã«äžå¿«ã§ãããªããªããã¹ã¯ããã«é¢ããããçš®ã®äœæ¥ã«ã€ããŠè©±ããšããããã¯OOMãã©ãŒã®ææ©ãè¶ ããå°çã§çµããããã§ãã ãããŠãOOM ãã©ãŒãã¿ã€ã ãªãŒã«å°çãããPostgreSQL ãããããããã®ã¯äžå¿«ã§ãã ããã«ã€ããŠã¯ãæåŸã®ãŠãŒã¶ãŒãŸã§èª°ããç¥ãããšã«ãªããŸãã
äœãèµ·ãã£ãŠããŸããïŒ ããã«ã¯å€§éã® RAM ãããããã¹ãŠãããŸãåäœããŸãã ããããäœããã®çç±ã§ãµãŒããŒãã¹ã¯ããã§ãã³ã°ãããããåå ã§é床ãäœäžããŸãã ã¡ã¢ãªãããããããããã«èŠããŸããããããèµ·ãããŸãã
以åã¯ãvm.swappiness ããŒãã«èšå®ãããã€ãŸãã¹ã¯ãããç¡å¹ã«ããããšããå§ãããŸããã 以åã¯ã32 GB ã® RAM ãšããã«å¯Ÿå¿ããå ±æãããã¡ã¯èšå€§ãªéã ãšæãããŠããŸããã 亀æã®äž»ãªç®çã¯ãèœã¡ãå Žåã«ã¯ã©ã¹ããæšãŠãå Žæã確ä¿ããããšã§ãã ãããŠããã¯ãã¯ãç¹ã«æºããããŠããŸããã§ããã ããã§ããã®ç®ãã©ãããã€ããã§ããïŒ ããã¯ãç¹ã«ãã®ãããªãµã€ãºã®ã¹ã¯ãããå¿ èŠãªçç±ãããŸãæ確ã§ã¯ãªãã¿ã¹ã¯ã§ãã
ããããããæ°ãããã€ãŸãã«ãŒãã«ã®ç¬¬ XNUMX ããŒãžã§ã³ã§ã¯ãåäœãå€ãããŸããã ãããŠãã¹ã¯ããããŒãã«èšå®ãããšãã€ãŸãã¹ã¯ããããªãã«ãããšãããšã RAM ãããããæ®ã£ãŠãããšããŠããé ããæ©ãããOOM ãã©ãŒããã£ãŠæ¥ãŠãæãéäžçãªã³ã³ã·ã¥ãŒããŒã殺ãããšã«ãªããŸãã ãªããªãããã®ãããªã¯ãŒã¯ããŒãã§ã¯ãŸã å°ãæ®ã£ãŠããã®ã§ãã·ã¹ãã ããã»ã¹ãç¹å®ããã®ã§ã¯ãªããããã»ã©éèŠã§ã¯ãªããã®ãç¹å®ããããã«é£ã³åºãã ãããšåœŒã¯èããããã§ãã ãã®ããã»ã©éèŠã§ã¯ãªããã®ã¯ãå ±æã¡ã¢ãªãéäžçã«æ¶è²»ãããã®ãã€ãŸããã¹ããã¹ã¿ãŒã«ãªããŸãã ããšã¯æ ç¹ã埩æ§ããªããŠãè¯ãã§ãã
ãããã£ãŠãçŸåšã®ããã©ã«ãã¯ãç§ãèŠããŠããéããã»ãšãã©ã®ãã£ã¹ããªãã¥ãŒã·ã§ã³ã¯ 6 ååŸã§ããã€ãŸããã¡ã¢ãªã®æ®éã«å¿ããŠãã©ã®æç¹ã§ã¹ã¯ããã®äœ¿çšãéå§ããå¿ èŠããããŸãã çŸåšãvm.swappiness = 1 ãèšå®ããããšããå§ãããŸããããã¯å®éã«ã¯ç¡å¹ã«ããŸãããäºæããå°çããŠå šäœãç Žå£ãã OOM ãã©ãŒãšåãå¹æã¯åŸãããŸããã
次ã¯äœã§ããïŒ ããŒã¿ããŒã¹ã®ããã©ãŒãã³ã¹ã«ã€ããŠè©±ããåŸã ã«ãã£ã¹ã¯ã«ç§»è¡ãããšã誰ããé ãæ±ãå§ããŸãã ãªããªãããã£ã¹ã¯ã¯é ããã¡ã¢ãªã¯éããšããçå®ã¯ãåäŸã®é ãã誰ããç¥ã£ãŠããããã§ãã ãããŠãããŒã¿ããŒã¹ã«ãã£ã¹ã¯ ããã©ãŒãã³ã¹ã®åé¡ãçºçããããšã¯èª°ããç¥ã£ãŠããŸãã
ãã£ã¹ã¯ãé ãããããã§ãã¯ãã€ã³ãã®ã¹ãã€ã¯ã«é¢é£ããäž»èŠãª PostgreSQL ããã©ãŒãã³ã¹åé¡ã¯çºçããŸããã ããã¯ãã¡ã¢ãªãšãã£ã¹ã¯ã®åž¯åå¹ ã®ãã©ã³ã¹ãåããŠããªãããšãåå ã§ãããšèããããŸãã ãã ããå Žæã«ãã£ãŠã¯ãã©ã³ã¹ãåããªãå ŽåããããŸãã PostgreSQL ãæ§æãããŠããããOS ãæ§æãããŠããããããŒããŠã§ã¢ãæ§æãããŠããããããŒããŠã§ã¢ãæ£ãããããŸããã ãããŠããã®åé¡ã¯ããã¹ãŠãæ³å®éãã«èµ·ãã£ãå Žåãã€ãŸããè² è·ããªãããèšå®ãšããŒããŠã§ã¢ãé©åã«éžæãããŠããå Žåã«ã®ã¿çºçããããã§ã¯ãããŸããã
ããã¯äœã§ããããŸãã©ã®ããã«èŠããŸãã? éåžžãPostgreSQL ãæ±ã人ã¯ãã®åé¡ã«äœåºŠãé¢ãã£ãããšãããã§ãããã 説æããŸãã å ã»ã©ãè¿°ã¹ãããã«ãPostgreSQL ã¯å®æçã«ãã§ãã¯ãã€ã³ããäœæããŠãå ±æã¡ã¢ãªå ã®ããŒã㣠ããŒãžããã£ã¹ã¯ã«ãã³ãããŸãã 倧éã®å ±æã¡ã¢ãªãããå Žåããã§ãã¯ãã€ã³ã㯠fsync ã§ãããã®ããŒãžããã³ãããããããã£ã¹ã¯ã«éäžçãªåœ±é¿ãåãŒãå§ããŸãã ããã¯ã«ãŒãã« ãããã¡ã«å°çããfsync ã䜿çšããŠãã£ã¹ã¯ã«æžã蟌ãŸããŸãã ãã®ããžãã¹ã®éãå€ãå Žåããã£ã¹ã¯ã®äœ¿çšéãéåžžã«å€ããªããšããäžå¿«ãªåœ±é¿ã芳å¯ãããå¯èœæ§ããããŸãã
ããã«90æã®åçããããŸãã ãããäœãªã®ãããããã説æããŠãããŸãã ããã㯠90 ã€ã®æéçžé¢ã°ã©ãã§ãã æåã®ã°ã©ãã¯ãã£ã¹ã¯äœ¿çšçã§ãã ãã¡ãã¯çŸæç¹ã§ã»ãŒ100ïŒ ã«éããŠãããŸãã ç©çãã£ã¹ã¯ã§ããŒã¿ããŒã¹é害ãçºçããRAID ã³ã³ãããŒã©ãŒã®äœ¿çšçã XNUMX% ã«ãªã£ãŠããå Žåãããã¯æªãç¥ããã§ãã ããã¯ãããå°ãé²ããš XNUMX ã«éããI/O ãåæ¢ããããšãæå³ããŸãã
ãã£ã¹ã¯ã¢ã¬ã€ã䜿çšããŠããå Žåã¯ãå°ã話ãç°ãªããŸãã ããã¯ãæ§ææ¹æ³ãã¢ã¬ã€ã®çš®é¡ãªã©ã«ãã£ãŠç°ãªããŸãã
ãããŠäžŠè¡ããŠãå éš postgres ãã¥ãŒããã®ã°ã©ããããã§æ§æããããã§ãã¯ãã€ã³ããã©ã®ããã«çºçãããã瀺ããŸãã ããã®ç·è²ã¯ããã®æç¹ã§åæã®ããã«ãã®ãã§ãã¯ãã€ã³ãã«å°çãããããã¡ (ããŒã㣠ããŒãž) ã®æ°ã瀺ããŠããŸãã ãããŠããããããã§ç¥ã£ãŠããã¹ãéèŠãªããšã§ãã ããã«ã¯å€§éã®ããŒãžããããããæç¹ã§ããŒãã«å°éããŸãããã€ãŸããæžãç¶ããŸãããããã§ã¯ããã£ã¹ã¯ ã·ã¹ãã ãæããã«éåžžã«ããžãŒã«ãªã£ãŠããŸãã ãããŠããã§ãã¯ãã€ã³ãã¯ãã£ã¹ã¯ã«éåžžã«åŒ·ã圱é¿ãäžããŸãã çæ³çã«ã¯ãç¶æ³ã¯æ¬¡ã®ããã«ãªãã¯ãã§ããã€ãŸããããã§ã¯é²é³ãå°ãªããªããŸãã ãããŠããã®ç¶æ ãç¶ç¶ããããã«èšå®ã§ä¿®æ£ããããšãã§ããŸãã ã€ãŸãããªãµã€ã¯ã«ã¯å°ããã§ãããã©ããã§ããã«äœãæžããŠããŸãã
ãã®åé¡ãå æããã«ã¯äœãããå¿ èŠããããŸãã? ããŒã¿ããŒã¹ã§ IO ãåæ¢ããå Žåããªã¯ãšã¹ããå®è¡ããããã«æ¥ããã¹ãŠã®ãŠãŒã¶ãŒãåŸ æ©ããããšã«ãªããŸãã
Linux ã®èŠ³ç¹ããèŠãå Žåãé©åãªããŒããŠã§ã¢ã䜿çšããæ£ããæ§æãããããã®ãã§ãã¯ãã€ã³ãã®é »åºŠãæžãããæéã®çµéãšãšãã«çžäºã«åæ£ããããã« PostgreSQL ãæ£åžžã«æ§æããå Žåãããã©ã«ãã® Debian ãã©ã¡ãŒã¿ã«èžã¿èŸŒãããšã«ãªããŸãã ã»ãšãã©ã® Linux ãã£ã¹ããªãã¥ãŒã·ã§ã³ã§ã¯ãvm.dirty_ratio=20ãvm.dirty_background_ratio=10 ã®ããã«ãªããŸãã
ããã¯ã©ãããæå³ã§ããïŒ ã«ãŒãã« 2.6 ããã¯ãã©ãã·ã³ã°ããŒã¢ã³ã XNUMX ã€åºçŸããŸããã Pdglush ã¯ã誰ãã©ãã䜿çšããŠãããã«å¿ããŠãã«ãŒãã« ãããã¡ããã®ããŒã㣠ããŒãžã®ããã¯ã°ã©ãŠã³ãã§ã®ç Žæ£ãšãããã¯ã°ã©ãŠã€ã³ãã§ã®ç Žæ£ã圹ã«ç«ããªãå Žåã«ã©ãããŠãããŒã㣠ããŒãžãç Žæ£ããå¿ èŠãããå Žåã«ç Žæ£ããŸãã
èæ¯ã¯ãã€æ¥ãã®ã§ããïŒ ãµãŒããŒäžã§äœ¿çšå¯èœãªåèš RAM ã® 10% ãã«ãŒãã« ãããã¡å ã®ããŒã㣠ããŒãžã«ãã£ãŠå æãããŠããå Žåãç¹å¥ãªã©ã€ããªãé¢æ°ãããã¯ã°ã©ãŠã³ãã§åŒã³åºãããŸãã ãªãèæ¯ãªã®ã§ããããïŒ ãã©ã¡ãŒã¿ãšããŠãæ¶å»ããããŒãžæ°ãèæ ®ãããŸãã ãããŠã圌㯠N ããŒãžãæžãæšãŠããšããŸãã ãããŠãã°ããã®éãããã¯ç ãã«èœã¡ãŸãã ãããŠåœŒå¥³ã¯åã³ãã£ãŠæ¥ãŠãããã«ããã€ãã®ããŒãžãã³ããŒããŸããã
ããã¯éåžžã«åçŽãªè©±ã§ãã ããã§ã®åé¡ã¯ãããŒã«ã®å Žåãšåãã§ãæ°Žã XNUMX ã€ã®ãã€ãã«æ³šã蟌ãŸãããšãå¥ã®ãã€ãã«æµã蟌ã¿ãŸãã ãã§ãã¯ãã€ã³ããå°çããç Žæ£ããããã«ããã€ãã®ããŒã㣠ããŒãžãéä¿¡ããå ŽåãåŸã ã«å šäœãã«ãŒãã« ãããã¡ãŒ pgflush ããé©åã«è§£æ±ºãããŸãã
ãããã®ããŒã㣠ããŒãžãèç©ãç¶ãããšãæ倧 20% ãŸã§èç©ãããŸãããã®åŸãé»æºãèœã¡ãŠãã¹ãŠãæªããªããããOS ã®åªå äºé ã¯ãã¹ãŠããã£ã¹ã¯ã«æžã蟌ãããšã§ãã ããšãã°ããã®ããŒã¿ã¯å€±ãããŸãã
ã³ãã¯äœã§ããïŒ éèŠãªã®ã¯ãçŸä»£äžçã«ããããããã®ãã©ã¡ãŒã¿ã¯ããã·ã³äžã®åèš RAM ã® 20% ãš 10% ã§ããããã£ã¹ã¯ ã·ã¹ãã ã®ã¹ã«ãŒããããšãã芳ç¹ããèŠããšããŸã£ãã巚倧ãªãã®ã§ãããšããããšã§ãã
128 GB ã® RAM ããããšæ³åããŠãã ããã 12,8 GB ããã£ã¹ã¯ ã·ã¹ãã ã«å°çããŸãã ãããŠãããã«ã©ããªãã£ãã·ã¥ããã£ãŠããã©ããªé åããã£ãŠãããããã¯ããã»ã©é·ãã¯ç¶ããŸããã
ãããã£ãŠãRAID ã³ã³ãããŒã©ãŒã®æ©èœã«åºã¥ããŠãããã®æ°å€ãããã«èª¿æŽããããšããå§ãããŸãã ç§ã¯ããã«ã512 MB ã®ãã£ãã·ã¥ãåããã³ã³ãããŒã©ãŒãããã§æšå¥šããŸããã
ãã¹ãŠãéåžžã«åçŽã§ãããšèããããŠããŸãã vm.dirty_background ããã€ãåäœã§æå®ã§ããŸãã ãããŠããããã®èšå®ã¯åã® XNUMX ã€ã®èšå®ããã£ã³ã»ã«ããŸãã ã©ã¡ããã®æ¯çãããã©ã«ãã§ãããããã€ããæã€æ¯çãã¢ã¯ãã£ãåãããŠããå Žåã¯ããã€ããæã€æ¯çãæ©èœããŸãã ããããç§ã¯ DBA ã³ã³ãµã«ã¿ã³ãã§ãããããŸããŸãªã¯ã©ã€ã¢ã³ããšä»äºãããŠããããããã€ãåäœã§ããã°ããã€ãåäœã§ã¹ãããŒãæãããã«ããŠããŸãã åªç§ãªç®¡çè ããµãŒããŒã«ã¡ã¢ãªãè¿œå ãããåèµ·åãããããªããšãæ°å€ãå€ãããªããšããä¿èšŒã¯èª°ãããŸããã§ããã ãã¹ãŠãä¿èšŒãããŠããã«åãŸãããã«ããããã®æ°å€ãèšç®ããã ãã§ãã
é©åããªãå Žåã¯ã©ããªããŸãã? ãã©ãã·ã³ã°ã¯å¹æçã«åæ¢ããããšæžããŸããããå®éã«ã¯ããã¯æ¯å©ã§ãã ãªãã¬ãŒãã£ã³ã° ã·ã¹ãã ã«ã¯å€§ããªåé¡ããããŸããå€ãã®ããŒã㣠ããŒãžããããããã¯ã©ã€ã¢ã³ããçæãã IO ã¯å®è³ªçã«åæ¢ãããŸããã€ãŸããã¢ããªã±ãŒã·ã§ã³ã¯ããŒã¿ããŒã¹ã« SQL ã¯ãšãªãéä¿¡ãã«æ¥ãŠåŸ æ©ããŠããŸãã ããŒã¿ããŒã¹ã¯ãã§ãã¯ãã€ã³ãã«ãã£ãŠå æãããŠãããããããŒã¿ããŒã¹ãžã®å ¥å/åºåã®åªå 床ã¯æãäœããªããŸãã ãããŠåœŒå¥³ããã€çµããã®ãã¯å®å šã«äžæã ã ãããŠãéããã¯ã°ã©ãŠã³ã ãã©ãã·ã¥ãéæãããšããã¹ãŠã® IO ããã®ãã©ãã·ã¥ã«ãã£ãŠå æãããããšãæå³ããŸãã ãããŠãããçµãããŸã§ãããªãã¯äœãããŸããã
ããã«ã¯ããã« XNUMX ã€ã®éèŠãªç¹ããããŸããããã®ã¬ããŒãã®ç¯å²ãè¶ ããŠããŸãã ãããã®èšå®ã¯ãpostgresql.conf ã®èšå®ãã€ãŸããã§ãã¯ãã€ã³ãèšå®ãšäžèŽããå¿ èŠããããŸãã ãŸãããã£ã¹ã¯ ã·ã¹ãã ãé©åã«æ§æãããŠããå¿ èŠããããŸãã RAID äžã«ãã£ãã·ã¥ãããå Žåã¯ãããããªãŒãå¿ èŠã§ãã 人ã ã¯ããããªãŒãªãã§ãåªãããã£ãã·ã¥ãåãã RAID ãè³Œå ¥ããŸãã RAID ã« SSD ãããå Žåãããã¯ãµãŒããŒçšã®ãã®ã§ããå¿ èŠããããããã«ã³ã³ãã³ãµããªããã°ãªããŸããã 詳现ãªãã§ãã¯ãªã¹ãã¯æ¬¡ã®ãšããã§ãã ãã®ãªã³ã¯ã«ã¯ãPostgreSQL ã§ããã©ãŒãã³ã¹ ãã£ã¹ã¯ãæ§æããæ¹æ³ã«é¢ããç§ã®ã¬ããŒããå«ãŸããŠããŸãã ããããã¹ãŠã®ãã§ãã¯ãªã¹ããããã«ãããŸãã
ä»ã«äººçãéåžžã«å°é£ã«ãããã®ã¯äœã§ããïŒ ããã㯠XNUMX ã€ã®ãã©ã¡ãŒã¿ã§ãã æ¯èŒçæ°ãããã®ã§ãã ããã©ã«ãã§ã¯ããããã¯ããŸããŸãªã¢ããªã±ãŒã·ã§ã³ã«å«ããããšãã§ããŸãã ãŸãã誀ã£ãŠãªã³ã«ãããšãåãããã«ç掻ãå°é£ã«ãªãå¯èœæ§ããããŸãã
æ¯èŒçæ°ãããã®ãXNUMXã€ãããŸãã ãããã¯ãã§ã«ç¬¬ XNUMX ã³ã¢ã«ç»å ŽããŠããŸãã ããã¯ãããç§åäœã® sched_migration_cost ãšãããã©ã«ã㧠XNUMX ã§ãã sched_autogroup_enabled ã§ãã
ãããŠããããã¯ã©ã®ããã«ããªãã®äººçãå°ç¡ãã«ããã®ã§ããããïŒ sched_migration_cost ãšã¯äœã§ãã? Linux ã§ã¯ãã¹ã±ãžã¥ãŒã©ã¯ããã»ã¹ããã CPU ããå¥ã® CPU ã«ç§»è¡ã§ããŸãã ãŸããã¯ãšãªãå®è¡ãã PostgreSQL ã«ã€ããŠã¯ãå¥ã® CPU ãžã®ç§»è¡ã¯ãŸã£ããäžéæã§ãã ãªãã¬ãŒãã£ã³ã° ã·ã¹ãã ã®èŠ³ç¹ããã¯ãopenoffice ãšã¿ãŒããã«ã®éã§ãŠã£ã³ããŠãåãæ¿ããå Žåãããã¯è¯ãããšãããããŸãããã ããŒã¿ããŒã¹ã«ãšã£ãŠãããã¯éåžžã«æªãããšã§ãã ãããã£ãŠãé©åãªããªã·ãŒã¯ãmigration_cost ãäœããã®å€§ããªå€ (å°ãªããšãæ°åããç§) ã«èšå®ããããšã§ãã
ããã¯ã¹ã±ãžã¥ãŒã©ãŒã«ãšã£ãŠäœãæå³ããŸãã? ãã®éãããã»ã¹ã¯ãŸã é«æž©ã§ãããšèããããŸãã ã€ãŸããé·æéäœããå®è¡ããŠããé·æéå®è¡ãã©ã³ã¶ã¯ã·ã§ã³ãããå Žåãã¹ã±ãžã¥ãŒã©ã¯ãããç解ããŸãã ãã®ã¿ã€ã ã¢ãŠããçµéãããŸã§ã¯ããã®ããã»ã¹ãã©ãã«ã移è¡ããå¿ èŠã¯ãªããšæ³å®ããŸãã åæã«ããã»ã¹ãäœããå®è¡ããå Žåããã®ããã»ã¹ã¯ã©ãã«ã移è¡ããããå²ãåœãŠããã CPU äžã§éãã«åäœããŸãã ãããŠçµæã¯çŽ æŽãããã§ãã
XNUMX çªç®ã®ãã€ã³ãã¯èªåã°ã«ãŒãã§ãã ææ°ã®ããŒã¿ããŒã¹ã«é¢ä¿ã®ãªãç¹å®ã®ã¯ãŒã¯ããŒãã«å¯ŸããŠã¯ãããã»ã¹ãèµ·åå ã®ä»®æ³ç«¯æ«ããšã«ã°ã«ãŒãåãããšããè¯ãã¢ã€ãã¢ããããŸãã ããã¯äžéšã®ã¿ã¹ã¯ã«äŸ¿å©ã§ãã å®éã«ã¯ãPostgreSQL ã¯åäžã®ç«¯æ«ããå®è¡ãããããªãã©ãŒã¯ãåãããã«ãããã»ã¹ ã·ã¹ãã ã§ãã ãã㯠ã©ã€ã¿ãŒãšãã§ãã¯ãã€ã³ããããããã¹ãŠã®ã¯ã©ã€ã¢ã³ã ãªã¯ãšã¹ãã CPU ããšã« XNUMX ã€ã®ã¹ã±ãžã¥ãŒã©ã«ã°ã«ãŒãåãããŸãã ãããŠåœŒãã¯ããäºãã«å¹²æžããŠåœŒãããé·ãå é ãç¶ããããã«ã圌ãèªç±ã«ãªããŸã§äžæã«ããã§åŸ ã¡ãŸãã ãã®ãããªè² è·ã®å Žåã¯å šãäžèŠãªã®ã§ãªãã«ããå¿ èŠããããšãã話ã§ãã
ç§ã®ååã® Alexey Lesovsky ã¯ãåçŽãª pgbench ã䜿çšããŠãã¹ããè¡ããããã§ãmigration_cost ãäžæ¡å¢ãããautogroup ããªãã«ããŸããã äžè¯ããŒããŠã§ã¢ã§ã¯ãã®å·®ã¯ã»ãŒ 10% ã§ããã postgres ã¡ãŒãªã³ã° ãªã¹ãã«ã¯ãã£ã¹ã«ãã·ã§ã³ããããã¯ãšãªé床ã«å¯Ÿããåæ§ã®å€æŽã®çµæãå ±åãããŠããŸãã 50%ã«åœ±é¿ãäžããã ãããã話ã¯çµæ§å€ãã§ãã
æåŸã«ãç¯é»æ¿çã«ã€ããŠã§ãã è¯ãã®ã¯ãã©ãããããã§ã Linux ã䜿çšã§ããããã«ãªã£ãããšã§ãã ãããŠãããããããããªãŒããã䜿ããŸãã ããããçªç¶ãããããµãŒããŒã§ãçºçããå¯èœæ§ãããããšãå€æããŸããã
ããã«ãã©ããã®ãã¹ãã£ã³ã°äŒç€ŸãããµãŒããŒãåããŠããå Žåããè¯ãããã¹ãã£ã³ã°äŒç€Ÿã¯ããªãã®ããã©ãŒãã³ã¹ãåªããŠãããã©ãããæ°ã«ããŸããã 圌ãã®ä»»åã¯ãéãã§ããã ãå¹ççã«å©çšããããšã§ãã ãããã£ãŠãããã©ã«ãã§ã¯ããªãã¬ãŒãã£ã³ã° ã·ã¹ãã ã§ã©ãããããã®çé»åã¢ãŒããæå¹ã«ããããšãã§ããŸãã
ããŒã¿ããŒã¹ã®è² è·ãé«ããµãŒããŒã§ããã䜿çšããå Žåã¯ãacpi_cpufreq + permormance ãéžæããŸãã ãªã³ããã³ãã§ãã£ãŠãåé¡ã¯çºçããŸãã
Intel_pstate ã¯å°ãç°ãªããã©ã€ããŒã§ãã ãããŠä»ã§ã¯ãåŸããã®ãã®ã§ããè¯ãæ©èœããããããããåªå ãããŸãã
ãããã£ãŠãç¥äºã¯ããã©ãŒãã³ã¹ã«ãããŸããã ãªã³ããã³ããçé»åããã®ä»ãã¹ãŠã¯ããªã次第ã§ã¯ãããŸããã
çé»åãæå¹ã«ãããšãPostgreSQL ã® Explain åæã®çµæãæ°æ¡ç°ãªãå ŽåããããŸããããã¯ãå®éã«ã¯ããŒã¿ããŒã¹ã® CPU ããŸã£ããäºæž¬ã§ããªãæ¹æ³ã§å®è¡ãããããã§ãã
ãããã®é ç®ã¯ããã©ã«ãã§å«ãŸããŠããå ŽåããããŸãã ããã©ã«ãã§ãªã³ã«ãªã£ãŠãããã©ããã泚ææ·±ã確èªããŠãã ããã ããã¯éåžžã«å€§ããªåé¡ã«ãªãå¯èœæ§ããããŸãã
ãããŠæåŸã«ããã®åé¡ã§æ¥ã åé²ããŠãããPosgreSQL-Consulting DBA ããŒã ã® Max Boguk ãš Alexey Lesovsky ã«æè¬ã®æãè¡šããããšæããŸãã ãããŠãç§ãã¡ã¯ã¯ã©ã€ã¢ã³ãã«ãšã£ãŠãã¹ãŠãããŸãããããã«ãã¯ã©ã€ã¢ã³ãã®ããã«ã§ããéãã®ããšãããããšåªããŠããŸãã èªç©ºå®å šã«é¢ããæ瀺ãšäŒŒãŠããŸãã ããã«ãããã®ã¯ãã¹ãŠè¡ã§æžãããŠããŸãã ãããã®ãããã¯ãããããäœããã®åé¡ã®éçšã§èŠã€ãããŸãã çãããšå ±æã§ããããšãå¬ããæããŸãã
質åïŒ
ããããšãïŒ ããšãã°ãäŒæ¥ãã³ã¹ããç¯çŽããŠããŒã¿ããŒã¹ãšã¢ããªã±ãŒã·ã§ã³ ããžãã¯ã XNUMX ã€ã®ãµãŒããŒã«é 眮ãããå ŽåããŸãã¯äŒæ¥ã PostgreSQL ãã³ã³ããå ã§å®è¡ãããã€ã¯ããµãŒãã¹ ã¢ãŒããã¯ãã£ã®æµè¡ã®ãã¬ã³ãã«åŸã£ãŠããå Žåã§ãã ã³ãã¯äœã§ããïŒ Sysctl ã¯ã«ãŒãã«å šäœã«ã°ããŒãã«ã«åœ±é¿ããŸãã sysctl ãã³ã³ããäžã§åå¥ã«åäœããããã«äœããã®åœ¢ã§ä»®æ³åãããŠãããšãã話ã¯èããããšããããŸããã cgroup ã ãããããããã«ã¯ã³ã³ãããŒã«ã®äžéšãããããŸããã ããã§ã©ããã£ãŠçããŠãããŸããïŒ ãããšããããã©ãŒãã³ã¹ãå¿ èŠãªå Žåã¯ãå¥ã®ããŒããŠã§ã¢ ãµãŒããŒã§ PostgreSQL ãå®è¡ããŠèª¿æŽããŸãã?
ç§ãã¡ã¯ããªãã®è³ªåã«XNUMXã€ã»ã©ã®æ¹æ³ã§çããŸããã 調æŽã§ããããŒããŠã§ã¢ ãµãŒããŒã®è©±ã§ã¯ãªãå Žåã¯ãå®å¿ããŠãã ããããããã®èšå®ããªããŠããã¹ãŠãæ£åžžã«åäœããŸãã ãããã®èšå®ãè¡ãå¿ èŠãããã»ã©ã®è² è·ãããå Žåã¯ããããã®èšå®ãããæ©ãã¢ã€ã¢ã³ ãµãŒããŒã«ã¢ã¯ã»ã¹ããããšã«ãªããŸãã
äœãåé¡ã§ããïŒ ãããä»®æ³ãã·ã³ã®å Žåãããšãã°ãã»ãšãã©ã®ä»®æ³ãã·ã³ã§ã¯ãã£ã¹ã¯ã®é 延ããŸã£ããäžè²«ããŠããªããªã©ãå€ãã®åé¡ãçºçããå¯èœæ§ããããŸãã ãã£ã¹ã¯ã®ã¹ã«ãŒããããè¯å¥œã§ãã£ãŠãããã§ãã¯ãã€ã³ãæãŸã㯠WAL ãžã®æžã蟌ã¿æã«çºçããå¹³åã¹ã«ãŒãããã«å€§ããªåœ±é¿ãåãŒããªã I/O ãã©ã³ã¶ã¯ã·ã§ã³ã XNUMX å倱æãããšãããŒã¿ããŒã¹ã¯ããã«ãã£ãŠå€§ããªåœ±é¿ãåããŸãã ãããŠããããã®åé¡ã«ééããåã«ãããã«æ°ã¥ãã§ãããã
åããµãŒããŒäžã« NGINX ãããå Žåããåãåé¡ãçºçããŸãã 圌ã¯å ±æãããèšæ¶ãæ±ããŠæŠãã ããã ãããŠãããã§èª¬æãããŠããåé¡ã«ééããããšã¯ãããŸããã
ãããäžæ¹ã§ããããã®ãã©ã¡ãŒã¿ã®äžã«ã¯ãäŸç¶ãšããŠéèŠãªãã®ããããŸãã ããšãã°ãããã»ã©ãããããªãããã« sysctl ã䜿çšã㊠Dirty_ratio ãèšå®ããŸãããããã«ãããããã¯åœ¹ã«ç«ã¡ãŸãã ãããã«ããããã£ã¹ã¯ãšå¯Ÿè©±ããããšã«ãªããŸãã ãããŠããã¯ééã£ããã¿ãŒã³ã«åŸã£ãŠããŸãã§ãããã ããã¯éåžžãç§ã瀺ãããã©ã¡ãŒã¿ã®ããã©ã«ãã§ãã ãããŠããããã«ãããããããå€æŽããæ¹ãè¯ãã§ãããã
ãã ããNUMA ã«åé¡ãããå¯èœæ§ããããŸãã ããšãã°ãVmWare ã¯ããŸã£ããéã®èšå®ã䜿çšãã NUMA ã§é©åã«åäœããŸãã ãããŠããã§ãé補ãµãŒããŒãéé補ãµãŒããŒããéžæããå¿ èŠããããŸãã
Amazon AWS ã«é¢ãã質åããããŸãã äºåã«æ§æãããã€ã¡ãŒãžããããŸãã ãã®ãã¡ã® XNUMX ã€ã¯ Amazon RDS ãšåŒã°ããŸãã ãªãã¬ãŒãã£ã³ã° ã·ã¹ãã çšã®ã«ã¹ã¿ã èšå®ã¯ãããŸãã?
èšå®ã¯ãããŸãããå¥ã®èšå®ã§ãã ããã§ã¯ãããŒã¿ããŒã¹ããããã©ã®ããã«äœ¿çšããããšãã芳ç¹ãããªãã¬ãŒãã£ã³ã° ã·ã¹ãã ãæ§æããŸãã ãããŠãæŽåœ¢ãªã©ãä»ã©ãã«è¡ãã¹ããã決å®ãããã©ã¡ãŒã¿ãŒããããŸãã ã€ãŸããéåžžã«å€ãã®è³æºãå¿ èŠãªã®ã§ããããä»ããã«é£ã¹ãŠããŸãã®ã§ãã ãã®åŸãAmazon RDS ã¯ãããã®ãªãœãŒã¹ã匷åããããã§ããã©ãŒãã³ã¹ãäœäžããŸãã 人ã ããã®åé¡ã«ã©ã®ããã«å¹²æžãå§ããŠãããã«ã€ããŠã¯ãåå¥ã®è©±ããããŸãã å Žåã«ãã£ãŠã¯éåžžã«æåããããšãããããŸãã ãã ããããã¯OSã®èšå®ãšã¯é¢ä¿ãããŸããã ã¯ã©ãŠãããããã³ã°ãããããªãã®ã§ãã ããã¯å¥ã®è©±ã§ãã
Huge TLB ãšæ¯èŒããŠãTransparent huge Page ãå¹æããªãã®ã¯ãªãã§ãã?
ãããªãã§ã ããã¯ããŸããŸãªæ¹æ³ã§èª¬æã§ããŸãã ãããå®éã«ã¯ã圌ãã¯ãã ãããäžããŸããã PostgreSQLã®æŽå²ã¯äœã§ãã? èµ·åæã«ã倧ããªå ±æã¡ã¢ãªãå²ãåœãŠãããŸãã éæãã©ããã¯å šãé¢ä¿ãããŸããã 圌ããæåã«ç®ç«ã€ãšããäºå®ããã¹ãŠã説æããŸãã ãŸãã倧éã®ã¡ã¢ãªããããshared_memory ã»ã°ã¡ã³ããåæ§ç¯ããå¿ èŠãããå Žåã¯ãééçãªãã¥ãŒãž ããŒãžãé¢é£ããŸãã PostgreSQL ã§ã¯ãæåã«å·šå€§ãªãã£ã³ã¯ã«å²ãåœãŠãããã ãã§ãããã ãã§ããã®åŸã¯ç¹å¥ãªããšã¯äœãèµ·ãããŸããã ãã¡ãã䜿çšã§ããŸãããäœããåå²ãåœãŠãããšãã«shared_memoryãç Žæããå¯èœæ§ããããŸãã PostgreSQL ã¯ããã«ã€ããŠç¥ããŸããã
åºæïŒ habr.com