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:
Many people make MySQL installations and do not care about the parameterization. The parameterization is important, one BD can work well (for now) with the default parameters, problems arise when the database grows or increases their workload.
In this post we will discuss the parameters that can affect the performance of any motor (the most used are InnoDB and MyISAM).
The variables are used by the server to size structures important memory for good engine performance of BD, states server will indicate if the variables we’ve defined are actually causing a positive effect or otherwise not suitable for absolutely nothing.
During a default installation of MySQL, a common mistake is not changing the settings as stored data in InnoDB engine. By default, all information is stored in one datafile, which is usually named:
To perform a tuning of MySQL, you must first become familiar with a few concepts, MySQL has variables and states:
- A variable has an associated value can be set in my.cnf (my.ini in Windows) or just set a default implicitly, although it is possible to modify some variables at runtime (care that is not the change is reflected in the configuration files). According to the version that we are using can have more than 300 (as with version 5.5). It is possible to know the value of all the variables:
In a production environment, it is recommended enabled the slow query log for analyzing occasionally.
Activation is performed in the parameter file used (usually my.cnf) adding within the section
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 10