almost 100% CPU used, but every night only between 22h and 24H

MySQL & MariaDB Other MySQL® Questions
1

Hi,

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_buffer_pool_reads 25
Innodb_buffer_pool_pages_dirty 32
Innodb_row_lock_time_avg 3 785
Innodb_row_lock_time_max 11 k
Innodb_row_lock_waits 18
Handler_read_rnd_next 1366
Handler_read_rnd 2 784k

Qcache_lowmem_prunes 197 k
Created_tmp_disk_tables 97k
Opened_tables 260
Select_full_join 2

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.

2

Two options:
1.
Turn on query log so that know what queries are being issued during this time.

Log in to the mysql server during this time and run a “SHOW PROCESSLIST;”.

Because you will have to find out what your server is doing.
It is nothing that MySQL does by itself. Something is running queries against the server at this time.

Some suggestions if the load is very repetitive to a certain time is scheduled things like:
Some sort of report tool?
Backups?
etc.
Which very often are placed on schedule during the night.