56

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?

analyticsPierce
  • 2,979
  • 9
  • 57
  • 81

5 Answers5

75

add this to your my.cnf

innodb_buffer_pool_size=1G

restart your mysql to make it effect

neocanable
  • 5,293
  • 2
  • 23
  • 28
  • 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? – analyticsPierce Apr 18 '11 at 04:54
  • @analyticsPierce may be your mysql config file is ~/.my.cnf,how do you start mysql?, try to use this script http://dev.mysql.com/doc/refman/5.0/en/mysql-server.html – neocanable Apr 18 '11 at 05:06
  • I am running on a Mac and MySQL is started when I start my laptop, done through a preference setting. – analyticsPierce Apr 18 '11 at 05:31
  • @analyicsPierce does the ~/.my.cnf exist？ – neocanable Apr 18 '11 at 05:56
  • It did not. I added it and it did not make a difference. same problem. Any suggestions? – analyticsPierce Apr 18 '11 at 13:56
  • 18
    On Ubuntu (and quite possibly other linux distros), the `my.cnf` file is at `/etc/mysql/my.cnf` not `/etc/my.cnf`. – pkaeding Feb 26 '12 at 05:17
  • it is also recommended to increase 'innodb_buffer_pool_instances' when using 1G or more buffer size. it will usually improve performance. – tony gil Aug 06 '13 at 14:49
  • restart mysql using this command ```sudo service mysqld stop``` & ```sudo service mysqld start``` Or ```sudo service mysql stop``` & ```sudo service mysql start``` – jeevu94 Dec 22 '22 at 11:55
36

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

SuperShoot
  • 9,880
  • 2
  • 38
  • 55
1000111
  • 13,169
  • 2
  • 28
  • 37
  • 1
    `ERROR 1238 (HY000): Variable 'innodb_buffer_pool_size' is a read only variable` – Alex Szücs Feb 18 '22 at 13:24
  • `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. – w.Daya Jul 13 '23 at 10:18
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.

Jorge Walters
  • 281
  • 3
  • 2
  • 2
    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. – Jorge Walters Aug 31 '12 at 06:07
3

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).
exceed
  • 31
  • 2
  • "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 http://osxdaily.com/2011/11/02/enable-and-use-the-locate-command-in-the-mac-os-x-terminal/ – Eric Johnson Sep 01 '13 at 06:05
  • Good addition, it appears to be a sort of cached search function. Thanks Eric. – exceed Oct 08 '13 at 15:50
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.

Luke Madhanga
  • 6,871
  • 2
  • 43
  • 47