Optimize Your MySQL Server With the MySQL Tuner Script

Posted: March 31, 2013 in Linux, Monitoring, Mysql
0

MySQL Tuner Script

The MySQL Tuner Script is a Perl script that analyses your running MySQL server and makes configuration recommendations based on past performance of the server.  Making the changes suggested by the MySQL Tuner Script can help improve the performance and stability of your MySQL server along with any applications that are using it.

Step 1: Start by downloading the Perl script from http://mysqltuner.com/mysqltuner.pl.

1. wget http://mysqltuner.com/mysqltuner.pl

Step 2: Make the script executable.

2.  chmod +x mysqltuner.pl

Step 3: Run the script as any user.

3.  ./mysqltuner.pl

Step 4: Enter your MySQL username and password when prompted.

Step 5: The script shows the values of current global MySQL variables.Any good values are shown as [OK] and bad values are shown with [!!].  Example output is shown below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
[user@mysql-server Desktop]$ ./mysqltuner.pl
 >>  MySQLTuner 1.2.0 - Major Hayden
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.13
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 43M (Tables: 103)
[--] Data in InnoDB tables: 48K (Tables: 3)
[!!] Total fragmented tables: 16
-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 19h 17m 51s (769K q [3.176 qps], 102 conn, TX: 301M, RX: 238M)
[--] Reads / Writes: 2% / 98%
[--] Total buffers: 288.0M global + 9.9M per thread (50 max threads)
[OK] Maximum possible memory usage: 784.9M (9% of installed RAM)
[OK] Slow queries: 0% (0/769K)
[OK] Highest usage of available connections: 24% (12/50)
[OK] Key buffer size / total MyISAM indexes: 64.0M/46.2M
[OK] Key buffer hit rate: 99.8% (8M cached / 19K reads)
[OK] Query cache efficiency: 62.1% (34K cached / 56K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2K sorts)
[OK] Temporary tables created on disk: 6% (33 on disk / 517 total)
[OK] Thread cache hit rate: 82% (18 created / 102 connections)
[!!] Table cache hit rate: 15% (163 open / 1K opened)
[OK] Open file limit used: 6% (285/4K)
[OK] Table locks acquired immediately: 100% (727K immediate / 727K locks)
[OK] InnoDB data size / buffer pool: 48.0K/128.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
 Run OPTIMIZE TABLE to defragment tables for better performance
 Enable the slow query log to troubleshoot bad queries
 Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
 table_cache (> 2048)
[user@mysql-server Desktop]$

Step 6: To change the values of the variables you will need to edit the [mysqld] section of your my.cnf file, usually located in /etc/my.cnf, but this may vary for you.

Step 7: Make the changes to the global variables suggested by the script and restart your MySQL server.

4.  /etc/init.d/service mysql restart

You can run the script again, but it is suggested that you wait 24 hours to see how the my.cnf changes affect the performance.

Leave a comment