Freigeben über

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

  • Artikel

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 sind die Methoden zum Laden von Daten in absteigender Reihenfolge vom größten bis zum geringsten Zeitaufwand aufgeführt:

  • Ausführen des Befehls INSERT für einen einzelnen Datensatz.
  • Batches von 100–1.000 Zeilen pro Commit erstellen 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 der COPY-Befehl. Wenn der COPY-Befehl nicht verwendet werden kann, ist die nächstbeste Methode die Batchverwendung des INSERT-Befehls. Die optimale Methode zum Massenladen von Daten ist Multithreading mit einem COPY-Befehl.

Schritte zum Upload von Massendaten

Dies sind die Schritte zum Massenupload von Daten in Azure Database for PostgreSQL Flexible Server.

Schritt 1: Aufbereiten der Daten

Stellen Sie sicher, dass Ihre Daten bereinigt und ordnungsgemäß für die Datenbank formatiert sind.

Schritt 2: Auswählen der Lademethode

Wählen Sie basierend auf der Größe und Komplexität Ihrer Daten die angemessene Lademethode aus.

Schritt 3: Ausführen der Lademethode

Führen Sie die ausgewählte Lademethode aus, um Ihre Daten in die Datenbank hochzuladen.

Schritt 4: Überprüfen der Daten

Überprüfen Sie nach dem Hochladen, ob die Daten ordnungsgemäß in die Datenbank geladen wurden.

Bewährte Methoden für erste Datenladevorgänge

Dies sind die bewährten Methoden für initiale Datenladevorgänge.

Drop-Indexes

Bevor Sie das erste Mal Daten laden, wird empfohlen, alle Indizes in den Tabellen zu entfernen. Indizes nach dem Laden der Daten zu erstellen, ist immer effizienter.

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, wird empfohlen, vor dem ersten Laden der Daten alle Einschränkungen für eindeutige Schlüssel zu entfernen und diese nach dem Laden neu zu erstellen. Durch das Ablegen eindeutiger Schlüsseleinschränkungen wird jedoch der Schutz vor duplizierten Daten abgebrochen.

  • Fremdschlüsseleinschränkungen

Einschränkungen für Fremdschlüssel sollten vor dem ersten Laden der Daten entfernt und nach dem Laden neu erstellt werden.

Durch eine Änderung des Parameters session_replication_role in replica werden ebenfalls alle Fremdschlüsselüberprüfungen deaktiviert. Wenn die Änderung jedoch nicht ordnungsgemäß verwendet wird, kann sie zu inkonsistenten Daten führen.

Nicht protokollierte Tabellen

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

Die Verwendung von nicht protokollierten Tabellen beschleunigt das Laden von Daten. Daten, die in nicht protokollierte Tabellen geschrieben wurden, werden nicht in das Schreib-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

  • auto vacuum': It's best to turn off auto vacuum'“ beim ersten Datenladevorgang zu deaktivieren. Nachdem der erste Ladevorgang abgeschlossen ist, wird empfohlen, VACUUM ANALYZE manuell für alle Tabellen in der Datenbank auszuführen und anschließend auto vacuum 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 eine Stunde zu erhöhen, bevor Sie die Daten erstmals auf die Instanz von Azure Database for PostgreSQL Flexible Server 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: Dieser Parameter kann aktiviert werden. Die Aktivierung kann zusätzliche CPU-Kosten für die Komprimierung während der Write-Ahead-Protokollierung und die Dekomprimierung während der Protokollwiedergabe verursachen.

Empfehlungen

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, wird empfohlen, in maintenance_work_mem (wie bereits erwähnt) hohe Werte zu verwenden, 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

Hier werden die bewährten Methoden für inkrementelle Datenladevorgänge beschrieben:

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 würde nur bei Partitionen ausgelöst werden, die während inkrementeller Ladevorgänge geändert oder hinzugefügt werden, wodurch die Verwaltung von Statistiken für die Tabelle erleichtert wird.

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. Sie können Fremdschlüssel in der untergeordneten Tabelle indizieren, um Lookups zu beschleunigen.
  • Abfragen, in denen Joins von übergeordneten und untergeordneten Tabellen in Schlüsselspalten angezeigt werden.

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

Hinweis

Befolgen Sie die Empfehlungen für folgenden Parameter nur, wenn genügend Arbeitsspeicher und Datenträgerspeicherplatz vorhanden sind.

  • maintenance_work_mem: Dieser Parameter kann auf maximal 2 GB für die flexible Serverinstanz von Azure Database for PostgreSQL festgelegt werden. maintenance_work_mem hilft bei der Beschleunigung der Indexerstellung und bei der Hinzufügung von Fremdschlüsseln.

  • checkpoint_timeout: Bei der flexiblen Serverinstanz von Azure Database for PostgreSQL kann der Wert checkpoint_timeout von der Standardeinstellung (5 Minuten) auf 10 bis 15 Minuten erhöht werden. Eine Erhöhung von checkpoint_timeout auf einen höheren Wert, z. B. 15 Minuten, kann die E/A-Last verringern, hat aber den Nachteil, dass die Wiederherstellung bei einem Absturz länger dauert. Erwägen Sie die Vor- und Nachteile sorgfältig, bevor Sie die Änderung vornehmen.

  • checkpoint_completion_target: Wir empfehlen einen Wert von 0,9.

  • max_wal_size: Dieser Wert hängt von SKU, Speicher und Workload ab. Das folgende Beispiel zeigt eine Möglichkeit, den richtigen Wert für max_wal_size zu ermitteln.

Während der Hauptgeschäftszeiten können Sie den Wert wie folgt ermitteln:

a. Ermitteln Sie die aktuelle Protokollfolgenummer (Log Sequence Number, LSN) des Write-Ahead-Protokolls, indem Sie die folgende Abfrage ausführen:

SELECT pg_current_wal_lsn ();

b. Warten Sie die für checkpoint_timeout definierte Anzahl von Sekunden ab. Ermitteln Sie die aktuelle LSN des Write-Ahead-Protokolls durch Ausführung der folgenden Abfrage:

SELECT pg_current_wal_lsn ();

c. Verwenden Sie die beiden Ergebnisse, um den Unterschied in GB zu überprüfen:

SELECT round (pg_wal_lsn_diff('LSN value when running the second time','LSN value when run the first time')/1024/1024/1024,2) WAL_CHANGE_GB;
  • wal_compression: Dieser Parameter kann aktiviert werden. Die Aktivierung kann zusätzliche CPU-Kosten für die Komprimierung während der Write-Ahead-Protokollierung und die Dekomprimierung während der Protokollwiedergabe verursachen.

Zugehöriger Inhalt