Sababaraha bulan ka pengker - umum kana PostgreSQL.
Anjeun parantos nganggo éta langkung ti 6000 kali ti saprak éta, tapi salah sahiji fitur anu gunana panginten henteu kapendak nyaéta clues struktural, anu katingali sapertos kieu:
Dangukeun aranjeunna sareng pamenta anjeun bakal "janten silky mulus". 🙂
Tapi sacara serius, seueur kaayaan anu ngajantenkeun pamundut anu laun sareng "rakus" dina hal sumberdaya, has sarta bisa dipikawanoh ku struktur jeung data rencana.
Dina hal ieu, unggal pamekar individu moal kudu néangan hiji pilihan optimasi on sorangan, ngandelkeun solely on pangalaman sorangan - urang bisa ngabejaan manehna naon anu lumangsung di dieu, naon bisa jadi alesan, sarta kumaha carana datang nepi ka solusi. Anu kami lakukeun.
Hayu urang ningal langkung caket kana kasus-kasus ieu - kumaha ditetepkeunana sareng naon saran anu dituju.
Pikeun immersion hadé dina topik, Anjeun mimitina tiasa ngadangukeun blok pakait tina , sarta ngan lajeng buka analisis lengkep unggal conto:
#1: indéks "undersorting"
Nalika timbul
Témbongkeun invoice panungtungan pikeun klien "LLC Kolokolchik".
Kumaha pikeun ngaidentipikasi
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
saran
Indéks dipaké dilegakeun ku widang asihan.
contona:
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;
Anjeun tiasa langsung perhatikeun yén langkung ti 100 rékaman dikurangan ku indéks, anu teras diurutkeun, teras ngan ukur hiji anu tinggaleun.
Urang ngalereskeun:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Malah dina sampel primitif sapertos - 8.5x langkung gancang sareng 33x langkung sakedik dibaca. Pangaruhna bakal langkung jelas, langkung seueur "fakta" anu anjeun gaduh pikeun tiap nilai. fk.
Kuring dicatet yén indéks misalna hiji bakal dianggo salaku indéks "awalan" teu leuwih goreng ti saméméhna pikeun queries séjén kalawan. fk, dimana asihan dumasar pk éta henteu sareng henteu (anjeun tiasa maca langkung seueur ngeunaan ieu ). Khususna, éta bakal nyayogikeun normal rojongan konci asing eksplisit ku widang ieu.
#2: parapatan indéks (BitmapAnd)
Nalika timbul
Témbongkeun sagala kontrak pikeun klien "LLC Kolokolchik" menyimpulkan atas nama "NJSC Lyutik".
Kumaha pikeun ngaidentipikasi
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index Scansaran
nyiptakeun indéks komposit ku widang ti duanana sumber atawa dilegakeun salah sahiji widang nu aya ti kadua.
contona:
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); -- отбор по конкретной паре
Urang ngalereskeun:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Di dieu gain leuwih leutik, saprak Bitmap Heap Scan cukup éféktif sorangan. Tapi atoh 7x langkung gancang sareng 2.5x langkung sakedik dibaca.
#3: Ngagabungkeun Indéks (BitmapAtawa)
Nalika timbul
Témbongkeun 20 pangkolotna pangkolotna "sorangan" atawa requests unassigned pikeun ngolah, kalawan prioritas sorangan.
Kumaha pikeun ngaidentipikasi
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index Scansaran
Anggo UNION [SADAYANA] pikeun ngagabungkeun subqueries pikeun tiap tina kaayaan OR blok.
contona:
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;
Urang ngalereskeun:
(
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, больше и не надо
Kami ngamangpaatkeun kanyataan yén sadaya 20 rékaman anu diperyogikeun langsung dicandak dina blok kahiji, janten anu kadua, kalayan Bitmap Heap Scan anu langkung "mahal", bahkan henteu dieksekusi - salaku hasilna. 22x langkung gancang, 44x langkung seueur bacaan!
Carita anu langkung rinci ngeunaan metode optimasi ieu dina conto konkrit bisa dibaca dina artikel и .
Vérsi umum maréntahkeun pilihan ku sababaraha kenop (teu ngan pikeun sapasang const / NULL) dibahas dina artikel .
# 4: Urang maca teuing
Nalika timbul
Sakumaha aturan, éta lumangsung nalika anjeun hoyong "ngagantelkeun saringan anu sanés" kana pamundut anu tos aya.
"Sareng anjeun henteu gaduh anu sami, tapi kalawan kancing mutiara? » pilem "Inten Hand"
Contona, ngaropea tugas di luhur, némbongkeun kahiji 20 pangkolotna "kritis" requests pikeun ngolah, paduli tujuanana.
Kumaha pikeun ngaidentipikasi
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
saran
Jieun [leuwih] husus indéks kalawan WHERE klausa atawa kaasup widang tambahan dina indéks.
Upami kaayaan nyaring "statis" pikeun tugas anjeun - éta teu kaasup ékspansi daptar nilai dina mangsa nu bakal datang - eta leuwih hade migunakeun indéks WHERE. Rupa-rupa status boolean/enum pas kana kategori ieu.
Lamun kaayaan filtration tiasa nyandak nilai anu béda, éta hadé pikeun dilegakeun indéks jeung widang ieu - sakumaha dina kaayaan kalawan BitmapAnd luhur.
contona:
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;
Urang ngalereskeun:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Sakumaha anjeun tiasa tingali, nyaring tina rencana tos rengse musna, sarta pamundut geus jadi 5 kali leuwih gancang.
# 5: méja sparse
Nalika timbul
Rupa-rupa usaha pikeun nyieun antrian ngolah tugas anjeun sorangan, nalika sajumlah ageung apdet / ngahapus rékaman dina méja nyababkeun kaayaan anu ageung tina rékaman "maot".
Kumaha pikeun ngaidentipikasi
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
saran
Sacara manual ngalaksanakeun rutin VACUUM [PENUH] atawa ngahontal processing adequately sering ku fine-tuning parameter na, kaasup .
Dina kalolobaan kasus, masalah sapertos anu disababkeun ku tata perenah query goréng lamun disebut ti logika bisnis, kayaning nu dibahas dina .
Tapi urang kudu ngarti yén sanajan VACUUM FULL teu salawasna bisa mantuan. Pikeun kasus sapertos kitu, anjeun kedah familiarize diri sareng algoritma tina tulisan. .
# 6: maca tina "tengah" indéks
Nalika timbul
Sigana aranjeunna maca sakedik, sareng sadayana diindeks, sareng aranjeunna henteu nyaring saha waé tambahan - tapi tetep, langkung seueur halaman anu dibaca tibatan anu urang pikahoyong.
Kumaha pikeun ngaidentipikasi
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
saran
Candak katingal caket di struktur indéks dipaké sarta widang konci dieusian dina query - paling dipikaresep, bagian indéks teu diatur. Anjeun paling dipikaresep kudu nyieun indéks sarupa, tapi tanpa widang awalan, atawa .
contona:
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;
Sagalana sigana rupa, sanajan dina hal indéks, tapi kumaha bae curiga - pikeun tiap tina 20 rékaman dibaca, 4 kaca data kudu dikurangan, 32KB per catetan - teu wani? Enya jeung ngaran indéks tbl_fk_org_fk_cli_idx ngarah kana pamikiran.
Urang ngalereskeun:
CREATE INDEX ON tbl(fk_cli);
Ngadadak- 10 kali leuwih gancang sarta 4 kali kirang maca!
Pikeun langkung seueur conto pamakean indéks anu teu cekap, tingali tulisan .
#7: CTE × CTE
Nalika timbul
Dina pamundut ngoleksi "gajih" CTE ti tabel béda, lajeng mutuskeun pikeun ngalakukeun antara aranjeunna JOIN.
Kasus ieu relevan pikeun versi handap v12 atanapi requests kalawan WITH MATERIALIZED.
Kumaha pikeun ngaidentipikasi
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
saran
Nganalisis paménta taliti ? Lamun enya, lajeng nerapkeun "kamus" dina hstore / json nurutkeun model digambarkeun dina .
#8: swap kana disk (temp ditulis)
Nalika timbul
Hiji-waktos processing (asihan atawa uniqueization) tina sajumlah badag rékaman teu cocog kana mémori disadiakeun pikeun ieu.
Kumaha pikeun ngaidentipikasi
-> *
&& temp written > 0saran
Lamun jumlah memori dipaké ku operasi teu greatly ngaleuwihan nilai set parameter , kudu dibenerkeun. Anjeun tiasa langsung di config for everyone, atawa anjeun bisa ngaliwatan SET [LOCAL] pikeun pamundut / transaksi husus.
contona:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Urang ngalereskeun:
SET work_mem = '128MB'; -- перед выполнением запроса
Kanggo alesan anu jelas, upami ngan ukur mémori anu dianggo sareng sanés disk, maka pamundutna bakal langkung gancang. Dina waktos anu sami, bagian tina beban ogé dipiceun tina HDD.
Tapi anjeun kedah ngartos yén alokasi seueur mémori ogé moal tiasa dianggo - éta ngan saukur moal cekap pikeun sadayana.
# 9: statistik teu relevan
Nalika timbul
Seueur anu dituang kana dasarna sakaligus, tapi aranjeunna henteu gaduh waktos pikeun ngusir éta ANALYZE.
Kumaha pikeun ngaidentipikasi
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10saran
Méakkeun sarua ANALYZE.
Kaayaan ieu dijelaskeun sacara langkung rinci dina .
#10: "aya anu salah"
Nalika timbul
Aya konci ngantosan hiji pamundut competing, atanapi aya teu cukup CPU / sumberdaya hardware hypervisor.
Kumaha pikeun ngaidentipikasi
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
saran
Paké éksternal sistem ngawaskeun server pikeun blocking atawa konsumsi sumberdaya abnormal. Kami parantos nyarioskeun ngeunaan versi kami pikeun ngatur prosés ieu pikeun ratusan server. и .
sumber: www.habr.com