การเพิ่มประสิทธิภาพของการสืบค้นฐานข้อมูลในตัวอย่างบริการ B2B สำหรับผู้สร้าง

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

ฉันกำลังให้บริการสำหรับการจัดการกระบวนการทางธุรกิจในบริษัทรับเหมาก่อสร้าง บริษัทประมาณ 3 แห่งร่วมงานกับเรา ผู้คนมากกว่า 10 คนทุกวันทำงานกับระบบของเราเป็นเวลา 4-10 ชั่วโมง ช่วยแก้ปัญหาการวางแผน การแจ้งเตือน คำเตือน การตรวจสอบความถูกต้อง... เราใช้ PostgreSQL 9.6 เรามีตารางประมาณ 300 ตารางในฐานข้อมูล และทุกวันจะได้รับคำขอมากถึง 200 ล้านคำขอ (ที่แตกต่างกัน 10 รายการ) โดยเฉลี่ยแล้วเรามี 3-4 คำขอต่อวินาที ในขณะที่มีการใช้งานมากที่สุดมากกว่า 10 คำขอต่อวินาที ข้อความค้นหาส่วนใหญ่เป็น OLAP มีการเพิ่มเติม แก้ไข และลบน้อยกว่ามาก กล่าวคือ โหลด OLTP มีขนาดค่อนข้างเล็ก ฉันให้ตัวเลขเหล่านี้ทั้งหมดเพื่อให้คุณสามารถประเมินขนาดของโครงการของเราและเข้าใจว่าประสบการณ์ของเรามีประโยชน์กับคุณอย่างไร

ภาพที่หนึ่ง โคลงสั้น ๆ

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

ภาพที่สอง ทางสถิติ

ดังนั้นเราจึงมีการค้นหาที่แตกต่างกันประมาณ 10 รายการที่ดำเนินการบนฐานข้อมูลของเราต่อวัน ในจำนวนนี้ 10 มีมอนสเตอร์ที่ถูกดำเนินการ 2-3 ล้านครั้งโดยมีเวลาดำเนินการเฉลี่ย 0.1-0.3 ms และมีคำขอที่มีเวลาดำเนินการเฉลี่ย 30 วินาทีซึ่งถูกเรียก 100 ครั้งต่อวัน

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

ข้อความค้นหายอดนิยม

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

แนวทางปฏิบัติตามปกติของทุกบริษัทคือการทำงานกับข้อความค้นหายอดนิยม มีไม่มากนัก การเพิ่มประสิทธิภาพของแบบสอบถามแม้แต่รายการเดียวสามารถเพิ่มทรัพยากรได้ 5-10% อย่างไรก็ตาม เมื่อโครงการเติบโตเต็มที่ การเพิ่มประสิทธิภาพการค้นหา TOP กลายเป็นงานที่ไม่สำคัญมากขึ้น วิธีการง่าย ๆ ทั้งหมดได้ดำเนินการไปแล้วและคำขอที่ "หนัก" ที่สุดใช้ทรัพยากร "เพียง" 3-5% หากการค้นหา TOP ใช้เวลาทั้งหมดน้อยกว่า 30-40% เป็นไปได้มากว่าคุณได้พยายามอย่างเต็มที่แล้วเพื่อให้ทำงานได้อย่างรวดเร็ว และถึงเวลาแล้วที่จะไปยังการเพิ่มประสิทธิภาพการค้นหาจากกลุ่มถัดไป
ยังคงต้องตอบคำถามเกี่ยวกับจำนวนคำขอสูงสุดที่จะรวมอยู่ในกลุ่มนี้ ฉันมักจะใช้เวลาอย่างน้อย 10 แต่ไม่เกิน 20 ฉันพยายามทำให้เวลาของคนแรกและคนสุดท้ายในกลุ่ม TOP แตกต่างกันไม่เกิน 10 ครั้ง นั่นคือหากเวลาในการดำเนินการค้นหาลดลงอย่างรวดเร็วจากอันดับ 1 เป็น 10 ฉันจะเลือก TOP-10 หากการลดลงราบรื่นขึ้น ฉันจะเพิ่มขนาดกลุ่มเป็น 15 หรือ 20

ชาวนากลาง (ปานกลาง)

ทั้งหมดนี้เป็นคำขอที่มาทันทีหลัง TOP ยกเว้น 5-10% ล่าสุด โดยปกติแล้วการเพิ่มประสิทธิภาพของแบบสอบถามเหล่านี้มีโอกาสที่จะเพิ่มประสิทธิภาพของเซิร์ฟเวอร์ได้อย่างมาก คำขอเหล่านี้สามารถ "มีน้ำหนัก" ได้มากถึง 80% แต่แม้ว่าส่วนแบ่งของพวกเขาจะเกิน 50% ก็ถึงเวลาที่จะต้องพิจารณาอย่างใกล้ชิด

หาง

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

ประเมินแต่ละกลุ่มอย่างไร?

ฉันใช้แบบสอบถาม SQL ที่ช่วยในการประเมินสำหรับ PostgreSQL (ฉันแน่ใจว่าสำหรับ DBMS อื่น ๆ อีกมากมายคุณสามารถเขียนแบบสอบถามที่คล้ายกัน)

แบบสอบถาม SQL เพื่อประเมินขนาดของกลุ่ม TOP-MEDIUM-TAIL

SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) AS sum_tail
FROM
(
  SELECT CASE WHEN rn <= 20              THEN tt_percent ELSE 0 END AS time_top,
         CASE WHEN rn > 20 AND rn <= 800 THEN tt_percent ELSE 0 END AS time_medium,
         CASE WHEN rn > 800              THEN tt_percent ELSE 0 END AS time_tail
  FROM (
    SELECT total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query,
    ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn
    FROM pg_stat_statements
    ORDER BY total_time DESC
  ) AS t
)
AS ts

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

นี่คือลักษณะที่ส่วนแบ่งของคำขอมีความสัมพันธ์กันโดยคร่าว ณ เวลาที่เริ่มต้นการเพิ่มประสิทธิภาพและตอนนี้

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

หากต้องการรับข้อความของคำขอ ให้ใช้คำขอต่อไปนี้

SELECT * FROM (
  SELECT ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn, total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query
  FROM pg_stat_statements
  ORDER BY total_time DESC
) AS T
WHERE
rn <= 20 -- TOP
-- rn > 20 AND rn <= 800 -- MEDIUM
-- rn > 800  -- TAIL

ต่อไปนี้คือรายการเคล็ดลับที่ใช้บ่อยที่สุดซึ่งช่วยให้เราเพิ่มความเร็วในการค้นหายอดนิยม:

• ตัวอย่างเช่น การออกแบบระบบใหม่ การทำงานซ้ำของลอจิกการแจ้งเตือนบนตัวจัดการข้อความแทนการสืบค้นเป็นระยะไปยังฐานข้อมูล
• เพิ่มหรือเปลี่ยนดัชนี
• เขียนแบบสอบถาม ORM ใหม่เป็น SQL แท้
• เขียนตรรกะการโหลดข้อมูลที่ขี้เกียจใหม่
• แคชผ่านการทำให้เป็นมาตรฐานของข้อมูล ตัวอย่างเช่น เรามีการเชื่อมต่อตาราง Delivery -> Invoice -> Request -> Application นั่นคือ การจัดส่งแต่ละครั้งจะเชื่อมโยงกับแอปพลิเคชันผ่านตารางอื่นๆ เพื่อไม่ให้เชื่อมโยงตารางทั้งหมดในแต่ละคำขอ เราจึงทำสำเนาลิงก์ไปยังคำสั่งซื้อในตารางการจัดส่ง
• การแคชตารางแบบสแตติกพร้อมไดเร็กทอรีและแทบไม่เปลี่ยนตารางในหน่วยความจำของโปรแกรม

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

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

• แทนที่จะตรวจสอบเรกคอร์ดโดยใช้ COUNT และการสแกนตารางแบบเต็ม ตอนนี้ใช้ EXISTS แล้ว
• เรากำจัด DISTINCT ออกไปแล้ว (ไม่มีสูตรทั่วไป แต่บางครั้งคุณสามารถกำจัดมันได้ง่ายๆ ด้วยการเร่งการค้นหา 10-100 เท่า)

  ตัวอย่างเช่น แทนที่จะใช้แบบสอบถามเพื่อเลือกไดรเวอร์ทั้งหมดจากตารางการจัดส่งขนาดใหญ่ (DELIVERY)

  SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME
  FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.ID
  

  ทำแบบสอบถามใน PERSON ตารางที่ค่อนข้างเล็ก

  SELECT P.ID, P.FIRST_NAME, P.LAST_NAME
  FROM PERSON
  WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)
  

  ดูเหมือนว่าเราใช้ข้อความค้นหาย่อยที่สัมพันธ์กัน แต่ให้ความเร็วมากกว่า 10 เท่า

• ในหลายกรณี COUNT ถูกละทิ้งทั้งหมดและ
  แทนที่ด้วยการคำนวณมูลค่าโดยประมาณ
• แทน

  UPPER(s) LIKE JOHN%’ 
  

  ใช้

  s ILIKE “John%”
  

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

ด้วยเหตุนี้ เราจึงทำงานบนฮาร์ดแวร์เดียวกันมาเป็นเวลาสามปีแล้ว โหลดรายวันเฉลี่ยประมาณ 30% โดยสูงสุดจะถึง 70% จำนวนคำขอรวมถึงจำนวนผู้ใช้เพิ่มขึ้นประมาณ 10 เท่า และทั้งหมดนี้ต้องขอบคุณการตรวจสอบอย่างต่อเนื่องของกลุ่มคำขอ TOP-MEDIUM เหล่านี้ ทันทีที่มีคำขอใหม่ปรากฏขึ้นในกลุ่ม TOP เราจะวิเคราะห์ทันทีและพยายามเร่งให้เร็วขึ้น เราตรวจสอบกลุ่ม MEDIUM สัปดาห์ละครั้งโดยใช้สคริปต์วิเคราะห์ข้อความค้นหา หากพบคำขอใหม่ที่เรารู้วิธีเพิ่มประสิทธิภาพแล้ว เราจะรีบเปลี่ยนอย่างรวดเร็ว บางครั้งเราพบวิธีการเพิ่มประสิทธิภาพแบบใหม่ที่สามารถใช้กับข้อความค้นหาหลายรายการพร้อมกันได้

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

ที่มา: will.com