ความสมบูรณ์ของดัชนีใน PostgreSQL ผ่านสายตาของนักพัฒนา Java

สวัสดี

ฉันชื่อ Vanya และฉันเป็นนักพัฒนา Java มันบังเอิญว่าฉันทำงานกับ PostgreSQL บ่อยครั้ง เช่น การตั้งค่าฐานข้อมูล เพิ่มประสิทธิภาพโครงสร้าง ประสิทธิภาพ และเล่น DBA เล็กน้อยในช่วงสุดสัปดาห์

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

ข้อจำกัดความรับผิดชอบ

เวอร์ชันหลักของ PostgreSQL ที่ฉันใช้งานคือ 10 แบบสอบถาม SQL ทั้งหมดที่ฉันใช้ได้รับการทดสอบในเวอร์ชัน 11 ด้วย เวอร์ชันขั้นต่ำที่รองรับคือ 9.6

ประวัติศาสตร์

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

ปัญหาที่หนึ่ง - การกำหนดค่าเริ่มต้น

ทุกคนอาจค่อนข้างเบื่อกับคำอุปมาเกี่ยวกับ Postgres ซึ่งสามารถเรียกใช้บนเครื่องชงกาแฟได้ แต่... การกำหนดค่าเริ่มต้นทำให้เกิดคำถามมากมาย อย่างน้อยที่สุดก็ควรค่าแก่การใส่ใจ การบำรุงรักษา_งาน_mem, temp_file_limit, คำสั่ง_หมดเวลา и lock_timeout.

ในกรณีของเรา การบำรุงรักษา_งาน_mem เป็นค่าเริ่มต้น 64 MB และ temp_file_limit ประมาณ 2 GB - เราไม่มีหน่วยความจำเพียงพอที่จะสร้างดัชนีบนโต๊ะขนาดใหญ่

ดังนั้นใน pg-index-สุขภาพ ฉันรวบรวมซีรีย์ สำคัญในความคิดของฉัน พารามิเตอร์ที่ควรกำหนดค่าสำหรับแต่ละฐานข้อมูล

ปัญหาที่สอง - ดัชนีซ้ำกัน

ฐานข้อมูลของเราอยู่บนไดรฟ์ SSD และเราใช้ HA- การกำหนดค่าด้วยศูนย์ข้อมูลหลายแห่ง โฮสต์หลัก และ n- จำนวนแบบจำลอง พื้นที่ดิสก์เป็นทรัพยากรที่มีค่ามากสำหรับเรา มีความสำคัญไม่น้อยไปกว่าประสิทธิภาพและการใช้ CPU ดังนั้นในอีกด้านหนึ่ง เราต้องการดัชนีเพื่อการอ่านที่รวดเร็ว และในทางกลับกัน เราไม่ต้องการเห็นดัชนีที่ไม่จำเป็นในฐานข้อมูล เนื่องจากดัชนีเหล่านี้กินพื้นที่และทำให้การอัปเดตข้อมูลช้าลง

และตอนนี้ได้ฟื้นฟูทุกอย่างแล้ว ดัชนีไม่ถูกต้อง และได้เห็นมามากพอแล้ว รายงานโดย Oleg Bartunovฉันตัดสินใจจัดการกวาดล้างที่ "ยิ่งใหญ่" ปรากฎว่านักพัฒนาไม่ชอบอ่านเอกสารฐานข้อมูล พวกเขาไม่ชอบมันมาก ด้วยเหตุนี้ จึงเกิดข้อผิดพลาดทั่วไปสองประการ - ดัชนีที่สร้างขึ้นด้วยตนเองบนคีย์หลักและดัชนี "คู่มือ" ที่คล้ายกันในคอลัมน์ที่ไม่ซ้ำกัน ความจริงก็คือมันไม่จำเป็น - Postgres จะทำทุกอย่างเอง ดัชนีดังกล่าวสามารถลบได้อย่างปลอดภัยและมีการวินิจฉัยเพื่อจุดประสงค์นี้ ซ้ำ_ดัชนี.

ปัญหาที่สาม - ดัชนีตัดกัน

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

ปัญหาที่สี่ - คีย์ต่างประเทศที่ไม่มีดัชนี

Postgres ช่วยให้คุณสร้างข้อจำกัดของคีย์ภายนอกโดยไม่ต้องระบุดัชนีสำรอง ในหลายสถานการณ์ นี่ไม่ใช่ปัญหา และอาจไม่แสดงออกมาด้วยซ้ำ... ในขณะนี้...

เช่นเดียวกับเรา: ในบางช่วงเวลางานที่ทำงานตามกำหนดเวลาและล้างฐานข้อมูลคำสั่งทดสอบเริ่มถูก "เพิ่ม" ให้เราโดยโฮสต์หลัก CPU และ IO เสียเปล่า คำขอช้าลงและหมดเวลา บริการมีห้าร้อยรายการ การวิเคราะห์อย่างรวดเร็ว pg_stat_activity แสดงให้เห็นว่าข้อความค้นหาเช่น:

delete from <table> where id in (…)

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

องค์อัศจรรย์ก็เข้ามาช่วยเหลือ อธิบายวิเคราะห์ และบอกว่านอกจากการลบบันทึกในตารางเป้าหมายแล้ว ยังมีการตรวจสอบ Referential Integrity และในตารางใดตารางหนึ่งที่เกี่ยวข้อง การตรวจสอบนี้ล้มเหลว การสแกนตามลำดับ เนื่องจากขาดดัชนีที่เหมาะสม การวินิจฉัยโรคจึงเกิดขึ้น Foreign_keys_without_index.

ปัญหาที่ห้า – ค่าว่างในดัชนี

ตามค่าเริ่มต้น Postgres จะรวมค่า Null ไว้ในดัชนี btree แต่โดยปกติแล้วไม่จำเป็น ดังนั้นฉันจึงพยายามกำจัดโมฆะเหล่านี้ออกอย่างขยันขันแข็ง (diagnostics indexes_with_null_values) การสร้างดัชนีบางส่วนในคอลัมน์ที่เป็นโมฆะตามประเภท where <A> is not null. ด้วยวิธีนี้ ฉันสามารถลดขนาดของหนึ่งในดัชนีของเราจาก 1877 MB เป็น 16 KB และในหนึ่งในบริการ ขนาดฐานข้อมูลลดลงทั้งหมด 16% (โดย 4.3 GB ในจำนวนสัมบูรณ์) เนื่องจากการยกเว้นค่า Null ออกจากดัชนี ประหยัดพื้นที่ดิสก์ได้มหาศาลพร้อมการปรับเปลี่ยนที่ง่ายมาก 🙂

ปัญหาที่หก – ขาดคีย์หลัก

เนื่องจากลักษณะของกลไก MVCC ใน Postgres สถานการณ์เช่นนี้เป็นไปได้ บวมเมื่อขนาดของโต๊ะของคุณเพิ่มขึ้นอย่างรวดเร็วเนื่องจากมีบันทึกที่เสียไปจำนวนมาก ฉันเชื่ออย่างไร้เดียงสาว่าสิ่งนี้จะไม่คุกคามเรา และสิ่งนี้จะไม่เกิดขึ้นกับฐานของเรา เพราะเรา ว้าว!!! เป็นนักพัฒนาปกติ... ฉันช่างโง่และไร้เดียงสาจริงๆ...

วันหนึ่ง การโยกย้ายที่ยอดเยี่ยมครั้งหนึ่งได้นำและอัปเดตบันทึกทั้งหมดในตารางขนาดใหญ่และใช้งานอยู่ เราได้รับขนาดตาราง +100 GB โดยไม่ทราบสาเหตุ มันเป็นความอัปยศอย่างยิ่ง แต่เหตุการณ์ร้ายของเราไม่ได้จบเพียงแค่นั้น หลังจากที่ระบบสุญญากาศอัตโนมัติบนโต๊ะนี้สิ้นสุดลงในอีก 15 ชั่วโมงต่อมา ก็เห็นได้ชัดว่าตำแหน่งทางกายภาพนั้นจะไม่กลับมาอีก เราไม่สามารถหยุดบริการและทำให้ VACUUM FULL ได้ เราจึงตัดสินใจใช้บริการ pg_repack. แล้วมันก็กลายเป็นว่า pg_repack ไม่รู้วิธีการประมวลผลตารางโดยไม่มีคีย์หลักหรือข้อจำกัดด้านเอกลักษณ์อื่นๆ และตารางของเราไม่มีคีย์หลัก การวินิจฉัยโรคจึงเกิดขึ้น tables_without_primary_key.

ในเวอร์ชั่นห้องสมุด 0.1.5 เพิ่มความสามารถในการรวบรวมข้อมูลจากตารางและดัชนีที่เพิ่มขึ้นและตอบสนองต่อข้อมูลได้ทันท่วงที

ปัญหาที่เจ็ดและแปด - ดัชนีไม่เพียงพอและดัชนีที่ไม่ได้ใช้

การวินิจฉัยสองประการต่อไปนี้คือ: tables_with_missing_indexes и unused_indexes – ปรากฏตัวในรูปแบบสุดท้ายเมื่อไม่นานมานี้ ประเด็นก็คือพวกเขาไม่สามารถนำมาและเพิ่มได้

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

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

เป็นข้อสรุป

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

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

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

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

ที่มา: will.com