MySQL server 99.99% usage

[O.L.]

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 !)

--
Olivier Ligny
Créateur web free-lance / www.cyber-tamtam.net

[Erwin Moller]

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

[O.L.]

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