(ãã®èšäºã¯ã Improve PostgreSQL performance: Diagnose and mitigate lock manager contention ã翻蚳ãããã®ã§ãã) ã¯ãŒã¯ããŒããæ¡åŒµããã«ã€ããŠãããŒã¿ããŒã¹ã®èªã¿åãæäœãäºæããé
ããªã£ãŠããŸãããïŒPostgreSQL ããŒã¹ã®ã·ã¹ãã ãéçšããŠããå€ãã®çµç¹ã§ã¯ãããã«ã¯æããã«ãªããªãããã©ãŒãã³ã¹ã®ããã«ããã¯ã«ééããäºããããŸãã倿°ã®ããŒãã£ã·ã§ã³ãã€ã³ããã¯ã¹ãæã€ããŒãã«ã«å¯ŸããŠå€ãã®åæèªã¿åãæäœãã¢ã¯ã»ã¹ãããšã PostgreSQL ã®é«éãã¹ããã¯æ©èœ ã䜿ãæãããã·ã¹ãã ãå
±æã¡ã¢ãªããã¯ã䜿çšããããåŸãªããªãããšããããŸããé«éãã¹ããå
±æã¡ã¢ãªããã¯ãžã®åãæ¿ãã¯ãããã¯ãããŒãžã£ãŒã«ãããŠè»œéãã㯠( LWLock ) ã®ç«¶åãçã¿åºããèªã¿åãå°çšæäœã§ãã£ãŠãããŒã¿ããŒã¹ã®ããã©ãŒãã³ã¹ã«åœ±é¿ãäžããŸãã æ¬æçš¿ã§ã¯ãèªã¿åãéçŽåã®ã¯ãŒã¯ããŒãããé«éãã¹ããã¯ã®å¶éãè¶
ããããšã«ãã£ãŠ LWLock ç«¶åãåŒãèµ·ããä»çµã¿ãæ¢æ±ããŸããããã¯ãPostgreSQL ãšã³ãžã³ãšãã®ããã¯æ©æ§ã«åºã¥ãä»»æã®ã·ã¹ãã ã§çºçããå¯èœæ§ãããåé¡ã§ãããã¢ã³ã¹ãã¬ãŒã·ã§ã³ç®çã§æ¬æçš¿ã§ã¯ Aurora PostgreSQL äºæãšãã£ã·ã§ã³ ã䜿çšããŸããå®éçãªå®éšãéããŠãããŒãã£ã·ã§ã³ã¹ãã£ã³ãã€ã³ããã¯ã¹ã®äœ¿çšãè€éãªçµåãããã¯åäœã«ã©ã®ãããªåœ±é¿ãäžãããã宿ŒããŸãããŸããã¯ãŒã¯ããŒããäœéãã¹ããã¯ã«ç§»è¡ããã¿ã€ãã³ã°ãç¹å®ããæ¹æ³ãšãããè¯ãããã©ãŒãã³ã¹ã®ããã«ã¯ãšãªèšèšãšã¹ããŒãæ§é ãæé©åããå
·äœçãªæè¡ãå®è£
ããæ¹æ³ã瀺ããŸãã èªã¿åãéçŽåã®ã¯ãŒã¯ããŒããå¢å ããã«ã€ããŠãããŒã¿ããŒã¹ç®¡çè
(DBA) ã¯ãããŒã¿ããŒã¹ãå¥å
šã«ä¿ã€ããã« LWLock ç«¶åãç£èŠãã察åŠããå¿
èŠããããŸããåæ§ã«ãéçºè
ã¯é床ã®ç«¶åãåŒãèµ·ãããã¿ãŒã³ãé¿ããªããã°ãªããŸãããããŒã¿ããŒã¹ãé«éãã¹ããäœéãã¹ããã¯ã«ç§»è¡ãããšãã¹ã«ãŒãããã¯å€§å¹
ã«äœäžããå¯èœæ§ããããŸã (æ¬æçš¿ã®ãã¹ãã§ã¯æå€§ 34 ããŒã»ã³ãã®ããã©ãŒãã³ã¹å·®ã瀺ããŠããŸã)ããã®ã¹ã«ãŒãããäœäžã¯ã LWLock:lock_manager ãªã©ã®åŸ
æ©ã€ãã³ããç£èŠãã pg_locks ãã¥ãŒã確èªããŠã¯ãŒã¯ããŒããå®è¡ããŠãã ããã¯ãšã³ãããã»ã¹ ã®é«éãã¹ã¹ããããæ¯æžããŠãããã©ããã確èªããããšã§ç¹å®ã§ããŸãããããã®ããã«ããã¯ã«å¯ŸåŠããã«ã¯ã广ç㪠ããŒãã£ã·ã§ã³ãã«ãŒãã³ã° ãæ
éãªã€ã³ããã¯ã¹ç®¡çãPostgreSQL ã®ããã¯ãšã³ãããã»ã¹ããã 16 ã¹ãããã®é«éãã¹å¶éå
ã«ã¯ãŒã¯ããŒããåããã·ã³ãã«ãªçµåãã¿ãŒã³ãªã©ã®æŠç¥ãå¿
èŠã§ãã LWLock ç«¶åã®çè§£ LWLock ã¯ãå
±æã¡ã¢ãªæ§é ãžã®ã¢ã¯ã»ã¹ã調æŽããããã« PostgreSQL ã§äœ¿çšãããåæã®ä»çµã¿ã§ãã ãããŒãŠã§ã€ããã㯠( ãŠãŒã¶ãŒäž»å°ã®ããŒã¿ããŒã¹ãªããžã§ã¯ãã¬ãã«ã®ããã¯) ãšã¯ç°ãªããLWLock ã¯è»œéã§é«ããã©ãŒãã³ã¹ã«æé©åãããŠãããå
±æããŒã¿ãžã®åæã¢ã¯ã»ã¹ã管çããªããäœããªãŒããŒããããæäŸããŸãã LWLock ç«¶åã¯ãè€æ°ã®ããã»ã¹ãå
±æã¡ã¢ãªå
ã® ããã¯ããŒã¿æ§é äžã®åã LWLock ãååŸããããšç«¶åããéã«çºçããé
å»¶ãåŒãèµ·ãããŸãããã®ç«¶åã¯éåžžãå€ãã®ããã¯ãšã³ãããã»ã¹ã次ã®ãããªé »ç¹ã«å
±æããããªãœãŒã¹ã«ã¢ã¯ã»ã¹ããå¿
èŠãããå Žåã«çºçããŸãã Buffer Manager â èªã¿åã/æžãèŸŒã¿æäœäžã«å
±æãããã¡ãä¿è·ãã Lock Manager â ããã¯é¢é£ã®ããŒã¿æ§é ãžã®ã¢ã¯ã»ã¹ã調æŽãã WAL management â å
è¡æžã蟌ã¿ãã° ( WAL ) ãžã®æžã蟌ã¿ãåæãã LWLock ç«¶åã¯ãåæããŒã¿ããŒã¹æ¥ç¶æ°ãå¢å ããã«ã€ããŠå¢å€§ããå¯èœæ§ããããŸãã ããã¯ç¹ã«ãAurora PostgreSQL äºæãšãã£ã·ã§ã³ããé«äžŠååŠçã倿°ã®ããŒãã£ã·ã§ã³ãæã€ããŒãã«ããŸãã¯å€æ°ã®ã€ã³ããã¯ã¹ãæã€ããŒãã«ãå«ãã¯ãŒã¯ããŒãã®é«ã¹ã«ãŒãããç°å¢ã§é¡èã§ãã ããŒãã«ã«å¯Ÿã㊠SQL ã¯ãšãªãå®è¡ããéãPostgreSQL ã¯ãã®ããŒãã«ãšé¢é£ããã€ã³ããã¯ã¹ã«å¯ŸããŠããã¯ãååŸããããšããŸãããããããŒãã£ã·ã§ã³ããŒãã«ã®å ŽåãSQL ã¯ãšãªã«ãã£ãŠã¢ã¯ã»ã¹ãããããŒãã«ããŒãã£ã·ã§ã³ã«å¯ŸããŠããã¯ãååŸãããŸããããã¯ãããé«éãã€å¹ççã«ãããããã·ã¹ãã ãä»ã®ããã¯ãšã®ç«¶åããªãããšãè¿
éã«ç¢ºèªã§ããå Žåãå¶éã®å°ãªã匱ããã㯠( AccessShareLock ã RowShareLock ã RowExclusiveLock ãªã©) ã«å¯ŸããŠé«éãã¹ããã¯ã䜿çšãããŸãã é«éãã¹ããã¯: åäœã¡ã«ããºã PostgreSQL ã§ã¯ãé«éãã¹ãããã³ã°æ©æ§ã«ãããç«¶åããªãæäœã¯å
±æã¡ã¢ãªããã¯ããã·ã¥ããŒãã«ãšããã«é¢é£ãã LWLocks ããã€ãã¹ããããšãã§ããŸããé«éãã¹ãããã³ã°ã¯ãæãäžè¬çãªäœ¿çšäŸãå³ã¡ãåããªã¬ãŒã·ã§ã³ã«ç«¶åãã匷åãªããã¯ãååšããªãéãã匱ãããã¯ãååŸããé »ç¹ãªåæã¯ãšãªåãã«èšèšãããŠããŸãã é«éãã¹ããã¯ã®åäœã¯ä»¥äžã®éãã§ãã ã»ãã·ã§ã³å¥ãã£ãã·ã¥ â åããã¯ãšã³ãããã»ã¹ã¯ãé«éãã¹ããã¯ãæ ŒçŽããããã«ããã©ã€ããŒã PGPROC æ§é å
ã«æå€§ 16 åã®ã¹ããã (ããã©ã«ãã§ FP_LOCK_SLOTS_PER_BACKEND = 16 ) ãå²ãåœãŠãŸãã è¿
éãªé«éãã¹é©æ Œæ§ãã§ã㯠â SELECT * FROM my_table; ãå®è¡ãããšãPostgreSQL ã¯å°ããªããã¯ãšã³ãå¥ã® LWLock ( MyProc->fpInfoLock ) ãååŸããŠãçŸåšã® SQL ã¯ãšãªã«é«éãã¹ããã¯æ©æ§ã䜿çšã§ãããã以äžã®é
ç®ããã§ãã¯ããŸãã ããã¯ã¢ãŒãã驿 ŒãªåŒ±ãã¢ãŒãã§ããããš ä»ã®ã»ãã·ã§ã³ãç«¶åããããã¯ãä¿æããŠããªãããš ããã¯ãšã³ããããŒã«ã«ããã¯ãšã³ãã¡ã¢ãªé
åã® 16 ã¹ãããããã¹ãŠäœ¿çšããŠããªãããš ããŒã«ã«èš±å¯ â äžèšã®é«éãã¹é©æ Œæ§ãã§ãã¯ããã¹ãããšã FastPathGrantRelationLock() ãããã¯ãšã³ãã®ããŒã«ã«ãã£ãã·ã¥ã«ããã¯ãæ ŒçŽããŸããå
±æã¡ã¢ãªããŒã¹ã®ããã¯ããã·ã¥ããŒãã«ãä¿è·ããå
±æã¡ã¢ãªããŒã¹ã® LWLock ã¯ååŸãããã颿°ã¯æåããŠå³åº§ã«ãªã¿ãŒã³ããŸãã å®éã«ã¯ãããã¯ãã©ã³ã¶ã¯ã·ã§ã³ãã¢ã¯ã»ã¹ããæåã® 16 åã®äžæã®ããŒãã« (ãŸãã¯ã€ã³ããã¯ã¹) ã§ã¯ãããã¯ãããŒãžã£ãŒã®ãªãŒããŒãããã¯ã»ãŒãŒãã«ãªãããšãæå³ããŸãã é«éãã¹ãã£ãã·ã¥ã¯å°ããã16 åã®ããã¯ãè¶
ãããããã匷ãããã¯ã¢ãŒããèŠæ±ããããããšãPostgreSQL ã¯äœéãã¹ã«ãã©ãŒã«ããã¯ããªããã°ãªããŸããã æ¢ã«ååŸæžã¿ã® 16 åã®é«éãã¹ããã¯ãè¶
ããé«éãã¹ããã¯ã®ãã¹ãŠã®èŠæ±ã¯ã FastPathTransferRelationLocks() ã䜿çšããŠå
±æããã¯ããŒãã«ã«ç§»è¡ãããŸã ããã¯ã¿ã°ïŒãªã¬ãŒã·ã§ã³ OID ãšããã¯ã¢ãŒããå«ãïŒã¯ãå
±æã¡ã¢ãªããã¯ããã·ã¥ããŒãã«ã® 16 åã®ããã¯ããŒãã£ã·ã§ã³ã®ãã¡ã® 1 ã€ã«ããã·ã¥åãããŸã PostgreSQL ã¯æ¬¡ã«ãããŒãã£ã·ã§ã³ LWLock ( LWLockAcquire(partitionLock, LW_EXCLUSIVE) ) ãååŸããå
±æããã·ã¥ããŒãã«ãæŽæ°ããLWLock ãè§£æŸããŸã ãã以éãããŒãã«ããã€ã³ããã¯ã¹ãŸã§ã®è¿œå ã®ããã¯ååŸã¯ãããã¯ãããŒãžã£ãŒãéããŠè¡ãããåæå®è¡äžã§ LWLock:LockManager åŸ
æ©ã€ãã³ããçæããŸãã é«éãã¹æé©åããäœéãã¹ç«¶åãžã®ç§»è¡ãçè§£ããããšã§ãé«éãã¹ã®å¶éå
ã«çãŸãã¯ãšãªãšã¹ããŒããèšèšããããã¯ãããŒãžã£ãŒã®ããã«ããã¯ãå®å
šã«åé¿ã§ããŸãã å®éšæŠèР以äžã®ã»ã¯ã·ã§ã³ã§ã¯ã3 ã€ã®å®éšãéã㊠LWLock ç«¶åã®è©³çްã説æããŸãã ããŒãã£ã·ã§ã³åãããããŒãã«ã§ã®ããã¯ã芳å¯ãã 䜿çšãããŠããªãããŸãã¯äžèŠãªè€æ°ã®ã€ã³ããã¯ã¹ãæã€ããŒãã£ã·ã§ã³åãããŠããªãããŒãã«ã§ã®ããã¯ã芳å¯ãã ãã«ãçµåã¯ãšãªã§ã®ããã¯åäœã芳å¯ãã ããããã®å®éšã§ã¯ãã¹ããŒãã®ã»ããã¢ãããã¯ãŒã¯ããŒãã®å®è¡ãPostgreSQL ã·ã¹ãã ãã¥ãŒã«ããããã¯ç£èŠãããã³ pgbench ã䜿çšããåæå®è¡ã®åœ±é¿åæã宿ŒããŸãããã¹ãŠã®å®éšã¯ãdb.r7g.4xlarge ã€ã³ã¹ã¿ã³ã¹ã䜿çšã㊠Aurora PostgreSQL äºæãšãã£ã·ã§ã³ (PostgreSQL 16.6 ãšäºææ§ãã) ã§å®æœããŸããã åææ¡ä»¶ å®éšãéå§ããåã«ã以äžãå¿
èŠãšãªããŸãã Aurora PostgreSQL ããŒã¿ããŒã¹ãžã®ã¢ã¯ã»ã¹æš©ãæã€ AWS ã¢ã«ãŠã³ã AWS Management Console ãžã®ã¢ã¯ã»ã¹æš© Aurora PostgreSQL ã€ã³ã¹ã¿ã³ã¹ãžã®æ¥ç¶æ§ãæã€ Amazon Elastic Compute Cloud (Amazon EC2) ã€ã³ã¹ã¿ã³ã¹ Amazon EC2 ã€ã³ã¹ã¿ã³ã¹äžã«ã€ã³ã¹ããŒã«ããã PostgreSQL ã¯ã©ã€ã¢ã³ã (psql ãªã©) Amazon EC2 ã€ã³ã¹ã¿ã³ã¹äžã«ã€ã³ã¹ããŒã«ããã pgbench Aurora PostgreSQL ã¯ã©ã¹ã¿ãŒãäœæã»ç®¡çããããã® AWS Identity and Access Management (IAM) æš©é å®éš 1 : ããŒãã£ã·ã§ã³åãããããŒãã«ã§ã®ããã¯ã芳å¯ãã ãã®å®éšã§ã¯ã3 ã€ã®äž»èŠãªãã¹ãã·ããªãªãå®è¡ããããŒãã£ã·ã§ã³åãããããŒãã«ãæ±ãéã® PostgreSQL ã®ããã¯åäœã調æ»ããŸãã orders ããŒãã«ã®ãã¹ãŠã®ããŒãã£ã·ã§ã³ãã¯ãšãªããŠãPostgreSQL ãããŒãã£ã·ã§ã³å
šäœã«ããã£ãŠããã¯ãã©ã®ããã«åŠçãããã芳å¯ããŸã ç¹å®ã®ããŒãã£ã·ã§ã³ã«ã¢ã¯ã»ã¹ããããã«ããŒãã£ã·ã§ã³ãã«ãŒãã³ã°ã䜿çšããŠãããçãçµã£ãã¢ãããŒããæ€èšŒããŸã å®äžçã®ã¯ãŒã¯ããŒããã·ãã¥ã¬ãŒããããã ã« pgbench ã䜿çšããŠãé«ãåæå®è¡äžã§äž¡æ¹ã®ã¢ãããŒããã¹ãã¬ã¹ãã¹ãããŸã ãããã®ã¯ãšãªãéããŠãPostgreSQL ã®é«éãã¹ããã¯æé©åãã©ã®ããã«åäœããããé«éãã¹ã¹ããããæ¯æžãããšãã«äœãèµ·ããããããã³åæå®è¡ã¯ãŒã¯ããŒãäžã§ããŒãã£ã·ã§ã³ãã«ãŒãã³ã°ãããã«ããã©ãŒãã³ã¹ãæ¹åã§ãããã宿ŒããŸãã order_ts ã¿ã€ã ã¹ã¿ã³ãåã䜿çšããŠæåäœã§ããŒã¿ãããŒãã£ã·ã§ã³åããã orders ããŒãã«ã䜿çšããŸãã ãã®å®éšã§ã¯ã以äžã®ç¹ã«ã€ããŠéèŠãªæŽå¯ãæããã«ãªããŸãã PostgreSQL ãèªã¿åãå°çšæäœäžã«ããã¯ãã©ã®ããã«ç®¡çããã é«éãã¹ãšäœéãã¹ããã¯ã®åœ±é¿ ããŒãã£ã·ã§ã³ãã«ãŒãã³ã°ãã©ã®ããã«ããã¯ç«¶åã軜æžã§ããã é«äžŠè¡æ§ç°å¢ã«ãããããã©ãŒãã³ã¹ãžã®åœ±é¿ ã¹ããŒãæºå 奜ã¿ã® PostgreSQL ã¯ã©ã€ã¢ã³ã (psql ãªã©) ã䜿çšããŠãEC2 ã€ã³ã¹ã¿ã³ã¹ãã Aurora PostgreSQL ã€ã³ã¹ã¿ã³ã¹ã«æ¥ç¶ãã12 ã®ææ¬¡åããŒãã£ã·ã§ã³ãæã€ orders ããŒãã«ãäœæããŸãã以äžã® SQL ã³ãŒããå®è¡ããŠãã ããã -- Create the schema CREATE SCHEMA experiment_1; SET search_path TO experiment_1; -- Create the partitioned parent table CREATE TABLE orders ( id int NOT NULL, order_ts timestamp NOT NULL, customer_id int, amount numeric, PRIMARY KEY (id, order_ts) ) PARTITION BY RANGE (order_ts); -- Create child partitions (example: Jan 2025 - Jun 2025) DO $$ DECLARE month int := 1; partition_name text; from_date date; to_date date; BEGIN WHILE month <= 12 LOOP partition_name := 'orders_2025_' || to_char(month, 'FM00'); from_date := make_date(2025, month, 1); to_date := from_date + interval '1 month'; EXECUTE format( 'CREATE TABLE %I.%I PARTITION OF %I.%I FOR VALUES FROM (%L) TO (%L)', 'experiment_1', partition_name, 'experiment_1', 'orders', from_date, to_date ); month := month + 1; END LOOP; END $$; ãã¹ã 1 : Orders ããŒãã«ã®å
šããŒãã£ã·ã§ã³ãã¯ãšãªããŠãããã¯åäœã芳å¯ãã ããã§ã¯ããã©ã³ã¶ã¯ã·ã§ã³ãéå§ããããŒãã£ã·ã§ã³åããã orders ããŒãã«ã®ãã¹ãŠã®ããŒãã£ã·ã§ã³ã«å¯ŸããŠã¯ãšãªãå®è¡ããŸããããŒãã£ã·ã§ã³ãã«ãŒãã³ã°ç¡ã§ã¯ãšãªãå®è¡ãããšãPostgreSQL ã¯ãã¹ãŠã®ããŒãã£ã·ã§ã³ã«ã¢ã¯ã»ã¹ããå¿
èŠããããããã¯ã®ãªãŒããŒãããã倧å¹
ã«å¢å ããŸãããã®ãã¹ããéå§ããã«ã¯ãAurora PostgreSQL ããŒã¿ããŒã¹ãžã®æ°ããæ¥ç¶ãéãã以äžã®ã³ãã³ããå®è¡ããŸã (ãããã»ãã·ã§ã³ 1 ãšåŒã³ãŸã) ã postgres=> set search_path to experiment_1; SET postgres=> begin; BEGIN postgres=*> select count(*) from orders; count ------- 0 (1 row) äžèšã®SQL ã¹ããŒãã¡ã³ãã¯ã12 ã®ããŒãã£ã·ã§ã³ãã¹ãŠãã¹ãã£ã³ãããã©ã³ã¶ã¯ã·ã§ã³ãéå§ããŸãã COMMIT ã ROLLBACK ãŸã㯠End ã³ãã³ããå®è¡ããããã©ã³ã¶ã¯ã·ã§ã³ãéãããŸãŸã«ããŠããã¯ãç¶æããŠãã ããã ã»ãã·ã§ã³ 1 ã®ãã©ã³ã¶ã¯ã·ã§ã³ãéãããŸãŸã®ç¶æ
ã§ã2 çªç®ã®ã»ãã·ã§ã³ (ãããã»ãã·ã§ã³ 2 ãšåŒã³ãŸã) ãéããããŒã¿ããŒã¹å
ã®ããã¯ã®ç¶æ
ã確èªããããã«æ¬¡ã® SQL ã¯ãšãªãå®è¡ããŠãã ããã postgres=> SELECT n.nspname AS schema, c.relname AS table, l.locktype, l.mode, l.fastpath FROM pg_locks l JOIN pg_class c ON l.relation = c.oid JOIN pg_namespace n ON c.relnamespace = n.oid AND n.nspname <> 'pg_catalog'; schema | table | locktype | mode | fastpath --------------+---------------------+----------+-----------------+---------- experiment_1 | orders_2025_07_pkey | relation | AccessShareLock | t experiment_1 | orders_2025_07 | relation | AccessShareLock | t experiment_1 | orders_2025_06_pkey | relation | AccessShareLock | t experiment_1 | orders_2025_06 | relation | AccessShareLock | t experiment_1 | orders_2025_05_pkey | relation | AccessShareLock | t experiment_1 | orders_2025_05 | relation | AccessShareLock | t experiment_1 | orders_2025_04_pkey | relation | AccessShareLock | t experiment_1 | orders_2025_04 | relation | AccessShareLock | t experiment_1 | orders_2025_03_pkey | relation | AccessShareLock | t experiment_1 | orders_2025_03 | relation | AccessShareLock | t experiment_1 | orders_2025_02_pkey | relation | AccessShareLock | t experiment_1 | orders_2025_02 | relation | AccessShareLock | t experiment_1 | orders_2025_01_pkey | relation | AccessShareLock | t experiment_1 | orders_2025_01 | relation | AccessShareLock | t experiment_1 | orders_pkey | relation | AccessShareLock | t experiment_1 | orders | relation | AccessShareLock | t experiment_1 | orders_2025_11 | relation | AccessShareLock | f experiment_1 | orders_2025_09 | relation | AccessShareLock | f experiment_1 | orders_2025_09_pkey | relation | AccessShareLock | f experiment_1 | orders_2025_11_pkey | relation | AccessShareLock | f experiment_1 | orders_2025_08_pkey | relation | AccessShareLock | f experiment_1 | orders_2025_10 | relation | AccessShareLock | f experiment_1 | orders_2025_08 | relation | AccessShareLock | f experiment_1 | orders_2025_10_pkey | relation | AccessShareLock | f experiment_1 | orders_2025_12_pkey | relation | AccessShareLock | f experiment_1 | orders_2025_12 | relation | AccessShareLock | f (26 rows) åè¿°ã®åºåã®æåŸã® 10 è¡ã§ fastpath åã®å€ã t (True) ãš f (False) ã®ãã¡ãf ã«ãªã£ãŠããããšã«æ³šç®ããŠãã ããããŸããè¿ãããè¡ã®ç·æ°ã¯ 26 ã§ããå€ t ã¯ã16 ã®é«éãã¹ã¹ãããã䜿ãæããããæ®ãã®ããŒãã£ã·ã§ã³/ã€ã³ããã¯ã¹ã® AccessShareLock ãå
±æã¡ã¢ãªããã¯ããã·ã¥ããŒãã« (äœéãã¹) ã«ç§»è¡ãããããšãæå³ããŸãããã©ã³ã¶ã¯ã·ã§ã³ãå®äºãããšããããã®ããã¯ã¯è§£æŸãããŸãã ãã¹ã 2 : 泚æããŒãã«ã®ç¹å®ã®ããŒãã£ã·ã§ã³ãã¯ãšãªããããã¯åäœã®å€åã芳å¯ãã ã»ãã·ã§ã³ 1 ãšããŠã以äžã«ç€ºãããã«ãæ°ãããã©ã³ã¶ã¯ã·ã§ã³å
ã§ããŒãã£ã·ã§ã³ãã«ãŒãã³ã°ã¢ãããŒãã䜿çšããã¯ãšãªãå®è¡ããŸããããå€ãã®ããŒãã£ã·ã§ã³ã«ã¢ã¯ã»ã¹ãç¶ãããšã远å ã®é«éãã¹ããã¯ãç²åŸãããŸãã postgres=> set search_path to experiment_1; SET postgres=> begin; BEGIN postgres=*> SELECT * FROM orders WHERE order_ts <= '2025-01-01'; id | order_ts | customer_id | amount ----+----------+-------------+-------- (0 rows) postgres=*> SELECT * FROM orders WHERE order_ts <= '2025-02-01'; id | order_ts | customer_id | amount ----+----------+-------------+-------- (0 rows) postgres=*> SELECT * FROM orders WHERE order_ts <= '2025-03-01'; id | order_ts | customer_id | amount ----+----------+-------------+-------- (0 rows) ã»ãã·ã§ã³ 1 ã®ãã©ã³ã¶ã¯ã·ã§ã³ãéãããŸãŸã®ç¶æ
ã§ãååã®ãã¹ãã§å©çšããã»ãã·ã§ã³ 2 ã§ä»¥äžã® SQL æãå®è¡ããŠãããŒã¿ããŒã¹å
ã®ããã¯ã®ç¶æ
ã確èªããŠãã ããã postgres=> SELECT n.nspname AS schema, c.relname AS table, l.locktype, l.mode, l.fastpath FROM pg_locks l JOIN pg_class c ON l.relation = c.oid JOIN pg_namespace n ON c.relnamespace = n.oid AND n.nspname <> 'pg_catalog'; schema | table | locktype | mode | fastpath --------------+---------------------+----------+-----------------+---------- experiment_1 | orders_2025_03_pkey | relation | AccessShareLock | t experiment_1 | orders_2025_03 | relation | AccessShareLock | t experiment_1 | orders_2025_02_pkey | relation | AccessShareLock | t experiment_1 | orders_2025_02 | relation | AccessShareLock | t experiment_1 | orders_2025_01_pkey | relation | AccessShareLock | t experiment_1 | orders_2025_01 | relation | AccessShareLock | t experiment_1 | orders_pkey | relation | AccessShareLock | t experiment_1 | orders | relation | AccessShareLock | t (8 rows) ãã®åºåã¯ããã¹ãŠã®ããã¯ãé«éãã¹ããã¯ã§ããããšã瀺ããŠããã fastpath åã®å€ã t (True) ã«ãªã£ãŠããŸããããã«ãããäœéãã¹ããã¯ãååŸããå¿
èŠããªããªããŸãã ããã¯é«éãã¹ã®æé©åã瀺ããŠããŸãããåæå®è¡ã䌎ãã·ããªãªã«ã€ããŠã¯èª¬æããŠããŸãããåæå®è¡ã䌎ãã·ããªãªã§ã¯ãåããã¯ãšã³ããé«éãã¹ã¹ãããã䜿ãåããã16 ã¹ãããã®å¶éå
ã«çãŸããã®ããããã«ãªããŸãããã®ç¹å®ã®ã·ããªãªã詳ããèŠãŠãããŸããããpgbench ã䜿çšããŠãã«ããŠãŒã¶ãŒã¯ãŒã¯ããŒããã·ãã¥ã¬ãŒãããŸãã ãã¹ã 3.1 : Orders ããŒãã«ã®å
šããŒãã£ã·ã§ã³ã«è€æ°ã¯ãšãªãåæå®è¡ããããã¯åäœã®å€åã芳å¯ãã ããŒãã£ã·ã§ã³ãã«ãŒãã³ã°ããã«ããŒãã£ã·ã§ã³ã«ã¢ã¯ã»ã¹ããè€æ°ã®èªã¿åãã¯ãŒã¯ããŒããã·ãã¥ã¬ãŒãããã«ã¯ã以äžã® pgbench ã³ãã³ãã䜿çšããŸãããã®ã³ãã³ãã¯è€æ°ã®ã¹ã¬ããã«ããã£ãŠ SELECT count(*) FROM orders ã¯ãšãªãç¶ç¶çã«çºè¡ããŸãããã®ãã¹ãã§ã¯ããã©ã³ã¶ã¯ã·ã§ã³ãé«éãã¹ã¹ãããã䜿ãæãããã¡ã€ã³ããã¯ãããŒãžã£ãŒãéããŠããã¯ã®ç²åŸã匷å¶ãã ( LWLock:LockManager ã®åŸ
æ©ãããªã¬ãŒãã) é«ã䞊å床ã®äžã§ãPostgreSQL ã®é«éãã¹ããã¯æé©åãã©ã®ããã«åäœããããè©äŸ¡ããŸãã pgbench -c 100 -j 10 -n -f transaction.sql -T 900 pgbench ã§ã¯ã -c ãš -j ãªãã·ã§ã³ã䜿çšããŠãã³ãããŒã¯ã¯ãŒã¯ããŒãã®åæå®è¡æ§ãšäžŠåæ§ãå¶åŸ¡ããŸãã-c ãªãã·ã§ã³ã¯åæã¯ã©ã€ã¢ã³ãæ°ãæå®ããåæã«ã¢ã¯ãã£ãã«ãªãã·ãã¥ã¬ãŒãããããŠãŒã¶ãŒã»ãã·ã§ã³ãŸãã¯ããŒã¿ããŒã¹æ¥ç¶ã®æ°ãæå³ããŸãããã®æ°å€ã«ãã£ãŠãPostgreSQL ããŒã¿ããŒã¹ã«é©çšãããè² è·ã®ã¬ãã«ã決ãŸããŸãã -j ãªãã·ã§ã³ã¯ pgbench ããããã®ã¯ã©ã€ã¢ã³ãæ¥ç¶ã管çããããã«äœ¿çšããã¯ãŒã«ãŒã¹ã¬ããã®æ°ãå®çŸ©ããŸããåã¹ã¬ããã¯å
šã¯ã©ã€ã¢ã³ãã®äžéšãåŠçããã¯ãŒã¯ããŒãã¯ã¹ã¬ããéã§åçã«åæ£ãããŸããããã«ãã pgbench ã¯ãã«ãã³ã¢ã·ã¹ãã ãããå¹ççã«äœ¿çšããã¯ã©ã€ã¢ã³ãåŽã®ããã«ããã¯ãåé¿ã§ããŸãã å®è¡æã«èªèšŒæ
å ±ãå
¥åããã« pgbench ã³ãã³ããå®è¡ããã«ã¯ã次ã®ç°å¢å€æ°ãèšå®ããŸãïŒ PGHOST (Auroraã¯ã©ã¹ã¿ãŒã®ãšã³ããã€ã³ã)ã PGPORT (ããŒãçªå·ãäŸãã° 5432)ã PGDATABASE (ããŒã¿ããŒã¹åãäŸãã° postgres)ã PGUSER (ããŒã¿ããŒã¹ãŠãŒã¶ãŒ)ãããã³ PGPASSWORD (ããŒã¿ããŒã¹ãŠãŒã¶ãŒã®ãã¹ã¯ãŒã)ã äžèšã® pgbench ã³ãã³ãã¯ãtransaction.sql ã§å®çŸ©ããããã©ã³ã¶ã¯ã·ã§ã³ãå®è¡ãã 100 ã®åæã¯ã©ã€ã¢ã³ã ( -c 100 ) ã 15 åéãŸã㯠900 ç§ ( -T 900) ã·ãã¥ã¬ãŒãããŸãã transaction.sql ãã¡ã€ã«ã«ã¯ã experiment_1 ã¹ããŒããžã®æ€çŽ¢ãã¹ã®èšå®ãšãšãã«ã次㮠SQL ãå«ãŸããŠããŸãã set search_path to experiment_1; DO $$ DECLARE random_date date; last_day date; sql text; BEGIN random_date := date '2025-01-01' + floor(random() * 365)::int; last_day := (date_trunc('month', random_date) + interval '1 month - 1 day')::date; sql := format('SELECT COUNT(*) FROM orders'); EXECUTE sql; END $$; ååã®ãã¹ãã®ã»ãã·ã§ã³ 1 ã¿ãŒããã«ãããæ¬¡ã® pgbench ã³ãã³ããå®è¡ããŠãã ããããã®ã³ãã³ã㯠15 åã§å®äºããŸãããã®ã³ãã³ãã®å®è¡äžã Amazon CloudWatch Database Insights ã®ããŒã¿ããŒã¹ã®åŸ
æ©ã€ãã³ããç£èŠããŸãã sh-5.1$ pgbench -c 100 -j 10 -n -f transaction.sql -T 900 transaction type: transaction.sql scaling factor: 1 query mode: simple number of clients: 100 number of threads: 10 duration: 900 s number of transactions actually processed: 42005251 latency average = 2.143 ms tps = 46672.159856 (including connections establishing) tps = 46672.894164 (excluding connections establishing) ãã®ã¯ãŒã¯ããŒãã§ã¯ã1 ç§ãããã®å¹³åãã©ã³ã¶ã¯ã·ã§ã³æ° (tps) 㯠46,672 ã«éãããã¹ãæéã® 15 åéã§ 4,200 äžã®ãã©ã³ã¶ã¯ã·ã§ã³ãåŠçãããŸããã CloudWatch Database Insights ã®ä»¥äžã®ã¹ã¯ãªãŒã³ã·ã§ããã¯ãã¢ã¯ãã£ããªã»ãã·ã§ã³ãã€ã³ã¹ã¿ã³ã¹ã® CPU 容éãè¶
ããèããããã¯ç«¶åã䌎ãé«è² è·ãçµéšããŠããããŒã¿ããŒã¹ã瀺ããŠããŸãã äžã®ã¹ã¯ãªãŒã³ã·ã§ããã¯ãdb.r7g.4xlarge ã€ã³ã¹ã¿ã³ã¹ã§å®è¡ãããŠãã Aurora PostgreSQL 16.6 ã¯ã©ã¹ã¿ãŒããCPU 䜿çšçãšããã¯ç«¶åã®äž¡æ¹ã«ããé«ãããŒã¿ããŒã¹è² è·ã瀺ããŠããŸããå¹³åã¢ã¯ãã£ãã»ãã·ã§ã³ (AAS) ã¯çŽ 21 ã§ç¶æãããŠãããããã¯ã€ã³ã¹ã¿ã³ã¹ã® 16 vCPU 容éãããé«ãå€ã§ããè² è·ã® 66 ããŒã»ã³ã㯠CPU 䜿çšçã«èµ·å ããŠããŸããã34 ããŒã»ã³ã㯠LWLock:LockManager ã®åŸ
æ©ã«è²»ããããŠãããPostgreSQL å
éšããã¯æ§é ã®ç«¶åã瀺ããŠããŸãã æ¬¡ã«ãããŒãã£ã·ã§ã³ãã«ãŒãã³ã°ã䜿çšããŠããã©ãŒãã³ã¹ãè©äŸ¡ããŸãã ãã¹ã 3.2 : ããŒãã£ã·ã§ã³ãã«ãŒãã³ã°ã䜿çšããŠãOrders ããŒãã«ã®ç¹å®ã®ããŒãã£ã·ã§ã³ã«è€æ°ã¯ãšãªãåæå®è¡ãã ããŒãã£ã·ã§ã³ãã«ãŒãã³ã°ã䜿çšããŠããªãååã®ãã¹ããšå¯Ÿæ¯ããããã«ãããã§ã¯ããŒãã£ã·ã§ã³ãã«ãŒãã³ã°ãã©ã®ããã«ããã©ãŒãã³ã¹ãåäžãããããå®èšŒããŸãããã®ã¯ãŒã¯ããŒãã§ã¯ããã®å®éšã«äœ¿çšããã transaction.sql ãã¡ã€ã«ã«ã¯ PL/pgSQL ãå«ãŸããŠããŸããããã¯åçŽãª SQL ã¯ãšãªã®ä»£ããã«äœ¿çšãããããŒãã£ã·ã§ã³åå²ãããããŒãã«ãšå®è¡æã«çæãããå€ãåŠçããéã«ãPostgreSQL ã§å¹ççãªããŒãã£ã·ã§ã³ãã«ãŒãã³ã°ãæäŸããŸãã以äžã®ã¯ãšãªã®ããã« SQL ã䜿çšããããšãã§ããŸããã order_ts ã«å¯Ÿãããã£ã«ã¿ãŒã¯å
±éããŒãã«åŒ (CTE) å
ã®ã©ã³ãã ã«çæãããæ¥ä»ããæŽŸçããŠãããæé©ãªå®è¡èšç»ãäœæããã¯ãšãªãã©ã³ããŒã¯ãã¯ãšãªèšç»æã« order_ts å€ã決å®ã§ããŸããããã®çµæãPostgreSQL ã¯ãã¹ãŠã®ããŒãã£ã·ã§ã³ãèæ
®ããå¿
èŠãããããã¹ãŠã®ããŒãã£ã·ã§ã³ã®äžå¿
èŠãªããã¯ãšã¹ãã£ã³ã«ã€ãªãããŸããããããã©ã³ãã ãªæ¥ä»ãèšç®ã EXECUTE ã䜿çšããŠã¯ãšãªãåçã«æ§ç¯ãã PL/pgSQL ãããã¯ã«åãæ¿ããããšã§ãå®éã®æ¥ä»å€ã SQL æååã«çŽæ¥æ³šå
¥ãããŸããããã«ãããã¯ãšãªãã©ã³ããŒã®èгç¹ãããã£ã«ã¿ãŒã宿°ã«å€æããã广çãªããŒãã£ã·ã§ã³ãã«ãŒãã³ã°ãå¯èœã«ãªããé¢é£ããããŒãã£ã·ã§ã³ã®ã¿ãã¢ã¯ã»ã¹ããããã¯ãããããšãä¿èšŒãããŸãã 以äžã¯ãäžèšã§èª¬æãã CTE ããŒã¹ã® SQL ã¯ãšãªã§ããã¹ãŠã®ããŒãã£ã·ã§ã³ãããã¯ããå¯èœæ§ããããŸãã WITH rand_date AS ( SELECT (date '2025-01-01' + (floor(random() * 365))::int) AS dt ), last_day AS ( SELECT dt, (date_trunc('month', dt) + interval '1 month - 1 day')::date AS last_day FROM rand_date ) SELECT dt AS random_date, last_day, ( SELECT COUNT(*) FROM orders WHERE order_ts >= last_day.last_day AND order_ts < last_day.last_day + interval '1 day' ) AS order_count FROM last_day; äžèšã§èª¬æãã广çãªããŒãã£ã·ã§ã³ãã«ãŒãã³ã°ãå®çŸããããã«ã以äžã® PL/pgSQL ã¢ãããŒãã䜿çšãã SQL ã䜿çšããŠãã ããã set search_path to experiment_1; DO $$ DECLARE random_date date; last_day date; sql text; BEGIN random_date := date '2025-01-01' + (floor(random() * 365))::int; last_day := (date_trunc('month', random_date) + interval '1 month - 1 day')::date; sql := format('SELECT count(*) FROM orders WHERE order_ts = %L', last_day); EXECUTE sql; ååã®ãã¹ããšåãæé ã«åŸããã»ãã·ã§ã³ 1 ã®ã¿ãŒããã«ãã pgbench ã³ãã³ããå®è¡ããŠãã ããããã®ã³ãã³ã㯠15 åã§ãã¹ããå®äºããã³ãã³ãã®å®è¡äžãCloudWatch Database Insights ã®ããŒã¿ããŒã¹ã®åŸ
æ©ã€ãã³ããç£èŠããŸãã sh-5.15 pgbench -c 100 -j 10 -n -f transaction.sql -T 900 transaction type: transaction.sql scaling factor: 1 query mode: simple number of clients: 100 number of threads: 10 duration: 900 s number of transactions actually processed: 53240775 latency average = 1.690 ms tps = 59255.673468 (including connections establishing) tps = 59156.616479 (excluding connections establishing) äžèšã® pgbench åºåã«ç€ºãããŠããããã«ããã®ã¯ãŒã¯ããŒã㯠1 ç§ãããå¹³å 59,255 ãã©ã³ã¶ã¯ã·ã§ã³ãéæãã15 åéã®ãã¹ãæéäžã«çŽ 5,330 äžãã©ã³ã¶ã¯ã·ã§ã³ãåŠçãããŸãããããã¯ç«¶åããªãç¶æ
ã§ã¯ãã·ã¹ãã 㯠1,100 äžãã©ã³ã¶ã¯ã·ã§ã³ã远å ã§åŠçåºæ¥ãäºã«ãªããŸãã CloudWatch Database Insights ã®ä»¥äžã®ã¹ã¯ãªãŒã³ã·ã§ããã¯ãããŒãã£ã·ã§ã³ãã«ãŒãã³ã°ãå®è£
ããåŸã®æ¹åãããããŒã¿ããŒã¹ããã©ãŒãã³ã¹ã瀺ããŠãããå®å®ããè² è·ãã¿ãŒã³ãšããã¯ç«¶åããªãããšãããããŸãã åè¿°ã®ã¯ãŒã¯ããŒãã«ããŒãã£ã·ã§ã³ãã«ãŒãã³ã°ãå°å
¥ããããšã§ãAurora PostgreSQL 16.6 ã¯ã©ã¹ã¿ãŒã®ããã©ãŒãã³ã¹ã¯å€§å¹
ã«åäžããŸããã以åã¯ãã¯ãŒã¯ããŒã㯠CPU 䜿çšçãšäžŠãã§ããŒã¿ããŒã¹è² è·ã®çŽ 34 ããŒã»ã³ããæ¶è²»ãã LWLock:LockManager ã®åŸ
æ©ã€ãã³ãã«ãã£ãŠç¹åŸŽä»ããããŠããŸããã 察ç
§çã«ãçŸåšã®ã¯ãŒã¯ããŒãããã©ãŒãã³ã¹ã¯ãã©ã³ã¹ã®åããã¯ãŒã¯ããŒãã瀺ããŠããŸããå¹³åã¢ã¯ãã£ãã»ãã·ã§ã³ (AAS) ã¯æå€§ vCPU éŸå€ãäžåã£ãŠãããåŸ
æ©ã¯ Timeout:SpinDelay ã®ããäžéšã芳枬ãããŠããã®ã¿ã§ããCPU ã¯æé©åããã OLTP ã·ã¹ãã ã§ã¯äºæ³ãããéããè² è·ã®äž»èŠãªèŠå ãšãªã£ãŠããŸãããããŠããã¯ç«¶åã¯å€§å¹
ã«æžå°ããŸãããããã¯ãããŒãã£ã·ã§ã³ãã«ãŒãã³ã°ã«ãã£ãŠååŸãããããã¯ã®æ°ãåæžããåã»ãã·ã§ã³ãé¢é£ããããŒãã£ã·ã§ã³ãšã»ãã·ã§ã³ããšã®é«éãã¹ããã¯ã®ã¿ã«ã¢ã¯ã»ã¹ããããã«ãªããåæå®è¡æ§ã倧å¹
ã«åäžããããšãæå³ããŸããããŒãã£ã·ã§ã³ãã«ãŒãã³ã°ã«ãããAAS ã¯æå€§ vCPU éŸå€ãäžåã£ããŸãŸã§ããã å®éš 2 : è€æ°ã®æªäœ¿çšãŸãã¯äžèŠãªã€ã³ããã¯ã¹ãæã€éããŒãã£ã·ã§ã³ããŒãã«ã®ããã¯ã芳å¯ãã ãã®å®éšã§ã¯ãPostgreSQL ãè€æ°ã® B-tree ã€ã³ããã¯ã¹ ãæã€éããŒãã£ã·ã§ã³åããŒãã«ã§ã®ããã¯åäœãã©ã®ããã«åŠçãããã調æ»ããŸããããã§ã¯ã䜿çšãããŠããªããŸãã¯äžèŠãªã€ã³ããã¯ã¹ã®åœ±é¿ã«çŠç¹ãåœãŠãããã«ããŒãã£ã·ã§ã³åãããŠããªãããŒãã«ã䜿çšããŠããŸããE ã³ããŒã¹ãŸãã¯åšåº«ã·ã¹ãã ã衚ã items ããŒãã«ã䜿çšããŠã2 ã€ã®éèŠãªãã¹ãã·ããªãªãæ¢ããŸãã ãŸããã€ã³ããã¯ã¹ã®ã¿ã®ã¹ãã£ã³ã䜿çšããŠåçŽãªã¯ãšãªãå®è¡ãã以äžã芳å¯ããŸã PostgreSQL ãè€æ°ã®æªäœ¿çšãŸãã¯äžèŠãªã€ã³ããã¯ã¹å
šäœã§ããã¯ãã©ã®ããã«ç®¡çããã é«éãã¹ã¹ãããã䜿ãæããããå Žåã«äœãèµ·ããã 20 åã® B-tree ã€ã³ããã¯ã¹ãæã€ããšãããã¯ååŸã«äžããåœ±é¿ æ¬¡ã«ãé«ãåæå®è¡æ§ã®äžã§ã·ã¹ãã ã®ã¹ãã¬ã¹ãã¹ããè¡ãã以äžãå®èšŒããŸã éå°ãªã€ã³ããã¯ã¹ãããã¯ãããŒãžã£ãŒã®åäœã«ã©ã®ããã«åœ±é¿ããã è€æ°ã®ã€ã³ããã¯ã¹ã«ããããã¯ç«¶åã®ããã©ãŒãã³ã¹ãžã®åœ±é¿ ã€ã³ããã¯ã¹æ°ãš LWLock:LockManager ã®åŸ
æ©ã®é¢ä¿ ãããã®ãã¹ããéããŠãã€ã³ããã¯ã¹é¢é£ã®ããã¯ãªãŒããŒãããã«é¢ããéèŠãªæŽå¯ãæããã«ããé«ãåæå®è¡æ§ã®ç°å¢ã«ãããã€ã³ããã¯ã¹ç®¡çã®ããã®å®è·µçãªã¬ã€ãã³ã¹ãæäŸããŸãã ã¹ããŒãæºå 以äžã® SQL ã³ãŒãã䜿çšããŠãAurora PostgreSQL ããŒã¿ããŒã¹ã« items ããŒãã«ã¹ããŒããšããã«é¢é£ããã€ã³ããã¯ã¹ãäœæããŠãã ããã -- Create Schema CREATE SCHEMA IF NOT EXISTS experiment_2; SET search_path TO experiment_2; CREATE TABLE items ( item_id SERIAL PRIMARY KEY, sku TEXT NOT NULL, barcode TEXT, name TEXT NOT NULL, description TEXT,category TEXT, subcategory TEXT, vendor_id INT, vendor_region TEXT, brand TEXT, model TEXT,price NUMERIC(10,2), cost NUMERIC(10,2), discount NUMERIC(5,2), margin NUMERIC(5,2), quantity_in_stock INT, reorder_level INT, lead_time_days INT, rating NUMERIC(2,1) ,num_reviews INT, available BOOLEAN DEFAULT true, warehouse_location TEXT, is_active BOOLEAN DEFAULT true,last_restocked TIMESTAMP, created_at TIMESTAMP DEFAULT now(), updated_at TIMESTAMP DEFAULT now() ); CREATE INDEX idx_items_sku ON items(sku); CREATE INDEX idx_items_barcode ON items(barcode); CREATE INDEX idx_items_name ON items(name); CREATE INDEX idx_items_category ON items(category); CREATE INDEX idx_items_vendor_id ON items(vendor_id); CREATE INDEX idx_items_brand ON items(brand); CREATE INDEX idx_items_model ON items(model); CREATE INDEX idx_items_price ON items(price); CREATE INDEX idx_items_cost ON items(cost); CREATE INDEX idx_items_quantity ON items(quantity_in_stock); CREATE INDEX idx_items_rating ON items(rating); CREATE INDEX idx_items_num_reviews ON items(num_reviews); CREATE INDEX idx_items_created_at ON items(created_at); CREATE INDEX idx_items_updated_at ON items(updated_at); CREATE INDEX idx_items_vendor_category ON items(vendor_id, category); CREATE INDEX idx_items_sku_available ON items(sku, available); CREATE INDEX idx_items_barcode_active ON items(barcode, is_active); CREATE INDEX idx_items_category_subcategory_price ON items(category, subcategory, price); CREATE INDEX idx_items_vendor_region_brand ON items(vendor_region, brand); CREATE INDEX idx_items_brand_model ON items(brand, model); äžèšã® SQL ã³ãŒãã¯ãååã®è©³çް ( SKU ã price ã inventory ãªã©) çšã® 26 ã«ã©ã ãæã€ E ã³ããŒã¹ã® items ããŒãã«ãšãããæ€çŽ¢ãããã«ã©ã ãã«ã©ã ã®çµã¿åããã«å¯Ÿãã 20 åã® B-tree ã€ã³ããã¯ã¹ãäœæããŸãã ãã¹ã 1 : è€æ°ã®ã€ã³ããã¯ã¹ãæã€éããŒãã£ã·ã§ã³ããŒãã«ãã¯ãšãªãã éå°ãªã€ã³ããã¯ã¹ã PostgreSQL ã®ããã¯åäœã«ã©ã®ããã«åœ±é¿ãããã®èª¿æ»ãå§ããããã«ãæåã®ãã¹ããšã㊠items ããŒãã«ã«å¯ŸããŠã¯ãšãªãå®è¡ããŸããããåçŽãªååæ€çŽ¢ã¯ãšãªãå®è¡ããŠãã»ãšãã©ã®ã€ã³ããã¯ã¹ããã®ã¯ãšãªã«ã¯äžèŠã§ããã«ãããããããPostgreSQL ã 20 åãã¹ãŠã®ã€ã³ããã¯ã¹ã«ããã£ãŠããã¯ç®¡çãã©ã®ããã«åŠçãããã芳å¯ããŸããããã«ãããéå°ãªã€ã³ããã¯ã¹ãç¶æããããšã§çããåºæ¬çãªããã¯ãªãŒããŒããããçè§£ããã®ã«åœ¹ç«ã¡ãŸãããã©ã³ã¶ã¯ã·ã§ã³ãéå§ããã€ã³ããã¯ã¹ã¢ã¯ã»ã¹ãã¹ã䜿çšã㊠items ããŒãã«ã®ç¹å®ã®ã«ã©ã ã«ã¯ãšãªãå®è¡ããŸãã postgres=> set search_path to experiment_2; SET postgres=> EXPLAIN SELECT name FROM items WHERE name = 'test'; QUERY PLAN ---------------------------------------------------------------- Index Only Scan using idx_items_name on items (cost=0.14..8.16 rows=1 width=32) Index Cond: (name = 'test'::text) (2 rows) postgres=> BEGIN; BEGIN postgres=> SELECT name FROM items WHERE name = 'test'; name ------ (0 rows) åè¿°ã® SQL ã¯ãšãªã«å¯ŸããŠãã¯ãšãªãã©ã³ããŒã¯ã€ã³ããã¯ã¹ã®ã¿ã®ã¹ãã£ã³ãã¹ãéžæããŸããããã® SQL ã§ã¯ items ããŒãã«ãã name ã«ã©ã ã®ã¿ãéžæããŠããŸããå¥ã®ã»ãã·ã§ã³ã§ãããã¯åäœã芳å¯ããŸãã 以äžã®åºåã§ã items ããŒãã«ãäž»ããŒãããã³ã¯ãšãªå®è¡ã®ããã«ãã©ã³ããŒã䜿çšããã€ã³ããã¯ã¹ ( idx_items_name ) ãšã¯å¥ã« AccessShareLock ãç²åŸããã€ã³ããã¯ã¹ã«æ³šç®ããŠäžãããè¿ãããè¡ã®ç·æ°ã¯ 22 ã§ãé«éãã¹ã¹ãããã¯äœ¿ãæãããã6 ã€ã®ããã¯ãå
±æã¡ã¢ãªããã¯ããŒãã«ã«ç§»åããŸããããã®ããŒãã«ã«ããã«ã€ã³ããã¯ã¹ããã£ãå Žåããããã®ã€ã³ããã¯ã¹ã AccessShareLock ãå¿
èŠãšããé«éãã¹ã¹ãããã䜿ãæããããŠããããå
±æã¡ã¢ãªããã¯ããŒãã«ã«é
眮ãããã§ãããããã®ããŒãã«ã«å¯ŸããŠé«ã䞊è¡ã¯ãŒã¯ããŒããçºçãããšãå
±æã¡ã¢ãªããã¯ããŒãã«ã§ç«¶åãçºçãããããããã©ãŒãã³ã¹ã¯äœäžããäºãæ³å®ãããŸãã postgres=> SELECT n.nspname AS schema, c.relname AS table, l.locktype, l.mode, l.fastpath FROM pg_locks l JOIN pg_class c ON l.relation = c.oid JOIN pg_namespace n ON c.relnamespace = n.oid AND n.nspname <> 'pg_catalog'; schema | table | locktype | mode | fastpath --------------+--------------------------------------+----------+-----------------+---------- experiment_2 | idx_items_updated_at | relation | AccessShareLock | t experiment_2 | idx_items_created_at | relation | AccessShareLock | t experiment_2 | idx_items_num_reviews | relation | AccessShareLock | t experiment_2 | idx_items_rating | relation | AccessShareLock | t experiment_2 | idx_items_quantity | relation | AccessShareLock | t experiment_2 | idx_items_cost | relation | AccessShareLock | t experiment_2 | idx_items_price | relation | AccessShareLock | t experiment_2 | idx_items_model | relation | AccessShareLock | t experiment_2 | idx_items_brand | relation | AccessShareLock | t experiment_2 | idx_items_vendor_id | relation | AccessShareLock | t experiment_2 | idx_items_category | relation | AccessShareLock | t experiment_2 | idx_items_name | relation | AccessShareLock | t experiment_2 | idx_items_barcode | relation | AccessShareLock | t experiment_2 | idx_items_sku | relation | AccessShareLock | t experiment_2 | items_pkey | relation | AccessShareLock | t experiment_2 | items | relation | AccessShareLock | t experiment_2 | idx_items_vendor_region_brand | relation | AccessShareLock | f experiment_2 | idx_items_brand_model | relation | AccessShareLock | f experiment_2 | idx_items_vendor_category | relation | AccessShareLock | f experiment_2 | idx_items_barcode_active | relation | AccessShareLock | f experiment_2 | idx_items_sku_available | relation | AccessShareLock | f experiment_2 | idx_items_category_subcategory_price | relation | AccessShareLock | f (22 rows) ãã¹ã 2 : é«ãåæå®è¡æ§ã®ããšã§ãè€æ°ã®ã€ã³ããã¯ã¹ãæã€éããŒãã£ã·ã§ã³ããŒãã«ãã¯ãšãªãã ãããã®äžèŠãªã€ã³ããã¯ã¹ãããã©ãŒãã³ã¹ã«ã©ã®ããã«åœ±é¿ããããçè§£ããããã«ãé«ãäžŠè¡æ§ã®äžã§ããŒãã£ã·ã§ã³åãããŠããªã items ããŒãã«ã®ã¹ãã¬ã¹ãã¹ããè¡ããŸããããããŒãã£ã·ã§ã³åãããããŒãã« (å®éš 1) ã§ã®å®éšãšåæ§ã«ãpgbench ã䜿çšããŠè€æ°ã®åæãŠãŒã¶ãŒãåæã«ããŒãã«ã«ã¢ã¯ã»ã¹ããããšãã·ãã¥ã¬ãŒãããŸãã æåã®å®éšãšåã pgbench ã³ãã³ãã䜿çšããŠãã»ãã·ã§ã³ 1 ã®ã¿ãŒããã«ãã以äžãå®è¡ããŸãããã®ãã¹ã㯠5 åéå®è¡ãããŸãããã¹ãå®è¡äžãCloudWatch Database Insights ã®ããŒã¿ããŒã¹ã®åŸ
æ©ã€ãã³ããç£èŠããŸãã pgbench -c 100 -j 10 -n -f transaction.sql -T 300 transaction.sql ãã¡ã€ã«ã«ã¯ãååã®ãã¹ããšåã items ããŒãã«ã«å¯Ÿããååæ€çŽ¢ SQL ã¯ãšãªãå«ãŸããŠããŸãã CloudWatch Database Insights ã®ä»¥äžã®ã¹ã¯ãªãŒã³ã·ã§ããã¯ã items ããŒãã«ã®éå°ãªã€ã³ããã¯ã¹ã«ãã£ãŠã LWLock:LockManager ã®åŸ
æ©æéãå¢å ããããŒã¿ããŒã¹è² è·ãš CPU 䜿çšçãå¢å ããæ§åã瀺ããŠããŸãã ããã§èгå¯ããã LWLock:LockManager åŸ
ã¡ã¯ã䞻㫠items ããŒãã«ã®éå°ãªã€ã³ããã¯ã¹ã«ãã£ãŠåŒãèµ·ããããŠããŸããããŒã¿ãç¡ãå Žåã§ããPostgreSQL ã¯ã¯ãšãªã®èšç»ãšå®è¡äžã« 20 åãã¹ãŠã®ã€ã³ããã¯ã¹ã調ã¹ãé¢é£ããããã¯ãååŸããã«ã¿ãã°ã¡ã¿ããŒã¿ã«ã¢ã¯ã»ã¹ããå¿
èŠãããããããªãŒããŒããããçºçããŸããé«ãäžŠè¡æ§ã®ããã倿°ã®ããã¯ãé¢äžããããŒã¿ããŒã¹ã»ãã·ã§ã³ã¯é«éãã¹ããã¯ã䜿ãæãããããã¯ãšã³ãããã»ã¹ãã¡ã€ã³ããã¯ãããŒãžã£ãŒã«é ŒããããåŸãªããªãã远å ã®ç«¶åãçºçããŸãããããã«ãããç¹°ãè¿ãããã«ã¿ãã°ã¹ãã£ã³ã«ãã CPU 䜿çšçã®å¢å ãšãããã¯ååŸã®ãªãŒããŒãããã«ããããŒã¿ããŒã¹è² è·ã®äžæãçããŸãããäžèŠãªã€ã³ããã¯ã¹ã®æ°ãæžããããšã¯ãã¯ãšãªãã©ã³ãã³ã°ã®è€éããæžå°ãããã ãã§ãªããé«éãã¹ãããã³ã°ãç¶æããã®ã«åœ¹ç«ã¡ãé«ãäžŠè¡æ§ã®ã¯ãŒã¯ããŒãã®äžã§ã·ã¹ãã å¹çãåäžãããã§ãããã å®éš 3 : è€æ°çµåã¯ãšãªã§ã®ããã¯åäœã芳å¯ãã ãã®å®éšã§ã¯ãPostgreSQL ãè€æ°ã®é¢é£ããŒãã«ã«ãŸãããè€éãªã¯ãšãªãå®è¡ããéã«ã©ã®ããã«ããã¯ã管çãããã調æ»ããŸããå®éç㪠E ã³ããŒã¹ããŒã¿ããŒã¹ã¹ããŒãã䜿çšããŠã2 ã€ã®éèŠãªãã¹ãã·ããªãªãæ¢ããŸãã ãŸããåäžã®ãã«ãçµåã¯ãšãªãæ€èšŒããŸã ãŠãŒã¶ãŒã®ã«ãŒãå
容ãã¢ã€ãã äŸ¡æ Œãæ³šæç¶æ³ãããã³æ¯æã詳现ãåäžã®èªã¿åãå°çšæäœã§ååŸããŸã 6 ã€ã®çžäºé¢é£ããŒãã« ( users ã carts ã cart_items ã items ã orders ã payments ) ãæ¥ç¶ããŸã PostgreSQL ãè€æ°ã®ããŒãã«ãšãã®ã€ã³ããã¯ã¹ã«ãŸãããããã¯ãã©ã®ããã«åŠçããããå®èšŒããŸã ãã®ã¯ãšãªã¯ãè€æ°ã®ããŒãã«çµåã环ç©çãªããã¯ã®ãããããªã³ãã«ã©ã®ããã«åœ±é¿ãããã芳å¯ããæ©äŒãæäŸããŸããåããŒãã«ã«ã¯ç¬èªã®äž»ããŒãšå€éšããŒã®ã€ã³ããã¯ã¹ããããããPostgreSQL ã¯ãããã®åçŽãªèªã¿åãã«é«éãã¹ããã¯ã䜿çšã§ããå¯èœæ§ããããå
±æã¡ã¢ãªããã¯ããŒãã«ã«ãšã³ããªãååŸãããªãŒããŒããããåé¿ã§ããŸãã æ¬¡ã«ãé«ãäžŠè¡æ§ã®äžã§ã·ã¹ãã ã®ã¹ãã¬ã¹ãã¹ããè¡ããŸã è€éãªçµåãå®è¡ããè€æ°ã®ã»ãã·ã§ã³ãããã¯ç®¡çã«ã©ã®ããã«åœ±é¿ãããã芳å¯ããŸã è€æ°ã®ã€ã³ããã¯ã¹ä»ãããŒãã«ã«ãŸãããããã¯ååŸã®ããã©ãŒãã³ã¹ãžã®åœ±é¿ã枬å®ããŸã çµåã®è€éãã CPU 䜿çšçãšããã¯ç«¶åã®äž¡æ¹ã«ã©ã®ããã«åœ±é¿ããããå®èšŒããŸã ãããã®ãã¹ããéããŠã以äžã«é¢ããéèŠãªæŽå¯ãæããã«ããŸãã è€éãªçžäºæ¥ç¶ãããããŒãã«æ§é ã«ãããããã¯ç®¡ç ããŒãã«çµåãã€ã³ããã¯ã¹ãããã³ããã¯ãªãŒããŒãããã®é¢ä¿ é«ãäžŠè¡æ§ç°å¢ã«ããããã«ãçµåã¯ãšãªã®ããã©ãŒãã³ã¹ã«é¢ããèæ
®äºé
ã¹ããŒãæºå 以äžã® SQL ã³ãŒãã䜿çšããŠã6 ã€ã®çžäºæ¥ç¶ãããããŒãã« ( users ã carts ã cart_items ã items ã orders ãããã³ payments ) ãšãããã«é¢é£ããã€ã³ããã¯ã¹ããã³å€éšããŒå¶çŽãå«ã E ã³ããŒã¹ã¹ããŒããäœæããŠãã ããããã®ã¹ããŒãã¯ãå®éã® E ã³ããŒã¹ããŒã¿ããŒã¹ãã·ãã¥ã¬ãŒãããããã«ãããŒãã«ããšã«è€æ°ã®ã€ã³ããã¯ã¹ãšé©åãªåç
§æŽåæ§å¶çŽãå«ãå
æ¬çãªèšèšã«ãªã£ãŠããŸãã -- Create Schema CREATE SCHEMA IF NOT EXISTS experiment_3; SET search_path TO experiment_3; -- USERS table CREATE TABLE users ( user_id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE NOT NULL, phone TEXT, role TEXT DEFAULT 'customer', is_active BOOLEAN DEFAULT TRUE, is_deleted BOOLEAN DEFAULT FALSE, created_by INT, updated_by INT, created_at TIMESTAMP DEFAULT now(), updated_at TIMESTAMP DEFAULT now(), deleted_at TIMESTAMP ); -- Indexes for USERS CREATE INDEX idx_users_name_created_at ON users(name, created_at); CREATE INDEX idx_users_email_active ON users(email, is_active); CREATE INDEX idx_users_role_active ON users(role, is_active); CREATE INDEX idx_users_created_at ON users(created_at); -- ITEMS table CREATE TABLE items ( item_id SERIAL PRIMARY KEY, name TEXT NOT NULL, description TEXT, sku TEXT UNIQUE, category TEXT, price NUMERIC(10,2) NOT NULL, discount NUMERIC(5,2) DEFAULT 0.00, stock_quantity INT NOT NULL, restock_threshold INT DEFAULT 10, is_active BOOLEAN DEFAULT TRUE, is_deleted BOOLEAN DEFAULT FALSE, created_by INT, updated_by INT, created_at TIMESTAMP DEFAULT now(), updated_at TIMESTAMP DEFAULT now(), deleted_at TIMESTAMP ); -- Indexes for ITEMS CREATE INDEX idx_items_price_stock ON items(price, stock_quantity); CREATE INDEX idx_items_category_active ON items(category, is_active); CREATE INDEX idx_items_name_price ON items(name, price); CREATE INDEX idx_items_created_at ON items(created_at); -- CARTS table CREATE TABLE carts ( cart_id SERIAL PRIMARY KEY, user_id INT REFERENCES users(user_id) ON DELETE CASCADE, status TEXT DEFAULT 'open', is_deleted BOOLEAN DEFAULT FALSE, expires_at TIMESTAMP, created_by INT, updated_by INT, created_at TIMESTAMP DEFAULT now(), updated_at TIMESTAMP DEFAULT now(), deleted_at TIMESTAMP ); -- Indexes for CARTS CREATE INDEX idx_carts_user_created ON carts(user_id, created_at); CREATE INDEX idx_carts_user_status ON carts(user_id, status); CREATE INDEX idx_carts_status_expires ON carts(status, expires_at); CREATE INDEX idx_carts_created_at ON carts(created_at); -- CART_ITEMS table CREATE TABLE cart_items ( cart_item_id SERIAL PRIMARY KEY, cart_id INT REFERENCES carts(cart_id) ON DELETE CASCADE, item_id INT REFERENCES items(item_id) ON DELETE RESTRICT, quantity INT NOT NULL CHECK (quantity > 0), price_at_addition NUMERIC(10,2), is_deleted BOOLEAN DEFAULT FALSE, added_at TIMESTAMP DEFAULT now() ); -- Indexes for CART_ITEMS CREATE INDEX idx_cart_items_cart_item ON cart_items(cart_id, item_id); CREATE INDEX idx_cart_items_item_deleted ON cart_items(item_id, is_deleted); CREATE INDEX idx_cart_items_cart_added ON cart_items(cart_id, added_at); CREATE INDEX idx_cart_items_added_at ON cart_items(added_at); -- ORDERS table CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, user_id INT REFERENCES users(user_id), cart_id INT REFERENCES carts(cart_id), status TEXT NOT NULL CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled', 'refunded')), shipping_address TEXT, billing_address TEXT, tracking_number TEXT, payment_due_date TIMESTAMP, is_deleted BOOLEAN DEFAULT FALSE, total NUMERIC(10,2), created_by INT, updated_by INT, created_at TIMESTAMP DEFAULT now(), updated_at TIMESTAMP DEFAULT now(), deleted_at TIMESTAMP ); -- Indexes for ORDERS CREATE INDEX idx_orders_status_created ON orders(status, created_at); CREATE INDEX idx_orders_user_status ON orders(user_id, status); CREATE INDEX idx_orders_user_created ON orders(user_id, created_at); CREATE INDEX idx_orders_tracking ON orders(tracking_number); -- PAYMENTS table CREATE TABLE payments ( payment_id SERIAL PRIMARY KEY, order_id INT REFERENCES orders(order_id) ON DELETE CASCADE, payment_method TEXT NOT NULL, payment_status TEXT DEFAULT 'initiated', transaction_id TEXT UNIQUE, currency TEXT DEFAULT 'USD', amount NUMERIC(10,2) NOT NULL, is_refundable BOOLEAN DEFAULT FALSE, paid_at TIMESTAMP DEFAULT now(), refunded_at TIMESTAMP ); -- Indexes for PAYMENTS CREATE INDEX idx_payments_method_paid_at ON payments(payment_method, paid_at); CREATE INDEX idx_payments_method_status ON payments(payment_method, payment_status); CREATE INDEX idx_payments_order_status ON payments(order_id, payment_status); CREATE INDEX idx_payments_currency_paid ON payments(currency, paid_at); ãã¹ã 1 : è€æ°ã®ã€ã³ããã¯ã¹ããŒãã«ã«ããããã«ãçµåã¯ãšãªãå®è¡ãã PostgreSQL ãè€éãªçµåæäœäžã«ããã¯ãã©ã®ããã«åŠçãããã®èª¿æ»ãéå§ããããã«ããŠãŒã¶ãŒã®ã·ã§ããã³ã°ã«ãŒãæ
å ±ãååŸãããã«ãçµåã¯ãšãªã䜿çšããŠæåã®ãã¹ããå®è¡ããŸãããããã®ã¯ãšãªã¯ãPostgreSQL ãè€æ°ã®ããŒãã«ãšãããã«é¢é£ããã€ã³ããã¯ã¹ã«ããã£ãŠããã¯ãã©ã®ããã«ç®¡çãããã瀺ããŸããã»ãã·ã§ã³ 1 ã§ããã©ã³ã¶ã¯ã·ã§ã³ãéå§ããŠæ¬¡ã®ã¯ãšãªãå®è¡ããŸãã postgres=> SET search_path TO experiment_3; BEGIN; SELECT u.user_id, u.name AS user_name, c.cart_id, i.name AS item_name, ci.quantity, i.price, (i.price * ci.quantity) AS line_total, o.order_id, o.status, p.payment_method, p.amount FROM users u JOIN carts c ON c.user_id = u.user_id JOIN cart_items ci ON ci.cart_id = c.cart_id JOIN items i ON i.item_id = ci.item_id LEFT JOIN orders o ON o.cart_id = c.cart_id LEFT JOIN payments p ON p.order_id = o.order_id WHERE u.user_id = (1 + floor(random() * 10))::int; SET BEGIN user_id | user_name | cart_id | item_name | quantity | price | line_total | order_id | status | payment_method | amount ---------+-----------+---------+-----------+----------+-------+------------+----------+--------+----------------+-------- (0 rows) ã»ãã·ã§ã³ 1 ã§äžèšã®ãã©ã³ã¶ã¯ã·ã§ã³ãéãããŸãŸã«ããŠãã»ãã·ã§ã³ 2 ã§ä»¥äžã®ã¯ãšãªãå®è¡ããPostgreSQL ããã«ãçµåã¯ãšãªã«é¢ãããã¹ãŠã®ããŒãã«ãšã€ã³ããã¯ã¹ã«ããã£ãŠããã¯ãã©ã®ããã«ç®¡çããŠãããã調ã¹ãŠãã ããã postgres=> SELECT n.nspname AS schema, c.relname AS table, l.locktype, l.mode, l.fastpath FROM pg_locks l JOIN pg_class c ON l.relation = c.oid JOIN pg_namespace n ON c.relnamespace = n.oid AND n.nspname <> 'pg_catalog'; schema | table | locktype | mode | fastpath ---------------+------------------------------+----------+----------------+---------- experiment_3 | idx_carts_status_expires | relation | AccessShareLock | t experiment_3 | idx_carts_user_status | relation | AccessShareLock | t experiment_3 | idx_carts_user_created | relation | AccessShareLock | t experiment_3 | carts_pkey | relation | AccessShareLock | t experiment_3 | idx_users_created_at | relation | AccessShareLock | t experiment_3 | idx_users_role_active | relation | AccessShareLock | t experiment_3 | idx_users_email_active | relation | AccessShareLock | t experiment_3 | idx_users_name_created_at | relation | AccessShareLock | t experiment_3 | users_email_key | relation | AccessShareLock | t experiment_3 | users_pkey | relation | AccessShareLock | t experiment_3 | payments | relation | AccessShareLock | t experiment_3 | orders | relation | AccessShareLock | t experiment_3 | items | relation | AccessShareLock | t experiment_3 | cart_items | relation | AccessShareLock | t experiment_3 | carts | relation | AccessShareLock | t experiment_3 | users | relation | AccessShareLock | t experiment_3 | idx_cart_items_item_deleted | relation | AccessShareLock | f experiment_3 | idx_orders_user_status | relation | AccessShareLock | f experiment_3 | idx_cart_items_cart_item | relation | AccessShareLock | f experiment_3 | idx_payments_order_status | relation | AccessShareLock | f experiment_3 | idx_carts_created_at | relation | AccessShareLock | f experiment_3 | idx_cart_items_cart_added | relation | AccessShareLock | f experiment_3 | idx_items_created_at | relation | AccessShareLock | f experiment_3 | orders_pkey | relation | AccessShareLock | f experiment_3 | payments_pkey | relation | AccessShareLock | f experiment_3 | idx_items_name_price | relation | AccessShareLock | f experiment_3 | idx_payments_method_status | relation | AccessShareLock | f experiment_3 | payments_transaction_id_key | relation | AccessShareLock | f experiment_3 | idx_items_category_active | relation | AccessShareLock | f experiment_3 | items_pkey | relation | AccessShareLock | f experiment_3 | idx_orders_status_created | relation | AccessShareLock | f experiment_3 | idx_items_price_stock | relation | AccessShareLock | f experiment_3 | idx_cart_items_added_at | relation | AccessShareLock | f experiment_3 | idx_orders_tracking | relation | AccessShareLock | f experiment_3 | idx_payments_currency_paid | relation | AccessShareLock | f experiment_3 | cart_items_pkey | relation | AccessShareLock | f experiment_3 | items_sku_key | relation | AccessShareLock | f experiment_3 | idx_orders_user_created | relation | AccessShareLock | f experiment_3 | idx_payments_method_paid_at | relation | AccessShareLock | f (39 rows) äžèšã®åºåã§ã¯ãPostgreSQL ãåèš 39 åã®ããã¯ãååŸããããšãããããŸãã fastpath åãèŠããšã23 è¡ã f (false) ã瀺ããŠããããããã®ããã¯ã¯é«éãã¹ã®ä»£ããã«å
±æã¡ã¢ãªããã¯ããŒãã«ãéãããäœéãã¹ã䜿çšããå¿
èŠããã£ãããšã瀺ããŠããŸããããã¯ãäžèŠåçŽãªãã¹ãçµåã¯ãšãªã§ããé«éãã¹ã¹ãããã䜿ãæãããããšèããããã¯ç«¶åãçºçããå¯èœæ§ãããããšã瀺ããŠããŸãã ãã¹ã 2 : é«ãåæå®è¡æ§ã§ãã«ãçµåã¯ãšãªãå®è¡ãã äžèšã® SQL ã¯ãšãªã«ãããè€éãªçµåãå€§èŠæš¡ç°å¢ã§ã©ã®ããã«å®è¡ãããããçè§£ããããã«ãé«ãäžŠè¡æ§ã®äžã§ E ã³ããŒã¹ã¹ããŒãã®ã¹ãã¬ã¹ãã¹ãã宿œããŸããããååã®å®éšãšåæ§ã«ãpgbench ã䜿çšããŠè€æ°ã®åæãŠãŒã¶ãŒãåæã«ãã«ãçµåã¯ãšãªãå®è¡ããããšãã·ãã¥ã¬ãŒãããŸãã ååã®å®éš (å®éš 1 ãš 2) ãšåã pgbench ã³ãã³ãã䜿çšããŠãã»ãã·ã§ã³ 1 ã®ã¿ãŒããã«ãã以äžãå®è¡ããŸãããã®ãã¹ã㯠5 åéå®è¡ãããŸãã pgbench -c 100 -j 10 -n -f transaction.sql -T 300 transaction.sql ãã¡ã€ã«ã«ã¯ãåã®ãã¹ããšåããã«ãçµå SQL ã¯ãšãªãå«ãŸããŠããŸãããã¹ãå®è¡äžãCloudWatch Database Insights ã§ããŒã¿ããŒã¹åŸ
æ©ã€ãã³ããç£èŠããŸãã CloudWatch Database Insights ã®ä»¥äžã®ã¹ã¯ãªãŒã³ã·ã§ããã¯ã倿°ã®ã€ã³ããã¯ã¹ãä»ããããŒãã«éã®ãã«ãçµåã¯ãšãªããã¢ã¯ãã£ãã»ãã·ã§ã³ã® 20 ããŒã»ã³ããæ¶è²»ãã LWLock:LockManager ç«¶åãšã80 ããŒã»ã³ãã§é£œåã«è¿ã¥ã CPU 䜿çšçãšããäºéã®ããã©ãŒãã³ã¹åœ±é¿ãããããããšã瀺ããŠããŸãã ããŒã¿ããŒã¹è² è·ã°ã©ãã¯ãè€æ°ã®ã€ã³ããã¯ã¹ãæã€ããŒãã«éã§ã®ãã«ãçµåã¯ãšãªã«ããèãã LWLock:LockManager ç«¶å (AAS ã® 20 ããŒã»ã³ã) ãšé«ã CPU 䜿çšç (80 ããŒã»ã³ã) ã瀺ããŠããŸããåçµåã¯ãPostgreSQL ãã€ã³ããã¯ã¹ã«å¯Ÿã㊠AccessShareLock ããã¯ãååŸããããšã匷å¶ããé«éãã¹ããã¯ã䜿ãæãããããé
ãã¡ã€ã³ããã¯ãããŒãžã£ãŒã«é Œãããšã«ãªããŸããã¯ãšãªãã©ã³ãã³ã°äžã®ç¹°ãè¿ãã®ã«ã¿ãã°ã¹ãã£ã³ã«ãããCPU 䜿çšçã¯é£œåã«è¿ã¥ããŠããŸãããã®ããã¯ãããŒãžã£ãŒã®ããã«ããã¯ã¯ãçµåãã©ã³ãã³ã°äžã«è€æ°ã®ã€ã³ããã¯ã¹ä»ãããŒãã«å
šäœã§ããã¯ãç¶æããããšã®è€åçãªãªãŒããŒãããã«èµ·å ããŠããŸããåé·ãªã€ã³ããã¯ã¹ãæžãããçµåãã¿ãŒã³ãç°¡çŽ åããããšã§ãã¯ãŒã¯ããŒãã§èŠãããããã¯ç«¶åãš CPU å§åã®äž¡æ¹ã軜æžãããã§ãããã PostgreSQL ã®ããã¯ç«¶åã管çããããã®äž»ãªç·©åæŠç¥ PostgreSQL ã®ããã¯ç«¶åã軜æžããããã®ä»¥äžã®äž»èŠãªå¯Ÿçãæ€èšããŠãã ããã ããŒãã£ã·ã§ã³åãããããŒãã«ãžã®æé©åãããã¢ã¯ã»ã¹ ããŒãã£ã·ã§ã³ãã«ãŒãã³ã°ãæå¹ã«ãã â ãã«ããŒãã«ã¹ãã£ã³ã®ä»£ããã«æç€ºçãªæ¥ä»ç¯å² ( WHERE order_ts BETWEEN X AND Y ) ã䜿çšããŸãã PostgreSQL ã®ããã¥ã¡ã³ã ããããŒãã£ã·ã§ã³ãã«ãŒãã³ã°ã«ã€ããŠè©³çްãåŠãã§ãã ããã 宿°ã®ãªãåç SQL ãé¿ãã â ãã«ãŒãã³ã°ã匷å¶ãããããæ¬æçš¿ã®æåã®å®éšã®ããã« CTE ã PL/pgSQL ãããã¯ã«çœ®ãæããŸãã ããŒãã£ã·ã§ã³æ°ãå¶éãã â å¯èœãªå Žåã¯ããŒãã£ã·ã§ã³ãæžãã (äŸãã°ãææ¬¡ããŒãã£ã·ã§ã³ã®ä»£ããã«ååæããŒãã£ã·ã§ã³ãæ€èš)ãé«éãã¹ã®å¶éå
ã«åããŸãã ã€ã³ããã¯ã¹ã®åçå æªäœ¿çšã®ã€ã³ããã¯ã¹ãç£æ»ãåé€ãã â pg_stat_user_indexes ã䜿çšããŠäœ¿çšé »åºŠã®äœãã€ã³ããã¯ã¹ãç¹å®ããŸãã åé·ãªã€ã³ããã¯ã¹ãçµ±åãã â åäžã«ã©ã ã®ã€ã³ããã¯ã¹ãè€åã€ã³ããã¯ã¹ã«çœ®ãæããŸã (äŸãã°ã3 ã€ã®åå¥ã€ã³ããã¯ã¹ã®ä»£ããã«( category , subcategory , price ))ã éå°ãªã€ã³ããã¯ã¹ä»ããé¿ãã â OLTP ã«ãšã£ãŠéèŠã§ãªãéããããŒãã«ãããã®ã€ã³ããã¯ã¹æ°ãå¶éããŸã (äŸãã°ã10 以äž)ã ã¹ããŒãèšèšã®èª¿æŽ äžå¿
èŠãªããŒãã£ã·ã§ã³åãé¿ãã â 100GB ãè¶
ããããŒãã«ãŸãã¯æç¢ºãªã¢ã¯ã»ã¹ãã¿ãŒã³ãããããŒãã«ã®ã¿ãããŒãã£ã·ã§ã³åããŸããããŒãã«ããŒãã£ã·ã§ã³åããã€ã©ã®ããã«å®è£
ãããã«ã€ããŠã®è©³çްãªã¬ã€ãã³ã¹ã¯ãã Improve performance and manageability of large PostgreSQL tables by migrating to partitioned tables on Amazon Aurora and Amazon RDS ããåç
§ããŠãã ããã ã«ããªã³ã°ã€ã³ããã¯ã¹ã䜿çšãã â ããŒãã«ã¢ã¯ã»ã¹ãé¿ããããã« INCLUDE åã远å ããŸã (ãªã¬ãŒã·ã§ã³ããã¯ãæžãããŸã)ãã«ããªã³ã°ã€ã³ããã¯ã¹ãšã€ã³ããã¯ã¹ã®ã¿ã®ã¹ãã£ã³ã®å®è£
ã«ã€ããŠã®è©³çްã¯ã PostgreSQL ã®ããã¥ã¡ã³ã ãåç
§ããŠãã ããã é«äžŠè¡æ§ããŒãã«ãæ£èŠåãã â å¹
åºãããŒãã« (äŸãã°ã items ) ãåå²ããŠã€ã³ããã¯ã¹ã®æ¡æ£ãæžãããŸãã ã¯ãšãªã®æé©å çµåãç°¡çŽ åãã â ãã«ãçµåã¯ãšãªããããªã¢ã©ã€ãºããã¥ãŒãŸãã¯ã¹ããŒãžãã¯ãšãªã«åå²ããŸãããããªã¢ã©ã€ãºããã¥ãŒã®å®è£
ã«ã€ããŠã¯ã PostgreSQL ã®ããã¥ã¡ã³ã ãåç
§ããŠãã ããã å°ããªèªã¿åãããããåŠçã«ãã â å°ããªæ€çŽ¢ (äŸãã°ã IN (...) å¥) ãçµã¿åãããŠããã¯é »åºŠãæžãããŸãã PostgreSQL ã®ãã¥ãŒãã³ã° max_locks_per_transaction ã調æŽãã â ããŒãã£ã·ã§ã³åãé¿ããããªãå Žå (ã¡ã¢ãªãç£èŠ)ãããã³äœåãªããã¯ãå
±æã¡ã¢ãªããã¯ããã·ã¥ããŒãã«ã«ç§»åãããå Žåã¯å¢ãããŸã (äŸãã°ã256 ãã 512 ãž)ã é«éãã¹ã®äœ¿çšç¶æ³ãç£èŠãã â pg_locks ã远跡ããŠã¹ãããã®äœ¿ãæãããç¹å®ããŸãã ã¯ãªãŒã³ã¢ãã æ¬æçš¿ã®ãœãªã¥ãŒã·ã§ã³ãå®è£
ããããšã«é¢é£ããå°æ¥ã®æéãçºçããªãããã«ããã«ã¯ãäœæãããªãœãŒã¹ãåé€ããŠãã ããã å®éšã§äœæãããã¹ãã¹ããŒããšããŒãã«ãåé€ããŸã ãã¹ãçšã«å°çšã® Aurora PostgreSQL ã¯ã©ã¹ã¿ãŒãäœæããå Žåã¯ããããåé€ããŸã äžèŠã«ãªã£ãé¢é£ã¹ãããã·ã§ãããåé€ããŸã çµè« PostgreSQL ã®èªã¿åãè² è·ã®å€ãã¯ãŒã¯ããŒãã«ããã LWLock ã®ç«¶åã¯ãé«éãã¹ãããã³ã°ã®å¶éãè¶
ããããšããçããŸããããã¯ãããŒãã£ã·ã§ã³ãã«ãŒãã³ã°ã®æªå®è£
ãåé·ãªã€ã³ããã¯ã¹ãããã³è€éãªçµåã«ãã£ãŠåŒãèµ·ããããŸããæ¬æçš¿ã®å®éšã§ã¯ã以äžã®ããšãå®èšŒãããŸããã ããŒãã£ã·ã§ã³ãã«ãŒãã³ã°ã¯ããã¯ã®ãªãŒããŒããããåæžããããã¯ãé«éãã¹ã¹ãããã«éå®ããããšã§ã34 ããŒã»ã³ãã®ããã©ãŒãã³ã¹åäž (46,000 tps ãã 59,000 tps ãž) ããããããŸãã 䜿çšãããŠããªãåã€ã³ããã¯ã¹ã¯ããã¯è² è·ãå¢å ããã空ã®ããŒãã«ã§ãäœéãã¹ãžã®ãã©ãŒã«ããã¯ã匷å¶ããŸã ãã«ãçµåã¯ãšãªã¯ç«¶åãå¢å¹
ãããã¹ããããã·ããªãªã§ã¯ 60 ããŒã»ã³ãã®ããã¯ãäœéãã¹ã«æº¢ããŸãã ããŒãã£ã·ã§ã³ãæèããã¯ãšãªã®åªå
ã峿 Œãªã€ã³ããã¯ã¹ã®ç®¡çãããã³çµåã®ç°¡çŽ åã«ãã£ãŠãé«éãã¹ã®å¹çãç¶æããAmazon Aurora PostgreSQL ãšAmazon RDS for PostgreSQL ã§ç·åœ¢ãªèªã¿åãã¹ã±ãŒã©ããªãã£ãæäŸã§ããŸããããŒã¿ããŒã¹ãæé·ããã«ã€ãããããã®æé©åã¯ããã¯ã®ããã«ããã¯ãªãã§ AWS ã®åŒŸåæ§ã掻çšããããã®åºç€ãšãªããŸãã Aurora ã§ã®ããã©ãŒãã³ã¹ãã¥ãŒãã³ã°ãšã¹ã±ãŒã©ãã«ãª PostgreSQL ã¯ãŒã¯ããŒãã®èšèšã«ã€ããŠã®è©³çްã¯ãã Aurora PostgreSQL ãã¥ãŒãã³ã°ã®åºæ¬æŠå¿µ ããåç
§ããŠãã ããã 翻蚳ã¯ãã¯ãã«ã«ã¢ã«ãŠã³ããããŒãžã£ãŒã®è¥¿åãæ
åœããŸãããåæã¯ ãã¡ã ãã芧äžããã