Hallo.
Mijn naam is Vanya en ik ben een Java-ontwikkelaar. Toevallig werk ik veel met PostgreSQL: het opzetten van de database, het optimaliseren van de structuur, de prestaties en het spelen van een beetje DBA in de weekenden.
Onlangs heb ik verschillende databases in onze microservices opgeruimd en een Java-bibliotheek geschreven , wat dit werk eenvoudiger maakt, mij tijd bespaart en mij helpt een aantal veelvoorkomende fouten van ontwikkelaars te vermijden. Het is deze bibliotheek waar we het vandaag over zullen hebben.
Disclaimer
De hoofdversie van PostgreSQL waarmee ik werk is 10. Alle SQL-query's die ik gebruik, zijn ook getest op versie 11. De minimaal ondersteunde versie is 9.6.
prehistorie
Het begon allemaal bijna een jaar geleden met een voor mij vreemde situatie: de competitieve creatie van een index uit het niets eindigde met een fout. De index zelf bleef, zoals gewoonlijk, in een ongeldige staat in de database. Loganalyse toonde een tekort aan . En daar gingen we... Toen ik dieper graafde, ontdekte ik een hele reeks problemen in de databaseconfiguratie en, terwijl ik de mouwen opstroopte, begon ik ze met een glinstering in mijn ogen op te lossen.
Probleem één: standaardconfiguratie
Waarschijnlijk is iedereen de metafoor over Postgres, die op een koffiezetapparaat kan draaien, behoorlijk beu, maar... de standaardconfiguratie roept echt een aantal vragen op. Het is op zijn minst de moeite waard om er aandacht aan te besteden onderhouds_werk_mem, temp_file_limit, statement_time-out и lock_time-out.
In ons geval onderhouds_werk_mem was de standaard 64 MB, en temp_file_limit iets van ongeveer 2 GB - we hadden simpelweg niet genoeg geheugen om een index op een grote tafel te maken.
Daarom, in pg-index-gezondheid Ik heb een serie verzameld naar mijn mening de parameters die voor elke database moeten worden geconfigureerd.
Probleem twee: dubbele indexen
Onze databases staan op SSD-schijven en wij gebruiken deze HA-configuratie met meerdere datacenters, masterhost en n-aantal replica's. Schijfruimte is voor ons een zeer waardevolle hulpbron; het is niet minder belangrijk dan prestaties en CPU-verbruik. Daarom hebben we aan de ene kant indexen nodig om snel te kunnen lezen, en aan de andere kant willen we geen onnodige indexen in de database zien, omdat ze ruimte in beslag nemen en het bijwerken van gegevens vertragen.
En nu, nadat we alles hebben hersteld en genoeg gezien , besloot ik een ‘grote’ zuivering te organiseren. Het bleek dat ontwikkelaars niet graag databasedocumentatie lezen. Ze vinden het niet zo leuk. Hierdoor ontstaan er twee typische fouten: een handmatig gemaakte index op een primaire sleutel en een soortgelijke “handmatige” index op een unieke kolom. Feit is dat ze niet nodig zijn - Postgres zal alles zelf doen. Dergelijke indexen kunnen veilig worden verwijderd en voor dit doel zijn diagnostiek verschenen .
Probleem drie: kruisende indices
De meeste beginnende ontwikkelaars maken indexen op één enkele kolom. Geleidelijk aan, nadat ze dit bedrijf grondig hebben ervaren, beginnen mensen hun zoekopdrachten te optimaliseren en complexere indexen toe te voegen die meerdere kolommen bevatten. Dit is hoe indexen op kolommen verschijnen A, A + B, A + B + C enzovoort. De eerste twee van deze indices kunnen veilig worden weggegooid, omdat ze voorvoegsels zijn van de derde. Dit scheelt ook veel schijfruimte en daar bestaat diagnostiek voor .
Probleem vier: externe sleutels zonder indexen
Met Postgres kunt u beperkingen voor externe sleutels maken zonder een back-upindex op te geven. In veel situaties is dit geen probleem en manifesteert het zich misschien niet eens... Voorlopig...
Bij ons was het hetzelfde: het is alleen dat op een gegeven moment een taak, die volgens een schema liep en de database met testorders opruimde, door de masterhost aan ons werd 'toegevoegd'. CPU en IO gingen verloren, verzoeken vertraagden en er trad een time-out op, de service was vijfhonderd. Snelle analyse liet zien dat zoekopdrachten als:
delete from <table> where id in (…)In dit geval was er uiteraard een index op ID in de doeltabel en werden er zeer weinig records verwijderd op basis van de voorwaarde. Het leek alsof alles zou moeten werken, maar helaas gebeurde dat niet.
De wonderbaarlijke kwam te hulp analyseren uitleggen en zei dat er naast het verwijderen van records in de doeltabel ook een controle op referentiële integriteit plaatsvindt, en dat deze controle op een van de gerelateerde tabellen mislukt sequentiële scan vanwege het ontbreken van een geschikte index. Zo werd de diagnostiek geboren .
Probleem vijf – nulwaarde in indexen
Standaard neemt Postgres null-waarden op in btree-indexen, maar daar zijn ze meestal niet nodig. Daarom probeer ik ijverig deze nulpunten weg te gooien (diagnostiek ), waarbij gedeeltelijke indexen worden gemaakt voor null-kolommen op type where <A> is not null. Op deze manier kon ik de grootte van een van onze indexen verkleinen van 1877 MB naar 16 KB. En bij een van de services daalde de databasegrootte in totaal met 16% (met 4.3 GB in absolute cijfers) als gevolg van de uitsluiting van nulwaarden uit de indexen. Enorme besparingen op schijfruimte met zeer eenvoudige aanpassingen. 🙂
Probleem zes – gebrek aan primaire sleutels
Vanwege de aard van het mechanisme een situatie als deze is mogelijk wanneer de omvang van uw tabel snel groeit vanwege een groot aantal dode records. Ik geloofde naïef dat dit ons niet zou bedreigen, en dat dit onze basis niet zou overkomen, omdat wij, wauw!!!, normale ontwikkelaars zijn... Wat was ik stom en naïef...
Op een dag werden tijdens een prachtige migratie alle records in een grote en actief gebruikte tabel bijgewerkt en bijgewerkt. We hebben uit het niets +100 GB aan tafelgrootte gekregen. Het was verdomd jammer, maar daar hielden onze tegenslagen niet op. Nadat het autovacuüm op deze tafel 15 uur later eindigde, werd duidelijk dat de fysieke locatie niet meer terug zou komen. We konden de service niet stoppen en VACUUM VOL maken, dus besloten we om gebruik te maken van . En toen bleek dat pg_repack weet niet hoe hij tabellen moet verwerken zonder een primaire sleutel of andere uniciteitsbeperking, en onze tabel had geen primaire sleutel. Zo werd de diagnostiek geboren .
In de bibliotheekversie 0.1.5 De mogelijkheid om gegevens te verzamelen uit een overvloed aan tabellen en indexen en daar tijdig op te reageren is toegevoegd.
Problemen zeven en acht - onvoldoende indexen en ongebruikte indexen
De volgende twee diagnostieken zijn: и – verscheen relatief recent in hun definitieve vorm. Het punt is dat ze niet zomaar kunnen worden genomen en toegevoegd.
Zoals ik al schreef, gebruiken we een configuratie met verschillende replica's, en de leesbelasting op verschillende hosts is fundamenteel verschillend. Als gevolg hiervan blijkt de situatie dat sommige tabellen en indexen op sommige hosts praktisch niet worden gebruikt, en voor analyse moet je statistieken verzamelen van alle hosts in het cluster. Dit is ook nodig op elke host in het cluster; je kunt dit niet alleen op de master doen.
Met deze aanpak konden we enkele tientallen gigabytes besparen door indexen te verwijderen die nooit werden gebruikt, en door ontbrekende indexen toe te voegen aan zelden gebruikte tabellen.
Als conclusie
Uiteraard kunt u voor vrijwel alle diagnostiek configureren . Op deze manier kunt u snel controles in uw applicatie implementeren, waardoor nieuwe fouten worden voorkomen, en vervolgens geleidelijk oude fouten herstellen.
Sommige diagnostiek kan direct na het uitrollen van databasemigraties worden uitgevoerd in functionele tests. En dit is misschien wel een van de krachtigste kenmerken van mijn bibliotheek. Een voorbeeld van gebruik vindt u in .
Het is zinvol om alleen op een echte database controles uit te voeren op ongebruikte of ontbrekende indexen, maar ook op bloat. De verzamelde waarden kunnen worden vastgelegd in of naar het monitoringsysteem gestuurd.
Dat hoop ik echt pg-index-gezondheid zal nuttig en gewild zijn. U kunt ook bijdragen aan de ontwikkeling van de bibliotheek door problemen die u tegenkomt te melden en nieuwe diagnostiek voor te stellen.
Bron: www.habr.com