Faasaoina se pene i luga o voluma tetele i PostgreSQL

Fa'aauauina le autu o le fa'amauina o fa'amaumauga tetele o lo'o laga e mataupu muamua e uiga i le vaeluaga, i lenei mea o le a tatou tilotilo ai i auala e te mafaia faʻaititia le "faʻaletino" tele o le teuina i PostgreSQL, ma o latou aʻafiaga i le faʻatinoga o le server.

O le a tatou talanoa i ai TOAST fa'atulagaina ma fa'aoga fa'amaumauga. "I le averesi," o nei metotia o le a le faʻasaoina ai le tele o punaoa, ae aunoa ma le suia o le numera o talosaga.

Ae ui i lea, o lo matou poto masani na matua aoga lava i lenei tulaga, talu ai o le teuina o toetoe lava o soʻo se mataʻituina e lona natura tele fa'aopoopo-na'o i tulaga o fa'amaumauga fa'amaumau. Ma afai o loʻo e mafaufau pe faʻapefea ona e aʻoaʻoina le database e tusi i le disk nai lo 200MB / s afa - fa'amolemole i lalo o pusi.

O mea lilo laiti o fa'amatalaga tetele

I le talaaga o galuega la matou auaunaga, latou te felelei atu e lē aunoa iā te ia mai i ana afifi o tusitusiga.

Ma talu mai lena taimi VLSI lavelaveo ana fa'amaumauga matou te mata'ituina o se oloa e tele-vaega fa'atasi ai ma fa'amaumauga lavelave, ona fai lea o fesili mo le faatinoga aupito maualuga e foliga mai e faapea "tele-volume" faʻatasi ai ma le faʻaogaina algorithmic logic. O lea la o le tele o faʻataʻitaʻiga taʻitasi o se talosaga poʻo le faʻataunuʻuina o le faʻatinoga o fuafuaga i totonu o le ogalaau e oʻo mai ia i matou e foliga mai e "i le averesi" tele.

Seʻi o tatou vaʻavaʻai i le fausaga o se tasi o laulau tatou te tusia ai faʻamatalaga "mata" - o lona uiga, o le uluaʻi tusitusiga lea mai le faʻamatalaga ogalaau:

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

O se faʻailoga masani (ua uma ona vaevaeina, ioe, o le mea lea o se faʻataʻitaʻiga vaega), lea o le mea sili ona taua o le tusitusiga. O nisi taimi e fai si voluminous.

Manatua o le "faʻaletino" tele o le tasi faʻamaumauga i totonu o le PG e le mafai ona sili atu ma le tasi le itulau o faʻamaumauga, ae o le "talafeagai" tele o se mea e matua ese lava. Ina ia tusia se tau volumetric (varchar/text/bytea) i se fanua, faʻaaoga TOAST tekinolosi:

PostgreSQL fa'aogaina le lapopoa o le itulau (e masani lava 8 KB), ma e le fa'atagaina tuples e fa'alautele itulau e tele. O le mea lea, e le mafai ona teuina sa'o le tele o tau fanua. Ina ia foia lenei tapulaʻa, e faʻapipiʻi ma / pe vaeluaina le tele o faʻatauga i luga o le tele o laina faaletino. E tupu lenei mea e le iloa e le tagata faʻaoga ma e itiiti sona aʻafiaga i le tele o code server. Ole auala lea e ta'ua ole TOAST...

O le mea moni, mo laulau uma e iai fa'ato'aga "mafaufau tetele", fa'apitoa ua faia se laulau fa'atasi ma le "slicing". fa'amaumauga ta'itasi "tele" i vaega 2KB:

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

O lona uiga, afai e tatau ona tatou tusia se manoa ma se "tele" tau data, ona tupu lea o le pueina moni e le gata i le laulau autu ma lona PK, ae faapea foi i le TOAST ma lona PK.

Fa'aititia le TOAST a'afiaga

Ae o le tele o a tatou faamaumauga e le o tele lava, e tatau ona ofi ile 8KB - E mafai faapefea ona ou sefeina tupe i lenei mea?..

O i ina e sau ai le uiga e fesoasoani ia i tatou STORAGE i le koluma laulau:

• FAʻALILIU fa'ataga uma fa'amalosi ma tu'ueseese le teuina. Lenei filifiliga masani mo le tele o ituaiga fa'amaumauga e tausisia TOAST. E taumafai muamua e fai fa'amalosi, ona teu ai lea i fafo o le laulau pe a lapo'a tele le laina.
• LIMA fa'ataga fa'amalosi ae le tu'ueseese le teuina. (O le mea moni, e ese le teuina o le a faia pea mo ia koluma, ae naʻo e fai ma ala mulimuli, pe a leai se isi auala e faʻaitiitia ai le manoa ina ia fetaui i luga o le itulau.)

O le mea moni, o le mea tonu lea tatou te manaʻomia mo le tusitusiga - fa'apipi'i i le tele e mafai ai, ma afai e le fetaui, tu'u ile TOAST. E mafai ona faia sa'o i luga o le lele, ma le tasi poloaiga:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Auala e iloilo ai le aafiaga

Talu ai e suia le tafe o faʻamatalaga i aso uma, e le mafai ona tatou faʻatusatusaina numera atoatoa, ae i ni faʻamatalaga sea ​​itiiti Na matou tusia i lalo i le TOAST - e sili atu le lelei. Ae o loʻo i ai se tulaga lamatia iinei - o le tele o le "faaletino" voluma o faʻamaumauga taʻitasi, o le "lautele" o le faʻailoga, aua e tatau ona tatou ufiufi atili itulau o faʻamaumauga.

Vaega a o lei suia:

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

Vaega pe a uma suiga:

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

O le mea moni, tatou amata ona tusitusi i TOAST 2 taimi fa'aitiitia soo, lea e le gata na faʻauluina le disk, ae faʻapea foi le PPU:

O le a ou matauina ua tatou laiti foi i le "faitau" le tisiki, e le gata i le "tusitusi" - talu ai pe a faʻapipiʻi se faʻamaumauga i totonu o se laulau, e tatau foi ona tatou "faitau" se vaega o le laʻau o faʻamaumauga taʻitasi ina ia iloa ai lona tulaga i le lumanai ia i latou.

O ai e mafai ona ola lelei i PostgreSQL 11

Ina ua uma ona faʻafouina i le PG11, na matou filifili e faʻaauau le "tuning" TOAST ma matauina e amata mai i lenei faʻasologa o le parakalafa toast_tuple_target:

O le TOAST process code e na'o le mu pe a lapopo'a le tau o le laina e teu i le laulau nai lo TOAST_TUPLE_THRESHOLD paita (e masani lava 2 KB). O le TOAST code o le a fa'apipi'i ma/po'o fa'ate'a ese tau o fanua mai le laulau se'ia o'o ina la'ititi le tau o le laina i le TOAST_TUPLE_TARGET paita (tau fesuia'i, e masani fo'i 2 KB) pe le mafai ona fa'aititia le tele.

Na matou filifili o faʻamaumauga e masani ona matou maua e "puʻupuʻu" pe "matua umi", o lea na matou filifili ai e faʻatapulaʻa i matou i le tau maualalo:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Sei o tatou va'ai pe fa'afefea ona a'afia le fa'amomoliina o tisiki i tulaga fou pe a uma ona toe fa'atulaga:

E leai se leaga! averesi ua faaitiitia le laina i le tisiki e tusa ma le 1.5 taimi, ma o le tisiki "pisi" e 20 pasene! Ae atonu o lenei mea na afaina ai le PPU?

O le mea sili e le'i atili leaga ai. E ui lava, e faigata ona faʻamasino pe a oʻo lava i ia voluma e le mafai lava ona siitia le averesi o le CPU uta maualuga 5%.

E ala i le suia o nofoaga o tuutuuga, o le aofaʻi ... suia!

E pei ona e iloa, o le sene e sefe ai se ruble, ma faʻatasi ai ma a matou voluma teuina e uiga i 10TB/masina e oo lava i sina optimization itiiti e mafai ona maua se tupe mama lelei. O le mea lea, matou te gauai atu i le fausaga faʻapitoa o matou faʻamaumauga - faʻafefea tonu "fa'aputu" fanua i totonu o fa'amaumauga o laulau taitasi.

Aua ona o fa'asologa o fa'amaumauga e sa'o i luma lea aafia ai le voluma e maua:

O le tele o tusiata e tu'uina atu fa'amaumauga i luga o tuaoi upu masini. Mo se faʻataʻitaʻiga, i luga o le 32-bit x86 system, o numera (ituaiga integer, 4 bytes) o le a faʻaoga i luga o le 4-byte upu tuaoi, faʻapea foʻi ma le faʻaluaina o numera faʻafefete saʻo (faʻalua saʻo faʻafefete, 8 paita). Ma i luga o le 64-bit system, faʻaluaina tau o le a faʻaogaina i le 8-byte upu tuaoi. O le isi lea mafuaaga o le le fetaui.

Ona o le alignment, o le tele o se laina laulau e faalagolago i le faasologa o fanua. E masani lava o lenei aafiaga e le iloa tele, ae i nisi tulaga e mafai ona oʻo atu ai i se faʻaopoopoga taua i le tele. Mo se faʻataʻitaʻiga, afai e te faʻafefiloi le char(1) ma le numera numera, e masani lava ona i ai le 3 paita faʻaumatia i le va.

Sei o tatou amata i faʻataʻitaʻiga synthetic:

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

O fea na maua mai ai ni nai pate faaopoopo i le mataupu muamua? E faigofie- 2-byte smallint fa'aoga i luga ole 4-byte tuaoi i luma o le isi fanua, ma a oʻo i le mea mulimuli, e leai se mea ma leai se manaʻoga e faʻaoga.

I le talitonuga, e lelei mea uma ma e mafai ona e toe faʻatulagaina fanua e te manaʻo ai. Sei o tatou siaki i luga o faʻamatalaga moni e faʻaaoga ai le faʻataʻitaʻiga o se tasi o laulau, o le vaega o aso taʻitasi e nofo ai le 10-15GB.

Fuafuaga muamua:

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)

Vaega pe a uma ona suia le faasologa o koluma - tonu tutusa fanua, naʻo le faʻatonuga eseese:

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)

O le aofaʻi atoa o le vaega e faʻamoemoeina i le numera o "mea moni" ma e faʻalagolago lava i faiga i fafo, o lea ia tatou vaevaeina le tele o le faʻaputuga (pg_relation_size) i le numera o faʻamaumauga i totonu - o lona uiga, matou te maua averesi le tele o faamaumauga moni o lo'o teuina:

Toese le 6% voluma, Matagofie!

Ae o mea uma, ioe, e le o se rosy - pe a uma, i index e le mafai ona tatou suia le faasologa o fanua, ma o lea “i le tulaga lautele” (pg_total_relation_size) ...

... iinei foi sefe 1.5%e aunoa ma le suia o se laina se tasi o code. Ioe, ioe!

Ou te maitauina o le filifiliga o loʻo i luga mo le faʻatulagaina o fanua e le o le mea moni e sili ona lelei. Talu ai e te le manaʻo e "saeia" nisi poloka o fanua mo mafuaaga faʻalelei - mo se faʻataʻitaʻiga, o se ulugalii (pack, recno), o le PK lea mo lenei laulau.

I se tulaga lautele, o le fuafuaina o le "maualalo" faatulagaga o fanua o se galuega faigofie "malosi malosi". O le mea lea, e mafai ona e maua atili i'uga lelei mai au fa'amatalaga nai lo matou - taumafai!

puna: www.habr.com