Mysql – innodb memory usage thesql

innodbMySQLvps

I have a small vps, with only 256mb of ram, with maximum burst up to 512mb. When I configure my vps without innodb, it only uses 130 mb of ram, so that is no problem for me. But when I turn on innodb, The memory usage grows to about 300-400 mb. Is it possible to run innodb such that I won't exceed the 256mb? preferably I don't want to use more than 100mb for innodb. I already came across some sites which said I could limit the memory usage, but if I limit it to only 100mb will the db run well enough? (compared to for example the MyISAM storage engine)

If 100mb is too little memory for innodb, can you recommend me any other storage engine which supports transactions?

Best Answer

You should use 75-80% or RAM as the max buffer pool size if the server is dedicated to doing MySQL

Please place these two lines in /etc/my.cnf (my.ini)

[mysqld]
innodb_buffer_pool_size=200M
innodb_log_file_size=50M

If you want to go with 100MB recommendation for InnoDB then use these instead

[mysqld]
innodb_buffer_pool_size=100M
innodb_log_file_size=25M

Make sure you resize the innodb log files as follows:

service mysql stop
rm -f /var/lib/mysql/ib_logfile*
service mysql start

(Edit: fixed typo in rm command)

Related Topic