Here we are going to discuss some basic MySQL configuration parameter for tuning MySql
max_connection defines the maximum number of simultaneous client connections. Default value is set to 100, you can start with a low value like 20 or 50 and increase them as your application grows.
table_cache determines how many open file descriptors MySQL will cache, recommended value for table_cache is max_connection * N where N is the maximum number of tables per join in any of the queries you execute plus some additional for temporary tables and files.
max_allowed packet defines maximum size data packet can be in MySQL. If you have large data dumps being imported or have blob fields which are large this may be raised. The largest possible packet that can be transmitted to or from a MySQL 5.5 server or client is 1GB.
tmp_table_size defines the maximum size a temporary table can be if this size is reached then temporary table will be created as a file and placed on disk instead of handling in memory.
max_heap_table_size defines maximum memory table can grow before it is placed on disk.
query_cache_size is a variable that defines how much memory is allocated for caching a query, query caching only caches the query and its internal execution methods it does not cache results of the query. Queries that are cached don’t have to be reevaluated by MySQL for execution methods thus increasing the speed of a query.
Keep in mind MySQL query caching is case sensitive so ‘select * from employee’ is not similar to ‘SELECT * FROM EMPLOYEE’ infact be careful about the extra spaces too :-).
query_cache_limit defines how big a query and its execution method can be and remain in the query cache.
innodb_buffer_pool_size = 16M
innodb_buffer_pool_size defines the amount of memory that is dedicated to caching innodb data and indexes and it should be at least the size of the_ innodb index space_ or larger, more data is indexed in Ram that means less disk access and faster query returns hence increasing the performance.
innodb_buffer_pool_size is one the most important variable to improve performance of MySql. We recommend reading this article for better understanding of this variable. Choosing innodb_buffer_pool_size
In the next article we’ll discuss how to use tuning-primer tool to determine current usage of index, memory etc and adjusting the values as recommended.