Birkaç ay önce - halk PostgreSQL'e.
Zaten 6000'den fazla kez kullandınız, ancak gözden kaçmış olabilecek kullanışlı bir özellik: yapısal ipuçları, şuna benzeyen:
Onları dinleyin; istekleriniz "pürüzsüz ve ipeksi hale gelecektir." 🙂
Ancak cidden, bir isteği yavaşlatan ve kaynak tüketen birçok durum var tipiktir ve planın yapısı ve verilerinden tanınabilir.
Bu durumda, her geliştiricinin yalnızca deneyimine dayanarak kendi başına bir optimizasyon seçeneği araması gerekmez; ona burada neler olduğunu, sebebinin ne olabileceğini anlatabiliriz ve çözüme nasıl yaklaşılır. Biz de öyle yaptık.
Gelin bu vakalara daha yakından bakalım; nasıl tanımlanıyorlar ve hangi önerilere yol açıyorlar.
Kendinizi konuya daha iyi kaptırmak için öncelikle ilgili bloğu şuradan dinleyebilirsiniz: ve ancak bundan sonra her örneğin ayrıntılı analizine geçin:
#1: indeks “eksik sıralama”
ne zaman ortaya çıkar
Müşteri "LLC Kolokolchik" için en son faturayı gösterin.
nasıl belirlenir
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
Öneriler
Kullanılan dizin sıralama alanlarıyla genişlet.
Örnek:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key
SELECT
*
FROM
tbl
WHERE
fk_cli = 1 -- отбор по конкретной связи
ORDER BY
pk DESC -- хотим всего одну "последнюю" запись
LIMIT 1;
Dizinden 100'den fazla kaydın çıkarıldığını, bunların hepsinin sıralandığını ve ardından tek kaydın kaldığını hemen fark edebilirsiniz.
Düzeltme:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Bu kadar ilkel bir örnekte bile - 8.5 kat daha hızlı ve 33 kat daha az okuma. Her değer için ne kadar çok "gerçek" varsa, etki o kadar belirgin olur fk.
Böyle bir indeksin, diğer sorgular için öncekinden daha kötü olmayan bir "önek" indeksi olarak çalışacağını not ediyorum. fk, sıralama ölçütü pk yoktu ve yoktu (bununla ilgili daha fazlasını okuyabilirsiniz) ). Dahil olmak üzere normal sağlayacaktır açık yabancı anahtar desteği bu alanda.
#2: dizin kesişimi (BitmapAnd)
ne zaman ortaya çıkar
“NAO Buttercup” adına imzalanan “LLC Kolokolchik” müşterisi için tüm sözleşmeleri gösterin.
nasıl belirlenir
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanÖneriler
Oluşturmak bileşik indeks her iki orijinal alandan alanlara göre veya mevcut olanlardan birini ikinciden alanlarla genişletin.
Örnek:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 100)::integer fk_org -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_org); -- индекс для foreign key
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key
SELECT
*
FROM
tbl
WHERE
(fk_org, fk_cli) = (1, 999); -- отбор по конкретной паре
Düzeltme:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Bitmap Yığın Taraması tek başına oldukça etkili olduğundan buradaki getirisi daha azdır. Ama yine de 7 kat daha hızlı ve 2.5 kat daha az okuma.
#3: Dizinleri birleştirme (BitmapOr)
ne zaman ortaya çıkar
İşleme için en eski ilk 20 "bizi" veya atanmamış istekleri, sizinki öncelikli olacak şekilde gösterin.
nasıl belirlenir
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanÖneriler
Kullanmak BİRLİK [TÜMÜ] Koşulların OR bloklarının her biri için alt sorguları birleştirmek.
Örnek:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, CASE
WHEN random() < 1::real/16 THEN NULL -- с вероятностью 1:16 запись "ничья"
ELSE (random() * 100)::integer -- 100 разных внешних ключей
END fk_own;
CREATE INDEX ON tbl(fk_own, pk); -- индекс с "вроде как подходящей" сортировкой
SELECT
*
FROM
tbl
WHERE
fk_own = 1 OR -- свои
fk_own IS NULL -- ... или "ничьи"
ORDER BY
pk
, (fk_own = 1) DESC -- сначала "свои"
LIMIT 20;
Düzeltme:
(
SELECT
*
FROM
tbl
WHERE
fk_own = 1 -- сначала "свои" 20
ORDER BY
pk
LIMIT 20
)
UNION ALL
(
SELECT
*
FROM
tbl
WHERE
fk_own IS NULL -- потом "ничьи" 20
ORDER BY
pk
LIMIT 20
)
LIMIT 20; -- но всего - 20, больше и не надо
Gerekli 20 kaydın tamamının ilk blokta anında alınması gerçeğinden yararlandık, böylece daha "pahalı" Bitmap Yığın Taraması olan ikincisi yürütülmedi bile - sonunda 22 kat daha hızlı, 44 kat daha az okuma!
Bu optimizasyon yöntemi hakkında daha ayrıntılı bir hikaye somut örnekler üzerinde makalelerde okunabilir и .
Genelleştirilmiş versiyon birkaç tuşa dayalı sıralı seçim (ve yalnızca const/NULL çifti değil) makalede tartışılmaktadır .
#4: Pek çok gereksiz şey okuyoruz
ne zaman ortaya çıkar
Kural olarak, mevcut bir isteğe "başka bir filtre eklemek" istediğinizde ortaya çıkar.
“Ve sende aynısı yok ama sedef düğmeler annesiyle? " "Elmas Kol" filmi
Örneğin, yukarıdaki görevi değiştirerek, amaçları ne olursa olsun, işlenecek ilk 20 en eski "kritik" isteği gösterin.
nasıl belirlenir
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
Öneriler
[Daha fazla] uzmanlık oluşturun WHERE koşuluyla dizin veya dizine ek alanlar ekleyin.
Filtre koşulu amaçlarınız açısından "statik" ise - yani genişleme anlamına gelmez Gelecekteki değerlerin listesi - WHERE dizinini kullanmak daha iyidir. Çeşitli boolean/enum durumları bu kategoriye çok iyi uyar.
Filtreleme koşulu ise farklı anlamlar alabilir, o zaman dizini bu alanlarla genişletmek daha iyidir - BitmapAnd'da olduğu gibi.
Örnek:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, CASE
WHEN random() < 1::real/16 THEN NULL
ELSE (random() * 100)::integer -- 100 разных внешних ключей
END fk_own
, (random() < 1::real/50) critical; -- 1:50, что заявка "критичная"
CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);
SELECT
*
FROM
tbl
WHERE
critical
ORDER BY
pk
LIMIT 20;
Düzeltme:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Gördüğünüz gibi filtreleme plandan tamamen kalktı ve istek 5 kat daha hızlı.
#5: seyrek masa
ne zaman ortaya çıkar
Tablodaki çok sayıda kaydın güncellenmesi/silinmesi, çok sayıda "ölü" kayıt durumuna yol açtığında, kendi görev işleme kuyruğunuzu oluşturmaya yönelik çeşitli girişimler.
nasıl belirlenir
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Öneriler
Düzenli olarak manuel olarak gerçekleştirin VAKUM [DOLU] veya yeterince sık eğitim almayı başarmak parametrelerinin ince ayarını yaparak .
Çoğu durumda, bu tür sorunlara iş mantığından çağrı yaparken, yukarıda tartışılanlar gibi zayıf sorgu kompozisyonu neden olur. .
Ancak VAKUM DOLU'nun bile her zaman yardımcı olmayabileceğini anlamalısınız. Bu gibi durumlarda, makaledeki algoritmayı tanımaya değer. .
#6: Endeksin “ortasından” okuma
ne zaman ortaya çıkar
Görünüşe göre biraz okuduk ve her şey dizine eklendi ve fazla kimseyi filtrelemedik - ancak yine de istediğimizden çok daha fazla sayfa okuyoruz.
nasıl belirlenir
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Öneriler
Kullanılan endeksin yapısına ve sorguda belirtilen anahtar alanlara yakından bakın - büyük olasılıkla endeksin bir kısmı belirtilmedi. Büyük olasılıkla benzer bir dizin oluşturmanız gerekecek, ancak önek alanları veya .
Örnek:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 100)::integer fk_org -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_org, fk_cli); -- все почти как в #2
-- только вот отдельный индекс по fk_cli мы уже посчитали лишним и удалили
SELECT
*
FROM
tbl
WHERE
fk_cli = 999 -- а fk_org не задано, хотя стоит в индексе раньше
LIMIT 20;
Endekse göre bile her şey yolunda görünüyor, ancak bir şekilde şüpheli - okunan 20 kaydın her biri için, 4 sayfalık veriyi çıkarmamız gerekti, kayıt başına 32 KB - bu çok kalın değil mi? Ve dizin adı tbl_fk_org_fk_cli_idx düşündürücü.
Düzeltme:
CREATE INDEX ON tbl(fk_cli);
Birden - 10 kat daha hızlı ve 4 kat daha az okuma!
Makalede indekslerin etkisiz kullanıldığı durumların diğer örnekleri görülebilir. .
#7: CTE × CTE
ne zaman ortaya çıkar
Talep üzerine “şişman” CTE puanı aldı farklı tablolardan ve sonra bunu aralarında yapmaya karar verdim JOIN.
Bu durum v12'nin altındaki sürümler veya aşağıdaki özelliklere sahip istekler için geçerlidir: WITH MATERIALIZED.
nasıl belirlenir
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
Öneriler
Talebi dikkatlice analiz edin ve ? Eğer evet ise, o zaman hstore/json'da "sözlüğü" uygulayın açıklanan modele göre .
#8: diske takas (geçici olarak yazıldı)
ne zaman ortaya çıkar
Çok sayıda kaydın tek seferlik işlenmesi (sıralanması veya benzersizleştirilmesi), bunun için ayrılan belleğe sığmıyor.
nasıl belirlenir
-> *
&& temp written > 0Öneriler
İşlem tarafından kullanılan bellek miktarı parametrenin belirtilen değerini büyük ölçüde aşmıyorsa , düzeltmeye değer. Hemen herkes için yapılandırmada veya aracılığıyla yapabilirsiniz. SET [LOCAL] belirli bir istek/işlem için.
Örnek:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Düzeltme:
SET work_mem = '128MB'; -- перед выполнением запроса
Belli nedenlerden dolayı, disk yerine yalnızca bellek kullanılırsa sorgu çok daha hızlı yürütülür. Aynı zamanda HDD'den gelen yükün bir kısmı da kaldırılır.
Ancak her zaman çok fazla bellek ayıramayacağınızı anlamalısınız - herkese yetecek kadar bellek olmayacak.
#9: alakasız istatistikler
ne zaman ortaya çıkar
Veritabanına aynı anda çok şey döktüler ama onu uzaklaştırmaya zamanları olmadı ANALYZE.
nasıl belirlenir
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10Öneriler
Bunu gerçekleştir ANALYZE.
Bu durum daha detaylı bir şekilde anlatılmaktadır. .
#10: “bir şeyler ters gitti”
ne zaman ortaya çıkar
Rakip bir istek tarafından uygulanan kilit için bir bekleme vardı veya CPU/hipervizör donanım kaynakları yetersizdi.
nasıl belirlenir
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
Öneriler
Harici kullan izleme sistemi engelleme veya anormal kaynak tüketimi için sunucu. Bu süreci yüzlerce sunucu için organize etme versiyonumuzdan zaten bahsetmiştik. и .
Kaynak: habr.com