🥇Timalembera ku PostgreSQL pamunsi: 1 khamu, tsiku limodzi, 1TB

Posachedwapa ndinakuuzani momwe, pogwiritsa ntchito maphikidwe ovomerezeka onjezerani magwiridwe antchito a mafunso owerengera a SQL kuchokera ku database ya PostgreSQL. Lero tikambirana momwe kujambula kungathe kuchitidwa bwino kwambiri m'nkhokwe popanda kugwiritsa ntchito "zopotoza" zilizonse mu config - mwa kungolinganiza bwino kayendedwe ka deta.

#1. Kugawa

Nkhani yokhudza momwe ndi chifukwa chake kuli koyenera kukonza kugwiritsa ntchito kugawa "mwa chiphunzitso" zakhalapo kale, apa tikambirana mchitidwe wogwiritsa ntchito njira zina mkati mwathu ntchito yowunikira mazana a maseva a PostgreSQL.

"Zinthu zakale zapita ..."

Poyambirira, monga MVP iliyonse, polojekiti yathu inayamba pansi pa katundu wopepuka - kuyang'anira kunkachitika kwa ma seva khumi ovuta kwambiri, matebulo onse anali ochepa kwambiri ... , ndipo kamodzinso tinayesa kuchita chinachake ndi mmodzi wa matebulo 1.5TB kukula, tinazindikira kuti ngakhale kuti n’zotheka kupitiriza kukhala ndi moyo wotero, zinali zovuta kwambiri.

Nthawizo zinali ngati nthawi zamatsenga, mitundu yosiyanasiyana ya PostgreSQL 9.x inali yofunika, kotero magawo onse amayenera kuchitidwa "pamanja" - kupyolera mu cholowa cha tebulo ndi zoyambitsa mayendedwe ndi dynamic EXECUTE.

Zotsatira zake zidakhala zapadziko lonse lapansi kotero kuti zitha kumasuliridwa kumatebulo onse:

Gome la kholo la "mutu" wopanda kanthu linalengezedwa, lomwe limafotokoza zonse ma index ndi zoyambitsa zofunika.
Zolemba kuchokera kumalingaliro a kasitomala zidapangidwa patebulo la "mizu", ndikugwiritsa ntchito mkati choyambitsa njira BEFORE INSERT mbiriyo inali "mwakuthupi" yoyikidwa mu gawo lofunikira. Ngati panalibe chinthu choterocho, tidachita chosiyana ndi ...
… pogwiritsa ntchito CREATE TABLE ... (LIKE ... INCLUDING ...) idapangidwa kutengera template ya tebulo la makolo gawo lomwe lili ndi chiletso pa tsiku lomwe mukufunakotero kuti data ikabwezedwa, kuwerenga kumangochitika momwemo.

PG10: kuyesa koyamba

Koma kugawa kudzera mu cholowa sikunakhale koyenera kuthana ndi zolembera zokhazikika kapena kuchuluka kwa magawo a ana. Mwachitsanzo, mutha kukumbukira kuti algorithm yosankha gawo lofunikira anali nayo quadratic zovuta, kuti imagwira ntchito ndi magawo 100+, inu nokha mukumvetsa momwe ...

Mu PG10 izi zidakonzedwa bwino pogwiritsa ntchito chithandizo kugawa kwawoko. Chifukwa chake, tidayesa kugwiritsa ntchito nthawi yomweyo titasamuka kosungirako, koma ...

Monga momwe zidakhalira pambuyo pokumba bukuli, tebulo logawika mwachilengedwe mumtunduwu ndi:

sichigwirizana ndi mafotokozedwe a index
sichithandizira zoyambitsa pa izo
sangakhale “mbadwa” ya aliyense
osachirikiza INSERT ... ON CONFLICT
sangathe kupanga gawo lokha

Titalandira kugunda kowawa pamphumi ndi chowotcha, tinazindikira kuti sizingatheke kuchita popanda kusintha ntchito, ndipo tidaimitsa kufufuza kwina kwa miyezi isanu ndi umodzi.

PG10: mwayi wachiwiri

Chifukwa chake, tidayamba kuthana ndi mavuto omwe adabuka m'modzim'modzi:

Chifukwa zoyambitsa ndi ON CONFLICT Tinapeza kuti timafunikirabe apa ndi apo, choncho tinapanga siteji yapakatikati kuti tikwaniritse tebulo la proxy.
Yatsani "routing" mu zoyambitsa - ndiko kuti, kuchokera EXECUTE.
Anazitulutsa padera tebulo la template ndi ma index onsekotero kuti sapezekanso pa tebulo la proxy.

Pomaliza, zitatha zonsezi, tinagawa tebulo lalikulu natively. Kupanga gawo latsopano kumasiyidwabe ku chikumbumtima cha wogwiritsa ntchitoyo.

"Sawing" mtanthauzira mawu

Monga m'dongosolo lililonse la analytics, tinalinso nazo "zoona" ndi "mabala" (madikishonale). Kwa ife, mu udindo uwu iwo anachita, mwachitsanzo, thupi la template mafunso ofanana pang'onopang'ono kapena mawu afunso lomwe.

"Zowona" zidagawika masana kwa nthawi yayitali, kotero tidachotsa mwakachetechete magawo akale, ndipo sanativutitse (mipika!). Koma panali vuto ndi dictionaries...

Osati kunena kuti anali ambiri a iwo, koma pafupifupi 100TB ya "zowona" idabweretsa dikishonale ya 2.5TB. Simungathe kuchotsa chilichonse patebulo loterolo, simungathe kufinya nthawi yokwanira, ndikulembera pang'onopang'ono.

Monga dikishonale... mmenemo, cholembedwa chilichonse chiyenera kuperekedwa kamodzi ndendende... ndipo izi ndi zolondola, koma!.. Palibe amene akutiletsa kukhala nazo. dikishonale yapadera ya tsiku lililonse! Inde, izi zimabweretsa kuperewera kwina, koma zimalola:

lembani/werengani mwachangu chifukwa cha kukula kwa gawo laling'ono
amadya kukumbukira pang'ono pogwira ntchito ndi ma index ang'onoang'ono
sungani deta yochepa chifukwa cha kuthekera kuchotsa msanga zakale

Chifukwa cha zovuta zonse za miyeso Kulemera kwa CPU kunatsika ndi ~ 30%, disk katundu ndi ~ 50%:

Panthawi imodzimodziyo, tinapitiriza kulemba zomwezo mu database, ndi katundu wochepa.

#2. Kusintha kwa Database ndi refactoring

Kotero ife tinakhazikika pa zomwe tiri nazo tsiku lililonse lili ndi gawo lake ndi data. Kwenikweni, CHECK (dt = '2018-10-12'::date) - ndipo pali fungulo logawanitsa ndi chikhalidwe kuti zolemba zigwere mu gawo linalake.

Popeza malipoti onse muutumiki wathu amapangidwa malinga ndi tsiku lenileni, ma index awo kuyambira "nthawi zosagawika" akhala amitundu yonse. (Seva, Tsiku, Plan template), (Seva, Tsiku, Mapulani node), (Tsiku, Gulu lolakwika, Seva)...

Koma tsopano akukhala m’gawo lililonse makope anu cholozera chilichonse chotere ... Ndipo mkati mwa gawo lililonse tsiku ndi lokhazikika... Zikuoneka kuti tsopano ife tiri mu ndondomeko iliyonse yotereyi ingolowetsani mokhazikika monga imodzi mwa minda, yomwe imawonjezera voliyumu yake komanso nthawi yofufuzira, koma sizibweretsa zotsatira. Adazisiyira okha, oops...

Mayendedwe a kukhathamiritsa ndi zoonekeratu - zosavuta chotsani gawo la deti kuchokera pazolozera zonse pa matebulo ogawa. Kutengera ma voliyumu athu, phindu lili pafupi 1TB / sabata!

Tsopano tiyeni tizindikire kuti terabyte iyi idayenera kujambulidwa mwanjira ina. Ndiko kuti, ifenso diski iyenera tsopano kutsitsa pang'ono! Chithunzichi chikuwonetsa bwino zotsatira zomwe timalandira kuchokera kuyeretsa, komwe tidapereka sabata imodzi:

#3. "Kufalitsa" katundu wapamwamba

Imodzi mwazovuta zazikulu zamakina odzaza ndi kulunzanitsa kosafunikira ntchito zina zomwe sizikufuna. Nthawi zina "chifukwa sanazindikire", nthawi zina "zinali zophweka mwanjira imeneyo", koma posakhalitsa muyenera kuzichotsa.

Tiyeni tiwone chithunzi chapitachi ndikuwona kuti tili ndi disk "mapampu" pansi pa katundu ndi matalikidwe awiri pakati pa zitsanzo zoyandikana, zomwe momveka bwino "zowerengera" siziyenera kuchitika ndi kuchuluka kwa ntchito:

Izi ndizosavuta kukwaniritsa. Tayamba kale kuyang'anira pafupifupi 1000 ma seva, iliyonse imakonzedwa ndi ulusi womveka wosiyana, ndipo ulusi uliwonse umakhazikitsanso zambiri zomwe zasonkhanitsidwa kuti zitumizidwe ku database pafupipafupi, monga chonchi:

setInterval(sendToDB, interval)

Vuto pano lagona ndendende kuti mitundu yonse imayamba pafupifupi nthawi yomweyo, kotero kuti nthaŵi zawo zotumiza pafupifupi nthaŵi zonse zimagwirizana “pa mfundo yake.” Uwu #2...

Mwamwayi, izi ndizosavuta kukonza, kuwonjezera "mwachisawawa" kuthamanga pa nthawi:

setInterval(sendToDB, interval * (1 + 0.1 * (Math.random() - 0.5)))

#4. Timasunga zomwe tikufuna

Lachitatu chikhalidwe highload vuto ndi palibe posungira kumene iye ali akhoza kukhala.

Mwachitsanzo, tidapanga zotheka kusanthula malinga ndi mapulani apulani (zonsezi Seq Scan on users), koma nthawi yomweyo amaganiza kuti iwo ali, makamaka, ofanana - anaiwala.

Ayi, ndithudi, palibe chomwe chalembedwa ku database kachiwiri, izi zimadula choyambitsa nacho INSERT ... ON CONFLICT DO NOTHING. Koma deta iyi imafikabe ku database, ndipo sizofunikira kuwerenga kuti muwone ngati pali kusamvana ndiyenera kuchita. Uwu #3...

Kusiyana kwa kuchuluka kwa ma rekodi omwe amatumizidwa ku nkhokwe isanayambike / itatha caching yayatsidwa ndizodziwikiratu:

Ndipo uku ndi kutsika kotsatira kwa katundu wosungira:

Chiwerengero

"Terabyte-pa-tsiku" zimangomveka zoopsa. Ngati muchita zonse bwino, ndiye kuti izi ndi zolondola 2 ^ 40 mabayiti / 86400 masekondi = ~ 12.5MB/skuti ngakhale zomangira za IDE za desktop zidagwira. 🙂

Koma mozama, ngakhale ndi "skew" yolemetsa masana, mutha kukumana ndi kuthekera kwa ma SSD amakono.

Source: www.habr.com

Timalemba mu PostgreSQL pamunsi: 1 host, tsiku limodzi, 1TB