Fa uns quants mesos - públic a PostgreSQL.
Ja l'heu utilitzat més de 6000 vegades, però una característica útil que pot haver passat desapercebuda és pistes estructurals, que semblen una cosa així:
Escolteu-los i les vostres peticions "es tornaran suaus i sedosos". 🙂
Però, seriosament, moltes situacions que fan que una sol·licitud sigui lenta i amb fam de recursos són típics i es poden reconèixer per l'estructura i les dades del pla.
En aquest cas, cada desenvolupador individual no ha de buscar una opció d'optimització pel seu compte, basant-se únicament en la seva experiència: podem dir-li què està passant aquí, quin podria ser el motiu i com abordar una solució. Això és el que vam fer.
Fem una ullada més de prop a aquests casos: com es defineixen i a quines recomanacions porten.
Per submergir-vos millor en el tema, primer podeu escoltar el bloc corresponent des de , i només després passar a una anàlisi detallada de cada exemple:
#1: índex "subclassificació"
Quan sorgeix
Mostra l'última factura per al client "LLC Kolokolchik".
Com identificar-se
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
Recomanacions
Índex utilitzat expandir amb camps d'ordenació.
Exemple:
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;
De seguida es pot notar que es van restar més de 100 registres de l'índex, que després es van ordenar tots i després n'ha quedat l'únic.
Correcció:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Fins i tot en una mostra tan primitiva - 8.5 vegades més ràpid i 33 vegades menys lectures. Com més "fets" tingueu per a cada valor, més evident serà l'efecte fk.
Observo que aquest índex funcionarà com a índex de "prefix" no pitjor que abans per a altres consultes amb fk, on ordenar per pk no hi havia i no hi ha (podeu llegir més sobre això ). Inclòs, proporcionarà normal suport explícit de clau estrangera en aquest camp.
#2: intersecció d'índex (BitmapAnd)
Quan sorgeix
Mostra tots els acords per al client "LLC Kolokolchik", celebrat en nom de "NAO Buttercup".
Com identificar-se
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanRecomanacions
crear índex compost per camps dels dos originals o ampliar un dels existents amb camps del segon.
Exemple:
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); -- отбор по конкретной паре
Correcció:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
El benefici aquí és més petit, ja que l'escaneig de bitmap Heap és bastant efectiu per si sol. De totes formes 7 vegades més ràpid i 2.5 vegades menys lectures.
#3: fusiona índexs (BitmapOr)
Quan sorgeix
Mostra les 20 primeres sol·licituds més antigues "nosaltres" o no assignades per processar-les, amb la teva prioritat.
Com identificar-se
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanRecomanacions
Utilitzeu UNIÓ [TOTS] per combinar subconsultes per a cadascun dels blocs OR de condicions.
Exemple:
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;
Correcció:
(
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, больше и не надо
Hem aprofitat que els 20 registres necessaris es van rebre immediatament al primer bloc, de manera que el segon, amb l'escaneig de mapa de bits més "car", ni tan sols es va executar, al final 22 vegades més ràpid, 44 vegades menys lectures!
Una història més detallada sobre aquest mètode d'optimització utilitzant exemples concrets es pot llegir als articles и .
Versió generalitzada selecció ordenada basada en diverses claus (i no només la parella const/NULL) es parla a l'article .
#4: llegim moltes coses innecessàries
Quan sorgeix
Per regla general, sorgeix quan es vol "adjuntar un altre filtre" a una sol·licitud ja existent.
“I tu no en tens el mateix, però amb botons de nacre? " pel·lícula "El braç de diamant"
Per exemple, si modifiqueu la tasca anterior, mostreu les 20 primeres sol·licituds "crítiques" més antigues per processar, independentment de la seva finalitat.
Com identificar-se
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
Recomanacions
Crea [més] especialitzat índex amb la condició ON o incloure camps addicionals a l'índex.
Si la condició del filtre és "estàtica" per als vostres propòsits, és a dir no implica expansió llista de valors en el futur: és millor utilitzar un índex WHERE. Diversos estats booleans/enum encaixen bé en aquesta categoria.
Si la condició de filtrat pot tenir diferents significats, llavors és millor ampliar l'índex amb aquests camps, com en la situació anterior amb BitmapAnd.
Exemple:
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;
Correcció:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Com podeu veure, el filtratge ha desaparegut completament del pla i la sol·licitud s'ha convertit en 5 vegades més ràpid.
#5: taula escassa
Quan sorgeix
Diversos intents de crear la vostra pròpia cua de processament de tasques, quan un gran nombre d'actualitzacions/supressions de registres a la taula condueixen a una situació d'un gran nombre de registres "morts".
Com identificar-se
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Recomanacions
Realitzeu-ho manualment amb regularitat BUIT [PLENA] o aconseguir un entrenament suficientment freqüent ajustant els seus paràmetres, inclòs .
En la majoria dels casos, aquests problemes són causats per una mala disposició de la consulta quan es fa una trucada des de la lògica empresarial com les que s'han comentat a .
Però heu d'entendre que fins i tot VACUUM FULL pot no ser útil. En aquests casos, val la pena familiaritzar-se amb l'algoritme de l'article .
#6: Llegir des del "mitjan" de l'índex
Quan sorgeix
Sembla que vam llegir una mica, i tot estava indexat, i no vam filtrar ningú en excés, però tot i així llegim molt més pàgines de les que voldríem.
Com identificar-se
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Recomanacions
Mireu de prop l'estructura de l'índex utilitzat i els camps clau especificats a la consulta, el més probable és part de l'índex no està establerta. El més probable és que haureu de crear un índex similar, però sense els camps de prefix o .
Exemple:
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;
Sembla que tot va bé, fins i tot segons l'índex, però d'alguna manera és sospitós: per a cadascun dels 20 registres llegits, hem hagut de restar 4 pàgines de dades, 32 KB per registre, no és negreta? I el nom de l'índex tbl_fk_org_fk_cli_idx que fa pensar.
Correcció:
CREATE INDEX ON tbl(fk_cli);
De sobte - 10 vegades més ràpid i 4 vegades menys de llegir!
Altres exemples de situacions d'ús ineficaç dels índexs es poden veure a l'article .
#7: CTE × CTE
Quan sorgeix
A petició va anotar un CTE "gros". de diferents taules, i després van decidir fer-ho entre elles JOIN.
El cas és rellevant per a versions inferiors a v12 o sol·licituds amb WITH MATERIALIZED.
Com identificar-se
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
Recomanacions
Analitzeu acuradament la sol·licitud - i ? Si sí, aleshores aplicar "diccionari" a hstore/json segons el model descrit a .
# 8: intercanviar a disc (escriptura temporal)
Quan sorgeix
El processament únic (ordenació o singularització) d'un gran nombre de registres no encaixa a la memòria assignada per a això.
Com identificar-se
-> *
&& temp written > 0Recomanacions
Si la quantitat de memòria utilitzada per l'operació no supera en gran mesura el valor especificat del paràmetre , val la pena corregir-ho. Podeu accedir immediatament a la configuració per a tothom, o podeu fer-ho SET [LOCAL] per a una sol·licitud/transacció específica.
Exemple:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Correcció:
SET work_mem = '128MB'; -- перед выполнением запроса
Per raons òbvies, si només s'utilitza memòria i no disc, la consulta s'executarà molt més ràpid. Al mateix temps, també s'elimina part de la càrrega del disc dur.
Però heu d'entendre que no sempre podreu assignar molta i molta memòria, simplement no n'hi haurà prou per a tothom.
#9: estadístiques irrellevants
Quan sorgeix
Van abocar molt a la base de dades alhora, però no van tenir temps d'eliminar-la ANALYZE.
Com identificar-se
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10Recomanacions
Porta-ho a terme ANALYZE.
Aquesta situació es descriu amb més detall a .
#10: "Alguna cosa ha anat malament"
Quan sorgeix
Hi va haver una espera per a un bloqueig imposat per una sol·licitud competidora o no hi havia prou recursos de maquinari de la CPU/hipervisor.
Com identificar-se
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
Recomanacions
Ús extern sistema de seguiment servidor per bloquejar o consumir recursos anormals. Ja hem parlat de la nostra versió d'organitzar aquest procés per a centenars de servidors и .
Font: www.habr.com