Microservice-architectuur heeft, zoals alles in deze wereld, zijn voor- en nadelen. Sommige processen worden er gemakkelijker mee, andere moeilijker. En omwille van de snelheid van verandering en betere schaalbaarheid moet je offers brengen. Eén daarvan is de toenemende complexiteit van analytics. Als in een monoliet alle operationele analyses kunnen worden teruggebracht tot SQL-query's tot een analytische replica, dan heeft in een multiservice-architectuur elke service zijn eigen database en lijkt het erop dat één query niet kan worden uitgevoerd (of misschien wel?). Voor degenen die geïnteresseerd zijn in hoe we het probleem van operationele analytics in ons bedrijf hebben opgelost en hoe we met deze oplossing hebben leren leven: welkom.
Mijn naam is Pavel Sivash, bij DomClick werk ik in een team dat verantwoordelijk is voor het onderhouden van het analytische datawarehouse. Conventioneel vallen onze activiteiten onder de noemer data engineering, maar in werkelijkheid is het takenpakket veel breder. Er zijn ETL/ELT-standaarden voor data-engineering, ondersteuning en aanpassing van tools voor data-analyse en ontwikkeling van uw eigen tools. Met name voor de operationele rapportage hebben we besloten te βdoen alsofβ we een monoliet hebben en analisten één database te geven die alle gegevens bevat die ze nodig hebben.
Over het algemeen hebben we verschillende opties overwogen. Het was mogelijk om een ββvolwaardige repository te bouwen - we hebben het zelfs geprobeerd, maar om eerlijk te zijn waren we niet in staat om vrij frequente veranderingen in de logica te combineren met het tamelijk langzame proces van het bouwen van een repository en het aanbrengen van wijzigingen erin (als het iemand lukte , schrijf in de reacties hoe). Het was mogelijk om tegen de analisten te zeggen: "Jongens, leer Python en ga naar analytische replica's", maar dit is een extra vereiste voor rekrutering, en het leek erop dat dit indien mogelijk vermeden moest worden. We besloten te proberen de FDW-technologie (Foreign Data Wrapper) te gebruiken: in wezen is dit een standaard dblink, die zich in de SQL-standaard bevindt, maar met een eigen, veel handiger interface. Op basis daarvan hebben we een oplossing bedacht, die uiteindelijk aansloeg, en daar hebben we ons op gevestigd. De details ervan zijn het onderwerp van een apart artikel, en misschien meer dan één, omdat ik over veel wil praten: van het synchroniseren van databaseschema's tot toegangscontrole en depersonalisatie van persoonlijke gegevens. We moeten ook voorbehoud maken dat deze oplossing geen vervanging is voor echte analytische databases en opslagplaatsen; het lost slechts een specifiek probleem op.
Op het hoogste niveau ziet het er als volgt uit:
Er is een PostgreSQL-database waarin gebruikers hun werkgegevens kunnen opslaan, en het allerbelangrijkste: analytische replica's van alle services zijn via FDW met deze database verbonden. Dit maakt het mogelijk om een ββquery naar verschillende databases te schrijven, en het maakt niet uit wat het is: PostgreSQL, MySQL, MongoDB of iets anders (bestand, API, als er plotseling geen geschikte wrapper is, kun je je eigen wrapper schrijven). Nou, alles lijkt geweldig! Gaan we uit elkaar?
Als alles zo snel en eenvoudig zou eindigen, zou er waarschijnlijk geen artikel zijn.
Het is belangrijk om duidelijk te zijn over de manier waarop Postgres verzoeken aan externe servers verwerkt. Dit lijkt logisch, maar vaak besteden mensen er geen aandacht aan: Postgres verdeelt het verzoek in delen die onafhankelijk op externe servers worden uitgevoerd, verzamelt deze gegevens en voert de uiteindelijke berekeningen zelf uit, dus de snelheid van de uitvoering van de query zal sterk afhangen van hoe het geschreven is. Er moet ook worden opgemerkt: wanneer de gegevens afkomstig zijn van een externe server, hebben deze geen indexen meer, er is niets dat de planner kan helpen, daarom kunnen alleen wijzelf hem helpen en adviseren. En dit is precies waar ik het in meer detail over wil hebben.
Een eenvoudige vraag en een plan erbij
Laten we een eenvoudig plan bekijken om te laten zien hoe Postgres een tabel met zes miljoen rijen op een externe server opvraagt.
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 msDoor de VERBOSE-instructie te gebruiken, kunnen we de query zien die naar de externe server wordt verzonden en waarvan we de resultaten zullen ontvangen voor verdere verwerking (RemoteSQL-regel).
Laten we nog een stap verder gaan en verschillende filters aan ons verzoek toevoegen: één voor boolean veld, één per voorkomen tijdstempel in het interval en één door 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 u op moet letten bij het schrijven van query's. De filters zijn niet naar de externe server overgebracht, wat betekent dat Postgres voor de uitvoering alle 6 miljoen rijen eruit haalt om vervolgens lokaal te filteren (Filterrij) en aggregatie uit te voeren. De sleutel tot succes is het schrijven van een query, zodat de filters naar de externe machine worden overgebracht en we alleen de noodzakelijke rijen ontvangen en aggregeren.
Dat is een booleanshit
Met Booleaanse velden is alles eenvoudig. In het oorspronkelijke verzoek was het probleem te wijten aan de operator is. Als je het vervangt door =, dan krijgen we het 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 msZoals u kunt zien, vloog het filter naar een externe server en werd de uitvoeringstijd teruggebracht van 27 naar 19 seconden.
Het is vermeldenswaard dat de exploitant is verschillend van exploitant = omdat het kan werken met de Null-waarde. Het betekent dat is niet waar laat de waarden False en Null in het filter staan, terwijl != Waar laat alleen valse waarden over. Daarom bij het vervangen van de aandrijving is niet twee voorwaarden met de OR-operator moeten aan het filter worden doorgegeven, bijvoorbeeld: WAAR (col != True) OF (col is nul).
We hebben met Boolean te maken gehad, laten we verder gaan. Laten we voorlopig het Booleaanse filter terugbrengen naar zijn oorspronkelijke vorm, zodat we onafhankelijk het effect van andere wijzigingen kunnen overwegen.
tijdstempel? Hz
Over het algemeen moet je vaak experimenteren met het correct schrijven van een verzoek waarbij externe servers betrokken zijn, en pas dan zoeken naar een verklaring waarom dit gebeurt. Op internet is hierover zeer weinig informatie te vinden. In experimenten hebben we dus ontdekt dat een filter met een vaste datum met een knal naar de externe server vliegt, maar als we de datum dynamisch willen instellen, bijvoorbeeld nu() of CURRENT_DATE, gebeurt dit niet. In ons voorbeeld hebben we een filter toegevoegd zodat de kolom Created_at gegevens bevat van precies één maand in het verleden (BETWEEN CURRENT_DATE - INTERVAL '1 month' AND CURRENT_DATE - INTERVAL '7 month'). Wat hebben wij in dit geval gedaan?
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 msWe vertelden de planner dat hij de datum in de subquery vooraf moest berekenen en de kant-en-klare variabele aan het filter moest doorgeven. En deze hint leverde ons een uitstekend resultaat op, het verzoek werd bijna 6 keer sneller!
Ook hier is het belangrijk om voorzichtig te zijn: het gegevenstype in de subquery moet hetzelfde zijn als dat van het veld waarop we filteren, anders zal de planner besluiten dat, aangezien de typen verschillend zijn, het noodzakelijk is om eerst alle de gegevens en filter deze lokaal.
Laten we het datumfilter terugzetten naar de oorspronkelijke waarde.
Freddy vs. Jsonb
Over het algemeen hebben Booleaanse velden en datums onze zoekopdracht al voldoende versneld, maar er was nog één gegevenstype over. De strijd met het erdoor filteren is eerlijk gezegd nog steeds niet voorbij, al is er ook hier succes. Dus zo zijn we erin geslaagd het filter te passeren jsonb veld naar de externe server.
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 plaats van operators te filteren, moet u de aanwezigheid van één operator gebruiken jsonb in een andere. 7 seconden in plaats van de oorspronkelijke 29. Tot nu toe is dit de enige succesvolle optie om filters via te verzenden jsonb naar een externe server, maar hier is het belangrijk om rekening te houden met één beperking: we gebruiken versie 9.6 van de database, maar tegen eind april zijn we van plan de laatste tests af te ronden en over te gaan naar versie 12. Zodra we de update hebben uitgevoerd, zullen we schrijven over de gevolgen hiervan, omdat er nogal wat veranderingen zijn waar veel hoop op bestaat: json_path, nieuw CTE-gedrag, push down (bestaand sinds versie 10). Ik wil het heel graag binnenkort proberen.
Maak hem af
We hebben getest hoe elke wijziging de verzoeksnelheid afzonderlijk beΓ―nvloedde. Laten we nu eens kijken wat er gebeurt als alle drie de filters correct zijn geschreven.
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, het verzoek ziet er ingewikkelder uit, dit is een gedwongen vergoeding, maar de uitvoeringssnelheid is 2 seconden, wat meer dan 10 keer sneller is! En we hebben het over een eenvoudige zoekopdracht op een relatief kleine dataset. Op echte verzoeken ontvingen we een verhoging tot enkele honderden keren.
Samenvattend: als u PostgreSQL met FDW gebruikt, controleer dan altijd of alle filters naar de externe server zijn verzonden, en u zult tevreden zijn... Tenminste totdat u joins krijgt tussen tabellen van verschillende servers. Maar dat is een verhaal voor een ander artikel.
Bedankt voor uw aandacht! Ik hoor graag vragen, opmerkingen en verhalen over uw ervaringen in de opmerkingen.
Bron: www.habr.com