Project

General

Profile

Actions

Bug #4252

closed

Slow queries for list precalculation (heavy parallel sequential scan 8-20 sec per each 10 mins)

Added by Pavel Kácha about 4 years ago. Updated over 3 years ago.

Status:
Closed
Priority:
Normal
Assignee:
Category:
Development - Core
Target version:
Start date:
10/05/2018
Due date:
% Done:

100%

Estimated time:
(Total: 0.00 h)
To be discussed:

Description

Date: Mon, 28 May 2018 14:17:49 +0200
From: Radko Krkoš <>
Subject: [Mentat] O optimalizácii dotazov pre číselníky

Zdravím páni,
najčastejším typom dotazu nad 2 s je získanie zoznamu unikátnych hodnôt
pre číselníky, ktoré sa opakuje každých 10 minút pre stĺpce category,
source_type, target_type, node_name, node_type, protocol,
cesnet_resolvedabuses, cesnet_eventclass a cesnet_eventseverity.

Aktuálne sa používa parlelný sekvenčný scan na 9 vlákien a každý dotaz
trvá 4 - 10 s. Dotaz je:

SELECT unnest(col) AS data FROM events GROUP BY data ORDER BY data;

pre polia, či:

SELECT col AS data FROM events GROUP BY data ORDER BY data;

pre položky typu text, t.j. cesnet_eventclass a cesnet_eventseverity.

Takýto dotaz viac nezoptimalizujeme, na unnest() index použiť nejde a
inak je sekvenčný scan aj tak najlepšia voľba.

Špecifikom týchto stĺpcov je, že pomer počtu unikátnych hodnôt k
celkovému počtu riadkov je veľmi malé číslo. Riešením by bol rekurzívny
dotaz s postupným hľadaním minima. Príklad (pre psql):

\set col cesnet_eventseverity
WITH RECURSIVE t AS (
SELECT MIN AS col FROM events
UNION ALL
SELECT (SELECT MIN FROM events WHERE :col > t.col) FROM t
WHERE t.col IS NOT NULL
)
SELECT col FROM t;

alebo pre polia (rozdiel len v poslednom riadku):

\set col protocol
WITH RECURSIVE t AS (
SELECT MIN AS col FROM events
UNION ALL
SELECT (SELECT MIN FROM events WHERE :col > t.col) FROM t
WHERE t.col IS NOT NULL
)
SELECT unnest(col) as data FROM t GROUP BY data ORDER BY data;

kde tieto sú schopné využiť veľmi rýchly Index Only Scan nad indexom
typu BTREE či lepšie s podmienkou WHERE col IS NOT NULL (menší
index). Dotaz beží jednotky ms, teda vidíme zlepšenie o 3 rády.
Problémom je že takýto index máme štandardne vytvorený len na
cesnet_eventseverity. Pre iné polia by ho bolo nutné pridať, bavíme sa o
1,5 - 3,5 GB na index teda dokopy takmer 20 GB indexov. Všetky naraz som
ich neskúšal ale začínajú sa nám objavovať volania INSERT či COMMIT
ktoré sa dostanú nad 2 s. Zatiaľ to asi problém nerobí ale nerád by som
sa dostal k zahlteniu skôr ako je nutné.*
Pre prípadné vaše odskúšanie som vytvoril index nad protocol.
Čo si o tom myslíte? Ja si nie som istý, či to nakoniec za to stojí. Ale
je to nahradenie dotazov a vytvorenie nových indexov ako sme sa bavili.


Subtasks 2 (0 open2 closed)

Bug #4350: Enum table not populated for cesnet_inspectionerrorsClosedRadko Krkoš10/05/2018

Actions
Bug #4367: Enumeration tables not consistent for non array columns that can contain NULLClosedPavel Kácha10/15/2018

Actions
Actions #1

Updated by Pavel Kácha about 4 years ago

Date: Mon, 28 May 2018 15:42:18 +0200
From: Pavel Kácha <>

...

Takýto dotaz viac nezoptimalizujeme, na unnest() index použiť nejde a
inak je sekvenčný scan aj tak najlepšia voľba.

Jo, tohle je oser. Je to vlastně jen kvůli "zhezčení" vstupu, a přitom je
to takovéhle kladivo na databázi. A jestli to chápu dobře, tak jen proto, že
v oněch buňkách máme pole, a PQ dělá index polí, nikoliv index položek v
poli (což je u SQL databáze poměrně logické), a tím pádem nemůže unikátní
hodnoty dostat jednoduše z levé strany indexu.

...

Nešel by tím nějaký jiný index nahradit? Tj. dosáhnout toho, aby se
používaly tyto indexy místo jiného/jiných?

Každopádně tady si zrovna myslím, že výkon na select vs cena zápisu
nestojí za to, řekl bych, že jsou ještě jiné možnosti, jak to vyřešit. Maně
mě napadá:

1, napsat průtokového démona, který to bude ze zpráv rovnou počítat a
updatovat nějakou malou kešovací tabuli
- menší problém je promazávání, to je ale řešitelné tak, že se u každé
unikátní hodnoty rovnou uloží i časová známka posledního update,
pak je smazání starých triviální a rychlé

2, totéž, ale bez démona, upravit SELECT na počítaní vždy za relevantní od
posledního počítání, tj. třeba za posledních 10 minut, a mergovat s
existujícími výsledky. To by mohlo být dostatečně rychlé, a opět, pokud
se přidá časová značka, promazávání je jednoduché.

Druhá varianta by mohla být poměrně jednoduchá úprava současného dotazu.
Pletu se?

Actions #2

Updated by Pavel Kácha about 4 years ago

Date: Thu, 31 May 2018 11:27:38 +0200
From: Radko Krkoš <>

Jo, tohle je oser. Je to vlastně jen kvůli "zhezčení" vstupu, a přitom je
to takovéhle kladivo na databázi. A jestli to chápu dobře, tak jen proto, že
v oněch buňkách máme pole, a PQ dělá index polí, nikoliv index položek v
poli (což je u SQL databáze poměrně logické), a tím pádem nemůže unikátní
hodnoty dostat jednoduše z levé strany indexu.

Má to dva dôvody, jeden je ako píšeš, BTREE indexy indexujú pole ako
celok, tým pádom sú veľké, na polia sú dobré GIN indexy ktoré pracujú
nad jednotlivými prvkami ale tie zase sú určené na operácie ako @>
(obsahuje) a vôbec nie na nájdi ďalší.
Druhá vec je že SELECT DISTINCT / GROUP BY je súčasťou štandardu a teda
musí byť podporovaný ale (ne)oficiálne stanovisko je že sa nebude príliš
optimalizovať lebo to nie je potrebná operácia. Ak to niekto potrebuje,
znamená to zlý návrh DB, nedodržanie normálnej formy, čo je pravda, ale
v našom prípade si nemyslím že to je cesta, skúšali sme to. A podobnú
úpravu by som v tejto fáze robiť nechcel.

Nešel by tím nějaký jiný index nahradit? Tj. dosáhnout toho, aby se
používaly tyto indexy místo jiného/jiných?

Teoreticky áno ale problém je že keď nahradíme GIN index nad poľom
BTREE, tak namiesto 100MB máme 2,5GB index a to za to podľa mňa nestojí.
Teoreticky by sa dal použiť HASH index ale ten sa bude nad takto veľkou
tabuľkou počítať donekonečna (po hodine som to zabil), takže to asi tiež
nie je cesta.

Každopádně tady si zrovna myslím, že výkon na select vs cena zápisu
nestojí za to, řekl bych, že jsou ještě jiné možnosti, jak to vyřešit. Maně
mě napadá:

Výborne, tak sme sa zhodli. Ako riešenie to je zaujímavé, zrýchlenie je
výborné ale tiež to podľa mňa za to nestojí.

1, napsat průtokového démona, který to bude ze zpráv rovnou počítat a
updatovat nějakou malou kešovací tabuli
- menší problém je promazávání, to je ale řešitelné tak, že se u každé
unikátní hodnoty rovnou uloží i časová známka posledního update,
pak je smazání starých triviální a rychlé

Nad týmto som rozmýšľal ale neprišlo mi rozumné navrhovať riešenie, kde
Mek by strávil 2 dni implementáciou, takže som najprv skúšal niečo iné.

2, totéž, ale bez démona, upravit SELECT na počítaní vždy za relevantní od
posledního počítání, tj. třeba za posledních 10 minut, a mergovat s
existujícími výsledky. To by mohlo být dostatečně rychlé, a opět, pokud
se přidá časová značka, promazávání je jednoduché.

Druhá varianta by mohla být poměrně jednoduchá úprava současného dotazu.
Pletu se?

Myslím že toto bude najlepšia cesta s ohľadom na výkon, miesto na disku
a čas implementácie.

Navrhujem teda (uvedené je pre stĺpec node_name, podobne treba pre ostatné):

Pridať do inicializačného skriptu:

CREATE TABLE enum_node_name(
data text UNIQUE,
last_seen TIMESTAMP WITHOUT TIME ZONE
);

a potom zameniť dotaz:

SELECT unnest("node_name") AS data FROM events GROUP BY data ORDER BY data

za

INSERT INTO enum_node_name (
SELECT unnest(node_name) AS data,
max(cesnet_storagetime) AS last_seen FROM events
WHERE cesnet_storagetime > COALESCE(
(SELECT max(last_seen) FROM enum_node_name),
(SELECT min(cesnet_storagetime) FROM events)
)
GROUP BY data
)
ON CONFLICT (data) DO UPDATE
SET last_seen = excluded.last_seen;

SELECT data from enum_node_name;

Tým vznikne malá tabuľka s číselníkom a časom posledného výskytu. Doba
behu je zhruba 50 ms pri spúšťaní každých 10 minút. Vzhľadom na dobu to
ale môžeme spúšťať častejšie, čo sa na dobe trvania kladne prejaví a
máme čerstvejšie dáta. Rieši sa aj prípad downtime, výsledky budú vždy
úplné (ak niekto nebude editovať číselníkovú tabuľku).
Problém je dizajn plánovača ktorý navrhne paralelný beh SELECT len v
prípade, že v dotaze nie je manipulácia s dátami. Jedná sa o umelé
obmedzenie ktoré môže byť v budúcnosti odstránené [1]. Predpokladám že
pre tento prípad sa tak aj stane, ak dochádza k nejakej šialenej
modifikácii zahrabanej niekde vnútri tak rozumiem že to je problém ale
ak len zabalíš SELECT do INSERT INTO, tak je to zbytočné.
V každom prípade prvotné naplnenie trvá až okolo 90 s, k dobe behu
zrovnateľnej s aktuálnym stavom sa dostaneme pri frekvencii raz za 3 dni
takže inak problém nebude.

Ak je doba prvotného naplnenia problém, tak alternatívne by šlo použiť:

INSERT INTO enum_node_name (
SELECT unnest(node_name) AS data,
max(cesnet_storagetime) AS last_seen FROM events
WHERE cesnet_storagetime >
(SELECT max(last_seen) FROM enum_node_name)
GROUP BY data
)
ON CONFLICT (data) DO UPDATE
SET last_seen = excluded.last_seen;

ale potom je treba detekovať prípad keď:

vráti prázdnu množinu a vykonať:

SELECT unnest(node_name) AS data, max(cesnet_storagetime) AS last_seen
FROM events;
INSERT INTO enum_node_name VALUES ((%s, %s), (%s, %s), ..., (%s, %s));

čiže SELECT tak ako ho máme teraz a následne INSERT všetkých dát, len
ich prehnať Pythonom, ide ale o malé množstvá, takže to nie je problém.

Obe riešenia sú citlivé na vonkajšiu manipuláciu s číselníkovou
tabuľkou, zvládnu len jej úplné vyprázdnenie, inak môže táto metóda
generovať neplatné číselníky. Nepovažujem to ale za reálne obmedzenie.

Bol by som bol za prvú variantu. Myslím že doba prvého behu nie je až
taká obmedzujúca a v budúcnosti sa to môže zadarmo zlepšiť. Druhá
varianta je workaround aktuálneho obmedzenia, zrejme to za to nestojí.

Actions #3

Updated by Pavel Kácha about 4 years ago

Note: moved from email, continuation in english, please.

Actions #4

Updated by Pavel Kácha about 4 years ago

  • Subject changed from Pomalé dotazy pro číselníky (náročný paralelní sekvenční sken 4-10 sekund každých 10 minut) to Slow queries for list precalculation (heavy parallel sequential scan 4-10 sec per each 10 mins)
Actions #5

Updated by Radko Krkoš about 4 years ago

  • Subject changed from Slow queries for list precalculation (heavy parallel sequential scan 4-10 sec per each 10 mins) to Slow queries for list precalculation (heavy parallel sequential scan 8-20 sec per each 10 mins)

Problém je dizajn plánovača ktorý navrhne paralelný beh SELECT len v prípade, že v dotaze nie je manipulácia s dátami.

Limitation lifted in PostgreSQL 11 [1], section E.1.3.1.2. Parallel Queries.

In postgres 11, proposed variant 1 has worst case performance equal to current state, median case below 100ms.
In postgres 10, worst case (first run only, very rare under normal operation) performance is still ~ 10x current state (no parallel SELECT).

Actions #7

Updated by Pavel Kácha about 4 years ago

Not sure of variants numbering, variant 1 means flow-through daemon, or it refers SELECT variants, as in "SELECT unnest(“node_name”) AS data FROM events GROUP BY data ORDER BY data"?

Also, which way you recommend to pursue then?

Actions #8

Updated by Radko Krkoš about 4 years ago

Pavel Kácha wrote:

Also, which way you recommend to pursue then?

This one.

Init:

CREATE TABLE enum_node_name(
    data text UNIQUE,
    last_seen TIMESTAMP WITHOUT TIME ZONE
);

New query (queries):

INSERT INTO enum_node_name (
    SELECT unnest(node_name) AS data, max(cesnet_storagetime) AS last_seen FROM events
    WHERE cesnet_storagetime > COALESCE(
        (SELECT max(last_seen) FROM enum_node_name),
        (SELECT min(cesnet_storagetime) FROM events)
)
GROUP BY data
)
ON CONFLICT (data) DO UPDATE
SET last_seen = excluded.last_seen;

SELECT data from enum_node_name;

Actions #9

Updated by Pavel Kácha about 4 years ago

Ok. Note for Mek: Radko's solution is purely db based (needs new table). If you already use some other kind of cache (disk based, shelve based, json based, whatever), and makes sense to incorporate data there (instead of db), then only SELECT unnest part is applicable. Choice is up to you.

Actions #10

Updated by Jan Mach about 4 years ago

  • Target version changed from Backlog to 2.1
Actions #11

Updated by Jan Mach about 4 years ago

  • Status changed from New to In Progress
  • % Done changed from 0 to 50

I have re-implemented the distinct value precaching mechanism according to the recommendations of Radko Krkoš. I have used the drop in replacement approach with separate tables for each enumeration list.

There is still the cleanup of these tables that needs to be implemented into our mentat-cleanup.py module.

Actions #12

Updated by Jan Mach about 4 years ago

  • Status changed from In Progress to Feedback
  • Assignee changed from Jan Mach to Radko Krkoš
  • % Done changed from 50 to 100

Latest commit adds support for cleanup of enumeration tables. That was final piece in the puzzle, I now consider this issue to be resolved and ask for feedback.

Actions #13

Updated by Radko Krkoš almost 4 years ago

  • Status changed from Feedback to Resolved

After longer period of observation due to what was identified as an unrelated problem, everything seems OK. All green for production environment deployment.

Actions #14

Updated by Radko Krkoš almost 4 years ago

  • Assignee changed from Radko Krkoš to Jan Mach
Actions #15

Updated by Jan Mach almost 4 years ago

  • Status changed from Resolved to Closed

Closing as successfully resolved, will be deployed with next release.

Actions

Also available in: Atom PDF