MySQL High CPU Load

[jordant]

Hi all,

After a few minutes of deploying my EnGarde 3.0.14 box mysqld uses 100% CPU and doesn't ever give up.

I managed to track this down to being triggered by /usr/libexec/webtool/generate_snort_graphs.pl - when this script is run (automatically via cron) it causes mysqld to behave badly.

Oddly though, terminating this script doesn't free up the CPU and I have to issue an `/etc/init.d/mysql restart` until the next instance of the script is run.

As a work around I have moved generate_snort_graphs.pl out of the way and replaced it with a simple "exit 0;" perl script and the system runs perfectly (without snort graphs ofcorse).

some background info:
hardware is a Dell SC430 (Pentium D 2.8GHz with 512mb RAM)
I've also tried enabling & disabling the snort service (it has been configured) but this had no affect.

Any ideas?

Jordan.

[esila]

Hi Jordan,

We'd like to further analyze the problem to help you out. Would you be
able to make a tar of /var/lib/mysql and send it as an attachment to
support@guardiandigital.com? From there we will keep you posted to
our discoverings.

Best regards,
~Eckie

Hi all,

After a few minutes of deploying my EnGarde 3.0.14 box mysqld uses 100% CPU and doesn't ever give up.

I managed to track this down to being triggered by /usr/libexec/webtool/generate_snort_graphs.pl - when this script is run (automatically via cron) it causes mysqld to behave badly.

Oddly though, terminating this script doesn't free up the CPU and I have to issue an `/etc/init.d/mysql restart` until the next instance of the script is run.

As a work around I have moved generate_snort_graphs.pl out of the way and replaced it with a simple "exit 0;" perl script and the system runs perfectly (without snort graphs ofcorse).

some background info:
hardware is a Dell SC430 (Pentium D 2.8GHz with 512mb RAM)
I've also tried enabling & disabling the snort service (it has been configured) but this had no affect.

Any ideas?

Jordan.

[jordant]

This has been done.

[erwindr]

This is what Cron e-mailed me this morning, have not seen it before.
This e-mail arrives every 5 minutes.
My setup is used almost out-of-the-box; I did not touch snort configuration yet. (But it is running in 'Services') I also noticed CPU load averaging at 50%.
The server is an Asus 2.2GHz Celeron with 512Mb.

From: (Cron Daemon)
To: xxxx
Subject: Cron <root@wilhelmina> /usr/libexec/webtool/generate_snort_graphs.pl
X-Cron-Env: <SHELL=/bin/sh>
X-Cron-Env: <HOME=/root>
X-Cron-Env: <PATH=/usr/bin:/bin>
X-Cron-Env: <LOGNAME=root>
X-Cron-Env: <USER=root>
Message-Id: <20070602141510.E3B144F405C@wilhelmina>
Date: Sat, 2 Jun 2007 16:15:10 +0200 (CEST)

Illegal division by zero at /usr/lib/perl5/vendor_perl/5.8.8/GD/Graph/axestype.pm line 1962.

Regards!
Erwin

[jordant]

Mine too, is out of the box. I noticed the CPU minutes after the first post-install boot up. I have not modified anything on the system yet (apart from configuration via webtool).

So is this a bug or something really bizaare - should i re-install and try again?

[erwindr]

Did a complete reinstall yesterday afternoon and now, after about 30 hours, the Cron e-mails start coming in again.
Definitely looks like a bug.

Regards!
Erwin

---
Received: by xxx (Postfix, from userid 0)
id 67E0DB405D; Tue, 5 Jun 2007 22:30:11 +0200 (CEST)
From: xxx (Cron Daemon)
To: xxx
Subject: Cron <root@xxx
> /usr/libexec/webtool/generate_snort_graphs.pl
X-Cron-Env: <SHELL=/bin/sh>
X-Cron-Env: <HOME=/root>
X-Cron-Env: <PATH=/usr/bin:/bin>
X-Cron-Env: <LOGNAME=root>
X-Cron-Env: <USER=root>
Message-Id: <20070605203011.67E0DB405D@xxx>
Date: Tue, 5 Jun 2007 22:30:11 +0200 (CEST)

Illegal division by zero at /usr/lib/perl5/vendor_perl/5.8.8/GD/Graph/axestype.pm line 1962.

[erwindr]

As a work around I have moved generate_snort_graphs.pl out of the way and replaced it with a simple "exit 0;" perl script and the system runs perfectly

Did what you did and CPU load was down immediately.



Erwin

[esila]

Jordan,

This has been done.

Thanks for the test data, it was extremely helpful in our analysis. Since you two have been communicating back and forth on this particular issue, I was wondering if you could try this fix that I've tested in vmware. It cut the processor usage from 90% plus over a 5min span to around 20% in just a few seconds :)

Log into the mysql server console and use the snort database.

Code: Select all

mysql > use snort;

From there, you will be creating indexes on two tables, tcphdr and iphdr.

(Note: In my instance, the tcphdr indexing took significantly longer than iphdr. Please be patient and let the query complete, should take a few minutes if you have 10,000+ entries.)

Code: Select all

mysql > create index tcphdr_cid_index on tcphdr (cid);
mysql > create index iphdr_cid_index on iphdr (cid);

After indexing is complete, you can reinitialize the generate_snort_graphs.pl cronjob, or simply run it from the command line:

Code: Select all

/usr/libexec/webtool/generate_snort_graphs.pl

This should generate all the graphs in /var/cache/webtool/snort-graphs in just a few seconds (or significantly less time than with non-indexed tables).

Give it a shot and let me know if this changes anything, I appreciate the feedback!

Best regards,
Eckie

[jordant]

Code: Select all

mysql> create index tcphdr_cid_index on tcphdr (cid);
create index ipQuery OK, 10329 rows affected (4.97 sec)
Records: 10329  Duplicates: 0  Warnings: 0

mysql> create index iphdr_cid_index on iphdr (cid);
Query OK, 10338 rows affected (0.09 sec)
Records: 10338  Duplicates: 0  Warnings: 0

when /usr/libexec/webtool/generate_snort_graphs.pl runs, system uses 50% CPU for about 2 or 3 seconds, then goes idle again. everything looks fine now.

Bug fixed, hooray!

Thanks and good work guys.

[esila]

when /usr/libexec/webtool/generate_snort_graphs.pl runs, system uses 50% CPU for about 2 or 3 seconds, then goes idle again. everything looks fine now.

Bug fixed, hooray!

Thanks and good work guys.

Hooray is always an awesome response :)

Thanks so much for putting the time in to report, test, and confirm the prementioned data, a fix will be in order soon!

Best regards,
Eckie

[hsinanch]

Hi,

Do we still have same problem in version 3.0.22? Looks like I have exactly same problem.

I just turn on snortd service, and found the same message and keep sending out email from cron like:
Illegal division by zero at /usr/lib/perl5/vendor_perl/5.8.8/GD/Graph/axestype.pm line 1962.

Then I tried the steps above. I got errors in the below commands because they existed in database already.
mysql > create index tcphdr_cid_index on tcphdr (cid);
mysql > create index iphdr_cid_index on iphdr (cid);

Then I run "/usr/libexec/webtool/generate_snort_graphs.pl", I got this error:
/usr/libexec/webtool/generate_snort_graphs.pl

DBI connect('database=mysql;host=localhost','engarde-maint',...) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at /usr/lib/perl5/vendor_perl/5.8.8/WebTool/MySQL.pm line 205
Can't call method "do" on unblessed reference at /usr/webtool/modules/snort/snort.pm line 137.

After I turn off SELinux and run it again, I got this error:
/usr/libexec/webtool/generate_snort_graphs.pl

Illegal division by zero at /usr/lib/perl5/vendor_perl/5.8.8/GD/Graph/axestype.pm line 1962.

Any idea?

Thanks,

Hsinan

[yog]

This should do the job:

Code: Select all

OPEN: /usr/lib/perl5/vendor_perl/5.8.8/GD/Graph/axestype.pm
GOTO line 1962
REPLACE:
        $self->{lg_rows} =
            int($num / $self->{lg_cols}) + (($num % $self->{lg_cols}) ? 1 : 0);
WITH:
   if ($self->{lg_cols} gt 0)
   {
      $self->{lg_rows} = int($num / $self->{lg_cols}) + (($num % $self->{lg_cols}) ? 1 : 0);
   }
   else
   {
      $self->{lg_rows} = $num;
   }


Regards, yog