Mikrodiensargitektuur, soos alles in hierdie wêreld, het sy voor- en nadele. Sommige prosesse word makliker daarmee, ander moeiliker. En ter wille van spoed van verandering en beter skaalbaarheid, moet jy opofferings maak. Een daarvan is die toenemende kompleksiteit van analise. As in 'n monoliet alle operasionele analise na SQL-navrae na 'n analitiese replika gereduseer kan word, dan het elke diens in 'n multidiensargitektuur sy eie databasis en dit blyk dat een navraag nie gedoen kan word nie (of dalk kan dit?). Vir diegene wat belangstel in hoe ons die probleem van operasionele analise in ons maatskappy opgelos het en hoe ons geleer het om met hierdie oplossing saam te leef - welkom.
My naam is Pavel Sivash, by DomClick werk ek in 'n span wat verantwoordelik is vir die instandhouding van die analitiese datapakhuis. Konvensioneel kan ons aktiwiteite as data-ingenieurswese geklassifiseer word, maar in werklikheid is die reeks take baie wyer. Daar is ETL/ELT-standaarde vir data-ingenieurswese, ondersteuning en aanpassing van gereedskap vir data-analise en ontwikkeling van jou eie gereedskap. Ons het veral vir operasionele verslagdoening besluit om te "voorgee" dat ons 'n monoliet het en aan ontleders een databasis te gee wat al die data sal bevat wat hulle nodig het.
Oor die algemeen het ons verskillende opsies oorweeg. Dit was moontlik om 'n volwaardige bewaarplek te bou - ons het selfs probeer, maar om eerlik te wees, was ons nie in staat om redelik gereelde veranderinge in logika te kombineer met die taamlik stadige proses om 'n bewaarplek te bou en veranderinge daaraan te maak nie (as iemand daarin geslaag het) , skryf in die kommentaar hoe). Dit was moontlik om vir die ontleders te sê: "Ouens, leer luislang en gaan na analitiese replikas," maar dit is 'n bykomende vereiste vir werwing, en dit het gelyk of dit vermy moet word indien moontlik. Ons het besluit om te probeer om FDW (Foreign Data Wrapper) tegnologie te gebruik: in wese is dit 'n standaard dblink, wat in die SQL-standaard is, maar met sy eie baie geriefliker koppelvlak. Op grond daarvan het ons 'n oplossing gemaak, wat uiteindelik vasgevang het, en ons het daarop besluit. Die besonderhede daarvan is die onderwerp van 'n aparte artikel, en miskien meer as een, aangesien ek baie oor wil praat: van sinchronisering van databasisskemas tot toegangsbeheer en depersonalisering van persoonlike data. Dit is ook nodig om 'n voorbehoud te maak dat hierdie oplossing nie 'n plaasvervanger vir werklike analitiese databasisse en bewaarplekke is nie, dit los slegs 'n spesifieke probleem op.
Op die boonste vlak lyk dit so:
Daar is 'n PostgreSQL-databasis waar gebruikers hul werkdata kan stoor, en die belangrikste is dat analitiese replikas van alle dienste via FDW aan hierdie databasis gekoppel is. Dit maak dit moontlik om 'n navraag na verskeie databasisse te skryf, en dit maak nie saak wat dit is nie: PostgreSQL, MySQL, MongoDB of iets anders (lêer, API, as daar skielik nie 'n geskikte omhulsel is nie, kan jy jou eie skryf). Wel, alles lyk wonderlik! Is ons besig om op te breek?
As alles so vinnig en eenvoudig geëindig het, sou daar waarskynlik nie 'n artikel gewees het nie.
Dit is belangrik om duidelik te wees oor hoe Postgres versoeke na afgeleë bedieners verwerk. Dit lyk logies, maar mense steur hulle dikwels nie daaraan nie: Postgres verdeel die versoek in dele wat onafhanklik op afgeleë bedieners uitgevoer word, versamel hierdie data en voer self die finale berekeninge uit, so die spoed van navraaguitvoering sal grootliks afhang van hoe dit geskryf is. Daar moet ook op gelet word: wanneer die data van 'n afgeleë bediener af kom, het dit nie meer indekse nie, daar is niks wat die skeduleerder sal help nie, daarom kan net ons self hom help en raad gee. En dit is presies waaroor ek in meer besonderhede wil praat.
'n Eenvoudige navraag en 'n plan daarmee
Kom ons kyk na 'n eenvoudige plan om te wys hoe Postgres 'n 6 miljoen ry-tabel op 'n afgeleë bediener navrae doen.
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 msDeur die VERBOSE-stelling te gebruik, kan ons die navraag sien wat na die afgeleë bediener gestuur sal word en die resultate waarvan ons sal ontvang vir verdere verwerking (RemoteSQL-lyn).
Kom ons gaan 'n bietjie verder en voeg verskeie filters by ons versoek: een vir boolean veld, een volgens voorkoms tydstempel in die interval en een vir 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 msDit is waar die punt lê waarna u moet let wanneer u navrae skryf. Die filters is nie na die afgeleë bediener oorgedra nie, wat beteken dat om dit uit te voer, Postgres al 6 miljoen rye uittrek om dan plaaslik te filter (Filterry) en samevoeging uit te voer. Die sleutel tot sukses is om 'n navraag te skryf sodat die filters na die afgeleë masjien oorgedra word, en ons ontvang en versamel slegs die nodige rye.
Dit is 'n bietjie boelie
Met Boolese velde is alles eenvoudig. In die oorspronklike versoek was die probleem te wyte aan die operateur is. As jy dit vervang met =, dan kry ons die volgende resultaat:
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 msSoos u kan sien, het die filter na 'n afgeleë bediener gevlieg, en die uitvoeringstyd is van 27 tot 19 sekondes verminder.
Dit is opmerklik dat die operateur is verskil van operateur = omdat dit met die Nul-waarde kan werk. Dit beteken dat is nie waar nie sal die waardes False en Null in die filter laat, terwyl != Waar sal slegs Vals waardes laat. Daarom, wanneer die operateur vervang word is nie twee voorwaardes met die OF-operateur moet na die filter deurgegee word, byvoorbeeld, WAAR (kol != Waar) OF (kol is nul).
Ons het boolean uitgesorteer, kom ons gaan aan. Kom ons stel die Boole-filter nou terug na sy oorspronklike vorm om die effek van ander veranderinge onafhanklik te oorweeg.
tydstempel? hz
Oor die algemeen moet jy dikwels eksperimenteer met hoe om 'n versoek korrek te skryf wat afgeleë bedieners behels, en dan eers 'n verduideliking soek van hoekom dit gebeur. Baie min inligting hieroor kan op die internet gevind word. So, in eksperimente het ons gevind dat 'n vaste datumfilter met 'n knal na die afgeleë bediener vlieg, maar wanneer ons die datum dinamies wil stel, byvoorbeeld now() of CURRENT_DATE, gebeur dit nie. In ons voorbeeld het ons 'n filter bygevoeg sodat die geskep_at-kolom data vir presies 1 maand in die verlede bevat het (TUSSEN CURRENT_DATE - INTERVAL '7 maand' EN CURRENT_DATE - INTERVAL '6 maand'). Wat het ons in hierdie geval gedoen?
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 msOns het die beplanner aangesê om die datum in die subnavraag vooraf te bereken en die klaargemaakte veranderlike na die filter deur te gee. En hierdie wenk het ons 'n uitstekende resultaat gegee, die versoek het amper 6 keer vinniger geword!
Weereens, dit is belangrik om hier versigtig te wees: die datatipe in die subnavraag moet dieselfde wees as dié van die veld waarop ons filtreer, anders sal die beplanner besluit dat aangesien die tipes anders is, dit nodig is om eers alle die data en filtreer dit plaaslik.
Kom ons stel die datumfilter terug na sy oorspronklike waarde.
Freddy vs. Jsonb
Oor die algemeen het Boole-velde en datums ons navraag reeds voldoende bespoedig, maar daar was nog een datatipe oor. Die stryd om daardeur te filtreer, om eerlik te wees, is nog nie verby nie, hoewel daar ook sukses hier is. So, dit is hoe ons daarin geslaag het om die filter verby te steek jsonb veld na die afgeleë bediener.
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 msIn plaas daarvan om operateurs te filter, moet jy die teenwoordigheid van een operateur gebruik jsonb in 'n ander. 7 sekondes in plaas van die oorspronklike 29. Tot dusver is dit die enigste suksesvolle opsie vir die oordrag van filters via jsonb na 'n afgeleë bediener, maar hier is dit belangrik om een beperking in ag te neem: ons gebruik weergawe 9.6 van die databasis, maar teen die einde van April beplan ons om die laaste toetse te voltooi en na weergawe 12 te skuif. Sodra ons opgedateer het, sal ons skryf oor hoe dit geraak het, want daar is nogal baie veranderinge waarvoor daar baie hoop is: json_path, nuwe CTE-gedrag, druk af (bestaan sedert weergawe 10). Ek wil dit regtig binnekort probeer.
Maak hom klaar
Ons het getoets hoe elke verandering die versoekspoed individueel beïnvloed het. Kom ons kyk nou wat gebeur as al drie filters korrek geskryf is.
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 msJa, die versoek lyk meer ingewikkeld, dit is 'n gedwonge fooi, maar die uitvoeringspoed is 2 sekondes, wat meer as 10 keer vinniger is! En ons praat van 'n eenvoudige navraag teen 'n relatief klein datastel. Op werklike versoeke het ons 'n verhoging van tot 'n paar honderd keer ontvang.
Om op te som: as jy PostgreSQL met FDW gebruik, maak altyd seker dat alle filters na die afgeleë bediener gestuur is, en jy sal gelukkig wees... Ten minste totdat jy by aansluitings tussen tabelle van verskillende bedieners kom. Maar dit is 'n storie vir 'n ander artikel.
Dankie vir jou aandag! Ek sal graag vrae, opmerkings en stories oor jou ervarings in die kommentaar wil hoor.
Bron: will.com