Discussion :

[eracius]

Bonjour,

L'application que je développe actuellement permet la visualisation de consommation d'énergie. La base de donnée MySQL est donc essentiellement utilisée pour gérer une table d'historisation des index des compteurs d'énergie.

La relève se fait toute les 10 minutes, donc toutes les 10 minutes, la table reçoit n lignes, n étant le nombre de compteurs du client.

La table d'historisation a le modèle suivant :

Code :

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

1
2
3
4
5
6
7
8
9
 
CREATE TABLE `history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idmeter` int(11) NOT NULL,
  `date` bigint(30) NOT NULL,
  `value` bigint(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `date` (`date`)
)

C'est à dire l'identifiant du compteur, la date de la mesure, la valeur de l'index. La date est un bigint car je préfère la gérer en millisecondes, je trouve ça plus pratique (peut être est-ce une erreur ?).

C'est table n'est pas énorme car pour 1,5 millions d'entrée elle fait un peu plus de 100Mo.

Néanmoins, les temps d'accès ne sont pas terribles, jusqu'à 10 secondes lorsque l'utilisateur sélectionne la visualisation des consommations sur toute une année jour par jour pour 1 compteur.

A noter que pour réaliser la vue, je vais chercher chaque donnée séparément, soit un select par entrée dans la table d'historisation.

Pour une année par jour, cela donne 365+1 select

Est-ce normal d'avoir ce genre de temps de réponse ?

Ai-je tout faux dans mes choix de structure, d'utilisation de ma table ?

Y-a-t-il des stratégies bien particulière pour gérer ce genre d'application ?

Pour infos, la machine local sur laquelle j'obtiens ces résultats a un double CPU 1.8GHz et 2Go de Ram. J'aimerais faire tourner cette appli sur un kimsufi L si possible.

Merci d'avance pour vos conseils.

[Jester]

1 - Il y a un raison particulière de faire 365 accès à la BD pour une seule requête?

2 - BIGINT comparé à INT joue peut-être mais négligeable. Par contre stocker les millisecondes quand l'unité de temps est les 10 minutes ...

3 - Est-on sur que tout est en mémoire, il faut que le cache associé au moteur des bases (innodb ou myisam ou autre) permettre que les données soient complètement en mémoire (disons 700Mo de RAM pour le cache).

PS : 4 - il manque une clé (idmeter,date) je dirais.

[SQLpro]

Sans index vos temps de réponse seront à chier !

Il vous faut un index de type CLUSTER sur l'uato incrément ou à défaut, la date.
Il vous faut aussi un index sur le compteur, construit de la sorte (si possible)
(idmeter, date) INCLUDE (value).

Pour une année par jour, cela donne 365+1 select

Ca c'est idiot, il vous faut une seule requête qui fait tout !

Pour infos, la machine local sur laquelle j'obtiens ces résultats a un double CPU 1.8GHz et 2Go de Ram. J'aimerais faire tourner cette appli sur un kimsufi L si possible.

Une base de donées à besoin de BEAUCOUP de RAM. 1 Go c'est ridicule. il vous faut au moins deux. Après tout va dépendre de la volumétrie de la fenêtre de données.

Lisez les articles que j'ai écrit à ce sujet :
1) indexation : http://sqlpro.developpez.com/cours/quoi-indexer/
2) optimisation : http://sqlpro.developpez.com/optimisation/

A +


A +

[Jester]

Pour compléter SQLPro, Mysql n'a pas ces capacitées que je sache. Par contre innodb stocke les données avec l'index de la clé primaire, donc choisissez la bien, ça devrait augmenter les performances.

Quoique si tout est en mémoire, cela joue moins je pense.

[eracius]

Une clef primaire (id,date) serait plus efficace que ma clef actuelle sur id avec un index sur date ?

Concernant la requête su-citée, elle me permet de récupérer les consommations selon un pas défini.

Par exemple, pour la consommation sur une année par jour, je fais un SELECT pour récupérer l'index sur le 1er janvier à 00h puis un SELECT sur le lendemain à 00h pour faire la différence entre les deux etc jusqu'au 1er janvier de l'année suivante à 00h

Vous me conseilleriez donc de ne faire qu'une seule requête ?

Dans ce cas, dois-je récupérer toutes les mesures entre deux dates (c'est à dire toutes les 10 minutes) puis trier programmatiquement celles dont j'ai besoin ?

Ou bien définir une requête qui pourrait me fournir directement les entrées nécessaires ? (dans ce cas, je ne vois pas comment faire)

Merci encore pour votre aide.

[SQLpro]

Ou bien définir une requête qui pourrait me fournir directement les entrées nécessaires ? (dans ce cas, je ne vois pas comment faire)

Oui !

Donnez nous un exemple sous forme SQL (INSERT INTO...)

A +

[eracius]

Vous souhaitez un exemple d'entrée comme ceci ?

Code :

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

1
2
 
INSERT INTO `history` VALUES (18124,1,1136070000037,2455418);

Ou bien vous voulez que je vous passe un fichier contenant le dump de ma table actuelle ?

Je ne suis pas sur de comprendre ce que vous voulez comme exemple.

Merci pour votre aide.

[Jester]

Vous faites de l'OLAP, rajoutez déjà une colone diff qui sera le différentiel par rapport à l'insertion précédente pour le compteur. Ainsi, vous pourrez faire de l'agrégation avec sum et group by day(date) par exemple. Donc 1 requête.

Si ça ne va pas assez vite, vous pouvez faire une table de cache de l'agrégation sur les jours (compteur, day, value).

Notez que dans ces deux cas, vous introduisez de la redondance dans la base de données pour gagner en performance. Selon le cas c'est envisageable ou déconseillé.

[SQLpro]

Un jeu de données suffisamment représentatif sous forme INSERT SQL et le DDL de votre table.

A +

[eracius]

Voici un exemple de table history, le jeu de données fictif représente un peu plus de 3 mois de données soit 40 milles lignes environ.

exemple.sql

Dites moi si ça vous suffit.

Je suis également entrain de creuser le modèle OLAP et j'y trouve des choses intéressantes, je continue dans cette voie.

Merci pour votre aide.

[eracius]

En me renseignant sur le modèle OLAP, j'ai repensé ma table history pour réussir à obtenir mes résultats en une seule requête.

J'ai éclaté ma date en sous-champs comme suit :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
CREATE TABLE IF NOT EXISTS `history_new` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idnode` int(11) NOT NULL,
  `value` bigint(30) NOT NULL,
  `year` int(11) NOT NULL,
  `month` int(11) NOT NULL,
  `day` int(11) NOT NULL,
  `hour` int(11) NOT NULL,
  `minute` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Les résultats sont sans communes mesures, je divise le temps de ma requête la plus longue, année en cours par jour, par 10. Je peux même me permettre de faire année en cours par heure maintenant.

J'ai aussi fait des tests avec cet index

Code :

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

1
2
 
  UNIQUE KEY `date` (`year`,`month`,`day`,`hour`,`minute`,`idnode`)

Mais les résultats ne s'améliorent pas vraiment.

Qu'en pensez-vous ? Voyez vous un autre modèle d'index plus pertinent.

[Jester]

A chaque requête vous lisez probablement toute la table, il manque toujours l'index sur idnode qui peut se révéler fort utile dans le cas d'un nombre de compteur élevé. Moi j'en airait fait un clé primaire, surtout en innodb.

Vous avez aussi sensiblement agrandit la table, peut-être une dimension date serait idoine.

[eracius]

A chaque requête vous lisez probablement toute la table, il manque toujours l'index sur idnode qui peut se révéler fort utile dans le cas d'un nombre de compteur élevé. Moi j'en airait fait un clé primaire, surtout en innodb.

J'y ai réfléchi mais quelque soit le nombre de compteurs (qui varie généralement de 5 à 50 par client), chaque idnode sera répété énormément de fois dans la table. Si j'ai bien compris le fonctionnement des index, ils sont plutôt à privilégier sur les colonnes dont les valeurs qui varient le plus justement non ? au pire rajouter idnode à l'index actuel.

[Jester]

idnode n'est pas une clé en soit, mais un premier niveau.

Le problème c'est que la vous lisez toute la table, on peut se dire que ne lire que les lignes avec le bon idnode est suffisant pour traiter voter requête.