Tehirizo penny amin'ny boky be ao amin'ny PostgreSQL

Manohy ny lohahevitra momba ny fandraketana ny angon-drakitra lehibe natsangan'ny lahatsoratra teo aloha momba ny fisarahana, amin'ity dia hojerentsika ny fomba azonao atao mampihena ny haben'ny "fizika" ny voatahiry ao amin'ny PostgreSQL, ary ny fiantraikany amin'ny fahombiazan'ny mpizara.

Hiresaka momba izany isika Toerana TOAST sy fampifanarahana angon-drakitra. "Amin'ny ankapobeny," ireo fomba ireo dia tsy hamonjy loharanon-karena be loatra, fa tsy misy fanovana ny fehezan-dalàna fampiharana mihitsy.

Na izany aza, ny traikefanay dia hita fa tena namokatra tamin'io lafiny io, satria ny fitahirizana ny fanaraha-maso rehetra amin'ny maha-izy azy ampiana ihany ny ankamaroany raha ny angon-drakitra voarakitra. Ary raha manontany tena ianao hoe ahoana no ahafahanao mampianatra ny angon-drakitra hanoratra amin'ny kapila 200MB / s antsasaky ny - azafady ambanin'ny saka.

Tsiambaratelo kely amin'ny angon-drakitra lehibe

Amin'ny mombamomba ny asa ny fanompoanay, dia manidina tsy tapaka any aminy avy any an-davaka izy ireo fonosana lahatsoratra.

Ary nanomboka VLSI complexizay angon-drakitra arahintsika dia vokatra misy singa maromaro miaraka amin'ny rafitra angon-drakitra sarotra, avy eo manontany ho an'ny fampisehoana ambony indrindra mivadika tahaka izao "multi-volume" miaraka amin'ny lojika algorithmika sarotra. Noho izany, ny habetsahan'ny tranga tsirairay amin'ny fangatahana na ny drafitra famonoana vokatra ao amin'ny log izay tonga amintsika dia lasa "amin'ny salan'isa" lehibe.

Andeha hojerentsika ny firafitry ny iray amin'ireo tabilao izay hanoratantsika angona "manta" - izany hoe, ity ny lahatsoratra voalohany avy amin'ny fidirana log:

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

Famantarana mahazatra (efa fizarana, mazava ho azy, ka modely fizarana ity), izay ny zava-dehibe indrindra dia ny lahatsoratra. Indraindray somary voluminous.

Tsarovy fa ny haben'ny "fizika" amin'ny rakitra iray ao amin'ny PG dia tsy afaka mibodo mihoatra ny pejy iray amin'ny angon-drakitra, fa ny haben'ny "lojika" dia zavatra hafa tanteraka. Raha hanoratana sanda volumetric (varchar/text/bytea) amin'ny saha iray dia ampiasao TOAST teknolojia:

PostgreSQL dia mampiasa haben'ny pejy raikitra (matetika 8 KB), ary tsy mamela ny tuple hamakivaky pejy maromaro. Noho izany, tsy azo atao ny mitahiry mivantana ny sandan'ny saha lehibe. Mba handresena an'io famerana io, ny sandan'ny saha lehibe dia voatsindry sy/na mizara amin'ny tsipika ara-batana maro. Mitranga tsy voamariky ny mpampiasa izany ary misy fiantraikany kely amin'ny ankamaroan'ny code server. Ity fomba ity dia fantatra amin'ny hoe TOAST...

Raha ny marina, ho an'ny latabatra rehetra misy saha "mety ho lehibe", mandeha ho azy misy latabatra miaraka amin'ny "slicing" dia noforonina ny firaketana "lehibe" tsirairay amin'ny fizarana 2KB:

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

Izany hoe, raha tsy maintsy manoratra tady misy sanda "lehibe". data, dia hiseho ny tena firaketana tsy amin'ny latabatra lehibe sy ny PK ihany, fa ny TOAST sy ny PK ihany koa.

Mampihena ny fiantraikan'ny TOAST

Saingy ny ankamaroan'ny firaketanay dia mbola tsy dia lehibe loatra, tokony hifanaraka amin'ny 8KB - Ahoana no ahafahako mitahiry vola amin'izany?...

Eto dia tonga ny toetra manampy antsika STORAGE eo amin'ny tsanganana latabatra:

• Extended mamela ny famatrarana sy ny fitehirizana misaraka. izany safidy mahazatra ho an'ny ankamaroan'ny karazana data mifanaraka amin'ny TOAST. Miezaka manao famoretana aloha izy, avy eo mitahiry azy any ivelan'ny latabatra raha mbola lehibe loatra ilay laharana.
• TANAN'ILAY mamela ny famoretana fa tsy misaraka fitahirizana. (Raha ny marina, ny fitehirizana misaraka dia mbola hatao ho an'ny tsanganana toy izany, fa ihany ho fomba farany, rehefa tsy misy fomba hafa hampihenana ny tady mba hifanaraka amin'ny pejy.)

Raha ny marina, izany indrindra no ilaintsika amin'ny lahatsoratra - compresse arak'izay azo atao, ary raha tsy mety mihitsy dia ataovy ao anaty TOAST. Izany dia azo atao mivantana amin'ny lalitra, miaraka amin'ny baiko iray:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Ahoana ny fanombanana ny vokany

Satria miova isan'andro ny fikorianan'ny angon-drakitra, tsy afaka mampitaha isa tanteraka isika, fa amin'ny teny mifandraika anjara kely kokoa Nosoratanay tao amin'ny TOAST izany - tena tsara kokoa. Saingy misy loza eto - arakaraka ny haben'ny boky "ara-batana" amin'ny firaketana tsirairay, ny "lehibe kokoa" ny fanondroana, satria tsy maintsy mandrakotra pejy maromaro kokoa isika.

Faritra alohan'ny fiovana:

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

Faritra aorian'ny fiovana:

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

Raha ny marina, isika nanomboka nanoratra tamin'ny TOAST in-2 tsy dia matetika, izay tsy ny kapila ihany no navoaka, fa ny CPU koa:

Marihiko fa lasa kely kokoa koa isika amin'ny "mamaky" ny kapila, tsy hoe "manoratra" fotsiny - satria rehefa mampiditra rakitsoratra ao anaty latabatra dia tsy maintsy "mamaky" ny ampahany amin'ny hazo isaky ny index ihany koa isika mba hamaritana azy. toerana ho avy ao aminy.

Iza no afaka miaina tsara amin'ny PostgreSQL 11

Rehefa avy nanavao ny PG11 izahay, dia nanapa-kevitra ny hanohy ny "tuning" TOAST ary nahatsikaritra fa nanomboka tamin'ity dikan-teny ity dia nanjary azo ampiasaina ny mari-pamantarana. toast_tuple_target:

Ny kaody fanodinana TOAST ihany no mirehitra rehefa lehibe kokoa noho ny TOAST_TUPLE_THRESHOLD bita (matetika 2 Kio) ny sandan'ny andalana hotehirizina ao amin'ny latabatra. Ny kaody TOAST dia hanery sy/na hamindra ny sandan'ny saha hiala amin'ny latabatra mandra-pahatongan'ny sandan'ny andalana ho latsaky ny TOAST_TUPLE_TARGET bytes (variable value, matetika 2 KB) na tsy azo ahena ny habeny.

Nanapa-kevitra izahay fa ny angon-drakitra anananay matetika dia "fohy be" na "lava be", ka nanapa-kevitra ny hametra ny tenanay amin'ny sanda faran'izay kely indrindra izahay:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Andeha hojerentsika hoe ahoana no fiantraikan'ny fanovana vaovao amin'ny fametahana kapila taorian'ny fanavaozana azy:

Tsy ratsy! Average nihena ny filaharana mankany amin'ny kapila in-1.5 eo ho eo, ary 20 isan-jato ny kapila “be atao”! Saingy mety hisy fiantraikany amin'ny CPU izany?

Farafaharatsiny tsy niharatsy izany. Na izany aza, sarotra ny mitsara raha toa ka mbola tsy afaka mampiakatra avo kokoa ny enta-mavesatra CPU 5%.

Amin'ny fanovana ny toeran'ny fehezanteny dia miova ny fitambarana...!

Araka ny fantatrao, ny denaria iray dia mitahiry roubles, ary miaraka amin'ny habetsaky ny fitahirizanay izany 10TB/volana na optimization kely aza dia afaka manome tombony tsara. Noho izany, nijery ny firafitry ny angonay izahay - ahoana marina izany saha "stacked" ao anaty rakitra ny latabatra tsirairay.

Satria noho ny fampifanarahana angon-drakitra mivantana izany misy fiantraikany amin'ny volume aterak'izany:

Architecture maro no manome fampifanarahana angon-drakitra amin'ny sisin'ny teny milina. Ohatra, amin'ny rafitra 32-bit x86, ny integers (karazana integer, 4 bytes) dia hifanaraka amin'ny sisin'ny teny 4-byte, toy izany koa ny isa avo roa heny amin'ny teboka mitsingevana (point mitsingevana avo roa heny, 8 bytes). Ary amin'ny rafitra 64-bit, ny soatoavina avo roa heny dia hifanaraka amin'ny sisin'ny teny 8-byte. Antony iray hafa mahatonga ny tsy fifanarahana izany.

Noho ny fampifanarahana dia miankina amin'ny filaharan'ny saha ny haben'ny andalana iray. Matetika io vokatra io dia tsy dia mibaribary loatra, fa amin'ny toe-javatra sasany dia mety hiteraka fitomboana lehibe izany. Ohatra, raha mampifangaro saha char(1) sy integer ianao, dia matetika misy 3 byte very eo anelanelan'izy ireo.

Andeha isika hanomboka amin'ny modely 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 байт

Avy aiza ny bita fanampiny roa tamin'ny tranga voalohany? Tsotra izany- 2-byte smallint mirindra amin'ny sisin-tany 4-byte alohan'ny saha manaraka, ary rehefa farany dia tsy misy na inona na inona ary tsy ilaina ny mampifanaraka.

Amin'ny teoria, tsara ny zava-drehetra ary azonao atao ny mandamina ny saha araka izay tianao. Andeha hojerentsika amin'ny angon-drakitra tena izy amin'ny fampiasana ny ohatra amin'ny iray amin'ireo latabatra, ny fizarana isan'andro izay misy 10-15GB.

Rafitra voalohany:

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)

Fizarana aorian'ny fanovana ny filaharan'ny tsanganana - marina saha mitovy, samy hafa ny filaharana:

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)

Ny habetsaky ny fizarana dia voafaritra amin'ny isan'ny "zava-misy" ary miankina amin'ny dingana ivelany ihany, koa andao hizara ny haben'ny antontam (pg_relation_size) amin'ny isan'ny firaketana ao aminy - izany hoe mahazo antonony ny haben'ny rakitra voatahiry:

minus 6% ny volume, Tsara!

Fa ny zava-drehetra, mazava ho azy, dia tsy dia rosy loatra - na izany aza, amin'ny indexes dia tsy afaka manova ny filaharan'ny saha isika, ary noho izany dia “amin’ny ankapobeny” (pg_total_relation_size) ...

...mbola eto koa voavonjy 1.5%nefa tsy manova andalana tokana. Eny eny!

Marihiko fa ny safidy etsy ambony amin'ny fandaminana saha dia tsy ny hoe io no tsara indrindra. Satria tsy te "hamiravira" faritra sasany ianao noho ny antony ara-estetika - ohatra, mpivady (pack, recno), izay ny PK ho an'ity latabatra ity.

Amin'ny ankapobeny, ny famaritana ny fandaharana "farafahakeliny" ny saha dia asa tsotra "hery mahery". Noho izany, afaka mahazo vokatra tsara kokoa ianao amin'ny angonao noho ny anay - andramo!

Source: www.habr.com