Petites tables ou grandes tables. . . Quelles conséquences sur les performances ?

[SQLpro]

Encore une fois vous vous focalisez tous sur les lectures, alors que ces dernières peuvent être non verrouillantes (mode READ UNCOMMITED ou SNAPSHOT).
Ce qui pose problème c'est bien plus les accès concurrent en écriture dans une seule et même table !

A +

[pacmann]

Sur une seule et même table ou sur une seule et même ligne ?

Dans le premier cas, ça ne concerne encore une fois que SQL Server..
En tous cas sous Oracle, dans le cas général, une modification d'une ligne ne lock que cette ligne...

[Luc Orient]

Envoyé par pacmann

... En tous cas sous Oracle, dans le cas général, une modification d'une ligne ne lock que cette ligne...

Avec DB2 for z/OS, on choisit la granularité du verrouillage (ligne, page ou table) avec souvent une préférence pour la page.

Dans un contexte de forte concurrence que sont les systèmes OLTP sur Mainframe, il ne viendrait à l'idée de personne de verouiller la table en mise à jour pour un seul processus ...

[fsmrel]

Bonsoir,

Vouloir tout mettre dans le minimum de tables et se retrouver avec un maximum de colonnes par table est évidemment la source de tous les ennuis (obésité, multiplicité des index (pensons aux conséquences des mises à jour...), mauvaise performance (y-compris pour les tâches de service : réorganisations, sauvegardes, j’en passe et des meilleures), redondances, incohérences, contentions, etc.) Pour assurer le coup, bannissons les « groupes répétitifs » et tant qu’à faire, normalisons au moins en 4NF, là au moins on est sûr de multiplier les tables de peu de colonnes, avec moins d’encombrement, de redondance, de perte de temps en mise à jour, on gagnera en cohérence, on économisera les ressources, etc.

Envoyé par Luc Orient

Avec DB2 for z/OS, on choisit la granularité du verrouillage (ligne, page ou table) avec souvent une préférence pour la page.

Dans un contexte de forte concurrence que sont les systèmes OLTP sur Mainframe, il ne viendrait à l'idée de personne de verrouiller la table en mise à jour pour un seul processus ...

Je confirme !

Envoyé par SQLpro

Pour le ROWID...

Ce choix n'est pas curieux il est même très intéressant, c'est la notion d'index cluster...
Effectivement tous les index non cluster reposent sur la valeur de la clef de l'index cluster pour retrouver la ligne originale.

C’est le concept d’index cluster façon SQL Server. Chaque SGBD voit le clustering à sa façon. On a déjà évoqué le sujet ici ou là à propos des soixante-sept millions de bovins chers à CinePhil.

Permettez-moi de me citer :

Envoyé par fsmrel

Si j’ai bien compris, et pour résumer : physiquement parlant, avec SQL Server, les données d’une table sont intégrées à l’index cluster, dont elles constituent le niveau feuilles. A cette occasion et par comparaison, je rappelle qu’avec DB2, la table est hébergée dans un table space à part. Reprenons le cas de la recherche d’une vachette. Si l’on effectue un accès selon la séquence cluster, tout le monde a bon, en quatre entrées/sorties (symbolisées par des flèches rouges) on récupère les données de Zaza parmi soixante-sept millions de bovins :

Envoyé par fsmrel

So far, so good. Si l’on effectue maintenant une recherche qui ne met pas directement en jeu l’index cluster, la stratégie n’est plus la même, cf. figure B ci-dessous. Dans tous les cas, le SGBD utilise les services de l’index non cluster qui va bien (s’il existe). Dans un cas, les feuilles de cet index adressent la racine de l’index cluster et dans l’autre cas directement le table space hébergeant la table, d’où un nombre d’entrées/sorties (ou de cailloux pour le Petit Poucet) égal à 8 dans un cas et à 5 dans l’autre cas (avec en l’occurrence un gain en I/O time plus que substantiel, conséquence du court-circuit de l’index cluster).

Envoyé par fsmrel

Est-ce bien cela ? Si ce n’est pas le cas, je referai les dessins.

Si quelqu'un avait la bonté de confirmer ou infirmer...

Envoyé par SQLpro

Cela permet beaucoup de choses, comme par exemple en cas de défragmentation de la table, ne pas toucher aux index !

On en a parlé ici et ce problème a toujours fait l’objet des plus grands soins de la part des SGBD, même bien avant l’arrivée des SGBD relationnels. Par exemple, ADABAS avec ses ISN numbers (~ RID) était exemplaire à ce sujet.

[pacmann]

Salut,

C'est exactement ce que j'essayais de dire... mais c'est vraiment mieux avec les dessins

Et pour refaire un peu de pub, sous Oracle, tu peux choisir si tu veux le modèle SQL Server (Organization = index) ou DB2 (Organization = heap).

[CinePhil]

Merci François de m'avoir raffraichi la mémoire sur ce vieux cours que tu avais donné lorsque je travaillais sur les bovins. On peut dire que j'avais carrément tout oublié !

Il reste cependant des points obscur mais je ne suis pas sûr que ce soit le lieu pour les aborder.

En fait, il faudrait carrément faire un article sur le fonctionnement des index dans les SGBD mais je n'ai pas le temps de m'y coller. En plus, je ne pourrais pas faire ça tout seul vu mon peu de connaissances en ce domaine.

Mais si quelqu'un veut lancer le projet, qu'il ne se gène pas !

[Waldar]

Envoyé par pacmann

Et pour refaire un peu de pub, sous Oracle, tu peux choisir si tu veux le modèle SQL Server (Organization = index) ou DB2 (Organization = heap).

C'est pareil chez la concurrence, sur SQL-Server aussi tu peux choisir d'avoir des index cluster ou non cluster.

[pacmann]

Ho, c'était donc ça...

Envoyé par Waldar

là où SQL-Server le propose quasiment par défaut.

[Waldar]

Tout est dans le quasiment !

[SQLpro]

Envoyé par fsmrel

Si quelqu'un avait la bonté de confirmer ou infirmer...

je te confirme....

Mais je rajouterais deux choses :
1) on n'est pas obligé de créer un index clustered sur chaque table dans MS SQL Server, même si c'est une bonne pratique.
2) il faut bien choisir son index cluster si l'on doit en implanter un : le plus petit possible, avec le plus grand nombre de valeur possible, invariant et si possible monotone en sus d'être UNIQUE. Bref, l'auto incrément ou un horodatage en PK est parfait
3) dans les SGBDR on s'aperçoit que les données les plus utilisées sont (à différentes échelles, mais c'est quasiment TOUJOURS vrai) les plus récentes :

• pour les commandes, facture, bons de livraisons c'est extrêmement vrai
• pour les produits c'est assez vrai (pense au téléviseur noir et blanc à tube cathodique),
• pour les clients c'est aussi relativement vrai (un client d'il y a 10 ans à plus de chance d'être mort qu'un client qui viens tout juste d'être saisi.)

Or l’organisation d'une table en cluster avec un auto incrément ou un horodatage maximise la mise en cache des données les plus fréquemment scrutées puisqu'elle regroupe dans les mêmes pages les données les plus récentes... alors que pour une table en HEAP ce n'est jamais le cas.... ce qui oblige à farcir la mémoire de pages sous utilisées (et conduit donc à une durée de vie des pages en mémoire bien moins longues..., donc du Roll Over, donc des temps de réponse globalement moins bon !!!)

Bref, il n'y a pas que le nombre de page a lire qui a son importance !

Comme tu le sais, le système est global (théorie des systèmes) et le voir par un seul bout de la lorgnette peut s'avérer casse gueule !

A +

[fsmrel]

Bonsoir,

Envoyé par SQLpro

je te confirme...

Merci...

Envoyé par SQLpro

il n'y a pas que le nombre de pages à lire qui a son importance !

On est bien d’accord. Lire un maximum de pages dans un minimum de temps est une excellente chose, mais si c’est ce qui est lu est faux, on a tout perdu.

Mais inversement, avec des données saines, s’il faut dix jours pour effectuer un batch quotidien sensible (j’ai eu à auditer et réparer ça...) parce qu’il y a des I/O bound dans tous les sens, ça n’est pas mieux (voir « Le clustering selon DB2 for z/OS », plus précisément la partie « Le rôle crucial de l'index cluster. I/O bound vs CPU bound »).

Les données ne sont pas forcément toutes présentes en mémoire, et les performances désastreuses qui sont la conséquence des I/O bound incitent à faire en sorte que les index cluster de deux tables (et plus) à joindre soient synchrones pour éliminer ce phénomène.

Je suis évidemment d'accord que les données les plus utilisées sont les plus récentes et qu’en conséquence l’auto-incrément ou un horodatage pour une clé primaire est une bonne chose (avec un bémol si les INSERT provoquent des phénomènes de verrouillage) parce que cela évacue efficacement les I/O bound lorsqu’on exécute des SELECT.

Cela dit, il y a des situations dans lesquelles on peut avoir intérêt à ce que les lignes d’une table qui sont chronologiquement consécutives soient en fait éparpillées dans l’espace (façon puzzle bien sûr). Si une foule d’utilisateurs saisissent des commandes en même temps, il y aura risque de contention impliquant les tables des commandes et des lignes de commande, conséquence du mécanisme de verrouillage (je parle pour DB2 for z/OS où, comme le rappelle Luc Orient, on privilégie plutôt le verrouillage au niveau page, sachant que je ne connais pas les usages avec SQL Server...). Que les valeurs de la clé primaire de la table des clients soient hachées plutôt qu’incrémentées ne me gêne pas, au contraire, car les risques de contention en cas de mise à jour des clients deviennent très minimes (création, modification, suppression). En revanche, le hachage des valeurs prises par la clé de la table des commandes et de celle de la table des lignes de commande n’est évidemment pas recommandé, car si les risques de contention disparaissent les I/O bounds ne manqueront alors pas de se manifester en masse...

C’est là où la soute et la dunette se rejoignent : une des clés de la performance des applications est liée à l’utilisation de l’identification relative au niveau conceptuel (disons MCD Merise). Dans la soute, si en conséquence la clé (hachée) de la table des clients est le singleton {CliId}, celle de la table des commandes est composée du couple {CliId, CdeId}, celle de la table des lignes de commande est composée du triplet {CliId, CdeId, LigneId}, celle de la table des engagements sur ligne de commande est composée du quadruplet {CliId, CdeId, LigneId, EngId}, etc., façon poupées russes, alors les I/O bounds et les contentions seront absents, si pour chacune de ces tables la clé de l’index cluster est celle de la clé de la table.

Si l’on souhaite savoir quels camions livrent le client untel (cf. Dénormalisation vs amélioration (optimisation), le 4e exemple et la note concernant l’identification relative), grâce à l’identification relative répercutée sur le clustering, non seulement on élimine le phénomène d’I/O bound, mais en plus on élimine des jointures qui, si on utilisait l’identification absolue seraient inévitables (2 jointures contre 5 dans l’exemple).

Pour une entreprise dans laquelle les traitements qui tournent autour des commandes sont ceux qui méritent la plus grande attention, la mise en œuvre de l’identification relative associée au clustering aura un impact déterminant. La stratégie est du reste la même si l’on traite des appels de cotisation dans les caisses de retraite ou autres organismes, etc.

Il est évident que toutes les requêtes ne peuvent pas bénéficier de ce traitement de faveur, et qu’il restera des I/O bound quelque part. Néanmoins, lors de l’étape de conception, suite aux entretiens avec la maîtrise d’œuvre et les chefs de projets, en tant que DBA on sait quand même quelles seront les requêtes les plus fréquentes, les plus sensibles, celles qui si on n’y prend pas garde feront que les applications partiront en vrille (j’en reviens au batch lourd quotidien qui a duré dix jours lors de sa mise en production (sur une machine bases de données à cent-vingt-huit millions de francs à l’époque, et qui n’avait bien entendu jamais fait l’objet du moindre prototypage de performances...).

Envoyé par SQLpro

Comme tu le sais, le système est global (théorie des systèmes) et le voir par un seul bout de la lorgnette peut s'avérer casse gueule !

Don’t worry! La théorie c'est bien, mais la pratique c'est pas mal non plus. Ça fait quarante cinq ans que la validité des données, la performance des applications et la satisfaction de mes clients font partie de mes préoccupations...

[SQLpro]

Note bien que tout ceci est en train de se remettre en cause notamment avec les SSD qui, agrégés sous forme RAID, donnent parfois des résultats spectaculaires...

Pour ce qui est de SQL Server, cela fonctionne de même (préférence au verrouillage page, mais descente à la ligne en cas de concurrence).

Pour ce qui concerne le hot spot de calcul des clefs auto incrémentées dans SQL Server, il a fait l'objet d'une excellente optimisation, mais au prix d'un bug rarissime, mais néanmoins gênant : celui de doublons (évidemment rejetés s'il y a contrainte PK ou UNIQUE).

A +

[fsmrel]

Bonsoir,

Envoyé par SQLpro

Pour ce qui est de SQL Server, cela fonctionne de même (préférence au verrouillage page, mais descente à la ligne en cas de concurrence).

Qui détermine la préférence ? Le DBA ? SQL Server ?
Si le granule de verrouillage est la page, et s’il y a de la concurrence en mise à jour, SQL Server prend-il dynamiquement l’initiative de passer au verrouillage au niveau de la ligne, quitte à revenir plus tard à la page (en supposant qu'il sache inférer que ça vaut le coup) ?

D'après ce que je lis dans la doc, le SGBD se baserait plutôt sur les caractéristiques du schéma et des requêtes :

The Database Engine automatically determines what locks are most appropriate when the query is executed, based on the characteristics of the schema and query.