MySQL tuning variables, parameters for InnoDB

Continuing the tuning MySQL will now play specifically InnoDB engine.

This post is linked to the “MySQL tuning parameters for any engine“, we will work from states and according a result see that variables can be modified to improve results.

In the post “MySQL variables states” already worked with variables and states.

Documentation of all the variables can be found in:
http://dev.mysql.com/doc/refman/5.5/en/dynamic-system-variables.html

And for states:
http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html

States or relationships affecting INNODB:

  • Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total*100, page percentage occupied by the buffer pool. If we have a 100% if possible we should increase the value of the variable:

innodb_buffer_pool_size

  • Innodb_log_waits/Innodb_log_write_requests*100, percentage of the number of times that the log buffer is too small for writing an operation and there has been a wait for writing in the log file. If the value is small need to expand the value of the variable:

innodb_log_file_size

  • (Innodb_rows_deleted+Innodb_rows_inserted+Innodb_rows_updated)/Innodb_rows_read*100, percentage modifications produced in the database, this value can be useful to highlight the futility of some really big log files.

Also in a production environment is highly recommended to have set the variables:

  • innodb_flush_log_at_trx_commit=1, value 1 lmakes a flush of log_buffer makes at each transaction commit, it is safest (0-every second, 2-makes a flush of the log buffer but not guaranteed to be written to the log file at that time)
  • innodb_file_per_table, a tablespace per table. This is necessary because otherwise we will have all the data in all tables of all DBs in the same tablespace … that fear !!! For more details see the entry “MySQL InnoDB file per table

Leave a Reply