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:

Source   
mysql> SHOW VARIABLES;

Or the value of a particular with:

Source   
mysql> SHOW VARIABLES LIKE 'version';
+---------------+-----------------------+
| Variable_name | Value |
+---------------+-----------------------+
| version | 5.5.18 |
+---------------+-----------------------+
1 row IN SET (0.00 sec)
  • A state is for example the number of seconds that the server is started, the number of states is also dependent on the version more than 300 for the 5.5 vesion. Access to the states is performed similarly to the variables:
Source   
mysql> SHOW STATUS LIKE 'Uptime';
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| Uptime | 1529023 |
+---------------+---------+
1 row IN SET (0.00 sec)

More information about syntax in: http://dev.mysql.com/doc/refman/5.5/en/show-status.html

Documentation of states in: http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html

Keep in mind that both the variables and states can be global in scope or session, you can consult a state or a variable globally with:

Source   
mysql> SHOW global VARIABLES;
mysql> SHOW global STATUS;

It is highly recommended reading for all states, even faster, and all variables. You can find very interesting relationships between variables and states, for example to see if we have properly sized buffer.

This will be in another post …

Leave a Reply