សន្សំមួយកាក់លើបរិមាណធំនៅក្នុង 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 KB) និងមិនអនុញ្ញាតឱ្យ tuples ពង្រីកទំព័រច្រើនទេ។ ដូច្នេះ វាមិនអាចទៅរួចទេក្នុងការរក្សាទុកដោយផ្ទាល់នូវតម្លៃវាលធំខ្លាំងណាស់។ ដើម្បីយកឈ្នះលើដែនកំណត់នេះ តម្លៃវាលធំត្រូវបានបង្ហាប់ និង/ឬបំបែកតាមបន្ទាត់រូបវន្តច្រើន។ វាកើតឡើងដោយអ្នកប្រើប្រាស់មិនមានការកត់សម្គាល់ និងមានផលប៉ះពាល់តិចតួចលើកូដម៉ាស៊ីនមេភាគច្រើន។ វិធីសាស្រ្តនេះត្រូវបានគេស្គាល់ថា TOAST...

ជាការពិត សម្រាប់រាល់តារាងដែលមានវាល "មានសក្តានុពល" ដោយស្វ័យប្រវត្តិ តារាងផ្គូផ្គងជាមួយ "ចំណិត" ត្រូវបានបង្កើតឡើង កំណត់ត្រា "ធំ" នីមួយៗក្នុងផ្នែក 2KB៖

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

នោះគឺប្រសិនបើយើងត្រូវសរសេរខ្សែអក្សរដែលមានតម្លៃ "ធំ" dataបន្ទាប់មកការថតពិតប្រាកដនឹងកើតឡើង មិនត្រឹមតែតារាងមេ និង PK របស់វាប៉ុណ្ណោះទេ ប៉ុន្តែថែមទាំង TOAST និង PK របស់វាផងដែរ។.

កាត់បន្ថយឥទ្ធិពល TOAST

ប៉ុន្តែកំណត់ត្រារបស់យើងភាគច្រើននៅតែមិនធំ គួរតែសមនឹង 8KB - តើខ្ញុំអាចសន្សំលុយដោយរបៀបណា? ..

នេះគឺជាកន្លែងដែលគុណលក្ខណៈមកដល់ជំនួយរបស់យើង។ 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 KB)។ កូដ TOAST នឹងបង្រួម និង/ឬផ្លាស់ទីតម្លៃវាលចេញពីតារាង រហូតដល់តម្លៃជួរដេកតិចជាង TOAST_TUPLE_TARGET បៃ (តម្លៃអថេរ ជាធម្មតា 2 KB) ឬទំហំមិនអាចកាត់បន្ថយបានទេ។

យើងបានសម្រេចចិត្តថាទិន្នន័យដែលយើងមានជាធម្មតាគឺ "ខ្លីណាស់" ឬ "យូរណាស់" ដូច្នេះយើងបានសម្រេចចិត្តដាក់កម្រិតខ្លួនយើងទៅនឹងតម្លៃអប្បបរមាដែលអាចធ្វើបាន៖

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

សូមមើលពីរបៀបដែលការកំណត់ថ្មីប៉ះពាល់ដល់ការផ្ទុកថាសបន្ទាប់ពីការកំណត់រចនាសម្ព័ន្ធឡើងវិញ៖

មិនអាក្រក់​ទេ! មធ្យម ជួរទៅឌីសបានថយចុះ ប្រហែល 1.5 ដងហើយថាស "រវល់" គឺ 20 ភាគរយ! ប៉ុន្តែ​ប្រហែល​ជា​វា​ប៉ះពាល់​ដល់​ស៊ីភីយូ​ខ្លះ​ដែរ​ឬ​ទេ?

យ៉ាងហោចណាស់វាមិនអាក្រក់ជាងនេះទេ។ ទោះបីជា, វាពិបាកក្នុងការវិនិច្ឆ័យថាតើសូម្បីតែបរិមាណបែបនេះនៅតែមិនអាចបង្កើនការផ្ទុកស៊ីភីយូជាមធ្យមខ្ពស់ជាងនេះ។ 5%.

ដោយ​ការ​ផ្លាស់​ប្តូ​រ​កន្លែង​នៃ​លក្ខខណ្ឌ​, បូក ... ផ្លាស់​ប្តូ​រ​!

ដូចដែលអ្នកបានដឹងហើយថាកាក់មួយសន្សំបានមួយរូប ហើយជាមួយនឹងទំហំផ្ទុករបស់យើងវាជិតដល់ហើយ។ 10TB/ខែ សូម្បី​តែ​ការ​បង្កើន​ប្រសិទ្ធភាព​តិចតួច​អាច​ផ្តល់​ប្រាក់​ចំណេញ​បាន​ល្អ។ ដូច្នេះហើយ យើងបានយកចិត្តទុកដាក់លើរចនាសម្ព័ន្ធរូបវន្តនៃទិន្នន័យរបស់យើង - របៀបពិតប្រាកដ វាល "ជង់" នៅខាងក្នុងកំណត់ត្រា តារាងនីមួយៗ។

ដោយសារតែ ការតម្រឹមទិន្នន័យ នេះគឺត្រង់ទៅមុខ ប៉ះពាល់ដល់បរិមាណលទ្ធផល:

ស្ថាបត្យកម្មជាច្រើនផ្តល់នូវការតម្រឹមទិន្នន័យនៅលើព្រំដែនពាក្យម៉ាស៊ីន។ ឧទាហរណ៍ នៅលើប្រព័ន្ធ 32 ប៊ីត x86 ចំនួនគត់ (ប្រភេទចំនួនគត់ 4 បៃ) នឹងត្រូវបានតម្រឹមនៅលើព្រំដែនពាក្យ 4 បៃ ដូចនឹងលេខចំនុចអណ្តែតទឹកដែលមានភាពជាក់លាក់ទ្វេដង (ចំណុចអណ្តែតភាពជាក់លាក់ពីរដង 8 បៃ)។ ហើយនៅលើប្រព័ន្ធ 64 ប៊ីត តម្លៃទ្វេដងនឹងត្រូវបានតម្រឹមទៅព្រំដែនពាក្យ 8 បៃ។ នេះជាហេតុផលមួយទៀតនៃភាពមិនស៊ីគ្នា

ដោយសារការតម្រឹម ទំហំនៃជួរតារាងអាស្រ័យលើលំដាប់នៃវាល។ ជាធម្មតាឥទ្ធិពលនេះមិនគួរអោយកត់សំគាល់ខ្លាំងទេ ប៉ុន្តែក្នុងករណីខ្លះវាអាចនាំអោយមានការកើនឡើងយ៉ាងខ្លាំងនៃទំហំ។ ឧទាហរណ៍ ប្រសិនបើអ្នកលាយ char(1) និង integer fields នោះជាធម្មតានឹងមាន 3 bytes ខ្ជះខ្ជាយរវាងពួកវា។

ចូរចាប់ផ្តើមជាមួយនឹងគំរូសំយោគ៖

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-15GB ។

រចនាសម្ព័ន្ធដើម៖

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 សម្រាប់តារាងនេះ។

ជាទូទៅ ការកំណត់ការរៀបចំ "អប្បបរមា" នៃវាលគឺជាកិច្ចការ "កម្លាំងសាហាវ" ដ៏សាមញ្ញ។ ដូច្នេះ អ្នក​អាច​ទទួល​បាន​លទ្ធផល​កាន់​តែ​ល្អ​ពី​ទិន្នន័យ​របស់​អ្នក​ជាង​ទិន្នន័យ​របស់​យើង - សាកល្បង​វា!

ប្រភព: www.habr.com