ProHoster > Blogs > Administrācija > Ietaupiet santīmu, iegādājoties lielus apjomus programmā PostgreSQL

Ietaupiet santīmu, iegādājoties lielus apjomus programmā PostgreSQL

Turpinot tēmu par lielu datu plÅ«smu ierakstÄ«Å”anu, ko izvirzÄ«ja IepriekŔējais raksts par sadalÄ«Å”anu, mēs apskatÄ«sim veidus, kādos jÅ«s varat samaziniet glabājamā "fizisko" izmēru PostgreSQL un to ietekmi uz servera veiktspēju.

Mēs runāsim par TOAST iestatÄ«jumi un datu izlÄ«dzināŔana. ā€œVidējiā€ Ŕīs metodes neietaupÄ«s pārāk daudz resursu, taču nemaz nemainot lietojumprogrammas kodu.

Ietaupiet santīmu, iegādājoties lielus apjomus programmā PostgreSQL
Tomēr mÅ«su pieredze Å”ajā ziņā izrādÄ«jās ļoti produktÄ«va, jo gandrÄ«z jebkura monitoringa glabāŔana pēc savas bÅ«tÄ«bas ir pārsvarā tikai pievienot ierakstÄ«to datu ziņā. Un, ja jÅ«s domājat, kā jÅ«s varat iemācÄ«t datubāzei rakstÄ«t diskā 200MB / s uz pusi mazāk - lÅ«dzu zem kat.

Lielo datu mazie noslēpumi

Pēc darba profila mūsu pakalpojums, viņi regulāri lido pie viņa no lairiem teksta paketes.

Un kopÅ” tā laika VLSI kompleksskuras datu bāze mēs uzraugām, ir daudzkomponentu produkts ar sarežģītām datu struktÅ«rām, pēc tam vaicājumiem maksimālai veiktspējai izrādÄ«ties diezgan Ŕādi ā€œvairāku sējumuā€ ar sarežģītu algoritmisku loÄ£iku. Tātad katra atseviŔķa pieprasÄ«juma gadÄ«juma vai no tā izrietoŔā izpildes plāna apjoms mums nonākuÅ”ajā žurnālā izrādās ā€œvidējiā€ diezgan liels.

ApskatÄ«sim vienas tabulas struktÅ«ru, kurā mēs ierakstām ā€œneapstrādātusā€ datus - tas ir, Å”eit ir oriÄ£inālais teksts no žurnāla ieraksta:

CREATE TABLE rawdata_orig(
  pack -- PK
    uuid NOT NULL
, recno -- PK
    smallint NOT NULL
, dt -- ŠŗŠ»ŃŽŃ‡ сŠµŠŗцŠøŠø
    date
, data -- сŠ°Š¼Š¾Šµ Š³Š»Š°Š²Š½Š¾Šµ
    text
, PRIMARY KEY(pack, recno)
);

Tipiska zÄ«me (protams, jau sagriezta, tāpēc Ŕī ir sadaļas veidne), kur svarÄ«gākais ir teksts. Dažreiz diezgan apjomÄ«gi.

Atgādiniet, ka viena ieraksta ā€œfiziskaisā€ lielums PG nevar aizņemt vairāk par vienu datu lapu, bet ā€œloÄ£iskaisā€ izmērs ir pavisam cita lieta. Lai laukā ierakstÄ«tu tilpuma vērtÄ«bu (varchar/text/bytea), izmantojiet TOAST tehnoloÄ£ija:

PostgreSQL izmanto fiksētu lapas izmēru (parasti 8 KB) un neļauj kortežām aptvert vairākas lapas. Tāpēc nav iespējams tieÅ”i saglabāt ļoti lielas lauka vērtÄ«bas. Lai pārvarētu Å”o ierobežojumu, lielas lauka vērtÄ«bas tiek saspiestas un/vai sadalÄ«tas vairākās fiziskās lÄ«nijās. Tas notiek lietotājam nepamanÄ«ts, un tas maz ietekmē lielāko daļu servera koda. Å Ä« metode ir pazÄ«stama kā TOAST...

Faktiski katrai tabulai ar "potenciāli lieliem" laukiem automātiski tiek izveidota pāra tabula ar ā€œÅ”Ä·elÅ”anuā€. katrs ā€œlielaisā€ ieraksts 2KB segmentos:

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

Tas ir, ja mums ir jāraksta virkne ar ā€œlieluā€ vērtÄ«bu data, tad notiks Ä«stais ieraksts ne tikai uz galveno galdu un tā PK, bet arÄ« uz TOAST un tā PK.

TOAST ietekmes samazināŔana

Bet lielākā daļa mūsu ierakstu joprojām nav tik lieli, jāietilpst 8KB - Kā es varu ietaupīt naudu par to?...

Šeit mums palīdz atribūts STORAGE tabulas kolonnā:

  • PAPLAÅ INĀTS ļauj gan saspieÅ”anu, gan atseviŔķu uzglabāŔanu. Å is standarta opcija lielākajai daļai TOAST saderÄ«go datu veidu. Vispirms tas mēģina veikt saspieÅ”anu, pēc tam saglabā to ārpus tabulas, ja rinda joprojām ir pārāk liela.
  • GALVENĀ ļauj saspiest, bet ne atseviŔķu glabāŔanu. (PatiesÄ«bā Ŕādām kolonnām joprojām tiks veikta atseviŔķa glabāŔana, bet tikai kā pēdējo lÄ«dzekli, ja nav citas iespējas samazināt virkni, lai tā ietilptu lapā.)

Faktiski tas ir tieÅ”i tas, kas mums ir vajadzÄ«gs tekstam - saspiediet to pēc iespējas vairāk un, ja tas vispār neder, ielieciet to TOAST. To var izdarÄ«t tieÅ”i lidojuma laikā ar vienu komandu:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Kā novērtēt efektu

Tā kā datu plÅ«sma mainās katru dienu, mēs nevaram salÄ«dzināt absolÅ«tos skaitļus, bet gan relatÄ«vā izteiksmē mazāka daļa Mēs to pierakstÄ«jām TOAST ā€” jo labāk. Bet Å”eit pastāv briesmas - jo lielāks ir katra atseviŔķa ieraksta ā€œfiziskaisā€ apjoms, jo ā€œplaŔāksā€ kļūst rādÄ«tājs, jo mums ir jāaptver vairāk datu lapu.

Sadaļa pirms izmaiņām:

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

Sadaļa pēc izmaiņām:

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

Patiesībā mēs sāka rakstīt TOAST 2 reizes retāk, kas izlādēja ne tikai disku, bet arī centrālo procesoru:

Ietaupiet santīmu, iegādājoties lielus apjomus programmā PostgreSQL
Ietaupiet santīmu, iegādājoties lielus apjomus programmā PostgreSQL
AtzÄ«mÄ“Å”u, ka esam kļuvuÅ”i mazāki arÄ« diska ā€œlasÄ«Å”anāā€, ne tikai ā€œrakstÄ«Å”anāā€ - tā kā, ievietojot ierakstu tabulā, mums ir ā€œjāizlasaā€ arÄ« daļa no katra indeksa koka, lai noteiktu tā nākotnes pozÄ«cija tajās.

KurÅ” var labi dzÄ«vot, izmantojot PostgreSQL 11

Pēc atjaunināŔanas uz PG11, mēs nolēmām turpināt ā€œnoskaņotā€ TOAST un pamanÄ«jām, ka sākot ar Å”o versiju parametrs toast_tuple_target:

TOAST apstrādes kods tiek aktivizēts tikai tad, ja tabulā saglabājamās rindas vērtÄ«ba ir lielāka par TOAST_TUPLE_THRESHOLD baitiem (parasti 2 KB). TOAST kods saspiedÄ«s un/vai pārvietos lauka vērtÄ«bas no tabulas, lÄ«dz rindas vērtÄ«ba kļūs mazāka par TOAST_TUPLE_TARGET baitiem (mainÄ«ga vērtÄ«ba, arÄ« parasti 2 KB) vai arÄ« izmēru nevarēs samazināt.

Mēs nolēmām, ka mÅ«su rÄ«cÄ«bā esoÅ”ie dati parasti ir ā€œÄ¼oti Ä«siā€ vai ā€œÄ¼oti gariā€, tāpēc nolēmām aprobežoties ar minimālo iespējamo vērtÄ«bu:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

ApskatÄ«sim, kā jaunie iestatÄ«jumi ietekmēja diska ielādi pēc atkārtotas konfigurÄ“Å”anas:

Ietaupiet santīmu, iegādājoties lielus apjomus programmā PostgreSQL
Nav slikti! Vidēji rinda uz disku ir samazinājusies aptuveni 1.5 reizes, un disks ir ā€œaizņemtsā€ par 20 procentiem! Bet varbÅ«t tas kaut kā ietekmēja centrālo procesoru?

Ietaupiet santīmu, iegādājoties lielus apjomus programmā PostgreSQL
Vismaz sliktāk nekļuva. Lai gan ir grÅ«ti spriest, vai pat Ŕādi apjomi joprojām nevar paaugstināt vidējo CPU slodzi 5%.

Mainot terminu vietas, summa... mainās!

Kā jÅ«s zināt, penss ietaupa rubli, un ar mÅ«su uzglabāŔanas apjomiem tas ir aptuveni 10 TB/mēnesÄ« pat neliela optimizācija var dot labu peļņu. Tāpēc mēs pievērsām uzmanÄ«bu mÅ«su datu fiziskajai struktÅ«rai ā€“ kā tieÅ”i ā€œsakrautiā€ lauki ieraksta iekÅ”pusē katrā no tabulām.

Jo tāpēc datu saskaņoÅ”ana tas ir taisni uz priekÅ”u ietekmē iegÅ«to apjomu:

Daudzas arhitektÅ«ras nodroÅ”ina datu izlÄ«dzināŔanu uz maŔīnu vārdu robežām. Piemēram, 32 bitu x86 sistēmā veseli skaitļi (vesela skaitļa tips, 4 baiti) tiks lÄ«dzināti uz 4 baitu vārda robežas, kā arÄ« dubultās precizitātes peldoŔā komata skaitļi (dubultās precizitātes peldoŔā komata, 8 baiti). Un 64 bitu sistēmā dubultās vērtÄ«bas tiks saskaņotas ar 8 baitu vārdu robežām. Tas ir vēl viens nesaderÄ«bas iemesls.

LÄ«dzināŔanas dēļ tabulas rindas lielums ir atkarÄ«gs no lauku secÄ«bas. Parasti Å”is efekts nav Ä«paÅ”i pamanāms, bet dažos gadÄ«jumos tas var izraisÄ«t ievērojamu izmēru palielināŔanos. Piemēram, ja sajaucat char(1) un veselu skaitļu laukus, starp tiem parasti tiek iztērēti 3 baiti.

Sāksim ar sintētiskiem modeļiem:

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 Š±Š°Š¹Ń‚

No kurienes pirmajā gadÄ«jumā radās pāris papildu baiti? Tas ir vienkārÅ”i - 2 baitu mazā daļa ir izlÄ«dzināta uz 4 baitu robežas pirms nākamā lauka, un kad tas ir pēdējais, tad nav nekā un nav jālÄ«dzina.

Teorētiski viss ir kārtībā un var pārkārtot laukus kā gribi. Pārbaudīsim to uz reāliem datiem, izmantojot vienas tabulas piemēru, kuras ikdienas sadaļa aizņem 10-15 GB.

Sākotnējā struktūra:

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)

Sadaļa pēc kolonnu secÄ«bas maiņas - precÄ«zi tie paÅ”i lauki, tikai cita secÄ«ba:

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)

Kopējo sadaļas apjomu nosaka ā€œfaktuā€ skaits un tas ir atkarÄ«gs tikai no ārējiem procesiem, tāpēc sadalÄ«sim kaudzes lielumu (pg_relation_size) pēc tajā esoÅ”o ierakstu skaita - tas ir, mēs iegÅ«stam faktiski saglabātā ieraksta vidējais lielums:

Ietaupiet santīmu, iegādājoties lielus apjomus programmā PostgreSQL
MÄ«nus 6% tilpuma, Lieliski!

Bet viss, protams, nav tik rožaini - galu galā, indeksos mēs nevaram mainÄ«t lauku secÄ«buun tāpēc ā€œvispārā€ (pg_total_relation_size) ...

Ietaupiet santīmu, iegādājoties lielus apjomus programmā PostgreSQL
...joprojām arÄ« Å”eit ietaupÄ«ti 1.5%nemainot nevienu koda rindiņu. Jā jā!

Ietaupiet santīmu, iegādājoties lielus apjomus programmā PostgreSQL

Es atzÄ«mēju, ka iepriekÅ” minētā lauku sakārtoÅ”anas iespēja nav tā, ka tā ir optimālākā. Tāpēc, ka estētisku apsvērumu dēļ nevēlaties ā€œnoplēstā€ dažus lauku blokus - piemēram, pāris (pack, recno), kas ir Ŕīs tabulas PK.

Kopumā lauku ā€œminimālāā€ izvietojuma noteikÅ”ana ir diezgan vienkārÅ”s ā€œbrutāla spēkaā€ uzdevums. Tāpēc no saviem datiem varat iegÅ«t vēl labākus rezultātus nekā mÅ«sējie ā€“ izmēģiniet!

Avots: www.habr.com

Pievieno komentāru