Set MySQL innodb buffer pool size on IIS7

Question

I am using VPS, Windows 2008 r2, IIS7, MYSQL 5.1.73, Hardward Config: Ram 2GB , Hardisk 40GB

On this VPS only 1 .NET application is running. Application is currently using by only 5 users. I found MySQL server INNODB BUFFER USAGE is 100%

I found some suggestions like change buffer size in my.cnf or my.ini file, but I didn't find any file on VPS. I have tried below command as well

SET GLOBAL innodb_buffer_pool_size = 33554432

but its giving me an error as

Variable 'innodb_buffer_pool_size' is a read only variable

Can any one please suggest me a solution for this.

Thanks

Answer 1

innodb_buffer_pool_size requires restarting MySQL. Why ?

Take a look at Percona's Vadim Tkachenko's Pictorial Representation of InnoDB

In the upper left corner, you see the InnoDB Buffer Pool. It has Data and Index Pages for InnoDB Tables and a giant scratch pad for migrating secondary index changes from the Buffer Pool to the Insert Buffer. All those things would have to be written to disk and all transactions completed. Since all that must happen, it was best to leave innodb_buffer_pool_size as a static variable.

What you need to do is add that option to my.ini

[mysqld]
innodb_buffer_pool_size = 32M

and restart mysql.