mysql wont start after increasing innodb_buffer_pool_size and innodb_log_file_size

Posted on

Question :

I am following this solution here https://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#comment14041132_4056261 and tried to increase my innodb_buffer_pool_size to 4G and later 1G (also 1024M) in addition to the log file size, but mysql wont start with those values.
If I put it back to 512M mysql starts fine.

How can I solve this? My server is a 16GB one, and according to Webmin sysinfo:

Real memory 15.62 GB total, 3.13 GB used

Meanwhile I found the error log as well:

120529 10:29:32 mysqld_safe mysqld from pid file
/var/run/mysqld/mysqld.pid ended

120529 10:29:33 mysqld_safe Starting mysqld daemon with databases from
/var/lib/mysql

120529 10:29:33 [Note] Plugin ‘FEDERATED’ is disabled.

120529 10:29:33 InnoDB: The InnoDB memory heap is disabled

120529 10:29:33 InnoDB: Mutexes and rw_locks use GCC atomic builtins

120529 10:29:33 InnoDB: Compressed tables use zlib 1.2.3

120529 10:29:33 InnoDB: Using Linux native AIO

120529 10:29:33 InnoDB: Initializing buffer pool, size = 1.0G

120529 10:29:33 InnoDB: Completed initialization of buffer pool

InnoDB: Error: log file ./ib_logfile0 is of different size 0 134217728
bytes

InnoDB: than specified in the .cnf file 0 268435456 bytes!

Answer :

The two answers given from @RickJames and @drogart are essentially the remedies. (+1 for each).

Right from the error log you present, the last two lines say:

InnoDB: Error: log file ./ib_logfile0 is of different size 0 134217728 bytes

InnoDB: than specified in the .cnf file 0 268435456 bytes! `

At that point, it was evident that you set innodb_log_file_size to 256M (268435456) in my.cnf while the InnoDB Transaction Logs (ib_logfile0,ib_logfile1) were respectively 128M (134217728) each. Looking back at the link to my StackOverflow answer in your question, you had to do the following:

Step 01) Add this to my.cnf:

[mysqld]
innodb_buffer_pool_size=4G
innodb_log_file_size=1G

Step 02) Run these command in the OS

mysql -u... -p... -e"SET GLOBAL innodb_fast_shutdown = 1"
service mysql stop
rm -f /var/lib/mysql/ib_logfile*
service mysql start

So as to have confidence in what is happening, run tail -f against the error log. You will see message telling you when each innodb log file is being created.

Based on the error in the log, I’m guessing you did this:

  • shut down mysql
  • edited my.cnf to change the innodb log file size
  • tried to start mysql (then it failed)

If you change the log file size, you need to remove the old log files. Innodb will not start successfully if the existing files do not match the specified size in the config file. If you move them elsewhere, innodb will create new transaction log files of the correct size when it starts.

I would recommend moving the old files to another directory instead of just deleting them, until the server is up and running with new log files and everything looks OK.

The buffer_pool should be set to about 70% of available RAM if you are running InnoDB only.

The log size does not matter a lot. The optimal is to set it so that (Uptime * innodb_log_file_size / Innodb_os_log_written) is roughly 3600 (1 hour).

To change the log size, one must

  1. shut down mysqld cleanly
  2. delete the value in my.cnf (my.ini)
  3. delete the log files
  4. retstart — new log files will be rebuilt.

There can also be some issue in your value provided for buffer pool size. like it happened in my case…

When increasing or decreasing innodb_buffer_pool_size, the operation is performed in chunks. Chunk size is defined by the innodb_buffer_pool_chunk_size configuration option, which has a default of 128M. For more information, see Configuring InnoDB Buffer Pool Chunk Size.

Buffer pool size must always be equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. If you configure innodb_buffer_pool_size to a value that is not equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances, buffer pool size is automatically adjusted to a value that is equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances that is not less than the specified buffer pool size.

In the example, innodb_buffer_pool_size is set to 8G, and innodb_buffer_pool_instances is set to 16. innodb_buffer_pool_chunk_size is 128M, which is the default value.

8G is a valid innodb_buffer_pool_size value because 8G is a multiple of innodb_buffer_pool_instances=16 * innodb_buffer_pool_chunk_size=128M, which is 2G.

The issue is most likely with the size you chosen to increase the logs by.

In my example Confluence recommends a log size of 2GB but when I made this change, even if I delete the old logfiles the service will not start. I reduced it down to 1GB in size and the service started without any issues…

Leave a Reply

Your email address will not be published. Required fields are marked *