L'architettura a microservizi, come tutto in questo mondo, ha i suoi pro e i suoi contro. Alcuni processi diventano più semplici, altri più complicati. E, in nome della rapidità nelle modifiche e di una migliore scalabilità, è necessario fare delle rinunce. Una di queste è la complicazione dell'analisi. Se nel monolite tutta l'analisi operativa può essere ridotta a query SQL sulla replica analitica, nella architettura a microservizi ogni servizio ha il proprio database e sembra che non possa bastare una sola query (o forse sì?). Per coloro che sono interessati a scoprire come abbiamo risolto il problema dell'analisi operativa nella nostra azienda e come abbiamo imparato a convivere con questa soluzione — benvenuti.
Mi chiamo Pavel Sivas e lavoro in DomClick in un team che si occupa della gestione del data warehouse analitico. La nostra attività può essere considerata in parte ingegneria dei dati, ma, in realtà, l'ampiezza delle mansioni è molto più vasta. Ci sono compiti standard per l'ingegneria dei dati come ETL/ELT, supporto e adattamento di strumenti per l'analisi dei dati e sviluppo di strumenti personalizzati. In particolare, per i report operativi abbiamo deciso di "fingere" di avere un monolite e di fornire agli analisti un unico database, contenente tutti i dati necessari.
In generale, abbiamo considerato diverse opzioni. Avremmo potuto costruire un vero e proprio repository — abbiamo anche provato, ma, ad essere onesti, non siamo riusciti a integrare frequenti cambiamenti nella logica con un processo di costruzione e modifica del repository piuttosto lento (se qualcuno ci è riuscito, sentitevi liberi di scrivere nei commenti come). Avremmo potuto dire agli analisti: "Ragazzi, imparate Python e adattatevi alle repliche analitiche", ma questo sarebbe stato un ulteriore requisito per il reclutamento del personale, e sembrava opportuno evitarlo, se possibile. Abbiamo deciso di provare a utilizzare la tecnologia FDW (Foreign Data Wrapper): in sostanza, è un dblink standard, presente nello standard SQL, ma con un'interfaccia molto più comoda. Su questa base, abbiamo creato una soluzione che alla fine ha preso piede, e ci siamo fermati su di essa. I dettagli della soluzione sono materia di un articolo a parte, e forse più di uno, poiché ci sono molte cose da raccontare: dalla sincronizzazione degli schemi dei database alla gestione degli accessi e all'anonimizzazione dei dati personali. Inoltre, è necessario sottolineare che questa soluzione non è una sostituzione di veri e propri database analitici e repository; risolve solo un compito specifico.
A livello generale, appare così:
C'è un database PostgreSQL, dove gli utenti possono conservare i propri dati di lavoro, e la cosa più importante è che a questo database sono collegate tramite FDW repliche analitiche di tutti i servizi. Questo consente di scrivere query a più database, non importa se siano PostgreSQL, MySQL, MongoDB o altro (file, API; se non c'è un wrapper adatto, si può scriverne uno proprio). Bene, sembra tutto! Possiamo andare?
Se tutto finisse così in fretta e semplicemente, probabilmente non ci sarebbe nemmeno bisogno di questo articolo.
È importante comprendere chiaramente come PostgreSQL gestisce le richieste ai server remoti. Questo sembra logico, ma spesso non ci si fa caso: PostgreSQL divide la query in parti che vengono eseguite sui server remoti in modo indipendente, raccoglie questi dati e poi esegue i calcoli finali, quindi la velocità di esecuzione della query dipenderà molto da come è scritta. È importante sottolineare che quando i dati arrivano da un server remoto, non hanno più indici, non c'è niente che possa aiutare il pianificatore; quindi solo noi possiamo aiutarlo e guidarlo. Ed è esattamente di questo che vorrei parlare in dettaglio.
Richiesta semplice e piano associato
Per illustrare come Postgres esegue una query su una tabella di 6 milioni di righe su un server remoto server, esaminiamo 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 consente di visualizzare la query che sarà inviata al server remoto e i cui risultati riceveremo per ulteriori elaborazioni (riga RemoteSQL).
Andiamo un po' oltre e aggiungiamo alla nostra query alcuni filtri: uno per booleano campo, uno per corrispondenza timestamp in un intervallo e uno per jsonb.
spiega analizzare verbose
SELEZIONA count(1)
DA fdw_schema.table
DOVE is_active è True
E created_dt TRA CURRENT_DATE - INTERVAL '7 month'
E CURRENT_DATE - INTERVAL '6 month'
E meta- >> 'source' = 'test';
Aggregato (costo=577487.69..577487.70 righe=1 larghezza=8) (tempo reale=27473.818..25473.819 righe=1 cicli=1)
Output: count(1)
-> Scansione remota su fdw_schema."table" (costo=100.00..577469.21 righe=7390 larghezza=0) (tempo reale=31.369..25372.466 righe=1360025 cicli=1)
Output: "table".id, "table".is_active, "table".meta, "table".created_dt
Filtro: (("table".is_active È TRUE) E (("table".meta - >> 'source'::text) = 'test'::text) E ("table".created_dt >= (('ora'::cstring)::data - '7 mons'::interval)) E ("table".created_dt <= ((('ora'::cstring)::data)::timestamp with time zone - '6 mons'::interval)))
Righe rimosse dal filtro: 5046843
SQL remoto: SELEZIONA created_dt, is_active, meta DA fdw_schema.table
Tempo di pianificazione: 0.665 ms
Tempo di esecuzione: 27474.118 msProprio qui si trova il punto su cui bisogna prestare attenzione nella scrittura delle query. I filtri non sono stati inviati al server remoto, il che significa che per l'esecuzione Postgres estrae tutti i 6 milioni di righe per poi filtrare localmente (riga Filtro) e produrre l'aggregazione. La chiave del successo è scrivere la query in modo che i filtri vengano inviati alla macchina remota, consentendoci di ricevere e aggregare solo le righe necessarie.
È una vera booleanshit
Con i campi booleani — è tutto semplice. Nel query originale il problema è emerso a causa dell'operatore è. Se lo sostituiamo con =, otteniamo il seguente risultato:
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 msCome potete vedere, il filtro è stato trasferito su un server remoto, e il tempo di esecuzione è diminuito da 27 a 19 secondi.
È importante notare che l'operatore è è diverso dall'operatore = nella misura in cui può gestire il valore Null. Questo 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 è necessario passare due condizioni nel filtro con l'operatore OR, ad esempio, WHERE (col != True) OR (col is null).
Con il booleano ci siamo chiariti, procediamo oltre. Ma per ora, ripristiniamo il filtro per il valore booleano alla sua forma originale, per esaminare indipendentemente l'effetto delle altre modifiche.
timestamptz? hz
In effetti, spesso è necessario sperimentare su come scrivere correttamente una query che coinvolge server remoti, e solo dopo cercare spiegazioni su perché accada in un certo modo. Ci sono poche informazioni al riguardo disponibili su Internet. Nei nostri esperimenti abbiamo scoperto che il filtro per una data fissa funziona bene sui server remoti, mentre quando vogliamo impostare una data in modo dinamico, ad esempio, now() o CURRENT_DATE, non succede lo stesso. Nel nostro esempio, abbiamo aggiunto un filtro in modo che la colonna created_at contenesse dati esattamente per 1 mese nel passato (BETWEEN CURRENT_DATE — INTERVAL ‘7 month’ AND CURRENT_DATE — INTERVAL ‘6 month’). Cosa abbiamo fatto in questo caso?
spiega analizza dettagliatamente
SELECT count(1)
FROM fdw_schema.table
WHERE is_active is True
AND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 months')
AND created_dt >'source' = 'test';
Aggregato (costo=306875.17..306875.18 righe=1 larghezza=8) (tempo effettivo=4789.114..4789.115 righe=1 cicli=1)
Uscita: count(1)
InitPlan 1 (restituisce $0)
-→ Risultato (costo=0.00..0.02 righe=1 larghezza=8) (tempo effettivo=0.007..0.008 righe=1 cicli=1)
Uscita: ((('adesso'::cstring)::date)::timestamp con fuso orario - '7 mesi'::interval)
InitPlan 2 (restituisce $1)
-→ Risultato (costo=0.00..0.02 righe=1 larghezza=8) (tempo effettivo=0.002..0.002 righe=1 cicli=1)
Uscita: ((('adesso'::cstring)::date)::timestamp con fuso orario - '6 mesi'::interval)
-→ Scansione Esterna su fdw_schema."table" (costo=100.02..306874.86 righe=105 larghezza=0) (tempo effettivo=23.475..4681.419 righe=1360025 cicli=1)
Uscita: "table".id, "table".is_active, "table".meta, "table".created_dt
Filtro: (("table".is_active IS TRUE) AND (("table".meta ->> 'source'::text) = 'test'::text))
Righe rimosse dal filtro: 76934
SQL remoto: SELECT is_active, meta FROM fdw_schema.table WHERE ((created_dt >= $1::timestamp con fuso orario)) AND ((created_dt < $2::timestamp con fuso orario))
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 passare la variabile già pronta al filtro. E questo suggerimento ci ha dato un risultato eccezionale, la query è diventata quasi sei volte più veloce!
Ancora una volta, è importante prestare attenzione: il tipo di dato nella sottoquery deve essere lo stesso di quello del campo su cui stiamo filtrando, altrimenti il pianificatore deciderà che, poiché i tipi sono diversi, è necessario prima estrarre tutti i dati e poi filtrare localmente.
Ripristiniamo il filtro sulla data al valore originale.
Freddy vs. Jsonb
In effetti, i campi booleani e le date hanno già accelerato notevolmente la nostra query, ma rimaneva un altro tipo di dato. La battaglia per la filtrazione su di esso, a dire il vero, non è ancora finita, anche se ci sono stati dei progressi. Ecco quindi come siamo riusciti a passare il filtro su jsonb un campo al server remoto.
spiega analizza 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;
Aggregato (costo=245463.60..245463.61 righe=1 larghezza=8) (tempo reale=6727.589..6727.590 righe=1 cicli=1)
Uscita: count(1)
-> Scan estero su fdw_schema."table" (costo=1100.00..245459.90 righe=1478 larghezza=0) (tempo reale=16.213..6634.794 righe=1360025 cicli=1)
Uscita: "table".id, "table".is_active, "table".meta, "table".created_dt
Filtro: (("table".is_active IS TRUE) E ("table".created_dt >= (('ora'::cstring)::date - '7 mesi'::interval)) E ("table".created_dt <= ((('ora'::cstring)::date)::timestamp con fuso orario - '6 mesi'::interval)))
Righe rimosse dal filtro: 619961
SQL remoto: SELECT created_dt, is_active FROM fdw_schema.table WHERE ((meta @> '{"source": "test"}'::jsonb))
Tempo di pianificazione: 0.747 ms
Tempo di esecuzione: 6727.815 msÈ necessario utilizzare l'operatore di esistenza invece degli operatori di filtraggio jsonb in un altro. 7 secondi invece dei 29 iniziali. Questo è attualmente l'unico modo riuscito per trasferire i filtri. jsonb su un server remoto, ma qui c'è un'importante limitazione: stiamo utilizzando la versione 9.6 del database, però entro la fine di aprile prevediamo di completare gli ultimi test e migrare alla versione 12. Non appena ci aggiorneremo, scriveremo come questa modifica ha influito, visto che ci sono molte speranze riguardo ai numerosi cambiamenti: json_path, il nuovo comportamento dei CTE, il push down (già presente dalla versione 10). Non vediamo l'ora di provare.
Finish him
Abbiamo verificato come ogni modifica influisce sulla velocità della richiesta singolarmente. Adesso vediamo cosa accade quando tutti e tre i filtri vengono scritti correttamente.
spiega analizzare dettagliatamente
SELEZIONA count(1)
DA fdw_schema.tabella
DOVE is_active = True
E created_dt >= (SELEZIONA CURRENT_DATE::timestamptz - INTERVAL '7 mesi')
E created_dt < (SELEZIONA CURRENT_DATE::timestamptz - INTERVAL '6 mesi')
E meta @> '{"source":"test"}'::jsonb;
Aggregato (costo=322041.51..322041.52 righe=1 larghezza=8) (tempo effettivo=2278.867..2278.867 righe=1 cicli=1)
Usci: count(1)
InitPlan 1 (restituisce $0)
-> Risultato (costo=0.00..0.02 righe=1 larghezza=8) (tempo effettivo=0.010..0.010 righe=1 cicli=1)
Usci: ((('ora'::cstring)::data)::timestamp con fuso orario - '7 mesi'::intervallo)
InitPlan 2 (restituisce $1)
-> Risultato (costo=0.00..0.02 righe=1 larghezza=8) (tempo effettivo=0.003..0.003 righe=1 cicli=1)
Usci: ((('ora'::cstring)::data)::timestamp con fuso orario - '6 mesi'::intervallo)
-> Scansione Remota su fdw_schema."tabella" (costo=100.02..322041.41 righe=25 larghezza=0) (tempo effettivo=8.597..2153.809 righe=1360025 cicli=1)
Usci: "tabella".id, "tabella".is_active, "tabella".meta, "tabella".created_dt
SQL Remoto: SELEZIONA NULL DA fdw_schema.tabella DOVE (is_active) E ((created_dt >= $1::timestamp con fuso orario)) E ((created_dt < $2::timestamp con fuso orario)) E ((meta @> '{"source": "test"}'::jsonb))
Tempo di pianificazione: 0.820 ms
Tempo di esecuzione: 2279.087 msSì, la query sembra più complessa, è un costo inevitabile, 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 ottenuto un incremento fino a diverse centinaia di volte.
In sintesi: se utilizzi PostgreSQL con FDW, controlla sempre che tutti i filtri vengano inviati al server remoto, e sarai fortunato... Almeno fino a quando non arriverai alle join tra tabelle di diversi server. Ma questa è già una storia per un altro articolo.
Grazie per l'attenzione! Sarò felice di ricevere domande, commenti e condividerò storie sulla tua esperienza nei commenti.
Fonte: habr.com
