De quoi dépend la vitesse d'interrogation et d'insertion des bases de données ?

Question

À mon travail, nous avons une petite base de données (deux cents tables et peut-être un total d'un million de lignes environ).

J'ai toujours pensé qu'il serait assez rapide, de l'ordre de plusieurs dizaines de milliers d'insertions par seconde, les requêtes prenant quelques millisecondes une fois la connexion établie.

Bien au contraire, nous avons des problèmes de performance qui font que nous n'obtenons que quelques centaines d'insertions par seconde et que les requêtes, même les plus simples, prennent un temps fou.

Je ne sais pas vraiment si c'est le comportement/les performances standard ou si nous faisons quelque chose de mal. Par exemple, 1500 requêtes qui impliquent de joindre 4 tables sur une seule colonne clé prennent environ 10 secondes. Il faut 3 minutes pour charger 300K de données au format xml dans la base de données en utilisant des insertions simples sans violer aucune contrainte.

La base de données est SQL Server 2005 et possède un riche modèle de dépendance relationnelle, ce qui signifie beaucoup de relations et de catégorisations sur les données ainsi qu'un ensemble complet de contraintes de contrôle pour les codes de catégorisation et plusieurs autres choses.

Ces heures sont-elles justes ? Si non, qu'est-ce qui pourrait affecter les performances ? (Toutes les requêtes sont effectuées sur des colonnes indexées)

Answer 1

Pour avoir une comparaison approximative : le Record de référence TPC-C pour SQL Server est à environ 1,2 million de transactions par minute, et il en est ainsi depuis environ 4 ans (limité par la limite de 64 CPU du système d'exploitation). C'est quelque chose dans la fourchette de ~16k transactions par seconde . Cela se passe sur des machines très haut de gamme, 64 processeurs, beaucoup de RAM, des clients affinitaires par nœud NUMA et un système d'E/S très court (seulement 1-2% de chaque broche est utilisée). Gardez à l'esprit que ce sont des transactions TPC-C, donc elles consistent en plusieurs opérations (je pense que c'est 4-5 lectures et 1-2 écritures chacune en moyenne).

Vous devez maintenant adapter ce matériel haut de gamme à votre déploiement réel et vous obtiendrez une estimation de vos attentes pour l'ensemble du système. Traitement des transactions OLTP .

Pour le téléchargement des données, l'actuel Le record du monde est d'environ 1 To en 30 minutes. (si elle est toujours d'actualité...). Plusieurs dizaines de milliers d'insertions par seconde, c'est assez ambitieux, mais réalisable, quand c'est bien fait sur du matériel sérieux. L'article dans le lien contient des conseils et des astuces pour l'ETL à haut débit (par exemple, utiliser plusieurs flux de téléchargement et les affiner aux nœuds NUMA).

Dans votre situation, je vous conseille avant tout mesure donc vous trouvez les goulots d'étranglement et ensuite vous demandez spécifique des questions sur la manière de résoudre des problèmes spécifiques. Un bon point de départ est le Livre blanc sur les attentes et les files d'attente .

Answer 2

L'indexation est un facteur important ici. Lorsqu'elle est effectuée correctement, elle peut accélérer les instructions Select, mais n'oubliez pas qu'un index ralentit l'insertion, car le serveur ne met pas seulement à jour les données, mais aussi les index. L'astuce est la suivante :

1) Déterminez les requêtes qui sont vraiment critiques en termes de vitesse, ces requêtes devraient avoir des index optimaux pour elles.

2) Le facteur de remplissage est également important ici. Il fournit un espace vide à une page d'index pour un remplissage ultérieur. Lorsqu'une page d'index est pleine (suffisamment de lignes sont insérées), une nouvelle page doit être créée, ce qui prend encore plus de temps. Cependant, les pages vides occupent de l'espace disque.

Mon astuce est la suivante : pour chaque application, je fixe des priorités comme suit :

1) Vitesse de lecture (SELECT, quelques UPDATE, quelques DELETE) - plus cette priorité est élevée, plus je crée d'index.
2) Vitesse d'écriture (INSERT, quelques Update, quelques DELETE) - plus cette priorité est élevée, moins je crée d'index.
3) Efficacité de l'espace disque - plus cette priorité est élevée, plus mon facteur de remplissage est élevé.

Notez que ces connaissances s'appliquent généralement à SQL Server. Votre expérience peut varier avec un autre SGBD.

L'évaluation de la déclaration SQL peut également être utile ici, mais il faut être un vrai pro pour cela. Une analyse minutieuse de WHERE et JOIN peut aider à déterminer les goulets d'étranglement et les endroits où vos requêtes souffrent. Activez SHOWPLAN et les plans de requête, évaluez ce que vous voyez et planifiez en conséquence.

Regardez aussi SQL Server 2008, Joins indexés !

Answer 3

Un modèle de "dépendance relationnelle riche" n'est pas propice à des vitesses d'insertion rapides. Chaque contrainte (clé primaire, contrôle des valeurs, et surtout clés étrangères), doit être vérifiée pour chaque enregistrement inséré. C'est beaucoup plus de travail qu'une "simple insertion".

Et il importe peu que vos insertions ne présentent aucune violation de contrainte, le temps sera probablement consacré à la vérification de vos clés étrangères. A moins que vous n'ayez aussi des triggers, car ils sont encore pires.

Bien sûr, il est possible que la seule chose qui ne va pas est que votre table d'insertion est le parent-FK pour une relation FK "must-have-children" pour une autre table qui a oublié d'ajouter un index pour le côté enfant-FK sur la relation FK (ce n'est pas automatique et est souvent oublié). Bien sûr, c'est juste espérer avoir de la chance :-)

Answer 4

Les contraintes ajoutent une petite pénalité de performance. Il doit également mettre à jour les index pour chaque insertion. Et si vous ne placez pas plusieurs insertions dans une seule transaction, le serveur de base de données doit exécuter chaque insertion comme une nouvelle transaction distincte, ce qui le ralentit encore plus.

150 requêtes/seconde joignant 4 tables semble normal, bien que je ne connaisse pas bien vos données.

Answer 5

"Je me suis toujours attendu à ce qu'il soit assez rapide, de l'ordre de plusieurs dizaines de milliers d'insertions par seconde et avec des requêtes prenant quelques millisecondes une fois la connexion établie."

(a) Les performances des bases de données dépendent à 99% de la quantité d'E/S physiques (à moins que vous ne soyez dans un petit site utilisant une base de données en mémoire, qui peut se permettre de reporter toutes les E/S physiques jusqu'à la fin de la journée). (b) Les entrées/sorties de la base de données impliquent non seulement les entrées/sorties physiques réelles vers les fichiers de données, mais aussi les entrées/sorties physiques pour conserver les journaux/logs/... (et la journalisation est même souvent effectuée). (et la journalisation est même souvent effectuée en mode double (c'est-à-dire deux fois) depuis environ deux décennies). (c) La manière dont la "quantité d'insertions" correspond à la "quantité d'E/S physiques" est entièrement déterminée par les options dont dispose le concepteur de la base de données pour optimiser la conception physique. On ne peut dire qu'une chose en général à ce sujet : Les systèmes SQL échouent le plus souvent (à fournir les options nécessaires pour transformer des "dizaines de milliers d'insertions" en seulement peut-être "quelques centaines" d'E/S physiques). Cela signifie que "des dizaines de milliers d'insertions" impliquent généralement aussi "des milliers d'E/S physiques", ce qui implique généralement "des dizaines de secondes".

Cela dit, votre message semble exprimer une attente selon laquelle, d'une manière ou d'une autre, "les insertions sont extrêmement rapides ("des dizaines de milliers par seconde")" tandis que "les requêtes sont plus lentes" ("millisecondes par requête", ce qui implique "moins de 1000 requêtes par seconde"). Cette attente est absurde.