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.

In the post “MySQL variable states” variables and server status are discussed, but quickly going over to see the value of a variable (total value not in session):

Source   
mysql> SHOW global VARIABLES LIKE 'table_open_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 400   |
+------------------+-------+
1 row IN SET (0.00 sec)

If you omit the like displays all variables. We can change the value of a variable with:

Source   
SET global table_open_cache=600;

For a state of the server:

Source   
mysql> SHOW global STATUS LIKE 'Uptime';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 194   |
+---------------+-------+
1 row IN SET (0.00 sec)

We can change the value of a variable but not a state server. The changes are the values of a variable dynamically are purely experimental, if we want change we have to be persistent add the corresponding parameter in the my.cnf file.

Documentation of all the variables (and their corresponding parameters for the my.cnf file) can be found in:

http://dev.mysql.com/doc/refman/5.5/en/dynamic-system-variables.html

 

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

Let’s address the issue from the server status and see how we can improve those states modifying variables.

States or relationships affecting any engine:

  • Opened_tables, if this value is too high or rapidly increases (and nothing is doing FLUSH TABLES) may have to increase the value of the variable:

table_open_cache, indicates the number of tables that can be open in cache.

  • Created_tmp_disk_tables/Created_tmp_tables => ideal 0, the statements used in the relationship created temporary tables show disk and temporary total respectively tables. If the ratio moves away from the ideal we must increase the variable:

tmp_table_size, maximum size of temporary tables in memory.

  • Sort_merge_passes, undicates the number of passes of “merge” type that has had to make the sorting algorithm. If the value increases quickly in time we need to increase the variable:

sort_buffer_size, sort buffer per session.

  • Qcache_free_blocks*100/Qcache_total_blocks, only the cache of results (variable query_cache_size> 0), occupancy rate of output cache if you have enabled. If we have a 100% increase variable:

query_cache_size, amount of memory to cache results.

  • Qcache_lowmem_prunes, only the cache of results (variable query_cache_size> 0) if you have activated, indicating the number of queries that have been evicted from the cache by insufficient memory, we must increase the varaible:

query_cache_size, amount of memory to cache results.

  • Threads_created*100/Connections, this ratio indicates the% of cache misses in creating threads, if the value is high is to increase variable:

thread_cache_size, indicating how many threads is to keep the server as cache to be reused.

What has been discussed in this entry is indifferent engine using the DBs tables in next posts I will try tuning InnoDB and MyISAM. But in any case it described a common task of tuning, which must be made and the specific engine we use.

I hope you find it useful…

Leave a Reply