You can choose to optimize the MySQL server at a basic level or at an advanced level.

Basic Optimization MySQL Server can be optimized at a basic level using the MySQL tuner script.

1. Download the script using the command:

   wget https://github.com/major/MySQLTuner-perl/zipball/master

2. Run the commands:

   unzip master cd major-MySQLTuner* chmod +x mysqltuner.pl perl mysqltuner.pl

The script will check the status of MySQL and update you with the variables that you need to tweak to optimize MySQL.

Advanced Optimization For advanced optimization, fine-tune your MySQL server based on applications and resource utilization. Below are some important system variables that need to be tweaked for normal use.

• table_cache: Each time MySQL accesses a table, it stores the table in the cache. Data can be retrieved faster from frequently accessed tables if they are stored in cache. Check if your system needs an increased table_cache value by checking the open_tables and opened_tables status variables during peak time. Use the command:

  SHOW STATUS LIKE "open%tables%";

• query_cache_size: Cache the results of frequently executed queries by setting the server variable query_cache_type to 1 and the cache size in query_cache_size. Query caching will not be enabled if either of the above is set to 0.

• key_buffer_size: The size of the buffer used by all indexes. Ideally, it should be set to at least a quarter of the available memory or more. The optimum ratio should be Key_reads: Key_read_requests = 1:100 and Key_writes / Key_write_requests < 1. If Key_reads is high compared to Key_read_requests, increase key_buffer_size. Get these values using:

   

  SHOW GLOBAL STATUS where Variable_name like "Key_%";

• sort_buffer_size: Improves large and complex sorts. Increase this value for faster ORDER BY or GROUP BY operations. The default value is 2MB.

• thread_cache_size: If your server has large traffic on MySQL, the server will create many new threads, consuming CPU time. When a connection disconnects, the threads are put in the cache. Increase thread_cache_size if Threads_created status variable is large. Calculate cache hit rate using:

  SHOW GLOBAL STATUS where Variable_name like "Connections"; SHOW GLOBAL STATUS where Variable_name like "Threads_created";

• read_rnd_buffer_size: Used after a sort operation to read rows in sorted order. Increase this variable for applications with many ORDER BY queries. Default value is 128K. General rule is to allot 1MB for every 1GB of server memory.

• tmp_table_size: Determines the maximum size for a temporary table in memory. Avoid temporary table creation by optimizing your query. Ensure the table is created in memory. If many tables are created on disk, increase tmp_table_size. Check Created_tmp_disk_tables and Created_tmp_tables status variables using:

  SHOW GLOBAL STATUS where Variable_name like "Created_tmp_disk_tables"; SHOW GLOBAL STATUS where Variable_name like "Created_tmp_tables";