How to change value for innodb_buffer_pool_size in MySQL on Mac OS?

Question

I am trying to increase the size of the innodb_buffer_pool_size in MySQL 5.1 as I keep running into the following error indicating I have run out of space for the table locks.

ERROR: The total number of locks exceeds the lock table size Error Code: 1206 

I have gone through the documentation and from what I gather, I need to update innodb_buffer_pool_size in the /etc/my.cnf file. My current value is 8M. However, even after creating that file and adding the following line to set the value it is not updating in MySQL.

set-variable=innodb_buffer_pool_size=256M 

Does have any advice on how I can adjust this value in MySQL on my mac? Any other advice or suggestions?

Answer 1

add this to your my.cnf

innodb_buffer_pool_size=1G 

restart your mysql to make it effect

Answer 2

In the earlier versions of MySQL ( < 5.7.5 ) the only way to set

'innodb_buffer_pool_size'

variable was by writing it to my.cnf (for linux) and my.ini (for windows) under [mysqld] section :

[mysqld]  innodb_buffer_pool_size = 2147483648 

You need to restart your mysql server to have it's effect in action.

UPDATE :

As of MySQL 5.7.5, the innodb_buffer_pool_size configuration option can be set dynamically using a SET statement, allowing you to resize the buffer pool without restarting the server. For example:

mysql> SET GLOBAL innodb_buffer_pool_size=402653184; 

Reference : https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html