한국어English日本語简体中文繁體中文DeutschไทยTiếng ViệtРусскийPortuguês (Brasil)EspañolBahasa Indonesia

คู่มือเกมแลค › L12 ฐานข้อมูล

คิวรีช้าเพราะ query plan เปลี่ยน Query plan regression (stats, parameter sniffing)

ID สาเหตุ db-plan-flip · ผู้รับผิดชอบหลัก อินฟรา DB (ทีมอินฟรา) · ร่วมกับ พัฒนาเซิร์ฟเวอร์ (ทีมพัฒนาเกม)

เปิดการ์ดในฉบับหลักที่มีภาพและการทดลอง →

โค้ดเหมือนเดิม แต่ถ้า DB เปลี่ยนวิธีประมวลผลคิวรีเดิม (query plan) คิวรีที่เมื่อวานใช้ 2 ms วันนี้อาจกลายเป็นหลายร้อย ms

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

อาการ
อินพุตดีเลย์, เข้าเกมไม่ได้/โหลดไม่จบ
ปัจจัย
ความหน่วง, การหยุดชะงัก
ใครเจอ
เฉพาะบางฟีเจอร์, ทั้งเซิร์ฟเวอร์
เกิดเมื่อไร
สุ่มเป็นครั้งคราว
ผู้รับผิดชอบ
ผู้รับผิดชอบหลัก อินฟรา DB (ทีมอินฟรา) · ร่วมกับ พัฒนาเซิร์ฟเวอร์ (ทีมพัฒนาเกม)
งานฝั่งทีมพัฒนาเกม
คิวรีที่จำนวนผลลัพธ์ต่างกันมากตามค่าที่ส่งเข้าไปให้แยกเขียนหรือพิจารณาใช้ plan hint, ออกแบบคิวรีให้ใช้อินเด็กซ์แน่นอน
งานฝั่งทีมอินฟรา
เฝ้าดูคิวรีที่ช้าและบันทึก query plan, ตรึง plan ที่ดีไว้ (Query Store ของ SQL Server ฯลฯ), จัดการเวลาอัปเดตสถิติ
บนกราฟ
ขึ้นเป็นขั้นบันไดจากจุดหนึ่ง · เวลารันเฉลี่ยแยกตามคิวรี
จุดที่ต้องดู
เก็บเวลาเฉลี่ยของคิวรีรูปแบบเดียวกันเป็นระยะแล้วดูแนวโน้ม MySQL คือ AVG_TIMER_WAIT ใน events_statements_summary_by_digest, PostgreSQL คือ mean_exec_time ใน pg_stat_statements (12 ลงไปคือ mean_time) เทียบ query plan ก่อนและหลังช้าด้วย EXPLAIN หรือ auto_explain ของ PostgreSQL ส่วน SQL Server ใช้หน้า Regressed Queries ของ Query Store
สัญญาณว่าใช่
ในช่วงที่ไม่มี deploy เวลาเฉลี่ยของคิวรีหนึ่งขึ้นเป็นขั้นบันไดหลายสิบเท่า จุดนั้นตรงกับการอัปเดตสถิติหรือการรีสตาร์ต DB และ query plan เปลี่ยนไปแล้ว
สัญญาณว่าไม่ใช่
query plan เหมือนเดิมแต่ช้าลง: น่าจะเป็นข้อมูลที่เพิ่มขึ้น, การรอล็อก (db-hot-row) หรือดิสก์
วิธีตรวจ
ใช้เครื่องมือฝั่งอินฟรา (ไม่ต้องใช้โค้ดเกม)
รายละเอียดเพิ่มเติม
SQL Server จะใช้ plan ที่วางตามค่าที่เข้ามาครั้งแรกซ้ำ (parameter sniffing) ถ้า plan ที่วางจากตัวละครใหม่ที่มีไอเทมไม่กี่ชิ้นถูกใช้กับตัวละครเก่าที่มีไอเทมหลายหมื่นชิ้น จะช้าลงมาก และกรณีกลับกันก็พบบ่อย บางครั้งรีสตาร์ตแล้ว plan ถูกล้างจนกลับมาปกติ ก่อนจะแย่ลงอีกครั้ง

แหล่งอ้างอิง

  1. Query Processing Architecture Guide Microsoft SQL Server
    parameter sniffing: วาง query plan ตามค่าพารามิเตอร์ที่เข้ามาตอน compile หรือ recompile
  2. Parameter Sensitive Plan Optimization Microsoft SQL Server
    ถ้าข้อมูลกระจายไม่สม่ำเสมอ plan ที่แคชไว้ตัวเดียวจะไม่เหมาะกับทุกค่าพารามิเตอร์
  3. Monitor performance by using the Query Store Microsoft SQL Server
    plan เปลี่ยนเพราะสถิติ สคีมา หรืออินเด็กซ์เปลี่ยน และ plan cache เก็บแค่ plan ล่าสุด, ใช้ plan forcing ของ Query Store ตรึง plan ที่ดี, ใช้หน้า Regressed Queries เทียบคิวรีที่ช้าลงกับ plan
  4. Statement Summary Tables MySQL
    events_statements_summary_by_digest: COUNT_STAR และ AVG_TIMER_WAIT (เวลาเฉลี่ย) แยกตามรูปแบบคิวรี
  5. pg_stat_statements — track statistics of SQL planning and execution PostgreSQL
    calls, total_exec_time และ mean_exec_time (เวลารันเฉลี่ย) ของแต่ละคำสั่ง
  6. pg_stat_statements (PostgreSQL 12 Documentation) PostgreSQL
    ถึงเวอร์ชัน 12 ชื่อคอลัมน์คือ total_time และ mean_time
  7. auto_explain — log execution plans of slow queries PostgreSQL
    บันทึก query plan ของคิวรีที่ใช้เวลานานกว่า auto_explain.log_min_duration ลง log

สาเหตุที่ควรดูประกอบ

ชั้นเดียวกัน: L12 ฐานข้อมูล

สาเหตุจากชั้นอื่นที่ทำให้เกิดอาการเดียวกัน (อินพุตดีเลย์)

ดูการ์ดในฉบับหลักที่มีภาพและการทดลอง