Hallo.
Mijn naam is Vanya en ik ben een Java-ontwikkelaar. Het gebeurde zo dat ik veel met PostgreSQL werk ā ik ben bezig met databaseconfiguratie, het optimaliseren van de structuur en prestaties, en ik speel een beetje DBA in het weekend.
Recent heb ik verschillende databases in onze microservices op orde gebracht en een Java-bibliotheek geschreven. , die dit werk vergemakkelijkt, mijn tijd bespaart en helpt om enkele veelvoorkomende fouten te vermijden die ontwikkelaars maken. Over deze bibliotheek gaat het vandaag.
Disclaimer
De belangrijkste versie van PostgreSQL waarmee ik werk, is versie 10. Alle SQL-queries die ik gebruik zijn ook getest op versie 11. De minimale ondersteunde versie is 9.6.
Achtergrond
Het begon bijna een jaar geleden met een vreemde situatie voor mij: het gelijktijdig aanmaken van een index op een lege plek eindigde in een fout. De index zelf bleef, zoals gebruikelijk, in een ongeldige staat in de database. Analyse van de logs toonde een tekort aan . En daar ging hetā¦ Toen ik dieper ging graven, ontdekte ik een heleboel problemen in de databaseconfiguratie en met opgekrulde mouwen en twinkelende ogen begon ik ze op te lossen.
Probleem Ć©Ć©n ā de standaardconfiguratie
Waarschijnlijk is de metafoor dat Postgres op een koffiezetapparaat kan draaien iedereen al flink beu, maarā¦ de standaardconfiguratie roept inderdaad een aantal vragen op. Ten minste, je moet aandacht besteden aan maintenance_work_mem, temp_file_limit, statement_timeout en lock_timeout.
In ons geval maintenance_work_mem was standaard 64 MB, en temp_file_limit iets van ongeveer 2 GB ā we hadden simpelweg niet genoeg geheugen om een index op een grote tabel te maken.
Daarom heb ik in pg-index-health een reeks , naar mijn mening, verzameld die u voor elke database moet configureren.
Probleem twee ā dubbele indexen
Onze databases draaien op SSD's en we gebruiken HA-configuraties met meerdere datacenters, een master-host en n-het aantal replicas. Opslagruimte op de schijf is voor ons een zeer waardevolle hulpbron; het is net zo belangrijk als prestaties en CPU-verbruik. Dus aan de ene kant hebben we indexen nodig voor snelle toegang, maar aan de andere kant willen we geen overbodige indexen in de database, omdat deze ruimte opslokken en de gegevensupdate vertragen.
En zo, nadat we alle hebben hersteld , ik besloot een 'grote' schoonmaak te houden. Blijkbaar houden ontwikkelaars er niet van de documentatie van de database te lezen. Ze houden daar helemaal niet van. Hierdoor ontstaan er twee typische fouten - handmatig gemaakte indexen op de primaire sleutel en een vergelijkbare 'handmatige' index op een unieke kolom. Het punt is dat ze niet nodig zijn - Postgres doet alles zelf. Dergelijke indexen kunnen zonder problemen worden verwijderd, en hiervoor is er diagnose beschikbaar. .
Derde probleem ā overlappende indexen
De meeste beginnende ontwikkelaars creƫren indexen op ƩƩn kolom. Geleidelijk, wanneer ze deze kwestie beter begrijpen, beginnen mensen hun queries te optimaliseren en meer complexe indexen toe te voegen die meerdere kolommen omvatten. Zo ontstaan indexen op kolommen A, A+B, A+B+C enzovoorts. De eerste twee van deze indexen kunnen zonder problemen worden weggegooid, omdat ze prefixen zijn van de derde. Dit bespaart ook behoorlijk wat schijfruimte en daarvoor is er diagnose beschikbaar. .
Vierde probleem ā externe sleutels zonder indexen
Postgres staat het creĆ«ren van beperkingen op externe sleutels toe zonder een ondersteunende index op te geven. In veel situaties is dit geen probleem en kan het zelfs geen merkbare effecten hebbenā¦ Tot een bepaald moment...
Zo was het ook bij ons: op een gegeven moment begon een job die op schema draait en de database reinigt van testbestellingen, ons master host 'op te stapelen'. CPU en IO schoten omhoog, queries vertraagden en werden door time-outs onderbroken, de service gaf een vijfhonderd. Een snelle analyse toonde aan dat de queries van het type vastliepen:
verwijder van <table> waar id in (…)Desondanks was er natuurlijk een index op id in de doel tabel, en de records werden op voorwaarde van slechts een paar verwijderd. Het leek alsof alles zou moeten werken, maar helaas werkte het niet.
Daar kwam de wonderbaarlijke explain analyze in beeld en vertelde dat naast het verwijderen van records in de doel tabel, ook de referentiƫle integriteit werd gecontroleerd, en op een van de gerelateerde tabellen viel deze controle terug op sequential scan door het ontbreken van een geschikte index. Zo ontstond de diagnose .
Vijfde probleem ā null waarde in indexen
Standaard omvat Postgres null waarden in btree-indexen, maar deze zijn daar meestal niet nodig. Daarom doe ik mijn best om deze nulls te verwijderen (diagnose ), door partiĆ«le indexen te creĆ«ren op nullable kolommen in de vorm van where is not nullOp deze manier is het mij gelukt om de grootte van een van onze indexen te verkleinen van 1877 MB naar 16 KB. En in een van de services is de totale grootte van de database met 16% (4,3 GB in absolute cijfers) verminderd door null-waarden uit de indexen te verwijderen. Een enorme besparing van schijfruimte bij vrij eenvoudige aanpassingen. š
Probleem zes - het ontbreken van primaire sleutels
Vanwege de eigenaardigheden van het mechanisme is er een situatie mogelijk, zoals , waarbij de grootte van uw tabel snel toeneemt door een groot aantal dode records. Ik dacht naĆÆef dat ons dit niet zou overkomen en dat dit met onze database niet zou gebeuren, want wij zijn, wauw!!!, toch normale ontwikkelaars... Wat was ik dom en naĆÆef...
Op een mooie dag heeft een geweldige migratie gewoon alle records in een grote en veelgebruikte tabel bijgewerkt. We kregen +100 GB bij de grootte van de tabel zonder enige reden. Het was ontzettend frustrerend, maar onze tegenslagen eindigden daar niet. Nadat de autovacuĆ¼m na 15 uur op deze tabel was afgerond, werd het duidelijk dat de fysieke ruimte niet terug zou komen. We konden de service niet stoppen en een VACUUM FULL uitvoeren, dus werd besloten om . En toen bleek dat pg_repack niet in staat is om tabellen zonder primaire sleutel of andere unieke beperkingen te verwerken, en onze tabel had geen primaire sleutel. Zo ontstond de diagnose .
In de versie van de bibliotheek 0.1.5 is de mogelijkheid toegevoegd om gegevens over bloat van tabellen en indexen te verzamelen en daar tijdig op te reageren.
Problemen zeven en acht - gebrek aan indexen en ongebruikte indexen
De volgende twee diagnoses zijn en ā in hun definitieve vorm relatief recent verschenen. Het probleem is dat ze niet zomaar toegevoegd konden worden.
Zoals ik al eerder zei, gebruiken we een configuratie met meerdere replica's, en de leesbelasting op verschillende hosts is principieel anders. Uiteindelijk ontstaat er een situatie waarin sommige tabellen en indexen op sommige hosts praktisch niet worden gebruikt, en voor de analyse moet er statistiek van alle hosts in het cluster worden verzameld. is ook nodig op elke host in het cluster, je kunt dit niet alleen op de master doen.
Deze benadering stelde ons in staat om enkele tientallen gigabytes te besparen door ongebruikte indexen te verwijderen en ontbrekende indexen toe te voegen aan zelden gebruikte tabellen.
Tot slot
Natuurlijk kunnen bijna alle diagnostieken worden ingesteld. . Zo kunt u snel controles implementeren in uw applicatie, nieuwe fouten voorkomen en geleidelijk oudere fouten corrigeren.
Deel van de diagnostieken kan al tijdens functionele tests worden uitgevoerd direct na het toepassen van database-migraties. Dit is misschien wel een van de krachtigste mogelijkheden van mijn bibliotheek. Een voorbeeld van gebruik is te zien in .
Controles op ongebruikte of ontbrekende indexen, evenals op bloat, hebben alleen zin om uit te voeren op een echte database. De verzamelde waarden kunnen worden opgeslagen in of naar het monitoringsysteem worden gestuurd.
Ik hoop echt dat pg-index-health nuttig en gewild zal zijn. U kunt ook bijdragen aan de ontwikkeling van de bibliotheek door problemen te melden en nieuwe diagnostieken voor te stellen.
Bron: habr.com
