{"id":72875,"date":"2020-03-05T08:42:09","date_gmt":"2020-03-05T05:42:09","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/zdorove-indeksov-v-postgresql-glazami-java-razrabotchika"},"modified":"2020-03-05T08:42:09","modified_gmt":"2020-03-05T05:42:09","slug":"zdorove-indeksov-v-postgresql-glazami-java-razrabotchika","status":"publish","type":"post","link":"https:\/\/prohoster.info\/es\/blog\/administrirovanie\/zdorove-indeksov-v-postgresql-glazami-java-razrabotchika","title":{"rendered":"La salud de los \u00edndices en PostgreSQL a trav\u00e9s de los ojos de un desarrollador Java","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Hola.<\/p>\n

<\/p>\n

Me llamo Vanya y soy desarrollador Java. Por casualidad, he trabajado mucho con PostgreSQL: estoy encargado de configurar bases de datos, optimizar estructuras, mejorar el rendimiento y un poco de DBA los fines de semana.<\/p>\n

<\/p>\n

\u00daltimamente, he ordenado varias bases de datos en nuestros microservicios y he escrito una biblioteca de Java pg-index-health<\/a><\/noindex>, que facilita este trabajo, ahorra mi tiempo y ayuda a evitar algunos errores comunes que cometen los desarrolladores. De eso es de lo que hablaremos hoy.<\/p>\n

<\/p>\n

\"La<\/p>\n

<\/p>\n

Descargo de responsabilidad<\/h3>\n

<\/p>\n

La versi\u00f3n principal de PostgreSQL con la que trabajo es la 10. Todas las consultas SQL que utilizo tambi\u00e9n han sido probadas en la versi\u00f3n 11. La versi\u00f3n m\u00ednima compatible es la 9.6.<\/p>\n

<\/p>\n

Antecedentes<\/h3>\n

<\/p>\n

Todo comenz\u00f3 hace casi un a\u00f1o con una situaci\u00f3n extra\u00f1a para m\u00ed: la creaci\u00f3n concurrente de un \u00edndice de repente fall\u00f3. El \u00edndice, como suele suceder, qued\u00f3 en un estado no v\u00e1lido en la base de datos. El an\u00e1lisis de los registros mostr\u00f3 la falta de temp_file_limit<\/a><\/noindex>. Y comenz\u00f3\u2026 Al profundizar, descubr\u00ed un mont\u00f3n de problemas en la configuraci\u00f3n de la base de datos y, arremang\u00e1ndome, me puse a solucionarlos con entusiasmo.<\/p>\n

<\/a><\/noindex><\/p>\n

El primer problema - la configuraci\u00f3n por defecto<\/h3>\n

<\/p>\n

Probablemente, la met\u00e1fora de un Postgres que se puede ejecutar en una cafetera ya ha cansado a muchos, pero\u2026 la configuraci\u00f3n por defecto realmente plantea varias preguntas. Como m\u00ednimo, vale la pena prestar atenci\u00f3n a maintenance_work_mem<\/strong>, temp_file_limit<\/strong>, statement_timeout<\/strong> y lock_timeout<\/strong>.<\/p>\n

<\/p>\n

En nuestro caso maintenance_work_mem<\/strong> era por defecto de 64 megabytes, y temp_file_limit<\/strong> algo alrededor de 2 gigabytes \u2013 simplemente no ten\u00edamos suficiente memoria para crear un \u00edndice en una tabla grande.<\/p>\n

<\/p>\n

Por eso, en pg-index-health<\/strong> reun\u00ed una serie de par\u00e1metros clave<\/a><\/noindex>, que en mi opini\u00f3n, vale la pena ajustar para cada base de datos.<\/p>\n

<\/p>\n

El segundo problema - \u00edndices duplicados<\/h3>\n

<\/p>\n

Nuestras bases viven en discos SSD, y utilizamos HA<\/em>-configuraciones con m\u00faltiples centros de datos, un host maestro y n<\/em>-un n\u00famero de r\u00e9plicas. El espacio en disco es un recurso muy valioso para nosotros; es tan importante como el rendimiento y el consumo de CPU. Por lo tanto, por un lado, necesitamos \u00edndices para una lectura r\u00e1pida, pero por otro lado, no queremos ver \u00edndices innecesarios en la base de datos, ya que consumen espacio y ralentizan la actualizaci\u00f3n de datos.<\/p>\n

<\/p>\n

Y as\u00ed, despu\u00e9s de restaurar todos los \u00edndices no v\u00e1lidos<\/a><\/noindex> y tras haber visto las presentaciones de Oleg Bartunov<\/a><\/noindex>, decid\u00ed hacer una \u00abgran\u00bb limpieza. Result\u00f3 que a los desarrolladores no les gusta leer la documentaci\u00f3n de la base de datos. No les gusta en absoluto. Por esta raz\u00f3n, surgen dos errores comunes: un \u00edndice creado manualmente en la clave primaria y un \u00edndice similar \u201cmanual\u201d en una columna \u00fanica. La cuesti\u00f3n es que no son necesarios; Postgres se encarga de ello. Estos \u00edndices se pueden eliminar sin problema, y para esto se ha creado un diagn\u00f3stico. \u00edndices_duplicados<\/a><\/noindex>.<\/p>\n

<\/p>\n

El tercer problema: \u00edndices superpuestos<\/h3>\n

<\/p>\n

La mayor\u00eda de los desarrolladores principiantes crea \u00edndices en una sola columna. Gradualmente, al experimentar y probar esta tarea, las personas comienzan a optimizar sus consultas y agregar \u00edndices m\u00e1s complejos que incluyen varias columnas. As\u00ed surgen \u00edndices en columnas. A<\/em>, A+B<\/em>, A+B+C<\/em> y as\u00ed sucesivamente. Los dos primeros de estos \u00edndices se pueden eliminar sin problema, ya que son prefijos del tercero. Esto tambi\u00e9n ahorra espacio en disco, y para esto hay un diagn\u00f3stico. \u00edndices_intersectados<\/a><\/noindex>.<\/p>\n

<\/p>\n

El cuarto problema: claves externas sin \u00edndices<\/h3>\n

<\/p>\n

Postgres permite crear restricciones de clave externa sin especificar un \u00edndice de soporte. En muchas situaciones, esto no es un problema y ni siquiera se manifiesta... Hasta que llega el momento...<\/p>\n

<\/p>\n

As\u00ed nos pas\u00f3 a nosotros: en un momento dado, un trabajo programado que limpiaba la base de datos de pedidos de prueba comenz\u00f3 a \u2018acumular\u2019 nuestro servidor maestro. El CPU y el IO se dispararon, las consultas se ralentizaron y fueron interrumpidas por tiempo de espera, el servicio ofrec\u00eda errores 500. Un an\u00e1lisis r\u00e1pido pg_stat_activity<\/a><\/noindex> mostr\u00f3 que las consultas del tipo:<\/p>\n

<\/p>\n

eliminar de <table> donde id está en (…)<\/code><\/pre>\n
<\/p>\n
Mientras tanto, el \u00edndice por id en la tabla de destino, por supuesto, estaba presente, y se eliminaban muy pocas entradas seg\u00fan la condici\u00f3n. Parec\u00eda que todo deber\u00eda funcionar, pero, lamentablemente, no funcionaba.<\/p>\n
<\/p>\n
El maravilloso explain analyze<\/strong> vino al rescate y revel\u00f3 que, adem\u00e1s de eliminar registros en la tabla de destino, tambi\u00e9n se estaba llevando a cabo una verificaci\u00f3n de la integridad referencial, y en una de las tablas relacionadas esta verificaci\u00f3n se ca\u00eda en un escaneo secuencial<\/em> debido a la falta de un \u00edndice adecuado. As\u00ed naci\u00f3 el diagn\u00f3stico. claves_externas_sin_indices<\/a><\/noindex>.<\/p>\n
<\/p>\n
El quinto problema: valor null en los \u00edndices<\/h3>\n
<\/p>\n
Por defecto, Postgres incluye valores null en los \u00edndices btree, pero generalmente no son necesarios all\u00ed. Por lo tanto, me esfuerzo por eliminar estos nulls (diagn\u00f3stico indices_con_valores_null<\/a><\/noindex>), creando \u00edndices parciales en columnas que admiten null con la condici\u00f3n donde  no es null<\/code>. De este modo, pude reducir el tama\u00f1o de uno de nuestros \u00edndices de 1877 MB a 16 KB. Y en uno de los servicios, el tama\u00f1o total de la base de datos se redujo en un 16% (en 4.3 GB en n\u00fameros absolutos) al eliminar valores nulos de los \u00edndices. Un ahorro colosal de espacio en disco con mejoras bastante simples. \ud83d\ude42 <\/p>\n
<\/p>\n
El sexto problema: falta de claves primarias<\/h3>\n
<\/p>\n
Debido a las peculiaridades del mecanismo MVCC en Postgres<\/a><\/noindex> puede darse la situaci\u00f3n en la que bloat<\/a><\/noindex>, el tama\u00f1o de su tabla crece r\u00e1pidamente debido a una gran cantidad de registros muertos. Na\u00efvamente pens\u00e9 que eso no nos afectar\u00eda, y que nuestra base no deber\u00eda tener ese problema, ya que somos, \u00a1vaya!, desarrolladores normales... Qu\u00e9 tonto y ingenuo fui...<\/p>\n
<\/p>\n
Un buen d\u00eda, una maravillosa migraci\u00f3n se encarg\u00f3 de actualizar todos los registros en una tabla grande que se utiliza activamente. Obtuvimos +100 GB en el tama\u00f1o de la tabla de la nada. Fue realmente frustrante, pero nuestras desventuras no terminaron ah\u00ed. Despu\u00e9s de 15 horas, el autovacuum en esta tabla termin\u00f3, y qued\u00f3 claro que el espacio f\u00edsico no regresar\u00eda. No pod\u00edamos detener el servicio para realizar un VACUUM FULL, as\u00ed que se decidi\u00f3 usar pg_repack<\/a><\/noindex>. Y aqu\u00ed result\u00f3 que pg_repack<\/em> no puede manejar tablas sin llave primaria o alg\u00fan otro tipo de restricci\u00f3n de unicidad, y nuestra tabla no ten\u00eda una llave primaria. As\u00ed naci\u00f3 el diagn\u00f3stico tables_without_primary_key<\/a><\/noindex>.<\/p>\n
<\/p>\n
En la versi\u00f3n de la biblioteca 0.1.5<\/strong> se agreg\u00f3 la posibilidad de recopilar datos sobre el bloat de las tablas e \u00edndices y reaccionar a tiempo.<\/p>\n
<\/p>\n
Los problemas siete y ocho: falta de \u00edndices e \u00edndices no utilizados<\/h3>\n
<\/p>\n
Los siguientes dos diagn\u00f3sticos son tables_with_missing_indexes<\/a><\/noindex> y unused_indexes<\/a><\/noindex> \u2013 en su forma final aparecieron relativamente recientemente. La cuesti\u00f3n es que no se pod\u00edan a\u00f1adir as\u00ed como as\u00ed.<\/p>\n
<\/p>\n
Como ya mencion\u00e9, utilizamos una configuraci\u00f3n con m\u00faltiples r\u00e9plicas, y la carga de lectura en diferentes hosts var\u00eda significativamente. Como resultado, existen tablas e \u00edndices en algunos hosts que pr\u00e1cticamente no se utilizan, y para el an\u00e1lisis es necesario recopilar estad\u00edsticas de todos los hosts en el cl\u00faster. Reiniciar las estad\u00edsticas<\/a><\/noindex> tambi\u00e9n es necesario en cada host del cl\u00faster, no se puede hacer solo en el maestro.<\/p>\n
<\/p>\n
Este enfoque nos permiti\u00f3 ahorrar varios decenas de gigabytes al eliminar \u00edndices que nunca se usaron, as\u00ed como agregar los \u00edndices faltantes en tablas poco utilizadas.<\/p>\n
<\/p>\n
En conclusi\u00f3n<\/h3>\n
<\/p>\n
Por supuesto, pr\u00e1cticamente todas las diagn\u00f3sticas se pueden configurar lista de excepciones<\/a><\/noindex>. De este modo, se pueden implementar r\u00e1pidamente las comprobaciones en su aplicaci\u00f3n, evitando la aparici\u00f3n de nuevos errores y luego corrigiendo gradualmente los antiguos.<\/p>\n
<\/p>\n
Algunas diagn\u00f3sticas pueden ejecutarse ya en pruebas funcionales justo despu\u00e9s de haber realizado las migraciones de la base de datos. Y esta es, sin duda, una de las caracter\u00edsticas m\u00e1s potentes de mi biblioteca. Puede ver un ejemplo de uso en demo<\/a><\/noindex>.<\/p>\n
<\/p>\n
Las comprobaciones de \u00edndices no utilizados o faltantes, as\u00ed como el bloat, solo tienen sentido realizarse sobre una base de datos real. Los valores recopilados pueden ser registrados en ClickHouse<\/a><\/noindex> o enviados al sistema de monitoreo.<\/p>\n
<\/p>\n
Espero sinceramente que pg-index-health<\/strong> sea \u00fatil y demandada. Tambi\u00e9n puede contribuir al desarrollo de la biblioteca informando sobre problemas encontrados y sugiriendo nuevas diagn\u00f3sticas.<\/p>\n
Fuente: habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"
\u041f\u0440\u0438\u0432\u0435\u0442. \u041c\u0435\u043d\u044f \u0437\u043e\u0432\u0443\u0442 \u0412\u0430\u043d\u044f, \u0438 \u044f Java-\u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a. \u0422\u0430\u043a \u043f\u043e\u043b\u0443\u0447\u0438\u043b\u043e\u0441\u044c, \u0447\u0442\u043e \u044f \u043c\u043d\u043e\u0433\u043e \u0440\u0430\u0431\u043e\u0442\u0430\u044e \u0441 PostgreSQL \u2013 \u0437\u0430\u043d\u0438\u043c\u0430\u044e\u0441\u044c \u043d\u0430\u0441\u0442\u0440\u043e\u0439\u043a\u043e\u0439 \u0411\u0414, \u043e\u043f\u0442\u0438\u043c\u0438\u0437\u0430\u0446\u0438\u0435\u0439 \u0441\u0442\u0440\u0443\u043a\u0442\u0443\u0440\u044b, \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u044c\u044e \u0438 \u043d\u0435\u043c\u043d\u043e\u0433\u043e \u0438\u0433\u0440\u0430\u044e \u0432 DBA \u043f\u043e \u0432\u044b\u0445\u043e\u0434\u043d\u044b\u043c. \u0417\u0430 \u043f\u043e\u0441\u043b\u0435\u0434\u043d\u0435\u0435 \u0432\u0440\u0435\u043c\u044f \u044f \u043f\u0440\u0438\u0432\u0451\u043b \u0432 \u043f\u043e\u0440\u044f\u0434\u043e\u043a \u043d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u043e \u0431\u0430\u0437 \u0434\u0430\u043d\u043d\u044b\u0445 \u0432 \u043d\u0430\u0448\u0438\u0445 \u043c\u0438\u043a\u0440\u043e\u0441\u0435\u0440\u0432\u0438\u0441\u0430\u0445 \u0438 \u043d\u0430\u043f\u0438\u0441\u0430\u043b java-\u0431\u0438\u0431\u043b\u0438\u043e\u0442\u0435\u043a\u0443 pg-index-health, \u043a\u043e\u0442\u043e\u0440\u0430\u044f \u043e\u0431\u043b\u0435\u0433\u0447\u0430\u0435\u0442 \u044d\u0442\u0443 \u0440\u0430\u0431\u043e\u0442\u0443, \u044d\u043a\u043e\u043d\u043e\u043c\u0438\u0442 \u043c\u043e\u0451 \u0432\u0440\u0435\u043c\u044f \u0438 \u043f\u043e\u043c\u043e\u0433\u0430\u0435\u0442 […]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":72876,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-72875","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\n","aioseo_head_json":{"title":"\ud83e\udd47La salud de los \u00edndices en PostgreSQL desde la perspectiva de un desarrollador de Java | ProHoster","description":"Hola. Me llamo Vanya y soy desarrollador de Java.","canonical_url":"https:\/\/prohoster.info\/es\/blog\/administrirovanie\/zdorove-indeksov-v-postgresql-glazami-java-razrabotchika","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"es_ES","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u0417\u0434\u043e\u0440\u043e\u0432\u044c\u0435 \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432 \u0432 PostgreSQL \u0433\u043b\u0430\u0437\u0430\u043c\u0438 Java-\u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a\u0430 | ProHoster","og:description":"\u041f\u0440\u0438\u0432\u0435\u0442. \u041c\u0435\u043d\u044f \u0437\u043e\u0432\u0443\u0442 \u0412\u0430\u043d\u044f, \u0438 \u044f Java-\u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a.","og:url":"https:\/\/prohoster.info\/es\/blog\/administrirovanie\/zdorove-indeksov-v-postgresql-glazami-java-razrabotchika","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2020-03-05T05:42:09+00:00","article:modified_time":"2020-03-05T05:42:09+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"72875","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":null,"breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 18:43:29","updated":"2022-09-27 23:10:08","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/es\/wp-json\/wp\/v2\/posts\/72875","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/es\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/es\/wp-json\/wp\/v2\/comments?post=72875"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/es\/wp-json\/wp\/v2\/posts\/72875\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/es\/wp-json\/wp\/v2\/media\/72876"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/es\/wp-json\/wp\/v2\/media?parent=72875"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/es\/wp-json\/wp\/v2\/categories?post=72875"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/es\/wp-json\/wp\/v2\/tags?post=72875"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}