Vi invito a prendere visione della trascrizione della relazione di inizio 2016 di Vladimir Sitnikov "PostgreSQL e JDBC: spremiamo ogni goccia"
Buongiorno! Mi chiamo Vladimir Sitnikov. Lavoro da 10 anni in NetCracker. E principalmente mi occupo di prestazioni. Tutto ciò che riguarda Java, tutto ciò che riguarda SQL – è quello che amo.
E oggi parlerò di ciò con cui ci siamo trovati in azienda quando abbiamo iniziato a utilizzare PostgreSQL come server di database. E lavoriamo principalmente con Java. Ma ciò di cui parlerò oggi non riguarda solo Java. Come la pratica ha dimostrato, si verifica anche in altri linguaggi.
Parleremo di:
- estrazione dei dati.
- Salvataggio dei dati.
- E anche delle prestazioni.
- E delle trappole per principianti, che si nascondono lì.
Iniziamo con una domanda semplice. Selezioniamo una riga da una tabella in base alla chiave primaria.
Il database si trova sullo stesso host. E tutto ciò richiede 20 millisecondi.
Questi 20 millisecondi sono molti. Se ne avete 100 di richieste, state sprecando tempo in secondi per far passare queste richieste, cioè state sprecando tempo.
Non ci piace farlo e vediamo cosa ci offre il database. Il database ci propone due opzioni per l'esecuzione delle richieste.
La prima opzione è una semplice richiesta. Cosa ha di buono? Che la prendiamo e la inviamo, e nient'altro.
Il database ha anche una richiesta avanzata, che è più astuta, ma più funzionale. È possibile inviare separatamente la richiesta per il parsing, l'esecuzione, l'associazione delle variabili, ecc.
La super richiesta estesa – è qualcosa che non tratteremo nella relazione attuale. Potremmo voler fare qualcosa dal database e c'è una lista di desideri che è stata formata in qualche modo, cioè è ciò che vogliamo, ma non è possibile al momento e nel prossimo anno. Pertanto, è stata semplicemente scritta e andremo a parlare con le persone principali.
Ciò che possiamo fare è una richiesta semplice e una richiesta estesa.
Qual è la peculiarità di ciascun approccio?
La richiesta semplice è bene usarla per esecuzioni una tantum. Una volta eseguita, basta dimenticarla. E il problema è che non supporta il formato binario dei dati, cioè non è adatta per alcuni sistemi ad alte prestazioni.
La query estesa consente di risparmiare tempo durante il parsing. È ciò che abbiamo fatto e iniziato a utilizzare. Ci ha aiutato enormemente. Non c'è solo risparmio sul parsing. C'è risparmio anche nella trasmissione dei dati. Trasmettere i dati in formato binario è molto più efficiente.
Passiamo alla pratica. Ecco come appare un'applicazione tipica. Può essere Java e così via.
Abbiamo creato un statement. Abbiamo eseguito il comando. Abbiamo creato un close. Qual è qui l'errore? Qual è il problema? Non ci sono problemi. Così è scritto in tutti i libri. È così che si deve scrivere. Se vuoi massimizzare le prestazioni, scrivi così.
Ma la pratica ha dimostrato che questo non funziona. Perché? Perché abbiamo il metodo "close". E quando facciamo così, dal punto di vista del database, sembra che sia come il lavoro di un fumatore con il database. Abbiamo detto "PARSE EXECUTE DEALLOCATE".
Perché creare e scaricare statement inutilmente? Non servono a nessuno. Ma di solito, in PreparedStatement, accade così, quando li chiudiamo, chiudono tutto nel database. Non è quello che vogliamo.
Vogliamo, come persone sane, lavorare con il database. Una volta abbiamo preparato il nostro statement e poi lo eseguiamo molte volte. In realtà, molte volte significa una volta per tutta la vita dell'applicazione, abbiamo fatto il parsing. E utilizziamo lo stesso statement id in diversi REST. Questa è la nostra meta.
Come possiamo raggiungere questo obiettivo?
Molto semplice: non dobbiamo chiudere gli statement. Scriviamo così: "prepare" "execute".
Se lanciamo qualcosa di simile, è chiaro che da qualche parte abbiamo un overflow. Se non è chiaro, possiamo misurare. Prendiamo e scriviamo un benchmark, in cui usiamo questo metodo semplice. Creiamo uno statement. Lo eseguiamo su una certa versione del driver e vediamo che va giù piuttosto velocemente, con perdita di tutta la memoria di cui disponevamo.
È chiaro che questi errori sono facili da correggere. Non parlerò di loro. Ma dirò che nella nuova versione funziona molto più velocemente. Il metodo è poco sensato, ma tuttavia.
Come lavorare correttamente? Cosa dobbiamo fare per questo?
Nella realtà, le applicazioni chiudono sempre gli statement. In tutti i libri si scrive che devono essere chiusi, altrimenti la memoria perde.
E PostgreSQL non sa come memorizzare nella cache le query. Ogni sessione deve creare questa cache per se stessa.
E non vogliamo nemmeno spendere tempo per il parsing.
E come al solito abbiamo due opzioni.
La prima opzione è che diciamo di avvolgere tutto in PgSQL. Lì c'è una cache. Essa memorizza tutto. Risulterà fantastico. Abbiamo dato un'occhiata a questo. Abbiamo 100500 richieste. Non funziona. Non siamo d'accordo a trasformare le richieste manualmente in procedure. No-no.
Abbiamo una seconda opzione: prenderci e svilupparlo noi stessi. Apriamo il codice sorgente, iniziamo a scrivere. Scriviamo e scriviamo. Si è scoperto che non è così difficile farlo.
Questo è emerso nell'agosto del 2015. Ora c'è già una versione più moderna. E tutto è fantastico. Funziona così bene che non cambiamo nulla nell'applicazione. E abbiamo persino smesso di pensare a PgSQL, cioè ci è bastato per ridurre praticamente a zero tutte le spese generali.
Di conseguenza, le dichiarazioni preparate dal server si attivano al quinto utilizzo per non sprecare memoria nel database per ogni richiesta una tantum.
Si può chiedere: dove sono i numeri? Cosa ottenete? E qui non darò numeri, perché ogni richiesta ha i propri.
Le nostre richieste erano tali che su richieste OLTP impiegavamo circa 20 millisecondi per il parsing. Ci volevano 0,5 millisecondi per l'esecuzione e 20 millisecondi per il parsing. La richiesta è di 10 KiB di testo, 170 righe di piano. Questa è una richiesta OLTP. Richiede 1, 5, 10 righe, a volte di più.
Ma non volevamo affatto spendere 20 millisecondi. Siamo riusciti a ridurre a 0. Tutto è fantastico.
Cosa potete trarre da questo? Se avete Java, prendete la versione moderna del driver e godetevela.
Se avete un'altra lingua, pensate: potrebbe servirvi anche a voi? Perché dal punto di vista del linguaggio finale, ad esempio, se PL 8 o avete LibPQ, non è chiaro che state sprecando tempo non per l'esecuzione, ma per il parsing e questo vale la pena verificare. Come? È tutto gratuito.
A parte che ci sono errori e alcune peculiarità. E di questo parleremo ora. Gran parte sarà sulla archeologia industriale, su ciò che abbiamo trovato, su cosa ci siamo imbattuti.
Se la richiesta viene generata dinamicamente. Questo accade. Qualcuno concatena le stringhe e ottiene una richiesta SQL.
Perché è negativa? È negativa perché ogni volta finiamo con stringhe diverse.
E anche questa stringa diversa deve ricalcolare il hashCode. È davvero un compito da CPU – trovare un lungo testo di query in un hash esistente non è così semplice. Quindi, la conclusione è semplice: non generare query. Conservale in una singola variabile. E sii soddisfatto.
Un altro problema. I tipi di dati sono importanti. Ci sono ORM che dicono che non importa quale sia NULL, va bene qualunque cosa. Se è Int, diciamo setInt. E se è NULL, deve sempre essere VARCHAR. E che differenza fa alla fine quale NULL? Il database capirà tutto da solo. E questo approccio non funziona.
In pratica, al database non importa affatto. Se la prima volta hai detto che è un numero e la seconda volta hai detto che è VARCHAR, non puoi riutilizzare le dichiarazioni preparate dal server. E in questo caso devi ricreare la nostra dichiarazione.
Se esegui la stessa query, assicurati che i tipi di dati nella colonna non siano confusi. Devi prestare attenzione a NULL. Questo è un errore comune che abbiamo commesso dopo aver iniziato a utilizzare i PreparedStatements.
Va bene, l'abbiamo attivata. Abbiamo preso, forse, un driver. E le prestazioni sono diminuite. Tutto è andato male.
Com'è possibile? È un bug o una funzione? Purtroppo, non siamo riusciti a capire se fosse un bug o una funzione. Ma c'è uno scenario di riproduzione piuttosto semplice per questo problema. Ci ha colto di sorpresa. Si tratta di una selezione da una sola tabella. Naturalmente, avevamo più di queste query. In genere includevano due o tre tabelle, ma c'è questo scenario di riproduzione. Prendi il tuo database di qualsiasi versione e riproducilo.
Il senso è che abbiamo due colonne, ognuna delle quali è indicizzata. In una colonna ci sono un milione di righe con valore NULL. E nell'altra colonna ci sono solo 20 righe. Quando eseguiamo senza variabili collegate, tutto funziona bene.
Se iniziamo a eseguire con variabili collegate, cioè utilizziamo il segno "?" o "$1" per la nostra query, cosa otteniamo alla fine?
La prima esecuzione – come di consueto. La seconda – un po' più veloce. Qualcosa è stato memorizzato nella cache. La terza, quarta, quinta. Poi, bam – e in questo modo. E la cosa peggiore è che succede alla sesta esecuzione. Chi sapeva che erano necessarie sei esecuzioni per capire qual è realmente il piano di esecuzione?
Chi è il colpevole? Cosa è successo? Il database contiene un'ottimizzazione. E sembra essere ottimizzato per il caso generico. Di conseguenza, a un certo punto, si passa a un piano generico, che purtroppo potrebbe rivelarsi diverso. Potrebbe essere lo stesso, oppure diverso. E c'è un certo valore soglia che porta a tale comportamento.
Cosa si può fare al riguardo? Qui, ovviamente, è più difficile fare previsioni. C'è una soluzione semplice che utilizziamo. È +0, OFFSET 0. Sicuramente conoscete queste soluzioni. Prendiamo semplicemente e aggiungiamo «+0» alla query ed è tutto a posto. Mostrerò più tardi.
E c'è un altro modo: guardare i piani più attentamente. Lo sviluppatore non deve solo scrivere la query, ma anche dire «explain analyze» sei volte. Se lo dice cinque volte, non funziona.
E c'è anche una terza opzione: scrivere una lettera a pgsql-hackers. L'ho fatto, ma finora non è chiaro se sia un bug o una funzionalità.
Mentre pensiamo se sia un bug o una funzionalità, ripariamo. Prendiamo la nostra query e aggiungiamo «+0». Va tutto bene. Due simboli e non è nemmeno necessario pensare a come stanno le cose. Molto semplice. Abbiamo semplicemente vietato al database di utilizzare l'indice su questa colonna. Non abbiamo un indice sulla colonna «+0» e quindi il database non utilizza l'indice, tutto a posto.
Questa è la regola dei 6 explain. Nelle versioni attuali, bisogna farlo sei volte, se avete variabili collegate. Se non avete variabili collegate, procediamo così. E alla fine proprio questa query fallisce. Non è difficile.
Sembra che non ci sia fine? Qui un bug, là un bug. In realtà, c'è bug ovunque.
Diamo un'altra occhiata. Ad esempio, abbiamo due schemi. Schema A con la tabella Ы e schema B con la tabella Ы. La query deve selezionare i dati dalla tabella. Cosa succederà? Avremo un errore. Tutto ciò di cui abbiamo parlato finora ci sarà. La regola è: bug ovunque, avremo tutto quanto precedentemente elencato.
Ora la domanda: «Perché?». Sembra che ci sia documentazione che, se abbiamo uno schema, c'è una variabile «search_path» che indica dove cercare la tabella. Sembra che la variabile esista.
Qual è il problema? Il problema è che le dichiarazioni prepare del server non sospettano che qualcuno possa cambiare il search_path. Questo valore rimane come una costante per il database. E alcune parti potrebbero non cogliere i nuovi valori.
Certo, dipende dalla versione su cui stai testando. Dipende da quanto siano diverse le tue tabelle. E la versione 9.1 eseguirà semplicemente le vecchie query. Le nuove versioni possono individuare l'inganno e dire che hai un errore.
Come si risolve questo? C'è una ricetta semplice: non farlo. Non bisogna cambiare il search_path mentre l'applicazione è in esecuzione. Se devi cambiare, è meglio creare una nuova connessione.
Possiamo discuterne, cioè aprire, discutere, aggiungere. Forse convinceremo anche gli sviluppatori del database che, nel caso in cui qualcuno cambi il valore, il database dovrebbe comunicarlo al cliente: 'Guarda, qui il tuo valore è stato aggiornato. Forse devi resettare le statements, ricrearle?'. Attualmente, il database si comporta in modo riservato e non informa in alcun modo che da qualche parte le statements sono cambiate.
E sottolineo di nuovo: questo non è tipico per Java. Vedremo la stessa cosa in PL/pgSQL uno a uno. Ma lì sarà riprodotto.
Proviamo a selezionare ancora i dati. Selezioniamo e selezioniamo. Abbiamo una tabella con un milione di righe. Ogni riga è di un kilobyte. Circa un gigabyte di dati. E abbiamo 128 megabyte di memoria di lavoro nella macchina Java.
Noi, come raccomandato in tutti i libri, utilizziamo l'elaborazione a flusso. Cioè, apriamo il resultSet e leggiamo gradualmente i dati. Funzionerà? Non andrà in OutOfMemory? Leggerà a poco a poco? Crediamo nel database, crediamo in Postgres. Non crediamo. Andremo in OutOfMemory? Chi è mai andato in OutOfMemory? E chi è riuscito a risolvere il problema dopo? Qualcuno ci è riuscito.
Se hai un milione di righe, non puoi semplicemente selezionare. Devi sicuramente usare OFFSET/LIMIT. Chi è favorevole a questa opzione? E chi è favorevole a giocare con l'autoCommit?
Qui, come al solito, l'opzione più inaspettata si rivela essere quella giusta. E se per caso disattivi l'autoCommit, questo ti aiuterà. Perché? Alla scienza non è noto.
Ma per impostazione predefinita, tutti i clienti che si connettono al database Postgres selezionano i dati nella loro interezza. PgJDBC non è un'eccezione e seleziona tutte le righe.
C'è una variazione sulla questione di FetchSize, cioè puoi a livello di singola statement dire che qui, per favore, seleziona i dati a blocchi di 10, 50. Ma questo non funziona finché non disattivi l'autoCommit. Disattiva l'autoCommit e inizia a funzionare.
Ma camminare nel codice e mettere setFetchSize ovunque è scomodo. Per questo motivo abbiamo fatto una configurazione che imposta il valore predefinito per tutta la connessione.
Ecco, l'abbiamo detto. Abbiamo configurato il parametro. E cosa abbiamo ottenuto? Se scegliamo in piccole quantità, come per esempio 10 righe, abbiamo costi di overhead piuttosto elevati. Quindi dovremmo impostare questo valore attorno a cento.
Ideale sarebbe anche limitare in byte, ma la ricetta è questa: impostiamo defaultRowFetchSize a più di cento e ci godiamo il risultato.
Passiamo ora all'inserimento dei dati. L'inserimento è più semplice, ci sono diverse opzioni. Ad esempio, INSERT, VALUES. Questo è un buon modo. Possiamo dire "INSERT SELECT". Nella pratica sono la stessa cosa. Non c'è differenza in termini di prestazioni.
I libri dicono che è necessario eseguire i Batch statement, i libri dicono che è possibile eseguire comandi più complessi con diverse parentesi. E in Postgres c'è una funzione fantastica: possiamo fare COPY, cioè farlo più rapidamente.
Se misuriamo, possiamo fare di nuovo alcune scoperte interessanti. Come vogliamo che questo funzioni? Vogliamo evitare di analizzare e di eseguire comandi superflui.
Nella pratica il TCP non ci permette di farlo. Se il client è occupato a inviare una richiesta, il database tenta di inviarci le risposte ma non legge le richieste. Di conseguenza, il client aspetta che il database legga la richiesta, mentre il database aspetta che il client legga la risposta.
E quindi il client è costretto a inviare periodicamente un pacchetto di sincronizzazione. Interazioni di rete superflue, perdita di tempo non necessaria.
E più ne aggiungiamo, peggio diventa. Il driver è piuttosto pessimista e li aggiunge abbastanza spesso, circa ogni 200 righe, a seconda della dimensione delle righe e così via.
A volte, correggendo solo una riga, tutto può accelerare di dieci volte. Questo succede. Perché? Come al solito, una costante è stata già utilizzata da qualche parte. E il valore "128" significava – non utilizzare il batching.
È una buona cosa che questo non sia finito nella versione ufficiale. È stato scoperto prima che iniziassero a rilasciare la versione. Tutti i valori che menziono si basano su versioni moderne.
Misuriamo. Misuriamo InsertBatch semplice. Misuriamo InsertBatch multiplo, cioè la stessa cosa, ma con molti valori. Un trucco astuto. Non tutti lo sanno fare, ma è un modo semplice, molto più facile rispetto a COPY.
È possibile fare COPY.
E si può farlo nelle strutture. Dichiarare il tipo predefinito User, passare un array e INSERT direttamente nella tabella.
Se apri il link: pgjdbc/ubenchmsrk/InsertBatch.java, troverai questo codice su GitHub. Puoi vedere specificamente quali query vengono generate lì. Non è l'essenziale.
Abbiamo lanciato. E la prima cosa che abbiamo capito è che non usare il batch è semplicemente impossibile. Tutte le opzioni di batching sono pari a zero, cioè il tempo di esecuzione è praticamente uguale a zero rispetto all'esecuzione singola.
Stiamo inserendo i dati. È una tabella piuttosto semplice. Tre colonne. E cosa vediamo qui? Vediamo che tutte e tre queste opzioni sono più o meno comparabili. E COPY, ovviamente, è migliore.
Questo è quando inseriamo a pezzi. Quando dicevamo, un valore VALUES, due valore VALUES, tre valore VALUES o li abbiamo specificati 10 separati da virgola. Questo è proprio ora in orizzontale. 1, 2, 4, 128. È evidente che l'insert batch, che è disegnato in blu, ne trae grande vantaggio. Cioè, quando inserisci uno alla volta, o anche quando inserisci quattro, migliora di due volte, semplicemente perché abbiamo messo un po' di più nei VALUES. Meno operazioni EXECUTE.
Usare COPY su piccole dimensioni è estremamente poco promettente. Non ho nemmeno disegnato i primi due. Vanno verso il cielo, cioè queste cifre verdi per COPY.
COPY deve essere usato quando hai un volume di dati di almeno più di cento righe. Le spese generali per aprire questa connessione sono elevate. E, onestamente, non ho approfondito in quella direzione. Ho ottimizzato il batch, ma non il COPY.
Cosa facciamo dopo? Misuriamo. Comprendiamo che dobbiamo usare o strutture o un raffinato batch che unisce più valori.
Cosa bisogna portare via dalla relazione di oggi?
- PreparedStatement è tutto per noi. Questo offre molto in termini di prestazioni. Porta una grande botte di catrame.
- E bisogna fare EXPLAIN ANALYZE 6 volte.
- E bisogna diluire OFFSET 0, e trucchi come +0 per modificare il restante percentuale dai nostri query problematici.
Fonte: habr.com
