Solved

Fix High MYSQL Load

Posted on 2008-06-10
15
348 Views
Last Modified: 2008-06-18
For the past day or so mysqld has been using anywhere from 80-200% of CPU, according to top from ssh.  I am looking for something that can log information for some time, and then I can review it and find out what is causing this.  I have tried the slow queries log and nothing went into it.
0
Comment
Question by:jls33fsls
  • 5
  • 5
  • 2
  • +1
15 Comments
 
LVL 9

Expert Comment

by:Rob Siklos
ID: 21755381
you can use " SHOW PROCESSLIST  " to see which queries are running - maybe that will give you some hints.
0
 

Author Comment

by:jls33fsls
ID: 21755427
I know that, and no that will not help.  There are hardly ever any queries that even show up because they all run so quickly.  I know there is software out there, I just can't seem to find it.
0
 
LVL 9

Expert Comment

by:Rob Siklos
ID: 21755434
0
 
LVL 9

Expert Comment

by:Rob Siklos
ID: 21755441
It's also possible that it's a bug in MySQL.  I think I've seen this kind of behaviour once in a while, and I just restarted the process and it fixed itself.  You could try upgrading to the latest version perhaps?
0
 

Author Comment

by:jls33fsls
ID: 21755449
I have the latest version, and I have tried restarting and the load goes straight back to what it was at.
0
 

Author Comment

by:jls33fsls
ID: 21755459
I have looked at mtop, but that seems to give about the same information as the process list.  I need something to gather data over time, there is nothing to see in real time because there are no queries sitting there lagging or it would be easier to figure out.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 9

Expert Comment

by:Rob Siklos
ID: 21755492
You could always use the general query log, I suppose.
0
 

Author Comment

by:jls33fsls
ID: 21755505
I have seen software specifically made for this in the past, I am pretty sure it was open source, but I can't remember what it was.  It isn't something built in to mysql.
0
 
LVL 2

Expert Comment

by:pain_is_relative
ID: 21756400
how about optimising your queries?

have you ever used the EXPLAIN command?

It will break down your request into the number of records that will be tested for a solution. This can often be reduced by adding a few indexes.... So rather than searching through 100,000 records, it may just need to search 6 records.

ie reduce server load by adding indexes to your tables, if required, to reduce the time of individual transaction.

0
 

Author Comment

by:jls33fsls
ID: 21756494
I know all about that, and of course I have optimized my queries.  The problem here is that I don't know where the problem is, that is why I am asking for the name or link to the software needed to find the problem.  I don't need basic tips about mysql optimization, I am not new to this.
0
 
LVL 9

Accepted Solution

by:
Rob Siklos earned 250 total points
ID: 21759812
Do you think that it's the volume of queries that's killing you?  Maybe it's a specific query that's doing something weird.

I would turn on the general query log (instead of slow query log), capture all the statements that get executed before and during the "high cpu usage" timeframe.  

Then, configure mysql to only accept connections from localhost, and replay the query log.  Maybe it's not the queries themselves, but the number of connections being open/closed etc.

I guess what I'm getting at is that you should try and reproduce the high-cpu-load behaviour in a controlled environment.  That way, you can adjust various parameters and see what makes a difference.
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 250 total points
ID: 21760006
Anything in /var/log/mysql.err and /var/log/mysql.log ?

Worse case scenario might be to re-install... Have seen a couple of posts regarding mysql-server (meta package depending on the latest version) and mysql-server-5.0 causing the conflict...

That program was probably mytop  : http://jeremy.zawodny.com/mysql/mytop/   but don't think it is for the latest versions... There was a 1.6 released in 2007, so who knows, might be OK.

You might also find some useful links : http://www.oreillynet.com/pub/a/mysql/2006/10/19/mysql-tools.html
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21762379
>>that is why I am asking for the name or link to the software<<

What was wrong with the post I provide - had a few choices there. Why have you requested this question be deleted ? It is a "common" occurance, and many people would like to know a possible solution.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now