İnşaatçılar için B2B hizmeti örneğini kullanarak veritabanı sorgularını optimize etme

Daha üretken bir sunucuya geçmeden ve sistem işlevselliğini sürdürmeden, veritabanına yapılan sorgu sayısını 10 kat nasıl artırabilirsiniz? Size veritabanımızın performansındaki düşüşle nasıl başa çıktığımızı, SQL sorgularını mümkün olduğunca çok kullanıcıya hizmet verecek ve bilgi işlem kaynaklarının maliyetini artırmayacak şekilde nasıl optimize ettiğimizi anlatacağım.

İnşaat firmalarında iş süreçlerinin yönetilmesine yönelik hizmet veriyorum. 3 bine yakın firma bizimle çalışıyor. Sistemimizle her gün 10 binden fazla kişi 4-10 saat çalışıyor. Planlama, bildirim, uyarı, doğrulama gibi çeşitli sorunları çözer... PostgreSQL 9.6 kullanıyoruz. Veritabanımızda yaklaşık 300 tablo var ve her gün 200 milyona kadar (10 bin farklı) sorgu alınıyor. Ortalama olarak saniyede 3-4 bin istek alıyoruz, en aktif anlarda ise saniyede 10 binin üzerinde istek alıyoruz. Sorguların çoğu OLAP'tır. Çok daha az ekleme, değişiklik ve silme vardır; bu da OLTP yükünün nispeten hafif olduğu anlamına gelir. Tüm bu rakamları projemizin ölçeğini değerlendirebilmeniz ve deneyimlerimizin sizin için ne kadar yararlı olabileceğini anlayabilmeniz için verdim.

Birinci resim. Lirik

Geliştirmeye başladığımızda veritabanına ne tür bir yük düşeceğini ve sunucu çekmeyi bırakırsa ne yapacağımızı pek düşünmedik. Veritabanını tasarlarken genel tavsiyelere uyduk ve kendi ayağımıza kurşun sıkmamaya çalıştık ama “deseni kullanmayın” gibi genel tavsiyelerin ötesine geçtik. Varlık Öznitelik Değerleri içeri girmedik. Normalleştirme, veri fazlalığından kaçınma ilkelerine göre tasarladık ve belirli sorguları hızlandırmayı umursamadık. İlk kullanıcılar gelir gelmez performans sorunuyla karşılaştık. Her zamanki gibi buna tamamen hazırlıksızdık. İlk sorunların basit olduğu ortaya çıktı. Kural olarak, her şey yeni bir dizin eklenerek çözüldü. Ancak basit yamaların çalışmayı bıraktığı bir zaman geldi. Tecrübemizin eksik olduğunu ve sorunlara neyin sebep olduğunu anlamamızın giderek zorlaştığını fark ederek, sunucuyu doğru şekilde kurmamıza, izlemeyi bağlamamıza yardımcı olan ve nereye bakacağımızı bize gösteren uzmanları işe aldık. istatistik.

İkinci resim. İstatistiksel

Yani veritabanımızda günde yaklaşık 10 bin farklı sorgu yürütülüyor. Bu 10 binin içinde ortalama 2-3 ms yürütme süresine sahip 0.1-0.3 milyon kez çalıştırılan canavarlar var ve günde 30 kez çağrılan ortalama yürütme süresi 100 saniye olan sorgular var.

10 bin sorgunun tamamını optimize etmek mümkün olmadığından veritabanı performansını doğru bir şekilde artırmak için çabalarımızı nereye yönlendireceğimizi bulmaya karar verdik. Birkaç yinelemeden sonra istekleri türlere ayırmaya başladık.

EN İYİ istekler

Bunlar en çok zaman alan (toplam süre) en ağır sorgulardır. Bunlar ya çok sık çağrılan sorgulardır ya da yürütülmesi çok uzun zaman alan sorgulardır (uzun ve sık sorgular, hız mücadelesinin ilk yinelemelerinde optimize edilmiştir). Sonuç olarak, sunucu en fazla zamanı bunların yürütülmesine harcar. Ayrıca, en yüksek istekleri toplam yürütme süresine göre ve ayrı olarak IO zamanına göre ayırmak önemlidir. Bu tür sorguları optimize etme yöntemleri biraz farklıdır.

Tüm şirketlerin olağan uygulaması, TOP talepleriyle çalışmaktır. Bunlardan çok azı var; tek bir sorguyu optimize etmek bile kaynakların %5-10'unu serbest bırakabilir. Ancak proje olgunlaştıkça, TOP sorgularını optimize etmek giderek önemsiz olmayan bir görev haline geliyor. Tüm basit yöntemler zaten geliştirildi ve en "ağır" istek, kaynakların "yalnızca"% 3-5'ini alıyor. Toplamda EN İYİ sorgular zamanın %30-40'ından azını alıyorsa, büyük olasılıkla bunların hızlı bir şekilde çalışmasını sağlamak için zaten çaba sarf etmişsinizdir ve bir sonraki gruptaki sorguları optimize etmeye geçmenin zamanı gelmiştir.
Bu gruba en çok kaç sorgunun dahil edilmesi gerektiği sorusunu yanıtlamak kalıyor. Genellikle en az 10, en fazla 20 alıyorum. TOP grubundaki ilk ve sonuncunun zamanının 10 kattan fazla farklılık göstermemesini sağlamaya çalışıyorum. Yani sorgu yürütme süresi 1. sıradan 10. sıraya keskin bir şekilde düşerse, o zaman TOP-10'u alırım, düşüş daha kademeli ise grup boyutunu 15 veya 20'ye çıkarırım.

Orta köylüler

Bunların tümü, son %5-10'luk kısım hariç, TOP'tan hemen sonra gelen isteklerdir. Genellikle bu sorguları optimize etmede sunucu performansını büyük ölçüde artırma fırsatı bulunur. Bu taleplerin ağırlığı %80'e kadar çıkabilir. Ancak payları yüzde 50'yi aşmış olsa bile artık onlara daha dikkatli bakmanın zamanı geldi.

Kuyruk

Belirtildiği gibi bu sorgular en sonda gelir ve zamanın %5-10'unu alır. Ancak otomatik sorgu analiz araçlarını kullanmazsanız bunları unutabilirsiniz, o zaman bunları optimize etmek de ucuz olabilir.

Her grup nasıl değerlendirilecek?

PostgreSQL için böyle bir değerlendirme yapmaya yardımcı olan bir SQL sorgusu kullanıyorum (Eminim ki benzer bir sorgu diğer birçok DBMS için de yazılabilir)

TOP-MEDIUM-KUYRUK gruplarının boyutunu tahmin etmek için SQL sorgusu

SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) AS sum_tail
FROM
(
  SELECT CASE WHEN rn <= 20              THEN tt_percent ELSE 0 END AS time_top,
         CASE WHEN rn > 20 AND rn <= 800 THEN tt_percent ELSE 0 END AS time_medium,
         CASE WHEN rn > 800              THEN tt_percent ELSE 0 END AS time_tail
  FROM (
    SELECT total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query,
    ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn
    FROM pg_stat_statements
    ORDER BY total_time DESC
  ) AS t
)
AS ts

Sorgunun sonucu, her biri bu gruptan gelen sorguların işlenmesi için gereken sürenin yüzdesini içeren üç sütundur. Talebin içinde, talepleri bir gruptan diğerinden ayıran iki sayı (benim durumumda 20 ve 800) var.

Optimizasyon çalışmasının başladığı zamandaki ve şimdiki istek payları kabaca bu şekilde karşılaştırılır.

Diyagram, TOP taleplerinin payının keskin bir şekilde azaldığını, ancak "orta köylülerin" arttığını gösteriyor.
İlk başta, TOP istekleri tamamen gafları içeriyordu. Zamanla çocukluk hastalıkları ortadan kalktı, TOP taleplerinin payı azaldı ve zor talepleri hızlandırmak için giderek daha fazla çaba sarf edilmesi gerekti.

İstek metnini almak için aşağıdaki isteği kullanıyoruz

SELECT * FROM (
  SELECT ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn, total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query
  FROM pg_stat_statements
  ORDER BY total_time DESC
) AS T
WHERE
rn <= 20 -- TOP
-- rn > 20 AND rn <= 800 -- MEDIUM
-- rn > 800  -- TAIL

EN İYİ sorguları hızlandırmamıza yardımcı olan en sık kullanılan tekniklerin listesi aşağıda verilmiştir:

• Sistemin yeniden tasarlanması, örneğin veritabanına yapılan periyodik sorgular yerine bir mesaj aracısı kullanılarak bildirim mantığının yeniden işlenmesi
• Dizin ekleme veya değiştirme
• ORM sorgularını saf SQL'e yeniden yazma
• Tembel veri yükleme mantığını yeniden yazma
• Verilerin normalleştirilmesi yoluyla önbelleğe alma. Örneğin Teslimat -> Fatura -> Talep -> Uygulama şeklinde bir tablo bağlantımız var. Yani her teslimat diğer tablolar aracılığıyla bir uygulamayla ilişkilendirilir. Her istekteki tüm tabloları birbirine bağlamamak için, Teslimat tablosundaki isteğin bağlantısını kopyaladık.
• Statik tabloların referans kitapları ile önbelleğe alınması ve program belleğindeki tabloların nadiren değiştirilmesi.

Bazen değişiklikler etkileyici bir yeniden tasarım anlamına geliyordu, ancak sistem yükünün %5-10'unu sağlıyorlardı ve haklıydılar. Zamanla egzoz giderek azaldı ve giderek daha ciddi bir yeniden tasarım gerekliydi.

Daha sonra dikkatimizi ikinci talep grubuna, yani orta köylü grubuna çevirdik. İçinde çok daha fazla sorgu var ve grubun tamamını analiz etmek çok zaman alacak gibi görünüyordu. Ancak çoğu sorgunun optimize edilmesinin çok basit olduğu ve birçok sorunun farklı varyasyonlarda onlarca kez tekrarlandığı ortaya çıktı. Düzinelerce benzer sorguya uyguladığımız bazı tipik optimizasyonların örneklerini burada bulabilirsiniz ve optimize edilmiş sorguların her bir grubu, veritabanının yükünü %3-5 oranında boşalttı.

• COUNT ve tam tablo taraması kullanarak kayıtların varlığını kontrol etmek yerine EXISTS kullanılmaya başlandı
• DISTINCT'ten kurtuldum (genel bir tarif yok ama bazen isteği 10-100 kat hızlandırarak kolayca kurtulabilirsiniz).

  Örneğin, büyük bir teslimat tablosundan tüm sürücüleri seçmek için bir sorgu yerine (TESLİMAT)

  SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME
  FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.ID
  

  nispeten küçük bir tablo üzerinde sorgu yaptı KİŞİ

  SELECT P.ID, P.FIRST_NAME, P.LAST_NAME
  FROM PERSON
  WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)
  

  Görünüşe göre ilişkili bir alt sorgu kullandık, ancak bu 10 kattan fazla hızlanma sağlıyor.

• Çoğu durumda COUNT tamamen terk edildi ve
  yaklaşık değerin hesaplanmasıyla değiştirildi
• yerine

  UPPER(s) LIKE JOHN%’ 
  

  kullanmak

  s ILIKE “John%”
  

Her özel istek bazen 3-1000 kat hızlandırıldı. Etkileyici göstergelere rağmen, ilk başta bize 10 ms boyunca çalışan, en ağır 3. yüz sorgudan biri olan ve veritabanındaki toplam yükleme süresinin yüzde yüzde birini kaplayan bir sorguyu optimize etmenin bir anlamı yok gibi geldi. Ancak aynı tarifi aynı türden bir grup sorguya uygulayarak yüzde birkaçını geri kazandık. Yüzlerce sorgunun tamamını manuel olarak inceleyerek zaman kaybetmemek amacıyla, aynı türdeki sorguları bulmak için düzenli ifadeler kullanan birkaç basit komut dosyası yazdık. Sonuç olarak, sorgu gruplarının otomatik olarak aranması, mütevazı bir çabayla performansımızı daha da artırmamıza olanak sağladı.

Sonuç olarak üç yıldır aynı donanım üzerinde çalışıyoruz. Ortalama günlük yük yaklaşık %30'dur, zirvelerde ise %70'e ulaşır. Talep sayısı ve kullanıcı sayısı yaklaşık 10 kat arttı. Ve tüm bunlar, aynı TOP-MEDIUM istek gruplarının sürekli izlenmesi sayesinde. TOP grubunda yeni bir istek göründüğünde hemen onu analiz edip hızlandırmaya çalışıyoruz. MEDIUM grubunu haftada bir kez sorgu analizi komut dosyalarını kullanarak inceliyoruz. Zaten nasıl optimize edeceğimizi bildiğimiz yeni sorgularla karşılaşırsak, bunları hızla değiştiririz. Bazen birden fazla sorguya aynı anda uygulanabilecek yeni optimizasyon yöntemleri buluyoruz.

Tahminlerimize göre mevcut sunucu, kullanıcı sayısında 3-5 kat daha artışa dayanabilecek. Doğru, elimizde bir koz daha var - tavsiye edildiği gibi hâlâ SELECT sorgularını aynaya aktarmadık. Ancak bunu bilinçli olarak yapmıyoruz çünkü "ağır silahları" açmadan önce "akıllı" optimizasyon olanaklarını tamamen tüketmek istiyoruz.
Yapılan işe eleştirel bir bakış, dikey ölçeklendirmenin kullanılmasını önerebilir. Uzmanların zamanını boşa harcamak yerine daha güçlü bir sunucu satın alın. Özellikle dikey ölçeklendirmenin sınırlarını henüz tüketmediğimiz için sunucunun maliyeti o kadar yüksek olmayabilir. Ancak sadece talep sayısı 10 kat arttı. Birkaç yıl içinde sistemin işlevselliği arttı ve artık daha fazla istek türü var. Önbelleğe alma sayesinde var olan işlevsellik daha az istekle ve daha verimli isteklerle gerçekleştirilir. Bu, gerçek ivme katsayısını elde etmek için güvenli bir şekilde başka bir 5 ile çarpabileceğiniz anlamına gelir. Yani en ihtiyatlı tahminlere göre ivmenin 50 kat veya daha fazla olduğunu söyleyebiliriz. Bir sunucuyu dikey olarak sallamak 50 kat daha pahalıya mal olur. Özellikle optimizasyon yapıldıktan sonra her zaman çalıştığını ve kiralanan sunucunun faturasının her ay geldiğini düşünürsek.

Kaynak: habr.com