ഒരു ജാവ ഡെവലപ്പറുടെ കണ്ണിലൂടെ PostgreSQL-ലെ സൂചികകളുടെ ആരോഗ്യം

ഹായ്

എന്റെ പേര് വന്യ, ഞാൻ ഒരു ജാവ ഡെവലപ്പർ ആണ്. PostgreSQL-ൽ ഞാൻ വളരെയധികം പ്രവർത്തിക്കുന്നു - ഡാറ്റാബേസ് സജ്ജീകരിക്കുക, ഘടന, പ്രകടനം ഒപ്റ്റിമൈസ് ചെയ്യുക, വാരാന്ത്യങ്ങളിൽ കുറച്ച് DBA പ്ലേ ചെയ്യുക.

അടുത്തിടെ ഞാൻ ഞങ്ങളുടെ മൈക്രോസർവീസുകളിൽ നിരവധി ഡാറ്റാബേസുകൾ ക്രമീകരിച്ച് ഒരു ജാവ ലൈബ്രറി എഴുതി pg-index-health, ഇത് ഈ ജോലി എളുപ്പമാക്കുകയും എന്റെ സമയം ലാഭിക്കുകയും ഡവലപ്പർമാർ ചെയ്യുന്ന ചില സാധാരണ തെറ്റുകൾ ഒഴിവാക്കാൻ എന്നെ സഹായിക്കുകയും ചെയ്യുന്നു. ഈ ലൈബ്രറിയെക്കുറിച്ചാണ് നമ്മൾ ഇന്ന് സംസാരിക്കുന്നത്.

നിരാകരണം

ഞാൻ പ്രവർത്തിക്കുന്ന PostgreSQL-ന്റെ പ്രധാന പതിപ്പ് 10 ആണ്. ഞാൻ ഉപയോഗിക്കുന്ന എല്ലാ SQL അന്വേഷണങ്ങളും പതിപ്പ് 11-ലും പരീക്ഷിച്ചു. ഏറ്റവും കുറഞ്ഞ പിന്തുണയുള്ള പതിപ്പ് 9.6 ആണ്.

ചരിത്രാതീതകാലം

ഇതെല്ലാം ഏതാണ്ട് ഒരു വർഷം മുമ്പ് എനിക്ക് വിചിത്രമായ ഒരു സാഹചര്യത്തിലാണ് ആരംഭിച്ചത്: നീലയിൽ നിന്ന് ഒരു സൂചികയുടെ മത്സരാധിഷ്ഠിത സൃഷ്ടി ഒരു പിശകോടെ അവസാനിച്ചു. സൂചിക തന്നെ, പതിവുപോലെ, അസാധുവായ അവസ്ഥയിൽ ഡാറ്റാബേസിൽ തുടർന്നു. ലോഗ് വിശകലനം ഒരു കുറവ് കാണിച്ചു temp_file_limit. ഞങ്ങൾ പോകുന്നു... കൂടുതൽ ആഴത്തിൽ കുഴിച്ചുനോക്കിയപ്പോൾ, ഡാറ്റാബേസ് കോൺഫിഗറേഷനിൽ ഒരു കൂട്ടം പ്രശ്‌നങ്ങൾ ഞാൻ കണ്ടെത്തി, എന്റെ കൈകൾ ചുരുട്ടി, എന്റെ കണ്ണുകളിൽ ഒരു തിളക്കത്തോടെ അവ പരിഹരിക്കാൻ തുടങ്ങി.

പ്രശ്നം ഒന്ന് - ഡിഫോൾട്ട് കോൺഫിഗറേഷൻ

ഒരു കോഫി മേക്കറിൽ പ്രവർത്തിപ്പിക്കാവുന്ന പോസ്റ്റ്‌ഗ്രെസിനെക്കുറിച്ചുള്ള രൂപകത്തിൽ എല്ലാവരും മടുത്തു. കുറഞ്ഞത്, ഇത് ശ്രദ്ധിക്കേണ്ടതാണ് മെയിന്റനൻസ്_വർക്ക്_മെം, temp_file_limit, സ്റ്റേറ്റ്മെന്റ്_ടൈമൗട്ട് и ലോക്ക്_ടൈംഔട്ട്.

ഞങ്ങളുടെ കാര്യത്തിൽ മെയിന്റനൻസ്_വർക്ക്_മെം സ്ഥിരസ്ഥിതി 64 MB ആയിരുന്നു, കൂടാതെ temp_file_limit ഏകദേശം 2 GB - ഒരു വലിയ ടേബിളിൽ ഒരു സൂചിക സൃഷ്ടിക്കാൻ ഞങ്ങൾക്ക് മതിയായ മെമ്മറി ഇല്ലായിരുന്നു.

അതിനാൽ, ഇൻ pg-index-health ഞാൻ ഒരു പരമ്പര ശേഖരിച്ചു താക്കോൽ, എന്റെ അഭിപ്രായത്തിൽ, ഓരോ ഡാറ്റാബേസിനും കോൺഫിഗർ ചെയ്യേണ്ട പരാമീറ്ററുകൾ.

പ്രശ്നം രണ്ട് - ഡ്യൂപ്ലിക്കേറ്റ് സൂചികകൾ

ഞങ്ങളുടെ ഡാറ്റാബേസുകൾ SSD ഡ്രൈവുകളിൽ പ്രവർത്തിക്കുന്നു, ഞങ്ങൾ ഉപയോഗിക്കുന്നു HAഒന്നിലധികം ഡാറ്റാ സെന്ററുകൾ, മാസ്റ്റർ ഹോസ്റ്റ് എന്നിവയുള്ള കോൺഫിഗറേഷൻ n- പകർപ്പുകളുടെ എണ്ണം. ഡിസ്ക് സ്പേസ് ഞങ്ങൾക്ക് വളരെ വിലപ്പെട്ട ഒരു വിഭവമാണ്; പ്രകടനത്തേക്കാളും സിപിയു ഉപഭോഗത്തേക്കാളും പ്രാധാന്യമില്ല. അതിനാൽ, ഒരു വശത്ത്, വേഗത്തിൽ വായിക്കുന്നതിനുള്ള സൂചികകൾ ഞങ്ങൾക്ക് ആവശ്യമാണ്, മറുവശത്ത്, ഡാറ്റാബേസിൽ അനാവശ്യ സൂചികകൾ കാണാൻ ഞങ്ങൾ ആഗ്രഹിക്കുന്നില്ല, കാരണം അവ ഇടം കഴിക്കുകയും ഡാറ്റ അപ്‌ഡേറ്റ് മന്ദഗതിയിലാക്കുകയും ചെയ്യുന്നു.

ഇപ്പോൾ, എല്ലാം പുനഃസ്ഥാപിച്ചു അസാധുവായ സൂചികകൾ കണ്ടിട്ടു മതി ഒലെഗ് ബാർറ്റുനോവ് റിപ്പോർട്ട് ചെയ്യുന്നു, ഒരു "മഹത്തായ" ശുദ്ധീകരണം സംഘടിപ്പിക്കാൻ ഞാൻ തീരുമാനിച്ചു. ഡാറ്റാബേസ് ഡോക്യുമെന്റേഷൻ വായിക്കാൻ ഡവലപ്പർമാർ ഇഷ്ടപ്പെടുന്നില്ലെന്ന് ഇത് മാറി. അവർക്കത് തീരെ ഇഷ്ടമല്ല. ഇക്കാരണത്താൽ, രണ്ട് സാധാരണ പിശകുകൾ ഉണ്ടാകുന്നു - ഒരു പ്രാഥമിക കീയിൽ സ്വമേധയാ സൃഷ്ടിച്ച സൂചികയും ഒരു അദ്വിതീയ കോളത്തിൽ സമാനമായ "മാനുവൽ" സൂചികയും. അവ ആവശ്യമില്ല എന്നതാണ് വസ്തുത - പോസ്റ്റ്ഗ്രെസ് എല്ലാം സ്വയം ചെയ്യും. അത്തരം സൂചികകൾ സുരക്ഷിതമായി ഇല്ലാതാക്കാൻ കഴിയും, ഈ ആവശ്യത്തിനായി ഡയഗ്നോസ്റ്റിക്സ് പ്രത്യക്ഷപ്പെട്ടു ഡ്യൂപ്ലിക്കേറ്റഡ്_ഇൻഡക്സുകൾ.

പ്രശ്നം മൂന്ന് - സൂചികകൾ വിഭജിക്കുന്നു

മിക്ക തുടക്കക്കാരായ ഡെവലപ്പർമാരും ഒരൊറ്റ നിരയിൽ സൂചികകൾ സൃഷ്ടിക്കുന്നു. ക്രമേണ, ഈ ബിസിനസ്സ് നന്നായി അനുഭവിച്ച ശേഷം, ആളുകൾ അവരുടെ ചോദ്യങ്ങൾ ഒപ്റ്റിമൈസ് ചെയ്യാൻ തുടങ്ങുകയും നിരവധി നിരകൾ ഉൾപ്പെടുന്ന കൂടുതൽ സങ്കീർണ്ണമായ സൂചികകൾ ചേർക്കുകയും ചെയ്യുന്നു. നിരകളിലെ സൂചികകൾ ഇങ്ങനെയാണ് ദൃശ്യമാകുന്നത് A, A + B., A+B+C ഇത്യാദി. ഈ സൂചികകളിൽ ആദ്യത്തെ രണ്ട്, മൂന്നാമത്തേതിന്റെ പ്രിഫിക്‌സുകൾ ആയതിനാൽ സുരക്ഷിതമായി എറിയാൻ കഴിയും. ഇത് ധാരാളം ഡിസ്ക് സ്ഥലവും ലാഭിക്കുന്നു, ഇതിനായി ഡയഗ്നോസ്റ്റിക്സ് ഉണ്ട് intersected_indexes.

പ്രശ്നം നാല് - സൂചികകളില്ലാത്ത വിദേശ കീകൾ

ഒരു ബാക്കിംഗ് സൂചിക വ്യക്തമാക്കാതെ തന്നെ വിദേശ കീ നിയന്ത്രണങ്ങൾ സൃഷ്ടിക്കാൻ Postgres നിങ്ങളെ അനുവദിക്കുന്നു. പല സാഹചര്യങ്ങളിലും ഇത് ഒരു പ്രശ്‌നമല്ല, അത് സ്വയം പ്രകടമാകാൻ പോലുമാകില്ല... തൽക്കാലം...

ഞങ്ങളുടെ കാര്യത്തിലും ഇതുതന്നെയായിരുന്നു: ചില സമയങ്ങളിൽ ഒരു ജോലി, ഒരു ഷെഡ്യൂൾ അനുസരിച്ച് പ്രവർത്തിക്കുകയും ടെസ്റ്റ് ഓർഡറുകളുടെ ഡാറ്റാബേസ് മായ്‌ക്കുകയും ചെയ്യുന്ന ഒരു ജോലി, മാസ്റ്റർ ഹോസ്റ്റ് ഞങ്ങളിലേക്ക് "ചേർക്കാൻ" തുടങ്ങി. സിപിയുവും ഐഒയും പാഴായി, അഭ്യർത്ഥനകൾ മന്ദഗതിയിലായി, സമയം കഴിഞ്ഞു, സേവനം അഞ്ഞൂറായി. ദ്രുത വിശകലനം pg_stat_activity ഇതുപോലുള്ള ചോദ്യങ്ങൾ കാണിച്ചു:

delete from <table> where id in (…)

ഈ സാഹചര്യത്തിൽ, തീർച്ചയായും, ടാർഗെറ്റ് ടേബിളിൽ ഐഡി പ്രകാരം ഒരു സൂചിക ഉണ്ടായിരുന്നു, കൂടാതെ വ്യവസ്ഥ അനുസരിച്ച് വളരെ കുറച്ച് റെക്കോർഡുകൾ ഇല്ലാതാക്കി. എല്ലാം പ്രവർത്തിക്കണമെന്ന് തോന്നി, പക്ഷേ, അയ്യോ, അത് ചെയ്തില്ല.

അത്ഭുതകരമായവൻ രക്ഷയ്ക്കെത്തി വിശകലനം വിശദീകരിക്കുക ടാർഗെറ്റ് ടേബിളിലെ റെക്കോർഡുകൾ ഇല്ലാതാക്കുന്നതിനു പുറമേ, ഒരു റഫറൻഷ്യൽ ഇന്റഗ്രിറ്റി പരിശോധനയും ഉണ്ടെന്നും, ബന്ധപ്പെട്ട ടേബിളുകളിലൊന്നിൽ ഈ പരിശോധന പരാജയപ്പെടുമെന്നും പറഞ്ഞു. തുടർച്ചയായ സ്കാൻ അനുയോജ്യമായ ഒരു സൂചികയുടെ അഭാവം കാരണം. അങ്ങനെ ഡയഗ്നോസ്റ്റിക്സ് പിറന്നു സൂചികയില്ലാത്ത_വിദേശ_കീകൾ.

പ്രശ്നം അഞ്ച് - സൂചികകളിലെ അസാധുവായ മൂല്യം

സ്ഥിരസ്ഥിതിയായി, പോസ്റ്റ്ഗ്രേസിൽ ബിട്രീ സൂചികകളിൽ അസാധുവായ മൂല്യങ്ങൾ ഉൾപ്പെടുന്നു, പക്ഷേ അവ സാധാരണയായി അവിടെ ആവശ്യമില്ല. അതിനാൽ, ഈ ശൂന്യതകൾ (ഡയഗ്നോസ്റ്റിക്സ്) തള്ളിക്കളയാൻ ഞാൻ ഉത്സാഹത്തോടെ ശ്രമിക്കുന്നു null_values_with_indexes), തരം അനുസരിച്ച് അസാധുവാക്കാവുന്ന നിരകളിൽ ഭാഗിക സൂചികകൾ സൃഷ്ടിക്കുന്നു where <A> is not null. ഈ രീതിയിൽ, ഞങ്ങളുടെ ഒരു സൂചികയുടെ വലുപ്പം 1877 MB-യിൽ നിന്ന് 16 KB ആയി കുറയ്ക്കാൻ എനിക്ക് കഴിഞ്ഞു. ഒരു സേവനത്തിൽ, സൂചികകളിൽ നിന്ന് അസാധുവായ മൂല്യങ്ങൾ ഒഴിവാക്കിയതിനാൽ ഡാറ്റാബേസ് വലുപ്പം മൊത്തത്തിൽ 16% (കേവല സംഖ്യകളിൽ 4.3 GB) കുറഞ്ഞു. വളരെ ലളിതമായ പരിഷ്‌ക്കരണങ്ങളോടെ ഡിസ്‌ക് സ്‌പെയ്‌സിൽ വലിയ ലാഭം. 🙂

പ്രശ്നം ആറ് - പ്രാഥമിക കീകളുടെ അഭാവം

മെക്കാനിസത്തിന്റെ സ്വഭാവം കാരണം പോസ്റ്റ്ഗ്രേസിലെ എം.വി.സി.സി ഇതുപോലുള്ള ഒരു സാഹചര്യം സാധ്യമാണ് വീർക്കുകനിങ്ങളുടെ ടേബിളിന്റെ വലിപ്പം വൻതോതിൽ ഡെഡ് റെക്കോർഡുകൾ കാരണം അതിവേഗം വളരുമ്പോൾ. ഇത് ഞങ്ങളെ ഭീഷണിപ്പെടുത്തില്ലെന്നും ഞങ്ങളുടെ അടിത്തറയ്ക്ക് ഇത് സംഭവിക്കില്ലെന്നും ഞാൻ നിഷ്കളങ്കമായി വിശ്വസിച്ചു, കാരണം ഞങ്ങൾ, കൊള്ളാം!!!, ഞങ്ങൾ സാധാരണ ഡെവലപ്പർമാരാണ്... ഞാൻ എത്ര മണ്ടനും നിഷ്കളങ്കനുമായിരുന്നു...

ഒരു ദിവസം, ഒരു അത്ഭുതകരമായ മൈഗ്രേഷൻ എടുത്ത് വലുതും സജീവമായി ഉപയോഗിക്കുന്നതുമായ ഒരു പട്ടികയിലെ എല്ലാ റെക്കോർഡുകളും അപ്ഡേറ്റ് ചെയ്തു. ടേബിൾ വലുപ്പത്തിൽ നിന്ന് ഞങ്ങൾക്ക് +100 GB ലഭിച്ചു. ഇത് ഒരു നാണക്കേടായിരുന്നു, പക്ഷേ ഞങ്ങളുടെ സാഹസങ്ങൾ അവിടെ അവസാനിച്ചില്ല. ഈ ടേബിളിലെ ഓട്ടോവാക്വം 15 മണിക്കൂർ കഴിഞ്ഞ് അവസാനിച്ചതിന് ശേഷം, ഫിസിക്കൽ ലൊക്കേഷൻ തിരികെ വരില്ലെന്ന് വ്യക്തമായി. ഞങ്ങൾക്ക് സേവനം നിർത്താനും വാക്വം ഫുൾ ആക്കാനും കഴിഞ്ഞില്ല, അതിനാൽ ഞങ്ങൾ ഉപയോഗിക്കാൻ തീരുമാനിച്ചു pg_repack. എന്നിട്ട് അത് മാറി pg_repack ഒരു പ്രൈമറി കീയോ മറ്റ് അദ്വിതീയതയോ ഇല്ലാതെ ടേബിളുകൾ എങ്ങനെ പ്രോസസ്സ് ചെയ്യണമെന്ന് അറിയില്ല, ഞങ്ങളുടെ ടേബിളിന് ഒരു പ്രാഥമിക കീ ഇല്ലായിരുന്നു. അങ്ങനെ ഡയഗ്നോസ്റ്റിക്സ് പിറന്നു ടേബിളുകൾ_പ്രൈമറി_കീ ഇല്ലാതെ.

ലൈബ്രറി പതിപ്പിൽ 0.1.5 ടേബിളുകളുടെയും ഇൻഡക്സുകളുടെയും ബ്ലോട്ടിൽ നിന്ന് ഡാറ്റ ശേഖരിക്കാനും സമയബന്ധിതമായി പ്രതികരിക്കാനുമുള്ള കഴിവ് ചേർത്തു.

ഏഴ്, എട്ട് പ്രശ്നങ്ങൾ - അപര്യാപ്തമായ സൂചികകളും ഉപയോഗിക്കാത്ത സൂചികകളും

ഇനിപ്പറയുന്ന രണ്ട് ഡയഗ്നോസ്റ്റിക്സ് ഇവയാണ്: പട്ടികകൾ_നഷ്ടപ്പെട്ട_സൂചികകൾ и ഉപയോഗിക്കാത്ത_സൂചികകൾ - താരതമ്യേന അടുത്തിടെ അവരുടെ അന്തിമ രൂപത്തിൽ പ്രത്യക്ഷപ്പെട്ടു. അവ എടുത്ത് ചേർക്കാൻ കഴിഞ്ഞില്ല എന്നതാണ് കാര്യം.

ഞാൻ ഇതിനകം എഴുതിയതുപോലെ, ഞങ്ങൾ നിരവധി പകർപ്പുകളുള്ള ഒരു കോൺഫിഗറേഷൻ ഉപയോഗിക്കുന്നു, കൂടാതെ വ്യത്യസ്ത ഹോസ്റ്റുകളിലെ വായന ലോഡ് അടിസ്ഥാനപരമായി വ്യത്യസ്തമാണ്. തൽഫലമായി, ചില ഹോസ്റ്റുകളിലെ ചില പട്ടികകളും സൂചികകളും പ്രായോഗികമായി ഉപയോഗിക്കുന്നില്ലെന്ന് സാഹചര്യം മാറുന്നു, വിശകലനത്തിനായി നിങ്ങൾ ക്ലസ്റ്ററിലെ എല്ലാ ഹോസ്റ്റുകളിൽ നിന്നും സ്ഥിതിവിവരക്കണക്കുകൾ ശേഖരിക്കേണ്ടതുണ്ട്. സ്ഥിതിവിവരക്കണക്കുകൾ പുനഃസജ്ജമാക്കുക ക്ലസ്റ്ററിലെ എല്ലാ ഹോസ്റ്റിലും ഇത് ആവശ്യമാണ്; നിങ്ങൾക്ക് മാസ്റ്ററിൽ മാത്രം ഇത് ചെയ്യാൻ കഴിയില്ല.

ഈ സമീപനം ഒരിക്കലും ഉപയോഗിക്കാത്ത സൂചികകൾ നീക്കം ചെയ്യുന്നതിലൂടെയും അപൂർവ്വമായി ഉപയോഗിക്കുന്ന പട്ടികകളിലേക്ക് വിട്ടുപോയ സൂചികകൾ ചേർക്കുന്നതിലൂടെയും നിരവധി പതിനായിരക്കണക്കിന് ജിഗാബൈറ്റുകൾ ലാഭിക്കാൻ ഞങ്ങളെ അനുവദിച്ചു.

ഒരു നിഗമനത്തിൽ

തീർച്ചയായും, മിക്കവാറും എല്ലാ ഡയഗ്നോസ്റ്റിക്സിനും നിങ്ങൾക്ക് ക്രമീകരിക്കാൻ കഴിയും ഒഴിവാക്കൽ പട്ടിക. ഇതുവഴി, നിങ്ങളുടെ ആപ്ലിക്കേഷനിൽ പരിശോധനകൾ വേഗത്തിൽ നടപ്പിലാക്കാനും പുതിയ പിശകുകൾ ദൃശ്യമാകുന്നത് തടയാനും പഴയവ ക്രമേണ പരിഹരിക്കാനും കഴിയും.

ഡാറ്റാബേസ് മൈഗ്രേഷനുകൾ പുറത്തിറക്കിയ ഉടൻ തന്നെ ഫങ്ഷണൽ ടെസ്റ്റുകളിൽ ചില ഡയഗ്നോസ്റ്റിക്സ് നടത്താം. ഇത് ഒരുപക്ഷേ എന്റെ ലൈബ്രറിയുടെ ഏറ്റവും ശക്തമായ സവിശേഷതകളിൽ ഒന്നാണ്. ഉപയോഗത്തിന്റെ ഒരു ഉദാഹരണം ഇതിൽ കാണാം ഡെമോ.

ഉപയോഗിക്കാത്തതോ നഷ്‌ടമായതോ ആയ സൂചികകൾ, അതുപോലെ തന്നെ ബ്ലാറ്റ് എന്നിവയ്ക്കായി ഒരു യഥാർത്ഥ ഡാറ്റാബേസിൽ മാത്രം പരിശോധനകൾ നടത്തുന്നത് യുക്തിസഹമാണ്. ശേഖരിച്ച മൂല്യങ്ങൾ ഇതിൽ രേഖപ്പെടുത്താം ക്ലിക്ക്ഹ ouse സ് അല്ലെങ്കിൽ മോണിറ്ററിംഗ് സിസ്റ്റത്തിലേക്ക് അയച്ചു.

ഞാൻ അത് ശരിക്കും പ്രതീക്ഷിക്കുന്നു pg-index-health ഉപയോഗപ്രദവും ആവശ്യക്കാരും ആയിരിക്കും. നിങ്ങൾ കണ്ടെത്തുന്ന പ്രശ്നങ്ങൾ റിപ്പോർട്ടുചെയ്‌ത് പുതിയ ഡയഗ്‌നോസ്റ്റിക്‌സ് നിർദ്ദേശിച്ചുകൊണ്ട് നിങ്ങൾക്ക് ലൈബ്രറിയുടെ വികസനത്തിന് സംഭാവന നൽകാനും കഴിയും.

അവലംബം: www.habr.com