Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

Optimizing MySQL - Low CPU but extremely high Memory usage

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
JohnnyBCJ
Asked:
JohnnyBCJ
  • 7
  • 4
  • 3
1 Solution
 
aboo_sCommented:
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
 
JohnnyBCJAuthor Commented:
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
 
aboo_sCommented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
JohnnyBCJAuthor Commented:
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
 
aboo_sCommented:
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
 
JohnnyBCJAuthor Commented:
'MySQL' is the service that is taking up the 150-155 megs or ram.
0
 
JohnnyBCJAuthor Commented:
It's taking up about 60 Megs when idled.
0
 
aboo_sCommented:
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
 
wolfgang_93Commented:
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
 
JohnnyBCJAuthor Commented:
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
 
wolfgang_93Commented:
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
 
JohnnyBCJAuthor Commented:
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
 
wolfgang_93Commented:
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
 
JohnnyBCJAuthor Commented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now