æ¬èšäºã¯ 2026 幎 1 æ 20 æ¥ ã«å
¬éãããã Using the shared plan cache for Amazon Aurora PostgreSQL ãã翻蚳ãããã®ã§ãã æ¬èšäºã§ã¯ã Amazon Aurora PostgreSQL äºæãšãã£ã·ã§ã³ ã®å
±æãã©ã³ãã£ãã·ã¥æ©èœã«ãããé«ãåæå®è¡æ§ç°å¢ã§æ±çš SQL ãã©ã³ã®ã¡ã¢ãªæ¶è²»ã倧å¹
ã«åæžã§ããããšã説æããŸãã40GB ã®ã¡ã¢ãªè² è·ã 400MB ãŸã§åæžã§ããŸãã Aurora PostgreSQL ããŒã¿ããŒã¹ã¯ã©ã¹ã¿ãŒãæ°åã®åææ¥ç¶ãåŠçãããããããåã ããªãã¢ãã¹ããŒãã¡ã³ã ãå®è¡ããŠããç¶æ³ãæ³åããŠãã ãããã¯ãšãªèªäœã¯ã·ã³ãã«ãªã®ã«ãã¡ã¢ãªäœ¿çšéãæ°å GB ãŸã§å¢å ããŠããŸããäœãèµ·ããŠããã®ã§ãããããããã¯ãã©ã³ã®éè€ã«ããé ããã³ã¹ããçºçããŠããå¯èœæ§ããããå
±æãã©ã³ãã£ãã·ã¥ã§è§£æ±ºã§ããŸãã PostgreSQL ã®æ±çšãã©ã³ãçè§£ãã ããªãã¢ãã¹ããŒãã¡ã³ãã¯ãã¢ããªã±ãŒã·ã§ã³ã§ (ããŒã¿ããŒã¹ãšããåããã颿°ãã¡ãœãããå®çŸ©ããéã«) äžè¬çã«äœ¿ãããŸã ããããã®ã¹ããŒãã¡ã³ãã¯ãããŒã¿ããŒã¹ã¢ã¯ã»ã¹ã³ãŒããã¡ãœããã«å«ãŸããŸããæºåãã§ãŒãºã«ã¯ SQL ã¹ããŒãã¡ã³ãã®æ§é ãšãã¬ãŒã¹ãã«ããŒãå«ãŸããã¢ããªã±ãŒã·ã§ã³ãããªãã¢ãã¹ããŒãã¡ã³ããå®è¡ããéã«å®éã®å€ãå
¥ããŸããæºåãã§ãŒãºã§ã¹ããŒãã¡ã³ããè§£æãåæãæžãæãããããããå®è¡æã®è§£æãšåæäœæ¥ã®ç¹°ãè¿ããçããŸãã ãœãªã¥ãŒã·ã§ã³ã«å
¥ãåã«ãPostgreSQL ãããªãã¢ãã¹ããŒãã¡ã³ããã©ãæ±ãããçè§£ããŸããããPostgreSQL ãš Aurora PostgreSQL ã§ã¯ãããªãã¢ãã¹ããŒãã¡ã³ã㯠2 çš®é¡ã®ãã©ã³ã§å®è¡ã§ããŸãã ã«ã¹ã¿ã ãã©ã³: å®è¡ããšã«ç¹å®ã®ãã©ã¡ãŒã¿å€ã§æ°ããäœæããããªãã©ã«ãå«ãŸããŸãã æ±çšãã©ã³: ãã©ã¡ãŒã¿ã«äŸåããªããã©ã³ã§ãå®è¡éã§åå©çšããããªãã©ã«ã¯å«ãŸããŸããã ããã©ã«ãã§ã¯ãPostgreSQL ã¯ããã 2 ã€ã®ãã©ã³ã¿ã€ãã®éžæã«ã€ã³ããªãžã§ã³ããªã¢ãããŒããçšããŸãã ããªãã¢ãã¹ããŒãã¡ã³ãã®æåã® 5 åã®å®è¡ã§ã¯ã«ã¹ã¿ã ãã©ã³ãäœ¿çš ãããã®ã«ã¹ã¿ã ãã©ã³ã®å¹³åã³ã¹ããèšç® 6 åç®ã®å®è¡ã§æ±çšãã©ã³ãäœæ æ±çšãã©ã³ã®ã³ã¹ããã«ã¹ã¿ã ãã©ã³ã®å¹³åã³ã¹ããšåçããã以äžãªãã以éã®å®è¡ã§äœ¿çš ãã®ã¢ãããŒãã¯é »ç¹ã«å®è¡ãããã¯ãšãªã®ãã©ã³äœææéãç¯çŽã§ããŸããã倿°ã®åæããŒã¿ããŒã¹æ¥ç¶ãããç°å¢ã§ã¯é ããã³ã¹ããçºçããŸãã åé¡: å€§èŠæš¡ç°å¢ã§ã®ã¡ã¢ãªéå¹ç ãã®ã¢ãããŒãã¯åã
ã®æ¥ç¶ã§ã¯ããŸãæ©èœããŸããã倿°ã®åæããŒã¿ããŒã¹æ¥ç¶ãããç°å¢ã§ã¯ 2 ã€ã®å€§ããªéå¹çãçããŸãã äžèŠãªãã©ã³çæ: æ±çšãã©ã³ã䜿ãããªãå Žå (ã«ã¹ã¿ã ãã©ã³ã®æ¹ãå¹ççãªãã) ã§ããã³ã¹ãæ¯èŒã®ããã«ã·ã¹ãã ã¯æ±çšãã©ã³ãäœæããŠã¡ã¢ãªã«ä¿åããŸããããšãã°ãããŒãã£ã·ã§ã³ããŒãã«ã§ã¯ãã³ã¹ãããªãŒãããŒãã£ã·ã§ã³ããšã«èšç®ãããŠåèšããããããæ±çšãã©ã³ã䜿ãããªãå¯èœæ§ãé«ããªããŸãã ãã©ã³ã®éè€: åãã¯ãšãªãæ°çŸãŸãã¯æ°åã®ã»ãã·ã§ã³ã§å®è¡ãããå Žåãåã»ãã·ã§ã³ãåäžã®æ±çšãã©ã³ã®ã³ããŒãä¿æãã倧éã®ã¡ã¢ãªéè€ãçºçããŸãã ãã®åé¡ãå
·äœäŸã§ç€ºããŠã¿ãŸãããã ãã¹ãç°å¢ã®ã»ããã¢ãã ãã®äŸã§ã¯ãæ°ããã»ãã·ã§ã³ã§å 1000 ããŒãã£ã·ã§ã³ãæã€ããŒãã« t1 ãš t2 ãäœæããŸããæ¬¡ã«ãåã«ãŒãã§ 1000 åã®å€ãæ¿å
¥ããåŠçã 100 åã«ãŒãããåããŒãã«ã« 100,000 è¡ãæ¿å
¥ããŸããæåŸã«äž¡æ¹ã®ããŒãã«ã®çµ±èšæ
å ±ãæŽæ°ããŸãã æ³šæ: å
±æãã©ã³ãã£ãã·ã¥æ©èœã䜿ãã«ã¯ãAurora PostgreSQL ããŒãžã§ã³ 17.6 以éããŸãã¯ããŒãžã§ã³ 16.10 以éã䜿çšããå¿
èŠããããŸãã -- Create partitioned tables CREATE TABLE t1(part_key int, c1 int) PARTITION BY RANGE(part_key); CREATE TABLE t2(part_key int, c1 int) PARTITION BY RANGE(part_key); \pset pager -- Generate 1000 partitions for each table (simulating large-scale partitioning) SELECT 'CREATE TABLE t1_' || x || ' PARTITION OF t1 FOR VALUES FROM (' || x || ') TO (' || x+1 || ')' FROM generate_series(1, 1000) x; \gexec SELECT 'CREATE TABLE t2_' || x || ' PARTITION OF t2 FOR VALUES FROM (' || x || ') TO (' || x+1 || ')' FROM generate_series(1, 1000) x; \gexec -- Populate tables with sample data DO $do$ BEGIN FOR i IN 1..100 LOOP INSERT INTO t1 SELECT x, i FROM generate_series(1, 1000) x; INSERT INTO t2 SELECT x, i FROM generate_series(1, 1000) x; END LOOP; END $do$; -- Update statistics for optimal query planning ANALYZE t1, t2; \gexec ã¹ã€ããã䜿ã£ãŠãselect ã®åºåãç¬ç«ãã SQL ã¹ããŒãã¡ã³ããšããŠå®è¡ã§ããŸãã\pset pager ã§ psql pager ãç¡å¹ã«ãããšãããŒãã«ããŒãã£ã·ã§ã³äœææã«äœåºŠã Enter ãæŒãå¿
èŠããªããªããŸãã ã¡ã¢ãªæ¶è²»ã®èŠ³å¯ Session 1 ã§ã次ã®ããªãã¢ãã¹ããŒãã¡ã³ããäœæããŠå®è¡ããŸãã -- Create a prepared statement with a simple join PREPARE p2(int, int) AS SELECT sum(t1.c1) FROM t1, t2 WHERE t1.part_key = t2.part_key AND t1.c1 = $1 AND t1.part_key = $2; -- Execute 6 times to trigger generic plan creation EXECUTE p2(1, 4); -- Execution 1: Custom plan EXECUTE p2(1, 4); -- Execution 2: Custom plan EXECUTE p2(1, 4); -- Execution 3: Custom plan EXECUTE p2(1, 4); -- Execution 4: Custom plan EXECUTE p2(1, 4); -- Execution 5: Custom plan EXECUTE p2(1, 4); -- Execution 6: Generic plan created 次ã«ãã¡ã¢ãªæ¶è²»ã確èªããŸãã -- Check memory usage for cached plans SELECT name, ident, pg_size_pretty(total_bytes) as size FROM pg_backend_memory_contexts WHERE name = 'CachedPlan'; -[ RECORD 1 ]-+--------------------------------------- name | CachedPlan ident | prepare p2(int, int) as + | select sum(t1.c1) + | from t1, t2 + | where t1.part_key = t2.part_key and + | t1.c1 = $1 and t1.part_key = $2; size | 4161 kB ãã®ãã¹ãã§ã¯ãæ±çšãã©ã³ãçŽ 4MB ãæ¶è²»ããããªãã¢ãã¹ããŒãã¡ã³ããè§£æŸããããæ¥ç¶ãçµäºãããŸã§ã¡ã¢ãªã«æ®ãããšãããããŸãã éè€ã®åé¡ æ¬¡ã«ãå¥ã®ã»ãã·ã§ã³ ( Session 2 ) ã§åãããªãã¢ãã¹ããŒãã¡ã³ããå®è¡ããŸãã -- Session 2: Using the same prepared statement PREPARE p2(int, int) AS SELECT sum(t1.c1) FROM t1, t2 WHERE t1.part_key = t2.part_key AND t1.c1 = $1 AND t1.part_key = $2; -- Execute 6 times EXECUTE p2(1, 4); EXECUTE p2(1, 4); EXECUTE p2(1, 4); EXECUTE p2(1, 4); EXECUTE p2(1, 4); EXECUTE p2(1, 4); -- Check memory usage SELECT name, ident, pg_size_pretty(total_bytes) as size FROM pg_backend_memory_contexts WHERE name = 'CachedPlan'; -[ RECORD 1 ]-+--------------------------------------- name | CachedPlan ident | prepare p2(int, int) as + | select sum(t1.c1) + | from t1, t2 + | where t1.part_key = t2.part_key and + | t1.c1 = $1 and t1.part_key = $2; size | 4161 kB Session 2 ãå
šãåãæ±çšãã©ã³ã§ 4MB ãæ¶è²»ããŠããŸã! ä¹ç®å¹æ ãã®éè€ã¯ãããªãã¢ãã¹ããŒãã¡ã³ããå®è¡ãããã¹ãŠã®ã»ãã·ã§ã³ã§çºçããŸãã圱é¿ãèšç®ããŠã¿ãŸãããã 1 ããªãã¢ãã¹ããŒãã¡ã³ã à 100 æ¥ç¶ à 4MB = 400MB ã®ã¡ã¢ãª 100 çš®é¡ã®ããªãã¢ãã¹ããŒãã¡ã³ã à 100 æ¥ç¶ à 4MB = 40GB ã®ã¡ã¢ãª ãã®å€§éã®ã¡ã¢ãªæ¶è²»ã¯ãã»ãã·ã§ã³ãåäžã®æ±çšãã©ã³ã®ã³ããŒãä¿åããŠããã«ããããããçºçããŸãã倿°ã®åæããŒã¿ããŒã¹æ¥ç¶ãããç°å¢ã§ã¯ãå©çšå¯èœãªã¡ã¢ãªãããã«äœ¿ãæããããã倧ããé«äŸ¡ãªã€ã³ã¹ã¿ã³ã¹ã¿ã€ãã䜿ããããåŸãªããªããŸãã ãœãªã¥ãŒã·ã§ã³: Aurora PostgreSQL å
±æãã£ãã·ã¥ãã©ã³ Aurora PostgreSQL ã¯å
±æãã£ãã·ã¥ãã©ã³ (SPC) ã§ãã®åé¡ã解決ããŸããåæ±çšãã©ã³ã®ã³ããŒã 1 ã€ã ãä¿æããè€æ°ã»ãã·ã§ã³ã䜿ããããã«ããŸãããã©ã³ãã£ãã·ã¥ã®ããã©ãŒãã³ã¹äžã®å©ç¹ãç¶æããªãããã¡ã¢ãªæ¶è²»ã倧å¹
ã«åæžããŸãã å
±æãã©ã³ãã£ãã·ã¥ (SPC) ã¯ã ã¯ã©ã¹ã¿ãŒãŸãã¯ã€ã³ã¹ã¿ã³ã¹ãã©ã¡ãŒã¿ã°ã«ãŒã ã§æå¹ã«ã§ããŸãã apg_shared_plan_cache.enable = ON apg_shared_plan_cache.enable ã¯åçãã©ã¡ãŒã¿ã§ããããã倿Žãæå¹ã«ããããã«ã€ã³ã¹ã¿ã³ã¹ãåèµ·åããå¿
èŠã¯ãããŸããã SPC ã¯åçããã·ã¥ããŒãã«ãšããŠå®è£
ãããã»ãã·ã§ã³éã§å
±æãããŸãããã£ãã·ã¥å
ã®ãšã³ããªæ°ã¯ apg_shared_plan_cache.max ã§å¶åŸ¡ã§ããŸããæ¬¡ã®ãã©ã¡ãŒã¿ã§ãšã³ããªã®æå°ãµã€ãºãšæå€§ãµã€ãºãå¶åŸ¡ã§ããŸãã apg_shared_plan_cache.min_size_per_entry apg_shared_plan_cache.max_size_per_entry å
±æãã©ã³ãã£ãã·ã¥ã®åäœã㢠å
±æãã©ã³ãã£ãã·ã¥ãæå¹ã«ããŠãå
ã»ã©ã®å®éšãç¹°ãè¿ããŠã¿ãŸãããã Session 1 (æåã®æ¥ç¶): -- Create and execute the same prepared statement PREPARE p2(int, int) AS SELECT sum(t1.c1) FROM t1, t2 WHERE t1.part_key = t2.part_key AND t1.c1 = $1 AND t1.part_key = $2; -- Execute 6 times EXECUTE p2(1, 4); EXECUTE p2(1, 4); EXECUTE p2(1, 4); EXECUTE p2(1, 4); EXECUTE p2(1, 4); EXECUTE p2(1, 4); -- Check memory usage SELECT name, ident, pg_size_pretty(total_bytes) as size FROM pg_backend_memory_contexts WHERE name = 'CachedPlan'; æåã®ã»ãã·ã§ã³ã¯ãããŒã«ã«ã¡ã¢ãªã« 4MB ã®ãã©ã³ã衚瀺ãããŸã (å
±æãã£ãã·ã¥ã«å
¥åããããã«å¿
èŠ)ã Session 2 (åŸç¶ã®æ¥ç¶): -- Create the same prepared statement PREPARE p2(int, int) AS SELECT sum(t1.c1) FROM t1, t2 WHERE t1.part_key = t2.part_key AND t1.c1 = $1 AND t1.part_key = $2; -- Execute 6 times EXECUTE p2(1, 4); EXECUTE p2(1, 4); EXECUTE p2(1, 4); EXECUTE p2(1, 4); EXECUTE p2(1, 4); EXECUTE p2(1, 4); -- Check memory usage SELECT name, ident, pg_size_pretty(total_bytes) as size FROM pg_backend_memory_contexts WHERE name = 'CachedPlan'; (0 rows) ããŒã«ã«ãã©ã³ã¹ãã¬ãŒãžãªã! 2 çªç®ã®ã»ãã·ã§ã³ã¯å
±æãã©ã³ãã£ãã·ã¥ã䜿ã£ãŠããŸãã ãã£ãã·ã¥äœ¿çšç¶æ³ã®ç£èŠ æ¬¡ã® SQL ãå®è¡ããŠããã£ãã·ã¥ã«ä¿åãããåã
ã®å
±æãã©ã³ãåãåã£ããã£ãã·ã¥ãããæ°ã衚瀺ããŸããåãããã¯ãã»ãã·ã§ã³ã¡ã¢ãªã§éè€ããå¿
èŠããªãã£ããã©ã³ã衚ããŸãã -- View shared plan cache statistics SELECT cache_key, query, hits FROM apg_shared_plan_cache(); -[ RECORD 1 ]------------------------------------- cache_key | -5127257242415815179 query | prepare p2(int, int) as + | select sum(t1.c1) + | from t1, t2 + | where t1.part_key = t2.part_key and + | t1.c1 = $1 and t1.part_key = $2; hits | 2 ã¯ãªãŒã³ã¢ãã: -- clear the cache SELECT * FROM apg_shared_plan_cache_reset(); -- drop the tables DROP TABLE t1; DROP TABLE t2; ããã©ãŒãã³ã¹ãžã®åœ±é¿ 100 æ¥ç¶ã§ 100 çš®é¡ã®ããªãã¢ãã¹ããŒãã¡ã³ãã䜿ãå
ã®ã·ããªãªäŸã§ã¯ã40GB ã®éè€ãã©ã³ã¹ãã¬ãŒãžããããã 400MB ã®å
±æãã£ãã·ã¥ã«å€æãããŸããã以äžã®ã¹ã¯ãªãŒã³ã·ã§ããã¯ã apg_shared_plan_cache.enable = off ã§ 100 æ¥ç¶ã§ 100 çš®é¡ã®ããªãã¢ãã¹ããŒãã¡ã³ã (äžèšã®äŸãã䜿çš) ã䜿ã£ãŠ pgbench ã§ãã¹ããå®è¡ããã€ã³ã¹ã¿ã³ã¹ããååŸãã Freeable Memory CloudWatch ã¡ããªãã¯ã®ã°ã©ãã§ãã02:05 ãã 02:10 ã®éã«ãFreeableMemory ãçŽ 40GB æžå°ããŠããŸããããã¯äºæ³ãããéè€ãã©ã³ã¹ãã¬ãŒãžã®ãããããªã³ããšäžèŽããŸããå
±æãã©ã³ãã£ãã·ã¥ãæå¹ã«ããŠåããã¹ããå床å®è¡ãããšãã¡ã¢ãªãžã®åœ±é¿ã¯å€§å¹
ã«åæžããã40GB ã§ã¯ãªãããããªã¡ã¢ãªããå¿
èŠãšããŸããã§ããã ãã®åæžã«ãããæ¬¡ã®ããšãå¯èœã«ãªããŸãã åãã¯ãŒã¯ããŒããããå°ããã€ã³ã¹ã¿ã³ã¹ã§å®è¡ ããAWS ã³ã¹ãã倧å¹
ã«åæž ã¡ã¢ãªå¶éã«éããããšãªã ããå€ãã®åææ¥ç¶ããµããŒã ãã©ãã£ãã¯ã¹ãã€ã¯æã® ã¡ã¢ãªäžè¶³ãšã©ãŒãåé¿ ãã¹ããã©ã¯ãã£ã¹ ãã®æ©èœã¯æ¬¡ã®å Žåã«ç¹ã«æçã§ãã ã¢ããªã±ãŒã·ã§ã³ãæ°çŸãŸãã¯æ°åã®ããŒã¿ããŒã¹æ¥ç¶ãç¶æããŠãã ããªãã¢ãã¹ããŒãã¡ã³ããå€çšããŠãã ã¯ãšãªã«ããŒãã£ã·ã§ã³ããŒãã«ãè€éãªæŒç®å (join ãå
±éããŒãã«åŒãªã©) ãå«ãŸãã倧ããªãã©ã³ãçæããã ããã¯ãšã³ãããã»ã¹ããã®é«ãã¡ã¢ãªäœ¿çšéã芳å¯ããã ã¯ãŒã¯ããŒãã«ããã©ã¡ãŒã¿åãããã¯ãšãªã䜿çšããå埩çãªã¯ãšãªãã¿ãŒã³ããã å
±æãã£ãã·ã¥ãã©ã³ã¯å€§ããªå©ç¹ãæäŸããŸãããæ¬¡ã®ã·ããªãªã«ã¯é©ããªãå ŽåããããŸãã äžææ§ãé«ãã¢ãããã¯ã¯ãšãªãçšããã¯ãŒã¯ããŒã ããªãã¢ãã¹ããŒãã¡ã³ããã»ãšãã©åå©çšããªãã¢ããªã±ãŒã·ã§ã³ åææ¥ç¶ãå°ãªãç°å¢ ãŸãšã æ¬èšäºã§ã¯ãAurora PostgreSQL ã§å
±æãã£ãã·ã¥ãã©ã³ãæå¹ã«ããæ¹æ³ã説æããŸããã倿°ã®åæããŒã¿ããŒã¹ã»ãã·ã§ã³ã§ããªãã¢ãã¹ããŒãã¡ã³ãã䜿çšããéã«ãåãæ±çšã¯ãšãªãã©ã³ãã¡ã¢ãªã«éè€ããŠä¿åãããã®ãé²ããããšã瀺ããŸããã ã»ãã·ã§ã³éã§åé·ãªãã©ã³ã¹ãã¬ãŒãžãåé€ããããšã§ãããå°ããã€ã³ã¹ã¿ã³ã¹ã§ããå€ãã®æ¥ç¶ãå®è¡ã§ããéçšã®è€éããšã³ã¹ãã®äž¡æ¹ãåæžã§ããŸããããŸããŸãªãã©ã³ã¿ã€ãã®è©³çްã«ã€ããŠã¯ PostgreSQL ããã¥ã¡ã³ãã® the prepare statement ãã空ãã¡ã¢ãªæž¬å®ã®è©³çްã«ã€ããŠã¯ Amazon CloudWatch metrics for Amazon Aurora ãåç
§ããŠãã ããã èè
ã«ã€ã㊠Souvik Bhattacherjee Souvik 㯠AWS ã® Senior Software Engineer ã§ãAurora PostgreSQL ããŒã¿ããŒã¹ã®ã¯ãšãªåŠçæ©èœã®åäžã«åãçµãã§ããŸã泚ãããŒã¿ããŒã¹/HPC æ¥çã§ 8 幎以äžã®çµéšããããããŒã¿ããŒã¹ã·ã¹ãã ãšé«æ§èœã³ã³ãã¥ãŒãã£ã³ã°ã·ã¹ãã ã«é¢é£ãããããã¯ã«è²¢ç®ããŠããŸããã Jungkook Lee Jungkook 㯠AWS ã® Senior Software Development Engineer ã§ãAurora PostgreSQL ã®ããã©ãŒãã³ã¹åäžãšæ©èœæ¡åŒµã«æ³šåããããŒã ããªãŒãããŠããŸããããŒã¿ããŒã¹ã·ã¹ãã ãšåæ£ã³ã³ãã¥ãŒãã£ã³ã°ã¢ãŒããã¯ãã£ã§ 10 幎以äžã®çµéšããããã¯ãšãªæé©åãšããŒã¿ããŒã¹ããã©ãŒãã³ã¹ãå°éãšããŠããŸãã Stephen Wood Stephen 㯠AWS ã® Senior Specialist Database Solutions Architect ã§ããAmazon RDS PostgreSQLãAmazon Aurora PostgreSQLãAmazon Aurora DSQL ãå°éãšããŠããŸããéå» 24 幎éãããŸããŸãªã¿ã€ãã®äŒæ¥ã§ããŒã¿ããŒã¹ã·ã¹ãã ã«é¢ãã£ãŠãããåžžã«æ°ããããŒã¿ããŒã¹ãã¯ãããžãŒã«åãçµãããšã楜ããã§ããŸãã 翻蚳㯠Technical Account Manager ã®ç³æž¡ãæ
åœããŸããã