Discussion :

[Synoyx]

Hello,

Je suis arrivé sur un projet l'année dernière en J2EE qui se base () sur une BDD MariaDB 10.1.26 sous Debian 9. Cette base est répliquée sur une autre machine via le service intégré Master/Slave.
A l'époque, la BDD comptait environ 45 tables pour un volume de données total de 6Go.
En moins d'un an, on est passé , 56 tables pour 24Go de données, dont une table de plus de 73M d'entrées et de 12Go à elle seule (et sa table de liaison 5.6Go), et environ 200M d'entrées au total.

Je suppose que vous voyez où je veux en venir, ça commence à devenir tendu. Tout manipulation sur la base est lourde, très lourde ... J'ai voulu ajouter 2 colonnes à la grosse table en question, ça m'a pris 2 jours car ça a bien planté (et le fait de se prendre des timeout de partout n'aide pas).

J'ai donc quelques questions que j'espère vous pourrez m'aider à répondre :

• Est-ce que je peux continuer sur MariaDB sachant que le volume va augmenter de façons exponentielles ? J'ai lu qu'en théorie on limite les tables à 2Go, j'ai déjà bien dépassé ce palier ...
• Est ce que passer la BDD sur un SSD va me permettre de résoudre le problème ? (à court termes je me doute, mais d'ici 2 ans ?) sachant que je n'ai que 32Go de ram sur le serveur, qui héberge aussi une VM et le serveur web, donc pas de possibilité de tout laisser en RAM
• Est-ce qu'il y a un moyen "d'archiver" les tables de façons automatique ? J'ai un champs en datetime dans la table principale, et généralement on ne travaille qu'avec les données des 5/6 derniers mois, mais on peut avoir besoin des données des 5/6 dernières années. Est-ce possible de faire une table "currentWork" et une table archivée, sans impact ou presque dans le code ?

[SQLpro]

Ni MariaDB ni MySQL ne sont fait pour gérer de grosses volumétries.

pour gérer de grosse volumétries, il faut un SGBDR doté des techniques suivantes ;
1) gestion du partitionnement (embryonnaire dans mAriaDB et MySQL)
2) multithreading des requêtes (une même requête est traitée en parallèle)
3) gestion des volumes de données (dans les gros SGBDR - oracle, DB2, SQL Server...-, la lecture et l'écriture des données est faite directement par le SGBDR pour des questions d'efficacité et non pas confié à l'OS)
4) compression des données (index et tables)
5) parallélisme des opérations de maintenance (sauvegarde, défragmentation des index, recalcul des statistiques, vérification d'intégrité du st ockage...)
6) découpage du stockage de la base en plusieurs "storage" dont certains (archive) peuvent être en "read only" ce qui évite de poser des verrous de lecture et accélère les sauvegardes.
7) optimiseur avancé (l'optimiseur de MariaDB ou MySQL en est à des données lumière de ce que font les gros SGBDR. Sur des requêtes complexes ou sur une forte volumétrie il se vautre
Quelques exemples :
https://www.periscopedata.com/blog/c...ver-and-oracle
https://blog.developpez.com/sqlpro/p...alles_en_sql_1

J'avais oublié :
8) pouvoir faire la majorité des opérations sans blocage : ALTER TABLE... ALTER INDEX....
9) pouvoir intervenir à chaud sur le matériel si besoin est (ajout de CPU de RAM....)

Vous avez donc le choix :
1) compenser par le matériel, mais c'est à courte vue
2) changez de SGBDR....

A +

[Artemus24]

Salut à tous.

Je suppose que vous voyez où je veux en venir, ça commence à devenir tendu.

Vu comme c'est expliqué, non pas trop.

Tout manipulation sur la base est lourde, très lourde ...

Je ne vois pas trop pourquoi.

J'ai voulu ajouter 2 colonnes à la grosse table en question, ça m'a pris 2 jours car ça a bien planté (et le fait de se prendre des timeout de partout n'aide pas).

Il fallait désactiver les contrôles.

Est-ce que je peux continuer sur MariaDB sachant que le volume va augmenter de façons exponentielles ?

Ce n'est pas un problème de SGBDR mais votre façon de gérer vos données au sein de ce SGBDR.

J'ai lu qu'en théorie on limite les tables à 2Go, j'ai déjà bien dépassé ce palier ...

Vous avez lu ça où ?
--> https://dev.mysql.com/doc/refman/8.0...ize-limit.html

Voici ce que l'on peut obtenir :

Code :

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

1
2
3
4
5
6
7
8
9
+------------------+-------------------------+
| InnoDB Page Size | Maximum Tablespace Size |
+------------------+-------------------------+
|        4KB       |           16TB          |
|        8KB       |           32TB          |
|       16KB       |           64TB          |
|       32KB       |          128TB          |
|       64KB       |          256TB          |
+------------------+-------------------------+

Est ce que passer la BDD sur un SSD va me permettre de résoudre le problème ?

Un problème de rapidité, peut-être. Mais un disque SSD est fragile en terme d'écriture. Je ne le recommade pas.

Est-ce qu'il y a un moyen "d'archiver" les tables de façons automatique ?

C'est en effet une solution pour alléger la volumétrie, mais il existe d'autres solutions.

Vous devez partitionner vos tables.
Vous pouvez aussi compresser vos tables afin de gagner en volumétrie.

Je suppose que vous faites surtout des lectures à 99%.
Envisagez de passer du moteur InnoDB au moteur MyIsam.

Il serait intéressant de savoir ce que vous faites dans votre base de données.
Je suppose que changer de SGBDR n'est pas ce que vous recherchez, n'est-ce pas ?

@+

[Synoyx]

Je ne vois pas trop pourquoi.

Les manipulations prennent beaucoup de temps. Et vu que la base est sur un serveur distant, que je ne peux pas configurer à ma guise pour augmenter les timeout, si je n'optimise pas la requête se fait stopper avant la fin.

Il fallait désactiver les contrôles.

Pour l'ajout des colonnes j'avais désactivé le foreign key check et l'auto commit

Vous avez lu ça où ?

Dans la FAQ de ce forum

Code :

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

1
2
3
4
5
6
7
8
9
+------------------+-------------------------+
| InnoDB Page Size | Maximum Tablespace Size |
+------------------+-------------------------+
|        4KB       |           16TB          |
|        8KB       |           32TB          |
|       16KB       |           64TB          |
|       32KB       |          128TB          |
|       64KB       |          256TB          |
+------------------+-------------------------+

Ce tableau est très intéressant, merci. Je doute par contre qu'une exploitation fluide soit possible sur de tels volume, vu ce que je rencontre déjà ...

Vous devez partitionner vos tables.
Vous pouvez aussi compresser vos tables afin de gagner en volumétrie.

Je suppose que vous faites surtout des lectures à 99%.
Envisagez de passer du moteur InnoDB au moteur MyIsam.

Il serait intéressant de savoir ce que vous faites dans votre base de données.
Je suppose que changer de SGBDR n'est pas ce que vous recherchez, n'est-ce pas ?

Si possible je ne souhaite pas changer de SGBDR, du moins pas maintenant. Je suis tout seul sur le développement du projet et tant que je n'ai pas de renfort je ne pourrai pas prendre le temps de faire ça. J'ai par contre déjà prévenu qu'un changement va s'imposer dans le futur et qu'il faudra prévoir le temps et l'argent à cette fin.

Le ratio lecture / écriture dépend des tables. Sur celle qui me pose problème c'est plutôt 80% écriture et 20% lecture.

Pour expliquer un peu plus le fonctionnement, on récupère chaque jour des données extérieures que l'on transforme selon nos besoins et que l'on stocke. Nous avons en plus des données "produites" par nous au même format qui s'ajoutent à cette liste. Pour la production de ces données, nous utilisons des mesures (la grosse table de 70M de lignes) que nous n'utilisons presque uniquement pour la génération des autres données (mais que les users consultent régulièrement tout de même).
La base évolue assez rapidement car le projet est en développement, j'ai donc régulièrement des modifications à faire sur la structure de la base, et il faut donc que ces opérations me prennent le moins de temps possible afin de ne pas interrompre le serveur opérationnel trop longtemps.
J'ai également des dumps à faire régulièrement de la base pour les transférer sur la machine de dev qui est sur un réseau séparé (donc pas de duplication en temps réel possible).

Actuellement les dumps prennent un peu moins de 2h.


Concernant le passage de InnoDB à MyIsam, ça a l'air vraiment intéressant.
Vous y voyez quel avantage ? Le stockage plus optimisé, les opérations de lectures plus rapide et pas de foreign key check c'est ça ?
Effectivement dans certaines tables ça ne poserait aucun soucis, mes plus grosses tables n'ont pour la plupart aucune clef étrangères.
Est-ce possible de laisser une partie de la base en InnoDB pour garder le système de foreign keys tout en passant les tables les plus grosses en MyIsam sans problème de stabilité ?



@SQLpro
Votre réponse me rappelle mes cours sur Oracle
Effectivement un plus gros SGBDr serait mieux mais à court terme (cette année) ne sera pas envisageable.
J'ai quand même regardé ce qu'on a à disposition, et le choix se portera surement sur SQL server, pour le prix beaucoup plus bas principalement.
J'ai vu votre super article sur la comparaison SQL Server linux / windows, j'avais peur que du fait que le portage soit réçent, il soit moins performant que sous Windows, mais apparement il n'y aura pas de soucis

[SQLpro]

Si possible je ne souhaite pas changer de SGBDR, du moins pas maintenant. Je suis tout seul sur le développement du projet et tant que je n'ai pas de renfort je ne pourrai pas prendre le temps de faire ça. J'ai par contre déjà prévenu qu'un changement va s'imposer dans le futur et qu'il faudra prévoir le temps et l'argent à cette fin.

Plus vous attendrez plus ce sera couteux et difficile. Couteux car, si vous avez beaucoup de données, mettons 100 Go, il vous faudra beaucoup de temps pour effectuer tous les essais et la migration finale. De plus tous les réglages spécifique que vous aurez fait sur MySQL seront du temps perdu... Il existe une version gratuite de SQL Server de nom Express mais limitée à des bases de 10 Go. Il existe une version hébergée de SQL Server (version Web) très peu cher en location... Pour chez vous (faire des tests et du dev) il existe la version gratuite Developper équivalente à la version Enterprise....

Le ratio lecture / écriture dépend des tables. Sur celle qui me pose problème c'est plutôt 80% écriture et 20% lecture.

My SQL est très mal taillé pour les mises à jour qui sont épouvantablement plus lente que dans n'importe quel autre SGBDR... Certains mise à jour étant d'ailleurs impossible à faire dans MySQL... sans parlé des calculs faux et des résultats erronés que MySQL prodigue...

... La base évolue assez rapidement car le projet est en développement, j'ai donc régulièrement des modifications à faire sur la structure de la base, et il faut donc que ces opérations me prennent le moins de temps possible afin de ne pas interrompre le serveur opérationnel trop longtemps.

MySQL étant bloquant pour toutes les opérations ALTER, votre table sera indisponible lorsque vous ajouterez des colonnes. Ce n'est pas le cas de SQL Server qui permet de faire des ALTER ... WITH ONLINE non bloquant...

J'ai également des dumps à faire régulièrement de la base pour les transférer sur la machine de dev qui est sur un réseau séparé (donc pas de duplication en temps réel possible).

Pareil, sur de grosses volumétrie SQL Server permet de faire des sauvegardes compressées et en parallèle ce qui divise par un facteur 4 à 8 le temps de la sauvegarde (qui est non bloquant dans SQL Server contrairement à MySQL) et diminu d'un facteur 2 à 4 le temps de la restauration...

Actuellement les dumps prennent un peu moins de 2h.


Concernant le passage de InnoDB à MyIsam, ça a l'air vraiment intéressant.

Si vous voulez pourrir vos données par l'absence de contraintes, alors oui, allez-y !

Vous y voyez quel avantage ? Le stockage plus optimisé, les opérations de lectures plus rapide et pas de foreign key check c'est ça ?
Effectivement dans certaines tables ça ne poserait aucun soucis, mes plus grosses tables n'ont pour la plupart aucune clef étrangères.

Donc aucun gain avec MyISAM !

A +

[Michel.Priori]

Le ratio lecture / écriture dépend des tables. Sur celle qui me pose problème c'est plutôt 80% écriture et 20% lecture.

Ce qui me pose problème c'est plutôt le ratio.
Les seules opérations qui ne font que des écritures sont INSERT et TRUNCATE TABLE, et inversement, la seule qui ne fait que des lectures est SELECT.
Les opérations UPDATE et DELETE faisant autant de l'un que de l'autre.

Vu que la taille est croissante et le ratio est ce qu'il est, j'en conclu que le contenu du type LOG.
Or une des principales problématiques des LOG est la période de rétention. Période qui donnent lieu à des "purges".

Dans ce cas la fonctionnalité de partitionnement est très appréciable.
La gestion du "point chaud" sera un autre problème.

Ce sera aussi l'occasion de revoir l'intérêt de la réplication.
Avant de partir sur les solutions AlwaysOn je vous engage à regarder avec attention le "log shipping" https://docs.microsoft.com/fr-fr/sql...ql-server-2017

Le changement d'éditeur étant planifié, à voir s'il faut d'emblée mettre en place les fonctionnalités espérées ou le faire en plusieurs temps.

Déjà la mise au point :
* de la migration des données (concordance des types, mise au point des scripts ETL, ...)
* des procédures et autre code
* des clients
vont vous occuper un peu

Planifiez bien votre projet car une migration plantée et c'est tout le projet qui est en danger.
N'hésitez pas à envisager de sous traiter, de déléguer, d'embaucher, ... bref, de mettre les moyens nécessaires.

[Artemus24]

Salut à tous.

Sur celle qui me pose problème c'est plutôt 80% écriture et 20% lecture.

Comme vous avez beaucoup d'écritures, le mieux est de rester dans le moteur InnoDB. Pourquoi ?
A cause des problèmes d'intégrités.

Par écriture, dois-je comprendre que vous modifiez vos données ?
Ou est-ce simplement du stockage à l'état brut ?

Concernant le passage de InnoDB à MyIsam, ça a l'air vraiment intéressant. Vous y voyez quel avantage ?

Gain de temps si vous faites beaucoup de lectures. Dans le cas contraire, il vaut mieux s'abstenir.

Le stockage plus optimisé, les opérations de lectures plus rapide et pas de foreign key check c'est ça ?

Cela dépend de l'usage que vous faites de vos tables. Le mieux est de faire des tests !

Actuellement les dumps prennent un peu moins de 2h.

Je dois comprendre que l'export se fait sur la totalité de vos données.
Est-il utile de le faire sur la totalité ?
Ne serait-il pas plus judicieux d'extraire que les nouvelles lignes ?
Ainsi vous pourriez gagner un peu de temps.

Je rejoins SQLPRO sur la nécessite de dimensionner, dès le départ, vos besoins.
C'est une erreur classique de croire qu'un petit SGBDR comme MariaDB fera l'affaire.
Puis la volumétrie vient à exploser et la nécessité de changer de SGBDR va se faire sentir.
Vous n'avez pas trop le choix car vous devrez basculer vers "Micosoft SQL Server".

@+