Optimisation ou problème d'index

[Erakis]

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.

[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 ?

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

[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 ?)

[Erakis]

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

[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

[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

[Maximilian]

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 )

[Erakis]

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

[Erakis]

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.

[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 ?

[Erakis]

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"

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

[Erakis]

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 ?

[Maximilian]

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

[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 ?

[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

[Erakis]

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

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 ?

[Alexandre T]

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.

[qi130]

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

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