ถอดความรายงานปี 2015 โดย Alexey Lesovsky "เจาะลึกสถิติภายในของ PostgreSQL"
ข้อจำกัดความรับผิดชอบจากผู้เขียนรายงาน: ฉันทราบว่ารายงานนี้ลงวันที่พฤศจิกายน 2015 - 4 ปีผ่านไปและเวลาผ่านไปนานมาก เวอร์ชัน 9.4 ที่กล่าวถึงในรายงานไม่ได้รับการสนับสนุนอีกต่อไป ในช่วง 4 ปีที่ผ่านมา มีการเปิดตัว 5 รุ่นใหม่ซึ่งมีนวัตกรรม การปรับปรุง และการเปลี่ยนแปลงเกี่ยวกับสถิติปรากฏขึ้นมากมาย และเนื้อหาบางส่วนก็ล้าสมัยและไม่เกี่ยวข้อง ขณะที่ฉันตรวจสอบ ฉันพยายามทำเครื่องหมายสถานที่เหล่านี้เพื่อไม่ให้คุณผู้อ่านเข้าใจผิด ฉันไม่ได้เขียนสถานที่เหล่านี้ใหม่ มีหลายแห่ง และผลที่ได้คือรายงานที่แตกต่างไปจากเดิมอย่างสิ้นเชิง
PostgreSQL DBMS เป็นกลไกขนาดใหญ่ และกลไกนี้ประกอบด้วยระบบย่อยจำนวนมาก ซึ่งการทำงานร่วมกันจะส่งผลโดยตรงต่อประสิทธิภาพของ DBMS ในระหว่างการดำเนินการ จะมีการรวบรวมสถิติและข้อมูลเกี่ยวกับการทำงานของส่วนประกอบ ซึ่งช่วยให้คุณสามารถประเมินประสิทธิภาพของ PostgreSQL และใช้มาตรการเพื่อปรับปรุงประสิทธิภาพ อย่างไรก็ตาม มีข้อมูลนี้จำนวนมากและนำเสนอในรูปแบบที่ค่อนข้างง่าย การประมวลผลข้อมูลนี้และการตีความบางครั้งเป็นงานที่ไม่สำคัญ และ "สวนสัตว์" ของเครื่องมือและยูทิลิตี้สามารถสร้างความสับสนให้กับ DBA ขั้นสูงได้อย่างง่ายดาย
สวัสดีตอนบ่าย ฉันชื่ออเล็กซี่ อย่างที่ Ilya พูด ฉันจะพูดถึงสถิติของ PostgreSQL
สถิติกิจกรรม PostgreSQL PostgreSQL มีสองสถิติ สถิติกิจกรรมที่จะกล่าวถึง และสถิติตัวกำหนดตารางเวลาเกี่ยวกับการกระจายข้อมูล ฉันจะพูดเฉพาะเกี่ยวกับสถิติกิจกรรมของ PostgreSQL ซึ่งทำให้เราสามารถตัดสินประสิทธิภาพและปรับปรุงได้
ฉันจะบอกคุณถึงวิธีการใช้สถิติอย่างมีประสิทธิภาพเพื่อแก้ปัญหาต่าง ๆ ที่คุณมีหรืออาจมี
อะไรจะไม่อยู่ในรายงาน? ในรายงานนี้ฉันจะไม่แตะต้องสถิติของตัวกำหนดตารางเวลาเพราะ นี่เป็นหัวข้อแยกต่างหากสำหรับรายงานแยกต่างหากเกี่ยวกับวิธีการจัดเก็บข้อมูลในฐานข้อมูลและวิธีที่ผู้วางแผนแบบสอบถามได้รับแนวคิดเกี่ยวกับลักษณะเชิงคุณภาพและเชิงปริมาณของข้อมูลนี้
และจะไม่มีการตรวจสอบเครื่องมือ ฉันจะไม่เปรียบเทียบผลิตภัณฑ์หนึ่งกับผลิตภัณฑ์อื่น จะไม่มีการโฆษณา ขอวางสิ่งนี้
ฉันต้องการแสดงให้คุณเห็นว่าการใช้สถิติมีประโยชน์ มันจำเป็น. ใช้มันอย่างไม่เกรงกลัว สิ่งที่เราต้องการคือ SQL ธรรมดาและความรู้พื้นฐานเกี่ยวกับ SQL
และเราจะพูดถึงสถิติที่จะเลือกใช้ในการแก้ปัญหา
ถ้าเราดู PostgreSQL และรันคำสั่งบนระบบปฏิบัติการเพื่อดูกระบวนการ เราจะเห็น "กล่องดำ" เราจะเห็นกระบวนการบางอย่างที่ทำอะไรบางอย่าง และจากชื่อ เราสามารถจินตนาการได้คร่าวๆ ว่าพวกเขากำลังทำอะไรอยู่ที่นั่น กำลังทำอะไรอยู่ แต่ความจริงแล้วนี่คือกล่องดำ เรามองเข้าไปข้างในไม่ได้
เราสามารถดูที่โหลดของ CPU topเราสามารถเห็นการใช้หน่วยความจำโดยยูทิลิตี้ระบบบางอย่าง แต่เราจะไม่สามารถดูภายใน PostgreSQL ได้ สำหรับสิ่งนี้เราต้องการเครื่องมืออื่น ๆ
และต่อไปฉันจะบอกคุณว่าเวลาที่ใช้ไป หากเรานำเสนอ PostgreSQL ในรูปแบบของโครงร่างดังกล่าว ก็จะสามารถตอบได้ว่าเวลาที่ใช้ไปนั้นอยู่ที่ไหน มีสองสิ่งนี้: เป็นการประมวลผลคำขอของไคลเอนต์จากแอปพลิเคชันและงานเบื้องหลังที่ PostgreSQL ดำเนินการเพื่อให้ทำงานต่อไป
หากเราเริ่มดูที่มุมซ้ายบน เราจะเห็นการประมวลผลคำขอของลูกค้า คำขอมาจากแอปพลิเคชันและเปิดเซสชันไคลเอ็นต์สำหรับการทำงานเพิ่มเติม คำขอถูกส่งไปยังตัวกำหนดตารางเวลา ผู้วางแผนสร้างแผนแบบสอบถาม ส่งต่อไปเพื่อดำเนินการ มีข้อมูลบล็อก I / O บางชนิดที่เกี่ยวข้องกับตารางและดัชนี ข้อมูลที่จำเป็นจะถูกอ่านจากดิสก์ไปยังหน่วยความจำในพื้นที่พิเศษที่เรียกว่า "บัฟเฟอร์ที่ใช้ร่วมกัน" ผลการสืบค้น หากมีการอัปเดต ลบ จะถูกบันทึกไว้ในบันทึกการทำธุรกรรมใน WAL ข้อมูลสถิติบางส่วนจะเข้าสู่ล็อกหรือตัวเก็บสถิติ และผลลัพธ์ของคำขอจะถูกส่งกลับไปยังลูกค้า หลังจากนั้นลูกค้าสามารถทำซ้ำทุกอย่างด้วยคำขอใหม่
เรามีงานพื้นหลังและกระบวนการพื้นหลังอะไรบ้าง เรามีกระบวนการหลายอย่างที่ช่วยให้ฐานข้อมูลทำงานในโหมดการทำงานปกติ กระบวนการเหล่านี้จะถูกกล่าวถึงในรายงานด้วย: กระบวนการเหล่านี้คือ autovacuum, เช็คพอยเตอร์, กระบวนการที่เกี่ยวข้องกับการจำลองแบบ, ผู้เขียนพื้นหลัง ฉันจะแตะแต่ละรายการเมื่อฉันรายงาน
ปัญหาเกี่ยวกับสถิติคืออะไร?
- ข้อมูลมากมาย PostgreSQL 9.4 มี 109 เมตริกสำหรับการดูข้อมูลสถิติ อย่างไรก็ตาม หากฐานข้อมูลจัดเก็บตาราง สคีมา ฐานข้อมูลจำนวนมาก ตัวชี้วัดทั้งหมดเหล่านี้จะต้องคูณด้วยจำนวนตารางฐานข้อมูลที่สอดคล้องกัน นั่นคือมีข้อมูลมากยิ่งขึ้น และง่ายมากที่จะจมน้ำตาย
- ปัญหาต่อไปคือสถิติแสดงด้วยตัวนับ หากเราดูสถิติเหล่านี้ เราจะเห็นตัวนับที่เพิ่มขึ้นอย่างต่อเนื่อง และหากเวลาผ่านไปนานนับตั้งแต่สถิติถูกรีเซ็ต เราจะเห็นค่าหลายพันล้านค่า และพวกเขาไม่บอกอะไรเราเลย
- ไม่มีประวัติ หากคุณประสบความล้มเหลว มีบางอย่างตกลงมาเมื่อ 15-30 นาทีที่แล้ว คุณจะไม่สามารถใช้สถิติและดูว่าเกิดอะไรขึ้นเมื่อ 15-30 นาทีที่แล้ว นี่คือปัญหา
- การขาดเครื่องมือที่สร้างขึ้นใน PostgreSQL เป็นปัญหา ผู้พัฒนาเคอร์เนลไม่มียูทิลิตี้ใดๆ พวกเขาไม่มีอะไรแบบนั้น พวกเขาเพียงแค่ให้สถิติในฐานข้อมูล ใช้มัน ร้องขอสิ่งที่คุณต้องการจากนั้นทำมัน
- เนื่องจากไม่มีเครื่องมือใน PostgreSQL จึงทำให้เกิดปัญหาอื่น เครื่องมือของบุคคลที่สามมากมาย ทุกบริษัทที่มีมือโดยตรงไม่มากก็น้อยพยายามเขียนโปรแกรมของตัวเอง และเป็นผลให้ชุมชนมีเครื่องมือมากมายที่คุณสามารถใช้เพื่อทำงานกับสถิติได้ และในบางเครื่องมือมีคุณลักษณะบางอย่าง ในเครื่องมืออื่นไม่มีคุณลักษณะอื่น หรือมีคุณลักษณะใหม่บางอย่าง และเกิดสถานการณ์ขึ้นว่าคุณต้องใช้เครื่องมือสองหรือสามหรือสี่อย่างที่ทับซ้อนกันและมีฟังก์ชันต่างกัน สิ่งนี้ไม่เป็นที่พอใจมาก
อะไรต่อจากนี้? สิ่งสำคัญคือต้องสามารถรับสถิติได้โดยตรง เพื่อไม่ให้ขึ้นอยู่กับโปรแกรม หรือปรับปรุงโปรแกรมเหล่านี้ด้วยตัวคุณเอง: เพิ่มฟังก์ชันบางอย่างเพื่อรับผลประโยชน์ของคุณ
และคุณต้องการความรู้พื้นฐานของ SQL ในการรับข้อมูลบางอย่างจากสถิติ คุณต้องสร้างแบบสอบถาม SQL เช่น คุณต้องรู้ว่าการเลือก การรวม เกิดขึ้นได้อย่างไร
สถิติบอกเราหลายอย่าง พวกเขาสามารถแบ่งออกเป็นหมวดหมู่
- ประเภทแรกคือเหตุการณ์ที่เกิดขึ้นในฐานข้อมูล นี่คือเมื่อเหตุการณ์บางอย่างเกิดขึ้นในฐานข้อมูล: คิวรี, การเข้าถึงตาราง, autovacuum, คอมมิต จากนั้นเหตุการณ์เหล่านี้ทั้งหมด ตัวนับที่สอดคล้องกับเหตุการณ์เหล่านี้จะเพิ่มขึ้น และเราสามารถติดตามเหตุการณ์เหล่านี้ได้
- ประเภทที่สองคือคุณสมบัติของวัตถุ เช่น ตาราง ฐานข้อมูล พวกเขามีคุณสมบัติ นี่คือขนาดของตาราง เราสามารถติดตามการเติบโตของตารางการเติบโตของดัชนี เราสามารถเห็นการเปลี่ยนแปลงไดนามิก
- และประเภทที่สามคือเวลาที่ใช้ในงาน คำขอเป็นเหตุการณ์ มีการวัดระยะเวลาเฉพาะของตัวเอง เริ่มต้นที่นี่ สิ้นสุดที่นี่ เราสามารถติดตามได้ ทั้งเวลาในการอ่านบล็อกจากดิสก์หรือการเขียน สิ่งเหล่านี้ยังติดตาม
ที่มาของสถิติมีดังนี้
- ในหน่วยความจำที่ใช้ร่วมกัน (บัฟเฟอร์ที่ใช้ร่วมกัน) มีเซ็กเมนต์สำหรับวางข้อมูลคงที่ นอกจากนี้ยังมีตัวนับที่เพิ่มขึ้นอย่างต่อเนื่องเมื่อมีเหตุการณ์บางอย่างเกิดขึ้น หรือบางช่วงเวลาเกิดขึ้นในการทำงานของฐานข้อมูล
- ตัวนับทั้งหมดเหล่านี้ไม่พร้อมใช้งานสำหรับผู้ใช้และไม่พร้อมใช้งานสำหรับผู้ดูแลระบบ สิ่งเหล่านี้เป็นของระดับต่ำ ในการเข้าถึง PostgreSQL มีอินเทอร์เฟซในรูปแบบของฟังก์ชัน SQL เราสามารถเลือกได้โดยใช้ฟังก์ชันเหล่านี้และรับเมตริกบางประเภท (หรือชุดของเมตริก)
- อย่างไรก็ตาม การใช้ฟังก์ชันเหล่านี้อาจไม่สะดวกเสมอไป ดังนั้นฟังก์ชันจึงเป็นพื้นฐานสำหรับมุมมอง (VIEW) ตารางเหล่านี้เป็นตารางเสมือนที่แสดงสถิติในระบบย่อยเฉพาะ หรือในชุดเหตุการณ์บางเหตุการณ์ในฐานข้อมูล
- มุมมองในตัวเหล่านี้ (VIEWs) เป็นส่วนติดต่อผู้ใช้หลักสำหรับการทำงานกับสถิติ พร้อมใช้งานตามค่าเริ่มต้นโดยไม่มีการตั้งค่าเพิ่มเติม คุณสามารถใช้ ดู รับข้อมูลจากที่นั่นได้ทันที และยังมีส่วนร่วมอีกด้วย เนื้อหาเป็นทางการ คุณสามารถติดตั้งแพ็คเกจ postgresql-contrib (เช่น postgresql94-contrib) โหลดโมดูลที่จำเป็นในการกำหนดค่า ระบุพารามิเตอร์สำหรับโมดูล รีสตาร์ท PostgreSQL และคุณสามารถใช้งานได้ (บันทึก. ขึ้นอยู่กับการกระจาย ใน contrib เวอร์ชันล่าสุด แพ็คเกจเป็นส่วนหนึ่งของแพ็คเกจหลัก).
- และมีส่วนสนับสนุนอย่างไม่เป็นทางการ ไม่ได้มาพร้อมกับการกระจาย PostgreSQL มาตรฐาน จะต้องรวบรวมหรือติดตั้งเป็นไลบรารี ตัวเลือกอาจแตกต่างกันมากขึ้นอยู่กับว่าผู้พัฒนาผลงานที่ไม่เป็นทางการนี้คิดขึ้นมาอย่างไร
สไลด์นี้แสดงมุมมองเหล่านั้นทั้งหมด (VIEWs) และบางฟังก์ชันที่มีอยู่ใน PostgreSQL 9.4 อย่างที่เราเห็นมีจำนวนมาก และค่อนข้างง่ายที่จะสับสนหากคุณเพิ่งเคยเจอเป็นครั้งแรก
แต่ถ้าเราถ่ายภาพก่อนหน้านี้ Как тратится время на PostgreSQL และเข้ากับรายการนี้เราได้ภาพนี้ แต่ละมุมมอง (VIEWs) หรือแต่ละฟังก์ชัน เราสามารถใช้เพื่อจุดประสงค์ใดจุดประสงค์หนึ่งเพื่อรับสถิติที่เหมาะสมเมื่อเราเรียกใช้ PostgreSQL และเราสามารถรับข้อมูลเกี่ยวกับการทำงานของระบบย่อยได้แล้ว
สิ่งแรกที่เราจะดูคือ pg_stat_database. อย่างที่เราเห็นนี่เป็นตัวแทน มันมีข้อมูลมากมาย ข้อมูลที่หลากหลายที่สุด และให้ความรู้ที่มีประโยชน์มากเกี่ยวกับสิ่งที่เรากำลังทำอยู่ในฐานข้อมูล
เราจะเอาอะไรไปจากที่นั่นได้บ้าง? เริ่มจากสิ่งที่ง่ายที่สุดกันก่อน
select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;สิ่งแรกที่เราสามารถดูได้คือเปอร์เซ็นต์การเข้าถึงแคช เปอร์เซ็นต์การเข้าถึงแคชเป็นเมตริกที่มีประโยชน์ ช่วยให้คุณประมาณจำนวนข้อมูลที่นำมาจากแคชบัฟเฟอร์ที่ใช้ร่วมกัน และจำนวนข้อมูลที่อ่านจากดิสก์
เป็นที่ชัดเจนว่า ยิ่งเรามีแคชมากเท่าไหร่ก็ยิ่งดีเท่านั้น. เราประเมินเมตริกนี้เป็นเปอร์เซ็นต์ ตัวอย่างเช่น หากเรามีเปอร์เซ็นต์ของการเข้าถึงแคชเหล่านี้มากกว่า 90% ก็ถือว่าดี หากลดลงต่ำกว่า 90% แสดงว่าเรามีหน่วยความจำไม่เพียงพอที่จะเก็บข้อมูลหัวร้อนไว้ในหน่วยความจำ และเพื่อใช้ข้อมูลนี้ PostgreSQL ถูกบังคับให้เข้าถึงดิสก์ ซึ่งจะช้ากว่าการอ่านข้อมูลจากหน่วยความจำ และคุณต้องคิดถึงการเพิ่มหน่วยความจำ: เพิ่มบัฟเฟอร์ที่ใช้ร่วมกันหรือเพิ่มหน่วยความจำเหล็ก (RAM)
select
datname,
(xact_commit*100)/(xact_commit+xact_rollback) as c_ratio,
deadlocks, conflicts,
temp_file, pg_size_pretty(temp_bytes) as temp_size
from pg_stat_database;มีอะไรอีกบ้างจากการแสดงนี้? คุณสามารถดูความผิดปกติที่เกิดขึ้นในฐานข้อมูล สิ่งที่แสดงที่นี่? มีการคอมมิต การย้อนกลับ การสร้างไฟล์ชั่วคราว ขนาด การหยุดชะงัก และความขัดแย้ง
เราสามารถใช้คำขอนี้ SQL นี้ค่อนข้างเรียบง่าย และเราสามารถดูข้อมูลนี้ได้ด้วยตนเอง
และนี่คือค่าเกณฑ์ เราดูที่อัตราส่วนของการคอมมิตและการย้อนกลับ Commits เป็นการยืนยันธุรกรรมที่สำเร็จ การย้อนกลับคือการย้อนกลับ กล่าวคือ ธุรกรรมทำงานบางอย่าง ทำให้ฐานข้อมูลตึงเครียด พิจารณาบางอย่างแล้วเกิดความล้มเหลวขึ้น และผลลัพธ์ของธุรกรรมจะถูกละทิ้ง เช่น. จำนวนการย้อนกลับที่เพิ่มขึ้นอย่างต่อเนื่องนั้นไม่ดี และคุณควรหลีกเลี่ยงและแก้ไขรหัสเพื่อไม่ให้สิ่งนี้เกิดขึ้น
ความขัดแย้งเกี่ยวข้องกับการจำลองแบบ และควรหลีกเลี่ยงด้วย หากคุณมีคำถามบางอย่างที่ดำเนินการกับแบบจำลองและมีข้อขัดแย้งเกิดขึ้น คุณต้องวิเคราะห์ข้อขัดแย้งเหล่านี้และดูว่าเกิดอะไรขึ้น สามารถดูรายละเอียดได้ในบันทึก และแก้ไขข้อขัดแย้งเพื่อให้คำขอแอปพลิเคชันทำงานได้โดยไม่มีข้อผิดพลาด
การหยุดชะงักยังเป็นสถานการณ์ที่เลวร้าย เมื่อคำขอแข่งขันกันเพื่อแย่งชิงทรัพยากร คำขอหนึ่งเข้าถึงทรัพยากรหนึ่งและล็อก คำขอที่สองเข้าถึงทรัพยากรที่สองและล็อกด้วย จากนั้นคำขอทั้งสองเข้าถึงทรัพยากรของกันและกันและบล็อกการรอให้เพื่อนบ้านปลดล็อก นี่เป็นสถานการณ์ที่มีปัญหาเช่นกัน สิ่งเหล่านี้จำเป็นต้องได้รับการแก้ไขที่ระดับของการเขียนแอปพลิเคชันใหม่และการเข้าถึงทรัพยากรให้เป็นอนุกรม และหากคุณเห็นว่าการหยุดชะงักของคุณเพิ่มขึ้นอย่างต่อเนื่อง คุณต้องดูรายละเอียดในบันทึก วิเคราะห์สถานการณ์ที่เกิดขึ้นและดูว่าปัญหาคืออะไร
ไฟล์ชั่วคราว (temp_files) ก็ไม่ดีเช่นกัน เมื่อคำขอของผู้ใช้มีหน่วยความจำไม่เพียงพอที่จะรองรับข้อมูลชั่วคราวที่ใช้งานได้ ระบบจะสร้างไฟล์บนดิสก์ และการดำเนินการทั้งหมดที่เขาสามารถทำได้ในบัฟเฟอร์ชั่วคราวในหน่วยความจำเขาก็เริ่มดำเนินการบนดิสก์แล้ว มันช้า สิ่งนี้จะเพิ่มเวลาดำเนินการแบบสอบถาม และไคลเอนต์ที่ส่งคำขอไปยัง PostgreSQL จะได้รับการตอบกลับในภายหลัง หากดำเนินการทั้งหมดนี้ในหน่วยความจำ Postgres จะตอบสนองเร็วขึ้นมากและไคลเอนต์จะรอน้อยลง
pg_stat_bgwriter - มุมมองนี้อธิบายการทำงานของระบบย่อยพื้นหลัง PostgreSQL สองระบบ: checkpointer и background writer.
เริ่มต้นด้วยการวิเคราะห์จุดควบคุมที่เรียกว่า checkpoints. จุดตรวจคืออะไร? จุดตรวจสอบคือตำแหน่งในบันทึกธุรกรรมที่บ่งชี้ว่าการเปลี่ยนแปลงข้อมูลทั้งหมดที่เกิดขึ้นในบันทึกได้รับการซิงโครไนซ์กับข้อมูลบนดิสก์เรียบร้อยแล้ว กระบวนการนี้อาจใช้เวลานานและส่วนใหญ่ประกอบด้วยการซิงโครไนซ์หน้าสกปรกในบัฟเฟอร์ที่ใช้ร่วมกันกับไฟล์ข้อมูลบนดิสก์ ทั้งนี้ขึ้นอยู่กับปริมาณงานและการตั้งค่า มีไว้เพื่ออะไร? หาก PostgreSQL เข้าถึงดิสก์ตลอดเวลาและรับข้อมูลจากที่นั่น และเขียนข้อมูลในการเข้าถึงแต่ละครั้ง มันจะช้า ดังนั้น PostgreSQL จึงมีส่วนหน่วยความจำซึ่งขนาดขึ้นอยู่กับพารามิเตอร์ในการกำหนดค่า Postgres จัดสรรข้อมูลการดำเนินงานในหน่วยความจำนี้สำหรับการประมวลผลหรือสอบถามเพิ่มเติม ในกรณีของคำขอเปลี่ยนแปลงข้อมูล จะมีการเปลี่ยนแปลง และเราได้ข้อมูลสองรูปแบบ อันหนึ่งอยู่ในหน่วยความจำ อีกอันอยู่ในดิสก์ และคุณต้องซิงโครไนซ์ข้อมูลนี้เป็นระยะ เราต้องการสิ่งที่เปลี่ยนแปลงในหน่วยความจำเพื่อซิงโครไนซ์กับดิสก์ นี้ต้องมีจุดตรวจ
จุดตรวจสอบต้องผ่านบัฟเฟอร์ที่ใช้ร่วมกัน ทำเครื่องหมายหน้าสกปรกที่จำเป็นสำหรับจุดตรวจสอบ จากนั้นจะเริ่มการส่งผ่านครั้งที่สองผ่านบัฟเฟอร์ที่ใช้ร่วมกัน และหน้าที่มีเครื่องหมายจุดตรวจเขาก็ซิงโครไนซ์ไว้ให้แล้ว ดังนั้นข้อมูลจะถูกซิงโครไนซ์กับดิสก์แล้ว
จุดควบคุมมีสองประเภท ด่านหนึ่งถูกดำเนินการเมื่อหมดเวลา ด่านนี้มีประโยชน์และดี - checkpoint_timed. และมีจุดตรวจตามความต้องการ - checkpoint required. จุดตรวจสอบดังกล่าวเกิดขึ้นเมื่อเรามีบันทึกข้อมูลขนาดใหญ่มาก เราบันทึกข้อมูลธุรกรรมจำนวนมาก และ PostgreSQL เชื่อว่าจำเป็นต้องซิงโครไนซ์ทั้งหมดนี้ให้เร็วที่สุด สร้างจุดตรวจสอบและดำเนินการต่อ
และถ้าดูจากสถิติแล้ว pg_stat_bgwriter และดูสิ่งที่คุณมี Checkpoint_req มีขนาดใหญ่กว่า Checkpoint_timed มาก ซึ่งถือว่าไม่ดี ทำไมไม่ดี? ซึ่งหมายความว่า PostgreSQL อยู่ภายใต้ความเครียดอย่างต่อเนื่องเมื่อจำเป็นต้องเขียนข้อมูลลงดิสก์ จุดตรวจตามเวลาหมดเวลาจะเครียดน้อยกว่าและดำเนินการตามกำหนดการภายในและยืดออกไปตามระยะเวลา PostgreSQL มีความสามารถในการหยุดการทำงานชั่วคราวและไม่ทำให้ระบบย่อยของดิสก์เครียด สิ่งนี้มีประโยชน์สำหรับ PostgreSQL และคำขอที่ดำเนินการระหว่างจุดตรวจสอบจะไม่พบความเครียดจากการที่ระบบย่อยของดิสก์ไม่ว่าง
และมีสามพารามิเตอร์ในการปรับด่าน:
-
сheckpoint_segments. -
сheckpoint_timeout. -
сheckpoint_competion_target.
ช่วยให้คุณสามารถควบคุมการทำงานของจุดควบคุมได้ แต่ฉันจะไม่ยุ่งเกี่ยวกับพวกเขา อิทธิพลของพวกเขาเป็นปัญหาแยกต่างหาก
คำเตือน: เวอร์ชัน 9.4 ที่พิจารณาในรายงานไม่เกี่ยวข้องอีกต่อไป ใน PostgreSQL เวอร์ชันใหม่ พารามิเตอร์ checkpoint_segments แทนที่ด้วยพารามิเตอร์ min_wal_size и max_wal_size.
ระบบย่อยถัดไปคือตัวเขียนพื้นหลัง - background writer. เขากำลังทำอะไร? มันวิ่งวนเป็นวงจรไม่รู้จบ โดยจะสแกนหน้าลงในบัฟเฟอร์ที่ใช้ร่วมกันและล้างหน้าสกปรกที่พบไปยังดิสก์ ด้วยวิธีนี้จะช่วยให้ผู้ตรวจสอบทำงานน้อยลงในระหว่างการตรวจสอบ
เขาต้องการอะไรอีก มันจัดเตรียมความต้องการในการล้างเพจในบัฟเฟอร์ที่ใช้ร่วมกัน หากจำเป็นอย่างกระทันหัน (ในปริมาณมากและทันที) เพื่อรองรับข้อมูล สมมติว่าสถานการณ์เกิดขึ้นเมื่อคำขอต้องการคลีนเพจ และเพจเหล่านั้นอยู่ในบัฟเฟอร์ที่ใช้ร่วมกันแล้ว โพสต์เกรส backend เขาแค่หยิบไปใช้เองไม่ต้องทำความสะอาดอะไรเอง แต่ถ้าจู่ๆ ไม่มีหน้าดังกล่าว แบ็กเอนด์จะหยุดชั่วคราวและเริ่มค้นหาหน้าเพื่อล้างข้อมูลลงดิสก์และนำไปใช้ตามความต้องการของตนเอง ซึ่งส่งผลเสียต่อเวลาของคำขอที่กำลังดำเนินการอยู่ในปัจจุบัน หากคุณเห็นว่าคุณมีพารามิเตอร์ maxwritten_clean ใหญ่ หมายความว่าตัวเขียนพื้นหลังไม่ได้ทำงาน และคุณต้องเพิ่มพารามิเตอร์ bgwriter_lru_maxpagesเพื่อให้เขาทำงานในรอบเดียวได้มากขึ้น เคลียร์เพจได้มากขึ้น
และอีกตัวบ่งชี้ที่มีประโยชน์มากคือ buffers_backend_fsync. แบ็กเอนด์ไม่ทำ fsync เพราะมันช้า พวกเขาส่ง fsync ขึ้นไปยังตัวตรวจสอบสแต็ก IO ตัวตรวจสอบมีคิวของตัวเอง มันประมวลผล fsync เป็นระยะและซิงโครไนซ์หน้าในหน่วยความจำกับไฟล์บนดิสก์ หากคิวด่านตรวจมีขนาดใหญ่และเต็ม แบ็กเอนด์จะถูกบังคับให้ทำ fsync เอง ซึ่งจะทำให้แบ็กเอนด์ทำงานช้าลงเช่น ลูกค้าจะได้รับการตอบกลับช้ากว่าที่ควรจะเป็น หากคุณเห็นว่าคุณมีค่านี้มากกว่าศูนย์แสดงว่าเป็นปัญหาแล้ว และ คุณต้องใส่ใจกับการตั้งค่าของตัวเขียนพื้นหลังและประเมินประสิทธิภาพของระบบย่อยของดิสก์ด้วย
คำเตือน: _ข้อความต่อไปนี้อธิบายมุมมองทางสถิติที่เกี่ยวข้องกับการจำลองแบบ ชื่อมุมมองและฟังก์ชันส่วนใหญ่ถูกเปลี่ยนชื่อใน Postgres 10 สาระสำคัญของการเปลี่ยนชื่อคือการแทนที่ xlog บน wal и location บน lsn ในชื่อฟังก์ชัน/มุมมอง ฯลฯ ตัวอย่างเฉพาะฟังก์ชัน pg_xlog_location_diff() ถูกเปลี่ยนชื่อเป็น pg_wal_lsn_diff()._
ที่นี่เรามีจำนวนมากเช่นกัน แต่เราต้องการเฉพาะรายการที่เกี่ยวข้องกับสถานที่เท่านั้น
หากเราเห็นว่าค่าทั้งหมดเท่ากัน นี่เป็นสิ่งที่ดีและแบบจำลองจะไม่ล้าหลังต้นแบบ
ตำแหน่งเลขฐานสิบหกนี่คือตำแหน่งในบันทึกธุรกรรม มันเพิ่มขึ้นอย่างต่อเนื่องหากมีกิจกรรมบางอย่างในฐานข้อมูล: แทรก ลบ ฯลฯ
сколько записано xlog в байтах
$ select
pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000');
лаг репликации в байтах
$ select
client_addr,
pg_xlog_location_diff(pg_current_xlog_location(), replay_location)
from pg_stat_replication;
лаг репликации в секундах
$ select
extract(epoch from now() - pg_last_xact_replay_timestamp());หากสิ่งเหล่านี้แตกต่างแสดงว่ามีความล่าช้าบางอย่าง Lag คือความล่าช้าของแบบจำลองจากต้นแบบ กล่าวคือ ข้อมูลแตกต่างกันระหว่างเซิร์ฟเวอร์
มีสามสาเหตุของความล่าช้า:
- เป็นระบบย่อยของดิสก์ที่ไม่สามารถจัดการการเขียนการซิงค์ไฟล์ได้
- สิ่งเหล่านี้คือข้อผิดพลาดของเครือข่ายที่เป็นไปได้ หรือเครือข่ายโอเวอร์โหลด เมื่อข้อมูลไม่มีเวลาไปถึงแบบจำลองและไม่สามารถทำซ้ำได้
- และโปรเซสเซอร์ โปรเซสเซอร์เป็นเคสที่หายากมาก และฉันเคยเห็นมาแล้วสองหรือสามครั้ง แต่นั่นก็สามารถเกิดขึ้นได้เช่นกัน
และนี่คือข้อความค้นหาสามข้อที่ช่วยให้เราใช้สถิติได้ เราสามารถประเมินจำนวนเงินที่บันทึกไว้ในบันทึกการทำธุรกรรมของเรา มีฟังก์ชั่นดังกล่าว pg_xlog_location_diff และเราสามารถประเมินความล่าช้าของการจำลองเป็นไบต์และวินาที เรายังใช้ค่าจากมุมมองนี้ (VIEWs) สำหรับสิ่งนี้
หมายเหตุ: _แทนที่จะเป็น pg_xlog_locationฟังก์ชัน diff() คุณสามารถใช้ตัวดำเนินการลบและลบตำแหน่งหนึ่งออกจากตำแหน่งอื่น สะดวกสบาย.
ด้วยความล่าช้าซึ่งมีหน่วยเป็นวินาที มีช่วงเวลาหนึ่ง หากไม่มีกิจกรรมในมาสเตอร์ แสดงว่ามีการทำธุรกรรมเมื่อประมาณ 15 นาทีที่แล้ว และไม่มีกิจกรรมใดๆ และถ้าเราดูที่ความล่าช้านี้ในแบบจำลอง เราจะเห็นความล่าช้า 15 นาที นี่เป็นสิ่งที่ควรค่าแก่การจดจำ และอาจทำให้มึนงงเมื่อคุณดูความล่าช้านี้
pg_stat_all_tables เป็นอีกหนึ่งมุมมองที่มีประโยชน์ มันแสดงสถิติบนตาราง เมื่อเรามีตารางในฐานข้อมูล มีกิจกรรมบางอย่างกับมัน การกระทำบางอย่าง เราสามารถรับข้อมูลนี้จากมุมมองนี้
select
relname,
pg_size_pretty(pg_relation_size(relname::regclass)) as size,
seq_scan, seq_tup_read,
seq_scan / seq_tup_read as seq_tup_avg
from pg_stat_user_tables
where seq_tup_read > 0 order by 3,4 desc limit 5;สิ่งแรกที่เราสามารถดูได้คือการสแกนตารางตามลำดับ ตัวเลขหลังจากข้อความเหล่านี้ไม่จำเป็นต้องแย่และไม่ได้บ่งบอกว่าเราต้องทำอะไรบางอย่างแล้ว
อย่างไรก็ตาม มีเมตริกที่สอง - seq_tup_read นี่คือจำนวนแถวที่ส่งคืนจากการสแกนตามลำดับ หากจำนวนเฉลี่ยเกิน 1, 000, 10, 000 นี่เป็นตัวบ่งชี้อยู่แล้วว่าคุณอาจต้องสร้างดัชนีที่ไหนสักแห่งเพื่อให้การเข้าถึงเป็นไปตามดัชนี หรือเป็นไปได้ที่จะเพิ่มประสิทธิภาพการค้นหาที่ใช้การสแกนตามลำดับดังกล่าว สิ่งนี้จะไม่เกิดขึ้น คือ
ตัวอย่างง่ายๆ สมมติว่าคำขอที่มีค่า OFFSET และ LIMIT มากนั้นคุ้มค่า ตัวอย่างเช่น 100 แถวในตารางจะถูกสแกน และหลังจากนั้น 000 แถวที่ต้องการจะถูกสแกน และแถวที่สแกนก่อนหน้านี้จะถูกยกเลิก นี่เป็นกรณีที่ไม่ดีเช่นกัน และคำขอดังกล่าวจำเป็นต้องได้รับการปรับให้เหมาะสม และนี่คือแบบสอบถาม SQL อย่างง่ายที่คุณสามารถดูและประเมินตัวเลขที่ได้รับ
select
relname,
pg_size_pretty(pg_total_relation_size(relname::regclass)) as
full_size,
pg_size_pretty(pg_relation_size(relname::regclass)) as
table_size,
pg_size_pretty(pg_total_relation_size(relname::regclass) -
pg_relation_size(relname::regclass)) as index_size
from pg_stat_user_tables
order by pg_total_relation_size(relname::regclass) desc limit 10;สามารถรับขนาดตารางได้โดยใช้ตารางนี้และใช้ฟังก์ชันเพิ่มเติม pg_total_relation_size(), pg_relation_size().
โดยทั่วไปมี metacommands dt и diซึ่งคุณสามารถใช้ใน PSQL และดูขนาดตารางและดัชนีได้ด้วย
อย่างไรก็ตาม การใช้ฟังก์ชันช่วยให้เราดูขนาดของตารางได้ แม้จะคำนึงถึงดัชนีหรือไม่คำนึงถึงดัชนีก็ตาม และทำการประมาณการบางส่วนตามการเติบโตของฐานข้อมูลแล้ว เช่น เติบโตไปกับเราอย่างไร ด้วย ความเข้มเท่าใดและได้ข้อสรุปเกี่ยวกับการปรับขนาดให้เหมาะสมแล้ว
กิจกรรมเขียน. บันทึกคืออะไร? ลองดูที่การดำเนินการ UPDATE – การดำเนินการปรับปรุงแถวในตาราง อันที่จริงแล้ว การอัปเดตคือสองการดำเนินการ (หรือมากกว่านั้น) นี่เป็นการแทรกเวอร์ชันแถวใหม่และทำเครื่องหมายเวอร์ชันแถวเก่าว่าล้าสมัย ในภายหลัง autovacuum จะมาและกำจัดบรรทัดที่ล้าสมัยเหล่านี้ ทำเครื่องหมายสถานที่นี้ว่าพร้อมใช้งานสำหรับใช้ซ้ำ
นอกจากนี้ การอัปเดตไม่ได้เป็นเพียงการอัปเดตตารางเท่านั้น มันยังคงเป็นการปรับปรุงดัชนี หากคุณมีดัชนีจำนวนมากในตาราง เมื่อมีการอัปเดต ดัชนีทั้งหมดที่ฟิลด์ที่อัปเดตในแบบสอบถามเข้าร่วมจะต้องได้รับการอัปเดตด้วย ดัชนีเหล่านี้จะมีเวอร์ชันแถวที่ล้าสมัยซึ่งจำเป็นต้องล้างข้อมูลด้วย
select
s.relname,
pg_size_pretty(pg_relation_size(relid)),
coalesce(n_tup_ins,0) + 2 * coalesce(n_tup_upd,0) -
coalesce(n_tup_hot_upd,0) + coalesce(n_tup_del,0) AS total_writes,
(coalesce(n_tup_hot_upd,0)::float * 100 / (case when n_tup_upd > 0
then n_tup_upd else 1 end)::float)::numeric(10,2) AS hot_rate,
(select v[1] FROM regexp_matches(reloptions::text,E'fillfactor=(\d+)') as
r(v) limit 1) AS fillfactor
from pg_stat_all_tables s
join pg_class c ON c.oid=relid
order by total_writes desc limit 50;และเนื่องจากการออกแบบ UPDATE จึงเป็นการทำงานที่มีน้ำหนักมาก แต่สามารถทำได้ง่ายขึ้น กิน hot updates. ปรากฏใน PostgreSQL เวอร์ชัน 8.3 และนี่คืออะไร? นี่คือการอัปเดตที่มีน้ำหนักเบาซึ่งไม่ก่อให้เกิดการสร้างดัชนีใหม่ นั่นคือ เราได้อัปเดตเรกคอร์ด แต่เฉพาะเรกคอร์ดในเพจ (ซึ่งเป็นของตาราง) เท่านั้นที่ได้รับการอัพเดต และดัชนียังคงชี้ไปที่เรกคอร์ดเดียวกันในเพจ มีตรรกะที่น่าสนใจเล็กๆ น้อยๆ ในการทำงาน เมื่อสุญญากาศเกิดขึ้น มันก็มีโซ่เหล่านี้ hot สร้างใหม่และทุกอย่างยังคงทำงานต่อไปโดยไม่ต้องอัปเดตดัชนี และทุกอย่างเกิดขึ้นโดยสิ้นเปลืองทรัพยากรน้อยลง
และเมื่อคุณมี n_tup_hot_upd ใหญ่ มันดีมาก ซึ่งหมายความว่าการอัปเดตที่มีน้ำหนักเบาจะมีผลเหนือกว่าและถูกกว่าสำหรับเราในแง่ของทรัพยากร และทุกอย่างเรียบร้อยดี
ALTER TABLE table_name SET (fillfactor = 70);วิธีเพิ่มปริมาณ hot updateไข่? เราสามารถใช้ fillfactor. กำหนดขนาดของพื้นที่ว่างที่สงวนไว้เมื่อกรอกหน้าในตารางโดยใช้ INSERT เมื่อแทรกไปที่ตารางจะเติมหน้าให้เต็มอย่าเว้นที่ว่างไว้ จากนั้นหน้าใหม่จะถูกเน้น ข้อมูลถูกกรอกอีกครั้ง และนี่คือพฤติกรรมเริ่มต้น ปัจจัยเติมเต็ม = 100%
เราสามารถตั้งค่าปัจจัยเติมเต็มเป็น 70% นั่นคือหน้าใหม่ถูกจัดสรรด้วยการแทรก แต่เติมเพียง 70% ของหน้าเท่านั้น และเรามีสำรองไว้ 30% เมื่อคุณต้องทำการอัปเดต มักจะเกิดขึ้นในหน้าเดียวกัน และแถวเวอร์ชันใหม่จะพอดีกับหน้าเดียวกัน และ hot_update จะเสร็จสิ้น ทำให้ง่ายต่อการเขียนบนโต๊ะ
select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));คิวดูดฝุ่นอัตโนมัติ Autovacuum เป็นระบบย่อยที่มีสถิติน้อยมากใน PostgreSQL เราเห็นได้เฉพาะในตารางใน pg_stat_activity ว่าเรามีเครื่องดูดฝุ่นกี่ตัวในขณะนี้ อย่างไรก็ตาม เป็นเรื่องยากมากที่จะเข้าใจว่ามีกี่โต๊ะในคิวที่กำลังเดินทาง
หมายเหตุ: _ ตั้งแต่ Postgres 10 สถานการณ์การติดตามสูญญากาศดีขึ้นมาก - มุมมอง pg_stat_progress ปรากฏขึ้นสูญญากาศซึ่งช่วยลดความยุ่งยากในการตรวจสอบสูญญากาศโดยอัตโนมัติ
เราสามารถใช้แบบสอบถามแบบง่ายนี้ และเราสามารถเห็นได้ว่าเมื่อใดควรทำสุญญากาศ แต่สูญญากาศควรเริ่มต้นอย่างไรและเมื่อไหร่? นี่คือสตริงเวอร์ชันเก่าที่ฉันพูดถึงก่อนหน้านี้ มีการอัปเดต มีการแทรกเวอร์ชันใหม่ของแถวแล้ว สตริงเวอร์ชันล้าสมัยปรากฏขึ้น โต๊ะ pg_stat_user_tables มีพารามิเตอร์ดังกล่าว n_dead_tup. แสดงจำนวนแถวที่ "ตาย" และทันทีที่จำนวนแถวที่ตายแล้วมีมากกว่าเกณฑ์ที่กำหนด เครื่องดูดฝุ่นอัตโนมัติจะมาถึงโต๊ะ
และเกณฑ์นี้คำนวณอย่างไร? นี่คือเปอร์เซ็นต์ที่เฉพาะเจาะจงมากของจำนวนแถวทั้งหมดในตาราง มีพารามิเตอร์ autovacuum_vacuum_scale_factor. เป็นการกำหนดเปอร์เซ็นต์ สมมติว่า 10% + มีเกณฑ์ฐานเพิ่มเติม 50 บรรทัด และจะเกิดอะไรขึ้น? เมื่อเรามีแถวที่ตายแล้วมากกว่า "10% + 50" ของแถวทั้งหมดในตาราง เราจะวางตารางไว้บนเครื่องดูดฝุ่นอัตโนมัติ
select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));อย่างไรก็ตามมีจุดหนึ่ง เกณฑ์พื้นฐานสำหรับพารามิเตอร์ av_base_thresh и av_scale_factor อาจได้รับมอบหมายเป็นรายบุคคล ดังนั้นเกณฑ์จะไม่เป็นสากล แต่เป็นรายบุคคลสำหรับตาราง ดังนั้นในการคำนวณคุณต้องใช้กลอุบายและลูกเล่น และหากคุณสนใจ คุณสามารถดูประสบการณ์ของเพื่อนร่วมงานของเราได้จาก Avito (ลิงก์บนสไลด์ไม่ถูกต้องและได้รับการอัปเดตเป็นข้อความ)
พวกเขาเขียนเพื่อ ที่คำนึงถึงสิ่งเหล่านี้ มีผ้าเช็ดเท้าสองแผ่น แต่เขาคิดอย่างถูกต้องและมีประสิทธิภาพช่วยให้เราสามารถประเมินว่าเราต้องการสุญญากาศมากสำหรับโต๊ะที่มีน้อย
เราจะทำอย่างไรกับมัน? หากคิวยาวและเครื่องดูดฝุ่นอัตโนมัติไม่สามารถรับมือได้ เราสามารถเพิ่มจำนวนพนักงานดูดฝุ่นหรือทำให้เครื่องดูดฝุ่นลุกลามมากขึ้นเพื่อให้ทำงานเร็วขึ้น ประมวลผลตารางเป็นชิ้นเล็กๆ และดังนั้นคิวจะลดลง - สิ่งสำคัญที่นี่คือการตรวจสอบการโหลดบนดิสก์เพราะ สิ่งที่เป็นสุญญากาศนั้นไม่ฟรีแม้ว่าจะมีอุปกรณ์ SSD / NVMe เกิดขึ้น แต่ปัญหาก็สังเกตเห็นได้น้อยลง
pg_stat_all_indexes เป็นสถิติเกี่ยวกับดัชนี เธอไม่ใหญ่ และเราสามารถรับข้อมูลเกี่ยวกับการใช้ดัชนีได้จากมัน ตัวอย่างเช่น เราสามารถกำหนดดัชนีที่เรามีเพิ่มเติมได้
อย่างที่ฉันพูดไปแล้ว การอัปเดตไม่ได้เป็นเพียงการอัปเดตตารางเท่านั้น แต่ยังเป็นการอัปเดตดัชนีด้วย ดังนั้นหากเรามีดัชนีจำนวนมากในตาราง เมื่ออัปเดตแถวในตาราง ดัชนีของฟิลด์ที่จัดทำดัชนีก็จำเป็นต้องได้รับการอัปเดตเช่นกัน และ หากเรามีดัชนีที่ไม่ได้ใช้ซึ่งไม่มีการสแกนดัชนีก็จะแขวนกับเราเหมือนอับเฉา และคุณต้องกำจัดพวกมัน สำหรับสิ่งนี้เราต้องการฟิลด์ idx_scan. เราเพียงแค่ดูที่จำนวนของการสแกนดัชนี หากดัชนีมีการสแกนเป็นศูนย์ในช่วงเวลาที่ค่อนข้างนานของการจัดเก็บสถิติ (อย่างน้อย 2-3 สัปดาห์) เป็นไปได้มากว่าสิ่งเหล่านี้คือดัชนีที่ไม่ดี เราจำเป็นต้องกำจัดทิ้ง
หมายเหตุ: เมื่อค้นหาดัชนีที่ไม่ได้ใช้ในกรณีของคลัสเตอร์การจำลองแบบสตรีม คุณต้องตรวจสอบโหนดทั้งหมดของคลัสเตอร์ เนื่องจาก สถิติไม่ใช่ส่วนกลางและหากไม่ได้ใช้ดัชนีกับต้นแบบ ก็จะสามารถใช้กับแบบจำลองได้ (หากมีการโหลด)
สองลิงค์:
นี่คือตัวอย่างข้อความค้นหาขั้นสูงเพิ่มเติมสำหรับวิธีค้นหาดัชนีที่ไม่ได้ใช้
ลิงค์ที่สองเป็นแบบสอบถามที่ค่อนข้างน่าสนใจ มีตรรกะที่ไม่สำคัญอยู่ในนั้น ฉันแนะนำให้ทบทวน
ดัชนีควรสรุปอะไรอีกบ้าง
-
ดัชนีที่ไม่ได้ใช้นั้นไม่ดี
-
พวกเขาใช้พื้นที่
-
ทำให้การดำเนินการอัปเดตช้าลง
-
งานพิเศษสำหรับเครื่องดูดฝุ่น
หากเราลบดัชนีที่ไม่ได้ใช้ เราก็จะทำให้ฐานข้อมูลดีขึ้นเท่านั้น
มุมมองต่อไปคือ pg_stat_activity. นี่คืออะนาล็อกของยูทิลิตี้ psเฉพาะใน PostgreSQL ถ้า ps'โอ้ คุณดูกระบวนการในระบบปฏิบัติการแล้ว pg_stat_activity จะแสดงกิจกรรมภายใน PostgreSQL
เราจะเอาอะไรไปจากที่นั่นได้บ้าง?
select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;เราสามารถเห็นกิจกรรมโดยรวมที่เกิดขึ้นในฐานข้อมูล เราสามารถทำการปรับใช้ใหม่ได้ ทุกอย่างระเบิดที่นั่น ไม่ยอมรับการเชื่อมต่อใหม่ เกิดข้อผิดพลาดในแอปพลิเคชัน
select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;เราสามารถเรียกใช้แบบสอบถามเช่นนี้และดูเปอร์เซ็นต์การเชื่อมต่อทั้งหมดเทียบกับขีดจำกัดการเชื่อมต่อสูงสุด และดูว่าเรามีการเชื่อมต่อกับใครมากที่สุด และในกรณีนี้ เราเห็นผู้ใช้รายนั้น cron_role เปิดการเชื่อมต่อ 508 และมีบางอย่างเกิดขึ้นกับเขา คุณต้องจัดการกับมันและดู และค่อนข้างเป็นไปได้ว่านี่คือจำนวนการเชื่อมต่อที่ผิดปกติ
หากเรามีโหลด OLTP ข้อความค้นหาควรเร็ว เร็วมาก และไม่ควรมีข้อความค้นหายาว อย่างไรก็ตามหากมีคำขอยาว ๆ ก็ไม่มีอะไรต้องกังวลในระยะสั้น แต่ ในระยะยาว การสืบค้นแบบยาวจะส่งผลเสียต่อฐานข้อมูล พวกมันจะเพิ่มผลกระทบของตารางเมื่อเกิดการแตกแฟรกเมนต์ของตาราง ต้องกำจัดทั้งข้อความค้นหาที่บวมและยาว
select
client_addr, usename, datname,
clock_timestamp() - xact_start as xact_age,
clock_timestamp() - query_start as query_age,
query
from pg_stat_activity order by xact_start, query_start;โปรดทราบ: ด้วยคำขอดังกล่าว เราสามารถกำหนดคำขอและธุรกรรมที่ยาวได้ เราใช้ฟังก์ชั่น clock_timestamp() เพื่อกำหนดเวลาในการทำงาน คำขอยาวที่เราพบ เราสามารถจดจำได้ ดำเนินการได้ explainดูแผนและปรับให้เหมาะสม เราดำเนินการตามคำขอที่ยาวในปัจจุบันและดำเนินต่อไป
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';ธุรกรรมที่ไม่ถูกต้องนั้นไม่ได้ใช้งานในธุรกรรมและไม่ได้ใช้งานในธุรกรรม (ยกเลิก)
มันหมายความว่าอะไร? ธุรกรรมมีหลายสถานะ และหนึ่งในรัฐเหล่านี้สามารถใช้เวลาใดก็ได้ มีฟิลด์สำหรับกำหนดสถานะ state ในมุมมองนี้ และเราใช้มันเพื่อกำหนดสถานะ
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';และอย่างที่ฉันได้กล่าวไปข้างต้น สถานะทั้งสองนี้ ไม่ได้ใช้งานในการทำธุรกรรมและไม่ได้ใช้งานในการทำธุรกรรม (ยกเลิก) นั้นไม่ดี มันคืออะไร? นี่คือตอนที่แอปพลิเคชันเปิดทำธุรกรรม ดำเนินการบางอย่าง และดำเนินการเกี่ยวกับธุรกิจ การทำธุรกรรมยังคงเปิดอยู่ มันหยุดทำงาน ไม่มีอะไรเกิดขึ้นในนั้น ต้องใช้การเชื่อมต่อ ล็อคแถวที่เปลี่ยนแปลง และอาจเพิ่มการขยายของตารางอื่นๆ เนื่องจากสถาปัตยกรรมของเอ็นจิ้นธุรกรรม Postrges และการทำธุรกรรมดังกล่าวควรถูกยิงเพราะโดยทั่วไปแล้วเป็นอันตรายไม่ว่าในกรณีใด ๆ
หากคุณเห็นว่าคุณมีมากกว่า 5-10-20 ในฐานข้อมูลของคุณ คุณต้องกังวลและเริ่มทำอะไรสักอย่างกับพวกเขา
ที่นี่เรายังใช้สำหรับการคำนวณเวลา clock_timestamp(). เราถ่ายทำธุรกรรม เราเพิ่มประสิทธิภาพแอปพลิเคชัน
ดังที่ฉันได้กล่าวไว้ข้างต้น การล็อกคือเมื่อธุรกรรมสองรายการขึ้นไปแข่งขันกันเพื่อแย่งชิงทรัพยากรกลุ่มหนึ่งหรือกลุ่มหนึ่ง สำหรับสิ่งนี้เรามีฟิลด์ waiting ด้วยค่าบูลีน true หรือ false.
จริง - หมายความว่ากระบวนการกำลังรออยู่ ต้องทำบางอย่าง เมื่อกระบวนการกำลังรอ ไคลเอนต์ที่เริ่มต้นกระบวนการก็รอเช่นกัน ลูกค้าในเบราว์เซอร์นั่งและรอ
คำเตือน: _เริ่มจาก Postgres 9.6 ฟิลด์ waiting ลบออกและแทนที่ด้วยช่องข้อมูลอีกสองช่อง wait_event_type и wait_event._
จะทำอย่างไร? หากคุณเห็นว่าเป็นจริงเป็นเวลานานคุณควรกำจัดคำขอดังกล่าว เราเพิ่งถ่ายทำธุรกรรมดังกล่าว เราเขียนถึงนักพัฒนาซอฟต์แวร์ว่าต้องปรับปรุงอะไรบ้างเพื่อไม่ให้เกิดการแย่งชิงทรัพยากร จากนั้นนักพัฒนาจะปรับแต่งแอปพลิเคชันเพื่อไม่ให้สิ่งนี้เกิดขึ้น
และกรณีที่รุนแรง แต่อาจไม่ถึงแก่ชีวิตก็คือ เกิดการหยุดชะงัก ธุรกรรม XNUMX รายการได้อัปเดตทรัพยากร XNUMX รายการ จากนั้นจึงเข้าถึงอีกครั้ง โดยเป็นทรัพยากรที่อยู่ตรงข้ามกันอยู่แล้ว ในกรณีนี้ PostgreSQL จะใช้และปิดการทำธุรกรรมเองเพื่อให้อีกรายการหนึ่งสามารถทำงานต่อไปได้ นี่เป็นสถานการณ์ทางตันและเธอไม่เข้าใจตัวเอง ดังนั้น PostgreSQL จึงถูกบังคับให้ใช้มาตรการที่รุนแรง
และนี่คือคำถามสองข้อที่ให้คุณติดตามการล็อกได้ เราใช้มุมมอง pg_locksซึ่งช่วยให้คุณติดตามการล็อคที่มีน้ำหนักมาก
และลิงค์แรกคือข้อความคำขอเอง มันค่อนข้างยาว
และลิงค์ที่สองคือบทความเกี่ยวกับการล็อค มันมีประโยชน์ในการอ่านมันน่าสนใจมาก
แล้วเราเห็นอะไร? เราเห็นสองคำขอ ทำธุรกรรมกับ ALTER TABLE เป็นการบล็อกธุรกรรม มันเริ่มต้นขึ้นแต่ยังไม่สิ้นสุด และแอปพลิเคชันที่โพสต์ธุรกรรมนี้กำลังทำสิ่งอื่นอยู่ที่ใดที่หนึ่ง และคำขอที่สองคือการอัปเดต มันรอให้ตารางแก้ไขเสร็จสิ้นก่อนที่จะทำงานต่อไป
นี่คือวิธีที่เราจะรู้ได้ว่าใครขังใคร ใครจับใคร และเราจะจัดการกับเรื่องนี้ต่อไป
โมดูลต่อไปคือ pg_stat_statements. อย่างที่ฉันพูดมันเป็นโมดูล หากต้องการใช้งาน คุณต้องโหลดไลบรารีในการกำหนดค่า รีสตาร์ท PostgreSQL ติดตั้งโมดูล (ด้วยคำสั่งเดียว) จากนั้นเราจะมีมุมมองใหม่
Cреднее время запроса в милисекундах
$ select (sum(total_time) / sum(calls))::numeric(6,3)
from pg_stat_statements;
Самые активно пишущие (в shared_buffers) запросы
$ select query, shared_blks_dirtied
from pg_stat_statements
where shared_blks_dirtied > 0 order by 2 desc;เราจะเอาอะไรไปจากที่นั่นได้บ้าง? หากเราพูดถึงสิ่งง่ายๆ เราสามารถใช้เวลาในการดำเนินการค้นหาโดยเฉลี่ย เวลาเพิ่มขึ้น ซึ่งหมายความว่า PostgreSQL ตอบสนองช้าและจำเป็นต้องทำบางอย่าง
เราสามารถเห็นธุรกรรมการเขียนที่ใช้งานมากที่สุดในฐานข้อมูลที่เปลี่ยนแปลงข้อมูลในบัฟเฟอร์ที่ใช้ร่วมกัน ดูว่าใครอัปเดตหรือลบข้อมูลที่นั่น
และเราสามารถดูสถิติต่างๆ สำหรับข้อความค้นหาเหล่านี้ได้
เรา pg_stat_statements ใช้ในการสร้างรายงาน เรารีเซ็ตสถิติวันละครั้ง มาสะสมกันนะครับ ก่อนรีเซ็ตสถิติในครั้งต่อไป เราสร้างรายงาน นี่คือลิงค์ไปยังรายงาน คุณสามารถรับชมได้
เรากำลังทำอะไรอยู่? เราคำนวณสถิติโดยรวมสำหรับคำขอทั้งหมด จากนั้น สำหรับการสืบค้นแต่ละครั้ง เราจะนับการมีส่วนร่วมของแต่ละคนในสถิติโดยรวมนี้
แล้วเราจะได้เห็นอะไร? เราสามารถดูเวลาการดำเนินการทั้งหมดของคำขอประเภทใดประเภทหนึ่งเทียบกับพื้นหลังของคำขออื่นๆ ทั้งหมด เราสามารถดูการใช้งาน CPU และ I/O โดยสัมพันธ์กันในภาพรวม และเพิ่มประสิทธิภาพคำขอเหล่านี้แล้ว เรากำลังสร้างข้อความค้นหายอดนิยมตามรายงานนี้ และกำลังได้รับอาหารสำหรับการคิดเกี่ยวกับสิ่งที่ควรเพิ่มประสิทธิภาพ
เรามีอะไรอยู่เบื้องหลังบ้าง? ยังมีบางงานที่ฉันไม่ได้พิจารณาเพราะเวลามีจำกัด
มี pgstattuple ยังเป็นโมดูลเพิ่มเติมจากแพ็คเกจมาตรฐาน ช่วยให้คุณสามารถประเมิน bloat ตารางที่เรียกว่า การกระจายตัวของตาราง และหากการแตกแฟรกเมนต์มีขนาดใหญ่ คุณต้องลบออก ใช้เครื่องมือต่างๆ และฟังก์ชัน pgstattuple ทำงานเป็นเวลานาน และยิ่งมีโต๊ะมากเท่าไหร่ก็ยิ่งใช้งานได้นานเท่านั้น
ผลงานต่อไปคือ pg_buffercache. ช่วยให้คุณตรวจสอบบัฟเฟอร์ที่ใช้ร่วมกัน: มีการใช้หน้าบัฟเฟอร์ของตารางอย่างเข้มข้นเพียงใดและสำหรับหน้าใด และช่วยให้คุณดูบัฟเฟอร์ที่ใช้ร่วมกันและประเมินสิ่งที่เกิดขึ้นที่นั่น
โมดูลต่อไปคือ pgfincore. ช่วยให้คุณสามารถดำเนินการตารางระดับต่ำผ่านการเรียกระบบ mincore()เช่น อนุญาตให้คุณโหลดตารางลงในบัฟเฟอร์ที่ใช้ร่วมกัน หรือยกเลิกการโหลด และช่วยให้สามารถตรวจสอบแคชของหน้าของระบบปฏิบัติการได้ กล่าวคือ ตารางใช้พื้นที่แคชของหน้าในบัฟเฟอร์ที่ใช้ร่วมกันมากน้อยเพียงใด และช่วยให้คุณประเมินภาระงานในตารางได้
โมดูลต่อไปคือ pg_stat_kcache. นอกจากนี้ยังใช้การเรียกระบบ getrusage(). และจะดำเนินการก่อนและหลังการดำเนินการตามคำขอ และในสถิติที่ได้รับจะช่วยให้เราสามารถประเมินได้ว่าคำขอของเราใช้ไปเท่าไรในดิสก์ I / O เช่นการดำเนินการกับระบบไฟล์และดูการใช้งานโปรเซสเซอร์ อย่างไรก็ตาม โมดูลยังใหม่อยู่ (khe-khe) และสำหรับการทำงานนั้น ต้องใช้ PostgreSQL 9.4 และ pg_stat_statements ซึ่งผมได้กล่าวถึงก่อนหน้านี้
-
ความสามารถในการใช้สถิติมีประโยชน์ คุณไม่จำเป็นต้องใช้ซอฟต์แวร์ของบุคคลที่สาม คุณสามารถดู ดู ทำบางสิ่งบางอย่าง แสดง
-
การใช้สถิติเป็นเรื่องง่าย มันเป็น SQL ธรรมดา คุณรวบรวมคำขอ เรียบเรียง ส่ง ดูมัน
-
สถิติช่วยตอบคำถาม หากคุณมีคำถาม คุณจะหันไปใช้สถิติ - ดู สรุปผล วิเคราะห์ผลลัพธ์
-
และทำการทดลอง คำขอจำนวนมาก ข้อมูลจำนวนมาก คุณสามารถเพิ่มประสิทธิภาพข้อความค้นหาที่มีอยู่ได้เสมอ คุณสามารถสร้างคำขอในเวอร์ชันของคุณเองที่เหมาะกับคุณมากกว่าต้นฉบับและใช้งานได้
การอ้างอิง
ลิงก์ที่ถูกต้องที่พบในบทความ ซึ่งอ้างอิงจากที่อยู่ในรายงาน
ผู้เขียนเขียนเพิ่มเติม
(ภาษาอังกฤษ)
นักสะสมสถิติ
ฟังก์ชั่นการดูแลระบบ
สนับสนุนโมดูล
ยูทิลิตี้ SQL และตัวอย่างโค้ด sql
ขอบคุณสำหรับความสนใจของคุณ!
ที่มา: will.com