L'architettura a microservizi, come tutto nel mondo, ha i suoi pro e i suoi contro. Alcuni processi diventano più semplici, altri più complessi. E a favore della velocità dei cambiamenti e di una migliore scalabilità, bisogna fare dei sacrifici. Uno di questi è la complicazione dell'analisi. Se nel monolite tutta l'analisi operativa può essere ridotta a query SQL su una replica analitica, nell'architettura a più servizi ogni servizio ha il proprio database e sembra che una sola query non sia sufficiente (o forse lo è?). Per coloro che sono interessati a sapere come abbiamo risolto il problema dell'analisi operativa nella nostra azienda e come abbiamo imparato a convivere con questa soluzione — benvenuti.
Mi chiamo Pavel Sivash, in DomClick lavoro nel team che si occupa della gestione del data warehouse analitico. In un certo senso, la nostra attività può essere considerata ingegneria dei dati, ma in realtà, l'intera gamma di compiti è molto più ampia. Ci sono attività standard per l'ingegneria dei dati come ETL/ELT, supporto e adattamento degli strumenti per l'analisi dei dati e sviluppo dei nostri strumenti. In particolare, per la reportistica operativa abbiamo deciso di "fingere" di avere un monolite e di fornire agli analisti un'unica base in cui ci siano tutti i dati necessari.
In generale, abbiamo considerato diverse opzioni. Si poteva costruire un deposito completo — abbiamo anche provato, ma, a essere sincero, non siamo riusciti a sincronizzare le frequenti modifiche nella logica con il processo piuttosto lento di costruzione del deposito e di implementazione delle modifiche (se qualcuno ci è riuscito, scrivete nei commenti come). Avremmo potuto dire agli analisti: «Ragazzi, imparate Python e lavorate con le repliche analitiche», ma si sarebbe trattato di una richiesta aggiuntiva nel reclutamento, e sembrava utile evitarla se possibile. Abbiamo deciso di provare a utilizzare la tecnologia FDW (Foreign Data Wrapper): in sostanza, è un dblink standard che esiste nello standard SQL, ma con un'interfaccia molto più comoda. Su questa base, abbiamo creato una soluzione che è stata alla fine accettata, e su cui ci siamo fermati. I dettagli di questa soluzione sono il tema di un articolo separato, e forse anche di più di uno, poiché ci sono molte cose da raccontare: dalla sincronizzazione degli schemi delle basi all'amministrazione degli accessi e all'anonimizzazione dei dati personali. È importante notare che questa soluzione non è un sostituto delle reali basi analitiche e dei depositi, ma risolve solo un compito specifico.
A un alto livello, appare così:
C'è un database PostgreSQL, dove gli utenti possono memorizzare i propri dati di lavoro, e la cosa più importante è che a questo database sono collegate tramite FDW le repliche analitiche di tutti i servizi. Questo consente di scrivere una query su più basi, indipendentemente dal fatto che siano: PostgreSQL, MySQL, MongoDB o altro (file, API, e se non c'è un wrapper adatto, è possibile scrivere il proprio). Bene, tutto qui, fantastico! Possiamo andare?
Se tutto finisse così in fretta e facilmente, probabilmente non ci sarebbe nemmeno un articolo.
È importante comprendere chiaramente come PostgreSQL gestisce le query ai server remoti. Questo sembra logico, ma spesso non viene prestata attenzione: PostgreSQL divide la query in parti che vengono eseguite sui server remoti in modo indipendente, raccoglie questi dati e quindi esegue i calcoli finali, quindi la velocità di esecuzione della query dipenderà molto da come è scritta. Va anche notato: quando i dati provengono da un server remoto, non hanno più indici, non c'è nulla che possa aiutare il pianificatore, quindi possiamo solo aiutare e suggerire a lui stessi. E proprio su questo vorrei parlare più dettagliatamente.
Richiesta semplice e piano associato
Per dimostrare come PostgreSQL esegue una richiesta su una tabella di 6 milioni di righe su un server remoto server, diamo un'occhiata a un piano semplice.
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 msL'uso dell'istruzione VERBOSE permette di vedere la richiesta che sarà inviata al server remoto e i risultati che riceveremo per ulteriori elaborazioni (riga RemoteSQL).
Andiamo un po' oltre e aggiungiamo al nostro query alcuni filtri: uno per campo il campo, uno per la presenza timestamp in un intervallo e uno per 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 msÈ proprio qui che risiede il punto su cui è necessario prestare attenzione quando si scrivono le query. I filtri non sono stati trasferiti al server remoto, il che significa che PostgreSQL deve estrarre tutte e 6 milioni di righe per filtrare localmente (riga Filter) e quindi aggregare. La chiave del successo è scrivere la query in modo tale che i filtri vengano passati alla macchina remota, così otteniamo e aggregiamo solo le righe di interesse.
Quella è solo una boiata
Con i campi booleani è tutto semplice. Nel query originale, il problema si presentava a causa dell'operatore is. Se lo sostituiamo con =, otterremo il seguente risultato:
spiega analizza dettagliatamente
SELEZIONA count(1)
DA fdw_schema.tabella
DOVE is_active = True
E created_dt TRA CURRENT_DATE - INTERVAL '7 mesi'
E CURRENT_DATE - INTERVAL '6 mesi'
E meta->>'source' = 'test';
Aggregato (costo=508010.14..508010.15 righe=1 larghezza=8) (tempo reale=19064.314..19064.314 righe=1 cicli=1)
Output: count(1)
-> Scansione Esterna su fdw_schema."tabella" (costo=100.00..507988.44 righe=8679 larghezza=0) (tempo reale=33.035..18951.278 righe=1360025 cicli=1)
Output: "tabella".id, "tabella".is_active, "tabella".meta, "tabella".created_dt
Filtro: ((("tabella".meta ->> 'source'::text) = 'test'::text) E ("tabella".created_dt >= (('ora'::cstring)::data - '7 mesi'::intervallo)) E ("tabella".created_dt <= ((('ora'::cstring)::data)::timestamp con fuso orario - '6 mesi'::intervallo)))
Righe rimosse dal Filtro: 3567989
SQL Remoto: SELEZIONA created_dt, meta DA fdw_schema.tabella DOVE (is_active)
Tempo di pianificazione: 0.834 ms
Tempo di esecuzione: 19064.534 msCome puoi vedere, il filtro è andato sul server remoto, e il tempo di esecuzione è diminuito da 27 a 19 secondi.
Vale la pena notare che l'operatore is è diverso dall'operatore = nel senso che può lavorare con il valore Null. Ciò significa che is not True nel filtro lascerà valori False e Null, mentre != True lascerà solo valori False. Pertanto, quando si sostituisce l'operatore is not dovrebbero essere passate nel filtro due condizioni con l'operatore OR, per esempio, DOVE (col != True) O (col è null).
Abbiamo chiarito il booleano, proseguiamo. Forse rimettiamo il filtro sul valore booleano nella sua forma originale, per esaminare indipendentemente l'effetto di altre modifiche.
timestamptz? hz
In generale, è spesso necessario sperimentare su come scrivere correttamente una query che coinvolge server remoti, e poi cercare spiegazioni sul perché accade proprio così. Ci sono poche informazioni a riguardo disponibili su Internet. Così, nei nostri esperimenti, abbiamo scoperto che il filtro su una data fissa funziona perfettamente sul server remoto, mentre quando vogliamo impostare una data dinamicamente, come now() o CURRENT_DATE, non succede lo stesso. Nel nostro esempio, abbiamo aggiunto un filtro per cui la colonna created_at contenga dati esattamente per 1 mese fa (TRA CURRENT_DATE — INTERVAL ‘7 mesi’ E CURRENT_DATE — INTERVAL ‘6 mesi’). Cosa abbiamo fatto in questo caso?
spiegare analizzare verbose
SELECT count(1)
FROM fdw_schema.table
WHERE is_active is True
AND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 mesi')
AND created_dt >'source' = 'test';
Aggregate (costo=306875.17..306875.18 righe=1 larghezza=8) (tempo effettivo=4789.114..4789.115 righe=1 cicli=1)
Output: count(1)
InitPlan 1 (restituisce $0)
-> Result (costo=0.00..0.02 righe=1 larghezza=8) (tempo effettivo=0.007..0.008 righe=1 cicli=1)
Output: ((('now'::cstring)::date)::timestamp with time zone - '7 mons'::interval)
InitPlan 2 (restituisce $1)
-> Result (costo=0.00..0.02 righe=1 larghezza=8) (tempo effettivo=0.002..0.002 righe=1 cicli=1)
Output: ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)
-> Foreign Scan on fdw_schema."table" (costo=100.02..306874.86 righe=105 larghezza=0) (tempo effettivo=23.475..4681.419 righe=1360025 cicli=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))
Righello rimossi dal filtro: 76934
SQL remoto: SELECT is_active, meta FROM fdw_schema.table WHERE ((created_dt >= $1::timestamp with time zone)) AND ((created_dt < $2::timestamp with time zone))
Tempo di pianificazione: 0.703 ms
Tempo di esecuzione: 4789.379 msAbbiamo suggerito al pianificatore di calcolare in anticipo la data nella sottoquery e di trasmettere la variabile già pronta nel filtro. E questo suggerimento ci ha dato un ottimo risultato, la query è diventata quasi sei volte più veloce!
Ancora una volta, è importante prestare attenzione: il tipo di dati nella sottoquery deve essere lo stesso di quello del campo su cui filtriamo, altrimenti il pianificatore deciderà che, poiché i tipi sono diversi, è necessario prima estrarre tutti i dati e poi filtrare localmente.
Riportiamo il filtro sulla data al valore originale.
Freddy contro Jsonb
In effetti, i campi booleani e le date hanno già accelerato abbastanza la nostra query, tuttavia c'era ancora un altro tipo di dato. La battaglia con la filtrazione su di esso, onestamente, non è ancora finita, anche se qui ci sono stati progressi. Quindi, ecco come siamo riusciti a trasmettere il filtro per jsonb il campo al server remoto.
spiegare analizzare verbose
SELECT count(1)
FROM fdw_schema.table
WHERE is_active is True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 mesi'
AND CURRENT_DATE - INTERVAL '6 mesi'
AND meta @> '{"source":"test"}'::jsonb;
Aggregate (costo=245463.60..245463.61 righe=1 larghezza=8) (tempo effettivo=6727.589..6727.590 righe=1 cicli=1)
Output: count(1)
-> Foreign Scan on fdw_schema."table" (costo=1100.00..245459.90 righe=1478 larghezza=0) (tempo effettivo=16.213..6634.794 righe=1360025 cicli=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 '{"source": "test"}'::jsonb))
Tempo di pianificazione: 0.747 ms
Tempo di esecuzione: 6727.815 msInvece degli operatori di filtraggio è necessario utilizzare l'operatore di esistenza jsonb in un altro. 7 secondi invece dei 29 originali. Finora è l'unica opzione di successo per la trasmissione dei filtri jsonb su un server remoto, ma qui è importante tener conto di una limitazione: stiamo utilizzando la versione del database 9.6, tuttavia entro la fine di aprile pianifichiamo di completare gli ultimi test e passare alla versione 12. Una volta aggiornati, scriveremo come questo ha influito, poiché ci sono molte modifiche attese: json_path, un nuovo comportamento CTE, push down (che esiste dalla versione 10). Non vediamo l'ora di provare.
Finish him
Abbiamo verificato come ogni modifica influisce sulla velocità della query singolarmente. Adesso vediamo cosa succede quando tutti e tre i filtri sono scritti correttamente.
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 '{"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 '{"source": "test"}'::jsonb))
Planning time: 0.820 ms
Execution time: 2279.087 msSì, la query sembra più complessa, è un costo forzato, ma la velocità di esecuzione è di 2 secondi, che è più di dieci volte più veloce! E stiamo parlando di una semplice query su un insieme di dati relativamente piccolo. Su query reali abbiamo registrato un incremento di centinaia di volte.
In sintesi: se utilizzi PostgreSQL con FDW, controlla sempre che tutti i filtri vengano inviati al server remoto, e vivrai felice… Fino a quando non arriverai ai join tra tabelle diverse server. Ma questa è già una storia per un altro articolo.
Grazie per l'attenzione! Sarò felice di ricevere domande, commenti e storie sulle tue esperienze nei commenti.
Fonte: habr.com
