#253 High CPU usage by mysqld process

[Anonymous]

Hello,

Last days the server where is running IPAM has had high cpu usage by the mysql process. Next, the output of top command:

                                                                  %CPU %ME

1021 mysql 20 0 689436 58496 10528 S 93.0 1.4 1:23.40 mysqld

Sometimes is above of 150%, an this is affecting the load pages time. I've seen that when the cpu is high there is a query stuck in the processlist:

mysql> show processlist;
+-----+---------+-----------+---------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+---------+-----------+---------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 132 | phpipam | localhost | phpipam | Query | 198 | Sending data | select SQL_CACHE * from (
select sectionId,id,subnet,cast(subnet as UNSIGNED) as cmp,`ma |
| 133 | root | localhost | NULL | Query | 0 | NULL | show processlist

And where that query is flushed the cpu usage get down. I've tried to tuned the config file of mysql but not success. I would like some advices to avoid this frequently spikes of cpu. I also tried to update to last version.

Thanks.

[Miha Petkovsek]

Hi, this query is used to display top subnets for dashboard graphs. Maybe try removing thise widgets ?

[Anonymous]

Hi Miha,

Thank for your reply. I disable this widget and I'm goint to test the server today to tell you if the change works.

[Miha Petkovsek]

Ok. Please also send me the create table syntax for table ipaddresses. You can omit any custom fields you created.

SHOW CREATE TABLE `ipaddresses`;

br

[Anonymous]

Hello, next the output of the command tah you ask:

mysql> SHOW CREATE TABLE ipaddresses;
----------------------------------------------------------------------------------------------------+
| Table | Create Table -------------------------------------------------------------------------------------------------+
| ipaddresses | CREATE TABLE ipaddresses (
id int(11) NOT NULL AUTO_INCREMENT,
subnetId int(11) unsigned DEFAULT NULL,
ip_addr varchar(100) NOT NULL,
description varchar(64) DEFAULT NULL,
dns_name varchar(64) NOT NULL,
mac varchar(20) DEFAULT NULL,
owner varchar(32) DEFAULT NULL,
state varchar(1) DEFAULT '1',
switch int(11) unsigned DEFAULT NULL,
port varchar(32) DEFAULT NULL,
note text,
lastSeen datetime DEFAULT '0000-00-00 00:00:00',
excludePing binary(1) DEFAULT '0',
editDate timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=92429 DEFAULT CHARSET=utf8 |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Thanks for your help.

[Miha Petkovsek]

Hi, as I suspected.

Please enter this SQL statement:

ALTER TABLE `ipaddresses` ADD INDEX (`subnetId`);

br

[Anonymous]

Its done. Im going to monitoring a couple of days, but yesterday I saw better performance than before.

Thanks again for your time.

[Miha Petkovsek]

Ok. You can put widgets back it shouldnt affect SQL load anymore.

[Anonymous]

Hello Miha,

The IPAM has been working fine since the changes suggested by you.

Again, thanks for your help.

[Miha Petkovsek]

Np, glad it is fixed niw br

[Anonymous]