Säästame sentti suurte mahtude puhul PostgreSQL-is

Jätkates teemat suurte andmevoogude salvestamisest, mis tõstatati eelnevas artiklis jaotamisest, vaatame selles, kuidas vähendada PostgreSQL-is salvestatava "füüsilise" suuruse ja nende mõju serveri jõudlusele.

Räägime TOAST seadistustest ja andmete joondamisest.. "Keskelt" need meetodid võimaldavad säästa mitte liiga palju ressursse, kuid täiesti ilma rakenduskoodi modifitseerimata.

Kuid meie kogemus osutus selles osas üsna produktiivseks, kuna peaaegu igasugune jälgimine on olemuselt enamasti append-only salvestatavate andmete osas. Ja kui teid huvitab, kuidas õpetada andmebaasi kirjutama kettale 200MB/s pool vähem - palun lugege edasi.

Väikesed saladused suurtest andmetest

Meie teenuse profiil, sellele saadetakse regulaarselt logidest teksti paketid..

Ja kuna SBIS-i kompleks, mille andmebaase me jälgime, on mitmekomponentne toode keeruliste andmestruktuuridega, siis tekivad ka päringud maksimaalse jõudluse saavutamiseks üpris selliseid "multivihikutes" keeruka algoritmilise loogikaga.Nii et iga eraldi päringu või tulemuse täitmise plaani maht, mis meile logisse jõuab, osutub "keskmiselt" piisavalt suureks.

Vaadakem ühte tabeli struktuuri, kuhu kirjutame "toored" andmed - see tähendab algset tekstiväli logikirjest:

CREATE TABLE rawdata_orig(
  pack -- PK
    uuid NOT NULL
, recno -- PK
    smallint NOT NULL
, dt -- sektori võti
    date
, data -- kõige olulisem
    text
, PRIMARY KEY(pack, recno)
);

Tüüpiline selline tabel (juba jaotatud, loomulikult, seetõttu on see - sektsiooni mall), kus kõige olulisem on tekst. Aeg-ajalt piisavalt mahukas.

Küll aga meenutame, et PG-s ühe salvestuse "füüsiline" suurus ei saa olla suurem kui üks andmeleht, kuid "loogiline" suurus - see on hoopis teine asi. Mahuka väärtuse (varchar/text/bytea) salvestamiseks kasutatakse TOAST tehnoloogiat.:

PostgreSQL kasutab fikseeritud lehekülje suurust (tavaliselt 8 KB) ja ei luba ridadel hõivata mitut lehte. Seetõttu ei ole väga suurte väljade väärtuste otsene salvestamine võimalik. Selle piirangu ületamiseks tihendatakse ja/või jagatakse suured väljade väärtused mitmeks füüsiliseks reale. See toimub kasutaja jaoks märkamatult ning enamikule serveri koodist on mõju minimaalne. Seda meetodit tuntakse kui TOAST...

Tegelikult luuakse iga tabeli jaoks, millel on "potentsiaalselt suured" väljad, automaatselt paaristabel "tükkide" jaoks iga "suur" kirje segmentideks 2KB:

TOAST(
  chunk_id
    integer
, chunk_seq
    integer
, chunk_data
    bytea
, PRIMARY KEY(chunk_id, chunk_seq)
);

Seega, kui peame salvestama rea, millel on "suur" väärtus data, siis toimub tegelik salvestamine mitte ainult põhitaotlusse ja selle PK-sse, vaid ka TOAST-i ja selle PK-sse.

Vähendame TOAST-i mõju

Kuid enamik meie kirjetest ei ole siiski nii suured, peaksid 8KB mahtuma — kuidas selle pealt säästa?..

Siin tuleb meile appi atribuuti STORAGE tabeli veerus:

• EXTENDED lubab nii tihendamist kui ka eraldi salvestamist. See on standardne variant enamikule TOAST-iga ühilduvatele andmetüüpidele. Esiteks proovitakse teha tihendamine, seejärel salvestatakse välja tabelist, kui rida on endiselt liiga suur.
• MAIN lubab tihendamist, kuid mitte eraldi salvestamist. (Tegelikult toimub eraldi salvestamine, kuid ainult viimase abinõuna, kui muud võimalust ei ole, et rida piisavalt väikeseks teha, et see lehte mahtuks.)

Tegelikult on see täpselt see, mida me vajame tekstile — maksimaalselt tihendada ja kui miski ei mahu — viia TOAST-i. Seda on võimalik teha otse "lendu", ühe käsuga:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Kuidas hinnata mõju

Kuna iga päev andmevoog muutub, ei saa me võrrelda absoluutarve, kuid suhtelist: mida väiksem osa oleme TOAST-i salvestanud — seda parem. Kuid siin on oht — mida suurem on meie "füüsiline" maht iga üksiku kirje puhul, seda "laiemaks" muutub indeks, kuna tuleb katta rohkem andmelehti.

Jaotis enne muudatusi:

heap  = 37GB (39%)
TOAST = 54GB (57%)
PK    =  4GB ( 4%)

Jaotis pärast muudatusi:

heap  = 37GB (67%)
TOAST = 16GB (29%)
PK    =  2GB ( 4%)

Tegelikult oleme hakkanud TOAST-i kirjutama 2 korda harvem, mis vähendas mitte ainult kettaruumihaldust, vaid ka CPU-koormust:

Tahan märkida, et me hakkasime ketast vähem "lugema", mitte ainult "kirjutama" — kuna kirje lisamisel mõnesse tabelisse tuleb "lugeda" ka osa iga indeksi puust, et määrata selle tulevane positsioon indeksites.

Kellele meeldib PostgreSQL 11-s elada

Pärast PG11-le üleminekut otsustasime jätkata TOAST-i "tuningut" ja tähelepanu juhtida sellele, et alates sellest versioonist on saanud seadistada parameetrit toast_tuple_target:

TOAST-i töötlemise kood käivitub ainult siis, kui tabelis salvestatav stringi väärtus ületab TOAST_TUPLE_THRESHOLD baitides (tavaliselt 2 kB). TOAST-i kood hakkab tihendama ja / või viima välja andmevälja tabelist, kuni stringi väärtus on väiksem kui TOAST_TUPLE_TARGET baitides (muutuv väärtus, samuti tavaliselt 2 kB) või kuni väiksemaks muutmine ei ole enam võimalik.

Otsustasime, et meie andmed on tavaliselt kas "ülimalt lühikesed" või "väga pikad", seega otsustasime jääda minimaalselt võimalikule väärtusele:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Vaatame, kuidas uued seaded on mõjutanud kettaruumi pärast ümberseadmisi:

Pole paha! Keskmine kettajuhtimise ooteaeg vähenes umbes 1,5 korda ja ketta "kasutatus" — 20% võrra! Kuid võib-olla see kuidagi mõjutas CPU-d?

Igal juhul ei saanud hullemaks. Kuigi, keeruline on hinnata, kui isegi sellised mahud ei suuda keskmist CPU-koormust tõsta üle 5%.

Kohtade vahetamisega muutub summa…!

Nagu teada, kaitseb ühe kopika abiga rubla, ja meie salvestusmahud, mis on umbes 10TB/kuus suudavad isegi väike optimeerimine pakkuda head kasu. Seetõttu pöörasime tähelepanu oma andmete füüsilisele struktuurile — kuidas konkreetselt "paigutatud" väljad igas tabeli kirjes. Kuna andmete "joondamine"

mõjutab otseselt saavutatud mahtu. Paljud arhitektuurid eeldavad andmete joondamist masinale vastavatesse piirteesse. Näiteks 32-bitises x86 süsteemis on täisarvud (tüüp integer, mille suurus on 4 baiti) joondatud 4-baitsete sõnade piiridesse, nagu ka topelt täpsusega ujukomaarvud (tüüp double precision, 8 baiti). Ja 64-bitises süsteemis on topeltväärtused joondatud 8-baitsete sõnade piiridesse. See on veel üks põhjus ühilduvuse probleemidele. mõjub lõppmahule:

Paljud arhitektuurid näevad ette andmete joondamist masinasõnade piiride järgi. Näiteks 32-bitises x86 süsteemis joondatakse täisarvud (tüüp integer, 4 bait) 4-baitiste sõnade piirile, samuti ka kahekordse täpsusega ujuvpunktarvud (tüüp double precision, 8 bait). 64-bitises süsteemis joondatakse double väärtused 8-baitiste sõnade piirile. See on veel üks mittesobivuse põhjus.

Ridade tõttu sõltub tabeli rida tulpa järjekorrast. Tavaliselt ei ole see efekt liiga silmatorkav, kuid mõnel juhul võib see põhjustada olulist suuruse suurenemist. Näiteks, kui segada char(1) ja integer tüüpe, siis nende vahel kaob tavaliselt 3 baiti.

Alustame sünteetiliste mudelitega:

SELECT pg_column_size(ROW(
  '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
, '2019-01-01'::date
));
-- 48 baiti

SELECT pg_column_size(ROW(
  '2019-01-01'::date
, '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
));
-- 46 baiti

Kust ilmus esimeses näites paar liigset baiti? Kõik on lihtne — 2-baidine smallint joondatakse 4-baidise piiri järgi enne järgmist välja, kuid kui see on viimane, siis ei ole joondamiseks midagi.

Teoorias on kõik hästi ja välju saab paigutada meelevaldselt. Kontrollime reaalseid andmeid ühe tabeli näitel, mille päevase sektsiooni maht on 10–15GB.

Algne struktuur:

CREATE TABLE public.plan_20190220
(
-- Pärandatud tabelist plan:  pack uuid NOT NULL,
-- Pärandatud tabelist plan:  recno smallint NOT NULL,
-- Pärandatud tabelist plan:  host uuid,
-- Pärandatud tabelist plan:  ts timestamp with time zone,
-- Pärandatud tabelist plan:  exectime numeric(32,3),
-- Pärandatud tabelist plan:  duration numeric(32,3),
-- Pärandatud tabelist plan:  bufint bigint,
-- Pärandatud tabelist plan:  bufmem bigint,
-- Pärandatud tabelist plan:  bufdsk bigint,
-- Pärandatud tabelist plan:  apn uuid,
-- Pärandatud tabelist plan:  ptr uuid,
-- Pärandatud tabelist 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)

Sektsioon pärast veergude järjekorra muutmist — täpselt samad väljad, ainult järjekord on erinev:

CREATE TABLE public.plan_20190221
(
-- Pärandatud tabelist plan:  dt date NOT NULL,
-- Pärandatud tabelist plan:  ts timestamp with time zone,
-- Pärandatud tabelist plan:  pack uuid NOT NULL,
-- Pärandatud tabelist plan:  recno smallint NOT NULL,
-- Pärandatud tabelist plan:  host uuid,
-- Pärandatud tabelist plan:  apn uuid,
-- Pärandatud tabelist plan:  ptr uuid,
-- Pärandatud tabelist plan:  bufint bigint,
-- Pärandatud tabelist plan:  bufmem bigint,
-- Pärandatud tabelist plan:  bufdsk bigint,
-- Pärandatud tabelist plan:  exectime numeric(32,3),
-- Pärandatud tabelist 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)

Sektsiooni kogumaht määratakse «faktide» arvu järgi ja sõltub ainult välistest protsessidest, seega jagame heap'i suuruse (pg_relation_sizekandmisel on arvestatud kirjeid — seega saame keskmise suurusega salvestatud kirje:

Miinus 6% mahust, suurepärane!

Kuid kõik pole siiski nii roosiline — sest indeksites ei saa me väljade järjekorda muuta, seega "üldiselt" (pg_total_relation_size)…

... siiski ka siin säästsime 1,5%, muutes koodis ainsatki rida. Nii see on!

Tahan märkida, et eespool toodud väljade paigutus ei ole kindlasti kõige optimaalsem. Kuna mõned väljade plokid ei taha „katkestada“ juba esteetilistel põhjustel — näiteks paar (pack, recno), mis on selle tabeli PK.

Üldiselt on „minimaalse“ väljade paigutuse määramine piisavalt lihtne „katsetamise“ ülesanne. Seega saate oma andmetega saavutada isegi paremaid tulemusi kui meil — proovige!

Allikas: habr.com