જાવા ડેવલપરની નજર દ્વારા PostgreSQL માં અનુક્રમણિકાઓની તંદુરસ્તી

હાય

મારું નામ વાણ્યા છે અને હું જાવા ડેવલપર છું. એવું બને છે કે હું PostgreSQL સાથે ઘણું કામ કરું છું - ડેટાબેઝ સેટઅપ કરું છું, સ્ટ્રક્ચર, પ્રદર્શનને ઑપ્ટિમાઇઝ કરું છું અને સપ્તાહના અંતે થોડું DBA રમું છું.

તાજેતરમાં મેં અમારી માઈક્રો સર્વિસિસમાં ઘણા ડેટાબેસેસ ગોઠવ્યા છે અને જાવા લાઈબ્રેરી લખી છે pg-ઇન્ડેક્સ-હેલ્થ, જે આ કાર્યને સરળ બનાવે છે, મારો સમય બચાવે છે અને વિકાસકર્તાઓ દ્વારા કરવામાં આવતી કેટલીક સામાન્ય ભૂલોને ટાળવામાં મને મદદ કરે છે. આ પુસ્તકાલય છે જેની આપણે આજે વાત કરીશું.

જવાબદારીનો ઇનકાર

હું જેની સાથે કામ કરું છું તે PostgreSQL નું મુખ્ય સંસ્કરણ 10 છે. હું જે SQL ક્વેરીઝનો ઉપયોગ કરું છું તેની પણ આવૃત્તિ 11 પર પરીક્ષણ કરવામાં આવે છે. ન્યૂનતમ સપોર્ટેડ વર્ઝન 9.6 છે.

પ્રાગૈતિહાસિક

આ બધું લગભગ એક વર્ષ પહેલાં મારા માટે વિચિત્ર એવી પરિસ્થિતિ સાથે શરૂ થયું હતું: વાદળીમાંથી ઇન્ડેક્સની સ્પર્ધાત્મક રચના ભૂલ સાથે સમાપ્ત થઈ. ઇન્ડેક્સ પોતે, હંમેશની જેમ, ડેટાબેઝમાં અમાન્ય સ્થિતિમાં રહ્યો. લોગ વિશ્લેષણમાં અછત જોવા મળી temp_file_limit. અને અમે જઈએ છીએ... ઊંડા ખોદતા, મેં ડેટાબેઝ રૂપરેખાંકનમાં સમસ્યાઓનો આખો સમૂહ શોધી કાઢ્યો અને, મારી સ્લીવ્ઝને ફેરવીને, મારી આંખોમાં ચમક સાથે તેને ઠીક કરવાનું શરૂ કર્યું.

સમસ્યા એક - ડિફૉલ્ટ રૂપરેખાંકન

સંભવતઃ, દરેક વ્યક્તિ પહેલેથી જ પોસ્ટગ્રેસ વિશેના રૂપકથી ખૂબ કંટાળી ગઈ છે, જે કોફી મેકર પર ચલાવી શકાય છે, પરંતુ... ડિફોલ્ટ ગોઠવણી ખરેખર ઘણા પ્રશ્નો ઉભા કરે છે. ઓછામાં ઓછું, તેના પર ધ્યાન આપવું યોગ્ય છે જાળવણી_કામ_મેમ, temp_file_limit, સ્ટેટમેન્ટ_સમયસમાપ્ત и lock_timeout.

અમારા કિસ્સામાં જાળવણી_કામ_મેમ ડિફોલ્ટ 64 MB હતું, અને temp_file_limit લગભગ 2 જીબી - મોટા ટેબલ પર ઇન્ડેક્સ બનાવવા માટે અમારી પાસે પૂરતી મેમરી નથી.

તેથી, માં pg-ઇન્ડેક્સ-હેલ્થ મેં શ્રેણી એકત્રિત કરી ચાવી, મારા મતે, પરિમાણો કે જે દરેક ડેટાબેઝ માટે ગોઠવેલા હોવા જોઈએ.

સમસ્યા બે - ડુપ્લિકેટ અનુક્રમણિકાઓ

અમારા ડેટાબેઝ એસએસડી ડ્રાઇવ્સ પર રહે છે, અને અમે ઉપયોગ કરીએ છીએ HA- બહુવિધ ડેટા કેન્દ્રો, માસ્ટર હોસ્ટ અને સાથે ગોઠવણી n- પ્રતિકૃતિઓની સંખ્યા. ડિસ્ક સ્પેસ એ આપણા માટે ખૂબ જ મૂલ્યવાન સ્ત્રોત છે; તે કામગીરી અને CPU વપરાશ કરતાં ઓછું મહત્વનું નથી. તેથી, એક તરફ, અમને ઝડપી વાંચન માટે અનુક્રમણિકાઓની જરૂર છે, અને બીજી બાજુ, અમે ડેટાબેઝમાં બિનજરૂરી અનુક્રમણિકાઓ જોવા માંગતા નથી, કારણ કે તે જગ્યા ખાય છે અને ડેટા અપડેટિંગ ધીમું કરે છે.

અને હવે, બધું પુનઃસ્થાપિત કર્યા અમાન્ય અનુક્રમણિકાઓ અને પૂરતું જોયું ઓલેગ બાર્ટુનોવ દ્વારા અહેવાલો, મેં "મહાન" શુદ્ધિકરણનું આયોજન કરવાનું નક્કી કર્યું. તે બહાર આવ્યું છે કે વિકાસકર્તાઓ ડેટાબેઝ દસ્તાવેજો વાંચવાનું પસંદ કરતા નથી. તેમને તે બહુ ગમતું નથી. આને કારણે, બે લાક્ષણિક ભૂલો ઊભી થાય છે - પ્રાથમિક કી પર જાતે બનાવેલ અનુક્રમણિકા અને અનન્ય કૉલમ પર સમાન "મેન્યુઅલ" અનુક્રમણિકા. હકીકત એ છે કે તેમની જરૂર નથી - પોસ્ટગ્રેસ બધું જ પોતે કરશે. આવા અનુક્રમણિકાઓ સુરક્ષિત રીતે કાઢી શકાય છે, અને આ હેતુ માટે ડાયગ્નોસ્ટિક્સ દેખાયા છે ડુપ્લિકેટ_ઇન્ડેક્સ.

સમસ્યા ત્રણ - સૂચકાંકોને છેદે છે

મોટાભાગના શિખાઉ વિકાસકર્તાઓ એક કૉલમ પર અનુક્રમણિકા બનાવે છે. ધીરે ધીરે, આ વ્યવસાયને સારી રીતે ચાખ્યા પછી, લોકો તેમના પ્રશ્નોને ઑપ્ટિમાઇઝ કરવાનું શરૂ કરે છે અને વધુ જટિલ અનુક્રમણિકાઓ ઉમેરવાનું શરૂ કરે છે જેમાં ઘણી કૉલમ્સ શામેલ હોય છે. આ રીતે કૉલમ પર અનુક્રમણિકા દેખાય છે A, એ + બી, A+B+C અને તેથી વધુ. આ સૂચકાંકોમાંથી પ્રથમ બે સુરક્ષિત રીતે ફેંકી શકાય છે, કારણ કે તે ત્રીજાના ઉપસર્ગ છે. આ ઘણી બધી ડિસ્ક જગ્યા પણ બચાવે છે અને આ માટે ડાયગ્નોસ્ટિક્સ છે છેદાયેલા_ઇન્ડેક્સ.

સમસ્યા ચાર - ઇન્ડેક્સ વિના વિદેશી કીઓ

પોસ્ટગ્રેસ તમને બેકિંગ ઇન્ડેક્સનો ઉલ્લેખ કર્યા વિના વિદેશી કી અવરોધો બનાવવા માટે પરવાનગી આપે છે. ઘણી પરિસ્થિતિઓમાં આ કોઈ સમસ્યા નથી, અને તે પોતાને પ્રગટ પણ કરી શકતું નથી... હાલ માટે...

તે અમારી સાથે સમાન હતું: તે માત્ર એટલું જ છે કે અમુક સમયે નોકરી, શેડ્યૂલ અનુસાર ચાલતી અને ટેસ્ટ ઓર્ડરના ડેટાબેઝને સાફ કરતી વખતે, માસ્ટર હોસ્ટ દ્વારા અમને "ઉમેરવામાં" આવવાનું શરૂ થયું. CPU અને IO નકામા ગયા, વિનંતીઓ ધીમી પડી અને સમય સમાપ્ત થયો, સેવા પાંચસો હતી. ઝડપી વિશ્લેષણ pg_stat_activity દર્શાવે છે કે પ્રશ્નો જેમ કે:

delete from <table> where id in (…)

આ કિસ્સામાં, અલબત્ત, લક્ષ્ય કોષ્ટકમાં id દ્વારા અનુક્રમણિકા હતી, અને શરત અનુસાર ખૂબ ઓછા રેકોર્ડ્સ કાઢી નાખવામાં આવ્યા હતા. એવું લાગતું હતું કે બધું કામ કરવું જોઈએ, પરંતુ, અરે, તે થયું નહીં.

અદ્ભુત બચાવમાં આવ્યો વિશ્લેષણ સમજાવો અને જણાવ્યું હતું કે લક્ષ્ય કોષ્ટકમાં રેકોર્ડ્સ કાઢી નાખવા ઉપરાંત, એક સંદર્ભ અખંડિતતા તપાસ પણ છે, અને સંબંધિત કોષ્ટકોમાંથી એક પર આ તપાસ નિષ્ફળ જાય છે. ક્રમિક સ્કેન યોગ્ય ઇન્ડેક્સના અભાવને કારણે. આમ ડાયગ્નોસ્ટિક્સનો જન્મ થયો વિદેશી_કીઓ_વિના_ઇન્ડેક્સ.

સમસ્યા પાંચ - અનુક્રમણિકાઓમાં શૂન્ય મૂલ્ય

ડિફૉલ્ટ રૂપે, પોસ્ટગ્રેસમાં btree ઇન્ડેક્સમાં શૂન્ય મૂલ્યોનો સમાવેશ થાય છે, પરંતુ તે સામાન્ય રીતે ત્યાં જરૂરી નથી. તેથી, હું ખંતપૂર્વક આ નલ્સને ફેંકી દેવાનો પ્રયાસ કરું છું (ડાયગ્નોસ્ટિક્સ અનુક્રમણિકા_વિથ_નલ_મૂલ્યો), પ્રકાર દ્વારા રદ કરી શકાય તેવા કૉલમ પર આંશિક અનુક્રમણિકાઓ બનાવવી where <A> is not null. આ રીતે હું અમારા એક ઇન્ડેક્સનું કદ 1877 MB થી 16 KB સુધી ઘટાડી શક્યો. અને એક સેવામાં, ઇન્ડેક્સમાંથી શૂન્ય મૂલ્યોને બાકાત રાખવાને કારણે ડેટાબેઝનું કદ કુલ 16% (નિરપેક્ષ સંખ્યામાં 4.3 જીબી દ્વારા) ઘટ્યું. ખૂબ જ સરળ ફેરફારો સાથે ડિસ્ક જગ્યામાં પ્રચંડ બચત. 🙂

સમસ્યા છ - પ્રાથમિક કીનો અભાવ

મિકેનિઝમની પ્રકૃતિને કારણે Postgres માં MVCC આવી પરિસ્થિતિ શક્ય છે ફૂલેલુંજ્યારે મોટી સંખ્યામાં મૃત રેકોર્ડ્સને કારણે તમારા ટેબલનું કદ ઝડપથી વધી રહ્યું છે. હું નિષ્કપટપણે માનતો હતો કે આ અમને ધમકી આપશે નહીં, અને અમારા આધાર સાથે આવું થશે નહીં, કારણ કે અમે, વાહ!!!, સામાન્ય વિકાસકર્તાઓ છીએ... હું કેટલો મૂર્ખ અને ભોળો હતો...

એક દિવસ, એક અદ્ભુત સ્થળાંતર એક વિશાળ અને સક્રિય રીતે ઉપયોગમાં લેવાતા કોષ્ટકમાં તમામ રેકોર્ડ્સ લીધા અને અપડેટ કર્યા. અમને વાદળીમાંથી ટેબલ સાઇઝમાં +100 GB મળ્યું. તે ખૂબ જ શરમજનક હતું, પરંતુ અમારી ગેરસમજ ત્યાં સમાપ્ત થઈ નહોતી. આ ટેબલ પર ઓટોવેક્યુમ 15 કલાક પછી સમાપ્ત થયા પછી, તે સ્પષ્ટ થઈ ગયું કે ભૌતિક સ્થાન પાછું નહીં આવે. અમે સેવા બંધ કરી શક્યા નથી અને VACUUM FULL બનાવી શક્યા નથી, તેથી અમે ઉપયોગ કરવાનું નક્કી કર્યું છે pg_repack. અને પછી તે બહાર આવ્યું pg_repack પ્રાથમિક કી અથવા અન્ય વિશિષ્ટતાના અવરોધ વિના કોષ્ટકોને કેવી રીતે પ્રક્રિયા કરવી તે જાણતું નથી, અને અમારા કોષ્ટકમાં પ્રાથમિક કી નથી. આમ ડાયગ્નોસ્ટિક્સનો જન્મ થયો કોષ્ટકો_વિના_પ્રાથમિક_કી.

પુસ્તકાલય સંસ્કરણમાં 0.1.5 કોષ્ટકો અને અનુક્રમણિકાઓના બ્લોટમાંથી ડેટા એકત્રિત કરવાની અને તેને સમયસર પ્રતિસાદ આપવાની ક્ષમતા ઉમેરવામાં આવી છે.

સાત અને આઠ સમસ્યાઓ - અપૂરતી અનુક્રમણિકાઓ અને ન વપરાયેલ અનુક્રમણિકાઓ

નીચેના બે ડાયગ્નોસ્ટિક્સ છે: કોષ્ટકો_સાથે_ગુમ થયેલ_ઇન્ડેક્સ и unused_indexes - પ્રમાણમાં તાજેતરમાં તેમના અંતિમ સ્વરૂપમાં દેખાયા. મુદ્દો એ છે કે તેઓ ફક્ત લઈ અને ઉમેરી શકાતા નથી.

મેં પહેલેથી જ લખ્યું છે તેમ, અમે ઘણી પ્રતિકૃતિઓ સાથે રૂપરેખાંકનનો ઉપયોગ કરીએ છીએ, અને વિવિધ યજમાનો પર વાંચન લોડ મૂળભૂત રીતે અલગ છે. પરિણામે, પરિસ્થિતિ તારણ આપે છે કે કેટલાક યજમાનો પરના કેટલાક કોષ્ટકો અને અનુક્રમણિકાઓ વ્યવહારીક રીતે ઉપયોગમાં લેવાતા નથી, અને વિશ્લેષણ માટે તમારે ક્લસ્ટરમાંના તમામ યજમાનો પાસેથી આંકડા એકત્રિત કરવાની જરૂર છે. આંકડા રીસેટ કરો ક્લસ્ટરમાં દરેક હોસ્ટ પર પણ આ જરૂરી છે; તમે આ ફક્ત માસ્ટર પર કરી શકતા નથી.

આ અભિગમથી અમને કદી ઉપયોગમાં ન લેવાયેલ ઇન્ડેક્સને દૂર કરીને, તેમજ ભાગ્યે જ ઉપયોગમાં લેવાતા કોષ્ટકોમાં ખૂટતા સૂચકાંકો ઉમેરીને અમને અનેક દસ ગીગાબાઇટ્સ બચાવવાની મંજૂરી મળી.

નિષ્કર્ષ તરીકે

અલબત્ત, લગભગ તમામ ડાયગ્નોસ્ટિક્સ માટે તમે ગોઠવી શકો છો બાકાત યાદી. આ રીતે, તમે તમારી એપ્લિકેશનમાં તપાસને ઝડપથી અમલમાં મૂકી શકો છો, નવી ભૂલોને દેખાવાથી અટકાવી શકો છો અને પછી ધીમે ધીમે જૂની ભૂલોને ઠીક કરી શકો છો.

કેટલાક ડાયગ્નોસ્ટિક્સ ડેટાબેઝ સ્થાનાંતરણને રોલઆઉટ કર્યા પછી તરત જ કાર્યાત્મક પરીક્ષણોમાં કરી શકાય છે. અને આ કદાચ મારી લાઇબ્રેરીની સૌથી શક્તિશાળી વિશેષતાઓમાંની એક છે. ઉપયોગનું ઉદાહરણ આમાં મળી શકે છે ડેમો.

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

હું ખરેખર એવી આશા રાખું છું pg-ઇન્ડેક્સ-હેલ્થ ઉપયોગી અને માંગમાં રહેશે. તમને મળેલી સમસ્યાઓની જાણ કરીને અને નવા ડાયગ્નોસ્ટિક્સ સૂચવીને તમે પુસ્તકાલયના વિકાસમાં પણ યોગદાન આપી શકો છો.

સોર્સ: www.habr.com