Discussion :

[Erakis]

Bonjour à tous.

Je travail sur un projet qui est responsable de récupérer des valeurs de capteurs pour des stations. Je peux avoir jusqu'à 32 capteurs par station et actuellement nous disposons de 20 stations et cela augmentet toujours. Les données doivent être organisées/classées par Date et par Station.

Ce matin après un COUNT sur ma table j'ai obtenu : 4 687 271 valeurs. Et croyez moi juste faire un COUNT c'est long !

Encore pour cette table :
- Data length : 201.8 MB
- Index length : 323.1 MB

Déjà près de 5 milions et cela pour un an alors j'imagine que dans 2-3 ans ce sera énorme !

J'utilise le moteur InnoDB car dans certains cas j'ai besion d'utiliser les transactions.

En passant, j'utilise la version MySQL v5.0.

Voilà la définition de ma table :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
CREATE TABLE `StationsSensorsValues` (
  `ssv_DateEntry` datetime NOT NULL default '0000-00-00 00:00:00',
  `ssv_StationID` int(11) NOT NULL default '0',
  `ssv_SensorID` int(11) NOT NULL default '0',
  `ssv_Value` float NOT NULL default '0',
  PRIMARY KEY  (`ssv_DateEntry`,`ssv_StationID`,`ssv_SensorID`),
  KEY `ssv_DateEntry_StationID_IDX` (`ssv_DateEntry`,`ssv_StationID`),
  KEY `ssv_StationID` (`ssv_StationID`),
  KEY `ssv_SensorID` (`ssv_SensorID`),
  CONSTRAINT `stationssensorsvalues_ibfk_1` FOREIGN KEY (`ssv_StationID`) REFERENCES `stations` (`s_ID`),
  CONSTRAINT `stationssensorsvalues_ibfk_2` FOREIGN KEY (`ssv_SensorID`) REFERENCES `sensors` (`s_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Je dois souvent parcourir cette table pour sortir des moyennes pour une station et un mois précis. Ça peut prendre jusqu'à 1 à 2 minutes avant de sortir les valeurs... c'est malheureusement TROP LENT pour mon client.

Est-ce que ma table est mal définit ou simplement que j'arrive aux limites de MySQL ou voir même d'une base de données tout simplement ? Si j'ai atteins les limites alors serait-il mieux de gérer tout cela avec un arborescence de fichires sur disque dur (classé par station/année/mois) ?

Merci beaucoup pour vos précieux conseils.

[Maximil ian]

Code :

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

KEY `ssv_DateEntry_StationID_IDX` (`ssv_DateEntry`,`ssv_StationID`),

Cet index ne sert à rien car déjà couvert par la clé primaire.

Sur quel type de serveur tourne la base ?
Peut-on avoir un exemple de requête lente ?

[Erakis]

Le serveur est un Pentium 4 Hyperthread avec 1Go de RAM.
Voilà un exemple de requête qui me donne la liste de points nécessaires pour bâtir une courbe graphique des valeurs moyennes pour une station, un capteur et un mois précis :

Code :

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

1
2
3
4
5
6
7
 
SELECT DAYOFMONTH(ssv_DateEntry) AS SDate, AVG(ssv_Value) AS SValue 
FROM StationsSensorsValues
WHERE ssv_DateEntry >= '2005-01-01 00:00:00' AND 
      ssv_DateEntry < '2005-02-01 00:00:00' AND 
      ssv_StationID = 1 AND ssv_SensorID = 2
GROUP BY DAYOFYEAR(ssv_DateEntry);

Maintenant, voilà une requête pour me donner une grilles des valeurs brutes pour une station et une date précise :

Code :

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

1
2
3
4
5
6
7
 
SELECT ssv_DateEntry AS DateEntry, ssv_StationID AS StationID, 
       ssv_SensorID AS SensorID, ssv_Value AS Value 
FROM StationsSensorsValues 
WHERE (ssv_StationID = 1 AND 
       ssv_DateEntry >= '2005-01-01 00:00:00' AND 
       ssv_DateEntry < '2005-02-01 00:00:00');

Pour obtenir la valeur moyenne d'un capteur pour une station et un mois précis :

Code :

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

1
2
3
4
5
6
SELECT AVG(ssv_Value) 
FROM StationsSensorsValues 
WHERE ssv_StationID = 1 AND 
      ssv_SensorID = 1 AND 
      ssv_DateEntry >= '2005-01-01 00:00:00' AND 
      ssv_DateEntry <= '2005-01-02 00:00:00';

Merci

[Maximil ian]

Le serveur est un Pentium 4 Hyperthread avec 1Go de RAM.

Dédié à MySQL ?

Peux-tu nous montrer ce que donne un EXPLAIN des requêtes ? Un SHOW VARIABLES ? (oui je sais je suis exigeant )

[Erakis]

Dédié à MySQL ?

Peux-tu nous montrer ce que donne un EXPLAIN des requêtes ? Un SHOW VARIABLES ? (oui je sais je suis exigeant )

Dédié à MySQL

Voilà un EXPLAIN pour la requête #1 :

Code :

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

1
2
3
4
 
id,"select_type","table","type","possible_keys","key","key_len","ref",rows,"Extra"
1,"SIMPLE","StationsSensorsValues","ref","PRIMARY,ssv_StationID,ssv_SensorID","ssv_StationID","4","const",1,
"Using where; Using temporary; Using filesort"

Voilà un EXPLAIN pour la requête #2 :

Code :

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

1
2
3
4
 
id,"select_type","table","type","possible_keys","key","key_len","ref",rows,"Extra"
1,"SIMPLE","StationsSensorsValues","ref","PRIMARY,ssv_StationID","ssv_StationID","4","const",1,
"Using where"

Voilà un EXPLAIN pour la requête #3 :

Code :

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

1
2
3
id,"select_type","table","type","possible_keys","key","key_len","ref",rows,"Extra"
1,"SIMPLE","StationsSensorsValues","ref","PRIMARY,ssv_StationID,ssv_SensorID","ssv_StationID","4","const",1,
"Using where"

[Maximil ian]

Je pense qu'au niveau des index on ne pourra pas faire mieux.

La marge de manoeuvre se situe effectivement dans la taille des colonnes et dans les réglages serveur. Pour ça un SHOW VARIABLES serait utile afin de voir ce qui peut être paramétré.

[Erakis]

Code :

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

KEY `ssv_DateEntry_StationID_IDX` (`ssv_DateEntry`,`ssv_StationID`),

Cet index ne sert à rien car déjà couvert par la clé primaire.

Sur quel type de serveur tourne la base ?
Peut-on avoir un exemple de requête lente ?

Voilà c'est fait, j'ai supprimé l'index inutile et lancer la commande d'optimisation de la table. Mais c'est encore trop lent

[Alexandre T]

La colonne `ssv_DateEntry` ne devrait pas faire partie à la fois d'une clé primaire et d'une clé index.

edit : Arf grillé...

[Alexandre T]

Déjà chaque enregistrement de ta table a une longueur fixe, c'est une bonne chose.

Par contre tu dis avoir pour l'instant 20 stations et que cela va augmenter. Je trouve qu'un INT(11) est un peu grand. De même pour tes capteurs. Si tu as 1000 capteurs par stations et 1000 stations, cela ne fait qu'un million. Hors INT(11) te donne 4 milliards.

Pour vérifier les valeurs maximales déjà présente ne dépassent pas mes préconisations , je te suggère cette requête :

Code :

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

1
2
3
SELECT * 
FROM `stationssensorsvalues`  
PROCEDURE ANALYSE ( )

Edit : je viens de faire le test avec 512 enregistrements et des colonnes de dimensions bien plus faible. dans les deux cas, je tombe au même résultat pour l'espace occupé. Là j'en reste sur mon séant !

Données 49 152 Octets Index 32 768 Octets Total 81 920 Octets

Edit encore grillé par qi130 cette fois... C'est la fête

[Alexandre T]

Tiens, les 2 liens que j'ai utilisé au passage


11-5 capacité des colonnes

11-1-1 Présentations des types de colonnes numériques

[qi130]

SAns te promettre une amélioration quelconque:

- as-tu besoin de 4 octets pour mémoriser ssv_StationID ?
- idem pour ssv_SensorID ?
- as-tu besoin de la partie "time" de la date ? (plusieurs mesures par jour pour 1 même station ?)

[Erakis]

SAns te promettre une amélioration quelconque:

- as-tu besoin de 4 octets pour mémoriser ssv_StationID ?
- idem pour ssv_SensorID ?
- as-tu besoin de la partie "time" de la date ? (plusieurs mesures par jour pour 1 même station ?)

Je vais tenter de réduire la taille de ces deux champs.

Effectivement j'ai besion de la partie TIME pour la date puisqu'un capteur donne une valeur à chaque ~5 min.

[Alexandre T]

Si le gain de places est intéressant, et vu le nombre de lignes que cela génèrent, essayez d'utiliser un timestamp en ce cas.

Par contre, j'aimerais vraiment avoir vos résultats. Ce sera possible de nous les noter car mon test m'inquiète ?