Ŝparu pencon sur grandaj volumoj en PostgreSQL

Daŭrigante la temon pri registrado de grandaj datumfluoj levitaj de antaŭa artikolo pri dispartigo, en ĉi tio ni rigardos la manierojn kiel vi povas redukti la "fizikan" grandecon de la stokita en PostgreSQL, kaj ilia efiko al servila efikeco.

Ni parolos pri TOAST-agordoj kaj datuma vicigo. "Averaĝe," ĉi tiuj metodoj ne ŝparos tro da rimedoj, sed tute sen modifi la aplikaĵokodon.

Tamen, nia sperto montriĝis tre produktiva ĉi-rilate, ĉar la konservado de preskaŭ ajna monitorado laŭ sia naturo estas plejparte nur almetas rilate al registritaj datumoj. Kaj se vi scivolas kiel vi povas instrui la datumbazon skribi al disko anstataŭe 200MB / s duono - bonvolu sub kato.

Malgrandaj sekretoj de grandaj datumoj

Laŭ laborprofilo nia servo, ili regule flugas al li el la kavernoj tekstaj pakoj.

Kaj poste VLSI-kompleksokies datumbazo ni kontrolas estas mult-komponenta produkto kun kompleksaj datumstrukturoj, tiam demandoj por maksimuma rendimento rezultas tute tiel "multvoluma" kun kompleksa algoritma logiko. Do la volumeno de ĉiu individua okazo de peto aŭ la rezulta ekzekutplano en la protokolo, kiu venas al ni, montriĝas "averaĝe" sufiĉe granda.

Ni rigardu la strukturon de unu el la tabeloj, en kiuj ni skribas "krudajn" datumojn - tio estas, jen la originala teksto de la protokolo:

CREATE TABLE rawdata_orig(
  pack -- PK
    uuid NOT NULL
, recno -- PK
    smallint NOT NULL
, dt -- ключ секции
    date
, data -- самое главное
    text
, PRIMARY KEY(pack, recno)
);

Tipa signo (jam sekciita, kompreneble, do jen sekcia ŝablono), kie la plej grava estas la teksto. Kelkfoje sufiĉe volumena.

Memoru, ke la "fizika" grandeco de unu rekordo en PG ne povas okupi pli ol unu paĝon da datumoj, sed la "logika" grandeco estas tute alia afero. Por skribi volumetran valoron (varchar/text/bytea) al kampo, uzu TOAST-teknologio:

PostgreSQL uzas fiksan paĝgrandecon (tipe 8 KB), kaj ne permesas al opoj etendi plurajn paĝojn. Tial, estas neeble rekte stoki tre grandajn kampovalorojn. Por venki ĉi tiun limigon, grandaj kampovaloroj estas kunpremitaj kaj/aŭ dividitaj tra pluraj fizikaj linioj. Ĉi tio okazas nerimarkite de la uzanto kaj havas malmulte da efiko al la plej multaj servilkodo. Ĉi tiu metodo estas konata kiel TOAST...

Fakte, por ĉiu tabelo kun "eble grandaj" kampoj, aŭtomate parigita tablo kun "tranĉado" estas kreita ĉiu "granda" rekordo en 2KB-segmentoj:

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

Tio estas, se ni devas skribi ĉenon kun "granda" valoro data, tiam la vera registrado okazos ne nur al la ĉefa tablo kaj ĝia PK, sed ankaŭ al TOAST kaj ĝia PK.

Reduktante TOAST-influon

Sed la plej multaj el niaj diskoj ankoraŭ ne estas tiom grandaj, devus konveni en 8KB - Kiel mi povas ŝpari monon pri tio?...

Jen kie la atributo venas al nia helpo STORAGE ĉe la tablokolumno:

• ETENDITA permesas kaj kunpremadon kaj apartan stokadon. Ĉi tio norma opcio por la plej multaj TOAST-konformaj datumtipoj. Ĝi unue provas elfari kunpremadon, tiam stokas ĝin ekster la tablo se la vico ankoraŭ estas tro granda.
• MANO permesas kunpremadon sed ne apartan stokadon. (Fakte, aparta stokado ankoraŭ estos farita por tiaj kolumnoj, sed nur kiel lasta rimedo, kiam ne ekzistas alia maniero ŝrumpi la ŝnuron tiel ke ĝi konvenu al la paĝo.)

Fakte, ĝuste tion ni bezonas por la teksto - kunpremu ĝin kiel eble plej multe, kaj se ĝi tute ne taŭgas, metu ĝin en TOAST. Ĉi tio povas esti farita rekte sur la muŝo, kun unu komando:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Kiel taksi la efikon

Ĉar la datumfluo ŝanĝiĝas ĉiutage, ni ne povas kompari absolutajn nombrojn, sed en relativaj terminoj pli malgranda parto Ni skribis ĝin en TOAST - des pli bone. Sed ĉi tie estas danĝero - ju pli granda la "fizika" volumo de ĉiu individua registro, des pli "larĝa" fariĝas la indekso, ĉar ni devas kovri pli da paĝoj da datumoj.

Sekcio antaŭ ŝanĝoj:

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

Sekcio post ŝanĝoj:

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

Fakte, ni komencis skribi al TOAST 2 fojojn malpli ofte, kiu malŝarĝis ne nur la diskon, sed ankaŭ la CPU:

Mi rimarkos, ke ni ankaŭ malpligrandiĝis en "legado" de la disko, ne nur "skribado" - ĉar enmetinte rekordon en tabelon, ni ankaŭ devas "legi" parton de la arbo de ĉiu indekso por determini ĝian. estonta pozicio en ili.

Kiu povas vivi bone sur PostgreSQL 11

Post ĝisdatigo al PG11, ni decidis daŭrigi "agordi" TOAST kaj rimarkis, ke ekde ĉi tiu versio la parametro toast_tuple_target:

La pretigkodo TOAST nur ekfunkciigas kiam la vicvaloro stokota en la tabelo estas pli granda ol TOAST_TUPLE_THRESHOLD bajtoj (kutime 2 KB). La TOAST-kodo kunpremos kaj/aŭ movos kampovalorojn el la tabelo ĝis la vicvaloro iĝos malpli ol TOAST_TUPLE_TARGET bajtoj (varia valoro, ankaŭ kutime 2 KB) aŭ la grandeco ne povas esti reduktita.

Ni decidis, ke la datumoj, kiujn ni kutime havas, estas aŭ "tre mallongaj" aŭ "tre longaj", do ni decidis limigi nin al la minimuma ebla valoro:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Ni vidu kiel la novaj agordoj influis diskŝarĝadon post reagordo:

Ne malbona! Averaĝa la vico al la disko malpliiĝis proksimume 1.5 fojojn, kaj la disko "okupata" estas 20 procentoj! Sed eble ĉi tio iel influis la CPU?

Almenaŭ ĝi ne plimalboniĝis. Kvankam, estas malfacile juĝi, ĉu eĉ tiaj volumoj ankoraŭ ne povas plialtigi la averaĝan CPU-ŝarĝon 5%.

Ŝanĝante la lokojn de la terminoj, la sumo... ŝanĝiĝas!

Kiel vi scias, penco ŝparas rublon, kaj kun niaj stokaj volumoj temas 10TB/monato eĉ iom da optimumigo povas doni bonan profiton. Tial ni atentis la fizikan strukturon de niaj datumoj - kiel ĝuste "stakitaj" kampoj ene de la rekordo ĉiu el la tabloj.

Ĉar pro datuma vicigo ĉi tio estas rekte antaŭen influas la rezultan volumon:

Multaj arkitekturoj disponigas datenparaleligon sur maŝinvortlimoj. Ekzemple, sur 32-bita x86 sistemo, entjeroj (entjera tipo, 4 bajtoj) estos vicigitaj sur 4-bajta vortlimo, kiel duobliĝos precizecaj glitkomaj nombroj (duobla precizeca glitkoma, 8 bajtoj). Kaj sur 64-bita sistemo, duoblaj valoroj estos vicigitaj al 8-bajtaj vortlimoj. Ĉi tio estas alia kialo de nekongruo.

Pro vicigo, la grandeco de tabelvico dependas de la ordo de la kampoj. Kutime ĉi tiu efiko ne estas tre rimarkebla, sed en iuj kazoj ĝi povas konduki al grava pliiĝo de grandeco. Ekzemple, se vi miksas char(1) kaj entjerajn kampojn, kutime estos 3 bajtoj malŝparitaj inter ili.

Ni komencu per sintezaj modeloj:

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 байт

De kie venis kelkaj kromaj bajtoj en la unua kazo? Estas simple - 2-bajta smallint vicigita sur 4-bajta limo antaŭ la sekva kampo, kaj kiam ĝi estas la lasta, estas nenio kaj ne necesas vicigi.

En teorio, ĉio estas en ordo kaj vi povas rearanĝi la kampojn laŭplaĉe. Ni kontrolu ĝin sur realaj datumoj uzante la ekzemplon de unu el la tabeloj, kies ĉiutaga sekcio okupas 10-15GB.

Komenca strukturo:

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)

Sekcio post ŝanĝado de kolumna ordo - ĝuste samaj kampoj, nur malsama ordo:

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)

La totala volumo de la sekcio estas determinita de la nombro da "faktoj" kaj dependas nur de eksteraj procezoj, do ni dividu la grandecon de la amaso (pg_relation_size) per la nombro da registroj en ĝi - tio estas, ni ricevas meza grandeco de reala stokita rekordo:

Minus 6% volumo, Bonege!

Sed ĉio, kompreneble, ne estas tiel rozkolora - finfine, en indeksoj ni ne povas ŝanĝi la ordon de kampoj, kaj tial "ĝenerale" (pg_total_relation_size) ...

...ankoraŭ ĉi tie ŝparis 1.5%sen ŝanĝi eĉ unu linion de kodo. Jes, jes!

Mi rimarkas, ke la supra opcio por aranĝi kampojn ne estas la fakto, ke ĝi estas la plej optimuma. Ĉar vi ne volas "ŝiri" iujn kampojn pro estetikaj kialoj - ekzemple, kelkaj (pack, recno), kiu estas la PK por ĉi tiu tabelo.

Ĝenerale, determini la "minimuman" aranĝon de kampoj estas sufiĉe simpla tasko de "brutforto". Tial vi povas akiri eĉ pli bonajn rezultojn de viaj datumoj ol la niaj - provu ĝin!

fonto: www.habr.com