mysql crash

Discussion dans 'Administration d'un site Web' créé par hanlin, 12 Mai 2010.

  1. hanlin
    hanlin WRInaute occasionnel
    Inscrit:
    23 Mars 2006
    Messages:
    263
    J'aime reçus:
    0
    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 !
     
  2. tamzac
    tamzac Nouveau WRInaute
    Inscrit:
    25 Mars 2010
    Messages:
    26
    J'aime reçus:
    0
    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 !
     
  3. jcaron
    jcaron WRInaute accro
    Inscrit:
    13 Février 2004
    Messages:
    2 579
    J'aime reçus:
    0
    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.
     
  4. hanlin
    hanlin WRInaute occasionnel
    Inscrit:
    23 Mars 2006
    Messages:
    263
    J'aime reçus:
    0
    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
     
  5. Julia41
    Julia41 WRInaute passionné
    Inscrit:
    31 Août 2007
    Messages:
    1 774
    J'aime reçus:
    0
    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...
     
  6. tamzac
    tamzac Nouveau WRInaute
    Inscrit:
    25 Mars 2010
    Messages:
    26
    J'aime reçus:
    0
    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 ?
     
  7. jcaron
    jcaron WRInaute accro
    Inscrit:
    13 Février 2004
    Messages:
    2 579
    J'aime reçus:
    0
    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.
     
  8. hanlin
    hanlin WRInaute occasionnel
    Inscrit:
    23 Mars 2006
    Messages:
    263
    J'aime reçus:
    0
    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
     
  9. hanlin
    hanlin WRInaute occasionnel
    Inscrit:
    23 Mars 2006
    Messages:
    263
    J'aime reçus:
    0
    le df c'est apres avoir relancé.
     
  10. tamzac
    tamzac Nouveau WRInaute
    Inscrit:
    25 Mars 2010
    Messages:
    26
    J'aime reçus:
    0
    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 ?
     
  11. hanlin
    hanlin WRInaute occasionnel
    Inscrit:
    23 Mars 2006
    Messages:
    263
    J'aime reçus:
    0
    Je ne comprends pas la question. L'ensemble de mes sites sont hebergés sur un serveur unique
     
  12. tamzac
    tamzac Nouveau WRInaute
    Inscrit:
    25 Mars 2010
    Messages:
    26
    J'aime reçus:
    0
    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 !
     
  13. hanlin
    hanlin WRInaute occasionnel
    Inscrit:
    23 Mars 2006
    Messages:
    263
    J'aime reçus:
    0
    Si j'utilisais plusieurs serveurs je l'aurais précisé au départ...
     
  14. jcaron
    jcaron WRInaute accro
    Inscrit:
    13 Février 2004
    Messages:
    2 579
    J'aime reçus:
    0
    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.
     
  15. hanlin
    hanlin WRInaute occasionnel
    Inscrit:
    23 Mars 2006
    Messages:
    263
    J'aime reçus:
    0
    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.
     
  16. jcaron
    jcaron WRInaute accro
    Inscrit:
    13 Février 2004
    Messages:
    2 579
    J'aime reçus:
    0
    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.
     
  17. hanlin
    hanlin WRInaute occasionnel
    Inscrit:
    23 Mars 2006
    Messages:
    263
    J'aime reçus:
    0
    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
    
     
  18. hanlin
    hanlin WRInaute occasionnel
    Inscrit:
    23 Mars 2006
    Messages:
    263
    J'aime reçus:
    0
    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'.
    
     
  19. hanlin
    hanlin WRInaute occasionnel
    Inscrit:
    23 Mars 2006
    Messages:
    263
    J'aime reçus:
    0
    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 <[email protected]>
     >>  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)
     
  20. Julia41
    Julia41 WRInaute passionné
    Inscrit:
    31 Août 2007
    Messages:
    1 774
    J'aime reçus:
    0
    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.
     
  21. hanlin
    hanlin WRInaute occasionnel
    Inscrit:
    23 Mars 2006
    Messages:
    263
    J'aime reçus:
    0
    J'ai introduis ces paramètres, je vais laisser tourner cela et voir comment ça roule
    merci
     
  22. hanlin
    hanlin WRInaute occasionnel
    Inscrit:
    23 Mars 2006
    Messages:
    263
    J'aime reçus:
    0
    24 heures après voila ce que donne mysqltunner.pl

    Code:
    >>  MySQLTuner 1.0.0 - Major Hayden <[email protected]>
     >>  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.
     
  23. Julia41
    Julia41 WRInaute passionné
    Inscrit:
    31 Août 2007
    Messages:
    1 774
    J'aime reçus:
    0
    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.
     
  24. jcaron
    jcaron WRInaute accro
    Inscrit:
    13 Février 2004
    Messages:
    2 579
    J'aime reçus:
    0
    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.
     
  25. hanlin
    hanlin WRInaute occasionnel
    Inscrit:
    23 Mars 2006
    Messages:
    263
    J'aime reçus:
    0
  26. Julia41
    Julia41 WRInaute passionné
    Inscrit:
    31 Août 2007
    Messages:
    1 774
    J'aime reçus:
    0
    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).
     
Chargement...
Similar Threads - mysql crash Forum Date
Crash MySQL régulier Administration d'un site Web 21 Août 2011
Mysql : Impact convertion champ numérique SMALLINT vers BIGINT Développement d'un site Web ou d'une appli mobile 23 Août 2021
Quel SGBDR autre que MySQL/MariaDB ? Administration d'un site Web 12 Janvier 2021
encodage texte sur requete mysql Demandes d'avis et de conseils sur vos sites 21 Octobre 2020
Requête MySql imbriquée Développement d'un site Web ou d'une appli mobile 8 Octobre 2020
Supprimer les doublons d'une table mysql Développement d'un site Web ou d'une appli mobile 16 Juin 2020
Mysql migration utf8->utf8mb4 Développement d'un site Web ou d'une appli mobile 17 Août 2019
recherche lettres dans mysql Développement d'un site Web ou d'une appli mobile 11 Juillet 2019
cache mysql maison Développement d'un site Web ou d'une appli mobile 18 Février 2019
Stocker dans des variables php les fonctions MySql Développement d'un site Web ou d'une appli mobile 2 Février 2019
message : [LEGACY][libmysqlclient] Please consider moving to stable and mysqlnd in Administration d'un site Web 8 Novembre 2018
Connexion à un serveur mysql distant Développement d'un site Web ou d'une appli mobile 21 Octobre 2018
Mysql, modifier des chaines avec différents caractères Administration d'un site Web 13 Septembre 2018
Fusionner deux champs sur la même table et même base de donnée Mysql Administration d'un site Web 12 Septembre 2018
Requête Mysql avec des string Développement d'un site Web ou d'une appli mobile 6 Février 2018
Surveiller les connexions à la base de données MySQL Développement d'un site Web ou d'une appli mobile 1 Février 2018
PHP : script pour mettre catalogue xml clickbank dans mysql Développement d'un site Web ou d'une appli mobile 6 Décembre 2017
Mise à jour MySql 5.1 vers 5.5 Administration d'un site Web 1 Juillet 2017
Problème avec un changement de version Mysql de 5.5 à 5.7 Développement d'un site Web ou d'une appli mobile 9 Juin 2017
Requete mysql Développement d'un site Web ou d'une appli mobile 30 Mai 2017