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?
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.
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
For standard OS X installations of MySQL you will find my.cnf located in the /etc/ folder.
Steps to update this variable:
cd /etc/
.sudo vi my.cnf
.sudo find / -name 'my.cnf' 2>1
- this will hide the errors and only report the successfile file location).innodb_buffer_pool_size
, press i
to start making changes.wq
.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.
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.
Source: Stackoverflow.com