Pagtipig usa ka sentimos sa daghang mga volume sa PostgreSQL

Nagpadayon sa hilisgutan sa pagrekord sa dagkong mga sapa sa datos nga gipatungha ni miaging artikulo bahin sa partitioning, niini atong tan-awon ang mga paagi nga imong mahimo pagpakunhod sa "pisikal" nga gidak-on sa gitipigan sa PostgreSQL, ug ang epekto niini sa performance sa server.

Atong hisgotan Mga setting sa TOAST ug pag-align sa datos. "Sa kasagaran," kini nga mga pamaagi dili makatipig sa daghang mga kapanguhaan, apan kung wala’y pagbag-o sa code sa aplikasyon.

Bisan pa, ang among kasinatian nahimo nga mabungahon kaayo bahin niini, tungod kay ang pagtipig sa hapit bisan unsang pag-monitor sa kinaiyahan niini kasagaran apend-on lang sa mga termino sa natala nga datos. Ug kung nahibulong ka kung giunsa nimo itudlo ang database sa pagsulat sa disk 200MB / s katunga sa daghan - palihug sa ilalum sa iring.

Gamay nga mga sekreto sa dagkong datos

Pinaagi sa profile sa trabaho among serbisyo, sila kanunay nga molupad ngadto kaniya gikan sa mga lungag mga pakete sa teksto.

Ug sukad VLSI complexkansang database nga among gibantayan usa ka multi-component nga produkto nga adunay komplikado nga istruktura sa datos, unya mga pangutana alang sa maximum performance nahimo nga ingon niini "multi-volume" nga adunay komplikado nga algorithmic logic. Mao nga ang gidaghanon sa matag indibidwal nga pananglitan sa usa ka hangyo o ang sangputanan nga plano sa pagpatuman sa log nga moabut kanamo nahimo nga "sa aberids" medyo dako.

Atong tan-awon ang istruktura sa usa sa mga lamesa diin atong gisulat ang "hilaw" nga datos - nga mao, ania ang orihinal nga teksto gikan sa log entry:

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

Usa ka tipikal nga ilhanan (na seksyon na, siyempre, mao nga kini mao ang usa ka seksyon template), diin ang labing importante nga butang mao ang teksto. Usahay medyo voluminous.

Hinumdomi nga ang "pisikal" nga gidak-on sa usa ka rekord sa usa ka PG dili mahimong mag-okupar sa labaw sa usa ka panid sa datos, apan ang "lohikal" nga gidak-on usa ka hingpit nga lahi nga butang. Aron magsulat ug volumetric nga kantidad (varchar/text/bytea) sa usa ka field, gamita TOAST nga teknolohiya:

Ang PostgreSQL naggamit sa usa ka piho nga gidak-on sa panid (kasagaran 8 KB), ug wala magtugot sa mga tuple nga mosangkad sa daghang mga panid. Busa, imposible nga direkta nga tipigan ang dako kaayo nga mga bili sa uma. Aron mabuntog kini nga limitasyon, ang dagkong mga kantidad sa field gi-compress ug/o gibahin sa daghang pisikal nga linya. Kini mahitabo nga wala mamatikdi sa tiggamit ug adunay gamay nga epekto sa kadaghanan sa code sa server. Kini nga pamaagi nailhan nga TOAST...

Sa tinuud, alang sa matag lamesa nga adunay "potensyal nga dako" nga mga uma, awtomatiko usa ka gipares nga lamesa nga adunay "paghiwa" gihimo matag "dako" nga rekord sa 2KB nga mga bahin:

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

Kana mao, kung kinahanglan naton magsulat usa ka hilo nga adunay "dako" nga kantidad data, unya ang tinuod nga pagrekord mahitabo dili lamang sa main table ug sa PK niini, kondili sa TOAST ug sa PK niini.

Pagkunhod sa impluwensya sa TOAST

Apan ang kadaghanan sa among mga rekord dili gihapon ingon kadako, kinahanglan nga mohaum sa 8KB - Unsaon nako pagdaginot og kwarta niini?..

Dinhi diin ang hiyas moabut sa atong tabang STORAGE sa kolum sa lamesa:

• GUSTO nagtugot sa compression ug bulag nga pagtipig. Kini standard nga kapilian para sa kadaghanan sa TOAST compliant data type. Kini una nga mosulay sa paghimo sa compression, dayon ibutang kini sa gawas sa lamesa kung ang laray dako pa kaayo.
• KAMOT nagtugot sa compression apan dili bulag nga pagtipig. (Sa tinuud, bulag nga pagtipig ang himuon gihapon alang sa ingon nga mga kolum, apan lamang isip usa ka katapusan nga paagi, kung wala nay laing paagi sa pagpamubu sa hilo aron kini mohaum sa panid.)

Sa tinuud, kini gyud ang kinahanglan naton para sa teksto - i-compress kini kutob sa mahimo, ug kung dili kini angay, ibutang kini sa TOAST. Mahimo kini direkta sa langaw, nga adunay usa ka mando:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Sa unsa nga paagi sa pagtimbang-timbang sa epekto

Tungod kay ang pagdagayday sa datos nagbag-o kada adlaw, dili nato itandi ang hingpit nga mga numero, apan sa relatibong termino gamay nga bahin Gisulat namo kini sa TOAST - mas maayo. Apan adunay usa ka kapeligrohan dinhi - ang mas dako nga "pisikal" nga gidaghanon sa matag indibidwal nga rekord, ang "mas lapad" nga indeks mahimo, tungod kay kinahanglan naton nga tabunan ang daghang mga panid sa datos.

Seksyon sa wala pa magbag-o:

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

Seksyon pagkahuman sa mga pagbag-o:

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

Sa pagkatinuod, kami nagsugod pagsulat sa TOAST 2 ka beses nga dili kaayo kanunay, nga nagdiskarga dili lamang sa disk, kondili usab sa CPU:

Akong timan-an nga kami usab nahimong mas gamay sa "pagbasa" sa disk, dili lamang sa "pagsulat" - tungod kay sa pagsal-ot sa usa ka rekord sa usa ka lamesa, kinahanglan usab nga "basahon" ang bahin sa kahoy sa matag indeks aron mahibal-an ang bahin niini. umaabot nga posisyon diha kanila.

Kinsa ang makapuyo nga maayo sa PostgreSQL 11

Pagkahuman sa pag-update sa PG11, nakahukom kami nga ipadayon ang "pag-tune" sa TOAST ug namatikdan nga sugod sa kini nga bersyon ang parameter mahimong magamit alang sa pag-tune toast_tuple_target:

Ang kodigo sa pagproseso sa TOAST modilaab lamang kon ang bili sa row nga itago sa lamesa mas dako kay sa TOAST_TUPLE_THRESHOLD bytes (kasagaran 2 KB). Ang TOAST code mag-compress ug/o mobalhin sa field values ​​gikan sa table hangtod ang row value mahimong ubos pa sa TOAST_TUPLE_TARGET bytes (variable value, kasagaran 2 KB) o ang gidak-on dili na mapakunhod.

Nakahukom kami nga ang datos nga kasagaran namong nabatonan mao ang "mubo kaayo" o "taas kaayo", mao nga nakahukom kami nga limitahan ang among kaugalingon sa labing gamay nga posible nga kantidad:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Atong tan-awon kung giunsa ang bag-ong mga setting nakaapekto sa pagkarga sa disk pagkahuman sa pag-configure:

Dili daotan! Average ang pila sa disk mikunhod gibana-bana nga 1.5 ka beses, ug ang disk "busy" mao ang 20 porsyento! Apan tingali kini sa usa ka paagi nakaapekto sa CPU?

Sa labing gamay wala kini mograbe. Bisan pa, lisud ang paghukom kung bisan ang ingon nga mga volume dili gihapon makapataas sa average nga load sa CPU nga mas taas 5%.

Pinaagi sa pag-usab sa mga dapit sa mga termino, ang sum... mausab!

Sama sa imong nahibal-an, ang usa ka sentimos makatipig usa ka ruble, ug sa among mga volume sa pagtipig kini bahin 10TB/bulan bisan gamay nga pag-optimize makahatag ug maayong ganansya. Busa, gihatagan namon ug pagtagad ang pisikal nga istruktura sa among datos - kung unsa gyud "stacked" nga mga natad sa sulod sa rekord matag usa sa mga lamesa.

Kay tungod sa pag-align sa datos kini diretso sa unahan makaapekto sa resulta nga gidaghanon:

Daghang arkitektura ang naghatag ug data alignment sa machine word boundaries. Pananglitan, sa usa ka 32-bit x86 nga sistema, ang mga integer (integer type, 4 bytes) ipahiangay sa 4-byte nga utlanan sa pulong, sama sa doble nga katukma nga floating point nga mga numero (doble nga katukma nga floating point, 8 bytes). Ug sa usa ka 64-bit nga sistema, doble nga kantidad ang ipahiangay sa 8-byte nga mga utlanan sa pulong. Kini mao ang lain nga rason sa incompatibility.

Tungod sa pag-align, ang gidak-on sa usa ka laray sa lamesa nagdepende sa han-ay sa mga uma. Kasagaran kini nga epekto dili kaayo mamatikdan, apan sa pipila ka mga kaso kini mahimong mosangpot sa usa ka mahinungdanon nga pagtaas sa gidak-on. Pananglitan, kung imong isagol ang char(1) ug integer nga mga natad, kasagaran adunay 3 ka byte nga mausik tali kanila.

Magsugod kita sa sintetikong mga modelo:

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

Diin gikan ang pipila ka dugang nga mga byte sa unang kaso? Simple ra - Ang 2-byte smallint gi-align sa 4-byte nga utlanan sa wala pa ang sunod nga uma, ug kung kini ang katapusan, wala’y bisan unsa ug dili kinahanglan nga i-align.

Sa teorya, maayo ang tanan ug mahimo nimong ihan-ay pag-usab ang mga natad kung gusto nimo. Atong susihon kini sa tinuud nga datos gamit ang panig-ingnan sa usa sa mga lamesa, ang adlaw-adlaw nga seksyon nga nag-okupar sa 10-15GB.

Inisyal nga istruktura:

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)

Seksyon human sa pagbag-o sa han-ay sa kolum - eksakto parehas nga mga natad, lahi ra nga pagkasunud:

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)

Ang kinatibuk-ang gidaghanon sa seksyon gitino sa gidaghanon sa "mga kamatuoran" ug nagdepende lamang sa mga eksternal nga proseso, busa atong bahinon ang gidak-on sa tapok (pg_relation_size) pinaagi sa gidaghanon sa mga rekord niini - nga mao, atong makuha average nga gidak-on sa aktuwal nga gitipigan nga rekord:

Minus 6% nga gidaghanon, Nindot!

Apan ang tanan, siyempre, dili kaayo rosy - pagkahuman, sa mga indeks dili nato mausab ang han-ay sa mga natad, ug busa “sa kinatibuk-an” (pg_total_relation_size) ...

...anhi man gihapon nakatipig 1.5%nga walay pag-usab sa usa ka linya sa code. Oo, oo!

Namatikdan nako nga ang kapilian sa ibabaw alang sa paghan-ay sa mga natad dili ang kamatuoran nga kini ang labing kamalaumon. Tungod kay dili nimo gusto nga "gisa" ang pipila ka mga bloke sa mga uma alang sa aesthetic nga mga hinungdan - pananglitan, usa ka magtiayon (pack, recno), nga mao ang PK alang niini nga lamesa.

Sa kinatibuk-an, ang pagtino sa "minimum" nga kahikayan sa mga uma usa ka yano nga "brute force" nga buluhaton. Busa, makakuha ka ug mas maayo nga mga resulta gikan sa imong datos kaysa sa amon - sulayi kini!

Source: www.habr.com