0

I have searched all over but couldn't find where service mysqld restart picks up the configuration file from:

sudo find / -name "*.cnf" | grep my

/usr/share/doc/mysql-community-server-5.6.33/my-default.cnf
/usr/share/mysql/my-default.cnf
/var/lib/mysql/auto.cnf
/var/lib/mysql/my.cnf
/etc/my.cnf

My MySQL 5.6.33 is running on 1 GB of InnoDB Buffer Pool whereas none of these files has that setting. I have setting

[mysqld]
innodb_buffer_pool_size = 4G

in the file copied from /usr/share/mysql/my-default.cnf to /etc/my.cnf - I did this:

service mysqld restart --defaults-file=/etc/my.cnf

Still not working.

  • Post output of following: show global variables like 'innodb_buffer%'; – mysql_user Oct 8 '16 at 14:03
  • grep innodb /etc/my.cnf – mysql_user Oct 8 '16 at 14:04
  • @mysql_user - 'grep innodb /etc/my.cnf' gives me innodb_buffer_pool_size = 4G but show variables like 'innodb_buffer_pool_size' gives me 134217728; – MontyPython Oct 8 '16 at 14:07
  • Check to be sure about my.cnf used as follows: mysqld –verbose –help | grep -C1 my.cnf – mysql_user Oct 8 '16 at 14:11
  • Also your error log will confirm "initializing innocent buffer pool..... " – mysql_user Oct 8 '16 at 14:12
1
  • About Service mysqld start --defaults-file=..

AFAIK it isn't correct way to pass argument. It should not be accepting that argument. Never done that; importantly it isn't even necessary for default location!!!

  • Confirm the which my.cnf is being used:

mysqld –verbose –help | grep -C1 my.cnf

  • Confirm we have correct entry in my.cnf

grep innodb_buffer_pool_size /etc/my.cnf

  • Verify your error log, you should see something like

InnoDB: Initializing buffer pool size = ...

0

You can use this to set global innodb buffer pool size

SET GLOBAL innodb_buffer_pool_size=4294967296;

http://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html

  • I did mention that I am using MySQL 5.6 - in MySQL 5.6, you can't change InnoDB Buffer Pool Size online. – MontyPython Oct 8 '16 at 13:58
0

In my case the problem was innodb_buffer_pool_instances.

Since I was reducing innodb_buffer_pool_size, it became less than one 1GB per instance, so it ended up rounding it.

When I also reduced the instances, it finally changed the pool size!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.