Come velocizzare le prestazioni di inserimento in PostgreSQL

Sto testando le prestazioni di inserimento di Postgres. Ho una tabella con una colonna con il numero come tipo di dati. C’è anche un indice. Ho riempito il database usando questa query:

insert into aNumber (id) values (564),(43536),(34560) ... 

Ho inserito 4 milioni di righe molto rapidamente 10.000 alla volta con la query sopra. Dopo che il database ha raggiunto 6 milioni di righe, le prestazioni sono diminuite drasticamente a 1 milione di righe ogni 15 minuti. C’è qualche trucco per aumentare le prestazioni di inserimento? Ho bisogno di prestazioni di inserimento ottimali per questo progetto.

Utilizzo di Windows 7 Pro su una macchina con 5 GB di RAM.

Vedi popolare un database nel manuale di PostgreSQL, l’eccellente articolo del depesz sull’argomento e questa domanda SO .

(Si noti che questa risposta riguarda il caricamento in massa di dati in un DB esistente o per crearne uno nuovo. Se si desidera ripristinare le prestazioni di DB con pg_restore o l’esecuzione di pg_dump dell’output di pg_dump , gran parte di ciò non si applica poiché pg_dump e pg_restore già fanno cose come la creazione di trigger e indici dopo che termina uno schema + ripristino dei dati) .

C’è molto da fare. La soluzione ideale sarebbe quella di importare in una tabella UNLOGGED senza indici, quindi cambiarlo in log e aggiungere gli indici. Sfortunatamente in PostgreSQL 9.4 non è supportato il cambio delle tabelle da UNLOGGED a loggato. 9.5 aggiunge ALTER TABLE ... SET LOGGED per consentirvi di farlo.

Se è ansible portare offline il database per l’importazione di massa, utilizzare pg_bulkload .

Altrimenti:

  • Disabilitare eventuali trigger sul tavolo

  • Rilasciare gli indici prima di iniziare l’importazione, quindi ricrearli in seguito. (Ci vuole molto meno tempo per build un indice in un passaggio piuttosto che aggiungere gli stessi dati ad esso progressivamente, e l’indice risultante è molto più compatto).

  • Se si esegue l’importazione in un’unica transazione, è ansible eliminare i vincoli delle chiavi esterne, eseguire l’importazione e ricreare i vincoli prima di eseguire l’importazione. Non farlo se l’importazione è suddivisa su più transazioni poiché potresti introdurre dati non validi.

  • Se ansible, utilizzare COPY anziché INSERT s

  • Se non è ansible utilizzare COPY prendere in considerazione l’utilizzo di INSERT multivalore se ansible. Sembra che tu lo stia già facendo. Non cercare di elencare troppi valori in un singolo VALUES ; quei valori devono essere memorizzati in memoria un paio di volte, quindi tienilo a poche centinaia per ogni affermazione.

  • Metti i tuoi inserti in transazioni esplicite, facendo centinaia di migliaia o milioni di inserti per transazione. Non esiste un limite pratico AFAIK, ma il batch ti consente di recuperare da un errore segnando l’inizio di ogni batch nei dati di input. Di nuovo, sembra che tu lo stia già facendo.

  • Usa synchronous_commit=off e un enorme commit_delay per ridurre i costi fsync (). Questo non sarà di grande aiuto se hai fatto il tuo lavoro in grandi transazioni, però.

  • INSERT o COPY in parallelo da più connessioni. Quanti dipendono dal sottosistema del disco del tuo hardware; come regola generale, si desidera una connessione per disco rigido fisico se si utilizza l’archiviazione collegata diretta.

  • Impostare un valore checkpoint_segments alto e abilitare log_checkpoints . Guarda i registri di PostgreSQL e assicurati che non si lamentino dei checkpoint che si verificano troppo frequentemente.

  • Se e solo se non ti dispiace perdere l’intero cluster PostgreSQL (il tuo database e tutti gli altri nello stesso cluster) in corruzione catastrofica se il sistema si blocca durante l’importazione, puoi fermare Pg, impostare fsync=off , avviare Pg, fare la tua importazione, quindi (in modo vitale) ferma Pg e imposta fsync=on nuovo. Vedi configurazione WAL . Non farlo se ci sono già dati che ti interessano in qualsiasi database sulla tua installazione di PostgreSQL. Se imposti fsync=off puoi anche impostare full_page_writes=off ; ancora una volta, ricorda di riaccenderlo dopo l’importazione per evitare il danneggiamento del database e la perdita di dati. Vedi le impostazioni non durevoli nel manuale Pg.

Dovresti anche dare un’occhiata al tuo sistema:

  • Utilizzare SSD di buona qualità per lo storage il più ansible. I buoni SSD con cache di write-back affidabili e protette da alimentazione rendono i tassi di commit incredibilmente più veloci. Sono meno utili quando si segue il consiglio di cui sopra – che riduce il flush del disco / numero di fsync() s – ma può comunque essere di grande aiuto. Non utilizzare SSD economici senza un’adeguata protezione contro l’interruzione dell’alimentazione, a meno che non ti interessi conservare i tuoi dati.

  • Se stai utilizzando RAID 5 o RAID 6 per l’archiviazione collegata diretta, interrompi ora. Esegui il backup dei dati, ristruttura il tuo array RAID su RAID 10 e riprova. RAID 5/6 non ha speranze per le prestazioni di scrittura di massa, anche se un buon controller RAID con una grande cache può essere d’aiuto.

  • Se si ha la possibilità di utilizzare un controller RAID hardware con una grande cache write-back con batteria tampone, ciò può davvero migliorare le prestazioni di scrittura per carichi di lavoro con molti commit. Non è di grande aiuto se si utilizza il commit asincrono con un commit_delay o se si eseguono meno grandi transazioni durante il caricamento di massa.

  • Se ansible, memorizzare WAL ( pg_xlog ) su un disk disk / disk array separato. Non ha molto senso usare un filesystem separato sullo stesso disco. Le persone spesso scelgono di utilizzare una coppia RAID1 per WAL. Di nuovo, questo ha più effetto sui sistemi con alti tassi di commit, e ha scarso effetto se si sta usando una tabella non loggata come destinazione del caricamento dei dati.

Potresti anche essere interessato a Optimize PostgreSQL per test rapidi .

Utilizzare la COPY table TO ... WITH BINARY che è secondo la documentazione è ” un po ‘più veloce del testo e dei formati CSV “. Esegui questa operazione solo se hai milioni di righe da inserire e se hai dimestichezza con i dati binari.

Ecco una ricetta di esempio in Python, usando psycopg2 con input binario .

Oltre all’eccellente post di Craig Ringer e al post sul blog di depesz, se desideri velocizzare i tuoi inserimenti tramite l’interfaccia ODBC ( psqlodbc ) utilizzando inserti di istruzioni preparati all’interno di una transazione, ci sono alcune cose extra che devi fare per renderlo lavorare velocemente:

  1. Imposta il livello di rollback sugli errori su “Transazione” specificando Protocol=-1 nella stringa di connessione. Per impostazione predefinita psqlodbc utilizza il livello “Statement”, che crea un SAVEPOINT per ogni istruzione piuttosto che un’intera transazione, rendendo gli inserimenti più lenti.
  2. Utilizzare istruzioni preparate sul lato server specificando UseServerSidePrepare=1 nella stringa di connessione. Senza questa opzione il client invia l’intera istruzione di inserimento insieme a ciascuna riga inserita.
  3. Disabilitare il commit automatico su ogni istruzione utilizzando SQLSetConnectAttr(conn, SQL_ATTR_AUTOCOMMIT, reinterpret_cast(SQL_AUTOCOMMIT_OFF), 0);
  4. Dopo aver inserito tutte le righe, eseguire il commit della transazione utilizzando SQLEndTran(SQL_HANDLE_DBC, conn, SQL_COMMIT); . Non è necessario aprire esplicitamente una transazione.

Sfortunatamente, psqlodbc “implementa” SQLBulkOperations immettendo una serie di istruzioni di inserimento non preparate, in modo che per ottenere l’inserto più veloce sia necessario codificare manualmente i passaggi sopra descritti.

Ho trascorso circa 6 ore sullo stesso numero oggi. Gli inserti vanno a una velocità “regolare” (meno di 3 secondi per 100.000) fino a 5 milioni (su un totale di 30 milioni) di righe e quindi le prestazioni diminuiscono drasticamente (fino a 1 minuto per 100.000).

Non elencherò tutte le cose che non hanno funzionato e tagliato direttamente all’incontro.

Ho lasciato cadere una chiave primaria sulla tabella di destinazione (che era un GUID) e la mia 30MI o le righe fluivano felicemente alla loro destinazione a una velocità costante inferiore a 3 secondi per 100.000.

Per prestazioni di inserimento ottimali, disabilita l’indice se questa è un’opzione per te. Oltre a questo, anche l’hardware migliore (disco, memoria) è utile

Ho riscontrato anche questo problema di prestazioni di inserimento. La mia soluzione è generare alcune routine per completare il lavoro di inserimento. Nel frattempo, SetMaxOpenConns dovrebbe avere un numero corretto altrimenti SetMaxOpenConns troppi errori di connessione aperti.

 db, _ := sql.open() db.SetMaxOpenConns(SOME CONFIG INTEGER NUMBER) var wg sync.WaitGroup for _, query := range queries { wg.Add(1) go func(msg string) { defer wg.Done() _, err := db.Exec(msg) if err != nil { fmt.Println(err) } }(query) } wg.Wait() 

La velocità di caricamento è molto più veloce per il mio progetto. Questo frammento di codice ha appena dato un’idea di come funziona. I lettori dovrebbero essere in grado di modificarlo facilmente.