Discussion :

[Erakis]

qu130 : Merci pour ton conseil, je vais toutefois garder cette solution pour la fin

Ré-essayons autre chose, si je décide de changer un des champs de ma clé primaire ssv_DateEntry pour un simple champs TimeStamp cela pourrait réduire la taille de la clé primaire ? Et ainsi accélérer le processus ? Si oui, alors dois-je procéder et quel est le type exacte de la colonne pour le nouveau champs ?

[gregory.broissard]

Je ne pense pas que tu gagneras énormément en changeant des types de colonnes.

Tu n'es pas arrivé aux limites de Mysql (du moins j'espere ) et encore moins aux limites d'un SGBD je te rassure, on connait des tables beaucoup plus grosses avec des milliards de lignes.

Par contre, avec 4m de lignes qui vont grossir, il est temps de songer au partitionnement de ta table.
En plus, tu nous parles de moyenne au mois et la à la station, ta clé de partitionnement est quasiment trouvée !!!!
Là tu gagneras énormément. Rien qu'en partitionannt à la station, tu passes de 4 millions de lignes à 20 partitions de 200000 ! Grosses améliorations en approche !!!

2eme chose, tu fais des moyennes, peut etre pourrais tu utiliser des trucs de datawarehousing comme les vues matérialisées qui vont te permettre d'aggréger des données en fonction de ta table (remarque : aucune idée si cela existe en Mysql ). Et apres tu fais juste un select tout bete sur ta requete.

Bon courage.

Remarque, si Mysql ne répond pas à ca, peut etre songer à prendre un vrai SGBD un peu plus robuste

[Maximil ian]

Le partitionnement de tables n'est disponible que dans la version 5.1 qui est encore en bêta (release candidate prévue pour dans quelques mois tout au plus). Les vues matérialisées n'existent pas sous MySQL.

Pour moi c'est le GROUP BY et pas forcément le AVG() qui ralentit la première requête car une table temporaire est créée. La n° 2 et la n° 3 doivent normalement être plus performantes.

[Erakis]

Lorsque l'utilisateur se rend dans l'interface Web permettant d'afficher la courbe graphique d'une station pour un mois précis, je dois parcourir la table à la recherche des dates MIN et MAX. Ainsi je peux par la suite proposer l'interval des dates disponibles à l'utilisateur. Mais, je me rend compte que juste faire cette opération c'est terriblement long.

Je n'ai jamais travaillé avec les trigger mais la solution de qi130 semble intéressant pour me faire sauver du temps. Et par la suite j'attendrai la prochaine version de MySQL pour enfin partionner ma table.

garder quelque part le nb de mesures et le cumul des valeurs (organisés par AAAA-MM/station/capteur), et mettre à jour ces valeurs lors de l'arrivée de nouvelles données via un trigger par exemple.
Ainsi, tu aurais immédiatement dispo ce qui est utile pour calculer cette moyenne: cumul/nb_de_mesure
=> 1 seule ligne à lire

Le gros du boulot étant fait au fil de l'eau de manière "indolore"

(à la réflexion, c'est pas si lourd que ça, non ?)

Idéalement lorsque le système reçoit des nouvelles données pour une station le trigger pourrait se déclancher et ainsi mettre à jour différent champs qui conservent par exemple (Min, Max, Moyenne, Écart-Type) pour chaque station (pour un jour, pour un mois et pour l'année et pour toute les données)

Cependant quelqu'un aurait un exemple ou un petit coup de main à me donner pour créer mon trigger ?

Merci

[qi130]

Un coup d'oeil ici pour commencer...
http://dev.mysql.com/doc/refman/5.0/fr/triggers.html

[qi130]

mais c'est extrèmement rudimentaire

Peux-tu décrire la manière avec laquelle les données arrivent et sont insérées en base ?

[Erakis]

Les données arrive sous forme de fichiers. Une fichier par station au 4h.
J'ouvre un fichier et je lis ligne par ligne afin de construire la requête d'insertion qui donne ceci :

Code :

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

1
2
3
4
5
INSERT IGNORE StationsSensorsValues (ssv_DateEntry, ssv_StationID, ssv_SensorID, ssv_Value) 
VALUES (x, x, x, x), (x, x, x, x), (x, x, x, x), (x, x, x, x), .... 
ON DUPLICATE KEY UPDATE ssv_Value = VALUES(ssv_Value);

Ensuite j'exécute cette requête sur le serveur MySQL.

[qi130]

Alors, puisque tu as la main sur les données, tu n'as même pas besoin du trigger

Il "suffit" de comptabiliser les mesures (nb et cumul des valeurs) puis d'enrichir le "passif" des mesures.

[Erakis]

Alors, puisque tu as la main sur les données, tu n'as même pas besoin du trigger

Il "suffit" de comptabiliser les mesures (nb et cumul des valeurs) puis d'enrichir le "passif" des mesures.

Et bien je vais tenter cette approche et attendre impatiemment le système de partitions de la nouvelle version de MySQL.

Mais juste comme ça qu'est-ce que cela aurait eu l'air avec les triggers ? En code SQL je veux dire...

Merci beaucoup.

[qi130]

De plus, compte-tenu du mode d'alimentation des tables (assimilable à un LOAD), le trigger ne pouvait pas être mis en oeuvre.

Par contre on aurait pu l'utiliser dans un contexte où les mesures arrivent au compte-goutte (genre 1/mn).

Là où MySQL me déçoit un peu, c'est qu'un trigger ne peut pas (encore ?) appeler une PS .
J'espère que ça viendra rapidement.

[Erakis]

Re-bonjour à tous.

Ce matin j'ai ouvert MySQLAdministrator et j'ai regardé l'état de ma table :

Est-ce que c'est normal un index length de 228.5 MB pour la ta taille de la table qui fait 217.8 MB ?

Mon index est plus gros que ma table ???

Merci à tous.

[gorgonite]

pas du tout impossible... tout dépend de ce que tu as mis dans ton index.

[Erakis]

Bonjour,

En rappel de ce que j'ai maintenant :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
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_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;

[Erakis]

Re-bonjour à tous.

J'ai fais des test une bonne partie de la journée d'hier et j'ai finalement trouvé un bon compromis.

Voilà ma nouvelle table :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
CREATE TABLE `stationssensorsvalues` (
  `ssv_StationID` int(11) NOT NULL default '0',
  `ssv_Year` smallint(4) NOT NULL default '0',
  `ssv_Month` tinyint(2) NOT NULL default '0',
  `ssv_Day` tinyint(2) NOT NULL default '0',
  `ssv_Time` time NOT NULL default '00:00:00',
  `ssv_SensorID` int(11) NOT NULL default '0',
  `ssv_Value` float NOT NULL default '0',
  PRIMARY KEY  (`ssv_StationID`,`ssv_Year`,`ssv_Month`,`ssv_Day`,`ssv_Time`,`ssv_SensorID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

De cette manière j'ai obtenu 100% et + d'amélioration en rapport avec les recherches des années MIN et MAX pour une station précise. Ou encore pour les mois MIN et MAX d'une station précise et d'une année précise. J'ai testé aussi les autres requêtes servant à créer les graphiques et elles aussi ont été très bénéfique en temps de réponse comparé à l'ancien format de ma table.

Donc le problème se situait entre autre dans la lenteur extrême avec les DateTime et l'ordre de la clé (Date_Entry <-> StationID)

Cependant j'ai un nouveau problème de requête. Supposons que je veuille récupérer toutes les valeurs pour la station 4 et ce entre deux dates précise ? Disons entre le 2006-06-07 15:00:00 et 2006-06-09 2:30:00.

Je ne peux pas faire ceci :

Code :

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

1
2
3
4
5
6
7
8
 
SELECT ... 
FROM StationsSensorsValues 
WHERE ssv_StationID = 4 AND
          ssv_Year BETWEEN minDateYear AND maxDateYear AND
          ssv_Month BETWEEN minDateMonth AND maxDateMonth AND
          ssv_Day BETWEEN minDateDay AND maxDateDay AND
          ssv_Time BETWEEN minDateTime AND maxDateTime;

Puisqu'il ne doit pas prendre en considération le temps pour le jour 8.
Quelqu'un à une solution ?
Merci à tous.

[qi130]

Puisqu'il ne doit pas prendre en considération le temps pour le jour 8.

Je ne comprends pas pourquoi le 08-06-2006 est à ignorer

[Erakis]

Je ne comprends pas pourquoi le 08-06-2006 est à ignorer

Ce que je veux dire c'est que la partie TIME de la date est à ignorer le jour 8. On doit seulement prendre la partie TIME du temps en considération que le premier jour et le dernier.

Cela pourrait être différent dans le cas d'un autre requête, par exemple je pourrais demandé les valeurs entre les dates suivantes : 2006-05-01 00:00:00 et 2006-06-01 2:30:00. Donc à ce moment-ci, la partie TIME serait importante seulement pour cette journée 2006-06-06 et celle-ci 2006-05-02. Et la partie JOUR serait importante seulement le premier et le dernier mois.