Ti suggerisco di leggere la trascrizione del rapporto di Vladimir Sitnikov dell'inizio del 2016 "PostgreSQL e JDBC stanno spremendo tutto il succo"
Buon pomeriggio Il mio nome è Vladimir Sitnikov. Lavoro per NetCracker da 10 anni. E mi interessa soprattutto la produttività. Tutto ciò che riguarda Java, tutto ciò che riguarda SQL è ciò che amo.
E oggi parlerò di ciò che abbiamo riscontrato in azienda quando abbiamo iniziato a utilizzare PostgreSQL come server di database. E lavoriamo principalmente con Java. Ma quello che ti dirò oggi non riguarda solo Java. Come ha dimostrato la pratica, ciò avviene anche in altre lingue.
Parleremo:
- sul campionamento dei dati.
- Informazioni sul salvataggio dei dati.
- E anche sulle prestazioni.
- E dei rastrelli sottomarini che sono sepolti lì.
Cominciamo con una semplice domanda. Selezioniamo una riga dalla tabella in base alla chiave primaria.
Il database si trova sullo stesso host. E tutta questa agricoltura richiede 20 millisecondi.
Questi 20 millisecondi sono tanti. Se hai 100 richieste di questo tipo, passi tempo al secondo a scorrere queste richieste, ad es. stiamo perdendo tempo.
Non ci piace farlo e guardiamo cosa ci offre la base per questo. Il database ci offre due opzioni per l'esecuzione delle query.
La prima opzione è una semplice richiesta. Cosa c'è di buono? Il fatto che lo prendiamo e lo inviamo, e niente di più.
Il database dispone anche di una query avanzata, che è più complicata, ma più funzionale. È possibile inviare separatamente una richiesta di analisi, esecuzione, associazione di variabili, ecc.
La query super estesa è qualcosa che non tratteremo nel rapporto attuale. Forse vogliamo qualcosa dal database e c'è una lista dei desideri che è stata formata in qualche modo, cioè questo è ciò che vogliamo, ma è impossibile ora e nel prossimo anno. Quindi l’abbiamo appena registrato e andremo in giro a scuotere le persone principali.
E quello che possiamo fare è una query semplice e una query estesa.
Cosa c'è di speciale in ciascun approccio?
Una query semplice è utile per l'esecuzione una tantum. Una volta fatto e dimenticato. E il problema è che non supporta il formato dati binario, cioè non è adatto per alcuni sistemi ad alte prestazioni.
Query estesa: consente di risparmiare tempo nell'analisi. Questo è ciò che abbiamo fatto e iniziato a utilizzare. Questo ci ha davvero aiutato. Non ci sono solo risparmi sull'analisi. Ci sono risparmi sul trasferimento dei dati. Il trasferimento dei dati in formato binario è molto più efficiente.
Passiamo alla pratica. Ecco come appare una tipica applicazione. Potrebbe essere Java, ecc.
Abbiamo creato una dichiarazione. Eseguito il comando. Creato vicino. Dov'è l'errore qui? Qual è il problema? Nessun problema. Questo è quello che dicono in tutti i libri. Ecco come dovrebbe essere scritto. Se vuoi il massimo delle prestazioni, scrivi così.
Ma la pratica ha dimostrato che questo non funziona. Perché? Perché abbiamo un metodo "chiuso". E quando lo facciamo, dal punto di vista del database risulta che è come un fumatore che lavora con un database. Abbiamo detto "PARSE EXECUTE DEALLOCATE".
Perché tutta questa ulteriore creazione e scaricamento di dichiarazioni? Nessuno ne ha bisogno. Ma ciò che di solito accade nelle PreparedStatement è che quando le chiudiamo, chiudono tutto nel database. Questo non è ciò che vogliamo.
Vogliamo, come le persone sane, lavorare con la base. Abbiamo preso e preparato la nostra dichiarazione una volta, poi la eseguiamo molte volte. In effetti, molte volte (questo accade una volta nell'intero ciclo di vita delle applicazioni) sono state analizzate. E usiamo lo stesso ID istruzione su REST diversi. Questo è il nostro obiettivo.
Come possiamo raggiungere questo risultato?
È molto semplice: non è necessario chiudere le dichiarazioni. Lo scriviamo così: “preparare” “eseguire”.
Se lanciamo qualcosa del genere, allora è chiaro che qualcosa traboccherà da qualche parte. Se non è chiaro, puoi provarlo. Scriviamo un benchmark che utilizza questo semplice metodo. Crea una dichiarazione. Lo lanciamo su qualche versione del driver e scopriamo che si blocca abbastanza rapidamente con la perdita di tutta la memoria che aveva.
È chiaro che tali errori possono essere facilmente corretti. Non ne parlerò. Ma dirò che la nuova versione funziona molto più velocemente. Il metodo è stupido, ma comunque.
Come lavorare correttamente? Cosa dobbiamo fare per questo?
In realtà, le applicazioni chiudono sempre le dichiarazioni. In tutti i libri dicono di chiuderlo, altrimenti perde la memoria.
E PostgreSQL non sa come memorizzare nella cache le query. È necessario che ogni sessione crei questa cache per se stessa.
E non vogliamo nemmeno perdere tempo con l’analisi.
E come al solito abbiamo due opzioni.
La prima opzione è prenderla e dire di avvolgere tutto in PgSQL. C'è una cache lì. Memorizza tutto nella cache. Risulterà fantastico. L'abbiamo visto. Abbiamo 100500 richieste. Non funziona. Non siamo d'accordo nel trasformare manualmente le richieste in procedure. No, no.
Abbiamo una seconda opzione: prendilo e taglialo noi stessi. Apriamo i sorgenti e iniziamo a tagliare. Abbiamo visto e visto. Si è scoperto che non è così difficile da fare.
Questo è apparso nell'agosto 2015. Ora c'è una versione più moderna. E tutto è fantastico. Funziona così bene che non cambiamo nulla nell'applicazione. E abbiamo addirittura smesso di pensare in direzione di PgSQL, cioè questo ci è bastato per ridurre quasi a zero tutti i costi generali.
Di conseguenza, le istruzioni preparate dal server vengono attivate alla quinta esecuzione per evitare di sprecare memoria nel database per ogni richiesta una tantum.
Potresti chiedere: dove sono i numeri? Cosa stai ottenendo? E qui non darò numeri, perché ogni richiesta ha la sua.
Le nostre query erano tali che abbiamo dedicato circa 20 millisecondi all'analisi delle query OLTP. C'erano 0,5 millisecondi per l'esecuzione, 20 millisecondi per l'analisi. Richiesta – 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 sprecare 20 millisecondi. Lo abbiamo ridotto a 0. È tutto fantastico.
Cosa puoi portare via da qui? Se hai Java, prendi la versione moderna del driver e rallegrati.
Se parli una lingua diversa, pensa: forse ne hai bisogno anche tu? Perché dal punto di vista della lingua finale, ad esempio, se hai PL 8 o LibPQ, allora non è ovvio per te che stai dedicando tempo non all'esecuzione, all'analisi, e vale la pena verificarlo. Come? Tutto è gratuito.
Solo che ci sono errori e alcune peculiarità. E ne parleremo proprio adesso. La maggior parte riguarderà l'archeologia industriale, ciò che abbiamo trovato, ciò che abbiamo trovato.
Se la richiesta viene generata dinamicamente. Succede. Qualcuno incolla insieme le stringhe, ottenendo una query SQL.
Perché è cattivo? È un male perché ogni volta ci ritroviamo con una stringa diversa.
E l'hashCode di questa stringa diversa deve essere riletto. Questo è davvero un compito della CPU: trovare un lungo testo di richiesta anche in un hash esistente non è così facile. Pertanto, la conclusione è semplice: non generare richieste. Memorizzarli in una variabile. E rallegrati.
Prossimo problema. I tipi di dati sono importanti. Ci sono ORM che dicono che non importa che tipo di NULL ci sia, lascia che ce ne sia qualche tipo. Se Int, allora diciamo setInt. E se NULL, lascia che sia sempre VARCHAR. E che differenza fa alla fine cosa NULL c'è? Il database stesso capirà tutto. E questa immagine non funziona.
In pratica, al database non interessa affatto. Se la prima volta hai detto che si tratta di un numero e la seconda volta che si tratta di un VARCHAR, è impossibile riutilizzare le istruzioni preparate dal server. E in questo caso, dobbiamo ricreare la nostra dichiarazione.
Se stai eseguendo la stessa query, assicurati che i tipi di dati nella colonna non siano confusi. Devi fare attenzione a NULL. Questo è un errore comune che abbiamo riscontrato dopo aver iniziato a utilizzare PreparedStatements
Ok, acceso. Forse hanno preso l'autista. E la produttività è crollata. Le cose sono andate male.
Come avviene questo? È un bug o una funzionalità? Purtroppo non è stato possibile capire se si tratti di un bug o di una funzionalità. Ma esiste uno scenario molto semplice per riprodurre questo problema. Ci ha teso un'imboscata in modo del tutto inaspettato. E consiste nel campionare letteralmente da una tabella. Naturalmente abbiamo avuto più richieste di questo tipo. Di norma, includevano due o tre tabelle, ma esiste uno scenario di riproduzione del genere. Prendi qualsiasi versione dal tuo database e riproducila.
Il punto è che abbiamo due colonne, ciascuna delle quali è indicizzata. Ci sono un milione di righe in una colonna NULL. E la seconda colonna contiene solo 20 righe. Quando eseguiamo senza variabili legate, tutto funziona bene.
Se iniziamo l'esecuzione con variabili legate, cioè eseguiamo il "?" o “$1” per la nostra richiesta, cosa otteniamo?
La prima esecuzione è come previsto. Il secondo è un po' più veloce. Qualcosa è stato memorizzato nella cache. Terzo, quarto, quinto. Poi bang - e qualcosa del genere. E la cosa peggiore è che ciò accade alla sesta esecuzione. Chi sapeva che era necessario fare esattamente sei esecuzioni per capire quale fosse il vero piano di esecuzione?
Chi è colpevole? Quello che è successo? Il database contiene l'ottimizzazione. E sembra essere ottimizzato per il caso generico. E, di conseguenza, a un certo punto passa a un piano generico, che purtroppo potrebbe rivelarsi diverso. Potrebbe rivelarsi lo stesso, o potrebbe essere diverso. E c'è una sorta di valore soglia che porta a questo comportamento.
Cosa puoi fare al riguardo? Qui, ovviamente, è più difficile dare per scontato qualcosa. C'è una soluzione semplice che usiamo. Questo è +0, OFFSET 0. Sicuramente conosci queste soluzioni. Lo prendiamo e aggiungiamo "+0" alla richiesta e tutto va bene. Te lo mostrerò più tardi.
E c'è un'altra opzione: guarda i piani con più attenzione. Lo sviluppatore non deve solo scrivere una richiesta, ma anche dire "spiega analizza" 6 volte. Se è 5, non funzionerà.
E c'è una terza opzione: scrivi una lettera agli hacker pgsql. Ho scritto, però, non è ancora chiaro se si tratti di un bug o di una funzionalità.
Mentre riflettiamo se si tratta di un bug o di una funzionalità, risolviamolo. Prendiamo la nostra richiesta e aggiungiamo "+0". Va tutto bene. Due simboli e non devi nemmeno pensare a come è o cosa è. Molto semplice. Abbiamo semplicemente proibito al database di utilizzare un indice su questa colonna. Non abbiamo un indice sulla colonna “+0” e basta, il database non usa l'indice, va tutto bene.
Questa è la regola del 6 spiegata. Ora nelle versioni attuali devi farlo 6 volte se hai variabili legate. Se non hai variabili legate, questo è ciò che facciamo. E alla fine è proprio questa richiesta a fallire. Non è una cosa complicata.
Sembrerebbe, quanto è possibile? Un insetto qui, un insetto là. In realtà, il bug è ovunque.
Diamo uno sguardo più da vicino. Ad esempio, abbiamo due schemi. Schema A con tavolo S e schema B con tavolo S. Query: seleziona i dati da una tabella. Cosa avremo in questo caso? Avremo un errore. Avremo tutto quanto sopra. La regola è: un bug è ovunque, avremo tutto quanto sopra.
Ora la domanda è: “Perché?” Sembrerebbe che esista documentazione secondo cui se abbiamo uno schema, allora esiste una variabile "search_path" che ci dice dove cercare la tabella. Sembrerebbe che ci sia una variabile.
Qual è il problema? Il problema è che le istruzioni preparate dal server non sospettano che search_path possa essere modificato da qualcuno. Questo valore rimane, per così dire, costante per il database. E alcune parti potrebbero non acquisire nuovi significati.
Naturalmente, questo dipende dalla versione su cui stai testando. Dipende da quanto seriamente differiscono i tuoi tavoli. E la versione 9.1 eseguirà semplicemente le vecchie query. Le nuove versioni potrebbero rilevare il bug e dirti che hai un bug.
Come trattarlo? C'è una ricetta semplice: non farlo. Non è necessario modificare search_path mentre l'applicazione è in esecuzione. Se cambi, è meglio creare una nuova connessione.
Puoi discutere, cioè aprire, discutere, aggiungere. Forse possiamo convincere gli sviluppatori del database che quando qualcuno modifica un valore, il database dovrebbe comunicarlo al cliente: “Guarda, il tuo valore è stato aggiornato qui. Forse è necessario reimpostare le dichiarazioni e ricrearle?" Adesso il database si comporta in segreto e non segnala in alcun modo che le dichiarazioni siano cambiate da qualche parte al suo interno.
E lo sottolineerò ancora: questo è qualcosa che non è tipico di Java. Vedremo la stessa cosa in PL/pgSQL uno a uno. Ma sarà riprodotto lì.
Proviamo un'ulteriore selezione dei dati. Scegliamo e scegliamo. Abbiamo una tabella con un milione di righe. Ogni riga è un kilobyte. Circa un gigabyte di dati. E abbiamo una memoria di lavoro nella macchina Java di 128 megabyte.
Noi, come raccomandato in tutti i libri, utilizziamo l'elaborazione del flusso. Cioè, apriamo resultSet e leggiamo i dati da lì poco a poco. Funzionerà? Cadrà dalla memoria? Leggerai un po'? Confidiamo nel database, confidiamo in Postgres. Non ci crediamo. Perderemo la memoria? Chi ha sperimentato OutOfMemory? Chi è riuscito a sistemarlo dopo? Qualcuno è riuscito a sistemarlo.
Se hai un milione di righe, non puoi semplicemente scegliere. OFFSET/LIMIT è obbligatorio. Chi è per questa opzione? E chi è favorevole a giocare con autoCommit?
Qui, come al solito, l'opzione più inaspettata risulta essere corretta. E se disattivi improvvisamente l'autoCommit, sarà d'aiuto. Perché? La scienza non lo sa.
Ma per impostazione predefinita, tutti i client che si connettono a un database Postgres recuperano tutti i dati. PgJDBC non fa eccezione a questo riguardo; seleziona tutte le righe.
Esiste una variazione sul tema FetchSize, ovvero puoi dire a livello di un'istruzione separata che qui seleziona i dati per 10, 50. Ma questo non funziona finché non disattivi l'autoCommit. Disattivato autoCommit: inizia a funzionare.
Ma esaminare il codice e impostare setFetchSize ovunque è scomodo. Pertanto, abbiamo creato un'impostazione che indicherà il valore predefinito per l'intera connessione.
Questo è quello che abbiamo detto. Il parametro è stato configurato. E cosa abbiamo ottenuto? Se selezioniamo piccoli importi, se, ad esempio, selezioniamo 10 righe alla volta, allora avremo costi generali molto elevati. Pertanto, questo valore dovrebbe essere impostato su circa cento.
Idealmente, ovviamente, devi ancora imparare a limitarlo in byte, ma la ricetta è questa: imposta defaultRowFetchSize su più di cento e sii felice.
Passiamo all'inserimento dei dati. L'inserimento è più semplice, ci sono diverse opzioni. Ad esempio, INSERISCI, VALORI. Questa è una buona opzione. Puoi dire "INSERISCI SELEZIONE". In pratica è la stessa cosa. Non c'è differenza in termini di prestazioni.
I libri dicono che devi eseguire un'istruzione Batch, i libri dicono che puoi eseguire comandi più complessi con più parentesi. E Postgres ha una funzionalità meravigliosa: puoi eseguire la COPIA, ovvero farlo più velocemente.
Se lo misuri, puoi ancora fare alcune scoperte interessanti. Come vogliamo che funzioni? Vogliamo non analizzare e non eseguire comandi non necessari.
In pratica, TCP non ci consente di farlo. Se il client è impegnato a inviare una richiesta, il database non legge le richieste nel tentativo di inviarci risposte. Il risultato finale è che il client attende che il database legga la richiesta e il database attende che il client legga la risposta.
E quindi il client è costretto a inviare periodicamente un pacchetto di sincronizzazione. Interazioni di rete extra, ulteriore perdita di tempo.
E più li aggiungiamo, peggio diventa. L'autista è piuttosto pessimista e li aggiunge abbastanza spesso, circa una volta ogni 200 righe, a seconda della dimensione delle righe, ecc.
Succede che correggi solo una riga e tutto accelererà 10 volte. Succede. Perché? Come al solito, una costante come questa è già stata utilizzata da qualche parte. E il valore “128” significava non utilizzare il batching.
È positivo che questo non sia stato incluso nella versione ufficiale. Scoperto prima dell'inizio del rilascio. Tutti i significati che do si basano su versioni moderne.
Proviamolo. Misuriamo InsertBatch semplice. Misuriamo InsertBatch più volte, ovvero la stessa cosa, ma ci sono molti valori. Mossa complicata. Non tutti possono farlo, ma è una mossa semplicissima, molto più facile di COPIA.
Puoi fare COPIA.
E puoi farlo sulle strutture. Dichiara il tipo predefinito dell'utente, passa l'array e INSERT direttamente alla tabella.
Se apri il collegamento: pgjdbc/ubenchmsrk/InsertBatch.java, questo codice è su GitHub. Puoi vedere nello specifico quali richieste vengono generate lì. Non importa.
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, ovvero il tempo di esecuzione è praticamente zero rispetto a un'esecuzione una tantum.
Inseriamo i dati. È una tabella molto semplice. Tre colonne. E cosa vediamo qui? Vediamo che tutte e tre queste opzioni sono più o meno comparabili. E COPIA è, ovviamente, migliore.
Questo è quando inseriamo i pezzi. Quando abbiamo detto che un valore VALUES, due valori VALUES, tre valori VALUES, oppure ne abbiamo indicati 10 separati da una virgola. Questo è solo orizzontale ora. 1, 2, 4, 128. Si vede che il Batch Insert, disegnato in blu, lo fa sentire molto meglio. Cioè, quando ne inserisci uno alla volta o anche quando ne inserisci quattro alla volta, diventa due volte più buono, semplicemente perché abbiamo inserito un po' di più in VALORI. Meno operazioni EXECUTE.
Usare COPY su piccoli volumi è estremamente poco promettente. Non ho nemmeno disegnato i primi due. Vanno in paradiso, cioè questi numeri verdi per COPY.
COPY dovrebbe essere utilizzato quando si hanno almeno cento righe di dati. Il sovraccarico per l'apertura di questa connessione è elevato. E, a dire il vero, non ho scavato in questa direzione. Ho ottimizzato Batch, ma non COPY.
Cosa facciamo dopo? L'abbiamo provato. Comprendiamo che dobbiamo utilizzare strutture o un bacth intelligente che combini diversi significati.
Cosa dovresti imparare dal resoconto di oggi?
- PreparedStatement è il nostro tutto. Questo dà molto in termini di produttività. Produce un grande flop nell'unguento.
- E devi fare SPIEGARE ANALISI 6 volte.
- E dobbiamo diluire OFFSET 0 e trucchi come +0 per correggere la percentuale rimanente delle nostre query problematiche.
Fonte: habr.com