Hai bisogno di consigli per spingere la busta con SqlBulkCopy su SQL Server

bulkinsert database-performance scalability sqlbulkcopy sql-server

Domanda

Sto progettando un'applicazione, un aspetto del quale è che dovrebbe essere in grado di ricevere enormi quantità di dati nel database SQL. Ho progettato la stenografia del database come un'unica tabella con identità bigint, qualcosa come questa:

CREATE TABLE MainTable
(
   _id bigint IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    field1, field2, ...
)

Tralascio come intendo eseguire query, poiché è irrilevante per la domanda che ho.

Ho scritto un prototipo, che inserisce i dati in questa tabella usando SqlBulkCopy. Sembrava funzionare molto bene in laboratorio. Sono stato in grado di inserire decine di milioni di record ad una velocità di ~ 3K records / sec (il record completo è piuttosto grande, ~ 4K). Dal momento che l'unico indice su questa tabella è bigint autoincrementing, non ho visto un rallentamento anche dopo aver spinto una quantità significativa di righe.

Considerando che il server SQL di laboratorio era una macchina virtuale con una configurazione relativamente debole (4 GB di RAM, condivisa con altro sistema di dischi VM), mi aspettavo di ottenere un throughput significativamente migliore sulla macchina fisica, ma non è successo, o diciamo il l'aumento delle prestazioni era trascurabile. Potrei, forse ottenere inserti più veloci del 25% sulla macchina fisica. Anche dopo aver configurato RAID0 a 3 unità, che ha funzionato 3 volte più velocemente di una singola unità (misurata da un software di benchmarking), non ho ottenuto alcun miglioramento. Fondamentalmente: sottosistema di unità più veloce, CPU fisica dedicata e doppia RAM quasi non si traducevano in alcun guadagno in termini di prestazioni.

Ho quindi ripetuto il test utilizzando l'istanza più grande su Azure (8 core, 16 GB) e ho ottenuto lo stesso risultato. Quindi, l'aggiunta di più core non ha modificato la velocità di inserimento.

In questo momento ho giocato con i seguenti parametri software senza alcun guadagno di prestazioni significativo:

  • Modifica del parametro SqlBulkInsert.BatchSize
  • Inserimento da più thread contemporaneamente e regolazione del numero di thread
  • Utilizzo dell'opzione Blocco tabella su SqlBulkInsert
  • Eliminazione della latenza di rete mediante l'inserimento da un processo locale utilizzando il driver di memoria condivisa

Sto provando ad aumentare le prestazioni almeno 2-3 volte, e la mia idea originale era che il lancio di più hardware avrebbe funzionato, ma finora non è così.

Quindi, qualcuno può raccomandarmi:

  • Quale risorsa potrebbe essere sospettata di un collo di bottiglia qui? Come confermare?
  • Esiste una metodologia che potrei provare ad ottenere un miglioramento dell'instabilità di massa scalabile in modo affidabile considerando che esiste un unico sistema SQL server?

AGGIORNAMENTO Sono certo che caricare l'app non è un problema. Crea record in una coda temporanea in un thread separato, quindi quando c'è un inserto va come questo (semplificato):

===>start logging time
int batchCount = (queue.Count - 1) / targetBatchSize + 1;
Enumerable.Range(0, batchCount).AsParallel().
    WithDegreeOfParallelism(MAX_DEGREE_OF_PARALLELISM).ForAll(i =>
{
    var batch = queue.Skip(i * targetBatchSize).Take(targetBatchSize);
    var data = MYRECORDTYPE.MakeDataTable(batch);
    var bcp = GetBulkCopy();
    bcp.WriteToServer(data);
});
====> end loging time

i tempi sono registrati e la parte che crea una coda non prende mai parti significative

AGGIORNATO2 Ho implementato la raccolta della durata di ciascuna operazione in quel ciclo e il layout è il seguente:

  • queue.Skip().Take() - trascurabile
  • MakeDataTable(batch) - 10%
  • GetBulkCopy() : trascurabile
  • WriteToServer(data) - 90%

UPDATE3 Sto progettando per la versione standard di SQL, quindi non posso fare affidamento sul partizionamento, dal momento che è disponibile solo nella versione Enterprise. Ma ho provato una variante dello schema di partizionamento:

  • creato 16 filegroup (da G0 a G15),
  • fatto 16 tabelle per l'inserimento solo (da T0 a T15) ciascuna vincolata al suo singolo gruppo. Le tabelle sono prive di indici, nemmeno di identità int per cluster.
  • i thread che inseriscono i dati scorreranno ciclicamente tra tutte e 16 le tabelle. Ciò rende quasi una garanzia che ogni operazione di inserimento di massa utilizza la propria tabella

Ciò ha prodotto un miglioramento del 20% circa dell'inserto di massa. Core CPU, interfaccia LAN, Drive I / O non sono stati massimizzati e utilizzati a circa il 25% della capacità massima.

UPDATE4 Penso che ora sia buono come sembra. Sono stato in grado di inserire gli inserti a una velocità ragionevole utilizzando le seguenti tecniche:

  • Ogni inserto di massa va nella propria tabella, quindi i risultati vengono uniti in quello principale
  • Le tabelle vengono ricreate fresche per ogni inserto di grandi dimensioni, vengono utilizzate le serrature da tavolo
  • Implementazione IDataReader utilizzata da qui anziché DataTable.
  • Inserti di massa eseguiti da più client
  • Ogni client accede a SQL utilizzando singole VLAN gigabit
  • I processi laterali che accedono alla tabella principale utilizzano l'opzione NOLOCK
  • Ho esaminato sys.dm_os_wait_stats e sys.dm_os_latch_stats per eliminare le contese

Ho difficoltà a decidere a questo punto chi ottiene un credito per la domanda risposta. Quelli tra voi che non hanno una "risposta", mi scuso, è stata una decisione davvero difficile, e ringrazio tutti voi.

UPDATE5 : l'elemento seguente potrebbe utilizzare qualche ottimizzazione:

  • Implementazione IDataReader utilizzata da qui anziché DataTable.

A meno che non si esegua il programma sulla macchina con un numero di core della CPU elevato, potrebbe essere necessario ricorrere al ri-factoring. Poiché utilizza la reflection per generare metodi get / set, diventa un carico importante sulle CPU. Se le prestazioni sono una chiave, aggiungono un sacco di prestazioni quando si codifica IDataReader manualmente, in modo che sia compilato, invece di usare la riflessione

Risposta accettata

Per suggerimenti sull'ottimizzazione di SQL Server per i carichi di massa, consultare il documento Caricamento dei dati e Guida alle prestazioni da MS e anche Linee guida per l'ottimizzazione dell'importazione di massa dai libri online. Sebbene si concentrino sul caricamento di massa da SQL Server, la maggior parte del consiglio si applica al caricamento di massa utilizzando l'API del client. Questo documento si applica a SQL 2008: non si specifica quale versione di SQL Server si sta utilizzando
Entrambi hanno un sacco di informazioni che vale la pena esaminare in dettaglio. Tuttavia, alcuni punti salienti:

  • Registra in modo minimo l'operazione di massa. Utilizza il recupero con registrazione minima o semplice. Potrebbe essere necessario abilitare traceflag 610 (ma vedere le avvertenze su come fare)
  • Accordare le dimensioni del batch
  • Prendi in considerazione il partizionamento della tabella di destinazione
  • Prendi in considerazione la possibilità di eliminare gli indici durante il caricamento di massa

Ben riassunto in questo diagramma di flusso dal caricamento dei dati e dalla Guida alle prestazioni : inserisci la descrizione dell'immagine qui

Come altri hanno già detto, è necessario ottenere alcuni contatori di prestazioni per stabilire l'origine del collo di bottiglia, poiché i tuoi esperimenti suggeriscono che l'IO potrebbe non essere la limitazione. Il caricamento dei dati e la Guida alle prestazioni includono un elenco di tipi di attesa SQL e contatori delle prestazioni da monitorare (non ci sono ancore nel documento da collegare ma questo è circa il 75% attraverso il documento, nella sezione "Ottimizzazione del carico di massa")

AGGIORNARE

Mi ci è voluto un po 'per trovare il collegamento, ma vale anche la pena di guardare questo talk su SQLBits di Thomas Kejser - le diapositive sono disponibili se non hai il tempo di guardare il tutto. Ripete parte del materiale collegato qui, ma copre anche un paio di altri suggerimenti su come affrontare le alte incidenze di particolari contatori di prestazioni.


Risposta popolare

Sembra che tu abbia fatto molto, ma non sono sicuro che tu abbia avuto la possibilità di studiare il report di Alberto Ferrari SqlBulkCopy Performance Analysis , che descrive diversi fattori per considerare le prestazioni correlate a SqlBulkCopy. Direi che molte cose discusse in quel documento valgono ancora la pena provare che sarebbe bello provare prima.



Autorizzato sotto: CC-BY-SA with attribution
Non affiliato con Stack Overflow
È legale questo KB? Sì, impara il perché
Autorizzato sotto: CC-BY-SA with attribution
Non affiliato con Stack Overflow
È legale questo KB? Sì, impara il perché