I kekahi lā i ka wā e hiki mai ana, ʻo ka wehe ʻana i nā ʻikepili pono ʻole kekahi o nā hana koʻikoʻi o kahi DBMS [1]. I kēia manawa, pono mākou iā mākou iho e mālama i ka holoi ʻana a i ʻole ka hoʻoneʻe ʻana i nā ʻikepili pono ʻole i nā ʻōnaehana mālama liʻiliʻi. E ʻōlelo mākou e hoʻoholo ʻoe e holoi i nā lālani miliona. ʻO kahi hana maʻalahi loa, ʻoiai inā ʻike ʻia ke kūlana a aia kahi kuhikuhi kūpono. "HELE MAI ka papa1 WHERE col1 = : waiwai" - he aha ka mea maʻalahi, ʻaʻole?
Video:
-
Aia wau ma ke komite papahana Highload mai ka makahiki mua, ʻo ia hoʻi mai ka makahiki 2007.
-
A ua hele au me Postgres mai ka makahiki 2005. Hoʻohana ʻia ia i nā papahana he nui.
-
Ua hui pū ka hui me RuPostges mai 2007.
-
Ua ulu mākou i 2100+ mau mea komo ma Meetup. ʻO ka lua kēia o ka honua ma hope o New York, ʻo ia ka mea ma mua o Kapalakiko i kahi manawa lōʻihi.
-
Ua noho au ma Kaleponi no kekahi mau makahiki. Hana nui wau me nā hui ʻAmelika, me nā hui nui. He poʻe hoʻohana Postgres ikaika lākou. A ʻo nā ʻano mea hoihoi a pau e kū mai ana ma laila.
– ʻo koʻu hui kēia. Aia mākou i ka ʻoihana o ka automating i nā hana e hoʻopau i ka lohi o ka hoʻomohala ʻana.
Inā hana ʻoe i kekahi mea, i kekahi manawa aia kekahi mau glitches a puni Postgres. E ʻōlelo mākou pono ʻoe e kali a hiki i ka loaʻa ʻana o ke alakaʻi iā ʻoe i kahi papa hoʻāʻo, a i ʻole pono ʻoe e kali a hiki i ka pane ʻana o ka DBA iā ʻoe. A ʻike mākou i nā bottlenecks i ka hoʻomohala ʻana, ka hoʻāʻo ʻana a me ke kaʻina hoʻokele a hoʻāʻo e hoʻopau iā lākou me ka hoʻohana ʻana i ka automation a me nā ala hou.
Ua hele au ma VLDB ma Los Angeles. ʻO kēia ka hālāwai kūkā nui loa. A aia kahi hōʻike i ka wā e hiki mai ana ʻaʻole e mālama wale nā DBMS, akā e holoi maʻalahi i ka ʻikepili. He kumuhana hou kēia.
Nui aʻe ka ʻikepili ma ka honua. ʻO 1 petabytes ka Zetabytes. A i kēia manawa ua manaʻo ʻia ua loaʻa iā mākou ma mua o 000 zettabytes o ka ʻikepili i mālama ʻia ma ka honua. A ʻoi aku ka nui o lākou.
A he aha ka hana me ia? Ua maopopo e pono ke holoi ia. Eia kahi loulou i kēia hōʻike hoihoi. Akā i kēia manawa ʻaʻole i hoʻokō ʻia kēia i ka DBMS.
ʻO ka poʻe ʻike i ka helu kālā, makemake lākou i ʻelua mea. Makemake lākou e holoi mākou, no laila pono mākou e hana.
ʻO kaʻu e haʻi aʻe nei he kūlana abstract e pili ana i kahi pūʻulu o nā kūlana maoli, ʻo ia hoʻi, kahi hōʻuluʻulu o nā mea i loaʻa iaʻu a me nā ʻikepili a puni i nā manawa he nui, mau makahiki. Aia nā Rakes ma nā wahi a pau a hehi nā kānaka a pau iā lākou i nā manawa a pau.
E ʻōlelo kākou he kumu a i ʻole kekahi mau kumu e ulu nei. A he ʻōpala maopopo kekahi o nā moʻolelo. No ka laʻana, hoʻomaka ka mea hoʻohana e hana i kekahi mea ma laila, akā ʻaʻole i pau. A ma hope o kekahi manawa, ʻike mākou ʻaʻole hiki ke mālama hou ʻia kēia hana i pau ʻole. ʻO ia hoʻi, makemake mākou e hoʻomaʻemaʻe i kekahi mau mea ʻino e mālama i ka lewa, hoʻomaikaʻi i ka hana, etc.
Ma keʻano laulā, ʻo ka hana ka hoʻopau ʻana i nā mea kikoʻī, nā laina kikoʻī i kahi papaʻaina.
A he noi kā mākou e kamaʻilio ai i kēia lā, ʻo ia hoʻi, e pili ana i ka lawe ʻōpala.
Ua noi mākou i kahi mea hoʻomohala akamai e hana i kēia. Lawe ʻo ia i kēia noi, nānā iā ia iho - hana nā mea a pau. Ua hoʻāʻo au iā ia ma ka hoʻokūkū - maikaʻi nā mea a pau. Hoʻopili ʻia - hana nā mea a pau. Hoʻokahi manawa i ka lā holo mākou i kēia - maikaʻi nā mea a pau.
Ke ulu a ulu nei ka waihona. Hoʻomaka lohi iki ka DELETE i kēlā me kēia lā.
A laila ʻike mākou he ʻoihana kūʻai mākou i kēia manawa a ʻoi aku ka nui o ke kaʻa, no laila hoʻoholo mākou e hoʻomaha iki i nā mea pono ʻole. A poina mākou e hoʻihoʻi.
Ma hope o kekahi mau mahina, hoʻomanaʻo lākou. A haʻalele kēlā mea hoʻomohala a hana paha i kekahi mea ʻē aʻe, hoʻonoho lākou i kekahi e hoʻihoʻi.
Ua nānā ʻo ia i ka dev, ma ke kahua - ua maikaʻi nā mea a pau. Ma keʻano maoli, pono ʻoe e hoʻomaʻemaʻe i nā mea i hōʻiliʻili ʻia. Ua nānā ʻo ia, hana nā mea a pau.
He aha ka hope? A laila hāʻule nā mea a pau iā mākou. Hāʻule nui ia i kekahi manawa hāʻule nā mea a pau i lalo. Pīhoihoi nā mea a pau, ʻaʻohe mea i hoʻomaopopo i ka mea e hana nei. A laila ua ʻike ʻia ʻo kēia DELETE ka pilikia.
Ua hewa kekahi mea? Eia ka papa inoa o nā mea hiki ke hele hewa. ʻO wai o kēia mau mea nui loa?
-
No ka laʻana, ʻaʻohe loiloi, ʻo ia hoʻi, ʻaʻole i nānā ka loea DBA. Me ka ʻike maka, e ʻike koke ʻo ia i ka pilikia, a ma waho aʻe, hiki iā ia ke komo i ka prod, kahi i hōʻuluʻulu ʻia ai nā miliona mau laina.
-
Ua nānā hewa paha lākou.
-
Malia paha ua kahiko ka lako a pono ʻoe e hoʻomaikaʻi i kēia waihona.
-
A i ʻole kekahi mea i hewa me ka waihona ponoʻī, a pono mākou e neʻe mai Postgres i MySQL.
-
A i ʻole he hewa paha ka hana.
-
Aia paha kekahi mau hewa i ka hoʻonohonoho ʻana i ka hana a pono ʻoe e puhi i kekahi a hoʻolimalima i nā poʻe ʻoi aku ka maikaʻi?
ʻAʻohe hōʻoia DBA. Inā he DBA, e ʻike ʻo ia i kēia mau laina miliona a ʻoiai ʻaʻohe hoʻokolohua e ʻōlelo: "ʻAʻole lākou e hana pēlā." E ʻōlelo mākou inā aia kēia code ma GitLab, GitHub a aia kahi kaʻina loiloi code a ʻaʻohe mea like me ka ʻole o ka ʻae ʻana o DBA e hana ʻia kēia hana ma ka prod, a laila maopopo ka ʻōlelo ʻana o ka DBA: "ʻAʻole hiki iā ʻoe ke hana i kēlā. ”
A e ʻōlelo ʻo ia e loaʻa iā ʻoe nā pilikia me ka disk IO a e hele pupule nā kaʻina āpau, aia paha nā laka, a e hoʻopaʻa pū ʻoe i ka autovacuum no kahi mau minuke, no laila ʻaʻole maikaʻi kēia.
ʻO ka lua o ka hewa ʻo ia kā lākou nānā ʻana ma kahi hewa. Ua ʻike mākou ma hope o ka nui o nā ʻōpala i hōʻiliʻili ʻia ma ka prod, akā ʻaʻole i hōʻiliʻili ka mea hoʻomohala i ka ʻikepili i loko o kēia waihona, a ʻaʻohe mea nāna i hana i kēia ʻōpala ma ka staging. No laila, he 1 laina, i hoʻopau koke ʻia.
Hoʻomaopopo mākou he nāwaliwali kā mākou mau hoʻāʻo, ʻo ia hoʻi, ʻo ke kaʻina hana i kūkulu ʻia ʻaʻole hopu i nā pilikia. ʻAʻole i hana ʻia kahi hoʻokolohua DB kūpono.
Pono e hana ʻia kahi hoʻokolohua kūpono ma luna o nā lako like. ʻAʻole hiki i nā manawa a pau ke hana i kēia ma ka lako like, akā he mea nui loa ia he kope piha piha o ka waihona. ʻO kēia kaʻu e haʻi aku nei no kekahi mau makahiki i kēia manawa. A i hoʻokahi makahiki i hala ua kamaʻilio wau e pili ana i kēia, hiki iā ʻoe ke nānā iā ia a pau ma YouTube.
He ʻino paha kā mākou mea hana? Inā ʻoe e nānā, ua lele ka latency. Ua ʻike mākou he 100% ka hana hou ʻana. ʻOiaʻiʻo, inā he mau NVMe drive kēia, a laila ʻoi aku ka maʻalahi o mākou. A ʻaʻole paha mākou e moe i lalo no ia mea.
Inā he ao kāu, a laila maʻalahi ka hoʻonui. Ua hoʻokuʻu mākou i nā kope hou ma nā ʻenehana hou. Hoʻololi. A maikaʻi nā mea a pau. Maʻalahi loa.
Hiki paha ke hoʻopā i nā diski liʻiliʻi? A eia, me ke kōkua o DBA, luʻu mākou i kekahi kumuhana i kapa ʻia ʻo checkpoint tuning. ʻIke ʻia ʻaʻole mākou i hoʻokō i ka hoʻopaʻa ʻana i ka wahi nānā.
He aha ke ʻano hōʻoia? Loaʻa kēia ma kekahi DBMS. Ke loli ka ʻikepili i kāu hoʻomanaʻo, ʻaʻole i kākau koke ʻia i nā disks. Ua kākau mua ʻia ka ʻike i hoʻololi ʻia ka ʻikepili i ka moʻolelo mua, ma ka log kākau mua. A i kekahi manawa, hoʻoholo ka DBMS ua hiki i ka manawa e hoʻolei i nā ʻaoʻao maoli i ka disk, no laila inā loaʻa iā mākou kahi hemahema, hiki iā mākou ke hana i ka REDO liʻiliʻi. Me he mea pāʻani lā. Inā make mākou, e hoʻomaka mākou i ka pāʻani mai ka helu hope loa. A hoʻokō nā DBMS a pau i kēia.
Ke lohi nei nā hoʻonohonoho ma Postgres. Hoʻolālā ʻia lākou no nā makahiki 10-15 mau makahiki o ka ʻikepili a me nā hana. A ʻaʻohe mea ʻē aʻe ka wahi nānā.
ʻO kēia ʻike mai kā mākou hōʻike me Postgres check-up, i.e. A eia kekahi waihona o nā terabytes. A maopopo ke koi ʻia nā mākaʻi ma kahi o 90% o nā hihia.
He aha ka manaʻo? ʻElua mau hoʻonohonoho ma laila. Hiki ke hōʻoia i ka manawa pau, no ka laʻana, i loko o 10 mau minuke. A i ʻole hiki ke hana i ka wā i hoʻopiha ʻia ka nui o ka ʻikepili.
A ma ka paʻamau, ua hoʻonohonoho ʻia ka max_wal_saze i 1 gigabyte. I ka ʻoiaʻiʻo, hana maoli kēia ma Postgres ma hope o 300-400 megabytes. Ua loli nui ʻoe i ka ʻikepili a loaʻa iā ʻoe kahi māka.
A inā ʻaʻohe mea i hoʻokani iā ia, akā ua ulu ka lawelawe, a loaʻa i ka ʻoihana ke kālā he nui, he nui kāna mau hana, a laila hiki ke nānā ʻia i hoʻokahi minuke, i kekahi manawa hoʻokahi i kēlā me kēia 30 kekona, a i kekahi manawa e uhi pū kekahi i kekahi. . He ino loa keia.
A pono mākou e hōʻoia e hele pinepine mai ia. ʻO ia hoʻi, hiki iā mākou ke hoʻonui i ka max_wal_size. A e hoʻouka pinepine ʻo ia.
Akā ua hoʻomohala mākou i kahi ʻano hana holoʻokoʻa e pili ana i ka hana ʻana i kēia me ka pololei, ʻo ia hoʻi, pehea e hoʻoholo ai e pili ana i ke koho ʻana i nā hoʻonohonoho, e pili ana i ka ʻikepili kikoʻī.
No laila, hana mākou i ʻelua pūʻulu hoʻokolohua waihona.
ʻO ka moʻo mua - hoʻololi mākou i ka max_wal_size. A ke hana nei mākou i kahi hana nui. ʻO ka mua mākou e hana ia ma ka hoʻonohonoho paʻamau o 1 gigabyte. A hana mākou i kahi DELETE nui o nā miliona o nā laina.
Hiki iā ʻoe ke ʻike i ka paʻakikī iā mākou. ʻIke mākou he ʻino loa ka disk IO. E ʻike kākou i ka nui o ka WAL a mākou i hana ai, no ka mea, he mea nui kēia. E ʻike kākou i ka nui o nā manawa i hiki mai ai ke kahua hoʻopaʻa. A ʻike mākou ʻaʻole maikaʻi.
A laila hoʻonui mākou i ka max_wal_size. Haʻi hou mākou. Hoʻonui, hana hou. A he nui na manawa. Ma ke kumu, maikaʻi nā helu 10, kahi 1, 2, 4, 8 gigabytes. A nānā mākou i ke ʻano o kahi ʻōnaehana kikoʻī. Ua maopopo ka pono o na lako maanei e like me ka prod. Pono ʻoe i nā disks like, ka nui o ka hoʻomanaʻo, a me nā hoʻonohonoho Postgres like.
A ma kēia ʻano e hoʻololi mākou i kā mākou ʻōnaehana, a ʻike mākou i ka hana ʻana o ka DBMS inā loaʻa kahi ʻino DELETE, pehea e nānā ai.
ʻO Checkpoint ma ka ʻōlelo Lūkini ʻo ia hoʻi nā wahi mana.
Laʻana: HELE i nā miliona mau lālani ma ka helu kuhikuhi, ua "puehu" nā lālani ma nā ʻaoʻao.
Eia kekahi laʻana. He kumu kēia. A me ka hoʻonohonoho paʻamau o 1 gigabyte no ka max_wal_size, maopopo loa ka hele ʻana o kā mākou mau disks no ka hoʻopaʻa ʻana i ka papa. ʻO kēia kiʻi he hōʻailona maʻamau o kahi maʻi maʻi nui, ʻo ia hoʻi, ua ʻino maoli ʻo ia. A hoʻokahi wale nō hana, eia wale nō ʻo DELETE o nā miliona mau laina.
Inā ʻae ʻia kēlā hana i ka prod, a laila e hāʻule wale mākou i lalo, no ka mea, ua maopopo ka pepehi ʻana o kekahi DELETE iā mākou ma ka papa.
Eia hou, ma kahi o 16 gigabytes, hiki iā ʻoe ke ʻike ua hoʻomaka mua nā niho. Ua ʻoi aku ka maikaʻi o nā niho, ʻo ia hoʻi, ke kīkē nei mākou i ke kaupaku, ʻaʻole naʻe ʻino loa. Ua ʻike ʻia kahi kūʻokoʻa liʻiliʻi ma laila. Aia ma ka ʻākau ka hoʻopaʻa ʻana. A ʻo ka helu o nā hana ka lua o ka pakuhi. A maopopo ke hanu maʻalahi mākou me 16 gigabytes.
A ma kahi e ʻike ʻia ai nā gigabytes 64, ua maikaʻi loa ia. Ua ʻike maopopo ʻia nā niho, ʻoi aku ka nui o nā manawa e ola ai i nā hana ʻē aʻe a hana i kekahi mea me ka disc.
No ke aha?
E luʻu liʻiliʻi wau i loko o nā kikoʻī, akā hiki i kēia kumuhana o ka hoʻokō ʻana i ka hoʻopaʻa ʻana i kahi hōʻoia i ka hōʻike holoʻokoʻa, no laila ʻaʻole wau e hele i nā kikoʻī nui loa, akā e wehewehe iki wau i nā pilikia i loaʻa.
Inā loaʻa pinepine ka mākaʻikaʻi, a ʻaʻole mākou e hōʻano hou i kā mākou mau lālani, akā e ʻike iā lākou ma ka index, maikaʻi ia, no ka mea ʻaʻole mākou e holoi i ka papaʻaina holoʻokoʻa, a laila hiki ke hoʻopā mua mākou i ka ʻaoʻao mua, a laila ka tausani, a laila hoʻi i ka mua. A inā ma waena o kēia mau kipa ʻana i ka ʻaoʻao mua ua hoʻopaʻa ʻia ka māka i ka disk, a laila e hoʻopakele hou ia, no ka mea ua haumia mākou i ka lua o ka manawa.
A e hoʻoikaika mākou i ka helu helu e mālama i nā manawa he nui. Me he mea la he mau hana hou no ia.
Akā ʻaʻole ʻo ia wale nō. Ma Postgres, he 8 kilobytes ke kaumaha o nā ʻaoʻao, a ma Linux he 4 kilobytes. A aia kahi hoʻonohonoho full_page_writes. Hoʻohana ʻia ma ka paʻamau. A pololei kēia, no ka mea, inā hoʻopau mākou, aia ka pilikia inā he hāʻule, ʻo ka hapalua wale nō o ka ʻaoʻao e mālama ʻia.
ʻO ke ʻano o ke komo ʻana i loko o ka WAL o ka log forward, ʻo ia ke loaʻa iā mākou kahi māka a hoʻololi mākou i kahi ʻaoʻao no ka manawa mua, ʻo ka ʻaoʻao holoʻokoʻa, ʻo ia hoʻi, nā 8 kilobytes a pau, e pau i ka log i mua, ʻoiai mākou wale nō. ua hoʻololi i kahi laina he 100 paita ke kaumaha. A koi ʻia mākou e kākau i ka ʻaoʻao holoʻokoʻa.
Ma nā hoʻololi aʻe e loaʻa wale kahi tuple kikoʻī, akā no ka manawa mua mākou e kākau i nā mea āpau.
A, no laila, inā e hoʻomaka hou ka ʻaoʻao, a laila pono mākou e hoʻomaka i nā mea āpau mai ka wā ʻōpala a hoʻopaʻa i ka ʻaoʻao holoʻokoʻa. Me nā mākaʻikaʻi pinepine, ke hele mākou ma nā ʻaoʻao like, ʻoi aku ka nui o ka full_page_writes = on ma mua o ka hiki, ʻo ia hoʻi, hoʻonui mākou i ka WAL. Hoʻouna ʻia nā mea hou aku i nā kope, i ka waihona, i ka disk.
A, no laila, loaʻa iā mākou ʻelua redundancies.
Inā mākou e hoʻonui i ka max_wal_size, ʻike ʻia e hoʻomaʻamaʻa mākou i ka hana ʻelua a me ka mea kākau wal. A maikaʻi kēlā.
E hoʻokomo i kahi terabyte a noho pū me ia. He aha ka hewa o ia mea? He ʻino kēia, no ka mea inā ʻaʻole hiki ke ala aʻe mākou no nā hola, no ka mea, ua lōʻihi ka manawa i hala a ua loli ka nui. A pono mākou e hana REDO no kēia mau mea a pau. A pēlā mākou e hana nei i ka lua o nā hoʻokolohua.
Hana mākou i ka hana a ʻike i ka wā kokoke i ka hoʻopau ʻana, hana mākou i kahi pepehi -9 Postgres ma ke kumu.
A ma hope o kēlā, hoʻomaka hou mākou, a ʻike i ka lōʻihi o ka piʻi ʻana o kēia mea hana, ʻo ia hoʻi, pehea ka nui o ka REDO e hana ai i kēia kūlana ʻino.
E ʻike ʻelua au i ka maikaʻi ʻole o ke kūlana. ʻO ka mea mua, hāʻule mākou ma mua pono o ka pau ʻana o ke kahua hoʻopaʻa, no laila, nui ka nalowale. A ʻo ka lua, ua hana nui mākou. A inā loaʻa ka manawa hoʻopaʻa ʻana, a laila, ʻoi aku ka liʻiliʻi o ka WAL i hana ʻia mai ka helu hope. ʻO ia hoʻi, he lua-loser kēia.
Ana mākou i kēia kūlana no nā max_wal_size ʻē aʻe a hoʻomaopopo inā ʻo ka max_wal_size he 64 gigabytes, a laila ma kahi kūlana ʻelua-ʻoi aku ka maikaʻi loa e ala mākou no 10 mau minuke. A manaʻo mākou inā kūpono kēia iā mākou a ʻaʻole paha. He pilikia ʻoihana kēia. Pono mākou e hōʻike i kēia kiʻi i ka poʻe kuleana no nā hoʻoholo ʻoihana a nīnau: "He aha ka manawa lōʻihi e hiki ai iā mākou ke kali i kahi pilikia? Hiki iā mākou ke moe i lalo i kahi kūlana maikaʻi loa no 3-5 mau minuke? A hoʻoholo ʻoe.
A eia kekahi wahi hoihoi. Loaʻa iā mākou kekahi mau hōʻike e pili ana iā Patroni ma ka ʻaha kūkā. A ke hoʻohana nei paha ʻoe. He autofailover kēia no Postgres. Ua kamaʻilio ʻo GitLab a me Data Egret e pili ana i kēia.
A inā loaʻa iā ʻoe kahi autofailover e hiki mai ana i 30 kekona, a laila hiki paha iā mākou ke moe i lalo no 10 mau minuke? No ka mea ma kēia wahi e hoʻololi mākou i ka replica, a maikaʻi nā mea a pau. He hihia hoʻopaʻapaʻa kēia. ʻAʻole maopopo iaʻu kahi pane maopopo. Manaʻo wau ʻaʻole pili wale kēia kumuhana i ka hoʻōla pōʻino.
Inā lōʻihi ka hoʻōla ʻana mai kahi hāʻule ʻole, a laila e hōʻoluʻolu ʻole mākou i nā kūlana ʻē aʻe. No ka laʻana, i nā hoʻokolohua like, ke hana mākou i kekahi mea a i kekahi manawa e kali no 10 mau minuke.
ʻAʻole naʻe au e hele lōʻihi loa, ʻoiai inā he autofailover kā mākou. Ma keʻano he kūlana, he mau waiwai maikaʻi nā waiwai e like me 64, 100 gigabytes. I kekahi manawa, pono ke koho liʻiliʻi. Ma keʻano laulā, he ʻepekema maʻalahi kēia.
I mea e hoʻololi ai, no ka laʻana, max_wal_size = 1, 8, pono ʻoe e hana hou i ka hana nui i nā manawa he nui. Ua hana ʻoe. A makemake ʻoe e hana hou ma ka waihona like, akā ua holoi ʻoe i nā mea āpau. He aha ka hana?
E haʻi aku wau iā ʻoe ma hope e pili ana i kā mākou hoʻonā a me nā mea a mākou e hana ai e hoʻololi i kēlā mau kūlana. A ʻo kēia ke ala pololei loa.
Akā ma kēia hihia ua laki mākou. Inā, e like me ka ʻōlelo ʻana ma aneʻi "BEGIN, DELETE, ROLLBACK", a laila hiki iā mākou ke hana hou i ka DELETE. ʻO ia hoʻi, inā mākou i kāpae iā mākou iho, a laila hiki iā mākou ke hana hou. A ma ke kino e loaʻa ana kāu ʻikepili. ʻAʻole loaʻa iā ʻoe kekahi bloat. Hiki iā ʻoe ke hoʻololi i kēlā DELETE.
He mea maikaʻi kēia DELETE me ROLLBACK no ka hoʻopaʻa ʻana i nā wahi kikoʻī, ʻoiai inā ʻaʻole ʻoe i hoʻonohonoho pono i nā labs database.
Hana mākou i hōʻailona me hoʻokahi kolamu "i". Loaʻa iā Postgres nā kolamu pono. ʻAʻole ʻike ʻia lākou ke ʻole i noi ʻia. ʻO kēia: ctid, xmid, xmax.
ʻO Ctid ka helu kino. ʻAoʻao ʻaʻohe, ka tuple mua ma ka ʻaoʻao.
Hiki ke ʻike ʻia ma hope o ROOLBACK ua noho ka tuple ma kahi hoʻokahi. ʻO ia, hiki iā mākou ke hoʻāʻo hou, e hana like ia. ʻO kēia ka mea nui.
ʻO Xmax ka manawa o ka make o ka tuple. Ua hoʻokomo ʻia, akā ʻike ʻo Postgres ua hoʻohuli ʻia kēia hana, no laila ʻaʻole ia he mea nui inā he 0 a i ʻole ka hoʻihoʻi ʻana. Hōʻike kēia hiki ke hoʻohana ʻia ʻo DELETE e hoʻomaʻamaʻa a hoʻāʻo i nā hana nui o ka ʻōnaehana ʻōnaehana. Hiki iā ʻoe ke hana i nā labs database no ka poʻe ʻilihune.
E pili ana kēia i nā mea polokalamu. E pili ana i ka DBA, ʻāhewa mau lākou i nā mea polokalamu no kēia: "No ke aha ʻoe e hana nei i nā hana lōʻihi a paʻakikī?" He kumuhana perpendicular ʻokoʻa loa kēia. Ma mua he hoʻoponopono, akā i kēia manawa e ulu ana.
ʻIke ʻia ʻaʻole mākou i wāwahi iā ia i ʻāpana. Ua maopopo. ʻAʻole hiki ke uhaʻi i kēlā DELETE no ka pūʻulu miliona o nā laina i mau ʻāpana. He 20 mau minuke e hana ai, a moe nā mea a pau. Akā, ʻo ka mea pōʻino, ʻo nā mea hoʻomohala ʻike e hana hewa, ʻoiai i nā ʻoihana nui loa.
No ke aha he mea nui ka uhaʻi?
-
Inā ʻike mākou he paʻakikī ka diski, a laila e hoʻolohi. A inā ua haki mākou, a laila hiki iā mākou ke hoʻohui i nā hoʻomaha, hiki iā mākou ke hoʻolohi i ka throttling.
-
A ʻaʻole mākou e pāpā i nā poʻe ʻē aʻe no ka lōʻihi. I kekahi mau hihia ʻaʻole ia he mea nui, inā ʻoe e wehe ana i ka ʻōpala maoli ʻaʻohe mea e hana nei, a laila ʻaʻole ʻoe e ālai i kekahi koe wale nō ka hana a autovacuum no ka mea e kali ia no ka hoʻopau ʻana o ke kālepa. Akā inā ʻoe e holoi i kahi mea a kekahi e noi ai, a laila e ālai ʻia lākou, aia kekahi ʻano o ke kaulahao. Pono e pale ʻia nā hana lōʻihi ma nā pūnaewele a me nā polokalamu kelepona.
He mea hoihoi kēia. ʻIke pinepine au i nā mea hoʻomohala e nīnau ana, "He aha ka nui o ka ʻeke e koho ai?"
ʻIke maopopo ʻia ʻo ka nui o ka nui o ka puʻupuʻu, ʻoi aku ka haʻahaʻa o ka hana ma luna o ke poʻo, ʻo ia hoʻi ka ʻoihana ʻoi aku ma luna. Akā i ka manawa like, piʻi ka manawa no kēia kālepa.
He lula maʻalahi kaʻu: e lawe i nā mea he nui e like me ka hiki, akā mai ʻoi aku ma mua o ka hoʻokō ʻana i kekona.
No ke aha kekona? He maʻalahi loa ka wehewehe ʻana a hiki ke hoʻomaopopo ʻia e nā poʻe a pau, ʻo ka poʻe ʻaʻole ʻenehana. ʻIke mākou i ka hopena. E lawe kākou i 50 milliseconds. Inā ua loli kekahi mea, e pane ko mākou maka. Inā liʻiliʻi, ʻoi aku ka paʻakikī. Inā pane mai kekahi mea ma hope o 100 milliseconds, no ka laʻana, kaomi ʻoe me ka ʻiole a pane mai ia iā ʻoe ma hope o 100 milliseconds, ua ʻike mua ʻoe i kēia lohi iki. Ua ʻike mua ʻia kahi lua me he brake.
No laila, inā mākou e puʻunaue i kā mākou mau hana nui i 10 mau kekona, a laila holo mākou i ka pilikia e pāpā mākou i kekahi. A e hana ia no kekahi mau kekona, a e ʻike mua nā kānaka. ʻO ia ke kumu i makemake ai au ʻaʻole e hana ia ma mua o hoʻokahi kekona. Akā i ka manawa like, mai hōʻoki i ka liʻiliʻi liʻiliʻi, no ka mea e ʻike ʻia ka ʻoihana ma luna. E ʻoi aku ke kaumaha o ke kumu, a hiki mai nā pilikia ʻē aʻe.
Koho mākou i ka nui o ka pā. I kēlā me kēia hihia hiki iā mākou ke hana i kēia ʻokoʻa. Hiki ke 'akomi. A maopopo mākou i ka maikaʻi o ka hana ʻana i hoʻokahi ʻeke. ʻO ia hoʻi, hoʻopau mākou i hoʻokahi ʻeke a i ʻole UPDATE.
Ma ke ala, ʻo nā mea a pau aʻu e haʻi aku nei iā ʻoe ʻaʻole wale no DELETE. E like me kāu i koho ai, he mau hana nui kēia ma ka ʻikepili.
A ʻike mākou he maikaʻi ka hoʻolālā. Hiki iā ʻoe ke ʻike i ka index scan, ʻoi aku ka maikaʻi o ka index scan wale nō. A loaʻa iā mākou kahi helu liʻiliʻi o ka ʻikepili. A hana nā mea a pau ma lalo o kekona. Super.
A pono mākou e hōʻoia ʻaʻole he degradation. Hiki ke hana koke ʻia nā pūʻulu mua, a laila ʻoi aku ka maikaʻi o nā mea a pau. ʻO ke kaʻina hana e pono ai ʻoe e hoʻāʻo nui. ʻO kēia ka mea e pono ai nā labs database.
A pono mākou e hoʻomākaukau i kahi mea e hiki ai iā mākou ke nānā pono i kēia i ka hana. No ka laʻana, hiki iā mākou ke kākau i ka manawa ma ka log, hiki iā mākou ke kākau i hea mākou i kēia manawa a me ka mea a mākou i holoi ai i kēia manawa. A ʻo kēia ka mea e hiki ai iā mākou ke hoʻomaopopo ma hope i ka mea e hana nei. A inā hewa kekahi mea, e ʻimi koke i ka pilikia.
Inā pono mākou e nānā i ka pono o nā nīnau a pono mākou e hoʻololi i nā manawa he nui, a laila aia kahi mea e like me ka hoa bot. Ua mākaukau ʻo ia. Hoʻohana ʻia ia e nā haneli o nā mea hoʻomohala i kēlā me kēia lā. A hiki iā ia ke hāʻawi i kahi waihona terabyte nui ma ke noi ʻana i 30 kekona, kāu kope ponoʻī. A hiki iā ʻoe ke holoi i kekahi mea ma laila a ʻōlelo ʻo RESET, a holoi hou. Hiki iā ʻoe ke hoʻokolohua me kēia ʻano. ʻIke au i ka wā e hiki mai ana i kēia mea. A ke hana nei mākou i kēia.
He aha nā hoʻolālā hoʻokaʻawale? ʻIke au i 3 mau hoʻolālā hoʻokaʻawale ʻokoʻa i hoʻohana ʻia e nā mea hoʻomohala ma ka pā.
He mea maʻalahi ka mea mua. Loaʻa iā mākou kahi ID helu. A e hoʻokaʻawale kākou i nā manawa like ʻole a hana pū me ia. Ua maopopo ka lalo. Ma ka mahele mua, he 100 laina o ka opala maoli, ma ka lua 5 laina a i ole ia, a i ole ia, lilo na laina 1 a pau i opala. ʻAʻohe hana like ʻole, akā maʻalahi ka haki. Lawe lākou i ka ID kiʻekiʻe loa a wāwahi. He ala naʻaupō kēia.
ʻO ka hoʻolālā ʻelua he ala kaulike. Hoʻohana ʻia ia ma Gitlab. Lawe mākou a nānā i ka papaʻaina. Ua loaʻa iā mākou nā palena o nā ʻeke ID i loaʻa i kēlā me kēia ʻeke nā moʻolelo 10 pololei. A hoʻopaʻa lākou iaʻu i kekahi ʻano laina. A hana hou mākou. Hiki iā ʻoe ke hana i kēia ma nā ʻāpana he nui.
Ma ka hoʻolālā mua, ma ke ala, hiki iā ʻoe ke hana i kēia ma nā ʻāpana he nui. ʻAʻole paʻakikī.
Akā aia kahi ala ʻoi aku ka maikaʻi a ʻoi aku ka maikaʻi. ʻO kēia ke kolu o ka hoʻolālā. A inā hiki, ʻoi aku ka maikaʻi o ke koho ʻana. Hana mākou i kēia ma muli o kahi kuhikuhi kikoʻī. I kēia hihia, ʻo ia paha ka helu kuhikuhi e pili ana i kā mākou kūlana ʻōpala a me ka ID. E hoʻokomo mākou i ka ID no laila he index scan wale nō i ʻole mākou e hele i ka puʻu.
ʻO ka maʻamau, ʻoi aku ka wikiwiki o ka scan index wale nō ma mua o ka scan index.
A ʻike koke mākou i kā mākou ID a mākou e makemake ai e holoi. Koho mākou i ka BATCH_SIZE ma mua. ʻAʻole mākou e loaʻa wale iā lākou, loaʻa iā mākou ma kahi ala kūikawā a hoʻoponopono koke iā lākou. Akā, hoʻopaʻa mākou iā lākou ma ke ʻano inā ua paʻa mua lākou, ʻaʻole mākou e laka iā lākou, akā e neʻe a lawe i nā mea aʻe. ʻO kēia no ka hoʻohou skip laka. ʻO kēia hiʻohiʻona nui Postgres hiki iā mākou ke hana i nā pae he nui inā makemake mākou. Malia paha i hoʻokahi pae. A laila aia ʻo CTE - hoʻokahi noi kēia. A loaʻa iā mākou ka wehe ʻana maoli ma ka papahele ʻelua o kēia CTE - returning *. Hiki iā ʻoe ke hoʻihoʻi i ka id, akā ʻoi aku ka maikaʻi *, inā he liʻiliʻi kāu ʻikepili i kēlā me kēia laina.
No ke aha mākou e pono ai i kēia? Pono mākou i kēia i mea e hōʻike ai. Ua holoi mākou i nā laina he nui i kēia manawa. A ʻo kā mākou mau palena ma ka ID a i ʻole create_at e like me kēia. Hiki iā ʻoe ke hana i ka min, max. Hiki ke hana i kekahi mea. Nui nā mea hiki iā ʻoe ke hoʻopuʻi ma ʻaneʻi. A he mea maʻalahi kēia no ka nānā ʻana.
Aia kekahi memo hou e pili ana i ka index. Inā hoʻoholo mākou e pono mākou i kahi kuhikuhi kūikawā no kēia hana kūikawā, a laila pono mākou e hōʻoia ʻaʻole ia e hoʻopōʻino i nā puʻupuʻu tuples wale nō. ʻO ia hoʻi, loaʻa iā Postgres nā helu helu. Hiki ke ʻike ʻia kēia ma nā pg_stat_user_tables no kāu pākaukau. Hiki iā ʻoe ke ʻike inā hoʻohana ʻia nā mea hou wela a i ʻole.
Aia kekahi mau manawa e ʻoki wale ai kāu papa kuhikuhi hou iā lākou. A e lohi nā mea hou a pau e holo nei. ʻAʻole wale no ka ʻike ʻia ʻana o ka papa kuhikuhi (e hoʻolōʻihi iki kēlā me kēia index i nā mea hou, akā liʻiliʻi wale nō), akā ma ʻaneʻi e hoʻopilikia mau i nā mea. A ʻaʻole hiki ke hana i ka optimization kūikawā no kēia pākaukau. Hana kēia i kekahi manawa. He mea maalea keia a kakaikahi ka poe i hoomanao. A he maʻalahi ke hehi i kēia rake. I kekahi manawa pono ʻoe e ʻimi i kahi ala mai kēlā ʻaoʻao a hana mau me ka ʻole o kēia kuhikuhi hou, a i ʻole e hana i kahi kuhikuhi hou, a i ʻole e hana i kahi mea ʻē aʻe, no ka laʻana, hiki iā ʻoe ke hoʻohana i ke ala ʻelua.
Akā ʻo kēia ka hoʻolālā maikaʻi loa, pehea e hoʻokaʻawale ai i nā pūʻulu a pana i nā pūʻulu me ka noi hoʻokahi, holoi liʻiliʻi i ka manawa, etc.
Nā hana lōʻihi -
Ua ālai ʻia ka autovacuum
Pilikia ālai
He hewa nui ka #5. Ua kamaʻilio ʻo Nikolay mai Okmeter e pili ana i ka nānā ʻana o Postgres. ʻO ka mea pōʻino, ʻaʻole i loaʻa ka nānā ʻana o Postgres. ʻOi aku kahi kokoke, ʻoi aku kekahi. Kokoke ʻo Okmeter e hemolele, akā nui nā mea i nalowale a pono e hoʻohui ʻia. Pono ʻoe e mākaukau no kēia.
No ka laʻana, ʻoi aku ka maikaʻi o ka nānā ʻana i nā tuple make. Inā he nui kāu mau mea make ma kāu papaʻaina, a laila ua hewa kekahi mea. ʻOi aku ka maikaʻi o ka pane ʻana i kēia manawa, inā ʻaʻole hiki ke hoʻohaʻahaʻa ʻia ma laila, a hiki iā mākou ke moe i lalo. Ia hana.
Inā he IO nui, a laila maopopo ʻaʻole maikaʻi kēia.
ʻO nā kālepa lōʻihi hoʻi. ʻAʻole ʻae ʻia nā hana lōʻihi ma OLTP. A eia kahi loulou i ka snippet, kahi e hiki ai iā ʻoe ke lawe i kēia snippet a hana mua i kekahi ʻimi ʻana i nā kālepa lōʻihi.
No ke aha i hewa ai nā kālepa lōʻihi? No ka mea, e hoʻokuʻu ʻia nā laka a pau ma ka hopena wale nō. A laka mākou i nā mea a pau. Hoʻopili mākou i ka autovacuum no nā papa āpau. ʻAʻole maikaʻi loa kēia. ʻOiai inā ua hoʻohana ʻia ka standby wela ma ka replica, maikaʻi ʻole kēia. Ma keʻano laulā, ʻoi aku ka maikaʻi o ka pale ʻana i nā kālepa lōʻihi ma nā wahi āpau.
Inā he nui nā papa ʻaʻole i hoʻomāmā ʻia, a laila pono mākou e makaʻala. Hiki ke kūlana maʻaneʻi. Hiki iā mākou ke hoʻopili pololei i ka hana o ka autovacuum. He snippet kēia mai Avito, aʻu i hoʻomaikaʻi iki. A ua lilo ia i mea hana hoihoi e ʻike i kā mākou mea me autovacuum. Eia kekahi laʻana, aia kekahi mau papaʻaina e kali ana ma laila aʻaʻole lākou e kali i ko lākou manawa. Pono ʻoe e hoʻokomo iā ia i ka nānā ʻana a loaʻa kahi makaʻala.
A hoʻopuka i nā poloka. Nahele lāʻau ālai. Makemake au e lawe i kekahi mea mai kekahi a hoʻomaikaʻi. Ma ʻaneʻi ua lawe au i kahi CTE recursive maikaʻi mai Data Egret, e hōʻike ana i kahi nahele o nā lāʻau laka. He mea maikaʻi kēia no ka diagnostics. A hiki ke kūkulu ʻia ka nānā ʻana ma luna o kāna kumu. Akā, pono e hana pono kēia. Pono ʻoe e hana i kahi ʻōlelo liʻiliʻi_timeout no ʻoe iho. A makemake ʻia ka lock_timeout.
I kekahi manawa, hui pū kēia mau hewa a pau.
I koʻu manaʻo, ʻo ka hewa nui loa ma ʻaneʻi ʻo ka hoʻonohonoho. He hoʻonohonoho ia, no ka mea, ʻaʻole hana ka ʻenehana. ʻO kēia ka helu 2 - ua nānā lākou ma kahi hewa.
ʻAʻole mākou i nānā ma laila no ka mea ʻaʻohe o mākou clone hana i maʻalahi ke nānā. ʻAʻole hiki i ka mea hoʻomohala ke komo i ka hana.
A ua nānā mākou ma kahi hewa. Inā lākou i nānā i laila, ʻike mākou iā mākou iho. Hiki i ka mea hoʻomohala ke ʻike i kēia mau mea a pau me ka ʻole o kahi DBA, inā ʻo ia i nānā iā ia ma kahi ʻano maikaʻi, kahi i loaʻa ai ka nui o ka ʻikepili a me kahi hoʻonohonoho like. Ua ʻike ʻo ia i kēia hoʻohaʻahaʻa a pau a hilahila.
Nā mea hou aku e pili ana i ke kaʻa kaʻa. Ma hope o kā mākou hana ʻana i kahi hoʻomaʻemaʻe nui o nā laina miliona, pono mākou e hana i kahi REPACK. He mea nui kēia no nā index. E ʻino lākou ma hope o kā mākou hoʻomaʻemaʻe ʻana i nā mea āpau ma laila.
A inā makemake ʻoe e hoʻihoʻi i ka hana o ka wehe ʻana i kēlā me kēia lā, a laila manaʻo wau e hana pinepine, akā liʻiliʻi. Hiki iā ia i hoʻokahi manawa i kēlā me kēia minuke a ʻoi aku paha i kahi manawa liʻiliʻi. A pono mākou e nānā i ʻelua mau mea: ʻaʻohe hewa o kēia mea a ʻaʻole ia e haʻalele. ʻO ka hoʻopunipuni aʻu i hōʻike ai e ʻae iā ʻoe e hoʻoponopono i kēia.
ʻO kā mākou hana he open source. Ua kau ʻia kēia ma GitLab. A hana mākou i hiki i nā kānaka ke nānā me ka ʻole o kahi DBA. Hana mākou i kahi kikowaena waihona, ʻo ia hoʻi, ke kāhea nei mākou i ka ʻāpana kumu a Joe e hana nei. A hiki iā ʻoe ke hopu i kahi kope o ka hana. I kēia manawa aia kahi hoʻokō o Joe no ka lohi, hiki iā ʻoe ke ʻōlelo ma laila: "wehewehe i kēlā me kēia noi" a loaʻa koke ka hopena no kāu kope o ka waihona. Hiki iā ʻoe ke hana DELETE ma laila, ʻaʻohe mea e ʻike.
E ʻōlelo mākou he 10 terabytes kāu, ke hana nei mākou i kahi keʻena waihona waihona me 10 terabytes. A hiki i nā mea hoʻomohala 10 ke hana like me ka ʻikepili 10 terabyte i ka manawa like. Hiki i nā kānaka a pau ke hana i kā lākou makemake. Hiki ke holoi, hāʻule, a pēlā aku. He mea kupanaha kēia. E kamaʻilio kākou no kēia ʻapōpō.
Kapa ʻia kēia ʻo ka hoʻolako lahilahi. He hoʻolako maʻalahi kēia. ʻO kēia kekahi ʻano noʻonoʻo e hoʻopau loa i ka lohi o ka hoʻomohala ʻana a me ka hoʻāʻo ʻana a hoʻolilo i ka honua i wahi maikaʻi loa ma kēia ʻano. ʻO ia, ʻae wale ia iā ʻoe e pale i nā pilikia me nā hana nui.
Laʻana: 5 terabyte waihona, loaʻa ke kope ma lalo o 30 kekona. A ʻaʻole pili i ka nui, ʻo ia hoʻi, ʻaʻole pili i ka nui o nā terabytes.
I kēia lā hiki iā ʻoe ke hele i a eli i loko o kā mākou mau mea hana. Hiki iā ʻoe ke kākau inoa a ʻike i ka mea i laila. Hiki iā ʻoe ke hoʻokomo i kēia bot nou iho. He manuahi. Kākau.
ʻO kāu mau nīnau
ʻO ka manawa pinepine i nā kūlana maoli, ʻike ʻia ka ʻikepili e waiho ʻia ma ka papa ʻaina ma mua o ka mea e pono e holoi ʻia. 'O ia ho'i, ma ia 'ano, 'oi aku ka ma'alahi o ka ho'okō 'ana i kēia ala, ke ma'alahi ka hana 'ana i kahi mea hou, e kope wale i ka 'ikepili e pono ai ma laila a kākau i ka papa kahiko. He mea maopopo e pono ana ʻoe i kahi polokalamu polokalamu no kēia manawa ke hoʻololi nei ʻoe. Pehea kēia ʻano hana?
He ala maikaʻi loa kēia a he hana maikaʻi loa. Ua like loa ia me ka hana a pg_repack, ua like loa ia me kāu e hana ai i ka wā i hana ai ka poʻe ID i 4 bytes. He nui nā papa hana i hana i kēia i nā makahiki i hala aku nei, a ua ulu nui nā papa, a pono lākou e hoʻololi i 8 bytes.
Paʻakikī loa kēia hana. Ua hana mākou. A pono ʻoe e makaʻala loa. Loaʻa nā laka, a pēlā aku. Akā ua hana ʻia. ʻO ia, ʻo ke ala maʻamau e hoʻohana i ka pg_repack. Hoʻolaha ʻoe i kēlā hōʻailona. A ma mua o kou hoʻomaka ʻana e hoʻopiha iā ia me ka ʻikepili snapshot, e haʻi pū ʻoe i hoʻokahi papa e hahai i nā loli āpau. Aia kekahi maalea ma laila ʻaʻole paha ʻoe e hahai i kekahi mau loli. Aia nā mea maʻalahi. A laila hoʻololi ʻoe, ʻōwili i nā hoʻololi. Aia kahi hoʻomaha pōkole ke hoʻopaʻa mākou i nā mea āpau, akā ma ka holoʻokoʻa ke hana ʻia nei.
Inā ʻoe e nānā iā pg_repack ma GitHub, a laila aia kahi hana e hoʻololi i ka ID mai int 4 a i int 8, a laila aia kahi manaʻo e hoʻohana i ka pg_repack iā ia iho. Hiki nō hoʻi kēia, akā he ʻano hana hacker kēia, akā e hana pū ia no kēia. Hiki iā ʻoe ke komo i loko o ke kumu hoʻohana i ka pg_repack a ʻōlelo ma laila: "ʻAʻole pono mākou i kēia ʻikepili," ʻo ia hoʻi, ke hoʻololi wale nei mākou i ka mea e pono ai. A laila hoʻololi wale ʻo ia a ʻo ia nō.
Me kēia ala, loaʻa iā mākou kahi kope ʻelua o ka papaʻaina, kahi i kuhikuhi ʻia ai ka ʻikepili a waiho ʻia me nā index nani.
Bloat ʻaʻole, he ala maikaʻi kēia. Akā ʻike wau aia nā hoʻāʻo e hoʻomohala i ka automation no kēia, ʻo ia hoʻi, e hana i kahi hopena honua. Hiki iaʻu ke hoʻolauna iā ʻoe i kēia automation. Ua kākau ʻia ma Python, mea maikaʻi.
He wahi liʻiliʻi wau mai ka honua MySQL, no laila ua hele mai au e hoʻolohe. A hoʻohana mākou i kēia ala.
Akā inā loaʻa iā mākou he 90%. Inā loaʻa iā mākou he 5%, a laila ʻaʻole maikaʻi loa ka hoʻohana ʻana.
Mahalo no ka hōʻike! Inā ʻaʻohe kumu e hana ai i kope piha o ka prod, aia kekahi algorithm a i ʻole ka hoʻohālikelike e helu ai i ka ukana a i ʻole ka nui?
He nīnau maikaʻi. I kēia manawa hiki iā mākou ke ʻike i nā ʻikepili multi-terabyte. ʻOiai ʻaʻole like ka lako lako, no ka laʻana, ka liʻiliʻi o ka hoʻomanaʻo, ka liʻiliʻi o ka processor a ʻaʻole like nā disks, ke hana nei mākou. Inā ʻaʻohe wahi, a laila pono ʻoe e noʻonoʻo. E noʻonoʻo wau a hiki i ka lā ʻapōpō, hele mai ʻoe, e kamaʻilio kāua, he nīnau maikaʻi kēia.
Mahalo no ka hōʻike! Ua hoʻomaka mua ʻoe e kamaʻilio e pili ana i ka ʻoiaʻiʻo aia kahi Postgres maikaʻi, nona nā palena like ʻole, akā ke ulu nei. A he koʻokoʻo kēia a pau. ʻAʻole kūʻē kēia mau mea i ka hoʻomohala ʻana o Postgres ponoʻī, kahi e ʻike ʻia ai kekahi ʻano DELETE deferent a i ʻole kekahi mea ʻē aʻe e mālama ai i kahi haʻahaʻa i ka mea a mākou e hoʻāʻo nei e uhi ma aneʻi me kekahi o kā mākou ʻano ʻano ʻē?
Inā ma SQL mākou i ʻōlelo e holoi a hoʻonui i nā moʻolelo he nui i hoʻokahi kālepa, a laila pehea e hiki ai i Postgres ke hāʻawi i kēia? Ua kaupalena kino mākou i nā hana. E hana ana mākou i kēia no ka manawa lōʻihi. A e laka mākou i kēia manawa, etc.
Ua hana like lākou me nā index.
Hiki iaʻu ke manaʻo e hiki ke hoʻopaʻa ʻia ka hoʻopaʻa ʻana like. I kekahi lā hiki mai kēia. Akā, ʻaʻole maopopo iaʻu ka nīnau.
ʻO ka nīnau: aia kahi vector hoʻomohala e hele pololei ma laila, a hele like me kāu ma ʻaneʻi? ʻO kēlā mau mea. ʻAʻole anei lākou e noʻonoʻo i ia mea?
Ua kamaʻilio wau e pili ana i nā loina hiki ke hoʻohana ʻia i kēia manawa. Aia kekahi bot , me kēia hiki iā ʻoe ke hana i ka hoʻopaʻa ʻana i nā wahi kikoʻī. E hiki mai anei kēia ma Postgres? ʻAʻole maopopo iaʻu, ʻaʻole i kūkākūkā ʻia kēia. Ua mamao loa mākou mai kēia. Akā aia nā kānaka ʻepekema e hana i nā ʻōnaehana hou. A hoʻokuʻu lākou iā mākou i loko o nā kuhikuhi kikoʻī. Aia nā mea hoʻomohala. No ka laʻana, hiki iā ʻoe ke nānā i ke kani kaʻa. Koho 'akomi ia i nā palena. Akā ʻaʻole ʻo ia e hana i ka hoʻopaʻa ʻana iā ʻoe. ʻO ia hoʻi, e koho ʻo ia no ka hana, shell buffer, etc.
A no ka hoʻopaʻa ʻana i ka helu kikoʻī hiki iā ʻoe ke hana i kēia mea: inā loaʻa iā ʻoe hoʻokahi kaukani puʻupuʻu a me nā ʻāpana like ʻole, nā mīkini virtual ʻē aʻe i ke ao, hiki iā ʻoe ke hoʻohana i kā mākou bot. e hana i ka automation. A e koho ʻokoʻa ʻia ka max_wal_size e like me kāu hoʻonohonoho ʻana. Akā i kēia manawa ʻaʻole kokoke kēia i ka noho ʻana i ka kernel, ʻaʻole naʻe.
Aloha awakea, Aloha Auinalā Ua kamaʻilio ʻoe e pili ana i ka pōʻino o nā kālepa lōʻihi. Ua ʻōlelo ʻoe ua ālai ʻia ka autovacuum inā pau ka holoi ʻana. Pehea ʻē aʻe e pōʻino ai kēia iā mākou? No ka mea, ke kamaʻilio nei mākou e pili ana i ka hoʻokuʻu ʻana i kahi ākea a hiki ke hoʻohana. He aha hou aʻe kā mākou e nalowale ai?
ʻAʻole paha ʻo Autovacuum ka pilikia nui ma aneʻi. A ʻo ka ʻoiaʻiʻo e hiki i kahi kālepa lōʻihi ke kāohi i nā hana ʻē aʻe he mea ʻoi aku ka pōʻino. Hiki iā ia ke hui a ʻaʻole paha. Inā hui ʻo ia, a laila, pilikia loa nā mea. A me ka autovacuum he pilikia nō hoʻi kēia. ʻElua mau pilikia me nā hana lōʻihi ma OLTP: nā laka a me ka autovacuum. A inā loaʻa iā ʻoe nā manaʻo hoʻokipa wela ma ka replica, a laila e hōʻea mai ka autovacuum blocking i ka haku, hiki mai ia mai ka replica. Akā ma ka liʻiliʻi ʻaʻole e loaʻa nā laka ma laila. A ma ʻaneʻi e loaʻa nā laka. Ke kamaʻilio nei mākou e pili ana i nā loli ʻikepili, no laila he wahi koʻikoʻi nā laka ma aneʻi. A inā e hoʻomau kēia no ka manawa lōʻihi, a laila ua hoʻopaʻa ʻia nā ʻoihana hou aʻe. Hiki iā lākou ke pahele i nā mea ʻē aʻe. A puka mai na laau laka. Hāʻawi wau i kahi loulou i ka snippet. A ʻoi aku ka wikiwiki o kēia pilikia ma mua o ka pilikia me ka autovacuum, hiki ke hōʻiliʻili wale.
Mahalo no ka hōʻike! Ua hoʻomaka ʻoe i kāu hōʻike ma ka ʻōlelo ʻana ua hoʻāʻo hewa ʻoe. Ua hoʻomau mākou i ko mākou manaʻo pono mākou e lawe i nā lako like, me ke kumu like. E ʻōlelo mākou ua hāʻawi mākou i ka mea hoʻomohala i kumu. A ua hoʻokō ʻo ia i ka noi. A me he mea lā e hana maikaʻi ana ʻo ia. Akā ʻaʻole ia e nānā i ke ola, akā ma ke ola, no ka laʻana, ʻo kā mākou ukana he 60-70%. A inā mākou e hoʻohana i kēia kani, ʻaʻole maikaʻi loa ia
He mea nui ka loaʻa ʻana o kahi loea ma kāu hui a me ka hoʻohana ʻana i nā loea DBA hiki ke wānana i ka mea e hiki mai ana ma lalo o ka ukana hope. Ke hele wale nei mākou i kā mākou hoʻololi maʻemaʻe, ʻike mākou i ke kiʻi. Akā ʻo kahi ala ʻoi aku ka holomua i ka wā i hana hou ai mākou i ka mea like, akā me ka ukana hana simulated. He ʻoluʻolu loa kēia. Pono mākou e ulu a hiki i kēia manawa. Ua oo. Ua nānā pono mākou i nā mea i loaʻa iā mākou a nānā pū i ka lawa ʻana o nā kumuwaiwai. He nīnau maikaʻi kēlā.
Ke hana nei mākou i kahi ʻōpala koho a loaʻa iā mākou, no ka laʻana, kahi hae holoi ʻia
ʻO kēia ka mea e hana maʻalahi ai ka autovacuum ma Postgres.
ʻAe, hana ʻo ia i kēlā?
ʻO Autovacuum ka ʻohi ʻōpala.
Alakaʻi!
Mahalo no ka hōʻike! Aia kahi koho e hoʻolālā koke i kahi waihona me ka hoʻokaʻawale ʻana i hiki ke hoʻoneʻe ʻia nā ʻōpala a pau mai ka papaʻaina nui ma kahi ʻaoʻao?
Ua loaʻa.
Hiki iā mākou ke pale iā mākou iho inā ua laka mākou i kahi papa ʻaʻole pono e hoʻohana ʻia?
Ua loaʻa. Akā, he nīnau moa a huamoa kēia. Inā ʻike mākou i nā mea e hiki mai ana i ka wā e hiki mai ana, a laila, ʻoiaʻiʻo, e hana mākou i nā mea maikaʻi a pau. Akā ke loli nei ka ʻoihana, ʻike ʻia nā kolamu hou a me nā noi hou. A laila - oops, makemake mākou e holoi. Akā he kūlana kūpono kēia; hiki i ke ola, akā ʻaʻole mau. Akā ma ka laulā he manaʻo maikaʻi. E ʻoki wale a ʻo ia nō.
Source: www.habr.com