วิธีใช้การตั้งค่าหน่วยความจำของ Postgres ให้คุ้มค่าสูงสุด

• ฐานข้อมูล Postgres ใช้ RAM จำนวนมาก โดยระหว่างการสร้าง result set จะมีขั้นตอนอย่างการจับคู่ดัชนี การค้นหาแถวที่เกี่ยวข้องจากตาราง และการ merge/filter/aggregate/sort tuple ซึ่งทุกขั้นตอนล้วนพึ่งพาหน่วยความจำ
• หากต้องการปรับการใช้หน่วยความจำของ Postgres ให้เหมาะสม ต้องใช้ RAM ที่มีอยู่ให้คุ้มค่าที่สุด พร้อมทั้งปรับสมดุลการจัดสรรหน่วยความจำหลายประเภทอย่างมีประสิทธิภาพ และป้องกันไม่ให้ OS ยุติโปรเซสเพราะใช้หน่วยความจำมากเกินไป

Sharing is Caring

• RAM ส่วนที่ใหญ่ที่สุดที่เกี่ยวข้องกับ Postgres คือ shared_buffers ซึ่งเก็บแทนแถวของทุกตารางและดัชนีที่ถูกเรียกใช้บ่อยที่สุด โดยมี heuristic ที่ให้คะแนนตามความถี่การใช้งานคอยสนับสนุน
  • shared_buffers เป็นค่าคงที่ที่ถูกจัดสรรตอนเริ่มต้น Postgres จึงไม่ก่อปัญหาหน่วยความจำแบบไม่คาดคิด
  • ค่าเริ่มต้นคือ 128MB
  • แต่ OS อาจไม่มองว่านี่คือหน่วยความจำที่จองไว้ล่วงหน้า ดังนั้นการตั้งค่าสูงมากจนใกล้เคียงกับ RAM ทั้งหมดของอินสแตนซ์อาจมีความเสี่ยง
• คำแนะนำที่พบบ่อยที่สุดสำหรับ shared_buffers ในระบบโปรดักชันคือ 25% ของ RAM ที่มีอยู่ ซึ่งเป็นจุดเริ่มต้นที่เหมาะกับระบบส่วนใหญ่เพราะสามารถปรับตามฮาร์ดแวร์ได้
  • ผล benchmark แสดงว่าคำแนะนำ 25% มักเพียงพอ แต่ก็ขึ้นอยู่กับลักษณะการใช้งานฐานข้อมูล
  • ตัวอย่างเช่น ระบบรายงานอาจมี cache hit rate ต่ำจากคำสั่ง query ชั่วคราวที่ซับซ้อน และกลับทำงานได้ดีกว่าเล็กน้อยเมื่อใช้ค่าที่ต่ำกว่า
• หากใช้ส่วนขยาย pg_buffercache จะสามารถดูได้อย่างแม่นยำว่าตารางและดัชนีใดถูกจัดสรรอยู่ใน shared buffer บ้าง และปรับค่า shared_buffers ได้จากจำนวนหน้าที่ถูกใช้งานในบัฟเฟอร์
  • หาก buffer cache ไม่ได้ถูกใช้งานถึง 100% ค่าอาจตั้งไว้สูงเกินไป จึงอาจลดขนาดอินสแตนซ์หรือปรับลด shared_buffers ได้
  • หากใช้งานเต็ม 100% และมีหลายตารางที่ถูกแคชไว้เพียงบางส่วน การเพิ่มค่าแบบค่อยเป็นค่อยไปจนกว่าจะเริ่มได้ผลตอบแทนลดลงอาจคุ้มกว่า
• มุมมอง pg_stat_io ใหม่ใน Postgres 16 ก็ช่วยในการจูน shared_buffers ได้เช่นกัน โดยดูได้ทั้ง hit rate และการอ่าน/เขียนของ client backend
  • หากอัตราส่วนการอ่านต่อการเขียนใกล้ 1 อาจหมายความว่า Postgres กำลังวนใช้หน้าเดิม ๆ ใน shared_buffers ซ้ำไปมา และควรเพิ่ม shared_buffers เพื่อลดอาการ thrashing นี้
• หากเริ่มใช้เกิน 50% ของ RAM ทั้งระบบ ควรพิจารณาเพิ่มขนาดอินสแตนซ์ เพราะ Postgres ยังต้องใช้หน่วยความจำสำหรับ user session และ query ที่เกี่ยวข้องด้วย

Working Memory

• หน่วยความจำอีกครึ่งหนึ่งที่ Postgres ใช้ทำงานจริงถูกควบคุมด้วยพารามิเตอร์ work_mem
  • ค่าเริ่มต้นคือ 4MB และมักเป็นหนึ่งในค่าที่ผู้ใช้ปรับเป็นอันดับแรกเพื่อเร่งความเร็วในการรัน query
  • แต่ถ้า OS ยุติ Postgres พร้อมข้อความลักษณะ “หน่วยความจำไม่พอ” การเพิ่ม work_mem ที่ดูเหมือนน่าจะช่วย กลับจะทำให้ปัญหาแย่ลง เพราะยิ่งเพิ่ม RAM ที่ Postgres ใช้และทำให้มีโอกาสถูกยุติมากขึ้น
• หลายคนตีความ “working memory” ว่าเป็นการจัดสรรก้อนเดียวสำหรับงานทั้งหมดที่ Postgres ทำระหว่างรัน query แต่จริง ๆ แล้วอาจมากกว่านั้น
  • แต่ละขั้นตอน (node) จะได้รับ work_mem แยกกัน เช่น หากใช้ค่าเริ่มต้น work_mem 4MB คำสั่ง query ที่ต้องใช้ 4 node อาจกิน RAM ได้สูงสุด 16MB
  • หากบนเซิร์ฟเวอร์ที่มีโหลดสูงมี query แบบนี้รันพร้อมกัน 100 ตัว ก็อาจใช้ RAM สูงสุด 1.6GB เพียงเพื่อคำนวณผลลัพธ์ และ query ที่ซับซ้อนกว่านี้อาจต้องใช้ RAM มากขึ้นตามจำนวน node ที่ต้องใช้ในการประมวลผล
• หากใช้คำสั่ง EXPLAIN เพื่อตรวจสอบ execution plan ของ query จะเห็นว่า Postgres รัน query อย่างไร และต้องใช้ node อะไรบ้างในการสร้างผลลัพธ์
  • เมื่อนำไปใช้ร่วมกับส่วนขยาย pg_stat_statements จะช่วยแยก query ที่ทำงานมากที่สุดออกมา และประเมินการใช้หน่วยความจำรวมจาก work_mem ได้
• หากตั้ง work_mem ต่ำเกินไป แถวข้อมูลหรือผลลัพธ์กลางที่ไม่พอดีกับ RAM จะถูก spill ลงดิสก์ ทำให้ช้าลงมาก
  • สามารถดูมุมมอง pg_stat_database เพื่อดูขนาดรวมสะสมและจำนวนของไฟล์ชั่วคราวทั้งหมดที่เขียนลงดิสก์ และหากขนาดเฉลี่ยเหมาะสมก็อาจเพิ่ม work_mem ให้เท่ากับระดับนั้นได้
• หากต้องการประมาณ RAM ที่ใช้ได้ต่อ session ให้ใช้สูตรนี้: (80% ของ RAM ทั้งหมด - shared_buffers) / (max_connections)
  • ตัวอย่างเช่น หากมี RAM 16GB, shared buffer 4GB และกำหนด maximum connections ไว้ 100 จะใช้ได้ประมาณ 88MB ต่อ session
  • จากนั้นให้นำค่านี้ไปหารด้วยจำนวนเฉลี่ยของ query plan node เพื่อหาค่าที่เหมาะสมสำหรับ work_mem

Ongoing Maintenance

• ส่วนสุดท้ายของการจูนการใช้ RAM ใน Postgres คล้ายกับ working memory แต่เกี่ยวข้องกับงานบำรุงรักษาโดยเฉพาะ และใช้พารามิเตอร์ชื่อ maintenance_work_mem
  • ค่าเริ่มต้นคือ 64MB และกำหนดปริมาณ RAM ที่ใช้เฉพาะกับงานอย่าง VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY
• เนื่องจากมักจำกัดไว้ที่หนึ่งงานต่อหนึ่ง session และมีโอกาสไม่มากที่จะเกิดงานพร้อมกันจำนวนมาก การใช้ค่าที่สูงกว่าจึงถือว่าปลอดภัยพอสมควร
  • งานบำรุงรักษาเหล่านี้อาจใช้หน่วยความจำสูงมาก และจะเสร็จเร็วขึ้นมากหากทำงานใน RAM ได้ทั้งหมด จึงพบได้บ่อยที่จะตั้งไว้ถึง 1GB หรือ 2GB
• ข้อควรระวังสำคัญคือกระบวนการ autovacuum ของ Postgres ที่ทำเครื่องหมาย tuple ที่ตายแล้วไว้เพื่อให้กลับมาใช้ภายหลังได้
  • autovacuum จะเริ่มงานเบื้องหลังได้จนถึงขีดจำกัด autovacuum_max_workers และแต่ละงานอาจใช้ maintenance_work_mem ได้เต็มหนึ่งชุด
  • เซิร์ฟเวอร์ที่มี RAM เหลือพอส่วนใหญ่มักปลอดภัยกับ maintenance working memory ที่ 1GB แต่ถ้า RAM ตึงต้องระวังให้มาก
  • โดยเฉพาะถ้าต้องการจำกัด worker ของ autovacuum จะมีพารามิเตอร์ autovacuum_work_mem แยกไว้ต่างหาก
  • worker ของ autovacuum ใน Postgres ใช้ได้ไม่เกิน 1GB ดังนั้นการตั้ง autovacuum_work_mem สูงกว่านี้จึงไม่มีผล

Session Pooling

• วิธีที่ง่ายที่สุดในการลดการใช้หน่วยความจำคือกำหนดขีดจำกัดเชิงตรรกะให้กับการจัดสรรที่อาจเกิดขึ้น
  • ปัจจุบัน Postgres เป็นเอนจินแบบ process-based ดังนั้นทุก user session จะได้ physical process ไม่ใช่ thread
  • ส่งผลให้ทุก connection มี RAM overhead บางส่วน และเพิ่มภาระจาก context switching
  • เพราะเหตุนี้ คำแนะนำทั่วไปคือกำหนด max_connections ไว้ไม่เกิน 4 เท่าของจำนวน CPU threads ที่มีอยู่ เพื่อลดเวลาที่ใช้สลับ active session ไปมาระหว่าง CPU และจำกัดปริมาณ RAM ที่ session ทั้งหมดอาจใช้ร่วมกันโดยธรรมชาติ
• หากทุก session กำลังรัน query และแต่ละ node เท่ากับการจัดสรร work_mem หนึ่งชุด การใช้ working memory สูงสุดในทางทฤษฎีคือ connections * nodes * work_mem
  • แม้จะไม่สามารถลดความซับซ้อนของ query ได้เสมอไป แต่โดยทั่วไปมักลดจำนวน connection ได้
  • แต่ก็อาจไม่ง่ายนักหากแอปพลิเคชันเปิด session จำนวนมากตลอดเวลา หรือมีหลายไมโครเซอร์วิสแยกกันที่พึ่งพา Postgres
• สูตร work_mem * max_connections * 5 เป็นค่าประมาณคร่าว ๆ ของ RAM สูงสุดที่อินสแตนซ์ Postgres อาจจัดสรรให้ user session เพื่อรองรับ query พื้นฐาน โดยสมมติว่าทุก connection ทำงานอยู่
  • หากเซิร์ฟเวอร์มี RAM ไม่พอกับค่านี้ ควรพิจารณาลดหนึ่งในปัจจัยเหล่านี้หรือเพิ่ม RAM
  • การประมาณว่ามี 5 node ต่อ query โดยเฉลี่ยอาจไม่ตรงกับแอปพลิเคชันของคุณ จึงควรปรับตามความเหมาะสมหลังจากเข้าใจ execution plan ของ query มากขึ้น
• ขั้นถัดไปคือการนำ connection pooler อย่าง PgBouncer มาใช้
  • มันจะแยก client connection ออกจากฐานข้อมูล และนำ Postgres session ที่มีต้นทุนสูงกลับมาใช้ร่วมกันระหว่างไคลเอนต์
  • หากตั้งค่าอย่างเหมาะสม ไคลเอนต์หลายร้อยรายสามารถแชร์ Postgres connection หลักสิบได้โดยไม่กระทบกับแอปพลิเคชัน
  • มีการพบว่า PgBouncer สามารถ multiplex มากกว่า 1000 connections ให้เหลือเพียง 40-50 ได้ในลักษณะนี้ และลดการใช้หน่วยความจำรวมจาก process overhead ได้อย่างมาก

Reducing Bloat

• ด้านที่ยากที่สุดในการติดตามการใช้หน่วยความจำ น่าจะเป็นเรื่อง table bloat
  • Postgres ใช้ multi-version concurrency control (MVCC) เพื่อแทนข้อมูลในระบบจัดเก็บ
  • หมายความว่าทุกครั้งที่มีการแก้ไขแถวในตาราง Postgres จะสร้างสำเนาอีกชุดของแถวนั้นไว้ที่ใดที่หนึ่งในตาราง และติดป้ายด้วย version number ใหม่
  • กระบวนการ VACUUM ของ Postgres จะทำเครื่องหมาย row version เก่าว่าเป็นพื้นที่ “ไม่ได้ใช้งาน” เพื่อให้สามารถวาง row version ใหม่ลงไปได้
• Postgres มีกระบวนการเบื้องหลัง autovacuum ที่คอยค้นหาพื้นที่จัดสรรที่นำกลับมาใช้ใหม่ได้อย่างต่อเนื่อง และป้องกันไม่ให้ตารางขยายตัวไม่สิ้นสุด
  • แต่บางครั้ง โดยเฉพาะในระบบขนาดใหญ่ การตั้งค่าเริ่มต้นอาจไม่เพียงพอ และงานบำรุงรักษานี้อาจตามไม่ทัน
  • ผลคืออาจเกิดตารางที่มีแถวตายมากกว่าแถวที่ยังใช้งานอยู่ ทำให้ตาราง “บวม” ไปด้วยข้อมูลเก่า
• หากตารางบวมมาก ควรพิจารณาผลกระทบต่อ shared buffer ด้วย
  • ถ้าแต่ละหน้าเก็บแถวที่ยังใช้งานได้เพียงแถวเดียวแต่มีแถวตายหลายแถว query หนึ่งที่ต้องการ 10 แถวก็จะต้องดึง 10 หน้าเข้า shared buffer ส่งผลให้เปลืองหน่วยความจำจำนวนมากที่อาจนำไปใช้กับอย่างอื่นได้
  • หากมีการเรียกใช้แถวเหล่านี้สูงเป็นพิเศษ จำนวนการใช้งานจะทำให้มันค้างอยู่ใน shared buffer ต่อไป และลดประสิทธิภาพของแคชลงอย่างมาก
• แม้บนอินเทอร์เน็ตจะมี query สำหรับประเมิน table bloat อยู่มากมาย แต่วิธีเดียวที่จะดูได้ชัดเจนว่าหน้าของตารางเป็นอย่างไรจริง ๆ คือใช้ส่วนขยาย pgstattuple
• หาก free_percent มากกว่า 30% อาจต้องปรับ autovacuum ให้ทำงานเชิงรุกมากขึ้น และถ้ามากกว่า 30% มาก ๆ ก็ควรกำจัด bloat ออกให้หมด
  • ปัจจุบันวิธีที่รองรับอย่างเป็นทางการมีเพียงคำสั่ง VACUUM FULL ซึ่งเป็นการสร้างตารางขึ้นใหม่โดยพื้นฐาน ย้ายทุกแถวที่ยังใช้งานอยู่ไปยังตำแหน่งใหม่ และทิ้งสำเนาที่บวมเดิม
  • กระบวนการนี้จะถือ lock แบบ exclusive ตลอดช่วงที่ทำงาน ดังนั้นแทบทุกกรณีจึงต้องมี downtime บางรูปแบบ
• อีกทางเลือกหนึ่งคือส่วนขยาย pg_repack ที่ Tembo สนับสนุน
  • เครื่องมือบรรทัดคำสั่งนี้สามารถจัดระเบียบตารางใหม่เพื่อกำจัด bloat ได้แบบออนไลน์เต็มรูปแบบโดยไม่ต้องใช้ exclusive lock
  • เนื่องจากเครื่องมือนี้อยู่นอก Postgres core และมีการแก้ไขที่จัดเก็บของตารางและดัชนี จึงมักถูกมองว่าเป็นการใช้งานระดับสูง
  • แนะนำให้ทดสอบอย่างเพียงพอในสภาพแวดล้อมที่ไม่ใช่โปรดักชันก่อนใช้งาน
• คุณยังอาจไปได้ไกลกว่านั้นด้วยการจัดเรียงลำดับคอลัมน์ใหม่ เพื่อทำ column tetris ให้มีจำนวนแถวต่อหน้าสูงที่สุด
  • นี่อาจเป็นการปรับแต่งในระดับสุดโต่ง แต่สำหรับสภาพแวดล้อมที่มีอิสระในการสร้างตารางใหม่แบบนี้ ก็ถือเป็นกลยุทธ์ที่ใช้งานได้จริง

The Balancing Act

• การตั้งค่าพารามิเตอร์และทรัพยากรทั้งหมดเหล่านี้ให้เหมาะสมเป็นทั้งศิลป์และวิทยาศาสตร์
  • เราได้ดูทั้งวิธีวัดการใช้งานจริงของ shared buffer และวิธีตรวจว่าค่า working memory ต่ำเกินไปหรือไม่
  • แต่ถ้าเหมือนกับหลายกรณีที่มีข้อจำกัดด้านฮาร์ดแวร์หรือด้านงบประมาณล่ะ? ตรงนี้เองคือส่วนที่ต้องอาศัย “ศิลป์”
• ในสถานการณ์ที่ RAM จำกัด อาจจำเป็นต้องลด shared_buffers ลงเล็กน้อยเพื่อเปิดพื้นที่ให้ work_mem มากขึ้น หรืออาจต้องลดทั้งสองอย่าง
  • หากแอปพลิเคชันต้องใช้ session จำนวนมาก การลด work_mem หรือเพิ่ม connection pool อาจสมเหตุสมผลกว่า เพื่อไม่ให้ concurrent session สะสมการจัดสรร RAM จำนวนมาก
  • หากก่อนหน้านี้เคยเพิ่ม maintenance_work_mem โดยตั้งอยู่บนสมมติฐานว่ามี RAM พอสำหรับทุกอย่าง การลดค่านี้ลงอาจสมเหตุสมผลกว่าเช่นกัน มีหลายปัจจัยที่ต้องพิจารณา
• สำหรับอินสแตนซ์หน่วยความจำต่ำ คำแนะนำข้างต้นอาจยังไม่เพียงพอ ในกรณีเช่นนี้ควรทำตามลำดับงานต่อไปนี้เพื่อใช้หน่วยความจำให้คุ้มที่สุดและหลีกเลี่ยงการใช้ทรัพยากรจนหมด:
  1. เพิ่ม connection pooler และลด max_connections นี่เป็นวิธีที่เร็วและง่ายที่สุดในการลดการใช้ทรัพยากรสูงสุด
  2. ใช้ EXPLAIN กับ query ที่ถูกรายงานว่าถี่ที่สุดใน pg_stat_statements เพื่อหาจำนวน node สูงสุดของ query ไม่ใช่ค่าเฉลี่ย จากนั้นตั้ง work_mem ให้ไม่เกิน (80% ของ RAM ทั้งหมด - shared_buffers) / (max_connections * จำนวน plan node สูงสุด)
  3. ปรับ maintenance_work_mem และ autovacuum_work_mem กลับไปที่ค่าเริ่มต้น 64MB หากงานบำรุงรักษาช้าเกินไปและยังพอใช้ RAM เพิ่มได้ ค่อยพิจารณาเพิ่มทีละ 8MB
  4. ใช้ส่วนขยาย pg_buffercache เพื่อตรวจดูปริมาณตารางที่เก็บอยู่ใน shared_buffers ตรวจแต่ละตารางและดัชนีอย่างละเอียด และดูว่ามีวิธีลดได้หรือไม่ เช่น การเก็บข้อมูลเก่าแยก archive การแก้ query ให้ใช้ข้อมูลน้อยลง เป็นต้น รวมถึงอาจใช้ VACUUM FULL หรือ pg_repack เพื่อบีบอัดหน้าที่ใช้โดยตารางที่กำลังบวมอยู่
  5. หาก pg_buffercache แสดงว่า shared_buffers เต็มและไม่สามารถลดได้อีกโดยไม่ทำให้ active page ถูกไล่ออก ให้ใช้คอลัมน์ usagecount เพื่อจัดลำดับความสำคัญของหน้าที่ active มากที่สุด เนื่องจากค่าของคอลัมน์นี้อยู่ที่ 1-5 การโฟกัสที่หน้าที่ถูกใช้งาน 3-5 ครั้งอาจช่วยลด shared_buffers ได้โดยกระทบประสิทธิภาพไม่มาก
  6. สุดท้ายคือจัดหา hardware ที่แรงขึ้น หากฐานข้อมูลต้องการ RAM มากกว่านี้สำหรับ workload ปัจจุบัน และการลดพารามิเตอร์ข้างต้นส่งผลเสียต่อประสิทธิภาพระบบมากเกินไป โดยทั่วไปการอัปเกรดมักสมเหตุสมผลกว่า