Indlela yezimboni yokulungisa i-PostgreSQL: izivivinyo nge-database. " Nikolay Samokhvalov

Ngiphakamisa ukuthi ufunde okulotshiwe kombiko ka-Nikolai Samokhvalov "Indlela yezimboni yokulungisa i-PostgreSQL: ukuhlolwa kolwazi"

Shared_buffers = 25% - kuningi noma kuncane? Noma kunjalo nje? Wazi kanjani uma lesi sincomo - kunalokho siphelelwe yisikhathi - sifanelekile esimweni sakho esithile?

Isikhathi sokubhekana nendaba yokukhetha amapharamitha we-postgresql.conf "njengomuntu omdala." Hhayi ngosizo "lwezishuna ezizenzakalelayo" ezingaboni noma izeluleko eziphelelwe yisikhathi ezivela kuma-athikili namabhulogi, kodwa ngokusekelwe kokuthi:

1. izivivinyo eziqinisekiswe ngokuqinile kulwazi olugciniwe, olwenziwa ngokuzenzakalelayo, ngobuningi nangaphansi kwezimo ezisondele kakhulu “zokulwa” nalezo,
2. ukuqonda okujulile kwezici ze-DBMS ne-OS.

Ukusebenzisa uNancy CLI (https://gitlab.com/postgres.ai/nancy), sizobheka isibonelo esithile - ama-shared_buffers adume kabi - ezimeni ezihlukene, kumaphrojekthi ahlukene futhi sizame ukuthola ukuthi singakhetha kanjani isilungiselelo esilungile sengqalasizinda yethu, isizindalwazi kanye nomthwalo.

Sizokhuluma ngokuhlolwa ngezinqolobane zolwazi. Lena indaba ethatha isikhathi esingaphezudlwana kwezinyanga eziyisithupha.

Kancane ngami. Zizwa nge-Postgres iminyaka engaphezu kwe-14. Izinkampani eziningi zokuxhumana nabantu sezisungule. I-Postgres yayisetshenziswa futhi isetshenziswa yonke indawo.

Futhi iqembu le-RuPostgres ku-Meetup, indawo yesi-2 emhlabeni. Kancane kancane sisondela kubantu abayizi-2. RuPostgres.org.

Futhi kuma-PC wezingqungquthela ezahlukahlukene, kufaka phakathi i-Highload, nginomthwalo wemininingwane yolwazi, ikakhulukazi ama-Postgres kusukela ekuqaleni.

Futhi eminyakeni embalwa edlule, ngiqale kabusha umkhuba wami wokubonisana we-Postgres izikhathi eziyi-11 ukusuka lapha.

Futhi ngenkathi ngenza lokhu eminyakeni embalwa edlule, ngaba nekhefu emsebenzini wezandla osebenzayo nge-Postgres, mhlawumbe kusukela ngo-2010. Ngimangele ukuthi kuncane kangakanani uguquko lwendlela yokusebenza ye-DBA, nokuthi ungakanani umsebenzi wezandla osadinga ukusetshenziswa. Futhi ngokushesha ngacabanga ukuthi kukhona okungahambi kahle lapha, ngidinga ukwenza ngokuzenzakalelayo okuningi kwakho konke.

Futhi njengoba konke kwakukude, iningi lamakhasimende lalisemafini. Futhi okuningi sekuvele ku-othomathikhi, kusobala. Okuningi ngalokhu kamuva. Okusho ukuthi, konke lokhu kubangele umbono wokuthi kufanele kube nenani lamathuluzi, okungukuthi, uhlobo oluthile lwesikhulumi oluzokwenza ngokuzenzakalelayo cishe zonke izenzo ze-DBA ukuze inani elikhulu lemininingwane likwazi ukuphathwa.

Lo mbiko ngeke uhlanganise:

• “Izinhlamvu zesiliva” nezitatimende ezinjengokuthi - setha u-8 GB noma u-25% wama-shared_buffers futhi uzolunga. Ngeke kube nokuningi mayelana nama-shared_buffers.
• Hardcore "innards".

Kuzokwenzekani?

• Kuzoba nezimiso zokuthuthukisa esizisebenzisayo nesizithuthukisayo. Kuzoba khona zonke izinhlobo zemibono ezovela endleleni kanye namathuluzi ahlukahlukene esiwadala kakhulu kumthombo ovulekile, okungukuthi senza isisekelo kokuthi Umthombo Ovulekile. Ngaphezu kwalokho, sinamathikithi, konke ukuxhumana kuwumthombo ovulekile. Ungabona esikwenzayo manje, kuzoba yini ekukhishweni okulandelayo, njll.
• Kuzophinde kube nesipiliyoni sokusebenzisa lezi zimiso, lawa mathuluzi ezinkampanini eziningi: kusukela kwabaqalayo abancane kuya ezinkampanini ezinkulu.

Kwenzeka kanjani konke lokhu?

Okokuqala, umsebenzi oyinhloko we-DBA, ngaphezu kokuqinisekisa ukudalwa kwezimo, ukuthunyelwa kwezipele, njll., ukuthola izingqinamba nokwandisa ukusebenza kahle.

Manje isethwe kanje. Sibheka ukuqapha, kukhona esikubonayo, kodwa sishoda ngemininingwane ethile. Siqala ukumba ngokucophelela, ngokuvamile ngezandla zethu, futhi siqonde ukuthi yini okufanele siyenze ngayo ngandlela thile.

Futhi kunezindlela ezimbili. I-Pg_stat_statements iyisixazululo esizenzakalelayo sokuhlonza imibuzo ehamba kancane. Nokuhlaziywa kwamalogi we-Postgres usebenzisa i-pgBadger.

Indlela ngayinye inezinkinga ezinkulu. Endleleni yokuqala, silahle wonke amapharamitha. Futhi uma sibona amaqembu KHETHA * KUSUKA etafuleni lapho ikholomu ilingana nokuthi "?" noma u-“$” kusukela ku-Postgres 10. Asazi ukuthi lokhu ukuskena kwenkomba noma ukuskena kwe-seq. Kuncike kakhulu kupharamitha. Uma ufaka esikhundleni senani okungajwayelekile ukutholwa lapho, kuzoba ukuskena kwenkomba. Uma ushintsha inani elithatha u-90% wetafula lapho, ukuskena kwe-seq kuzoba sobala, ngoba u-Postgres uyazazi izibalo. Futhi lokhu kuyisiphazamiso esikhulu se-pg_stat_statements, nakuba omunye umsebenzi usaqhubeka.

Ububi obukhulu bokuhlaziywa kwelogi ukuthi awukwazi ukukhokhela okuthi "log_min_duration_statement = 0" njengomthetho. Futhi sizokhuluma ngalokhu futhi. Ngokunjalo, awuboni isithombe sonke. Futhi omunye umbuzo, oshesha kakhulu, ungadla inani elikhulu lezinsiza, kodwa ngeke uwubone ngoba ungaphansi komkhawulo wakho.

Ama-DBA azixazulula kanjani izinkinga abazitholayo?

Ngokwesibonelo, sithole inkinga ethile. Yini evame ukwenziwa? Uma ungunjiniyela, lapho-ke uzobe wenza okuthile okungafani nosayizi. Uma uyi-DBA, khona-ke unesiteji. Futhi kungaba eyodwa kuphela. Futhi wayesemuva ngezinyanga eziyisithupha. Futhi ucabanga ukuthi uzoya ekukhiqizeni. Futhi ngisho nama-DBA anolwazi abe esehlola ukukhiqizwa, kumfanekiso. Futhi kwenzeka ukuthi bakha inkomba yesikhashana, baqiniseke ukuthi iyasiza, bayilahle futhi bayinikeze abathuthukisi ukuze bakwazi ukuyibeka kumafayela okufuduka. Yilolu hlobo lombhedo esenzeka manje. Futhi lokhu kuyinkinga.

• Shuna izilungiselelo.
• Lungiselela isethi yezinkomba.
• Shintsha umbuzo we-SQL ngokwawo (le indlela enzima kakhulu).
• Engeza umthamo (indlela elula ezimweni eziningi).

Kuningi okwenzekayo ngalezi zinto. Kukhona izibambo eziningi ku-Postgres. Kuningi okumele ukwazi. Ziningi izikhombo ku-Postgres, ngibonga nabahleli bale ngqungquthela. Futhi konke lokhu kudinga ukwaziwa, futhi yilokhu okwenza abangewona ama-DBA bazizwe sengathi ama-DBA enza umlingo omnyama. Okusho ukuthi, udinga ukufunda iminyaka eyi-10 ukuze uqale ukuqonda konke lokhu ngokujwayelekile.

Futhi ngilwa nalo mlingo omnyama. Ngifuna ukwenza konke ukuze kube nobuchwepheshe, futhi akukho intuition kukho konke lokhu.

Izibonelo zokuphila kwangempela

Lokhu ngikubone okungenani kumaphrojekthi amabili, okuhlanganisa neyami. Okunye okuthunyelwe kwebhulogi kusitshela ukuthi inani elingu-1 le-default_statistict_target lihle. Kulungile, asizame ekukhiqizeni.

Futhi silapha, sisebenzisa ithuluzi lethu eminyakeni emibili kamuva, ngosizo lokuhlola kudathabhesi esikhuluma ngayo namuhla, singakwazi ukuqhathanisa lokho okwakukhona nokuthi sekube yini.

Futhi ngenxa yalokhu sidinga ukudala isilingo. Iqukethe izingxenye ezine.

• Okokuqala imvelo. Sidinga ucezu lwehadiwe. Futhi uma ngifika enkampanini ethile futhi ngisayina inkontileka, ngibatshela ukuthi banginike i-hardware efanayo naleyo ekukhiqizeni. Kwi-Masters yakho ngayinye, ngidinga okungenani ucezu olulodwa lwehadiwe olufana nalolu. Lokhu kungaba umshini obonakalayo e-Amazon noma ku-Google, noma ngidinga ucezu olufanayo lwehadiwe. Okungukuthi, ngifuna ukudala kabusha imvelo. Futhi emcabangweni wendawo sifaka inguqulo enkulu ye-Postgres.
• Ingxenye yesibili yinhloso yocwaningo lwethu. Lena database. Ingadalwa ngezindlela eziningana. Ngizokukhombisa ukuthi kanjani.
• Ingxenye yesithathu umthwalo. Lesi yisikhathi esinzima kakhulu.
• Futhi ingxenye yesine yilokho esikubhekayo, okungukuthi sizoqhathanisa nani. Ake sithi singashintsha ipharamitha eyodwa noma ngaphezulu ku-config, noma singakha inkomba, njll.

Sethula ukuhlola. Nazi pg_stat_statements. Kwesokunxele yilokho okwenzekile. Kwesokudla - kwenzekeni.

Kwesokunxele default_statistics_target = 100, kwesokudla =1. Siyabona ukuthi lokhu kusizile. Sekukonke, konke kwaba ngcono ngo-000%.

Kodwa uma siskrolela phansi, kuzoba namaqembu ezicelo ezivela ku-pgBadger noma ku-pg_stat_statements. Kunezinketho ezimbili. Sizobona ukuthi ezinye izicelo zehle ngo-88%. Futhi nansi indlela yobunjiniyela. Singakwazi ukumba ngaphakathi ngoba siyazibuza ukuthi licwileni. Udinga ukuqonda ukuthi kwenzekeni ngezibalo. Kungani amabhakede amaningi ezibalweni eholela emiphumeleni emibi kakhulu.

Noma asikwazi ukumba, kodwa yenza okuthi “ALTER TABLE ... ALTER COLUMN” bese ubuyisela amabhakede ayi-100 ezibalweni zale kholomu. Futhi-ke ngokunye ukuhlola singaqinisekisa ukuthi lesi siqeshana sisizile. Konke. Lena indlela yobunjiniyela esisiza ukuthi sibone isithombe esikhulu futhi senze izinqumo ezisekelwe kudatha esikhundleni sokuzwa ulwazi.

Izibonelo ezimbalwa ezivela kwezinye izindawo. Kube nokuhlolwa kwe-CI ekuhlolweni iminyaka eminingi. Futhi ayikho iphrojekthi engqondweni yayo elungile engaphila ngaphandle kokuhlolwa okuzenzakalelayo.

Kwezinye izimboni: kwezokundiza, embonini yezimoto, lapho sihlola i-aerodynamics, siphinde sibe nethuba lokwenza izivivinyo. Ngeke sethule okuthile emdwebeni siqonde emkhathini, noma ngeke sisheshe sibeke imoto ethile emzileni. Ngokwesibonelo, kunomhubhe womoya.

Singafinyelela iziphetho ngokubhekwa kwezinye izimboni.

Okokuqala, sinendawo ekhethekile. Iseduze nokukhiqizwa, kodwa hhayi eduze. Isici sawo esiyinhloko ukuthi kufanele ishibhile, siphindeke futhi sibe ngokuzenzakalela ngangokunokwenzeka. Futhi kufanele kube namathuluzi akhethekile okwenza ukuhlaziya okuningiliziwe.

Ngokunokwenzeka, lapho sethula indiza futhi sindiza, siba namathuba amancane okutadisha yonke imilimitha yobuso bephiko kunesithola emhubheni womoya. Sinamathuluzi amaningi okuxilonga. Singakwazi ukuthwala izinto ezinzima kakhulu esingenakukwazi ukuzifaka endizeni emoyeni. Ngokufanayo ne-Postgres. Kwezinye izimo, singase sivumele ukuloga kwemibuzo egcwele ngesikhathi sokuhlolwa. Futhi asifuni ukwenza lokhu ekukhiqizeni. Singase sihlele ukunika amandla lokhu sisebenzisa i-auto_explain.

Futhi njengoba ngishilo, izinga eliphezulu lokuzenzakalela lisho ukuthi sicindezela inkinobho bese siphinda. Yile ndlela okufanele kube ngayo, ukuze kube nokuhlolwa okuningi, ukuze kusakazeke.

UNancy CLI - isisekelo "se-database laboratory"

Futhi ngakho senza le nto. Okungukuthi, ngikhulume ngale mibono ngoJuni, cishe unyaka odlule. Futhi sesivele sinalokho okubizwa nge-Nancy CLI ku-Open Source. Lesi yisisekelo sokwakha ilabhorethri egciniwe.

Nancy - Itholakala ku-Open Source, ku-Gitlab. Ungakusho, ungakuzama. Nginikeze isixhumanisi kumaslayidi. Ungachofoza kuyo futhi izoba lapho Usizo ngazo zonke izindlela.

Yebo, kuningi okusathuthukiswa. Miningi imibono lapho. Kodwa lokhu kuyinto esiyisebenzisa cishe nsuku zonke. Futhi uma sinombono - kungani kwenzeka ukuthi uma sisusa imigqa engu-40, konke kuza phansi ku-IO, khona-ke singakwazi ukwenza isilingo futhi sibheke kabanzi ukuze siqonde ukuthi kwenzekani bese sizame ukukulungisa ekuhambeni. Okusho ukuthi, senza ucwaningo. Isibonelo, silungisa okuthile futhi sibone ukuthi kwenzekani ekugcineni. Futhi asikwenzi lokhu ekukhiqizeni. Lona umnyombo wombono.

Kungasebenza kuphi lokhu? Lokhu kungasebenza endaweni, i.e. ungakwenza noma yikuphi, ungakusebenzisa naku-MacBook. Sidinga idokodo, masihambe. Yilokho kuphela. Ungayisebenzisa kwesinye isikhathi ocezu lwehadiwe, noma emshinini obonakalayo, noma yikuphi.

Futhi kunethuba lokugijima ukude e-Amazon ku-EC2 Instance, ezindaweni. Futhi leli yithuba elihle kakhulu. Isibonelo, izolo senze izivivinyo ezingaphezu kuka-500 kusibonelo se-i3, siqala ngomncane sagcina ngo-i3-16-xlarge. Futhi ukuhlola okungu-500 kusibize u-$64. Ngayinye yathatha imizuzu engu-15. Okusho ukuthi, ngenxa yokuthi amabala asetshenziswa lapho, ishibhile kakhulu - isaphulelo esingu-70%, ukukhokhiswa kwe-Amazon ngomzuzwana ngamunye. Kuningi ongakwenza. Ungenza ucwaningo lwangempela.

Futhi izinguqulo ezintathu ezinkulu ze-Postgres ziyasekelwa. Akunzima kangako ukuqeda ezinye ezindala kanye nenguqulo entsha ye-12 futhi.

Singachaza into ngezindlela ezintathu. Lokhu:

• Lahla/sql ifayela.
• Indlela eyinhloko ukuhlanganisa uhla lwemibhalo lwe-PGDATA. Njengomthetho, ithathwa kuseva eyisipele. Uma unezipele ezijwayelekile kanambambili, ungenza ama-clone ukusuka lapho. Uma unamafu, ihhovisi lefu elifana ne-Amazon ne-Google lizokwenzela lokhu. Lena indlela ebaluleke kakhulu yokuhlanganisa ukukhiqizwa kwangempela. Le yindlela esembula ngayo.
• Futhi indlela yokugcina ifanele ucwaningo uma ufuna ukuqonda ukuthi okuthile kusebenza kanjani ku-Postgres. Lokhu kuyi-pgbench. Ungakwazi ukukhiqiza usebenzisa pgbench. Kuyinketho eyodwa nje ethi "db-pgbench". Umtshele ukuthi yisiphi isikali. Futhi konke kuzokwenziwa efwini, njengoba kushiwo.

Futhi layisha:

• Singakwazi ukwenza umthwalo ngochungechunge olulodwa lwe-SQL. Lena indlela yakudala kakhulu.
• Futhi singalingisa umthwalo. Futhi singakulingisa kuqala ngendlela elandelayo. Kudingeka siqoqe zonke izingodo. Futhi kubuhlungu. Ngizokukhombisa ukuthi kungani. Futhi usebenzisa i-pgreplay esiyidlalayo, eyakhelwe kuNancy.
• Noma enye inketho. Okubizwa ngokuthi umthwalo wezandla, esikwenza ngomzamo othile. Ukuhlaziya umthwalo wethu wamanje ohlelweni lokulwa, sikhipha amaqembu aphezulu ezicelo. Futhi sisebenzisa i-pgbench singalingisa lo mthwalo elabhorethri.

• Kuphakathi kokuthi kufanele senze uhlobo oluthile lwe-SQL, okungukuthi sihlola uhlobo oluthile lokufuduka, sakhe inkomba lapho, senze i-ANALAZE lapho. Futhi sibheka okwenzeka ngaphambi kwe-vacuum nangemva kwe-vacuum. Ngokuvamile, noma iyiphi i-SQL.
• Kuphakathi kokuthi sishintsha ipharamitha eyodwa noma ngaphezulu ku-config. Singasitshela ukuthi sihlole, ngokwesibonelo, amanani ayi-100 e-Amazon ukuthola imininingwane yethu ye-terabyte. Futhi emahoreni ambalwa uzoba nomphumela. Njengomthetho, kuzokuthatha amahora ambalwa ukusebenzisa isizindalwazi se-terabyte. Kodwa kukhona isiqeshana ekuthuthukisweni, sinochungechunge okungenzeka, i.e. ungakwazi njalo ukusebenzisa pgdata efanayo kuseva efanayo futhi uhlole. Ama-Postgres azoqala kabusha futhi izinqolobane zizosethwa kabusha. Futhi ungakwazi ukushayela umthwalo.

• Uhla lwemibhalo lufika nenqwaba yamafayela ahlukene, aqala ku-pg snapshotsisimo***. Futhi into ethakazelisa kakhulu lapho pg_stat_statements, pg_stat_kcacke. Lezi izandiso ezimbili ezihlaziya izicelo. Futhi i-pg_stat_bgwriter ayiqukethe izibalo zombhali we-pgwriter kuphela, kodwa futhi nendawo yokuhlola nokuthi ama-backends ngokwawo asusa kanjani ama-buffer angcolile. Futhi konke kuyathakazelisa ukukubona. Isibonelo, uma sisetha ama-shared_buffers, kuyajabulisa kakhulu ukubona ukuthi yimalini eshintshiwe wonke umuntu.
• Izingodo ze-postgres nazo ziyafika. Amalogi amabili - ilogi yokulungiselela kanye nerekhodi lokudlala lomthwalo.
• Isici esisha kakhulu yi-FlameGraphs.
• Futhi, uma usebenzise izinketho ze-pgreplay noma ze-pgbench zokudlala umthwalo, umphumela wazo uzoba owomdabu. Futhi uzobona i-latency ne-TPS. Kuzokwaziwa ukuqonda ukuthi babone kanjani.
• Ulwazi lwesistimu.
• Ukuhlola okuyisisekelo kwe-CPU ne-IO. Lokhu kungokwesibonelo se-EC2 e-Amazon, lapho ufuna ukwethula izehlakalo ezifanayo eziyi-100 ochungechungeni futhi usebenzise imigijimo ehlukene eyi-100 lapho, uzoba nokuhlolwa okungu-10. Futhi udinga ukwenza isiqiniseko sokuthi awuhlangani nesenzakalo esinamaphutha esivele sicindezelwa othile. Abanye bayasebenza kulolu cezu lwehadiwe futhi unensiza encane esele. Kungcono ukulahla imiphumela enjalo. Futhi ngosizo lwe-sysbench evela ku-Alexey Kopytov, senza amasheke ambalwa amancane azofika futhi angaqhathaniswa nabanye, okungukuthi uzoqonda ukuthi i-CPU iziphatha kanjani nokuthi i-IO iziphatha kanjani.

Yibuphi ubunzima bezobuchwepheshe obusekelwe esibonelweni sezinkampani ezahlukene?

Ake sithi sifuna ukuphinda umthwalo wangempela sisebenzisa izingodo. Kungumbono omuhle uma kubhalwe ku-Open Source pgreplay. Siyayisebenzisa. Kodwa ukuze isebenze kahle, kufanele unike amandla ukugawulwa kwemibuzo okugcwele ngamapharamitha nesikhathi.

Kunezinkinga ezithile ngobude nesitembu sesikhathi. Sizokhipha lonke leli khishi. Umbuzo omkhulu wukuthi ungakwazi yini ukukukhokhela noma cha?

https://gist.github.com/NikolayS/08d9b7b4845371d03e195a8d8df43408

Inkinga ukuthi ingase ingatholakali. Okokuqala, kufanele uqonde ukuthi yikuphi ukusakaza okuzobhalwa kulogi. Uma une-pg_stat_statements, ungasebenzisa lo mbuzo (isixhumanisi sizotholakala kumaslayidi) ukuze uqonde cishe ukuthi mangaki amabhayithi azobhalwa ngomzuzwana.

Sibheka ubude besicelo. Sishaya indiva iqiniso lokuthi awekho amapharamitha, kodwa siyabazi ubude besicelo futhi siyazi ukuthi senziwa izikhathi ezingaki ngesekhondi. Ngale ndlela singakwazi ukulinganisa cishe ukuthi mangaki amabhayithi ngomzuzwana. Singenza iphutha ngokuphindwe kabili, kodwa ngokuqinisekile sizoqonda ukuhleleka ngale ndlela.

Siyabona ukuthi izikhathi ezingama-802 ngomzuzwana lesi sicelo senziwa. Futhi siyabona ukuthi amabhayithi_ngesekhondi – 300 kB/s azobhalwa kuhlanganiswe noma kukhishwe. Futhi, njengomthetho, singakwazi ukukhokhela ukugeleza okunjalo.

Kodwa! Iqiniso liwukuthi kunezinhlelo ezihlukene zokugawula. Futhi okuzenzakalelayo kwabantu kuvame ukuthi "syslog".

Futhi uma une-syslog, ungase ube nesithombe esinjengalesi. Sizothatha i-pgbench, sinikeze amandla ukuloga kwemibuzo futhi sibone ukuthi kwenzekani.

Ngaphandle kokungena - lena yikholomu engakwesokunxele. Sithole i-161 TPS. Nge-syslog - lokhu kuku-Ubuntu 000 e-Amazon, sithola i-16.04 TPS. Futhi uma sishintshela kwezinye izindlela ezimbili zokugawula imithi, khona-ke isimo siba ngcono kakhulu. Okusho ukuthi, besilindele ukuthi yehle, kodwa hhayi ngezinga elifanayo.

Futhi ku-CentOS 7, lapho ijenali nayo ibamba iqhaza, iguqula izingodo zibe ifomethi kanambambili ukuze ziseshwe kalula, njll., khona-ke kuyiphupho elibi lapho, silahla izikhathi ezingu-44 ku-TPS.

Futhi yilokhu abantu abahlala nakho. Futhi ngokuvamile ezinkampanini, ikakhulukazi ezinkulu, lokhu kunzima kakhulu ukushintsha. Uma ungakwazi ukubalekela i-syslog, sicela ubalekele kuyo.

• Linganisa i-IOPS bese ubhala ukugeleza.
• Hlola uhlelo lwakho lokungena.
• Uma umthwalo ohlongozwayo mkhulu ngokwedlulele, cabanga ukwenza amasampula.

Sine pg_stat_statements. Njengoba ngishilo, kufanele kube khona. Futhi singathatha futhi sichaze iqembu ngalinye lezicelo ngendlela ekhethekile efayeleni. Futhi-ke singasebenzisa isici esilula kakhulu ku-pgbench - leli yikhono lokufaka amafayela amaningana usebenzisa inketho ethi "-f".

Iqonda okuningi "-f". Futhi ungakwazi ukusho ngosizo luka-“@” ekugcineni ukuthi ifayela ngalinye kufanele libe nasabelo sini. Okusho ukuthi, singasho ukuthi yenza lokhu ku-10% yamacala, futhi lokhu ngama-20%. Futhi lokhu kuzosisondeza kulokho esikubona ekukhiqizeni.

Sizokuqonda kanjani esinakho ekukhiqizeni? Isabelo sini futhi kanjani? Lokhu kancane eceleni. Sinomkhiqizo owodwa ngaphezulu postgres-checkup. Futhi isisekelo kumthombo ovulekile. Futhi manje sesiyithuthukisa ngenkuthalo.

Wazalwa ngenxa yezizathu ezihluke kancane. Ngezizathu zokuthi ukuqapha akwanele. Okusho ukuthi, woza, ubheke isisekelo, ubheke izinkinga ezikhona. Futhi, njengomthetho, wenza isheke_lezempilo. Uma uyi-DBA enolwazi, khona-ke wenza i-health_check. Sibheke ukusetshenziswa kwezinkomba, njll. Uma une-OKmeter, kuhle kakhulu. Lokhu ukuqapha okuhle kwe-Postgres. OKmeter.io - sicela uyifake, yonke into yenziwa kahle kakhulu lapho. Ikhokhiwe.

Uma ungenayo, ngokuvamile awunakho okuningi. Ekuqaphelweni, kuvame ukuba ne-CPU, i-IO, bese kuba nokubhuka, futhi yilokho kuphela. Futhi sidinga okwengeziwe. Sidinga ukubona ukuthi i-autovacuum isebenza kanjani, ukuthi indawo yokuhlola isebenza kanjani, ku-io sidinga ukuhlukanisa indawo yokuhlola kusuka ku-bgwriter kanye ne-backends, njll.

Inkinga ukuthi uma usiza inkampani enkulu, ayikwazi ukwenza okuthile ngokushesha. Abakwazi ukuthenga ngokushesha i-OKmeter. Mhlawumbe bazoyithenga ezinyangeni eziyisithupha. Abakwazi ukuletha amaphakheji ngokushesha.

Futhi siqhamuke nombono wokuthi sidinga ithuluzi elikhethekile elingadingi noma yini ukuthi lifakwe, okungukuthi akudingekile ukuthi ufake noma yini ekukhiqizeni. Ifake kukhompuyutha yakho ephathekayo, noma kuseva yokubuka lapho uzoyiqhuba khona. Futhi izohlaziya izinto eziningi: isistimu yokusebenza, isistimu yefayela, kanye ne-Postgres ngokwayo, yenza imibuzo ethile elula engaqhutshwa ngokuqondile ekukhiqizeni futhi akukho lutho oluzohluleka.

Sayibiza ngokuthi i-Postgres-checkup. Ngokwezokwelapha, lokhu ukuhlola impilo evamile. Uma kuyitimu yezimoto, kufana nokulungiswa. Ulungisa imoto yakho njalo ezinyangeni eziyisithupha noma ngonyaka, kuye ngohlobo. Uyayenza isondlo isisekelo sakho? Okungukuthi, ingabe wenza ucwaningo olunzulu njalo? Kumele kwenziwe. Uma wenza izipele, bese wenza ukuhlola, lokhu akubalulekile kangako.

Futhi sinethuluzi elinjalo. Iqale ukuvela ngenkuthalo ezinyangeni ezintathu ezedlule. Usemusha, kodwa kuningi lapho.

Ukuqoqa amaqembu emibuzo “anamandla” kakhulu - bika i-K003 ku-Postgres-checkup

Futhi kukhona iqembu imibiko K. Three imibiko kuze kube manje. Futhi kukhona umbiko onjalo K003. Kukhona phezulu kusuka ku-pg_stat_statements, kuhlungwe nge- total_time.

Uma sihlunga amaqembu ezicelo ngetotal_time, phezulu sibona iqembu elilayisha isistimu yethu kakhulu, okungukuthi, elisebenzisa izinsiza ezengeziwe. Kungani ngiqamba amaqembu emibuzo? Ngoba silahle amapharamitha. Lezi akusezona izicelo, kodwa amaqembu ezicelo, okungukuthi azifinyeziwe.

Futhi uma silungiselela ukusuka phezulu kuye phansi, sizolula izinsiza zethu futhi sibambezele isikhathi lapho sidinga ukuthuthukisa. Lena indlela enhle kakhulu yokonga imali.

Mhlawumbe lena akuyona indlela enhle kakhulu yokunakekela abasebenzisi, ngoba singase singaboni izimo ezingavamile, kodwa ezicasula kakhulu lapho umuntu elinde imizuzwana engu-15. Sezizonke, ziyivelakancane kangangokuthi asiziboni, kodwa sibhekene nezinsiza.

Kwenzekeni kuleli tafula? Sithathe izifinyezo ezimbili. I-Postgres_checkup izokunikeza i-delta yemethrikhi ngayinye: isikhathi esiphelele, amakholi, imigqa, okwabiwe_blks_read, njll. Yilokho nje, i-delta ibaliwe. Inkinga enkulu nge-pg_stat_statements ukuthi ayikhumbuli ukuthi isethwe kabusha nini. Uma i-pg_stat_database ikhumbula, khona-ke i-pg_stat_statements ayikhumbuli. Uyabona ukuthi kunenani elingu-1, kodwa asazi ukuthi sibale sisukaphi.

Futhi lapha siyazi, lapha sinezifinyezo ezimbili. Siyazi ukuthi i-delta kuleli cala yayiyimizuzwana engu-56. Igebe elifushane kakhulu. Ihlungwe nge total_time. Bese singahlukanisa, okungukuthi sihlukanisa wonke amamethrikhi ngobude besikhathi. Uma sihlukanisa imethrikhi ngayinye ngobude besikhathi, sizoba nenombolo yamakholi ngesekhondi ngalinye.

Okulandelayo, inani_lesikhathi ngesekhondi i-metric engiyithandayo. Kukalwa ngamasekhondi, ngomzuzwana ngamunye, okungukuthi ukuthi isistimu yethu ithathe imizuzwana emingaki ukwenza leli qembu lezicelo ngomzuzwana. Uma ubona okungaphezu kwesekhondi ngomzuzwana lapho, kusho ukuthi bekufanele unikeze umongo ongaphezu kowodwa. Lena imethrikhi enhle kakhulu. Ungaqonda ukuthi lo mngane, isibonelo, udinga okungenani ama-cores amathathu.

Lokhu kuwukwazi kwethu, angikaze ngikubone okufana nakho noma kuphi. Sicela uqaphele - lokhu kuyinto elula kakhulu - okwesibili ngomzuzwana. Kwesinye isikhathi, lapho i-CPU yakho ingu-100%, bese kuba isigamu sehora ngomzuzwana, okusho ukuthi, uchithe ingxenye yehora wenza lezi zicelo nje.

Okulandelayo sibona imigqa ngomzuzwana. Siyazi ukuthi zingaki imigqa ebuyisiwe ngesekhondi ngayinye.

Bese kuba khona into ethokozisayo. Mangaki ama-shared_buffers esiwafunda ngesekhondi ngalinye kuma-shared_buffers ngokwawo. Amahithi ayesevele ekhona, futhi sithathe imigqa kunqolobane yesistimu yokusebenza noma kudiski. Inketho yokuqala iyashesha, kanti eyesibili ingase isheshe noma ingabi ngokushesha, kuye ngesimo.

Futhi indlela yesibili yokuhlukanisa iwukuhlukanisa inani lezicelo kuleli qembu. Kukholomu yesibili uzohlala unombuzo owodwa ohlukaniswe umbuzo ngamunye. Futhi-ke kuyathakazelisa - ukuthi mangaki ama-millisecond abekulesi sicelo. Siyazi ukuthi lo mbuzo uziphatha kanjani ngokwesilinganiso. Ama-millisecond angu-101 ayedingeka esicelweni ngasinye. Lena imethrikhi evamile okudingeka siyiqonde.

Umbuzo ngamunye ubuyise imigqa emingaki ngokwesilinganiso? Sibona 8 leli qembu libuya. Ngokwesilinganiso, yimalini ethathwe kunqolobane yafundwa. Siyabona ukuthi yonke into igcinwe kahle. Amahithi aqinile eqembu lokuqala.

Futhi uchungechunge oluncane lwesine emugqeni ngamunye lusho ukuthi yimaphi amaphesenti engqikithi. Sinazo izingcingo. Ake sithi 1. Futhi singaqonda ukuthi leli qembu linikela muni. Siyabona ukuthi kulokhu iqembu lokuqala linikela ngaphansi kuka-000%. Okusho ukuthi, ihamba kancane kangangokuthi asiyiboni esithombeni sonke. Futhi iqembu lesibili lingu-000% ezicingweni. Okusho ukuthi, u-0,01% wazo zonke izingcingo ziyiqembu lesibili.

Total_time naso siyathandeka. Sisebenzise u-14% wengqikithi yesikhathi sethu somsebenzi eqenjini lokuqala lezicelo. Futhi okwesibili - 11%, njll.

Ngeke ngingene emininingwaneni, kodwa kukhona ubuqili lapho. Sibonisa iphutha phezulu, ngoba uma siqhathanisa, izifinyezo zingase zintante, okungukuthi, ezinye izicelo zingase ziphume futhi ngeke zisaba khona kwesesibili, kuyilapho ezinye ezintsha zingavela. Futhi lapho sibala iphutha. Uma ubona u-0, kuhle lokho. Awekho amaphutha. Uma izinga lephutha lifika ku-20%, kulungile.

Bese sibuyela esihlokweni sethu. Sidinga ukwenza umsebenzi onzima. Sithatha ukusuka phezulu siye phansi sihambe size sifike ku-80% noma ku-90%. Ngokuvamile lokhu kuba ngamaqembu angu-10-20. Futhi senza amafayela we-pgbench. Sisebenzisa okungahleliwe lapho. Kwesinye isikhathi lokhu, ngeshwa, akuphumeleli. Futhi ku-Postgres 12 kuzoba namathuba amaningi okusebenzisa le ndlela.

Bese sizuza u-80-90% ngesikhathi_sengqikithi ngale ndlela. Yini okufanele ngiyibeke ngokulandelayo ngemva kuka-“@”? Sibheka izingcingo, sibheke ukuthi ingakanani inzalo futhi siyaqonda ukuthi sikweleta inzalo engaka lapha. Kusukela kulawa maphesenti singaqonda ukuthi sibhalansisa kanjani ifayela ngalinye. Ngemuva kwalokho sisebenzisa i-pgbench bese siya emsebenzini.

Siphinde sibe no-K001 no-K002.

I-K001 intambo eyodwa enkulu enezintambo ezingaphansi ezine. Lesi yisici sawo wonke umthwalo wethu. Bona ikholomu yesibili nomugqa ongaphansi wesibili. Siyabona ukuthi cishe isekhondi elilodwa nesigamu ngomzuzwana, okungukuthi uma kukhona ama-cores amabili, kuyoba kuhle. Kuzoba namandla acishe abe ngu-75%. Futhi izosebenza kanje. Uma sinama-cores angu-10, ngakho-ke sizobe sizolile. Ngale ndlela singakwazi ukuhlola izinsiza.

I-K002 yilokho engikubiza ngamakilasi emibuzo, okungukuthi KHETHA, FAKA, BUYEKEZA, SUSA. Futhi ngokwehlukana KHETHA UKUZE UBUYEKEZE, ngoba iwukhiye.

Futhi lapha singaphetha ngokuthi SELECT abafundi abavamile - 82% yazo zonke izingcingo, kodwa ngesikhathi esifanayo - 74% in total_time. Okusho ukuthi, zibizwa kakhulu, kodwa zidla izinsiza ezincane.

Futhi sibuyela embuzweni othi: “Singawakhetha kanjani ama-shared_buffers afanele?” Ngiyabona ukuthi amabhentshimakhi amaningi asekelwe embonweni - ake sibone ukuthi i-throughput izoba yini, i.e. izoba yini i-throughput. Ngokuvamile kukalwa nge-TPS noma i-QPS.

Futhi sizama ukuminyanisa ukuthengiselana okuningi ngomzuzwana ngangokunokwenzeka emotweni sisebenzisa amapharamitha wokushuna. Nakhu ncamashi 311 ngesekhondi ngokukhetha.

Kodwa akekho oshayelayo emsebenzini futhi abuyele ekhaya ngesivinini esigcwele. Lokhu kuwubuwula. Okufanayo nezingosi zolwazi. Akudingekile ukuthi sishayele ngesivinini esikhulu, futhi akekho oshayelayo. Akekho ohlala emkhiqizweni, one-CPU engu-100%. Nakuba, mhlawumbe othile uhlala, kodwa lokhu akukuhle.

Umqondo uwukuthi ngokuvamile sishayela ngamaphesenti angu-20 omthamo, okungcono angabi ngaphezu kwama-50%. Futhi sizama ukuthuthukisa isikhathi sokuphendula kubasebenzisi bethu ngaphezu kwakho konke. Okusho ukuthi, kufanele siguqule ama-knob ethu ukuze kube ne-latency encane ngesivinini esingu-20%, ngokwemibandela. Lona umbono esizama ukuwusebenzisa ekuhloleni kwethu.

Futhi ekugcineni, izincomo:

• Qiniseka ukuthi wenza I-Database Lab.
• Uma kungenzeka, kwenze ngokufunwayo ukuze kuvele isikhashana - dlala futhi ulahle. Uma unamafu, khona-ke lokhu kuhamba ngaphandle kokusho, okungukuthi, unokuma okuningi.
• Yiba nelukuluku. Futhi uma kukhona okungalungile, bese uhlola ngokuhlolwa ukuthi kuziphatha kanjani. U-Nancy angasetshenziselwa ukuziqeqesha ukuze uhlole ukuthi isisekelo sisebenza kanjani.
• Futhi hlose ukuthola isikhathi esincane sokuphendula.
• Futhi ungesabi imithombo ye-Postgres. Lapho usebenza nemithombo, kufanele wazi isiNgisi. Kuningi ukuphawula lapho, konke kuchazwa lapho.
• Futhi hlola impilo yesizindalwazi njalo, okungenani kanye njalo ezinyangeni ezintathu, ngokwenza, noma ukuhlolwa kwe-Postgres.

Imibuzo yakho

Ngibonga kakhulu! Into ejabulisa kakhulu.

Izingcezu ezimbili.

Yebo, izingcezu ezimbili. Yimina kuphela engangingaqondi kahle. Lapho mina no-Nancy sisebenza, singalungisa ipharamitha eyodwa noma iqembu lonke?

Sinepharamitha yokumisa i-delta. Ungajika lapho kaningi ngendlela ofuna ngayo ngesikhathi esisodwa. Kodwa udinga ukuqonda ukuthi uma ushintsha izinto eziningi, ungafinyelela iziphetho ezingalungile.

Yebo. Ngabuzani? Ngoba kunzima ukwenza izivivinyo uma unepharamitha eyodwa kuphela. Uyiqinise, ubone ukuthi isebenza kanjani. Ngimkhiphe. Bese uqala okulandelayo.

Ungakwazi ukuqinisa ngesikhathi esifanayo, kodwa kuncike esimweni, kunjalo. Kodwa kungcono ukuhlola umqondo owodwa. Sibe nombono izolo. Sasisondelene kakhulu nesimo. Kwakukhona ukucushwa okubili. Futhi asizange siqonde ukuthi kungani kunomehluko omkhulu. Futhi kwavela umqondo wokuthi udinga ukusebenzisa i-dichotomy ukuze uqonde njalo futhi uthole ukuthi umehluko uyini. Ungenza ngokushesha uhhafu wamapharamitha afanayo, bese kuba ikota, njll. Konke kuyavumelana nezimo.

Futhi kukhona omunye umbuzo. Le phrojekthi isencane futhi iyathuthuka. Imibhalo isivele isilungile, ingabe ikhona incazelo enemininingwane?

Ngenze isixhumanisi lapho ngqo encazelweni yamapharamitha. Ikhona. Kodwa izinto eziningi azikakafiki. Ngifuna abantu abanomqondo ofanayo. Futhi ngiyabathola uma ngicula. Lokhu kuhle kakhulu. Kukhona osevele esebenza nami, othile wasiza futhi wenza okuthile lapho. Futhi uma unentshisekelo kulesi sihloko, nikeza impendulo ngalokho okushodayo.

Uma sesakhe ilabhorethri, mhlawumbe kuzoba nempendulo. Asibone. Ngiyabonga!

Sawubona! Siyabonga ngombiko! Ngabona ukuthi kukhona ukwesekwa kwe-Amazon. Ingabe zikhona izinhlelo zokusekela i-GSP?

Umbuzo omuhle. Saqala ukukwenza. Futhi sikumise okwamanje ngoba sifuna ukonga imali. Okusho ukuthi, kukhona ukwesekwa kusetshenziswa run on localhost. Ungakwazi ukwenza isibonelo ngokwakho futhi usebenze endaweni yangakini. Ngendlela, yilokho esikwenzayo. Lokhu ngikwenza e-Getlab, laphaya kwa-GSP. Kodwa asiboni phuzu ekwenzeni i-orchestration enjalo okwamanje, ngoba i-Google ayinazo izindawo ezishibhile. Nakhu ??? izimo, kodwa zinemikhawulo. Okokuqala, bahlala benesaphulelo esingu-70% kuphela futhi awukwazi ukudlala ngentengo lapho. Ezindaweni ezithile, sinyusa intengo ngo-5-10% ukuze sinciphise amathuba okuthi uzokhahlelwa. Okusho ukuthi, ugcina amabala, kodwa angasuswa kuwe nganoma yisiphi isikhathi. Uma ufaka isicelo esingaphezulu kancane kunabanye, uzobulawa kamuva. I-Google inokucaciswa okuhluke ngokuphelele. Futhi kukhona omunye umkhawulo omubi kakhulu - bahlala amahora angama-24 kuphela. Futhi ngezinye izikhathi sifuna ukwenza isilingo izinsuku ezi-5. Kodwa ungakwenza lokhu ezindaweni; amabala kwesinye isikhathi ahlala izinyanga.

Sawubona! Siyabonga ngombiko! Ukhulume ngokuhlolwa. Uwabala kanjani amaphutha ezitatimende_zezibalo?

Umbuzo omuhle kakhulu. Ngingakubonisa futhi ngikutshele ngokuningiliziwe. Ngamafuphi, sibheka ukuthi isethi yamaqembu esicelo intante kanjani: bangaki abawile futhi bangaki abasha abavele. Bese sibheka amamethrikhi amabili: inani_isikhathi namakholi, ngakho-ke kukhona amaphutha amabili. Futhi sibheka igalelo lamaqembu antantayo. Kunamaqenjana amabili: abahambile nabafikile. Ake sibone ukuthi liyini iqhaza labo esithombeni esiphelele.

Awesabi yini ukuthi izojika lapho kabili noma kathathu phakathi nesikhathi phakathi kwezifinyezo?

Okusho ukuthi baphinde babhalisa noma kwenzenjani?

Isibonelo, lesi sicelo sesivele sikhishiwe kanye, sabe sesifika futhi sabuye salungiselelwa futhi, saphinde safika futhi saphinde salungiselelwa futhi. Futhi ubale okuthile lapha, futhi kuphi konke?

Umbuzo omuhle, kuzomele sibheke.

Ngenza into efanayo. Kwakulula, kunjalo, ngikwenze ngedwa. Kodwa kwadingeka ngisethe kabusha, ngisethe kabusha izibalo_izitatimende futhi ngithole ngesikhathi sesifinyezo ukuthi kwakukhona ngaphansi kwengxenye ethile, eyayingakafinyeleli ophahleni lokuthi zingakanani izibalo_izitatimende ezinganqwabelana lapho. Futhi ukuqonda kwami ​​​​ukuthi, cishe, akukho lutho olwasuswa.

Yebo Yebo.

Kodwa angiqondi ukuthi ngingayenza kanjani ngokuthembekile.

Ngeshwa, angikhumbuli kahle ukuthi sisebenzisa umbhalo wombuzo lapho noma umbuzo one-pg_stat_statements futhi sigxile kuwo. Uma sigxila ku-queryid, ngokombono siqhathanisa izinto ezifanayo.

Cha, angakhishwa izikhathi eziningana phakathi kwezifinyezo bese ebuya futhi.

Nge-id efanayo?

Yebo.

Sizokufunda lokhu. Umbuzo omuhle. Kudingeka sikufunde. Kepha okwamanje, esikubonayo kubhalwe ukuthi 0...

Kunjalo, kuyicala eliyivelakancane, kodwa ngethuka lapho ngithola ukuthi i-stat_statemetns ingasuka lapho.

Kungaba nezinto eziningi ku-Pg_stat_statements. Sihlangabezane neqiniso lokuthi uma une-track_utility = on, amasethi akho nawo ayalandelelwa.

O impela.

Futhi uma une-java hibernate, okungahleliwe, khona-ke itafula le-hashi liqala ukutholakala lapho. Futhi lapho nje uvala uhlelo lokusebenza olulayishwe kakhulu, ugcina unamaqembu angama-50-100. Futhi yonke into izinzile lapho. Enye indlela yokulwa nalokhu ukukhulisa pg_stat_statements.max.

Yebo, kodwa udinga ukwazi ukuthi yimalini. Futhi ngandlela-thile kudingeka sihlale simbhekile. Yilokho engikwenzayo. Okusho ukuthi, ngine-pg_stat_statements.max. Futhi ngibona ukuthi ngesikhathi sesithombe esincane ngangingakafiki ku-70%. Kulungile, ngakho-ke asilahlekelwanga lutho. Asisethe kabusha. Futhi silondoloza futhi. Uma isifinyezo esilandelayo singaphansi kwama-70, cishe cishe awuphindanga ulahlekelwe lutho.

Yebo. Okuzenzakalelayo manje sekungu-5. Futhi lokhu kwanele kubantu abaningi.

Ngokuvamile yebo.

Ividiyo:

PS Egameni lami, ngizongeza ukuthi uma i-Postgres iqukethe idatha eyimfihlo futhi ayikwazi ukufakwa endaweni yokuhlola, ungasebenzisa I-PostgreSQL Anonymizer. Uhlelo lucishe lube ngolulandelayo:

Source: www.habr.com