Desember lalu, saya menerima laporan bug yang menarik dari tim dukungan VWO. Waktu pemuatan untuk salah satu laporan analitik untuk klien korporat besar tampaknya mahal. Dan karena ini adalah area tanggung jawab saya, saya segera fokus untuk menyelesaikan masalah.
Latar belakang
Untuk memperjelas apa yang saya bicarakan, saya akan memberi tahu Anda sedikit tentang VWO. Ini adalah platform tempat Anda dapat menjalankan berbagai kampanye bertarget di situs Anda: melakukan eksperimen A / B, melacak pengunjung dan konversi, menganalisis saluran penjualan, menampilkan peta panas dan memutar rekaman kunjungan.
Tetapi hal terpenting dalam platform adalah pelaporan. Semua fungsi di atas saling berhubungan. Dan untuk klien korporat, sejumlah besar informasi tidak akan berguna tanpa platform yang kuat yang menghadirkan mereka dalam bentuk analitik.
Menggunakan platform, Anda dapat membuat permintaan sewenang-wenang pada kumpulan data besar. Ini adalah contoh sederhana:
Tampilkan semua klik di abc.com
DARI <date d1> TO <date d2>
untuk orang yang
menggunakan Chrome OR
(berada di Eropa dan menggunakan iPhone)
Perhatikan operator boolean. Mereka tersedia untuk klien di antarmuka kueri untuk membuat kueri kompleks yang sewenang-wenang untuk mengambil sampel.
Permintaan lambat
Klien yang dimaksud sedang mencoba melakukan sesuatu yang secara intuitif harus bekerja dengan cepat:
Tampilkan semua catatan sesi
untuk pengguna yang mengunjungi halaman apa pun
dengan url di mana ada "/ pekerjaan"
Ada banyak lalu lintas di situs ini, dan kami menyimpan lebih dari satu juta URL unik hanya untuk itu. Dan mereka ingin menemukan templat url yang cukup sederhana terkait dengan model bisnis mereka.
Investigasi awal
Mari kita lihat apa yang terjadi di database. Berikut ini adalah query SQL lambat asli:
SELECT count(*) FROM acc_{account_id}.urls as recordings_urls, acc_{account_id}.recording_data as recording_data, acc_{account_id}.sessions as sessions WHERE recording_data.usp_id = sessions.usp_id AND sessions.referrer_id = recordings_urls.id AND ( urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[] ) AND r_time > to_timestamp(1542585600) AND r_time < to_timestamp(1545177599) AND recording_data.duration >=5 AND recording_data.num_of_pages > 0 ;
Dan inilah waktunya:
Waktu yang direncanakan: 1,480 ms
Waktu pimpin: 1431924.650 ms
Permintaan melewati 150 ribu baris. Perencana permintaan menunjukkan beberapa detail menarik, tetapi tidak ada hambatan yang jelas.
Mari kita pelajari pertanyaan lebih lanjut. Seperti yang Anda lihat, itu membuat tiga tabel JOIN :
- sesi : untuk menampilkan informasi sesi: browser, agen pengguna, negara, dan sebagainya.
- recording_data : url yang direkam, halaman, durasi kunjungan
- url : untuk menghindari duplikasi url yang sangat besar, kami menyimpannya di tabel terpisah.
Perhatikan juga bahwa semua tabel kami sudah dibagi oleh account_id . Dengan demikian, suatu situasi dikecualikan ketika, karena satu akun yang sangat besar, yang lain memiliki masalah.
Mencari bukti
Pada pemeriksaan lebih dekat, kita melihat bahwa sesuatu dalam permintaan tertentu tidak benar. Layak dilihat pada baris ini:
urls && array( select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%' )::text[]
Pikiran pertama adalah bahwa mungkin karena ILIKE di semua URL panjang ini (kami memiliki lebih dari 1,4 juta URL unik yang dikumpulkan untuk akun ini), kinerjanya mungkin berkurang.
Tapi tidak - bukan itu intinya!
SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%'; id -------- ... (198661 rows) Time: 5231.765 ms
Permintaan pencarian template itu sendiri hanya membutuhkan waktu 5 detik. Mencari pola pada sejuta URL unik jelas bukan masalah.
Tersangka berikutnya dalam daftar adalah beberapa JOIN . Mungkin berlebihan mereka menyebabkan perlambatan? JOIN biasanya merupakan kandidat yang paling jelas untuk masalah kinerja, tetapi saya tidak yakin kasus kami tipikal.
analytics_db=# SELECT count(*) FROM acc_{account_id}.urls as recordings_urls, acc_{account_id}.recording_data_0 as recording_data, acc_{account_id}.sessions_0 as sessions WHERE recording_data.usp_id = sessions.usp_id AND sessions.referrer_id = recordings_urls.id AND r_time > to_timestamp(1542585600) AND r_time < to_timestamp(1545177599) AND recording_data.duration >=5 AND recording_data.num_of_pages > 0 ; count ------- 8086 (1 row) Time: 147.851 ms
Dan ini juga bukan kasus kami. JOIN ternyata cukup cepat.
Kami mempersempit lingkaran tersangka
Saya siap untuk mulai mengubah kueri untuk mencapai setiap peningkatan kinerja yang mungkin. Tim saya dan saya mengembangkan 2 ide utama:
- Gunakan EXISTS untuk URL subquery : Kami ingin memeriksa lagi apakah ada masalah dengan subquery untuk url. Salah satu cara untuk mencapai ini adalah dengan hanya menggunakan
EXISTS . EXISTS dapat sangat meningkatkan kinerja karena segera berakhir segera setelah ditemukan satu baris berdasarkan kondisi.
SELECT count(*) FROM acc_{account_id}.urls as recordings_urls, acc_{account_id}.recording_data as recording_data, acc_{account_id}.sessions as sessions WHERE recording_data.usp_id = sessions.usp_id AND ( 1 = 1 ) AND sessions.referrer_id = recordings_urls.id AND (exists(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')) AND r_time > to_timestamp(1547585600) AND r_time < to_timestamp(1549177599) AND recording_data.duration >=5 AND recording_data.num_of_pages > 0 ; count 32519 (1 row) Time: 1636.637 ms
Ya benar. Subquery, ketika dibungkus dalam EXISTS , membuat semuanya super cepat. Pertanyaan logis berikutnya adalah mengapa kueri dengan BERGABUNG dan subquery itu sendiri cepat secara individual, tetapi sangat lambat bersama?
- Kami memindahkan subquery ke CTE : jika permintaan cepat dengan sendirinya, kami hanya dapat menghitung hasil cepat terlebih dahulu dan kemudian memberikannya ke permintaan utama
WITH matching_urls AS ( select id::text from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%' ) SELECT count(*) FROM acc_{account_id}.urls as recordings_urls, acc_{account_id}.recording_data as recording_data, acc_{account_id}.sessions as sessions, matching_urls WHERE recording_data.usp_id = sessions.usp_id AND ( 1 = 1 ) AND sessions.referrer_id = recordings_urls.id AND (urls && array(SELECT id from matching_urls)::text[]) AND r_time > to_timestamp(1542585600) AND r_time < to_timestamp(1545107599) AND recording_data.duration >=5 AND recording_data.num_of_pages > 0;
Tapi itu masih sangat lambat.
Temukan pelakunya
Selama ini, satu hal kecil melintas di depan mataku, yang darinya aku terus-menerus menyingkir. Tetapi karena tidak ada yang tersisa, saya memutuskan untuk melihatnya. Saya berbicara tentang operator && . Sementara EXISTS hanya meningkatkan kinerja, && adalah satu-satunya faktor umum yang tersisa di semua versi permintaan lambat.
Melihat dokumentasi , kita melihat bahwa && digunakan ketika Anda perlu menemukan elemen umum di antara dua array.
Dalam permintaan asli, ini adalah:
AND ( urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[] )
Yang berarti bahwa kami melakukan pencarian templat untuk url kami, lalu kami menemukan persimpangan dengan semua url dengan catatan bersama. Ini agak membingungkan, karena "url" di sini tidak merujuk ke tabel yang berisi semua URL, tetapi ke kolom "url" di tabel recording_data .
Ketika kecurigaan && , saya mencoba menemukan konfirmasi dalam rencana kueri yang dihasilkan oleh EXPLAIN ANALYZE (Saya sudah memiliki rencana tersimpan, tetapi biasanya lebih mudah untuk bereksperimen dengan SQL daripada mencoba memahami opacity dari perencana kueri).
Filter: ((urls && ($0)::text[]) AND (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) AND (r_time < '2018-12-18 23:59:59+00'::timestamp with time zone) AND (duration >= '5'::double precision) AND (num_of_pages > 0)) Rows Removed by Filter: 52710
Ada beberapa baris filter dari && saja. Yang berarti bahwa operasi ini tidak hanya mahal, tetapi juga dilakukan beberapa kali.
Saya memeriksa ini dengan mengisolasi kondisinya
SELECT 1 FROM acc_{account_id}.urls as recordings_urls, acc_{account_id}.recording_data_30 as recording_data_30, acc_{account_id}.sessions_30 as sessions_30 WHERE urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[]
Permintaan ini lambat. Karena JOIN cepat dan subkueri cepat, hanya operator && tersisa.
Ini hanya operasi kunci. Kami selalu perlu mencari di seluruh tabel utama URL untuk mencari berdasarkan pola, dan kami selalu perlu menemukan persimpangan. Kami tidak dapat mencari entri url secara langsung, karena ini hanya pengidentifikasi yang menautkan ke urls .
Menuju solusi
&& lambat karena kedua set sangat besar. Operasi akan relatif cepat jika saya mengganti urls dengan { "http://google.com/", "http://wingify.com/" } .
Saya mulai mencari cara untuk membuat persimpangan set di Postgres tanpa menggunakan && , tetapi tanpa banyak keberhasilan.
Pada akhirnya, kami memutuskan untuk menyelesaikan masalah secara terpisah: beri saya semua urls string yang url cocok dengan pola. Tanpa syarat tambahan, itu akan menjadi -
SELECT urls.url FROM acc_{account_id}.urls as urls, (SELECT unnest(recording_data.urls) AS id) AS unrolled_urls WHERE urls.id = unrolled_urls.id AND urls.url ILIKE '%jobs%'
Alih-alih JOIN sintaks, saya hanya menggunakan subquery dan memperluas array recording_data.urls sehingga kondisinya dapat langsung diterapkan ke WHERE .
Yang paling penting di sini adalah && digunakan untuk memeriksa apakah entri yang diberikan berisi URL yang sesuai. Menyipitkan mata sedikit, Anda dapat melihat dalam operasi ini bergerak melalui elemen-elemen dari array (atau baris tabel) dan berhenti ketika kondisi (pencocokan) terpenuhi. Tidak menyerupai apa pun? Ya, EXISTS .
Karena recording_data.urls dapat direferensikan dari luar konteks subquery ketika ini terjadi, kita dapat kembali ke teman lama kita EXISTS dan membungkusnya dengan subquery.
Menggabungkan semuanya bersama-sama, kami mendapatkan permintaan final yang dioptimalkan:
SELECT count(*) FROM acc_{account_id}.urls as recordings_urls, acc_{account_id}.recording_data as recording_data, acc_{account_id}.sessions as sessions WHERE recording_data.usp_id = sessions.usp_id AND ( 1 = 1 ) AND sessions.referrer_id = recordings_urls.id AND r_time > to_timestamp(1542585600) AND r_time < to_timestamp(1545177599) AND recording_data.duration >=5 AND recording_data.num_of_pages > 0 AND EXISTS( SELECT urls.url FROM acc_{account_id}.urls as urls, (SELECT unnest(urls) AS rec_url_id FROM acc_{account_id}.recording_data) AS unrolled_urls WHERE urls.id = unrolled_urls.rec_url_id AND urls.url ILIKE '%enterprise_customer.com/jobs%' );
Dan Time: 1898.717 ms time terakhir Time: 1898.717 ms Saatnya untuk merayakan?!?
Tidak secepat itu! Pertama, Anda perlu memeriksa kebenarannya. Saya sangat curiga dengan optimasi EXISTS , karena mengubah logika ke tujuan sebelumnya. Kami harus yakin bahwa kami belum menambahkan kesalahan yang tidak jelas pada permintaan.
Pemeriksaan sederhana adalah untuk melakukan count(*) pada permintaan lambat dan cepat untuk sejumlah besar kumpulan data yang berbeda. Kemudian, untuk sebagian kecil dari data, saya memeriksa semua hasil secara manual.
Semua cek memberi hasil positif secara konsisten. Kami memperbaikinya!
Pelajaran yang Dipetik
Ada banyak pelajaran yang bisa dipetik dari kisah ini:
- Rencana kueri tidak menceritakan keseluruhan cerita, tetapi dapat memberikan petunjuk
- Para tersangka utama tidak selalu menjadi penyebab utama
- Permintaan lambat dapat dipecah untuk mengisolasi kemacetan
- Tidak semua optimasi bersifat reduktif
- Menggunakan
EXIST , jika memungkinkan, dapat menyebabkan peningkatan tajam dalam produktivitas.
Kesimpulan
Kami beralih dari waktu permintaan ~ 24 menit menjadi 2 detik - peningkatan kinerja yang sangat serius! Meskipun artikel ini ternyata besar, semua percobaan yang kami lakukan terjadi pada hari yang sama, dan menurut perkiraan, butuh 1,5 hingga 2 jam untuk optimasi dan pengujian.
SQL adalah bahasa yang indah, jika tidak takut, tetapi cobalah untuk belajar dan menggunakannya. Memiliki pemahaman yang baik tentang bagaimana query SQL dieksekusi, bagaimana database menghasilkan rencana query, cara kerja indeks, dan hanya ukuran data yang Anda hadapi, Anda dapat sangat berhasil dalam optimasi query. Namun sama pentingnya, untuk terus mencoba pendekatan yang berbeda dan perlahan-lahan memecah masalah, menemukan kemacetan.
Bagian terbaik dalam mencapai hasil seperti itu adalah peningkatan kecepatan yang terlihat - ketika laporan yang bahkan belum diunduh sebelumnya sekarang dimuat hampir secara instan.
Terima kasih khusus kepada rekan satu tim saya Aditya Misra , Aditya Gauru dan Varun Malhotra untuk brainstorming dan Dinkar Pandir karena menemukan kesalahan penting dalam permintaan terakhir kami sebelum akhirnya kami mengucapkan selamat tinggal kepadanya!