Bonjour à tous,

je me permets de vous contacter pour recueillir vos avis, expériences sur la gestion d'une base de données très volumineuse.

Ma base de données (Mysql) contient une table particulièrement volumineuse (+ de 4 000 000 de lignes, une quinzaine de colonnes). Or, face à ce volume de données, la moindre requête (surtout les SELECT) demande beaucoup de RAM (ce qui fait planter parfois mon serveur virtuel). J'ai pourtant optimisé mes requêtes pour ne sélectionner que les champs nécessaires.

Je ne suis pas totalement contre supprimer les données les plus anciennes de cette table, ou de passer sur un VPS, mais j'aimerais recueillir vos avis, retours d'expérience sur les bonnes pratiques à mettre en place pour faire face à ce type de problème.

Supprimer des données pour alléger une table est-ce une bonne pratique ? Peut-on envisager de faire une sauvegarde de cette table auparavant ? est-ce-que certains d'entre vous ont déjà eu à faire face à ce type de problème ?

Bonjour,

Il faudrait préciser un peu la nature des données, l'indexation existante, le type de requêtes notamment les colonnes utilisées par les jointures et les clauses WHERE, avec des LIKE des MATCH des fonctions d'agrégats, etc.

Est-ce que les données sont horodatées et est-ce que les SELECT concerne toute la table à chaque fois ou seulement une partie datée des données ?

Les solutions, à part de changer de SGBD (:P), sont multiples ...

-
Edité par Benzouye 10 octobre 2018 à 17:14:35

Je vais essayer de préciser un peu plus :

les données de toutes les tables sont (presque) toutes horodatées (date ou datetime).

La table me posant problème contient 6 clés étrangères qui sont toutes des int renvoyant vers des ID d'autres tables.

Cette table contient 22 colonnes : 10 int, 7 varchar (dont la taille max est déjà limitée), 2 text, 2 date et 1 float

J'ai déjà limité mes SELECT pour ne récupérer que les colonnes de chaque table dont j'ai besoin. Je pense que de ce côté-là on est optimisé.

D'une amnière générale, j'ai (je pense) assez bien optimisé mon code php pour limiter mes requêtes et mes jointures. Mais je n'ai jamais eu à traiter avec une base aussi importante et qui me cause des problèmes de RAM.

A titre d'exemple, un simple COUNT sur cette table uniquement (sans jointures) et avec le WHERE ne contenant que des requêtes sur des colonnes de type INT et DATE demande plusieurs longues secondes.

-
Edité par Cby 10 octobre 2018 à 17:29:20

A lire, ca devrait t'intérésser : https://blog.developpez.com/sqlpro/p10070/langage-sql-norme/base_de_donnees_et_performances_petites 

Bref, la clef c'est la normalisation. Il y a peut être un problème avec texts et les varchars. Es-tu certains qu'il n'y a jamais de doublons ? Toutes ces valeurs sont toujours renseignée (jamais de NULL) ?

Il peut être très intéressant de les sortir de cette table, et de créer encore une jointure supplémentaire (jointure de type one to one). Tout simplement car cela permettra de supprimer du texte pour le remplacer par un entier de 4 octets ! Les performances seront forcément meilleur lors de la lecture.

Ainsi le moteur pourra trier / filtrer rapidement avec des entiers / date, même sur de gros volumes, puis faire les jointures pour lire les données "lourdes" (les textes et varchar) seulement sur les données éligibles au résultat du SELECT.

Après l'étape de la normalisation, il faut voir quels sont les index posés. Pour optimiser ça, il faudra analyser le type de requêtes effectuées. Mais c'est dans un second temps.

A titre personnel, j'ai pu en suivant cette manière de faire, travailler avec mysql jusqu'à 400 millions de lignes dans ma table principale (un champ texte, que des entier en clef étrangère, et un timestamp) avant de devoir passer à postgres ( qui gére maintenant ~ 3 milliards de lignes).