Solved

Fix High MYSQL Load

Posted on 2008-06-10
15
355 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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
 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Exchange 2013 tmp files 3 40
corrupt Databases 9 63
A beginner asks: Which macos postgreSQL client? 5 45
jQuery force form POST 7 43
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

809 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