Unable to lower innodb_buffer_pool_size

TheYeti asked:

Really frustrating issue. My innodb_buffer_pool_size is set to 32M, and I want to lower it. The variable in my.cnf is actually set to 2M, and I can’t find any other overriding configuration files (and yes, it’s set in the mysqld block). When I try to set the variable at the command line, I get: ERROR 1238 (HY000): Variable ‘innodb_buffer_pool_size’ is a read only variable. Where is the 32M coming from??

UPDATE: Is it possible the variable was compiled in and cannot be changed at my.cnf? Or is it possible my Linux distro (openSUSE 11.3) has an include I can’t find? And please read below before telling me to change the variable and restart MySQL. That does nothing.

My answer:

You can only set innodb_buffer_pool_size in my.cnf. It cannot be set while the database is running.

In any event, 2M is far too small for all but the tiniest test database. 32M is almost certainly too small for any real database. If you’re tuning a database server, this is usually the number to increase, not decrease.

View the full question and answer on Server Fault.

Creative Commons License
This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.