mysql crash

hanlin · 12 Mai 2010

Bonjour,
Cela devient vra&iment genant pour mes sites.J'ai des crash réguliers de mysql (deux fois par jours). Voici ce que je pux voir dans mes logs mysql :

Code:

100510 1:04:20 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.44' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Gentoo Linux mysql-5.0.44-r2
100510 14:11:55 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_77a5_37.MYI'; try to repair it
100510 14:11:56 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_77a5_59.MYI'; try to repair it
100510 14:11:56 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_77a5_18.MYI'; try to repair it
100510 14:11:56 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_77a5_52.MYI'; try to repair it
100510 14:11:56 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_77a5_20.MYI'; try to repair it
100510 14:11:56 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_77a5_49.MYI'; try to repair it
100510 14:11:56 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_77a5_45.MYI'; try to repair it
100510 14:11:56 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_77a5_35.MYI'; try to repair it
100510 14:11:56 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_77a5_14.MYI'; try to repair it
100510 14:11:56 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_77a5_51.MYI'; try to repair it
100510 14:11:56 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_77a5_16.MYI'; try to repair it
100510 14:11:56 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_77a5_30.MYI'; try to repair it
100510 14:11:56 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_77a5_26.MYI'; try to repair it
100510 14:11:56 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_77a5_54.MYI'; try to repair it
100510 19:57:18 [Note] /usr/sbin/mysqld: Normal shutdown

100510 19:57:22 [ERROR] /usr/sbin/mysqld: Sort aborted
100510 19:57:26 [Warning] /usr/sbin/mysqld: Forcing close of thread 73420 user: 'user456'

100510 19:57:26 [Warning] /usr/sbin/mysqld: Forcing close of thread 72935 user: 'user456'

100510 19:57:26 [Warning] /usr/sbin/mysqld: Forcing close of thread 72322 user: 'user456'

100510 19:57:26 [Warning] /usr/sbin/mysqld: Forcing close of thread 72164 user: 'user456'

100510 19:57:26 [Warning] /usr/sbin/mysqld: Forcing close of thread 72037 user: 'user456'

100510 19:57:26 [Warning] /usr/sbin/mysqld: Forcing close of thread 70531 user: 'user456'

100510 19:57:26 [Warning] /usr/sbin/mysqld: Forcing close of thread 70390 user: 'user456'

100510 19:57:26 [Warning] /usr/sbin/mysqld: Forcing close of thread 70319 user: 'user456'

100510 19:57:26 [Warning] /usr/sbin/mysqld: Forcing close of thread 70311 user: 'user456'

100510 19:57:26 [Warning] /usr/sbin/mysqld: Forcing close of thread 70301 user: 'user456'

100510 19:57:26 [Warning] /usr/sbin/mysqld: Forcing close of thread 70293 user: 'user456'

100510 19:57:26 [Warning] /usr/sbin/mysqld: Forcing close of thread 70272 user: 'user456'

100510 19:57:26 [Warning] /usr/sbin/mysqld: Forcing close of thread 70266 user: 'user456'

100510 19:57:26 [Warning] /usr/sbin/mysqld: Forcing close of thread 70257 user: 'user456'

100510 19:57:26 [Warning] /usr/sbin/mysqld: Forcing close of thread 69506 user: 'user456'

100510 19:57:26 [Warning] /usr/sbin/mysqld: Forcing close of thread 69176 user: 'user456'

100510 19:58:37 [Note] /usr/sbin/mysqld: Shutdown complete

100510 19:59:09 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.44' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Gentoo Linux mysql-5.0.44-r2
100510 19:59:16 [Note] /usr/sbin/mysqld: Normal shutdown

100510 19:59:18 [Note] /usr/sbin/mysqld: Shutdown complete

100510 19:59:35 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.44' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Gentoo Linux mysql-5.0.44-r2
100510 20:35:09 [ERROR] /usr/sbin/mysqld: Table '' is marked as crashed and should be repaired
100510 20:35:09 [ERROR] /usr/sbin/mysqld: Sort aborted
100511 22:47:09 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use
100511 22:47:09 [ERROR] Do you already have another mysqld server running on port: 3306 ?
100511 22:47:09 [ERROR] Aborting

100511 22:47:09 [Note] /usr/sbin/mysqld: Shutdown complete

100511 22:48:15 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use
100511 22:48:15 [ERROR] Do you already have another mysqld server running on port: 3306 ?
100511 22:48:15 [ERROR] Aborting

100511 22:48:15 [Note] /usr/sbin/mysqld: Shutdown complete

100511 22:48:46 [Note] /usr/sbin/mysqld: Normal shutdown

100511 22:48:52 [Warning] /usr/sbin/mysqld: Forcing close of thread 129906 user: 'tarevue'

100511 22:48:52 [Warning] /usr/sbin/mysqld: Forcing close of thread 129676 user: 'user456'

100511 22:48:52 [Warning] /usr/sbin/mysqld: Forcing close of thread 129430 user: 'user456'

100511 22:48:52 [Warning] /usr/sbin/mysqld: Forcing close of thread 129396 user: 'user456'

100511 22:48:52 [Warning] /usr/sbin/mysqld: Forcing close of thread 129375 user: 'user456'

100511 22:48:52 [Warning] /usr/sbin/mysqld: Forcing close of thread 129168 user: 'user456'

A chaque fois pour relancer le serveur je tape :

Code:

killall mysqld
ps ax | grep mysqld
rm /var/run/mysqld/mysqld.pid
/etc/init.d/mysql zap
/etc/init.d/mysql start

Ce n'est pas un solution, car cela se reproduit a chaque fois.

Pour info, voici ce que donnne la commande df -k

Code:

Sys. de fich.        1K-blocs       Occupé Disponible Capacité Monté sur
/dev/md/1             10317752   3775556   6018084  39% /
udev                   1020724       192   1020532   1% /dev
/dev/md/2            469887224 154131388 291886868  35% /home
shm                    1020724         0   1020724   0% /dev/shm

J'ai recherché sur google des solutions relatives à "[Warning] /usr/sbin/mysqld: Forcing close of thread " Il n'y en a pas de vraiment explicite
Une solution ? Je suis preneur !!
Merci encore !

tamzac · 12 Mai 2010

Salut,

quelle est ta requête ? Peut être une erreur logique :!:

il y a ici une solution qui a été donné (sur un autre forum)

Il semblerait en fait que tu manques de place sur ton disque dur ?!? Cherche donc à savoir combien d'espace libre il te reste !

jcaron · 12 Mai 2010

C''est effectivement la première chose qui m'est venue à l'esprit vu les messages, mais ton df semble indiquer que tu aurais 6 Go disponibles, ça me paraît quand même difficile à remplir avec des tables temporaires (ou alors tu as un gros problème d'optimisation de tes requêtes...). Que disent /var/log/messages et/ou dmesg?

Jacques.

hanlin · 12 Mai 2010

danns les messages log :

Code:

May 12 14:44:17 nsXXXXX collectd[4357]: rrd_update failed: apache/apache_bytes.rrd: illegal attempt to update using time 1273668202 when last update time is 1273668202 (minimum one second step)
May 12 14:44:17 nsXXXXX collectd[31761]: rrd_update failed: apache/apache_scoreboard-waiting.rrd: could not lock RRD
May 12 14:44:17 nsXXXXX collectd[4357]: rrd_update failed: apache/apache_scoreboard-starting.rrd: could not lock RRD
May 12 14:44:18 nsXXXXX collectd[4357]: rrd_update failed: apache/apache_scoreboard-reading.rrd: could not lock RRD
May 12 14:44:18 nsXXXXX collectd[4357]: rrd_update failed: apache/apache_scoreboard-sending.rrd: could not lock RRD
May 12 14:44:18 nsXXXXX collectd[4357]: rrd_update failed: apache/apache_scoreboard-keepalive.rrd: could not lock RRD
May 12 14:44:19 nsXXXXX collectd[31761]: rrd_update failed: apache/apache_scoreboard-dnslookup.rrd: could not lock RRD
May 12 14:44:19 nsXXXXX collectd[31761]: rrd_update failed: apache/apache_scoreboard-closing.rrd: could not lock RRD
May 12 14:44:20 nsXXXXX collectd[31761]: rrd_update failed: apache/apache_scoreboard-logging.rrd: could not lock RRD
May 12 14:44:20 nsXXXXX collectd[31761]: rrd_update failed: apache/apache_scoreboard-finishing.rrd: could not lock RRD
May 12 14:44:20 nsXXXXX collectd[31761]: rrd_update failed: apache/apache_scoreboard-idle_cleanup.rrd: could not lock RRD
May 12 14:44:20 nsXXXXX collectd[31761]: No sleeping because `timeval_sub_timespec' returned non-zero!
May 12 14:44:20 nsXXXXX collectd[4357]: No sleeping because `timeval_sub_timespec' returned non-zero!
May 12 14:44:20 nsXXXXX collectd[4357]: rrd_update failed: traffic-lo.rrd: could not lock RRD
May 12 14:44:20 nsXXXXX collectd[31761]: rrd_update failed: traffic-dummy0.rrd: could not lock RRD
May 12 14:44:20 nsXXXXX collectd[4357]: rrd_update failed: traffic-eth0.rrd: could not lock RRD
May 12 14:44:20 nsXXXXX collectd[31761]: rrd_update failed: traffic-tunl0.rrd: could not lock RRD
May 12 14:44:20 nsXXXXX collectd[4357]: rrd_update failed: traffic-gre0.rrd: could not lock RRD
May 12 14:44:20 nsXXXXX collectd[31761]: rrd_update failed: swap.rrd: illegal attempt to update using time 1273668260 when last update time is 1273668260 (minimum one second step)
May 12 14:44:20 nsXXXXX collectd[4357]: rrd_update failed: processes.rrd: could not lock RRD
May 12 14:44:20 nsXXXXX collectd[31761]: rrd_update failed: memory.rrd: could not lock RRD
May 12 14:44:20 nsXXXXX collectd[31761]: rrd_update failed: load.rrd: could not lock RRD
May 12 14:44:20 nsXXXXX collectd[4357]: rrd_update failed: disk-8-0.rrd: could not lock RRD
May 12 14:44:21 nsXXXXX collectd[31761]: rrd_update failed: disk-8-1.rrd: illegal attempt to update using time 1273668260 when last update time is 1273668260 (minimum one second step)
May 12 14:44:21 nsXXXXX collectd[31761]: rrd_update failed: disk-8-2.rrd: could not lock RRD
May 12 14:44:21 nsXXXXX collectd[4357]: rrd_update failed: disk-8-3.rrd: could not lock RRD
May 12 14:44:21 nsXXXXX collectd[31761]: rrd_update failed: disk-8-16.rrd: could not lock RRD
May 12 14:44:21 nsXXXXX collectd[4357]: rrd_update failed: disk-8-17.rrd: could not lock RRD
May 12 14:44:22 nsXXXXX collectd[31761]: rrd_update failed: disk-8-18.rrd: illegal attempt to update using time 1273668260 when last update time is 1273668260 (minimum one second step)
May 12 14:44:22 nsXXXXX collectd[31761]: rrd_update failed: disk-8-19.rrd: illegal attempt to update using time 1273668260 when last update time is 1273668260 (minimum one second step)
May 12 14:44:22 nsXXXXX collectd[31761]: rrd_update failed: disk-9-2.rrd: could not lock RRD
May 12 14:44:24 nsXXXXX collectd[4357]: rrd_update failed: disk-9-1.rrd: could not lock RRD
May 12 14:44:39 nsXXXXX collectd[31761]: rrd_update failed: df-root.rrd: could not lock RRD
May 12 14:44:40 nsXXXXX collectd[4357]: rrd_update failed: df-home.rrd: could not lock RRD
May 12 14:44:40 nsXXXXX collectd[31761]: rrd_update failed: df-dev.rrd: could not lock RRD
May 12 14:44:41 nsXXXXX collectd[31761]: rrd_update failed: df-dev-shm.rrd: illegal attempt to update using time 1273668260 when last update time is 1273668260 (minimum one second step)
May 12 14:44:41 nsXXXXX collectd[4357]: rrd_update failed: cpu-0.rrd: could not lock RRD
May 12 14:44:41 nsXXXXX collectd[31761]: rrd_update failed: cpu-1.rrd: could not lock RRD
May 12 14:44:46 nsXXXXX collectd[31761]: rrd_update failed: apache/apache_requests.rrd: could not lock RRD
May 12 14:44:46 nsXXXXX collectd[31761]: rrd_update failed: apache/apache_scoreboard-open.rrd: could not lock RRD
May 12 14:44:47 nsXXXXX collectd[31761]: rrd_update failed: apache/apache_scoreboard-waiting.rrd: could not lock RRD
May 12 14:44:47 nsXXXXX collectd[31761]: rrd_update failed: apache/apache_scoreboard-sending.rrd: could not lock RRD
May 12 14:44:47 nsXXXXX collectd[31761]: rrd_update failed: apache/apache_scoreboard-dnslookup.rrd: could not lock RRD
May 12 14:44:46 nsXXXXX collectd[4357]: rrd_update failed: apache/apache_bytes.rrd: could not lock RRD
May 12 14:44:47 nsXXXXX collectd[4357]: rrd_update failed: apache/apache_scoreboard-starting.rrd: could not lock RRD
May 12 14:44:47 nsXXXXX collectd[4357]: rrd_update failed: apache/apache_scoreboard-reading.rrd: could not lock RRD
May 12 14:44:47 nsXXXXX collectd[4357]: rrd_update failed: apache/apache_scoreboard-keepalive.rrd: could not lock RRD
May 12 14:44:47 nsXXXXX collectd[4357]: rrd_update failed: apache/apache_scoreboard-closing.rrd: could not lock RRD
May 12 14:44:47 nsXXXXX collectd[31761]: rrd_update failed: apache/apache_scoreboard-logging.rrd: could not lock RRD
May 12 14:44:48 nsXXXXX collectd[4357]: rrd_update failed: apache/apache_scoreboard-finishing.rrd: could not lock RRD
May 12 14:44:48 nsXXXXX collectd[31761]: rrd_update failed: apache/apache_scoreboard-idle_cleanup.rrd: could not lock RRD
May 12 14:44:48 nsXXXXX collectd[31761]: No sleeping because `timeval_sub_timespec' returned non-zero!
May 12 14:44:48 nsXXXXX collectd[4357]: No sleeping because `timeval_sub_timespec' returned non-zero!
May 12 14:44:48 nsXXXXX collectd[4357]: rrd_update failed: traffic-lo.rrd: could not lock RRD
May 12 14:44:49 nsXXXXX collectd[31761]: rrd_update failed: traffic-dummy0.rrd: could not lock RRD
May 12 14:44:49 nsXXXXX collectd[4357]: rrd_update failed: traffic-eth0.rrd: could not lock RRD
May 12 14:44:49 nsXXXXX collectd[31761]: rrd_update failed: traffic-tunl0.rrd: could not lock RRD
May 12 14:44:50 nsXXXXX collectd[4357]: rrd_update failed: traffic-gre0.rrd: could not lock RRD
May 12 14:44:50 nsXXXXX collectd[31761]: rrd_update failed: swap.rrd: could not lock RRD
May 12 14:44:51 nsXXXXX collectd[31761]: rrd_update failed: processes.rrd: could not lock RRD
May 12 14:44:51 nsXXXXX collectd[31761]: rrd_update failed: memory.rrd: could not lock RRD
May 12 14:44:52 nsXXXXX collectd[31761]: rrd_update failed: load.rrd: could not lock RRD
May 12 14:44:52 nsXXXXX collectd[31761]: rrd_update failed: disk-8-0.rrd: could not lock RRD
May 12 14:44:52 nsXXXXX collectd[4357]: rrd_update failed: disk-8-1.rrd: could not lock RRD
May 12 14:44:53 nsXXXXX collectd[31761]: rrd_update failed: disk-8-2.rrd: could not lock RRD
May 12 14:44:53 nsXXXXX collectd[4357]: rrd_update failed: disk-8-3.rrd: could not lock RRD
May 12 14:44:56 nsXXXXX collectd[4357]: rrd_update failed: disk-8-16.rrd: could not lock RRD
May 12 14:44:56 nsXXXXX collectd[31761]: rrd_update failed: disk-8-17.rrd: could not lock RRD
May 12 14:44:56 nsXXXXX collectd[4357]: rrd_update failed: disk-8-18.rrd: could not lock RRD
May 12 14:44:58 nsXXXXX collectd[31761]: rrd_update failed: disk-8-19.rrd: illegal attempt to update using time 1273668288 when last update time is 1273668288 (minimum one second step)
May 12 14:44:58 nsXXXXX collectd[31761]: rrd_update failed: disk-9-2.rrd: illegal attempt to update using time 1273668288 when last update time is 1273668288 (minimum one second step)
May 12 14:44:58 nsXXXXX collectd[31761]: rrd_update failed: disk-9-1.rrd: could not lock RRD
May 12 14:44:58 nsXXXXX collectd[4357]: rrd_update failed: df-root.rrd: illegal attempt to update using time 1273668288 when last update time is 1273668288 (minimum one second step)
May 12 14:44:58 nsXXXXX collectd[4357]: rrd_update failed: df-dev.rrd: illegal attempt to update using time 1273668288 when last update time is 1273668288 (minimum one second step)
May 12 14:44:58 nsXXXXX collectd[4357]: rrd_update failed: df-home.rrd: could not lock RRD
May 12 14:44:58 nsXXXXX collectd[31761]: rrd_update failed: df-dev-shm.rrd: could not lock RRD
May 12 14:45:01 nsXXXXX collectd[4357]: rrd_update failed: cpu-0.rrd: could not lock RRD
May 12 14:45:05 nsXXXXX collectd[4357]: rrd_update failed: cpu-1.rrd: could not lock RRD
May 12 14:45:05 nsXXXXX cron[4354]: (root) CMD (/usr/local/rtm/bin/rtm 24 > /dev/null 2> /dev/null)
May 12 14:45:08 nsXXXXX collectd[4357]: rrd_update failed: apache/apache_requests.rrd: could not lock RRD
May 12 14:45:09 nsXXXXX collectd[31761]: rrd_update failed: apache/apache_bytes.rrd: illegal attempt to update using time 1273668288 when last update time is 1273668288 (minimum one second step)
May 12 14:45:09 nsXXXXX collectd[31761]: rrd_update failed: apache/apache_scoreboard-open.rrd: illegal attempt to update using time 1273668288 when last update time is 1273668288 (minimum one second step)
May 12 14:45:09 nsXXXXX collectd[31761]: rrd_update failed: apache/apache_scoreboard-waiting.rrd: illegal attempt to update using time 1273668288 when last update time is 1273668288 (minimum one second step)
May 12 14:45:09 nsXXXXX collectd[31761]: rrd_update failed: apache/apache_scoreboard-starting.rrd: illegal attempt to update using time 1273668288 when last update time is 1273668288 (minimum one second step)
May 12 14:45:09 nsXXXXX collectd[31761]: rrd_update failed: apache/apache_scoreboard-reading.rrd: illegal attempt to update using time 1273668288 when last update time is 1273668288 (minimum one second step)
May 12 14:45:09 nsXXXXX collectd[31761]: rrd_update failed: apache/apache_scoreboard-sending.rrd: illegal attempt to update using time 1273668288 when last update time is 1273668288 (minimum one second step)
May 12 14:45:09 nsXXXXX collectd[31761]: rrd_update failed: apache/apache_scoreboard-keepalive.rrd: illegal attempt to update using time 1273668288 when last update time is 1273668288 (minimum one second step)
May 12 14:45:09 nsXXXXX collectd[31761]: rrd_update failed: apache/apache_scoreboard-dnslookup.rrd: illegal attempt to update using time 1273668288 when last update time is 1273668288 (minimum one second step)
May 12 14:45:09 nsXXXXX collectd[31761]: rrd_update failed: apache/apache_scoreboard-closing.rrd: illegal attempt to update using time 1273668288 when last update time is 1273668288 (minimum one second step)
May 12 14:45:09 nsXXXXX collectd[31761]: rrd_update failed: apache/apache_scoreboard-logging.rrd: illegal attempt to update using time 1273668288 when last update time is 1273668288 (minimum one second step)
May 12 14:45:09 nsXXXXX collectd[31761]: rrd_update failed: apache/apache_scoreboard-finishing.rrd: illegal attempt to update using time 1273668288 when last update time is 1273668288 (minimum one second step)
May 12 14:45:09 nsXXXXX collectd[31761]: rrd_update failed: apache/apache_scoreboard-idle_cleanup.rrd: could not lock RRD
May 12 14:45:09 nsXXXXX collectd[31761]: No sleeping because `timeval_sub_timespec' returned non-zero!
May 12 14:45:09 nsXXXXX collectd[4357]: No sleeping because `timeval_sub_timespec' returned non-zero!
May 12 14:45:09 nsXXXXX collectd[4357]: rrd_update failed: traffic-lo.rrd: could not lock RRD
May 12 14:45:09 nsXXXXX collectd[31761]: rrd_update failed: traffic-dummy0.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:09 nsXXXXX collectd[31761]: rrd_update failed: traffic-eth0.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:09 nsXXXXX collectd[31761]: rrd_update failed: traffic-tunl0.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:09 nsXXXXX collectd[31761]: rrd_update failed: traffic-gre0.rrd: could not lock RRD
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: swap.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: processes.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: memory.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: load.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: disk-8-0.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: disk-8-1.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: disk-8-2.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: disk-8-3.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: disk-8-16.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: disk-8-17.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: disk-8-18.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: disk-8-19.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: disk-9-2.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: disk-9-1.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: df-root.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: df-dev.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: df-home.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: df-dev-shm.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: cpu-0.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: cpu-1.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: apache/apache_requests.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: apache/apache_bytes.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: apache/apache_scoreboard-open.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: apache/apache_scoreboard-waiting.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: apache/apache_scoreboard-starting.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: apache/apache_scoreboard-reading.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: apache/apache_scoreboard-sending.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: apache/apache_scoreboard-keepalive.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: apache/apache_scoreboard-dnslookup.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: apache/apache_scoreboard-closing.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: apache/apache_scoreboard-logging.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: apache/apache_scoreboard-finishing.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:14 nsXXXXX collectd[4357]: rrd_update failed: apache/apache_scoreboard-idle_cleanup.rrd: illegal attempt to update using time 1273668309 when last update time is 1273668309 (minimum one second step)
May 12 14:45:20 nsXXXXX collectd[31761]: rrd_update failed: traffic-dummy0.rrd: could not lock RRD
May 12 14:45:20 nsXXXXX collectd[31761]: rrd_update failed: traffic-tunl0.rrd: could not lock RRD

J'ai pas tout mis mais les lignes sont à peu prêt les mêmes dans le reste du fichier

Julia41 · 12 Mai 2010

Je dis peut-être des conneries, mais pas mal de personnes avaient eu ce genre de soucis sur des noyaux OVH GRS en 64 bits...
Il m'ait aussi arrivé il y a quelques jours de voir une requête "temporaire" écrire un fichier de plus de 12Go (qui a dit site codé avec les pieds ?)
Voilà pour 2/3 pistes, aucune idée donc mais possible que...

tamzac · 12 Mai 2010

A priori, je pencherai pour un problème d'accès (ou de non-accès).

Fais donc un essai en ouvrant plus de droits sur tes fichiers de base ...

le user utilisé pour déclencher les process a-t-il des droits suffisants ?

jcaron · 12 Mai 2010

Et un "grep -v collected /var/log/messages"? Parce que collectd on s'en fout un peu.

Evidemment, si tu as une requête du genre select * from table t1,table t2 non qualifiée, tu peux très vite te retrouver avec une table temporaire énorme. 10 000 lignes ^ 2 ça fait tout de suite une table de plusieurs Go. Et le df, c'est après avoir relancé le tout, ou c'est pendant le problème?

Jacques.

hanlin · 12 Mai 2010

Ce sont des sites qui tournent sous joomla.
voici un extrait du top :
Tasks: 163 total, 5 running, 158 sleeping, 0 stopped, 0 zombie
Cpu(s): 56.4% us, 28.7% sy, 0.0% ni, 1.5% id, 11.9% wa, 0.2% hi, 1.3% si
Mem: 2041448k total, 1501968k used, 539480k free, 123620k buffers
Swap: 1044208k total, 105144k used, 939064k free, 997104k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
8470 mysql 40 0 215m 126m 4456 S 130 6.3 59:25.12 mysqld

hanlin · 12 Mai 2010

le df c'est apres avoir relancé.

tamzac · 12 Mai 2010

hanlin a dit:
Ce sont des sites qui tournent sous joomla.

Oui je m'en suis douté en visitant les sites que vous réalisez.

Le problème est peut-être alors lié à un add-on, si vous en utilisez un.

Le problème est spécifique à un serveur ou à tous ?

hanlin · 12 Mai 2010

Je ne comprends pas la question. L'ensemble de mes sites sont hebergés sur un serveur unique

tamzac · 12 Mai 2010

Dans le cas où vous utilisiez plusieurs serveurs, il aurait été intéressant de constater la redondance du problème et donc de déterminer si c'était un problème en commun à tous vos serveurs ou simplement un problème isolé sur un seul serveur !

hanlin · 12 Mai 2010

Si j'utilisais plusieurs serveurs je l'aurais précisé au départ...

jcaron · 12 Mai 2010

Ben ton top il nous apprend que ta machine était plutôt ras la gueule au moment où tu l'as capturé, mais (a priori) pas de lien avec ton problème initial. Regarde un peu les slow-queries de mysql, et éventuellement ce que dit show full processlist, et explain sur les requêtes les plus lourdes. Regarde aussi s'il y a des variations plus ou moins importantes de l'espace disque disponible sur / et/ou le nombre et la taille des fichiers temporaires mysql dans /tmp.

Le but du jeu, c'est de savoir si c'est effectivement dû à un problème de place lié à des requêtes qui ont besoin de tables temporaires trop grosses. Si c'est le cas, il y a deux solutions:
- déplacer /tmp (ou l'endroit où mysql crée ses tables temporaires) dans une partition où il y a plus de place (mais ça n'enlève rien au fait qu'il crée des tables temporaires énoooormes qui bouffent probablement des ressources pour pas grand chose).
- trouver les requêtes problématiques et les corriger (la bonne solution), directement ou indirectement (il est possible qu'avec des index bien placés le problème puisse être résolu).

Jacques.

hanlin · 12 Mai 2010

j'utilise un composant de rewriting pour joomla shsef404 il a 302094 urls enregistrées. Cela signifie que pour l'ensemble de ces urls il doit parcourir la table pour attribuer l'url reecrite.
Peut etre est ce du à cela mais je ne suis pas sur.

jcaron · 12 Mai 2010

Si tu la requête est correcte et qu'il y a un index approprié, non, il n'a pas besoin de parcourir l'ensemble de la table pour ça. Et surtout il ne devrait pas avoir besoin de créer une table temporaire énorme.

Mais bon, sans voir les requêtes exactes, le schéma de la base, etc, impossible d'en dire plus. Mais commence par regarder ce qu'on te dit de regarder plutôt que d'essayer de deviner, ce sera plus facile...

Jacques.

hanlin · 12 Mai 2010

voici le rapport de mysql report :

Code:

MySQL 5.0.44             uptime 0 8:26:48       Wed May 12 23:30:27 2010

__ Key _________________________________________________________________
Buffer used    82.27M of 100.00M  %Used:  82.27
  Current      85.07M            %Usage:  85.07
Write hit      99.69%
Read hit       99.93%

__ Questions ___________________________________________________________
Total          26.83M   882.2/s
  QC Hits      18.36M   603.8/s  %Total:  68.44
  DMS           8.27M   271.8/s           30.81
  Com_        183.74k     6.0/s            0.68
  COM_QUIT     45.79k     1.5/s            0.17
  -Unknown     29.39k     1.0/s            0.11
Slow 10 s          94     0.0/s            0.00  %DMS:   0.00  Log: OFF
DMS             8.27M   271.8/s           30.81
  UPDATE        6.40M   210.5/s           23.86         77.44
  SELECT        1.81M    59.4/s            6.74         21.86
  DELETE       36.53k     1.2/s            0.14          0.44
  INSERT       21.41k     0.7/s            0.08          0.26
  REPLACE           0       0/s            0.00          0.00
Com_          183.74k     6.0/s            0.68
  set_option  102.87k     3.4/s            0.38
  change_db    49.40k     1.6/s            0.18
  admin_comma  29.49k     1.0/s            0.11

__ SELECT and Sort _____________________________________________________
Scan          107.45k     3.5/s %SELECT:   5.95
Range           9.29k     0.3/s            0.51
Full join      11.00k     0.4/s            0.61
Range check     2.39k     0.1/s            0.13
Full rng join     883     0.0/s            0.05
Sort scan     177.28k     5.8/s
Sort range     55.77k     1.8/s
Sort mrg pass  37.58k     1.2/s

__ Query Cache _________________________________________________________
Memory usage   19.71M of  32.00M  %Used:  61.60
Block Fragmnt  18.54%
Hits           18.36M   603.8/s
Inserts         1.79M    58.7/s
Insrt:Prune   10.76:1    53.3/s
Hit:Insert    10.28:1

__ Table Locks _________________________________________________________
Waited          6.94k     0.2/s  %Total:   0.08
Immediate       9.15M   300.8/s

__ Tables ______________________________________________________________
Open              640 of  640    %Cache: 100.00
Opened         14.63k     0.5/s

__ Connections _________________________________________________________
Max used           49 of  100      %Max:  49.00
Total          45.80k     1.5/s

__ Created Temp ________________________________________________________
Disk table     20.81k     0.7/s
Table         166.91k     5.5/s    Size:  32.0M
File           21.90k     0.7/s

__ Threads _____________________________________________________________
Running             1 of    2
Cached              3 of    4      %Hit:  95.43
Created         2.09k     0.1/s
Slow                0       0/s

__ Aborted _____________________________________________________________
Clients             4     0.0/s
Connects           27     0.0/s

__ Bytes _______________________________________________________________
Sent           70.40G    2.3M/s
Received        2.10G   69.0k/s

__ InnoDB Buffer Pool __________________________________________________
Usage               0 of       0  %Used:   0.00
Read hit        0.00%
Pages
  Free              0            %Total:   0.00
  Data              0                      0.00 %Drty:   0.00
  Misc              0                      0.00
  Latched           0                      0.00
Reads               0       0/s
  From file         0       0/s            0.00
  Ahead Rnd         0       0/s
  Ahead Sql         0       0/s
Writes              0       0/s
Flushes             0       0/s
Wait Free           0       0/s

__ InnoDB Lock _________________________________________________________
Waits               0       0/s
Current             0
Time acquiring
  Total             0 ms
  Average           0 ms
  Max               0 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
  Reads             0       0/s
  Writes            0       0/s
  fsync             0       0/s
  Pending
    Reads           0
    Writes          0
    fsync           0

Pages
  Created           0       0/s
  Read              0       0/s
  Written           0       0/s

Rows
  Deleted           0       0/s
  Inserted          0       0/s
  Read              0       0/s
  Updated           0       0/s

hanlin · 13 Mai 2010

Voici le rapport de tuing primer :

Code:

- MYSQL PERFORMANCE TUNING PRIMER --
             - By: Matthew Montgomery -

MySQL Version 5.0.44-log x86_64

Uptime = 0 days 0 hrs 27 min 22 sec
Avg. qps = 992
Total Questions = 1629512
Threads Connected = 4

Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 10 sec.
You have 9 out of 1629543 that take longer than 10 sec. to complete
Your long_query_time may be too high, I typically set this under 5 sec.

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 4
Current threads_cached = 2
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 100
Current threads_connected = 4
Historic max_used_connections = 12
The number of used connections is 12% of the configured maximum.
Your max_connections variable seems to be fine.

MEMORY USAGE
Max Memory Ever Allocated : 177 M
Configured Max Per-thread Buffers : 161 M
Configured Max Global Buffers : 158 M
Configured Max Memory Limit : 319 M
Physical Memory : 1.94 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 263 M
Current key_buffer_size = 100 M
Key cache miss rate is 1 : 978
Key buffer free ratio = 59 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 32 M
Current query_cache_used = 19 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 59.41 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 512 K
Current read_rnd_buffer_size = 508 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 132.00 K
You have had 502 queries where a join could not use an index properly
You have had 108 joins without keys that check for key usage after each row
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 1390 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
You currently have open more than 75% of your open_files_limit
You should set a higher value for open_files_limit in my.cnf

TABLE CACHE
Current table_cache value = 640 tables
You have a total of 2864 tables
You have 640 open tables.
Current table_cache hit rate is 19%, while 100% of your table cache is in use
You should probably increase your table_cache

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 32 M
Of 6296 temp tables, 12% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 252 K
Current table scan ratio = 3368 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 2684
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.

hanlin · 13 Mai 2010

Je finis par le rapport mysqltuner.pl , je vais essayer d'apporter les modifs recommandées mais j'aimerai bien avoir l'avis d'experts :

Code:

 >>  MySQLTuner 1.0.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.44-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 2847)
[!!] Total fragmented tables: 162

-------- Performance Metrics -------------------------------------------------
[--] Up for: 34m 2s (1M q [949.225 qps], 2K conn, TX: 4B, RX: 139M)
[--] Reads / Writes: 9% / 91%
[--] Total buffers: 174.0M global + 1.6M per thread (100 max threads)
[OK] Maximum possible memory usage: 335.7M (16% of installed RAM)
[OK] Slow queries: 0% (13/1M)
[OK] Highest usage of available connections: 12% (12/100)
[OK] Key buffer size / total MyISAM indexes: 100.0M/263.7M
[OK] Key buffer hit rate: 99.9% (89M cached / 97K reads)
[OK] Query cache efficiency: 93.9% (1M cached / 1M selects)
[OK] Query cache prunes per day: 0
[!!] Sorts requiring temporary tables: 13% (1K temp sorts / 10K sorts)
[!!] Joins performed without indexes: 768
[OK] Temporary tables created on disk: 12% (1K on disk / 8K total)
[OK] Thread cache hit rate: 96% (79 created / 2K connections)
[!!] Table cache hit rate: 10% (640 open / 6K opened)
[!!] Open file limit used: 90% (1K/1K)
[OK] Table locks acquired immediately: 99% (823K immediate / 824K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    sort_buffer_size (> 511K)
    read_rnd_buffer_size (> 508K)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    table_cache (> 640)
    open_files_limit (> 1390)

Julia41 · 13 Mai 2010

Code:

Avg. qps = 992

Avec ça ça tappe quand même...

Bon, c'est de l'optimisation en vrac, avec autant de requête par seconde, faut que tu utilises un peu plus ta RAM:

Code:

Max Memory Ever Allocated : 177 M
Configured Max Per-thread Buffers : 161 M
Configured Max Global Buffers : 158 M
Configured Max Memory Limit : 319 M
Physical Memory : 1.94 G

Donc recommandations, à l'arrache:

Code:

key_buffer_size = 270M # 512 tu peux tenter
query_cache_size = 64M 
join_buffer_size = 1M ## Et là c'est toute l'optimisation à faire je pense au niveau des Index
open_files_limit = 4096
table_cache = 1024 # voir 4096 mais rare que ça tienne
max_heap_table_size = 64M
tmp_table_size = 64M ## tente de monter ces deux là à 128

Laisse tourner ton site et MySQL pendant au moins 24h.

hanlin · 13 Mai 2010

J'ai introduis ces paramètres, je vais laisser tourner cela et voir comment ça roule
merci

hanlin · 14 Mai 2010

24 heures après voila ce que donne mysqltunner.pl

Code:

>>  MySQLTuner 1.0.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.44-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 2847)
[!!] Total fragmented tables: 165

-------- Performance Metrics -------------------------------------------------
[--] Up for: 18h 23m 45s (53M q [812.846 qps], 81K conn, TX: 154B, RX: 4B)
[--] Reads / Writes: 24% / 76%
[--] Total buffers: 424.0M global + 2.5M per thread (100 max threads)
[OK] Maximum possible memory usage: 672.8M (33% of installed RAM)
[OK] Slow queries: 0% (329/53M)
[OK] Highest usage of available connections: 20% (20/100)
[OK] Key buffer size / total MyISAM indexes: 270.0M/264.1M
[OK] Key buffer hit rate: 100.0% (2B cached / 914K reads)
[OK] Query cache efficiency: 92.6% (40M cached / 43M selects)
[!!] Query cache prunes per day: 351907
[OK] Sorts requiring temporary tables: 3% (16K temp sorts / 453K sorts)
[!!] Joins performed without indexes: 24889
[OK] Temporary tables created on disk: 7% (28K on disk / 363K total)
[OK] Thread cache hit rate: 97% (2K created / 81K connections)
[OK] Table cache hit rate: 70% (724 open / 1K opened)
[OK] Open file limit used: 30% (1K/4K)
[OK] Table locks acquired immediately: 99% (14M immediate / 14M locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
Variables to adjust:
    query_cache_size (> 64M)
    join_buffer_size (> 1020.0K, or always use indexes with joins)

Mes sites n'ont pas crachés mais je continue à voir des pics de CPU à 150 voir 190 % pendant une ou deux secondes à cause de MYSQL.
Les requetes des sites joomla sont celles qui consomment le plus, j'ai activé le cache sur ces sites.

Julia41 · 14 Mai 2010

Mes sites n'ont pas crachés mais je continue à voir des pics de CPU à 150 voir 190 % pendant une ou deux secondes à cause de MYSQL.
Les requetes des sites joomla sont celles qui consomment le plus, j'ai activé le cache sur ces sites.

Bon, donc t'as vraiment des requêtes de merde dans un des joomla voir les deux.
Au niveau de ton "query_cache_size", augmente le comme te dis mysqltunner (tuning-primer est un peu mieux d'après moi).

Pour trouver ces requêtes gênantes tu peux tenter de faire un :

Code:

mysql -u root -p<ton pass> -e "SHOW FULL PROCESSLIST;" >> temp.txt

toutes les 2 secondes (avec un watch par exemple ou à la main).

Si tu arrives à chopper ces "moments de pics de CPU", lance ça pendant le pic, et tu pourras trouver cette requête qui fou la merde (peut-être pleins de petites remarques).

Bon courage quand même.

jcaron · 14 Mai 2010

S'il n'est pas déjà activé, ajoute le logging des "slow queries". S'il est activé, regarde dans le log correspondant les requêtes problématiques...

Jacques.

hanlin · 14 Mai 2010

j'ai activé le fichier le log slowqueries il grossit vite effectivement. Je voulais ajouter la solution proposée ici (http://www.webdevelopmentstuff.com/112/optimizing-mysql-log-slow-queries.html) qui me permettrais de mieux lire ces logs mais je ne sais pas si cela ne va pas finalement ajouter une charge à mysql et empirer les choses

Julia41 · 14 Mai 2010

Je me double post, mais si tu trouves cette requête ou ces requêtes fait un :

Code:

EXPLAIN SELECT blabla FROM blabla

et mets nous les résultats (soit sur que c'est la bonne).