# Try number of CPU's*2 for thread_concurrency
Query caching was added as of MySQL version 4, the following three directives will greatly enhance mysql server performance.
Query caching is a server wide variable, so set these generous. I have found the above levels are generally best if you server has at least 512 ram. If you run a server just for DBs with a lot of ram, you can up these quite a bit, like 2m limit and a 64+M cache size.
The key buffer is a variable that is shared amongst all MySQL clients on the server. A large setting is recomended, particularly helpful with tables that have unique keys. (Most do)
The next set of buffers are at a per client level. It is important to play around with these and get them just right for your machine. With the setting below, every active mysql client will have close to 3 MB's in buffers. So 100 clients = almost 300 MB. Giving too much to these buffers will be worse than giving too little. Nothing kills a server quite like memory swapping will.
The following directive should be set to 2X the number of processors in your machine for best performance.
Heres a few example configurations for servers running MySQL and web for common memory sizes. These are not perfect, but good starting points.
Server with 512MB RAM:
For servers with 1 GB ram:
For optimizing mysql, first we need to know the values of mysql variables and status.
The following are some commands used for this purpose:
# mysqladmin processlist extended-status
mysql> show status;
mysql> show variables;
To get more specific answer, the commands can be enhanced a little more like as follows:
mysql> show status like '%Open%_tables';
mysql> show variables like 'table_cache';
1. The most important variables in mysql are table_cache and key_buffer_size
a) Run the above two commands and check Open_tables and Opened_tables
If Opened_tables is big, then your table_cache variable is probably
So increase the table_cache variable. Open /etc/my.cnf and change/add table_cache=newvalue
b) Run the following commands to check key_buffer_size, key_read_requests and key_reads
mysql> show variables like '%key_buffer_size%';
mysql> show status like '%key_read%';
If key_reads / key_read_requests is < 0.01, key_buffer_size is enough. Otherwise key_buffer_size should be increased.
Also run the following command to check key_write_requests and key_writes
mysql> show status like '%key_write%';
If key_writes / key_write_requests is not less than 1 (near 0.5 seems to be fine), increase key_buffer_size.
Check the total size of all .MYI files. If it is larger than key_buffer_size change key_buffer_size to total size of MYI files.
2. Wait_timeout, max_connection, thread_cache
If you want to allow more connections, reduce wait_timeout to 15 seconds and increase max_connection as you want.
Check the number of idle connections. If it is too high reduce the wait_timeout and use Thread_cache
How many threads we should keep in a cache for reuse. When a client disconnects, the client's threads are put in the cache if there aren't more than thread_cache_size threads from before. All new threads are first taken from the cache, 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 doesn't give a notable performance improvement if you have a good thread implementation.) By examing the difference between the Connections and Threads_created you can see how efficient the current thread cache is for you.
If Threads_created is big, you may want to increase the
thread_cache_size variable. The cache hit rate can be calculated with
Default thread_cache_size may be 0 if so increase it to 8.
You may try this formula : table_cache = opened table / max_used_connection