MySQL server is consuming 100% CPU

MySQL & MariaDB Other MySQL® Questions
1

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)

2

indices on AlarmCounter?

3

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

4

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.