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

User · Answer

In the earlier versions of MySQL    lt  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 gt  SET GLOBAL innodb buffer pool size 402653184     Reference   https   dev mysql com doc refman 5 7 en innodb-buffer-pool-resize html

User · Answer

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

User · Answer

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

User · Answer

add this to your my cnf  innodb buffer pool size 1G   restart your mysql to make it effect

User · Answer

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 gt 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

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

UPDATE :

Examples related to mysql

Examples related to innodb