జావా డెవలపర్ దృష్టిలో పోస్ట్‌గ్రెస్‌ఎస్‌క్యూఎల్‌లోని ఇండెక్స్‌ల ఆరోగ్యం

హలో.

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

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

నిరాకరణ

నేను పని చేస్తున్న PostgreSQL యొక్క ప్రధాన వెర్షన్ 10. నేను ఉపయోగించే అన్ని SQL ప్రశ్నలు కూడా వెర్షన్ 11లో పరీక్షించబడ్డాయి. కనీస మద్దతు వెర్షన్ 9.6.

పూర్వచరిత్ర

ఇది దాదాపు ఒక సంవత్సరం క్రితం నాకు వింతగా ఉన్న పరిస్థితితో ప్రారంభమైంది: నీలిరంగు నుండి ఇండెక్స్ యొక్క పోటీ సృష్టి లోపంతో ముగిసింది. ఇండెక్స్, ఎప్పటిలాగే, చెల్లని స్థితిలో డేటాబేస్లో ఉంది. లాగ్ విశ్లేషణ కొరతను చూపింది temp_file_limit. మరియు మేము వెళ్తాము... లోతుగా త్రవ్వినప్పుడు, నేను డేటాబేస్ కాన్ఫిగరేషన్‌లో మొత్తం సమస్యల సమూహాన్ని కనుగొన్నాను మరియు నా స్లీవ్‌లను పైకి లేపి, నా కళ్ళలో మెరుపుతో వాటిని పరిష్కరించడం ప్రారంభించాను.

సమస్య ఒకటి - డిఫాల్ట్ కాన్ఫిగరేషన్

కాఫీ మేకర్‌లో అమలు చేయగల పోస్ట్‌గ్రెస్ గురించిన రూపకంతో బహుశా ప్రతి ఒక్కరూ చాలా అలసిపోయారు, కానీ... డిఫాల్ట్ కాన్ఫిగరేషన్ నిజంగా అనేక ప్రశ్నలను లేవనెత్తుతుంది. కనీసం, ఇది దృష్టి పెట్టారు విలువ నిర్వహణ_పని_మెమ్, temp_file_limit, ప్రకటన_సమయం ముగిసింది и లాక్_టైమ్ అవుట్.

మా విషయంలో నిర్వహణ_పని_మెమ్ డిఫాల్ట్ 64 MB, మరియు temp_file_limit 2 GB చుట్టూ ఏదో - పెద్ద టేబుల్‌పై ఇండెక్స్‌ని సృష్టించడానికి మాకు తగినంత మెమరీ లేదు.

అందువలన, లో pg-సూచిక-ఆరోగ్యం నేను ఒక సిరీస్‌ని సేకరించాను కీ, నా అభిప్రాయం ప్రకారం, ప్రతి డేటాబేస్ కోసం కాన్ఫిగర్ చేయవలసిన పారామితులు.

సమస్య రెండు - నకిలీ సూచికలు

మా డేటాబేస్‌లు SSD డ్రైవ్‌లలో నివసిస్తాయి మరియు మేము ఉపయోగిస్తాము HA- బహుళ డేటా కేంద్రాలతో కాన్ఫిగరేషన్, మాస్టర్ హోస్ట్ మరియు n- ప్రతిరూపాల సంఖ్య. డిస్క్ స్థలం మాకు చాలా విలువైన వనరు; ఇది పనితీరు మరియు CPU వినియోగం కంటే తక్కువ ముఖ్యమైనది కాదు. అందువల్ల, ఒక వైపు, మనకు వేగంగా చదవడానికి సూచికలు అవసరం, మరియు మరోవైపు, డేటాబేస్లో అనవసరమైన సూచికలను చూడకూడదనుకుంటున్నాము, ఎందుకంటే అవి ఖాళీని తింటాయి మరియు డేటా నవీకరణను నెమ్మదిస్తాయి.

మరియు ఇప్పుడు, ప్రతిదీ పునరుద్ధరించబడింది చెల్లని సూచికలు మరియు తగినంత చూసిన ఒలేగ్ బార్టునోవ్ నివేదికలు, నేను "గొప్ప" ప్రక్షాళనను నిర్వహించాలని నిర్ణయించుకున్నాను. డెవలపర్లు డేటాబేస్ డాక్యుమెంటేషన్ చదవడానికి ఇష్టపడరని తేలింది. వారికి అది పెద్దగా నచ్చదు. దీని కారణంగా, రెండు విలక్షణమైన లోపాలు తలెత్తుతాయి - ప్రాథమిక కీపై మానవీయంగా సృష్టించబడిన సూచిక మరియు ప్రత్యేక నిలువు వరుసలో సారూప్యమైన "మాన్యువల్" సూచిక. వాస్తవం ఏమిటంటే అవి అవసరం లేదు - పోస్ట్‌గ్రెస్ ప్రతిదీ స్వయంగా చేస్తుంది. ఇటువంటి సూచికలు సురక్షితంగా తొలగించబడతాయి మరియు ఈ ప్రయోజనం కోసం డయాగ్నస్టిక్స్ కనిపించాయి నకిలీ_సూచికలు.

సమస్య మూడు - ఖండన సూచికలు

చాలా అనుభవం లేని డెవలపర్‌లు ఒకే నిలువు వరుసలో సూచికలను సృష్టిస్తారు. క్రమంగా, ఈ వ్యాపారాన్ని పూర్తిగా అనుభవించిన తర్వాత, వ్యక్తులు వారి ప్రశ్నలను ఆప్టిమైజ్ చేయడం మరియు అనేక నిలువు వరుసలను కలిగి ఉన్న మరింత క్లిష్టమైన సూచికలను జోడించడం ప్రారంభిస్తారు. నిలువు వరుసలపై సూచికలు ఈ విధంగా కనిపిస్తాయి A, A + B., A+B+C మరియు అందువలన న. ఈ సూచికలలో మొదటి రెండు సురక్షితంగా విసిరివేయబడతాయి, ఎందుకంటే అవి మూడవది ఉపసర్గలు. ఇది చాలా డిస్క్ స్థలాన్ని కూడా ఆదా చేస్తుంది మరియు దీని కోసం డయాగ్నస్టిక్స్ ఉన్నాయి ఖండన_సూచికలు.

సమస్య నాలుగు - సూచికలు లేని విదేశీ కీలు

పోస్ట్‌గ్రెస్ బ్యాకింగ్ ఇండెక్స్‌ను పేర్కొనకుండా విదేశీ కీ పరిమితులను సృష్టించడానికి మిమ్మల్ని అనుమతిస్తుంది. అనేక సందర్భాల్లో ఇది సమస్య కాదు, అలాగే కనిపించకపోవచ్చు... ప్రస్తుతానికి...

ఇది మాతో కూడా అలాగే ఉంది: ఏదో ఒక సమయంలో ఒక ఉద్యోగం, షెడ్యూల్ ప్రకారం నడుస్తుంది మరియు పరీక్ష ఆర్డర్‌ల డేటాబేస్‌ను క్లియర్ చేయడం మాస్టర్ హోస్ట్ ద్వారా మాకు “జోడించడం” ప్రారంభించింది. CPU మరియు IO వృధాగా పోయాయి, అభ్యర్థనలు మందగించాయి మరియు సమయం ముగిసింది, సేవ ఐదు వందలు. త్వరిత విశ్లేషణ pg_stat_activity వంటి ప్రశ్నలను చూపించింది:

delete from <table> where id in (…)

ఈ సందర్భంలో, వాస్తవానికి, లక్ష్య పట్టికలో id ద్వారా సూచిక ఉంది మరియు షరతు ప్రకారం చాలా తక్కువ రికార్డ్‌లు తొలగించబడ్డాయి. ప్రతిదీ పని చేయాలని అనిపించింది, కానీ, అయ్యో, అది చేయలేదు.

అద్భుతమైన వ్యక్తి రక్షించటానికి వచ్చాడు విశ్లేషణను వివరించండి మరియు టార్గెట్ టేబుల్‌లోని రికార్డులను తొలగించడంతో పాటు, రెఫరెన్షియల్ ఇంటిగ్రిటీ చెక్ కూడా ఉందని, సంబంధిత టేబుల్‌లలో ఒకదానిలో ఈ చెక్ విఫలమైందని చెప్పారు. వరుస స్కాన్ తగిన సూచిక లేకపోవడం వల్ల. అలా డయాగ్నస్టిక్స్ పుట్టింది విదేశీ_కీలు_ఇండెక్స్ లేకుండా.

సమస్య ఐదు - సూచికలలో శూన్య విలువ

డిఫాల్ట్‌గా, పోస్ట్‌గ్రెస్ బిట్రీ ఇండెక్స్‌లలో శూన్య విలువలను కలిగి ఉంటుంది, అయితే అవి సాధారణంగా అక్కడ అవసరం లేదు. అందువల్ల, నేను ఈ శూన్యాలను (డయాగ్నస్టిక్స్) విసిరేయడానికి శ్రద్ధగా ప్రయత్నిస్తాను శూన్య_విలువలతో_సూచికలు), రకాన్ని బట్టి రద్దు చేయదగిన నిలువు వరుసలపై పాక్షిక సూచికలను సృష్టించడం where <A> is not null. ఈ విధంగా నేను మా ఇండెక్స్‌లలో ఒకదాని పరిమాణాన్ని 1877 MB నుండి 16 KBకి తగ్గించగలిగాను. మరియు ఒక సేవలో, సూచికల నుండి శూన్య విలువలను మినహాయించడం వల్ల డేటాబేస్ పరిమాణం మొత్తం 16% (సంపూర్ణ సంఖ్యలో 4.3 GB ద్వారా) తగ్గింది. చాలా సులభమైన మార్పులతో డిస్క్ స్థలంలో అపారమైన పొదుపులు. 🙂

సమస్య ఆరు - ప్రాథమిక కీలు లేకపోవడం

యంత్రాంగం యొక్క స్వభావం కారణంగా పోస్ట్‌గ్రెస్‌లో MVCC ఇలాంటి పరిస్థితి సాధ్యమే ఉబ్బరంపెద్ద సంఖ్యలో డెడ్ రికార్డ్‌ల కారణంగా మీ టేబుల్ పరిమాణం వేగంగా పెరుగుతున్నప్పుడు. ఇది మనల్ని బెదిరించదని మరియు మా స్థావరానికి ఇది జరగదని నేను అమాయకంగా నమ్మాను, ఎందుకంటే మేము, వావ్!!!, సాధారణ డెవలపర్‌లమే... నేను ఎంత తెలివితక్కువవాడిని మరియు అమాయకుడిని...

ఒక రోజు, ఒక అద్భుతమైన మైగ్రేషన్ పెద్ద మరియు చురుకుగా ఉపయోగించిన పట్టికలోని అన్ని రికార్డులను తీసుకుంది మరియు నవీకరించబడింది. మేము నీలిరంగు నుండి టేబుల్ పరిమాణానికి +100 GBని పొందాము. ఇది చాలా అవమానకరం, కానీ మా దురదృష్టాలు అక్కడితో ముగియలేదు. ఈ టేబుల్‌పై ఆటోవాక్యూమ్ 15 గంటల తర్వాత ముగిసిన తర్వాత, భౌతిక స్థానం తిరిగి రాదని స్పష్టమైంది. మేము సేవను ఆపివేసి, వాక్యూమ్‌ని పూర్తి చేయలేకపోయాము, కాబట్టి మేము ఉపయోగించాలని నిర్ణయించుకున్నాము pg_repack. ఆపై అది తేలింది pg_repack ప్రాథమిక కీ లేదా ఇతర ప్రత్యేకత పరిమితి లేకుండా పట్టికలను ఎలా ప్రాసెస్ చేయాలో తెలియదు మరియు మా టేబుల్‌కి ప్రాథమిక కీ లేదు. అలా డయాగ్నస్టిక్స్ పుట్టింది ప్రాథమిక_కీ లేని_పట్టికలు.

లైబ్రరీ వెర్షన్‌లో 0.1.5 బ్లోట్ ఆఫ్ టేబుల్స్ మరియు ఇండెక్స్‌ల నుండి డేటాను సేకరించే సామర్థ్యం మరియు దానికి సకాలంలో ప్రతిస్పందించే సామర్థ్యం జోడించబడింది.

ఏడు మరియు ఎనిమిది సమస్యలు - సరిపోని సూచికలు మరియు ఉపయోగించని సూచికలు

కింది రెండు రోగనిర్ధారణలు: పట్టికలు_మిస్సింగ్_ఇండెక్స్‌లు и ఉపయోగించని_సూచికలు - సాపేక్షంగా ఇటీవల వారి చివరి రూపంలో కనిపించింది. విషయమేమిటంటే, వాటిని తీయడం మరియు జోడించడం సాధ్యం కాదు.

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

ఈ విధానం ఎన్నడూ ఉపయోగించని సూచికలను తీసివేయడం ద్వారా అనేక పదుల గిగాబైట్‌లను ఆదా చేయడానికి మాకు అనుమతి ఇచ్చింది, అలాగే అరుదుగా ఉపయోగించే పట్టికలకు తప్పిపోయిన సూచికలను జోడించడం.

ఒక ముగింపుగా

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

డేటాబేస్ మైగ్రేషన్‌లను విడుదల చేసిన వెంటనే ఫంక్షనల్ పరీక్షలలో కొన్ని డయాగ్నస్టిక్‌లు నిర్వహించబడతాయి. మరియు ఇది బహుశా నా లైబ్రరీ యొక్క అత్యంత శక్తివంతమైన లక్షణాలలో ఒకటి. ఉపయోగం యొక్క ఉదాహరణలో చూడవచ్చు డెమో.

నిజమైన డేటాబేస్లో మాత్రమే ఉపయోగించని లేదా తప్పిపోయిన ఇండెక్స్‌ల కోసం, అలాగే బ్లోట్ కోసం తనిఖీలు చేయడం అర్ధమే. సేకరించిన విలువలను నమోదు చేయవచ్చు క్లిక్హౌస్ లేదా పర్యవేక్షణ వ్యవస్థకు పంపబడుతుంది.

నేను నిజంగా ఆశిస్తున్నాను pg-సూచిక-ఆరోగ్యం ఉపయోగకరంగా మరియు డిమాండ్ ఉంటుంది. మీరు కనుగొన్న సమస్యలను నివేదించడం మరియు కొత్త డయాగ్నస్టిక్‌లను సూచించడం ద్వారా మీరు లైబ్రరీ అభివృద్ధికి సహకరించవచ్చు.

మూలం: www.habr.com