በ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)
);

የተለመደው ምልክት (ቀድሞውኑ ተከፋፍሏል, በእርግጥ, ይህ የክፍል አብነት ነው), በጣም አስፈላጊው ነገር ጽሑፉ ነው. አንዳንድ ጊዜ በጣም ብዙ መጠን ያለው።

ያስታውሱ በፒጂ ውስጥ ያለው የአንድ መዝገብ "አካላዊ" መጠን ከአንድ ገጽ በላይ ውሂብ ሊይዝ አይችልም, ነገር ግን "ሎጂካዊ" መጠኑ ፍጹም የተለየ ጉዳይ ነው. የቮልሜትሪክ እሴት (ቫርቻር/ጽሑፍ/ ባይት) ወደ መስክ ለመጻፍ ይጠቀሙ TOAST ቴክኖሎጂ:

PostgreSQL ቋሚ የገጽ መጠን ይጠቀማል (በተለምዶ 8 ኪባ)፣ እና ቱፕሎች ብዙ ገጾችን እንዲያራዝሙ አይፈቅድም። ስለዚህ, በጣም ትልቅ የመስክ እሴቶችን በቀጥታ ማከማቸት አይቻልም. ይህንን ገደብ ለማሸነፍ ትላልቅ የመስክ እሴቶች ተጨምቀው እና/ወይም በበርካታ አካላዊ መስመሮች ተከፍለዋል። ይሄ የሚከሰተው በተጠቃሚው ሳይታወቅ ነው እና በአብዛኛዎቹ የአገልጋይ ኮድ ላይ ብዙም ተጽእኖ የለውም። ይህ ዘዴ TOAST በመባል ይታወቃል።

በእውነቱ, ለእያንዳንዱ ጠረጴዛ "በሚቻል ትልቅ" መስኮች, በራስ-ሰር ከ "መቁረጥ" ጋር የተጣመረ ጠረጴዛ ይፈጠራል እያንዳንዱ “ትልቅ” መዝገብ በ2KB ክፍሎች፡-

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

ማለትም "ትልቅ" እሴት ያለው ሕብረቁምፊ መፃፍ ካለብን ማለት ነው። data, ከዚያም እውነተኛው ቀረጻ ይከሰታል ወደ ዋናው ጠረጴዛ እና ፒኬ ብቻ ሳይሆን TOAST እና ፒኬ.

የTOAST ተጽእኖን በመቀነስ ላይ

ግን አብዛኛዎቹ መዝገቦቻችን አሁንም ያን ያህል ትልቅ አይደሉም። ከ 8 ኪ.ባ - በዚህ ላይ ገንዘብ እንዴት መቆጠብ እችላለሁ? ..

ባህሪው ወደ እኛ እርዳታ የሚመጣው እዚህ ላይ ነው። STORAGE በጠረጴዛው አምድ ላይ;

• ተከፍቷል ሁለቱንም መጭመቅ እና የተለየ ማከማቻ ይፈቅዳል። ይህ መደበኛ አማራጭ ለአብዛኛዎቹ TOAST ታዛዥ የውሂብ አይነቶች። በመጀመሪያ መጭመቂያውን ለማከናወን ይሞክራል, ከዚያም ረድፉ አሁንም በጣም ትልቅ ከሆነ ከጠረጴዛው ውጭ ያከማቻል.
• ዋና መጭመቅ ይፈቅዳል ነገር ግን የተለየ ማከማቻ አይደለም. (በእርግጥ፣ ለእንደዚህ አይነት አምዶች የተለየ ማከማቻ አሁንም ይከናወናል፣ ግን ብቻ እንደ የመጨረሻ አማራጭገመዱን በገጹ ላይ እንዲገጣጠም ሌላ የመቀነስ መንገድ ከሌለ።)

በእውነቱ ፣ ለጽሑፉ በትክክል የምንፈልገው ይህ ነው - በተቻለ መጠን ጨመቁት፣ እና ጨርሶ የማይመጥን ከሆነ፣ TOAST ውስጥ ያስገቡት።. ይህ በቀጥታ በራሪ ላይ ሊከናወን ይችላል, በአንድ ትዕዛዝ:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

ውጤቱን እንዴት መገምገም እንደሚቻል

የመረጃ ፍሰቱ በየቀኑ ስለሚለዋወጥ ፍፁም ቁጥሮችን ማወዳደር አንችልም ፣ ግን በአንጻራዊ ሁኔታ አነስተኛ ድርሻ በ TOAST ውስጥ ጽፈነዋል - በጣም የተሻለው ነው። ግን እዚህ አንድ አደጋ አለ - የእያንዳንዱ ግለሰብ መዝገብ ትልቅ "አካላዊ" መጠን, ጠቋሚው "ሰፊ" ይሆናል, ምክንያቱም ተጨማሪ የውሂብ ገጾችን መሸፈን አለብን.

ክፍል ለውጦች በፊት:

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_TUPLE_TARGET ባይት (ተለዋዋጭ እሴት፣ እንዲሁም አብዛኛውን ጊዜ 2 ኪባ) እስኪቀንስ ወይም መጠኑን መቀነስ እስካልቻል ድረስ የTOAST ኮድ የመስክ እሴቶችን ከሠንጠረዡ ያስወጣል።

እኛ ብዙውን ጊዜ ያለን መረጃ “በጣም አጭር” ወይም “በጣም ረጅም” እንደሆነ ወስነናል፣ ስለዚህ እራሳችንን በተቻለ መጠን በትንሹ ለመገደብ ወስነናል፡

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

አዲሶቹ ቅንጅቶች እንደገና ከተዋቀሩ በኋላ የዲስክን ጭነት እንዴት እንደጎዱ እንይ፡-

መጥፎ አይደለም! አማካኝ ወደ ዲስክ ያለው ወረፋ ቀንሷል በግምት 1.5 ጊዜ, እና ዲስኩ "የተጨናነቀ" 20 በመቶ ነው! ግን ይህ በሆነ መንገድ ሲፒዩውን ነካው?

ቢያንስ የባሰ አልሆነም። ምንም እንኳን ፣ እንደዚህ ያሉ መጠኖች እንኳን አሁንም አማካይ የሲፒዩ ጭነት ከፍ ማድረግ ካልቻሉ ለመፍረድ አስቸጋሪ ነው። 5%.

የቃላቶቹን ቦታዎች በመቀየር, ድምር ... ይለወጣል!

እንደሚያውቁት አንድ ሳንቲም አንድ ሩብል ይቆጥባል፣ እና ከማከማቻ ጥራዞች ጋር በወር 10 ቴባ ትንሽ ማመቻቸት እንኳን ጥሩ ትርፍ ሊሰጥ ይችላል. ስለዚህ, ለመረጃዎቻችን አካላዊ መዋቅር ትኩረት ሰጥተናል - እንዴት በትክክል በመዝገቡ ውስጥ "የተደራረቡ" መስኮች እያንዳንዱ ጠረጴዛዎች.

ምክንያቱም የውሂብ አሰላለፍ ይህ በቀጥታ ወደ ፊት ነው የውጤቱን መጠን ይነካል:

ብዙ አርክቴክቸር በማሽን ቃል ድንበሮች ላይ የመረጃ አሰላለፍ ይሰጣሉ። ለምሳሌ፣ በ32-ቢት x86 ሲስተም ኢንቲጀር (ኢንቲጀር ዓይነት፣ 4 ባይት) በ4-ባይት የቃላት ወሰን ላይ ይስተካከላሉ፣ እንዲሁም በእጥፍ ትክክለኛነት ተንሳፋፊ ነጥብ ቁጥሮች (ድርብ ትክክለኛነት ተንሳፋፊ ነጥብ ፣ 8 ባይት)። እና በ 64-ቢት ስርዓት ፣ ድርብ እሴቶች ከ 8-ባይት የቃላት ወሰኖች ጋር ይጣጣማሉ። ይህ ሌላው ተኳሃኝ አለመሆን ምክንያት ነው።

በአሰላለፍ ምክንያት የጠረጴዛው ረድፍ መጠን በመስኮቹ ቅደም ተከተል ይወሰናል. ብዙውን ጊዜ ይህ ተፅዕኖ በጣም የሚታይ አይደለም, ነገር ግን በአንዳንድ ሁኔታዎች ከፍተኛ መጠን መጨመር ሊያስከትል ይችላል. ለምሳሌ፣ ቻር(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 ነው.

በአጠቃላይ፣ የመስኮችን “አነስተኛ” አቀማመጥ መወሰን በጣም ቀላል “የጭካኔ ኃይል” ተግባር ነው። ስለዚህ ከውሂብዎ ከእኛ የተሻለ ውጤት ሊያገኙ ይችላሉ - ይሞክሩት!

ምንጭ: hab.com