🥇Kunyura kwakadzika muPostgreSQL yemukati manhamba. Alexey Lesovsky

Kunyorwa kweiyo 2015 mushumo naAlexey Lesovsky "Yakadzika dive muPostgreSQL yemukati manhamba"

Disclaimer kubva kumunyori weshumo: Ndinocherechedza kuti chirevo ichi chakaitwa munaNovember 2015 - makore anopfuura 4 apfuura uye nguva yakawanda yapfuura. Shanduro 9.4 yakakurukurwa mumushumo haichatsigirwi. Kwemakore mana apfuura, 4 zvitsva zvakaburitswa zvakaburitswa umo zvakawanda zvitsva, kuvandudzwa uye shanduko maererano nenhamba dzakaonekwa, uye zvimwe zvezvinyorwa ndezvekare uye hazvina basa. Sezvandakaongorora, ndakaedza kumaka nzvimbo idzi kuti ndisakurasisa iwe muverengi. Ini handina kunyorazve nzvimbo idzi, kune dzakawanda dzadzo, uye semhedzisiro, mushumo wakasiyana zvachose uchabuda.

Iyo PostgreSQL DBMS inzira hombe, uye iyi michina ine akawanda subsystems, iro rakarongwa basa rinokanganisa zvakananga kuita kweDBMS. Panguva yekushanda, nhamba uye ruzivo pamusoro pekushanda kwezvikamu zvinounganidzwa, izvo zvinokubvumira kuti uongorore kushanda kwePostgreSQL uye kutora matanho ekuvandudza kushanda. Nekudaro, kune yakawanda yeruzivo urwu uye inoratidzwa mune yakareruka fomu. Kugadzirisa ruzivo urwu nekududzira dzimwe nguva ibasa risiri diki, uye "zoo" yezvishandiso uye zvekushandisa zvinogona kuvhiringa nyore nyore kunyange DBA yepamusoro.

Masikati akanaka Ini ndinonzi Aleksey. Sezvakataurwa naIlya, ndichataura nezve PostgreSQL manhamba.

PostgreSQL chiitiko nhamba. PostgreSQL ine nhamba mbiri. Chiitiko nhamba, izvo zvichakurukurwa. Uye scheduler manhamba nezve kugovera data. Ini ndichataura zvakananga nezve PostgreSQL chiitiko nhamba, iyo inotitendera kutonga kuita uye neimwe nzira kuivandudza.

Ini ndichakuudza kuti ungashandisa sei zvinobudirira manhamba kugadzirisa akasiyana ematambudziko auinawo kana angave nawo.

Chii chisiri mushumo? Mumushumo, ini handisi kuzobata pane manhamba emugadziri, nekuti. iyi inyaya yakaparadzana yemushumo wakasiyana wekuti data rinochengetwa sei mudhatabhesi uye kuti murongi wemubvunzo anowana sei pfungwa nezve hunhu uye huwandu hwe data iyi.

Uye hapazove nekuongororwa kwechishandiso, ini handinga enzanisa chigadzirwa nechimwe. Pachange pasina kushambadza. Ngatizvidonhedze.

Ndinoda kukuratidza kuti kushandisa manhamba kunobatsira. Zvakafanira. Rishandise usingatyi. Zvese zvatinoda iSQL yakajeka uye ruzivo rwekutanga rweSQL.

Uye isu tichataura pamusoro pezviverengero zvekusarudza kugadzirisa matambudziko.

Kana tikatarisa PostgreSQL uye tomhanyisa murairo pane inoshanda sisitimu kuti titarise maitiro, isu tichaona "dema bhokisi". Tichaona mamwe maitiro anoita chimwe chinhu, uye nemazita tinogona kufungidzira zvavari kuita ikoko, zvavari kuita. Asi, kutaura zvazviri, iri ibhokisi dema, hatigoni kutarisa mukati.

Tinogona kutarisa CPU mutoro mukati top, tinogona kuona kushandiswa kwendangariro nemamwe masystem ekushandisa, asi isu hatizokwanisa kutarisa mukati mePostgreSQL. Nokuda kweizvi tinoda mamwe maturusi.

Uye ndichienderera mberi, ini ndichakuudzai kuti nguva yacho yapera. Kana isu tikamiririra PostgreSQL muchimiro chechirongwa chakadaro, zvino zvichagoneka kupindura uko nguva inopedzwa. Izvi zvinhu zviviri: iko kugadzirisa kwezvikumbiro zvevatengi kubva kumashandisirwo uye mabasa ekumashure ayo PostgreSQL inoita kuti irambe ichishanda.

Kana tikatanga kutarisa kumusoro kuruboshwe, tinogona kuona kuti zvikumbiro zvevatengi zvinogadziriswa sei. Chikumbiro chinobva kuchikumbiro uye chikamu chemutengi chinovhurwa kune rimwe basa. Chikumbiro chinopfuudzwa kune anoronga. Murongi anovaka chirongwa chemubvunzo. Inoitumira zvakare kuti iitwe. Kune imwe mhando ye block I / O data yakabatana nematafura uye indexes. Iyo data inodiwa inoverengwa kubva kumadhisiki kuenda mundangariro munzvimbo yakakosha inonzi "shared buffers". Mhedzisiro yemubvunzo, kana iri migadziriso, inodzima, inorekodhwa mugidhi rekutengesa muWAL. Rumwe ruzivo rwenhamba rwunopinda murogi kana muunganidzi wenhamba. Uye mhedzisiro yechikumbiro inodzoserwa kumutengi. Mushure meizvozvo, mutengi anogona kudzokorora zvese nechikumbiro chitsva.

Chii chatinacho nemabasa ekumashure uye maitiro ekumashure? Isu tine akati wandei maitiro anochengeta dhatabhesi kumusoro uye kushanda mune yakajairika maitiro ekushandisa. Aya maitiro achavharwawo mumushumo: aya autovacuum, checkpointer, maitiro ane hukama nekudzokorora, munyori wekumashure. Ndichabata pane mumwe nemumwe wavo sezvandinoshuma.

Ndeapi matambudziko ane nhamba?

Ruzivo rwakawanda. PostgreSQL 9.4 inopa 109 metrics yekuona nhamba yedata. Nekudaro, kana iyo dhatabhesi ichichengeta akawanda matafura, schemas, dhatabhesi, saka ese aya metrics achafanirwa kuwanzwa nenhamba inoenderana yematafura, dhatabhesi. Ndiko kuti, pane mamwe mashoko. Uye zviri nyore kwazvo kunyura mariri.
Dambudziko rinotevera nderekuti nhamba dzinomiririrwa nemakaunda. Tikatarisa manhamba aya, tinozoona macounters achiwedzera. Uye kana nguva yakawanda yapfuura kubva manhamba akaiswa patsva, tichaona mabhiriyoni ehukoshi. Uye hapana chavanotiudza.
Hakuna nhoroondo. Kana uine rumwe rudzi rwekukundikana, chimwe chinhu chakawa 15-30 maminitsi apfuura, haungakwanisi kushandisa nhamba uye ona zvakaitika 15-30 maminitsi apfuura. Iri idambudziko.
Kushaikwa kwechishandiso chakavakirwa muPostgreSQL idambudziko. Ivo vanogadzira kernel havape chero chekushandisa. Havana chinhu chakadaro. Vanongopa nhamba mu database. Ishandise, ita chikumbiro kwairi, chero chaunoda, wobva wazviita.
Sezvo pasina chishandiso chakavakirwa muPostgreSQL, izvi zvinokonzeresa rimwe dambudziko. Zvishandiso zvakawanda zvebato rechitatu. Kambani yega yega ine akawanda kana mashoma maoko akatwasuka iri kuyedza kunyora chirongwa chayo. Uye semhedzisiro, nharaunda ine zvakawanda zvekushandisa zvaunogona kushandisa kushanda nenhamba. Uye mune mamwe maturusi mune mamwe maficha, mune mamwe maturusi hapana mamwe maitiro, kana kune zvimwe zvitsva. Uye mamiriro ezvinhu anomuka ekuti iwe unofanirwa kushandisa maviri kana matatu kana mana maturusi anopindirana uye ane mabasa akasiyana. Izvi hazvifadzi.

Chii chinotevera kubva pane izvi? Zvakakosha kukwanisa kutora nhamba zvakananga kuitira kuti usatsamira pamapurogiramu, kana neimwe nzira kuvandudza zvirongwa izvi pachako: wedzera mamwe mabasa kuti uwane rubatsiro rwako.

Uye iwe unoda ruzivo rwekutanga rweSQL. Kuti uwane imwe data kubva kune manhamba, iwe unofanirwa kuita SQL mibvunzo, i.e. iwe unofanirwa kuziva kuti kusarudzwa, kujoinwa kunoitwa sei.

Nhamba dzinotiudza zvinhu zvakawanda. Vanogona kupatsanurwa muzvikamu.

Chikamu chekutanga zviitiko zviri kuitika mudhatabhesi. Apa ndipo panoitika chimwe chiitiko mudhatabhesi: mubvunzo, kuwana tafura, autovacuum, kuita, zvino izvi zvese zviitiko. Makaunda anoenderana nezviitiko izvi anowedzera. Uye tinogona kutevera zviitiko izvi.
Chikamu chechipiri ndechezvinhu zvezvinhu zvakadai sematafura, dhatabhesi. Vane zvinhu. Uku ndiko kukura kwematafura. Tinogona kuronda kukura kwematafura, kukura kwemaindekisi. Tinogona kuona shanduko mumasimba.
Uye chikamu chechitatu inguva yakashandiswa pachiitiko. Chikumbiro chiitiko. Iine mwero wayo chaiwo wenguva. Zvakatangira pano, zvikagumira pano. Tinogona kuitevera. Ingave nguva yekuverenga block kubva kudhisiki kana kunyora. Zviro izvi zvinorondwawo.

Mabviro ehuwandu anoratidzwa sezvizvi:

Mundangariro dzakagovaniswa (shared buffers) pane chikamu chekuisa static data ipapo, kune zvakare iwo macounter anogara achiwedzerwa kana zvimwe zviitiko zvichiitika, kana dzimwe nguva dzinomuka mukushanda kwedhatabhesi.
Macounter ese aya haawanikwe kumushandisi uye haatombowanikwe kune maneja. Izvi zvinhu zvakaderera. Kuti uvawane, PostgreSQL inopa chinongedzo muchimiro cheSQL mabasa. Tinogona kuita sarudzo tichishandisa aya mabasa uye towana imwe mhando yemetric (kana seti yemametric).
Nekudaro, hazvisi nyore nguva dzose kushandisa mabasa aya, saka mabasa ndiwo hwaro hwemaonero (VIEWs). Aya ndiwo matafura chaiwo anopa zviverengero pane chaiyo subsystem, kana pane imwe seti yezviitiko mudhatabhesi.
Aya akavakirwa-mukati maonero (VIEWs) ndiwo akanyanya mushandisi interface yekushanda nenhamba. Iwo anowanikwa nekusarudzika pasina mamwe marongero, unogona kuashandisa nekukurumidza, tarisa, tora ruzivo kubva ipapo. Uye kune zvakare zvirevo. Contribs zviri pamutemo. Iwe unogona kuisa iyo postgresql-contrib package (semuenzaniso, postgresql94-contrib), takura iyo inodiwa module mukumisikidzwa, tsanangura maparamendi ayo, tangazve PostgreSQL uye unogona kuishandisa. (Cherechedza. Zvichienderana nekugovera, mune ichangoburwa vhezheni ye contrib iyo package chikamu cheiyo main package).
Uye kune unofficial contrib. Ivo havana kupihwa neyakajairika PostgreSQL kugovera. Dzinofanirwa kuunganidzwa kana kuiswa seraibhurari. Sarudzo dzinogona kuve dzakasiyana zvakanyanya, zvichienderana nekuti mugadziri weiyi isiri yepamutemo contrib akauya nei.

Iyi siraidhi inoratidza ese iwo maonero (VIEWs) uye mamwe eaya mabasa anowanikwa muPostgreSQL 9.4. Sezvatinoona, kune zvakawanda zvavo. Uye zviri nyore kuvhiringika kana iwe uchisangana nazvo kekutanga.

Zvisinei, kana tikatora mufananidzo yapfuura Как тратится время на PostgreSQL uye inoenderana nerondedzero iyi, tinowana mufananidzo uyu. Maonero ega ega (maonero), kana basa rega rega, tinogona kushandisa kune chimwe chinangwa kana chimwe kuti tiwane nhamba dzakakodzera kana isu tine PostgreSQL ichimhanya. Uye isu tinogona kutowana ruzivo nezve mashandiro eiyo subsystem.

Chekutanga chatichatarisa ndechekuti pg_stat_database. Sezvatinoona, ichi ndicho chinomiririra. Rine ruzivo rwakawanda. Ruzivo rwakasiyana zvakanyanya. Uye inopa ruzivo rwakakosha rwezvatiri kuitika mudhatabhesi.

Chii chatingatora kubva ipapo? Ngatitange nezvinhu zviri nyore.

select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;

Chinhu chekutanga chatinogona kutarisa ndeye cache hit percentage. Cache hit percentage ndiyo inobatsira metric. Iyo inokutendera iwe kufungidzira kuti ingani data inotorwa kubva kune yakagovaniswa buffers cache, uye yakawanda sei inoverengwa kubva kudhisiki.

Zviri pachena kuti iyo yakawanda cache hit yatinayo, zviri nani. Isu tinoongorora metric iyi sechikamu. Uye, semuenzaniso, kana tiine chikamu cheiyi cache hits inopfuura 90%, zvino izvi zvakanaka. Kana ikadonha pasi pe90%, saka isu hatina ndangariro yakakwana yekuchengeta musoro unopisa wedata mundangariro. Uye kuti ushandise iyi data, PostgreSQL inomanikidzwa kuwana dhisiki uye izvi zvinononoka pane kana iyo data yakaverengwa kubva mundangariro. Uye iwe unofanirwa kufunga nezve kuwedzera ndangariro: kana kuwedzera yakagovaniswa mabhafa, kana kuwedzera iron memory (RAM).

select
datname,
(xact_commit*100)/(xact_commit+xact_rollback) as c_ratio,
deadlocks, conflicts,
temp_file, pg_size_pretty(temp_bytes) as temp_size
from pg_stat_database;

Chii chimwe chinogona kutorwa kubva mukuita uku? Iwe unogona kuona iyo anomalies inoitika mu database. Chii chinoratidzwa pano? Kune kuzvipira, kudzoreredza, kugadzirwa kwemafaira enguva pfupi, saizi yavo, madhiri uye kupokana.

Tinogona kushandisa chikumbiro ichi. Iyi SQL iri nyore. Uye isu tinogona kuzvionera iyi data pachedu.

Uye hezvino zvikumbaridzo zvakakosha. Isu tinotarisa chiyero chekuita uye rollbacks. Commits inosimbisa budiriro yekutengeserana. Rollbacks ndeyekudzosera kumashure, i.e. kutengeserana kwakaita rimwe basa, kudzvanya dhatabhesi, kufunga chimwe chinhu, uye ipapo kukundikana kwakaitika, uye mhedzisiro yekutengeserana inoraswa. i.e. kuwanda kwema rollbacks anoramba achiwedzera akaipa. Uye iwe unofanirwa kuvadzivirira neimwe nzira, uye gadzirisa kodhi kuti izvi zvisaitike.

Kusawirirana kunoenderana nekudzokorora. Uye dzinofanira kudziviswawo. Kana iwe uine mimwe mibvunzo inoitwa pane replica uye kukakavara kuchimuka, saka unofanirwa kuongorora makakatanwa aya woona zvinoitika. Tsanangudzo inogona kuwanikwa mumatanda. Uye gadzirisa kusawirirana kuti zvikumbiro zvekushandisa zvishande pasina zvikanganiso.

Deadlocks zvakare mamiriro akaipa. Kana zvikumbiro zvichikwikwidza zviwanikwa, chikumbiro chimwe chakawana sosi imwe ndokutora kiyi, chikumbiro chechipiri chakawana chechipiri chimbo uye ndokutorawo kiyi, uyezve zvikumbiro zvese zvakawana zviwanikwa zvemumwe uye zvakavharwa zvakamirira muvakidzani kuti aburitse kiyi. Iyi idambudziko zvakare. Ivo vanofanirwa kutariswa pamwero wekunyorazve zvikumbiro uye kuenzanisa kuwana zviwanikwa. Uye kana iwe ukaona kuti madhiri ako ari kuramba achiwedzera, iwe unofanirwa kutarisa zvakadzama mumatanda, ongorora mamiriro amuka uye ona kuti dambudziko nderei.

Mafaira enguva pfupi (temp_files) akashata zvakare. Kana chikumbiro chemushandisi chisina chiyeuchidzo chakakwana kuti chigadzirire iyo inoshanda, data yenguva pfupi, inogadzira faira pa diski. Uye mabasa ose aaigona kuita mune imwe nguva buffer mundangariro, anotanga kuita kare padhisiki. Inononoka. Izvi zvinowedzera nguva yekubvunza mibvunzo. Uye mutengi akatumira chikumbiro kuPostgreSQL anogashira mhinduro zvishoma gare gare. Kana zvese izvi zviitwa mundangariro, Postgres inopindura nekukurumidza uye mutengi anomirira zvishoma.

pg_stat_bgwriter - Aya maonero anotsanangura kushanda kwemashure maviri ePostgreSQL kumashure: checkpointer и background writer.

Kutanga, ngationgororei mapoinzi ekudzora, anonzi. checkpoints. Chii chinonzi chekuongorora? Nzvimbo yekutarisa inzvimbo murogi yekutengeserana inoratidza kuti shanduko dzese dze data dzakaitwa murogi dzakanyatso kuwiriraniswa nedata riri pa diski. Maitiro acho, zvichienderana nebasa uye zvigadziriso, zvinogona kureba uye zvakanyanya kusanganisa mapeji akasviba mumabhafa akagovaniswa ane data mafaera padhisiki. Ndechei? Kana PostgreSQL yaiwana dhisiki nguva dzese uye kutora data kubva ipapo, uye kunyora data pane yega yega yekuwana, yaizononoka. Naizvozvo, PostgreSQL ine chikamu chekurangarira, saizi yacho inotsamira pamatanho mukugadzirisa. Postgres inogovera data rekushanda mundangariro iyi kuti iwedzere kugadzirisa kana kubvunza. Panyaya yezvikumbiro zvekuchinja data, zvinoshandurwa. Uye isu tinowana mavhezheni maviri e data. Imwe iri mundangariro, imwe iri padisiki. Uye nguva nenguva unofanirwa kuwiriranisa iyi data. Tinoda izvo zvinoshandurwa mundangariro kuti zvienderane kune dhisiki. Izvi zvinoda kuongorora.

Checkpoint inoenda nemabhafa akagovaniswa, inomaka mapeji ane tsvina ayo anodiwa pakutarisa. Inobva yatanga yechipiri kupfuura kuburikidza nemabhafa akagovaniswa. Uye mapeji akaiswa chiratidzo chekutarisa, anotoaenzanisa. Saka, iyo data yakawiriraniswa nechekare ne diski.

Kune marudzi maviri ekudzora mapoinzi. Imwe yekutarisa inoitwa panguva yekupera. Iyi yekutarisa inobatsira uye yakanaka - checkpoint_timed. Uye kune nzvimbo dzekutarisa pazvinodiwa - checkpoint required. Kuongorora kwakadaro kunoitika kana isu tine yakakura kwazvo data rekodhi. Takarekodha matanda akawanda ekutengeserana. Uye PostgreSQL inotenda kuti inoda kuwiriranisa zvese izvi nekukurumidza sezvinobvira, ita cheki uye uenderere mberi.

Uye kana wakatarisa nhamba pg_stat_bgwriter uye ona zvaunazvo checkpoint_req yakakura kudarika checkpoint_timed, saka izvi zvakaipa. Nei zvakaipa? Izvi zvinoreva kuti PostgreSQL iri pasi pekunetsekana nguva dzose kana ichida kunyora data kune disk. Checkpoint by timeout haina kunyanya kunetseka uye inoitwa zvinoenderana nehurongwa hwemukati uye, sekunge, yakatambanudzwa nekufamba kwenguva. PostgreSQL ine kugona kumbomira pabasa uye kwete kunetsa dhisiki subsystem. Izvi zvinobatsira kuPostgreSQL. Uye zvikumbiro zvinoitwa panguva yekutarisa hazvizosangana nekushushikana kubva pakuti iyo disk subsystem yakabatikana.

Uye kune matatu paramita kugadzirisa cheki:

сheckpoint_segments.
сheckpoint_timeout.
сheckpoint_competion_target.

Vanokutendera kuti udzore kushanda kwenzvimbo dzekutonga. Asi handisi kuzoramba ndichifunga nezvazvo. Kurudziro yavo inyaya yakaparadzana.

Yambiro: Shanduro 9.4 yakatariswa mumushumo haisisina basa. Mune shanduro dzemazuva ano dzePostgreSQL, iyo parameter checkpoint_segments yakatsiviwa nema parameters min_wal_size и max_wal_size.

Iyo inotevera subsystem ndeye munyori wekumashure - background writer. Arikuitei? Inogara ichimhanya muchiuno chisingaperi. Inotarisa mapeji mumabhafa akagovaniswa uye inotsvaira mapeji akasviba ayo anowana kudhisiki. Nenzira iyi, inobatsira chekicho kuita basa shoma panguva yekutarisa.

Chii chimwe chaari kudiwa? Inopa kudiwa kwemapeji akachena mumabhafa akagovaniswa kana akangoerekana adiwa (muhuwandu uye nekukurumidza) kugadzirisa data. Ngatitii mamiriro amuka apo chikumbiro chaida mapeji akachena uye atove mumabhafa akagovaniswa. Postgres backend anongodzitora odzishandisa, hapana chaanofanira kuchenesa iye pachake. Asi kana kamwe kamwe pasina mapeji akadaro, iyo backend inomira uye inotanga kutsvaga mapeji kuti aiswe ku diski uye otora kune zvaanoda - izvo zvinokanganisa nguva yechikumbiro chirikuitwa. Kana iwe ukaona kuti une parameter maxwritten_clean hombe, izvi zvinoreva kuti munyori wekumashure haasi kuita basa rayo uye unofanirwa kuwedzera ma parameter bgwriter_lru_maxpageskuitira kuti aite basa rakawanda mudenderedzwa rimwe, jekesa mamwe mapeji.

Uye chimwe chinoratidza chinobatsira zvikuru buffers_backend_fsync. Backends haiite fsync nekuti inononoka. Ivo vanopfuura fsync kumusoro kweIO stack checkpointer. Iyo checkpointer ine mutsara wayo, inogara ichiita fsync uye inowiriranisa mapeji mundangariro nemafaira ari pa diski. Kana iyo yekutarisa mutsara yakakura uye yakazara, ipapo iyo yekumashure inomanikidzwa kuita fsync pachayo uye izvi zvinodzikisira kumashure., i.e. mutengi achagamuchira mhinduro gare gare kupfuura zvayaigona. Kana iwe ukaona kuti iwe une kukosha uku kwakakura kupfuura zero, saka ichi chatova dambudziko uye iwe unofanirwa kutarisisa kune marongero emunyori wekumashure uye zvakare kuongorora mashandiro eiyo disk subsystem.

Yambiro: _Chinyorwa chinotevera chinotsanangura maonero enhamba ane chekuita nekudzokorora. Mazhinji ekuona uye mazita emabasa akatumidzwa zita muPostgres 10. Mhedzisiro yemarenames yaive yekutsiva. xlog pamusoro wal и location pamusoro lsn mumabasa/mazita ekuona, nezvimwe. Kunyanya muenzaniso, basa pg_xlog_location_diff() rakatumidzwa zita rokuti pg_wal_lsn_diff()._

Tine zvakawanda pano zvakare. Asi isu tinongoda zvinhu zvine chekuita nenzvimbo.

Kana tikaona kuti hunhu hwese hwakaenzana, saka izvi zvakanaka uye replica haina kusarira kumashure kwatenzi.

Iyi hexadecimal chinzvimbo pano ndiyo nzvimbo mune yekutengeserana log. Inogara ichiwedzera kana paine chimwe chiitiko mudhatabhesi: kuisa, kubvisa, nezvimwe.

сколько записано xlog в байтах
$ select
pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000');
лаг репликации в байтах
$ select
client_addr,
pg_xlog_location_diff(pg_current_xlog_location(), replay_location)
from pg_stat_replication;
лаг репликации в секундах
$ select
extract(epoch from now() - pg_last_xact_replay_timestamp());

Kana zvinhu izvi zvakasiyana, saka pane imwe mhando yekusarira. Lag ndiyo lag replica kubva kuna tenzi, i.e. data inosiyana pakati pemaseva.

Pane zvikonzero zvitatu zvekunonoka:

Ndiyo disk subsystem isingakwanise kubata faira sync kunyora.
Izvi zvinogoneka zvikanganiso zvetiweki, kana network kuwandisa, kana data riine nguva yekusvika kune replica uye haikwanise kuiburitsa.
Uye processor. Iyo processor inyaya isingawanzoitiki. Uye ndakazviona kaviri kana katatu, asi izvo zvinogona kuitika zvakare.

Uye heino mibvunzo mitatu inotitendera kushandisa manhamba. Isu tinogona kufungidzira kuti yakawanda sei yakanyorwa mubhuku redu rekutengeserana. Pane basa rakadaro pg_xlog_location_diff uye isu tinogona kufungidzira kudzokorodza lag mumabhaiti nemasekonzi. Isu tinoshandisawo kukosha kubva pakuona uku (VIEWs) kune izvi.

Cherechedza: _Panzvimbo pepg_xlog_locationdiff () basa, unogona kushandisa bvisa opareta uye kubvisa imwe nzvimbo kubva kune imwe. Comfortable.

Nekunonoka, kuri mumasekonzi, pane imwe nguva. Kana pasina chiitiko pane tenzi, kutengeserana kwaivepo anenge maminetsi gumi nemashanu apfuura uye hapana chiitiko, uye kana tikatarisa iyi lag pane replica, tinoona lag yemaminetsi gumi nemashanu. Izvi zvakakodzera kuyeuka. Uye zvinogona kutungamirira kune stupor kana iwe wakatarisa iyi lag.

pg_stat_all_tables ndeimwe maonero anobatsira. Inoratidza nhamba pamatafura. Kana isu tine matafura mudhatabhesi, pane chimwe chiitiko nacho, zvimwe zviito, tinogona kuwana ruzivo urwu kubva pakuona uku.

select
relname,
pg_size_pretty(pg_relation_size(relname::regclass)) as size,
seq_scan, seq_tup_read,
seq_scan / seq_tup_read as seq_tup_avg
from pg_stat_user_tables
where seq_tup_read > 0 order by 3,4 desc limit 5;

Chinhu chekutanga chatinogona kutarisa ndeye sequential tafura scans. Nhamba yacho pachayo pashure pendima idzi haina hazvo kuipa uye hairatidzi kuti tinofanira kutoita chimwe chinhu.

Nekudaro, kune yechipiri metric - seq_tup_read. Iyi ndiyo nhamba yemitsara yakadzoswa kubva mukutevedzana kwekuongorora. Kana huwandu hweavhareji huchipfuura 1, 000, 10, 000, saka ichi chatova chiratidzo chekuti ungangoda kuvaka index pane imwe nzvimbo kuitira kuti masvikiro ave neindex, kana zvinokwanisika kukwirisa mibvunzo inoshandisa sequential scans kuitira kuti. izvi hazvina kuitika.

Muenzaniso wakapfava - ngatiti chikumbiro chine hombe OFFSET uye LIMIT yakakosha. Semuenzaniso, mitsara ye100 mutafura inoongororwa uye mushure meiyo mitsara ye000 inodiwa inotorwa, uye mitsara yakapfuura yakaongororwa inoraswa. Iyi inyayawo yakaipa. Uye zvikumbiro zvakadaro zvinoda kugadziriswa. Uye heino mubvunzo wakareruka weSQL waunogona kuiona uye kuongorora nhamba dzakagamuchirwa.

select
relname,
pg_size_pretty(pg_total_relation_size(relname::regclass)) as
full_size,
pg_size_pretty(pg_relation_size(relname::regclass)) as
table_size,
pg_size_pretty(pg_total_relation_size(relname::regclass) -
pg_relation_size(relname::regclass)) as index_size
from pg_stat_user_tables
order by pg_total_relation_size(relname::regclass) desc limit 10;

Tafura saizi inogona zvakare kuwanikwa uchishandisa iyi tafura uye uchishandisa mamwe mabasa pg_total_relation_size(), pg_relation_size().

Kazhinji, kune metacommands dt и di, iyo iwe yaunogona kushandisa muPSQL uye zvakare kuona tafura uye index saizi.

Zvisinei, kushandiswa kwemabasa kunotibatsira kuti titarise hukuru hwematafura, kunyange kutora zvinyorwa zvinyorwa, kana kuti tisingafungi indexes, uye totoita dzimwe fungidziro zvichienderana nekukura kwedhatabhesi, i.e. inokura sei nesu, ne kusimba kwakadii, uye tora mhedziso nezve saizi optimization.

Nyora chiitiko. Chii chinonzi rekodhi? Ngatitarisei kuvhiyiwa UPDATE - kushanda kwemitsara yekuvandudza mutafura. Muchokwadi, kugadzirisa maitiro maviri (kana kutopfuura). Uku kuisa imwe vhezheni yemutsara uye kumaka iyo yekare mutsara seyakasakara. Gare gare, autovacuum ichauya ichichenesa aya mavhezheni akasakara emitsara, maka nzvimbo ino seiripo kuti ishandiswezve.

Zvakare, kugadzirisa hakusi kwekugadzirisa tafura chete. Ichiri indekisi update. Kana iwe uine akawanda ma indexes patafura, ipapo nekuvandudza, ese ma indexes umo minda yakagadziridzwa mumubvunzo inotora chikamu inodawo kuvandudzwa. Idzi indekisi dzichavawo neshanduro dzemitsara dzakasakara dzinoda kucheneswa.

select
s.relname,
pg_size_pretty(pg_relation_size(relid)),
coalesce(n_tup_ins,0) + 2 * coalesce(n_tup_upd,0) -
coalesce(n_tup_hot_upd,0) + coalesce(n_tup_del,0) AS total_writes,
(coalesce(n_tup_hot_upd,0)::float * 100 / (case when n_tup_upd > 0
then n_tup_upd else 1 end)::float)::numeric(10,2) AS hot_rate,
(select v[1] FROM regexp_matches(reloptions::text,E'fillfactor=(\d+)') as
r(v) limit 1) AS fillfactor
from pg_stat_all_tables s
join pg_class c ON c.oid=relid
order by total_writes desc limit 50;

Uye nekuda kwekugadzirwa kwayo, UPDATE ibasa rinorema. Asi vanogona kuitwa nyore. Eat hot updates. Ivo vakaonekwa muPostgreSQL vhezheni 8.3. Uye chii ichi? Ichi chigadziriso chakareruka icho chisingakonzerese kuti indexes ivakwe patsva. Ndiko kuti, isu takagadziridza rekodhi, asi chete rekodhi iri papeji (iri yetafura) yakagadziridzwa, uye ma indexes achiri kunongedzera kune imwecheteyo rekodhi peji. Pane zvishoma zveinonakidza logic yebasa, kana vacuum yauya, ipapo ine maketani aya hot inovakazve uye zvese zvinoramba zvichishanda pasina kuvandudza ma indexes, uye zvese zvinoitika nekutambisa kushoma kwezvinhu.

Uye kana uine n_tup_hot_upd hombe, yakanaka kwazvo. Izvi zvinoreva kuti zvigadziriso zvisingaremi zvinokunda uye izvi zvakachipa kwatiri maererano nezviwanikwa uye zvese zvakanaka.

ALTER TABLE table_name SET (fillfactor = 70);

Nzira yekuwedzera inzwi hot updateov? Tinogona kushandisa fillfactor. Iyo inosarudza saizi yenzvimbo yakachengeterwa yemahara paunenge uchizadza peji mutafura uchishandisa INSERTs. Kana kuisa patafura, vanozadza zvizere peji, usasiye nzvimbo isina chinhu mairi. Zvadaro peji itsva inosimbiswa. Iyo data inozadzwa mukati zvakare. Uye iyi ndiyo yakasarudzika maitiro, fillfactor = 100%.

Tinogona kuseta iyo fillfactor ku70%. Ndiko kuti, nekuisa, peji nyowani yakagoverwa, asi chete 70% yepeji yakazadzwa. Uye isu tine 30% yasara mudura. Paunenge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchingerwa Uye hot_update ichaitwa. Izvi zvinoita kuti zvive nyore kunyora pamatafura.

select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));

Autovacuum mutsara. Autovacuum iyi subsystem iyo ine mashoma mashoma manhamba muPostgreSQL. Isu tinongoona mumatafura mune pg_stat_activity kuti mangani mavacuum atinawo panguva ino. Nekudaro, zvakanyanya kuoma kunzwisisa kuti mangani matafura mumutsara ane pakufamba.

Cherechedza: _Kubva Postgres 10, mamiriro ezvinhu nekutevera vacuum vacuum akavandudza zvakanyanya - iyo pg_stat_progress maonero aonekwa.vacuum, iyo inorerutsa zvakanyanya nyaya yekutarisa autovacuum.

Tinogona kushandisa mubvunzo wakareruka uyu. Uye isu tinogona kuona apo vacuum inofanira kuitwa. Asi, sei uye riini vacuum inofanira kutanga? Aya ndiwo mavhezheni ekare etambo dzandambotaura nezvazvo. Kuvandudzwa kwaitika, shanduro itsva yemutsara yaiswa. Imwe shanduro yechinyakare yetambo yaonekwa. Tafura pg_stat_user_tables pane parameter yakadaro n_dead_tup. Inoratidza nhamba yemitsara "yakafa". Uye kana nhamba yemitsara yakafa yave yakawanda kupfuura imwe chikumbaridzo, autovacuum ichauya patafura.

Uye chikumbaridzo ichi chinoverengwa sei? Iyi iperesenti chaiyo yehuwandu hwemitsara mutafura. Pane parameter autovacuum_vacuum_scale_factor. Inotsanangura chikamu. Ngatitii 10% + pane imwe nheyo yekuwedzera ye50 mitsetse. Uye chii chinoitika? Kana isu tine mitsara yakafa kupfuura "10% + 50" yemitsara yese patafura, tinoisa tafura pane autovacuum.

select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));

Zvisinei, pane imwe pfungwa. Basic thresholds for parameters av_base_thresh и av_scale_factor inogona kugoverwa yega. Uye, maererano, chikumbaridzo hachizove chepasirese, asi munhu patafura. Naizvozvo, kuti uverenge, ipapo iwe unofanirwa kushandisa manomano nemanomano. Uye kana iwe uchifarira, unogona kutarisa ruzivo rwevatinoshanda navo kubva kuAvito (iyo link pane slide haina basa uye yakagadziridzwa muzvinyorwa).

Vakanyorera munin pluginizvo zvinorangarira zvinhu izvi. Pane jira retsoka pamapepa maviri. Asi iye anofunga nenzira kwayo uye zvinonyatso shanda anotibvumira kuongorora kwatinoda yakawanda vacuum yematafura, pane zvishoma.

Chii chatingaita nezvazvo? Kana isu tiine mutsara wakareba uye autovacuum haigone kurarama, saka tinogona kuwedzera huwandu hwevashandi vevacuum, kana kungoita kuti vacuum ive nehasha.kuitira kuti itange kutanga, inogadzirisa tafura muzvidimbu zvidiki. Uye nokudaro mutsara uchadzikira. - Chinhu chikuru pano ndechekucherechedza mutoro pa disks, nokuti. Chinhu chevacuum hachisi chemahara, kunyangwe nekuuya kweSSD / NVMe zvishandiso, dambudziko rave kushomeka.

pg_stat_all_indexes nhamba pane indexes. Haasi mukuru. Uye isu tinogona kuwana ruzivo rwekushandiswa kwemaindex kubva mairi. Uye semuenzaniso, isu tinokwanisa kuona kuti ndeapi ma index atinawo ekuwedzera.

Sezvandambotaura, update haisi kungovandudza matafura chete, asi zvakare kugadzirisa indexes. Saizvozvo, kana isu tiine akawanda ma indexes patafura, saka kana tichivandudza mitsara mutafura, indexes yeminda ine indexed inodawo kuvandudzwa, uye. kana tiine ma indexes asina kushandiswa ayo asina index scans, ivo vanorembera nesu seballast. Uye unofanira kuvabvisa. Nokuda kweizvi tinoda munda idx_scan. Isu tinongotarisa huwandu hwe index scans. Kana ma indexes ane zero scans pamusoro penguva yakareba yekuchengetera nhamba (inenge 2-3 mavhiki), saka kazhinji aya indekisi yakaipa, isu tinofanirwa kuabvisa.

Cherechedza: Paunenge uchitsvaga ma indexes asina kushandiswa mune yekutepfenyura kudzokorora masumbu, iwe unofanirwa kutarisa ese node dze cluster, nekuti. nhamba haisi yepasi rose, uye kana iyo index isingashandiswi pane tenzi, inogona kushandiswa pane replicas (kana pane mutoro).

Zvisungo zviviri:

https://github.com/dataegret/pg-utils/blob/master/sql/low_used_indexes.sql

http://www.databasesoup.com/2014/05/new-finding-unused-indexes-query.html

Iyi mimwe mienzaniso yemhando yepamusoro yekutarisa ma indexes asina kushandiswa.

Yechipiri link ndeye mubvunzo unonakidza. Pane pfungwa isiri-nhando mazviri. Ndinoikurudzira kuti iongororwe.

Chii chimwe chinofanira kupfupikiswa nema indexes?

Indekisi dzisina kushandiswa dzakaipa.
Vanotora nzvimbo.
Deredzai mashandiro ekuvandudza.
Kuwedzera basa rekutsvaira.

Kana tikabvisa ma indexes asina kushandiswa, saka isu tichaita chete kuti database ive nani.

Maonero anotevera ndeaya pg_stat_activity. Iyi ndiyo analogue yezvishandiso ps, chete muPostgreSQL. Kana ps'ohm iwe unotarisa maitiro ari musystem yekushandisa, ipapo pg_stat_activity inokuratidza chiitiko mukati mePostgreSQL.

Chii chatingatora kubva ipapo?

select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;

Isu tinogona kuona chiitiko chese chiri kuitika mudhatabhesi. Tinogona kuita kutumirwa kutsva. Zvese zvakaputika ipapo, zvitsva zvinongedzo hazvigamuchirwe, zvikanganiso zviri kudururwa mukushandisa.

select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;

Tinogona kumhanyisa mubvunzo seizvi uye toona huwandu hweseji hwekubatanidza hunoenderana nehukuru hwekubatanidza muganho uye toona kuti ndiani watinonyanya kubatanidza. Uye mune iyi kesi yakapihwa, tinoona iyo mushandisi cron_role yakavhura 508 zvinongedzo. Uye chimwe chinhu chakaitika kwaari. Unofanira kubata nazvo woona. Uye zvinogoneka kuti iyi imhando yehuwandu husinganzwisisike hwekubatanidza.

Kana tiine mutoro weOLTP, mibvunzo inofanirwa kukurumidza, nekukurumidza, uye hapafanirwe kuve nemibvunzo mirefu. Zvisinei, kana pane zvikumbiro zvenguva refu, saka munguva pfupi hapana chekunetseka pamusoro, asi nekufamba kwenguva, mibvunzo mirefu inokuvadza dhatabhesi, inowedzera bloat mhedzisiro yematafura kana kupatsanurwa kwetafura kuchiitika. Zvese zviri zviviri bloat uye refu mibvunzo inoda kuraswa.

select
client_addr, usename, datname,
clock_timestamp() - xact_start as xact_age,
clock_timestamp() - query_start as query_age,
query
from pg_stat_activity order by xact_start, query_start;

Ndapota cherechedza: nechikumbiro chakadaro, tinogona kutsanangura zvikumbiro zvenguva refu uye kutengeserana. Isu tinoshandisa basa clock_timestamp() kuziva nguva yekushanda. Zvikumbiro zvirefu zvatakawana, tinogona kuzviyeuka, tozviita explain, tarisa zvirongwa uye neimwe nzira gadzirisa. Isu tinopfura zvikumbiro zvazvino zvenguva refu uye tichirarama.

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

Mabatiro akashata haana basa mukutengeserana uye asina chekuita mukutengesa (kubviswa) kutengeserana.

Zvinorevei? Transactions ine nyika dzakawanda. Uye imwe yeidzi nyika inogona kutora chero nguva. Pane ndima yekutsanangura nyika state mukuona uku. Uye isu tinoishandisa kuona iyo nyika.

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

Uye, sezvandambotaura pamusoro, idzi mbiri dzinoti kusaita basa mukutengesa uye kusaita basa mukutengesa (kubviswa) kwakaipa. Chii? Apa ndipo apo application yakavhura kutengeserana, ikaita zvimwe zviito uye ikaita bhizinesi rayo. Mutengesi unoramba wakavhurika. Inorembera, hapana chinoitika mairi, inotora chinongedzo, kukiya pamitsara yakashandurwa uye inogona kuramba ichiwedzera bloat yemamwe matafura, nekuda kwekuvakwa kweiyo Postrges transactional injini. Uye kutengeserana kwakadaro kunofanirawo kupfurwa, nokuti zvinokuvadza kazhinji, chero zvakadaro.

Kana iwe ukaona kuti une anopfuura 5-10-20 avo mune yako dhatabhesi, saka iwe unofanirwa kunetseka uye kutanga kuita chimwe chinhu navo.

Pano tinoshandisawo nguva yekuverenga clock_timestamp(). Isu tinopfura kutengeserana, isu tinogonesa application.

Sezvandambotaura pamusoro, makiyi ndeapo maviri kana anopfuura ekutengeserana anokwikwidza kune imwe kana boka rezviwanikwa. Nokuda kwaizvozvi tine munda waiting ine boolean value true kana false.

Chokwadi - izvi zvinoreva kuti maitiro akamirira, chimwe chinhu chinoda kuitwa. Kana chirongwa chakamirira, ipapo mutengi akatanga hurongwa anenge akamirirawo. Mutengi ari mubrowser anogara uye zvakare anomirira.

Yambiro: _Kutanga kubva kuPostgres 9.6, munda waiting yakabviswa ndokutsiviwa nemimwe minda miviri inodzidzisa wait_event_type и wait_event._

Chii chandinofanira kuita? Kana iwe ukaona chokwadi kwenguva yakareba, saka iwe unofanirwa kubvisa zvikumbiro zvakadaro. Tinongopfura kutengeserana kwakadaro. Isu tinonyorera kuvagadziri izvo zvinoda kuvandudzwa neimwe nzira kuti pasave nemujaho wezviwanikwa. Uye ipapo vagadziri vanokwirisa application kuti izvi zvisaitike.

Uye yakanyanyisa, asi inogona kunge isiri inouraya nyaya kuitika kwe deadlocks. Mabatiro maviri akagadziridza zviwanikwa zviviri, vobva vazviwana zvakare, vatove kune zvakapesana zviwanikwa. PostgreSQL mune iyi kesi inotora uye inopfura iyo kutengeserana pachayo kuitira kuti imwe yacho ienderere mberi ichishanda. Iyi inguva yakafa uye haazvinzwisise. Naizvozvo, PostgreSQL inomanikidzwa kutora matanho akanyanya.

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/c4_06_show_locked_queries.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_95.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_96.sql

http://big-elephants.com/2013-09/exploring-query-locks-in-postgres/

Uye heino mibvunzo miviri inobvumidza iwe kuteedzera makiyi. Isu tinoshandisa maonero pg_locks, izvo zvinokutendera kuti utarise zvinorema zvivharo.

Uye chinongedzo chekutanga igwaro rekukumbira pacharo. Yakareba.

Uye chechipiri chinongedzo inyaya pakiyi. Inobatsira kuverenga, inonakidza zvikuru.

Saka chii chatinoona? Tinoona zvikumbiro zviviri. Transaction ne ALTER TABLE chinhu chinovharira kutengeserana. Yakatanga, asi haina kupera, uye application yakatumira iyi kutengeserana iri kuita zvimwe zvinhu kumwe kunhu. Uye chikumbiro chechipiri ndechekuvandudza. Inomirira kuti alter table ipedze isati yaenderera mberi nebasa rayo.

Aya ndiwo maziviro atingaita kuti ndiani akavharira ani, ndiani anobata ani, uye isu togona kubata nazvo mberi.

Iyo inotevera module ndeye pg_stat_statements. Sezvandambotaura, imodule. Kuti uishandise, unofanirwa kurodha raibhurari yayo mukugadzirisa, tangazve PostgreSQL, isa module (nemurairo mumwe), uye ipapo tichava nemaonero matsva.

Cреднее время запроса в милисекундах
$ select (sum(total_time) / sum(calls))::numeric(6,3)
from pg_stat_statements;

Самые активно пишущие (в shared_buffers) запросы
$ select query, shared_blks_dirtied
from pg_stat_statements
where shared_blks_dirtied > 0 order by 2 desc;

Chii chatingatora kubva ipapo? Kana tikataura nezve zvinhu zviri nyore, tinogona kutora avhareji yekubvunza nguva. Nguva iri kukura, zvinoreva kuti PostgreSQL iri kupindura zvishoma nezvishoma uye chimwe chinhu chinoda kuitwa.

Isu tinogona kuona iyo inonyanya kushanda yekunyora kutengeserana mudhatabhesi inoshandura data mumabhafa akagovaniswa. Ona kuti ndiani anogadziridza kana kudzima data ipapo.

Uye isu tinogona kungotarisa kune dzakasiyana nhamba dzezvikumbiro izvi.

https://github.com/dataegret/pg-utils/blob/master/sql/global_reports/query_stat_total.sql

Isu tiri pg_stat_statements inoshandiswa kugadzira zvinyorwa. Isu tinogadzirisa zvakare nhamba kamwe pazuva. Ngatizviunganidze. Tisati tagadzirisa nhamba nguva inotevera, tinovaka rondedzero. Heino link kune report. Unogona kuzvionera.

Tiri kuitei? Isu tinoverenga nhamba dzese dzemibvunzo yese. Zvino, pamubvunzo wega wega, isu tinoverengera mupiro wayo wega kune iyi nhamba yese.

Uye chii chatinogona kuona? Isu tinogona kuona iyo yakazara nguva yekuitwa kwese zvikumbiro zveimwe mhando maererano nemashure ezvimwe zvikumbiro zvese. Tinogona kutarisa CPU uye I / O kushandiswa maererano nemufananidzo wakazara. Uye kutokwirisa zvikumbiro izvi. Isu tiri kugadzira mibvunzo yepamusoro inoenderana neshumo iyi uye tiri kutowana chikafu chekufunga pamusoro pezvatinofanira kugadzirisa.

Chii chatinacho kuseri kwezviitiko? Pachine zvinyorwa zvishoma zvandisina kufunga, nekuti nguva ishoma.

kune pgstattuple iriwo imwe module yekuwedzera kubva kune yakajairwa contribs package. Inokubvumira kuti uongorore bloat matafura, anonzi. kupatsanurwa kwetafura. Uye kana kupatsanurwa kwakakura, unofanirwa kuibvisa, shandisa zvishandiso zvakasiyana. Uye basa pgstattuple inoshanda kwenguva yakareba. Uye iyo yakawanda matafura, iyo yakareba ichashanda.

Chikamu chinotevera ndeche pg_buffercache. Iyo inokutendera iwe kuti uongorore akagovaniswa mabuffers: zvakanyanya sei uye ndeapi mapeji buffer mapeji anoshandiswa. Uye zvinongobvumidza iwe kuti utarise mumabhafa akagovaniswa uye kuongorora zviri kuitika ipapo.

Iyo inotevera module ndeye pgfincore. Iyo inokutendera iwe kuita yakaderera-pamwero wetafura mashandiro kuburikidza nehurongwa hwekufona mincore(), i.e. inobvumidza iwe kurodha tafura mumabhafa akagoverwa, kana kuiburitsa. Uye inobvumira, pakati pezvimwe zvinhu, kuongorora iyo cache peji yeiyo inoshanda sisitimu, ndiko kuti, yakawanda sei iyo tafura inogara mune peji cache, mune yakagovaniswa mabuffers, uye inongobvumidza iwe kuti uongorore mutoro uri patafura.

Iyo inotevera module ndeye pg_stat_kcache. Iyo zvakare inoshandisa iyo system call getrusage(). Uye inozviita pamberi uye mushure mekunge chikumbiro chaitwa. Uye mune zviverengero zvakawanikwa, zvinotibvumira kufungidzira kuti yakawanda sei chikumbiro chedu chakashandisa pa diski I / O, kureva, mashandiro neiyo faira system uye anotarisa pakushandiswa kwe processor. Nekudaro, iyo module idiki (khe-khe) uye nebasa rayo inoda PostgreSQL 9.4 uye pg_stat_statements, yandambotaura.

Iko kukwanisa kushandisa nhamba kunobatsira. Iwe haudi wechitatu bato software. Unogona kutarisa, kuona, kuita chimwe chinhu, kuita.
Kushandisa nhamba kuri nyore, iri pachena SQL. Iwe wakaunganidza chikumbiro, wakachiunganidza, wakachitumira, wakachitarisa.
Nhamba dzinobatsira kupindura mibvunzo. Kana uine mibvunzo, iwe unotendeukira kune nhamba - tarisa, tora mhedziso, ongorora mhedzisiro.
Uye kuedza. Zvikumbiro zvakawanda, data rakawanda. Iwe unogona kugara uchikwenenzvera mumwe mubvunzo uripo. Iwe unogona kugadzira yako vhezheni yechikumbiro inokodzera iwe zvirinani pane yekutanga uye woishandisa.