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.

Requirements

Before you start, this article has the following dependencies:

• You must have SSH access set up for root or a sudo user.
  • Connecting via SSH to your server
  • How do I enable root access to my DV?
  • How do I disable SSH login for the root user?
• Make a back up before you make any change to your DV.

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.

ramCount=`awk 'match($0,/vmguar/) {print $4}' /proc/user_beancounters`
ramBase=-16 && for ((;ramCount>1;ramBase++)); do ramCount=$((ramCount/2)); done

1. 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:

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

1. 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.

• 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 --trust-server-names 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.

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.

Source: DV – Making it Better