Besoin de recommandations pour repousser les limites avec SqlBulkCopy sur SQL Server

bulkinsert database-performance scalability sqlbulkcopy sql-server

Question

Je conçois une application dont l’un des aspects est qu’elle est supposée pouvoir recevoir des quantités énormes de données dans une base de données SQL. J'ai conçu la structure de base de données comme une seule table avec une identité bigint, semblable à celle-ci:

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

J'omettrai de quelle manière j'ai l'intention d'effectuer des requêtes, car cela n'a aucune pertinence pour la question que j'ai.

J'ai écrit un prototype qui insère des données dans cette table à l'aide de SqlBulkCopy. Cela semblait très bien fonctionner dans le laboratoire. J'ai pu insérer des dizaines de millions d'enregistrements à un taux d'environ 3 000 enregistrements par seconde (l'enregistrement complet lui-même est plutôt volumineux, environ 4 000). Comme le seul index de cette table est l'auto-incrémentation de bigint, je n'ai pas constaté de ralentissement, même après la poussée d'un nombre important de lignes.

Etant donné que le serveur SQL de laboratoire était une machine virtuelle avec une configuration relativement faible (4 Go de RAM, partagée avec d'autres systèmes de disque de machines virtuelles), je m'attendais à un débit nettement meilleur sur la machine physique, mais cela n'a pas été le cas L'augmentation de la performance était négligeable. Je pourrais peut-être obtenir des inserts 25% plus rapides sur une machine physique. Même après avoir configuré RAID0 à 3 lecteurs, qui fonctionnait 3 fois plus rapidement qu'un lecteur unique (mesuré par un logiciel d'analyse comparative), je n'ai obtenu aucune amélioration. Fondamentalement, un sous-système de disque plus rapide, un processeur physique dédié et une double RAM ne se traduisent presque pas par un gain de performance.

J'ai ensuite répété le test en utilisant l'instance la plus grande sur Azure (8 cœurs, 16 Go) et j'ai obtenu le même résultat. Ainsi, l'ajout de nouveaux cœurs n'a pas modifié la vitesse d'insertion.

À ce stade, je me suis amusé avec les paramètres logiciels suivants sans aucun gain de performances significatif:

  • Modification du paramètre SqlBulkInsert.BatchSize
  • Insertion simultanée de plusieurs threads et réglage du nombre de threads
  • Utilisation de l'option de verrouillage de table sur SqlBulkInsert
  • Éliminer la latence du réseau en l'insérant à partir d'un processus local à l'aide du pilote de mémoire partagée

J'essaie d'augmenter les performances au moins 2 ou 3 fois, et mon idée initiale était que le fait de lancer plus de matériel aurait pour effet de provoquer des modifications, mais jusqu'à présent, ce n'est pas le cas.

Alors, quelqu'un peut-il me recommander:

  • Quelle ressource pourrait être soupçonné un goulot d'étranglement ici? Comment confirmer?
  • Existe-t-il une méthodologie pour laquelle je pourrais essayer d’obtenir une amélioration d’insert en vrac évolutive et fiable en tenant compte du fait qu’il existe un seul système de serveur SQL?

UPDATE Je suis certain que l'application de chargement n'est pas un problème. Il crée un enregistrement dans une file d'attente temporaire dans un thread séparé. Ainsi, lorsqu'il y a une insertion, cela se passe comme ceci (simplifié):

===>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

les timings sont enregistrés et la partie qui crée une file d'attente ne prend jamais de morceau significatif

UPDATE2 J'ai implémenté la collecte de la durée de chaque opération de ce cycle et la présentation est la suivante:

  • queue.Skip().Take() - négligeable
  • MakeDataTable(batch) - 10%
  • GetBulkCopy() - négligeable
  • WriteToServer(data) - 90%

UPDATE3 Je conçois pour la version standard de SQL. Je ne peux donc pas compter sur le partitionnement, car il n'est disponible que dans la version Enterprise. Mais j'ai essayé une variante du schéma de partitionnement:

  • créé 16 groupes de fichiers (G0 à G15),
  • fait 16 tables pour insertion seulement (T0 à T15) chacune liée à son groupe individuel. Les tables sont sans aucun index, pas même en cluster d'identité.
  • les threads qui insèrent des données parcourent chacun des 16 tableaux. Cela garantit quasiment que chaque opération d’insertion en bloc utilise son propre tableau.

Cela a entraîné une amélioration d'environ 20% de l'insert en vrac. Les cœurs de processeur, l'interface LAN et les E / S de lecteur n'ont pas été maximisés et utilisés à environ 25% de la capacité maximale.

UPDATE4 Je pense que c'est maintenant aussi bon que possible. J'ai été capable de pousser les inserts à une vitesse raisonnable en utilisant les techniques suivantes:

  • Chaque insertion en bloc entre dans sa propre table, puis les résultats sont fusionnés dans la principale
  • Les tables sont recréées fraîches pour chaque insert en vrac, des verrous de table sont utilisés
  • Utilisé implémentation IDataReader à partir d'ici au lieu de DataTable.
  • Inserts en masse réalisés à partir de plusieurs clients
  • Chaque client accède à SQL en utilisant un VLAN gigabit individuel
  • Les processus secondaires accédant à la table principale utilisent l'option NOLOCK
  • J'ai examiné sys.dm_os_wait_stats, et sys.dm_os_latch_stats pour éliminer les contentions

J'ai du mal à décider à ce stade qui obtient un crédit pour la question répondue. Ceux d'entre vous qui n'obtiennent pas de "réponse", je m'excuse, ce fut une décision très difficile et je vous remercie tous.

UPDATE5 : L'élément suivant pourrait utiliser une optimisation:

  • Utilisé implémentation IDataReader à partir d'ici au lieu de DataTable.

À moins que vous n'exécutiez votre programme sur une machine avec un nombre de cœurs de processeur important, il pourrait nécessiter une nouvelle factorisation. Comme il utilise la réflexion pour générer des méthodes get / set, cela représente une charge importante pour les processeurs. Si performance est une clé, cela ajoute beaucoup de performance lorsque vous codez IDataReader manuellement, de sorte qu'il soit compilé au lieu d'utiliser la réflexion

Réponse acceptée

Pour des recommandations sur le réglage de SQL Server pour les chargements en masse, voir le document Guide de chargement et de performances des données de MS, ainsi que le Guide pour l'optimisation de l'importation en bloc de livres en ligne. Bien qu'ils se concentrent sur le chargement en bloc à partir de SQL Server, la plupart des conseils s'appliquent au chargement en bloc à l'aide de l'API client. Ces documents s'appliquent à SQL 2008 - vous ne dites pas quelle version de SQL Server vous souhaitez cibler
Les deux ont beaucoup d'informations qu'il vaut la peine de passer en détail. Cependant, quelques points saillants:

  • Consignez au minimum l'opération en bloc. Utilisez la récupération en bloc ou simple. Vous devrez peut-être activer traceflag 610 (mais voyez les mises en garde à ce sujet).
  • Ajuster la taille du lot
  • Envisagez de partitionner la table cible
  • Envisagez de supprimer des index lors du chargement en bloc

Joliment résumé dans cet organigramme tiré du Guide de chargement et de performance des données : entrez la description de l'image ici

Comme d'autres l'ont dit, vous devez vous procurer des compteurs de performance pour déterminer la source du goulot d'étranglement, car vos expériences laissent penser que l'IO n'est peut-être pas la limitation. Le Guide de chargement et de performances des données inclut une liste des types d’attente SQL et des compteurs de performance à surveiller (le document ne contient pas d’ancres à relier, mais il représente environ 75% du document, dans la section "Optimisation du chargement en bloc").

METTRE À JOUR

Il m'a fallu un certain temps pour trouver le lien, mais cette présentation de Thomas Kejser sur SQLBits mérite également d'être visionnée - les diapositives sont disponibles si vous n'avez pas le temps de regarder le tout. Il reprend une partie du matériel lié ici mais couvre également quelques autres suggestions sur la façon de traiter les incidences élevées de compteurs de performance particuliers.


Réponse populaire

Il semble que vous ayez fait beaucoup, mais je ne sais pas si vous avez eu l'occasion d'étudier le rapport d' analyse des performances Alberto Ferrari SqlBulkCopy , qui décrit plusieurs facteurs permettant de prendre en compte les performances associées à SqlBulkCopy. Je dirais que beaucoup de choses discutées dans ce document valent toujours la peine d'essayer. Ce serait bien d'essayer d'abord.



Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi
Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi