# MySQL server is consuming 100% CPU

**URL:** <https://forums.percona.com/t/mysql-server-is-consuming-100-cpu/1339>\
**Category:** Other MySQL® Questions\
**Created:** [February 4, 2010, 3:46am UTC](https://forums.percona.com/t/mysql-server-is-consuming-100-cpu/1339 "2010-02-04T03:46:57Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![vikramdarsi](https://avatars.discourse-cdn.com/v4/letter/v/bb73d2/32.png) [@vikramdarsi](https://forums.percona.com/u/vikramdarsi)\
**Post date:** [February 4, 2010, 3:46am UTC](https://forums.percona.com/t/mysql-server-is-consuming-100-cpu/1339/1 "2010-02-04T03:46:57Z")

</div>

Someboby please help me,

MySQL server is consuming 100% CPU  
from “show processlist” we observed that 42 queries status is showing as “sorting”;  
we restarted the mysql server, but this situation happened again after 3 days, so looking for a permanenet solution for this  
this is the query:  
select \* from AlarmCounter where Status = ‘0’ AND Severity=4 ORDER BY Time DESC LIMIT 0, 1000

this is the query we run from our EMS every after 10 seconds

AlarmCounter is having 7000 records  
the same dump we got and loaded on our local machines and running the same query with still less delay ,but never reproduced that…so is this the query the root cause for this situation? or something else?

/etc/my.cnf is this:  
flush\_time=10  
sync\_binlog=1  
innodb\_flush\_log\_at\_trx\_commit=1  
external-locking  
log-bin-trust-function-creators=1  
sql-mode=STRICT\_ALL\_TABLES  
slave-skip-errors=1062  
max\_allowed\_packet=16M  
log\_slow\_queries=/var/log/mysql-slow.log

other varibles are mysql defaults  
we use myisam storage engine

can you suggest me any tuning params, if the query run on 7000 recrds is not an issue for every 10 sec, the record size may go up to 1,00,000

mysql\> select version();  
±----------------------+  
| version() |  
±----------------------+  
| 5.0.64-enterprise-log |  
±----------------------+  
1 row in set (0.00 sec)

---

<div class="post-metadata">

**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\
**Post date:** [February 4, 2010, 2:57pm UTC](https://forums.percona.com/t/mysql-server-is-consuming-100-cpu/1339/2 "2010-02-04T14:57:22Z")

</div>

indices on AlarmCounter?

---

<div class="post-metadata">

**Author:** ![vikramdarsi](https://avatars.discourse-cdn.com/v4/letter/v/bb73d2/32.png) [@vikramdarsi](https://forums.percona.com/u/vikramdarsi)\
**Post date:** [February 4, 2010, 10:52pm UTC](https://forums.percona.com/t/mysql-server-is-consuming-100-cpu/1339/3 "2010-02-04T22:52:48Z")

</div>

Hi gmouse,

first of all,thanks for the reply

show indexes from AlarmCounter;  
±-------------±-----------±---------±-------------±---- --------±----------±------------±---------±-------±---- -±-----------±--------+  
| Table | Non\_unique | Key\_name | Seq\_in\_index | Column\_name | Collation | Cardinality | Sub\_part | Packed | Null | Index\_type | Comment |  
±-------------±-----------±---------±-------------±---- --------±----------±------------±---------±-------±---- -±-----------±--------+  
| AlarmCounter | 0 | PRIMARY | 1 | TejasKey | A | 6863 | NULL | NULL | | BTREE | |  
±-------------±-----------±---------±-------------±---- --------±----------±------------±---------±-------±---- -±-----------±--------+  
1 row in set (0.01 sec)

mysql\> desc AlarmCounter;  
±----------------------±-------------±-----±----±------ --±------+  
| Field | Type | Null | Key | Default | Extra |  
±----------------------±-------------±-----±----±------ --±------+  
| TrapId | varchar(768) | YES | | NULL | |  
| AdditionalInformation | varchar(768) | YES | | NULL | |  
| StateMarker | double | YES | | 0 | |  
| AckBy | varchar(768) | YES | | | |  
| IPAddress | varchar(768) | YES | | NULL | |  
| Time | double | YES | | NULL | |  
| Object | varchar(768) | YES | | NULL | |  
| Status | int(11) | YES | | 0 | |  
| Deferred | int(11) | YES | | 0 | |  
| Severity | int(11) | YES | | 1 | |  
| TejasKey | varchar(768) | NO | PRI | NULL | |  
| AckMessage | varchar(768) | YES | | | |  
| LctName | varchar(768) | YES | | NULL | |  
±----------------------±-------------±-----±----±------ --±------+  
13 rows in set (0.00 sec)

please let me know if you need some more details

awaiting for your reply

Thanks  
Vikram

---

<div class="post-metadata">

**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\
**Post date:** [February 5, 2010, 2:13pm UTC](https://forums.percona.com/t/mysql-server-is-consuming-100-cpu/1339/4 "2010-02-05T14:13:43Z")

</div>

varchar(768) to store an IP-address? Please use appropriate data types.

And read about multi-column indices and try to understand why an index on (status,severity,time) would help here.