Optimizing MySql

You may choose optimize the MySQL sserver 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. Now, 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 level of optimization, you must fine tune your MySQL server based on the 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.

    You can check whether your system needs to have the table_cache value increased by checking the open_tables and opened_tables status variables during peak time. Use the command:

    SHOW STATUS LIKE “open%tables%”;

    open_tables is the number of tables opened in cache, whereas opened_tables is the total number of tables open. Since MySQL supports multi-threading, several queries might be executed on the same table at the same time. So each of these queries will open a table.

    Default value for table_cache is 64. If you have enough RAM available on your server, you can increase the table_cache value. This will reduce total number of tables open by moving those tables to cache.

  • query_cache_size: If you have a MySQL query that is being executed repeatedly by your website, MySQL can be set to cache the results of this query.

    You can enable query caching by setting the server variable query_cache_type to 1 and setting the cache size in the variable query_cache_size. If either of the above is set to 0, query caching will not be enabled.

  • key_buffer_size: This is the size of buffer used by all the indexes. Ideally, it should be set to at least a quarter of the memory available or more.

    The optimum solution is to keep the ratio as follows:

    • Key_reads : Key_read_requests should be 1 : 100 and Key_writes / Key_write_requests should always be less than 1.

    • If the Key_reads value is high compared to Key_read_requests, you need to increase key_buffer_size.

    You can get the value of these variables using the command:

    SHOW GLOBAL STATUS where Variable_name like “Key_%”; +————————+———–+
    | Variable_name | Value |
    +————————+———–+
    | Key_blocks_not_flushed | 0 |
    | Key_blocks_unused | 48394 |
    | Key_blocks_used | 8078 |
    | Key_read_requests | 973911676 |
    | Key_reads | 54135 |
    | Key_write_requests | 824911 |
    | Key_writes | 739554 |
    +————————+———–+

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

    Sufficient size of sort_buffer_size allows the sort operations to be performed in memory cache rather than in temp files in hard disks.

  • thread_cache_size: If your server has large traffic on MySQL server, then the server will create a lot of new threads at a very high rate. This may take up a lot of CPU time. When a connection disconnects, the threads are put in the cache and a new thread is taken from this cache.

    If the value of the Threads_created status variable is large, you may want to increase value for the thread_cache_size system variable. The cache hit rate can be calculated using the commands:

    SHOW GLOBAL STATUS where Variable_name like “Connections”;

    SHOW GLOBAL STATUS where Variable_name like “Threads_created”;

  • read_rnd_buffer_size: This is used after a sort operation, to read the rows in the sorted order. If your application has a lot of queries with ORDER BY, increasing value of this variable can improve the performance. This buffer is also at a per client basis. The default value for read_rnd_buffer_size is 128K. General rule is to allot 1MB of read_rnd_buffer_size for every 1GB memory in server.

  • tmp_table_size: Sometimes, a temporary table needs to be created for executing a statement. This variable determines the maximum size for a temporary table in memory.

    Always try to avoid temporary table creation by optimizing your query. But if it is unavoidable, make sure that the table is created in the memory. If the memory is not sufficient, a MyISAM table will be created in the disk. If a large number of tables are created in the disk, you need to increase your tmp_table_size. You can also check the status variables Created_tmp_disk_tables and Created_tmp_tables. Created_tmp_disk_tables is the number of temporary tables created on disk while executing a statement, while Created_tmp_tables is the number of in-memory tables created.

    Use the commands:

    SHOW GLOBAL STATUS where Variable_name like “Created_tmp_disk_tables”;

    SHOW GLOBAL STATUS where Variable_name like “Created_tmp_tables”;