🥇Sungani mozama mu ziwerengero zamkati za PostgreSQL. Alexey Lesovsky

Zolemba za lipoti la Alexey Lesovsky la 2015 "Kuzama mozama mu PostgreSQL ziwerengero zamkati"

Chodzikanira kuchokera kwa wolemba lipoti: Ndikuzindikira kuti lipotili ndi la November 2015 - zaka zoposa 4 zapita ndipo nthawi yambiri yadutsa. Mtundu wa 9.4 womwe wafotokozedwa mu lipotilo sunathandizidwenso. Pazaka zapitazi za 4, zotulutsidwa zatsopano za 5 zatulutsidwa momwe muli zatsopano zambiri, zosintha ndi zosintha zokhudzana ndi ziwerengero, ndipo zina mwazinthuzi ndi zachikale ndipo sizothandiza. Pamene ndikubwereza, ndinayesa kuika chizindikiro malowa kuti asasocheretse owerenga. Sindinalembenso ndimezi, pali zambiri ndipo zotsatira zake zidzakhala lipoti losiyana kotheratu.

PostgreSQL DBMS ndi njira yayikulu, ndipo makinawa ali ndi ma subsystems ambiri, ntchito yolumikizidwa yomwe imakhudza mwachindunji magwiridwe antchito a DBMS. Panthawi yogwira ntchito, ziwerengero ndi zidziwitso zokhudzana ndi kayendetsedwe ka zigawozi zimasonkhanitsidwa, zomwe zimakulolani kuti muwone momwe PostgreSQL ikuyendera ndikuchitapo kanthu kuti mupititse patsogolo ntchito. Komabe, pali zambiri za izi ndipo zimaperekedwa m'njira yosavuta. Kukonza chidziwitsochi ndikutanthauzira nthawi zina ndi ntchito yosachepera, ndipo "zoo" ya zida ndi zofunikira zimatha kusokoneza ngakhale DBA yapamwamba.

Masana abwino Dzina langa ndine Aleksey. Monga Ilya adanena, ndilankhula za ziwerengero za PostgreSQL.

Ziwerengero zantchito za PostgreSQL. PostgreSQL ili ndi ziwerengero ziwiri. Ziwerengero za ntchito zomwe zidzakambidwe. Ndipo ziwerengero zapandandanda za kugawa kwa data. Ndilankhula mwachindunji za ziwerengero za PostgreSQL, zomwe zimatilola kuweruza momwe ntchito ikuyendera ndikuwongolera mwanjira ina.

Ndikuuzani momwe mungagwiritsire ntchito ziwerengero moyenera kuthetsa mavuto osiyanasiyana omwe muli nawo kapena mungakhale nawo.

Ndi chiyani chomwe sichikhala mu lipoti? Mu lipotilo sindikhudza ziwerengero za scheduler, chifukwa... Uwu ndi mutu wosiyana wa lipoti lapadera la momwe deta imasungidwira munkhokwe komanso momwe wokonzera mafunso amapeza lingaliro la momwe deta iyi imakhalira komanso kuchuluka kwake.

Ndipo sipadzakhala ndemanga za zida, sindingafanizire chinthu chimodzi ndi china. Sipadzakhala kutsatsa. Tiyeni tiyike pambali izo.

Ndikufuna kukuwonetsani kuti kugwiritsa ntchito ziwerengero ndikothandiza. Ndizofunikira. Ndi zotetezeka kugwiritsa ntchito. Zomwe timafunikira ndi SQL yokhazikika komanso chidziwitso choyambirira cha SQL.

Ndipo tiyeni tikambirane zimene ziwerengero kusankha kuthetsa mavuto.

Ngati tiyang'ana pa PostgreSQL ndikuyendetsa lamulo pa opareshoni kuti tiwone njira, tiwona "bokosi lakuda". Tidzawona njira zina zomwe zikuchita chinachake, ndipo kuchokera ku dzina tikhoza kulingalira zomwe akuchita kumeneko, zomwe akuchita. Koma kwenikweni, ndi bokosi lakuda, sitingathe kuyang'ana mkati.

Titha kuwona kuchuluka kwa CPU mkati top, titha kuyang'ana kugwiritsa ntchito kukumbukira ndi zida zina zamakina, koma sitingathe kuyang'ana mkati mwa PostgreSQL. Kwa ichi tikufuna zida zina.

Ndipo kupitilizabe, ndikuwuzani komwe nthawi imathera. Ngati tilingalira PostgreSQL ngati chithunzi chotere, ndiye kuti titha kuyankha komwe nthawi yatha. Izi ndi zinthu ziwiri: ndikukonza zopempha zamakasitomala kuchokera pamapulogalamu ndi ntchito zakumbuyo zomwe PostgreSQL imachita kuti ipitilize kuyendetsa.

Ngati tiyamba kuyang'ana pamwamba kumanzere, tikhoza kuona momwe zopempha za kasitomala zimapangidwira. Pempho limachokera ku pulogalamuyo ndipo gawo la kasitomala limatsegulidwa kuti agwire ntchito ina. Pempho litumizidwa kwa wokonza mapulani. Wopangayo amapanga dongosolo lamafunso. Amatumizanso kuti aphedwe. Pali mtundu wina wa block data input / zotuluka zogwirizana ndi matebulo ndi indexes. Deta yofunikira imawerengedwa kuchokera ku ma disks kukumbukira kupita kudera lapadera "zogawana nawo". Zotsatira za pempho, ngati ndizosintha, zochotsa, zimalembedwa mu chipika chogulitsira mu WAL. Zambiri zamawerengero zimathera mu chipika kapena osonkhanitsa ziwerengero. Ndipo zotsatira za pempho zimatumizidwa kwa kasitomala. Pambuyo pake kasitomala akhoza kubwereza chirichonse kachiwiri ndi pempho latsopano.

Nanga bwanji za ntchito zakumbuyo ndi njira zakumbuyo? Tili ndi njira zingapo zomwe zimasunga malo osungiramo zinthu zakale ndikugwira ntchito moyenera. Njirazi zidzakhudzidwanso mu lipoti: autovacuum, checkpointer, njira zokhudzana ndi kubwerezabwereza, wolemba zakumbuyo. Ndikhudza aliyense wa iwo monga ine lipoti.

Kodi pali mavuto otani ndi ziwerengero?

Pali zambiri zambiri. PostgreSQL 9.4 imapereka ma metric 109 owonera ziwerengero. Komabe, ngati nkhokwe imasunga matebulo ambiri, schemas, nkhokwe, ndiye kuti ma metric onsewa ayenera kuchulukitsidwa ndi kuchuluka kwa matebulo, ma database. Ndiko kuti, pali zambiri zambiri. Ndipo n’kosavuta kumiza m’menemo.
Vuto lotsatira ndiloti ziwerengero zimayimiridwa ndi zowerengera. Ngati tiyang'ana pa ziwerengerozi, tidzawona zowerengera zikuchulukirachulukira. Ndipo ngati nthawi yayitali yadutsa kuyambira pomwe ziwerengero zidakhazikitsidwanso, tiwona mabiliyoni ambiri. Ndipo satiuza kalikonse.
Palibe nkhani. Ngati mutakhala ndi vuto linalake, chinachake chinagwa 15-30 mphindi zapitazo, simungathe kugwiritsa ntchito ziwerengero ndikuwona zomwe zinachitika 15-30 mphindi zapitazo. Ili ndi vuto.
Kusowa kwa chida chopangidwa mu PostgreSQL ndi vuto. Opanga kernel samapereka chilichonse chothandizira. Iwo alibe chirichonse chonga icho. Amangopereka ziwerengero mu database. Gwiritsani ntchito, pemphani, chitani chilichonse chomwe mukufuna.
Popeza palibe chida chomangidwa mu PostgreSQL, izi zimayambitsa vuto lina. Zida zambiri za gulu lachitatu. Kampani iliyonse yomwe ili ndi manja olunjika kapena ocheperako ikuyesera kulemba pulogalamu yakeyake. Ndipo chifukwa chake, anthu ammudzi ali ndi zida zambiri zomwe zingagwiritsidwe ntchito pogwiritsira ntchito ziwerengero. Ndipo zida zina zimakhala ndi mphamvu zina, zida zina zilibe luso lina, kapena pali zina zatsopano. Ndipo pakachitika vuto loti muyenera kugwiritsa ntchito zida ziwiri, zitatu kapena zinayi zomwe zimalumikizana ndipo zimakhala ndi ntchito zosiyanasiyana. Izi sizosangalatsa.

Chotsatira pa izi? Ndikofunikira kuti muthe kutenga ziwerengero mwachindunji, kuti musadalire mapulogalamu, kapena mwinamwake kusintha mapulogalamuwa nokha: onjezani ntchito zina kuti mupindule nokha.

Ndipo muyenera kudziwa zoyambira za SQL. Kuti mupeze zambiri kuchokera ku ziwerengero, muyenera kupanga mafunso a SQL, mwachitsanzo, muyenera kudziwa momwe kusankha ndikujowina kumapangidwira.

Ziwerengero zimatiuza zinthu zingapo. Akhoza kugawidwa m'magulu.

Gulu loyamba ndi zochitika zomwe zikuchitika mu database. Apa ndipamene chochitika china chimachitika mu nkhokwe: pempho, kupeza tebulo, autovacuum, kuchita, ndiye zonsezi ndizochitika. Zowerengera zogwirizana ndi zochitika izi zikuwonjezeka. Ndipo tikhoza kutsatira zochitika izi.
Gulu lachiwiri ndi katundu wa zinthu monga matebulo ndi nkhokwe. Iwo ali ndi katundu. Uku ndi kukula kwa matebulo. Titha kuyang'anira kukula kwa matebulo ndi kukula kwa ma index. Titha kuwona kusintha kwamphamvu.
Ndipo gulu lachitatu ndi nthawi yogwiritsidwa ntchito pazochitikazo. Pempho ndi chochitika. Lili ndi muyeso wakewake wa nthawi. Zinayambira apa, zinathera apa. Tikhoza kuzilondolera. Zimatenga nthawi kuti muwerenge chipika kuchokera pa disk kapena kulemba. Zinthu zoterezi zimatsatiridwanso.

Magwero a ziwerengero amaperekedwa motere:

M'makumbukidwe omwe amagawana nawo (ma buffers omwe amagawana nawo) pali gawo losungirako deta yosasunthika, palinso zowerengera zomwe zimachulukitsidwa nthawi zonse pamene zochitika zina zimachitika, kapena nthawi zina zimachitika pogwiritsira ntchito deta.
Zowerengera zonsezi sizipezeka kwa wogwiritsa ntchito komanso sizipezeka kwa woyang'anira. Izi ndi zinthu zotsika. Kuti muwapeze, PostgreSQL imapereka mawonekedwe amtundu wa SQL ntchito. Titha kupanga zosankha zoponya pogwiritsa ntchito izi ndikupeza mtundu wina wa ma metric (kapena seti ya ma metrics).
Komabe, kugwiritsa ntchito izi sikophweka nthawi zonse, kotero ntchito ndizo maziko a malingaliro (mawonedwe). Awa ndi matebulo enieni omwe amapereka ziwerengero pa kachitidwe kakang'ono, kapena pagulu lina la zochitika munkhokwe.
Mawonedwe ophatikizidwawa (mawonedwe) ndiye mawonekedwe oyambira ogwiritsa ntchito ndi ziwerengero. Amapezeka mwachisawawa popanda zoikamo zina zowonjezera, mutha kuzigwiritsa ntchito nthawi yomweyo, kuziyang'ana, ndikutenga zambiri kuchokera kwa iwo. Ndiyeno pali contribs. Contribs ndi zovomerezeka. Mutha kukhazikitsa phukusi la postgresql-contrib (mwachitsanzo, postgresql94-contrib), tsitsani gawo lofunikira pakukonza, tchulani magawo ake, yambitsaninso PostgreSQL ndipo mutha kuyigwiritsa ntchito. (Zindikirani. Kutengera kugawa, m'mitundu yaposachedwa phukusi la contrib ndi gawo la phukusi lalikulu).
Ndipo pali zosavomerezeka. Sanaphatikizidwe pakugawa kwaposachedwa kwa PostgreSQL. Ayenera kupangidwa kapena kuikidwa ngati laibulale. Zosankha zitha kukhala zosiyana kwambiri, kutengera zomwe woyambitsa wa contrib yosavomerezekayi adabwera nazo.

Tsambali likuwonetsa ma VIEW onsewa ndi zina mwazinthu zomwe zikupezeka mu PostgreSQL 9.4. Monga tikuonera, alipo ambiri. Ndipo ndizosavuta kusokonezeka mukakumana nazo koyamba.

Komabe, ngati titenga chithunzi chapitacho Как тратится время на PostgreSQL ndipo n'zogwirizana ndi mndandanda, ife tikupeza chithunzi ichi. Titha kugwiritsa ntchito malingaliro aliwonse (mawonedwe) kapena ntchito iliyonse pazifukwa zina kuti tipeze ziwerengero zofananira PostgreSQL ikugwira ntchito. Ndipo titha kudziwa kale za kagwiritsidwe ntchito ka subsystem.

Chinthu choyamba chomwe tiwona ndi pg_stat_database. Monga tikuonera, ichi ndi ntchito. Muli zambiri zambiri mmenemo. Zosiyanasiyana zambiri. Ndipo imapereka chidziwitso chothandiza kwambiri pazomwe zikuchitika mu database yathu.

Ndi zinthu ziti zothandiza zomwe tingatenge kuchokera pamenepo? Tiyeni tiyambe ndi zinthu zosavuta.

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

Chinthu choyamba chomwe tingayang'ane ndi cache hit percentage. Cache hit rate ndi metric yothandiza. Zimakuthandizani kuti muyerekeze kuchuluka kwa deta yomwe imatengedwa kuchokera ku cache yogawana nawo komanso kuchuluka kwa zomwe zimawerengedwa kuchokera ku disk.

Zikuwonekeratu kuti kugunda kochulukira komwe tili nako, kumakhala bwinoko. Timayezera metric iyi ngati peresenti. Ndipo, mwachitsanzo, ngati kuchuluka kwathu kwa kugunda kwa cache uku kupitilira 90%, ndiye kuti izi ndizabwino. Ngati itsika pansi pa 90%, zikutanthauza kuti tilibe kukumbukira kokwanira kuti tikumbukire mutu wotentha wa data. Ndipo kuti agwiritse ntchito deta iyi, PostgreSQL imakakamizika kupeza diski ndipo izi ndizochepa kusiyana ndi ngati deta inawerengedwa kuchokera pamtima. Ndipo muyenera kuganizira zokulitsa kukumbukira: mwina kuwonjezera zosungira zogawana, kapena kuwonjezera kukumbukira kwa hardware (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;

Ndi chiyani chinanso chomwe mungatenge pakuchita izi? Mutha kuwona zolakwika zomwe zikuchitika mu database. Kodi chikuwonetsedwa apa ndi chiyani? Pali mabizinesi, ma rollbacks, kupanga mafayilo osakhalitsa, kukula kwawo, zolephera komanso mikangano.

Titha kugwiritsa ntchito pempholi. SQL iyi ndiyosavuta. Ndipo tikhoza kuyang'ana deta iyi apa.

Ndipo apa pali zoyambira. Timayang'ana chiŵerengero cha ma commits ndi ma rollbacks. Kudzipereka ndi chitsimikizo chopambana cha malonda. Rollbacks ndi kubwezeretsanso, mwachitsanzo, kugulitsako kunagwira ntchito, kusokoneza database, kuwerengera chinachake, ndiyeno kulephera kunachitika ndipo zotsatira za ntchitoyo zimatayidwa. Ndiko kuti chiwerengero cha rollbacks kuchuluka mosalekeza ndi zoipa. Ndipo muyenera kuwapewa mwanjira ina, ndikusintha ma code kuti izi zisachitike.

Mikangano ikugwirizana ndi kubwerezabwereza. Ndipo ziyenera kupeŵedwanso. Ngati muli ndi mafunso omwe amachitidwa pachifaniziro ndipo mikangano ikabuka, muyenera kuthetsa mikanganoyi ndikuwona zomwe zikuchitika. Tsatanetsatane angapezeke mu zipika. Ndipo chotsani mikangano kuti zopempha zigwire ntchito popanda zolakwika.

Ma Deadlocks nawonso ndizovuta. Pamene zopempha zikumenyera chuma, pempho limodzi lidapeza gwero limodzi ndikutenga loko, pempho lachiwiri lidapeza gwero lachiwiri ndipo lidatenganso loko, kenako zopempha zonse zidapeza zida za wina ndi mnzake ndikutsekereza podikirira mnansi kuti amasule loko. Izinso ndizovuta. Ayenera kuyankhidwa pamlingo wolemberanso mapulogalamu ndikusinthanso mwayi wopeza zothandizira. Ndipo ngati muwona kuti ma deadlocks anu akuchulukirachulukira, muyenera kuyang'ana tsatanetsatane wa zipika, pendani zochitika zomwe zimabwera ndikuwona vuto.

Mafayilo osakhalitsa (temp_files) nawonso ndi oyipa. Pamene pempho la wosuta alibe kukumbukira kokwanira kuti agwirizane ndi ntchito, deta yosakhalitsa, imapanga fayilo pa disk. Ndipo ntchito zonse zomwe zingagwire mu buffer kwakanthawi kukumbukira zimayamba kuchitidwa pa disk. Ndi pang'onopang'ono. Izi zimawonjezera nthawi yofunsa mafunso. Ndipo kasitomala yemwe adatumiza pempho ku PostgreSQL alandila yankho mtsogolo. Ngati zonsezi zikuchitidwa pokumbukira, Postgres iyankha mwachangu kwambiri ndipo kasitomala amadikirira pang'ono.

Pg_stat_bgwriter - Maonedwe awa akufotokoza momwe machitidwe awiri akumbuyo a PostgreSQL amagwirira ntchito: izi checkpointer и background writer.

Choyamba, tiyeni tiwone malo olamulira, otchedwa. checkpoints. Kodi ma control points ndi chiyani? Choyang'ana ndi malo omwe ali mu chipika chamalonda chosonyeza kuti zosintha zonse zomwe zalembedwa mu chipika zalumikizidwa bwino ndi zomwe zili pa disk. Njirayi, kutengera kuchuluka kwa ntchito ndi makonda, imatha kukhala yayitali ndipo makamaka imakhala ndi kulunzanitsa masamba odetsedwa m'ma buffer omwe amagawana ndi mafayilo a data pa disk. Ndi cha chiyani? Ngati PostgreSQL imangofikira pa diski ndikutenga deta kuchokera pamenepo, ndikulemba zambiri panjira iliyonse, zikadachedwa. Chifukwa chake, PostgreSQL ili ndi gawo la kukumbukira lomwe kukula kwake kumadalira zosintha zomwe zasinthidwa. Postgres imasunga zomwe zili muchikumbutsochi kuti zisinthidwe pambuyo pake kapena kufunsa mafunso. Pankhani yopempha kusintha deta, imasinthidwa. Ndipo timapeza mitundu iwiri ya data. Imodzi ili m'chikumbukiro chathu, ina ili pa disk. Ndipo nthawi ndi nthawi muyenera kulunzanitsa izi. Tiyenera kulunzanitsa zomwe zasinthidwa kukumbukira kukhala disk. Pachifukwa ichi, mukufunikira ma checkpoints.

Checkpoint amadutsa m'mabafa omwe amagawana nawo, amalemba masamba akuda omwe amafunikira pofufuza. Kenako imatsegula chiphaso chachiwiri kudzera pamabafa omwe adagawana nawo. Ndipo masamba omwe alembedwa kuti afufuzidwe, amawagwirizanitsa kale. Mwanjira iyi deta imalumikizidwa ndi diski.

Pali mitundu iwiri ya cheke. Choyang'ana chimodzi chimachitidwa ndi nthawi yomaliza. Chowunikira ichi ndi chothandiza komanso chabwino - checkpoint_timed. Ndipo pali cheke pakufunika - checkpoint required. Kufufuza uku kumachitika tikakhala ndi deta yayikulu kwambiri. Tinalemba zolemba zambiri zamalonda. Ndipo PostgreSQL ikukhulupirira kuti ikuyenera kulumikiza zonsezi mwachangu momwe mungathere, fufuzani ndikupitilira.

Ndipo ngati inu munayang'ana pa ziwerengero pg_stat_bgwriter ndikuwona zomwe muli nazo checkpoint_req ndi yayikulu kwambiri kuposa checkpoint_timed, ndiye izi ndizoyipa. Chifukwa chiyani? Izi zikutanthauza kuti PostgreSQL imakhala ndi nkhawa nthawi zonse ikafunika kulemba deta ku diski. Kuwunika kwanthawi yayitali sikumakhala kovutitsa ndipo kumachitika molingana ndi dongosolo lamkati ndipo kumakhala ngati kufalikira pakapita nthawi. PostgreSQL imatha kuyimitsa kaye ntchito osati kusokoneza ma disk subsystem. Izi ndizothandiza kwa PostgreSQL. Ndipo mafunso omwe amachitidwa panthawi yoyang'anira sadzakhala ndi nkhawa chifukwa disk subsystem ili yotanganidwa.

Ndipo kusintha cheke pali magawo atatu:

сheckpoint_segments.
сheckpoint_timeout.
сheckpoint_competion_target.

Iwo amakulolani kuti muyang'anire ntchito ya malo olamulira. Koma sindidzalingalira za iwo. Chikoka chawo ndi mutu wosiyana.

Chenjezo: Mtundu wa 9.4 womwe wafotokozedwa mu lipotilo sulinso wofunikira. M'mitundu yamakono ya PostgreSQL parameter checkpoint_segments m'malo ndi magawo min_wal_size и max_wal_size.

Dongosolo lotsatira ndi wolemba wakumbuyo - background writer. Kodi iye akuchita chiyani? Imayenda mosalekeza mu lupu losatha. Imasanthula masamba omwe amagawana nawo ndikutaya masamba akuda omwe amapeza kuti ali pa disk. Chifukwa chake, zimathandizira cholozera kuti chigwire ntchito yocheperako pakuwunika.

Chikufunikanso chiyani? Zimapereka kufunikira kwa masamba opanda kanthu m'mabafa omwe amagawidwa ngati akufunika mwadzidzidzi (zambiri komanso nthawi yomweyo) kuti apeze deta. Tiyerekeze kuti zinthu zidachitika pomwe masamba opanda kanthu amafunikira kuti amalize pempho ndipo anali kale m'mabafa omwe adagawana nawo. Postgresive backend amangowanyamula ndikuzigwiritsa ntchito, sakuyenera kuyeretsa yekha chilichonse. Koma ngati mwadzidzidzi palibe masamba oterowo, backend imayimitsa ntchito ndikuyamba kusaka masamba kuti awatayire pa disk ndikuwatengera pazosowa zake - zomwe zimakhudza nthawi ya pempho lomwe likufunsidwa. Ngati muwona kuti muli ndi parameter maxwritten_clean chachikulu, izi zikutanthauza kuti wolemba maziko sakuchita ntchito yake ndipo muyenera kuwonjezera magawo bgwriter_lru_maxpages, kuti athe kugwira ntchito zambiri m'njira imodzi, tsegulani masamba ambiri.

Ndipo chizindikiro china chothandiza kwambiri ndi buffers_backend_fsync. Backends sakhala fsync chifukwa imachedwa. Amadutsa fsync pamwamba pa IO stack checkpointer. Checkpointer ili ndi mzere wake, imagwiritsa ntchito fsync nthawi ndi nthawi ndikugwirizanitsa masamba kukumbukira ndi mafayilo pa disk. Ngati mzere pa checkpointer ndi waukulu komanso wodzaza, ndiye kuti backend imakakamizika kuchita fsync yokha ndipo izi zimachepetsa ntchito ya backend., i.e. kasitomala alandila yankho mochedwa kuposa momwe akanatha. Ngati muwona kuti mtengo wanu ndi waukulu kuposa zero, ndiye kuti izi ndizovuta komanso muyenera kulabadira zosintha za wolemba zakumbuyo ndikuwunikanso magwiridwe antchito a disk subsystem.

Chenjezo: _Malemba otsatirawa akufotokoza za ziwerengero zomwe zimagwirizanitsidwa ndi kubwerezabwereza. Maina ambiri owonera ndi ntchito adasinthidwanso mu Postgres 10. Chofunikira pakusinthidwanso chinali kusintha xlog pa wal и location pa lsn m'mayina a ntchito/mawonedwe, ndi zina. Mwachitsanzo, ntchito pg_xlog_location_diff() adasinthidwa kukhala pg_wal_lsn_diff()._

Tilinso ndi zinthu zambiri pano. Koma timangofunika zinthu zokhudzana ndi malo.

Ngati tiwona kuti zikhalidwe zonse ndizofanana, ndiye kuti iyi ndi njira yabwino ndipo chofanizira sichitsalira kumbuyo kwa mbuye.

Malo a hexadecimal apa ndi omwe ali mu chipika chamalonda. Imachulukirachulukira ngati pali ntchito iliyonse mu database: zoyika, zochotsa, ndi zina.

сколько записано 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());

Ngati zinthu izi ndi zosiyana, ndiye kuti pali mtundu wina wa kuchedwa. Lag ndiye kutsalira pakati pa chofanizira ndi master, i.e. data imasiyana pakati pa ma seva.

Pali zifukwa zitatu za kuchedwa:

Dongosolo la disk iyi silingathe kuthana ndi kulumikiza mafayilo.
Izi ndi zolakwika zotheka pa netiweki, kapena kuchuluka kwa maukonde, pomwe deta ilibe nthawi yofikira pachifanizirocho ndipo sichingabwerezenso.
Ndipo purosesa. Purosesa ndi vuto losowa kwambiri. Ndipo ine ndinaziwona izi kawiri kapena katatu, koma izi zikhoza kuchitikanso.

Ndipo apa pali mafunso atatu omwe amatilola kugwiritsa ntchito ziwerengero. Tikhoza kulingalira kuchuluka kwa zomwe talemba mu chipika cha malonda. Pali ntchito yotere pg_xlog_location_diff ndipo titha kuyerekeza kubwerezabwereza kwa ma byte ndi masekondi. Timagwiritsanso ntchito mtengo wamalingaliro awa (mawonedwe) pa izi.

Taonani: _M'malo mwa pg_xlog_locationDiff () ntchito imatha kugwiritsa ntchito wochotsa ndikuchotsa malo amodzi kuchokera kwina. Omasuka.

Pali mfundo imodzi yokhala ndi lag, yomwe ili mumasekondi. Ngati palibe ntchito pa mbuyeyo, kugulitsako kunalipo mphindi 15 zapitazo ndipo palibe ntchito, ndipo ngati tiyang'ana pa izi pa chithunzichi, tiwona kutsalira kwa mphindi 15. Izi ndi zofunika kuzikumbukira. Ndipo izi zitha kukhala zosokoneza mukawona kuchedwa uku.

Pg_stat_all_tables ndikuwonanso kothandiza. Imawonetsa ziwerengero pamatebulo. Tikakhala ndi matebulo mu nkhokwe, pali zochitika nazo, zochita zina, titha kupeza izi kuchokera pamalingaliro awa.

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;

Chinthu choyamba chomwe tingayang'ane ndikuwunika motsatizana patebulo. Nambala yokhayo ikadutsa izi sizoyipa kwenikweni ndipo si chizindikiro choti tifunika kuchitapo kanthu.

Komabe, pali metric yachiwiri - seq_tup_read. Ichi ndi chiwerengero cha mizere yomwe yabwezedwa kuchokera ku sikani yotsatizana. Ngati chiwerengero chikuposa 1, 000, 10, 000, ndiye kuti ichi ndi chizindikiro kale kuti muyenera kupanga index kwinakwake kuti mafunso akhazikike pa index, kapena ndizotheka kukhathamiritsa mafunso omwe amagwiritsa ntchito masikanidwe otsatizana. kuti izi sizichitika.

Chitsanzo chosavuta - tinene pempho lokhala ndi ndalama zambiri za OFFSET ndi LIMIT. Mwachitsanzo, mizere 100 patebulo imafufuzidwa ndipo pambuyo pake mizere yofunikira 000 imatengedwa, ndipo mizere yojambulidwa kale imatayidwa. Ilinso ndi mlandu woyipa. Ndipo mafunso oterowo ayenera kukonzedwa. Ndipo nali funso losavuta la SQL pomwe mutha kuyang'ana izi ndikuwunika manambala omwe akubwera.

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;

Makulidwe atebulo amathanso kupezeka pogwiritsa ntchito tebulo ili ndikugwiritsa ntchito zina pg_total_relation_size(), pg_relation_size().

Nthawi zambiri, pali ma metacommands dt и di, yomwe ingagwiritsidwe ntchito mu PSQL ndikuwonanso kukula kwa matebulo ndi ma index.

Komabe, kugwiritsa ntchito ntchito kumatithandiza kuyang'ana kukula kwa matebulo, ngakhale kutengera ma index, kapena osaganizira ma index, ndikupanga kale kuyerekezera kutengera kukula kwa nkhokwe, i.e. momwe ikukula, ndi mphamvu yanji, ndi perekani malingaliro ena okhudza kukhathamiritsa kwa sizing.

Kujambula. Kodi kujambula ndi chiyani? Tiyeni tiwone opareshoni UPDATE - kagwiritsidwe ntchito ka mizere yosinthira patebulo. M'malo mwake, zosintha ndizochita ziwiri (kapena kupitilira apo). Uku ndikuyika mtundu watsopano wa mzere ndikuyika mtundu wakale wa mzerewu ngati wachikale. Pambuyo pake, autovacuum ibwera ndikuyeretsa mizere yakaleyi, ndikuyika malowa ngati alipo kuti agwiritsidwenso ntchito.

Kuphatikiza apo, kusintha sikungokhudza kukonzanso tebulo. Ichinso ndikusintha kwa index. Ngati muli ndi ma index ambiri patebulo, ndiye kuti pakusintha ma index onse omwe akuphatikiza magawo omwe asinthidwa pafunso adzafunikanso kusinthidwa. Ma index awa adzakhalanso ndi mizere yakale yomwe ikufunika kukonzedwa.

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;

Ndipo chifukwa cha mapangidwe ake atsopano, UPDATE ndi ntchito yolemetsa. Koma zikhoza kukhala zosavuta. Idyani hot updates. Adawonekera mu mtundu wa PostgreSQL 8.3. Ndipo ichi ndi chiyani? Uku ndikusintha kopepuka komwe sikumapangitsa kuti ma index amangidwenso. Ndiko kuti, tinasintha zolembazo, koma zolemba zokha zomwe zili patsamba (zomwe zili patebulo) zidasinthidwa, ndipo ma index amalozabe zomwe zili patsambalo. Pali malingaliro osangalatsa ogwiritsira ntchito: pamene vacuum ibwera, imapanga maunyolo awa hot imamanganso ndipo chilichonse chikupitilizabe kugwira ntchito popanda kukonzanso ma index, ndipo chilichonse chimachitika ndikuwononga zinthu zochepa.

Ndipo inu mutero liti n_tup_hot_upd chachikulu, ndiye chabwino kwambiri. Izi zikutanthauza kuti zosintha zopepuka zimachulukirachulukira ndipo izi ndizotsika mtengo kwa ife pankhani yazachuma ndipo zonse zili bwino.

ALTER TABLE table_name SET (fillfactor = 70);

Momwe mungawonjezere mawu hot updateov? Titha kugwiritsa ntchito fillfactor. Imazindikira kukula kwa malo osungidwa aulere podzaza tsamba patebulo pogwiritsa ntchito INSERTs. Zoyikazo zikawonjezeredwa patebulo, zimadzaza tsambalo ndikusiya malo opanda kanthu. Kenako tsamba latsopano liwunikiridwa. Deta imadzazidwanso. Ndipo ichi ndi chikhalidwe chosasinthika, fillfactor = 100%.

Titha kupanga fillfactor 70%. Ndiye kuti, pakuyika, tsamba latsopano lidawonetsedwa, koma 70% yokha ya tsambalo idadzazidwa. Ndipo tatsala ndi 30% ngati nkhokwe. Mukafuna kupanga zosintha, zitha kuchitika patsamba lomwelo, ndipo mtundu watsopano wa mzerewu ukwanira patsamba lomwelo. Ndipo hot_update ichitika. Izi zimapangitsa kuti zikhale zosavuta kulemba pamatebulo.

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));

Mzere wa Autovacuum. Autovacuum ndi kachitidwe kakang'ono komwe kuli ziwerengero zochepa mu PostgreSQL. Titha kuwona m'magome mu pg_stat_activity kuchuluka kwa vacuum komwe tili nako pakadali pano. Komabe, ndizovuta kwambiri kumvetsetsa kuti ndi matebulo angati omwe ali pamzere nthawi yomweyo.

Taonani: _Kuyambira ndi Postgres 10, momwe zinthu zilili ndi Vatovac kutsatira zasintha kwambiri - pg_stat_progress view yawonekera.vacuum, zomwe zimachepetsa kwambiri nkhani yowunika momwe magalimoto amayendera.

Titha kugwiritsa ntchito funso losavutali. Ndipo titha kuwona nthawi yomwe vacuum iyenera kupangidwa. Koma kodi vacuum iyenera kuyamba bwanji ndipo liti? Awa ndi matembenuzidwe a cholowa cha mizere yomwe ndimanena kale. Kusintha kwachitika, mtundu watsopano wa mzerewu udayikidwa. Chingwe chachikale chawonekera. Mu tebulo pg_stat_user_tables pali parameter yotere n_dead_tup. Ikuwonetsa kuchuluka kwa mizere "yakufa". Ndipo chiwerengero cha mizere yakufa chikakhala chachikulu kuposa malo enaake, autovacuum idzabwera patebulo.

Ndipo kodi malire awa amawerengedwa bwanji? Ichi ndi chiwerengero chenicheni cha mizere yonse patebulo. Pali parameter autovacuum_vacuum_scale_factor. Zimatsimikizira kuchuluka kwake. Tinene kuti 10% + pali gawo lowonjezera la mizere 50. Ndipo chimachitika ndi chiyani? Tikakhala ndi mizere yakufa kuposa "10% + 50" ya mizere yonse patebulo, ndiye timayika tebulo pa 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));

Komabe, pali mfundo imodzi. Magawo oyambira a parameters av_base_thresh и av_scale_factor akhoza kuperekedwa payekha. Ndipo, motere, malirewo sadzakhala padziko lonse lapansi, koma payekha patebulo. Chifukwa chake, kuti muwerenge, muyenera kugwiritsa ntchito zidule ndi zidule. Ndipo ngati muli ndi chidwi, ndiye kuti mutha kuyang'ana zomwe zachitika kwa anzathu ochokera ku Avito (ulalo wa slide ndi wolakwika ndipo wasinthidwa m'mawuwo).

Iwo analembera pulogalamu yowonjezera, amene amaganizira zinthu zimenezi. Pali nsalu yamapepala awiri pamenepo. Koma kuwerengera molondola komanso mogwira mtima kumatilola kuwunika komwe timafunikira vacuum yambiri pamagome pomwe pali zochepa.

Nanga tingatani? Ngati tili ndi mzere waukulu ndipo autovacuum siyingathe kupirira, ndiye kuti titha kuwonjezera kuchuluka kwa ogwira ntchito zochotsa, kapena kungopangitsa kuti vacuum ikhale yankhanza., kotero kuti imayamba kale, imakonza tebulo mu tiziduswa tating'ono. Ndipo motero mzerewo udzachepa. - Chinthu chachikulu apa ndikuwunika katundu pa disks, chifukwa ... vacuum si chinthu chaulere, ngakhale kubwera kwa zida za SSD/NVMe vuto silinawonekere.

Pg_stat_all_indexes ndi ziwerengero pama index. Iye si wamkulu. Ndipo titha kuzigwiritsa ntchito kuti tipeze chidziwitso chakugwiritsa ntchito ma index. Ndipo mwachitsanzo, titha kudziwa ma index omwe tili nawo owonjezera.

Monga ndanenera kale, update sikusintha kwamatebulo, komanso ndikusintha kwa indexes. Chifukwa chake, ngati tili ndi ma index ambiri patebulo, ndiye posintha mizere patebulo, ma index a magawo omwe ali ndi indexed amafunikanso kusinthidwa, ndipo ngati tili ndi ma index osagwiritsidwa ntchito omwe palibe ma index a index, ndiye kuti amakhala ngati ballast. Ndipo tiyenera kuwachotsa. Kwa ichi tikufuna munda idx_scan. Timangoyang'ana chiwerengero cha ma index. Ngati zisonyezo zili ndi zero kwa nthawi yayitali yosungira ziwerengero (osachepera masabata 2-3), ndiye kuti izi ndizoyipa zoyipa, tiyenera kuzichotsa.

Taonani: Mukasaka ma index omwe sanagwiritsidwe ntchito ngati akukhamukira magulu obwerezabwereza, muyenera kuyang'ana ma cluster node, chifukwa ziwerengero siziri zapadziko lonse lapansi, ndipo ngati indexyo siigwiritsidwa ntchito pa mbuye, ndiye kuti ingagwiritsidwe ntchito pazofananira (ngati pali katundu pamenepo).

Maulalo awiri:

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

Izi ndi zitsanzo zamafunso zapamwamba za momwe mungayang'anire ma index omwe sanagwiritsidwe ntchito.

Ulalo wachiwiri ndi pempho losangalatsa. Pali malingaliro osakhala ang'onoang'ono pamenepo. Ndikupangira kuti mufotokozere.

Ndi chiyani chinanso chomwe muyenera kufotokozera mwachidule pogwiritsa ntchito ma indices?

Zolozera zosagwiritsidwa ntchito ndizoyipa.
Iwo amatenga malo.
Chepetsani zosintha.
Ntchito yowonjezera ya vacuum.

Ngati tichotsa ma index omwe sanagwiritsidwe ntchito, tidzangopanga database kukhala yabwino.

Ulaliki wotsatira ndi pg_stat_activity. Ichi ndi analogue ya zothandiza ps, mu PostgreSQL yokha. Ngati ps'om mumayang'ana njira zomwe zili mumayendedwe opangira, ndiye pg_stat_activity Ikuwonetsani zomwe zikuchitika mkati mwa PostgreSQL.

Ndi zinthu ziti zothandiza zomwe tingatenge kuchokera pamenepo?

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

Titha kuwona zochitika zonse, zomwe zikuchitika mu database. Titha kupanga ntchito yatsopano. Chilichonse pano chaphulika, maulumikizidwe atsopano sakuvomerezedwa, zolakwika zikutsanulidwa muzogwiritsira ntchito.

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

Titha kuyendetsa funso ngati ili ndikuwona kuchuluka kwa maulumikizidwe okhudzana ndi malire olumikizirana ndikuwona yemwe ali ndi maulumikizidwe ambiri. Ndipo mu nkhani iyi tikuwona wosuta cron_role anatsegula 508 malumikizidwe. Ndipo chinachake chinachitika kwa iye kumeneko. Tiyenera kuthana nazo ndikuziyang'ana. Ndipo ndizotheka kuti uwu ndi mtundu wina wa maulumikizidwe osiyanasiyana.

Ngati tili ndi ntchito ya OLTP, mafunso akuyenera kukhala achangu, mwachangu kwambiri ndipo pasakhale mafunso ataliatali. Komabe, ngati mafunso aatali abuka, ndiye kuti pakanthawi kochepa palibe chodetsa nkhawa, koma M'kupita kwa nthawi, mafunso aatali amawononga nkhokwe; amawonjezera kuphulika kwa matebulo pamene kugawanika kwa tebulo kumachitika. Muyenera kuchotsa mafunso onse otupa komanso aatali.

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;

Chonde dziwani: ndi pempholi titha kuzindikira mafunso ndi zochitika zazitali. Timagwiritsa ntchito clock_timestamp() kudziwa nthawi yogwira ntchito. Mafunso ataliatali omwe tawapeza, titha kuwakumbukira, kuwakwaniritsa explain, yang'anani mapulaniwo ndikuwongolera mwanjira ina. Timawombera pansi zopempha zomwe zilipo kale ndikupitirizabe ndi moyo wathu.

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

Zochita zoyipa ndizochita zomwe zili mubizinesi komanso osachitapo kanthu m'maboma (othetsedwa).

Zikutanthauza chiyani? Zogulitsa zili ndi zigawo zingapo. Ndipo imodzi mwa mayikowa ikhoza kuganiziridwa nthawi iliyonse. Pali gawo lofotokozera mayiko state mu chiwonetsero ichi. Ndipo ife timagwiritsa ntchito kudziwa dziko.

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

Ndipo, monga ndanenera pamwambapa, zigawo ziwirizi osagwira ntchito komanso osagwira ntchito (ochotsedwa) ndizoyipa. Ndi chiyani? Apa ndi pomwe pulogalamuyo idatsegula bizinesi, idachitapo kanthu ndikuchita bizinesi yake. Ntchitoyi ikadali yotseguka. Imapachikidwa, palibe chomwe chimachitika mmenemo, imatenga kugwirizana, kutseka mizere yosinthidwa ndikuwonjezera kuphulika kwa matebulo ena, chifukwa cha zomangamanga za injini ya Postrges. Ndipo zochitika zotere ziyenera kuponyedwanso pansi, chifukwa nthawi zambiri zimakhala zovulaza, mulimonse.

Ngati muwona kuti muli ndi opitilira 5-10-20 m'dawunilodi yanu, muyenera kuda nkhawa ndikuyamba kuchita nawo kanthu.

Apa timagwiritsanso ntchito nthawi yowerengera clock_timestamp(). Timawombera zochitika ndikukulitsa pulogalamuyo.

Monga ndanenera pamwambapa, kutsekereza ndi pamene zochitika ziwiri kapena zingapo zimamenyera chimodzi kapena gulu lazinthu. Kwa ichi tili ndi munda waiting ndi mtengo wa boolean true kapena false.

Zoona - izi zikutanthauza kuti ndondomekoyi ikudikira, chinachake chiyenera kuchitika. Pamene ndondomeko ikudikira, zikutanthauza kuti kasitomala amene anayambitsa ndondomekoyi akudikiriranso. Makasitomala amakhala mu msakatuli komanso amadikirira.

Chenjezo: _Kuyambira patsamba la Postgres 9.6 waiting kuchotsedwa ndipo magawo awiri odziwitsa anawonjezedwa m'malo mwake wait_event_type и wait_event._

Chochita? Ngati muwona zowona kwa nthawi yayitali, zikutanthauza kuti muyenera kuchotsa zopempha zotere. Timangowombera pansi zochitika zoterezi. Timalembera omanga kuti akuyenera kuwongolera mwanjira ina kuti pasakhale mpikisano wazinthu. Ndiyeno Madivelopa amakonza ntchito kuti izi zisachitike.

Ndipo vuto lalikulu, koma lomwe lingakhale losakhala lakupha ndi kuchitika kwa matenda. Zochita ziwiri zidasintha zida ziwiri, kenako zidafikiranso, nthawi ino kuzinthu zina. Pankhaniyi, PostgreSQL imapha malondawo kuti wina apitirize kugwira ntchito. Izi ndizovuta ndipo sangathe kuzizindikira yekha. Chifukwa chake, PostgreSQL ikukakamizika kuchitapo kanthu monyanyira.

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/

Ndipo apa pali mafunso awiri omwe amakupatsani mwayi wotsata kutsekereza. Timagwiritsa ntchito mawonekedwe pg_locks, zomwe zimakulolani kuti muzitsatira maloko olemera.

Ndipo ulalo woyamba ndi pempho lemba lokha. Ndi yaitali ndithu.

Ndipo ulalo wachiwiri ndi nkhani yokhudza maloko. Ndi zothandiza kuwerenga, ndi chidwi kwambiri.

Ndiye tikuwona chiyani? Tikuwona zopempha ziwiri. Kugulitsa ndi ALTER TABLE ndi ntchito yotsekereza. Zinayamba, koma sizinathe, ndipo pulogalamu yomwe idalemba izi ikuchita zinthu zina kwinakwake. Ndipo pempho lachiwiri ndikusintha. Amadikirira kuti alter table ithe asanapitirize ntchito yake.

Umu ndi momwe tingadziwire yemwe watseka yemwe, wagwira ndani, ndipo titha kuthana nazo mopitilira.

Gawo lotsatira ndi pg_stat_statements. Monga ndanenera, iyi ndi module. Kuti mugwiritse ntchito, muyenera kuyika laibulale yake mu kasinthidwe, yambitsaninso PostgreSQL, yikani gawo (ndi lamulo limodzi) ndiyeno tidzakhala ndi malingaliro atsopano.

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;

Kodi tingatenge chiyani kuchokera pamenepo? Ngati tilankhula za zinthu zosavuta, titha kutenga nthawi yofunsa mafunso. Nthawi ikukula, zomwe zikutanthauza kuti PostgreSQL ikuyankha pang'onopang'ono ndipo tiyenera kuchita chinachake.

Titha kuyang'ana zolemba zomwe zimagwira ntchito kwambiri mu nkhokwe zomwe zimasintha deta muzosungira zogawana. Onani amene akusintha kapena kuchotsa deta pamenepo.

Ndipo tikhoza kungoyang'ana ziwerengero zosiyanasiyana za zopemphazi.

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

ife pg_stat_statements Timagwiritsa ntchito kupanga malipoti. Timayikanso ziwerengero kamodzi patsiku. Tiyeni tiwuunjike. Tisanakhazikitsenso ziwerengero nthawi ina, tiyeni tipange lipoti. Nawu ulalo wa lipotilo. Mutha kuziwonera.

Kodi tikuchita chiyani? Timawerengera ziwerengero zonse zopempha. Kenako, pa pempho lililonse, timawerengera zomwe zathandizira paziwerengero zonse.

Ndipo tingawone chiyani? Titha kuyang'ana nthawi yokwanira yoperekera zopempha zamtundu wina malinga ndi zopempha zina zonse. Titha kuyang'ana pakugwiritsa ntchito kwa CPU ndi I / O pokhudzana ndi chithunzi chonse. Ndipo konzani kale mafunso awa. Tikupanga mafunso apamwamba kutengera lipotili ndipo tikupeza kale mfundo zoti tikwaniritse.

Kodi tasiya zotani? Palinso zolemba zina zomwe zatsala zomwe sindinaziganizire chifukwa nthawi ndi yochepa.

pali pgstattuple ilinso gawo lowonjezera kuchokera pa phukusi lokhazikika la contribs. Zimakupatsani mwayi wowunika bloat matebulo, otchedwa kugawanika kwa tebulo. Ndipo ngati pali kugawanika kwakukulu, muyenera kuchotsa ndikugwiritsa ntchito zida zosiyanasiyana. Ndipo ntchito pgstattuple amagwira ntchito kwa nthawi yayitali. Ndipo matebulo ochulukira m'pamenenso adzagwira ntchito yayitali.

Chotsatira chotsatira ndi pg_buffercache. Zimakuthandizani kuti muyang'ane ma buffer omwe amagawidwa: momwe masamba a buffer amagwiritsidwira ntchito. Ndipo zimangokulolani kuti muyang'ane ma buffers omwe amagawidwa ndikuwunika zomwe zikuchitika kumeneko.

Gawo lotsatira ndi pgfincore. Imalola magwiridwe antchito a tebulo otsika kudzera pa foni yadongosolo mincore(), i.e. imakupatsani mwayi wotsitsa tebulo muzosungira zogawana, kapena kutsitsa. Ndipo imalola, pakati pa zinthu zina, kuyang'ana cache ya tsamba la opareshoni, mwachitsanzo, kuchuluka kwa malo omwe tebulo limakhala mu cache yamasamba, muzosungira zogawana, ndikungolola kuti tiyese ntchito ya tebulo.

Gawo lotsatira - pg_stat_kcache. Imagwiritsanso ntchito kuyimba foni getrusage(). Ndipo imakwaniritsa pempho lisanayambe komanso litatha. Ndipo mu ziwerengero zomwe zimatsatira, zimatithandiza kulingalira kuchuluka kwa pempho lathu lomwe tinagwiritsa ntchito pa disk I / O, mwachitsanzo, ntchito ndi fayilo ya fayilo ndikuyang'ana ntchito yogwiritsira ntchito purosesa. Komabe, gawoli ndi laling'ono (chifuwa cha chifuwa) ndipo kuti ligwire ntchito likufunika PostgreSQL 9.4 ndi pg_stat_statements, zomwe ndinanena kale.

Kudziwa kugwiritsa ntchito ziwerengero ndikothandiza. Simufunika mapulogalamu ena. Inu mukhoza kubwera mkati, mwawona, kuchita chinachake, kukwaniritsa chinachake.
Kugwiritsa ntchito ziwerengero sikovuta, ndi SQL yokhazikika. Munatolera zopemphazo, munazilemba, kuzitumiza, kuziyang'ana.
Ziwerengero zimathandiza kuyankha mafunso. Ngati muli ndi mafunso, mumatembenukira ku ziwerengero - yang'anani, ganizirani, fufuzani zotsatira.
Ndipo kuyesa. Pali zopempha zambiri, zambiri za data. Mutha kukhathamiritsa funso lomwe lilipo nthawi zonse. Mutha kupanga mtundu wanu wa pempho lomwe limakuyenererani kuposa choyambirira ndikuligwiritsa ntchito.