Support / KnowledgeBase

 
Search the KnowledgeBase Search

Making it Better: Basic MySQL performance tuning (MySQLd)

  • Applies to: (dv) 4.0, All DV, DV, Grid

  • Difficulty: Hard

  • Time needed: 15 minutes

  • Tools needed: root, SSH, vi knowledge

 
  • Applies to: Grid
    • Difficulty: Easy
    • Time Needed: 10
    • Tools Required: AccountCenter access, phpMyAdmin access
  • Applies to: All DV
    • Difficulty: Medium
    • Time Needed: 20
    • Tools Required: SSH, root, vi knowledge

Introduction

The following is just one simple way to get started with optimizing your MySQL databases.

Requirements

Be sure to back up your database before continuing with this article.

READ ME FIRST

This article is provided as a courtesy. Installing, configuring, and troubleshooting third-party applications is outside the scope of support provided by (mt) Media Temple. Please take a moment to review the Statement of Support.

CloudTech_ProductPage_217x57

CloudTech Can Help!

If you’re having trouble with the steps in this article, additional assistance is available via CloudTech, our premium services division. Our expert engineers can tweak and tune your server for optimal performance. For more information on what CloudTech can do for you, please click here.

Instructions

  1. Log into your AccountCenter.
  2. Click on your primary domain.
  3. Click on Manage Databases.
  4. Click on Admin for the database you wish to optimize.
  5. This will open in a new window/tab of your browser. Log into phpMyAdmin with your MySQL username and password.
  6. Click on the database name on the left hand side frame (not the table, the database name).
  7. On the right hand column, your tables should be listed. Scroll down till you see the Check all link.
  8. Click on that link, make sure all database tables are checked and then from the dropdown next to it, carefully select Optimize table.

This should perform an analysis, repair, and an optimization of your database. Another fairly certain method of fixing your database/tables with phpMyAdmin is to export your data, delete your existing tables, and then re-import. PhpMyAdmin performs certain optimization tasks on the data that may be very beneficial for your databases. You can find even more MySQL Optimization tips at dev.mysql.com.

TIP:

If you haven't already purchased one already, you may want to consider getting a MySQL GridContainer add-on. The MySQL GridContainer gives your databases dedicated resources, most importantly RAM (memory). The container will also provide two very useful tools in finding ways to better optimize your database. These tools are the Query Analyzer and MySQL Report. For more details regarding the MySQL GridContainer, please visit our website.

DV - Making it Better

This article is part of the DV server - Making it Better series, which details advanced enhancements and customizations to your DV server.

This article is provided as a courtesy. Installing, configuring, and troubleshooting third-party applications is outside the scope of support provided by (mt) Media Temple. Please take a moment to review the Statement of Support.

CloudTech_ProductPage_217x57

CloudTech Can Help!

If you’re having trouble with the steps in this article, additional assistance is available via CloudTech, our premium services division. Our expert engineers can tweak and tune your server for optimal performance. For more information on what CloudTech can do for you, please click here.

Overview

DV servers do not have any MySQL modifications when they are initially provisioned. In fact, the my.cnf file that is included as part of the database server's configuration includes multiple deprecated directives. Although this article will actually increase the memory used by MySQL the performance gain can be dramatic depending on your queries and database usage. The average user will get more mileage out of the server's resources with MySQL changes similar to the following.

CAUTION:

With that said, it should always be noted that this can not be guaranteed to be a one-size-fits-all solution. It is possible that these recommendations are not ideal for a specific server configuration. More information on tuning specific situations can be found at the bottom of this article.

Requirements

Before you start, this article has the following dependencies:

Instructions

Tuning MySQL based on available RAM

  1. Create a backup of your MySQL config:
    cp /etc/my.cnf /etc/my.cnf.YYYY-MM-DD.bak
    

    (Remember to replace YYYY-MM-DD with today's date, ie: 2012-02-14.)

  2. All of your server's memory allocations can be found in /proc/user_beancounters. However, these can be difficult to read. The following commands take this information and turn your server into a number. If your DV has 1G RAM, the number is 2; for 2G RAM, it is 3; so on and so forth.
  3. ramCount=`awk 'match($0,/vmguar/) {print $4}' /proc/user_beancounters`
    ramBase=-16 && for ((;ramCount>1;ramBase++)); do ramCount=$((ramCount/2)); done
  4. On its own, this number may not mean much more than the beancounters themselves. Consider this example, and the logic behind it: Why would a DV 4.0 with 4G of RAM have the same MySQL configuration as a server with DV 4.0 with 1G of RAM? It is very unlikely that those two servers will have an identical MySQL workload - their configuration files should reflect as much. Using the server's beancounters as a guideline, a more suitable my.cnf can be crafted. The following is a single command:
  5. cat <<EOF > /etc/my.cnf
    [mysqld]
    # Basic settings
    user = mysql
    datadir = /var/lib/mysql
    socket = /var/lib/mysql/mysql.sock
    
    # Security settings
    local-infile = 0
    symbolic-links = 0
    
    # Memory and cache settings
    query_cache_type = 1
    query_cache_size = $((2**($ramBase+2)))M
    thread_cache_size = $((2**($ramBase+2)))
    table_cache = $((2**($ramBase+7)))
    tmp_table_size = $((2**($ramBase+3)))M
    max_heap_table_size = $((2**($ramBase+3)))M
    join_buffer_size = ${ramBase}M
    key_buffer_size = $((2**($ramBase+4)))M
    max_connections = $((100 + (($ramBase-1) * 50)))
    wait_timeout = 300
    
    # Innodb settings
    innodb_buffer_pool_size = $((2**($ramBase+3)))M
    innodb_additional_mem_pool_size = ${ramBase}M
    innodb_log_buffer_size = ${ramBase}M
    innodb_thread_concurrency = $((2**$ramBase))
    
    [mysqld_safe]
    # Basic safe settings
    log-error = /var/log/mysqld.log
    pid-file = /var/run/mysqld/mysqld.pid
    EOF
  6. Now, armed with a new configuration, all that is left to do is to restart MySQL:
    /etc/init.d/mysqld restart

Using mysqltuner.pl for more advanced/specific changes

Obviously, every hosting situation is different. Certain values in your my.cnf may need to be set more specifically than the general recommendations outlined above. One of the easiest to use tools in this regard is mysqltuner.pl.

NOTE:

The mysqltuner.pl tool is a third-party utility. All credit belongs to the original authors. The source code, which includes authorship and licensing information, can be found at http://mysqltuner.pl/mysqltuner.pl

  • In order to use mysqltuner.pl, it first needs to be downloaded. If you would like the script to be executable at any time, from anywhere, via SSH, download it into a bin directory.
    cd /usr/bin
    wget mysqltuner.pl
    chmod 755 $_
  • From there, you can execute the script at any time simply with using the file itself as a command:
    mysqltuner.pl
  • Upon executing the command above, you will receive a screen full of output. The majority of it is an advanced, numerical breakdown for how the recommendations towards the bottom were reached. This bottom portion may appear something like this:
    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Set thread_cache_size to 4 as a starting value
    Your applications are not closing MySQL connections properly
    Variables to adjust:
    query_cache_limit (> 1M, or use smaller result sets)
    thread_cache_size (start at 4)
    innodb_buffer_pool_size (>= 2M)
    • The suggestion to run OPTIMIZE TABLE is almost impossible to avoid. It is healthy practice to periodically optimize and repair all MySQL tables - but it should be something used with moderation. It is possible to optimize 100 times in a row and still have this suggested by mysqltuner.pl.
    • It should also be noted that the script will be very straight forward about its own accuracy. The amount of data available to analyze is directly proportional to how long your MySQL server has been running. Whenever making changes, as long as the server is not in a state where it is consistently crashing, it is a good idea to "let the dust settle" on one round of changes before making another.
    • The real value of this script is the "Variables to adjust" section. Here, it compares what it sees on your server against changes you may want to make. Using 'innodb_buffer_pool_size (>= 2M)' as an example. Opening up this server's my.cnf reveals the following:
      innodb_buffer_pool_size=2M
    Since this already meets the suggested requirement, you may want to leave it alone, or try raising it slightly. After making a change to your my.cnf, restart the MySQL server to reload it. If the variable being suggested does not appear in my.cnf, that means the server is using its default setting. To make a change, simply add it to my.cnf somewhere below where it says "[mysql]" in a similar format to the example above.
  • NOTE:

    Always keep in mind that automated changes and scripted suggestions can only go so far. Some level of familiarity with MySQL documentation can go a long way when troubleshooting database performance.

    More tips

    • Defragment the query cache to better utilize its memory with the FLUSH QUERY CACHE statement. The statement does not remove any queries from the cache.
    • The RESET QUERY CACHE statement removes all query results from the query cache.
    • The FLUSH TABLES statement also does this.
    • To monitor query cache performance, use SHOW STATUS to view the cache status variables:
      SHOW STATUS LIKE 'Qcache%';
      

    The output of this command will look similar to this:

     | Qcache_free_blocks             | 376       |
     | Qcache_free_memory             | 1724608   |
     | Qcache_hits                    | 65894     |
     | Qcache_inserts                 | 53777     |
     | Qcache_lowmem_prunes           | 9307      |
     | Qcache_not_cached              | 1348      |
     | Qcache_queries_in_cache        | 1676      |
     | Qcache_total_blocks            | 4168      |
    

    In the above example, you can see that Qcache_lowmem_prunes is a large number. This means that the query cache had to remove queries to make room for other queries. Based on this example, you would want to increase query-cache-size slightly to keep your queries inside cache. If your Qcache_lowmem_prunes is "0", then you may want to decrease query-cache-size to free up memory for other processes.

 

Continue