{"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 Wanja und bin Java-Entwickler. Es hat sich so ergeben, dass ich viel mit PostgreSQL arbeite \u2013 ich k\u00fcmmere mich um die Datenbankkonfiguration, die Struktur, die Leistung und spiele ein wenig am Wochenende DBA.<\/p>\n

<\/p>\n

In letzter Zeit habe ich mehrere Datenbanken in unseren Mikrodiensten in Ordnung gebracht und eine Java-Bibliothek geschrieben pg-index-health<\/a><\/noindex>, die diese Arbeit erleichtert, meine Zeit spart und hilft, einige typische Fehler, die Entwickler machen, zu vermeiden. Genau \u00fcber 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 11. Version getestet. Die minimal unterst\u00fctzte Version ist 9.6.<\/p>\n

<\/p>\n

Vorgeschichte<\/h3>\n

<\/p>\n

Vor etwa einem Jahr begann alles mit einer f\u00fcr mich seltsamen Situation: die gleichzeitige Erstellung eines Indexes an einem leeren Ort endete mit einem Fehler. Der Index blieb, wie es nun mal ist, im ung\u00fcltigen Zustand in der Datenbank. Die Analyse der Protokolle zeigte einen Mangel an temp_file_limit<\/a><\/noindex>. Und dann ging es los... Als ich tiefer grub, entdeckte ich eine ganze Reihe von Problemen in der Datenbankkonfiguration und packte, mit einem Funkeln in den Augen, voller Elan an, diese zu beheben.<\/p>\n

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

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

<\/p>\n

Wahrscheinlich ist die Metapher \u00fcber Postgres, das man auf einer Kaffeemaschine ausf\u00fchren kann, den meisten schon ziemlich leid, aber... die Standardkonfiguration wirft tats\u00e4chlich einige Fragen auf. Zumindest sollte man auf maintenance_work_mem<\/strong>, temp_file_limit<\/strong>, statement_timeout<\/strong> und lock_timeout<\/strong>.<\/p>\n

<\/p>\n

achten. In unserem Fall maintenance_work_mem<\/strong> betrug der Standardwert 64 MB, w\u00e4hrend temp_file_limit<\/strong> wir etwa 2 GB ben\u00f6tigten \u2013 uns fehlte einfach der Speicher, um einen Index auf einer gro\u00dfen Tabelle zu erstellen.<\/p>\n

<\/p>\n

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

<\/p>\n

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

<\/p>\n

Unsere Datenbanken laufen auf SSD-Laufwerken und wir verwenden eine HA<\/em>-Konfiguration mit mehreren Rechenzentren, einem Master-Host und n<\/em>-irgendwie vielen Replikaten. Der Platz auf der Festplatte ist f\u00fcr uns eine sehr wertvolle Ressource; er ist nicht weniger wichtig als die Leistung und der CPU-Verbrauch. Daher brauchen wir auf der einen Seite Indizes f\u00fcr schnelles Lesen, auf der anderen Seite wollen wir keine \u00fcberfl\u00fcssigen Indizes in der Datenbank sehen, da sie Platz verbrauchen und die Datenaktualisierung verlangsamen.<\/p>\n

<\/p>\n

Und so, nachdem ich alle ung\u00fcltigen Indizes<\/a><\/noindex> wiederhergestellt habe und mir die Vortr\u00e4ge von Oleg Bartunov angesehen habe<\/a><\/noindex>, ich habe mich entschieden, eine \u201egro\u00dfe\u201c Bereinigung durchzuf\u00fchren. Es stellte sich heraus, dass die Entwickler die Dokumentation zur Datenbank nicht gerne lesen. Sehr ungern. Dadurch entstehen zwei typische Fehler \u2013 manuell erstellter Index auf dem Prim\u00e4rschl\u00fcssel und ein \u00e4hnlicher \u201emanueller\u201c Index auf einer einzigartigen Spalte. Das Problem ist, dass diese nicht notwendig sind \u2013 Postgres erledigt alles selbst. Solche Indizes k\u00f6nnen bedenkenlos gel\u00f6scht werden, und daf\u00fcr gibt es eine Diagnose. duplicated_indexes<\/a><\/noindex>.<\/p>\n

<\/p>\n

Drittes Problem \u2013 sich \u00fcberschneidende Indizes.<\/h3>\n

<\/p>\n

Die meisten Anf\u00e4nger-Entwickler erstellen Indizes f\u00fcr eine einzelne Spalte. Mit der Zeit, wenn sie den Geschmack daf\u00fcr entwickeln, fangen die Leute an, ihre Abfragen zu optimieren und komplexere Indizes hinzuzuf\u00fcgen, die mehrere Spalten enthalten. So entstehen Indizes f\u00fcr Spalten. A<\/em>, A+B<\/em>, A+B+C<\/em> usw. Die ersten beiden dieser Indizes k\u00f6nnen bedenkenlos verworfen werden, da sie Pr\u00e4fixe des dritten sind. Das spart auch ordentlich Platz auf der Festplatte und daf\u00fcr gibt es eine Diagnose. intersected_indexes<\/a><\/noindex>.<\/p>\n

<\/p>\n

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

<\/p>\n

Postgres erlaubt es, Einschr\u00e4nkungen von Fremdschl\u00fcsseln ohne Angabe eines unterst\u00fctzenden Index zu erstellen. In vielen Situationen ist das kein Problem und zeigt sich sogar gar nicht ... bis zu einem gewissen Zeitpunkt ...<\/p>\n

<\/p>\n

So war es auch bei uns: einfach in einem bestimmten Moment blieb der Job, der nach Zeitplan l\u00e4uft und die Datenbank von Testbestellungen bereinigt, bei uns h\u00e4ngen. CPU und IO gingen durch die Decke, Abfragen wurden langsamer und brachen wegen Zeit\u00fcberschreitung ab, der Service zeigte Fehler 500. Eine schnelle Analyse. pg_stat_activity<\/a><\/noindex> zeigte, dass Abfragen der Art hingen:<\/p>\n

<\/p>\n

löschen von <table> wo id in (…)<\/code><\/pre>\n
<\/p>\n
In der Ziel-Tabelle war nat\u00fcrlich ein Index nach id vorhanden, und die Datens\u00e4tze wurden unter der Bedingung nur sehr wenig gel\u00f6scht. Es schien, als sollte alles funktionieren, aber leider tat es das nicht.<\/p>\n
<\/p>\n
Zur Hilfe kam ein wunderbares explain analyze<\/strong> und erkl\u00e4rte, dass neben dem L\u00f6schen von Datens\u00e4tzen in der Ziel-Tabelle auch die \u00dcberpr\u00fcfung der referenziellen Integrit\u00e4t durchgef\u00fchrt wird, und bei einer der verkn\u00fcpften Tabellen f\u00e4llt diese \u00dcberpr\u00fcfung in einen sequentiellen Scan<\/em> aufgrund des Fehlens eines geeigneten Index. So entstand die Diagnose. foreign_keys_without_index<\/a><\/noindex>.<\/p>\n
<\/p>\n
F\u00fcnftes Problem \u2013 null-Werte in Indizes.<\/h3>\n
<\/p>\n
Standardm\u00e4\u00dfig schlie\u00dft Postgres null-Werte in btree-Indizes ein, aber in der Regel sind sie dort nicht n\u00f6tig. Daher bem\u00fche ich mich, diese null-Werte zu eliminieren (Diagnose indexes_with_null_values<\/a><\/noindex>), indem ich partielle Indizes f\u00fcr nullable-Spalten vom Typ where  is not null<\/code>. Auf diese Weise gelang es mir, die Gr\u00f6\u00dfe eines unserer Indizes von 1877 MB auf 16 KB zu reduzieren. In einem unserer Dienste verringerte sich die Gesamtgr\u00f6\u00dfe der DB um 16 % (um 4,3 GB in absoluten Zahlen), indem Nullwerte aus den Indizes entfernt wurden. Eine enorme Einsparung an Speicherplatz bei relativ einfachen Anpassungen. \ud83d\ude42 <\/p>\n
<\/p>\n
Problem sechs \u2013 Fehlen von Prim\u00e4rschl\u00fcsseln<\/h3>\n
<\/p>\n
Aufgrund der Besonderheiten des Mechanismus MVCC in Postgres\u2019<\/a><\/noindex> kann eine solche Situation entstehen wie bloat<\/a><\/noindex>, wenn die Gr\u00f6\u00dfe Ihrer Tabelle schnell aufgrund der Vielzahl von toten Datens\u00e4tzen w\u00e4chst. Ich nahm naiv an, dass uns das nicht betreffen w\u00fcrde und dass so etwas mit unserer Datenbank nicht passieren w\u00fcrde, denn wir sind, wow!!!, schlie\u00dflich normale Entwickler\u2026 Wie dumm und naiv ich war\u2026<\/p>\n
<\/p>\n
Eines Tages nahm eine wunderbare Migration alle Datens\u00e4tze in einer gro\u00dfen und aktiv genutzten Tabelle und aktualisierte sie. Wir erhielten +100 GB an Tabellengr\u00f6\u00dfe aus dem Nichts. Es war verdammt \u00e4rgerlich, aber unser Ungl\u00fcck endete damit nicht. Nachdem nach 15 Stunden der Autovacuum f\u00fcr diese Tabelle abgeschlossen war, wurde klar, dass der physische Speicherplatz nicht zur\u00fcckkehren w\u00fcrde. Wir konnten den Dienst nicht stoppen und ein VACUUM FULL durchf\u00fchren, daher wurde beschlossen, pg_repack<\/a><\/noindex>. Und dann stellte sich heraus, dass pg_repack<\/em> nicht mit Tabellen ohne Prim\u00e4rschl\u00fcssel oder anderer Eindeutigkeitsbeschr\u00e4nkung umgehen kann, und in unserer Tabelle gab es 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 zu den Bloat von Tabellen und Indizes zu sammeln und rechtzeitig darauf zu reagieren.<\/p>\n
<\/p>\n
Probleme sieben und acht \u2013 Mangel an Indizes und ungenutzte Indizes<\/h3>\n
<\/p>\n
Die n\u00e4chsten beiden Diagnosen sind tables_with_missing_indexes<\/a><\/noindex> und unused_indexes<\/a><\/noindex> \u2013 in ihrer endg\u00fcltigen Form sind sie relativ neu aufgetaucht. Der Grund ist, dass man sie nicht einfach so hinzuf\u00fcgen konnte.<\/p>\n
<\/p>\n
Wie ich bereits schrieb, verwenden wir eine Konfiguration mit mehreren Replikaten, und die Leseauslastung auf verschiedenen Hosts ist prinzipiell unterschiedlich. Infolgedessen ergibt sich die Situation, dass bestimmte Tabellen und Indizes auf bestimmten Hosts praktisch nicht genutzt werden und f\u00fcr die Analyse Statistiken von allen Hosts im Cluster gesammelt werden m\u00fcssen. Die Statistiken m\u00fcssen<\/a><\/noindex> auch auf jedem Host im Cluster zur\u00fcckgesetzt werden, es kann nicht nur auf dem Master erfolgen.<\/p>\n
<\/p>\n
Dieser Ansatz hat es uns erm\u00f6glicht, mehrere Dutzend Gigabyte zu sparen, indem wir nicht verwendete Indizes entfernt und fehlende Indizes f\u00fcr selten genutzte Tabellen hinzugef\u00fcgt haben.<\/p>\n
<\/p>\n
Als Fazit<\/h3>\n
<\/p>\n
Nat\u00fcrlich kann praktisch f\u00fcr alle Diagnosen eine Ausnahmeliste<\/a><\/noindex>eingerichtet werden. So k\u00f6nnen Sie schnell \u00dcberpr\u00fcfungen in Ihrer Anwendung implementieren, um das Auftreten neuer Fehler zu verhindern, und dann schrittweise alte Fehler beheben.<\/p>\n
<\/p>\n
Einige Diagnosen k\u00f6nnen bereits bei funktionalen Tests direkt nach dem Einspielen der DB-Migrationen durchgef\u00fchrt werden. Und das ist wahrscheinlich eine der m\u00e4chtigsten Funktionen meiner Bibliothek. Ein Anwendungsbeispiel finden Sie in Demo<\/a><\/noindex>.<\/p>\n
<\/p>\n
\u00dcberpr\u00fcfungen auf nicht verwendete oder fehlende Indizes sowie auf Bloat sollten nur in einer echten DB durchgef\u00fchrt werden. Die gesammelten Werte k\u00f6nnen in ClickHouse<\/a><\/noindex> geschrieben 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\udd47Die Gesundheit von Indizes in PostgreSQL aus Sicht eines Java-Entwicklers | ProHoster","description":"Hallo. Ich hei\u00dfe Wanja und 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}]}}