Une seule table ou plein ?

[Sergejack]

Bonjour.

J'aimerais avoir votre avis et connaître vos habitudes pour un cas pratique :
J'ai besoin de gérer des messages à envoyer à des utilisateurs.
Chaque message est ordonné du plus ancien au plus récent et ne concerne/n'intéresse qu'un seul et unique utilisateur.

Il y a deux scénarios à comparer :
1) Une seule table (userid, messageid, ...) pour tous les utilisateurs
2) Une table (messageid, ...) par utilisateur (table_user001, table_user002, ...)

Sachant que les messages seront des données très mouvementés (par insertion principalement) et par soucis de performance, laquelle de ces solutions préfériez-vous et pourquoi ?

Merci pour votre avis.

[Sergejack]

Moi, je pense qu'une table par utilisateur est préférable ainsi, le SGBD aura très peu à faire pour maintenir les indexes (ces derniers ne portant dès lors plus que sur un identifiant de message exclusivement croissant).

Je ne pense pas que d'avoir beaucoup de tables ainsi que beaucoup de création de tables crée une forte charge pour un SGBD.

[Oishiiii]

De quels "soucis de performances" parlez-vous ?

Vous avez estimé la taille de la table et des indexs ?

La recherche par exemple, de tous les messages d'un utilisateur ne serait-pas performante ? J'en doute très fortement.

[Yanika_bzh]

Sans aucune hesitation, réponse n°1 !
Une seule table.
Pour plusieures raisons :

1) C'est le modele le plus propre
2) C'est le plus maintenable
3) C'est le plus optimisé

Il n'y a pas de relation entre architecture et données, en effet ajouter un utilisateur ne demandera pas l'intervention du DBA (en esperant qu'il ne soit pas en vacances) pour créer une (ou plusieurs) table(s) pour le nouvel arrivant.

Vous n'aurez pas a vous trimbaler des requetes dynamiques en fonction de l'utilisateur.

En ce qui concerne les performances, les SGBDR ont été developpés et optimisés pour ce genre de choses. Apres il existe encore des procédés d'optimisation pour les indexes (partionnement entre autre), et surtout une bonne écriture de vos requetes devrait ammener a des performances optimales (ou pas tres loin).

[Sergejack]

Si on a une seule table et une insertion tous les dixièmes de secondes, ça fait quand même beaucoup de recalcule sur la totalité de l'indexe.
Je trouve ça un peu dommage.

[David55]

J'aime bien utiliser ce genre de structure :http://fr.wikipedia.org/wiki/Objet_composite

Ainsi, on peut archiver les messages et naviguer entre eux!

A voir mais je pense qu'une seul table est le moyen le plus propre!

[Yanika_bzh]

cela ne sera que deplacer le probleme, car si vos insertions n'ont lieu que pour 1 seul utilisateur, cela revient a la meme chose !
La solution de partitionnement d'indexes peut etre une solution dans ce cas.

[nathieb]

Bonjour,

Il faut rentrer plusieurs critères dans cette réflexion :

- Quel SGBD ? sur quelle machine ?
- Type de réseau ? proxy, bande passante ?
- criticité ?
- redondance ?

Si on s'en tient à une formalisation simple
Un utilisateur (nom prénom ...) reçoit/envoie des messages

Je vois trois tables users, messages, type de messages à l'extrème.
Bref dans la table messages, il y aura un test sur la clef étrangère de l'utilisateur
et encore .... , je ne suis pas sûr que cela soit risqué.

On reçoit en moyenne, 100 message/jour on en envoie 20 en étant optimiste
sur 200 utilisateurs sur 2 actions création (insert), delete soit (100 + 20)*200 * 2 non ?

48 000 requêtes/jours, c'est pas franchement la mort ...

C'est une question de dimensionnement et cela peut/doit être projeté.
Le modèle peut être imparfait, mais cela permet de se poser les bonnes questions.

Souvent on modélise (académique ), on teste ( vrai test ) on optimise, identification des goulots d'étranglements (dénormalisation).

C'est comme dire avec le dernier GSXR full power à Carol, je vous mets, faux.
Le circuits est sinueux et technique, donc un super motard avec un bon pilote te met out .
J'adore ;=)


Olivier

[chaplin]

Envoyé par Sergejack

Une table (messageid, ...) par utilisateur (table_user001, table_user002, ...)
...
Merci pour votre avis.

J'appellerais ça de la suroptimisation. Les données de même structures se trouvent dans une et une seule table. A la limite, on pourrait envisager une vue union de deux tables de même structure et une recopie de l'une dans l'autre pour optimiser le calcul d'index, sinon cette approche est implémentée de façon transparente sur la version Enterprise de SQL server.

[fsmrel]

Envoyé par Sergejack

Si on a une seule table et une insertion tous les dixièmes de secondes, ça fait quand même beaucoup de recalcule sur la totalité de l'indexe.
Je trouve ça un peu dommage.

Je ne sais pas quel SGBD vous utilisez, mais avec le mien (DB2 for z/OS), concernant ses statistiques, on lui dit une bonne fois pour toutes que la table fait dix millions de lignes et il n’a rien à recalculer. On paramètre les table spaces et index (PCTFREE, FREEPAGE, etc.) de telle sorte qu’un taux élevé d’insertions ne soit pas sensible (disons avec quelques milliers d’utilisateurs connectés). Régulièrement, on demande à DB2 de pondre ses statistiques sur le taux de désorganisation des table spaces et index et au besoin on déclenche la réorganisation de tel table space et/ou index. Tout cela fait partie du travail du DBA (qui en production automatise ce genre de tâches).

Envoyé par nathieb

Souvent on modélise (académique), on teste ( vrai test ) on optimise, identification des goulots d'étranglements (dénormalisation).

Dans le contexte des très grosses applications de gestion avec un nombre de tables qui peut atteindre les 2000 (banque, assurance, caisses de retraites, etc.) on modélise toujours, sinon on est mort ; ne pas modéliser relève de la faute professionnelle et, croyez-moi, on est loin des cercles académiques, ce sont les sous de l’entreprise qui sont en jeu.

Les goulots d’étranglement n’ont rien à voir avec la normalisation, et en tant que DBA depuis les années soixante-dix, j’en sais quelque chose. Vous avez raison concernant les tests, et pour cela on met en œuvre des prototypes de performance, lesquels sont révélateurs de problèmes d’I/O bound qui, eux, font patiner le système, et correspondent en général aux goulots d’étranglement auxquels vous faites allusion.

[nathieb]

Bonjour,

Effectivement, vous avez raison, j'ai pris des raccourcis.
Pour ce qui est de la modélisation, je voulais simplement
dire que l'on doit s'appuyer sur des normes ou des les langages communs
type UML.
Comme je suis sur la partie applicative, nous devons souvent prendre
en compte cette couche. Car même avec un SGBD de qualité et un modèle
bien étudié, il arrive souvent que les personnes qui exploite cette
partie fassent n'importe quoi ( boucle infinie, requête de sous requête imbriquées dans un sous select, etc ... )
LA partie I/O fait partie de ces pb sous les grosses applis.

Les goulots d’étranglement n’ont rien à voir avec la normalisation, et en tant que DBA depuis les années soixante-dix, j’en sais quelque chose. Vous avez raison concernant les tests, et pour cela on met en œuvre des prototypes de performance, lesquels sont révélateurs de problèmes d’I/O bound qui, eux, font patiner le système, et correspondent en général aux goulots d’étranglement auxquels vous faites allusion.

Mais la modélisation et les tests sont la clef d'un succès ...

Olivier

[chaplin]

Envoyé par fsmrel

on lui dit une bonne fois pour toutes que la table fait dix millions de lignes et il n’a rien à recalculer. On paramètre les table spaces et index (PCTFREE, FREEPAGE, etc.) de telle sorte qu’un taux élevé d’insertions ne soit pas sensible (disons avec quelques milliers d’utilisateurs connectés). Régulièrement, on demande à DB2 de pondre ses statistiques sur le taux de désorganisation des table spaces et index et au besoin on déclenche la réorganisation de tel table space et/ou index.

1) D'une manière ou d'une autre, on échappe pas au fait de recalculer les index, quelque soit le nom de la méthode utilisée.
2) Ensuite il faut calibrer la base de données par rapport aux besoins.

Sinon, c'est toujours intéressant de voir les approches de l'un et de l'autre.

PS: Si table = 10 000 000 d'enregistrements alors rien à calculer, faut-il le comprendre de cette manière.

[fsmrel]

Bonjour,

Envoyé par nathieb

Comme je suis sur la partie applicative, nous devons souvent prendre en compte cette couche. Car même avec un SGBD de qualité et un modèle bien étudié, il arrive souvent que les personnes qui exploite cette partie fassent n'importe quoi ( boucle infinie, requête de sous requête imbriquées dans un sous select, etc ... )
La partie I/O fait partie de ces pb sous les grosses applis.

Vous avez raison, les programmes en général et les requêtes SQL qu’ils hébergent en particulier sont systématiquement à expertiser, auditer et bien souvent à améliorer (optimiser en franglais) : les développeurs restent sur le c... quand on leur démontre que tel ou tel de leurs programmes (batch lourd) pourrait durer 10¹² secondes (sans que ce soit forcément de leur faute !) et qu’on peut ramener cela à 10 minutes. Mais les problèmes d’I/O peuvent avoir des causes très diverses, auxquelles on ne pense pas forcément immédiatement : ça peut être la conséquence d’une organisation inter-projets conduisant à émettre des dizaines de millions d’appels « S’il vous plaît/ Merci ! » à des services, des composants dont l’organisation peut être très simple, avec des requêtes SQL irréprochables : cette fois-ci c’est cette organisation inter-projets qui est revoir. Ça peut être la conséquence de l’utilisation d’un AGL qui produit lui-même les requêtes SQL mais qui est algébriquement incomplet et remplace l’opération d’UNION par un OU, etc., etc.

Mais dans une organisation des services digne de ce nom, ce sont les DBA qui auditent les requêtes et les programmes avant leur mise en production, ils diagnostiquent les problèmes de performance et indiquent aux équipes de développement les aménagements à apporter aux programmes pour résoudre ces problèmes.

Envoyé par nathieb

Mais la modélisation et les tests sont la clef d'un succès ...

Ce sont deux conditions nécessaires mais non suffisantes. A quoi sert un modèle exemplaire, conforme aux canons de la modélisation si le recueil des règles de gestion des données se révèle incomplet alors que le projet est bien avancé dans sa réalisation ? La non complétude des règles peut conduire à l’échec du projet, et cela peut être dramatique s’il est sensible, j’ai pu le constater.
Quant à la normalisation, si j’ai pris la peine d’écrire plus de 150 pages sur le sujet, c’est bien parce qu’en tant que baroudeur, j’ai très tôt compris qu’on avait là une des clés du succès, nécessaire, même si bien sûr elle n'est pas non plus la panacée à elle seule.

[nathieb]

Bonjour,

Respect Monsieur fsmrel, j'avais commencé la lecture de votre article sur la normalisation, j'avoue j'ai pas tout compris, je ne suis expert SGBD.
Mais effectivement je m'appuie sur les compétences des dits maîtres dans le domaine.

d'accord pour l'expertise des professionelles admin SGBD, mais de plus en plus les SSII utilisent des frameworks avec une couche ORM ( pas de SQL natif) et là cela se complique...

Bref, tout cela pour dire que le sujet est complexe mais que les solutions existent, il faut cependant penser qu'on a pas tous un expert SGBD, Réseau et ou applicatif du framework X sous la main.
Et cela, c'est du vécu ...

Olivier

[fsmrel]

Envoyé par chaplin

D'une manière ou d'une autre, on échappe pas au fait de recalculer les index, quelque soit le nom de la méthode utilisée.

Qu’entendez-vous exactement par « recalculer les index » ?

Envoyé par chaplin

Ensuite il faut calibrer la base de données par rapport aux besoins

Mais encore ?

Envoyé par chaplin

Si table = 10 000 000 d'enregistrements alors rien à calculer, faut-il le comprendre de cette manière

Là non plus je ne suis pas sûr de ce que vous attendez. Néanmoins je peux dire ceci : DB2 for z/OS s’appuie sur des données volumétriques (nombre de lignes des tables pour faire court, sans parler des index) pour décider de sa stratégie d’accès aux tables et index : cette stratégie ne sera évidemment pas la même selon qu’une table T contient 25 lignes ou 10 000 000. Pour m’assurer de son comportement, je lui demande de bien vouloir noter dans ses tablettes (le catalogue relationnel) le nombre de lignes que comportera la table T ; il s’agit là d’une information à caractère purement statistique et peu importe le nombre réel de lignes à terme, dix ou vingt millions. Cette possibilité de renseigner à l’avance le catalogue est extrêmement précieuse, car bien avant de remplir la table T pour de vrai, c'est-à-dire quand elle est encore vide, je sais quel sera le comportement du SGBD en relation avec les requêtes que je m’empresse de lui soumettre via un EXPLAIN. Better prevent than cure.
Cette réponse va-t-elle dans le sens de ce que vous attendiez ?

[chaplin]

A chaque insertion d'un enregistrement dans la table, une valeur va être insérée dans l'index, et plus l'index est grand plus il faudra de temps pour mettre à jour l'index. En bref, il y a une stratégie à adopter, j'ai la réponse, elle va dans le sens de ma question. Si l'index est un arbre, il doit rester équilibré, il faut donc le régénérer.

Par calibrer, j'entends dimensionner la base de données, version SGBDR/matériel.

[Oishiiii]

Envoyé par chaplin

A chaque insertion d'un enregistrement dans la table, une valeur va être insérée dans l'index, et plus l'index est grand plus il faudra de temps pour mettre à jour l'index.

Ce n'est pas toujours vrai.
Cela dépend de beaucoup de facteurs; il faut prendre en compte le type de l'index (cluster ou non), sa clé, le taux de fragmentation de celui-ci, et il me manque peut-être d'autres éléments.

Quelques exemples :

Si la clé de l'index est un entier auto-incrémenté, peu importe la taille de l'index, le SGBD (celui sur lequel je travail est SQL Server) ira insérer l'enregistrement dans la dernière page de l'index, ou au pire il en créera une nouvelle.

Si vous avez spécifié un seuil de remplissage (FILLFACTOR), peu importe la taille de l'index; s'il reste de la place dans la page d'index visée, le SGBD n'aura qu'une simple insertion d'enregistrement à effectuer (pas de split).

[chaplin]

Il y a bien un index qui est utilisé pour le champ utilisateur, il faut constamment le mettre à jour.

Finalement, il faut comprendre que chaque SGBDR possède un ensemble de stratégies pour tout simplement gérer l'index, notamment en faisant un paramétrage sur DB2.

[fsmrel]

Bonsoir,

Envoyé par chaplin

A chaque insertion d'un enregistrement dans la table, une valeur va être insérée dans l'index, et plus l'index est grand plus il faudra de temps pour mettre à jour l'index.

Pour faire en sorte que l’ajout de clés ne déstabilise pas l’index, le DBA (à nouveau dans le contexte DB2 for z/OS) aura pris la précaution de définir un pourcentage de place disponible (PCTFREE) dans chaque page index, réservée pour les INSERT, donc gelée lors de l’exécution des utilitaires tels que LOAD (chargement des tables) et REORG (réorganisation des tables et/ou index). De même, il aura pris soin de définir un nombre de pages entièrement gelées entre deux pages (FREEPAGE), toujours pour améliorer la performance des lectures/écritures (SELECT, INSERT, UPDATE, DELETE), de telle sorte que ces pages fassent l’objet d’une seule I/O, essentiellement lors des traitements de masse.
Si la clé à insérer dans l’index n’y existe pas déjà, DB2 insérera effectivement dans une page feuille (le plus bas niveau de l’index) cette valeur + un pointeur (appelé RID) vers la page physique hébergeant la table. Si cette clé existe déjà dans l’index, DB2 se contentera d’ajouter le pointeur. Supposons qu’il s’agisse d’insérer (INSERT) une nouvelle clé Ki. DB2 cherchera à insérer cette clé physiquement entre les clés Ki-1 et Ki+1. S’il y a de la place (PCTFREE jouant son rôle), l’opération est terminée, car les niveaux supérieurs de l’index ne sont pas impactés. S’il n’y a plus assez de place, DB2 mettra à profit le FREEPAGE et consommera une page feuille supplémentaire, physiquement contiguë à la page cible, avec mise à jour du niveau supérieur de l’index. Si le FREEPAGE a été consommé, DB2 utilisera une page ailleurs.
Mais dans tout cela, que l’index contienne un million ou cent millions de clés, la durée de l'ajout d'une clé dans l'index est la même à un pouième près, ce sont les paramètres PCTFREE et FREEPAGE qui sont déterminants ès matière. Quant à savoir si une réorganisation de l’index s’impose, je demande un rafraichissement des statistiques du catalogue relationnel pour l’index en cause et décide en conséquence.

Envoyé par chaplin

Si l'index un arbre, il doit resté équilibré, il faut donc le régénérer.

Par construction, un index DB2 est toujours équilibré. Sa régénération, ou plutôt sa réorganisation n’est à effectuer que si les statistiques que j’ai demandées me montrent qu’il est bon de le faire (opération rapide, une réorganisation dure disons entre 2 et 5 minutes pour un index de dix millions de clés). Il va sans dire que dans le contexte de la production informatique, les opérations de rafraichissement des statistiques et de réorganisation des index sont automatisées.

Envoyé par chaplin

Il y a bien un index qui est utilisé pour le champ utilisateur, il faut constamment le mettre à jour. Finalement, il faut comprendre que chaque SGBDR possède un ensemble de stratégies pour tout simplement gérer l'index, notamment en faisant un paramétrage sur DB2.

J’espère que, sans être exhaustif, ce qui précède vous apporte quelques précisions.

[chaplin]

Oui je comprends mieux, merci fsmrel.

Envoyé par fsmrel

Bonsoir,

Par construction, un index DB2 est toujours équilibré. Sa régénération, ou plutôt sa réorganisation n’est à effectuer que si les statistiques que j’ai demandées me montrent qu’il est bon de le faire

La réorganisation consiste à faire quoi au juste, si l'index est équilibré ?