Trop grande table SQL -> quelle solution ?

Par Canonier, il y a 9 ans

Base de données MySQL

Bonjour à tous,

Aujourd'hui je travail avec une entreprise dans la réalisation d'un outil de gestion de données statistiques. Jusque là tout va bien.
L'entreprise m'impose l'utilisation d'un serveur Apache avec Php/MySQL.

Cette entreprise reçoit des données de différents capteurs (donnée de type : un temps, une valeur).
Pour stoquer les données j'ai simplement fait : un capteur -> une table (deux colonnes par table : time, value)

Soucis: certaines tables ont jusqu'à 10 000 000 de lignes..

Du coup quand je dois trouver des lignes d'apres un temps donné, le resultat met plus de 30s et donc php bloque le processus.
Rallonger le temps de processus php n'est pas une solution pour moi (il se peut qu'un jour un capteur ait encore plus de donnée).

Du coup je me suis dit qu'il serait peut etre pas mal de limiter le nombre de ligne par table (donc en scindant un capteur en plusieurs table).
Mais du coup cb de lignes ? 50 000 ?

Voilà donc où j'en suis et j'aimerais connaitre votre avis / vos solutions / vos experiences / etc..
Je vous remercie!

7 réponses

Huggy, il y a 9 ans

En effet 30s c'est beaucoup
qu'essayes-tu de faire comme requête ?
normalement un SELECT d'une valeur ne doit pas dépasser 1 ms quelle que soit la taille de la table, à condition qu'il y ait un index.
Tu peux faire un EXPLAIN de ta requête pour voir les index utilisés et les index possibles.
Tu peux aussi créer un index qui couvre tous les champs (time +value) ,l'intérêt est que MySQL n'aura même pas à lire la table puisque la valeur sera déjà dans l'index.
Si tu fais des agrégats (GROUP BY ...) tu dois surement charger beaucoup de données en ram, il faut surement augmenter la taille des buffers, ça peut monter à plusieurs Go, la config dépend du type d'engine utilisé.
Si vraiment la taille est pénalisante, tu peux partitionner ta table en fonction de la date (par mois / trimestre /année)

En espérant t'être utile.

Canonier, il y a 9 ans

Merci pour ta réponse ;)
Je peux pas faire quoi que ce soit avant demain (j'ia pas accès aux sources hors horaire de bureau ^^)

En revanche je peux te répondre sur un point ou en effet j'ai mélangé deux idées (je vais éditer mon post) :

  • Un select par 'time' met trop de temps
  • Un select par 'time' compris entre deux valeurs met toujours plus de 30s ..

Voici donc un exemple de requête (parfois la limite est inférieure à 1000 mais jamais supérieure) :

SELECT * FROM table WHERE time >= timeMin AND time <= timeMax LIMIT 1000;

En ce qui concerne les indexes je t'avoue pas comprendre à 100% comment MySQL les gères .. pourrais tu m'éclairer d'avantage sur ce point ?

Je te remercie ;)

Ps : les deux champs sont de type "double"

Huggy, il y a 9 ans

Vu que tu cherches à encadrer des valeurs, Mysql va restreindre en 2 fois (inf puis sup) et travailler le résultat intermédiaire en mémoire, il faut regarder la taille des buffers pour que ça tienne en mémoire
dans le my.ini (my.cnf sous linux)

innodb_buffer_pool_size = 512M

Sinon Il y a moyen d'utiliser les index spaciaux, c'est fait pour cerner une zone géographique par la latitude/longitude
en trichant un peu on définit un rectangle dont la latitude min serait timeMin et la latitude max serait de timeMax
pour la largeur du rectangle on s'en fiche, on met 1
j'utilise ça pour pour retrouver les blocs d'ip (retrouver le pays d'une ip)

Huggy, il y a 9 ans

un article qui explique le détournement des index spaciaux ici

Canonier, il y a 9 ans

Merci pour tes conseils et lectures!

Dejà voici l'EXPLAIN d'une des tables :

$req = "EXPLAIN EXTENDED SELECT * FROM data_9 WHERE time > -50 AND time <= 500 ORDER BY time ASC LIMIT 1000"; result -> [ { "id": "1", "select_type": "SIMPLE", "table": "data_9", "partitions": null, "type": "ALL", "possible_keys": null, "key": null, "key_len": null, "ref": null, "rows": "652875", "filtered": "11.11", "Extra": "Using where; Using filesort" } ]

Je connaissais pas du tout les indexes spatiaux, mais ca m'a l'air assez lourd pour ce que j'ai non ? Je n'ai jamais besoin de chercher une ligne par le champ "value" ce sera toujours pas le champ "time". Du coup je n'ai besoin d'indexes que sur time, n'est ce pas ?

Huggy, il y a 9 ans

As-tu un index sur time ?
car d'après EXPLAIN il n'y a pas d'index utilisé et MySQL doit parcourir toutes les lignes (pas top)
commence par ça, ensuite augmente la taille des buffers et sinon fait des essais avec des index spaciaux (j'ai pas testé pour ton cas)
le lien que j'ai indiqué montre des améliorations de 1 à 100

pour les index couvrant, pas sûr que ça apporte un gain dans ton cas
tu peux toujours essayer de créer un index time + value puis regarder avec explain s'il est utilisé exclusivement (à tester)

Canonier, il y a 9 ans

Ok alors j'ai fait des tests :

1- Ajouter la clef 'time' à la table (~2 452 664 lignes) :

[ { "id": "1", "select_type": "SIMPLE", "table": "data_8", "partitions": null, "type": "range", "possible_keys": "time", "key": "time", "key_len": "8", "ref": null, "rows": "1226332", "filtered": "100.00", "Extra": "Using index condition" } ]

La requete plante au bout de 30s.

2- Y ajouter la clef 'value' :
Meme résultat quand je met aussi le champ 'value' en index. J'aurais cru que le champ "possible-keys" au moins aurait changé.. mais non :/

Je vais essayer de mettre en place les indexes spatiaux. On verra bien :)

Si je dois couper les tables, combien de lignes par table préconises-tu au maximum ?

Merci toujours pour ton aide :)