LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices

Reply
 
Search this Thread
Old 09-02-2006, 09:39 AM   #1
bentz
Member
 
Registered: Mar 2003
Distribution: Fedora, Mac OSX
Posts: 362

Rep: Reputation: 30
high mysql load causing snmp to become unresponsive


Greetings,
I'm running an iptables firewall and am using MySQL to store the associated logging information. I'm also running net-snmp on the firewall to monitor interface state and bandwidth.

Each night, an insert ... select statement runs that moves about 2,000,000 records from the primary firewall log database to an archive database located on the same machine. While this process is running, the mysql server process consumes much of the available CPU.

My problem is that while this is running, the net-snmp server becomes completely unresponsive, and causes our network monitoring system to lose statistics and indicate that the firewall is down. I can reproduce the problem each time by executing the SQL statements manually and then attempting to create snmp connections, and I get a timeout every time until the SQL statement completes.

I've tried moving the SQL process from insert ... select to a dumpfile process, I've tried renice on the mysql client (nice), mysql server (nice), and snmp server (not nice) - no luck there.

Does anyone have any ideas?
 
Old 09-03-2006, 06:23 AM   #2
stress_junkie
Senior Member
 
Registered: Dec 2005
Location: Massachusetts, USA
Distribution: Ubuntu 10.04 and CentOS 5.5
Posts: 3,873

Rep: Reputation: 331Reputation: 331Reputation: 331Reputation: 331
You could try running the SQL software at nice priority of 19. I've just spent a couple of weeks doing some stress testing. I used the dd command to write 30 gigabytes to a disk and then tried to get the system to do other things as well. I found that if I ran the dd job at nice 19 then the rest of the jobs still worked fairly well.

If the job is already running the you can use the renice command to add niceness to a process.

Last edited by stress_junkie; 09-03-2006 at 06:26 AM.
 
Old 09-03-2006, 09:54 AM   #3
bentz
Member
 
Registered: Mar 2003
Distribution: Fedora, Mac OSX
Posts: 362

Original Poster
Rep: Reputation: 30
Yep, I did try that. I tried renicing the mysql server process and the client process to a lower priority (higher nice number), and it didn't help. net-snmp was still effected by the CPU load placed by the MySQL server process.
 
Old 09-03-2006, 01:06 PM   #4
stress_junkie
Senior Member
 
Registered: Dec 2005
Location: Massachusetts, USA
Distribution: Ubuntu 10.04 and CentOS 5.5
Posts: 3,873

Rep: Reputation: 331Reputation: 331Reputation: 331Reputation: 331
How about increasing the priority of net-snmp by giving it a negative nice value?
 
Old 09-03-2006, 07:21 PM   #5
bentz
Member
 
Registered: Mar 2003
Distribution: Fedora, Mac OSX
Posts: 362

Original Poster
Rep: Reputation: 30
Quote:
Originally Posted by stress_junkie
How about increasing the priority of net-snmp by giving it a negative nice value?
Yes, I've done that, too.

My original post says "I've tried moving the SQL process from insert ... select to a dumpfile process, I've tried renice on the mysql client (nice), mysql server (nice), and snmp server (not nice) - no luck there." which is where I mentioned this.
 
Old 09-03-2006, 08:27 PM   #6
stress_junkie
Senior Member
 
Registered: Dec 2005
Location: Massachusetts, USA
Distribution: Ubuntu 10.04 and CentOS 5.5
Posts: 3,873

Rep: Reputation: 331Reputation: 331Reputation: 331Reputation: 331
Sorry I wasted your time. Mea culpa. I'll definitely think twice before I respond to another of your posts.

Last edited by stress_junkie; 09-03-2006 at 08:29 PM.
 
Old 09-04-2006, 05:39 AM   #7
unSpawn
Moderator
 
Registered: May 2001
Posts: 27,451
Blog Entries: 54

Rep: Reputation: 2893Reputation: 2893Reputation: 2893Reputation: 2893Reputation: 2893Reputation: 2893Reputation: 2893Reputation: 2893Reputation: 2893Reputation: 2893Reputation: 2893
I'm running an iptables firewall and am using MySQL to store the associated logging information. I'm also running net-snmp on the firewall to monitor interface state and bandwidth.

Each night, an insert ... select statement runs that moves about 2,000,000 records from the primary firewall log database to an archive database located on the same machine.

So aren't your *real* problems running MySQL on the same host *and* running an "archive" (backup?) database on the same host? (A firewall being a firewall and not a database server) Looking for cause instead or symptoms, are these 2M -j LOG lines really necessary for accounting or is it in essence overkill? Could spreading archiving over, say 4 times, help alleviate the problem?
 
Old 09-04-2006, 06:17 AM   #8
syg00
LQ Veteran
 
Registered: Aug 2003
Location: Australia
Distribution: Lots ...
Posts: 12,351

Rep: Reputation: 1041Reputation: 1041Reputation: 1041Reputation: 1041Reputation: 1041Reputation: 1041Reputation: 1041Reputation: 1041
And further to that ...,

is it really CPU that's the problem ???.
With all that I/O, interrupt handling (in addition to device busy) is going to be a real issue. Are you seeing high CPU usage or high load values ??? - they are not the same thing, especially where high (extreme) I/O loads are involved.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mysql and httpd memory load is HIGH !!! Pariah Linux - General 3 10-31-2009 12:56 PM
Load Avg High/Phys Mem High teamh Debian 2 12-26-2006 05:03 PM
RAID5 and RAID1 causing high system load on Suse 10.1 with no activity desibeli Linux - General 11 06-10-2006 01:50 PM
Disk performance causing high Load Avg? craigeb78 Linux - Hardware 6 03-09-2006 04:47 PM
high mysql load hardigunawan Linux - General 0 03-04-2003 01:43 AM


All times are GMT -5. The time now is 09:06 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration