2

I have following environment:

# cat /etc/redhat-release 
CentOS Linux release 7.2.1511 (Core) 
# rpm -qa | grep ^mysql-community-
mysql-community-common-5.5.48-2.el7.x86_64
mysql-community-libs-5.5.48-2.el7.x86_64
mysql-community-client-5.5.48-2.el7.x86_64
mysql-community-release-el7-5.noarch
mysql-community-server-5.5.48-2.el7.x86_64
# 

I need to adjust innodb_buffer_pool_size value for mysql, however last time when I try to do that, something went south and I end up restoring database from backup( database is ~100G, so ideally I wouldn't want to go that route again.

steps that I took last time:

  • stop mysqld
  • adjust value of innodb_buffer_pool_size inside of my.cnf
  • killed ib_logfile*
  • start mysqld

What did I do wrong? What should I do different this time?

3

Changing innodb_buffer_pool_size doesn't need you to nuke ib_logfiles. This could easily be the reason for you had to restore the database (*).

Just stop mysql -> change the settings in my.cnf -> start mysql.

(*) ib_logfiles are holding recovery data, pages which were not yet checkpoint to the tablespace. If you remove them without making sure you have a clean shutdown you risk losing data and corrupting your database.

2
  • how do I force mysql to save everything from logs into data file(s)? – alexus Apr 6 '16 at 15:38
  • You cannot really "force" it but you can change the global variable innodb_max_dirty_pages_pct to 0: set global innodb_max_dirty_pages_pct=0. This will trigger more aggressive checkpointing. This is a common practice to stop mysql "safely" with least amount of downtime. – Károly Nagy Apr 6 '16 at 19:48
1

Do not delete the iblog files. You probably have innodb_fast_shutdown = 1, which lets you shutdown fast, at the expense of some extra effort on startup. The files are needed.

  1. Change my.cnf - add or increase setting for innodb_buffer_pool_size in the [mysqld] section.
  2. Stop mysqld
  3. Start mysqld

Generally 70% of available RAM is a good value.

Version 5.7.5 implemented dynamic resizing of innodb_buffer_pool_size. With this version (or later), you can do

  1. Change my.cnf so that it will be in effect after any restart.
  2. SET GLOBAL... while the system is running.

No bounce is needed. The change in size will be gracefully performed in the running system.

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.