Discussion :

[ceoner]

Bonjour,

J'ai l'habitude pour mes projets web d'utiliser mysql de manière plutôt traditionnelle mais ici les besoins sont différents :

L'objectif est de créer une plateforme qui permettrai à chacun (après acceptation) de créer son propre espace sur la plateforme.

Je me pose une question pour la réalisation d'un tel outil :
- Dois je utiliser une BDD par utilisateur (espace) ou une seule BDD suffira ?

Voici les contraintes :
- La plateforme permettra dans un premier temps de créer environ 500 espaces puis environ 1500.
- Certaines tables pourront atteindre plus de 1500 lignes pour chaque espace.


Merci de m'orienter pour prendre la décision adéquate.

[CinePhil]

Ca ressemble à ce que je connais dans le logiciel sur lequel je travaille.

Dans mon cas :
- Un utilisateur peut créer autant de "zones de dépôt" qu'il veut. Une zone de dépôt = une base de données MySQL.
- Pour les besoin de stockage temporaire de ses traitements statistiques, il y a en plus, potentiellement, une base de données par utilisateur.

Ne pas s'inquiéter pour les requêtes. Il suffit de ne pas sélectionner la base de données à la connexion mais seulement dans la requête avec une syntaxe du type :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT *
FROM nombase.nomtable
 
INSERT INTO nombase.nomtable
 
CREATE TABLE nombase.nomtable

Les avantages de ce système :
- Accès réservé au possesseur de la base de données (même si nous utilisons un système différent à l'aide de métadonnées)
- Le même nom de table peut exister dans plusieurs bases de données

[ceoner]

Merci pour cette orientation ça me conforte dans mon idée, est ce que quelqu'un d'autre peut donner son avis ?

[gregory.broissard]

si ton volume de données max d'une table c'est 1500 lignes, tu peux largement mutualiser les tables et créer un espace unique où les données de tous les utilisateurs sont dedans et tu mets une colonne avec l'id de l'utilisateur...

1500 lignes * 1500 utilisateurs = 2 millions de lignes, ca reste un volume assez faible.

Tu gagnes en terme de simplicité après à l'exploitation, une seule base à sauvegarder et à administrer.
ton modèle de données est pas beaucoup plus compliqué mais par contre dans ton code c'est super simple, puisque tu n'as meme plus besoin de gérer dans quelle base tu dois aller chercher l'info.

Comme toujours y a beaucoup d'avantages à mutualiser....

[ceoner]

OK merci pour cet autre point de vue.

Cependant j'estime dans mon cas que le backup de plusieurs bases n'est pas réellement contraignant et ce seront les possesseurs des espaces qui devront administrer leurs données depuis leur espace, il ne devrait pas y avoir besoins d'administrer les données de différents espaces simultanément.

J'aurai besoin encore de quelques informations, donc si quelqu'un à des conseils, des habitudes... n'hésitez pas.
- Est ce que 2 millions de lignes pour une table sur un serveur mysql ça ne commence pas à faire beaucoup.
- La majorité (pour ne pas dire totalité) des requêtes depuis un espace ne concerneront que les données relatives à cette espace. N'y aurait il pas un gain de performances à séparer les données dans des bases dédiés aux espaces, ainsi nous limiterons le nombre de données à parcourir ?

Lorsque les 1500 espaces seront créés, le nombre de requêtes simultanées à la bdd va devenir conséquent. (en moyenne 150 visites par espace et par jour certains espaces ne générerons que très peu de requêtes et d'autres énorméments)
- Est ce que cela vous semble viable pour un serveur mysql ?
- Est ce que l'utilisation d'une seule base ne risque pas de réduire les performances lors de recherches et sur l'utilisation des jointures ?
- Est ce que la recherche sur 2 champs (id de l'espace + id de l'enregistrement) dans le cas de l'utilisation d'une seule base ne va pas nécessiter beaucoup de ressources que la recherche sur un seul champ (id de l'enreg) dans le cas de l'utilisation d'une base par espace ?

Je m'interroge sur un autre point, la gestion des données dans une seule base ne risque t il pas de causer un plus grand nombre d'erreurs d'accès concurrentiel et de ralentir les requêtes d'ajout et de modification ?


Merci à vous pour vos conseils et retours d'expériences
Si vous voyez d'autres questions que je ne me pose pas, merci de me le signaler.

[CinePhil]

Compte tenu de ce que tu viens de dire, je pense que tu devrais rester sur l'idée de plusieurs bases en effet. Il vaut mieux faire une requête sur une table de 1500 lignes plutôt que sur une table de 2 millions de lignes.
Dans le premier cas c'est instantané, dans le second ça commence quand même à se faire sentir, même avec un serveur correct.

[gregory.broissard]

OK merci pour cet autre point de vue.

De rien. des méthodes de séparation des bases de données étaient très utilisées il y a 20 ans mais maintenant avec les progres des SGBD, des machines, la mutualisation est en vogue depuis quelques années pour permettre une réduction des couts.

- Est ce que 2 millions de lignes pour une table sur un serveur mysql ça ne commence pas à faire beaucoup.

Non clairement, 2 millions de lignes, c'est pas énorme (à condition que ton serveur MySQL soit dédié et soit pas un Pentium II avec 32Mo de RAM
Je vois au quotidien des applications avec plusieurs To de données en ligne donc non 2 millions c'est loin d'etre volumineux
Je vous assure donc que faire une requete SQL sur un champ indexé d'une table de 1500 lignes ou de 2 millions de lignes, c'est exactement la même chose.
Il va utiliser l'index B-Tree et parcourir l'arbre de la meme facon et l'arbre ne sera pas plus long à parcourir qu'on est 1500 ou 2000000 lignes (j'exagere peut etre un peu mais le principe de l'index c'est ca!). Et heureusement pour nos grosses applications de production qu'on peut aller chercher une donnée en quelques millièmes de secondes meme sur 2 millions de lignes

- La majorité (pour ne pas dire totalité) des requêtes depuis un espace ne concerneront que les données relatives à cette espace. N'y aurait il pas un gain de performances à séparer les données dans des bases dédiés aux espaces, ainsi nous limiterons le nombre de données à parcourir ?

Alors tes index devront avoir en premier colonne le filtre sur l'espace à sélectionner.
Un gain faible et non visible de l'utilisateur puisque si ton modèle de données est bien foutu, systematiquement il utilisera l'index sur l'espace en premier qui filtrera deja sur les données de l'utilisateur.
Enfin, en MYSQL 5, tu as (enfin) la possibilité de partitionner tes tables.

Lorsque les 1500 espaces seront créés, le nombre de requêtes simultanées à la bdd va devenir conséquent. (en moyenne 150 visites par espace et par jour certains espaces ne générerons que très peu de requêtes et d'autres énorméments)
- Est ce que cela vous semble viable pour un serveur mysql ?
- Est ce que l'utilisation d'une seule base ne risque pas de réduire les performances lors de recherches et sur l'utilisation des jointures ?
- Est ce que la recherche sur 2 champs (id de l'espace + id de l'enregistrement) dans le cas de l'utilisation d'une seule base ne va pas nécessiter beaucoup de ressources que la recherche sur un seul champ (id de l'enreg) dans le cas de l'utilisation d'une base par espace ?

- Oui, largement si serveur dédié et correctement configuré
- Si, mais à vous de bien concevoir l'application et de bien développer pour éviter cela (requetes SQL executant les filtres sur prédicats indexés avant d'effectuer les jointures)
- Non, ou tellement peu que ce n'est pas significatif ni à prendre en compte dans le choix

Je m'interroge sur un autre point, la gestion des données dans une seule base ne risque t il pas de causer un plus grand nombre d'erreurs d'accès concurrentiel et de ralentir les requêtes d'ajout et de modification ?

En MYISAM, tu pourrais avoir des problèmes, c'est possible mais quand meme rare. Sauf si tu fais dans ton appli, des updates massifs sur un grand nombre de lignes, ou des inserts massifs. Mais si tu fais un update ou un insert, ca devrait aller.
Dans ce cas là, il faut utiliser un moteur transactionnel comme InnoDB par exemple, qui va gérer les transactions et la concurrence d'accès et tu pourras jouer sur le niveau d'isolation de tes transactions de mise à jour et permettre par exemple le dirty read.


Les 2 solutions me semblent avoir des avantages et réalisables sans trop de difficultés au vue des chiffres annoncés (visites, volumes, etc...)
Si vous n'avez pas à gérer la création des bases lors de l'ajout d'une base , la maintenance des bases (sauvegardes, restaurations, etc...) alors la solution d'avoir plusieurs bases est peut etre la plus intéressante meme si elle semble en décalage avec ce qui se fait actuellement .

[ceoner]

Merci pour ces 2 points de vues,

Je penche plutôt pour scinder en plusieurs bases, c'est aussi une forme de mutualisation, sauf qu'au lieu de mutualiser au niveau de la base, on mutualise au niveau du serveur.

Scinder en plusieurs bases permettra de gérer les droits d'utilisateurs sur les données, ce qui est un avantage dans mon cas.


Merci encore, si d'autres personnes on un point de vue à partager je suis toujours intéressé.

[SQLpro]

C'est généralement une fort mauvais idée que de créer de multiples bases identiques sur un même serveur si une seule base peut faire l'affaire :
1) chaque base consomme des ressources propres tel que le catalogue des méta données (tables systèmes). N base c'est dont n fois ces tables ouvertes, c'est à dire bien plus de ressources que pour une seule. Prévoyez dond de la RAM et des processeurs en quantité pour votre serveur si vous voulez vous offrir l'option multibase...
2) lorsqu'il faudra optimiser des requêtes, par exemple en créant des index il faudra le faire sur chaque base... Pensez donc à prévoir l'embauche d'un DBA !
3) la sauvegarde d'une seule base même grosse, pendra moins de temps que l'ensemble des sauvegardes de toutes les bases. Mais si vous n'êtes pas pressé, alors conservez votre vision multi base.
4) Lorsqu'un base grossie, il convient de bien administrer les espaces de stockage des donnés. Cela est beaucoup moins facile lorsqu'il y a de multiples base car l'écriture des fichiers s'entrelace et une fragmentation physique irrémédiable apparaît. Bref, si vous voulez des performances lamentable à terme, faites le plus de bases possible !

J'espère que ces argument suffirons, mais si vous doutez encore, je peut en sortir une bonne dizaine de plus !

A +

[gregory.broissard]

on a la même vision des choses ! ca fait toujours plaisir de trouver un soutien

[ceoner]

Ok merci pour cet réponse, c'est intéressant.

Je rappel que dans mon cas un espace utilisateur c'est comme un site à part entière. En prenant cela en compte, est ce que vos dernières préconisations sont toujours les plus adaptés ?

Est il possible en mysql de gérer des contraintes d'intégrité sur plusieurs champs à la fois ? (ex : id d'enregistrement + id d'espace utilisateur) C'est pour le cas ou je ne gère pas l'id d'enregistrement avec une clé unique mais la clé d'enregistrement ne sera unique que pour un espace utilisateur.
Est ce que vous me déconseillez cette mise en oeuvre ?

Toujours pour mysql. Si plusieurs utilisateurs font la même action insert sur la même table en même temps, que se passe t il, est ce que pour l'un des utilisateurs l'action sera mise en attente tant que l'autre utilisateur n'a pas terminé sa transaction, ou ça se passe autrement ?
Pareil pour un update d'un enregistrement par un utilisateur alors qu'un autre utilisateur est en train de le supprimer ! Comment cela sera géré par le SGBD ?


Je suis preneur de toute ressource récente ou piste qui me permettrai de trouver la meilleur solution pour répondre à mes contraintes.


Merci

[gregory.broissard]

Je rappel que dans mon cas un espace utilisateur c'est comme un site à part entière. En prenant cela en compte, est ce que vos dernières préconisations sont toujours les plus adaptés ?

Oui, pourquoi pas?
En fait ce à quoi il faut répondre de ton côté c'est dans quelle type de société tu travailles. Si c'est une grosse boite alors toutes les contraintes évoquées par SQLPro et moi seront présentes et amplifiées. Si c'est plus une petite entreprise où c'est toi qui fait tout de A à Z alors pourquoi pas avoir plusieurs bases...

Est il possible en mysql de gérer des contraintes d'intégrité sur plusieurs champs à la fois ? (ex : id d'enregistrement + id d'espace utilisateur) C'est pour le cas ou je ne gère pas l'id d'enregistrement avec une clé unique mais la clé d'enregistrement ne sera unique que pour un espace utilisateur.
Est ce que vous me déconseillez cette mise en oeuvre ?

Oui, MYSQL comme tous les SGBDR peut gérer cela.
Non, personne ne deconseille ce type de mise en oeuvre...

Toujours pour mysql. Si plusieurs utilisateurs font la même action insert sur la même table en même temps, que se passe t il, est ce que pour l'un des utilisateurs l'action sera mise en attente tant que l'autre utilisateur n'a pas terminé sa transaction, ou ça se passe autrement ?
Pareil pour un update d'un enregistrement par un utilisateur alors qu'un autre utilisateur est en train de le supprimer ! Comment cela sera géré par le SGBD ?

Si plusieurs insert différents, pas de souci, le SGBDR le gère parfaitement et heureusement !
Pour la seconde partie, c'est les bases des SGBDR, la concurrence d'accès sur les données. Tout dépend des niveaux d'isolation de tes transactions (je ne parle donc pas de MYISAM qui ne gère pas les transactions). en consistent read, l'utilisateur qui veut updater un enregistrement déjà en cours de suppression dans une transaction n'a pas accès à cet enregistrement il est locké jusqu'au commit/rollback de la transaction.
Apres tu as différents niveaux d'isolation qui vont te permettre un certain nombre de choses.

[SQLpro]

Si plusieurs utilisateurs font la même action insert sur la même table en même temps, que se passe t il, est ce que pour l'un des utilisateurs l'action sera mise en attente tant que l'autre utilisateur n'a pas terminé sa transaction, ou ça se passe autrement ?

Une mise à jour implique forcément une transaction et donc des verrous. Aucune insertion dans une table normalement constituée (c'est à dire au moins dotée d'une clef primaire), ne peut se faire en même temps. En effet pour vérifier l'unicité il faut le faire séquentiellement. Mais cela est très rapide. Les plus grosse config aujourd'hui savent faire plus de 4 millions de transactions à la minute. Hélas pour MySQL, c'est le plus lent en matière de transaction... Mais je pense qu'avec une petite config il doit pouvoir au moins faire du 100 lignes insérées par seconde. Cela vous laisse quand même de la marge...
Bien entendue les transactions, pour les SGBDR bien constitués (mécanisme de journalisation ARIES par exemple) ce ne sont pas des écritures physique de ligne, mais des écritures logique. Tout se passe en mémoire et de temps à autre le SGBDR écrit les données. Seule la transaction elle même est d'abord écrite dans le journal, mais c'est du binaire WAL (Write Ahead Log) donc très rapide.

Il n'y a d'ailleurs aucune autre solution que de poser des verrous et d'agir séquentiellement. ALors SGBDR ou pas, mieux vaut un SGBDR pour ce faire car il est optimisé pour cela !
PS : si vous en trouvez une autre, pensez à postuler pour le Turing Award ou la médaille Fields.

Pareil pour un update d'un enregistrement par un utilisateur alors qu'un autre utilisateur est en train de le supprimer ! Comment cela sera géré par le SGBD ?

Même topo : le premier pose un verrou et le suivant attend la fin du traitement. Mais encore une fois cela se fait UNIQUEMENT en mémoire. C'est donc extrêmement rapide !

Pour en savoir plus sur le fonctionnement des SGBDR, lisez les articles que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/sqlaz/techniques/
1 transaction
8 journalisation
http://blog.developpez.com/sqlpro?ti...s_sur_ms_sql_s
en particulier commandement n°1

A +

[ceoner]

Encore très intéressant, petit à petit je vais me rapprocher d'une bonne solution.

@SQLPro : Merci pour les liens, je n'ai pas encore tout lu mais déjà les 10 commandements me permettent de me poser les bonnes questions

@gregory.broissard : Concernant la gestion d'une contrainte d'intégrité portant sur plusieurs champs (ex : id d'enregistrement + id d'espace utilisateur) en MYSql, est-ce que tu as des liens à me fournir là dessus.


Je reviendrai sans doute vers vous quand j'aurai digéré vos infos.


Merci

[SQLpro]

@gregory.broissard : Concernant la gestion d'une contrainte d'intégrité portant sur plusieurs champs (ex : id d'enregistrement + id d'espace utilisateur) en MYSql, est-ce que tu as des liens à me fournir là dessus.

http://sqlpro.developpez.com/cours/s...partie2#L7.2.4

A +