InnoDB innodb_log_file_size

MySQL & MariaDB Other MySQL® Questions
1

Hi,

I have a server with 16Gb RAM, 8Gb of which is used by innodb_buffer_pool_size.

Now, innodb_log_file_size is almost 25% (combined limit of innodb is 4Gb), so it’s around 1.9xGb is size.

Is this OTT (feels like it to me!)? Peter notes generally using 128Mb to 512Mb for this variable, I don’t know what size of RAM his comments are in relation to.

Also, are portions of innodb_log_file_size stored in RAM?

Cheers!

2

I cant answer your question, but take a look at the example configs that come with mysql. Theres configs for small, large and huge.
If the huge config (referring to database size and hardware) is using a large value, then its safe to assume its not OTT )

3

Thanks buddy, the huge-innodb config only has 4Gb of RAM, so it’s hard to know how things relate on a system with 4x the RAM. E.g. is it simply linear? For now I’ve set each log file at 1Gb, with a combined of 2Gb. My gut feeling is that this is high enough.

4

The InnoDB log file is not stored in RAM more than the innodb_log_buffer_size variable defines.
Usually between 1 and 8 MB.
This does usually not need to scale linearly.

The log files is generally recommended to be about 25% of the innodb_buffer_pool_size. While there is a total of 4GB for all log files for a 32bit system.

But I would also say that you can be content with 2x1G.

5

Cheers!

The 4Gb combined limit occurs on my 64Bit setup too.

6

http://www.mysqlperformanceblog.com/2007/05/09/how-to-estima te-time-it-takes-innodb-to-recover/

The 25% hint is IMHO related to older setups (not everybody can afford 16G RAM in a server )) So virtually having files > to 1GB would cause recovery and shutdown times to be extremely slow and inefficient.