A mikroszolgáltatási architektúrának, mint mindennek a világon, megvannak az előnyei és hátrányai. Egyes folyamatok könnyebbé válnak vele, mások nehezebbek. A változás gyorsasága és a jobb méretezhetőség érdekében pedig áldozatokat kell hozni. Ezek egyike az elemzés összetettsége. Ha egy monolitban az összes működési elemzés SQL lekérdezésekre redukálható egy analitikus replikára, akkor egy többszolgáltatásos architektúrában minden szolgáltatásnak saját adatbázisa van, és úgy tűnik, hogy egy lekérdezés nem elég (vagy talán lesz?). Akit érdekel, hogy miként oldottuk meg cégünknél az operatív analitika problémáját, és hogyan tanultunk meg ezzel a megoldással együtt élni, üdvözöljük.
A nevem Pavel Sivash, a DomClicknél egy olyan csapatban dolgozom, amely az analitikai adattárház karbantartásáért felelős. Hagyományosan az adatmérnökségnek tudható be tevékenységünk, valójában azonban sokkal szélesebb a feladatkör. Vannak szabványos adatmérnöki ETL / ELT, adatelemző eszközök támogatása és adaptálása, valamint saját eszközök fejlesztése. Különösen az operatív jelentésekhez úgy döntöttünk, hogy „úgy teszünk”, mintha monolitunk lenne, és egyetlen adatbázist adunk az elemzőknek, amely tartalmazza az összes szükséges adatot.
Általában különböző lehetőségeket mérlegeltünk. Lehetett teljes értékű adattárat építeni - meg is próbáltuk, de őszintén szólva nem tudtunk megbarátkozni a meglehetősen gyakori logikai változtatásokkal a tároló építésének és módosításának meglehetősen lassú folyamatával ( Ha valakinek sikerült, írja meg kommentben, hogyan). Mondhatnánk az elemzőknek: „Srácok, tanuljatok pythont, és menjetek az analitikai vonalra”, de ez egy további toborzási követelmény, és úgy tűnt, hogy ezt lehetőség szerint kerülni kell. Úgy döntöttünk, hogy kipróbáljuk az FDW (Foreign Data Wrapper) technológiát: valójában ez egy szabványos dblink, ami az SQL szabványban van, de sokkal kényelmesebb felülettel. Ez alapján hoztunk egy döntést, ami végül gyökeret vert, rátelepedtünk. Ennek részletei egy külön cikk témája, és talán több is, mert sok mindenről szeretnék beszélni: az adatbázisséma szinkronizálásától a hozzáférés-szabályozásig és a személyes adatok személytelenítéséig. Azt is meg kell jegyezni, hogy ez a megoldás nem helyettesíti a valódi analitikai adatbázisokat és adattárakat, csak egy konkrét problémát old meg.
Legfelső szinten így néz ki:
Van egy PostgreSQL adatbázis, ahol a felhasználók tárolhatják munkaadataikat, és ami a legfontosabb, az összes szolgáltatás analitikai replikája FDW-n keresztül kapcsolódik ehhez az adatbázishoz. Ez lehetővé teszi, hogy több adatbázisba írjunk lekérdezést, és teljesen mindegy, hogy mi az: PostgreSQL, MySQL, MongoDB vagy valami más (fájl, API, ha hirtelen nincs megfelelő wrapper, írhat sajátot). Nos, úgy tűnik, minden nagyszerű! Szakítani?
Ha minden ilyen gyorsan és egyszerűen véget érne, akkor valószínűleg a cikk nem létezne.
Fontos tisztában lenni azzal, hogy a Postgres hogyan kezeli a távoli szerverekhez intézett kéréseket. Ez logikusnak tűnik, de az emberek gyakran nem figyelnek rá: a postgres a lekérdezést olyan részekre osztja, amelyek egymástól függetlenül futnak le távoli szervereken, összegyűjti ezeket az adatokat, és maga végzi el a végső számításokat, így a lekérdezés végrehajtási sebessége nagyban függ attól, hogy le van írva. Azt is meg kell jegyezni: amikor távoli szerverről érkeznek az adatok, már nincs indexük, nincs semmi, ami segítené az ütemezőt, ezért csak mi magunk tudunk segíteni és javasolni. És erről szeretnék részletesebben beszélni.
Egy egyszerű kérés és egy terv hozzá
Annak bemutatásához, hogyan kérdez le a Postgres egy 6 millió soros táblázatot egy távoli szerveren, nézzünk meg egy egyszerű tervet.
explain analyze verbose
SELECT count(1)
FROM fdw_schema.table;
Aggregate (cost=418383.23..418383.24 rows=1 width=8) (actual time=3857.198..3857.198 rows=1 loops=1)
Output: count(1)
-> Foreign Scan on fdw_schema."table" (cost=100.00..402376.14 rows=6402838 width=0) (actual time=4.874..3256.511 rows=6406868 loops=1)
Output: "table".id, "table".is_active, "table".meta, "table".created_dt
Remote SQL: SELECT NULL FROM fdw_schema.table
Planning time: 0.986 ms
Execution time: 3857.436 msA VERBOSE utasítás használatával láthatjuk azt a lekérdezést, amelyet a távoli szervernek küldünk, és amelynek eredményét további feldolgozásra megkapjuk (RemoteSQL karakterlánc).
Menjünk egy kicsit tovább, és adjunk hozzá több szűrőt a kérésünkhöz: egyet a számára logikai mezőben, egyenként időbélyeg intervallumonként és egyenként jsonb.
explain analyze verbose
SELECT count(1)
FROM fdw_schema.table
WHERE is_active is True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month'
AND CURRENT_DATE - INTERVAL '6 month'
AND meta->>'source' = 'test';
Aggregate (cost=577487.69..577487.70 rows=1 width=8) (actual time=27473.818..25473.819 rows=1 loops=1)
Output: count(1)
-> Foreign Scan on fdw_schema."table" (cost=100.00..577469.21 rows=7390 width=0) (actual time=31.369..25372.466 rows=1360025 loops=1)
Output: "table".id, "table".is_active, "table".meta, "table".created_dt
Filter: (("table".is_active IS TRUE) AND (("table".meta ->> 'source'::text) = 'test'::text) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
Rows Removed by Filter: 5046843
Remote SQL: SELECT created_dt, is_active, meta FROM fdw_schema.table
Planning time: 0.665 ms
Execution time: 27474.118 msItt van a pillanat, amire figyelni kell a lekérdezések írásakor. A szűrők nem kerültek át a távoli szerverre, ami azt jelenti, hogy a végrehajtáshoz a postgres mind a 6 millió sort kihúzza, hogy helyi szűrést végezzen (Szűrő sor), és később összesítést hajtson végre. A siker kulcsa egy lekérdezés írása úgy, hogy a szűrők a távoli gépre kerüljenek, mi pedig csak a szükséges sorokat kapjuk és összesítjük.
Ez valami logikai szar
A logikai mezőkkel minden egyszerű. Az eredeti lekérdezésben a probléma az operátor miatt volt is. Ha helyettesítjük azzal =, akkor a következő eredményt kapjuk:
explain analyze verbose
SELECT count(1)
FROM fdw_schema.table
WHERE is_active = True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month'
AND CURRENT_DATE - INTERVAL '6 month'
AND meta->>'source' = 'test';
Aggregate (cost=508010.14..508010.15 rows=1 width=8) (actual time=19064.314..19064.314 rows=1 loops=1)
Output: count(1)
-> Foreign Scan on fdw_schema."table" (cost=100.00..507988.44 rows=8679 width=0) (actual time=33.035..18951.278 rows=1360025 loops=1)
Output: "table".id, "table".is_active, "table".meta, "table".created_dt
Filter: ((("table".meta ->> 'source'::text) = 'test'::text) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
Rows Removed by Filter: 3567989
Remote SQL: SELECT created_dt, meta FROM fdw_schema.table WHERE (is_active)
Planning time: 0.834 ms
Execution time: 19064.534 msMint látható, a szűrő a távoli szerverre repült, és a végrehajtási idő 27 másodpercről 19 másodpercre csökkent.
Megjegyzendő, hogy az üzemeltető is különbözik az operátortól = amelyik Null értékkel tud működni. Ez azt jelenti nem igaz a szűrőben meghagyja a False és Null értékeket, míg != Igaz csak hamis értékeket hagy. Ezért a kezelő cseréjekor nem két feltételt kell átadnia a szűrőnek az OR operátorral, például: WHERE (oszlop != igaz) VAGY (a oszlop nulla).
A logikai érték kitalálásával továbblépünk. Addig is állítsuk vissza a logikai érték szerinti szűrőt az eredeti formájába, hogy önállóan mérlegelhessük más változtatások hatását.
időbélyeg? hz
Általában gyakran kell kísérletezni azzal, hogyan írjon helyesen egy lekérdezést, amely távoli szervereket is érint, és csak ezután kell magyarázatot keresnie arra, hogy ez miért történik. Erről nagyon kevés információ található az interneten. Kísérletek során azt találtuk, hogy egy rögzített dátum szűrő nagy lendülettel repül egy távoli szerverre, de amikor dinamikusan akarjuk beállítani a dátumot, például a now() vagy CURRENT_DATE, ez nem történik meg. Példánkban egy szűrőt adtunk hozzá, hogy a Created_at oszlop pontosan 1 hónapra vonatkozó adatokat tartalmazzon a múltban (BEWEEN CURRENT_DATE - INTERVAL '7 hónap' ÉS CURRENT_DATE - INTERVAL '6 hónap'). Mit tettünk ebben az esetben?
explain analyze verbose
SELECT count(1)
FROM fdw_schema.table
WHERE is_active is True
AND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 month')
AND created_dt <(SELECT CURRENT_DATE::timestamptz - INTERVAL '6 month')
AND meta->>'source' = 'test';
Aggregate (cost=306875.17..306875.18 rows=1 width=8) (actual time=4789.114..4789.115 rows=1 loops=1)
Output: count(1)
InitPlan 1 (returns $0)
-> Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=1)
Output: ((('now'::cstring)::date)::timestamp with time zone - '7 mons'::interval)
InitPlan 2 (returns $1)
-> Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)
Output: ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)
-> Foreign Scan on fdw_schema."table" (cost=100.02..306874.86 rows=105 width=0) (actual time=23.475..4681.419 rows=1360025 loops=1)
Output: "table".id, "table".is_active, "table".meta, "table".created_dt
Filter: (("table".is_active IS TRUE) AND (("table".meta ->> 'source'::text) = 'test'::text))
Rows Removed by Filter: 76934
Remote SQL: SELECT is_active, meta FROM fdw_schema.table WHERE ((created_dt >= $1::timestamp with time zone)) AND ((created_dt < $2::timestamp with time zone))
Planning time: 0.703 ms
Execution time: 4789.379 msArra kértük a tervezőt, hogy az allekérdezésben előre számítsa ki a dátumot, és adja át a már elkészített változót a szűrőnek. És ez a tipp remek eredményt hozott, közel 6-szor gyorsabb lett a lekérdezés!
Itt is fontos ügyelni: az allekérdezés adattípusának meg kell egyeznie annak a mezőnek a típusával, amely alapján szűrünk, különben a tervező úgy dönt, hogy mivel a típusok eltérőek, és először be kell szerezni az összes adatokat, és helyileg szűrjük.
Állítsuk vissza a dátum szerinti szűrőt az eredeti értékére.
Freddy vs. jsonb
Általában a logikai mezők és dátumok már kellőképpen felgyorsították a lekérdezést, de volt még egy adattípus. Az általa való szűréssel folytatott harc, őszintén szólva, még mindig nem ért véget, bár itt is vannak sikerek. Tehát így sikerült átengednünk a szűrőt jsonb mező egy távoli szerverre.
explain analyze verbose
SELECT count(1)
FROM fdw_schema.table
WHERE is_active is True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month'
AND CURRENT_DATE - INTERVAL '6 month'
AND meta @> '{"source":"test"}'::jsonb;
Aggregate (cost=245463.60..245463.61 rows=1 width=8) (actual time=6727.589..6727.590 rows=1 loops=1)
Output: count(1)
-> Foreign Scan on fdw_schema."table" (cost=1100.00..245459.90 rows=1478 width=0) (actual time=16.213..6634.794 rows=1360025 loops=1)
Output: "table".id, "table".is_active, "table".meta, "table".created_dt
Filter: (("table".is_active IS TRUE) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
Rows Removed by Filter: 619961
Remote SQL: SELECT created_dt, is_active FROM fdw_schema.table WHERE ((meta @> '{"source": "test"}'::jsonb))
Planning time: 0.747 ms
Execution time: 6727.815 msAz operátorok szűrése helyett egy operátor jelenlétét kell használnia. jsonb egy másikban. 7 másodperc az eredeti 29 helyett. Eddig ez az egyetlen sikeres lehetőség a szűrők átadására jsonb távoli szerverre, de itt fontos figyelembe venni egy korlátot: az adatbázis 9.6-os verzióját használjuk, de április végére tervezzük az utolsó tesztek befejezését és a 12-es verzióra való átállást. Frissítéskor megírjuk, hogy ez hogyan érintette, mert nagyon sok változás van, amihez sok remény van: json_path, új CTE viselkedés, push down (10-es verziótól). Nagyon szeretném hamarosan kipróbálni.
Végezzen vele
Megvizsgáltuk, hogy az egyes változtatások egyenként hogyan befolyásolják a lekérdezés sebességét. Lássuk most, mi történik, ha mindhárom szűrőt helyesen írják.
explain analyze verbose
SELECT count(1)
FROM fdw_schema.table
WHERE is_active = True
AND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 month')
AND created_dt <(SELECT CURRENT_DATE::timestamptz - INTERVAL '6 month')
AND meta @> '{"source":"test"}'::jsonb;
Aggregate (cost=322041.51..322041.52 rows=1 width=8) (actual time=2278.867..2278.867 rows=1 loops=1)
Output: count(1)
InitPlan 1 (returns $0)
-> Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=1)
Output: ((('now'::cstring)::date)::timestamp with time zone - '7 mons'::interval)
InitPlan 2 (returns $1)
-> Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1)
Output: ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)
-> Foreign Scan on fdw_schema."table" (cost=100.02..322041.41 rows=25 width=0) (actual time=8.597..2153.809 rows=1360025 loops=1)
Output: "table".id, "table".is_active, "table".meta, "table".created_dt
Remote SQL: SELECT NULL FROM fdw_schema.table WHERE (is_active) AND ((created_dt >= $1::timestamp with time zone)) AND ((created_dt < $2::timestamp with time zone)) AND ((meta @> '{"source": "test"}'::jsonb))
Planning time: 0.820 ms
Execution time: 2279.087 msIgen, a lekérdezés bonyolultabbnak tűnik, ez egy kényszerár, de a végrehajtási sebesség 2 másodperc, ami több mint 10-szer gyorsabb! És egy viszonylag kis adathalmazra vonatkozó egyszerű lekérdezésről beszélünk. Valós kérésre akár több százszoros emelést is kaptunk.
Összegezve: ha PostgreSQL-t használsz FDW-vel, mindig ellenőrizze, hogy minden szűrő elküldésre került-e a távoli szerverre, és elégedett lesz... Legalábbis addig, amíg el nem éri a csatlakozásokat a különböző szerverekről származó táblák között. De ez egy másik cikk története.
Köszönöm a figyelmet! Szeretnék kérdéseket, megjegyzéseket és történeteket hallani a tapasztalatairól a megjegyzésekben.
Forrás: will.com