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 மடங்கு குறைவாக அடிக்கடி எழுத ஆரம்பித்தேன், இது வட்டை மட்டுமல்ல, CPU ஐயும் இறக்கியது:

"எழுதுதல்" மட்டுமல்ல, வட்டை "படிப்பதிலும்" நாங்கள் சிறியவர்களாகிவிட்டோம் என்பதை நான் கவனிக்கிறேன் - ஒரு பதிவை அட்டவணையில் செருகும்போது, ​​​​ஒவ்வொரு குறியீட்டின் மரத்தின் பகுதியையும் தீர்மானிக்க "படிக்க" வேண்டும். அவற்றில் எதிர்கால நிலை.

PostgreSQL 11 இல் யார் நன்றாக வாழ முடியும்

PG11 க்கு புதுப்பித்த பிறகு, TOAST ஐ "டியூனிங்" செய்வதைத் தொடர முடிவு செய்தோம், மேலும் இந்த பதிப்பில் இருந்து அளவுரு ட்யூனிங்கிற்குக் கிடைத்ததைக் கவனித்தோம். toast_tuple_target:

அட்டவணையில் சேமிக்கப்பட வேண்டிய வரிசை மதிப்பு TOAST_TUPLE_THRESHOLD பைட்டுகளை (பொதுவாக 2 KB) விட அதிகமாக இருக்கும் போது மட்டுமே TOAST செயலாக்கக் குறியீடு செயல்படும். வரிசை மதிப்பு TOAST_TUPLE_TARGET பைட்டுகளை விட (மாறி மதிப்பு, பொதுவாக 2 KB) குறைவாக இருக்கும் வரை அல்லது அளவைக் குறைக்க முடியாத வரை TOAST குறியீடு புல மதிப்புகளை அட்டவணைக்கு வெளியே சுருக்கி/அல்லது நகர்த்தும்.

எங்களிடம் உள்ள தரவு "மிகக் குறுகியது" அல்லது "மிக நீளமானது" என்று நாங்கள் முடிவு செய்தோம், எனவே சாத்தியமான குறைந்தபட்ச மதிப்புக்கு நம்மை கட்டுப்படுத்த முடிவு செய்தோம்:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

மறுகட்டமைப்பிற்குப் பிறகு புதிய அமைப்புகள் வட்டு ஏற்றுதலை எவ்வாறு பாதித்தன என்பதைப் பார்ப்போம்:

மோசமாக இல்லை! சராசரி வட்டில் வரிசை குறைந்துவிட்டது தோராயமாக 1.5 மடங்கு, மற்றும் வட்டு "பிஸி" 20 சதவீதம்! ஆனால் இது எப்படியாவது CPU ஐ பாதித்திருக்கலாம்?

குறைந்தபட்சம் அது மோசமாகவில்லை. இருப்பினும், அத்தகைய தொகுதிகள் கூட சராசரி CPU சுமைகளை இன்னும் உயர்த்த முடியவில்லையா என்று தீர்ப்பது கடினம் 5%.

விதிமுறைகளின் இடங்களை மாற்றுவதன் மூலம், தொகை... மாறுகிறது!

உங்களுக்குத் தெரியும், ஒரு பைசா ஒரு ரூபிளைச் சேமிக்கிறது, மேலும் எங்கள் சேமிப்பக அளவுகளுடன் அது பற்றி 10TB/மாதம் ஒரு சிறிய தேர்வுமுறை கூட நல்ல லாபத்தை தரும். எனவே, எங்கள் தரவின் இயற்பியல் கட்டமைப்பில் கவனம் செலுத்தினோம் - எவ்வளவு சரியாக பதிவின் உள்ளே "அடுக்கப்பட்ட" புலங்கள் அட்டவணைகள் ஒவ்வொன்றும்.

ஏனெனில் தரவு சீரமைப்பு இது நேராக முன்னோக்கி உள்ளது இதன் விளைவாக வரும் அளவை பாதிக்கிறது:

பல கட்டமைப்புகள் இயந்திர வார்த்தை எல்லைகளில் தரவு சீரமைப்பை வழங்குகின்றன. எடுத்துக்காட்டாக, 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), இது இந்த அட்டவணைக்கான பி.கே.

பொதுவாக, புலங்களின் "குறைந்தபட்ச" ஏற்பாட்டைத் தீர்மானிப்பது மிகவும் எளிமையான "முரட்டுப் படை" பணியாகும். எனவே, எங்களுடைய தரவை விட சிறந்த முடிவுகளை நீங்கள் பெறலாம் - இதை முயற்சிக்கவும்!

ஆதாரம்: www.habr.com