Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Here is my situation.

I have an ubuntu server monitored by nagios. recently nagios sends alarms regarding the memory consumption on the server. I logged into the server and executed the top command then I found that mysql is the process that consumes a lot of the memory (about 64%).

This server contains a forum, and DB replica for another server so I logged into mysql and show processlist I found only 7 records.

How could I dig more in this situation to decide what is the application which consumes all that memory?

share|improve this question

migrated from stackoverflow.com Jan 3 '12 at 11:41

This question came from our site for professional and enthusiast programmers.

    
first of all, how much is 64%? –  Topener Jan 3 '12 at 8:22
    
free -m total > 7366 –  Anas Rabei Jan 3 '12 at 8:28
1  
check the my.cnf file, it'll configure how much memory mysql is to use. –  nos Jan 3 '12 at 8:37
1  
Read this. In short, if your server is dedicated to MySQL then I would configure MySQL to use as much memory as possible so it can cache as much data as possible. This is critical to the performance of a traditional RDBMS. –  Nick Chammas Jan 3 '12 at 21:48

2 Answers 2

MySQL consumes 64% is not a very big deal if the load is very high and the memory is low on the machine. Typically I would say its better that it should not consume more than 20 to 25% of the total memory available.

Anyways for your question, just try to find out the bottlenecks.
1 You should be able to find out if there are any long running queries - Say for example you are fetching some 1 million rows or so in a InnoDB engine (where transaction is supported) and your query has lot of joins.
2 Then you can tweak the actual config file of MySQL to increase the number of connections that is allowed and the max memory that is assigned to MySQL.
3 Also make sure you close all the sql connections in your application if you have not done it.
4 If long running queries are the bottleneck, then you can change some of the tables to MyIsam if it doesn't matter with transactions.

I hope it helps !

share|improve this answer
    
1-The load is not high and the total memory is 8G so it is not normal situation. –  Anas Rabei Jan 3 '12 at 8:38

In my experience, 64% is very low for a machine that is dedicated to a MySQL server. I have numerous machines where the InnoDB buffer pool size is set at 75-80% of the physical RAM on the machine, with even more of the remaining RAM allocated to the query cache and key_buffer.

Remember, that it is very dependant on the my.cnf settings, so check through those, lookng at the buffer sizes, cache sizes, etc.

MySQL will cache queries whenever possible (if set to do so), and this will each up the query cache allocation, also the key cache is used to hold myISAM index in memory for faster performance.

MySQL system tables in the "mysql" database are all myISAM, and so any indexes of those tables will be cached.

Other areas that will use RAM are the sort buffers, read buffers, etc. which are allocated on a per connection basis - remember, you can have connections established which aren't running queries, and would therefore not show up on the processlist...

Don't worry about MySQL using that much RAM, I'd even configure it to use more if possible!

You should start looking at things if you start to see the swap file being used lots, and I'm sure nagios can be set up to monitor that - I'd set the threshold for MySQL within nagios for MUCH higher that whatever you have it set to at the moment!

Check out the MySQL Configuration wizard from Percona - these guys really know what they are talking about...

http://www.mysqlperformanceblog.com/2011/12/23/online-mysql-configuration-wizard-from-percona/

Hope that helps :)

Dave

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.