Posted by O.L. on 12/01/06 18:09

Erwin Moller a couché sur son écran :
> O.L. wrote:
>
>> Hello,
>>
>> 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
>> minutes.
>> Does someone know what could be the causes ?
>>
>> I searched on MySQL.com 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
>> OPTIMIZE ...)
>> 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 !)
>>
>
> 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.
>
> Regards,
> Erwin Moller

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 / www.cyber-tamtam.net

[Back to original message]