3

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?

Anas Rabei
  • 31
  • 1
  • 2

2 Answers2

3

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 !

2

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

Dave Rix
  • 1,343
  • 9
  • 16