Bug #4252

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

Added by Pavel Kácha 6 months ago. Updated 30 days ago.

Status:ClosedStart date:10/05/2018
Priority:NormalDue date:
Assignee:Jan Mach% Done:

100%

Category:Development - Core
Target version:2.1

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

Bug #4350: Enum table not populated for cesnet_inspectionerrorsClosedRadko Krkoš

Bug #4367: Enumeration tables not consistent for non array columns t...ClosedPavel Kácha

Associated revisions

Revision 01afee95
Added by Jan Mach 5 months ago

Rewritten the retrieval of distinct values for better performance.

Our database specialist pointed out, that currently the distinct value calculations for event table columns result in heavy paralel sequential scans. This is because the data model is optimized for efficiency and we are using as few relations and joins as possible for best performance. This means our event data model is flat and there are no tables containing common values like categories etc. The drawback if this approach is, that for retrieving column value enumerations the queries are very, very resource consuming. These results are used “only” for things like generating select boxes containing possible values in the web interface. These queries are executed periodically and they can load the database for tens of seconds, which is does not make sense in comparison with the use cases. Therefore we have decided to implement optimalization mechanism, that will cleverly precalculate the lists into separate tables and fetching it then will be a matter fraction of a second. (Redmine issue: #4252)

Revision f18468d0
Added by Jan Mach 5 months ago

Added missing enum precalculations for source_type and target_type columns.

(Redmine issue: #4252)

Revision 0eec1c90
Added by Jan Mach 5 months ago

Minor improvements of logging in mentat-precache.py module.

Added debug level logs to better track precaching operations. (Redmine issue: #4252)

Revision bdbd97a3
Added by Jan Mach 5 months ago

Documented the steps necessary for upgrading the Mentat 2.x to latest version.

With the addition of database migration tools the steps for successfull upgrade of Mentat system within the 2.x series had to be documented. (Redmine issue: #4252)

Revision 89fb5818
Added by Jan Mach 5 months ago

Added support for cleaning up enumeration tables into mentat-cleanup.py module.

This is a final piece in the puzzle. It was necessary to implement mechanism for removing outdated records from enumeration tables. (Redmine issue: #4252)

History

#1 Updated by Pavel Kácha 6 months 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?

#2 Updated by Pavel Kácha 6 months 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í.

#3 Updated by Pavel Kácha 6 months ago

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

#4 Updated by Pavel Kácha 6 months 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)

#5 Updated by Radko Krkoš 6 months 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).

#7 Updated by Pavel Kácha 6 months 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?

#8 Updated by Radko Krkoš 6 months 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;

#9 Updated by Pavel Kácha 6 months 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.

#10 Updated by Jan Mach 5 months ago

  • Target version changed from Future to 2.1

#11 Updated by Jan Mach 5 months 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.

#12 Updated by Jan Mach 5 months 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.

#13 Updated by Radko Krkoš 5 months 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.

#14 Updated by Radko Krkoš 5 months ago

  • Assignee changed from Radko Krkoš to Jan Mach

#15 Updated by Jan Mach 5 months ago

  • Status changed from Resolved to Closed

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

Also available in: Atom PDF