MySQL system variables

Important MySQL System Variables

MySQL is mostly used open source database which is very fast and effective when you perfectly configure it.  In this article I will discuss with some important MySQL system variables which mostly affect MySQL performance.  Properly tuned important MySQL system variables will give you a great database experience.  We know that MySQL has multiple database engine among them mostly used are MyISAM and InnoDB. Some variables are specific engine related and some are applicable for all.

1. innodb_buffer_pool_size :  The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables.  The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated database server, you may set this to up to 80% of the machine physical memory size. However, do not set it too large because competition for physical memory might cause paging in the operating system. MyISAM engine table has no effect for this variable. If your all tables are MyISAM you should set it very low.

2. Key_buffer_size : The key_buffer_size indicates the size of the key cache that MySQL uses to store indexes in memory. The cache stores index blocks in memory to avoid reading the disk repeatedly. The key_buffer_size is one of the most important variables to tune to improve MySQL database performance. The index blocks of MyISAM tables are stored in the key cache and are accessible to all processes which use MySQL globally. InnoDB engine table has no effect for this variable. Keep its value very low if your all tables are InnoDB.  Just opposite innodb_buffer_pool_size

3. tmp_table_size : The maximum size of internal in-memory temporary tables. (The actual limit is determined as the minimum of tmp_table_size and max_heap_table_size.) If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table. The on-disk table is expensive and affects your database performance. The temporary table may be created when you do many advanced GROUP BY queries, multiple table joining without proper indexing.

4. max_heap_table_size:  This variable sets the maximum size to which user-created MEMORY tables are permitted to grow.  This variable is used in conjunction with tmp_table_size to limit the size of internal in-memory tables.  The tmp_table_size and max_heap_table_size shoule be same value and they are generally set to 16MB to 32MB.

5. query_cache_size:  The amount of memory allocated for caching query results. The bigger size can cache more result set. So,  query result  for a SQL which result set has already been cached will be served from memory and response time will be very fast. For medium size database you should set it 16 to 32 MB.

6. query_cache_type:  Set the query cache type which should be set 1. It is 0 means query cache is disabled. So, to active query cache  query_cache_type should be 1 and query_cache_size should be an reasonable size.

7.   query_cache_limit : Do not cache results that are larger than this number of bytes. You can set it 1MB

8. table_open_cache : The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. You can check whether you need to increase the table cache by checking the Opened_tables status variable.  If the value of Opened_tables is large and you do not use FLUSH TABLES often (which just forces all tables to be closed and reopened), then you should increase the value of the table_open_cache variable.
9. thread_cache_size: How many threads the server should cache for reuse. When a client disconnects, the client’s threads are put in the cache if there are fewer than thread_cache_size threads there. Requests for threads are satisfied by reusing threads taken from the cache if possible, and only when the cache is empty is a new thread created. This variable can be increased to improve performance if you have a lot of new connections. Normally, this does not provide a notable performance improvement if you have a good thread implementation. You can use the formula:  8 + (max_connections / 100) to set its value.

The above mentioned MySQL System variables are mostly used to tune MySQL. So, always check these important MySQL system variables are properly configured. What value of those variables you should use depends on a lot of factors. Other MySQL system variables which are not described here , don’t affect MySQL as much as these MySQL system variables do.