Fetch time trop long

Version imprimable

Salut tout le monde,

Je rencontre des soucis de lenteurs sur une base de données MySQL. A savoir que j'ai une table :

Code:

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
CREATE TABLE `machinedata` (
  `dat_id` int(11) NOT NULL AUTO_INCREMENT,
  `dat_macid` int(11) NOT NULL,
  `dat_datetime` datetime DEFAULT NULL,
  `dat_field1` varchar(40) COLLATE latin1_general_cs DEFAULT NULL,
  `dat_field2` varchar(40) COLLATE latin1_general_cs DEFAULT NULL,
  `dat_field3` date DEFAULT NULL,
  `dat_field4` datetime DEFAULT NULL,
  `dat_field5` datetime DEFAULT NULL,
  `dat_field6` varchar(15) COLLATE latin1_general_cs DEFAULT NULL,
  `dat_field7` decimal(12,2) DEFAULT NULL,
  `dat_field8` int(11) DEFAULT NULL,
  `dat_field9` int(11) DEFAULT NULL,
  `dat_field10` int(11) DEFAULT NULL,
  `dat_field11` varchar(10) COLLATE latin1_general_cs DEFAULT NULL,
  `dat_field12` varchar(17) COLLATE latin1_general_cs DEFAULT NULL,
  `dat_field13` int(11) DEFAULT NULL,
  `dat_field14` varchar(20) COLLATE latin1_general_cs DEFAULT NULL,
  `dat_field15` datetime DEFAULT NULL,
  `dat_field16` datetime DEFAULT NULL,
  `dat_field17` int(11) DEFAULT NULL,
  `dat_field18` int(11) DEFAULT NULL,
  `dat_field19` datetime DEFAULT NULL,
  `dat_field20` int(11) DEFAULT NULL,
  `dat_field21` int(11) DEFAULT NULL,
  `dat_field22` int(11) DEFAULT NULL,
  `dat_field23` int(11) DEFAULT NULL,
  `dat_field24` varchar(15) COLLATE latin1_general_cs DEFAULT NULL,
  `dat_field25` varchar(15) COLLATE latin1_general_cs DEFAULT NULL,
  `dat_field26` int(11) DEFAULT NULL,
  `dat_field27` varchar(35) COLLATE latin1_general_cs DEFAULT NULL,
  `dat_field28` int(11) DEFAULT NULL,
  `dat_field29` int(11) DEFAULT NULL,
  `dat_field30` int(11) DEFAULT NULL,
  `dat_field31` int(11) DEFAULT NULL,
  `dat_field32` varchar(15) COLLATE latin1_general_cs DEFAULT NULL,
  `dat_field33` varchar(35) COLLATE latin1_general_cs DEFAULT NULL,
  `dat_field34` int(11) DEFAULT NULL,
  `dat_field35` int(11) DEFAULT NULL,
  `dat_field36` int(11) DEFAULT NULL,
  `dat_field37` int(11) DEFAULT NULL,
  `dat_field38` int(11) DEFAULT NULL,
  `dat_field39` varchar(15) COLLATE latin1_general_cs DEFAULT NULL,
  `dat_field40` int(11) DEFAULT NULL,
  `dat_field41` int(11) DEFAULT NULL,
  `dat_field42` int(11) DEFAULT NULL,
  `dat_field43` int(11) DEFAULT NULL,
  `dat_field44` int(11) DEFAULT NULL,
  `dat_field45` int(11) DEFAULT NULL,
  `dat_field46` int(11) DEFAULT NULL,
  `dat_field47` int(11) DEFAULT NULL,
  `dat_field48` int(11) DEFAULT NULL,
  `dat_field49` decimal(12,2) DEFAULT NULL,
  `dat_field50` int(11) DEFAULT NULL,
  `dat_field51` int(11) DEFAULT NULL,
  `dat_field52` int(11) DEFAULT NULL,
  `dat_field53` int(11) DEFAULT NULL,
  `dat_field54` int(11) DEFAULT NULL,
  `dat_field55` int(11) DEFAULT NULL,
  `dat_field56` int(11) DEFAULT NULL,
  `dat_field57` int(11) DEFAULT NULL,
  `dat_field58` int(11) DEFAULT NULL,
  `dat_field59` int(11) DEFAULT NULL,
  `dat_field60` int(11) DEFAULT NULL,
  `dat_field61` int(11) DEFAULT NULL,
  `dat_field62` int(11) DEFAULT NULL,
  `dat_field63` int(11) DEFAULT NULL,
  `dat_field64` int(11) DEFAULT NULL,
  `dat_field65` int(11) DEFAULT NULL,
  `dat_field66` int(11) DEFAULT NULL,
  `dat_field67` int(11) DEFAULT NULL,
  `dat_field68` int(11) DEFAULT NULL,
  `dat_field69` int(11) DEFAULT NULL,
  `dat_field70` int(11) DEFAULT NULL,
  `dat_field71` int(11) DEFAULT NULL,
  `dat_field72` int(11) DEFAULT NULL,
  `dat_field73` int(11) DEFAULT NULL,
  `dat_field74` int(11) DEFAULT NULL,
  `dat_field75` int(11) DEFAULT NULL,
  `dat_field76` int(11) DEFAULT NULL,
  `dat_field77` int(11) DEFAULT NULL,
  `dat_field78` int(11) DEFAULT NULL,
  `dat_field79` int(11) DEFAULT NULL,
  `dat_field80` int(11) DEFAULT NULL,
  `dat_field81` int(11) DEFAULT NULL,
  `dat_field82` int(11) DEFAULT NULL,
  `dat_field83` int(11) DEFAULT NULL,
  `dat_field84` int(11) DEFAULT NULL,
  `dat_field85` int(11) DEFAULT NULL,
  `dat_field86` int(11) DEFAULT NULL,
  `dat_field87` int(11) DEFAULT NULL,
  `dat_field88` int(11) DEFAULT NULL,
  `dat_field89` int(11) DEFAULT NULL,
  `dat_field90` int(11) DEFAULT NULL,
  `dat_field91` int(11) DEFAULT NULL,
  `dat_field92` int(11) DEFAULT NULL,
  `dat_field93` int(11) DEFAULT NULL,
  `dat_field94` int(11) DEFAULT NULL,
  `dat_field95` int(11) DEFAULT NULL,
  `dat_field96` int(11) DEFAULT NULL,
  `dat_field97` varchar(10) COLLATE latin1_general_cs DEFAULT NULL,
  `dat_field98` varchar(10) COLLATE latin1_general_cs DEFAULT NULL,
  `dat_field99` varchar(10) COLLATE latin1_general_cs DEFAULT NULL,
  `dat_field100` varchar(10) COLLATE latin1_general_cs DEFAULT NULL,
  `dat_field101` varchar(10) COLLATE latin1_general_cs DEFAULT NULL,
  `dat_field102` varchar(10) COLLATE latin1_general_cs DEFAULT NULL,
  `dat_field103` varchar(10) COLLATE latin1_general_cs DEFAULT NULL,
  `dat_field104` int(11) DEFAULT NULL,
  `dat_field105` int(11) DEFAULT NULL,
  `dat_field106` int(11) DEFAULT NULL,
  `dat_field107` int(11) DEFAULT NULL,
  `dat_field108` int(11) DEFAULT NULL,
  `dat_field109` int(11) DEFAULT NULL,
  `dat_field110` varchar(155) COLLATE latin1_general_cs DEFAULT NULL,
  `dat_field111` datetime DEFAULT NULL,
  `dat_field112` int(11) DEFAULT NULL,
  `dat_field113` text COLLATE latin1_general_cs,
  `dat_field114` int(11) DEFAULT '0',
  PRIMARY KEY (`dat_id`),
  KEY `fk_machinesv_idx` (`dat_macid`),
  KEY `date_datetime` (`dat_datetime`),
  CONSTRAINT `fk_machinesv` FOREIGN KEY (`dat_macid`) REFERENCES `machines` (`mac_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=251882 DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;

L'objectif de cette table est le stockage d'informations machines, donc beaucoup de données par heure.

Sauf qu'aujourd'hui, lorsque je fais une requête dessus :

Code:

select * from machinedata ORDER BY dat_id;

Mon temps de réponse est affreusement long (en local) :

Citation:

Duration : 0,031 secondes
Fetch : 13,969 secondes

J'ai épluché les différents articles sur le net et sur le forum, augmenté table_open_cache à 2000, query_cache_size à 1 000 000 et même innodb_buffer_pool_size à 2G, sans succès...

A part IIS, il n'y a que MySQL sur mon serveur, et il dispose de 6 Go de mémoire...

Est-ce que quelqu'un aurait des pistes à exploiter ? Merci pour votre retours. ;)

17/09/2018, 14h15
tatayo

Bonjour,
Si la table contient "beaucoup de données par heure", est-il vraiment nécessaire de tout afficher ?

Tatayo.
17/09/2018, 14h27
windmastr26

Hélas le client est susceptible de faire des rapports et graphiques sur ces données...

Aujourd'hui la table dispose de 238 439 enregistrements, et lorsque je lance la requête suivante :

Code:

select * from machinedata WHERE dat_datetime >= '20180910' ORDER BY dat_datetime DESC;

J'obtiens 104 378 résultats en 9,957 secondes...
17/09/2018, 14h41
tatayo

Ok, mais je doute que le rapport contiennent 104378 lignes de 117 colonnes !
Il faut prévoir des requêtes qui renvoient les résultats agrégés, qui correspondent aux graphiques et autres rapport qui seront affichés.

Tatayo.

P.S. un select * en production ? Vraiment ?
17/09/2018, 15h25
windmastr26

Je viens de redémarrer le serveur sql et les résultats semblent meilleurs... Bizarre il me semblait l'avoir déjà fait...

Merci pour le retour.
18/09/2018, 11h20
Artemus24

Salut windmastr26.

Code:

select * from machinedata WHERE dat_datetime >= '20180910' ORDER BY dat_datetime DESC;

Trois remarques :

1) utilisez le type "datetime" pour vos dates et non un type char ou varchar.

2) utilisez ceci : "where dat_datetime between '2018-09-10 and '2018-12-31'".

3) utilisez un index sur la colonne "dat_datetime"

@+
18/09/2018, 13h36
escartefigue
Bonjour,

Plusieurs remarques

En terme de modélisation
Tous les attributs sont ils pertinents pour chaque occurrence de "machine_data", ça semble peu probable puisque vous avez mentionné "default null" systématiquement.
A priori nous sommes ici en présence d'une table dite "obèse" qui comporte bien trop d'attributs pour l'identifiant.
Si certains attributs ne sont pertinents que pour certaines machines, il faut revoir la modélisation en respectant les formes normales et ne pas tout mettre dans une seule table.

Le nom des attributs laisse aussi à désirer, tant qu'à revoir la modélisation, profitez en pour utiliser des noms adaptés.

Le typage des données doit correspondre au contenu. Stocker des dates dans un colonne d'un type non date est une hérésie, à corriger d'urgence.

Une fois la modélisation corrigée, il conviendra de définir les index sur vos critères de filtrage et/ou jointure, en fonction des volumes à traiter, du facteur de filtrage etc...

En terme de requête
Une requête SELECT * est à proscrire :
- le résultat est instable, chaque modification de la table, et même dans certains cas d'index, peut provoquer des résultats différents
- la requête n'est pas optimisée, elle récupère des données dont vous n'avez que faire et nécessite une consultation du catalogue pour connaitre les noms des colonnes
18/09/2018, 14h04
windmastr26

J'ai rebaptisé les différents champs en field1, 2, 3, etc. pour des questions de confidentialité mais il s'agit bien de champs avec un nom explicite dans la vraie base.

Si j'ai typé des champs avec le type "datetime" c'est que j'y stocke à la fois une date et une heure. D'ailleurs chaque champ contient sa valeur à chaque retour, je ne peux donc pas éclater cette table en plusieurs tables selon le type de machines... :(

Pour finir, je répond à plusieurs remarques concernant le "select *", rassurez-vous : je ne prend bien que les champs dont j'ai besoin puisque lors de la création d'un rapport dans l'applicatif, le SELECT est composé selon les champs sélectionnés par l'utilisateur ;)

Merci pour vos retours.
19/09/2018, 01h41
SQLpro

Citation:

Envoyé par windmastr26

Salut tout le monde,

Je rencontre des soucis de lenteurs sur une base de données MySQL. A savoir que j'ai une table : ….

Votre table est un exemple majeur de la stupidité qu'il y a à vouloir faire un tableur dans une base de données et a ne pas modéliser correctement. Avec une modélisation respectueuses des règles de l'art, vos résultats sortirait en quelques milliseconde !

Lisez l'article que j'ai écrit à ce sujet :
https://blog.developpez.com/sqlpro/p...mances_petites

A +
19/09/2018, 06h10
windmastr26

Mais c'est toujours un plaisir de poster ici et d'avoir affaire a certains développeurs imbu d'eux même !

Sachez que vous n'êtes pas né avec une cuillère en argent dans la bouche. Si vous semblez connaître les SGBD (sans maîtriser mon dossier), le savoir vivre vous est totalement étranger.
19/09/2018, 08h49
SQLpro

Citation:

Envoyé par windmastr26

Mais c'est toujours un plaisir de poster ici et d'avoir affaire a certains développeurs imbu d'eux même !

Sachez que vous n'êtes pas né avec une cuillère en argent dans la bouche. Si vous semblez connaître les SGBD (sans maîtriser mon dossier), le savoir vivre vous est totalement étranger.

Le savoir vivre consiste aussi à commencer par étudier avant d'agir !
Si vous aviez étudié la conception des bases de données relationnelles vous n'auriez pas fait cette erreur...

Le fait de foncer tête baissée sans commencer par comprendre ce que l'on fait est le meilleur gage de concevoir un système, inepte, inapte et inadapté...

Je n'ai pas l'habitude de mâcher mes mots et je ne suis pas adepte du consensus mou ni du politiquement correct. Mais ce que je déplore, c'est que malgré toutes les ressources que nous mettons en ligne pour aider les autres, certaines personnes, dont vous semblez faire partie, s'en foutent éperdument et viennent ensuite réclamer de l'aide... Et malheureusement la qualité des interventions dans les forums baisse considérablement depuis quelques années du fait de cette "prolétarisation" de la société informatique, comme en parlait Christian Fauré dans cet article :
http://www.christian-faure.net/2009/...informatiques/

Le fait de vous avoir interpeler violemment a été fait de manière consciente afin de marquer votre esprit... Cela présente l'avantage de vous faire rentrer dans le crâne les concepts évoqués... Peut être y resteront-ils et vous éviterez donc à l'avenir de telles erreurs !

A +
19/09/2018, 09h02
windmastr26

Citation:

Envoyé par SQLpro

Le fait de vous avoir interpeler violemment a été fait de manière consciente afin de marquer votre esprit... Cela présente l'avantage de vous faire rentrer dans le crâne les concepts évoqués... Peut être y resteront-ils et vous éviterez donc à l'avenir de telles erreurs !

Je suis sur un forum informatique et non pas sur psychologies.com, alors merci de garder pour vous votre psychologie de bazar.

Vous ne marquez rien du tout. Le seul effet que vous produisez c'est de ne pas donner envie de revenir sur ce forum avec des attitudes comme la votre. C'est contre productif et représentatif d'une suffisance que je n'ai que rarement rencontré. Autant je comprends qu'il n'est pas plaisant de répondre à un message de "débutant", mais si vous n'êtes pas capable de le faire avec la courtoisie qui va avec : ne le faites pas du tout. D'autant que votre intervention n'apporte rien.

De quel droit vous permettez vous de juger un projet dans son ensemble sur un simple post ? Vous n'en connaissez ni les tenants ni les aboutissants.

Alors avant de juger et de prodiguer des leçons de morale... N'oubliez pas d'où vous venez. Vous avez certainement débuté comme tout le monde.
19/09/2018, 14h24
escartefigue

Citation:

Envoyé par windmastr26

Si j'ai typé des champs avec le type "datetime" c'est que j'y stocke à la fois une date et une heure. D'ailleurs chaque champ contient sa valeur à chaque retour, je ne peux donc pas éclater cette table en plusieurs tables selon le type de machines... :(

En ce cas pas de souci, mais dans vos requêtes le format devrait être 'AAAA-MM-JJ' et non pas 'AAAAMMJJ' qui suppose une colonne de type CHAR.
Et pour rappel, les champs sont les données d'un formulaire de saisie, dans une table d'un SGBD relationnel, on trouve des colonnes ;)
19/09/2018, 14h33
windmastr26

Citation:

Envoyé par escartefigue

En ce cas pas de souci, mais dans vos requêtes le format devrait être 'AAAA-MM-JJ' et non pas 'AAAAMMJJ' qui suppose une colonne de type CHAR.

C'est noté pour les tirets :) Instinctivement quand je compare une date/heure je mets bien "aaaa-mm-jj hh:mm:ss", mais bizarrement quand je compare juste des dates j'ai un automatisme idiot qui se met en place et j'en oublie les tirets...

Citation:

Envoyé par escartefigue

Et pour rappel, les champs sont les données d'un formulaire de saisie, dans une table d'un SGBD relationnel, on trouve des colonnes ;)

:oops: