Eya
Dzina langa ndine Vanya ndipo ndine wopanga Java. Zimachitika kuti ndimagwira ntchito kwambiri ndi PostgreSQL - kukhazikitsa nkhokwe, kukhathamiritsa kapangidwe kake, magwiridwe antchito, ndikusewera DBA pang'ono kumapeto kwa sabata.
Posachedwapa ndakonza nkhokwe zingapo m'ma microservices athu ndikulemba laibulale ya java , zomwe zimapangitsa kuti ntchitoyi ikhale yosavuta, imandisungira nthawi komanso imandithandiza kupewa zolakwika zomwe anthu ambiri amapanga. Ndi laibulale iyi yomwe tikambirana lero.
chandalama
Mtundu waukulu wa PostgreSQL womwe ndimagwira nawo ndi 10. Mafunso onse a SQL omwe ndimagwiritsa ntchito amayesedwanso pa mtundu 11. Mtundu wocheperako wothandizidwa ndi 9.6.
prehistory
Zonse zidayamba pafupifupi chaka chapitacho ndi zinthu zomwe zinali zachilendo kwa ine: kupanga mpikisano wa index kuchokera pamtambo kunatha ndi cholakwika. Mlozera womwewo, monga mwanthawi zonse, udakhalabe munkhokwe molakwika. Kusanthula kwamitengo kunawonetsa kuchepa . Ndipo timapita ... Kukumba mozama, ndinapeza mulu wonse wa mavuto mu kasinthidwe ka database ndipo, ndikugudubuza manja anga, ndinayamba kuwakonza ndi kunyezimira m'maso mwanga.
Vuto loyamba - kasinthidwe kosasintha
Mwinamwake aliyense ali wotopa kwambiri ndi fanizo la Postgres, lomwe lingathe kuyendetsedwa pa wopanga khofi, koma ... kusinthika kosasintha kumadzutsa mafunso angapo. Pang'ono ndi pang'ono, ndi bwino kumvetsera kukonza_work_mem, temp_file_limit, statement_timeout ΠΈ lock_timeout.
Kwa ife kukonza_work_mem anali kusakhulupirika 64 MB, ndi temp_file_limit china chozungulira 2 GB - tinalibe kukumbukira kokwanira kuti tipange index patebulo lalikulu.
Chifukwa chake, mu pg-index-umoyo Ndinatolera zingapo , m'malingaliro anga, magawo omwe ayenera kukhazikitsidwa pa database iliyonse.
Vuto lachiwiri - zobwerezabwereza
Ma database athu amakhala pama drive a SSD, ndipo timagwiritsa ntchito HA-Kukonzekera ndi malo angapo a data, master host ndi n- chiwerengero cha zofananira. Danga la Disk ndilofunika kwambiri kwa ife; ndizofunikanso kuposa momwe zimagwirira ntchito komanso kugwiritsa ntchito CPU. Chifukwa chake, mbali imodzi, timafunikira ma index owerengera mwachangu, ndipo kumbali ina, sitikufuna kuwona ma index osafunikira mu database, chifukwa amadya malo ndikuchepetsa kukonzanso kwa data.
Ndipo tsopano, atabwezeretsa chirichonse ndi kuona mokwanira , ndinaganiza zokonza zoyeretsa "zabwino". Zinapezeka kuti opanga sakonda kuwerenga zolemba za database. Sakonda kwambiri. Pachifukwa ichi, pali zolakwika ziwiri zomwe zimachitika - index yopangidwa pamanja pa kiyi yoyamba ndi "manual" index pagawo lapadera. Chowonadi ndi chakuti sakufunika - Postgres adzachita zonse zokha. Zolozera zoterezi zitha kuchotsedwa bwino, ndipo zowunikira zawonekera pachifukwa ichi .
Vuto lachitatu - ma index a intersecting
Madivelopa ambiri a novice amapanga ma index pagawo limodzi. Pang'onopang'ono, atadziwa bwino bizinesiyi, anthu amayamba kuwongolera mafunso awo ndikuwonjezera ma index omwe ali ndi magawo angapo. Umu ndi momwe ma index pamizere amawonekera A, A + B, A + B + C ndi zina zotero. Ziwiri zoyamba za zizindikirozi zitha kutayidwa bwino, popeza ndizoyambira zachitatu. Izi zimapulumutsanso malo ambiri a disk ndipo pali zowunikira pa izi .
Vuto lachinayi - makiyi akunja opanda ma index
Postgres imakupatsani mwayi wopanga zopinga zakunja popanda kufotokoza cholozera chothandizira. Nthawi zambiri izi sizovuta, ndipo sizingawonekere ...
Zinali chimodzimodzi ndi ife: zimangokhala kuti panthawi ina ntchito, ikuyenda molingana ndi ndondomeko ndikuchotsa mndandanda wa malamulo a mayesero, inayamba "kuwonjezeredwa" kwa ife ndi mbuye wamkulu. CPU ndi IO zinawonongeka, zopempha zinachepetsedwa ndipo zinatha nthawi, ntchitoyo inali mazana asanu. Kusanthula mwachangu adawonetsa kuti mafunso monga:
delete from <table> where id in (β¦)Pamenepa, ndithudi, panali index ndi id mu tebulo chandamale, ndipo zolemba zochepa kwambiri zinachotsedwa malinga ndi chikhalidwe. Zinkawoneka ngati zonse ziyenera kugwira ntchito, koma, tsoka, sizinatero.
Wodabwitsa adabwera kudzapulumutsa fotokozani kusanthula ndipo ananena kuti kuwonjezera pa kuchotsa zolembedwa mu gome lomwe mukufuna, palinso cheke cha umphumphu, ndipo pa imodzi mwa matebulo okhudzana ndi cheke ichi sichilephera. jambulani motsatizana chifukwa chosowa index yoyenera. Motero diagnostics anabadwa .
Vuto lachisanu - mtengo wopanda pake muzolozera
Mwachikhazikitso, Postgres imaphatikizapo zinthu zopanda pake m'ma index a btree, koma nthawi zambiri safunikira pamenepo. Chifukwa chake, ndimayesetsa mwamphamvu kutaya izi (diagnostics ), kupanga zolozera pang'ono pamizere yosasinthika potengera mtundu where <A> is not null. Mwanjira imeneyi ndinatha kuchepetsa kukula kwa imodzi mwazolozera zathu kuchokera ku 1877 MB kufika ku 16 KB. Ndipo mu umodzi mwamautumikiwo, kukula kwa database kudatsika ndi 16% (ndi 4.3 GB mu manambala mtheradi) chifukwa cha kuchotsedwa kwa zinthu zopanda pake pama index. Kupulumutsa kwakukulu mu disk space ndi zosintha zosavuta. π
Vuto lachisanu ndi chimodzi - kusowa kwa makiyi oyambirira
Chifukwa cha chikhalidwe cha makina zinthu ngati izi ndizotheka pamene kukula kwa tebulo lanu kukukula mofulumira chifukwa cha zolemba zambiri zakufa. Ndidakhulupirira mopusa kuti izi sizingatiwopseza, komanso kuti izi sizingachitike ku maziko athu, chifukwa ife, wow !!!, ndife otukuka bwino ... Ndinali wopusa komanso wosazindikira ...
Tsiku lina, kusamuka kumodzi kodabwitsa kunatenga ndikusintha zolemba zonse patebulo lalikulu komanso logwiritsidwa ntchito mwachangu. Tili ndi +100 GB pakukula kwa tebulo kunja kwa buluu. Zinali zamanyazi kwambiri, koma zowawa zathu sizinathere pamenepo. Pambuyo pa autovacuum patebuloli itatha maola 15 pambuyo pake, zinaonekeratu kuti malo enieniwo sangabwerere. Sitinathe kuyimitsa ntchitoyi ndikupanga VACUUM FULL, kotero tinaganiza zogwiritsa ntchito . Ndiyeno zinapezeka kuti pg_pack sadziwa kukonza matebulo popanda kiyi yoyamba kapena zopinga zina zapadera, ndipo tebulo lathu linalibe kiyi yoyamba. Motero diagnostics anabadwa .
Mu laibulale Baibulo 0.1.5 Kutha kusonkhanitsa deta kuchokera ku bloat of tables ndi indexes ndikuyankha mu nthawi yake yawonjezedwa.
Mavuto asanu ndi awiri ndi asanu ndi atatu - ma index osakwanira ndi ma index osagwiritsidwa ntchito
Ma diagnostics awiri otsatirawa ndi: ΠΈ - adawonekera mu mawonekedwe awo omaliza posachedwa. Mfundo ndi yakuti sakanangotengedwa ndikuwonjezedwa.
Monga ndalembera kale, timagwiritsa ntchito masinthidwe okhala ndi ma replicas angapo, ndipo kuchuluka kwa zowerengera pa makamu osiyanasiyana ndikosiyana kwambiri. Zotsatira zake, zimachitika kuti matebulo ndi ma index ena pa makamu ena sagwiritsidwa ntchito, ndipo kuti muwunike muyenera kusonkhanitsa ziwerengero kuchokera kwa onse omwe ali mgululi. Izi ndizofunikiranso pagulu lililonse lamagulu; simungathe kuchita izi pa master.
Njirayi inatithandiza kupulumutsa makumi angapo a ma gigabytes mwa kuchotsa ma index omwe sanagwiritsidwepo ntchito, komanso kuwonjezera ma index omwe akusowa pa matebulo omwe sagwiritsidwa ntchito kawirikawiri.
Monga chomaliza
Kumene, pafupifupi diagnostics onse mukhoza sintha . Mwanjira iyi, mutha kukhazikitsa macheke mu pulogalamu yanu mwachangu, kuletsa zolakwika zatsopano kuti zisawonekere, kenako pang'onopang'ono kukonza zakale.
Kuzindikira kwina kutha kuchitidwa pakuyesa magwiridwe antchito atangotulutsa kusamuka kwa database. Ndipo ichi mwina ndi chimodzi mwazinthu zamphamvu kwambiri za library yanga. Chitsanzo chogwiritsira ntchito chingapezeke mu .
Ndizomveka kuyang'ana ma index omwe sanagwiritsidwe ntchito kapena osowa, komanso bloat, pokhapokha pa database yeniyeni. Zomwe zasonkhanitsidwa zitha kulembedwa mkati kapena kutumizidwa ku kachitidwe kowunika.
Ine ndikuyembekezadi zimenezo pg-index-umoyo zidzakhala zothandiza komanso zofunika. Mukhozanso kuthandizira pa chitukuko cha laibulale pofotokoza mavuto omwe mwawapeza ndi kusonyeza matenda atsopano.
Source: www.habr.com