Bewährte Methoden für das Massenhochladen von Daten in Azure Database for PostgreSQL – Flexible Server

Artikel
08/15/2024

GILT FÜR: Azure Database for PostgreSQL – Flexibler Server

In diesem Artikel werden verschiedene Verfahren für das Massenladen von Daten in Azure Database for PostgreSQL –- Flexible Server sowie bewährte Methoden für das anfängliche Laden von Daten in leere Datenbanken und das inkrementelle Laden von Daten beschrieben.

Lademethoden

Im Folgenden werden die Methoden zum Laden von Daten in der Reihenfolge des größten Zeitaufwands bis zum geringsten Zeitaufwand aufgeführt:

Ausführen des Befehls INSERT für einen einzelnen Datensatz.
Batch von 100–1.000 Zeilen pro Commit. Mithilfe eines Transaktionsblocks können Sie mehrere Datensätze pro Commit umschließen.
Ausführen von INSERT mit mehreren Zeilenwerten.
Führen Sie den Befehl COPY aus.

Die bevorzugte Methode zum Laden von Daten in eine Datenbank ist die Verwendung des Befehls COPY. Wenn der Befehl COPY nicht verwendet werden kann, ist die nächstbeste Methode die Anwendung des Befehls INSERT als Batch. Die optimale Methode zum Massenladen von Daten ist Multithreading mit einem COPY-Befehl.

Bewährte Methoden für erste Datenladevorgänge

Drop-Indexes

Bevor Sie das erste Mal Daten laden, sollten Sie alle Indizes in den Tabellen löschen. Es ist immer effizienter, die Indizes nach dem Laden der Daten zu erstellen.

DROP-Einschränkungen

Im Folgenden werden die wichtigsten DROP-Einschränkungen beschrieben:

Einschränkungen für eindeutige Schlüssel

Um eine hohe Leistung zu erzielen, empfiehlt es sich, die Einschränkungen für eindeutige Schlüssel vor dem ersten Laden der Daten zu entfernen und sie nach dem Laden der Daten neu zu erstellen. Durch das Ablegen eindeutiger Schlüsseleinschränkungen wird jedoch der Schutz vor duplizierten Daten abgebrochen.
Fremdschlüsseleinschränkungen

Fremdschlüsseleinschränkungen sollten vor dem ersten Laden der Daten entfernt und nach dem Laden der Daten neu erstellt werden.

Durch eine Änderung des Parameters session_replication_role in replica werden ebenfalls alle Fremdschlüsselüberprüfungen deaktiviert. Beachten Sie jedoch, dass die Änderung Daten in einem inkonsistenten Zustand hinterlassen kann, wenn sie nicht ordnungsgemäß verwendet wird.

Nicht protokollierte Tabellen

Wägen Sie die Vor- und Nachteile der Verwendung von nicht protokollierten Tabellen ab, bevor Sie sie in anfänglichen Datenladevorgängen verwenden.

Die Verwendung von nicht protokollierten Tabellen beschleunigt das Laden der Daten. Daten, die in nicht protokollierte Tabellen geschrieben wurden, werden nicht in das Write-Ahead-Protokoll geschrieben.

Nicht protokollierte Tabellen haben die folgenden Nachteile:

Sie sind nicht absturzsicher. Eine nicht protokollierte Tabelle wird nach einem Absturz oder nach unsauberem Herunterfahren automatisch abgeschnitten.
Daten aus nicht protokollierten Tabellen können nicht auf Standbyservern repliziert werden.

Verwenden Sie die folgenden Optionen, um eine nicht protokollierte Tabelle zu erstellen oder eine vorhandene Tabelle in eine nicht protokollierte Tabelle zu ändern:

Erstellen einer neuen, nicht protokollierten Tabelle mit der folgenden Syntax:
```
CREATE UNLOGGED TABLE <tablename>;
```
Konvertieren einer vorhandenen protokollierten Tabelle in eine nicht protokollierte Tabelle mit der folgenden Syntax:
```
ALTER TABLE <tablename> SET UNLOGGED;
```

Optimieren von Serverparametern

autovacuum: Während des ersten Ladevorgangs sollte autovacuum am besten deaktiviert werden. Nachdem der erste Ladevorgang abgeschlossen ist, wird empfohlen, VACUUM ANALYZE manuell für alle Tabellen in der Datenbank durchzuführen und anschließend autovacuum zu aktivieren.

Hinweis

Befolgen Sie die hier aufgeführten Empfehlungen nur, wenn genügend Arbeitsspeicher und Datenträgerspeicherplatz vorhanden sind.

maintenance_work_mem: Kann auf maximal 2 Gigabyte (GB) für eine flexible Serverinstanz von Azure Database for PostgreSQL festgelegt werden. maintenance_work_mem hilft beim Beschleunigen der Autovacuum-, Index- und Fremdschlüsselerstellung.
checkpoint_timeout: Bei einer flexiblen Serverinstanz von Azure Database for PostgreSQL kann der Wert checkpoint_timeout von der Standardeinstellung (5 Minuten) auf bis zu 24 Stunden erhöht werden. Es wird empfohlen, den Wert auf 1 Stunde zu erhöhen, bevor Sie die Daten erstmals auf die flexible Serverinstanz von Azure Database for PostgreSQL laden.
checkpoint_completion_target: Wir empfehlen einen Wert von 0,9.
max_wal_size: Kann bei einer flexiblen Serverinstanz von Azure Database for PostgreSQL auf den maximal zulässigen Wert (64 GB) festgelegt werden, während der erste Datenladevorgang ausgeführt wird.
wal_compression: Kann aktiviert werden. Die Aktivierung dieses Parameters kann zusätzliche CPU-Kosten für die Komprimierung während der Write-Ahead-Protokollierung und für die Dekomprimierung während der Write-Ahead-Protokollwiedergabe verursachen.

Empfehlungen zu Azure Database for PostgreSQL – Flexible Server

Bevor Sie damit beginnen, die ersten Daten auf die flexible Serverinstanz von Azure Database for PostgreSQL zu laden, empfehlen wir Folgendes:

Deaktivieren Sie die Hochverfügbarkeit für den Server. Sie können die Hochverfügbarkeit erneut aktivieren, nachdem der erste Ladevorgang auf dem Masterserver/dem primären Server abgeschlossen ist.
Erstellen Sie Lesereplikate, nachdem der erste Datenladevorgang abgeschlossen ist.
Reduzieren Sie die Protokollierung auf ein Minimum, oder deaktivieren Sie die Protokollierung bei den ersten Datenladevorgängen vollständig (deaktivieren Sie z. B. „pgaudit“, „pg_stat_statements“, Abfragespeicher).

Erneutes Erstellen von Indizes und Hinzufügen von Einschränkungen

Sofern Sie die Indizes und Beschränkungen vor dem ersten Laden gelöscht haben, empfehlen wir Ihnen, hohe Werte in maintenance_work_mem zu verwenden (wie bereits erwähnt), um Indizes zu erstellen und Beschränkungen hinzuzufügen. Darüber hinaus können ab PostgreSQL-Version 11 die folgenden Parameter geändert werden, um die parallele Indexerstellung nach dem ersten Datenladevorgang zu beschleunigen:

max_parallel_workers: Legt die maximale Anzahl von Workern fest, die das System für parallele Abfragen unterstützen kann.
max_parallel_maintenance_workers: Steuert die maximale Anzahl von Workerprozessen, die in CREATE INDEX verwendet werden kann.

Sie können auch Indizes erstellen, indem Sie die empfohlenen Einstellungen auf Sitzungsebene vornehmen. Hier finden Sie ein Beispiel für die Vorgehensweise:

SET maintenance_work_mem = '2GB';
SET max_parallel_workers = 16;
SET max_parallel_maintenance_workers = 8;
CREATE INDEX test_index ON test_table (test_column);

Bewährte Methoden für inkrementelle Datenladevorgänge

Partitionstabellen

Sie sollten große Tabellen immer partitionieren. Die Partitionierung bietet verschiedene Vorteile, insbesondere bei inkrementellen Ladevorgängen:

Durch die Erstellung neuer Partitionen auf der Grundlage neuer Deltas können Sie der Tabelle effizient neue Daten hinzufügen.
Die Verwaltung von Tabellen wird einfacher. Sie können eine Partition während eines inkrementellen Datenladevorgangs löschen, um zeitintensive Löschvorgänge in großen Tabellen zu vermeiden.
„autovacuum“ wird nur für Partitionen ausgelöst, die während inkrementeller Ladevorgänge geändert oder hinzugefügt wurden, was die Verwaltung von Statistiken für die Tabelle erleichtert.

Wahren des aktuellen Stands der Tabellenstatistiken

Die Überwachung und Wartung von Tabellenstatistiken ist für die Abfrageleistung in der Datenbank wichtig. Dies umfasst auch Szenarien mit inkrementellen Ladevorgängen. PostgreSQL verwendet den Autovacuum-Daemon-Prozess, um inaktive Tupel zu bereinigen und die Tabellen zu analysieren und so die Statistiken auf dem aktuellen Stand zu halten. Weitere Informationen finden Sie unter Autovacuum-Überwachung und -Optimierung.

Erstellen von Indizes für Fremdschlüsseleinschränkungen

Das Erstellen von Indizes für Fremdschlüssel in den untergeordneten Tabellen kann in den folgenden Szenarien vorteilhaft sein:

Datenaktualisierungen oder -löschungen in der übergeordneten Tabelle. Wenn Daten in der übergeordneten Tabelle aktualisiert oder gelöscht werden, werden in der untergeordneten Tabelle Lookups ausgeführt. Um Lookups schneller zu machen, können Sie Fremdschlüssel in der untergeordneten Tabelle indizieren.
Abfragen, in denen die Verknüpfung zwischen übergeordneten und untergeordneten Tabellen in Schlüsselspalten angezeigt wird.

Identifizieren von nicht verwendeten Indizes

Identifizieren Sie nicht verwendete Indizes in der Datenbank, und legen Sie sie ab. Indizes bedeuten bei Datenladevorgängen einen Mehraufwand. Je weniger Indizes in einer Tabelle enthalten sind, umso besser ist die Leistung während der Datenerfassung.

Sie können nicht verwendete Indizes auf zwei Arten identifizieren: durch den Abfragespeicher und durch eine Indexverwendungsabfrage.

Abfragespeicher

Mit dem Abfragespeicher können Sie Indizes identifizieren, die basierend auf den Abfragemustern in der Datenbank gelöscht werden können. Eine detaillierte Anleitung finden Sie unter Abfragespeicher.

Nachdem Sie den Abfragespeicher für den Server aktiviert haben, können Sie mithilfe der folgenden Abfrage Indizes ermitteln, die durch eine Verbindungsherstellung mit der Datenbank „azure_sys“ gelöscht werden können.

SELECT * FROM IntelligentPerformance.DropIndexRecommendations;

Indexnutzung

Sie können auch die folgende Abfrage verwenden, um ungenutzte Indizes zu identifizieren:

SELECT 
    t.schemaname, 
    t.tablename, 
    c.reltuples::bigint                            AS num_rows, 
    pg_size_pretty(pg_relation_size(c.oid))        AS table_size, 
    psai.indexrelname                              AS index_name, 
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size, 
    CASE WHEN i.indisunique THEN 'Y' ELSE 'N' END AS "unique", 
    psai.idx_scan                                  AS number_of_scans, 
    psai.idx_tup_read                              AS tuples_read, 
    psai.idx_tup_fetch                             AS tuples_fetched 
FROM 
    pg_tables t 
    LEFT JOIN pg_class c ON t.tablename = c.relname 
    LEFT JOIN pg_index i ON c.oid = i.indrelid 
    LEFT JOIN pg_stat_all_indexes psai ON i.indexrelid = psai.indexrelid 
WHERE 
    t.schemaname NOT IN ('pg_catalog', 'information_schema') 
ORDER BY 1, 2;

Die Spalten number_of_scans, tuples_read und tuples_fetched zeigen die Indexnutzung an. Ein Wert von 0 Punkten für die Spalte „number_of_scans“ weist auf einen nicht verwendeten Index hin.

Optimierungvon Serverparametern