Postgres: bloat, pg_repack และข้อจำกัดที่เลื่อนออกไป

ผลกระทบของการบวมต่อตารางและดัชนีนั้นเป็นที่รู้จักอย่างกว้างขวาง และไม่ได้ปรากฏเฉพาะใน Postgres เท่านั้น มีวิธีจัดการกับมันตั้งแต่แกะกล่อง เช่น VACUUM FULL หรือ CLUSTER แต่จะล็อคตารางระหว่างดำเนินการ ดังนั้นจึงไม่สามารถใช้งานได้ตลอดเวลา

บทความนี้จะประกอบด้วยทฤษฎีเล็กๆ น้อยๆ เกี่ยวกับวิธีการที่การขยายตัวเกิดขึ้น วิธีที่คุณจะต่อสู้กับมัน เกี่ยวกับข้อจำกัดที่เลื่อนออกไป และปัญหาที่เกิดขึ้นกับการใช้ส่วนขยาย pg_repack

บทความนี้เขียนขึ้นจาก คำพูดของฉัน ที่ PgConf.Russia 2020

ทำไมอาการบวมจึงเกิดขึ้น?

Postgres ขึ้นอยู่กับรุ่นหลายเวอร์ชัน (เอ็มวีซีซี). สิ่งสำคัญคือแต่ละแถวในตารางสามารถมีได้หลายเวอร์ชัน ในขณะที่ธุรกรรมจะไม่เห็นเวอร์ชันเหล่านี้มากกว่าหนึ่งเวอร์ชัน แต่ไม่จำเป็นต้องเป็นเวอร์ชันเดียวกัน ช่วยให้ธุรกรรมหลายรายการทำงานพร้อมกันและแทบไม่มีผลกระทบต่อกัน

แน่นอนว่าจำเป็นต้องจัดเก็บเวอร์ชันเหล่านี้ทั้งหมดไว้ Postgres ทำงานร่วมกับหน่วยความจำทีละหน้า และหน้าคือจำนวนข้อมูลขั้นต่ำที่สามารถอ่านจากดิสก์หรือเขียนได้ ลองดูตัวอย่างเล็กๆ น้อยๆ เพื่อทำความเข้าใจว่าสิ่งนี้เกิดขึ้นได้อย่างไร

สมมติว่าเรามีตารางที่เราได้เพิ่มหลายระเบียนลงไป ข้อมูลใหม่ปรากฏในหน้าแรกของไฟล์ที่จัดเก็บตาราง แถวเหล่านี้เป็นเวอร์ชันที่ใช้งานจริงซึ่งพร้อมใช้งานสำหรับธุรกรรมอื่นๆ หลังจากการคอมมิต (เพื่อความง่าย เราจะถือว่าระดับการแยกคือ Read Commited)

จากนั้นเราได้อัปเดตรายการใดรายการหนึ่ง จึงทำเครื่องหมายเวอร์ชันเก่าว่าไม่เกี่ยวข้องอีกต่อไป

ทีละขั้นตอนในการอัปเดตและลบเวอร์ชันแถว เราพบหน้าเว็บที่มีข้อมูลประมาณครึ่งหนึ่งเป็น "ขยะ" ข้อมูลนี้ไม่สามารถมองเห็นได้ในการทำธุรกรรมใดๆ

Postgres มีกลไก สูญญากาศซึ่งจะล้างเวอร์ชันที่ล้าสมัยและเพิ่มพื้นที่สำหรับข้อมูลใหม่ แต่หากไม่ได้กำหนดค่าเชิงรุกเพียงพอหรือยุ่งอยู่กับการทำงานในตารางอื่น “ข้อมูลขยะ” จะยังคงอยู่ และเราต้องใช้หน้าเพิ่มเติมสำหรับข้อมูลใหม่

ดังนั้นในตัวอย่างของเรา ในช่วงเวลาหนึ่ง ตารางจะประกอบด้วยสี่หน้า แต่เพียงครึ่งเดียวเท่านั้นที่จะมีข้อมูลสด เป็นผลให้เมื่อเข้าถึงตารางเราจะอ่านข้อมูลได้มากกว่าที่จำเป็น

แม้ว่าตอนนี้ VACUUM จะลบเวอร์ชันแถวที่ไม่เกี่ยวข้องทั้งหมดแล้ว แต่สถานการณ์ก็จะไม่ดีขึ้นอย่างมาก เราจะมีพื้นที่ว่างในหน้าต่างๆ หรือแม้แต่ทั้งหน้าสำหรับแถวใหม่ แต่เราจะยังคงอ่านข้อมูลเกินความจำเป็น
อย่างไรก็ตาม หากหน้าว่างทั้งหมด (หน้าที่สองในตัวอย่างของเรา) อยู่ที่ท้ายไฟล์ VACUUM จะสามารถตัดมันได้ แต่ตอนนี้เธออยู่ตรงกลางดังนั้นจึงทำอะไรกับเธอไม่ได้

เมื่อจำนวนหน้าว่างหรือหน้ากระจัดกระจายมากดังกล่าวมีมากขึ้น ซึ่งเรียกว่าการบวม หน้าจะเริ่มส่งผลต่อประสิทธิภาพการทำงาน

ทุกสิ่งที่อธิบายไว้ข้างต้นเป็นกลไกของการเกิดการขยายตัวในตาราง ในดัชนีสิ่งนี้เกิดขึ้นในลักษณะเดียวกันมาก

ฉันมีอาการบวมหรือไม่?

มีหลายวิธีในการพิจารณาว่าคุณมีอาการท้องอืดหรือไม่ แนวคิดแรกคือการใช้สถิติ Postgres ภายในซึ่งมีข้อมูลโดยประมาณเกี่ยวกับจำนวนแถวในตารางจำนวนแถว "สด" ฯลฯ คุณสามารถค้นหาสคริปต์สำเร็จรูปได้หลายรูปแบบบนอินเทอร์เน็ต เราเอาเป็นพื้นฐาน สคริปต์ จากผู้เชี่ยวชาญของ PostgreSQL ซึ่งสามารถประเมินตารางขยายพร้อมกับดัชนี toast และ bloat btree จากประสบการณ์ของเรา ข้อผิดพลาดคือ 10-20%

อีกวิธีหนึ่งคือการใช้ส่วนขยาย pgstattupleซึ่งช่วยให้คุณดูภายในหน้าต่างๆ และรับทั้งค่าประมาณและค่าขยายที่แน่นอน แต่ในกรณีที่สอง คุณจะต้องสแกนทั้งตาราง

เราพิจารณาค่าการขยายตัวเล็กน้อยมากถึง 20% ซึ่งยอมรับได้ ถือได้ว่าเป็นอะนาล็อกของตัวเติมสำหรับ ตาราง и ดัชนี. ที่ 50% ขึ้นไป ปัญหาด้านประสิทธิภาพอาจเริ่มต้นขึ้น

วิธีต่อสู้กับอาการบวม

Postgres มีหลายวิธีในการจัดการกับอาการบวมที่เกิดขึ้นทันที แต่ก็ไม่เหมาะสำหรับทุกคนเสมอไป

กำหนดค่า AUTOVACUUM เพื่อไม่ให้เกิดการบวม. หรือพูดให้ชัดเจนกว่านั้นคือเพื่อให้อยู่ในระดับที่ยอมรับได้สำหรับคุณ ดูเหมือนคำแนะนำของ "กัปตัน" แต่ในความเป็นจริงแล้ว มันไม่ง่ายเสมอไปที่จะบรรลุผล ตัวอย่างเช่น คุณมีการพัฒนาอย่างต่อเนื่องโดยมีการเปลี่ยนแปลงสคีมาข้อมูลเป็นประจำ หรือมีการย้ายข้อมูลบางประเภทเกิดขึ้น เป็นผลให้โปรไฟล์โหลดของคุณอาจเปลี่ยนแปลงบ่อยครั้งและโดยทั่วไปจะแตกต่างกันไปในแต่ละตาราง ซึ่งหมายความว่าคุณต้องทำงานล่วงหน้าเล็กน้อยอย่างต่อเนื่องและปรับ AUTOVACUUM ให้เข้ากับโปรไฟล์ที่เปลี่ยนแปลงไปของแต่ละโต๊ะ แต่เห็นได้ชัดว่านี่ไม่ใช่เรื่องง่ายที่จะทำ

สาเหตุทั่วไปอีกประการหนึ่งที่ทำให้ AUTOVACUUM ไม่สามารถตามตารางได้ก็คือ เนื่องจากมีธุรกรรมที่ใช้เวลานานซึ่งทำให้ไม่สามารถล้างข้อมูลที่มีอยู่ในธุรกรรมเหล่านั้นได้ คำแนะนำที่นี่ก็ชัดเจนเช่นกัน - กำจัดธุรกรรมที่ "ห้อยต่องแต่ง" และลดเวลาของธุรกรรมที่ใช้งานอยู่ให้เหลือน้อยที่สุด แต่หากภาระงานในแอปพลิเคชันของคุณเป็นลูกผสมระหว่าง OLAP และ OLTP คุณสามารถรับการอัปเดตบ่อยครั้งและการสืบค้นสั้นๆ มากมายพร้อมกัน รวมถึงการดำเนินการระยะยาว เช่น การสร้างรายงาน ในสถานการณ์เช่นนี้ การพิจารณากระจายโหลดไปยังฐานต่างๆ เป็นสิ่งที่ควรค่าแก่การพิจารณา ซึ่งจะช่วยให้ปรับแต่งแต่ละฐานได้ละเอียดยิ่งขึ้น

อีกตัวอย่างหนึ่ง - แม้ว่าโปรไฟล์จะเป็นเนื้อเดียวกัน แต่ฐานข้อมูลอยู่ภายใต้ภาระที่สูงมาก แม้แต่ AUTOVACUUM ที่ก้าวร้าวที่สุดก็อาจไม่สามารถรับมือได้ และอาการบวมจะเกิดขึ้น การปรับขนาด (แนวตั้งหรือแนวนอน) เป็นเพียงวิธีแก้ปัญหาเท่านั้น

จะทำอย่างไรในสถานการณ์ที่คุณตั้งค่า AUTOVACUUM แล้ว แต่อาการบวมยังคงเพิ่มขึ้น

ทีม สูญญากาศเต็ม สร้างเนื้อหาของตารางและดัชนีขึ้นใหม่ และเหลือเฉพาะข้อมูลที่เกี่ยวข้องไว้ในนั้น เพื่อกำจัดการขยายตัว มันทำงานได้อย่างสมบูรณ์แบบ แต่ในระหว่างการดำเนินการ ล็อคพิเศษบนโต๊ะจะถูกจับ (AccessExclusionLock) ซึ่งจะไม่อนุญาตให้ดำเนินการค้นหาบนโต๊ะนี้ แม้กระทั่งเลือก หากคุณสามารถหยุดบริการหรือบางส่วนได้ระยะหนึ่ง (ตั้งแต่สิบนาทีไปจนถึงหลายชั่วโมง ขึ้นอยู่กับขนาดของฐานข้อมูลและฮาร์ดแวร์ของคุณ) ตัวเลือกนี้จะดีที่สุด ขออภัย เราไม่มีเวลาเรียกใช้ VACUUM FULL ในระหว่างการบำรุงรักษาตามกำหนดการ ดังนั้นวิธีนี้จึงไม่เหมาะกับเรา

ทีม กลุ่ม สร้างเนื้อหาของตารางใหม่ในลักษณะเดียวกับ VACUUM FULL แต่อนุญาตให้คุณระบุดัชนีตามลำดับข้อมูลที่จะเรียงลำดับทางกายภาพบนดิสก์ (แต่ในอนาคตจะไม่รับประกันลำดับสำหรับแถวใหม่) ในบางสถานการณ์ นี่เป็นการเพิ่มประสิทธิภาพที่ดีสำหรับการสืบค้นจำนวนหนึ่ง โดยการอ่านหลายบันทึกตามดัชนี ข้อเสียของคำสั่งเหมือนกับ VACUUM FULL - จะล็อคตารางระหว่างการทำงาน

ทีม ดัชนี คล้ายกับสองรายการก่อนหน้า แต่สร้างดัชนีเฉพาะหรือดัชนีทั้งหมดของตารางขึ้นมาใหม่ การล็อคอ่อนแอกว่าเล็กน้อย: ShareLock บนโต๊ะ (ป้องกันการดัดแปลง แต่อนุญาตให้เลือก) และ AccessExclusiveLock บนดัชนีที่ถูกสร้างขึ้นใหม่ (บล็อกการสืบค้นโดยใช้ดัชนีนี้) อย่างไรก็ตาม ใน Postgres เวอร์ชันที่ 12 พารามิเตอร์ปรากฏขึ้น พร้อมกันซึ่งช่วยให้คุณสามารถสร้างดัชนีใหม่ได้โดยไม่ต้องปิดกั้นการเพิ่ม การแก้ไข หรือการลบเรคคอร์ดที่เกิดขึ้นพร้อมกัน

ใน Postgres เวอร์ชันก่อนหน้า คุณสามารถบรรลุผลลัพธ์ที่คล้ายกับการใช้ REINDEX CONCURRENTLY สร้างดัชนีพร้อมกัน. ช่วยให้คุณสร้างดัชนีโดยไม่ต้องล็อกอย่างเข้มงวด (ShareUpdateExclusionLock ซึ่งไม่รบกวนการสืบค้นแบบขนาน) จากนั้นแทนที่ดัชนีเก่าด้วยดัชนีใหม่ และลบดัชนีเก่า สิ่งนี้ช่วยให้คุณกำจัดการขยายตัวของดัชนีได้โดยไม่รบกวนแอปพลิเคชันของคุณ สิ่งสำคัญคือต้องพิจารณาว่าเมื่อสร้างดัชนีใหม่ จะมีการโหลดเพิ่มเติมบนระบบย่อยของดิสก์

ดังนั้นหากสำหรับดัชนีมีวิธีกำจัดการบวม "ทันที" ก็ไม่มีสำหรับตาราง นี่คือจุดที่ส่วนขยายภายนอกต่างๆ เข้ามามีบทบาท: pg_repack (เดิมชื่อ pg_reorg) pgcompact, pgกะทัดรัด และคนอื่น ๆ. ในบทความนี้ ฉันจะไม่เปรียบเทียบและจะพูดถึง pg_repack เท่านั้น ซึ่งหลังจากแก้ไขแล้วเราจะใช้เอง

pg_repack ทำงานอย่างไร

สมมติว่าเรามีตารางธรรมดาๆ ที่มีดัชนี ข้อจำกัด และน่าเสียดายที่มีการขยายตัว ขั้นตอนแรกของ pg_repack คือการสร้างตารางบันทึกเพื่อจัดเก็บข้อมูลเกี่ยวกับการเปลี่ยนแปลงทั้งหมดในขณะที่กำลังทำงานอยู่ ทริกเกอร์จะจำลองการเปลี่ยนแปลงเหล่านี้ทุกครั้งที่แทรก อัปเดต และลบ จากนั้นจะมีการสร้างตารางคล้ายกับตารางดั้งเดิม แต่ไม่มีดัชนีและข้อ จำกัด เพื่อไม่ให้กระบวนการแทรกข้อมูลช้าลง

จากนั้น pg_repack จะถ่ายโอนข้อมูลจากตารางเก่าไปยังตารางใหม่ โดยกรองแถวที่ไม่เกี่ยวข้องทั้งหมดออกโดยอัตโนมัติ จากนั้นสร้างดัชนีสำหรับตารางใหม่ ในระหว่างการดำเนินการการดำเนินการทั้งหมดเหล่านี้ การเปลี่ยนแปลงจะสะสมในตารางบันทึก

ขั้นตอนต่อไปคือการโอนการเปลี่ยนแปลงไปยังตารางใหม่ การย้ายข้อมูลจะดำเนินการซ้ำหลายครั้ง และเมื่อมีรายการเหลือน้อยกว่า 20 รายการในตารางบันทึก pg_repack จะได้รับการล็อคที่รัดกุม ย้ายข้อมูลล่าสุด และแทนที่ตารางเก่าด้วยตารางใหม่ในตารางระบบ Postgres นี่เป็นช่วงเวลาเดียวและสั้นมากที่คุณจะไม่สามารถทำงานกับโต๊ะได้ หลังจากนั้น ตารางเก่าและตารางที่มีบันทึกจะถูกลบ และพื้นที่ว่างในระบบไฟล์จะเพิ่มขึ้น กระบวนการนี้เสร็จสมบูรณ์

ทุกอย่างดูดีในทางทฤษฎี แต่จะเกิดอะไรขึ้นในทางปฏิบัติ? เราทดสอบ pg_repack โดยไม่มีโหลดและต่ำกว่าโหลด และตรวจสอบการทำงานของมันในกรณีที่หยุดก่อนเวลาอันควร (หรืออีกนัยหนึ่งคือใช้ Ctrl+C) การทดสอบทั้งหมดเป็นบวก

เราไปร้านขายอาหาร แล้วทุกอย่างก็ไม่เป็นไปตามที่เราคาดไว้

ขายแพนเค้กชิ้นแรก

ในคลัสเตอร์แรก เราได้รับข้อผิดพลาดเกี่ยวกับการละเมิดข้อจำกัดเฉพาะ:

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

ข้อจำกัดนี้มีชื่อที่สร้างขึ้นโดยอัตโนมัติ index_16508 - สร้างขึ้นโดย pg_repack จากคุณลักษณะที่รวมอยู่ในองค์ประกอบ เราได้กำหนดข้อจำกัด "ของเรา" ที่สอดคล้องกับมัน ปัญหากลายเป็นว่านี่ไม่ใช่ข้อจำกัดธรรมดาโดยสิ้นเชิง แต่เป็นข้อจำกัดที่เลื่อนออกไป (ข้อจำกัดที่เลื่อนออกไป), เช่น. การตรวจสอบจะดำเนินการช้ากว่าคำสั่ง sql ซึ่งนำไปสู่ผลลัพธ์ที่ไม่คาดคิด

ข้อจำกัดที่เลื่อนออกไป: เหตุใดจึงมีความจำเป็นและวิธีการทำงาน

ทฤษฎีเล็กๆ น้อยๆ เกี่ยวกับข้อจำกัดที่เลื่อนออกไป
ลองพิจารณาตัวอย่างง่ายๆ: เรามีหนังสืออ้างอิงตารางของรถยนต์ที่มีคุณลักษณะสองประการ - ชื่อและลำดับของรถในไดเร็กทอรี

create table cars
(
  name text constraint pk_cars primary key,
  ord integer not null constraint uk_cars unique
);

สมมติว่าเราจำเป็นต้องสลับรถคันแรกและคันที่สอง วิธีแก้ปัญหาที่ตรงไปตรงมาคืออัปเดตค่าแรกเป็นค่าที่สอง และค่าที่สองเป็นค่าแรก:

begin;
  update cars set ord = 2 where name = 'audi';
  update cars set ord = 1 where name = 'bmw';
commit;

แต่เมื่อเรารันโค้ดนี้ เราคาดว่าจะมีการละเมิดข้อจำกัด เนื่องจากลำดับของค่าในตารางไม่ซ้ำกัน:

[23305] ERROR: duplicate key value violates unique constraint “uk_cars”
Detail: Key (ord)=(2) already exists.

ฉันจะทำให้มันแตกต่างออกไปได้อย่างไร? ตัวเลือกที่หนึ่ง: เพิ่มการแทนที่มูลค่าเพิ่มเติมให้กับคำสั่งซื้อที่รับประกันว่าไม่มีอยู่ในตาราง เช่น "-1" ในการเขียนโปรแกรม สิ่งนี้เรียกว่า “การแลกเปลี่ยนค่าของตัวแปรสองตัวผ่านหนึ่งในสาม” ข้อเสียเปรียบเพียงอย่างเดียวของวิธีนี้คือการอัพเดตเพิ่มเติม

ตัวเลือกที่สอง: ออกแบบตารางใหม่เพื่อใช้ชนิดข้อมูลจุดลอยตัวสำหรับค่าลำดับแทนจำนวนเต็ม จากนั้น เมื่ออัปเดตค่าจาก 1 เช่น เป็น 2.5 รายการแรกจะ "ยืน" โดยอัตโนมัติระหว่างรายการที่สองและสาม โซลูชันนี้ใช้งานได้ แต่มีข้อจำกัดสองประการ ประการแรก มันจะไม่ทำงานสำหรับคุณหากมีการใช้ค่าที่ไหนสักแห่งในอินเทอร์เฟซ ประการที่สอง ขึ้นอยู่กับความแม่นยำของประเภทข้อมูล คุณจะมีส่วนแทรกที่เป็นไปได้ในจำนวนจำกัดก่อนที่จะคำนวณค่าของบันทึกทั้งหมดใหม่

ตัวเลือกที่สาม: ทำให้ข้อ จำกัด เลื่อนออกไปเพื่อให้มีการตรวจสอบในเวลาที่กระทำเท่านั้น:

create table cars
(
  name text constraint pk_cars primary key,
  ord integer not null constraint uk_cars unique deferrable initially deferred
);

เนื่องจากตรรกะของคำขอเริ่มต้นของเราทำให้มั่นใจได้ว่าค่าทั้งหมดไม่ซ้ำกัน ณ เวลาที่กระทำการ จึงจะสำเร็จ

แน่นอนว่าตัวอย่างที่กล่าวถึงข้างต้นนั้นเป็นเรื่องสังเคราะห์มาก แต่ก็เผยให้เห็นแนวคิดนี้ ในแอปพลิเคชันของเรา เราใช้ข้อจำกัดที่เลื่อนออกไปเพื่อใช้ตรรกะที่รับผิดชอบในการแก้ไขข้อขัดแย้งเมื่อผู้ใช้ทำงานกับออบเจ็กต์วิดเจ็ตที่แชร์บนบอร์ดพร้อมกัน การใช้ข้อจำกัดดังกล่าวช่วยให้เราทำให้โค้ดของแอปพลิเคชันง่ายขึ้นเล็กน้อย

โดยทั่วไป ขึ้นอยู่กับประเภทของข้อจำกัด Postgres มีรายละเอียดสามระดับสำหรับการตรวจสอบ ได้แก่ ระดับแถว ธุรกรรม และนิพจน์

ที่มา: ขอทาน

CHECK และ NOT NULL จะถูกตรวจสอบที่ระดับแถวเสมอ สำหรับข้อจำกัดอื่นๆ ดังที่เห็นจากตาราง จะมีตัวเลือกที่แตกต่างกัน คุณสามารถอ่านเพิ่มเติมได้ ที่นี่.

เพื่อสรุปโดยย่อ ข้อจำกัดที่เลื่อนออกไปในหลายสถานการณ์ทำให้มีโค้ดที่อ่านได้ง่ายขึ้นและมีคำสั่งน้อยลง อย่างไรก็ตาม คุณต้องจ่ายเงินสำหรับสิ่งนี้โดยทำให้กระบวนการดีบักซับซ้อนขึ้น เนื่องจากช่วงเวลาที่เกิดข้อผิดพลาดและช่วงเวลาที่คุณทราบจะถูกแยกออกจากกันทันเวลา ปัญหาที่เป็นไปได้อีกประการหนึ่งคือ ผู้จัดกำหนดการอาจไม่สามารถสร้างแผนที่เหมาะสมที่สุดได้เสมอไป ถ้าคำขอเกี่ยวข้องกับข้อจำกัดที่เลื่อนออกไป

การปรับปรุง pg_repack

เราได้กล่าวถึงข้อจำกัดที่เลื่อนออกไปแล้ว แต่จะเกี่ยวข้องกับปัญหาของเราอย่างไร จำข้อผิดพลาดที่เราได้รับก่อนหน้านี้:

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

มันเกิดขึ้นเมื่อข้อมูลถูกคัดลอกจากตารางบันทึกไปยังตารางใหม่ มันดูแปลกเพราะว่า... ข้อมูลในตารางบันทึกจะถูกคอมมิตพร้อมกับข้อมูลในตารางต้นฉบับ หากพวกเขาปฏิบัติตามข้อจำกัดของตารางดั้งเดิม พวกเขาจะละเมิดข้อจำกัดเดียวกันในตารางใหม่ได้อย่างไร

ปรากฎว่าต้นตอของปัญหาอยู่ที่ขั้นตอนก่อนหน้าของ pg_repack ซึ่งสร้างเฉพาะดัชนีเท่านั้น แต่ไม่มีข้อจำกัด ตารางเก่ามีข้อจำกัดเฉพาะ และตารางใหม่สร้างดัชนีเฉพาะแทน

สิ่งสำคัญที่ควรทราบในที่นี้คือ หากข้อจำกัดเป็นเรื่องปกติและไม่ถูกเลื่อนออกไป ดัชนีเฉพาะที่สร้างขึ้นแทนจะเทียบเท่ากับข้อจำกัดนี้ เนื่องจาก ข้อจำกัดเฉพาะใน Postgres ถูกนำมาใช้โดยการสร้างดัชนีเฉพาะ แต่ในกรณีของข้อจำกัดที่เลื่อนออกไป ลักษณะการทำงานจะไม่เหมือนเดิม เนื่องจากดัชนีไม่สามารถเลื่อนออกไปได้ และจะถูกตรวจสอบเสมอในขณะที่ดำเนินการคำสั่ง sql

ดังนั้นสาระสำคัญของปัญหาจึงอยู่ที่ "ความล่าช้า" ของการตรวจสอบ: ในตารางต้นฉบับจะเกิดขึ้น ณ เวลาที่กระทำและในตารางใหม่ในขณะที่ดำเนินการคำสั่ง sql ซึ่งหมายความว่าเราจำเป็นต้องตรวจสอบให้แน่ใจว่าการตรวจสอบจะดำเนินการเหมือนกันในทั้งสองกรณี: ล่าช้าเสมอหรือทันทีเสมอ

แล้วเรามีไอเดียอะไรบ้าง?

สร้างดัชนีที่คล้ายกับการเลื่อนออกไป

แนวคิดแรกคือดำเนินการตรวจสอบทั้งสองในโหมดทันที สิ่งนี้อาจทำให้เกิดข้อจำกัดเชิงบวกที่ผิดพลาดหลายประการ แต่หากมีเพียงไม่กี่ข้อ สิ่งนี้ไม่ควรส่งผลกระทบต่อการทำงานของผู้ใช้ เนื่องจากความขัดแย้งดังกล่าวเป็นสถานการณ์ปกติสำหรับพวกเขา ตัวอย่างเช่นเกิดขึ้นเมื่อผู้ใช้สองคนเริ่มแก้ไขวิดเจ็ตเดียวกันในเวลาเดียวกัน และไคลเอนต์ของผู้ใช้คนที่สองไม่มีเวลารับข้อมูลที่วิดเจ็ตถูกบล็อกอยู่แล้วสำหรับการแก้ไขโดยผู้ใช้คนแรก ในสถานการณ์เช่นนี้ เซิร์ฟเวอร์ปฏิเสธผู้ใช้คนที่สอง และไคลเอนต์จะย้อนกลับการเปลี่ยนแปลงและบล็อกวิดเจ็ต อีกไม่นานเมื่อผู้ใช้คนแรกแก้ไขเสร็จแล้ว คนที่สองจะได้รับข้อมูลที่วิดเจ็ตไม่ได้ถูกบล็อกอีกต่อไปและจะสามารถดำเนินการซ้ำได้

เพื่อให้แน่ใจว่าการตรวจสอบจะอยู่ในโหมดไม่เลื่อนออกไปเสมอ เราได้สร้างดัชนีใหม่ที่คล้ายกับข้อจำกัดที่เลื่อนออกไปเดิม:

CREATE UNIQUE INDEX CONCURRENTLY uk_tablename__immediate ON tablename (id, index);
-- run pg_repack
DROP INDEX CONCURRENTLY uk_tablename__immediate;

ในสภาพแวดล้อมการทดสอบ เราได้รับข้อผิดพลาดที่คาดไว้เพียงไม่กี่ข้อเท่านั้น ความสำเร็จ! เรารัน pg_repack อีกครั้งในการใช้งานจริงและได้รับข้อผิดพลาด 5 ข้อในคลัสเตอร์แรกในหนึ่งชั่วโมงของการทำงาน นี่เป็นผลลัพธ์ที่ยอมรับได้ อย่างไรก็ตาม จำนวนข้อผิดพลาดเพิ่มขึ้นอย่างมากในคลัสเตอร์ที่สองแล้ว และเราต้องหยุด pg_repack

ทำไมมันถึงเกิดขึ้น? โอกาสที่จะเกิดข้อผิดพลาดขึ้นอยู่กับจำนวนผู้ใช้ที่ทำงานกับวิดเจ็ตเดียวกันในเวลาเดียวกัน เห็นได้ชัดว่าในขณะนั้นมีการเปลี่ยนแปลงทางการแข่งขันน้อยลงมากกับข้อมูลที่จัดเก็บไว้ในคลัสเตอร์แรกมากกว่าคลัสเตอร์อื่น ๆ กล่าวคือ เราแค่ "โชคดี"

ความคิดนี้ไม่ได้ผล ณ จุดนั้น เราเห็นวิธีแก้ปัญหาอื่นอีกสองวิธี: เขียนโค้ดแอปพลิเคชันของเราใหม่เพื่อกำจัดข้อจำกัดที่เลื่อนออกไป หรือ "สอน" pg_repack ให้ทำงานร่วมกับพวกมัน เราเลือกอันที่สอง

แทนที่ดัชนีในตารางใหม่ด้วยข้อจำกัดที่เลื่อนออกไปจากตารางเดิม

วัตถุประสงค์ของการแก้ไขนั้นชัดเจน - หากตารางต้นฉบับมีข้อจำกัดที่เลื่อนออกไป คุณจะต้องสร้างข้อจำกัดดังกล่าวสำหรับตารางใหม่ ไม่ใช่ดัชนี

เพื่อทดสอบการเปลี่ยนแปลงของเรา เราได้เขียนการทดสอบง่ายๆ:

• ตารางที่มีข้อจำกัดที่เลื่อนออกไปและหนึ่งบันทึก
• แทรกข้อมูลในลูปที่ขัดแย้งกับบันทึกที่มีอยู่
• ทำการอัพเดต – ข้อมูลไม่ขัดแย้งกันอีกต่อไป
• กระทำการเปลี่ยนแปลง

create table test_table
(
  id serial,
  val int,
  constraint uk_test_table__val unique (val) deferrable initially deferred 
);

INSERT INTO test_table (val) VALUES (0);
FOR i IN 1..10000 LOOP
  BEGIN
    INSERT INTO test_table VALUES (0) RETURNING id INTO v_id;
    UPDATE test_table set val = i where id = v_id;
    COMMIT;
  END;
END LOOP;

pg_repack เวอร์ชันดั้งเดิมจะขัดข้องเสมอในการแทรกครั้งแรก เวอร์ชันที่แก้ไขนั้นทำงานได้โดยไม่มีข้อผิดพลาด ยอดเยี่ยม.

เราไปที่การผลิตและได้รับข้อผิดพลาดอีกครั้งในขั้นตอนเดียวกันของการคัดลอกข้อมูลจากตารางบันทึกไปยังตารางใหม่:

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

สถานการณ์คลาสสิก: ทุกอย่างทำงานในสภาพแวดล้อมการทดสอบ แต่ไม่ใช่ในการใช้งานจริง?!

APPLY_COUNT และทางแยกของสองชุด

เราเริ่มวิเคราะห์โค้ดทีละบรรทัดและค้นพบจุดสำคัญ: ข้อมูลถูกถ่ายโอนจากตารางบันทึกไปยังตารางใหม่เป็นชุด ค่าคงที่ APPLY_COUNT ระบุขนาดของชุดงาน:

for (;;)
{
num = apply_log(connection, table, APPLY_COUNT);

if (num > MIN_TUPLES_BEFORE_SWITCH)
     continue;  /* there might be still some tuples, repeat. */
...
}

ปัญหาคือข้อมูลจากธุรกรรมดั้งเดิม ซึ่งการดำเนินการหลายอย่างอาจละเมิดข้อจำกัด เมื่อถ่ายโอน อาจจบลงที่จุดเชื่อมต่อของสองชุด - ครึ่งหนึ่งของคำสั่งจะกระทำในชุดแรก และอีกครึ่งหนึ่งจะกระทำ ในวินาที และที่นี่ขึ้นอยู่กับโชคของคุณ: หากทีมไม่ละเมิดสิ่งใด ๆ ในชุดแรก ทุกอย่างก็เรียบร้อยดี แต่ถ้าพวกเขาทำผิดก็จะเกิดข้อผิดพลาด

APPLY_COUNT เท่ากับ 1000 บันทึก ซึ่งอธิบายว่าทำไมการทดสอบของเราจึงประสบความสำเร็จ - ไม่ครอบคลุมถึงกรณีของ "จุดเชื่อมต่อแบบแบตช์" เราใช้สองคำสั่ง - แทรกและอัปเดต ดังนั้นธุรกรรม 500 รายการของสองคำสั่งจึงถูกจัดอยู่ในแบทช์เสมอ และเราไม่พบปัญหาใดๆ หลังจากเพิ่มการอัปเดตครั้งที่สอง การแก้ไขของเราหยุดทำงาน:

FOR i IN 1..10000 LOOP
  BEGIN
    INSERT INTO test_table VALUES (1) RETURNING id INTO v_id;
    UPDATE test_table set val = i where id = v_id;
    UPDATE test_table set val = i where id = v_id; -- one more update
    COMMIT;
  END;
END LOOP;

ดังนั้น งานถัดไปคือตรวจสอบให้แน่ใจว่าข้อมูลจากตารางเดิมซึ่งมีการเปลี่ยนแปลงในธุรกรรมหนึ่ง ไปสิ้นสุดในตารางใหม่ภายในธุรกรรมเดียวด้วย

การปฏิเสธจากการผสม

และอีกครั้ง เรามีวิธีแก้ปัญหาสองวิธี อันดับแรก: ละทิ้งการแบ่งพาร์ติชันออกเป็นชุด ๆ และถ่ายโอนข้อมูลในธุรกรรมเดียว ข้อดีของโซลูชันนี้คือความเรียบง่าย - การเปลี่ยนแปลงโค้ดที่จำเป็นมีเพียงเล็กน้อย (อย่างไรก็ตามในเวอร์ชันเก่า pg_reorg ทำงานเช่นนั้นทุกประการ) แต่มีปัญหาเกิดขึ้น - เรากำลังสร้างธุรกรรมที่ใช้เวลานาน และดังที่กล่าวไว้ก่อนหน้านี้ว่าเป็นภัยคุกคามต่อการเกิดขึ้นของการขยายตัวครั้งใหม่

วิธีที่สองนั้นซับซ้อนกว่า แต่น่าจะถูกต้องมากกว่า: สร้างคอลัมน์ในตารางบันทึกพร้อมตัวระบุธุรกรรมที่เพิ่มข้อมูลลงในตาราง จากนั้น เมื่อเราคัดลอกข้อมูล เราสามารถจัดกลุ่มตามแอตทริบิวต์นี้ และให้แน่ใจว่าการเปลี่ยนแปลงที่เกี่ยวข้องจะถูกโอนไปพร้อมกัน แบทช์จะถูกสร้างขึ้นจากธุรกรรมหลายรายการ (หรือธุรกรรมขนาดใหญ่หนึ่งรายการ) และขนาดจะแตกต่างกันไปขึ้นอยู่กับจำนวนข้อมูลที่เปลี่ยนแปลงในธุรกรรมเหล่านี้ สิ่งสำคัญที่ควรทราบคือเนื่องจากข้อมูลจากธุรกรรมที่แตกต่างกันเข้าสู่ตารางบันทึกในลำดับแบบสุ่ม จึงไม่สามารถอ่านตามลำดับได้อีกต่อไปเหมือนเมื่อก่อน seqscan สำหรับแต่ละคำขอที่มีการกรองด้วย tx_id นั้นแพงเกินไป จำเป็นต้องมีดัชนี แต่จะทำให้วิธีการช้าลงเนื่องจากค่าใช้จ่ายในการอัปเดต โดยทั่วไปแล้วคุณต้องเสียสละบางอย่างเช่นเคย

ดังนั้นเราจึงตัดสินใจเริ่มด้วยตัวเลือกแรกเนื่องจากง่ายกว่า อันดับแรก จำเป็นต้องทำความเข้าใจว่าธุรกรรมระยะยาวจะเป็นปัญหาจริงหรือไม่ เนื่องจากการถ่ายโอนข้อมูลหลักจากตารางเก่าไปยังตารางใหม่เกิดขึ้นในธุรกรรมที่ยาวนานเพียงครั้งเดียว คำถามจึงเปลี่ยนไปเป็น “เราจะเพิ่มธุรกรรมนี้เท่าใด?” ระยะเวลาของการทำธุรกรรมครั้งแรกขึ้นอยู่กับขนาดของตารางเป็นหลัก ระยะเวลาของการเปลี่ยนแปลงใหม่ขึ้นอยู่กับจำนวนการเปลี่ยนแปลงที่สะสมในตารางระหว่างการถ่ายโอนข้อมูล เช่น ในเรื่องความเข้มของภาระ การเรียกใช้ pg_repack เกิดขึ้นในช่วงเวลาที่มีภาระบริการน้อยที่สุด และปริมาณการเปลี่ยนแปลงมีขนาดเล็กอย่างไม่เป็นสัดส่วนเมื่อเทียบกับขนาดดั้งเดิมของตาราง เราตัดสินใจว่าสามารถละเลยเวลาของการทำธุรกรรมใหม่ได้ (สำหรับการเปรียบเทียบ โดยเฉลี่ยคือ 1 ชั่วโมง 2-3 นาที)

การทดลองเป็นบวก เปิดตัวในการผลิตด้วย เพื่อความชัดเจน นี่คือรูปภาพที่มีขนาดเท่ากับฐานข้อมูลใดฐานข้อมูลหนึ่งหลังจากรันแล้ว:

เนื่องจากเราพอใจกับโซลูชันนี้อย่างสมบูรณ์ เราจึงไม่ได้พยายามใช้โซลูชันที่สอง แต่เรากำลังพิจารณาถึงความเป็นไปได้ที่จะหารือเกี่ยวกับโซลูชันนี้กับนักพัฒนาส่วนขยาย น่าเสียดายที่การแก้ไขในปัจจุบันของเรายังไม่พร้อมสำหรับการเผยแพร่ เนื่องจากเราแก้ไขปัญหาด้วยข้อจำกัดการเลื่อนออกไปเท่านั้น และสำหรับแพตช์ฉบับสมบูรณ์ จำเป็นต้องให้การสนับสนุนสำหรับประเภทอื่นๆ เราหวังว่าจะสามารถทำเช่นนี้ได้ในอนาคต

บางทีคุณอาจมีคำถามว่าทำไมเราถึงเข้าไปเกี่ยวข้องกับเรื่องนี้ด้วยการดัดแปลง pg_repack และไม่ใช้แอนะล็อกของมัน เป็นต้น เมื่อถึงจุดหนึ่ง เราก็คิดถึงเรื่องนี้เช่นกัน แต่ประสบการณ์เชิงบวกของการใช้งานก่อนหน้านี้ บนโต๊ะโดยไม่มีข้อจำกัดที่เลื่อนออกไป เป็นแรงบันดาลใจให้เราพยายามเข้าใจแก่นแท้ของปัญหาและแก้ไข นอกจากนี้ การใช้วิธีแก้ปัญหาอื่นยังต้องใช้เวลาในการดำเนินการทดสอบ ดังนั้นเราจึงตัดสินใจว่าจะพยายามแก้ไขปัญหาในนั้นก่อน และหากเราตระหนักว่าเราไม่สามารถทำเช่นนี้ได้ในเวลาที่เหมาะสม เราก็จะเริ่มดูที่อะนาล็อก .

ผลการวิจัย

สิ่งที่เราแนะนำได้จากประสบการณ์ของเราเอง:

1. สังเกตอาการบวมของคุณ จากข้อมูลการตรวจสอบ คุณสามารถเข้าใจได้ว่าการกำหนดค่าระบบสูญญากาศอัตโนมัติดีเพียงใด
2. ปรับ AUTOVACUUM เพื่อรักษาอาการบวมให้อยู่ในระดับที่ยอมรับได้
3. หากการขยายตัวยังคงเพิ่มขึ้นและคุณไม่สามารถเอาชนะมันได้โดยใช้เครื่องมือที่แกะกล่อง อย่ากลัวที่จะใช้ส่วนขยายภายนอก สิ่งสำคัญคือการทดสอบทุกอย่างให้ดี
4. อย่ากลัวที่จะปรับเปลี่ยนโซลูชันภายนอกให้เหมาะกับความต้องการของคุณ - บางครั้งวิธีนี้อาจมีประสิทธิภาพมากกว่าและง่ายกว่าการเปลี่ยนโค้ดของคุณเอง

ที่มา: will.com