Version imprimable
Bon et bien après toutes ces belles améliorations mes tables ont l'air d'être indexées... Sympa parce que je n'y comprenais pas grand chose !
Par contre la consommation de CPU par MySQL est à 30% alors que nous sommes en fin de matinée et que le pic de fréquentation sera cet après midi. Alors si le problème ne vient pas de mes tables il peut venir d'où ?
Comme je te disais un SHOW STATUS serait intéressant pour diagnostiquer le problème ;)
Et quelles sont les caractéristiques de ton serveur ?
Pour le serveur c'est un dédié tournant sous FreeBSD 4.7-RELEASE-p28 #40: Fr i386
Pour controler les tables j'utilise phpMyAdmin 2.6.4-pl2
La version de PHP est PHP Version 4.3.11
Et MySQL c'est la 4.0.24
Voici le résultat du SHOW STATUS :
mysql> SHOW STATUS;
+--------------------------------+------------+
| Variable_name | Value |
+--------------------------------+------------+
| Aborted_clients | 20 |
| Aborted_connects | 19 |
| Bytes_received | 101199626 |
| Bytes_sent | 259634402 |
| Com_admin_commands | 0 |
| Com_alter_table | 125 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 1116415 |
| Com_change_master | 0 |
| Com_check | 327 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 4 |
| Com_create_table | 0 |
| Com_delete | 393 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 2 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 3546 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 111 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 218 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 1104631 |
| Com_set_option | 2 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 15 |
| Com_show_create | 9 |
| Com_show_databases | 330 |
| Com_show_fields | 600 |
| Com_show_grants | 0 |
| Com_show_keys | 365 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 2 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 2125 |
| Com_show_variables | 10 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 7302 |
| Com_update_multi | 0 |
| Connections | 91706 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 1019 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 41087 |
| Handler_read_first | 1026 |
| Handler_read_key | 84243379 |
| Handler_read_next | 71138497 |
| Handler_read_prev | 1948446 |
| Handler_read_rnd | 58178548 |
| Handler_read_rnd_next | 1619048642 |
| Handler_rollback | 0 |
| Handler_update | 6914 |
| Handler_write | 8157205 |
| Key_blocks_used | 7793 |
| Key_read_requests | 268686672 |
| Key_reads | 44116 |
| Key_write_requests | 4196691 |
| Key_writes | 47426 |
| Max_used_connections | 16 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 50 |
| Open_files | 92 |
| Open_streams | 0 |
| Opened_tables | 1740 |
| Questions | 2328216 |
| Qcache_queries_in_cache | 0 |
| Qcache_inserts | 0 |
| Qcache_hits | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_free_memory | 0 |
| Qcache_free_blocks | 0 |
| Qcache_total_blocks | 0 |
| Rpl_status | NULL |
| Select_full_join | 146 |
| Select_full_range_join | 0 |
| Select_range | 537 |
| Select_range_check | 0 |
| Select_scan | 356368 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 15 |
| Sort_merge_passes | 0 |
| Sort_range | 2896 |
| Sort_rows | 692747531 |
| Sort_scan | 65830 |
| Ssl_accepts | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_accept_renegotiates | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_client_connects | 0 |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_sessions_reused | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_verify_depth | 0 |
| Ssl_version | |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_default_timeout | 0 |
| Table_locks_immediate | 1172886 |
| Table_locks_waited | 3092 |
| Threads_cached | 0 |
| Threads_created | 91705 |
| Threads_connected | 1 |
| Threads_running | 1 |
| Uptime | 80074 |
+--------------------------------+------------+
156 rows in set (0.04 sec)
Tu as un nombre énorme qui saute aux yeux !!
voilà ce que dit la doc MySQL :Code:Handler_read_rnd_next | 1619048642 |
Donc, il faudrait procéder à un petit tour d'horizon de tes requetes pour les optimiser un peu...Citation:
Handler_read_rnd_next
The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.
ren0>désolé mais je vois pas en quoi un index sur l'âge va accélerer les choses sachant qu'entre rechercher une valeur dans un index ou recherche une valeur dans une colonne d'une table c'est la même chose.
MySQL devra dans tous les cas balayer le champ de la table ou l'index à la recherche de la valeur !
Concerannt le Handler_read_rnd_next en clair c'est un comptage total du nombre de lectures depuis que MySQL a démarré ?
Mes requêtes SQL sont pourtant très simples, je n'ai aucune requête qui fait appel à plus de deux bases à la fois, il y a simplement beaucoup d'utilisateurs.
>> tidou <<
Si tel est le cas, les index en général n'ont aucune utilité !Citation:
rechercher une valeur dans un index ou recherche une valeur dans une colonne d'une table c'est la même chose
Pour info, je te cite un extrait de la doc, en français pour que comprenne bien, qui t'explique brievement le but d'un index :
:wink:
Pour en savoir plus : http://dev.mysql.com/doc/refman/5.0/...l-indexes.htmlCitation:
Les index sont utilisés pour trouver des lignes de résultat avec une valeur spécifique, très rapidement. Sans index, MySQL doit lire successivement toutes les lignes, et à chaque fois, faire les comparaisons nécessaires pour extraire un résultat pertinent. Plus la table est grosse, plus c'est coûteux. Si la table dispose d'un index pour les colonnes utilisées, MySQL peut alors trouver rapidement les positions des lignes dans le fichier de données, sans avoir à fouiller toute la table. Si une table à 1000 lignes, l'opération sera alors 100 fois plus rapide qu'une lecture séquentielle. Notez que si vous devez lire la presque totalité des 1000 lignes, la lecture séquentielle se révélera alors plus rapide, malgré tout.
le Handler_read_rnd_next c'est en fait le nombre de fois ou MySQL a du aller lire dans un fichier de données pour lire les données. Ce qui n'est pas normal, c'est que ça arrive souvent.
De plus, ton Select_full_join = 146 dénombre le nombre de requetes qui ne s'appuient pas sur un index.
ça conforte l'idée que tes requetes ne sont pas optimisées...
Ces indicateurs sont remis zéro quand ? Ils tiennent compte d'une moyenne ou simplement d'un comptage depuis le démarrage de MySQL ?
ren0> non les index sont utiles : par exemple indexer les champs qui sont cléfs étrangères d'une table et qui sont utilisés pour faire des jointures entres les tables : dans ce cas là les indexs accélèrent grandement la vitesse.
En faite nous avons vu l'intérêt des index sur un vrai SBGD en cours (pas MySQL donc) et le prof m'a bien dit que mettre un index sur ce type de champ (l'âge) ne sert à rien et ralentit même car l'index aura besoin d'être modifié en cas de modification du champ. Dans tous les cas il devra balayer séquentiellement : soit il balayera le champ de l'index jusqu'à trouver ce qu'il lui faut, soit il balayera séquentiellement le champ de la table ... je serai curieux de savoir les différences en terme de performance de requête avec ou sans l'index ... Bluelane tu peux nous le dire ?
Pour l'instant ce qui est clair c'est que sur cette table l'index n'a rien apporté de significatif. Je pense que mon problème de ressources ne relève pas des tables en elles mêmes mais de MySQL ou d'un problème de mémoire allouée ou encore de configuration du serveur. Pour l'instant mes administrateurs réseau ne trouvent pas de solution et sont comme moi dans l'impasse.
Ceci étant je pense que le fait d'avoir rajouté des index n'est pas une mauvaise chose, ça ne peut qu'améliorer les performances de recherches. De plus, concernant la mise à jour des index, j'ai une crontab qui s'exécute toutes les nuits et qui effectue un MyIsamCheck afin d'analyser, d'optimiser et de réparer les tables, comme ça je suis sûr que les index sont mis à jours chaque jour.
Par contre je m'interroge sur un point : pourquoi lorsque je coupe les sites du serveur, que je coupe MySQL puis que je le redémarre alors qu'il n'y a aucun visiteur sur les sites et aucun accès au bases de données la consommation du CPU par MySQL remonte soudainnement à 30% de charge ?!!!
désole de te contredire MAIS "ça ne peut qu'améliorer les performances de recherches" est faux. Je ne vais pas expliquer plus en détail mais en gros, parcourir séquentiellement un champ d'une table et parcourir séquentiellement un champ d'un index à la recherche d'un ou plusieurs valeurs est identique (selon moi mais je n'ai pas étudié l'indexation de MySQL en détail mais à ce que j'ai pu lire, MySQL ne gère que les index en "arbre" et ce type d'index nécéssite à chaque fois qu'une valeur de l'index est modifié un ré-équilibrage ET DONC mettre un index sur un champ comme l'âge ne t'accèlera pas selon moi la vitesse de tes sélections mais t'allongera à coup sur le temps que prendra les mises à jours ou insertion dans les champs que tu as indexés.
Tout ca pour dire que selon moi tu devrais : mettre des indexs sur les champs qui sont des clefs étrangères et avec lequels tu effectue des jointures entre les tables : dans ce cas là, les index sont très utiles !
Tu devrais également voir quels sont les requêtes qu'ils seraient bon de mettre en cache. Pour cela je t'invite à lire la doc ou le cours qui est sur developpez et qui explique les techniques de mises en cache sous MySQL et qui est très clair et très bien fait. Par contre ton serveur devra disposer d'une grande mémoire pour mettre en cache un maximum de donnée. L'accès aux données dans la mémoire étant bien plus rapide que l'accès sur le disque dur qu'effectue MySQL pour effectuer tes requêtes.
Indexer à tout va aura l'effet inverse que celui escompté. Il faut bien réfléchir aux champs que l'on va indexer. J'ai étudie ce principe avec Oracle et dans certains cas les indexs sont mêmes nuisibles. Par exemple indexer l'âge de la personne (il existe bien d'autres exemples).
Bonne chance pour la suite
Ils sont réinitialisés au démarrage du serveur. En l'occurence l'uptime indique un démarrage il y a une vingtaine d'heures. Tu n'avais pas encore dû mettre les index à ce moment-là, ce qui explique sans doute le nombre élevé de requêtes n'utilisant pas d'index.Citation:
Envoyé par Bluelane
Bizarre en effet. Je me doute bien qu'il est difficile de redémarrer le serveur en pleine journée, mais on dirait que tu ne l'as pas fait depuis assez longtemps donc ça pourrait être instructif de voir le status au démarrage avec les nouveaux paramétrages.Citation:
Envoyé par Bluelane
Quant à la config du serveur, j'entendais matériellement parlant (si ce n'est pas indiscret) ;)
Le problème vient peut-être de ton OS.
Il semble que ton probleme soit récurrent entre MySQL et FreeBSD.
essaye de rajouter --skip-name-resolve dans ton fichier my.cnf et tu verras au reboot si MySQL prend toujours autant de CPU.
Je continue de creuser la piste FreeBSD...
Plus d'info :
http://dev.mysql.com/doc/refman/5.0/en/freebsd.html
non, ce n'est pas spécifique à MySQL 5 !
ou bien :
http://lists.freebsd.org/pipermail/f...st/000203.html
et http://<br /> http://jeremy.zawodny...es/000697.html
Voici pour la config matériel du serveur :
Xeon 2.4 Ghz Processor
512 MB RAM
3 x 18 GB RAID
pas mal :)
Merci... Mais bon c'est pas à moi, ch'suis prestataire de service pour le développement des sites qu'il y a dessus..
Ceci dit j'ai lu l'artique que ren0 a envoyé, si je comprends bien une solution radicale pour échapper à ce BUG (car apparemment c'est un bug entre MySQL et FreeBSD) ce serait de passer à FreeBSD 5 ?
franchement perso je me demande si je vais encore utilisé longtemps MySQL. Je sais pas si tu le sais mais InnoDb vient d'être racheté par Oracle avec ce que cela implique (si tu vois ?). Postegresql dispose de quasi toutes les fonctions d'un vrai SBGD comme ORACLE et en plus il es t gratuit alors pourquoi s'en priver. MySQL 5 n'apportera même pas les transactions si InnoDb est retiré !
J'aimerais bien que tu m'expliques quelle est selon toi la différence fondamentale entre l'utilisation des index dans une clause WHERE et dans des jointures :roll:Citation:
Envoyé par tidou
Comme dit ren0 si les indexes n'apportaient aucun gain de performance dans les recherches, on n'en utiliserait jamais...
Tout à fait d'accord. Lorsqu'ils sont utilisés à bon escient les indexes sont salutaires.Citation:
Envoyé par tidou
OK, s'il te reste une marge en RAM et que tu comptes utiliser des indexes, tu peux augmenter le key_buffer_size. Lis aussi le tuto sur le query_cache pour savoir si ton contexte est favorable à son activation (peu d'accès en écriture, des jeux de résultats pas trop gros...)Citation:
Envoyé par Bluelane
Comment je rajoute ça exactement ?Citation:
Envoyé par ren0
Pour le problème alors il s'agit d'un BUG ? Et si je demande aux administrateurs réseau de passer à FreeBSD 5 ça changerait les choses ?