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

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 suggestion开发者_StackOverflows?

add this to your my.cnf

innodb_buffer_pool_size=1G

restart your mysql to make it effect

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

I had to put the statement under the [mysqld] block to make it work. Otherwise the change was not reflected. I have a REL distribution.

For standard OS X installations of MySQL you will find my.cnf located in the /etc/ folder.

Steps to update this variable:

1. Load Terminal.
2. Type cd /etc/.
3. sudo vi my.cnf.
4. This file should already exist (if not please use sudo find / -name 'my.cnf' 2>1 - this will hide the errors and only report the successfile file location).
5. Using vi(m) find the line innodb_buffer_pool_size, press i to start making changes.
6. When finished, press esc, shift+colon and type wq.
7. Profit (done).

As stated,

innodb_buffer_pool_size=50M

Following the convention on the other predefined variables, make sure there is no space either side of the equals sign.

Then run

sudo service mysqld stop
sudo service mysqld start

Note

Sometimes, e.g. on Ubuntu, the MySQL daemon is named mysql as opposed to mysqld

I find that running /etc/init.d/mysqld restart doesn't always work and you may get an error like

Stopping mysqld:                                           [FAILED]
Starting mysqld:                                           [  OK  ]

To see if the variable has been set, run show variables and see if the value has been updated.