Community Wiki

From (mt) Community Wiki

The (dv) Dedicated-Virtual 4.0 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. The average user will get more mileage out of the server's resources with MySQL changes similar to the following.

1. Make a backup! Whenever you're making changes to your configuration files, you always want to have the ability to quickly roll-back to previous settings if something doesn't go as expected. So that's the first thing we'll be doing:

   cp -p /etc/my.cnf /etc/my.cnf-backupMIB
   




2. All of your server's memory allocations can be found in /proc/user_beancounters. However, these are very difficult to read, and will generally not translate to much useful information unless you are an advanced user. The following commands take this information and turn your server into a number. If your (dv) has 512M RAM, this number is 1; for 1G RAM, it 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
   

3. On it's own, this number may not mean much more than the beancounters themselves. The general logic is this: Why would a (dv) 4.0 with 4G of RAM have the same MySQL configuration as a server with (dv) 4.0 with 512M 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, and should be copy and pasted as such:

   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
   

4. Now, armed with a new configuration, all that is left to do is load it into MySQL with a restart:

   /etc/init.d/mysqld restart
   

That's all there is to it. The section below covers one way to possibly troubleshoot further if database performance on your server still seems sluggish.

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.

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

2. From there, you can execute the script at any time simply with using the file itself as a command:

   mysqltuner.pl
   

3. Upon executing the command above, you'll receive a screen full of output. The majority of it is a more advanced, numerical breakdown for how the recommendations are reached. The bottom portion is what you should be most interested in unless you are an advanced user. It 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 again 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 it's 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.

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.