Tunning / Optimizing my.cnf file for MySQL in Linux

While optimizing our mysql server, we should know about the parameters and the value assign to it. I am explaining the parameters and the appropriate value that should assign to it.

1. query_cache_size: In a situation where the database has to repeatedly run the same queries on the same data set, returning the same results each time, MySQL can cache the result set, avoiding the overhead of running through the data over and over and is extremely helpful on busy servers.

2. key_buffer: The value of key_buffer_size is the size of the buffer used with indexes. The larger the buffer, the faster the SQL command will finish and a result will be returned. The rule-of-thumb is to set the key_buffer_size to at least a quarter, but no more than half, of the total amount of memory on the server. Ideally, it will be large enough to contain all the indexes (the total size of all .MYI files on the server).

3. sort_buffer: The sort_buffer is very useful for speeding up myisamchk operations (which is why it is set much higher for that purpose in the default configuration files), but it can also be useful everyday when performing large numbers of sorts.

4. read_buffer_size: If you want fast full table scans for large table you should set this variable to some high value.

5. read_rnd_buffer_size: The read_rnd_buffer_size is used after a sort, when reading rows in sorted order. If you use many queries with ORDER BY, upping this can improve performance. It defaults to the same size as the read_buffer_size. A rule-of-thumb is to allocate 1KB for each 1MB of memory on the server, for example 1MB on a machine with 1GB memory.

6. thread_concurrency: This function enables applications to give the threads system a hint about the desired number of threads that should be run at the same time.

7. table_cache: 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. MySQL, being multi-threaded, may be running many queries on the table at one time, and each of these will open a table. Examine the value of open_tables at peak times. If you find it stays at the same value as your table_cache value, and then the number of opened_tables starts rapidly increasing, you should increase the table_cache if you have enough memory.

========
query_cache_size=64M ## 32MB for every 1GB of RAM

key_buffer=256M ## 128MB for every 1GB of RAM

sort_buffer_size=2M ## 1MB for every 1GB of RAM

read_buffer_size=2M ## 1MB for every 1GB of RAM

read_rnd_buffer_size=2M ## 1MB for every 1GB of RAM

thread_concurrency=8 ## Number of CPUs x 2

table_cache=64 ## default
========

Thank you.