Vi invitiamo a consultare la trascrizione della presentazione di Vladimir Sitnikov all'inizio del 2016 "PostgreSQL e JDBC: tiriamo fuori tutto il potenziale".
Buongiorno! Mi chiamo Vladimir Sitnikov. Lavoro da 10 anni in NetCracker e mi occupo principalmente di prestazioni. Tutto ciò che riguarda Java e SQL è ciò che amo.
Oggi parlerò delle esperienze che abbiamo avuto in azienda quando abbiamo iniziato a utilizzare PostgreSQL come server di database. Principalmente lavoriamo con Java, ma ciò di cui parlerò oggi non riguarda solo Java. Come ha dimostrato la pratica, sorgono anche in altri linguaggi.
Parleremo di:
- estrazione dei dati.
- salvataggio dei dati.
- e delle prestazioni.
- E delle insidie nascoste che ci sono.
Iniziamo con una domanda semplice. Estraiamo una riga da una tabella utilizzando la chiave primaria.
Il database si trova sullo stesso host. E tutto questo richiede 20 millisecondi.
Questi 20 millisecondi sono veramente molti. Se hai 100 richieste, spendi tempo in un secondo per elaborarle, ovvero perdi tempo inutilmente.
Non ci piace fare questo e osserviamo cosa ci offre il database. Il database ci propone due modalità di esecuzione delle query.
La prima opzione è una query semplice. Qual è il suo vantaggio? Il fatto che la prendiamo e la inviamo, e nient'altro.
Il database ha anche una query avanzata, che è più complessa, ma più funzionale. Puoi inviare separatamente richieste per parsing, esecuzione, legame di variabili, ecc.
La super query estesa è qualcosa che non tratteremo in questa presentazione. Potremmo avere delle esigenze specifiche, che in qualche modo sono state formulate in una lista, cioè ciò che desideriamo, ma che attualmente non è possibile realizzare e nemmeno nel prossimo anno. Quindi lo abbiamo semplicemente annotato e andremo a convincere le persone principali.
Ciò che possiamo fare è utilizzare la query semplice e la query estesa.
Qual è la particolarità di ciascun approccio?
La query semplice è utile per un'esecuzione una tantum. Una volta eseguita, puoi dimenticarla. Il problema è che non supporta il formato binario dei dati, quindi non è adatta per sistemi ad alte prestazioni.
La query estesa consente di risparmiare tempo sul parsing. Questo è quello che abbiamo fatto e iniziato a utilizzare. Ci è stato di grande aiuto. Non solo risparmiamo sul parsing, ma anche sulla trasmissione dei dati. Trasmettere dati in formato binario è molto più efficiente.
Passiamo alla pratica. Ecco come appare un'applicazione tipica. Potrebbe essere Java, ecc.
Abbiamo creato uno statement. Abbiamo eseguito un comando. Abbiamo creato un close. Qual è qui l'errore? Qual è il problema? Non ci sono problemi. È così che è scritto in tutti i libri. È così che bisogna scrivere. Se desideri la massima prestazione, scrivi in questo modo.
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, è come se un fumatore lavorasse con il database. Abbiamo detto «PARSE EXECUTE DEALLOCATE».
Perché creare e liberare statement è superfluo? Non serve a nessuno. Tuttavia, di solito, in PreparedStatement succede proprio così: quando li chiudiamo, chiudono tutto nel database. Non è ciò che vogliamo.
Vogliamo lavorare con il database in modo sano. Prepariamo il nostro statement una volta e poi lo eseguiamo più volte. In realtà, molte volte significa una sola volta per tutta la vita dell'applicazione, in modo da utilizzare lo stesso statement id in diversi REST. Questo è il nostro obiettivo.
Come possiamo raggiungere questo obiettivo?
È molto semplice: non chiudiamo gli statement. Scriviamo in questo modo: «prepare», «execute».
Se eseguiamo questo, è chiaro che da qualche parte qualcosa si riempirà. Se non è chiaro, possiamo misurarlo. Prendiamo e scriviamo un benchmark in cui utilizziamo questo metodo semplice. Creiamo uno statement, lo eseguiamo su una certa versione del driver e scopriamo che crolla piuttosto rapidamente con la perdita di tutta la memoria
È chiaro che tali errori possono essere facilmente corretti. Non parlerò di essi. Ma dirò che nella nuova versione funziona molto più velocemente. Il metodo è inefficace, ma comunque.
Come lavorare correttamente? Cosa dobbiamo fare per questo?
In realtà, le applicazioni chiudono sempre gli statement. In tutti i libri scrivono di chiuderli, altrimenti si verifica una perdita di memoria.
E PostgreSQL non è in grado di memorizzare nella cache le query. Ogni sessione deve crearsi autonomamente questa cache.
E noi non vogliamo nemmeno perdere tempo sul parsing.
E come al solito, abbiamo due opzioni.
La prima opzione è che possiamo dire di avvolgere tutto in PgSQL. Lì c'è la cache. Memorizza tutto nella cache. Sarebbe fantastico. Abbiamo esaminato questa soluzione. Abbiamo 100500 query. Non funziona. Non siamo d'accordo: non trasformeremo manualmente le query in procedure. No, no.
Abbiamo una seconda opzione: prendere e modificarlo noi stessi. Apriamo i sorgenti e iniziamo a modificare. Modifichiamo, modifichiamo. Si è rivelato che non è così difficile da fare.
È stato introdotto nell'agosto 2015. Ora c'è una versione più moderna. E tutto va alla grande. Funziona così bene che non cambiamo nulla nell'applicazione. E abbiamo anche smesso di considerare PgSQL, cioè ci è bastato per ridurre praticamente a zero tutte le spese generali.
Di conseguenza, le dichiarazioni preparate dal server si attivano alla quinta esecuzione per non consumare memoria nel database per ogni singola richiesta.
Si potrebbe chiedere: dove sono i numeri? Cosa state ottenendo? E qui non posso dare numeri, perché ogni richiesta ha i suoi.
Abbiamo avuto richieste tali che spendevamo circa 20 millisecondi per il parsing nelle richieste OLTP. C'erano 0,5 millisecondi per l'esecuzione e 20 millisecondi per il parsing. La richiesta consisteva in 10 KiB di testo, 170 righe di piano. Questa è una richiesta OLTP. Richiede 1, 5, 10 righe, a volte di più.
Ma non volevamo assolutamente spendere 20 millisecondi. Siamo riusciti a ridurlo a zero. Tutto va bene.
Cosa potete dedurre da tutto ciò? Se utilizzate Java, prendete la versione moderna del driver e ne sarete contenti.
Se utilizzate un altro linguaggio, pensate: forse questo vi serve anche? Perché dal punto di vista del linguaggio finale, ad esempio, se PL 8 o avete LibPQ, non è ovvio che stiate perdendo tempo non nell'esecuzione, ma nel parsing e questo vale la pena verificarlo. Come? Tutto gratuitamente.
A parte eventuali errori o particolarità. E proprio di questo parleremo adesso. La maggior parte riguarderà l'archeologia industriale, cioè ciò che abbiamo trovato e a cosa ci siamo imbattuti.
Se la richiesta è generata dinamicamente. Può succedere. Qualcuno concatena le stringhe e ottiene una richiesta SQL.
Qual è il problema? Il problema è che ogni volta otteniamo una stringa diversa.
E per questa stringa diversa è necessario ricalcolare nuovamente il hashCode. Questa è davvero una questione di CPU: trovare un testo lungo nella richiesta anche se già presenti nel hash non è così semplice. Pertanto, la conclusione è semplice: non generare richieste. Conservatele in una sola variabile. E siate contenti.
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 è un Int, diciamo setInt. E se è NULL, potrebbe sempre essere VARCHAR. E in fin dei conti, quale differenza fa quale NULL? Al database non importa nulla.
In pratica, al database non è affatto indifferente. Se per la prima volta dite che è un numero, e la seconda volta dite che è VARCHAR, non è possibile riutilizzare le dichiarazioni preparate dal server. In tal caso, è necessario ricreare la nostra dichiarazione.
Se eseguite la stessa richiesta, assicuratevi che i tipi di dati nella colonna non si confondano. Dovete prestare attenzione a NULL. Questo è un errore frequente che abbiamo riscontrato dopo aver iniziato ad utilizzare PreparedStatements.
Bene, l'abbiamo attivato. Abbiamo preso, forse, il driver. E le prestazioni sono calate. È andato tutto male.
Com'è possibile? È un bug o una funzionalità? Sfortunatamente, non siamo riusciti a capire se fosse un bug o una funzionalità. Ma c'è uno scenario piuttosto semplice per riprodurre questo problema. Ci ha davvero colti di sorpresa. E consiste nel selezionare letteralmente da una sola tabella. Naturalmente, avevamo più di queste richieste. Solitamente comprendevano due o tre tabelle, ma c'è tale scenario di riproduzione. Prendete il vostro database di qualsiasi versione e provate a riprodurlo.
Il punto è che abbiamo due colonne, ognuna delle quali è indicizzata. In una colonna ci sono un milione di righe con il valore NULL. E nell'altra colonna ci sono solo 20 righe. Quando eseguiamo senza variabili collegate, tutto funziona bene.
Se iniziamo ad eseguire con variabili collegate, cioè utilizziamo il segno «?» o «$1» per la nostra richiesta, cosa otteniamo alla fine?
La prima esecuzione – come previsto. La seconda – un po' più veloce. Qualcosa è stato memorizzato nella cache. La terza, quarta, quinta. Poi bam – e in qualche modo così. E la cosa peggiore è che questo accade alla sesta esecuzione. Chi sapeva che era necessario farne esattamente sei per comprendere quale fosse il piano di esecuzione effettivo?
Chi è colpevole? Cosa è successo? Il database contiene ottimizzazione. Ed è ottimizzato per casi generici. E, di conseguenza, a partire da un certo numero di esecuzioni, passa a un piano generico che, sfortunatamente, può rivelarsi diverso. Potrebbe essere anche lo stesso, ma potrebbe anche essere diverso. E c'è un valore soglia che porta a questo comportamento.
Cosa si può fare a riguardo? Qui, naturalmente, è complicato fare delle ipotesi. Esiste una soluzione semplice che utilizziamo. È +0, OFFSET 0. Sicuramente conoscete soluzioni simili. Prendiamo e aggiungiamo «+0» alla richiesta e va tutto bene. Ve lo mostrerò più tardi.
C'è anche un'altra opzione: guardare i piani con più attenzione. Lo sviluppatore deve non solo scrivere la richiesta, ma anche dire «explain analyze» 6 volte. Se lo dice 5, non andrà bene.
E c'è un terzo modo: scrivere un’email a pgsql-hackers. L'ho fatto, ma non è ancora chiaro se sia un bug o una feature.
Mentre riflettiamo se sia un bug o una feature, vediamo di aggiustarlo. Prendiamo la nostra richiesta e aggiungiamo «+0». Va tutto bene. Solo due caratteri e non è nemmeno necessario pensare a come fare. Molto semplice. Abbiamo semplicemente impedito al database di utilizzare un indice su questa colonna. Non abbiamo un indice sulla colonna «+0» e quindi il database non usa l'indice, tutto va bene.
Questa è la regola dei 6 explain. Nelle versioni attuali è necessario farlo 6 volte se avete variabili collegate. Se non avete variabili collegate, procediamo in questo modo. E alla fine, è proprio questa richiesta che fallisce. Non è affatto complicato.
Sembra strano, quanto ci si può aspettare? Qui un bug, lì un bug. Realmente ci sono bug ovunque.
Diamo un'altra occhiata. Ad esempio, abbiamo due schemi. Schema A con la tabella Y e schema B con la tabella Y. La richiesta è di selezionare dati dalla tabella. Cosa avremo? Avremo un errore. Tutto quanto detto sopra. La regola è: ci sono bug ovunque, quindi avremo tutto quanto menzionato.
Ora la domanda è: «Perché?». Sembrerebbe che ci sia documentazione, dato che se abbiamo uno schema, c’è la variabile «search_path» che indica dove cercare la tabella. Sembrerebbe che la variabile ci sia.
Qual è il problema? Il problema è che le dichiarazioni preparate dal server non sospettano che qualcuno possa cambiare il search_path. Questo valore rimane come costante per il database. E alcune parti potrebbero non cogliere i nuovi valori.
Certamente, dipende dalla versione su cui stai effettuando il test. Dipende da quanto le tue tabelle differiscono. E la versione 9.1 eseguirà semplicemente le vecchie richieste. Le versioni più recenti potrebbero scoprire l'inganno e segnalare un errore.
Come si può affrontare questo problema? C'è una semplice ricetta: non fatelo. Non cambiate il search_path mentre l'applicazione è in esecuzione. Se lo cambiate, è meglio creare una nuova connessione.
Possiamo discuterne, cioè aprirlo, discuterlo, aggiungerlo. Forse convinceremo gli sviluppatori del database che, nel caso in cui qualcuno cambi il valore, il database dovrebbe avvisare il cliente: «Guarda, il tuo valore è stato aggiornato. Forse dovresti resettare o ricreare le dichiarazioni?». Attualmente il database si comporta in modo subdolo e non informa affatto che le dichiarazioni interne sono cambiate.
E ancora una volta sottolineo: questo non è tipico per Java. Lo stesso lo vedremo in PL/pgSQL uno a uno. Ma lì sarà riprodotto.
Proviamo a selezionare di nuovo i dati. Selezioniamo, 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.
Come consigliato in tutti i libri, utilizziamo l'elaborazione a flusso. Cioè, apriamo il resultSet e leggiamo i dati poco alla volta. Funzionerà? Non andrà in errore di memoria? Leggerà poco alla volta? Crediamo nel database, crediamo in Postgres. Non ci fidiamo. Cadremo in OutOfMemory? Chi di voi ha ricevuto un OutOfMemory? E chi è riuscito a risolverlo dopo? Qualcuno è riuscito a risolverlo.
Se hai un milione di righe, non puoi semplicemente selezionare così. Devi assolutamente usare OFFSET/LIMIT. Chi è a favore di questa opzione? E chi è a favore dell'idea di giocare con autoCommit?
Qui, come al solito, l'opzione più inaspettata si rivela quella giusta. E se all'improvviso disabiliti autoCommit, questo aiuterà. Perché? La scienza non lo sa.
Ma per impostazione predefinita, tutti i client che si connettono al database Postgres selezionano i dati per intero. PgJDBC non fa eccezione, seleziona tutte le righe.
C'è una variante sul tema del FetchSize, cioè puoi dire a livello di singola dichiarazione di selezionare i dati in blocchi di 10, 50. Ma questo non funziona finché non disattivi autoCommit. Disabilitato autoCommit – inizia a funzionare.
Ma sfogliare il codice e impostare ovunque setFetchSize è scomodo. Quindi abbiamo creato una configurazione che imposta un valore predefinito per l'intera connessione.
Ecco, lo abbiamo impostato. Abbiamo configurato il parametro. E cosa abbiamo ottenuto? Se selezioniamo in piccolo, ad esempio, 10 righe, abbiamo costi di overhead piuttosto elevati. Pertanto, dovremmo impostare questo valore intorno a cento.
In ideale, naturalmente, dovremmo anche imparare a limitare in byte, ma la ricetta è questa: impostiamo defaultRowFetchSize superiore a cento e ci rallegriamo.
Passiamo all'inserimento dei dati. L'inserimento è più semplice e offre diverse opzioni. Ad esempio, INSERT, VALUES. Questo è un buon metodo. Possiamo anche dire 'INSERT SELECT'. In pratica, è la stessa cosa. Non c'è alcuna differenza in termini di prestazioni.
I libri affermano che è necessario eseguire il Batch statement, e che si possono eseguire comandi più complessi con più parentesi. In Postgres c'è una funzione fantastica: è possibile fare COPY, ovvero farlo più velocemente.
Misurando, possiamo scoprire cose interessanti. Come vogliamo che funzioni? Vogliamo evitare il parsing e l'esecuzione di comandi superflui.
In pratica, il TCP non ci permette di farlo. Se il client è impegnato a inviare una richiesta, il database non legge le richieste mentre cerca di inviarci le risposte. Di conseguenza, il client attende che il database legga la richiesta, mentre il database aspetta che il client legga la risposta.
Pertanto, il client è costretto a inviare periodicamente un pacchetto di sincronizzazione. Interazioni di rete non necessarie, perdita di tempo aggiuntiva.
E più ne aggiungiamo, peggio diventa. Il driver è piuttosto pessimista e le aggiunge abbastanza frequentemente, circa una volta ogni 200 righe, a seconda della lunghezza delle righe, ecc.
A volte, modificando una sola riga, tutto può diventare dieci volte più veloce. Questo può capitare. Perché? Come al solito, una costante è stata già utilizzata da qualche parte, e il valore '128' significava non utilizzare il batching.
È un bene che questo non sia finito nella versione ufficiale. È stato scoperto prima di iniziare a rilasciare la versione.
Misuriamo. Stiamo misurando InsertBatch semplice. Stiamo misurando InsertBatch multiplo, cioè lo stesso, ma con molti valori. Un trucco astuto. Non tutti lo sanno fare, ma è un approccio semplice, molto più facile di COPY.
È possibile fare COPY.
E può essere fatto con strutture. Dichiarare un tipo di utente predefinito, passare un array e inserire direttamente nella tabella.
Se aprite il link: pgjdbc/ubenchmsrk/InsertBatch.java, potete trovare questo codice su GitHub. Potete vedere specificamente quali query vengono generate. Non è questo il punto.
Abbiamo eseguito il test. 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 pari a zero rispetto all'esecuzione singola.
Stiamo inserendo dati. La tabella è piuttosto semplice. Tre colonne. E cosa vediamo qui? Vediamo che tutte e tre le opzioni sono comparabili. E COPY è ovviamente la migliore.
Questo è quando ci inseriamo a pezzi. Quando dicevamo valore VALUES singolo, due valori VALUES, tre valori VALUES, o se ne menzioniamo 10 separati da virgole. Questo è il modo in cui appare ora in orizzontale. 1, 2, 4, 128. È chiaro che Batch Insert, rappresentato in blu, ne guadagna molto. Cioè, quando inserite uno alla volta, o persino quando inserite quattro alla volta, diventa due volte più veloce, semplicemente perché abbiamo inserito un po' più di dati in VALUES. Meno operazioni EXECUTE.
Usare COPY su piccole quantità è estremamente inefficace. Non l'ho nemmeno disegnato per le prime due. Vanno alle stelle, cioè questi numeri verdi per COPY.
COPY dovrebbe essere usato quando hai un volume di dati di almeno più di cento righe. Le spese generali per l'apertura di quella connessione sono elevate. E, a essere onesti, non ho approfondito questo aspetto. Ho ottimizzato il batch, ma non COPY.
Cosa facciamo dopo? Abbiamo misurato. Comprendiamo che dobbiamo usare o strutture, o un astuto batch che combina più valori.
Qual è il messaggio chiave di questa presentazione?
- PreparedStatement è tutto per noi. Offre molto in termini di prestazioni. Porta una buona dose di problemi.
- E dobbiamo eseguire EXPLAIN ANALYZE 6 volte.
- Dobbiamo anche diversificare OFFSET 0 e utilizzare trucchi come +0 per correggere il restante percentuale dei nostri problemi con le query.
Fonte: habr.com
