Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

My innodb_buffer_pool_size is set to 32M

Do you mean you see this value by checking:

mysql> select @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                  33554432 |
+---------------------------+
1 row in set (0.00 sec)

and I want to lower it.

Why? How many RAM do you have?

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.

As it said, innodb_buffer_pool_size is not a dynamic variable. You cannot change it without restarting MySQL.

Where is the 32M coming from?

One possible reason is... it is set to 32M in /etc/my.cnf originally, and someone change it to 2M but didn't restart MySQL.

share|improve this answer
    
First question: Yes. Answer to second question: The vast majority of my tables are using mysiam, so it doesn't need to be that high. Finally, restarting MySQL does nothing. I should've made that clear in the original question. And it was never set to 32M in my.cnf. –  TheYeti Aug 1 '12 at 13:36

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.

share|improve this answer
    
I'm in the process of moving from one server to another. As I stated above, my databases are running on mysiam, not innodb. Also, as I stated above, it was set as a default of 2M in my my.cnf. I've restarted MYSQL multiple times with no change. Plesk is the only database using innodb tables. On my current server, it's set at 2M and I have no problems. 32M is unnecessary. –  TheYeti Aug 1 '12 at 13:40
    
P.S. I should clarify that I know this because 1) I checked SHOW TABLE STATUS on my (Joomla) databases and 2) when I added skip-innodb, the only thing that broke was Plesk. I ran mysqltuner.pl and it confirmed that I had some innodb tables. –  TheYeti Aug 1 '12 at 13:48
    
In that case, change it to enough for Plesk and restart MySQL. –  Michael Hampton Aug 1 '12 at 15:08
    
That's kind of the problem: Changing the value and restarting MySQL does nothing. –  TheYeti Aug 1 '12 at 16:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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