Administration MySQL

Using Mysqltuner

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

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  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

Other links and Info:

I would recommend making sure the following is optimized.

Your mysql queries in your code to make sure the queries are being closed.
Your mysql tables are optimized.
Your mysql configuration is optimized for your web site needs.

Here are some helpful links on mysql optimization.

http://www.codero.com/knowledge-base/questions/319/How+to+install+mytop+for+database+performance+monitoring%3A
http://www.codero.com/knowledge-base/questions/298/How+can+I+view+currently+running+MySQL+queries%3F
http://www.codero.com/knowledge-base/questions/274/How+to+check+and+repair+MySQL+database+tables
http://www.codero.com/knowledge-base/questions/96/How+do+I+optimze+mysql%3F
http://www.codero.com/knowledge-base/questions/195/How+can+I+trouble+shoot+my+MySQL+database%3F
http://dev.mysql.com/doc/refman/5.0/en/optimization.html