Ang pagpapatuloy ng paksa ng pagtatala ng malalaking data stream na itinaas ni , dito titingnan natin ang mga paraan kung paano mo magagawa bawasan ang "pisikal" na laki ng nakaimbak sa PostgreSQL, at ang epekto nito sa pagganap ng server.
Pag-uusapan natin Mga setting ng TOAST at pag-align ng data. "Sa karaniwan," ang mga pamamaraang ito ay hindi magse-save ng napakaraming mapagkukunan, ngunit nang hindi binabago ang code ng aplikasyon.
Gayunpaman, ang aming karanasan ay naging napaka-produktibo sa bagay na ito, dahil ang pag-iimbak ng halos anumang pagsubaybay sa likas na katangian nito karamihan ay nakadugtong-lamang sa mga tuntunin ng naitala na data. At kung ikaw ay nagtataka kung paano mo matuturuan ang database na magsulat sa disk sa halip 200MB / s kalahati ng mas maraming - mangyaring sa ilalim ng pusa.
Maliit na lihim ng malaking data
Sa pamamagitan ng profile ng trabaho , regular silang lumilipad sa kanya mula sa mga lungga mga pakete ng teksto.
At mula noon na ang database na aming sinusubaybayan ay isang multi-component na produkto na may mga kumplikadong istruktura ng data, pagkatapos ay mga query para sa maximum na pagganap maging ganito . Kaya't ang dami ng bawat indibidwal na halimbawa ng isang kahilingan o ang nagresultang plano sa pagpapatupad sa log na dumarating sa amin ay lumalabas na "sa karaniwan" ay medyo malaki.
Tingnan natin ang istraktura ng isa sa mga talahanayan kung saan isinusulat namin ang "raw" na data - iyon ay, narito ang orihinal na teksto mula 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)
);Isang tipikal na palatandaan (na-sectioned na, siyempre, kaya ito ay isang template ng seksyon), kung saan ang pinakamahalagang bagay ay ang teksto. Minsan medyo voluminous.
Alalahanin na ang "pisikal" na laki ng isang tala sa isang PG ay hindi maaaring sumakop ng higit sa isang pahina ng data, ngunit ang "lohikal" na laki ay isang ganap na naiibang bagay. Para magsulat ng volumetric na value (varchar/text/bytea) sa isang field, gamitin :
Gumagamit ang PostgreSQL ng nakapirming laki ng pahina (karaniwang 8 KB), at hindi pinapayagan ang mga tuple na sumasaklaw sa maraming pahina. Samakatuwid, imposibleng direktang mag-imbak ng napakalaking mga halaga ng field. Upang malampasan ang limitasyong ito, ang mga malalaking halaga ng field ay na-compress at/o hatiin sa maraming pisikal na linya. Nangyayari ito nang hindi napapansin ng user at may maliit na epekto sa karamihan ng server code. Ang pamamaraang ito ay kilala bilang TOAST...
Sa katunayan, para sa bawat talahanayan na may "potensyal na malalaking" field, awtomatiko bawat "malaking" record sa 2KB na mga segment:
TOAST(
chunk_id
integer
, chunk_seq
integer
, chunk_data
bytea
, PRIMARY KEY(chunk_id, chunk_seq)
);
Iyon ay, kung kailangan nating magsulat ng isang string na may "malaking" halaga data, pagkatapos ay magaganap ang tunay na pag-record hindi lamang sa main table at sa PK nito, kundi pati sa TOAST at sa PK nito.
Binabawasan ang impluwensya ng TOAST
Ngunit karamihan sa aming mga tala ay hindi pa rin ganoon kalaki, dapat magkasya sa 8KB - Paano ako makakatipid dito?..
Dito nakakatulong ang katangian sa hanay ng talahanayan:
- LALAKI nagbibigay-daan sa parehong compression at hiwalay na imbakan. Ito karaniwang opsyon para sa karamihan ng mga uri ng data na sumusunod sa TOAST. Sinusubukan muna nitong magsagawa ng compression, pagkatapos ay iimbak ito sa labas ng talahanayan kung ang row ay masyadong malaki.
- Pangunahin nagbibigay-daan sa compression ngunit hindi hiwalay na imbakan. (Sa katunayan, isasagawa pa rin ang hiwalay na storage para sa mga naturang column, ngunit lamang bilang huling paraan, kapag walang ibang paraan para paliitin ang string para magkasya ito sa page.)
Sa katunayan, ito mismo ang kailangan natin para sa teksto - i-compress ito hangga't maaari, at kung hindi ito magkasya, ilagay ito sa TOAST. Maaari itong gawin nang direkta sa mabilisang, na may isang utos:
ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;Paano suriin ang epekto
Dahil nagbabago ang daloy ng data araw-araw, hindi namin maihahambing ang mga ganap na numero, ngunit sa mga kaugnay na termino mas maliit na bahagi Isinulat namin ito sa TOAST - mas mabuti. Ngunit may panganib dito - kung mas malaki ang "pisikal" na dami ng bawat indibidwal na tala, nagiging "mas malawak" ang index, dahil kailangan nating saklawin ang higit pang mga pahina ng data.
Seksyon bago magbago:
heap = 37GB (39%)
TOAST = 54GB (57%)
PK = 4GB ( 4%)
Seksyon pagkatapos ng mga pagbabago:
heap = 37GB (67%)
TOAST = 16GB (29%)
PK = 2GB ( 4%)Sa katunayan, kami nagsimulang magsulat sa TOAST nang 2 beses na mas madalang, na nag-unload hindi lamang sa disk, kundi pati na rin sa CPU:
Mapapansin ko na naging mas maliit din tayo sa "pagbabasa" ng disk, hindi lamang sa "pagsusulat" - dahil kapag nagpasok ng isang tala sa isang talahanayan, kailangan din nating "basahin" ang bahagi ng puno ng bawat index upang matukoy ang posisyon sa hinaharap sa kanila.
Sino ang mabubuhay nang maayos sa PostgreSQL 11
Pagkatapos mag-update sa PG11, nagpasya kaming ipagpatuloy ang "tuning" TOAST at napansin na simula sa bersyong ito ang parameter :
Ang code sa pagpoproseso ng TOAST ay gagana lamang kapag ang halaga ng row na itatabi sa talahanayan ay mas malaki kaysa sa TOAST_TUPLE_THRESHOLD byte (karaniwang 2 KB). Ang TOAST code ay mag-i-compress at/o maglilipat ng mga value ng field palabas ng talahanayan hanggang sa ang row value ay maging mas mababa sa TOAST_TUPLE_TARGET bytes (variable value, kadalasang 2 KB) o hindi mababawasan ang laki.
Napagpasyahan namin na ang data na karaniwan naming mayroon ay alinman sa "napakaikli" o "napakahaba", kaya nagpasya kaming limitahan ang aming sarili sa pinakamababang posibleng halaga:
ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);Tingnan natin kung paano naapektuhan ng mga bagong setting ang paglo-load ng disk pagkatapos ng reconfiguration:
Hindi masama! Katamtaman ang pila sa disk ay nabawasan humigit-kumulang 1.5 beses, at ang disk ay "abala" ay 20 porsiyento! Ngunit marahil ito sa paanuman ay nakaapekto sa CPU?
At least hindi na lumala. Bagaman, mahirap husgahan kung kahit na ang mga naturang volume ay hindi pa rin makapagtaas ng average na load ng CPU nang mas mataas 5%.
Sa pamamagitan ng pagpapalit ng mga lugar ng mga termino, ang kabuuan... ay nagbabago!
Tulad ng alam mo, ang isang sentimos ay nakakatipid ng isang ruble, at sa dami ng aming imbakan ay tungkol ito 10TB/buwan kahit kaunting pag-optimize ay makapagbibigay ng magandang kita. Samakatuwid, binigyan namin ng pansin ang pisikal na istraktura ng aming data - kung paano eksakto "nakasalansan" na mga field sa loob ng record bawat isa sa mga talahanayan.
Dahil dahil sa straight forward ito :
Maraming mga arkitektura ang nagbibigay ng data alignment sa mga hangganan ng machine word. Halimbawa, sa isang 32-bit x86 system, ang mga integer (uri ng integer, 4 na byte) ay ihahanay sa isang 4-byte na hangganan ng salita, pati na rin ang dobleng katumpakan na mga numero ng floating point (double precision floating point, 8 bytes). At sa isang 64-bit na sistema, ang mga dobleng halaga ay ihahanay sa 8-byte na mga hangganan ng salita. Ito ay isa pang dahilan ng hindi pagkakatugma.
Dahil sa pagkakahanay, ang laki ng isang hilera ng talahanayan ay nakasalalay sa pagkakasunud-sunod ng mga patlang. Karaniwan ang epekto na ito ay hindi masyadong kapansin-pansin, ngunit sa ilang mga kaso maaari itong humantong sa isang makabuluhang pagtaas sa laki. Halimbawa, kung paghaluin mo ang char(1) at integer na mga field, karaniwang may 3 byte na masasayang sa pagitan ng mga ito.
Magsimula tayo sa mga sintetikong 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 байтSaan nagmula ang ilang dagdag na byte sa unang kaso? Ito ay simple - 2-byte smallint na nakahanay sa 4-byte na hangganan bago ang susunod na field, at kapag ito na ang huli, wala at hindi na kailangang ihanay.
Sa teorya, maayos ang lahat at maaari mong muling ayusin ang mga patlang ayon sa gusto mo. Suriin natin ito sa totoong data gamit ang halimbawa ng isa sa mga talahanayan, ang pang-araw-araw na seksyon na sumasakop sa 10-15GB.
Paunang istraktura:
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 pagkatapos baguhin ang pagkakasunud-sunod ng hanay - eksakto parehong mga patlang, iba lang ang pagkakasunod-sunod:
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 kabuuang dami ng seksyon ay tinutukoy ng bilang ng "mga katotohanan" at nakasalalay lamang sa mga panlabas na proseso, kaya hatiin natin ang laki ng tambak (pg_relation_size) sa pamamagitan ng bilang ng mga tala sa loob nito - iyon ay, nakukuha namin average na laki ng aktwal na nakaimbak na tala:
Minus 6% volume, Malaki!
Ngunit ang lahat, siyempre, ay hindi masyadong malarosas - pagkatapos ng lahat, sa mga index hindi natin mababago ang pagkakasunud-sunod ng mga patlang, at samakatuwid ay "sa pangkalahatan" (pg_total_relation_size) ...
...nandito pa rin nakatipid ng 1.5%nang hindi binabago ang isang linya ng code. Oo, oo!
Tandaan ko na ang opsyon sa itaas para sa pag-aayos ng mga patlang ay hindi ang katotohanan na ito ang pinakamainam. Dahil hindi mo nais na "punitin" ang ilang mga bloke ng mga patlang para sa mga aesthetic na dahilan - halimbawa, isang mag-asawa (pack, recno), na siyang PK para sa talahanayang ito.
Sa pangkalahatan, ang pagtukoy sa "minimum" na pag-aayos ng mga patlang ay isang medyo simpleng "brute force" na gawain. Samakatuwid, maaari kang makakuha ng mas mahusay na mga resulta mula sa iyong data kaysa sa amin - subukan ito!
Pinagmulan: www.habr.com