Ti suggerenu di leghje a trascrizione di u rapportu di u principiu di u 2016 di Vladimir Sitnikov "PostgreSQL è JDBC sprimenu tuttu u sucu"
Bonghjornu Mi chjamu Vladimir Sitnikov. Aghju travagliatu per NetCracker per 10 anni. È sò soprattuttu in a produtividade. Tuttu ciò chì riguarda Java, tuttu ciò chì hè in relazione cù SQL hè ciò chì mi piace.
È oghje parleraghju di ciò chì avemu scontru in a cumpagnia quandu avemu cuminciatu à aduprà PostgreSQL cum'è un servitore di basa di dati. È avemu principalmente travagliatu cù Java. Ma ciò chì vi dicu oghje ùn hè micca solu di Java. Comu a pratica hà dimustratu, questu accade ancu in altre lingue.
Parleremu :
- nantu à u campionamentu di dati.
- Circa a salvezza di dati.
- È ancu nantu à u rendiment.
- È nantu à i razzi sott'acqua chì ci sò intarrati.
Cuminciamu cù una quistione simplice. Selezziemu una fila da a tavula basatu annantu à a chjave primaria.
A basa di dati si trova nantu à u stessu host. È tutta sta agricultura pigghia 20 millisecondi.
Questi 20 millisecondi sò assai. Sè vo avete 100 tali dumandesi, allura vi passanu tempu per seconda scrolling à traversu sti dumandesi, vale à dì, avemu perde u tempu.
Ùn ci piace micca fà questu è fighjate ciò chì a basa ci prupone per questu. A basa di dati ci offre duie opzioni per eseguisce dumande.
A prima opzione hè una dumanda simplice. Chì ci hè di bonu? U fattu chì l'avemu pigliatu è mandatu, è nunda di più.
A basa di dati hà ancu una dumanda avanzata, chì hè più complicata, ma più funziunale. Pudete invià separatamente una dumanda per l'analisi, l'esekzione, u ligame di variabili, etc.
A dumanda super estesa hè qualcosa chì ùn copreremu micca in u rapportu attuale. Avemu, forsi, vulemu qualcosa da a basa di dati è ci hè una lista di desideri chì hè stata formata in qualchì forma, vale à dì questu hè ciò chì vulemu, ma hè impussibile avà è in l'annu dopu. Allora l'avemu appena arregistratu è andemu in giru à scuzzulà e persone principali.
È ciò chì pudemu fà hè una dumanda simplice è una dumanda allargata.
Chì ci hè di particulare in ogni approcciu?
Una dumanda simplice hè bona per l'esecuzione una volta. Una volta fattu è scurdatu. È u prublema hè chì ùn sustene micca u formatu di dati binari, vale à dì ùn hè micca adattatu per certi sistemi d'altu rendiment.
Interrogazione estesa - permette di risparmià tempu nantu à l'analisi. Questu hè ciò chì avemu fattu è cuminciamu à aduprà. Questu ci hà veramente, veramente aiutatu. Ùn ci hè micca solu risparmiu nantu à l'analisi. Ci sò risparmi nantu à u trasferimentu di dati. Trasferimentu di dati in formatu binariu hè assai più efficaci.
Passemu à a pratica. Questu hè ciò chì una applicazione tipica pare. Puderia esse Java, etc.
Avemu creatu dichjarazione. Eseguitu u cumandamentu. Creatu vicinu. Induve hè u sbagliu quì? Chì ghjè u prublema ? Nisunu prublema. Questu hè ciò chì dice in tutti i libri. Questu hè cumu si deve esse scrittu. Se vulete u massimu rendiment, scrivite cusì.
Ma a pratica hà dimustratu chì questu ùn hè micca travagliatu. Perchè? Perchè avemu un metudu "close". È quandu facemu questu, da u puntu di vista di a basa di dati, risulta chì hè cum'è un fumatore chì travaglia cù una basa di dati. Avemu dettu "PARSE EXECUTE DEALLOCATE".
Perchè tutta sta creazione extra è scaricamentu di dichjarazioni? Nimu hà bisognu di elli. Ma ciò chì succede di solitu in PreparedStatements hè chì quandu li chjudemu, chjudenu tuttu nantu à a basa di dati. Questu ùn hè micca ciò chì vulemu.
Vulemu, cum'è e persone sane, per travaglià cù a basa. Avemu pigliatu è preparatu a nostra dichjarazione una volta, poi eseguimu parechje volte. In fatti, parechje volte - questu hè una volta in tutta a vita di l'applicazioni - sò stati analizati. È usemu u listessu id di dichjarazione in REST differenti. Questu hè u nostru scopu.
Cumu pudemu ottene questu?
Hè assai simplice - ùn hè micca bisognu di chjude dichjarazioni. Scrivemu cusì: "preparate" "eseguite".
Sè avemu lanciatu qualcosa cusì, allora hè chjaru chì qualcosa sboccarà in qualchì locu. Se ùn hè micca chjaru, pudete pruvà. Scrivemu un benchmark chì usa stu metudu simplice. Crea una dichjarazione. L'avemu lanciatu nantu à una versione di u cunduttore è truvamu chì si chjappà abbastanza rapidamente cù a perdita di tutta a memoria chì avia.
Hè chjaru chì tali errori sò facilmente corretti. Ùn parleraghju micca di elli. Ma diceraghju chì a nova versione travaglia assai più veloce. U metudu hè stupidu, ma sempre.
Cumu travaglià bè? Chì avemu bisognu di fà per questu?
In realità, l'applicazioni sempre chjude dichjarazioni. In tutti i libri dicenu di chjude, altrimenti a memoria sguasserà.
È PostgreSQL ùn sapi micca cumu cache e dumande. Hè necessariu chì ogni sessione crea sta cache per ellu stessu.
È ùn vulemu micca perde u tempu in l'analisi.
È cum'è solitu avemu duie opzioni.
A prima opzione hè chì l'avemu pigliata è dicemu chì imbottitemu tuttu in PgSQL. Ci hè una cache quì. Cache tuttu. Risultarà grande. Avemu vistu questu. Avemu 100500 dumande. Ùn funziona micca. Ùn avemu micca d'accordu per trasfurmà e dumande in prucedure manualmente. Innò nò.
Avemu una seconda opzione - pigliate è tagliate noi stessi. Avemu apertu e fonti è cuminciamu à cutà. Avemu vistu è vistu. Hè risultatu chì ùn hè micca cusì difficiule di fà.
Questu hè apparsu in Aostu 2015. Avà ci hè una versione più muderna. È tuttu hè grande. Funziona cusì bè chì ùn cambiamu nunda in l'applicazione. È avemu ancu cessatu di pensà à a direzzione di PgSQL, vale à dì chì questu era abbastanza abbastanza per noi per riduce tutti i costi generali à quasi zero.
In cunsiquenza, e dichjarazioni preparate da u Server sò attivate nantu à a 5a esecuzione per evità di perda memoria in a basa di dati per ogni dumanda una volta.
Pudete dumandà - induve sò i numeri? Chì avete? È quì ùn daraghju micca numeri, perchè ogni dumanda hà u so propiu.
E nostre dumande eranu tali chì avemu passatu circa 20 millisecondi per analizà e dumande OLTP. Ci era 0,5 millisecondi per l'esekzione, 20 millisecondi per l'analisi. Request - 10 KiB di testu, 170 linii di pianu. Questa hè una dumanda OLTP. Richiede 1, 5, 10 linee, a volte più.
Ma ùn vuliamu micca perdi 20 millisecondi in tuttu. L'avemu ridutta à 0. Tuttu hè grande.
Chì pudete piglià da quì ? Sì avete Java, allora pigliate a versione muderna di u driver è rallegrate.
Se parlate una lingua diversa, allora pensate - forse avete ancu bisognu di questu? Perchè da u puntu di vista di a lingua finali, per esempiu, se PL 8 o avete LibPQ, allora ùn hè micca evidenti per voi chì vi passanu u tempu micca in esicuzzioni, in parsing, è questu vale a pena di verificà. Cumu? Tuttu hè liberu.
Salvu chì ci sò errori è qualchi peculiarità. È parleremu di elli avà. A maiò parte sarà di l'archeologia industriale, di ciò chì avemu trovu, di ciò chì avemu trovu.
Se a dumanda hè generata dinamicamente. Si passa. Qualchissia incolla e corde inseme, risultatu in una dumanda SQL.
Perchè hè male ? Hè male perchè ogni volta finisci cù una corda diversa.
È l'hashCode di sta diversa stringa deve esse lettu di novu. Questu hè veramente un compitu di CPU - truvà un testu di dumanda longu ancu in un hash esistente ùn hè micca cusì faciule. Dunque, a cunclusione hè simplice - ùn generà micca richieste. Guardali in una variabile. È rallegra.
Prublemu dopu. Tipi di dati sò impurtanti. Ci sò ORM chì dicenu chì ùn importa micca u tipu di NULL chì ci hè, chì ci sia qualcosa. Se Int, allora dicemu setInt. È se NULL, allora sia sempre VARCHAR. E chì differenza face à a fine ciò chì NULL hè quì? A basa di dati stessu capisce tuttu. È sta stampa ùn viaghja micca.
In pratica, a basa di dati ùn importa micca. Sè vo avete dettu a prima volta chì questu hè un numeru, è a seconda volta chì avete dettu chì hè un VARCHAR, allura hè impussibile di riutilizà dichjarazioni preparate da u Server. È in questu casu, avemu da ricreà a nostra dichjarazione.
Sè eseguite a stessa dumanda, assicuratevi chì i tipi di dati in a vostra colonna ùn sò micca cunfusi. Avete bisognu di attente à NULL. Questu hè un errore cumuni chì avemu avutu dopu avè cuminciatu à aduprà PreparedStatements
Va bè, accesa. Forse anu pigliatu u cunduttore. È a produtividade hè cascata. E cose sò andate male.
Cumu succede questu? Hè questu un bug o una funzione? Sfortunatamente, ùn era micca pussibule di capisce s'ellu hè un bug o una funzione. Ma ci hè un scenariu assai simplice per ripruduce stu prublema. Ella ci hà sbulicatu in modu cumplettamente inaspettatu. È hè custituitu di campionamentu literalmente da una tavola. Avemu, sicuru, avemu avutu più tali richieste. In regula, includenu dui o trè tavule, ma ci hè un tali scenariu di riproduzione. Pigliate ogni versione da a vostra basa di dati è ghjucà.
U puntu hè chì avemu dui culonni, ognunu di i quali hè indexatu. Ci hè un milione di fila in una colonna NULL. È a seconda colonna cuntene solu 20 linee. Quandu eseguimu senza variàbili ligate, tuttu funziona bè.
Se cumminciamu à eseguisce cù variabili ligati, vale à dì, eseguimu u "?" o "$1" per a nostra dumanda, chì finiscemu per ottene?
A prima esecuzione hè cum'è prevista. U sicondu hè un pocu più veloce. Qualcosa era in cache. Terzu, quartu, quintu. Allora bang - è qualcosa cusì. È u peghju hè chì questu succede nantu à a sesta esecuzione. Quale sapia chì era necessariu di fà esattamente sei esecuzioni per capisce quale era u pianu di esecutivu propiu?
Quale hè culpèvule ? Chi hè successu? A basa di dati cuntene ottimisazione. È pare esse ottimizatu per u casu genericu. È, per quessa, à partesi da un certu puntu, cambia à un pianu genericu, chì, sfurtunatamenti, pò esse diversu. Pò esse u listessu, o pò esse diversu. È ci hè un tipu di valore di soglia chì porta à stu cumpurtamentu.
Chì pudete fà? Quì, sicuru, hè più difficiule di assume qualcosa. Ci hè una suluzione simplice chì usemu. Questu hè + 0, OFFSET 0. Di sicuru, cunnosci tali suluzioni. Pigliamu solu è aghjunghje "+0" à a dumanda è tuttu hè bè. Vi mustraraghju dopu.
E ci hè una altra opzione - fighjate i piani più attentamente. U sviluppatore ùn deve micca solu scrive una dumanda, ma ancu dì "spiegà analizà" 6 volte. S'ellu hè 5, ùn funziona micca.
È ci hè una terza opzione - scrivite una lettera à pgsql-hackers. Aghju scrittu, però, ùn hè ancu chjaru se questu hè un bug o una funzione.
Mentre pensemu s'ellu hè un bug o una funzione, correggemu. Pigliemu a nostra dumanda è aghjunghje "+0". Tuttu hè bè. Dui simboli è ùn avete mancu à pensà à cumu hè o ciò chì hè. Assai sèmplice. Semu pruibitu à a basa di dati di utilizà un indice nantu à sta colonna. Ùn avemu micca un indice nantu à a colonna "+0" è questu hè, a basa di dati ùn usa micca l'indici, tuttu hè bè.
Questa hè a regula di 6 spiegà. Avà in e versioni attuali avete da fà 6 volte s'è vo avete variàbili bound. Se ùn avete micca variabili ligate, questu hè ciò chì facemu. È à a fine hè precisamente sta dumanda chì falla. Ùn hè micca una cosa complicata.
Sembra, quantu hè pussibule? Un bug quì, un bug quì. In fatti, u bug hè in ogni locu.
Fighjemu un ochju più vicinu. Per esempiu, avemu dui schemi. Schema A cù tavula S è diagramma B cù tavula S. Query - selezziunate dati da una tavula. Chì averemu in stu casu? Averemu un errore. Averemu tuttu ciò chì sopra. A regula hè - un bug hè in ogni locu, averemu tuttu ciò chì sopra.
Avà a quistione hè: "Perchè?" Sembra chì ci hè una ducumentazione chì se avemu un schema, allora ci hè una variabile "search_path" chì ci dice induve circà a tavola. Sembra chì ci hè una variabile.
Chì ghjè u prublema ? U prublema hè chì e dichjarazioni preparate da u servitore ùn suspettanu micca chì search_path pò esse cambiatu da qualchissia. Stu valore resta, per esse, custanti per a basa di dati. È certi parti ùn ponu micca piglià novi significati.
Di sicuru, questu dipende da a versione chì site a prova. Dipende da quantu seriamente i vostri tavulini sò diffirenti. È a versione 9.1 solu eseguirà e vechji dumande. E versioni novi ponu catturà u bug è vi dicenu chì avete un bug.
Cumu trattà? Ci hè una ricetta simplice - ùn fate micca. Ùn ci hè bisognu di cambià search_path mentre l'applicazione hè in esecuzione. Se cambiassi, hè megliu creà una nova cunnessione.
Pudete discutiri, vale à dì apre, discute, aghjunghje. Forsi pudemu cunvince i sviluppatori di basa di dati chì quandu qualchissia cambia un valore, a basa di dati deve dì à u cliente nantu à questu: "Fighjate, u vostru valore hè statu aghjurnatu quì. Forse avete bisognu di resettate e dichjarazioni è ricreate?" Avà a basa di dati si cumporta in sicretu è ùn informa in ogni modu chì e dichjarazioni anu cambiatu in qualchì locu.
È enfatizaraghju novu - questu hè qualcosa chì ùn hè micca tipicu per Java. Videremu a stessa cosa in PL / pgSQL unu à unu. Ma ci sarà ripruduciutu.
Pruvemu un pocu di più selezzione di dati. Avemu sceltu è sceglie. Avemu un tavulinu cù un milione di fila. Ogni linea hè un kilobyte. Circa un gigabyte di dati. È avemu una memoria di travagliu in a macchina Java di 128 megabytes.
Avemu, cum'è ricumandemu in tutti i libri, usemu u prucessu di flussu. Vale à dì, avemu apertu resultSet è leghje i dati da quì à pocu à pocu. Funcionerà? Cascà da a memoria ? Leghjite un pocu ? Fidemu in a basa di dati, cunfidemu in Postgres. Ùn avemu micca cridutu. Caderemu fora di a memoria ? Quale hà sperimentatu OutOfMemory? Quale hà sappiutu di riparà dopu? Qualchissia hà sappiutu di riparà.
Sì avete un milione di fila, ùn pudete micca solu sceglie è sceglie. OFFSET/LIMIT hè necessariu. Quale hè per questa opzione? E quale hè in favore di ghjucà cù autoCommit ?
Quì, cum'è di solitu, l'opzione più inespettata risulta esse curretta. È se di colpu disattivate l'autoCommit, vi aiuterà. Perchè hè questu? A scienza ùn sapi micca di questu.
Ma di manera predeterminata, tutti i clienti chì si cunnettanu à una basa di dati Postgres piglianu tutti i dati. PgJDBC ùn hè micca eccezzioni in questu sensu; selezziunate tutte e fila.
Ci hè una variazione nantu à u tema FetchSize, vale à dì pudete dì à u livellu di una dichjarazione separata chì quì, per piacè selezziunate e dati da 10, 50. Ma questu ùn hè micca travagliatu finu à chì disattiveghjanu autoCommit. Disattivatu autoCommit - cumencia à travaglià.
Ma passà per u codice è stabilisce setFetchSize in ogni locu hè inconveniente. Dunque, avemu fattu un paràmetru chì dicerà u valore predeterminatu per tutta a cunnessione.
Hè ciò chì avemu dettu. U paràmetru hè statu cunfiguratu. È chì avemu avutu ? Se selezziunà picculi quantità, se, per esempiu, selezziunate 10 fila à u mumentu, allora avemu un grande spesa generale. Per quessa, stu valore deve esse stabilitu à circa un centu.
Ideale, sicuru, avete sempre à amparà à limità in byte, ma a ricetta hè questu: stabilisce defaultRowFetchSize à più di centu è esse felice.
Andemu à inserisce dati. L'inserimentu hè più faciule, ci sò diverse opzioni. Per esempiu, INSERT, VALUES. Questa hè una bona opzione. Pudete dì "INSERT SELECT". In pratica hè a stessa cosa. Ùn ci hè micca differenza in u rendiment.
I libri dicenu chì avete bisognu di eseguisce una dichjarazione Batch, i libri dicenu chì pudete eseguisce cumandamenti più cumplessi cù parechje parentesi. È Postgres hà una funzione maravigliosa - pudete fà COPIA, vale à dì fà più veloce.
Se misurate, pudete fà torna qualchì scuperta interessante. Cumu vulemu chì questu travagliu? Vulemu micca analizà è micca eseguisce cumandamenti inutili.
In pratica, TCP ùn ci permette micca di fà questu. Se u cliente hè occupatu à mandà una dumanda, allora a basa di dati ùn leghje micca e dumande in tentativu di mandà risposte. U risultatu finale hè chì u cliente aspetta chì a basa di dati leghje a dumanda, è a basa di dati aspetta chì u cliente leghje a risposta.
È dunque u cliente hè obligatu à mandà periodicamente un pacchettu di sincronizazione. Interazioni di rete extra, perdita di tempu extra.
E più l'aghjunghjemu, u peghju hè. U cunduttore hè abbastanza pessimistu è aghjunghjenu abbastanza spessu, circa una volta ogni 200 linee, secondu a dimensione di e linee, etc.
Succede chì corregge una sola linea è tuttu accelerà 10 volte. Si passa. Perchè? Cum'è di solitu, una constante cum'è questu hè digià stata utilizata in qualchì locu. È u valore "128" significava micca aduprà batching.
Hè bonu chì questu ùn hè micca inclusu in a versione ufficiale. Scopre prima di a liberazione. Tutti i significati chì dugnu sò basati nantu à versioni muderni.
Pruvemu. Misuremu InsertBatch simplice. Misuremu InsertBatch parechje volte, vale à dì a stessa cosa, ma ci sò assai valori. Mossa dilicata. Micca tutti ponu fà questu, ma hè un muvimentu cusì simplice, assai più faciule ch'è COPIA.
Pudete fà COPIA.
È pudete fà questu nantu à strutture. Dichjarà u tipu predeterminatu di l'Usuariu, passa l'array è INSERT direttamente à a tavola.
Se apre u ligame: pgjdbc/ubenchmsrk/InsertBatch.java, allora stu codice hè in GitHub. Pudete vede specificamente ciò chì e dumande sò generate quì. Ùn importa micca.
Avemu lanciatu. È a prima cosa chì avemu capitu era chì ùn aduprà micca batch hè simplicemente impussibile. Tutte l'opzioni di batching sò zero, vale à dì chì u tempu d'esekzione hè praticamente zero cumparatu cù una esecuzione unica.
Inseremu dati. Hè una tavola assai simplice. Trè culonni. È chì vedemu quì ? Avemu vistu chì tutte e trè di queste opzioni sò apprussimatamente paragunabili. E COPY hè, sicuru, megliu.
Questu hè quandu inserimu pezzi. Quandu avemu dettu chì un valore VALORI, dui VALORI VALORI, trè VALORI VALORI, o avemu indicatu 10 di elli siparati da una virgola. Questu hè solu horizontale avà. 1, 2, 4, 128. Si pò vede chì u Batch Insert, chì hè disegnatu in turchinu, li fa sentu assai megliu. Questu hè, quandu inserisci unu à un tempu o ancu quandu inserisci quattru à un tempu, diventa duie volte più bonu, solu perchè avemu crammed un pocu più in VALORI. Meno operazioni EXECUTE.
L'usu di COPIA nantu à i picculi volumi ùn hè assai promettente. Ùn aghju mancu tiratu nantu à i primi dui. Vannu à u celu, vale à dì sti numeri verdi per COPIA.
COPY deve esse usatu quandu avete almenu un centu di fila di dati. U overhead di apertura di sta cunnessione hè grande. È, per esse onestu, ùn aghju micca scavatu in questa direzzione. Aghju ottimisatu Batch, ma micca COPY.
Chì facemu dopu ? Avemu pruvatu. Avemu capitu chì avemu bisognu di usà sia strutture o un battu intelligente chì combina parechji significati.
Chì duvete piglià da u rapportu d'oghje ?
- PreparedStatement hè u nostru tuttu. Questu dà assai per a produtividade. Produce un grande flop in l'unguentu.
- È avete bisognu di fà ANALISI 6 volte.
- È avemu bisognu di dilute OFFSET 0, è trucchi cum'è + 0 per corregge u percentualità restante di e nostre dumande problematiche.
Source: www.habr.com