Using Mysqltuner – GEEKDECODER

Download/Installation
Simple

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
# chmod +x mysqltuner.pl
# ./mysqltuner.pl

You can download the entire repository by using ‘git clone’ followed by the cloning URL above. The simplest and shortest method is:

wget mysqltuner.pl
perl mysqltuner.pl

Change the permissions you can execute it without calling perl directly.

[root@austin ~]# chmod +x mysqltuner.pl

[root@austin ~]# ./mysqltuner.pl

 &gt;&gt;  MySQLTuner 1.2.0 - Major Hayden &lt;major@mhtx.net&gt;
 &gt;&gt;  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 &gt;&gt;  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.73-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 4M (Tables: 230)
[--] Data in InnoDB tables: 12M (Tables: 419)
[--] Data in MEMORY tables: 0B (Tables: 2)
[!!] Total fragmented tables: 423

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 16s (25 q [1.562 qps], 8 conn, TX: 62K, RX: 1K)
[--] Reads / Writes: 93% / 7%
[--] Total buffers: 58.0M global + 1.6M per thread (151 max threads)
[OK] Maximum possible memory usage: 303.4M (3% of installed RAM)
[OK] Slow queries: 0% (0/25)
[OK] Highest usage of available connections: 0% (1/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/1.1M
[!!] Key buffer hit rate: 77.6% (98 cached / 22 reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2 sorts)
[OK] Temporary tables created on disk: 20% (1 on disk / 5 total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 68% (15 open / 22 opened)
[OK] Open file limit used: 3% (32/1K)
[OK] Table locks acquired immediately: 100% (29 immediate / 29 locks)
[!!] Connections aborted: 25%
[OK] InnoDB data size / buffer pool: 12.1M/16.0M

[stextbox id=”warning”]——– Recommendations —————————————————–[/stextbox]

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_size (>= 8M)
thread_cache_size (start at 4)

Optimize tables

See – http://geekdecoder.com/mysql-checkrepair-database/

Enable the slow query log to troubleshoot bad queries
Set thread_cache_size to 4 as a starting value
query_cache_size (>= 8M)
thread_cache_size (start at 4)

[root@austin ~]# nano /etc/my.cnf

[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
interactive_timeout=1800
wait_timeout=1800

Add the following if mysql is 5.6.1 or higher

slow_query_log = 1;
slow_query_log_file = '/var/log/mysql-slow.log';
query_cache_type = 1
query_cache_size = 32M
thread_cache_size = 4

Leave a Comment