Optimisation ou problème d'index

Version imprimable

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:

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.

10/05/2006, 16h16
Maximil ian

Code:

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 ?
10/05/2006, 16h21
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é...
10/05/2006, 16h30
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 ?)

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:

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:

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:

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

10/05/2006, 16h50
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:

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
10/05/2006, 16h58
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
10/05/2006, 17h06
Maximil ian

Citation:

Envoyé par Erakis

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 ;))
10/05/2006, 17h10
Erakis

Citation:

Envoyé par Maximilian

Code:

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 :(
10/05/2006, 17h12
Erakis

Citation:

Envoyé par 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 ?)

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.
10/05/2006, 17h19
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 ?

Citation:

Envoyé par Maximilian

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:

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:

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:

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"

10/05/2006, 17h28
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é.

Citation:

Envoyé par Maximilian

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é.

Voilà un SHOW VARIABLES :

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
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
"Variable_name";"Value"
"auto_increment_increment";"1"
"auto_increment_offset";"1"
"automatic_sp_privileges";"ON"
"back_log";"50"
"basedir";"C:\Program Files\MySQL\MySQL Server 5.0\"
"bdb_cache_size";"8388600"
"bdb_home";"D:\MySQL Datafiles\"
"bdb_log_buffer_size";"131072"
"bdb_logdir";""
"bdb_max_lock";"10000"
"bdb_shared_data";"OFF"
"bdb_tmpdir";"C:\WINDOWS\TEMP\"
"binlog_cache_size";"32768"
"bulk_insert_buffer_size";"8388608"
"character_set_client";"latin1"
"character_set_connection";"latin1"
"character_set_database";"latin1"
"character_set_results";"latin1"
"character_set_server";"latin1"
"character_set_system";"utf8"
"character_sets_dir";"C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\"
"collation_connection";"latin1_swedish_ci"
"collation_database";"latin1_swedish_ci"
"collation_server";"latin1_swedish_ci"
"completion_type";"0"
"concurrent_insert";"1"
"connect_timeout";"5"
"datadir";"D:\MySQL Datafiles\"
"date_format";"%Y-%m-%d"
"datetime_format";"%Y-%m-%d %H:%i:%s"
"default_week_format";"0"
"delay_key_write";"ON"
"delayed_insert_limit";"100"
"delayed_insert_timeout";"300"
"delayed_queue_size";"1000"
"div_precision_increment";"4"
"engine_condition_pushdown";"OFF"
"expire_logs_days";"0"
"flush";"OFF"
"flush_time";"1800"
"ft_boolean_syntax";"+ -><()~*:\"\"&|"
"ft_max_word_len";"84"
"ft_min_word_len";"4"
"ft_query_expansion_limit";"20"
"ft_stopword_file";"(built-in)"
"group_concat_max_len";"1024"
"have_archive";"YES"
"have_bdb";"YES"
"have_blackhole_engine";"YES"
"have_compress";"YES"
"have_crypt";"NO"
"have_csv";"NO"
"have_example_engine";"YES"
"have_federated_engine";"YES"
"have_geometry";"YES"
"have_innodb";"YES"
"have_isam";"NO"
"have_ndbcluster";"NO"
"have_openssl";"NO"
"have_query_cache";"YES"
"have_raid";"NO"
"have_rtree_keys";"YES"
"have_symlink";"YES"
"init_connect";""
"init_file";""
"init_slave";""
"innodb_additional_mem_pool_size";"2097152"
"innodb_autoextend_increment";"8"
"innodb_buffer_pool_awe_mem_mb";"0"
"innodb_buffer_pool_size";"75497472"
"innodb_checksums";"ON"
"innodb_commit_concurrency";"0"
"innodb_concurrency_tickets";"500"
"innodb_data_file_path";"ibdata1:10M:autoextend"
"innodb_data_home_dir";"D:\MySQL Datafiles\"
"innodb_doublewrite";"ON"
"innodb_fast_shutdown";"1"
"innodb_file_io_threads";"4"
"innodb_file_per_table";"ON"
"innodb_flush_log_at_trx_commit";"1"
"innodb_flush_method";""
"innodb_force_recovery";"0"
"innodb_lock_wait_timeout";"50"
"innodb_locks_unsafe_for_binlog";"OFF"
"innodb_log_arch_dir";""
"innodb_log_archive";"OFF"
"innodb_log_buffer_size";"1048576"
"innodb_log_file_size";"37748736"
"innodb_log_files_in_group";"2"
"innodb_log_group_home_dir";".\"
"innodb_max_dirty_pages_pct";"90"
"innodb_max_purge_lag";"0"
"innodb_mirrored_log_groups";"1"
"innodb_open_files";"300"
"innodb_support_xa";"ON"
"innodb_sync_spin_loops";"20"
"innodb_table_locks";"ON"
"innodb_thread_concurrency";"8"
"innodb_thread_sleep_delay";"10000"
"interactive_timeout";"28800"
"join_buffer_size";"131072"
"key_buffer_size";"39845888"
"key_cache_age_threshold";"300"
"key_cache_block_size";"1024"
"key_cache_division_limit";"100"
"language";"C:\Program Files\MySQL\MySQL Server 5.0\share\english\"
"large_files_support";"ON"
"large_page_size";"0"
"large_pages";"OFF"
"license";"GPL"
"local_infile";"ON"
"log";"OFF"
"log_bin";"OFF"
"log_bin_trust_function_creators";"OFF"
"log_error";".\SERVER.err"
"log_slave_updates";"OFF"
"log_slow_queries";"OFF"
"log_warnings";"1"
"long_query_time";"10"
"low_priority_updates";"OFF"
"lower_case_file_system";"OFF"
"lower_case_table_names";"1"
"max_allowed_packet";"1048576"
"max_binlog_cache_size";"4294967295"
"max_binlog_size";"1073741824"
"max_connect_errors";"10"
"max_connections";"100"
"max_delayed_threads";"20"
"max_error_count";"64"
"max_heap_table_size";"16777216"
"max_insert_delayed_threads";"20"
"max_join_size";"4294967295"
"max_length_for_sort_data";"1024"
"max_relay_log_size";"0"
"max_seeks_for_key";"4294967295"
"max_sort_length";"1024"
"max_sp_recursion_depth";"0"
"max_tmp_tables";"32"
"max_user_connections";"0"
"max_write_lock_count";"4294967295"
"multi_range_count";"256"
"myisam_data_pointer_size";"6"
"myisam_max_sort_file_size";"107374182400"
"myisam_recover_options";"OFF"
"myisam_repair_threads";"1"
"myisam_sort_buffer_size";"50331648"
"myisam_stats_method";"nulls_unequal"
"named_pipe";"OFF"
"net_buffer_length";"16384"
"net_read_timeout";"30"
"net_retry_count";"10"
"net_write_timeout";"60"
"new";"OFF"
"old_passwords";"OFF"
"open_files_limit";"622"
"optimizer_prune_level";"1"
"optimizer_search_depth";"62"
"pid_file";"D:\MySQL Datafiles\SERVER.pid"
"port";"3306"
"preload_buffer_size";"32768"
"protocol_version";"10"
"query_alloc_block_size";"8192"
"query_cache_limit";"1048576"
"query_cache_min_res_unit";"4096"
"query_cache_size";"0"
"query_cache_type";"ON"
"query_cache_wlock_invalidate";"OFF"
"query_prealloc_size";"8192"
"range_alloc_block_size";"2048"
"read_buffer_size";"61440"
"read_only";"OFF"
"read_rnd_buffer_size";"258048"
"relay_log_purge";"ON"
"relay_log_space_limit";"0"
"rpl_recovery_rank";"0"
"secure_auth";"OFF"
"shared_memory";"OFF"
"shared_memory_base_name";"MYSQL"
"server_id";"0"
"skip_external_locking";"ON"
"skip_networking";"OFF"
"skip_show_database";"OFF"
"slave_compressed_protocol";"OFF"
"slave_load_tmpdir";"C:\WINDOWS\TEMP\"
"slave_net_timeout";"3600"
"slave_skip_errors";"OFF"
"slave_transaction_retries";"10"
"slow_launch_time";"2"
"sort_buffer_size";"262136"
"sql_mode";"STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
"sql_notes";"ON"
"sql_warnings";"ON"
"storage_engine";"MyISAM"
"sync_binlog";"0"
"sync_frm";"ON"
"sync_replication";"0"
"sync_replication_slave_id";"0"
"sync_replication_timeout";"10"
"system_time_zone";"Est (heure d'"
"table_cache";"256"
"table_lock_wait_timeout";"50"
"table_type";"MyISAM"
"thread_cache_size";"8"
"thread_stack";"196608"
"time_format";"%H:%i:%s"
"time_zone";"SYSTEM"
"timed_mutexes";"OFF"
"tmp_table_size";"25165824"
"tmpdir";""
"transaction_alloc_block_size";"8192"
"transaction_prealloc_size";"4096"
"tx_isolation";"REPEATABLE-READ"
"updatable_views_with_limit";"YES"
"version";"5.0.18-nt-max"
"version_bdb";"Sleepycat Software: Berkeley DB 4.1.24: (December 21, 2005)"
"version_comment";"MySQL Community Edition (GPL)"
"version_compile_machine";"ia32"
"version_compile_os";"Win32"
"wait_timeout";"28800"

Question comme ça, avez-vous déjà eu un projet similaire ?
Autant de données ?

Autre chose, j'utilise le logiciel MySql Front et par conséquent je créer mes table à l'aide du GUI. Dans la précédante déclaration de ma table j'ai remarqué ces deux lignes :

Code:

1
2
3
 
  KEY `ssv_StationID` (`ssv_StationID`),
  KEY `ssv_SensorID` (`ssv_SensorID`),

Qu'est-ce c'est ? Sont-ils nécessaires ? C'est en rapport avec les FOREIGN KEY ?

10/05/2006, 18h05
Maximil ian

"have_bdb";"YES" => si tu n'as pas de tables de type BDB tu peux le désactiver avec l'option skip_bdb

"innodb_buffer_pool_size";"75497472" => Peut être augmenté à plusieurs centaines de mégas sans problème (procède par paliers)

"query_cache_size";"0" => peut être très utile dans un environnement OLAP lorsque les requêtes exécutées sont souvent les mêmes et que les données changent peu. On peut le paramétrer à plusieurs dizaines de mégas, ça dépend de la taille des jeux de résultats qu'on veut garder en cache.
10/05/2006, 18h30
Erakis

Merci pour les conseils :)

Si je m'organise pour ne pas utiliser les transaction et que je passe sous MyIsam ? Est-ce que je pourrais gagner suffisament ?
10/05/2006, 20h50
qi130

Autre idée (sans préjugé de la façon dont MySQL travaille): L'utilisation de BETWEEN pour les dates ne serait-elle pas préférable à la double comparaison ?

De plus, pour les requètes #1 et #3, tu fais appel à une fonction (AVG) qui peut expliquer la lenteur, surtout à la lumière des volumes évoqués.

Ceci dit, je serais bien en peine de proposer une solution de rechange sans sortir l'artillerie lourde :mouarf:

Citation:

Envoyé par qi130

Autre idée (sans préjugé de la façon dont MySQL travaille): L'utilisation de BETWEEN pour les dates ne serait-elle pas préférable à la double comparaison ?

De plus, pour les requètes #1 et #3, tu fais appel à une fonction (AVG) qui peut expliquer la lenteur, surtout à la lumière des volumes évoqués.

Ceci dit, je serais bien en peine de proposer une solution de rechange sans sortir l'artillerie lourde :mouarf:

Merci, je vais jeter un coup d'oeil à BETWEEN et poser la question sur le forum du site officiel de MySQL.

Que veux-tu dire par artillerie lourde ?
Un gars qui travail avec moi dit que ce serait mieux de créer des fichiers sur disque dur selon un arborescence :

Exemple :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 Station 1 - 2005 - 01 - Fichier 1 - Fichier 2 - Fichier 3 - 02 - Fichier 1 - 2006 - 01 - Fichier 1 Station 2 - 2006 - 02 - Fichier 1
Ensuite pour afficher à l'utilisateur des graphiques ou voir des grilles de données il serait plus simple de tout charger en mémoire (à partir du fichier qui selon lui serait plus facile d'accès de cette manière) et construire/générer ce que l'on désire. Mais l'histoire dans tout cela c'est que pour un mois on peut avoir plus de 180 fichiers contenant chacun environ 4h de données soit une valeur au 5min. Et ce fichier peut contenir plusieurs capteurs...

Êtes-vous d'accord avec son principe ?

10/05/2006, 21h03
Alexandre T

Citation:

Envoyé par Erakis

Merci pour les conseils :)

Si je m'organise pour ne pas utiliser les transaction et que je passe sous MyIsam ? Est-ce que je pourrais gagner suffisament ?

En théorie MyIsam insère surtout les données plus rapidement puisqu'il n'a pas a géré les transactions, les locks, etc... . La lecture avec des index similaires doit suivre le même algorithme. Je ne pense pas que cela aide réellement.

Crée une copie de ta table en myIsam, copie les données par insert select et mesure les requêtes.

Autre idée, je suppose que tes données s'insère chronologiquement, donc que tes données sont triées selon le premier champ de la clef primaire. Il me semble (j'en suis quasi certain) qu'on peut informer la base de cela. Du coup l'index est simplifié et moins lourd...

Je suis désolé, je ne peux pas effectuer ces dernières recherches ce soir sinon je serais en retard.
10/05/2006, 21h23
qi130

Citation:

Que veux-tu dire par artillerie lourde ?

L'idée serait de maintenir à jour cette moyenne en quasi temps réel.

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 :lol:

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 :aie: ?)
10/05/2006, 23h45
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 ?
11/05/2006, 06h47
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 !!! :D

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 :aie: ). 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:cry:
11/05/2006, 13h20
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.
11/05/2006, 14h39
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.

Citation:

Envoyé par qi130

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 :lol:

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 :aie: ?)

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
11/05/2006, 15h07
qi130

Un coup d'oeil ici pour commencer...
http://dev.mysql.com/doc/refman/5.0/fr/triggers.html
11/05/2006, 15h17
qi130

8O mais c'est extrèmement rudimentaire :lol:

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

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:

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.

11/05/2006, 17h26
qi130

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

Il "suffit" de comptabiliser les mesures (nb et cumul des valeurs) puis d'enrichir le "passif" des mesures.
11/05/2006, 20h16
Erakis

Citation:

Envoyé par qi130

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

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.
12/05/2006, 09h28
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.
23/05/2006, 18h12
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.
23/05/2006, 21h35
gorgonite

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

Bonjour,

En rappel de ce que j'ai maintenant :

Code:

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;

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:

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:

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.

02/06/2006, 19h25
qi130

Citation:

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 :roll:
02/06/2006, 19h37
Erakis

Citation:

Envoyé par qi130

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

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.