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

Continue reading

MySQL tuning parameters for any engine

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.

Continue reading

MySQL InnoDB file per table

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:

Source   
ibdata1

Continue reading

MySQL variables states

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:

Continue reading

Enabling MySQL slow query log or without indexes

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

[mysqld]:

Source   
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 10
log-queries-not-using-indexes

Continue reading