Documentazione di PostgreSQL 9.0 > Il linguaggio SQL > Suggerimenti per migliorare le prestazioni > Popolare un database
PrecedenteControllare il pianificatore con clausole JOIN espliciteImpostazioni non durevoliSuccessivo

14.4. Popolare un database

Si potrebbe voler inserire una grande quantità di dati quando si popola per la prima volta un database. Questa sezione contiene alcuni suggerimenti su come farlo nel modo più efficiente possibile.

14.4.1. Disabilitare l'autocommit

Quando si usano molteplici INSERT, disabilitare l'autocommit e fare solo una commit alla fine. (In SQL, questo significa scrivere BEGIN all'inizio e COMMIT alla fine. Alcune librerie client potrebbero farlo alle vostre spalle, nel qual caso ci si deve assicurare che la libreria lo faccia quando si vuole che venga fatto). Se si consente che ogni inserimento sia fatto separatamente, PostgreSQL™ farà molto lavoro per ogni riga che viene aggiunta. Un beneficio aggiuntivo di fare tutti gli inserimenti in una transazione è che se l'inserimento di una riga è fallito allora l'inserimento di tutte le righe inserite fino a quel punto sarà sottoposto a rollback, così non si rimarrà bloccati con dati caricati parzialmente.

14.4.2. Usare COPY

Usare COPY(7) per caricare tutte le righe con un comando, invece di usare una serie di comandi INSERT. Il comando COPY è ottimizzato per il caricaemento di grandi quantità di righe; è meno flessibile di INSERT, ma provoca un overhead significativamente minore per caricamenti di grandi quantità di dati. Dato che COPY è un singolo comando, non c'è bisogno di disabilitare l'autocommit se si usa questo metodo per popolare una tabella.

Se non si può usare COPY, potrebbe essere d'aiuto usare PREPARE(7) per creare un'istruzione INSERT "preparata", quindi usare EXECUTE tante volte quanto richiesto. Questo evita un po' dell'overhead di fare ripetutamente il parsing e la pianificazione dell'INSERT. Le diverse interfaccie forniscono questa facilitazione in maniere differenti; si cerchi «istruzioni preparate» nella documentazione dell'interfaccia.

Notare che caricare un grande numero di righe usando COPY di solito è quasi sempre più veloce che usare INSERT, anche se viene usato PREPARE e inserimenti multipli sono racchiusi in una singola transazione.

COPY è più veloce quando usato all'interno della stessa transazione di un precedente comando CREATE TABLE o TRUNCATE. In tali casi non deve essere scritto nessun WAL, dato che in caso di un errore, i file contenenti i dati nuovi caricati saranno comunque rimossi. Comunque, questa considerazione si applica solo quando wal_level è minimal altrimenti tutti i comandi devono scrivere WAL.

14.4.3. Rimozione di indici

Se si sta caricando una tabella creata di recente, il metodo più veloce è di creare la tabella, caricare i dati della tabella usando COPY, quindi creare gli indici necessari per la tabella. Creare un indice su dati pre-esistenti è più veloce di aggiornarli in modo incrementale quando ogni riga viene caricata.

Se si sta aggiungendo una grande quantità di dati a una tabella esistente, potrebbe essere vantaggioso eliminare gli indici, caricare la tabella, e quindi ricreare gli indici. Ovviamente, le prestazioni del database per altri utenti potrebbero soffrire durante il periodo che gli indici sono mancanti. Ci si dovrebbe pensare due volte prima di eliminare un indice univoco, dato che i controlli di errore permessi dal vincolo unique andranno persi mentre l'indice è mancante.

14.4.4. Rimozione di vincoli chiave esterna

Esattamente come con gli indici, un vincolo chiave esterna può essere controllato «in grandi quantità» più efficientemente riga per riga. Per questo potrebbe essere utile eliminare vincoli chiave esterna, caricare dati, e ri-creare i vincoli. Di nuovo, c'è un bilanciamento tra la velocità di caricamento dei dati e la perdita di controllo di errori mentre il vincolo è mancante.

Oltretutto, quando si caricano dati all'interno di tabelle coni vincoli di chiave esterna, ogni nuova riga richiede una voce nell'elenco di trigger pendenti del server (dato che è l'attivazione di un trigger che controlla il vincolo di chiave esterna della riga). Caricare molti milioni di righe può causare che la coda di eventi del trigger superi la memoria disponibile, portando a uno swapping intollerabile o al fallimento totale del comando. Inoltre può essere necessario, non semplicemenete desiderabile, eliminare e ri-applicare le chiavi esterne quando si caricano grandi quantità di dati. Se rimuovere temporaneamente il vincolo non è accettabile, l'unico altro modo può essere dividere l'operazione di caicamenti in transazioni più piccole.

14.4.5. Incrementare maintenance_work_mem

Aumentare temporaneamente la variabile di configurazione maintenance_work_mem quando si caricano grandi quantità di dati può portare ad un aumento delle prestazioni. Questo aiuterà a velocizzare i comandi CREATE INDEX e ALTER TABLE ADD FOREIGN KEY. Non farà molto per il comando COPY stesso, così questo consiglio è utile solo quando si sta usando una o entrambe delle tecniche viste sopra.

14.4.6. Incrementare checkpoint_segments

Anche incrementare temporaneamente la variabile di configurazione checkpoint_segments puù rendere più veloce il caricamento dei dati. Questo perchè il caricamento di una grande quantità di dati in PostgreSQL™ causerà che si verifichino checkpoint più spesso della frequenza normale dei checkpoint (specificata dalla variabile di configurazione checkpoint_timeout). Ogni volta che avviene un checkpoint, tutte le pagine sporche devono essere liberate su disco. Incrementando temporaneamente checkpoint_segments durante un caricamento di grandi quantità di dati, il numero di checkpoint richiesti può essere ridotto.

14.4.7. Disabilitare l'archiviazione WAL e la replica streaming

Quando si caricano grandi quantità di dati all'interno di una installazione che usa l'archiviazione WAL o la replica streaming, potrebbe essere più veloce prendere un nuovo backup di base dopoi che il caricamento è completato piuttosto che processare una grande quantità di dati WAL. Per prevenire i log WAL incrementali durante il caricamento, disabilitare l'archiviazione e la replica streaming, impostando wal_level a minimal, archive_mode a off, e max_wal_senders a zero. Ma si noti che cambiare queste impostazioni richiede che il server venga riavviato.

A prescindere dall'eliminazione del tempo impegato dall'archiviatore o dallo smistatore WAL per elaborare i dati, fare questo renderà effettivamente più veloci certi comandi, dato che sono sono progettati per non scrivere i WAL del tutto se wal_level è minimal. (Essi possono garantire sicurezza rispetto ai crash in maniera più conveniente facendo un fsync alla fine invece che scrivere WAL). Questo si applica ai seguenti comandi:

  • CREATE TABLE AS SELECT

  • CREATE INDEX (e varianti tipo ALTER TABLE ADD PRIMARY KEY)

  • ALTER TABLE SET TABLESPACE

  • CLUSTER

  • COPY FROM, quando la tabella destinazione è stata creata o troncata precedentemente nella stessa transazione

14.4.8. Eseguire ANALYZE dopo

Ogni volta che si è modificato significativamente la distribuzione dei dati all'interno di una tabella, si raccomanda fortemente di eseguire ANALYZE(7). Questo include il caricamento in massa di grandi quantità di dati nella tabella. Eseguire ANALYZE (o VACUUM ANALYZE) assicute che il pianificatore abbia statistiche aggiornate sulla tabella. Senza statistiche o con statistiche obsolete, il pianificatore potrebbe prendere decisioni sbagliate durante la pianificazione della query, generando cattive prestazioni su qualsiasi tabella con statistiche inaccurate o non esistenti. Notare che se il demone di autovacuum è attivo, potrebbe eseguire ANALYZE automaticamente; si veda Sezione 23.1.3, «Updating Planner Statistics» e Sezione 23.1.5, «The Autovacuum Daemon» per maggiori informazioni.

14.4.9. Alcune note riguardo pg_dump

Gli script di dump generati da pg_dump applicano automaticamente diverse, ma non tutte, linee guida viste sopra. Per ricaricare un dump pg_dump il più velocemente possibile, si devono fare alcune cose aggiuntive manualmente. (Si noti che questi punti si applicano mentre si ripristina un dump, non mentre lo si crea. Gli stessi punti si applicano sia che si carichi un dump testuale con psql sia usando pg_restore per caricare da un file di archivio pg_dump).

In maniera predefinita, pg_dump usa COPY, e quando sta generando un dump completo di schema e dati, è prudente caricari i dati prima di creare gli indici e le chiavi esterne. Così, in questo caso diverse linee guida sono gestite automaticamente. Quello che viene lasciato da fare all'utente è:

  • Impostare valori appropriati (per es., più grandi del normale) per maintenance_work_mem e checkpoint_segments.

  • Se si usa l'archiviazione WAL o le replica streaming, considerare di disabilitarle durante il restore. Per fare ciò, impostare archive_mode a off, wal_level a minimal e max_wal_senders a zero prima di caricare il dump. Successivamente, rimetterli ai valori giusti e prendere un nuovo backup di base.

  • Considerare se l'intero dump debba essere ripristinato come singola transazione. Per fare ciò, passare a psql o a pg_restore l'opzione a riga di comando -1 o --single-transaction. Quando si utilizza questa modalità, il minimo errore provocherà il rollback dell'intero ripristino, eventualmente annullando diverse ore di elaborazione. Dipende da quanto sono collegati i dati il fatto che sia preferibile o meno eseguirer una pulizia manuale. I comandi COPY saranno più veloci se si usa una singola transazione e l'archiviazione WAL è disabilitata.

  • Se sul server database sono disponibili diverse CPU, considerare l'utilizzo dell'opzione --jobs di pg_restore. Questo permette il caricamento concorrente dei dati e la creazione concorrente di indici.

  • Successivamente lanciare ANALYZE.

Anche un dump solamente dei dati utilizza COPY, ma non cancella o ricrea indici, e di solito non tocca le chiavi esterne. [10] Così quando si caricano dump di soli dati, è compito vostro cancellare e ricreare gli indici e le chiavi esterne se si desidera usare quelle tecniche. È comuqnue utile incrementare checkpoint_segments mentre si caricano i dati, ma non preoccuparsi di incrementare maintenance_work_mem; piuttosto, si potrebbe farlo successivamente mentre si ricreano manualmente gli indici e le chiavi esterne. E non dimenticarsi di lanciare ANALYZE quando si è fatto; si veda Sezione 23.1.3, «Updating Planner Statistics» e Sezione 23.1.5, «The Autovacuum Daemon» per maggiori informazioni.



[10] Si può ottenere l'effetto di disabilitazione delle chiavi esterne usando l'opzione --disable-triggers - ma si tenga presente che questo elimina, piuttosto che solamente postporre, la validazione delle chiavi esterne, e così è possibile inserire dati sbagliati se la si usa.

Documentazione di PostgreSQL 9.0 > Il linguaggio SQL > Suggerimenti per migliorare le prestazioni > Popolare un database
PrecedenteControllare il pianificatore con clausole JOIN espliciteImpostazioni non durevoliSuccessivo