This is part 3 of the series on using MySQLTuner
to optimize MySQL database performance and stability. Part 1 explains how to install and run MySQLTuner
. Part 2 addresses the area of database defragmentation. This post illustrates how to manage MySQL
memory footprint.
MySQLTuner
output
MySQLTuner
was used to analyze a WordPress database deployed on the LAMP platform (Linux
, Apache
, MySQL
, PHP
). The host was a VPS server with only 512 MB of memory.
$ perl mysqltuner.pl
If you scroll down to the Recommendations
section of the above report, it is hard to miss the eye-catching message:
'MySQL's maximum memory usage is dangerously high. Add RAM before increasing MySQL buffer variables.'
Indeed, adding more RAM is often the cheapest and simplest solution to out-of-memory problems. By spending an extra $5 per month, I can upgrade my VPS server to have 1 GB of RAM. But, before you go spend your hard-earned money on RAM, let's explore some other ways to reduce MySQL's memory footprint.
Maximum number of database connections
Lines that begin with two exclamation marks ('!!') are warnings. Note the following lines in the above Performance Metrics
section:
-------- Performance Metrics --------------------------------------
...
[--] Up for: 36d 23h 28m 39s (758K q [0.237 qps], 53K conn, TX: 6B, RX: 98M)
[--] Total buffers: 192.0M global + 2.7M per thread (151 max threads)
[!!] Maximum possible memory usage: 597.8M (120% of installed RAM)
...
[OK] Highest usage of available connections: 8% (13/151)
According to the above warning, MySQL
could potentially use up to 597.8 MB of RAM. Where did the number come from?
The number was derived from the preceding line. MySQL required 192MB globally and 2.7 MB per connection to the database. By default, the maximum number of connections was 150+1. (The 151st connection would be restricted to database administration only.) Hence, the maximum memory usage was 192 + 150 * 2.7, equaling 597.
Should you allow for 150 connections? Keep in mind that each connection, even in the idle state, will take up some memory.
MySQLTuner
can help you answer the question with confidence.
MySQLTuner
reports the highest number of concurrent connections since the last MySQL
restart (13 in the above example). The database should be up for a minimum of 24 hours before you run MySQLTuner
. In fact, the longer the time elapses since the last restart, the more trustworthy is the statistic.
You can find out from the MySQLTuner
report how long MySQL
has been up. Go back to the first line under the Performance Metrics
section. In the above example, MySQL
had been up for 36 days since the last restart.
Although MySQL
was configured for accepting 150 connections, the highest number of concurrent connections made in the past 36 days was only 13 (8% of the maximum). In light of that knowledge, we could lower the maximum number of connections allowed, therefore, reducing the total memory footprint for MySQL
.
Before we go ahead to reconfigure MySQL
, we will consider the wait-timeout threshold which affects how long idle connections stay alive before timing out.
Wait timeout
One of the General recommendations
in the above example was:
'Your applications are not closing MySQL connections properly.'
In other words, database connections were opened but not properly closed after queries or updates were already completed. These idle connections would hang around until a predefined timeout threshold was reached. The default timeout threshold was 8 hours. So, if a query completed in 2 seconds, but because the connection was not closed properly, the connection would live for another 28,798 seconds before timing out. In the meantime, the idle connections continued to consume resources including counting toward the maximum number of open connections.
The culprit was easily identified in the above case: the database was used exclusively for WordPress, an application written in PHP. However, solving the problem can be out of your reach, unless you are a PHP developer.
The good news is that you can reduce the timeout interval by adjusting a MySQL
configuration parameter. By making idle connections time out faster, there will be less concurrent connections. For WordPress/PHP applications, I set the wait timeout to be 60 seconds.
It is also worth mentioning that because there are less idle connections due to quicker timeout, you can further reduce the maximum number of connections.
Re-configuring MySQL
To change the maximum number of connections or the wait timeout threshold, edit the MySQL
configuration file as follows.
$ sudo vi /etc/mysql/my.cnf
The configuration variables of interest are max_connections
, and wait_timeout
. Enter a value for each variable using the following syntax:
max_connections = 50
wait_timeout = 60
For the above configuration changes to take effect, a restart of the MySQL
daemon is needed.
For non-systemd systems, run the following command:
$ sudo service mysql restart
For systemd-enabled systems, run:
$ sudo systemctl restart mysql
Alternatively, you can dynamically change the configuration variables, thereby avoiding the database restart. To do that, issue the following commands.
$ mysql -u root -p -e "SET GLOBAL max_connections=50;"
$ mysql -u root -p -e "SET GLOBAL wait_timeout=60;"
Note that modifying the MySQL
configuration file is still required if you want the changes to persist after future system restarts.
What's next?
MySQLTuner
is not something you run once and forget about it. Your web traffic pattern changes over time. You should schedule to run it regularly and examine the output. Please refer back to Part 1 of this series for instructions on how to schedule a run.
The more knowledgeable you are about database optimization, the more effective you become on using the information provided by MySQLTuner
. I recommend the following videos if you want to learn more about MySQL
optimization:
You may also be interested in the following MySQL-related articles from this blog:
1 comment:
MySQL is pretty much the de facto database engine for most open source scripts, and it is almost-always installed on hosted servers. However the default MySQL installation on Debian/Ubuntu takes around 25MB RSS on a cold-start.
Post a Comment