Mentat

Date: Mon, 28 May 2018 15:42:18 +0200
From: Pavel Kácha <ph@cesnet.cz>

...

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?

Date: Thu, 31 May 2018 11:27:38 +0200
From: Radko Krkoš <krkos@cesnet.cz>

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í.

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

[1] https://www.postgresql.org/docs/devel/static/release-11.html

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?

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;

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.