Buku Putih Lag Game › L12 Database

Query melambat karena query plan berubah Query plan regression (stats, parameter sniffing)

ID penyebab db-plan-flip · Penanggung jawab utama Infrastruktur DB (Tim Infrastruktur) · Turut terlibat Pengembangan server (Tim Pengembang Game)

Buka kartu interaktif dengan gambar dan simulasi →

Walaupun kodenya tidak berubah, jika DB mengganti cara memproses query yang sama (query plan), query yang kemarin 2 ms bisa menjadi ratusan ms hari ini.

Mengapa Pembaruan statistik otomatis, restart DB, atau perubahan distribusi data membuat DB menyusun query plan baru → Akibatnya Plan yang tidak memakai indeks terpilih, sehingga query yang sama menjadi puluhan hingga ratusan kali lebih lambat dan koneksi tertahan → Di layar Tanpa ada deploy, loading fitur tertentu tiba-tiba melambat dan request lain ikut menunggu

Gejala

Input lag, Tidak bisa masuk / loading tanpa henti

Faktor

Latensi, Stall

Siapa yang mengalami

Fitur tertentu saja, Seluruh server

Kapan

Sesekali secara acak

Penanggung jawab

Penanggung jawab utama Infrastruktur DB (Tim Infrastruktur) · Turut terlibat Pengembangan server (Tim Pengembang Game)

Tugas Tim Pengembang Game

Pisahkan query yang jumlah hasilnya sangat berbeda tergantung nilainya atau pertimbangkan plan hint, rancang query yang pasti memakai indeks.

Tugas Tim Infrastruktur

Pantau query lambat dan riwayat query plan, kunci plan yang baik (Query Store SQL Server dan sejenisnya), atur waktu pembaruan statistik.

Di grafik

Naik seperti anak tangga · Rata-rata waktu eksekusi per query

Yang diperiksa

Kumpulkan rata-rata waktu query berpola sama secara berkala dan periksa trennya. MySQL: AVG_TIMER_WAIT di events_statements_summary_by_digest. PostgreSQL: mean_exec_time di pg_stat_statements (versi 12 ke bawah: mean_time). Bandingkan query plan sebelum dan sesudah melambat dengan EXPLAIN atau auto_explain PostgreSQL; di SQL Server, gunakan tampilan Regressed Queries di Query Store

Cocok jika

Pada waktu tanpa deploy, rata-rata waktu satu query naik seperti anak tangga hingga puluhan kali lipat, waktunya bertepatan dengan pembaruan statistik atau restart DB, dan query plan-nya sudah berubah

Tidak cocok jika

Query plan tidak berubah tetapi query melambat: lebih mungkin pertambahan data, menunggu lock (db-hot-row), atau disk

Sarana pemeriksaan

Tools infrastruktur (tanpa perlu kode game)

Pelajari lebih lanjut

SQL Server memakai ulang plan yang disusun berdasarkan nilai yang pertama kali masuk (parameter sniffing). Jika plan yang disusun untuk karakter baru dengan beberapa item saja dipakai untuk karakter lama dengan puluhan ribu item, query menjadi sangat lambat, dan kasus sebaliknya juga sering terjadi. Saat plan terhapus karena restart, query kembali normal, lalu bisa memburuk lagi.

Sumber

1. Query Processing Architecture Guide Microsoft SQL Server
   Parameter sniffing: query plan disusun berdasarkan nilai parameter yang masuk saat kompilasi atau rekompilasi
2. Parameter Sensitive Plan Optimization Microsoft SQL Server
   Jika distribusi data tidak merata, satu plan yang disimpan di cache tidak cocok untuk semua nilai parameter
3. Monitor performance by using the Query Store Microsoft SQL Server
   Plan berubah karena perubahan statistik, skema, atau indeks, dan plan cache hanya menyimpan plan terbaru; plan forcing di Query Store mengunci plan yang baik; tampilan Regressed Queries membandingkan query yang melambat beserta plan-nya
4. Statement Summary Tables MySQL
   events_statements_summary_by_digest: COUNT_STAR dan AVG_TIMER_WAIT (waktu rata-rata) per pola query yang sama
5. pg_stat_statements — track statistics of SQL planning and execution PostgreSQL
   calls, total_exec_time, dan mean_exec_time (rata-rata waktu eksekusi) per statement
6. pg_stat_statements (PostgreSQL 12 Documentation) PostgreSQL
   Sampai versi 12, nama kolomnya total_time dan mean_time
7. auto_explain — log execution plans of slow queries PostgreSQL
   Mencatat query plan dari query yang berjalan lebih lama dari auto_explain.log_min_duration ke log

Lihat juga

Lapisan yang sama: L12 Database

• Query tanpa indeks
• Perebutan lock pada hot row
• Deadlock DB
• Connection pool habis
• Replication lag
• Checkpoint dan log flush
• Cold cache (tepat setelah restart)
• Lonjakan login dan N+1 query
• Batch job berskala besar
• Failover DB
• Progres hilang karena interval penyimpanan yang panjang
• Cache stampede
• Transaksi yang terbuka lama
• Perintah lambat di Redis
• Lock akibat perubahan skema (DDL) saat layanan berjalan

Penyebab di lapisan lain dengan gejala yang sama (Input lag)

• Beban rendering kerumunan besar L1 Proses game di klien
• Bottleneck pemrosesan paket di main thread L1 Proses game di klien
• V-Sync dan antrean render L1 Proses game di klien
• Mode hemat daya dan thermal throttling L2 OS dan perangkat klien
• Pemakaian bandwidth oleh aplikasi lain di perangkat yang sama L2 OS dan perangkat klien
• Latensi layar, perangkat input, dan frame generation L2 OS dan perangkat klien
• Channel Wi-Fi padat L3 Jaringan rumah
• Bufferbloat (antrean router) L3 Jaringan rumah

Lihat kartu interaktif dengan gambar dan simulasi