we have a mysql server (bi xeon dual core), with 6 GB of memory.
all works perectly during the day (maximum connections between 18H and 20H, but still very hight at the end of the day), but every night, nearly 22H, mysql use something like 97% of the cpu (380% with top commande).
and at midnight, it stops.
The server is configured so that only our tomcat/apache/mod_jk can access to the database.
server version : 5.0.45-Debian_1
we have also strange values in phpmyadmin (server process):
Innodb_row_lock_time_avg 3 785
Innodb_row_lock_time_max 11 k
Handler_read_rnd 2 784k
Qcache_lowmem_prunes 197 k
i know there are some troubles in SQL queries, we are working on that (in fact i just arrived in the project).
But what i can’t undertsand is why it is every night at the same time.
We have automatic updates every hours made by a tomcat thread.
here the my.cfg for information :
key_buffer = 64M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 10 (change from 8 to 1à today)
max_connections = 5000 (will change to 100 today)
table_cache = 256 (will change to 300 today)
wait_timeout = 60 (just put this one today 11/24/2007 in the config file)
#thread_concurrency = 10
* Query Cache Configuration
query_cache_limit = 1M
query_cache_size = 16M
thanks for help.