Mysqltuner Serial Usage Iii - Minimizing Mysql Retentivity Footprint
This is business office three of the serial on using Part 1 explains how to install in addition to move MySQLTuner. Part 2 addresses the expanse of database defragmentation. This postal service illustrates how to acquire by MySQL retention 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 amongst only 512 MB of memory.
$ perl mysqltuner.pl
If you lot scroll downwardly to the Recommendations department of the inward a higher house report, it is difficult to immature adult woman the eye-catching message: 'MySQL's maximum retention usage is dangerously high. Add RAM earlier increasing MySQL buffer variables.'
Indeed, adding to a greater extent than RAM is oft the cheapest in addition to simplest solution to out-of-memory problems. By spending an extra $5 per month, I tin upgrade my VPS server to receive got 1 GB of RAM. But, earlier you lot buy the farm pass your hard-earned coin on RAM, let's explore some other ways to cut MySQL's retention footprint.
Maximum number of database connections
Lines that start amongst ii exclamation marks ('!!') are warnings. Note the next lines inward the inward a higher house 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 retention usage: 597.8M (120% of installed RAM)
...
[OK] Highest usage of available connections: 8% (13/151)
According to the inward a higher house warning, MySQL could potentially role upwardly to 597.8 MB of RAM. Where did the number come upwardly from?
The number was derived from the preceding line. MySQL required 192MB globally in addition to 2.7 MB per connexion to the database. By default, the maximum number of connections was 150+1. (The 151st connexion would live on restricted to database management only.) Hence, the maximum retention usage was 192 + 150 * 2.7, equaling 597.
Should you lot allow for 150 connections? Keep inward heed that each connection, fifty-fifty inward the idle state, volition convey upwardly some memory. MySQLTuner tin deal you lot response the enquiry amongst confidence.
MySQLTuner reports the highest number of concurrent connections since the final MySQL restart (13 inward the inward a higher house example). The database should live on upwardly for a minimum of 24 hours earlier you lot move MySQLTuner. In fact, the longer the fourth dimension elapses since the final restart, the to a greater extent than trustworthy is the statistic.
You tin uncovering out from the MySQLTuner written report how long MySQL has been up. Go dorsum to the start trace of piece of work nether the Performance Metrics section. In the inward a higher house example, MySQL had been upwardly for 36 days since the final restart.
Although MySQL was configured for accepting 150 connections, the highest number of concurrent connections made inward the past times 36 days was only xiii (8% of the maximum). In low-cal of that knowledge, nosotros could lower the maximum number of connections allowed, therefore, reducing the full retention footprint for MySQL.
Before nosotros buy the farm ahead to reconfigure MySQL, nosotros volition view the wait-timeout threshold which affects how long idle connections remain live on earlier timing out.
Wait timeout
One of the General recommendations inward the inward a higher house representative was:
'Your applications are non closing MySQL connections properly.'
In other words, database connections were opened but non properly unopen later on queries or updates were already completed. These idle connections would hang approximately until a predefined timeout threshold was reached. The default timeout threshold was eight hours. So, if a query completed inward 2 seconds, but because the connexion was non unopen properly, the connexion would live on for some other 28,798 seconds earlier timing out. In the meantime, the idle connections continued to swallow resources including counting toward the maximum number of opened upwardly connections.
The culprit was easily identified inward the inward a higher house case: the database was used alone for WordPress, an application written inward PHP. However, solving the work tin live on out of your reach, unless you lot are a PHP developer.
The practiced word is that you lot tin cut the timeout interval past times adjusting a MySQL configuration parameter. By making idle connections fourth dimension out faster, at that spot volition live on less concurrent connections. For WordPress/PHP applications, I laid the await timeout to live on lx seconds.
It is every bit good worth mentioning that because at that spot are less idle connections due to quicker timeout, you lot tin farther cut the maximum number of connections.
Re-configuring MySQL
To alter the maximum number of connections or the await timeout threshold, edit the MySQL configuration file every bit follows.
$ sudo vi /etc/mysql/my.cnf
The configuration variables of involvement are max_connections, in addition to wait_timeout. Enter a value for each variable using the next syntax:
max_connections = 50
wait_timeout = lx
For the inward a higher house configuration changes to convey effect, a restart of the MySQL daemon is needed.
For non-systemd systems, move the next command:
$ sudo service mysql restart
For systemd-enabled systems, run:
$ sudo systemctl restart mysql
Alternatively, you lot tin dynamically alter the configuration variables, thereby avoiding the database restart. To produce that, number the next commands.
$ mysql -u rootage -p -e "SET GLOBAL max_connections=50;"
$ mysql -u rootage -p -e "SET GLOBAL wait_timeout=60;"
Note that modifying the MySQL configuration file is all the same required if you lot desire the changes to persist later on futurity organization restarts.
What's next?
MySQLTuner is non something you lot move in i lawsuit in addition to forget virtually it. Your spider web traffic blueprint changes over time. You should schedule to move it regularly in addition to essay out the output. Please refer dorsum to Part 1 of this serial for instructions on how to schedule a run.
The to a greater extent than knowledgeable you lot are virtually database optimization, the to a greater extent than effective you lot buy the farm on using the information provided past times MySQLTuner. I recommend the next videos if you lot desire to larn to a greater extent than virtually MySQL optimization:
You may every bit good live on interested inward the next MySQL-related articles from this blog:
For Apache optimization, delight refer to this weblog post.
0 Response to "Mysqltuner Serial Usage Iii - Minimizing Mysql Retentivity Footprint"
Post a Comment