Jatkaen aihetta suurten tietovirtojen tallentamisesta , tässä tarkastelemme tapoja, joilla voit pienentää tallennettujen "fyysistä" kokoa PostgreSQL:ssä ja niiden vaikutus palvelimen suorituskykyyn.
Puhumme asiasta TOAST-asetukset ja tietojen kohdistus. "Keskimäärin" nämä menetelmät eivät säästä liikaa resursseja, mutta muuttamatta sovelluskoodia ollenkaan.
Kokemuksemme osoittautui kuitenkin tältä osin erittäin tuottoisiksi, sillä lähes minkä tahansa valvonnan tallennus luonteeltaan on enimmäkseen vain liite tallennettujen tietojen suhteen. Ja jos mietit, kuinka voit opettaa tietokannan kirjoittamaan sen sijaan levylle 200MB / s puolet niin paljon - kiitos alle cat.
Big datan pienet salaisuudet
Työprofiilin mukaan , he lentävät säännöllisesti hänen luokseen pesistä tekstipaketteja.
Ja siitä lähtien jonka tietokanta valvomme on monikomponenttinen tuote, jossa on monimutkaiset tietorakenteet, sitten kyselyt maksimaalisen suorituskyvyn saavuttamiseksi osoittautua aivan näin . Joten jokaisen yksittäisen pyynnön esiintymän määrä tai tuloksena oleva toteutussuunnitelma meille saapuvassa lokissa osoittautuu "keskimäärin" melko suureksi.
Katsotaanpa yhden taulukon rakennetta, johon kirjoitamme "raaka" dataa - eli tässä on alkuperäinen teksti lokimerkinnästä:
CREATE TABLE rawdata_orig(
pack -- PK
uuid NOT NULL
, recno -- PK
smallint NOT NULL
, dt -- ключ секции
date
, data -- самое главное
text
, PRIMARY KEY(pack, recno)
);Tyypillinen kyltti (jo tietysti jaettu, joten tämä on osiomalli), jossa tärkeintä on teksti. Joskus melko laaja.
Muista, että yhden tietueen "fyysinen" koko PG:ssä ei voi viedä enempää kuin yhden sivun dataa, mutta "looginen" koko on täysin eri asia. Voit kirjoittaa tilavuusarvon (varchar/text/bytea) kenttään käyttämällä :
PostgreSQL käyttää kiinteää sivukokoa (yleensä 8 kilotavua), eikä se salli monikkojen ulottuvan useille sivuille. Siksi on mahdotonta tallentaa suoraan erittäin suuria kenttäarvoja. Tämän rajoituksen voittamiseksi suuret kenttäarvot pakataan ja/tai jaetaan useille fyysisille riveille. Tämä tapahtuu käyttäjän huomaamatta ja sillä on vain vähän vaikutusta useimpiin palvelinkoodeihin. Tämä menetelmä tunnetaan nimellä TOAST...
Itse asiassa jokaiseen taulukkoon, jossa on "mahdollisesti suuret" kentät, automaattisesti jokainen "suuri" tietue 2 kt:n osissa:
TOAST(
chunk_id
integer
, chunk_seq
integer
, chunk_data
bytea
, PRIMARY KEY(chunk_id, chunk_seq)
);
Eli jos meidän on kirjoitettava merkkijono, jolla on "suuri" arvo data, silloin tapahtuu todellinen tallennus ei vain pääpöytään ja sen PK:hen, vaan myös TOASTiin ja sen PK:hen.
Vähentää TOAST-vaikutusta
Mutta suurin osa levyistämme ei ole vieläkään niin suuria, pitäisi mahtua 8 kilotavuun - Miten voin säästää rahaa tässä?
Tässä ominaisuus tulee avuksemme taulukon sarakkeessa:
- LAAJENNETTU mahdollistaa sekä pakkaamisen että erillisen tallennuksen. Tämä vakiovaihtoehto useimmille TOAST-yhteensopiville tietotyypeille. Se yrittää ensin suorittaa pakkausta ja tallentaa sen sitten taulukon ulkopuolelle, jos rivi on edelleen liian suuri.
- KESKEISET mahdollistaa pakkaamisen, mutta ei erillistä tallennusta. (Itse asiassa tällaisille sarakkeille suoritetaan edelleen erillinen tallennus, mutta vain viimeisenä keinona, kun ei ole muuta tapaa kutistaa merkkijonoa niin, että se mahtuu sivulle.)
Itse asiassa tämä on juuri sitä, mitä tarvitsemme tekstiin - purista se niin paljon kuin mahdollista, ja jos se ei sovi ollenkaan, laita se TOASTiin. Tämä voidaan tehdä suoraan lennossa yhdellä komennolla:
ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;Kuinka arvioida vaikutus
Koska tietovirta muuttuu päivittäin, emme voi verrata absoluuttisia lukuja, vaan suhteellisesti pienempi osuus Kirjoitimme sen ylös TOASTiin - sen parempi. Mutta tässä on vaara - mitä suurempi kunkin yksittäisen tietueen "fyysinen" tilavuus on, sitä "leveämmäksi" indeksi tulee, koska meidän on katettava enemmän sivuja tietoja.
Jakso ennen muutoksia:
heap = 37GB (39%)
TOAST = 54GB (57%)
PK = 4GB ( 4%)
Jakso muutosten jälkeen:
heap = 37GB (67%)
TOAST = 16GB (29%)
PK = 2GB ( 4%)Itse asiassa me alkoi kirjoittaa TOASTille 2 kertaa harvemmin, joka tyhjensi paitsi levyn myös prosessorin:
Huomautan, että olemme myös pienentyneet levyn "lukemisessa" ei vain "kirjoittamisessa" - koska kun lisäämme tietuetta taulukkoon, meidän on myös "luettava" osa kunkin indeksin puusta määrittääksemme sen tuleva asema heissä.
Kuka voi elää hyvin PostgreSQL 11:ssä
PG11:een päivittämisen jälkeen päätimme jatkaa TOASTin "viritystä" ja huomasimme, että tästä versiosta alkaen parametri tuli viritettäväksi :
TOAST-käsittelykoodi käynnistyy vain, kun taulukkoon tallennettava riviarvo on suurempi kuin TOAST_TUPLE_THRESHOLD tavua (yleensä 2 kt). TOAST-koodi pakkaa ja/tai siirtää kenttäarvoja pois taulukosta, kunnes rivin arvo on pienempi kuin TOAST_TUPLE_TARGET tavua (muuttuva arvo, myös yleensä 2 kt) tai kokoa ei voida pienentää.
Päätimme, että meillä yleensä olevat tiedot ovat joko "erittäin lyhyitä" tai "erittäin pitkiä", joten päätimme rajoittua mahdollisimman pieniin arvoihin:
ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);Katsotaanpa, kuinka uudet asetukset vaikuttivat levyn lataamiseen uudelleenmäärityksen jälkeen:
Ei paha! Keskiverto jono levylle on pienentynyt noin 1.5 kertaa, ja levy "varattu" on 20 prosenttia! Mutta ehkä tämä jotenkin vaikutti prosessoriin?
Ei se ainakaan huonommaksi mennyt. On kuitenkin vaikea arvioida, pystyvätkö sellaisetkin volyymit nostamaan keskimääräistä suorittimen kuormitusta korkeammalle 5%.
Ehtojen paikkoja muuttamalla summa... muuttuu!
Kuten tiedät, penni säästää ruplan, ja varastomäärämme ansiosta se on noin 10TB/kk pienikin optimointi voi tuottaa hyvän tuoton. Siksi kiinnitimme huomiota tietojemme fyysiseen rakenteeseen - kuinka tarkalleen "pinotut" kentät tietueen sisällä jokaisesta pöydästä.
Koska sen takia tämä on suoraan eteenpäin :
Monet arkkitehtuurit tarjoavat tietojen kohdistuksen koneen sanarajoilla. Esimerkiksi 32-bittisessä x86-järjestelmässä kokonaisluvut (kokonaislukutyyppi, 4 tavua) tasataan 4-tavuisen sanan rajalla, samoin kuin kaksinkertaiset liukulukuluvut (kaksinkertainen tarkkuus liukuluku, 8 tavua). Ja 64-bittisessä järjestelmässä kaksinkertaiset arvot tasataan 8-tavuisten sanarajojen mukaan. Tämä on toinen syy yhteensopimattomuuteen.
Tasauksen vuoksi taulukon rivin koko riippuu kenttien järjestyksestä. Yleensä tämä vaikutus ei ole kovin havaittavissa, mutta joissakin tapauksissa se voi johtaa merkittävään koon kasvuun. Jos esimerkiksi sekoitat char(1)- ja kokonaislukukenttiä, niiden välillä menee yleensä 3 tavua hukkaan.
Aloitetaan synteettisistä malleista:
SELECT pg_column_size(ROW(
'0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
, '2019-01-01'::date
));
-- 48 байт
SELECT pg_column_size(ROW(
'2019-01-01'::date
, '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
));
-- 46 байтMistä pari ylimääräistä tavua tuli ensimmäisessä tapauksessa? Se on yksinkertaista - 2-tavuinen smallint tasataan 4-tavun rajalla ennen seuraavaa kenttää ja kun se on viimeinen, ei ole mitään eikä tarvetta kohdistaa.
Teoriassa kaikki on hyvin ja voit järjestää kentät uudelleen haluamallasi tavalla. Tarkastetaan se todellisilla tiedoilla yhden taulukon esimerkin avulla, jonka päivittäinen osa vie 10-15 Gt.
Alkuperäinen rakenne:
CREATE TABLE public.plan_20190220
(
-- Унаследована from table plan: pack uuid NOT NULL,
-- Унаследована from table plan: recno smallint NOT NULL,
-- Унаследована from table plan: host uuid,
-- Унаследована from table plan: ts timestamp with time zone,
-- Унаследована from table plan: exectime numeric(32,3),
-- Унаследована from table plan: duration numeric(32,3),
-- Унаследована from table plan: bufint bigint,
-- Унаследована from table plan: bufmem bigint,
-- Унаследована from table plan: bufdsk bigint,
-- Унаследована from table plan: apn uuid,
-- Унаследована from table plan: ptr uuid,
-- Унаследована from table plan: dt date,
CONSTRAINT plan_20190220_pkey PRIMARY KEY (pack, recno),
CONSTRAINT chck_ptr CHECK (ptr IS NOT NULL),
CONSTRAINT plan_20190220_dt_check CHECK (dt = '2019-02-20'::date)
)
INHERITS (public.plan)Osio sarakejärjestyksen muuttamisen jälkeen - täsmälleen samat kentät, vain eri järjestys:
CREATE TABLE public.plan_20190221
(
-- Унаследована from table plan: dt date NOT NULL,
-- Унаследована from table plan: ts timestamp with time zone,
-- Унаследована from table plan: pack uuid NOT NULL,
-- Унаследована from table plan: recno smallint NOT NULL,
-- Унаследована from table plan: host uuid,
-- Унаследована from table plan: apn uuid,
-- Унаследована from table plan: ptr uuid,
-- Унаследована from table plan: bufint bigint,
-- Унаследована from table plan: bufmem bigint,
-- Унаследована from table plan: bufdsk bigint,
-- Унаследована from table plan: exectime numeric(32,3),
-- Унаследована from table plan: duration numeric(32,3),
CONSTRAINT plan_20190221_pkey PRIMARY KEY (pack, recno),
CONSTRAINT chck_ptr CHECK (ptr IS NOT NULL),
CONSTRAINT plan_20190221_dt_check CHECK (dt = '2019-02-21'::date)
)
INHERITS (public.plan)
Osion kokonaistilavuus määräytyy "faktien" lukumäärän mukaan ja riippuu vain ulkoisista prosesseista, joten jaetaan kasan koko (pg_relation_size) siinä olevien tietueiden lukumäärällä - eli saamme todellisen tallennetun tietueen keskimääräinen koko:
Miinus 6 % tilavuudesta, Loistava!
Mutta kaikki ei tietenkään ole niin ruusuista - loppujen lopuksi hakemistoissa emme voi muuttaa kenttien järjestystäja siksi "yleisesti" (pg_total_relation_size) ...
... vielä täälläkin säästetty 1.5 %muuttamatta yhtäkään koodiriviä. Kyllä kyllä!
Huomaan, että yllä oleva vaihtoehto kenttien järjestämiseksi ei ole se tosiasia, että se on optimaalisin. Koska et halua "revitä" joitain kenttälohkoja esteettisistä syistä - esimerkiksi paria (pack, recno), joka on tämän taulukon PK.
Yleensä kenttien "minimijärjestelyn" määrittäminen on melko yksinkertainen "raaka voima" -tehtävä. Siksi voit saada tiedoistasi jopa parempia tuloksia kuin meidän - kokeile!
Lähde: will.com