{"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\/de\/blog\/administrirovanie\/zdorove-indeksov-v-postgresql-glazami-java-razrabotchika","title":{"rendered":"Die Gesundheit der Indizes in PostgreSQL aus der Sicht eines Java-Entwicklers","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Hallo.<\/p>\n

<\/p>\n

Ich hei\u00dfe Ivan und bin Java-Entwickler. Es hat sich ergeben, dass ich viel mit PostgreSQL arbeite \u2013 ich k\u00fcmmere mich um die Datenbankkonfiguration, Strukturoptimierung, Leistungssteigerung und spiele ein wenig am Wochenende DBA.<\/p>\n

<\/p>\n

In letzter Zeit habe ich mehrere Datenbanken in unseren Mikrodiensten optimiert und eine Java-Bibliothek geschrieben pg-index-health<\/a><\/noindex>, die diese Arbeit erleichtert, meine Zeit spart und hilft, einige typische Fehler zu vermeiden, die von Entwicklern gemacht werden. Genau um diese Bibliothek wird es heute gehen.<\/p>\n

<\/p>\n

\"Die<\/p>\n

<\/p>\n

Haftungsausschluss<\/h3>\n

<\/p>\n

Die Hauptversion von PostgreSQL, mit der ich arbeite, ist die 10. Alle von mir verwendeten SQL-Abfragen wurden auch auf der Version 11 getestet. Die minimal unterst\u00fctzte Version ist 9.6.<\/p>\n

<\/p>\n

Hintergrund<\/h3>\n

<\/p>\n

Es begann vor fast einem Jahr mit einer f\u00fcr mich seltsamen Situation: Das konkurrierende Erstellen eines Indexes endete unerwartet mit einem Fehler. Der Index selbst blieb, wie es oft der Fall ist, in einem ung\u00fcltigen Zustand in der Datenbank. Eine Analyse der Protokolle zeigte einen Mangel an temp_file_limit<\/a><\/noindex>. Und das war erst der Anfang\u2026 Als ich tiefer gruben, entdeckte ich eine ganze Reihe von Problemen in der Datenbankkonfiguration und krempelte die \u00c4rmel hoch, um sie mit leuchtenden Augen zu beheben.<\/p>\n

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

Das erste Problem \u2013 die Standardkonfiguration<\/h3>\n

<\/p>\n

Zugegeben, die Metapher, dass man Postgres auf einer Kaffeemaschine betreiben kann, ist m\u00f6glicherweise abgedroschen, aber\u2026 die Standardkonfiguration wirft tats\u00e4chlich einige Fragen auf. Zumindest sollte man auf Folgendes achten: maintenance_work_mem<\/strong>, temp_file_limit<\/strong>, statement_timeout<\/strong> und lock_timeout<\/strong>.<\/p>\n

<\/p>\n

In unserem Fall maintenance_work_mem<\/strong> lag standardm\u00e4\u00dfig bei 64 Megabyte, w\u00e4hrend temp_file_limit<\/strong> wir eigentlich etwa 2 Gigabyte ben\u00f6tigten \u2013 wir hatten einfach nicht gen\u00fcgend Speicher, um einen Index auf einer gro\u00dfen Tabelle zu erstellen.<\/p>\n

<\/p>\n

Deshalb habe ich pg-index-health<\/strong> eine Reihe wichtiger<\/a><\/noindex>, die meiner Meinung nach f\u00fcr jede Datenbank angepasst werden sollten.<\/p>\n

<\/p>\n

Das zweite Problem \u2013 doppelte Indizes<\/h3>\n

<\/p>\n

Unsere Datenbanken befinden sich auf SSDs, und wir nutzen HA<\/em>eine Konfiguration mit mehreren Rechenzentren, einem Master-Host und n<\/em>einer entsprechenden Anzahl von Replikaten. Speicherplatz auf der Festplatte ist f\u00fcr uns eine \u00e4u\u00dferst wertvolle Ressource; er ist ebenso wichtig wie Leistung und CPU-Verbrauch. Deshalb ben\u00f6tigen wir einerseits Indizes f\u00fcr schnelle Lesevorg\u00e4nge, m\u00f6chten andererseits aber keine \u00fcberfl\u00fcssigen Indizes in der Datenbank sehen, da diese Speicherplatz verbrauchen und die Aktualisierung von Daten verlangsamen.<\/p>\n

<\/p>\n

Und nachdem wir alle nicht g\u00fcltigen Indizes<\/a><\/noindex> wiederhergestellt haben und die Berichte von Oleg Bartunov durchgesehen haben<\/a><\/noindex>, ich beschloss, eine \u201egro\u00dfe\u201c Bereinigung vorzunehmen. Es stellte sich heraus, dass Entwickler die Dokumentation zur Datenbank \u00fcberhaupt nicht gerne lesen. Dies f\u00fchrt zu zwei typischen Fehlern \u2013 einem manuell erstellten Index auf dem Prim\u00e4rschl\u00fcssel und einem \u00e4hnlichen \u201emanuellen\u201c Index auf einer eindeutigen Spalte. Das Problem ist, dass diese nicht n\u00f6tig sind \u2013 Postgres erledigt alles selbst. Solche Indizes k\u00f6nnen ohne Bedenken gel\u00f6scht werden, und daf\u00fcr gibt es eine Diagnose. duplicated_indexes<\/a><\/noindex>.<\/p>\n

<\/p>\n

Das dritte Problem \u2013 \u00fcberlappende Indizes.<\/h3>\n

<\/p>\n

Die meisten unerfahrenen Entwickler erstellen Indizes f\u00fcr eine einzige Spalte. Mit der Zeit, nachdem sie Gefallen daran gefunden haben, beginnen die Leute, ihre Abfragen zu optimieren und komplexere Indizes zu erstellen, die mehrere Spalten enthalten. So entstehen Indizes f\u00fcr die Spalten A<\/em>, A+B<\/em>, A+B+C<\/em> usw. Die ersten beiden dieser Indizes k\u00f6nnen bedenkenlos entfernt werden, da sie Pr\u00e4fixe des dritten sind. Das spart ebenfalls erheblich Platz auf der Festplatte, und daf\u00fcr gibt es eine Diagnose. intersected_indexes<\/a><\/noindex>.<\/p>\n

<\/p>\n

Das vierte Problem \u2013 Fremdschl\u00fcssel ohne Indizes.<\/h3>\n

<\/p>\n

Postgres erm\u00f6glicht das Erstellen von Einschr\u00e4nkungen f\u00fcr Fremdschl\u00fcssel, ohne einen unterst\u00fctzenden Index anzugeben. In vielen Situationen ist das kein Problem und \u00e4u\u00dfert sich sogar erst sp\u00e4t...<\/p>\n

<\/p>\n

So erging es uns: Irgendwann begann der Job, der nach Zeitplan lief und die Datenbank von Testbestellungen befreite, unsere Hauptinstanz zu \u00fcberlasten. CPU- und IO-Ressourcen waren \u00fcberlastet, die Anfragen stockten und liefen aufgrund von Timeouts ins Leere, der Dienst lieferte Fehler 500. Eine schnelle Analyse pg_stat_activity<\/a><\/noindex> zeigte, dass die Anfragen wie folgt hingen:<\/p>\n

<\/p>\n

löschen von <table> wo id in (…)<\/code><\/pre>\n
<\/p>\n
Dabei war der Index nach id in der Zieltabelle nat\u00fcrlich vorhanden, und es wurden nur sehr wenige Datens\u00e4tze entfernt. Es schien, als m\u00fcsste alles funktionieren, aber leider tat es das nicht.<\/p>\n
<\/p>\n
Ein wunderbares explain analyze<\/strong> half uns, denn es stellte fest, dass neben der Entfernung von Datens\u00e4tzen in der Zieltabelle auch die \u00dcberpr\u00fcfung der referenziellen Integrit\u00e4t stattfand, wobei diese \u00dcberpr\u00fcfung auf einer der verkn\u00fcpften Tabellen in einen sequential scan<\/em> wegen fehlender geeigneter Indizes fiel. So entstand die Diagnose foreign_keys_without_index<\/a><\/noindex>.<\/p>\n
<\/p>\n
Das f\u00fcnfte Problem \u2013 null-Werte in Indizes.<\/h3>\n
<\/p>\n
Standardm\u00e4\u00dfig inkludiert Postgres Nullwerte in Btree-Indizes, aber in der Regel sind sie dort nicht erforderlich. Daher bem\u00fche ich mich, diese Nullwerte zu entfernen (Diagnose indizes_mit_nullwerten<\/a><\/noindex>), indem ich partielle Indizes auf nullable-Spalten erstelle, wie etwa where  is not null<\/code>. Auf diese Weise konnte ich die Gr\u00f6\u00dfe eines unserer Indizes von 1877 MB auf 16 kB reduzieren. In einem unserer Dienste verringerte sich die Gesamtgr\u00f6\u00dfe der Datenbank um 16 % (in absoluten Zahlen um 4,3 GB), indem ich Nullwerte aus den Indizes ausschloss. Eine enorme Einsparung an Speicherplatz durch relativ einfache Anpassungen. \ud83d\ude42 <\/p>\n
<\/p>\n
Problem sechs \u2013 Fehlen von Prim\u00e4rschl\u00fcsseln<\/h3>\n
<\/p>\n
Aufgrund der Eigenheiten des Mechanismus MVCC in Postgres<\/a><\/noindex> kann es zu einer Situation kommen, in der Bloat<\/a><\/noindex>die Gr\u00f6\u00dfe Ihrer Tabelle schnell aufgrund einer gro\u00dfen Anzahl von toten Datens\u00e4tzen w\u00e4chst. Ich nahm naiverweise an, dass uns das nicht betreffen w\u00fcrde und dass mit unserer Datenbank so etwas nicht passiert, schlie\u00dflich sind wir, wow!!!, normale Entwickler\u2026 Wie dumm und naiv ich war\u2026<\/p>\n
<\/p>\n
Eines sch\u00f6nen Tages f\u00fchrte eine wunderbare Migration dazu, dass alle Eintr\u00e4ge in einer gro\u00dfen, aktiv genutzten Tabelle aktualisiert wurden. Wir erhielten pl\u00f6tzlich +100 GB an zus\u00e4tzlichem Platz f\u00fcr die Tabelle. Es war \u00e4u\u00dferst frustrierend, aber unsere Probleme h\u00f6rten damit nicht auf. Nach 15 Stunden war der Autovacuum-Prozess f\u00fcr diese Tabelle abgeschlossen, und es wurde klar, dass der physische Platz nicht zur\u00fcckkehren w\u00fcrde. Den Dienst anzuhalten und ein VACUUM FULL durchzuf\u00fchren, war nicht m\u00f6glich, daher entschieden wir uns f\u00fcr pg_repack<\/a><\/noindex>. Und hier stellte sich heraus, dass pg_repack<\/em> nicht in der Lage ist, Tabellen ohne Prim\u00e4rschl\u00fcssel oder andere eindeutige Einschr\u00e4nkungen zu verarbeiten, und unsere Tabelle hatte keinen Prim\u00e4rschl\u00fcssel. So entstand die Diagnose tables_without_primary_key<\/a><\/noindex>.<\/p>\n
<\/p>\n
In der Version der Bibliothek 0.1.5<\/strong> wurde die M\u00f6glichkeit hinzugef\u00fcgt, Daten \u00fcber die Bloat von Tabellen und Indizes zu sammeln und entsprechend darauf zu reagieren.<\/p>\n
<\/p>\n
Die Probleme sieben und acht \u2013 Mangel an Indizes und ungenutzte Indizes<\/h3>\n
<\/p>\n
Die n\u00e4chsten beiden Diagnosen \u2013 tables_with_missing_indexes<\/a><\/noindex> und unused_indexes<\/a><\/noindex> \u2013 erschienen in ihrer endg\u00fcltigen Form relativ neu. Der Grund daf\u00fcr ist, dass sie nicht einfach hinzugef\u00fcgt werden konnten.<\/p>\n
<\/p>\n
Wie ich bereits erw\u00e4hnt habe, verwenden wir eine Konfiguration mit mehreren Replikaten, und die Lese-Last auf verschiedenen Hosts ist grunds\u00e4tzlich unterschiedlich. Infolgedessen ergibt sich eine Situation, in der einige Tabellen und Indizes auf bestimmten Hosts praktisch nicht verwendet werden, und um Analysen durchzuf\u00fchren, m\u00fcssen wir Statistiken von allen Hosts im Cluster sammeln. Statistiken zur\u00fccksetzen<\/a><\/noindex> muss ebenfalls auf jedem Host im Cluster erfolgen, es ist nicht m\u00f6glich, dies nur auf dem Master zu tun.<\/p>\n
<\/p>\n
Dieser Ansatz hat es uns erm\u00f6glicht, mehrere Dutzend Gigabyte durch das Entfernen von Indizes zu sparen, die nie verwendet wurden, und zus\u00e4tzlich fehlende Indizes f\u00fcr selten verwendete Tabellen hinzuzuf\u00fcgen.<\/p>\n
<\/p>\n
Zusammenfassend<\/h3>\n
<\/p>\n
Nat\u00fcrlich kann f\u00fcr nahezu alle Diagnosen eine Ausnahmeliste<\/a><\/noindex>konfiguriert werden. So k\u00f6nnen Sie schnell Pr\u00fcfungen in Ihrer Anwendung implementieren, um das Auftreten neuer Fehler zu verhindern, und dann schrittweise alte Fehler beheben.<\/p>\n
<\/p>\n
Ein Teil der Diagnosen kann bereits in Funktionstest durchgef\u00fchrt werden, sofort nach dem Einspielen der Datenbankmigrationen. Und das ist wahrscheinlich eine der st\u00e4rksten Funktionen meiner Bibliothek. Ein Beispiel f\u00fcr die Nutzung finden Sie in Demo<\/a><\/noindex>.<\/p>\n
<\/p>\n
\u00dcberpr\u00fcfungen auf ungenutzte oder fehlende Indizes sowie auf Bloat sollten sinnvollerweise nur auf einer realen Datenbank durchgef\u00fchrt werden. Die gesammelten Werte k\u00f6nnen in ClickHouse<\/a><\/noindex> oder an ein \u00dcberwachungssystem gesendet werden.<\/p>\n
<\/p>\n
Ich hoffe sehr, dass pg-index-health<\/strong> n\u00fctzlich und gefragt sein wird. Sie k\u00f6nnen auch zur Weiterentwicklung der Bibliothek beitragen, indem Sie auf entdeckte Probleme hinweisen und neue Diagnosen vorschlagen.<\/p>\n
Quelle: 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\udd47Der Gesundheitszustand von Indizes in PostgreSQL aus der Sicht eines Java-Entwicklers | ProHoster","description":"Hallo. Ich hei\u00dfe Ivan und ich bin Java-Entwickler.","canonical_url":"https:\/\/prohoster.info\/de\/blog\/administrirovanie\/zdorove-indeksov-v-postgresql-glazami-java-razrabotchika","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"de_DE","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\/de\/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\/de\/wp-json\/wp\/v2\/posts\/72875","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/de\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/de\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/de\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/de\/wp-json\/wp\/v2\/comments?post=72875"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/de\/wp-json\/wp\/v2\/posts\/72875\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/de\/wp-json\/wp\/v2\/media\/72876"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/de\/wp-json\/wp\/v2\/media?parent=72875"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/de\/wp-json\/wp\/v2\/categories?post=72875"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/de\/wp-json\/wp\/v2\/tags?post=72875"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}