MySQL Server performance tuning

Tuning the following parameters in mysql configuration file were found very efficient. These below parameters should be configured in my.cnf(linux) and my.ini(windows)

 

table_cache

It changes the maximum number of files the server keeps open. Opening tables is expensive.

Each time MySQL accesses a table, it places it in the cache. If the system accesses many tables, it is faster to have these in the cache

table_cache is related to max_connections. For example, for 200 concurrent running connections, its optimal to have a table cache size of at least 200 * N, where N is the maximum number of tables per join in any of the queries which you execute. You must also reserve some extra file descriptors for temporary tables and files.

sort_buffer_size

It is the buffer memory used to process the sort queries. It has negative impact if increased too much with low volume of data. Optimum size is 2MB .

read_buffer_size

It is the buffer memory used to process the sort queries. It has negative impact if increased too much with low volume of data. Optimum size is 2MB .

thread_cache_size

Thread creation/destructions can be expensive, which happen at each connect/disconnect.

query_cache_size

Increasing query_cache_size improves the performance while executing frequent read queries.

thread_concurrency

No of threads executing at a time. It should be tuned based on the CPU the application is deployed.

optimum value=number of CPU's*2

innodb_flush_log_at_trx_commit

This determines the frequency of log flushing to disk.

If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit.

When the value is 1, the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file.

When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2.

innodb_buffer_pool_size
The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. The default value is 8MB. The larger this value is set, the less disk I/O is needed to access data in tables. On a dedicated database server, it can be set up to 80% of the machine physical memory size.