mysql tuner reports that mySQL can use 166% of installed ram, how do I limit RAM usage ?

[!!] Maximum possible memory usage: 426.8M (166% of installed RAM)

  • You should also add some RAM. 256 MB is not very much.
    – user81458
    Aug 8, 2012 at 14:24
  • @Tibor It's a cheap low end VPS
    – HackToHell
    Aug 8, 2012 at 15:02

3 Answers 3


You can either configure

  • All MyISAM
  • All InnoDB
  • Mixture of MyISAM and InnoDB

Before allocating memory to either engine, it is good to reflect on what caching goes with each Storage Engine

Configuring for MYISAM

The main mechanism used is the key cache. It only caches index pages from .MYI files. To size your key cache, run the following query:

SUBSTR(' KMG',IF(PowerOf1024<0,0,
recommended_key_buffer_size FROM
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;

This will give the Recommended Setting for MyISAM Key Cache (key_buffer_size) given your current data set (the query will cap the recommendation at 4G (4096M). For 32-bit OS, 4GB is the limit. For 64-bit, 8GB.

Configuring for InnoDB

The main mechanism used is the InnoDB Buffer Pool. It caches data and index pages from InnoDB tables accessed. To size your InnoDB Buffer Pool, run the following query:

SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 2 PowerOf1024) B;

This will give the Recommended Setting for the size of the InnoDB Buffer Pool (innodb_buffer_pool_size) given your current data set.

Don't forget to resize the InnoDB Log Files (ib_logfile0 and ib_logfile1). MySQL Source Code places a cap of the combined sizes of all InnoDB Log Files must be < 4G (4096M). (NOTE: Percona Server binaries surpass this. I recently setup a large DB server with 4G for a single InnoDB Log File using innodb_log_file_size)

For the sake of simplicity, given just two log files, here is how you can size them:

  • Step 1) add innodb_log_file_size=NNN to /etc/my.cnf (NNN should be 25% of innodb_buffer_pool_size or 2047M, whichever is smaller)
  • Step 2) service mysql stop
  • Step 3) rm /var/log/mysql/ib_logfile[01]
  • Step 4) service mysql start (ib_logfile0 and ib_logfile1 are recreated)


At the End of both queries is a an Inline Query : (SELECT 2 PowerOfTwo) B

  • (SELECT 0 PowerOf1024) gives the Setting in Bytes
  • (SELECT 1 PowerOf1024) gives the Setting in Kilobytes
  • (SELECT 2 PowerOf1024) gives the Setting in Megabytes
  • (SELECT 3 PowerOf1024) gives the Setting in Gigabytes
  • No powers less that 0 or greater than 3 is accepted


There is no substitute for common sense. If you have limited memory, a mixture of storage engines, or a combination thereof, you will have to adjust for different scenarios.

If you have 2GB RAM and 16GB of InnoDB, allocate 512M as innodb_buffer_pool_size.

If you have 2GB RAM and 4GB of MyISAM Indexes, allocate 512M as key_buffer_size.

If you have 2GB RAM and 4GB of MyISAM Indexes and 16GB InnoDB, allocate 512M as key_buffer_size and 512M as innodb_buffer_pool_size.

Possible scenarios are endless !!!

Remember, whatever you allocate for, leave enough RAM for DB Connections and the Operating System.


You should tweak key_buffer_size and innodb_buffer_pool_size in your my.cnf, these two are the most important memory-related settings. To get your current values use show variables like 'key_buffer_size'; and show variables like 'innodb_buffer_pool_size'; in mysql command-line client.


in case of Myisam Engine

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections

this value gives the total possible memory that mysql can consume.This should be less than your RAM or nearly 60 % of RAM. incase of innodb innodb_buffer_pool_size should be less than your RAM or 60% of your ram

Tune above values,so that RAM usage will be 60% of your RAM.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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