MYSQL Memory Usage

Question

I have MySQL installed on a VPS Windows 2008 Web Server with 1GB of memory.

Is there a way of setting a maximum memory usage limit for MYSQL.

I have workbench installed if it can be done through that.

Many Thanks

John

Answer 1

If you really want to impose a hard limit, you could do so, but you'd have to do it at the OS level as there is no built-in setting. In linux, you could utilize ulimit, but you'd likely have to modify the way MySQL starts in order to impose this.

The best solution is to tune your server down, so that a combination of the usual MySQL memory settings will result in generally lower memory usage by your MySQL installation. This will of course have a negative impact on the performance of your database, but some of the settings you can tweak in my.ini are:

key_buffer_size
query_cache_size
query_cache_limit
table_cache
max_connections
tmp_table_size
innodb_buffer_pool_size

To Plot memory usage in Linux you can easily use a script

while true
do
  date >> ps.log
  ps aux | grep mysqld >> ps.log
  sleep 60
done

if you are trying to Check for Table Cache Related Allocations Run “FLUSH TABLES” and see whenever memory usage goes down.

Note though because of how memory is allocated from OS you might not see “VSZ” going down. What you might see instead is flushing tables regularly or reducing table cache reduces memory consumption to be withing the reason.

It is often helpful to check how much memory Innodb has allocated. In fact this is often one of the first things I do as it is least intrusive. Run SHOW ENGINE INNODB STATUS and look for memory information block