PostgreSQLలో పెద్ద వాల్యూమ్‌లలో ఒక పెన్నీ ఆదా చేయండి

ద్వారా లేవనెత్తిన పెద్ద డేటా స్ట్రీమ్‌లను రికార్డ్ చేసే అంశాన్ని కొనసాగిస్తోంది విభజన గురించి మునుపటి వ్యాసం, దీనిలో మీరు చేయగల మార్గాలను మేము పరిశీలిస్తాము నిల్వ చేయబడిన "భౌతిక" పరిమాణాన్ని తగ్గించండి PostgreSQLలో, మరియు సర్వర్ పనితీరుపై వాటి ప్రభావం.

గురించి మాట్లాడతాం TOAST సెట్టింగ్‌లు మరియు డేటా అమరిక. "సగటున," ఈ పద్ధతులు చాలా వనరులను సేవ్ చేయవు, కానీ అప్లికేషన్ కోడ్‌ను సవరించకుండానే.

అయినప్పటికీ, మా అనుభవం ఈ విషయంలో చాలా ఉత్పాదకతను కలిగి ఉంది, ఎందుకంటే దాని స్వభావం ద్వారా దాదాపు ఏదైనా పర్యవేక్షణ నిల్వ ఉంటుంది ఎక్కువగా అనుబంధం-మాత్రమే రికార్డ్ చేయబడిన డేటా పరంగా. మరియు మీరు బదులుగా డిస్క్‌కి వ్రాయడానికి డేటాబేస్‌ను ఎలా నేర్పించవచ్చు అని మీరు ఆలోచిస్తున్నట్లయితే 200MB / s సగం ఎక్కువ - దయచేసి పిల్లి కింద.

పెద్ద డేటా యొక్క చిన్న రహస్యాలు

జాబ్ ప్రొఫైల్ ద్వారా మా సేవ, వారు తరచుగా గుహల నుండి అతని వద్దకు ఎగురుతారు టెక్స్ట్ ప్యాకేజీలు.

మరియు అప్పటి నుండి VLSI కాంప్లెక్స్మేము పర్యవేక్షించే డేటాబేస్ అనేది సంక్లిష్ట డేటా స్ట్రక్చర్‌లతో కూడిన బహుళ-భాగాల ఉత్పత్తి, ఆపై ప్రశ్నలు గరిష్ట పనితీరు కోసం చాలా ఈ విధంగా మారుతుంది సంక్లిష్టమైన అల్గారిథమిక్ లాజిక్‌తో "మల్టీ-వాల్యూమ్". కాబట్టి మాకు వచ్చిన లాగ్‌లోని అభ్యర్థన యొక్క ప్రతి వ్యక్తి సందర్భం లేదా ఫలితంగా అమలు ప్రణాళిక "సగటున" చాలా పెద్దదిగా మారుతుంది.

మనం “రా” డేటాను వ్రాసే పట్టికలలో ఒకదాని నిర్మాణాన్ని చూద్దాం - అంటే, లాగ్ ఎంట్రీ నుండి అసలు వచనం ఇక్కడ ఉంది:

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

ఒక సాధారణ సంకేతం (ఇప్పటికే విభజించబడింది, కాబట్టి ఇది విభాగం టెంప్లేట్), ఇక్కడ అత్యంత ముఖ్యమైన విషయం టెక్స్ట్. కొన్నిసార్లు చాలా పెద్దది.

PGలోని ఒక రికార్డ్ యొక్క “భౌతిక” పరిమాణం డేటా యొక్క ఒకటి కంటే ఎక్కువ పేజీలను ఆక్రమించలేదని గుర్తుంచుకోండి, అయితే “లాజికల్” పరిమాణం పూర్తిగా భిన్నమైన విషయం. ఫీల్డ్‌కు వాల్యూమెట్రిక్ విలువను (వర్చార్/టెక్స్ట్/బైటీ) వ్రాయడానికి, ఉపయోగించండి 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 కంప్లైంట్ డేటా రకాల కోసం. ఇది మొదట కుదింపును నిర్వహించడానికి ప్రయత్నిస్తుంది, ఆపై అడ్డు వరుస చాలా పెద్దదిగా ఉంటే దానిని టేబుల్ వెలుపల నిల్వ చేస్తుంది.
• HAND కుదింపును అనుమతిస్తుంది కానీ ప్రత్యేక నిల్వ కాదు. (వాస్తవానికి, అటువంటి నిలువు వరుసల కోసం ప్రత్యేక నిల్వ ఇప్పటికీ నిర్వహించబడుతుంది, కానీ మాత్రమే చివరి ప్రయత్నంగా, స్ట్రింగ్‌ను కుదించడానికి వేరే మార్గం లేనప్పుడు అది పేజీకి సరిపోతుంది.)

నిజానికి, టెక్స్ట్ కోసం మనకు కావలసింది ఇదే - దీన్ని వీలైనంత వరకు కుదించండి మరియు అది అస్సలు సరిపోకపోతే, దానిని 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-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