Tune MySQL Performance with MySQLTuner

In the previous tutorial we discussed about how to install mysql-server and as a continual this tutorial explains you how to tune mysql server with MysqlTuner. MySQLTurner is a perl script that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability.

.mysql, install mysql, install and configure mysql, mysql service, mysql performance tuning
How to install and run mysqltuner ?

To install mysqltuner, update your repository if you didn’t update it before

apt-get update

apt-get install mysqltuner

After installation over, check whether the mysql server is running or not, if not start the mysql server

Note :
For checking mysql status

/etc/init.d/mysql status

service mysql status

To start mysql server

/etc/init.d/mysql start

service mysql start

Run mysqltuner in your console, you will get an output as like shown below

It provides the General Statistics information, Storage Engine Statistics information and other Performance Metrics. Read the following output carefully to know more.

linuxinternetworks ~ # mysqltuner

>> MySQLTuner 1.0.1 – Major Hayden <major@mhtx.net>
>> 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.1.63-0ubuntu0.11.04.1
[OK] Operating on 32-bit architecture with less than 2GB RAM

——– Storage Engine Statistics ——————————————-
[–] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[!!] InnoDB is enabled but isn’t being used
[OK] Total fragmented tables: 0

——– Performance Metrics ————————————————-
[–] Up for: 1m 43s (326 q [3.165 qps], 46 conn, TX: 31K, RX: 50K)
[–] Reads / Writes: 86% / 14%
[–] Total buffers: 58.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 463.8M (22% of installed RAM)
[OK] Slow queries: 0% (0/326)
[OK] Highest usage of available connections: 0% (1/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/87.0K
[OK] Key buffer hit rate: 100.0% (30 cached / 0 reads)
[!!] Query cache efficiency: 0.0% (0 cached / 81 selects)
[OK] Query cache prunes per day: 0
[OK] Temporary tables created on disk: 23% (48 on disk / 207 total)
[OK] Thread cache hit rate: 97% (1 created / 46 connections)
[!!] Table cache hit rate: 8% (23 open / 264 opened)
[OK] Open file limit used: 4% (46/1K)
[OK] Table locks acquired immediately: 100% (201 immediate / 201 locks)

——– Recommendations —————————————————–
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
MySQL started within last 24 hours – recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_limit (> 1M, or use smaller result sets)
table_cache (> 64)

Also if you want to check the mysql process in realtime, check this tutorial.

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)

Add a Comment

Your email address will not be published. Required fields are marked *