1

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.

Improve this question
6
  • Post output of following: show global variables like 'innodb_buffer%';
    – mysql_user
    Commented Oct 8, 2016 at 14:03
  • grep innodb /etc/my.cnf
    – mysql_user
    Commented Oct 8, 2016 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
    Commented Oct 8, 2016 at 14:07
  • Check to be sure about my.cnf used as follows: mysqld –verbose –help | grep -C1 my.cnf
    – mysql_user
    Commented Oct 8, 2016 at 14:11
  • Also your error log will confirm "initializing innocent buffer pool..... "
    – mysql_user
    Commented Oct 8, 2016 at 14:12

3 Answers 3

Reset to default
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 = ...

Improve this answer
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

Improve this answer
1
  • I did mention that I am using MySQL 5.6 - in MySQL 5.6, you can't change InnoDB Buffer Pool Size online.
    – MontyPython
    Commented Oct 8, 2016 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!

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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