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

Lire la suite…