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?

Neo · Accepted Answer · 2011-04-18 02:58:14Z

76

add this to your my.cnf

innodb_buffer_pool_size=1G

restart your mysql to make it effect

answered Apr 18, 2011 at 2:58

Neo

5,5132 gold badges25 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

analyticsPierce Over a year ago

thanks for your help. I added what you suggested with no success. I checked the innodb_buffer_pool_size after and it was still 8M. My file is /etc/my.cnf. Should it be in a different location? do I need anything else in the file?

Neo Over a year ago

@analyticsPierce may be your mysql config file is ~/.my.cnf,how do you start mysql?, try to use this script dev.mysql.com/doc/refman/5.0/en/mysql-server.html

analyticsPierce Over a year ago

I am running on a Mac and MySQL is started when I start my laptop, done through a preference setting.

Neo Over a year ago

@analyicsPierce does the ~/.my.cnf exist？

pkaeding Over a year ago

On Ubuntu (and quite possibly other linux distros), the my.cnf file is at /etc/mysql/my.cnf not /etc/my.cnf.

|

SuperShoot · Accepted Answer · 2018-03-02 06:20:48Z

40

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

edited Mar 2, 2018 at 6:20

SuperShoot

11k2 gold badges45 silver badges63 bronze badges

answered Jul 12, 2016 at 15:23

1000111

13.5k2 gold badges32 silver badges43 bronze badges

2 Comments

Alex Szücs Over a year ago

ERROR 1238 (HY000): Variable 'innodb_buffer_pool_size' is a read only variable

w.Daya Over a year ago

mysql> SET GLOBAL innodb_buffer_pool_size=402653184; this worked for me, but this reset when you restart. So have to fire same query if you restart mysql.

Jorge Walters · Accepted Answer · 2012-08-31 06:02:03Z

28

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.

answered Aug 31, 2012 at 6:02

Jorge Walters

2813 silver badges2 bronze badges

1 Comment

Jorge Walters Over a year ago

sorry, this was meant to be a feedback in the previous answer. I was referring to put innodb_buffer_pool_size=1G inside the [mysqld] section.

exceed · Accepted Answer · 2013-04-11 15:13:37Z

3

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

Steps to update this variable:

Load Terminal.
Type cd /etc/.
sudo vi my.cnf.
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).
Using vi(m) find the line innodb_buffer_pool_size, press i to start making changes.
When finished, press esc, shift+colon and type wq.
Profit (done).

answered Apr 11, 2013 at 15:13

exceed

312 bronze badges

2 Comments

Eric Johnson Over a year ago

"locate my.cnf" ("sudo updatedb" if its not found) will be much faster than a find from root, especially on a large file system. If you cannot use locate yet, take a look at osxdaily.com/2011/11/02/…

exceed Over a year ago

Good addition, it appears to be a sort of cached search function. Thanks Eric.

Luke Madhanga · Accepted Answer · 2014-11-05 10:53:47Z

3

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.

answered Nov 5, 2014 at 10:53

Luke Madhanga

7,5572 gold badges48 silver badges52 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

8 Comments

UPDATE :

2 Comments

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

8 Comments

UPDATE :

2 Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related