Tuning MySQL

If tmp_table_size is not equal to max_heap_table_size then change them to be the same.

If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size value to cause 
temporary tables to be memory-based instead of disk-based.

select @@global.tmp_table_size, @@global.max_heap_table_size

If opened tables is big, your table cache value is probably too small (defalt 64).

# Checked opened tables and adjusted accordingly after running for a while.
table_cache=512

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

Turn on the query_cache_size, default is zero which is off.

Some dynamic commands for tuning:
mysql> SET GLOBAL query_cache_size = 8192000;
mysql> SET GLOBAL query_cache_limit = 102400;
mysql> SHOW VARIABLES LIKE 'query_cache_size';
mysql> SHOW STATUS LIKE 'Qcache%';

Change Settings for Glogal (all but current session)

Modify the contents of the /etc/my.cnf file under the [mysqld] section.

Here are my.cnf values I use for 100 active concurrent users.

tmp_table_size=524288000
max_heap_table_size=524288000
table_cache=768
set-variable=max_connections=300
wait_timeout=7800
query_cache_size = 12582912

query_cache_limit=80000

thread_cache_size = 4
join_buffer_size = 128K

You can also change some of these from the command prompt:

mysql> Set global thread_cache_size = 4;

mysql> Set global query_cache_size = 64*1024*1024;

mysql> Set global query_cache_limit=768*1024;

mysql> Set global query_cache_min_res_unit = 2048;

mysql> Set long_query_time = 5;

mysql> Set log-slow-queries=/var/log/mysql/log-slow-queries.log;

mysql> Set global wait_timeout = 7800;

mysql> Set global key_buffer_size = 10*1024*1024;

mysql> Set global table_cache = 800;

      Will clear your status and start logging with fresh data

External Links

• Tuning / Optimizing my.cnf file for MySQL
• MySQL Query Cache

• How to Optimise MySQL