Ola
Chámome Vanya e son un programador de Java. Acontece que traballo moito con PostgreSQL: configurando a base de datos, optimizando a estrutura, o rendemento e xogando un pouco a DBA os fins de semana.
Recentemente ordenei varias bases de datos nos nosos microservizos e escribín unha biblioteca java , o que facilita este traballo, aforra tempo e axúdame a evitar algúns erros comúns cometidos polos desenvolvedores. É desta biblioteca da que falaremos hoxe.
retratação
A versión principal de PostgreSQL coa que traballo é a 10. Todas as consultas SQL que uso tamén se proban na versión 11. A versión mínima admitida é 9.6.
prehistoria
Todo comezou hai case un ano cunha situación que me resultaba estraña: a creación competitiva dun índice de repente rematou cun erro. O propio índice, como é habitual, permaneceu na base de datos nun estado non válido. A análise do rexistro mostrou unha escaseza . E imos... Afondando, descubrín un montón de problemas na configuración da base de datos e, arremangándome, comecei a solucionalos cun brillo nos meus ollos.
Problema primeiro: configuración predeterminada
Probablemente todos estean bastante cansos da metáfora sobre Postgres, que se pode executar nunha cafeteira, pero... a configuración predeterminada realmente suscita unha serie de preguntas. Como mínimo, paga a pena prestarlle atención traballo_mantemento_mem, límite_de_ficheiros_temporais, declaración_tempo de espera и lock_timeout.
No noso caso traballo_mantemento_mem era o predeterminado de 64 MB, e límite_de_ficheiros_temporais algo ao redor de 2 GB: simplemente non tiñamos memoria suficiente para crear un índice nunha táboa grande.
Polo tanto, en pg-index-saúde Coleccionei unha serie , na miña opinión, os parámetros que se deben configurar para cada base de datos.
Problema dous: índices duplicados
As nosas bases de datos viven en unidades SSD e usamos HA-configuración con varios centros de datos, host mestre e n-Número de réplicas. O espazo no disco é un recurso moi valioso para nós; non é menos importante que o rendemento e o consumo de CPU. Polo tanto, por unha banda, necesitamos índices para a lectura rápida e, por outra banda, non queremos ver índices innecesarios na base de datos, xa que consumen espazo e retardan a actualización de datos.
E agora, tendo todo restaurado e tendo visto abondo , decidín organizar unha "gran" purga. Resultou que aos desenvolvedores non lles gusta ler a documentación da base de datos. Non lles gusta moito. Debido a isto, xorden dous erros típicos: un índice creado manualmente nunha clave principal e un índice "manual" similar nunha columna única. O caso é que non son necesarios: Postgres fará todo por si mesmo. Estes índices pódense eliminar con seguridade e apareceron diagnósticos para este fin .
Problema tres: índices que se cruzan
A maioría dos desenvolvedores novatos crean índices nunha única columna. Aos poucos, despois de probar a fondo este negocio, a xente comeza a optimizar as súas consultas e engadir índices máis complexos que inclúen varias columnas. Así aparecen os índices nas columnas A, A + B, A + B + C etcétera. Os dous primeiros destes índices poden ser eliminados con seguridade, xa que son prefixos do terceiro. Isto tamén aforra moito espazo no disco e hai diagnósticos para iso .
Problema catro: chaves estranxeiras sen índices
Postgres permítelle crear restricións de chave estranxeira sen especificar un índice de respaldo. En moitas situacións isto non é un problema, e pode que nin sequera se manifeste... Polo momento...
O mesmo ocorreu con nós: é que nalgún momento un traballo, que se executaba segundo unha programación e borraba a base de datos de pedidos de proba, comezou a ser "engadido" a nós polo host mestre. A CPU e o IO foron desperdiciados, as solicitudes diminuíron e esgotaron o tempo de espera, o servizo era de cincocentos. Análise rápida mostrou que consultas como:
delete from <table> where id in (…)Neste caso, por suposto, había un índice por id na táboa de destino e elimináronse moi poucos rexistros segundo a condición. Parecía que todo debería funcionar, pero, por desgraza, non foi así.
O marabilloso veu ao rescate explicar analizar e dixo que ademais de eliminar rexistros na táboa de destino, tamén hai unha comprobación de integridade referencial, e nunha das táboas relacionadas esta comprobación falla. exploración secuencial debido á falta dun índice axeitado. Así naceu o diagnóstico .
Problema cinco: valor nulo nos índices
Por defecto, Postgres inclúe valores nulos nos índices btree, pero normalmente non son necesarios alí. Polo tanto, intento eliminar estes nulos (diagnóstico ), creando índices parciais en columnas anulables por tipo where <A> is not null. Deste xeito puiden reducir o tamaño dun dos nosos índices de 1877 MB a 16 KB. E nun dos servizos, o tamaño da base de datos diminuíu en total un 16% (en 4.3 GB en números absolutos) debido á exclusión de valores nulos dos índices. Enorme aforro de espazo en disco con modificacións moi sinxelas. 🙂
Problema seis: falta de chaves primarias
Debido á natureza do mecanismo unha situación como esta é posible cando o tamaño da túa táboa está a crecer rapidamente debido a un gran número de rexistros mortos. Eu cría inxenuamente que iso non nos ameazaría, e que isto non lle pasaría á nosa base, porque nós, wow!!!, somos desenvolvedores normais... Que estúpido e inxenuo fun...
Un día, unha migración marabillosa levou e actualizou todos os rexistros nunha táboa grande e utilizada activamente. Conseguimos + 100 GB ao tamaño da táboa sen dúbida. Foi unha puta pena, pero as nosas desventuras non remataron aí. Despois de que o baleiro automático nesta mesa rematase 15 horas despois, quedou claro que a localización física non volvería. Non puidemos deter o servizo e facer o VACUUM FULL, polo que decidimos utilizalo . E entón resultou que pg_repack non sabe como procesar táboas sen unha chave primaria ou outra restrición de exclusividade e a nosa táboa non tiña unha chave primaria. Así naceu o diagnóstico .
Na versión da biblioteca 0.1.5 Engadiuse a capacidade de recompilar datos de táboas e índices inflados e responder a eles de forma oportuna.
Problemas sete e oito: índices insuficientes e índices non utilizados
Os seguintes dous diagnósticos son: и – apareceron na súa forma final hai relativamente pouco tempo. A cuestión é que non só se podían tomar e engadir.
Como xa escribín, usamos unha configuración con varias réplicas e a carga de lectura en diferentes hosts é fundamentalmente diferente. Como resultado, a situación resulta que algunhas táboas e índices nalgúns hosts practicamente non se usan, e para a análise cómpre recoller estatísticas de todos os hosts do clúster. Isto tamén é necesario en todos os hosts do clúster; non podes facelo só no mestre.
Este enfoque permitiunos aforrar varias decenas de gigabytes eliminando índices que nunca se utilizaron, así como engadindo índices que faltan a táboas que raramente se usan.
Como conclusión
Por suposto, para case todos os diagnósticos pode configurar . Deste xeito, pode implementar comprobacións rapidamente na súa aplicación, evitando que aparezan novos erros e, a continuación, corrixir gradualmente os antigos.
Algúns diagnósticos pódense realizar en probas funcionais inmediatamente despois de que se implementaran as migracións de bases de datos. E esta é quizais unha das características máis poderosas da miña biblioteca. Pódese atopar un exemplo de uso en .
Ten sentido realizar comprobacións de índices non utilizados ou que faltan, así como de inchazo, só nunha base de datos real. Os valores recollidos pódense rexistrar en ou enviado ao sistema de seguimento.
Realmente espero que pg-index-saúde será útil e demandado. Tamén podes contribuír ao desenvolvemento da biblioteca informando dos problemas que atopes e suxerindo novos diagnósticos.
Fonte: www.habr.com