Salutacions.
Em dic Vanya i sóc desenvolupador de Java. Succeeix que treballo molt amb PostgreSQL: configurar la base de dades, optimitzar l'estructura, el rendiment i jugar una mica de DBA els caps de setmana.
Recentment he endreçat diverses bases de dades als nostres microserveis i he escrit una biblioteca java , que facilita aquesta feina, m'estalvia temps i m'ajuda a evitar alguns errors habituals dels desenvolupadors. És d'aquesta biblioteca de la que parlarem avui.
renúncia
La versió principal de PostgreSQL amb la qual treballo és la 10. Totes les consultes SQL que faig servir també es proveen a la versió 11. La versió mínima admesa és la 9.6.
prehistòria
Tot va començar fa gairebé un any amb una situació que em va resultar estranya: la creació competitiva d'un índex a l'improvisa va acabar amb un error. L'índex en si, com és habitual, va romandre a la base de dades en un estat no vàlid. L'anàlisi del registre va mostrar una mancança . I anem... Aprofundint, vaig descobrir un munt de problemes en la configuració de la base de dades i, arremangant-me, vaig començar a solucionar-los amb una brillantor als ulls.
Primer problema: configuració predeterminada
Probablement tothom està bastant cansat de la metàfora sobre Postgres, que es pot executar amb una cafetera, però... la configuració predeterminada realment planteja una sèrie de preguntes. Com a mínim, val la pena parar-hi atenció treball_manteniment_mem, límit_fitxer_temp, Statement_timeout и lock_timeout.
En el nostre cas treball_manteniment_mem era el valor predeterminat de 64 MB, i límit_fitxer_temp alguna cosa al voltant de 2 GB: simplement no teníem prou memòria per crear un índex en una taula gran.
Per tant, a pg-index-salut Vaig recopilar una sèrie , al meu entendre, els paràmetres que s'han de configurar per a cada base de dades.
Problema dos: índexs duplicats
Les nostres bases de dades viuen en unitats SSD i les fem servir HA-configuració amb múltiples centres de dades, host principal i n-nombre de rèpliques. L'espai en disc és un recurs molt valuós per a nosaltres; no és menys important que el rendiment i el consum de CPU. Per tant, d'una banda, necessitem índexs per a una lectura ràpida, i d'altra banda, no volem veure índexs innecessaris a la base de dades, ja que consumeixen espai i frenen l'actualització de dades.
I ara, havent-ho restaurat tot i havent vist prou , vaig decidir organitzar una "gran" purga. Va resultar que als desenvolupadors no els agrada llegir la documentació de la base de dades. No els agrada gaire. A causa d'això, sorgeixen dos errors típics: un índex creat manualment en una clau primària i un índex "manual" similar en una columna única. El fet és que no són necessaris: Postgres ho farà tot ell mateix. Aquests índexs es poden suprimir de manera segura i han aparegut diagnòstics amb aquesta finalitat .
Problema tres: índexs d'intersecció
La majoria dels desenvolupadors novells creen índexs en una sola columna. A poc a poc, després d'haver experimentat a fons aquest negoci, la gent comença a optimitzar les seves consultes i a afegir índexs més complexos que inclouen diverses columnes. Així apareixen els índexs a les columnes A, A + B, A + B + C etcètera. Els dos primers d'aquests índexs es poden llençar amb seguretat, ja que són prefixos del tercer. Això també estalvia molt d'espai al disc i hi ha diagnòstics per a això .
Problema quatre: claus forasteres sense índexs
Postgres us permet crear restriccions de clau estrangera sense especificar un índex de suport. En moltes situacions això no és un problema, i potser ni tan sols es manifesta... De moment...
Amb nosaltres va passar el mateix: és que en algun moment un treball, que s'executava segons una programació i esborrava la base de dades d'ordres de prova, ens va començar a "afegir" per part de l'amfitrió principal. La CPU i l'IO es van perdre, les sol·licituds es van alentir i es van esgotar el temps, el servei va ser de cinc-centes. Anàlisi ràpida va mostrar que consultes com:
delete from <table> where id in (…)En aquest cas, per descomptat, hi havia un índex per identificador a la taula de destinació i es van suprimir molt pocs registres segons la condició. Semblava que tot hauria de funcionar, però, per desgràcia, no va ser així.
El meravellós va venir al rescat explicar analitzar i va dir que a més d'esborrar registres a la taula de destinació, també hi ha una comprovació d'integritat referencial, i en una de les taules relacionades aquesta comprovació falla. exploració seqüencial per la manca d'un índex adequat. Així va néixer el diagnòstic .
Problema cinc: valor nul en els índexs
Per defecte, Postgres inclou valors nuls als índexs btree, però normalment no es necessiten allí. Per tant, intento diligentment llençar aquests nuls (diagnòstic ), creant índexs parcials en columnes anul·lables per tipus where <A> is not null. D'aquesta manera vaig poder reduir la mida d'un dels nostres índexs de 1877 MB a 16 KB. I en un dels serveis, la mida de la base de dades va disminuir en total un 16% (en 4.3 GB en nombres absoluts) a causa de l'exclusió dels valors nuls dels índexs. Enorme estalvi d'espai en disc amb modificacions molt senzilles. 🙂
Problema sis: manca de claus primàries
Per la naturalesa del mecanisme una situació com aquesta és possible quan la mida de la vostra taula està creixent ràpidament a causa d'un gran nombre de registres morts. Vaig creure ingènuament que això no ens amenaçaria, i que això no passaria a la nostra base, perquè nosaltres, vaja!!!, som desenvolupadors normals... Que estúpid i ingenu que era...
Un dia, una migració meravellosa va prendre i actualitzar tots els registres d'una taula gran i utilitzada activament. Hem aconseguit +100 GB a la mida de la taula de manera inesperada. Va ser una llàstima, però les nostres desventures no van acabar aquí. Després que l'autoaspiració d'aquesta taula acabés 15 hores després, va quedar clar que la ubicació física no tornaria. No vam poder aturar el servei i fer VACUUM FULL, així que vam decidir utilitzar-lo . I després va resultar que pg_repack no sap com processar taules sense una clau primària o una altra restricció d'unicitat, i la nostra taula no tenia una clau primària. Així va néixer el diagnòstic .
A la versió de la biblioteca 0.1.5 S'ha afegit la possibilitat de recopilar dades de la inflació de taules i índexs i respondre-hi de manera oportuna.
Problemes set i vuit: índexs insuficients i índexs no utilitzats
Els dos diagnòstics següents són: и – va aparèixer en la seva forma final relativament recentment. La qüestió és que no només es podrien agafar i afegir.
Com ja he escrit, fem servir una configuració amb diverses rèpliques i la càrrega de lectura en diferents hosts és fonamentalment diferent. Com a resultat, la situació resulta que algunes taules i índexs d'alguns amfitrions pràcticament no s'utilitzen, i per a l'anàlisi cal recopilar estadístiques de tots els amfitrions del clúster. Això també és necessari a tots els hosts del clúster; no ho podeu fer només al mestre.
Aquest enfocament ens va permetre estalviar diverses desenes de gigabytes eliminant índexs que mai s'utilitzaven, així com afegint índexs que falten a taules poc utilitzades.
Com a conclusió
Per descomptat, per a gairebé tots els diagnòstics que podeu configurar . D'aquesta manera, podeu implementar comprovacions ràpidament a la vostra aplicació, evitant que apareguin nous errors i, a continuació, corregir-ne progressivament els antics.
Alguns diagnòstics es poden realitzar en proves funcionals immediatament després de desplegar les migracions de bases de dades. I aquesta és potser una de les característiques més potents de la meva biblioteca. Un exemple d'ús es pot trobar a .
Té sentit dur a terme comprovacions dels índexs no utilitzats o que falten, així com de la inflació, només en una base de dades real. Els valors recollits es poden registrar a o enviat al sistema de seguiment.
Realment espero això pg-index-salut serà útil i demanat. També podeu contribuir al desenvolupament de la biblioteca informant dels problemes que trobeu i suggerint nous diagnòstics.
Font: www.habr.com