Njira yamakampani yosinthira PostgreSQL: kuyesa ndi nkhokwe. ” Nikolay Samokhvalov

Ndikupangira kuti muwerenge zolembedwa za lipoti la Nikolai Samokhvalov "Njira yamakampani yosinthira PostgreSQL: zoyeserera pama database"

Shared_buffers = 25% - ndi zambiri kapena pang'ono? Kapena basi? Mumadziwa bwanji ngati malingaliro awa - m'malo mwake - ali oyenera kwa inu?

Yakwana nthawi yoti muyandikire nkhani yosankha magawo a postgresql.conf "monga munthu wamkulu." Osati mothandizidwa ndi "ma auto tuners" akhungu kapena upangiri wachikale kuchokera pazolemba ndi mabulogu, koma kutengera:

1. zoyeserera zotsimikizika pazosungidwa, zomwe zimachitika zokha, mochulukira komanso pansi pamikhalidwe yoyandikira kwambiri "kulimbana" ndi iwo,
2. kumvetsetsa mozama za mawonekedwe a DBMS ndi OS.

Kugwiritsa ntchito Nancy CLI (https://gitlab.com/postgres.ai/nancy), tiwona chitsanzo china - odziwika bwino Share_buffers - m'malo osiyanasiyana, m'mapulojekiti osiyanasiyana ndikuyesera kudziwa momwe tingasankhire malo abwino kwambiri a zomangamanga, database ndi katundu.

Tidzakambirana zoyeserera ndi database. Iyi ndi nkhani yomwe imatenga miyezi isanu ndi umodzi.

Pang'ono za ine. Dziwani ndi Postgres kwa zaka zopitilira 14. Makampani angapo ochezera a pa Intaneti akhazikitsa. Postgres anali ndipo amagwiritsidwa ntchito kulikonse.

Komanso gulu la RuPostgres pa Meetup, malo a 2nd padziko lapansi. Pang'onopang'ono tikuyandikira anthu 2. RuPostgres.org.

Ndipo pama PC amisonkhano yosiyanasiyana, kuphatikiza Highload, ndimayang'anira nkhokwe, makamaka Postgres kuyambira pachiyambi.

Ndipo m'zaka zingapo zapitazi, ndayambiranso mayendedwe anga a Postgres 11 nthawi kuchokera pano.

Ndipo nditachita izi zaka zingapo zapitazo, ndidasiya kugwira ntchito ndi Postgres, mwina kuyambira 2010. Ndinadabwa kuti ndondomeko ya ntchito ya DBA yasintha pang'ono, komanso kuchuluka kwa ntchito zamanja zomwe zikufunikabe kugwiritsidwa ntchito. Ndipo nthawi yomweyo ndinaganiza kuti china chake chalakwika apa, ndiyenera kusinthiratu chilichonse.

Ndipo popeza zonse zinali zakutali, makasitomala ambiri anali m'mitambo. Ndipo zambiri zapangidwa kale, mwachiwonekere. Zambiri pa izi pambuyo pake. Ndiko kuti, zonsezi zinayambitsa lingaliro lakuti payenera kukhala zida zingapo, mwachitsanzo, mtundu wina wa nsanja yomwe idzapangitse pafupifupi zochita zonse za DBA kuti chiwerengero chachikulu cha database chikhoza kuyendetsedwa.

Lipotili siliphatikiza:

• "Silver bullets" ndi mawu ngati - ikani 8 GB kapena 25% Share_buffers ndipo mukhala bwino. Sipadzakhala zambiri za share_buffers.
• Hardcore "innards".

Kodi chidzachitike n'chiyani?

• Padzakhala mfundo zokometsera zomwe timagwiritsa ntchito ndikukulitsa. Padzakhala mitundu yonse ya malingaliro omwe amabwera panjira ndi zida zosiyanasiyana zomwe timapanga nthawi zambiri mu Open Source, i.e. timapanga maziko ku Open Source. Kuphatikiza apo, tili ndi matikiti, kulumikizana konse ndi Open Source. Mutha kuwona zomwe tikuchita tsopano, zomwe zidzakhale mu kutulutsidwa kotsatira, ndi zina.
• Padzakhalanso chidziwitso chogwiritsa ntchito mfundozi, zida izi m'makampani angapo: kuyambira oyambira ang'onoang'ono kupita kumakampani akulu.

Kodi zonsezi zikukula bwanji?

Choyamba, ntchito yayikulu ya DBA, kuphatikiza pakuwonetsetsa kupangidwa kwa zochitika, kutumizidwa kwa zosunga zobwezeretsera, ndi zina zambiri, ndikupeza zolepheretsa ndikuwongolera magwiridwe antchito.

Tsopano zakhazikitsidwa motere. Timayang'ana kuwunika, tikuwona zina, koma tikusowa zambiri. Timayamba kukumba mosamala kwambiri, nthawi zambiri ndi manja athu, ndikumvetsetsa zoyenera kuchita nawo mwanjira ina.

Ndipo pali njira ziwiri. Pg_stat_statements ndiye yankho lokhazikika pakuzindikiritsa mafunso ochedwa. Ndipo kusanthula kwa zipika za Postgres pogwiritsa ntchito pgBadger.

Njira iliyonse ili ndi zovuta zake. Mu njira yoyamba, tataya magawo onse. Ndipo ngati tiwona magulu sankhani *KUCHOKERA patebulo pomwe ndime ili yofanana ndi "?" kapena "$" kuyambira Postgres 10. Sitikudziwa ngati izi ndizojambula kapena seq scan. Zimatengera kwambiri parameter. Ngati mutasintha mtengo womwe simunapezekepo pamenepo, kudzakhala kuwonetsa index. Mukalowetsa mtengo womwe umakhala 90% patebulo pamenepo, seq scan idzakhala yodziwikiratu, chifukwa Postgres amadziwa ziwerengero. Ndipo ichi ndi vuto lalikulu la pg_stat_statements, ngakhale ntchito ina ikuchitika.

Choyipa chachikulu pakuwunika kwa chipika ndikuti simungakwanitse "log_min_duration_statement = 0" monga lamulo. Ndipo tikambirananso za izi. Chifukwa chake, simukuwona chithunzi chonse. Ndipo funso lina, lomwe ndi lachangu kwambiri, litha kuwononga zinthu zambiri, koma simudzaziwona chifukwa zili pansipa.

Kodi ma DBA amathetsa bwanji mavuto omwe amapeza?

Mwachitsanzo, tapeza vuto lina. Kodi nthawi zambiri amachita chiyani? Ngati ndinu wopanga mapulogalamu, ndiye kuti mukuchita zinazake zomwe sizili zofanana. Ngati ndinu DBA, ndiye kuti muli ndi masewera. Ndipo pakhoza kukhala mmodzi yekha. Ndipo anali atatsala miyezi isanu ndi umodzi. Ndipo mukuganiza kuti mupita kukupanga. Ndipo ngakhale ma DBA odziwa zambiri ndiye amayang'ana kupanga, pa chithunzi. Ndipo zimachitika kuti amapanga ndondomeko yanthawi yochepa, onetsetsani kuti imathandizira, igwetseni ndikuipereka kwa omanga kuti ayike m'mafayilo osamukira. Izi ndi zamkhutu zomwe zikuchitika tsopano. Ndipo ili ndi vuto.

• Sinthani zosintha.
• Konzani mndandanda wa ma index.
• Sinthani funso la SQL lokha (iyi ndiyo njira yovuta kwambiri).
• Onjezani mphamvu (njira yosavuta nthawi zambiri).

Pali zambiri zomwe zikuchitika ndi zinthu izi. Pali zogwirira ntchito zambiri ku Postgres. Pali zambiri zoti mudziwe. Pali ma index ambiri ku Postgres, zikomonso kwa okonza msonkhano uno. Ndipo zonsezi ziyenera kudziwika, ndipo izi ndizomwe zimapangitsa omwe si a DBA kumva ngati ma DBA akuchita zamatsenga. Ndiko kuti, muyenera kuphunzira kwa zaka 10 kuti muyambe kumvetsa zonsezi bwinobwino.

Ndipo ndine wolimbana ndi matsenga akudawa. Ndikufuna kuchita chirichonse kuti pakhale teknoloji, ndipo palibe chidziwitso mu zonsezi.

Zitsanzo zenizeni za moyo

Ndidawona izi m'mapulojekiti osachepera awiri, kuphatikiza yanga. Cholemba china chabulogu chimatiuza kuti mtengo wa 1 wa default_statistict_target ndi wabwino. Chabwino, tiyeni tiyese popanga.

Ndipo ife tiri pano, pogwiritsa ntchito chida chathu zaka ziwiri pambuyo pake, mothandizidwa ndi zoyesera pazithunzithunzi zomwe tikukamba lero, tikhoza kuyerekezera zomwe zinali ndi zomwe zakhala.

Ndipo chifukwa cha izi tiyenera kupanga kuyesera. Amakhala ndi magawo anayi.

• Choyamba ndi chilengedwe. Tikufuna chidutswa cha hardware. Ndipo ndikabwera kukampani ina ndikusayina kontrakitala, ndimawauza kuti andipatse zida zofananira ndi zomwe zikupanga. Pa Masters anu aliwonse, ndimafunikira chida chimodzi chotere. Mwina awa ndi makina enieni ku Amazon kapena Google, kapena ndikufunika chida chomwecho. Ndiye kuti, ndikufuna kupanganso chilengedwe. Ndipo mu lingaliro la chilengedwe timaphatikizapo mtundu waukulu wa Postgres.
• Gawo lachiwiri ndi cholinga cha kafukufuku wathu. Iyi ndi database. Ikhoza kupangidwa m'njira zingapo. Ndikuwonetsani momwe mungachitire.
• Gawo lachitatu ndi katundu. Iyi ndi nthawi yovuta kwambiri.
• Ndipo gawo lachinayi ndi lomwe timayang'ana, mwachitsanzo, zomwe tidzafanizire ndi chiyani. Tiyerekeze kuti titha kusintha magawo amodzi kapena angapo mu config, kapena titha kupanga index, ndi zina.

Tikuyambitsa kuyesera. Nayi pg_stat_statements. Kumanzere ndi zomwe zinachitika. Kumanja - zomwe zidachitika.

Kumanzere default_statistics_target = 100, kumanja = 1. Tikuwona kuti izi zidatithandiza. Ponseponse, zonse zidayenda bwino ndi 000%.

Koma ngati tipita pansi, padzakhala magulu a zopempha kuchokera ku pgBadger kapena pg_stat_statements. Pali njira ziwiri. Tiwona kuti pempho lina latsika ndi 88%. Ndipo apa pakubwera njira ya uinjiniya. Tikhoza kukumba mopitirira m’kati chifukwa timadabwa kuti n’chifukwa chiyani linamira. Muyenera kumvetsetsa zomwe zidachitika ndi ziwerengero. Chifukwa chiyani zidebe zambiri mu ziwerengero zimabweretsa zotsatira zoyipa.

Kapena sitingathe kukumba, koma chitani "ALTER TABLE ... ALTER COLUMN" ndikubweza zidebe 100 ku ziwerengero zagawoli. Ndiyeno ndi kuyesa kwina tikhoza kuonetsetsa kuti chigambachi chinathandiza. Zonse. Iyi ndi njira ya uinjiniya yomwe imatithandiza kuwona chithunzi chachikulu ndikupanga zisankho motengera deta osati mwachilengedwe.

Zitsanzo zingapo zochokera kumadera ena. Pakhala pali mayeso a CI poyesa kwa zaka zambiri. Ndipo palibe projekiti yomwe ili m'malingaliro ake abwino yomwe ingakhale popanda mayeso odzichitira.

M'mafakitale ena: mu ndege, m'makampani oyendetsa galimoto, tikamayesa aerodynamics, timakhalanso ndi mwayi woyesera. Sitidzayambitsa china chake kuchokera muzojambula molunjika mumlengalenga, kapena sitidzakwera galimoto nthawi yomweyo panjanji. Mwachitsanzo, pali ngalande yamphepo.

Titha kuganiza mozama kuchokera kuzinthu zina zamafakitale.

Choyamba, tili ndi malo apadera. Ili pafupi ndi kupanga, koma osati pafupi. Mbali yake yayikulu ndikuti iyenera kukhala yotsika mtengo, yobwerezabwereza komanso yodzipangira yokha. Ndipo payeneranso kukhala zida zapadera zowunikira mwatsatanetsatane.

Mosakayika, tikamayendetsa ndege ndi kuuluka, timakhala ndi mwayi wochepa wowerengera mamilimita aliwonse a mapiko kuposa momwe timachitira munjira yamphepo. Tili ndi zida zambiri zowunikira. Titha kunyamula zinthu zolemera kwambiri zomwe sitingakwanitse kuziyika mundege mumlengalenga. Zomwezo ndi Postgres. Tikhoza, nthawi zina, kulola kudula mitengo yonse panthawi yoyesera. Ndipo sitikufuna kuchita izi popanga. Tithanso kukonzekera kuti izi zitheke pogwiritsa ntchito auto_explain.

Ndipo monga ndidanenera, mulingo wapamwamba wa automation umatanthauza kuti timakanikiza batani ndikubwereza. Umu ndi momwe ziyenera kukhalira, kuti pakhale zoyesera zambiri, kuti zikhale pamtsinje.

Nancy CLI - maziko a "database laboratory"

Ndipo kotero ife tinachita chinthu ichi. Ndiko kuti, ndinalankhula za malingalirowa mu June, pafupifupi chaka chapitacho. Ndipo tili ndi omwe amatchedwa Nancy CLI mu Open Source. Awa ndiye maziko omanga labotale ya database.

Nancy - Ili mu Open Source, pa Gitlab. Inu mukhoza kunena izo, inu mukhoza kuyesa izo. Ndapereka ulalo pazithunzi. Mukhoza alemba pa izo ndipo adzakhala pamenepo Thandizeni m'njira zonse.

Inde, pali zambiri zomwe zidakalipo. Pali malingaliro ambiri pamenepo. Koma ichi ndi chinthu chomwe timagwiritsa ntchito pafupifupi tsiku lililonse. Ndipo tikakhala ndi lingaliro - chifukwa chiyani tikachotsa mizere 40, zonse zimatsikira ku IO, ndiye titha kuchita zoyeserera ndikuyang'ana mwatsatanetsatane kuti timvetsetse zomwe zikuchitika ndikuyesa kukonza pa ntchentche. Ndiko kuti, tikuchita kuyesa. Mwachitsanzo, timasintha china chake ndikuwona zomwe zimachitika kumapeto. Ndipo sitimachita izi popanga. Ichi ndi chiyambi cha lingaliro.

Kodi zimenezi zingagwire ntchito kuti? Izi zitha kugwira ntchito kwanuko, mwachitsanzo, mutha kuchita kulikonse, mutha kuyendetsa pa MacBook. Tikufuna docker, tiyeni tizipita. Ndizomwezo. Mutha kuyiyendetsa nthawi ina pachidutswa cha Hardware, kapena pamakina enieni, kulikonse.

Ndipo palinso mwayi wothamangira kutali ku Amazon mu EC2 Instance, m'malo. Ndipo uwu ndi mwayi wabwino kwambiri. Mwachitsanzo, dzulo tinayesa zoposa 500 pa chitsanzo cha i3, kuyambira wamng'ono kwambiri ndi kutha ndi i3-16-xlarge. Ndipo zoyeserera 500 zimatitengera $64. Iliyonse inatenga mphindi 15. Ndiko kuti, chifukwa chakuti mawanga amagwiritsidwa ntchito kumeneko, ndizotsika mtengo kwambiri - kuchotsera 70%, kulipira kwa Amazon pamphindi. Mutha kuchita zambiri. Mutha kuchita kafukufuku weniweni.

Ndipo mitundu itatu yayikulu ya Postgres imathandizidwa. Sizovuta kumaliza zina zakale komanso mtundu watsopano wa 12.

Tingathe kufotokozera chinthu m’njira zitatu. Izi:

• Tsitsani fayilo / sql.
• Njira yayikulu ndikugwirizanitsa chikwatu cha PGDATA. Monga lamulo, imatengedwa kuchokera ku seva yosunga zobwezeretsera. Ngati muli ndi zosunga zobwezeretsera zabwinobwino, mutha kupanga ma clones kuchokera pamenepo. Ngati muli ndi mitambo, ndiye kuti ofesi yamtambo monga Amazon ndi Google idzakuchitirani izi. Iyi ndiye njira yofunikira kwambiri yofananizira kupanga kwenikweni. Umu ndi momwe timawululira.
• Ndipo njira yomaliza ndiyoyenera kufufuza mukafuna kumvetsetsa momwe china chake chimagwirira ntchito ku Postgres. Ichi ndi pgbench. Mutha kupanga pogwiritsa ntchito pgbench. Ndi njira imodzi yokha ya "db-pgbench". Muuzeni sikelo yake. Ndipo zonse zidzapangidwa mumtambo, monga zanenedwa.

Ndipo katundu:

• Titha kuyika katunduyo mu ulusi umodzi wa SQL. Iyi ndiye njira yakale kwambiri.
• Ndipo tikhoza kutengera katunduyo. Ndipo ife tikhoza kutengera izo poyamba mwa zonse motere. Tiyenera kusonkhanitsa zipika zonse. Ndipo ndi zowawa. Ndikuwonetsani chifukwa chake. Ndipo kugwiritsa ntchito pgreplay timasewera, yomwe imamangidwa mu Nancy.
• Kapena njira ina. Zomwe zimatchedwa craft load, zomwe timachita ndi khama linalake. Kusanthula katundu wathu wapano pa dongosolo lankhondo, timatulutsa magulu apamwamba a zopempha. Ndipo pogwiritsa ntchito pgbench titha kutsanzira katundu uyu mu labotale.

• Mwina tiyenera kuchita mtundu wina wa SQL, mwachitsanzo, timayang'ana mtundu wina wakusamuka, kupanga index pamenepo, ndikuchita ANALAZE pamenepo. Ndipo timayang'ana zomwe zidachitika chisanachitike vacuum ndi pambuyo pa vacuum. Kawirikawiri, SQL iliyonse.
• Mwina tisintha magawo amodzi kapena angapo mu config. Titha kutiuza kuti tiwone, mwachitsanzo, 100 values ​​ku Amazon pa database yathu ya terabyte. Ndipo mu maola ochepa mudzakhala ndi zotsatira. Monga lamulo, zidzakutengerani maola angapo kuti mutumize database ya terabyte. Koma pali chigamba pakukula, tili ndi mndandanda wotheka, mwachitsanzo, mutha kugwiritsa ntchito pgdata yomweyo pa seva yomweyo ndikuwunika. Ma Postgres ayambiranso ndipo zosungira zidzakhazikitsidwanso. Ndipo mukhoza kuyendetsa katunduyo.

• Chikwatu chimafika ndi mulu wamafayilo osiyanasiyana, kuyambira pa pg snapshotsstat***. Ndipo chosangalatsa kwambiri ndi pg_stat_statements, pg_stat_kcacke. Izi ndi zowonjezera ziwiri zomwe zimasanthula zopempha. Ndipo pg_stat_bgwriter ilibe ziwerengero za pgwriter, komanso poyang'ana ndi momwe ma backends amachotsa zosungira zonyansa. Ndipo zonse ndizosangalatsa kuziwona. Mwachitsanzo, tikakhazikitsa share_buffers, ndizosangalatsa kuwona kuchuluka kwa aliyense m'malo mwake.
• Mitengo ya Postgres ikufikanso. Zipika ziwiri - chipika chokonzekera ndi chipika chojambulira katundu.
• Chinthu chatsopano ndi FlameGraphs.
• Komanso, ngati mutagwiritsa ntchito pgreplay kapena pgbench zosankha posewera katunduyo, ndiye kuti zotsatira zake zidzakhala zakwawo. Ndipo mudzawona latency ndi TPS. Zidzakhala zotheka kumvetsetsa momwe adaziwonera.
• Zambiri zamakina.
• Macheke a Basic CPU ndi IO. Izi ndi zinanso za EC2 ku Amazon, mukafuna kuyambitsa zochitika 100 zofanana mu ulusi ndikuyendetsa maulendo 100 osiyanasiyana pamenepo, mudzakhala ndi zoyesera 10. Ndipo muyenera kuwonetsetsa kuti simukumana ndi cholakwika chomwe chikuponderezedwa kale ndi wina. Ena akugwira ntchito pachidutswachi cha Hardware ndipo muli ndi zida zochepa zomwe zatsala. Ndi bwino kutaya zotsatirazi. Ndipo mothandizidwa ndi sysbench kuchokera ku Alexey Kopytov, timachita macheke angapo afupiafupi omwe angabwere ndipo akhoza kufananizidwa ndi ena, i.e. mudzamvetsetsa momwe CPU imakhalira komanso momwe IO imachitira.

Ndi zovuta zotani zaukadaulo kutengera zitsanzo zamakampani osiyanasiyana?

Tiyerekeze kuti tikufuna kubwereza katundu weniweni pogwiritsa ntchito zipika. Ndilo lingaliro labwino ngati lalembedwa pa Open Source pgreplay. Timachigwiritsa ntchito. Koma kuti igwire bwino ntchito, muyenera kuloleza kudula mitengo yonse ndi magawo ndi nthawi.

Pali zovuta zina ndi nthawi komanso nthawi. Tichotsa khitchini yonseyi. Funso lalikulu ndilakuti mungakwanitse kapena ayi?

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

Vuto ndilakuti mwina sapezeka. Choyamba, muyenera kumvetsetsa kuti mtsinje uti udzalembedwe pa chipikacho. Ngati muli ndi pg_stat_statements, mutha kugwiritsa ntchito funsoli (ulalo upezeka pazithunzi) kuti mumvetsetse kuchuluka kwa ma byte omwe adzalembedwe pamphindikati.

Timayang'ana kutalika kwa pempho. Tikunyalanyaza mfundo yoti palibe magawo, koma tikudziwa kutalika kwa pempholi ndipo tikudziwa kuti ndi kangati pa sekondi imodzi yomwe idaperekedwa. Mwanjira iyi titha kuyerekeza ma byte angati pamphindikati. Tikhoza kulakwitsa kuwirikiza kawiri, koma tidzamvetsetsa dongosolo motere.

Titha kuwona kuti nthawi 802 pa sekondi pempholi likuchitidwa. Ndipo tikuwona kuti ma bytes_per sec - 300 kB/s adzalembedwa kuphatikiza kapena kuchotsera. Ndipo, monga lamulo, titha kukwanitsa kuyenda kotere.

Koma! Zoona zake n’zakuti pali mitundu yosiyanasiyana yodula mitengo. Ndipo kusakhazikika kwa anthu nthawi zambiri kumakhala "syslog".

Ndipo ngati muli ndi syslog, ndiye kuti mutha kukhala ndi chithunzi chonga ichi. Titenga pgbench, yambitsani mitengo yamafunso ndikuwona zomwe zimachitika.

Popanda kudula mitengo - iyi ndiye gawo lakumanzere. Tili ndi 161 TPS. Ndi syslog - iyi ili ku Ubuntu 000 pa Amazon, timapeza 16.04 TPS. Ndipo ngati tisintha ku njira zina ziwiri zodula mitengo, ndiye kuti zinthu zikhala bwino. Ndiko kuti, tinkayembekezera kuti idzagwa, koma osati pamlingo womwewo.

Ndipo pa CentOS 7, yomwe nyuzipepala imakhalanso nawo, kutembenuza zipika kukhala mtundu wa binary kuti mufufuze mosavuta, ndi zina zotero, ndiye kuti ndizoopsa kumeneko, timasiya nthawi 44 mu TPS.

Ndipo izi ndi zomwe anthu amakhala nazo. Ndipo nthawi zambiri m'makampani, makamaka akuluakulu, izi ndizovuta kwambiri kusintha. Ngati mutha kuchoka ku syslog, chonde chokanipo.

• Yang'anirani IOPS ndikulemba mayendedwe.
• Onani ndondomeko yanu yodula mitengo.
• Ngati katundu yemwe akuyembekezeredwa ndi wamkulu kwambiri, lingalirani zachitsanzo.

Tili ndi pg_stat_statements. Monga ndidanenera, ziyenera kukhala pamenepo. Ndipo tikhoza kutenga ndi kufotokoza gulu lirilonse la zopempha mwapadera mu fayilo. Kenako titha kugwiritsa ntchito chinthu chosavuta kwambiri mu pgbench - uku ndikutha kuyika mafayilo angapo pogwiritsa ntchito njira ya "-f".

Zimamvetsetsa zambiri za "-f". Ndipo mutha kudziwa mothandizidwa ndi "@" pamapeto pake zomwe fayilo iliyonse iyenera kukhala nayo. Ndiye kuti, titha kunena kuti chitani izi mu 10% yamilandu, ndipo izi mu 20%. Ndipo izi zidzatifikitsa pafupi ndi zomwe timawona popanga.

Kodi tingamvetse bwanji zomwe tili nazo popanga? Gawani chiyani ndipo motani? Izi ndi mbali ina. Tili ndi chinthu chimodzi chinanso postgres-kufufuza. Komanso maziko mu Open Source. Ndipo tsopano tikukulitsa mwakhama.

Iye anabadwa pa zifukwa zosiyana pang'ono. Pazifukwa zomwe kuwunika sikokwanira. Ndiko kuti, mubwere, yang'anani pansi, yang'anani mavuto omwe alipo. Ndipo, monga lamulo, mumayang'ana thanzi. Ngati ndinu DBA wodziwa zambiri, ndiye kuti mukuchita health_check. Tinayang'ana pa ntchito indexes, etc. Ngati muli ndi OKmeter, ndiye zabwino. Uku ndikuwunikira kozizira kwa Postgres. OKmeter.io - chonde ikani, zonse zimachitika bwino pamenepo. Izo zalipidwa.

Ngati mulibe, ndiye kuti nthawi zambiri mulibe zambiri. Poyang'anira, nthawi zambiri pamakhala CPU, IO, ndiyeno ndikusungitsa, ndipo ndizo zonse. Ndipo ife tikusowa zambiri. Tiyenera kuwona momwe autovacuum imagwirira ntchito, momwe checkpoint imagwirira ntchito, mu io tiyenera kulekanitsa cheke kuchokera kwa bgwriter ndi kumbuyo, etc.

Vuto ndilakuti mukathandiza kampani yayikulu, sangathe kuchitapo kanthu mwachangu. Sangagule mwachangu OKmeter. Mwina adzagula mu miyezi isanu ndi umodzi. Sangathe kutumiza mapaketi mwachangu.

Ndipo tidabwera ndi lingaliro lakuti timafunikira chida chapadera chomwe sichifuna kuti chilichonse chiyike, mwachitsanzo, simuyenera kuyika chilichonse pakupanga. Ikani pa laputopu yanu, kapena pa seva yowonera komwe mungayendetse. Ndipo idzasanthula zinthu zambiri: makina ogwiritsira ntchito, mawonekedwe a fayilo, ndi Postgres palokha, kupanga mafunso opepuka omwe angayendetsedwe mwachindunji kupanga ndipo palibe chomwe chidzalephera.

Tidachitcha kuti Postgres-checkup. M'mawu azachipatala, uku ndikuwunika thanzi nthawi zonse. Ngati ili ndi mitu yamagalimoto, ndiye kuti ili ngati kukonza. Mumakonza galimoto yanu miyezi isanu ndi umodzi kapena chaka chilichonse, kutengera mtundu wake. Kodi mumakonza malo anu? Ndiko kuti, kodi mumafufuza mozama nthawi zonse? Izo ziyenera kuchitidwa. Ngati mupanga zosunga zobwezeretsera, fufuzani, izi ndizofunikanso.

Ndipo tili ndi chida choterocho. Idayamba kuwonekera mwachangu miyezi itatu yapitayo. Iye akadali wamng'ono, koma pali zambiri kumeneko.

Kusonkhanitsa mafunso "amphamvu" kwambiri - lipoti K003 mu Postgres-checkup

Ndipo pali gulu la malipoti K. Malipoti atatu mpaka pano. Ndipo pali lipoti loterolo K003. Pali pamwamba kuchokera pg_stat_statements, zosankhidwa ndi total_time.

Tikamasankha magulu opempha ndi Total_time, pamwamba timawona gulu lomwe limadzaza makina athu kwambiri, mwachitsanzo, limagwiritsa ntchito zinthu zambiri. N'chifukwa chiyani ndimatchula magulu a mafunso? Chifukwa tinataya magawo. Izi sizilinso zopempha, koma magulu a zopempha, mwachitsanzo, akufotokozedwa.

Ndipo ngati tikulitsa kuchokera pamwamba mpaka pansi, tidzapeputsa chuma chathu ndikuchedwetsa nthawi yomwe tikufuna kukweza. Iyi ndi njira yabwino kwambiri yopulumutsira ndalama.

Mwina iyi si njira yabwino kwambiri yosamalira ogwiritsa ntchito, chifukwa mwina sitingawone osowa, koma zokhumudwitsa kwambiri pamene munthu anadikira 15 masekondi. Pazonse, ndizosowa kwambiri moti sitiziwona, koma tikuchita ndi zothandizira.

Nchiyani chinachitika pa tebulo ili? Tinatenga zithunzi ziwiri. Postgres_checkup ikupatsani delta pa metric iliyonse: nthawi yonse, mafoni, mizere, share_blks_read, ndi zina zotero. Ndizo zonse, delta yawerengedwa. Vuto lalikulu ndi pg_stat_statements ndikuti silikumbukira pomwe idakhazikitsidwanso. Ngati pg_stat_database ikumbukira, ndiye kuti pg_stat_statements sakumbukira. Mukuwona kuti pali 1, koma sitikudziwa komwe tidawerengera.

Ndipo apa tikudziwa, apa tili ndi zithunzi ziwiri. Tikudziwa kuti delta pankhaniyi inali masekondi 56. Chipatala chachifupi kwambiri. Zosanjidwa ndi total_time. Ndiyeno tikhoza kusiyanitsa, mwachitsanzo, timagawa ma metric onse ndi nthawi. Ngati tigawa metric iliyonse ndi nthawi yayitali, tidzakhala ndi nambala yakuyimbira pa sekondi iliyonse.

Chotsatira, total_time pa sekondi iliyonse ndizomwe ndimakonda kwambiri. Imayesedwa mumasekondi, pamphindikati, mwachitsanzo, ndi masekondi angati zomwe zidatengera dongosolo lathu kuti likwaniritse zopempha izi pamphindikati. Ngati muwona kupitilira sekondi pa sekondi pamenepo, zikutanthauza kuti mumayenera kupereka kopitilira imodzi pachimake. Ichi ndi metric yabwino kwambiri. Mutha kumvetsetsa kuti mnzakeyu, mwachitsanzo, amafunikira ma cores osachepera atatu.

Uku ndiko kudziwa kwathu, sindinawonepo chilichonse ngati icho kulikonse. Chonde dziwani - ichi ndi chinthu chophweka - kachiwiri pa sekondi iliyonse. Nthawi zina, CPU yanu ikakhala 100%, ndiye theka la ola pamphindikati, ndiye kuti, mudakhala theka la ola mukuchita izi.

Kenako timawona mizere pamphindikati. Tikudziwa mizere ingati pa sekondi yomwe idabweza.

Ndiyeno palinso chinthu chochititsa chidwi. Ndi angati omwe adagawana_mabufa omwe timawerenga pamphindikati kuchokera ku share_buffers komwe. Kugunda kunali kale, ndipo tidatenga mizere kuchokera ku cache ya opaleshoni kapena kuchokera pa disk. Njira yoyamba ndi yofulumira, ndipo yachiwiri ikhoza kukhala yofulumira kapena ayi, malingana ndi momwe zinthu zilili.

Ndipo njira yachiwiri yosiyanitsira ndikugawa chiwerengero cha zopempha mu gulu ili. Mugawo lachiwiri mudzakhala ndi funso limodzi logawidwa pafunso lililonse. Ndipo ndizosangalatsa - ndi ma milliseconds angati omwe anali mu pempholi. Tikudziwa momwe funsoli limakhalira pafupipafupi. 101 milliseconds ankafunika pa pempho lililonse. Ichi ndiye metric yachikhalidwe yomwe tiyenera kumvetsetsa.

Kodi funso lililonse labweza mizere ingati? Tikuwona 8 gulu ili likubwerera. Pa avareji, ndi zingati zomwe zidatengedwa ku cache ndikuwerenga. Tikuwona kuti zonse zasungidwa bwino. Kumenyedwa kolimba kwa gulu loyamba.

Ndipo kachigawo kakang'ono kachinayi mumzere uliwonse ndi kuchuluka kwa kuchuluka kwake. Tili ndi mafoni. Tinene kuti 1. Ndipo titha kumvetsetsa zomwe gululi limapereka. Tikuwona kuti pakadali pano gulu loyamba limapereka zosakwana 000%. Ndiko kuti, ikuchedwa kwambiri kotero kuti sitikuiona pa chithunzi chonse. Ndipo gulu lachiwiri ndi 000% pa mafoni. Ndiko kuti, 0,01% ya mafoni onse ndi gulu lachiwiri.

Total_time ndiyosangalatsanso. Tinathera 14% ya nthawi yathu yonse ya ntchito pa gulu loyamba la zopempha. Ndipo chachiwiri - 11%, etc.

Sindingapite mwatsatanetsatane, koma pali zobisika pamenepo. Timawonetsa cholakwika pamwamba, chifukwa tikayerekeza, zithunzithunzi zimatha kuyandama, ndiko kuti, zopempha zina zimatha kugwa ndipo sizingakhaleponso mwachiwiri, pomwe zina zatsopano zitha kuwoneka. Ndipo pamenepo timawerengera cholakwika. Ngati muwona 0, ndiye kuti ndizabwino. Palibe zolakwika. Ngati chiwopsezo chikufikira 20%, zili bwino.

Kenako tibwereranso kumutu wathu. Tiyenera kugwira ntchito. Timazitenga kuchokera pamwamba mpaka pansi ndikupita mpaka titafika 80% kapena 90%. Kawirikawiri izi ndi magulu 10-20. Ndipo timapanga mafayilo a pgbench. Timagwiritsa ntchito mwachisawawa pamenepo. Nthawi zina izi, mwatsoka, sizikuyenda. Ndipo mu Postgres 12 padzakhala mipata yambiri yogwiritsira ntchito njirayi.

Kenako timapeza 80-90% mu nthawi yonse motere. Ndiyike chiyani pambuyo pa "@"? Timayang'ana mafoni, kuyang'ana kuchuluka kwa chidwi komwe kulipo ndikumvetsetsa kuti tili ndi ngongole zambiri pano. Kuchokera pamaperesenti awa titha kumvetsetsa momwe mungasinthire mafayilo onse. Pambuyo pake timagwiritsa ntchito pgbench ndikupita kuntchito.

Tilinso ndi K001 ndi K002.

K001 ndi chingwe chimodzi chachikulu chokhala ndi zingwe zinayi. Ichi ndi chikhalidwe cha katundu wathu wonse. Onani gawo lachiwiri ndi lachiwiri. Tikuwona kuti pafupifupi masekondi ndi theka pamphindikati, mwachitsanzo, ngati pali ma cores awiri, ndiye kuti zikhala bwino. Padzakhala pafupifupi 75% mphamvu. Ndipo izo zigwira ntchito motere. Ngati tili ndi ma cores 10, ndiye kuti nthawi zambiri tikhala odekha. Mwanjira iyi titha kuwunika momwe zinthu ziliri.

K002 ndizomwe ndimatcha query classes, mwachitsanzo, SELECT, INSERT, UPDATE, DELETE. Ndipo padera SINANI ZOCHITIKA, chifukwa ndi loko.

Ndipo apa tikhoza kunena kuti SELECT ndi owerenga wamba - 82% ya mafoni onse, koma nthawi yomweyo - 74% mu total_time. Ndiko kuti, amatchedwa zambiri, koma amadya zinthu zochepa.

Ndipo timabwerera ku funso: "Tingasankhe bwanji zogawana_mabuffer?" Ndikuwona kuti ma benchmark ambiri amatengera lingaliro - tiyeni tiwone zomwe zidzachitike, mwachitsanzo, zomwe zidzachitike. Nthawi zambiri amayezedwa mu TPS kapena QPS.

Ndipo timayesa kufinya zambiri zomwe tingathe pa sekondi imodzi kuchokera pagalimoto pogwiritsa ntchito magawo osinthira. Apa pali ndendende 311 pamphindikati posankha.

Koma palibe amene amayendetsa galimoto kupita kuntchito ndi kubwerera kunyumba mofulumira. Izi ndi zopusa. Zomwezo ndi databases. Sitiyenera kuyendetsa galimoto mothamanga kwambiri, ndipo palibe amene amatero. Palibe amene amakhala mukupanga, komwe kuli 100% CPU. Ngakhale, mwinamwake wina amakhala, koma izi si zabwino.

Lingaliro ndiloti nthawi zambiri timayendetsa pa 20 peresenti ya mphamvu, makamaka osapitirira 50%. Ndipo timayesetsa kukulitsa nthawi yoyankha kwa ogwiritsa ntchito kuposa zonse. Ndiko kuti, tiyenera kutembenuza nsonga zathu kuti pakhale latency yochepa pa liwiro la 20%, mokhazikika. Ili ndi lingaliro lomwe timayesetsanso kugwiritsa ntchito pazoyeserera zathu.

Ndipo pomaliza, malangizo:

• Onetsetsani kuti mukuchita Database Lab.
• Ngati n'kotheka, chitani pofunidwa kuti chiwonekere kwakanthawi - sewera ndikutaya. Ngati muli ndi mitambo, ndiye kuti izi sizikutanthauza, mwachitsanzo, mukhale ndi maimidwe ambiri.
• Khalani ndi chidwi. Ndipo ngati chinachake chalakwika, fufuzani ndi zoyesera momwe zimakhalira. Nancy angagwiritsidwe ntchito kudziphunzitsa kuti muwone momwe maziko amagwirira ntchito.
• Ndipo khalani ndi nthawi yochepa yoyankha.
• Ndipo musawope magwero a Postgres. Mukamagwira ntchito ndi magwero, muyenera kudziwa Chingerezi. Pali ndemanga zambiri kumeneko, zonse zikufotokozedwa pamenepo.
• Ndipo yang'anani thanzi la nkhokwe pafupipafupi, kamodzi pa miyezi itatu iliyonse, pamanja, kapena Postgres-checkup.

Mafunso anu

Zikomo kwambiri! Chinthu chosangalatsa kwambiri.

Zidutswa ziwiri.

Inde, zidutswa ziwiri. Kungoti sindimamvetsetsa. Ine ndi Nancy tikamagwira ntchito, tingathe kusintha gawo limodzi kapena gulu lonse?

Tili ndi delta config parameter. Mutha kutembenukira kumeneko momwe mukufunira nthawi imodzi. Koma muyenera kumvetsetsa kuti mukasintha zinthu zambiri, mutha kupeza malingaliro olakwika.

Inde. Ndafunsa chifukwa chiyani? Chifukwa ndizovuta kuchita zoyeserera mukakhala ndi gawo limodzi lokha. Inu muzilimbitsa izo, onani momwe zimagwirira ntchito. Ndidamutulutsa. Kenako mumayamba chotsatira.

Mukhoza kulimbitsa nthawi yomweyo, koma zimadalira momwe zinthu zilili, ndithudi. Koma ndi bwino kuyesa lingaliro limodzi. Tinali ndi lingaliro dzulo. Tinali ndi mkhalidwe woyandikana kwambiri. Panali ma configs awiri. Ndipo sitinkamvetsa chifukwa chake panali kusiyana kwakukulu. Ndipo lingaliro lidawuka loti muyenera kugwiritsa ntchito dichotomy kuti mumvetsetse nthawi zonse ndikupeza kusiyana kwake. Mutha kupanga nthawi yomweyo theka la magawo omwewo, ndiye kotala, etc. Chilichonse chimasinthasintha.

Ndipo pali funso linanso. Ntchitoyi ndi yachinyamata komanso ikukula. Zolembazo zakonzeka kale, kodi pali kufotokozera mwatsatanetsatane?

Ndidapanga ulalo pamenepo kuti ndikufotokozereni magawo. Zili pamenepo. Koma zinthu zambiri sizinalipobe. Ndikuyang'ana anthu amalingaliro ofanana. Ndipo ndimawapeza ndikamachita. Izi ndizabwino kwambiri. Winawake akugwira ntchito ndi ine kale, wina adandithandiza ndipo adachitapo kanthu pamenepo. Ndipo ngati muli ndi chidwi ndi mutuwu, perekani ndemanga pazomwe zikusowa.

Tikamanga labotale, mwina padzakhala ndemanga. Tiyeni tiwone. Zikomo!

Moni! Zikomo chifukwa cha lipoti! Ndinawona kuti pali thandizo la Amazon. Kodi pali mapulani othandizira GSP?

Funso labwino. Tinayamba kuchita. Ndipo tidayimitsa pano chifukwa tikufuna kusunga ndalama. Ndiye kuti, pali thandizo pogwiritsa ntchito run on localhost. Mukhoza kupanga chitsanzo nokha ndikugwira ntchito kwanuko. Mwa njira, ndi zomwe timachita. Ndimachita izi ku Getlab, ku GSP. Koma sitikuwona cholinga chopanga kuyimba koteroko, chifukwa Google ilibe malo otsika mtengo. Pali ??? zochitika, koma ali ndi malire. Choyamba, nthawi zonse amakhala ndi kuchotsera kwa 70% ndipo simungathe kusewera ndi mtengo pamenepo. Pamalo, timawonjezera mtengo ndi 5-10% kuti muchepetse mwayi woti mudzakankhidwe. Ndiko kuti, mumasunga mawanga, koma akhoza kuchotsedwa kwa inu nthawi iliyonse. Ngati mupereka ndalama zambiri kuposa momwe ena amachitira, mudzaphedwa pambuyo pake. Google ili ndi zosiyana kwambiri. Ndipo pali malire ena oyipa kwambiri - amakhala maola 24 okha. Ndipo nthawi zina timafuna kuyesa kwa masiku asanu. Koma mutha kuchita izi m'malo; mawanga nthawi zina amakhala kwa miyezi ingapo.

Moni! Zikomo chifukwa cha lipoti! Mwatchulapo za kuyezetsa. Kodi mumawerengera bwanji zolakwika za stat_statements?

Funso labwino kwambiri. Ndikhoza kukuwonetsani ndikukuuzani mwatsatanetsatane. Mwachidule, timayang'ana momwe magulu opempha adayandama: angati agwa ndi angati atsopano omwe adawonekera. Ndiyeno timayang'ana ma metrics awiri: Total_time ndi mafoni, kotero pali zolakwika ziwiri. Ndipo timayang'ana zopereka za magulu oyandama. Pali magulu awiri: omwe adachoka ndi omwe adafika. Tiyeni tiwone chomwe athandizira pa chithunzi chonse.

Kodi simukuwopa kuti idzatembenukira pamenepo kawiri kapena katatu panthawi yapakati pazithunzi?

Ndiko kuti adalembetsanso kapena chiyani?

Mwachitsanzo, pempholi lidayesedwa kale kamodzi, kenako linabwera ndikuyesedwanso, kenako linabweranso ndipo linayesedwanso. Ndipo mwawerengerapo kanthu apa, ndipo zonsezi zili kuti?

Funso labwino, tiyenera kuyang'ana.

Ndinachitanso chimodzimodzi. Zinali zophweka, ndithudi, ndinazichita ndekha. Koma ndinayenera kukonzanso, kukonzanso stat_statements ndikuwona pa nthawi ya chithunzithunzi kuti panali zochepa kuposa gawo linalake, lomwe silinafike padenga la kuchuluka kwa ma stat_statements angaunjike pamenepo. Ndipo kumvetsetsa kwanga ndikuti, mwachidziwikire, palibe chomwe chidachotsedwa.

Inde Inde.

Koma sindikumvetsa momwe ndingachitire modalirika.

Tsoka ilo, sindikukumbukira ngati timagwiritsa ntchito mawu apa kapena queryid ndi pg_stat_statements ndikuyang'ana kwambiri. Ngati tiyang'ana pa queryid, ndiye kuti m'malingaliro tikufanizira zinthu zofanana.

Ayi, akhoza kutulutsidwa kangapo pakati pazithunzi ndikubweranso.

Ndi id yomweyi?

Inde.

Tiphunzira izi. Funso labwino. Tiyenera kuliphunzira. Koma pakadali pano, zomwe tikuwona mwina zidalembedwa 0 ...

Izi ndizosowa, koma ndidadabwa nditazindikira kuti stat_statemetns imatha kuchoka pamenepo.

Pakhoza kukhala zinthu zambiri mu Pg_stat_statements. Tidazindikira kuti ngati muli ndi track_utility = on, ndiye kuti ma seti anu amatsatiridwanso.

Inde ndithudi.

Ndipo ngati muli ndi java hibernate, yomwe ili mwachisawawa, ndiye kuti tebulo la hashi limayamba kupezeka pamenepo. Ndipo mukangoyimitsa pulogalamu yodzaza kwambiri, mumakhala ndi magulu 50-100. Ndipo chilichonse chimakhala chokhazikika pamenepo. Njira imodzi yothanirana ndi izi ndikuwonjezera pg_stat_statements.max.

Inde, koma muyenera kudziwa kuti zingati. Ndipo mwanjira ina tiyenera kuyang’anitsitsa pa iye. Ndi zomwe ndimachita. Ndiye kuti, ndili ndi pg_stat_statements.max. Ndipo ndikuwona kuti panthawi yachithunzichi sindinafike 70%. Chabwino, kotero sitinataye kalikonse. Tiyeni tikonzenso. Ndipo timasunganso. Ngati chithunzi chotsatira chili chochepera 70, ndiye kuti simunataye kalikonse.

Inde. Zosasintha tsopano ndi 5. Ndipo izi ndizokwanira kwa anthu ambiri.

Nthawi zambiri inde.

Video:

PS M'malo mwa ine ndekha, ndikuwonjezera kuti ngati Postgres ili ndi zinsinsi ndipo sizingaphatikizidwe m'malo oyesera, ndiye kuti mutha kugwiritsa ntchito PostgreSQL Anonymizer. Dongosolo ili pafupifupi motere:

Source: www.habr.com