Inkonsistenzen in Datenbankindexen können aus verschiedenen Gründen auftreten, darunter Softwarefehler, Hardwareprobleme oder zugrunde liegenden Verhaltensänderungen wie Sortierreihenfolge-Änderungen.
Die PostgreSQL-Community hat Tools entwickelt, mit denen sich solche Probleme erkennen und beheben lassen.
Dazu gehören Tools wie amcheck
, die von der PostgreSQL-Community empfohlen werden, um Konsistenzprobleme zu identifizieren, einschließlich Problemen, die in früheren Versionen von PostgreSQL 14 aufgetreten sind.
Dieses Playbook dient als Referenz für AlloyDB for PostgreSQL-Nutzer, die solche Probleme haben. Wir hoffen, dass diese Seite Informationen enthält, die auch anderen PostgreSQL-Nutzern helfen, inkonsistente B-Baum-Indexe zu identifizieren und zu beheben. Unser Ziel ist es, dieses Dokument kontinuierlich als Ressource für die breitere Open-Source-Community zu verbessern. Wenn Sie uns Feedback geben möchten, verwenden Sie bitte die Schaltfläche Feedback senden oben und unten auf dieser Seite.
Zum Beheben von Inkonsistenzen eines Index sind folgende Schritte erforderlich:
-
Bevor Sie mit der Neuindexierung beginnen, sollten Sie Ihre Datenbank sichern, die richtigen Berechtigungen festlegen, Ihre
psql
-Clientversion prüfen und dieamcheck
-Erweiterung aktivieren. Prüfen Sie auf inkonsistente B-Baum-Indexe.
Um die Indexe zu identifizieren, für die Sie Inkonsistenzen beheben müssen, müssen Sie alle B-Baum-Indexe mit Inkonsistenzen und alle eindeutigen und Primärschlüsselverletzungen identifizieren.
Inkonsistenzen des Index korrigieren.
Die Neuindexierung eines Index behebt alle Inkonsistenzen. Möglicherweise müssen Sie die Speichereinstellungen Ihrer Instanz anpassen, um die Leistung zu verbessern.
Neuindexierungsvorgänge beobachten.
Wir empfehlen Ihnen, den Fortschritt der Neuindexierung zu überwachen, um sicherzustellen, dass der Vorgang fortgesetzt und nicht blockiert wird.
Prüfen, ob die Indexe konsistent sind
Nachdem Sie den Index neu indexiert haben, sollten Sie prüfen, ob er keine Inkonsistenzen enthält.
Hinweis
Daten Ihres AlloyDB-Clusters sichern
Um sicherzustellen, dass während der Neuindexierung keine Daten verloren gehen, empfehlen wir Ihnen, die Daten Ihres Clusters zu sichern. Weitere Informationen finden Sie unter On-Demand-Sicherung erstellen.
Berechtigung alloydbsuperuser
festlegen
Um die Schritte auf dieser Seite ausführen zu können, benötigen Sie die Berechtigung alloydbsuperuser
. Weitere Informationen finden Sie unter Vordefinierte PostgreSQL-Rollen in AlloyDB.
Achten Sie darauf, dass die psql
-Clientversion 9.6 oder höher ist
Damit Sie die Schritte auf dieser Seite ausführen können, muss Ihre psql
-Clientversion mindestens 9.6 sein. Führen Sie den Befehl psql --version
aus, um die aktuelle psql
-Clientversion zu prüfen.
Amcheck-Erweiterung aktivieren
Um auf Indexinkonsistenzen zu prüfen, müssen Sie die amcheck
-Erweiterung aktivieren.
Codebeispiel
CREATE EXTENSION amcheck;
Auf inkonsistente B-Baum-Indexe prüfen
In folgenden Abschnitten wird beschrieben, wie man auf inkonsistente B-Baum-Indexe prüft. Dazu werden Inkonsistenzen eines Index sowie eindeutige und Primärschlüsselverstöße geprüft.
Auf Inkonsistenzen prüfen
Führen Sie folgende Anweisung in jeder Ihrer Datenbanken aus, um alle B-Baum-Indexe auf Inkonsistenzen zu prüfen:
Codebeispiel
DO $$ DECLARE r RECORD; version varchar(100); BEGIN RAISE NOTICE 'Started amcheck on database: %', current_database(); SHOW server_version into version; SELECT split_part(version, '.', 1) into version; FOR r IN SELECT c.oid, c.oid::regclass relname, i.indisunique FROM pg_index i JOIN pg_opclass op ON i.indclass[0] = op.oid JOIN pg_am am ON op.opcmethod = am.oid JOIN pg_class c ON i.indexrelid = c.oid JOIN pg_namespace n ON c.relnamespace = n.oid WHERE am.amname = 'btree' AND c.relpersistence != 't' AND c.relkind = 'i' AND i.indisready AND i.indisvalid LOOP BEGIN RAISE NOTICE 'Checking index %:', r.relname; PERFORM bt_index_check(index => r.oid, heapallindexed => r.indisunique); EXCEPTION WHEN undefined_function THEN RAISE EXCEPTION 'Failed to find the amcheck extension'; WHEN OTHERS THEN RAISE LOG 'Failed to check index %: %', r.relname, sqlerrm; RAISE WARNING 'Failed to check index %: %', r.relname, sqlerrm; END; END LOOP; RAISE NOTICE 'Finished amcheck on database: %', current_database(); END $$;
Die Ausgabe sollte in etwa so aussehen:
Codebeispiel
NOTICE: Checking index t_pkey: NOTICE: Checking index t_i_key: WARNING: Failed to check index t_i_key: item order invariant violated for index "t_i_key" NOTICE: Checking index t_j_key: WARNING: Failed to check index t_j_key: item order invariant violated for index "t_j_key" NOTICE: Checking index ij: WARNING: Failed to check index ij: item order invariant violated for index "ij"
Weitere Informationen zum Anzeigen von Logs finden Sie unter Logs mit dem Log-Explorer ansehen.
Verstöße gegen eindeutige und Primärschlüssel identifizieren und beheben
In diesem Abschnitt wird beschrieben, wie Sie Ihren Index auf Verstöße gegen eindeutige und Primärschlüssel prüfen und gegebenenfalls beheben können.
Verstöße gegen eindeutige Schlüssel identifizieren
Verstöße gegen eindeutige Schlüssel müssen behoben werden, bevor Sie einen Index neu indexieren. Führen Sie folgenden Befehl in jeder einzelnen Datenbank aus, um nach eindeutigen Verstößen zu suchen:
Codebeispiel
WITH q AS ( /* this gets info for all UNIQUE indexes */ SELECT indexrelid::regclass as idxname, indrelid::regclass as tblname, indcollation, pg_get_indexdef(indexrelid), format('(%s)',(select string_agg(quote_ident(attname), ', ') from pg_attribute a join unnest(indkey) ia(nr) on ia.nr = a.attnum where attrelid = indrelid)) as idxfields, COALESCE(substring(pg_get_indexdef(indexrelid) FROM '[)] (WHERE .*)$'), '') as whereclause FROM pg_index WHERE indisunique /* next line excludes indexes not affected by collation changes */ AND trim(replace(indcollation::text, '0', '')) != '' ) SELECT /* the format constructs the query to execute for each index */ format( $sql$ DO $$ BEGIN RAISE NOTICE 'checking index %3$I%2$I on table %1$I %4$I'; END;$$; SELECT this, prev, /* we detect both reversed ordering or just not unique */ (CASE WHEN this = prev THEN 'DUPLICATE' ELSE 'BACKWARDS' END) as violation_type FROM (SELECT %2$s AS this, lag(%2$s) OVER (ORDER BY %2$s) AS prev FROM %1$s %4$s ) s WHERE this <= prev and this IS NOT NULL and prev IS NOT NULL; /* change to just '<' if looking for reverse order in index */ $sql$, tblname, idxfields, idxname, whereclause ) FROM q -- LIMIT 20 /* may use limit for testing */ -- the next line tells psql to executes this query and then execute each returned line separately \gexec
Die Ausgabe dieses Skripts sieht in etwa so aus:
Ausgabe
NOTICE: checking index=users_email_key on table=users key_columns="(email)" NOTICE: checking index=games_title_key on table=games key_columns="(title)" this | prev | violation_type --------------------+--------------------+---------------- Game #16 $soccer 2 | Game #16 $soccer 2 | DUPLICATE Game #18 $soccer 2 | Game #18 $soccer 2 | DUPLICATE Game #2 $soccer 2 | Game #2 $soccer 2 | DUPLICATE Game #5 $soccer 2 | Game #5 $soccer 2 | DUPLICATE
In dieser Ausgabe enthält die Tabellenüberschrift NOTICE
den Index, die Spalte und die Tabelle für die darunter angezeigten Werte. Wenn Ihre Ausgabe Zeilen mit DUPLICATE
oder BACKWARDS
enthält, ist der Index beschädigt und muss möglicherweise korrigiert werden. Zeilen mit BACKWARDS
weisen auf mögliche doppelte Werte hin, die eventuell ausgeblendet sind. Wenn einer dieser Einträge in der Tabelle angezeigt wird, finden Sie weitere Informationen unter Verstöße vom Typ „Schlüsselduplikat“ beheben.
Verstöße vom Typ "Schlüsselduplikat" beheben
Wenn Sie einen doppelten eindeutigen Index identifiziert haben oder eine Neuindexierung aufgrund eines Verstoßes gegen die doppelten Schlüssel fehlschlägt, führen Sie die folgenden Schritte aus, um die doppelten Schlüssel zu finden und zu entfernen.
Extrahieren Sie
key_columns
aus der TabellenüberschriftNOTICE
, wie in der vorherigen Beispielausgabe gezeigt. Im folgenden Beispiel istemail
die Schlüsselspalte.Ausgabe
NOTICE: checking index=users_email_key on table=users key_columns="(email)"
Verwenden Sie diese Werte in KEY_COLUMNS in der Abfrage in Schritt 3.
Suchen Sie das Schema für Ihre Tabelle. Stellen Sie mit
psql
eine Verbindung zu Ihrer Datenbank her und führen Sie den folgenden Befehl aus:Codebeispiel
Der Wert in der\dt TABLE_NAME
schema
-Spalte ist der Wert, den Sie für SCHEMA_NAME in der Abfrage in Schritt 3 verwenden.Beispiel:
\dt games
Die Ausgabe sieht in etwa so aus:
List of relations Schema | Name | Type | Owner --------+-------+-------+---------- public | games | table | postgres (1 row)
Führen Sie die folgenden Anweisungen aus, um einen vollständigen Tabellenscan zu erzwingen und Schlüsselduplikate abzurufen.
Codebeispiel
SET enable_indexscan = off; SET enable_bitmapscan = off; SET enable_indexonlyscan = off; SELECT KEY_COLUMNS, count(*) FROM SCHEMA_NAME.TABLE_NAME GROUP BY KEY_COLUMNS HAVING count(*) > 1;
In der obigen Anweisung sind KEY_COLUMNS eine oder mehrere Spalten, die vom eindeutigen Index oder vom Primärschlüssel in der zu prüfenden Tabelle abgedeckt werden. Diese wurden bei der Prüfung auf Verstöße gegen eindeutige Schlüssel erkannt. Die Anweisung gibt die Schlüsselduplikate und die Anzahl der Duplikate zurück.
Beispiel:
SELECT name,count(*) FROM public.TEST_NAMES GROUP BY name HAVING count(*) > 1;
Die Ausgabe sieht in etwa so aus:
name | count --------------------+------- Johnny | 2 Peter | 2 (2 rows)
Fahren Sie in diesem Fall mit dem nächsten Schritt fort, um die Schlüsselduplikate zu entfernen.
Wenn eine der Spalten in KEY_COLUMNS den Wert „NULL“ hat, können Sie sie ignorieren, da eindeutige Einschränkungen nicht für NULL-Spalten gelten.
Wenn keine Schlüsselduplikate gefunden werden, können Sie mit Inkonsistente Indexe korrigieren fortfahren.
Optional, aber empfohlen: Erstellen Sie eine Sicherung der Datensätze mit Schlüsselduplikaten. Führen Sie die folgende Anweisung aus, um Sicherungseinträge zu erstellen:
In dieser Anweisung ist KEY_VALUES eine Liste an Werten, die aus dem Ergebnis des vorherigen Schritts kopiert wurden. Beispiel:Codebeispiel
CREATE TABLE SCHEMA_NAME.TABLE_NAME_bak AS SELECT * FROM SCHEMA_NAME.TABLE_NAME WHERE (KEY_COLUMNS) IN (KEY_VALUES);
Codebeispiel
CREATE TABLE public.TEST_NAMES_bak AS SELECT * FROM public.TEST_NAMES WHERE (name) IN (('Johnny'),('Peter'))
Bei einer großen Anzahl von Zeilen ist es einfacher, den Parameter ((KEY_VALUES )) in der Anweisung
IN
mit der AnweisungSELECT
aus Schritt 2 ohne den vcount
zu ersetzen. Beispiel:Codebeispiel
CREATE TABLE SCHEMA_NAME.TABLE_NAME_bak AS SELECT * FROM SCHEMA_NAME.TABLE_NAME WHERE (KEY_COLUMNS) IN ( SELECT (KEY_COLUMNS) FROM SCHEMA_NAME.TABLE_NAME GROUP BY (KEY_COLUMNS) HAVING count(*) > 1);
Fügen Sie dem Nutzer eine Replikationsrolle hinzu, um Trigger zu deaktivieren:
Codebeispiel
ALTER USER CURRENT_USER with REPLICATION; SET session_replication_role = replica;
Führen Sie die folgende Anweisung aus, um die Schlüsselduplikate zu löschen:
Codebeispiel
BEGIN; DELETE FROM SCHEMA_NAME.TABLE_NAME a USING ( SELECT min(ctid) AS ctid, KEY_COLUMNS FROM SCHEMA_NAME.TABLE_NAME GROUP BY KEY_COLUMNS HAVING count(*) > 1 ) b WHERE a.KEY_COLUMNS = b.KEY_COLUMNS AND a.ctid <> b.ctid;
Zum Beispiel für KEY_COLUMNS mit mehreren Spalten:
Codebeispiel
Dabei sind Tag und rnum KEY_COLUMNS.DELETE FROM public.test_random a USING ( SELECT min(ctid) AS ctid, day, rnum FROM public.test_random GROUP BY day, rnum HAVING count(*) > 1 ) b WHERE a.day=b.day and a.rnum = b.rnum AND a.ctid <> b.ctid;
Wenn Sie diese Anweisung ausführen, wird eine Zeile beibehalten und andere werden für jeden Satz doppelter Zeilen gelöscht. Wenn Sie steuern möchten, welche Version der Zeile gelöscht wird, führen Sie folgenden Filter in der Löschanweisung aus:
Codebeispiel
DELETE FROM SCHEMA_NAME.TABLE_NAME WHERE ( KEY_COLUMNS, ctid) = (KEY_VALUES, CTID_VALUE);
Führen Sie folgende Schritte aus, um zu prüfen, ob der
DELETE
-Befehl die erwartete Anzahl von Zeilen ohne Fehler zurückgegeben hat:Führen Sie folgende Anweisung aus, um die Zeilen zu ermitteln, in denen die Tabellen geändert wurden:
Codebeispiel
SELECT schemaname, relname, n_tup_del, n_tup_upd FROM pg_stat_xact_all_tables WHERE n_tup_del+n_tup_upd > 0;
Wenn alle Zeilen korrekt sind, führen Sie den Commit der
DELETE
-Transaktion durch:Codebeispiel
END;
Wenn Fehler auftreten, heben Sie die Änderungen auf, um sie zu beheben:
Codebeispiel
ROLLBACK;
Nachdem die Schlüsselduplikate gelöscht wurden, können Sie den Index neu indexieren.
Inkonsistente Indexe korrigieren
In den folgenden Abschnitten wird beschrieben, wie Sie Indexinkonsistenzen in Ihrer Instanz beheben können.
Je nach Konfiguration Ihrer Datenbank müssen Sie möglicherweise für jeden in den vorherigen Schritten identifizierten Index Folgendes tun:
Wenn die Neuindexierung aufgrund von Verstößen gegen Fremdschlüssel fehlschlägt, müssen Sie diese Verstöße finden und beheben.
Führen Sie den Vorgang zum Erstellen eines neuen Index noch einmal aus.
Neuindexierung Ihres Index vorbereiten
Indexgröße ermitteln
Das Indexieren größerer Datenbanken erfordert mehr Zeit als das kleinerer Datenbanken. Um die Geschwindigkeit von Index- und Neuindexierungsvorgängen größerer Datenbanken zu verbessern, können Sie diesen Vorgängen mehr Arbeitsspeicher und CPU-Leistung zuweisen. Dies ist ein wichtiger Schritt bei der Planung Ihres Neuindexierungsvorgangs. Nachdem Sie die Indexgröße kennen, können Sie die vom Neuindexierungsvorgang verwendete Speichergröße festlegen und die Anzahl der parallelen Worker festlegen.
Führen Sie die folgende Anweisung aus, um die Größe des Index, den Sie korrigieren möchten, in Kilobyte zu ermitteln:
Codebeispiel
SELECT i.relname AS index_name, pg_size_pretty(pg_relation_size(x.indexrelid)) AS index_size FROM pg_index x JOIN pg_class i ON i.oid = x.indexrelid WHERE i.relname = 'INDEX_NAME';
Die Ausgabe dieser Anweisung sieht so aus:
Ausgabe
index_name | index_size ------------+------------ my_index | 16 kB (1 row)
Speichergröße für die Neuindexierung festlegen
Abhängig von der Größe des Index, wie im vorherigen Abschnitt beschrieben, ist es wichtig, einen geeigneten Wert für den Konfigurationsparameter maintenance_work_mem
festzulegen. Dieser Parameter gibt die Speichermenge an, die für die Neuindexierung verwendet werden soll. Wenn Ihre Indexgröße beispielsweise größer als 15 GB ist, empfehlen wir, den Wartungsspeicher anzupassen.
Im folgenden Beispiel wird gezeigt, wie maintenance_work_mem
festgelegt wird:
Codebeispiel
SET maintenance_work_mem TO "1GB";
Das Indexieren größerer Datenbanken erfordert mehr Zeit als das kleinerer Datenbanken. Um die Geschwindigkeit von Index- und Neuindexierungsvorgängen zu verbessern, empfehlen wir, maintenance_work_mem
während dieses Neuindexierungsvorgangs auf mindestens 2 % des Instanzspeichers für Instanzen mit 4 GB oder mehr Arbeitsspeicher zu setzen.
Anzahl der parallelen Worker festlegen
Sie können die Anzahl der parallelen Worker für die Neuindexierung erhöhen, indem Sie den Konfigurationsparameter max_parallel_maintenance_workers
in der Datenbank festlegen. Der Standardwert dieses Parameters ist „2“. Sie können ihn jedoch auf einen höheren Wert festlegen, um die Anzahl der Worker für die Neuindexierung zu erhöhen. Bei Instanzen mit 8 oder mehr vCPU-Kernen empfehlen wir, den Wert des max_parallel_maintenance_workers
-Flags auf 4 zu setzen.
Im Folgenden wird beschrieben, wie Sie die für diese Parameter festgelegten Werte ermitteln:
Codebeispiel
SHOW max_parallel_maintenance_workers; SHOW max_worker_processes; SHOW max_parallel_workers;
Der Parameter max_parallel_maintenance_workers
ist eine Teilmenge von max_worker_processes
und durch max_parallel_workers
begrenzt. Wenn Sie mehr parallele Worker benötigen, erhöhen Sie den Wert von max_worker_processes
und max_parallel_workers
.
Im folgenden Beispiel wird gezeigt, wie max_parallel_maintenance_workers
festgelegt wird:
Codebeispiel
SET max_parallel_maintenance_workers TO 4;
Der Parameter max_parallel_maintenance_workers
garantiert nicht die Zuweisung von Workern. Um zu prüfen, ob durch die Neuindexierung mehrere parallele Worker gestartet wurden, führen Sie nach dem Start der Neuindexierung die folgende Abfrage in einer anderen Sitzung aus:
Codebeispiel
SELECT leader.leader_pid,leader.pid "worker_pid",leader.query FROM pg_stat_activity leader, pg_stat_activity worker WHERE leader.leader_pid = worker.pid;
Index neu indexieren.
Sie können Indexe mit dem Dienstprogramm pg_repack
neu indexieren, ohne Ihre Produktionsarbeitslast zu blockieren. Dieses Dienstprogramm automatisiert und vereinfacht den gleichzeitigen Neuindexierungsprozess, sodass Sie eine Neuindexierung ohne Ausfallzeiten durchführen können. Verwenden Sie für dieses Verfahren die pg_repack
-Version 1.4.7.
Führen Sie folgende Schritte aus, um Ihren Index mit pg_repack
neu zu indexieren:
Laden Sie das Dienstprogramm
pg_repack
von der Seite „pg_repack” herunter, kompilieren und installieren Sie es.Erstellen Sie die Erweiterung
pg_repack
:Codebeispiel
CREATE EXTENSION pg_repack;
Führen Sie folgenden Befehl aus, um Ihren Index gleichzeitig neu zu indexieren:
Codebeispiel
pg_repack -h HOSTIP -p 5432 -U USERNAME -d "DATABASE_NAME" -i "INDEX_NAME" --no-superuser-check --no-kill-backend --wait-timeout=3600
Die Ausgabe dieses Befehls sieht in etwa so aus:
Ausgabe
INFO: repacking index "public.t_i_key"
Wenn beim Ausführen von
pg_repack
Fehler aufgetreten sind, können Sie diese beheben und es noch einmal versuchen. Nachdem Sie alle Indexe für eindeutige Schlüssel und Primärschlüssel korrigiert haben, sollten Sie nach Fremdschlüsselverstößen suchen und alle gefundenen beheben.
Fremdschlüsselverstöße ermitteln und beheben
Informationen zum Suchen und Beheben von Verstößen gegen Fremdschlüssel finden Sie unter Fremdschlüsselverstöße ermitteln und beheben.
Neuindexierungsvorgänge überwachen
Gelegentlich kann der Neuindexierungsvorgang durch andere Sitzungen blockiert werden. Wir empfehlen, dies alle vier Stunden zu prüfen. Wenn der Neuindexierungsvorgang blockiert ist, können Sie die Blockierungssitzung abbrechen, damit der Neuindexierungsvorgang abgeschlossen werden kann.
Führen Sie folgende Schritte aus, um blockierende und wartende Sitzungen zu identifizieren und sie dann im INDEX-Vorgang abzubrechen:
Führen Sie die folgende Abfrage aus, um blockierende Sitzungen zu ermitteln:
Codebeispiel
SELECT pid, usename, pg_blocking_pids(pid) AS blocked_by, query AS blocked_query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;
Führen Sie die folgende Abfrage mit der PID der blockierenden Sitzung aus der vorherigen Abfrage aus, um eine Sitzung abzubrechen:
Codebeispiel
SELECT pg_cancel_backend(PID);
Prüfen, ob Ihre Indexe konsistent sind
Prüfen Sie bei jedem einzelnen inkonsistenten Index weiterhin auf Indexinkonsistenzen. Nachdem Sie alle inkonsistenten Indexe und Schlüsselverstöße Ihrer Instanz behoben haben, können Sie prüfen, ob Probleme vorliegen. Dazu führen Sie die Schritte in den vorherigen Abschnitten aus: