Procedure consigliate per caricare i dati in blocco in Database di Azure per PostgreSQL - Server flessibile

  • Articolo

SI APPLICA A: Database di Azure per PostgreSQL - Server flessibile

Questo articolo illustra vari metodi per caricare i dati in blocco in Database di Azure per PostgreSQL server flessibile, insieme alle procedure consigliate per i caricamenti iniziali dei dati in database vuoti e caricamenti incrementali.

Metodi di caricamento

I metodi di caricamento dei dati seguenti sono disposti in ordine dal più lungo tempo al minor tempo dispendioso in termini di tempo:

  • Eseguire un comando a record INSERT singolo.
  • Eseguire il batch in 100-1000 righe per commit. È possibile usare un blocco di transazioni per eseguire il wrapping di più record per ogni commit.
  • Eseguire INSERT con più valori di riga.
  • Eseguire il comando COPY.

Il metodo preferito per il caricamento dei dati in un database consiste nell'usare il COPY comando . Se il comando non è possibile, l'uso COPY del batch INSERT è il metodo migliore successivo. Il multithreading con un COPY comando è il metodo ottimale per il caricamento bulk dei dati.

Procedure consigliate per i caricamenti iniziali dei dati

Eliminare gli indici

Prima di eseguire un caricamento iniziale dei dati, è consigliabile eliminare tutti gli indici nelle tabelle. È sempre più efficiente creare gli indici dopo il caricamento dei dati.

Vincoli drop

I vincoli di rilascio principali sono descritti di seguito:

  • Vincoli di chiave univoci

    Per ottenere prestazioni elevate, è consigliabile eliminare vincoli di chiave univoci prima del caricamento iniziale dei dati e ricrearli al termine del caricamento dei dati. Tuttavia, l'eliminazione di vincoli di chiave univoci annulla le misure di sicurezza contro i dati duplicati.

  • Vincoli di chiave esterna

    È consigliabile eliminare i vincoli di chiave esterna prima del caricamento iniziale dei dati e ricrearli dopo il completamento del caricamento dei dati.

    La modifica del session_replication_role parametro in replica disabilita anche tutti i controlli di chiave esterna. Tenere tuttavia presente che apportare la modifica può lasciare i dati in uno stato incoerente se non viene usato correttamente.

Tabelle non registrate

Considerare i vantaggi e i svantaggi dell'uso di tabelle non registrate prima di usarle nei caricamenti iniziali dei dati.

L'uso di tabelle non registrate rende più veloce il caricamento dei dati. I dati scritti nelle tabelle non registrate non vengono scritti nel log write-ahead.

Gli svantaggi dell'uso di tabelle non registrate sono:

  • Non sono sicuri per gli arresti anomali. Una tabella non registrata viene troncata automaticamente dopo un arresto anomalo o unclean.
  • I dati delle tabelle non registrate non possono essere replicati nei server di standby.

Per creare una tabella non registrata o modificare una tabella esistente in una tabella non registrata, utilizzare le opzioni seguenti:

  • Creare una nuova tabella non registrata usando la sintassi seguente:

    CREATE UNLOGGED TABLE <tablename>;

  • Convertire una tabella registrata esistente in una tabella non registrata usando la sintassi seguente:

    ALTER TABLE <tablename> SET UNLOGGED;

Ottimizzazione dei parametri del server

  • autovacuum: durante il caricamento iniziale dei dati, è consigliabile disattivare autovacuum. Al termine del caricamento iniziale, è consigliabile eseguire un manuale VACUUM ANALYZE in tutte le tabelle del database e quindi attivare autovacuum.

Nota

Seguire le indicazioni riportate qui solo se è disponibile memoria e spazio su disco sufficiente.

  • maintenance_work_mem: può essere impostato su un massimo di 2 gigabyte (GB) in un'istanza del server flessibile Database di Azure per PostgreSQL. maintenance_work_mem consente di velocizzare la creazione di autovacuum, indici e chiavi esterne.

  • checkpoint_timeout: in un'istanza del server flessibile Database di Azure per PostgreSQL, il checkpoint_timeout valore può essere aumentato fino a un massimo di 24 ore dall'impostazione predefinita di 5 minuti. È consigliabile aumentare il valore a 1 ora prima di caricare i dati inizialmente nell'istanza del server flessibile Database di Azure per PostgreSQL.

  • checkpoint_completion_target: è consigliabile usare il valore 0,9.

  • max_wal_size: può essere impostato sul valore massimo consentito in un'istanza del server flessibile Database di Azure per PostgreSQL, ovvero 64 GB durante l'esecuzione del caricamento iniziale dei dati.

  • wal_compression: può essere attivato. L'abilitazione di questo parametro può comportare un costo aggiuntivo della CPU speso per la compressione durante la registrazione del log write-ahead (WAL) e sulla decompressione durante la riproduzione wal.

Database di Azure per PostgreSQL raccomandazioni per i server flessibili

Prima di iniziare un caricamento iniziale dei dati nell'istanza del server flessibile Database di Azure per PostgreSQL, è consigliabile:

  • Disabilitare la disponibilità elevata nel server. È possibile abilitarlo dopo il completamento del caricamento iniziale nel database primario.
  • Creare repliche di lettura al termine del caricamento iniziale dei dati.
  • Rendere minima la registrazione o disabilitarla completamente durante i caricamenti iniziali dei dati (ad esempio: disabilitare pgaudit, pg_stat_statements, archivio query).

Ricreare gli indici e aggiungere vincoli

Supponendo di aver eliminato gli indici e i vincoli prima del caricamento iniziale, è consigliabile usare valori elevati in (come indicato in maintenance_work_mem precedenza) per la creazione di indici e l'aggiunta di vincoli. Inoltre, a partire dalla versione 11 di PostgreSQL, è possibile modificare i parametri seguenti per una creazione più rapida dell'indice parallelo dopo il caricamento iniziale dei dati:

  • max_parallel_workers: imposta il numero massimo di ruoli di lavoro che il sistema può supportare per le query parallele.

  • max_parallel_maintenance_workers: controlla il numero massimo di processi di lavoro, che possono essere usati in CREATE INDEX.

È anche possibile creare gli indici rendendo le impostazioni consigliate a livello di sessione. Ecco un esempio di come eseguire questa operazione:

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);

Procedure consigliate per i caricamenti incrementali dei dati

Tabelle di partizione

È sempre consigliabile partizionare tabelle di grandi dimensioni. Alcuni vantaggi del partizionamento, in particolare durante i caricamenti incrementali, includono:

  • La creazione di nuove partizioni in base ai nuovi delta rende più efficiente aggiungere nuovi dati alla tabella.
  • La gestione delle tabelle diventa più semplice. È possibile eliminare una partizione durante un caricamento incrementale dei dati per evitare eliminazioni che richiedono molto tempo in tabelle di grandi dimensioni.
  • Autovacuum viene attivato solo nelle partizioni modificate o aggiunte durante i caricamenti incrementali, semplificando la gestione delle statistiche nella tabella.

Gestire le statistiche delle tabelle aggiornate

Il monitoraggio e la gestione delle statistiche delle tabelle sono importanti per le prestazioni delle query nel database. Sono inclusi anche scenari in cui sono presenti carichi incrementali. PostgreSQL usa il processo del daemon autovacuum per pulire le tuple inattiche e analizzare le tabelle per mantenere aggiornate le statistiche. Per altre informazioni, vedere Monitoraggio e ottimizzazione di Autovacuum.

Creare indici su vincoli di chiave esterna

La creazione di indici in chiavi esterne nelle tabelle figlio può essere utile negli scenari seguenti:

  • Aggiornamenti o eliminazioni dei dati nella tabella padre. Quando i dati vengono aggiornati o eliminati nella tabella padre, le ricerche vengono eseguite nella tabella figlio. Per velocizzare le ricerche, è possibile indicizzare le chiavi esterne nella tabella figlio.
  • Query, in cui è possibile visualizzare il join di tabelle padre e figlio nelle colonne chiave.

Identificare gli indici inutilizzati

Identificare gli indici inutilizzati nel database e rilasciarli. Gli indici sono un sovraccarico sui caricamenti dei dati. Minore è il numero di indici in una tabella, migliori sono le prestazioni durante l'inserimento dei dati.

È possibile identificare gli indici inutilizzati in due modi: da Query Store e da una query sull'utilizzo dell'indice.

Archivio query

La funzionalità Query Store consente di identificare gli indici, che possono essere eliminati in base ai modelli di utilizzo delle query nel database. Per istruzioni dettagliate, vedere Query Store.

Dopo aver abilitato Query Store nel server, è possibile usare la query seguente per identificare gli indici che possono essere eliminati connettendosi al database azure_sys.

SELECT * FROM IntelligentPerformance.DropIndexRecommendations;

Utilizzo dell'indice

È anche possibile usare la query seguente per identificare gli indici inutilizzati:

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;

Le number_of_scanscolonne , tuples_reade tuples_fetched indicano l'indice usage.number_of_scans valore di colonna pari a zero punti come indice non utilizzato.

Ottimizzazione dei parametri del server

Nota

Seguire le indicazioni nei parametri seguenti solo se è disponibile memoria e spazio su disco sufficiente.

  • maintenance_work_mem: questo parametro può essere impostato su un massimo di 2 GB nell'istanza del server flessibile Database di Azure per PostgreSQL. maintenance_work_mem consente di velocizzare la creazione dell'indice e le aggiunte di chiavi esterne.

  • checkpoint_timeout: nell'istanza del server flessibile Database di Azure per PostgreSQL, il checkpoint_timeout valore può essere aumentato a 10 o 15 minuti dall'impostazione predefinita di 5 minuti. L'aumento checkpoint_timeout di un valore maggiore, ad esempio 15 minuti, può ridurre il carico di I/O, ma lo svantaggio è che il ripristino richiede più tempo in caso di arresto anomalo. È consigliabile prestare attenzione prima di apportare la modifica.

  • checkpoint_completion_target: è consigliabile usare il valore 0,9.

  • max_wal_size: questo valore dipende da SKU, archiviazione e carico di lavoro. Un modo per arrivare al valore corretto per max_wal_size è illustrato nell'esempio seguente.

    Durante le ore lavorative di punta, arrivare a un valore eseguendo le operazioni seguenti:

    a. Accettare il numero di sequenza di log (LSN) corrente eseguendo la query seguente:

    SELECT pg_current_wal_lsn ();

    b. checkpoint_timeout Attendere il numero di secondi. Accettare l'LSN WAL corrente eseguendo la query seguente:

    SELECT pg_current_wal_lsn ();

    c. Usare i due risultati per verificare la differenza, in GB:

    SELECT round (pg_wal_lsn_diff('LSN value when run second time','LSN value when run first time')/1024/1024/1024,2) WAL_CHANGE_GB;

  • wal_compression: può essere attivato. L'abilitazione di questo parametro può comportare un costo aggiuntivo della CPU speso per la compressione durante la registrazione WAL e la decompressione durante la riproduzione wal.

Passaggi successivi