Saya percaya bahwa hampir setiap proyek yang menggunakan Ruby on Rails dan Postgres sebagai senjata utama di backend berada dalam perjuangan permanen antara kecepatan pengembangan, keterbacaan / pemeliharaan kode dan kecepatan proyek dalam produksi. Saya akan bercerita tentang pengalaman saya menyeimbangkan antara tiga paus ini dalam kasus di mana keterbacaan dan kecepatan kerja menderita di pintu masuk, dan pada akhirnya ternyata melakukan apa yang beberapa insinyur berbakat coba lakukan sebelum saya tidak berhasil.
Keseluruhan cerita akan mengambil beberapa bagian. Ini adalah yang pertama di mana saya akan berbicara tentang apa PMDSC untuk mengoptimalkan query SQL, berbagi alat yang berguna untuk mengukur kinerja permintaan di postgres, dan mengingatkan saya pada satu lembar cheat lama yang bermanfaat yang masih relevan.
Sekarang, setelah beberapa waktu, "di belakang" saya mengerti bahwa di pintu masuk ke kasus ini saya tidak berharap sama sekali bahwa saya akan berhasil. Oleh karena itu, postingan ini akan lebih bermanfaat bagi para pemberani dan bukan pengembang yang paling berpengalaman daripada bagi para super-senior yang telah melihat rails dengan SQL telanjang.
Memasukan data
Kami di Appbooster mempromosikan aplikasi seluler. Untuk dengan mudah mengajukan dan menguji hipotesis, kami mengembangkan beberapa aplikasi kami. Backend untuk kebanyakan dari mereka adalah Rails API dan Postgresql.
Pahlawan publikasi ini telah dalam pengembangan sejak akhir 2013 - kemudian rel 4.1.0.beta1 baru saja dirilis. Sejak saat itu, proyek ini telah berkembang menjadi aplikasi web terisi penuh yang berjalan di beberapa server di Amazon EC2 dengan basis data terpisah di Amazon RDS (db.t3.xlarge dengan 4 vCPUs dan 16 GB RAM). Beban puncak mencapai 25k RPM, rata-rata muatan hari 8-10k RPM.
Kisah ini dimulai dengan contoh basis data, atau lebih tepatnya, dengan saldo kreditnya.
Bagaimana Postgres ketik "t" instance bekerja di Amazon RDS: jika database Anda berjalan dengan rata-rata konsumsi CPU di bawah nilai tertentu, maka Anda mengumpulkan kredit pada akun Anda, yang mana instance dapat menghabiskan pada konsumsi CPU selama jam-jam beban tinggi - ini menghemat Anda membayar lebih tinggi untuk kapasitas server dan untuk mengatasi beban tinggi. Rincian lebih lanjut tentang apa dan berapa banyak mereka membayar menggunakan AWS dapat ditemukan di artikel CTO kami .
Saldo pinjaman pada titik tertentu habis. Untuk beberapa waktu, ini tidak terlalu penting, karena saldo pinjaman dapat diisi kembali dengan uang - biayanya sekitar $ 20 per bulan, yang tidak terlalu terlihat untuk total biaya menyewa daya komputasi. Dalam pengembangan produk, biasanya memperhatikan tugas-tugas yang dirumuskan dari persyaratan bisnis. Peningkatan konsumsi CPU dari server database sesuai dengan hutang teknis dan diimbangi dengan biaya pembelian saldo kredit yang kecil.
Suatu hari, saya menulis dalam ringkasan harian bahwa saya sangat lelah memadamkan "api" yang muncul secara berkala di berbagai bagian proyek. Jika ini terus berlanjut, pengembang yang kelelahan akan mencurahkan waktu untuk tugas-tugas bisnis. Pada hari yang sama, saya pergi ke manajer proyek utama, menjelaskan keberpihakan dan meminta waktu untuk menyelidiki penyebab kebakaran dan perbaikan berkala. Setelah menerima lampu hijau, saya mulai mengumpulkan data dari berbagai sistem pemantauan.
Kami menggunakan Newrelic untuk melacak total waktu respons per hari. Gambarnya tampak seperti ini:
Bagian dari waktu respons yang diambil Postgres disorot dengan warna kuning pada grafik. Seperti yang Anda lihat, kadang-kadang waktu respon mencapai 1000 ms, dan sebagian besar waktu itu adalah database yang merenungkan respons. Jadi, Anda perlu melihat apa yang terjadi dengan kueri SQL.
PMDSC adalah praktik sederhana dan langsung untuk pekerjaan optimasi SQL yang membosankan
Mainkan!
Ukur itu!
Gambar itu!
Misalkan saja!
Periksa!
Mainkan!
Mungkin bagian terpenting dari keseluruhan latihan. Ketika seseorang mengatakan frasa "Mengoptimalkan kueri SQL" - itu agak menyebabkan kecocokan dan kebosanan di sebagian besar orang. Ketika Anda mengatakan "Investigasi detektif dan mencari penjahat berbahaya" - itu lebih melibatkan dan membuat Anda sendiri dalam suasana hati yang tepat. Karena itu, penting untuk masuk ke dalam permainan. Saya menikmati bermain detektif. Saya membayangkan bahwa masalah dengan database adalah penjahat berbahaya atau penyakit langka. Dan dia membayangkan dirinya berperan sebagai Sherlock Holmes, Letnan Columbo atau Doctor House. Pilih pahlawan sesuai selera Anda dan pergi!
Ukur itu!
Untuk menganalisis statistik permintaan, saya menginstal PgHero . Ini adalah cara yang sangat nyaman untuk membaca data dari pg_stat_statements ekstensi Postgres. Pergi ke / kueri dan lihat statistik semua kueri selama 24 jam terakhir. Menyortir kueri secara default menurut kolom Total Waktu - proporsi total waktu database memproses kueri - sumber berharga dalam menemukan tersangka. Waktu Rata-Rata - berapa banyak, rata-rata, permintaan dieksekusi. Panggilan - berapa banyak permintaan yang dilakukan selama waktu yang dipilih. PgHero menganggap permintaan lambat jika dieksekusi lebih dari 100 kali per hari dan rata-rata memakan waktu lebih dari 20 milidetik. Daftar kueri lambat di halaman pertama, segera setelah daftar indeks rangkap.
Kami mengambil yang pertama dalam daftar dan melihat rincian kueri, Anda dapat langsung melihatnya menjelaskan analisis. Jika waktu perencanaan jauh lebih sedikit daripada waktu eksekusi, maka ada sesuatu yang salah dengan permintaan ini dan kami memusatkan perhatian kami pada tersangka ini.
PgHero memiliki metode visualisasi sendiri, tetapi saya lebih suka menggunakan delete.depesz.com, menyalin data dari menjelaskan analisis di sana.
Salah satu pertanyaan yang dicurigai menggunakan Pemindaian Indeks. Visualisasi menunjukkan bahwa indeks ini tidak efektif dan merupakan titik lemah - disorot dengan warna merah. Baik! Kami memeriksa jejak tersangka dan menemukan bukti penting! Keadilan tidak bisa dihindari!
Gambar itu!
Mari kita menggambar banyak data yang digunakan di bagian query yang bermasalah. Akan bermanfaat untuk membandingkan dengan data apa yang dicakup oleh indeks.
Sedikit konteks. Kami menguji salah satu cara untuk menjaga penonton di aplikasi - sesuatu seperti lotre di mana Anda dapat memenangkan beberapa mata uang lokal. Anda memasang taruhan, menebak angka dari 0 hingga 100 dan mengambil seluruh pot jika nomor Anda paling dekat dengan yang diterima oleh pembuat angka acak. Kami menyebutnya "Arena" dan disebut demonstrasi "Pertempuran".
Basis data pada saat investigasi berisi sekitar lima ratus ribu catatan pertempuran. Di bagian bermasalah dari permintaan, kami mencari pertempuran di mana nilainya tidak melebihi keseimbangan pengguna dan status pertempuran sedang menunggu para pemain. Kita melihat bahwa persimpangan set (disorot dalam oranye) adalah sejumlah kecil catatan.
Indeks yang digunakan di bagian tersangka dari permintaan mencakup semua pertempuran yang dibuat di bidang Created_at. Permintaan berjalan melalui 505330 catatan dari yang dipilihnya 40, dan 505290 dihilangkan. Terlihat sangat boros.
Misalkan saja!
Kami mengajukan hipotesis. Apa yang akan membantu database menemukan empat puluh dari lima ratus ribu catatan? Mari kita coba membuat indeks yang mencakup bidang nilai, hanya untuk pertempuran dengan status "menunggu pemain" - indeks parsial.
add_index :arena_battles, :bet,
where: "status = 'waiting_for_players'",
name: "index_arena_battles_on_bet_partial_status"
Indeks parsial - hanya ada untuk catatan yang cocok dengan kondisi: bidang status sama dengan "menunggu pemain" dan mengindeks bidang nilai - persis apa yang ada dalam kondisi kueri. Sangat menguntungkan menggunakan indeks khusus ini: hanya membutuhkan 40 kilobyte dan tidak mencakup pertempuran yang telah dimainkan dan kami tidak perlu mendapatkan sampel. Sebagai perbandingan, indeks index_arena_battles_on_created_at, yang digunakan oleh tersangka, membutuhkan sekitar 40 MB, dan tabel dengan pertempuran sekitar 70 MB. Indeks ini dapat dihapus dengan aman jika kueri lain tidak menggunakannya.
Periksa!
Kami meluncurkan migrasi dengan indeks baru ke produksi dan mengamati bagaimana respons titik akhir dengan pertempuran telah berubah.
Grafik menunjukkan jam berapa kami meluncurkan migrasi. Pada malam 6 Desember, waktu respons menurun sekitar 10 kali dari ~ 500 ms hingga ~ 50 ms. Tersangka di pengadilan menerima status tahanan dan sekarang di penjara. Baik!
Melarikan diri dari penjara
Beberapa hari kemudian, kami menyadari bahwa kami bahagia lebih awal. Sepertinya tahanan menemukan kaki, mengembangkan dan menerapkan rencana pelarian.
Pada pagi hari tanggal 11 Desember, penjadwalan permintaan postgres memutuskan bahwa menggunakan indeks yang diuraikan baru tidak lagi menguntungkan untuk itu dan mulai menggunakan yang lama lagi.
Kami kembali pada tahap Misalkan! Menyusun diagnosis diferensial, dalam semangat Dr. House:
- Mungkin perlu untuk mengoptimalkan pengaturan postgres;
- mungkin tingkatkan postgres ke versi yang lebih baru dalam jumlah kecil (9.6.11 -> 9.6.15);
- dan mungkin, sekali lagi, hati-hati mempelajari query SQL yang membentuk Rails?
Kami menguji ketiga hipotesis. Yang terakhir membawa kami ke jejak kaki.
SELECT "arena_battles".*
FROM "arena_battles"
WHERE "arena_battles"."status" = 'waiting_for_players'
AND (arena_battles.bet <= 98.13)
AND (NOT EXISTS (
SELECT 1 FROM arena_participations
WHERE arena_battle_id = arena_battles.id
AND (arena_profile_id = 46809)
))
ORDER BY "arena_battles"."created_at" ASC
LIMIT 10 OFFSET 0Mari kita berjalan bersama SQL ini. Kami memilih semua medan perang dari tabel pertempuran yang statusnya sama dengan "menunggu pemain" dan nilainya kurang dari atau sama dengan jumlah tertentu. Sejauh ini semuanya jelas. Istilah selanjutnya dalam kondisi terlihat menyeramkan.
NOT EXISTS (
SELECT 1 FROM arena_participations
WHERE arena_battle_id = arena_battles.id
AND (arena_profile_id = 46809)
)Kami mencari hasil subquery yang tidak ada. Dapatkan bidang pertama dari tabel partisipasi pertempuran, tempat ID pertempuran cocok dan profil peserta milik pemain kami. Saya akan mencoba menggambar set yang dijelaskan dalam subquery.
Sulit untuk dipahami, tetapi pada akhirnya, dengan subquery ini, kami mencoba untuk mengecualikan pertempuran di mana pemain sudah berpartisipasi. Kami melihat penjelasan umum dari permintaan dan melihat Perencanaan waktu: 0,180 ms, Waktu pelaksanaan: 12.119 ms. Kami menemukan kaki tangan!
Sudah waktunya untuk lembar contekan favorit saya, yang telah ada di internet sejak 2008. Ini dia:
Ya! Segera setelah kueri menemukan sesuatu yang harus mengecualikan sejumlah catatan berdasarkan data dari tabel lain, meme dengan jenggot dan ikal ini akan muncul di memori.
Sebenarnya, inilah yang kami butuhkan:
Simpan gambar ini untuk Anda sendiri, atau lebih baik lagi, cetak dan gantung di beberapa tempat di kantor.
Kami menulis ulang subquery menjadi LEFT JOIN WHERE B.key IS NULL, kami mendapatkan:
SELECT "arena_battles".*
FROM "arena_battles"
LEFT JOIN arena_participations
ON arena_participations.arena_battle_id = arena_battles.id
AND (arena_participations.arena_profile_id = 46809)
WHERE "arena_battles"."status" = 'waiting_for_players'
AND (arena_battles.bet <= 98.13)
AND (arena_participations.id IS NULL)
ORDER BY "arena_battles"."created_at" ASC
LIMIT 10 OFFSET 0Kueri yang dikoreksi berjalan di dua tabel sekaligus. Kami menambahkan tabel dengan catatan partisipasi pengguna dalam pertempuran di "kiri" dan menambahkan kondisi bahwa pengidentifikasi partisipasi tidak ada. Mari kita lihat menjelaskan analisis kueri yang diterima: Waktu perencanaan: 0,185 ms, Waktu eksekusi: 0,337 ms. Baik! Sekarang perencana kueri tidak akan ragu bahwa itu harus menggunakan indeks parsial, tetapi akan menggunakan opsi tercepat. Tahanan yang melarikan diri dan kaki tangannya dijatuhi hukuman penjara seumur hidup di sebuah institusi rezim yang ketat. Akan lebih sulit bagi mereka untuk melarikan diri.
Ringkasannya singkat.
- Gunakan Newrelic atau layanan serupa lainnya untuk menemukan prospek. Kami menyadari bahwa masalahnya ada di kueri basis data.
- Gunakan latihan PMDSC - itu berhasil dan dalam hal apa pun sangat menarik.
- Gunakan PgHero untuk menemukan tersangka dan menyelidiki petunjuk dalam statistik kueri SQL.
- Gunakan jelaskan.depesz.com - mudah dibaca menjelaskan analisis permintaan di sana.
- Cobalah menggambar banyak data saat Anda tidak tahu persis apa yang dilakukan permintaan.
- Pikirkan pria tangguh dengan rambut ikal di kepalanya ketika Anda melihat subquery mencari sesuatu yang tidak ada di meja lain.
- Bermain detektif, Anda bahkan mungkin mendapatkan lencana.