19

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!

Community
  • 1
giorgio79
  • 1,387
  • 7
  • 18
  • 19

6 Answers6

20

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.

Community
  • 1
RolandoMySQLDBA
  • 177,694
  • 32
  • 308
  • 507
  • Thanks, yep I did not delete them first. Just wanted to see how mysql behaves. After performing step 3 restarting worked. – giorgio79 May 30 '12 at 09:22
  • 3
    I think one shouldn't delete the log files, but rather move them elsewhere, and delete them a while later when you've successfully changed the log file size. Otherwise, you'd be in trouble if MySQL crashed before you ran `service mysqld stop`. – KajMagnus Oct 11 '13 at 07:48
4

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.

drogart
  • 636
  • 5
  • 2
3

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.
Rick James
  • 73,608
  • 4
  • 41
  • 101
2

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.

SHIVI
  • 21
  • 2
0

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...

John K. N.
  • 15,941
  • 10
  • 46
  • 103
Andro
  • 1
0

I had the same problem. here is my error log...

221123 05:05:40 mysqld_safe Starting mariadbd daemon with databases from /www/server/data
2022-11-23  5:05:40 0 [Warning] option 'max_allowed_packet': unsigned value 107374182400 adjusted to 1073741824
2022-11-23  5:05:40 0 [Note] /www/server/mysql/bin/mariadbd (server 10.7.3-MariaDB-log) starting as process 2185 ...
2022-11-23  5:05:40 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2022-11-23  5:05:40 0 [Note] InnoDB: Number of transaction pools: 1
2022-11-23  5:05:40 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2022-11-23  5:05:40 0 [Note] InnoDB: Initializing buffer pool, total size = 268435456, chunk size = 134217728
2022-11-23  5:05:40 0 [Note] InnoDB: Completed initialization of buffer pool
2022-11-23  5:05:40 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=42227,42227
2022-11-23  5:05:40 0 [ERROR] InnoDB: Missing FILE_CHECKPOINT at 42227 between the checkpoint 42227 and the end 98966.
2022-11-23  5:05:40 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2022-11-23  5:05:40 0 [Note] InnoDB: Starting shutdown...
2022-11-23  5:05:40 0 [ERROR] Plugin 'InnoDB' init function returned error.
2022-11-23  5:05:40 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2022-11-23  5:05:40 0 [Note] Plugin 'FEEDBACK' is disabled.
2022-11-23  5:05:40 0 [ERROR] Unknown/unsupported storage engine: InnoDB
2022-11-23  5:05:40 0 [ERROR] Aborting
221123 05:05:40 mysqld_safe mysqld from pid file /www/server/data/##VM-HOST-NAME##.pid ended

I changed the max_allowed_packet to 1073741824 in the config file, deleted the ib_logfile0 log file, and then restarted the SQL server. It's solved my problem.

IamProgrammerLK
  • 1
  • 1