Solved

Optimizing MySQL - Low CPU but extremely high Memory usage

Posted on 2010-11-15
14
2,209 Views
Last Modified: 2012-05-10
I am attempting to optimize the MySQL 5.1.49 server. I noticed that the CPU usage is at about 20% but Memory usage is 90%. I am curious on if I should do anything about this. At this time I am the only person on the server running a series of queries. Traffic is between 900-1000 KB/S. Query Cache Hitrate is 0.13%. Key Efficiency is steady at 50%.

I need to be as prepared as possible for having 50 users on the server running similar queries. What can I do?
0
Comment
Question by:JohnnyBCJ
  • 7
  • 4
  • 3
14 Comments
 
LVL 10

Expert Comment

by:aboo_s
ID: 34138508
I suggest you kill your mysql server process to see memory level on idle state.
I frankly don't think mysql will use 90% unless memory is low!
You might be infected with some spyware or something!
0
 

Author Comment

by:JohnnyBCJ
ID: 34138635
After it runs through the last series of queries I'll shut down the MySQL Server to see what the memory level is on idle state like you suggested. MySQL is installed on an SBS 2003 machine with 3.5 gigs of ram and  about 20 or so users currently logged in.

I'm not so concerned with the huge difference between the CPU usage and the memory usage. I'm more concerned on why the memory usage is so high. The process 'mysqld.exe' is only using 150,000K of memory.
0
 
LVL 10

Expert Comment

by:aboo_s
ID: 34138694
if your server is consuming 90% of 3.5 gigs with cpu on 20%, well this seems very much strange.
Something is deffinitly wrong. Any way if mysql.exe is consuming 150mb which is not little! then what process is taking over your RAM, you must find out!
0
 

Author Comment

by:JohnnyBCJ
ID: 34138794
I'm curious, where does it get this 90% from?

The server has 4 Gigs of ram (3.5 Gigs is accessible). 'MySQLD.exe' is taking up about 150 to 155 megs of ram. Does the memory usage in MySQL Workbench refer to something different?
0
 
LVL 10

Expert Comment

by:aboo_s
ID: 34138829
It might be the case, if you shut it down, then all subroutines will be killed with it (kill process tree)
this way you can find out if it is this process or something else maliciouse !
0
 

Author Comment

by:JohnnyBCJ
ID: 34138971
'MySQL' is the service that is taking up the 150-155 megs or ram.
0
 

Author Comment

by:JohnnyBCJ
ID: 34139037
It's taking up about 60 Megs when idled.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 10

Expert Comment

by:aboo_s
ID: 34139120
So it is mysql that has been taking about 90% of memory!?
Now when you run it again does it get back to the same level again!?
0
 
LVL 8

Expert Comment

by:wolfgang_93
ID: 34139139
I can think of a simple example where little of the available memory gets used and you have
a poor hit-cache ratio:
-  Tables involved are of engine type InnoDB and the buffer pool size is set based
    on default, i.e. very low. To fix:
    -  Look for a line like this in your my.cnf or my.ini config file and set it to at least 2 gig"
       (though if you are running 32 bit version of MySQL, you likely have to set it to something
       less as there is an upper limit of addressable RAM)
          innodb_buffer_pool_size   = 2G
        Restart your MySQL server to make it use the new parameter
0
 

Author Comment

by:JohnnyBCJ
ID: 34146678
After leaving MySQL running for a day, after starting at 60 megs, it ran up to 100 megs.

I don't see how 100 megs could take up 90% of the memory when the system has 4 gigs of ram installed.

I guess what MySQL Workbench is reporting, is the system resources that are used. Not what MySQL is using.

The tables involved are of engine type InnoDB and the innodb_buffer_pool_size was at the default before I changed them yesterday (before posting this question). I noticed that Key Efficiency was running at 100% so I decided to double what was set as the buffer pool.  Now the key efficiency is running at a steady 50%. I'd rather have too much memory allocated and not needed than vise versa.
0
 
LVL 8

Expert Comment

by:wolfgang_93
ID: 34147594
Did you make sure to reboot the server after changing the value in the my.cnf or my.ini file?

The MySQL Workbench should be able to report what the active value of innodb_buffer_pool_size
is being used by the server at the moment. Verify that it matches what you believe you have
it set to.


0
 

Author Comment

by:JohnnyBCJ
ID: 34148926
I did reboot the server afterwards. Multiple times in fact.

When I doubled the buffer, the key efficiency was cut in half. I don't have a problem with that.
0
 
LVL 8

Accepted Solution

by:
wolfgang_93 earned 500 total points
ID: 34149484
Did you check the queries themselves to ensure they are optimized?
A slow-query log can be turned on to identify slow running queries.

Just a single query involving a join of 2 large tables on unindexed
fields can suck the overall performance out of a SQL Server system.

Rule of thumb: you want an index on any fields involved in a join,
fields involved in a GROUP BY, and fields involved in a ORDER BY,
and fields commonly used in WHERE clauses to narrow down
queries.
0
 

Author Comment

by:JohnnyBCJ
ID: 34189726
wolfgang_93:
Did you check the queries themselves to ensure they are optimized?
A slow-query log can be turned on to identify slow running queries.

Just a single query involving a join of 2 large tables on unindexed
fields can suck the overall performance out of a SQL Server system.

Rule of thumb: you want an index on any fields involved in a join,
fields involved in a GROUP BY, and fields involved in a ORDER BY,
and fields commonly used in WHERE clauses to narrow down
queries.


I will do as you suggested and will tell you the results. I'm pretty sure you nailed my problem.

0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

760 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