🥇Kisah salah satu investigasi SQL

Desember lalu saya menerima laporan bug menarik dari tim dukungan VWO. Waktu pemuatan salah satu laporan analitik untuk klien korporat besar tampaknya terlalu mahal. Dan karena ini adalah tanggung jawab saya, saya langsung fokus menyelesaikan masalah.

prasejarah

Agar lebih jelas apa yang saya bicarakan, saya akan bercerita sedikit tentang VWO. Ini adalah platform yang dengannya Anda dapat meluncurkan berbagai kampanye bertarget di situs web Anda: melakukan eksperimen A/B, melacak pengunjung dan konversi, menganalisis saluran penjualan, menampilkan peta panas, dan memutar rekaman kunjungan.

Namun hal terpenting tentang platform ini adalah pelaporan. Semua fungsi di atas saling berhubungan. Dan untuk klien korporat, sejumlah besar informasi tidak akan berguna tanpa platform kuat yang menyajikannya dalam bentuk analitik.

Dengan menggunakan platform ini, Anda dapat membuat kueri acak pada kumpulan data yang besar. Berikut ini contoh sederhananya:

Tampilkan semua klik pada halaman "abc.com" DARI <tanggal d1> SAMPAI <tanggal d2> untuk orang yang menggunakan Chrome ATAU (berlokasi di Eropa DAN menggunakan iPhone)

Perhatikan operator Boolean. Mereka tersedia bagi klien di antarmuka kueri untuk membuat kueri rumit yang sewenang-wenang guna mendapatkan sampel.

Permintaan lambat

Klien yang dimaksud sedang mencoba melakukan sesuatu yang secara intuitif akan bekerja dengan cepat:

Tampilkan semua catatan sesi untuk pengguna yang mengunjungi halaman mana pun dengan URL yang berisi "/jobs"

Situs ini memiliki banyak lalu lintas dan kami menyimpan lebih dari satu juta URL unik hanya untuk itu. Dan mereka ingin menemukan template URL yang cukup sederhana yang terkait dengan model bisnis mereka.

Investigasi awal

Mari kita lihat apa yang terjadi di database. Di bawah ini adalah kueri SQL lambat yang 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 eksekusi: 1431924.650 ms

Kueri tersebut merayapi 150 ribu baris. Perencana kueri menunjukkan beberapa detail menarik, namun tidak ada hambatan yang jelas.

Mari kita pelajari permintaan tersebut lebih jauh. Seperti yang Anda lihat, dia melakukannya JOIN tiga tabel:

sesi: untuk menampilkan informasi sesi: browser, agen pengguna, negara, dan sebagainya.
rekaman_data: rekaman URL, halaman, durasi kunjungan
url: Untuk menghindari duplikasi URL yang sangat besar, kami menyimpannya dalam tabel terpisah.

Perhatikan juga bahwa semua tabel kita sudah dipartisi account_id. Dengan cara ini, situasi di mana satu akun yang sangat besar menyebabkan masalah bagi akun lainnya dapat dikesampingkan.

Mencari petunjuk

Setelah diperiksa lebih dekat, kami melihat ada yang salah dengan permintaan tertentu. Ada baiknya melihat lebih dekat baris ini:

urls && array(
	select id from acc_{account_id}.urls 
	where url  ILIKE  '%enterprise_customer.com/jobs%'
)::text[]

Pikiran pertama adalah mungkin karena ILIKE pada semua URL yang panjang ini (kami memiliki lebih dari 1,4 juta unik URL yang dikumpulkan untuk akun ini) kinerjanya mungkin menurun.

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 sendiri hanya membutuhkan waktu 5 detik. Mencari pola di sejuta URL unik jelas tidak menjadi masalah.

Tersangka berikutnya dalam daftar ada beberapa JOIN. Mungkin penggunaannya yang berlebihan menyebabkan perlambatan? Biasanya JOINIni adalah kandidat yang paling jelas untuk masalah kinerja, namun menurut saya kasus kami bukanlah kasus yang 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. JOINternyata cukup cepat.

Mempersempit lingkaran tersangka

Saya siap untuk mulai mengubah kueri untuk mencapai kemungkinan peningkatan kinerja. Saya dan tim mengembangkan 2 ide utama:

Gunakan EXISTS untuk URL subkueri: Kami ingin memeriksa lagi apakah ada masalah dengan subkueri URL. Salah satu cara untuk mencapai hal ini adalah dengan menggunakan EXISTS. EXISTS bisa sangat meningkatkan kinerja karena berakhir segera setelah menemukan satu-satunya string yang cocok dengan 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

Baiklah. Subkueri saat dibungkus EXISTS, membuat segalanya menjadi super cepat. Pertanyaan logis berikutnya adalah mengapa permintaan dengan JOIN-ami dan subkuerinya sendiri cepat secara individual, tetapi sangat lambat jika digabungkan?

Memindahkan subquery ke CTE : Jika kuerinya sendiri cepat, kita cukup menghitung hasil cepatnya terlebih dahulu lalu memberikannya ke kueri 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.

Menemukan pelakunya

Selama ini, satu hal kecil terlintas di depan mataku, yang terus-menerus aku abaikan. Tapi karena tidak ada lagi yang tersisa, aku memutuskan untuk melihatnya juga. yang saya bicarakan && operator. Selamat tinggal EXISTS hanya meningkatkan kinerja && adalah satu-satunya faktor umum yang tersisa di semua versi kueri lambat.

Melihat ke dokumentasi, kami melihatnya && digunakan ketika Anda perlu menemukan elemen umum 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[]   )

Artinya kita melakukan pencarian pola pada URL kita, lalu menemukan perpotongan semua URL tersebut dengan postingan umum. Ini agak membingungkan karena "url" di sini tidak mengacu pada tabel yang berisi semua URL, tetapi pada kolom "url" pada tabel recording_data.

Dengan meningkatnya kecurigaan mengenai &&, saya mencoba mencari konfirmasi untuk mereka dalam rencana kueri yang dibuat EXPLAIN ANALYZE (Saya sudah menyimpan rencana, tapi saya biasanya lebih nyaman bereksperimen dalam SQL daripada mencoba memahami opacity 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

Hanya ada beberapa baris filter dari &&. Artinya, operasi ini tidak hanya mahal, tetapi juga dilakukan berkali-kali.

Saya mengujinya 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[]

Kueri ini lambat. Karena JOIN-s cepat dan subkuerinya cepat, satu-satunya yang tersisa hanyalah && operator.

Ini hanyalah operasi kunci. Kita selalu perlu menelusuri seluruh tabel URL yang mendasarinya untuk mencari suatu pola, dan kita selalu perlu menemukan titik temu. Kami tidak dapat menelusuri berdasarkan data URL secara langsung, karena ini hanya mengacu pada ID urls.

Dalam perjalanan menuju solusi

&& lambat karena kedua set sangat besar. Pengoperasiannya akan relatif cepat jika saya menggantinya urls pada { "http://google.com/", "http://wingify.com/" }.

Saya mulai mencari cara untuk melakukan set persimpangan di Postgres tanpa menggunakan &&, tetapi tanpa banyak keberhasilan.

Pada akhirnya, kami memutuskan untuk menyelesaikan masalah secara terpisah: berikan saya segalanya urls baris yang URL-nya cocok dengan polanya. 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 baru saja menggunakan subquery dan memperluas recording_data.urls array sehingga Anda dapat langsung menerapkan kondisi tersebut WHERE.

Hal terpenting di sini adalah itu && digunakan untuk memeriksa apakah entri tertentu berisi URL yang cocok. Jika Anda sedikit menyipitkan mata, Anda dapat melihat operasi ini berpindah melalui elemen array (atau baris tabel) dan berhenti ketika kondisi (kecocokan) terpenuhi. Tidak mengingatkanmu pada apa pun? Ya, EXISTS.

Sejak itu recording_data.urls dapat direferensikan dari luar konteks subquery, ketika hal ini terjadi kita dapat kembali ke teman lama kita EXISTS dan bungkus subquery dengan itu.

Dengan menggabungkan semuanya, kami mendapatkan kueri akhir 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 waktu tunggu terakhir Time: 1898.717 ms Saatnya merayakan?!?

Tidak secepat itu! Pertama, Anda perlu memeriksa kebenarannya. Saya sangat curiga EXISTS optimasi karena mengubah logika untuk menyelesaikan lebih awal. Kami perlu memastikan bahwa kami belum menambahkan kesalahan yang tidak jelas pada permintaan.

Tes sederhana harus dijalankan count(*) pada kueri lambat dan cepat untuk sejumlah besar kumpulan data berbeda. Kemudian, untuk sebagian kecil data, saya memverifikasi secara manual bahwa semua hasil sudah benar.

Semua tes memberikan hasil positif secara konsisten. Kami memperbaiki semuanya!

Pelajaran yang Dipetik

Banyak hikmah yang bisa dipetik dari cerita ini:

Rencana kueri tidak menceritakan keseluruhan cerita, namun dapat memberikan petunjuk
Tersangka utama tidak selalu merupakan pelaku sebenarnya
Kueri yang lambat dapat dipecah untuk mengisolasi kemacetan
Tidak semua optimasi bersifat reduktif
Menggunakan EXIST, jika memungkinkan, dapat menghasilkan peningkatan produktivitas yang dramatis

Keluaran

Kami beralih dari waktu kueri ~24 menit menjadi 2 detik - peningkatan kinerja yang cukup signifikan! Meskipun artikel ini diterbitkan dalam jumlah besar, semua eksperimen yang kami lakukan dilakukan dalam satu hari, dan diperkirakan memerlukan waktu antara 1,5 dan 2 jam untuk pengoptimalan dan pengujian.

SQL adalah bahasa yang luar biasa jika Anda tidak takut, tetapi cobalah mempelajari dan menggunakannya. Dengan memiliki pemahaman yang baik tentang bagaimana kueri SQL dieksekusi, bagaimana database menghasilkan rencana kueri, cara kerja indeks, dan ukuran data yang Anda tangani, Anda bisa sangat berhasil dalam mengoptimalkan kueri. Namun, yang juga sama pentingnya adalah terus mencoba pendekatan yang berbeda dan perlahan-lahan memecahkan masalah, serta menemukan hambatannya.

Bagian terbaik dalam mencapai hasil seperti ini adalah peningkatan kecepatan yang nyata - di mana laporan yang sebelumnya tidak dapat dimuat kini dimuat hampir secara instan.

Terima kasih khusus kepada teman-temanku atas perintah Aditya Mishra, Aditya Gauru и Varun Malhotra untuk bertukar pikiran dan Dinkar Pandir karena menemukan kesalahan penting dalam permintaan terakhir kami sebelum akhirnya mengucapkan selamat tinggal!

Sumber: www.habr.com

Kisah salah satu investigasi SQL