Gorde zentimo bat PostgreSQL-n bolumen handietan

Datu-jario handiak grabatzearen gaiarekin jarraituz, planteatu zen aurreko artikulua sekzioei buruz, honetan, nola egin dezakezun aztertuko dugu gordetakoaren "tamaina fisikoa" murriztu PostgreSQL-en eta zerbitzariaren errendimenduan duten eragina.

Hitz egingo dugu TOAST ezarpenak eta datuen lerrokatzeaBatez beste, metodo hauek ez dizute baliabide gehiegi aurreztuko, baina zure aplikazioaren kodean aldaketarik egin beharrik gabe egingo dute.

Hala ere, gure esperientzia oso emankorra izan da alderdi honetan, ia edozein monitorizazio biltegiratzea berez baita gehienbat eranskin soilik idazten ari diren datuei dagokienez. Eta datu-baseari diskoan idazteko nola irakatsi diezaiokezun galdetzen ari bazara 200MB / s erdia - mesedez, ikusi ebakiaren azpian.

Datu Handien Sekretu Txikiak

Lan-profilaren arabera gure zerbitzua, erregistroetatik mezuak jasotzen ditu aldizka testu paketeak.

Eta geroztik SBIS konplexua, zeinaren datu-basea monitorizatzen dugun, datu-egitura konplexuak dituen osagai anitzeko produktua da, eta kontsultak errendimendu maximoa lortzeko nahiko horrela ateratzen dira "bolumen anitzeko" liburuak, logika algoritmiko konplexua dutenakBeraz, jasotzen dugun erregistroan kontsulta-instantzia bakoitzaren edo ondoriozko exekuzio-planaren tamaina nahiko handia da "batez beste".

Ikus dezagun datu gordinak idazten ditugun tauletako baten egitura, hau da, erregistro-sarrerako jatorrizko testua:

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

Ohiko taula bat (atalka banatuta, noski, beraz, atal-txantiloi bat da hau), non garrantzitsuena testua den. Batzuetan nahiko bolumena izaten da.

Gogora dezagun PG-ko erregistro bakar baten "tamaina fisikoak" ezin duela datu-orrialde bat baino gehiago hartu, baina "tamaina logikoa" guztiz bestelako kontua dela. Eremu batean balio handi bat (varchar/text/bytea) idazteko, erabili TOAST teknologia:

PostgreSQL-ek orrialde-tamaina finko bat erabiltzen du (normalean 8 KB) eta ez du onartzen tuplak hainbat orrialdetan zehar hedatzea. Beraz, ezinezkoa da eremu-balio oso handiak zuzenean gordetzea. Muga hori gainditzeko, eremu-balio handiak konprimitu eta/edo hainbat errenkada fisikotan banatzen dira. Hau modu garden batean gertatzen da erabiltzailearentzat eta eragin txikia du zerbitzari-kode gehienetan. Metodo hau TOAST bezala ezagutzen da...

Izan ere, “potentzialki handiak” diren eremuak dituen taula bakoitzeko, automatikoki "Ebaki" batekin parekatutako taula bat sortzen da 2KB segmentutan "erregistro handi" bakoitza:

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

Hau da, balio "handia" duen kate bat idatzi behar badugu data, orduan benetako grabaketa gertatuko da ez bakarrik taula nagusian eta bere PK-n, baita TOAST-en eta bere PK-n ere.

TOASTen eragina murriztea

Baina gure disko gehienak ez dira hain handiak, 8KB-tan sartu beharko luke — Nola aurreztu dezaket dirua honetan?...

Hemen datorkigu atributua laguntzera STORAGE taulako zutabearentzat:

• EXTENDED konpresioa eta biltegiratze bereizia baimentzen ditu. Honek aukera estandarra TOASTekin bateragarriak diren datu mota gehienentzat, lehenik konpresioa saiatzen da, eta ondoren taulatik kanpo gordetzen da errenkada oraindik handiegia bada.
• NAGUSIAK konpresioa baimentzen du, baina ez biltegiratze bereizia. (Izan ere, biltegiratze bereizia egingo da zutabe horietarako, baina bakarrik azken aukera gisa, lerroa orrialdean sartzeko beste modurik ez dagoenean.)

Izan ere, hau da testurako behar duguna - ahalik eta gehien konprimitu, eta batere sartzen ez bada, sartu TOSTADA bateanKomando bakarrarekin egin dezakezu hau berehala:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Nola ebaluatu eragina

Datu-fluxua egunero aldatzen denez, ezin ditugu zenbaki absolutuak alderatu, baina bai termino erlatiboetan. zati txikiagoa. TOAST-en idatzi genuen, hobeto. Baina arrisku bat dago hemen: erregistro bakoitzaren "tamaina fisikoa" zenbat eta handiagoa izan, orduan eta "zabalagoa" bihurtzen da indizea, datu-orrialde gehiago hartu behar baititu.

Atala aldaketak baino lehen:

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

Atala aldaketen ondoren.:

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

Izan ere, gu jendeak bi aldiz gutxiagotan hasi zen TOASTen idazten, diskoa ez ezik, CPUa ere deskargatu zuena:

Ohartuko naiz diskoa gutxiago "irakurtzen" hasi ginela, ez bakarrik "idazten" - izan ere, erregistro bat taula batean txertatzean, indize bakoitzaren zuhaitzaren zati bat ere "irakurri" behar dugu etorkizunean bertan izango duen posizioa zehazteko.

Nork bizi du ondo PostgreSQL 11n?

PG11ra eguneratu ondoren, TOAST "doitzen" jarraitzea erabaki genuen eta bertsio honekin hasita parametroa konfiguraziorako eskuragarri zegoela ohartu ginen. toast_tuple_target:

TOAST prozesamendua taulan gorde beharreko errenkada-balioa TOAST_TUPLE_THRESHOLD byte baino handiagoa denean bakarrik abiarazten da (normalean 2 KB). TOASTek eremu-balioak konprimitu eta/edo taulatik kanpo mugituko ditu errenkada-balioa TOAST_TUPLE_TARGET byte baino txikiagoa izan arte (balio aldakorra, normalean 2 KB) edo tamaina murriztu ezin den arte.

Gure datuak normalean "oso laburrak" edo "oso luzeak" direla erabaki genuen, beraz, ahalik eta balio txikienera mugatzea erabaki genuen:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Ikus dezagun nola eragin zuten ezarpen berriek diskoaren kargan birkonfigurazioaren ondoren:

Ez txarra! Erdikoa disko ilara murriztu da 1.5 aldiz gutxi gorabehera, eta diskoaren "okupazioa" %20 ingurukoa da! Baina agian honek nolabait CPUari eragin zion?

Gutxienez, ez da okerrera egin. Hala ere, zaila da esatea bolumen horiek ere ezin duten CPUaren batez besteko karga gehiago igo. 5%.

Terminoen ordena aldatuz, batura… aldatu egiten da!

Badakizuenez, aurreztutako zentimo bat irabazitako zentimo bat da, eta gure biltegiratze-bolumenekin, ordena da... 10 TB/hilean Optimizazio txiki batek ere irabazi nabarmenak eman ditzake. Horregatik aztertu dugu gure datuen egitura fisikoa: nola zehazki eremuak erregistroaren barruan "jarrita" daude mahai bakoitza.

Zeren eta horregatik datuen lerrokatzea zuzena da. ondoriozko bolumenean eragina du:

Arkitektura askok datuak makina-hitzen mugetan lerrokatzen dituzte. Adibidez, 32 biteko x86 sistema batean, zenbaki osoak (zenbaki osoa, 4 byte mota) 4 byteko hitzen mugetan lerrokatuko dira, baita zehaztasun bikoitzeko puntu mugikorreko zenbakiak ere (zehaztasun bikoitzeko mota, 8 byte). Hala ere, 64 biteko sistema batean, balio bikoitzak 8 byteko hitzen mugetan lerrokatuko dira. Hau bateraezintasun iturri bat da.

Lerrokatzea dela eta, taula-errenkada baten tamaina eremuen ordenaren araberakoa da. Efektu hau normalean ez da oso nabarmena izaten, baina kasu batzuetan tamaina nabarmen handitzea ekar dezake. Adibidez, char(1) eta zenbaki osoko eremuak nahasten badituzu, hiru byte alferrik galduko dira normalean bien artean.

Has gaitezen eredu sintetikoekin:

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

Nondik atera ziren byte gehigarri horiek lehenengo kasuan? Erraza da— 2 byteko smallint bat 4 byteko muga batean lerrokatuta dago. hurrengo eremuaren aurretik, eta azkena denean, ez dago ezer eta ez dago lerrokatzeko beharrik.

Teorian, dena ondo dago, eta eremuak nahi duzun bezala berrantola ditzakezu. Probatu dezagun hau benetako datuekin, adibide gisa taula bat erabiliz, zeinaren eguneroko atalak 10-15 GB hartzen dituen.

Jatorrizko egitura:

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)

Zutabeen ordena aldatu ondorengo atala berdin-berdina da eremu berdinak, ordena desberdina besterik ez:

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)

Atalaren bolumen osoa "gertakari" kopuruak zehazten du eta kanpoko prozesuen araberakoa da soilik, beraz, heap tamaina zatitzen dugu (pg_relation_size) bertan dauden erregistro kopuruaren arabera - hau da, lortzen dugu benetako gordetako erregistroaren batez besteko tamaina:

% 6ko bolumena, Bikain!

Baina dena, noski, ez da hain arrosa kolorekoa - azken finean Ezin dugu indizeetako eremuen ordena aldatu., eta beraz, “orokorrean” (pg_total_relation_size) ...

...eta oraindik hemen % 1.5 aurreztu da, kode lerro bakar bat ere aldatu gabe. Bai, noski!

Goiko eremu-diseinua ez dela nahitaez egokiena adierazi nahi nuke. Eremu-bloke batzuk ez baitira "zatitu" nahi arrazoi estetikoengatik —adibidez, pare bat (pack, recno), hau da, taula honen PK.

Oro har, "minimo" eremuaren diseinua zehaztea "zenbaketa" lan nahiko erraza da. Beraz, zure datuekin emaitza hobeak lor ditzakezu guk baino: saiatu!

Iturria: www.habr.com