Announcement

Announcement Module
Collapse
No announcement yet.

MySQL server is consuming 100% CPU

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL server is consuming 100% CPU

    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?

    Comment


    • #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

      Comment


      • #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.

        Comment

        Working...
        X