🥇PostgreSQL dahili istatistiklerine derinlemesine bakış. Alexey Lesovski

Alexey Lesovsky'nin 2015 tarihli "PostgreSQL dahili istatistiklerine derinlemesine bakış" raporunun metni

Raporun yazarının sorumluluk reddi beyanı: Bu raporun Kasım 2015 tarihli olduğunu belirtmek isterim - 4 yıldan fazla zaman geçti ve çok zaman geçti. Raporda tartışılan 9.4 sürümü artık desteklenmiyor. Son 4 yılda istatistiklerle ilgili pek çok yenilik, iyileştirme ve değişikliğin yer aldığı, bazı materyallerin güncelliğini yitirmiş ve konuyla alakası olmayan 5 yeni sürüm yayınlandı. İncelemeyi yaparken okuyucuyu yanıltmamak adına bu yerleri işaretlemeye çalıştım. Bu pasajları yeniden yazmadım, birçoğu var ve sonuç tamamen farklı bir rapor olacak.

PostgreSQL DBMS çok büyük bir mekanizmadır ve bu mekanizma, koordineli çalışması DBMS'nin performansını doğrudan etkileyen birçok alt sistemden oluşur. Operasyon sırasında, PostgreSQL'in etkinliğini değerlendirmenize ve performansı artırmak için önlemler almanıza olanak tanıyan bileşenlerin çalışmasıyla ilgili istatistikler ve bilgiler toplanır. Ancak bu bilgilerin çoğu var ve oldukça basitleştirilmiş bir biçimde sunuluyor. Bu bilgiyi işlemek ve yorumlamak bazen tamamen önemsiz olmayan bir iştir ve araçlar ve yardımcı programlardan oluşan "hayvanat bahçesi", gelişmiş bir DBA'nın bile kafasını kolayca karıştırabilir.

Tünaydın Benim adım Aleksey. İlya'nın dediği gibi PostgreSQL istatistiklerinden bahsedeceğim.

PostgreSQL etkinlik istatistikleri. PostgreSQL'in iki istatistiği vardır. Tartışılacak aktivite istatistikleri. Ve veri dağıtımıyla ilgili zamanlayıcı istatistikleri. Performansı değerlendirmemize ve bir şekilde iyileştirmemize olanak tanıyan PostgreSQL aktivite istatistiklerinden özellikle bahsedeceğim.

Karşılaştığınız veya karşılaşabileceğiniz çeşitli sorunları çözmek için istatistikleri etkili bir şekilde nasıl kullanacağınızı anlatacağım.

Raporda ne olmayacak? Raporda zamanlayıcı istatistiklerine değinmeyeceğim çünkü... Bu, verilerin veritabanında nasıl saklandığı ve sorgu planlayıcının bu verilerin niteliksel ve niceliksel özellikleri hakkında nasıl fikir edindiği ayrı bir rapor için ayrı bir konudur.

Ve hiçbir alet incelemesi olmayacak, bir ürünü diğeriyle karşılaştırmayacağım. Reklam yapılmayacaktır. Bunu bir kenara bırakalım.

Sizlere istatistik kullanmanın faydalı olduğunu göstermek istiyorum. Gereklidir. Kullanımı güvenlidir. Tek ihtiyacımız olan düzenli SQL ve temel SQL bilgisi.

Ve sorunları çözmek için hangi istatistiklerin seçileceği hakkında konuşalım.

PostgreSQL'e bakıp işletim sistemindeki işlemleri görüntülemek için komutu çalıştırırsak bir "kara kutu" göreceğiz. Bir şeyler yapan bazı süreçleri göreceğiz ve adından da kabaca orada ne yaptıklarını, ne yaptıklarını hayal edebiliyoruz. Ama aslında kara bir kutu, içine bakamıyoruz.

CPU yükünü görebiliriz topbazı sistem yardımcı programlarının bellek kullanımına bakabiliriz ancak PostgreSQL'in içine bakamayız. Bunun için başka araçlara ihtiyacımız var.

Ve daha da devam ederek size zamanın nerede harcandığını anlatacağım. PostgreSQL'i böyle bir diyagram şeklinde hayal edersek zamanın nerede harcandığını cevaplayabiliriz. Bunlar iki şeydir: uygulamalardan gelen istemci isteklerini işler ve PostgreSQL'in kendisini çalışır durumda tutmak için gerçekleştirdiği arka plan görevlerini işler.

Sol üst köşeye bakmaya başlarsak client isteklerinin nasıl işlendiğini görebiliriz. İstek uygulamadan gelir ve daha ileri çalışmalar için bir istemci oturumu açılır. İstek zamanlayıcıya gönderilir. Zamanlayıcı bir sorgu planı oluşturur. Yürütülmesi için daha da gönderir. Tablolar ve dizinlerle ilişkili bir tür blok veri girişi/çıkışı vardır. Gerekli veriler disklerden belleğe özel bir "paylaşılan arabellekler" alanına okunur. Talebin sonuçları, eğer güncelleme veya silme ise, WAL'deki işlem günlüğüne kaydedilir. Bazı istatistiksel bilgiler günlükte veya istatistik toplayıcıda yer alır. Ve isteğin sonucu müşteriye geri gönderilir. Bundan sonra müşteri yeni bir istekle her şeyi tekrarlayabilir.

Arka plan görevleri ve arka plan süreçleri ne olacak? Veritabanını normal çalışma modunda çalışır durumda tutan çeşitli süreçlerimiz var. Raporda şu süreçlere de değinilecek: otovakum, kontrol noktası, replikasyonla ilgili süreçler, arka plan yazarı. Rapor ederken her birine değineceğim.

İstatistiklerde ne gibi sorunlar var?

Çok fazla bilgi var. PostgreSQL 9.4, istatistik verilerini görüntülemek için 109 ölçüm sağlar. Bununla birlikte, veritabanı çok sayıda tablo, şema, veritabanı saklıyorsa, tüm bu ölçümlerin karşılık gelen tablo ve veritabanı sayısıyla çarpılması gerekecektir. Yani daha da fazla bilgi var. Ve içinde boğulmak çok kolaydır.
Bir sonraki sorun, istatistiklerin sayaçlarla temsil edilmesidir. Bu istatistiklere baktığımızda sürekli artan sayaçları göreceğiz. İstatistiklerin sıfırlanmasından bu yana çok zaman geçtiyse milyarlarca değerleri göreceğiz. Ve bize hiçbir şey söylemiyorlar.
Hikaye yok. Eğer bir tür başarısızlık yaşadıysanız, 15-30 dakika önce bir şey düştüyse, istatistikleri kullanıp 15-30 dakika önce ne olduğunu göremeyeceksiniz. Bu bir sorundur.
PostgreSQL'de yerleşik bir aracın bulunmaması bir sorundur. Çekirdek geliştiricileri herhangi bir yardımcı program sağlamaz. Onların öyle bir şeyleri yok. Sadece veritabanındaki istatistikleri sağlarlar. Kullanın, talepte bulunun, ne istiyorsanız yapın.
PostgreSQL'de yerleşik bir araç bulunmadığından bu başka bir soruna neden olur. Çok sayıda üçüncü taraf araç. Az ya da çok doğrudan eli olan her şirket kendi programını yazmaya çalışıyor. Sonuç olarak topluluk istatistiklerle çalışmak için kullanılabilecek birçok araca sahiptir. Ve bazı araçların belirli yetenekleri vardır, bazılarının başka yetenekleri yoktur veya bazı yeni yetenekler vardır. Ve öyle bir durum ortaya çıkıyor ki, birbiriyle örtüşen ve farklı işlevlere sahip iki, üç veya dört araç kullanmanız gerekiyor. Bu çok tatsız.

Bundan ne sonuç çıkıyor? Programlara bağlı kalmamak veya bu programları bir şekilde kendiniz geliştirmek için istatistikleri doğrudan alabilmeniz önemlidir: kendi yararınıza olacak bazı işlevler ekleyin.

Ve temel SQL bilgisine ihtiyacınız var. İstatistiklerden bazı veriler elde etmek için SQL sorguları oluşturmanız gerekir, yani seçme ve birleştirmenin nasıl derlendiğini bilmeniz gerekir.

İstatistikler bize birkaç şey söylüyor. Kategorilere ayrılabilirler.

İlk kategori veritabanında meydana gelen olaylardır. Bu, veritabanında bir olayın meydana geldiği zamandır: bir istek, bir tabloya erişim, otomatik vakum, taahhütler, o zaman bunların hepsi olaylardır. Bu olaylara karşılık gelen sayaçlar artırılır. Ve bu olayları takip edebiliyoruz.
İkinci kategori ise tablolar ve veritabanları gibi nesnelerin özellikleridir. Özellikleri var. Bu tabloların boyutudur. Tabloların büyümesini ve endekslerin büyümesini takip edebiliyoruz. Dinamiklerdeki değişiklikleri görebiliriz.
Üçüncü kategori ise olaya harcanan zamandır. Talep bir olaydır. Kendine özgü bir süre ölçüsü vardır. Burada başladım, burada bitti. Onu takip edebiliriz. Ya diskten bir bloğu okumak ya da yazmak için gereken süre. Bu tür şeyler de takip ediliyor.

İstatistik kaynakları şu şekilde sunulmaktadır:

Paylaşılan bellekte (paylaşılan arabellekler), statik verileri depolamak için bir bölüm vardır, ayrıca belirli olaylar meydana geldiğinde veya veritabanının çalışmasında bazı anlar ortaya çıktığında sürekli olarak artan sayaçlar da vardır.
Bu sayaçların tümüne kullanıcı erişemez, hatta yönetici bile erişemez. Bunlar düşük seviyeli şeyler. Bunlara erişmek için PostgreSQL, SQL işlevleri biçiminde bir arayüz sağlar. Bu işlevleri kullanarak seçme atışları yapabilir ve bir tür metrik (veya metrik kümesi) elde edebiliriz.
Ancak bu işlevlerin kullanılması her zaman uygun değildir, bu nedenle işlevler görünümlerin (GÖRÜNÜMLER) temelini oluşturur. Bunlar, belirli bir alt sistem veya veritabanındaki belirli bir dizi olay hakkında istatistik sağlayan sanal tablolardır.
Bu gömülü görünümler (VIEW'ler), istatistiklerle çalışmak için birincil kullanıcı arayüzüdür. Herhangi bir ek ayar gerektirmeden varsayılan olarak mevcutturlar, hemen kullanabilir, bakabilir ve onlardan bilgi alabilirsiniz. Ve sonra katkılar var. Katkılar resmidir. Postgresql-contrib paketini (örneğin postgresql94-contrib) kurabilir, gerekli modülü konfigürasyona yükleyebilir, bunun için parametreleri belirleyebilir, PostgreSQL'i yeniden başlatabilir ve kullanabilirsiniz. (Not. Dağıtıma bağlı olarak son sürümlerde katkı paketi ana paketin bir parçasıdır).
Resmi olmayan katkılar da var. Standart PostgreSQL dağıtımına dahil değildirler. Ya derlenmeleri ya da kütüphane olarak kurulmaları gerekir. Bu resmi olmayan katkının geliştiricisinin ne bulduğuna bağlı olarak seçenekler çok farklı olabilir.

Bu slaytta tüm VIEW'ler ve PostgreSQL 9.4'te mevcut olan bazı işlevler sunulmaktadır. Gördüğümüz gibi onlardan çok var. Ve ilk kez karşılaştığınızda kafanızın karışması oldukça kolaydır.

Ancak önceki fotoğrafı çekersek Как тратится время на PostgreSQL ve bu listeyle uyumlu olarak bu resmi elde ediyoruz. PostgreSQL çalışırken ilgili istatistikleri elde etmek için her görünümü (VIEW'ler) veya her işlevi şu veya bu amaç için kullanabiliriz. Ve alt sistemin işleyişi hakkında zaten bazı bilgiler alabiliyoruz.

İlk bakacağımız şey pg_stat_database. Gördüğümüz gibi bu bir performans. İçinde pek çok bilgi var. En çeşitli bilgiler. Ve veritabanımızda olup bitenler hakkında çok faydalı bilgiler veriyor.

Oradan ne gibi faydalı şeyler alabiliriz? En basit şeylerle başlayalım.

select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;

Bakabileceğimiz ilk şey önbellek isabet yüzdesidir. Önbellek isabet oranı yararlı bir ölçümdür. Paylaşılan arabellek önbelleğinden ne kadar veri alındığını ve diskten ne kadar veri okunduğunu tahmin etmenizi sağlar.

Açık ki ne kadar çok önbellek isabetimiz olursa o kadar iyi. Bu ölçümü yüzde olarak ölçüyoruz. Ve örneğin, bu önbellek isabetlerinin yüzdesi %90'ın üzerindeyse bu iyidir. Eğer %90'ın altına düşerse, bu, sıcak veriyi hafızada tutacak kadar hafızamızın olmadığı anlamına gelir. Ve bu verileri kullanmak için PostgreSQL'in diske erişmesi gerekir ve bu, verilerin bellekten okunmasına göre daha yavaştır. Ve belleği artırmayı düşünmeniz gerekir: ya paylaşılan arabellekleri artırın ya da donanım belleğini (RAM) artırın.

select
datname,
(xact_commit*100)/(xact_commit+xact_rollback) as c_ratio,
deadlocks, conflicts,
temp_file, pg_size_pretty(temp_bytes) as temp_size
from pg_stat_database;

Bu performanstan başka neler çıkarabilirsiniz? Veritabanında meydana gelen anormallikleri görebilirsiniz. Burada ne gösteriliyor? Taahhütler, geri almalar, geçici dosyaların oluşturulması, boyutları, kilitlenmeler ve çakışmalar vardır.

Bu isteği kullanabiliriz. Bu SQL oldukça basittir. Ve bu verilere buradan bakabiliriz.

İşte eşik değerleri. Taahhütlerin ve geri almaların oranına bakıyoruz. Taahhütler, bir işlemin başarılı bir şekilde onaylanmasıdır. Geri almalar bir geri alma işlemidir; yani bir işlem biraz iş yaptı, veritabanını zorladı, bir şeyler hesapladı ve ardından bir hata oluştu ve işlemin sonuçları atıldı. Yani sürekli artan geri alma sayısının kötü olması. Ve bir şekilde bunlardan kaçınmalı ve bunun olmaması için kodu düzenlemelisiniz.

Çatışmalar kopyalanmayla ilgilidir. Ve bunlardan da kaçınılmalıdır. Bir kopya üzerinde yürütülen bazı sorgularınız varsa ve çakışmalar ortaya çıkıyorsa, bu çakışmaları çözmeniz ve neler olduğunu görmeniz gerekir. Ayrıntıları günlüklerde bulabilirsiniz. Uygulama isteklerinin hatasız çalışması için çakışma durumlarını ortadan kaldırın.

Kilitlenmeler de kötü bir durumdur. İstekler kaynaklar için mücadele ederken, bir istek bir kaynağa erişip kilidi aldı, ikinci bir istek ikinci kaynağa erişti ve kilidi de aldı ve ardından her iki istek de birbirinin kaynaklarına erişti ve komşunun kilidi açmasını beklerken bloke edildi. Bu da sorunlu bir durum. Uygulamaların yeniden yazılması ve kaynaklara erişimin serileştirilmesi düzeyinde ele alınmaları gerekir. Ve çıkmazlarınızın sürekli arttığını görüyorsanız, loglardaki detaylara bakmanız, ortaya çıkan durumları analiz etmeniz ve sorunun ne olduğunu görmeniz gerekir.

Geçici dosyalar (temp_files) da kötüdür. Bir kullanıcı isteğinin operasyonel, geçici verileri barındırmak için yeterli belleği olmadığında diskte bir dosya oluşturulur. Ve bellekteki geçici bir arabellekte yapabileceği tüm işlemler disk üzerinde yapılmaya başlar. Yavaş. Bu, sorgu yürütme süresini artırır. PostgreSQL'e istek gönderen müşteri ise bir süre sonra yanıt alacaktır. Tüm bu işlemler hafızada yapılırsa Postgres çok daha hızlı yanıt verecek ve istemci daha az bekleyecektir.

Pg_stat_bgwriter - Bu görünüm iki PostgreSQL arka plan alt sisteminin çalışmasını açıklar: bu checkpointer и background writer.

Öncelikle sözde kontrol noktalarına bakalım. checkpoints. Kontrol noktaları nelerdir? Denetim noktası, işlem günlüğünde, günlüğe kaydedilen tüm veri değişikliklerinin diskteki verilerle başarıyla senkronize edildiğini gösteren bir konumdur. İşlem, iş yüküne ve ayarlara bağlı olarak uzun olabilir ve çoğunlukla paylaşılan arabelleklerdeki kirli sayfaların diskteki veri dosyalarıyla senkronize edilmesinden oluşur. Bu ne için? PostgreSQL sürekli olarak diske erişip oradan veri getirip her erişimde veri yazsaydı yavaş olurdu. Bu nedenle PostgreSQL, boyutu konfigürasyondaki ayarlara bağlı olan bir bellek segmentine sahiptir. Postgres, daha sonra işlenmek veya sorgulanmak üzere canlı verileri bu bellekte saklar. Verilerin değiştirilmesine yönelik talep olması durumunda değiştirilir. Ve verilerin iki versiyonunu alıyoruz. Biri hafızamızda, diğeri diskte. Ve periyodik olarak bu verileri senkronize etmeniz gerekir. Bellekte değişenleri diske senkronize etmemiz gerekiyor. Bunun için kontrol noktalarına ihtiyacınız var.

Checkpoint, paylaşılan arabelleklerden geçer, kirli sayfaları kontrol noktası için ihtiyaç duydukları şekilde işaretler. Daha sonra paylaşılan arabelleklerden ikinci bir geçiş başlatır. Ve kontrol noktası olarak işaretlenen sayfaları zaten senkronize ediyor. Bu şekilde veriler diskle senkronize edilir.

İki tür kontrol noktası vardır. Bir kontrol noktası zaman aşımı ile yürütülür. Bu kontrol noktası kullanışlı ve iyidir – checkpoint_timed. Ve talep üzerine kontrol noktaları var - checkpoint required. Bu kontrol noktası, çok büyük bir veri kaydımız olduğunda ortaya çıkar. Çok sayıda işlem günlüğü kaydettik. Ve PostgreSQL, tüm bunları olabildiğince hızlı bir şekilde senkronize etmesi, bir kontrol noktası oluşturması ve yoluna devam etmesi gerektiğine inanıyor.

Ve eğer istatistiklere bakarsanız pg_stat_bgwriter ve sahip olduğun şeyi gördüm checkpoint_req checkpoint_timed'dan çok daha büyük, o zaman bu kötü. Neden kötü? Bu, PostgreSQL'in diske veri yazması gerektiğinde sürekli stres altında olduğu anlamına gelir. Zaman aşımı kontrol noktası daha az streslidir ve dahili programa göre gerçekleştirilir ve bir nevi zamana yayılır. PostgreSQL, çalışmayı duraklatma ve disk alt sistemini zorlamama özelliğine sahiptir. Bu PostgreSQL için kullanışlıdır. Ve denetim noktası sırasında yürütülen sorgular, disk alt sisteminin meşgul olmasından kaynaklanan strese maruz kalmayacaktır.

Ve kontrol noktasını ayarlamak için üç parametre vardır:

сheckpoint_segments.
сheckpoint_timeout.
сheckpoint_competion_target.

Kontrol noktalarının çalışmasını düzenlemenizi sağlar. Ama bunların üzerinde durmayacağım. Onların etkisi ayrı bir konudur.

Not: Raporda tartışılan 9.4 sürümü artık geçerli değil. PostgreSQL'in modern sürümlerinde parametre checkpoint_segments parametrelerle değiştirildi min_wal_size и max_wal_size.

Bir sonraki alt sistem arka plan yazarıdır – background writer. O ne yapıyor? Sonsuz bir döngü içerisinde sürekli çalışır. Paylaşılan arabelleklerdeki sayfaları tarar ve bulduğu kirli sayfaları diske atar. Böylece kontrol noktasının çalıştırılması sırasında kontrol noktasının daha az iş yapmasına yardımcı olur.

Başka ne için gerekli? Verileri barındırmak için aniden (büyük miktarlarda ve hemen) ihtiyaç duyulması durumunda, paylaşılan arabelleklerde boş sayfalara ihtiyaç duyulmasını sağlar. Bir isteği tamamlamak için boş sayfalara ihtiyaç duyulduğunda ve bunların zaten paylaşılan arabelleklerde yer aldığında bir durumun ortaya çıktığını varsayalım. Postgresif backend sadece onları alıp kullanıyor, hiçbir şeyi kendisinin temizlemesine gerek yok. Ancak birdenbire bu tür sayfalar kalmazsa, arka uç çalışmayı duraklatır ve bunları diske atmak ve kendi ihtiyaçları için almak için sayfaları aramaya başlar; bu da, o anda yürütülen isteğin süresini olumsuz etkiler. Bir parametreniz olduğunu görürseniz maxwritten_clean büyükse bu, arka plan yazarının işini yapmadığı ve parametreleri artırmanız gerektiği anlamına gelir bgwriter_lru_maxpages, böylece bir döngüde daha fazla iş yapabilir, daha fazla sayfa temizleyebilir.

Ve bir başka çok yararlı gösterge de buffers_backend_fsync. Arka uçlar yavaş olduğu için fsync yapmıyor. Fsync'i IO yığın kontrol noktasının yukarısına geçirirler. Denetim işaretçisinin kendi kuyruğu vardır, periyodik olarak fsync'i işler ve bellekteki sayfaları diskteki dosyalarla senkronize eder. Kontrol işaretçisindeki kuyruk büyük ve doluysa, arka uç fsync'i kendisi yapmak zorunda kalır ve bu da arka ucun çalışmasını yavaşlatır., yani müşteri alabileceğinden daha geç bir yanıt alacaktır. Değerinizin sıfırdan büyük olduğunu görüyorsanız bu zaten bir sorundur ve arka plan yazıcısının ayarlarına dikkat etmeniz ve ayrıca disk alt sisteminin performansını değerlendirmeniz gerekir.

Not: _Aşağıdaki metin çoğaltmayla ilişkili istatistiksel görünümleri açıklamaktadır. Görünüm ve işlev adlarının çoğu Postgres 10'da yeniden adlandırıldı. Yeniden adlandırmanın özü, görünüm ve işlev adlarının yerini almaktı. xlog üzerinde wal и location üzerinde lsn işlev/görünüm adlarında vb. Özel örnek, fonksiyon pg_xlog_location_diff() olarak yeniden adlandırıldı pg_wal_lsn_diff()._

Burada da pek çok şeyimiz var. Ancak yalnızca konumla ilgili öğelere ihtiyacımız var.

Tüm değerlerin eşit olduğunu görürsek, bu ideal bir seçenektir ve kopya, ustanın gerisinde kalmaz.

Buradaki onaltılık konum, işlem günlüğündeki konumdur. Veritabanında herhangi bir aktivite varsa sürekli olarak artar: ekleme, silme vb.

сколько записано xlog в байтах
$ select
pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000');
лаг репликации в байтах
$ select
client_addr,
pg_xlog_location_diff(pg_current_xlog_location(), replay_location)
from pg_stat_replication;
лаг репликации в секундах
$ select
extract(epoch from now() - pg_last_xact_replay_timestamp());

Eğer bunlar farklıysa, o zaman bir çeşit gecikme var demektir. Gecikme, kopya ile ana sunucu arasındaki gecikmedir, yani veriler sunucular arasında farklılık gösterir.

Gecikmenin üç nedeni var:

Bu disk alt sistemi kayıt dosyası senkronizasyonuyla baş edemez.
Bunlar, verilerin kopyaya ulaşmak için yeterli zamanı olmadığı ve onu yeniden oluşturamadığı durumlarda olası ağ hataları veya ağ aşırı yüklenmesidir.
Ve işlemci. İşlemci çok nadir görülen bir durumdur. Bunu iki üç kez gördüm ama bu da olabilir.

Ve işte istatistikleri kullanmamıza izin veren üç sorgu. İşlem günlüğüne ne kadar kayıt yaptığımızı tahmin edebiliriz. Böyle bir işlev var pg_xlog_location_diff ve çoğaltma gecikmesini bayt ve saniye cinsinden tahmin edebiliriz. Bunun için bu görünümdeki değeri de (VIEW'ler) kullanırız.

Not: _pg_xlog_location yerinediff() işlevi çıkarma operatörünü kullanabilir ve bir konumu diğerinden çıkarabilir. Rahat.

Gecikmede saniye cinsinden bir nokta var. Master üzerinde aktivite yoksa yaklaşık 15 dakika önce işlem vardı ve aktivite de yok, replikadaki bu gecikmeye bakarsak 15 dakikalık bir gecikme göreceğiz. Bunu hatırlamaya değer. Ve bu gecikmeyi izlediğinizde kafa karıştırıcı olabilir.

Pg_stat_all_tables başka bir yararlı görünümdür. İstatistikleri tablolarda gösterir. Veritabanında tablolarımız olduğunda, onunla ilgili bazı aktiviteler, bazı eylemler vardır, bu bilgiyi bu görünümden alabiliriz.

select
relname,
pg_size_pretty(pg_relation_size(relname::regclass)) as size,
seq_scan, seq_tup_read,
seq_scan / seq_tup_read as seq_tup_avg
from pg_stat_user_tables
where seq_tup_read > 0 order by 3,4 desc limit 5;

Bakabileceğimiz ilk şey, tablodaki sıralı taramalardır. Bu geçişlerden sonraki sayının kendisi mutlaka kötü değildir ve bir şeyler yapmamız gerektiğinin göstergesi değildir.

Ancak ikinci bir ölçüm daha var: seq_tup_read. Bu, sıralı taramadan döndürülen satır sayısıdır. Ortalama sayı 1, 000, 10, 000'i aşarsa, bu zaten sorguların dizini temel alması için bir yerde bir dizin oluşturmanız gerektiğinin veya bu tür sıralı taramaları kullanan sorguları optimize etmenin mümkün olduğunun bir göstergesidir. bunun gerçekleşmemesiydi.

Basit bir örnek - büyük bir OFFSET ve LIMIT maliyeti olan bir istek diyelim. Örneğin bir tablodaki 100 satır taranır ve ardından gerekli 000 satır alınır ve daha önce taranan satırlar atılır. Bu aynı zamanda kötü bir durumdur. Ve bu tür sorguların optimize edilmesi gerekiyor. Ve işte buna bakabileceğiniz ve ortaya çıkan sayıları değerlendirebileceğiniz basit bir SQL sorgusu.

select
relname,
pg_size_pretty(pg_total_relation_size(relname::regclass)) as
full_size,
pg_size_pretty(pg_relation_size(relname::regclass)) as
table_size,
pg_size_pretty(pg_total_relation_size(relname::regclass) -
pg_relation_size(relname::regclass)) as index_size
from pg_stat_user_tables
order by pg_total_relation_size(relname::regclass) desc limit 10;

Tablo boyutları bu tablo ve ek işlevler kullanılarak da elde edilebilir. pg_total_relation_size(), pg_relation_size().

Genel olarak meta komutlar vardır dt и diPSQL'de kullanılabilen ve ayrıca tabloların ve dizinlerin boyutlarını görüntüleyen.

Bununla birlikte, işlevleri kullanmak, dizinleri hesaba katarak veya dizinleri hesaba katmadan tabloların boyutlarına bakmamıza ve zaten veritabanının büyümesine, yani nasıl, hangi yoğunlukta büyüdüğüne ve nasıl büyüdüğüne dayalı bazı tahminler yapmamıza yardımcı olur. boyutlandırma optimizasyonu hakkında bazı sonuçlar çıkarmak.

Etkinlik kaydetme. Kayıt nedir? Operasyona bakalım UPDATE – bir tablodaki satırların güncellenmesi işlemi. Aslında güncelleme iki işlemden (veya daha fazlasından) oluşur. Bu, satırın yeni bir sürümünü eklemek ve satırın eski sürümünü eski olarak işaretlemektir. Daha sonra otomatik vakum gelip hatların bu eski versiyonlarını temizleyecek ve burayı yeniden kullanıma uygun olarak işaretleyecek.

Ayrıca güncelleme sadece bir tablonun güncellenmesinden ibaret değildir. Bu aynı zamanda bir indeks güncellemesidir. Tabloda çok sayıda dizin varsa, güncelleme sırasında sorguda güncellenen alanları içeren tüm dizinlerin de güncellenmesi gerekecektir. Bu dizinler ayrıca temizlenmesi gereken satırların eski sürümlerine de sahip olacaktır.

select
s.relname,
pg_size_pretty(pg_relation_size(relid)),
coalesce(n_tup_ins,0) + 2 * coalesce(n_tup_upd,0) -
coalesce(n_tup_hot_upd,0) + coalesce(n_tup_del,0) AS total_writes,
(coalesce(n_tup_hot_upd,0)::float * 100 / (case when n_tup_upd > 0
then n_tup_upd else 1 end)::float)::numeric(10,2) AS hot_rate,
(select v[1] FROM regexp_matches(reloptions::text,E'fillfactor=(\d+)') as
r(v) limit 1) AS fillfactor
from pg_stat_all_tables s
join pg_class c ON c.oid=relid
order by total_writes desc limit 50;

Ve yeni tasarımı nedeniyle UPDATE ağır bir işlemdir. Ancak bunlar daha kolay hale getirilebilir. Yemek yemek hot updates. PostgreSQL sürüm 8.3'te göründüler. Peki bu nedir? Bu, dizinlerin yeniden oluşturulmasına neden olmayan hafif bir güncellemedir. Yani kaydı güncelledik ama sadece sayfadaki (tabloya ait olan) kayıt güncellendi ve indeksler hala sayfadaki aynı kaydı gösteriyor. Biraz ilginç bir çalışma mantığı var: Bir boşluk geldiğinde bu zincirleri oluşturur hot yeniden oluşturur ve her şey, dizinleri güncellemeden çalışmaya devam eder ve her şey daha az kaynak israfıyla gerçekleşir.

Peki ne zaman n_tup_hot_upd büyük, o zaman çok iyi. Bu, hafif güncellemelerin ağırlıklı olduğu ve kaynak açısından bizim için daha ucuz olduğu ve her şeyin yolunda olduğu anlamına geliyor.

ALTER TABLE table_name SET (fillfactor = 70);

Ses düzeyi nasıl artırılır hot updateov? Kullanabiliriz fillfactor. INSERT'leri kullanarak bir tablodaki sayfayı doldururken ayrılan boş alanın boyutunu belirler. Bir tabloya ekler eklendiğinde sayfayı tamamen doldurur ve boş alan bırakmaz. Daha sonra yeni bir sayfa vurgulanır. Veriler tekrar doldurulur. Ve bu varsayılan davranıştır, doldurma faktörü = %100.

Doldurma faktörünü %70 yapabiliriz. Yani, eklemeler sırasında yeni bir sayfa vurgulandı, ancak sayfanın yalnızca% 70'i dolduruldu. Ve yedek olarak %30'umuz kaldı. Güncelleme yapmanız gerektiğinde büyük ihtimalle aynı sayfada gerçekleşecek ve satırın yeni versiyonu da aynı sayfaya sığacaktır. Ve hot_update yapılacaktır. Bu, tablolara yazmayı kolaylaştırır.

select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));

Otovakum kuyruğu. Autovacuum, PostgreSQL'de çok az istatistiğin bulunduğu bir alt sistemdir. Şu anda kaç tane vakumumuz olduğunu sadece pg_stat_activity'deki tablolarda görebiliyoruz. Ancak sırada kaç masa olduğunu hemen anlamak oldukça zordur.

Not: _Postgres 10'dan itibaren Vatovac izleme durumu büyük ölçüde iyileşti - pg_stat_progress görünümü ortaya çıktıAraba vakumunun izlenmesi sorununu önemli ölçüde kolaylaştıran vakum.

Bu basitleştirilmiş sorguyu kullanabiliriz. Ve vakumun ne zaman yapılması gerektiğini görebiliriz. Peki boşluk nasıl ve ne zaman başlamalı? Bunlar daha önce bahsettiğim hatların eski versiyonları. Güncelleme gerçekleşti, satırın yeni bir sürümü eklendi. Dizenin eski bir sürümü ortaya çıktı. Masada pg_stat_user_tables böyle bir parametre var n_dead_tup. "Ölü" satırların sayısını gösterir. Ve ölü sıraların sayısı belirli bir eşiği aştığında, masaya bir otovakum gelecektir.

Peki bu eşik nasıl hesaplanır? Bu, tablodaki toplam satır sayısının oldukça spesifik bir yüzdesidir. Bir parametre var autovacuum_vacuum_scale_factor. Yüzdeyi belirler. Diyelim ki %10 + 50 satırlık ek bir temel eşik var. Peki ne olur? Tablodaki tüm satırların “%10 + 50”sinden daha fazla ölü satırımız olduğunda, tabloyu otomatik vakuma koyarız.

select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));

Ancak bir nokta var. Parametreler için temel eşikler av_base_thresh и av_scale_factor bireysel olarak atanabilir. Ve buna göre eşik küresel değil, tablo için bireysel olacaktır. Bu nedenle hesaplamak için hileler ve püf noktaları kullanmanız gerekir. Eğer ilgileniyorsanız Avito'daki meslektaşlarımızın deneyimlerine bakabilirsiniz (slayttaki bağlantı geçersizdir ve metinde güncellenmiştir).

Onlar için yazdılar munin eklentisi, bu şeyleri hesaba katar. Orada iki sayfalık bir ayak örtüsü var. Ancak doğru hesaplama yapar ve oldukça etkili bir şekilde, az olan masalar için nerede çok fazla vakuma ihtiyacımız olduğunu değerlendirmemizi sağlar.

Bunun hakkında ne yapabiliriz? Eğer büyük bir kuyruğumuz varsa ve otovakum başa çıkamıyorsa, o zaman vakum çalışanlarının sayısını artırabilir veya vakumu daha agresif hale getirebiliriz., daha erken tetiklenmesi için tabloyu küçük parçalar halinde işler. Ve böylece kuyruk azalacak. — Burada asıl önemli olan disklerdeki yükü izlemek, çünkü... Vakum ücretsiz bir şey değildir, ancak SSD/NVMe cihazlarının ortaya çıkışıyla sorun daha az fark edilir hale gelmiştir.

Pg_stat_all_indexes indekslerdeki istatistiklerdir. O büyük değil. Ve bunu indekslerin kullanımı hakkında bilgi edinmek için kullanabiliriz. Ve örneğin hangi indekslerin fazla olduğunu belirleyebiliriz.

Daha önce de söylediğim gibi, update sadece tabloların güncellenmesi değil aynı zamanda indekslerin de güncellenmesidir. Buna göre, eğer tabloda çok sayıda indeksimiz varsa, tablodaki satırları güncellerken indekslenen alanların indekslerinin de güncellenmesi gerekir ve dizin taraması olmayan kullanılmamış dizinlerimiz varsa, bunlar balast olarak asılı kalır. Ve onlardan kurtulmamız gerekiyor. Bunun için bir alana ihtiyacımız var idx_scan. Sadece indeks taramalarının sayısına bakıyoruz. Dizinler nispeten uzun bir istatistik depolama süresi boyunca (en az 2-3 hafta) sıfır taramaya sahipse, o zaman bunlar büyük olasılıkla kötü dizinlerdir, onlardan kurtulmamız gerekir.

Not: Akış çoğaltma kümeleri durumunda kullanılmayan dizinleri ararken tüm küme düğümlerini kontrol etmeniz gerekir, çünkü istatistikler global değildir ve eğer indeks ana bilgisayarda kullanılmıyorsa kopyalarda kullanılabilir (eğer orada bir yük varsa).

İki bağlantı:

https://github.com/dataegret/pg-utils/blob/master/sql/low_used_indexes.sql

http://www.databasesoup.com/2014/05/new-finding-unused-indexes-query.html

Bunlar kullanılmayan dizinlerin nasıl aranacağına ilişkin daha gelişmiş sorgu örnekleridir.

İkinci bağlantı oldukça ilginç bir istek. Orada çok basit olmayan bir mantık var. Referans olarak tavsiye ederim.

Endeksleri kullanarak özetlemeye değer başka ne var?

Kullanılmayan indeksler kötüdür.
Yer kaplıyorlar.
Güncelleme işlemlerini yavaşlatın.
Vakum için ekstra iş.

Kullanılmayan indeksleri kaldırırsak sadece veritabanını daha iyi hale getiririz.

Bir sonraki sunum pg_stat_activity. Bu yardımcı programın bir analogudur ps, yalnızca PostgreSQL'de. Eğer ps'işletim sistemindeki süreçlere bakarsanız, o zaman pg_stat_activity Size PostgreSQL içindeki etkinliği gösterecektir.

Oradan ne gibi faydalı şeyler alabiliriz?

select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;

Genel aktiviteyi, veritabanında neler olduğunu görebiliriz. Yeni bir dağıtım yapabiliriz. Burada her şey patladı, yeni bağlantılar kabul edilmiyor, uygulamaya hatalar yağıyor.

select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;

Bunun gibi bir sorgu çalıştırabilir ve maksimum bağlantı sınırına göre toplam bağlantı yüzdesini görebilir ve kimin en fazla bağlantıya sahip olduğunu görebiliriz. Ve bu verilen durumda o kullanıcıyı görüyoruz cron_role 508 bağlantı açıldı. Ve orada ona bir şey oldu. Bunu ele almamız ve ona bakmamız gerekiyor. Ve bunun bir tür anormal sayıda bağlantı olması oldukça olası.

OLTP iş yükümüz varsa sorguların hızlı olması, çok hızlı olması ve uzun sorguların olmaması gerekir. Ancak uzun sorular ortaya çıkarsa kısa vadede endişelenecek bir şey yoktur, ancak Uzun vadede, uzun sorgular veritabanına zarar verir; tablo parçalanması meydana geldiğinde tabloların şişme etkisini artırır. Hem şişkinlikten hem de uzun sorgulardan kurtulmanız gerekiyor.

select
client_addr, usename, datname,
clock_timestamp() - xact_start as xact_age,
clock_timestamp() - query_start as query_age,
query
from pg_stat_activity order by xact_start, query_start;

Lütfen unutmayın: Bu istekle uzun sorguları ve işlemleri tanımlayabiliriz. Fonksiyonu kullanıyoruz clock_timestamp() Çalışma süresini belirlemek için. Bulduğumuz uzun sorguları hatırlayabilir, yerine getirebiliriz explain, planlara bakın ve bir şekilde optimize edin. Mevcut uzun istekleri bir kenara atıp hayatımıza devam ediyoruz.

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

Kötü işlemler, işlemde boşta olan ve işlemde boşta olan (durdurulmuş) durumlardaki işlemlerdir.

Bu ne anlama geliyor? İşlemlerin birden fazla durumu vardır. Ve bu durumlardan biri herhangi bir zamanda varsayılabilir. Durumları tanımlamak için bir alan var state bu sunumda. Ve bunu durumu belirlemek için kullanıyoruz.

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

Yukarıda da söylediğim gibi bu iki durum işlemde boşta kalmak ve işlemde boşta olmak (iptal edildi) kötüdür. Ne olduğunu? Bu, uygulamanın bir işlem açtığı, bazı eylemler yaptığı ve işine devam ettiği zamandır. İşlem açık kalır. Postrges işlem motorunun mimarisi nedeniyle askıda kalıyor, hiçbir şey olmuyor, bağlantıyı kesiyor, değişen satırlarda kilitleniyor ve potansiyel olarak diğer tabloların şişmesini artırıyor. Ve bu tür işlemler de durdurulmalı çünkü bunlar her halükarda genel olarak zararlıdır.

Veritabanınızda bunlardan 5-10-20'den fazlasının bulunduğunu görürseniz endişelenmeniz ve onlarla bir şeyler yapmaya başlamanız gerekir.

Burada hesaplama süresi için de kullanıyoruz clock_timestamp(). İşlemleri çekiyor ve uygulamayı optimize ediyoruz.

Yukarıda da söylediğim gibi engelleme, iki veya daha fazla işlemin bir veya bir grup kaynak için mücadele etmesidir. Bunun için bir alanımız var waiting Boole değeriyle true veya false.

Doğru – bu, sürecin beklemede olduğu, bir şeyler yapılması gerektiği anlamına gelir. Bir süreç beklerken bu süreci başlatan istemcinin de beklediği anlamına gelir. İstemci tarayıcıda oturur ve bekler.

Not: _Postgres sürüm 9.6 alanından başlayarak waiting kaldırıldı ve yerine iki bilgilendirici alan daha eklendi wait_event_type и wait_event._

Ne yapmalı? Uzun süre doğru görüyorsanız bu tür isteklerden kurtulmanız gerektiği anlamına gelir. Biz sadece bu tür işlemleri ortadan kaldırırız. Geliştiricilere, kaynaklar için yarış olmaması için bir şekilde optimize etmeleri gerektiğini yazıyoruz. Ve sonra geliştiriciler, bunun olmaması için uygulamayı optimize eder.

Ve aşırı ama potansiyel olarak ölümcül olmayan durum ise şu: kilitlenmelerin ortaya çıkması. İki işlem, iki kaynağı güncelledi ve bu kez karşıt kaynaklara tekrar erişti. Bu durumda PostgreSQL, başka bir işlemin çalışmaya devam edebilmesi için işlemin kendisini öldürür. Bu çıkmaz bir durumdur ve bunu kendi başına çözemez. Bu nedenle PostgreSQL aşırı önlemler almak zorunda kalıyor.

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/c4_06_show_locked_queries.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_95.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_96.sql

http://big-elephants.com/2013-09/exploring-query-locks-in-postgres/

Ve burada engellemeyi izlemenize olanak tanıyan iki sorgu var. Görünümü kullanıyoruz pg_locksağır kilitleri izlemenizi sağlar.

Ve ilk bağlantı istek metninin kendisidir. Oldukça uzun.

İkinci bağlantı ise kilitlerle ilgili bir makale. Okumak faydalıdır, çok ilginçtir.

Peki ne görüyoruz? İki istek görüyoruz. İle işlem ALTER TABLE bloke edici bir işlemdir. Başladı ama tamamlanmadı ve bu işlemi kaydeden uygulama bir yerde başka şeyler yapıyor. İkinci istek ise güncellemedir. Çalışmasına devam edebilmek için altlık masasının bitmesini bekliyor.

Kimin kimi kilitlediğini, kimi tuttuğunu bu şekilde öğrenebilir ve bununla daha fazla ilgilenebiliriz.

Bir sonraki modül pg_stat_statements. Dediğim gibi bu bir modül. Kullanmak için, kütüphanesini konfigürasyona yüklemeniz, PostgreSQL'i yeniden başlatmanız, modülü kurmanız (tek komutla) ve ardından yeni bir görünüme sahip olmamız gerekir.

Cреднее время запроса в милисекундах
$ select (sum(total_time) / sum(calls))::numeric(6,3)
from pg_stat_statements;

Самые активно пишущие (в shared_buffers) запросы
$ select query, shared_blks_dirtied
from pg_stat_statements
where shared_blks_dirtied > 0 order by 2 desc;

Oradan ne alabiliriz? Basit şeylerden bahsedecek olursak ortalama sorgu yürütme süresini alabiliriz. Zaman artıyor, bu da PostgreSQL'in yavaş yanıt verdiği ve bir şeyler yapmamız gerektiği anlamına geliyor.

Paylaşılan tamponlardaki verileri değiştiren veritabanındaki en aktif yazma işlemlerine bakabiliriz. Orada verileri kimin güncellediğini veya sildiğini görün.

Ve bu talepler için farklı istatistiklere bakabiliriz.

https://github.com/dataegret/pg-utils/blob/master/sql/global_reports/query_stat_total.sql

Biz pg_stat_statements Rapor oluşturmak için kullanıyoruz. İstatistikleri günde bir kez sıfırlıyoruz. Hadi biriktirelim. Bir dahaki sefere istatistikleri sıfırlamadan önce bir rapor oluşturalım. İşte raporun bağlantısı. İzleyebilirsin.

Biz ne yapıyoruz? Tüm talepler için genel istatistikleri hesaplıyoruz. Daha sonra her isteğin bu genel istatistiklere olan bireysel katkısını sayarız.

Peki ne izleyebiliriz? Belirli bir türdeki tüm isteklerin toplam yürütme süresine, diğer tüm isteklerin arka planına bakabiliriz. Genel resme göre CPU ve I/O kaynak kullanımına bakabiliriz. Ve zaten bu sorguları optimize edin. En sık yapılan sorguları bu rapora dayanarak oluşturuyoruz ve neyin optimize edileceği konusunda şimdiden düşünmeye yetecek şeyler elde ediyoruz.

Perde arkasında ne bıraktık? Zaman kısıtlı olduğu için değerlendirmeye almadığım birkaç başvuru daha kaldı.

Var pgstattuple aynı zamanda standart katkı paketinin ek bir modülüdür. Değerlendirmenizi sağlar bloat denilen tablolar masa parçalanması. Ve eğer çok fazla parçalanma varsa, onu kaldırmanız ve farklı araçlar kullanmanız gerekir. Ve fonksiyon pgstattuple uzun süre çalışır. Ve ne kadar çok masa olursa o kadar uzun süre çalışır.

Bir sonraki katkı pg_buffercache. Paylaşılan arabellekleri incelemenize olanak tanır: arabellek sayfalarının ne kadar yoğun ve hangi tablolar için kullanıldığı. Ve bu, paylaşılan tamponlara bakmanıza ve orada neler olduğunu değerlendirmenize olanak tanır.

Bir sonraki modül pgfincore. Bir sistem çağrısı yoluyla düşük seviyeli tablo işlemlerine izin verir mincore(), yani bir tabloyu paylaşılan arabelleklere yüklemenize veya boşaltmanıza olanak tanır. Ve diğer şeylerin yanı sıra, işletim sisteminin sayfa önbelleğini, yani tablonun sayfa önbelleğinde, paylaşılan arabelleklerde ne kadar yer kapladığını incelemeye olanak tanır ve tablonun iş yükünü değerlendirmemize olanak tanır.

Sonraki modül - pg_stat_kcache. Ayrıca bir sistem çağrısı kullanır getrusage(). Ve isteğin yürütülmesinden önce ve sonra bunu yürütür. Ve ortaya çıkan istatistiklerde, isteğimizin disk I/O'ya yani dosya sistemiyle yapılan işlemlere ne kadar harcadığını tahmin etmemizi sağlıyor ve işlemci kullanımına bakıyor. Ancak modül genç (öksürük öksürük) ve çalışması için daha önce bahsettiğim PostgreSQL 9.4 ve pg_stat_statements'a ihtiyaç duyuyor.

İstatistiklerin nasıl kullanılacağını bilmek faydalıdır. Üçüncü parti programlara ihtiyacınız yok. İçeri girebilir, görebilir, bir şeyler yapabilir, bir şeyler başarabilirsiniz.
İstatistikleri kullanmak zor değil, sadece normal SQL'dir. İsteği topladınız, derlediniz, gönderdiniz, baktınız.
İstatistikler soruların yanıtlanmasına yardımcı olur. Sorularınız varsa istatistiklere yönelirsiniz - bakın, sonuç çıkarın, sonuçları analiz edin.
Ve deney yapın. Çok fazla talep var, çok fazla veri var. Mevcut bir sorguyu her zaman optimize edebilirsiniz. Talebin orijinalinden daha kendinize uygun versiyonunu hazırlayıp kullanabilirsiniz.