Aditionnal recommendation (InnoDB log file size) #258

Open
acs-ferreira opened this Issue Oct 10, 2016 · 17 comments

Projects

DONE in Version 1.7

3 participants

@acs-ferreira
acs-ferreira commented Oct 10, 2016 edited

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 ?

@acs-ferreira acs-ferreira changed the title from Aditionnal recommandation (InnoDB log file size) to Aditionnal recommendation (InnoDB log file size) Oct 10, 2016
@jmrenouard
Collaborator

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.

@acs-ferreira

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
Collaborator

Hi @acs-ferreira

This test have be added.

Can you give me you feedback ?
@jmrenouard

@acs-ferreira
@SnAFKe
SnAFKe commented Oct 20, 2016

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.

@SnAFKe
SnAFKe commented Oct 20, 2016

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

@acs-ferreira

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
SnAFKe commented Oct 21, 2016

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

@acs-ferreira

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
SnAFKe commented Oct 25, 2016

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%

@jmrenouard
Collaborator

Hi @SnAFKe

can you run mysqltuner with --debug option ?

Thanks
@jmrenouard

@jmrenouard
Collaborator

Hi @SnAFKe

Very strange.
details and last code:
'innodb_buffer_pool_size' : 41943040,
'innodb_log_file_size' : 10485760,
'innodb_log_size_pct' : 25,

goodprint "InnoDB log file size / InnoDB Buffer pool size: "
. hr_bytes( $myvar{'innodb_log_file_size'} ) . "/"
. hr_bytes( $myvar{'innodb_buffer_pool_size'} ) . " should be equal 25%";

best regards,
@jmrenouard

@jmrenouard jmrenouard added a commit that referenced this issue Nov 2, 2016
@jmrenouard jmrenouard #265 TOP 15 queries with temp table to disk
#260 Memory used > 100% ?
#258
5872fcc
@jmrenouard
Collaborator

Hi

Can you give me your feeedback, please ?

Thanks

@SnAFKe
SnAFKe commented Dec 27, 2016

Sorry for late response.

Still the issue.

[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (50 %): 8.0M * 2/32.0M should be equal 25%

tunerdebug.txt

@acs-ferreira

Same issue here.

On my.cnf

innodb_log_file_size = 78M
innodb_buffer_pool_size = 312M

so 25%. Verified by:

MariaDB [(none)]> show global variables like '%innodb_log_file_size%';
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| innodb_log_file_size | 81788928 |
+----------------------+----------+
1 row in set (0.00 sec)

But on MySQLTuner:

[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (50 %): 78.0M * 2/312.0M should be equal 25%
innodb_log_file_size should be equals to 1/4 of buffer pool size (=78M) if possible.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment