Basic MySQL Configuration Parameters

Here we are going to discuss some basic MySQL configuration parameter for tuning MySql

max_connection=50

maxconnection_ 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=2048

tablecache_ determines how many open file descriptors MySQL will cache, recommended value for tablecache is maxconnection * 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=16M

maxallowed 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=64M

tmptablesize 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=64M

maxheaptablesize_ defines maximum memory table can grow before it is placed on disk.

query_cache_size=32M

querycachesize 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=64M

querycachelimit 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 innodbbufferpool_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.