Խնայեք մեկ կոպեկ PostgreSQL-ում մեծ ծավալների վրա

Շարունակելով բարձրացված տվյալների մեծ հոսքերի գրանցման թեման Նախորդ հոդվածը բաժանման մասին, այստեղ մենք կանդրադառնանք այն ուղիներին, որոնցով դուք կարող եք նվազեցնել պահեստի «ֆիզիկական» չափը PostgreSQL-ում և դրանց ազդեցությունը սերվերի աշխատանքի վրա:

Մենք կխոսենք TOAST-ի կարգավորումներ և տվյալների հավասարեցում. «Միջին հաշվով», այս մեթոդները չեն խնայի չափազանց շատ ռեսուրսներ, բայց ընդհանրապես առանց կիրառման կոդը փոփոխելու:

Սակայն մեր փորձը շատ արդյունավետ է այս առումով, քանի որ իր բնույթով գրեթե ցանկացած մոնիտորինգի պահպանումն է հիմնականում միայն հավելվածով գրանցված տվյալների առումով։ Եվ եթե դուք մտածում եք, թե ինչպես կարող եք սովորեցնել տվյալների բազայի փոխարեն գրել սկավառակի վրա 200MB / վրկ կեսը - խնդրում եմ կատվի տակ:

Մեծ տվյալների փոքր գաղտնիքները

Ըստ աշխատանքի պրոֆիլի մեր ծառայությունը, որջերից պարբերաբար թռչում են նրա մոտ տեքստային փաթեթներ.

Եվ քանի որ VLSI համալիրորի տվյալների բազան մենք վերահսկում ենք, բազմաբաղադրիչ արտադրանք է՝ տվյալների բարդ կառուցվածքներով, այնուհետև հարցումներով առավելագույն կատարման համար ստացվում է բավականին այսպես «բազմածավալ» բարդ ալգորիթմական տրամաբանությամբ. Այսպիսով, մեզ հասած տեղեկամատյանում հարցման յուրաքանչյուր առանձին օրինակի կամ արդյունքում կատարման պլանի ծավալը պարզվում է, որ «միջին հաշվով» բավականին մեծ է:

Եկեք նայենք աղյուսակներից մեկի կառուցվածքին, որում մենք գրում ենք «հում» տվյալներ, այսինքն՝ ահա օրիգինալ տեքստը մատյան մուտքից.

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

Տիպիկ նշան (իհարկե արդեն կտրված է, այնպես որ սա հատվածի կաղապար է), որտեղ ամենակարևորը տեքստն է։ Երբեմն բավականին ծավալուն:

Հիշեցնենք, որ PG-ում մեկ գրառումի «ֆիզիկական» չափը չի կարող զբաղեցնել մեկից ավելի էջ տվյալների, բայց «տրամաբանական» չափը բոլորովին այլ խնդիր է: Դաշտում ծավալային արժեք (varchar/text/bytea) գրելու համար օգտագործեք TOAST տեխնոլոգիա:

PostgreSQL-ն օգտագործում է էջի ֆիքսված չափս (սովորաբար 8 ԿԲ) և թույլ չի տալիս բազմակի էջերը տարածել: Հետևաբար, անհնար է ուղղակիորեն պահել դաշտի շատ մեծ արժեքներ: Այս սահմանափակումը հաղթահարելու համար դաշտի մեծ արժեքները սեղմվում և/կամ բաժանվում են բազմաթիվ ֆիզիկական գծերի վրա: Սա տեղի է ունենում օգտագործողի կողմից աննկատ և քիչ ազդեցություն ունի սերվերի կոդի մեծ մասի վրա: Այս մեթոդը հայտնի է որպես ՏՈՍՏ...

Փաստորեն, «պոտենցիալ մեծ» դաշտերով յուրաքանչյուր աղյուսակի համար՝ ավտոմատ կերպով ստեղծվում է զուգակցված աղյուսակ «կտրատումով»: յուրաքանչյուր «մեծ» գրառում 2 ԿԲ հատվածներում.

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

Այսինքն, եթե մենք պետք է գրենք «մեծ» արժեքով տող data, ապա իրական ձայնագրությունը տեղի կունենա ոչ միայն հիմնական սեղանին և նրա ՊԿ-ին, այլ նաև ՏՈՍՏ-ին և նրա ՊԿ-ին.

Նվազեցնելով TOAST-ի ազդեցությունը

Բայց մեր ռեկորդների մեծ մասը դեռ այնքան էլ մեծ չէ, պետք է տեղավորվի 8 ԿԲ-ի մեջ -Ինչպե՞ս կարող եմ գումար խնայել սրա վրա...

Այստեղ է, որ հատկանիշը մեզ օգնության է հասնում STORAGE աղյուսակի սյունակում.

• Ընդլայնված թույլ է տալիս ինչպես սեղմում, այնպես էլ առանձին պահեստավորում: Սա ստանդարտ տարբերակ TOAST-ին համապատասխանող տվյալների մեծ մասի համար: Այն սկզբում փորձում է սեղմել, այնուհետև այն պահում է աղյուսակից դուրս, եթե տողը դեռ շատ մեծ է:
• ԳԼԽԱՎՈՐ թույլ է տալիս սեղմել, բայց ոչ առանձին պահեստավորում: (Իրականում նման սյունակների համար դեռ կկատարվի առանձին պահեստավորում, բայց միայն որպես վերջին միջոց, երբ տողը սեղմելու այլ միջոց չկա, որպեսզի այն տեղավորվի էջի վրա։)

Փաստորեն, սա հենց այն է, ինչ մեզ անհրաժեշտ է տեքստի համար. որքան հնարավոր է սեղմեք այն, իսկ եթե ընդհանրապես չի տեղավորվում, դրեք TOAST-ի մեջ. Դա կարելի է անել անմիջապես թռչելիս՝ մեկ հրամանով.

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Ինչպես գնահատել ազդեցությունը

Քանի որ տվյալների հոսքը փոխվում է ամեն օր, մենք չենք կարող համեմատել բացարձակ թվերը, բայց հարաբերական առումով ավելի փոքր մասնաբաժին Մենք դա գրել ենք ԿԵՆԱՑԻ մեջ, այնքան լավ: Բայց այստեղ վտանգ կա՝ որքան մեծ է յուրաքանչյուր առանձին ռեկորդի «ֆիզիկական» ծավալը, այնքան «լայն» է դառնում ցուցանիշը, քանի որ մենք պետք է ավելի շատ տվյալների էջեր ծածկենք։

Բաժին փոփոխություններից առաջ:

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

Բաժին փոփոխություններից հետո:

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

Փաստորեն, մենք սկսեց գրել TOAST-ին 2 անգամ ավելի հազվադեպ, որը բեռնաթափեց ոչ միայն սկավառակը, այլև պրոցեսորը.

Նկատեմ, որ մենք նաև փոքրացել ենք սկավառակը «կարդալու» մեջ, ոչ միայն «գրելու», քանի որ աղյուսակի մեջ գրառում մտցնելիս պետք է նաև «կարդալ» յուրաքանչյուր ինդեքսի ծառի մի մասը՝ որոշելու համար ապագա դիրքերը դրանցում:

Ով կարող է լավ ապրել PostgreSQL 11-ում

PG11-ին թարմացնելուց հետո մենք որոշեցինք շարունակել «թյունինգը» TOAST-ը և նկատեցինք, որ այս տարբերակից սկսած պարամետրը. toast_tuple_target:

TOAST մշակման կոդը գործարկվում է միայն այն դեպքում, երբ աղյուսակում պահվող տողի արժեքը մեծ է TOAST_TUPLE_THRESHOLD բայթից (սովորաբար 2 ԿԲ): TOAST կոդը կսեղմի և (կամ) դաշտի արժեքները կտեղափոխի աղյուսակից դուրս, մինչև տողի արժեքը դառնա TOAST_TUPLE_TARGET բայթից պակաս (փոփոխական արժեք, սովորաբար 2 ԿԲ) կամ չափը չկրճատվի:

Մենք որոշեցինք, որ սովորաբար մեր ունեցած տվյալները կա՛մ «շատ կարճ» են, կա՛մ «շատ երկար», ուստի որոշեցինք սահմանափակվել հնարավոր նվազագույն արժեքով.

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Տեսնենք, թե ինչպես են նոր կարգավորումներն ազդել վերակազմավորումից հետո սկավառակի բեռնման վրա.

Վատ չէ! Միջին սկավառակի հերթը նվազել է մոտավորապես 1.5 անգամ, իսկ սկավառակը «զբաղված» է 20 տոկոս: Բայց միգուցե սա ինչ-որ կերպ ազդեց պրոցեսորի վրա:

Գոնե ավելի վատ չեղավ։ Չնայած, դժվար է դատել, թե արդյոք նույնիսկ նման ծավալները դեռ չեն կարող բարձրացնել միջին պրոցեսորի բեռնվածությունը 5%.

Ժամկետների տեղերը փոխելով՝ գումարը... փոխվում է։

Ինչպես գիտեք, մեկ կոպեկը խնայում է ռուբլին, և մեր պահեստային ծավալների դեպքում դա մոտավորապես է 10 ՏԲ / ամիս նույնիսկ մի փոքր օպտիմալացումը կարող է լավ շահույթ տալ: Հետևաբար, մենք ուշադրություն դարձրինք մեր տվյալների ֆիզիկական կառուցվածքին` ինչպես ճշգրիտ Գրառման մեջ «դասավոր» դաշտեր աղյուսակներից յուրաքանչյուրը:

Քանի որ պատճառով տվյալների հավասարեցում սա ուղիղ առաջ է ազդում է ստացված ծավալի վրա:

Շատ ճարտարապետություններ ապահովում են տվյալների հավասարեցում մեքենայի բառերի սահմանների վրա: Օրինակ, 32-բիթանոց x86 համակարգում ամբողջ թվերը (ամբողջ թվի տեսակը, 4 բայթ) կհավասարեցվեն 4 բայթ բառի սահմանի վրա, ինչպես նաև կկրկնապատկվեն լողացող կետի ճշգրիտ թվերը (կրկնակի ճշգրտության լողացող կետ, 8 բայթ): Իսկ 64-բիթանոց համակարգում կրկնակի արժեքները կհամապատասխանեցվեն 8 բայթ բառի սահմաններին: Սա անհամատեղելիության ևս մեկ պատճառ է։

Հավասարեցման պատճառով աղյուսակի տողի չափը կախված է դաշտերի հերթականությունից: Սովորաբար այս ազդեցությունը այնքան էլ նկատելի չէ, բայց որոշ դեպքերում դա կարող է հանգեցնել չափի զգալի աճի: Օրինակ, եթե խառնեք char(1) և ամբողջ թվերի դաշտերը, ապա դրանց միջև սովորաբար վատնվելու է 3 բայթ:

Սկսենք սինթետիկ մոդելներից.

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

Որտեղի՞ց առաջացան մի քանի լրացուցիչ բայթ առաջին դեպքում: Դա պարզ է - 2-բայթանոց փոքրագույնը հավասարեցված է 4 բայթ սահմանի վրա հաջորդ դաշտից առաջ, և երբ այն վերջինն է, հավասարեցնելու ոչինչ և կարիք չկա:

Տեսականորեն ամեն ինչ լավ է, և դուք կարող եք վերադասավորել դաշտերը, ինչպես ցանկանում եք: Եկեք ստուգենք այն իրական տվյալների վրա՝ օգտագործելով աղյուսակներից մեկի օրինակը, որի ամենօրյա բաժինը զբաղեցնում է 10-15 ԳԲ։

Սկզբնական կառուցվածք.

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)

Բաժին սյունակի կարգը փոխելուց հետո - ճիշտ նույն դաշտերը, պարզապես այլ կարգ:

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)

Բաժնի ընդհանուր ծավալը որոշվում է «փաստերի» քանակով և կախված է միայն արտաքին գործընթացներից, ուստի եկեք բաժանենք կույտի չափը (pg_relation_size) դրանում եղած գրառումների քանակով, այսինքն՝ ստանում ենք փաստացի պահված գրառումների միջին չափը:

Մինուս 6% ծավալ, Հիանալի!

Բայց ամեն ինչ, իհարկե, այնքան էլ վարդագույն չէ, ի վերջո, ինդեքսներում մենք չենք կարող փոխել դաշտերի հերթականությունըև, հետևաբար, «ընդհանուր առմամբ» (pg_total_relation_size) ...

...դեռ այստեղ էլ խնայել է 1.5%առանց կոդի մեկ տող փոխելու: Այո այո!

Նշում եմ, որ դաշտերի դասավորության վերը նշված տարբերակը այն չէ, որ ամենաօպտիմալն է։ Քանի որ դուք չեք ցանկանում «պատռել» դաշտերի որոշ բլոկներ գեղագիտական ​​պատճառներով, օրինակ՝ զույգ (pack, recno), որն այս աղյուսակի PK-ն է:

Ընդհանուր առմամբ, դաշտերի «նվազագույն» դասավորությունը որոշելը բավականին պարզ «բիրտ ուժի» խնդիր է: Հետևաբար, դուք կարող եք ավելի լավ արդյունքներ ստանալ ձեր տվյալներից, քան մերը. փորձեք այն:

Source: www.habr.com