Changing innodb_buffer_pool_size on the fly

Question

I'm wondering if it's possible to change the innodb_buffer_pool_size on the fly.

It looks like the buffer pool has been over allocated on my server and it's swapping now. I've changed this value on the slave server, restarted the MySQL daemon and everything looks fine now - however I can't take the master server offline

I'm not sure if I can at least flush the inndb buffer to free up some memory?

# ps aux --sort -vsz | head -n2
USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
mysql     2064 14.6 93.8 17369060 15328044 ?   Sl   Jul22 7096:48 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysql/mysql-error.log --pid-file=/var/lib/mysql/mysql.pid --socket=/var/lib/mysql/mysql.sock --port=3306

# free -m
                 total       used       free     shared    buffers     cached
    Mem:         15949      15786        163          0          4        104
    -/+ buffers/cache:      15677        272
Swap:         4095        346       3749

Answer 1

In MySQL 5.7 it is now possible to modify the innodb_buffer_pool size on the fly dynamically:

15.4.3.2 Configuring InnoDB Buffer Pool Size

The new pool size must be a multiple of:

innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances

or will use the next highest multiple, if set to an invalid number.

Answer 2

I'm wondering if it's possible to change the innodb_buffer_pool_size on the fly.

You can't. innodb_buffer_pool_size is not a dynamic variable.

I'm not sure if I can at least flush the inndb buffer to free up some memory?

Troubleshooting MySQL Memory Usage may be useful.