[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

Fix High MYSQL Load

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
jls33fsls
Asked:
jls33fsls
  • 5
  • 5
  • 2
  • +1
2 Solutions
 
Rob SiklosCommented:
you can use " SHOW PROCESSLIST  " to see which queries are running - maybe that will give you some hints.
0
 
jls33fslsAuthor Commented:
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
 
Rob SiklosCommented:
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Rob SiklosCommented:
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
 
jls33fslsAuthor Commented:
I have the latest version, and I have tried restarting and the load goes straight back to what it was at.
0
 
jls33fslsAuthor Commented:
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
 
Rob SiklosCommented:
You could always use the general query log, I suppose.
0
 
jls33fslsAuthor Commented:
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
 
pain_is_relativeCommented:
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
 
jls33fslsAuthor Commented:
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
 
Rob SiklosCommented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
Mark WillsTopic AdvisorCommented:
>>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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 5
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now