MySQL server 99.99% usage

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

On my LAMP webserver, I noticed that sometimes the MySQL daemon uses  
99.99% of the CPU, and does not answer SQL queries, while several  
Does someone know what could be the causes ?

I searched on forums, and I found answers about query cache,  
but this is not enabled on my server.
I looked the process list (SHOW FULL PROCESSLIST) :
- almost all the queries where "Locked"
- almost all queries where SELECT
- there was no "difficult" query (big UPDATE or big DELETE or REPAIR or  
My database is mainly used with the 'members' table which contains  
100000+ records, and a lot of INDEXes. All the queries shown in the  
process list were about this table.

Any idea ? Any solution to prevent this periodical server lag ?

Thanks in advance for your help :)
Olivier (and excuse my bad english !)

Olivier Ligny
Créateur web free-lance /

Re: MySQL server 99.99% usage

O.L. wrote:

Quoted text here. Click to load it

Hi Oliver,

You say you have a lot of indexes.
This means that the simple insert or update you mentioned must also update  
all these indexes.
Could that explain the high processorload?

Remember that indexes can be usefull, but only if you do a lot more selects  
with the indexed columns in the where-clause than you do inserts/updates.  
Indexes slow down inserts/updates, but will greatly increase selects, so  
use them wisely.

Erwin Moller

Re: MySQL server 99.99% usage

Erwin Moller a couché sur son écran :
Quoted text here. Click to load it

Hi Erwin, and thank you for your answer.

When I looked the process list, there was only 1 update, and about 20  
or 30 SELECTs. My website mainly do SELECTs, rather than UPDATEs. The  
INSERTs are rare : only 1 every minute approximatively (each time a new  
member join the site). PHPMyAdmin shows me that the average query/sec  
is 15 queries by second on the whole server, and I suppose it's SELECTs  
at 80%.

Olivier Ligny
Créateur web free-lance /

Site Timeline