Diagnostizieren einer Überlastung von Tabellen und Indizes

Sie können PostgreSQL Multiversion Concurrency Control (MVCC) verwenden, um die Datenintegrität zu wahren. PostgreSQL MVCC funktioniert, indem es eine interne Kopie aktualisierter oder gelöschter Zeilen (auch Tupel genannt) speichert, bis für eine Transaktion entweder ein Commit oder ein Rollback ausgeführt wird. Diese gespeicherte interne Kopie ist für Benutzer nicht sichtbar. Wenn diese nicht sichtbaren Kopien nicht regelmäßig von den Dienstprogrammen VACUUM oder AUTOVACUUM bereinigt werden, kann es jedoch zu einer Überlastung der Tabelle kommen. Wenn diese Option nicht aktiviert ist, kann die Überlastung der Tabelle zu erhöhten Speicherkosten führen und Ihre Verarbeitungsgeschwindigkeit verlangsamen.

In vielen Fällen reichen die Standardeinstellungen für VACUUM oder AUTOVACUUM in Aurora aus, um eine ungewollte Überlastung von Tabellen zu vermeiden. Möglicherweise möchten Sie jedoch überprüfen, ob eine Überlastung vorliegt, wenn in Ihrer Anwendung die folgenden Bedingungen vorliegen:

• Verarbeitet eine große Anzahl von Transaktionen in relativ kurzer Zeit zwischen VACUUM-Prozessen.

• Funktioniert schlecht und der Speicherplatz ist knapp.

Sammeln Sie zunächst möglichst genaue Informationen darüber, wie viel Speicherplatz tote Tupel belegen und wie viel Sie wiederherstellen können, indem Sie die Überlastung der Tabellen und Indizes bereinigen. Verwenden Sie dazu die pgstattuple-Erweiterung, um Statistiken über Ihren Aurora-Cluster zu sammeln. Weitere Informationen finden Sie unter pgstattuple. Die Berechtigungen zum Verwenden der pgstattuple-Erweiterung sind auf die Rolle pg_stat_scan_tables und die Datenbank-Superuser beschränkt.

Um die pgstattuple-Erweiterung in Aurora zu erstellen, verbinden Sie eine Client-Sitzung mit dem Cluster, z. B. psql oder pgAdmin, und verwenden Sie den folgenden Befehl:

CREATE EXTENSION pgstattuple;

Erstellen Sie die Erweiterung in jeder Datenbank, für die Sie ein Profil erstellen möchten. Verwenden Sie nach dem Erstellen der Erweiterung die Befehlszeilenschnittstelle (CLI), um zu messen, wie viel unbenutzbaren Speicherplatz Sie zurückgewinnen können. Bevor Sie Statistiken sammeln, ändern Sie die Cluster-Parametergruppe, indem Sie AUTOVACUUM auf 0 festlegen. Die Einstellung 0 verhindert, dass Aurora automatisch alle toten Tupel bereinigt, die Ihre Anwendung hinterlassen hat, was sich auf die Genauigkeit der Ergebnisse auswirken kann. Verwenden Sie den folgenden Befehl, um eine einfache Tabelle zu erstellen:

postgres=> CREATE TABLE lab AS SELECT generate_series (0,100000);
SELECT 100001

Im folgenden Beispiel führen wir die Abfrage mit aktiviertem AUTOVACUUM für den DB-Cluster aus. Der Wert dead_tuple_count ist 0, was bedeutet, dass AUTOVACUUM die veralteten Daten oder Tupel aus der PostgreSQL-Datenbank gelöscht hat.

Wenn Sie Informationen über die Tabelle mit pgstattuple sammeln möchten, geben Sie in der Abfrage den Namen einer Tabelle oder eine Objektkennung (OID) an:

postgres=> SELECT * FROM pgstattuple('lab');

    
    
table_len  | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
3629056    | 100001      | 2800028   | 77.16         | 0                | 0              | 0                  | 16616      | 0.46
(1 row)
        
    

In der folgenden Abfrage schalten wir AUTOVACUUM aus und geben einen Befehl ein, mit dem 25 000 Zeilen aus der Tabelle gelöscht werden. Infolgedessen steigt der Wert dead_tuple_count auf 25 000.

postgres=> DELETE FROM lab WHERE generate_series < 25000;    

DELETE 25000  
   

SELECT * FROM pgstattuple('lab');
    

        
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
3629056 | 75001 | 2100028 | 57.87 | 25000 | 700000 | 19.29 | 16616 | 0.46
(1 row)        
    
    

Starten Sie einen VACUUM-Prozess, um diese toten Tupel zurückzugewinnen.

Beobachten von Überlastungen ohne Unterbrechung Ihrer Anwendung

Die Einstellungen in einem Aurora-Cluster sind optimiert, um die bewährten Methoden für die meisten Workloads bereitzustellen. Möglicherweise möchten Sie jedoch einen Cluster optimieren, damit er besser zu Ihren Anwendungen und Nutzungsmustern passt. In diesem Fall können Sie die pgstattuple-Erweiterung verwenden, ohne eine aktive Anwendung zu unterbrechen. Führen Sie dazu die folgenden Schritte aus:

1. Klonen Sie Ihre Aurora-Instance.

2. Ändern Sie die Parameterdatei, um AUTOVACUUM im Klon zu deaktivieren.

3. Führen Sie eine pgstattuple-Abfrage durch, während Sie den Klon mit einem Beispiel-Workload oder mit pgbench testen, einem Programm zum Ausführen von Benchmark-Tests in PostgreSQL. Weitere Informationen finden Sie unter pgbench.

Nachdem Sie Ihre Anwendungen ausgeführt und das Ergebnis angezeigt haben, verwenden Sie pg_repack oder VACUUM FULL für die wiederhergestellte Kopie und vergleichen Sie die Unterschiede. Wenn Sie einen deutlichen Rückgang der Werte dead_tuple_count, dead_tuple_len oder dead_tuple_percent feststellen, passen Sie den Bereinigungsplan Ihres Produktions-Clusters an, um die Überlastung zu minimieren.

Vermeiden von Überlastung in temporären Tabellen

Wenn Ihre Anwendung temporäre Tabellen erstellt, vergewissern Sie sich, dass Ihre Anwendung diese temporären Tabellen entfernt, wenn sie nicht mehr benötigt werden. Automatische Bereinigungsprozesse finden keine temporären Tabellen. Wenn diese Option nicht aktiviert ist, können temporäre Tabellen schnell zu einer Überlastung der Datenbank führen. Darüber hinaus kann sich die Überlastung auf die Systemtabellen ausweiten. Hierbei handelt es sich um interne Tabellen, die PostgreSQL-Objekte und -Attribute verfolgen, wie pg_attribute und pg_depend.

Wenn eine temporäre Tabelle nicht mehr benötigt wird, können Sie eine TRUNCATE-Anweisung verwenden, um die Tabelle zu leeren und Speicherplatz freizugeben. Bereinigen Sie dann die Tabellen pg_attribute und pg_depend manuell. Durch das Bereinigen dieser Tabellen wird sichergestellt, dass durch das kontinuierliche Erstellen und Kürzen/Löschen temporärer Tabellen keine Tupel hinzugefügt werden und das System nicht überlastet wird.

Sie können dieses Problem beim Erstellen einer temporären Tabelle vermeiden, indem Sie die folgende Syntax verwenden, die die neuen Zeilen löscht, wenn für Inhalt ein Commit ausgeführt wird:

CREATE TEMP TABLE IF NOT EXISTS table_name(table_description) ON COMMIT DELETE ROWS;

Die ON COMMIT DELETE ROWS-Klausel kürzt die temporäre Tabelle, wenn für die Transaktion ein Commit ausgeführt wird.

Vermeiden von Überlastung in Indizes

Wenn Sie ein indiziertes Feld in einer Tabelle ändern, führt die Indexaktualisierung zu einem oder mehreren toten Tupeln in diesem Index. Standardmäßig entfernt der automatische Bereinigungsprozess überflüssige Indizes, aber diese Bereinigung erfordert einen erheblichen Zeit- und Ressourcenaufwand. Um die Einstellungen für die Indexbereinigung beim Erstellen einer Tabelle anzugeben, schließen Sie die Klausel vacuum_index_cleanup ein. Standardmäßig ist die Klausel bei der Tabellenerstellung auf AUTO gesetzt, was bedeutet, dass der Server entscheidet, ob Ihr Index bereinigt werden muss, wenn er die Tabelle bereinigt. Sie können die Klausel auf EIN einstellen, um die Indexbereinigung für eine bestimmte Tabelle zu aktivieren, oder auf AUS, um die Indexbereinigung für diese Tabelle zu deaktivieren. Denken Sie daran, dass das Deaktivieren der Indexbereinigung zwar Zeit sparen kann, aber möglicherweise zu einer Überlastung des Index führen kann.

Sie können die Indexbereinigung manuell steuern, wenn Sie eine Tabelle über die Befehlszeile bereinigen. Wenn Sie eine Tabelle bereinigen und tote Tupel aus den Indizes entfernen möchten, schließen Sie die INDEX_CLEANUP-Klausel mit dem Wert EIN und dem Tabellennamen ein:

acctg=> VACUUM (INDEX_CLEANUP ON) receivables;
        
INFO: aggressively vacuuming "public.receivables"
VACUUM

Wenn Sie eine Tabelle bereinigen möchten, ohne die Indizes zu bereinigen, geben Sie den Wert AUS an:

acctg=> VACUUM (INDEX_CLEANUP OFF) receivables;
        
INFO: aggressively vacuuming "public.receivables"
VACUUM