Aditionnal recommendation (InnoDB log file size)

[solracsf]

Not really an issue, but a "notice", trying making things more consistent.

Here is my phpMyAdmin Advisor advice that is not present on MySQLTuner:

:: The InnoDB log file size is not an appropriate size, in relation to the InnoDB buffer pool.

Especially on a system with a lot of writes to InnoDB tables you should set innodb_log_file_size to 25% of innodb_buffer_pool_size. However the bigger this value, the longer the recovery time will be when database crashes, so this value should not be set much higher than 256 MiB. Please note however that you cannot simply change the value of this variable. You need to shutdown the server, remove the InnoDB log files, set the new value in my.cnf, start the server, then check the error logs if everything went fine. See also this blog entry

MySQLTuner Output:

General recommendations:
    Adjust your join queries to always utilize indexes
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Beware that open_files_limit (5000) variable
    should be greater than table_open_cache ( 64)
Variables to adjust:
    join_buffer_size (> 256.0K, or always use indexes with joins)
    table_open_cache (> 64)

Maybe include this on this script ?

[jmrenouard]

Hi @acs-ferreira

Thanks for this updates.

Is there any low and high limits for this size ?

I have read interesting article on Percona blog on this aspect

https://www.percona.com/blog/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/
https://www.percona.com/blog/2016/05/31/what-is-a-big-innodb_log_file_size/

I think this is not a so evident setting to used innodb buffer pool size / 4.

Thanks for your help and your subjection.

[solracsf]

As a rule of thumb, the innodb_log_file_size is supposed to be 25% of innodb_buffer_pool_size.

Larger values mean less disk I/O due to less flushing checkpoint activity.

Fine tuning these values is the MBA role, which the script isn't supposed to replace :)

Also, the posts on Percona Blog are from 2006 and 2008, so i think that things are considerably different nowadays.

Thanks.

[jmrenouard]

Hi @acs-ferreira

This test have be added.

Can you give me you feedback ?
@jmrenouard

[solracsf]

Seems OK to me. Maybe wait some more feedback before close this?

[SnAFKe]

I'm not sure if i need to add or reduce 25% but in both case i have this

Ratio InnoDB log file size / InnoDb Buffer pool size (100 %): 64.0M/64.0M should be equal 25%

Case 1

innodb_buffer_pool_size = 64M
innodb_log_file_size = 16M

Case 2

innodb_buffer_pool_size = 64M
innodb_log_file_size = 256M

Always return me

innodb_log_file_size should be equals to 1/4 of buffer pool size (= 16M) if possible.

[solracsf]

MySQL 5.5: https://dev.mysql.com/doc/refman/5.5/en/innodb-data-log-reconfiguration.html

MySQL 5.6: https://dev.mysql.com/doc/refman/5.6/en/innodb-data-log-reconfiguration.html

MySQL 5.7: https://dev.mysql.com/doc/refman/5.7/en/innodb-data-log-reconfiguration.html

[SnAFKe]

I did notice until now there is another ini overwrite my configuration that is why always getting the same innodb_log_file_size.

Now i'm getting

[OK] InnoDB log file size / InnoDb Buffer pool size: 64.0M/64.0M should be equal 25%

But not should be 16.0M/64.0M

[solracsf]

Have you followed the procedure on my previous post?
It is not simply a case of changing innodb_log_file_size to the new size and restarting MySQL...

[SnAFKe]

Do you mean innodb_fast_shutdown? yes i did and always show that.

[solracsf]

No, move old log files:

1. Set innodb_fast_shutdown = 0 with mysql -uroot -pYOURPASSWORD -e"SET GLOBAL innodb_fast_shutdown = 0"
2. Stop MySQL service mysql stop
3. Move old LOG files: mv /var/lib/mysql/ib_logfile[01] /tmp
4. Change the value of innodb_log_file_size on my.cnf file
5. Start MySQL service mysql start

[SnAFKe]

Yes i did that but with the option 1 not 0 and always return me

[OK] InnoDB log file size / InnoDb Buffer pool size: 64.0M/64.0M should be equal 25%