Vladimir Sitnikov'un 2016 başındaki "PostgreSQL ve JDBC tüm gücü tüketiyor" raporunun metnini okumanızı öneririm.
Tünaydın Adım Vladimir Sitnikov. 10 yıldır NetCracker'da çalışıyorum. Ve çoğunlukla üretkenlikle ilgileniyorum. Java ile ilgili her şey, SQL ile ilgili her şey sevdiğim şeydir.
Bugün ise PostgreSQL'i veritabanı sunucusu olarak kullanmaya başladığımızda şirkette nelerle karşılaştığımızdan bahsedeceğim. Ve çoğunlukla Java ile çalışıyoruz. Ama bugün size anlatacaklarım sadece Java ile ilgili değil. Uygulamanın gösterdiği gibi, bu diğer dillerde de geçerlidir.
Biz konuşacağız:
- Veri örneklemesi hakkında.
- Veri kaydetme hakkında.
- Ve ayrıca performans hakkında.
- Ve orada gömülü olan su altı tırmıkları hakkında.
Basit bir soruyla başlayalım. Tablodan birincil anahtara göre bir satır seçiyoruz.
Veritabanı aynı ana bilgisayarda bulunur. Ve tüm bu çiftçilik 20 milisaniye sürüyor.
Bu 20 milisaniye çok fazla. Bu tür 100 isteğiniz varsa, bu istekler arasında gezinmek için saniyede zaman harcıyorsunuz, yani zaman harcıyoruz.
Bunu yapmayı sevmiyoruz ve üssün bize bunun için neler sunduğuna bakıyoruz. Veritabanı bize sorguları yürütmek için iki seçenek sunar.
İlk seçenek basit bir istektir. Bunun nesi iyi? Onu alıp göndermemiz gerçeği, başka bir şey değil.
Veritabanında ayrıca daha karmaşık ama daha işlevsel olan gelişmiş bir sorgu bulunur. Ayrıştırma, yürütme, değişken bağlama vb. için ayrı ayrı istek gönderebilirsiniz.
Süper genişletilmiş sorgu, mevcut raporda ele almayacağımız bir şeydir. Belki veri tabanından bir şeyler istiyoruz ve bir şekilde oluşturulmuş bir istek listesi var, yani istediğimiz bu, ancak bu şimdi ve gelecek yıl imkansız. Biz de bunu yeni kaydettik ve etrafta dolaşıp ana insanları sallayacağız.
Yapabileceğimiz şey basit sorgu ve genişletilmiş sorgudur.
Her yaklaşımın özelliği nedir?
Basit bir sorgu tek seferlik yürütme için iyidir. Bir kez yapılır ve unutulur. Ve sorun şu ki, ikili veri formatını desteklemiyor, yani bazı yüksek performanslı sistemler için uygun değil.
Genişletilmiş sorgu – ayrıştırma sırasında zaman kazanmanıza olanak tanır. Biz de bunu yaptık ve kullanmaya başladık. Bu bize gerçekten çok yardımcı oldu. Yalnızca ayrıştırmada tasarruf sağlanmaz. Veri aktarımında tasarruf var. Verilerin ikili formatta aktarılması çok daha verimlidir.
Hadi uygulamaya geçelim. Tipik bir uygulama böyle görünür. Java vb. olabilir.
Açıklamayı oluşturduk. Komutu yürüttü. Yakın oluşturuldu. Burada hata nerede? Sorun nedir? Sorun değil. Bütün kitaplarda bu yazıyor. Bu şekilde yazılmalıdır. Maksimum performans istiyorsanız bu şekilde yazın.
Ancak uygulama bunun işe yaramadığını gösterdi. Neden? Çünkü bizim "kapat" yöntemimiz var. Ve bunu yaptığımızda, veritabanı açısından bakıldığında bunun sigara içen birinin veritabanıyla çalışmasına benzediği ortaya çıkıyor. "AYRIŞTIRMA ÇALIŞTIR DAĞIT" dedik.
Neden tüm bu ekstra ifade oluşturma ve boşaltma? Kimsenin onlara ihtiyacı yok. Ancak, PrettyStatements'ta genellikle olan şey, onları kapattığımızda veritabanındaki her şeyi kapatmalarıdır. İstediğimiz bu değil.
Sağlıklı insanlar gibi tabanla çalışmak istiyoruz. İfademizi bir kere alıp hazırladık, sonra defalarca icra ediyoruz. Aslında birçok kez (bu, uygulamaların ömrü boyunca bir kez olur) ayrıştırılmıştır. Ve aynı ifade kimliğini farklı REST'lerde kullanıyoruz. Bu bizim hedefimiz.
Bunu nasıl başarabiliriz?
Çok basit; açıklamaları kapatmaya gerek yok. Bunu şu şekilde yazıyoruz: “hazırla” “yürüt”.
Böyle bir şeyi başlatırsak bir yerden bir şeylerin taşacağı açıktır. Net değilse, deneyebilirsiniz. Bu basit yöntemi kullanan bir kıyaslama yazalım. Bir beyan oluşturun. Bunu sürücünün bir sürümünde başlatıyoruz ve sahip olduğu tüm hafızanın kaybıyla oldukça hızlı bir şekilde çöktüğünü görüyoruz.
Bu tür hataların kolaylıkla düzeltilebileceği açıktır. Onlar hakkında konuşmayacağım. Ancak yeni sürümün çok daha hızlı çalıştığını söyleyeceğim. Yöntem aptalca ama yine de.
Nasıl doğru çalışılır? Bunun için ne yapmamız gerekiyor?
Gerçekte uygulamalar her zaman ifadeleri kapatır. Bütün kitaplarda kapatılması söylenir, aksi takdirde hafıza sızar.
Ve PostgreSQL sorguların nasıl önbelleğe alınacağını bilmiyor. Her oturumun bu önbelleği kendisi için oluşturması gereklidir.
Ayrıca ayrıştırmayla da zaman kaybetmek istemiyoruz.
Ve her zamanki gibi iki seçeneğimiz var.
İlk seçenek onu alıp her şeyi PgSQL'e saralım diyoruz. Orada bir önbellek var. Her şeyi önbelleğe alır. Harika olacak. Bunu gördük. 100500 talebimiz var. Çalışmıyor. Talepleri manuel olarak prosedürlere dönüştürmeyi kabul etmiyoruz. Hayır hayır.
İkinci bir seçeneğimiz var; onu alıp kendimiz keseceğiz. Kaynakları açıp kesmeye başlıyoruz. Gördük ve gördük. Bunu yapmanın o kadar da zor olmadığı ortaya çıktı.
Bu Ağustos 2015'te ortaya çıktı. Şimdi daha modern bir versiyonu var. Ve her şey harika. O kadar iyi çalışıyor ki uygulamada hiçbir şeyi değiştirmiyoruz. Hatta PgSQL yönünde düşünmeyi bile bıraktık, yani bu, tüm genel giderleri neredeyse sıfıra indirmemiz için oldukça yeterliydi.
Buna göre, her bir defalık istekte veritabanındaki hafızanın boşa harcanmasını önlemek için Sunucu tarafından hazırlanan ifadeler 5. yürütmede etkinleştirilir.
Şunu sorabilirsiniz; sayılar nerede? Ne alıyorsun? Ve burada rakam vermeyeceğim çünkü her talebin kendine ait bir talebi var.
Sorgularımız öyleydi ki OLTP sorgularını ayrıştırmak için yaklaşık 20 milisaniye harcadık. Yürütme için 0,5 milisaniye, ayrıştırma için 20 milisaniye vardı. Talep – 10 KiB metin, 170 satırlık plan. Bu bir OLTP isteğidir. 1, 5, 10 satır, bazen daha fazlasını ister.
Ama 20 milisaniyeyi hiç boşa harcamak istemedik. 0'a düşürdük. Her şey yolunda.
Buradan ne alabilirsin? Java'nız varsa, sürücünün modern sürümünü alırsınız ve sevinirsiniz.
Farklı bir dil konuşuyorsanız, düşünün - belki buna da ihtiyacınız var? Çünkü son dil açısından bakıldığında, örneğin, eğer PL 8 veya LibPQ'nuz varsa, o zaman yürütmeye, ayrıştırmaya zaman harcamadığınız sizin için açık değildir ve bu kontrol edilmeye değerdir. Nasıl? Her şey ücretsizdir.
Bunun dışında hatalar ve bazı tuhaflıklar var. Ve şimdi onlar hakkında konuşacağız. Çoğu endüstriyel arkeolojiyle, ne bulduğumuzla, neyle karşılaştığımızla ilgili olacak.
İstek dinamik olarak oluşturulmuşsa. Olur. Birisi dizeleri birbirine yapıştırıyor ve sonuçta bir SQL sorgusu oluşuyor.
O neden kötü? Bu kötü çünkü her seferinde farklı bir dizeyle karşılaşıyoruz.
Ve bu farklı stringin hashCode'unun tekrar okunması gerekiyor. Bu gerçekten bir CPU görevidir; mevcut bir karmada bile uzun bir istek metni bulmak o kadar kolay değildir. Bu nedenle sonuç basit: istek oluşturmayın. Bunları tek bir değişkende saklayın. Ve sevinin.
Sonraki sorun. Veri türleri önemlidir. Ne tür bir NULL olduğunun önemli olmadığını söyleyen ORM'ler var, bırakın bir tür olsun. Int ise setInt deriz. Ve eğer NULL ise, o zaman her zaman VARCHAR olsun. Ve sonuçta NULL'un ne olduğu ne fark eder? Veritabanının kendisi her şeyi anlayacaktır. Ve bu resim çalışmıyor.
Uygulamada veritabanı hiç umursamıyor. İlk kez bunun bir sayı olduğunu söylediyseniz ve ikinci kez bunun bir VARCHAR olduğunu söylediyseniz, Sunucu tarafından hazırlanan ifadeleri yeniden kullanmak imkansızdır. Ve bu durumda ifademizi yeniden oluşturmamız gerekiyor.
Aynı sorguyu yürütüyorsanız sütununuzdaki veri türlerinin karışık olmadığından emin olun. NULL'a dikkat etmeniz gerekir. Bu, PrettyStatements'ı kullanmaya başladıktan sonra karşılaştığımız yaygın bir hatadır.
Tamam, açıldı. Belki sürücüyü götürmüşlerdir. Ve üretkenlik düştü. İşler kötüye gitti.
Bu nasıl oluyor? Bu bir hata veya özellik mi? Bunun bir hata mı yoksa bir özellik mi olduğunu anlamak maalesef mümkün olmadı. Ancak bu sorunu yeniden oluşturmak için çok basit bir senaryo var. Tamamen beklenmedik bir şekilde bizi pusuya düşürdü. Ve kelimenin tam anlamıyla tek bir tablodan örneklemeden oluşur. Elbette bu tür isteklerimiz daha da arttı. Kural olarak iki veya üç tablo içeriyorlardı, ancak böyle bir oynatma senaryosu var. Veritabanınızdan herhangi bir sürümü alın ve oynayın.
Mesele şu ki, her biri indekslenmiş iki sütunumuz var. Bir NULL sütununda bir milyon satır var. İkinci sütun ise yalnızca 20 satır içeriyor. Sınırlı değişkenler olmadan çalıştırdığımızda her şey iyi çalışır.
Bağlı değişkenlerle yürütmeye başlarsak, yani "?" veya isteğimiz için "1$" alırsak ne elde ederiz?
İlk uygulama beklendiği gibi oldu. İkincisi biraz daha hızlı. Bir şey önbelleğe alındı. Üçüncü, dördüncü, beşinci. Sonra bang - ve buna benzer bir şey. Ve en kötüsü de bunun altıncı infazda gerçekleşmesi. Gerçek infaz planının ne olduğunu anlamak için tam olarak altı infaz yapılması gerektiğini kim bilebilirdi?
Kim suçlu? Ne oldu? Veritabanı optimizasyonu içerir. Ve genel durum için optimize edilmiş gibi görünüyor. Ve buna göre, bir noktadan başlayarak, maalesef farklı olabilecek genel bir plana geçiyor. Aynısı da çıkabilir, farklı da olabilir. Ve bu davranışa yol açan bir çeşit eşik değeri var.
Bu konuda ne yapabilirsiniz? Burada elbette herhangi bir şeyi varsaymak daha zordur. Kullandığımız basit bir çözüm var. Bu +0, OFFSET 0. Bu tarz çözümleri mutlaka biliyorsunuzdur. Sadece onu alıyoruz ve isteğe “+0” ekliyoruz ve her şey yolunda. Sana sonra göstereceğim.
Ve başka bir seçenek daha var - planlara daha dikkatli bakın. Geliştiricinin sadece istek yazmaması aynı zamanda 6 kez “analizi açıkla” demesi gerekiyor. Eğer 5 ise işe yaramaz.
Ve üçüncü bir seçenek daha var - pgsql korsanlarına bir mektup yazmak. Yazdım ancak bunun bir hata mı yoksa özellik mi olduğu henüz belli değil.
Bu bir hata mı, yoksa bir özellik mi diye düşünürken gelin düzeltelim. İsteğimizi alıp "+0" ekleyelim. Herşey yolunda. İki sembol ve bunun nasıl olduğunu veya ne olduğunu düşünmenize bile gerek yok. Çok basit. Veritabanının bu sütunda indeks kullanmasını yasakladık. “+0” sütununda indeksimiz yok ve işte bu, veritabanı indeksi kullanmıyor, her şey yolunda.
Bu 6 kuralını açıklayın. Şimdi mevcut sürümlerde, bağlı değişkenleriniz varsa bunu 6 kez yapmanız gerekir. Bağlı değişkenleriniz yoksa yaptığımız şey budur. Ve sonunda başarısız olan tam da bu istektir. Bu zor bir şey değil.
Görünüşe göre ne kadar mümkün? Burada bir böcek, orada bir böcek. Aslında hata her yerde.
Hadi daha yakından bakalım. Örneğin iki şemamız var. S tablosuyla A Şeması ve S tablosuyla B şeması. Sorgu – bir tablodan veri seçin. Bu durumda elimizde ne kalacak? Bir hatamız olacak. Yukarıdakilerin hepsine sahip olacağız. Kural şu; her yerde bir böcek var, yukarıdakilerin hepsine sahip olacağız.
Şimdi soru şu: “Neden?” Görünüşe göre, eğer bir şemamız varsa, o zaman bize tabloyu nerede arayacağımızı söyleyen bir "search_path" değişkeni olduğuna dair belgeler var. Görünüşe göre bir değişken var.
Sorun nedir? Sorun, sunucu tarafından hazırlanan ifadelerin, arama_yolunun birisi tarafından değiştirilebileceğinden şüphelenmemesidir. Bu değer veritabanı için olduğu gibi sabit kalır. Ve bazı kısımlar yeni anlamlar kazanmayabilir.
Elbette bu, test ettiğiniz sürüme bağlıdır. Tablolarınızın ne kadar ciddi şekilde farklılaştığına bağlıdır. Ve 9.1 sürümü sadece eski istekleri yerine getirecek. Yeni sürümler hatayı yakalayabilir ve size bir hatanın olduğunu söyleyebilir.
Nasıl tedavi edilir? Basit bir tarif var; yapmayın. Uygulama çalışırken arama_yolunu değiştirmeye gerek yoktur. Değiştirirseniz yeni bir bağlantı oluşturmak daha iyidir.
Tartışabilirsiniz, yani açabilir, tartışabilir, ekleyebilirsiniz. Belki veritabanı geliştiricilerini, birisi bir değeri değiştirdiğinde veritabanının müşteriye şunu söylemesi gerektiğine ikna edebiliriz: “Bakın, değeriniz burada güncellendi. Belki de ifadeleri sıfırlayıp yeniden oluşturmanız gerekiyor?” Artık veritabanı gizli davranıyor ve ifadelerin içeride bir yerde değiştiğini hiçbir şekilde bildirmiyor.
Tekrar vurgulayacağım ki bu Java'ya özgü olmayan bir şey. Aynı şeyi PL/pgSQL'de de birebir göreceğiz. Ama orada çoğaltılacak.
Biraz daha veri seçmeyi deneyelim. Seçiyoruz ve seçiyoruz. Milyonlarca satırdan oluşan bir tablomuz var. Her satır bir kilobayttır. Yaklaşık bir gigabayt veri. Ve Java makinesinde 128 megabaytlık çalışan bir hafızamız var.
Tüm kitaplarda önerildiği gibi akış işlemeyi kullanıyoruz. Yani resultSet'i açıp oradan verileri yavaş yavaş okuyoruz. İşe yarayacak mı? Hafızadan düşecek mi? Biraz okur musun? Veritabanına güvenelim, Postgres'e güvenelim. Biz buna inanmıyoruz. OutOFMemory'ye düşecek miyiz? OutOfMemory'yi kimler deneyimledi? Bundan sonra bunu kim düzeltmeyi başardı? Birisi bunu düzeltmeyi başardı.
Milyonlarca satırınız varsa, öylece seçip seçemezsiniz. OFSET/LIMIT gerekli. Bu seçeneği kim tercih ediyor? Ve kim autoCommit ile oynamayı destekliyor?
Burada her zamanki gibi en beklenmedik seçeneğin doğru olduğu ortaya çıkıyor. Ve eğer aniden autoCommit'i kapatırsanız, bunun faydası olacaktır. Nedenmiş? Bilim bunu bilmiyor.
Ancak varsayılan olarak Postgres veritabanına bağlanan tüm istemciler verilerin tamamını getirir. PgJDBC bu konuda bir istisna değildir; tüm satırları seçer.
FetchSize temasının bir varyasyonu var, yani ayrı bir ifade düzeyinde burada lütfen verileri 10, 50'ye kadar seçin diyebilirsiniz. Ancak bu, siz autoCommit'i kapatana kadar çalışmaz. AutoCommit kapatıldı - çalışmaya başlıyor.
Ancak kodun üzerinden geçmek ve her yerde setFetchSize ayarını yapmak zahmetlidir. Bu nedenle bağlantının tamamı için varsayılan değeri söyleyecek bir ayar yaptık.
Biz de öyle demiştik. Parametre yapılandırıldı. Peki ne elde ettik? Küçük miktarlar seçersek, örneğin 10 satırı tek seferde seçersek, o zaman çok büyük genel giderlerimiz olur. Bu nedenle bu değerin yüz civarına ayarlanması gerekir.
İdeal durumda elbette bunu bayt cinsinden nasıl sınırlayacağınızı öğrenmeniz gerekir, ancak tarif şudur: defaultRowFetchSize'ı yüzün üzerine ayarlayın ve mutlu olun.
Veri eklemeye devam edelim. Ekleme daha kolaydır, farklı seçenekler vardır. Örneğin, INSERT, VALUES. Bu iyi bir seçenek. “SEÇİMİ EKLEYİN” diyebilirsiniz. Pratikte de durum aynı. Performansta hiçbir fark yoktur.
Kitaplar bir Toplu deyimi yürütmeniz gerektiğini söylüyor, kitaplar birkaç parantezle daha karmaşık komutları yürütebileceğinizi söylüyor. Ve Postgres'in harika bir özelliği var - KOPYALAMA yapabilirsiniz, yani daha hızlı yapabilirsiniz.
Eğer ölçerseniz yine ilginç keşifler yapabilirsiniz. Bunun nasıl çalışmasını istiyoruz? Gereksiz komutları ayrıştırmamak ve yürütmemek istiyoruz.
Pratikte TCP bunu yapmamıza izin vermiyor. İstemci bir istek göndermekle meşgulse, veritabanı bize yanıt gönderme girişimlerinde istekleri okumaz. Sonuçta istemci veritabanının isteği okumasını bekler ve veritabanı da istemcinin yanıtı okumasını bekler.
Bu nedenle istemci periyodik olarak bir senkronizasyon paketi göndermek zorunda kalır. Ekstra ağ etkileşimleri, ekstra zaman kaybı.
Ve bunları ekledikçe durum daha da kötüleşiyor. Sürücü oldukça kötümser ve satırların boyutuna vs. bağlı olarak bunları oldukça sık, yaklaşık her 200 satırda bir ekliyor.
Sadece bir satırı düzelttiğinizde her şey 10 kat hızlanacaktır. Olur. Neden? Her zamanki gibi buna benzer bir sabit zaten bir yerlerde kullanılmıştı. Ve “128” değeri toplu işlemin kullanılmaması anlamına geliyordu.
Bunun resmi versiyona dahil edilmemesi iyi. Sürüm başlamadan önce keşfedildi. Verdiğim tüm anlamlar modern versiyonlara dayanmaktadır.
Hadi deneyelim. InsertBatch'i basit bir şekilde ölçüyoruz. InsertBatch'i birden çok kez ölçüyoruz, yani aynı şeyi, ancak birçok değer var. Zor bir hareket. Bunu herkes yapamaz ama bu çok basit bir harekettir, KOPYALAMA'dan çok daha kolaydır.
KOPYALAMA yapabilirsiniz.
Ve bunu yapılar üzerinde yapabilirsiniz. Kullanıcının varsayılan türünü bildirin, diziyi iletin ve INSERT'i doğrudan tabloya aktarın.
Bağlantıyı açarsanız: pgjdbc/ubenchmsrk/InsertBatch.java, bu kod GitHub'dadır. Burada özellikle hangi isteklerin oluşturulduğunu görebilirsiniz. Önemli değil.
Başlattık. Ve fark ettiğimiz ilk şey toplu iş kullanmamanın kesinlikle imkansız olduğuydu. Tüm toplu işlem seçenekleri sıfırdır; yani yürütme süresi, tek seferlik yürütmeyle karşılaştırıldığında neredeyse sıfırdır.
Verileri ekliyoruz. Çok basit bir tablo. Üç sütun. Peki burada ne görüyoruz? Bu seçeneklerin üçünün de kabaca karşılaştırılabilir olduğunu görüyoruz. Ve KOPYALAMA elbette daha iyidir.
Bu, parçaları yerleştirdiğimiz zamandır. Bir DEĞER değeri, iki DEĞER değeri, üç DEĞER değeri derken veya bunlardan 10'unu virgülle ayırarak belirttik. Bu artık sadece yatay. 1, 2, 4, 128. Mavi renkle çizilen Toplu Ekin kendisini çok daha iyi hissettirdiği görülüyor. Yani, birer birer eklediğinizde, hatta dörter birer eklediğinizde, iki kat daha iyi oluyor, çünkü DEĞERLER'e biraz daha fazla sıkıştırdık. Daha az EXECUTE işlemi.
KOPYALAMA'yı küçük hacimlerde kullanmak son derece taviz vermez. İlk ikisine çizim bile yapmadım. Cennete yani KOPYA için bu yeşil numaralara gidiyorlar.
En az yüz satırlık veriye sahip olduğunuzda COPY kullanılmalıdır. Bu bağlantıyı açmanın maliyeti büyüktür. Ve dürüst olmak gerekirse, bu yönde araştırma yapmadım. Batch'i optimize ettim ancak COPY'yi optimize etmedim.
Sonra ne yapıyoruz? Biz bunu denedik. Ya yapıları ya da birçok anlamı birleştiren akıllı bir banyoyu kullanmamız gerektiğini anlıyoruz.
Bugünkü rapordan ne çıkarmalısınız?
- ReadyStatement bizim her şeyimizdir. Bu üretkenlik açısından çok şey sağlar. Merhemde büyük bir flop üretir.
- Ve 6 kez AÇIKLAMA ANALİZİ yapmanız gerekiyor.
- Sorunlu sorgularımızın geri kalan yüzdesini düzeltmek için OFFSET 0'ı sulandırmamız ve +0 gibi hilelere ihtiyacımız var.
Kaynak: habr.com