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

એક લાક્ષણિક ચિહ્ન (પહેલેથી જ વિભાગ કરેલ છે, અલબત્ત, તેથી આ એક વિભાગનો નમૂનો છે), જ્યાં સૌથી મહત્વની વસ્તુ ટેક્સ્ટ છે. ક્યારેક તદ્દન પ્રચંડ.

યાદ કરો કે પીજીમાં એક રેકોર્ડનું "ભૌતિક" કદ ડેટાના એક કરતાં વધુ પૃષ્ઠને રોકી શકતું નથી, પરંતુ "તાર્કિક" કદ સંપૂર્ણપણે અલગ બાબત છે. ફીલ્ડમાં વોલ્યુમેટ્રિક મૂલ્ય (varchar/text/bytea) લખવા માટે, ઉપયોગ કરો TOAST ટેકનોલોજી:

PostgreSQL નિશ્ચિત પૃષ્ઠ કદ (સામાન્ય રીતે 8 KB) નો ઉપયોગ કરે છે, અને ટ્યુપલ્સને બહુવિધ પૃષ્ઠોને ફેલાવવાની મંજૂરી આપતું નથી. તેથી, ખૂબ મોટા ફીલ્ડ મૂલ્યોને સીધા સંગ્રહિત કરવું અશક્ય છે. આ મર્યાદાને દૂર કરવા માટે, મોટા ક્ષેત્રના મૂલ્યોને સંકુચિત અને/અથવા બહુવિધ ભૌતિક રેખાઓમાં વિભાજિત કરવામાં આવે છે. આ વપરાશકર્તા દ્વારા કોઈના ધ્યાન વિના થાય છે અને મોટાભાગના સર્વર કોડ પર તેની ઓછી અસર પડે છે. આ પદ્ધતિ TOAST તરીકે ઓળખાય છે...

વાસ્તવમાં, "સંભવિત રૂપે મોટા" ક્ષેત્રો સાથેના દરેક કોષ્ટક માટે, આપમેળે "સ્લાઇસિંગ" સાથે જોડી ટેબલ બનાવવામાં આવે છે 2KB સેગમેન્ટમાં દરેક "મોટો" રેકોર્ડ:

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

એટલે કે, જો આપણે "મોટા" મૂલ્ય સાથે સ્ટ્રિંગ લખવાની હોય data, પછી વાસ્તવિક રેકોર્ડિંગ થશે માત્ર મુખ્ય ટેબલ અને તેના પીકે માટે જ નહીં, પણ ટોસ્ટ અને તેના પીકે માટે પણ.

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 પ્રોસેસિંગ કોડ ત્યારે જ ફાયર થાય છે જ્યારે કોષ્ટકમાં સંગ્રહિત કરવાની પંક્તિની કિંમત TOAST_TUPLE_THRESHOLD બાઇટ્સ (સામાન્ય રીતે 2 KB) કરતાં મોટી હોય. જ્યાં સુધી પંક્તિનું મૂલ્ય 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-બાઈટ શબ્દ સીમાઓ સાથે સંરેખિત થશે. આ અસંગતતાનું બીજું કારણ છે.

સંરેખણને લીધે, કોષ્ટકની હરોળનું કદ ક્ષેત્રોના ક્રમ પર આધારિત છે. સામાન્ય રીતે આ અસર ખૂબ જ નોંધનીય નથી, પરંતુ કેટલાક કિસ્સાઓમાં તે કદમાં નોંધપાત્ર વધારો તરફ દોરી શકે છે. ઉદાહરણ તરીકે, જો તમે 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-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