Optimize MySQL From my-large.cnf

1. Copy the my-large.cnf which usually comes with mysql-server , usually inside the /usr/share/mysql  directory

Note: you might want to backup your existing my.cnf before doing this.

cp -p /usr/share/mysql/my-large.cnf /etc/my.cnf

Note: All values are in seconds for time and bytes for size unless otherwise specified.

Now Adjust the variables.

wait_timeout = 60

connect_timeout=10

interactive_timeout=120

join_buffer_size=1M

query_cache_size = 128M

query_cache_limit = 2M

max_allowed_packet=16M

table_cache=1024

sort_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size=4M

sort_buffer_size = 8M

key_buffer = 256M

key_buffer_size = 64M

Note : If a query has any of the following functions, it cannot be cached:

• BENCHMARK
• CURRENT_DATE
• CURTIME
• FOUND_ROWS
• LOAD_FILE
• RAND
• UNIX_TIMESTAMP
• CONNECTION_ID
• CURRENT_TIME
• DATABASE
• GET_LOCK
• MASTER_POS_WAIT
• RELEASE_LOCK
• USER
• CURDATE
• CURRENT_TIMESTAMP
• ENCRYPT
• LAST_INSERT_ID
• NOW
• SYSDATE