Mi sugestas vin legi la transskribon de la frua 2016 raporto de Vladimir Sitnikov "PostgreSQL kaj JDBC elpremas la tutan sukon"
Bonan posttagmezon Mia nomo estas Vladimir Sitnikov. Mi laboras por NetCracker dum 10 jaroj. Kaj mi estas plejparte en produktiveco. Ĉio rilata al Java, ĉio rilata al SQL estas tio, kion mi amas.
Kaj hodiaŭ mi parolos pri tio, kion ni renkontis en la kompanio kiam ni komencis uzi PostgreSQL kiel datumbaza servilo. Kaj ni plejparte laboras kun Java. Sed tio, kion mi rakontos al vi hodiaŭ, ne temas nur pri Java. Kiel la praktiko montris, tio okazas ankaŭ en aliaj lingvoj.
Ni parolos:
- pri datumprovado.
- Pri konservado de datumoj.
- Kaj ankaŭ pri agado.
- Kaj pri la subakvaj rastiloj, kiuj estas tie enterigitaj.
Ni komencu per simpla demando. Ni elektas unu vicon el la tabelo bazita sur la ĉefa ŝlosilo.
La datumbazo situas sur la sama gastiganto. Kaj ĉio ĉi terkultivado bezonas 20 milisekundojn.
Ĉi tiuj 20 milisekundoj estas multe. Se vi havas 100 tiajn petojn, tiam vi pasigas tempon por sekundo trarulante ĉi tiujn petojn, t.e. ni perdas tempon.
Ni ne ŝatas fari ĉi tion kaj rigardi kion la bazo proponas al ni por ĉi tio. La datumbazo ofertas al ni du eblojn por plenumi demandojn.
La unua opcio estas simpla peto. Kio estas bona pri ĝi? La fakto ke ni prenas ĝin kaj sendas ĝin, kaj nenio pli.
La datumbazo ankaŭ havas altnivelan demandon, kiu estas pli malfacila, sed pli funkcia. Vi povas aparte sendi peton por analizado, ekzekuto, varia ligado ktp.
Super etendita demando estas io, kion ni ne kovros en la nuna raporto. Ni, eble, volas ion el la datumbazo kaj estas dezirlisto kiu estis formita en iu formo, t.e. ĉi tio estas kion ni volas, sed ĝi estas neebla nun kaj en la venonta jaro. Do ni ĵus registris ĝin kaj ni ĉirkaŭos skuante la ĉefajn homojn.
Kaj kion ni povas fari estas simpla konsulto kaj etendita konsulto.
Kio estas speciala pri ĉiu aliro?
Simpla demando estas bona por unufoja ekzekuto. Iam farita kaj forgesita. Kaj la problemo estas, ke ĝi ne subtenas la binaran datumformaton, t.e. ĝi ne taŭgas por iuj alt-efikecaj sistemoj.
Plilongigita konsulto - permesas vin ŝpari tempon pri analizado. Jen kion ni faris kaj komencis uzi. Ĉi tio vere, vere helpis nin. Estas ne nur ŝparaĵoj pri analizado. Estas ŝparaĵoj pri transdono de datumoj. Transdono de datumoj en binara formato estas multe pli efika.
Ni plu praktiku. Jen kiel aspektas tipa aplikaĵo. Ĝi povus esti Java, ktp.
Ni kreis deklaron. Efektivigis la komandon. Kreita proksime. Kie estas la eraro ĉi tie? Kio estas la problemo? Nedankinde. Jen kion ĝi diras en ĉiuj libroj. Jen kiel ĝi devus esti skribita. Se vi volas maksimuman rendimenton, skribu tiel.
Sed praktiko montris, ke tio ne funkcias. Kial? Ĉar ni havas "proksiman" metodon. Kaj kiam ni faras tion, el la datumbaza vidpunkto rezultas, ke ĝi estas kiel fumanto laboranta kun datumbazo. Ni diris "PARSE EXECUTE DEALLOCATE".
Kial ĉiu ĉi tiu ekstra kreado kaj malŝarĝo de deklaroj? Neniu bezonas ilin. Sed kio kutime okazas en PreparedStatements estas, ke kiam ni fermas ilin, ili fermas ĉion en la datumbazo. Ĉi tio ne estas kion ni volas.
Ni volas, kiel sanaj homoj, labori kun la bazo. Ni prenis kaj preparis nian deklaron unufoje, poste ni plenumas ĝin multfoje. Fakte, multfoje - ĉi tio estas unufoje en la tuta vivo de aplikaĵoj - ili estis analizitaj. Kaj ni uzas la saman deklaron-identigilon sur malsamaj REST-oj. Jen nia celo.
Kiel ni povas atingi ĉi tion?
Ĝi estas tre simpla - ne necesas fermi deklarojn. Ni skribas ĝin tiel: "prepari" "ekzekuti".
Se ni lanĉas ion tian, tiam estas klare, ke io superfluos ie. Se ĝi ne estas klara, vi povas provi ĝin. Ni skribu komparnormon, kiu uzas ĉi tiun simplan metodon. Kreu deklaron. Ni lanĉas ĝin sur iu versio de la ŝoforo kaj trovas ke ĝi frakasas sufiĉe rapide kun la perdo de la tuta memoro kiun ĝi havis.
Estas klare, ke tiaj eraroj estas facile korekteblaj. Mi ne parolos pri ili. Sed mi diros, ke la nova versio funkcias multe pli rapide. La metodo estas stulta, sed tamen.
Kiel labori ĝuste? Kion ni devas fari por ĉi tio?
En realeco, aplikoj ĉiam fermas deklarojn. En ĉiuj libroj oni diras fermi ĝin, alie la memoro likos.
Kaj PostgreSQL ne scias kiel kaŝmemori demandojn. Necesas, ke ĉiu sesio kreu ĉi tiun kaŝmemoron por si.
Kaj ni ankaŭ ne volas perdi tempon per analizado.
Kaj kiel kutime ni havas du eblojn.
La unua opcio estas, ke ni prenu ĝin kaj diru, ke ni envolvu ĉion en PgSQL. Estas kaŝmemoro tie. Ĝi konservas ĉion. Ĝi rezultos bonega. Ni vidis ĉi tion. Ni havas 100500 petojn. Ne funkcias. Ni ne konsentas transformi petojn en procedurojn mane. Ne ne.
Ni havas duan eblon - prenu ĝin kaj tranĉu ĝin mem. Ni malfermas la fontojn kaj komencas tranĉi. Ni vidis kaj vidis. Montriĝis, ke ĝi ne estas tiel malfacile fari.
Ĉi tio aperis en aŭgusto 2015. Nun ekzistas pli moderna versio. Kaj ĉio estas bonega. Ĝi funkcias tiel bone, ke ni ŝanĝas nenion en la aplikaĵo. Kaj ni eĉ ĉesis pensi en la direkto de PgSQL, t.e. ĉi tio sufiĉis por ke ni reduktu ĉiujn superkostojn al preskaŭ nulo.
Sekve, servil-pretaj deklaroj estas aktivigitaj je la 5-a ekzekuto por eviti malŝpari memoron en la datumbazo je ĉiu unufoja peto.
Vi povas demandi - kie estas la nombroj? Kion vi ricevas? Kaj ĉi tie mi ne donos nombrojn, ĉar ĉiu peto havas sian propran.
Niaj demandoj estis tiaj, ke ni elspezis ĉirkaŭ 20 milisekundojn por analizado de OLTP-demandoj. Estis 0,5 milisekundoj por ekzekuto, 20 milisekundoj por analizado. Peto - 10 KiB da teksto, 170 linioj de plano. Ĉi tio estas OLTP-peto. Ĝi petas 1, 5, 10 liniojn, foje pli.
Sed ni tute ne volis malŝpari 20 milisekundojn. Ni reduktis ĝin al 0. Ĉio estas bonega.
Kion vi povas forporti de ĉi tie? Se vi havas Java, tiam vi prenas la modernan version de la ŝoforo kaj ĝojas.
Se vi parolas alian lingvon, tiam pensu - eble vi ankaŭ bezonas ĉi tion? Ĉar el la vidpunkto de la fina lingvo, ekzemple, se PL 8 aŭ vi havas LibPQ, tiam ne estas evidente al vi, ke vi pasigas tempon ne por ekzekuto, pri analizado, kaj tion indas kontroli. Kiel? Ĉio estas senpaga.
Krom ke estas eraroj kaj iuj proprecoj. Kaj ni parolos pri ili nun. Plejparte temas pri industria arkeologio, pri tio, kion ni trovis, kion ni renkontis.
Se la peto estas generita dinamike. Ĝi okazas. Iu kungluas la ŝnurojn, rezultigante SQL-demandon.
Kial li estas malbona? Estas malbone ĉar ĉiufoje ni finiĝas kun malsama ŝnuro.
Kaj la hashCode de ĉi tiu malsama ĉeno devas esti legita denove. Ĉi tio vere estas CPU-tasko - trovi longan petan tekston en eĉ ekzistanta hash ne estas tiel facila. Tial, la konkludo estas simpla - ne generu petojn. Konservu ilin en unu variablo. Kaj ĝoju.
Sekva problemo. Datumtipoj estas gravaj. Estas ORM-oj kiuj diras, ke ne gravas kia NULL ekzistas, estu ia. Se Int, tiam ni diras setInt. Kaj se NULL, tiam estu ĉiam VARCHAR. Kaj kian diferencon faras finfine kio NULL estas tie? La datumbazo mem komprenos ĉion. Kaj ĉi tiu bildo ne funkcias.
En la praktiko, la datumbazo tute ne zorgas. Se vi diris la unuan fojon, ke ĉi tio estas nombro, kaj la duan fojon vi diris, ke ĝi estas VARCHAR, tiam estas neeble reuzi asertojn preparitajn de Servilo. Kaj en ĉi tiu kazo, ni devas rekrei nian deklaron.
Se vi plenumas la saman demandon, certigu, ke la datumtipoj en via kolumno ne estas konfuzitaj. Vi devas atenti pri NULL. Ĉi tio estas ofta eraro, kiun ni havis post kiam ni komencis uzi PreparedStatements
Bone, ŝaltita. Eble ili prenis la ŝoforon. Kaj produktiveco falis. Aferoj malboniĝis.
Kiel tio okazas? Ĉu ĉi tio estas cimo aŭ funkcio? Bedaŭrinde, ne eblis kompreni ĉu ĉi tio estas cimo aŭ funkcio. Sed estas tre simpla scenaro por reprodukti ĉi tiun problemon. Ŝi tute neatendite embuskis nin. Kaj ĝi konsistas el specimenigo laŭvorte el unu tablo. Ni, kompreneble, havis pli da tiaj petoj. Kiel regulo, ili inkludis du aŭ tri tablojn, sed ekzistas tia reprodukta scenaro. Prenu ajnan version el via datumbazo kaj ludu ĝin.
La punkto estas, ke ni havas du kolumnojn, ĉiu el kiuj estas indeksita. Estas miliono da vicoj en unu NULA kolumno. Kaj la dua kolumno enhavas nur 20 liniojn. Kiam ni plenumas sen ligitaj variabloj, ĉio funkcias bone.
Se ni komencas ekzekuti kun ligitaj variabloj, t.e. ni ekzekutas la "?" aŭ "$1" por nia peto, kion ni finfine ricevas?
La unua ekzekuto estas kiel atendita. La dua estas iom pli rapida. Io estis kaŝmemorigita. Tria, kvara, kvina. Tiam frapu — kaj io tia. Kaj la plej malbona afero estas, ke tio okazas ĉe la sesa ekzekuto. Kiu sciis, ke necesas fari ekzakte ses ekzekutojn por kompreni, kio estas la efektiva ekzekutplano?
Kiu estas kulpa? Kio okazis? La datumbazo enhavas optimumigon. Kaj ĝi ŝajnas esti optimumigita por la ĝenerala kazo. Kaj, sekve, ekde iu momento, ŝi ŝanĝas al ĝenerala plano, kiu, bedaŭrinde, povas montriĝi malsama. Ĝi povas rezulti esti la sama, aŭ ĝi povas esti malsama. Kaj estas ia sojla valoro, kiu kondukas al ĉi tiu konduto.
Kion vi povas fari pri ĝi? Ĉi tie, kompreneble, estas pli malfacile supozi ion ajn. Estas simpla solvo, kiun ni uzas. Ĉi tio estas +0, OFFSET 0. Certe vi konas tiajn solvojn. Ni nur prenas ĝin kaj aldonu "+0" al la peto kaj ĉio estas en ordo. Mi montros al vi poste.
Kaj ekzistas alia eblo - rigardu la planojn pli atente. La programisto devas ne nur skribi peton, sed ankaŭ diri "klarigi analizi" 6 fojojn. Se ĝi estas 5, ĝi ne funkcios.
Kaj ekzistas tria opcio - skribu leteron al pgsql-hackers. Mi skribis, tamen, ankoraŭ ne estas klare ĉu ĉi tio estas cimo aŭ funkcio.
Dum ni pensas, ĉu ĉi tio estas cimo aŭ funkcio, ni riparu ĝin. Ni prenu nian peton kaj aldonu "+0". Ĉio estas en ordo. Du simboloj kaj vi eĉ ne devas pensi pri kiel ĝi estas aŭ kio ĝi estas. Tre simpla. Ni simple malpermesis al la datumbazo uzi indekson en ĉi tiu kolumno. Ni ne havas indekson sur la kolumno "+0" kaj jen ĝi, la datumbazo ne uzas la indekson, ĉio estas en ordo.
Ĉi tio estas la regulo de 6 klarigoj. Nun en nunaj versioj vi devas fari ĝin 6 fojojn se vi havas ligitajn variablojn. Se vi ne havas ligitajn variablojn, tion ni faras. Kaj finfine ĝuste tiu ĉi peto malsukcesas. Ĝi ne estas malfacila afero.
Ŝajnus, kiom eblas? Cimo ĉi tie, cimo tie. Fakte, la cimo estas ĉie.
Ni rigardu pli detale. Ekzemple, ni havas du skemojn. Skemo A kun tabelo S kaj diagramo B kun tabelo S. Demando - elektu datumojn de tabelo. Kion ni havos en ĉi tiu kazo? Ni havos eraron. Ni havos ĉion supre. La regulo estas - cimo estas ĉie, ni havos ĉion supre.
Nun la demando estas: "Kial?" Ŝajnus, ke ekzistas dokumentado, ke se ni havas skemon, tiam ekzistas variablo "serĉo_voja" kiu diras al ni kie serĉi la tabelon. Ŝajnus, ke ekzistas variablo.
Kio estas la problemo? La problemo estas, ke servilpretaj deklaroj ne suspektas, ke serĉo_vojo povas esti ŝanĝita de iu. Ĉi tiu valoro restas kvazaŭ konstanta por la datumbazo. Kaj iuj partoj eble ne prenas novajn signifojn.
Kompreneble, tio dependas de la versio, kiun vi testas. Dependas de kiom grave viaj tabloj diferencas. Kaj versio 9.1 simple plenumos la malnovajn petojn. Novaj versioj povas kapti la cimon kaj diri al vi, ke vi havas cimon.
Kiel trakti ĝin? Estas simpla recepto - ne faru ĝin. Ne necesas ŝanĝi search_path dum la aplikaĵo funkcias. Se vi ŝanĝas, estas pli bone krei novan konekton.
Vi povas diskuti, t.e. malfermi, diskuti, aldoni. Eble ni povas konvinki la datumbazajn programistojn, ke kiam iu ŝanĝas valoron, la datumbazo devas informi la klienton pri ĉi tio: "Rigardu, via valoro estas ĝisdatigita ĉi tie. Eble vi bezonas restarigi la deklarojn kaj rekrei ilin?" Nun la datumbazo kondutas sekrete kaj neniel raportas, ke la deklaroj ŝanĝiĝis ie interne.
Kaj mi denove emfazos - tio estas io, kio ne estas tipa por Java. Ni vidos la samon en PL/pgSQL unu al unu. Sed ĝi estos reproduktita tie.
Ni provu plian elektadon de datumoj. Ni elektas kaj elektas. Ni havas tabelon kun miliono da vicoj. Ĉiu linio estas kilobajto. Proksimume gigabajto da datumoj. Kaj ni havas labormemoron en la Java-maŝino de 128 megabajtoj.
Ni, kiel rekomendite en ĉiuj libroj, uzas flutraktadon. Tio estas, ni malfermas resultSet kaj legas la datumojn de tie iom post iom. Ĉu ĝi funkcios? Ĉu ĝi falos de memoro? Ĉu vi legos iomete? Ni fidu al la datumbazo, ni fidu al Postgres. Ni ne kredas ĝin. Ĉu ni falos el Memoro? Kiu spertis OutOfMemory? Kiu sukcesis ripari ĝin post tio? Iu sukcesis ripari ĝin.
Se vi havas milionon da vicoj, vi ne povas simple elekti kaj elekti. OFFSET/LIMIT estas postulata. Kiu estas por ĉi tiu opcio? Kaj kiu estas favora ludi kun autoCommit?
Ĉi tie, kiel kutime, la plej neatendita opcio montriĝas ĝusta. Kaj se vi subite malŝaltas autoCommit, ĝi helpos. Kial estas tio? Scienco ne scias pri tio.
Sed defaŭlte, ĉiuj klientoj konektantaj al Postgres-datumbazo alportas la tutan datumon. PgJDBC ne estas escepto ĉi-rilate; ĝi elektas ĉiujn vicojn.
Estas variaĵo pri la temo FetchSize, t.e. vi povas diri je la nivelo de aparta deklaro, ke ĉi tie, bonvolu elekti datumojn antaŭ 10, 50. Sed ĉi tio ne funkcias ĝis vi malŝaltas aŭtokommit. Malŝaltita autoCommit - ĝi ekfunkcias.
Sed trairi la kodon kaj agordi setFetchSize ĉie estas maloportuna. Tial ni faris agordon, kiu diros la defaŭltan valoron por la tuta konekto.
Tion ni diris. La parametro estas agordita. Kaj kion ni ricevis? Se ni elektas malgrandajn kvantojn, se, ekzemple, ni elektas 10 vicojn samtempe, tiam ni havas tre grandajn superkostojn. Tial ĉi tiu valoro devas esti agordita al ĉirkaŭ cent.
Ideale, kompreneble, vi ankoraŭ devas lerni kiel limigi ĝin en bajtoj, sed la recepto estas jena: agordu defaultRowFetchSize al pli ol cent kaj estu feliĉa.
Ni pluiru al enmeti datumojn. Enmeto estas pli facila, estas malsamaj ebloj. Ekzemple, INSERT, VALORES. Ĉi tio estas bona elekto. Vi povas diri "INSERT SELECT". En la praktiko estas la sama afero. Ne estas diferenco en rendimento.
Libroj diras, ke vi devas efektivigi Batch-deklaron, libroj diras, ke vi povas plenumi pli kompleksajn komandojn per pluraj krampoj. Kaj Postgres havas mirindan funkcion - vi povas fari KOPION, t.e. fari ĝin pli rapide.
Se vi mezuras ĝin, vi denove povas fari kelkajn interesajn malkovrojn. Kiel ni volas, ke ĉi tio funkciu? Ni volas ne analizi kaj ne ekzekuti nenecesajn komandojn.
En praktiko, TCP ne permesas al ni fari tion. Se la kliento estas okupata sendante peton, tiam la datumbazo ne legas la petojn en provoj sendi al ni respondojn. La fina rezulto estas, ke la kliento atendas ke la datumbazo legu la peton, kaj la datumbazo atendas ke la kliento legu la respondon.
Kaj tial la kliento estas devigita periode sendi sinkronigan paketon. Kromaj retaj interagoj, ekstra tempoperdo.
Kaj ju pli ni aldonas ilin, des pli malbona ĝi fariĝas. La ŝoforo estas sufiĉe pesimisma kaj sufiĉe ofte aldonas ilin, proksimume unufoje ĉiujn 200 liniojn, depende de la grandeco de la linioj, ktp.
Okazas, ke vi korektas nur unu linion kaj ĉio rapidiĝos 10 fojojn. Ĝi okazas. Kial? Kiel kutime, konstanto tia jam estis uzata ie. Kaj la valoro "128" signifis ne uzi batadon.
Estas bone, ke ĉi tio ne estis inkluzivita en la oficiala versio. Malkovrita antaŭ ol la liberigo komenciĝis. Ĉiuj signifoj, kiujn mi donas, baziĝas sur modernaj versioj.
Ni provu ĝin. Ni mezuras InsertBatch simpla. Ni mezuras InsertBatch plurfoje, t.e. la sama afero, sed estas multaj valoroj. Delikata movo. Ne ĉiuj povas fari tion, sed ĝi estas tiel simpla movo, multe pli facila ol KOPIO.
Vi povas fari KOPION.
Kaj vi povas fari ĉi tion sur strukturoj. Deklaru defaŭltan tipon de Uzanto, pasu tabelon kaj INSERT rekte al la tabelo.
Se vi malfermas la ligilon: pgjdbc/ubenchmsrk/InsertBatch.java, tiam ĉi tiu kodo estas en GitHub. Vi povas vidi specife, kiaj petoj estas generitaj tie. Ne gravas.
Ni lanĉis. Kaj la unua afero, kiun ni rimarkis, estis, ke ne uzi batch estas simple neeble. Ĉiuj bataj opcioj estas nul, t.e. la ekzekuttempo estas preskaŭ nul kompare kun unufoja ekzekuto.
Ni enmetas datumojn. Ĝi estas tre simpla tablo. Tri kolumnoj. Kaj kion ni vidas ĉi tie? Ni vidas, ke ĉiuj tri ĉi tiuj opcioj estas proksimume kompareblaj. Kaj COPY estas, kompreneble, pli bona.
Jen kiam ni enmetas pecojn. Kiam ni diris, ke unu VALOJ valoro, du VALORO-valoroj, tri VALORO-valoroj, aŭ ni indikis 10 el ili apartigitaj per komo. Ĉi tio estas nur horizontala nun. 1, 2, 4, 128. Oni povas vidi, ke la Batch Insert, kiu estas desegnita en blua, faras lin senti multe pli bone. Tio estas, kiam oni enmetas po unu aŭ eĉ kiam oni enmetas po kvar, ĝi fariĝas duoble pli bona, simple ĉar ni enŝtopiĝis iom pli en VALOJN. Malpli da operacioj EXECUTE.
Uzi COPY sur malgrandaj volumoj estas ege nepromesplena. Mi eĉ ne tiris la unuajn du. Ili iras al la ĉielo, tio estas, ĉi tiuj verdaj nombroj por KOPIO.
KOPIO devus esti uzata kiam vi havas almenaŭ cent vicoj da datumoj. La supra kosto por malfermi ĉi tiun konekton estas granda. Kaj, sincere, mi ne fosis en ĉi tiu direkto. Mi optimumigis Batch, sed ne COPY.
Kion ni faru poste? Ni provis ĝin. Ni komprenas, ke ni devas uzi aŭ strukturojn aŭ lertan baĥton, kiu kombinas plurajn signifojn.
Kion vi forprenu el la hodiaŭa raporto?
- PreparedStatement estas nia ĉio. Ĉi tio donas multon por produktiveco. Ĝi produktas grandan fiaskon en la ungvento.
- Kaj vi devas fari EXPLIGI ANALIZ 6 fojojn.
- Kaj ni devas dilui OFFSET 0, kaj lertaĵojn kiel +0 por korekti la restantan procenton de niaj problemaj demandoj.
fonto: www.habr.com