Discussion :

[rene92]

Bonjour à tous,

Je ne suis pas développeur de formation mais je travaille avec une base MySQL qui commence à être assez massive. Parmi les grosses tables, meter_table_fr compte plus de 10 million de lignes. Elle contient les données de production d'une trentaine de compteurs d'énergie et ce depuis quelques années déjà. Les champs 'compteur' et 'plant_id' représente schématiquement les compteurs à quelques nuances près.
J'ai deux index sur 'compteur' et 'plant_id' et partionner sur YEAR(date).

Voici à quoi ressemble la table.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
CREATE TABLE `meter_data_fr_copy` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `plant_id` int(11) NOT NULL,
  `trigram` char(5) DEFAULT NULL,
  `compteur` char(5) DEFAULT NULL,
  `tstamp` datetime NOT NULL,
  `is_erdf` tinyint(4) NOT NULL COMMENT 'set to 1 if erdf data 0 if actaris data',
  `power` smallint(6) DEFAULT NULL,
  `data_ok` int(11) DEFAULT NULL,
  `comment` varchar(100) DEFAULT NULL,
  `mail_download_log_id_ref` int(11) DEFAULT NULL,
  `avail` decimal(11,2) DEFAULT '1.00',
  PRIMARY KEY (`id`,`plant_id`,`tstamp`),
  KEY `idx_pid` (`plant_id`) USING BTREE,
  KEY `idx_compteur` (`compteur`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=35497903 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (YEAR(tstamp))
(PARTITION p2013 VALUES LESS THAN (2013) ENGINE = InnoDB,
 PARTITION p2014 VALUES LESS THAN (2014) ENGINE = InnoDB,
 PARTITION p2015 VALUES LESS THAN (2015) ENGINE = InnoDB,
 PARTITION p2016 VALUES LESS THAN (2016) ENGINE = InnoDB,
 PARTITION p2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION p2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION p2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
 PARTITION p2020 VALUES LESS THAN (2020) ENGINE = InnoDB,
 PARTITION p2021 VALUES LESS THAN (2021) ENGINE = InnoDB,
 PARTITION p2022 VALUES LESS THAN (2022) ENGINE = InnoDB,
 PARTITION p2023 VALUES LESS THAN (2023) ENGINE = InnoDB) */;

Les requêtes suivantes répondent en plus ou moins 50-60 secondes.

Code :

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

1
2
3
SELECT plant_id, SUM(power) FROM meter_data_fr
WHERE tstamp BETWEEN "2018-01-01" and "2018-02-01"
GROUP BY plant_id

ou

Code :

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

1
2
3
4
SELECT compteur, SUM(power) FROM meter_data_fr
WHERE year(tstamp) = 2018 and 
tstamp BETWEEN "2018-01-01" and "2018-02-01"
GROUP BY compteur

J'aimerais vraiment que ça tourne en 10 fois moins de temps. La seule piste que j'ai pour le moment est de scinder la table en une "archive" pour les années passées et une table "courante" pour l'année en cours. C'est un peu pénible pour la maintenance.
Avez-vous une meilleur idée, des suggestions, des conseils pour optimiser cette table et/ou les requêtes?

Merci d'avance pour vos réponses éclairées

[tatayo]

Bonjour,
Je vois que les requêtes portent toutes sur la colonne tstamp, mais je ne vois aucun index sur celle-ci.
Comme elle arrive en troisième position dans la clé primaire, et que les deux autres colonnes ne sont pas filtrées dans les requêtes, le moteur ne peut pas non plus utiliser la clé primaire.

Je pense donc que tu devrais commencer par ajouter un index sur la colonne tstamp, pour vor ce que ça donne.

Tatayo.

P.S. Pense aux balises Code pour les requêtes, ton message sera plus visible.

[rene92]

Merci Tatayo pour ta réponse,

Après ajout d'un index sur tstamp, le temps d’exécution n'est que très légèrement réduit (-10% environ).

Finalement, j'ai voulu faire un test sans partition (mais avec indexes), et c'est juste 10x fois plus rapide. J'ai aussi testé sans indexes (mais avec partitions) et c'est aussi 10x plus rapide. Surprenant! Sans partitions et sans indexes, ça reste deux fois plus rapide.

Je n'explique pas se comportement.

Merci

[tbc92]

Une hypothèse, parmi d'autres.

Quand on crée une table et qu'on insère des lignes, les unes après les autres, au fil du temps (environnement de production, j'imagine que c'est ton cas), les écritures se font ici et là, les indexes se mettent à jour comme ils peuvent, les indexes deviennent complètement déséquilibrés, et les performances sont très moyennes. Il faut de temps en temps faire un Refresh des indexes pour retrouver des performances correctes ; ça doit même être vrai sur une table sans index.
J'utilise très très peu MySQL, mais sur d'autres environnements, j'ai vu des temps de traitements divisés par 10 juste par un refresh des indexes.

Ici, tu as recréé une nouvelle table, from scratch. Ca peut suffire à expliquer l'amélioration des performances.

[aieeeuuuuu]

Bonjour,

La remarque de Tatayo concernant la colonne tstamp est pertinente.
Plutôt que de créer un index dessus, on pourrait aussi modifier la clef primaire pour mettre cette colonne en premier, mais attention aux effets de bords sur d'autres requête qui profiterait éventuellement de l'ordre actuel.

Dans ce cas, on pourra effectivement créer un nouvel index. Pour les requêtes présentées, il serait sans doute utile d'ajouter les colonnes plant_id et power à l'index afin que celui-ci soit couvrant.

attention aussi, je ne pense pas que MySQL soit capable d'utiliser un index pour ce genre de prédicat year(tstamp) = 2018

[SQLpro]

Votre partitionnement est totalement inepte… D'ailleurs la méthode de partitionnement calquée sur Oracle en moins bien est inepte aussi.

1) partitionner sur une colonne calculée oblige à recalculer sans cesse, Y COMPRIS POUR LES REQUËTES SELECT….

2) selon ce partitionnement, la même ligne pourrait se retrouver dans plusieurs partitions !
Exemple : tstamp = 1/1/2000 peut se trouver dans les partition P2013 à p2023...

Enfin, MySQmerde est très loin de valoir ce que fait SQL Server ou Oracle n la matière. Je dirais même que le partitionnement c'est juste un argument marketing, mais en matière de performance c'est à chier !

A +

[escartefigue]

Pourquoi avoir créé un PK sur 3 colonnes alors que la première d'entre elles est un ID attribué par le SGBD ?
Une PK sur la colonne ID seule + un index unique ou pas (selon vos règles de gestion) sur la colonne time_stamp seule serait préférable

Rappelons si nécessaire qu'un timestamp n'est pas unique par construction, même si les risques de doublons sont minimes.
Du coup, si le timestamp seul n'est pas unique, créez éventuellement un index sur les colonnes time_stamp + plant_id dans cet ordre, dans l'ordre inverse, ou les deux en fonctions des requêtes

[Artemus24]

Salut à tous.

Il faut de temps en temps faire un Refresh des indexes pour retrouver des performances correctes

Votre remarque est pertinente. Mais la réorganisation ne se fait pas sur les index, mais sur la totalité de la table.
A savoir, la trier sur la colonne tstamp. Ce qui revient à dire, que la primary key est fausse ou mal adapté à ce que vous désirez faire.

Il faut d'abord raisonner sur la requête la plus critique en terme de performance, ce qui donne :

Code :

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

1
2
3
4
5
SELECT    plant_id,
          SUM(power)
    FROM  meter_data_fr
   WHERE  tstamp BETWEEN "2018-01-01" and "2018-02-01"
GROUP BY  plant_id

Je constate aussi que la seconde requête se base aussi sur la colonne "tstamp".
Essayez de modifier votre primary key ainsi :
[code]PRIMARY KEY (`tstamp`,`plant_id`,`id`),[code]
C'est exactement l'inverse de ce que vous avez indiqué dans votre table "meter_data_fr".

Oui, mais voilà, la seconde requête sera dégradé coté performance. En fait, elle l'était déjà :

Code :

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

1
2
3
4
5
6
SELECT    compteur,
          SUM(power)
    FROM  meter_data_fr
   WHERE  year(tstamp) = 2018
     AND  tstamp BETWEEN "2018-01-01" and "2018-02-01"
GROUP BY  compteur

La condition "year(tstamp) = 2018" ne sert à rien. Pourquoi ?
Parce que nous retrouvons la condition sur l'année, dans l'intervalle "2018-01-01" jusqu'à "2018-02-01".
Ce qui donne :

Code :

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

1
2
3
4
5
SELECT    compteur,
          SUM(power)
    FROM  meter_data_fr
   WHERE  tstamp BETWEEN "2018-01-01" and "2018-02-01"
GROUP BY  compteur

On peut alors créer un autre index non unique :

Code :

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

index (`tstamp`,`compteur`)

Ici, tu as recréé une nouvelle table, from scratch. Ca peut suffire à expliquer l'amélioration des performances.

Bien sûr que oui.

attention aussi, je ne pense pas que MySQL soit capable d'utiliser un index pour ce genre de prédicat year(tstamp) = 2018

Non, l'index ne sera pas utilisé si vous mettez une fonction sur la colonne "tstamp".
Une possible solution est de créer une colonne uniquement avec l'année. Mais est-ce vraiment utile ?

Une PK sur la colonne ID seule + un index unique ou pas (selon vos règles de gestion) sur la colonne time_stamp seule serait préférable

Je pense qu'il faut changer la Primary key pour réorganiser la façon dont les lignes vont s'insérer dans la table.
Comme je le suppose, la colonne "tstamp" doit être la date+heure d'insertion et donc sera toujours croissante.

Mais mettre la Primary key seulement sur la colonne "tstamp" n'est pas suffisante pour obtenir une bonne performance sur la première requête.

@+

[hotcryx]

Enfin, MySQmerde est très loin de valoir ce que fait SQL Server ou Oracle n la matière. Je dirais même que le partitionnement c'est juste un argument marketing, mais en matière de performance c'est à chier !

A +

LOL et faux.
Je trouve Mysql bien meilleur.