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.
If you're having trouble with the steps in this article, additional assistance is available via Advanced Support, our premium services division. For more information on what Advanced Support can do for you, please click here.
Instructions
- Log into your AccountCenter.
- Click on your primary domain.
- Click on Manage Databases.
- Click on Admin for the database you wish to optimize.
- This will open in a new window/tab of your browser. Log into phpMyAdmin with your MySQL username and password.
- Click on the database name on the left hand side frame (not the table, the database name).
- On the right hand column, your tables should be listed. Scroll down till you see the Check all link.
- 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.
Overview
VPS 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.
STATEMENT OF SUPPORT:
Please keep in mind that troubleshooting the configuration/functionality of third-party applications is not covered by our statement of support. These resources were provided as a courtesy to assist you to the extent of our abilities. For more information on our statement of support, feel free to click here.
If you're having trouble with the steps in this article, additional assistance is available via Advanced Support, our premium services division. For more information on what Advanced Support can do for you, please click here.
Requirements
Before you start, this article has the following dependencies:
- You must have SSH access set up for root or a sudo user.
- Make a back up before you make any change to your VPS
Instructions
Tuning MySQL based on available RAM
- 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.)
- 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 VPS 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- 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- 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/binwget --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
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 CACHEstatement. The statement does not remove any queries from the cache. - The
RESET QUERY CACHEstatement removes all query results from the query cache. - The
FLUSH TABLESstatement also does this. - To monitor query cache performance, use
SHOW STATUSto 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.
Comments