🥇Bir SQL araştırmasının hikayesi

Geçen Aralık ayında VWO destek ekibinden ilginç bir hata raporu aldım. Büyük bir kurumsal müşterinin analiz raporlarından birinin yükleme süresi çok yüksek görünüyordu. Ve bu benim sorumluluk alanım olduğu için hemen sorunun çözümüne odaklandım.

tarih öncesi

Neden bahsettiğimi netleştirmek için size biraz VWO'dan bahsedeceğim. Bu, web sitelerinizde çeşitli hedefli kampanyalar başlatabileceğiniz bir platformdur: A/B denemeleri gerçekleştirin, ziyaretçileri ve dönüşümleri izleyin, satış hunisini analiz edin, ısı haritalarını görüntüleyin ve ziyaret kayıtlarını oynatın.

Ancak platformla ilgili en önemli şey raporlamadır. Yukarıdaki işlevlerin tümü birbiriyle bağlantılıdır. Kurumsal müşteriler için ise büyük miktarda bilgi, onu analitik biçimde sunan güçlü bir platform olmadan hiçbir işe yaramaz.

Platformu kullanarak büyük bir veri seti üzerinde rastgele sorgulama yapabilirsiniz. İşte basit bir örnek:

Chrome kullanan VEYA (Avrupa'da bulunan VE iPhone kullanan) kişiler için <tarih d1> İLE <tarih d2> İLE "abc.com" sayfasındaki tüm tıklamaları göster

Boole operatörlerine dikkat edin. Örnekler elde etmek amacıyla isteğe bağlı olarak karmaşık sorgular yapmak için sorgu arayüzünde istemcilerin kullanımına sunulurlar.

Yavaş istek

Söz konusu müşteri sezgisel olarak hızlı çalışması gereken bir şey yapmaya çalışıyordu:

"/jobs" içeren bir URL'ye sahip herhangi bir sayfayı ziyaret eden kullanıcılar için tüm oturum kayıtlarını göster

Bu sitenin çok fazla trafiği vardı ve biz de sırf bunun için bir milyondan fazla benzersiz URL saklıyorduk. Ve iş modelleriyle ilgili oldukça basit bir URL şablonu bulmak istediler.

Ön soruşturma

Veritabanında neler olduğuna bir göz atalım. Orijinal yavaş SQL sorgusu aşağıdadır:

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 ;

Ve işte zamanlamaları:

Planlanan süre: 1.480 ms Uygulama süresi: 1431924.650 ms

Sorgu 150 bin satırı taradı. Sorgu planlayıcı birkaç ilginç ayrıntı gösterdi ancak belirgin bir darboğaz yoktu.

İsteği daha ayrıntılı olarak inceleyelim. Gördüğünüz gibi öyle yapıyor JOIN üç tablo:

oturumları: oturum bilgilerini görüntülemek için: tarayıcı, kullanıcı aracısı, ülke vb.
kayıt_verileri: kayıtlı URL'ler, sayfalar, ziyaretlerin süresi
urls: Aşırı büyük URL'lerin kopyalanmasını önlemek için bunları ayrı bir tabloda saklıyoruz.

Ayrıca tüm tablolarımızın zaten şu şekilde bölümlenmiş olduğunu unutmayın: account_id. Bu şekilde, özellikle büyük bir hesabın başkaları için sorun yaratması durumu ortadan kalkar.

İpuçları arıyorum

Daha yakından incelediğimizde belirli bir istekte bir sorun olduğunu görüyoruz. Bu satıra daha yakından bakmakta fayda var:

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

İlk düşünce şuydu; belki de çünkü ILIKE tüm bu uzun URL'lerde (1,4 milyondan fazla var) benzersiz Bu hesap için toplanan URL'lerin performansı düşebilir.

Ama hayır, konu bu değil!

SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%';
  id
--------
 ...
(198661 rows)

Time: 5231.765 ms

Şablon arama isteğinin kendisi yalnızca 5 saniye sürer. Milyonlarca benzersiz URL'de bir model aramak kesinlikle sorun değil.

Listedeki bir sonraki şüpheli birkaç kişi JOIN. Belki de aşırı kullanımları yavaşlamaya neden olmuştur? Genellikle JOIN'ler performans sorunlarının en bariz adaylarıdır, ancak bizim durumumuzun tipik olduğuna inanmadım.

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

Ve bu bizim durumumuz da değildi. JOINoldukça hızlı olduğu ortaya çıktı.

Şüpheli çemberi daraltılıyor

Olası performans iyileştirmelerini elde etmek için sorguyu değiştirmeye başlamaya hazırdım. Ekibim ve ben 2 ana fikir geliştirdik:

Alt sorgu URL'si için EXISTS'i kullanın: URL'lere ilişkin alt sorguda herhangi bir sorun olup olmadığını tekrar kontrol etmek istedik. Bunu başarmanın bir yolu basitçe kullanmaktır EXISTS. EXISTS kutu koşulla eşleşen tek dizeyi bulduğu anda hemen sona erdiğinden performansı büyük ölçüde artırır.

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

İyi evet. Sarıldığında alt sorgu EXISTS, her şeyi süper hızlı hale getirir. Bir sonraki mantıksal soru, isteğin neden JOIN-ami ve alt sorgunun kendisi tek başına hızlı, ancak birlikte çok yavaş mı?

Alt sorguyu CTE'ye taşıma : Sorgu tek başına hızlıysa, önce hızlı sonucu hesaplayıp daha sonra ana sorguya sağlayabiliriz.

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;

Ama yine de çok yavaştı.

Suçluyu bulmak

Bunca zaman, sürekli bir kenara ittiğim küçük bir şey gözlerimin önünde parladı. Ama başka bir şey kalmadığı için ona da bakmaya karar verdim. Hakkında konuşuyorum && Şebeke. Hoşçakal EXISTS sadece geliştirilmiş performans && yavaş sorgunun tüm sürümlerinde kalan tek ortak faktördü.

Bakmak belgeleme, bunu görüyoruz && İki dizi arasında ortak öğeler bulmanız gerektiğinde kullanılır.

Orijinal istekte bu:

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

Bu, URL'lerimizde bir kalıp araması yaptığımız ve ardından ortak gönderilere sahip tüm URL'lerin kesişimini bulduğumuz anlamına gelir. Bu biraz kafa karıştırıcı çünkü buradaki "url'ler", tüm URL'leri içeren tabloyu değil, tablodaki "url'ler" sütununu ifade ediyor recording_data.

ile ilgili şüphelerin artmasıyla birlikte &&, oluşturulan sorgu planında onlar için onay bulmaya çalıştım EXPLAIN ANALYZE (Kayıtlı bir planım zaten vardı, ancak genellikle sorgu planlayıcıların opaklığını anlamaya çalışmaktansa SQL'de denemeler yapmaktan daha rahatım).

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

Yalnızca birkaç filtre satırı vardı &&. Bu, bu operasyonun sadece pahalı olduğu değil, aynı zamanda birkaç kez gerçekleştirildiği anlamına da geliyordu.

Bunu durumu izole ederek test ettim

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

Bu sorgu yavaştı. Çünkü JOIN-'ler hızlıdır ve alt sorgular hızlıdır, geriye kalan tek şey && Şebeke.

Bu sadece önemli bir operasyon. Bir model aramak için her zaman temel URL tablosunun tamamını aramamız ve her zaman kesişimleri bulmamız gerekir. Doğrudan URL kayıtlarına göre arama yapamayız çünkü bunlar yalnızca URL kayıtlarına atıfta bulunan kimliklerdir. urls.

Çözüm yolunda

&& yavaş çünkü her iki set de çok büyük. Değiştirirsem operasyon nispeten hızlı olacak urls üzerinde { "http://google.com/", "http://wingify.com/" }.

Postgres'te set kavşağını kullanmadan yapmanın bir yolunu aramaya başladım. &&, ama çok başarılı olmadan.

Sonunda sorunu tek başına çözmeye karar verdik: bana her şeyi ver urls URL'nin kalıpla eşleştiği satırlar. Ek koşullar olmadan şu şekilde olacaktır:

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%'

Yerine JOIN sözdizimi Az önce bir alt sorgu kullandım ve genişlettim recording_data.urls koşulu doğrudan uygulayabilmeniz için dizi WHERE.

Burada en önemli şey şu && Belirli bir girişin eşleşen bir URL içerip içermediğini kontrol etmek için kullanılır. Biraz gözlerinizi kısarsanız, bu işlemin bir dizinin (veya tablonun satırlarının) öğeleri arasında hareket ettiğini ve bir koşul (eşleşme) karşılandığında durduğunu görebilirsiniz. Sana hiçbir şey hatırlatmıyor mu? Evet, EXISTS.

O zamandan beri recording_data.urls alt sorgu bağlamının dışından referans alınabilir, bu olduğunda eski dostumuza başvurabiliriz EXISTS ve alt sorguyu onunla sarın.

Her şeyi bir araya getirerek optimize edilmiş son sorguyu elde ederiz:

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%'
    );

Ve son teslim süresi Time: 1898.717 ms Kutlama zamanı?!?

Çok hızlı değil! Öncelikle doğruluğunu kontrol etmeniz gerekiyor. hakkında son derece şüpheciydim EXISTS optimizasyonu daha önce sonlandırmak için mantığı değiştirdiğinden. İsteğe bariz olmayan bir hata eklemediğimizden emin olmamız gerekiyor.

Basit bir test yürütülecekti count(*) Çok sayıda farklı veri kümesi için hem yavaş hem de hızlı sorgularda. Daha sonra verilerin küçük bir alt kümesi için tüm sonuçların doğru olduğunu manuel olarak doğruladım.

Tüm testler tutarlı bir şekilde olumlu sonuçlar verdi. Her şeyi düzelttik!

Dersler öğrenildi

Bu hikayeden çıkarılacak pek çok ders var:

Sorgu planları hikayenin tamamını anlatmaz ancak ipuçları sağlayabilir
Ana şüpheliler her zaman gerçek suçlular değildir
Yavaş sorgular darboğazları izole etmek için parçalara ayrılabilir
Tüm optimizasyonlar doğası gereği indirgeyici değildir
kullanımı EXISTmümkün olduğu durumlarda üretkenlikte dramatik artışlara yol açabilir

Aviator apk

~24 dakikalık bir sorgu süresini 2 saniyeye çıkardık; oldukça önemli bir performans artışı! Her ne kadar bu makale çok büyük çıksa da yaptığımız tüm deneyler bir günde gerçekleşti ve optimizasyon ve testlerin 1,5 ile 2 saat arasında süreceği tahmin ediliyordu.

SQL harika bir dildir, eğer ondan korkmuyorsanız ama öğrenmeye ve kullanmaya çalışın. SQL sorgularının nasıl yürütüldüğünü, veritabanının sorgu planlarını nasıl oluşturduğunu, dizinlerin nasıl çalıştığını ve yalnızca uğraştığınız verilerin boyutunu iyi anlayarak, sorguları optimize etmede çok başarılı olabilirsiniz. Bununla birlikte, farklı yaklaşımları denemeye devam etmek ve sorunu yavaş yavaş çözerek darboğazları bulmak da aynı derecede önemlidir.

Bunun gibi sonuçlara ulaşmanın en iyi yanı, daha önce yüklenmeyen bir raporun artık neredeyse anında yüklendiği fark edilebilir, gözle görülür hız artışıdır.

Özel teşekkürler yoldaşlarım Aditya Mishra'nın komutasında, Aditya Gauru и Varun Malhotra beyin fırtınası yapmak için ve Dinkar Pandir Nihayet veda etmeden önce son isteğimizde önemli bir hata bulduğumuz için!

Kaynak: habr.com

Bir SQL araştırmasının hikayesi