obey-robots.txt
View Thread
Web Master Trouble » Linux Apache MySQL PHP optimalisation tips tricks » MySQL optimalisation Tools
 Print Thread
MySqlTuner MySql tuning script
webmastertrouble
When you run the MySqlTuner MySql tuning script you get advice what variables to adjust in my.cnf.
My.cnf holds the settings for the MySql server.
Adjusting these settings can increase your website performance dramatically.
Wrong settings can be disastrous!


#!/usr/bin/perl -w
# mysqltuner.pl - Version 1.2.0
# High Performance MySQL Tuning Script
# Copyright (C) 2006-2011 Major Hayden - major@mhtx.net
#
# For the latest updates, please visit http://mysqltuner.com/
# Git repository available at http://github.com/rackerhacker/MySQLTuner-perl


Download the script to your server. (remember de directory your in!)

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

alternatively you can download it as an attached file here at the bottom of the post or try the line below.

wget -O mysqltuner.pl http://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl

You must make it executable otherwise it won't run.

chmod +x mysqltuner.pl 

Now we can run/execute it from the directory where you saved it in an SSH terminal as root with

./mysqltuner.pl


You'll see:

[root@demo]# ./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:


Please enter your MySQL administrative password:


You can either use root as login name or your MySql login name. So login can be for example root or da_admin.
Then enter the corresponding password.

The output of the MySqlTuner tuning script looks something like this:

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

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 82M (Tables: 441)
[--] Data in InnoDB tables: 7M (Tables: 151)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 163

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 22h 26m 2s (191K q [2.365 qps], 10K conn, TX: 298M, RX: 46M)
[--] Reads / Writes: 46% / 54%
[--] Total buffers: 448.0M global + 22.6M per thread (25 max threads)
[OK] Maximum possible memory usage: 1013.6M (79% of installed RAM)
[OK] Slow queries: 0% (0/191K)
[OK] Highest usage of available connections: 16% (4/25)
[OK] Key buffer size / total MyISAM indexes: 32.0M/23.2M
[OK] Key buffer hit rate: 97.2% (1M cached / 42K reads)
[OK] Query cache efficiency: 59.9% (62K cached / 103K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 8K sorts)
[!!] Temporary tables created on disk: 43% (6K on disk / 15K total)
[OK] Thread cache hit rate: 99% (4 created / 10K connections)
[OK] Table cache hit rate: 47% (1K open / 2K opened)
[OK] Open file limit used: 0% (1K/262K)
[OK] Table locks acquired immediately: 99% (105K immediate / 105K locks)
[OK] InnoDB data size / buffer pool: 7.4M/128.0M

-------- Recommendations -----------------------------------------------------
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
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses


In my case no variables to adjust where suggested, but in your case you could get something like this:

Variables to adjust:
    query_cache_size (> 16M)
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)
    table_cache (> 64)

Just add or alter them in my.cnf:

query_cache_size = 32M
    tmp_table_size = 64M
    max_heap_table_size = 64M
    table_cache = 128


In my opinion this is the most important line!!

Highest usage of available connections: 16% (4/25)

Standard MySQL installations have a max_connections = 250 setting as default.
This means when you start tuning with this setting you will ran out of memory very quickly.
So make sure this is the first to adjust.

I all ready reduced max_connections to 25 ten times less as default. My next step would be to reduce max_connections to 10. Don't go below the Highest usage of available connections but keep a safe margin, otherwise your users will see a mysql warning.

To be continued.
webmastertrouble attached the following file:
You are not allowed to see attachments in this thread.
 
Jump to Forum
New Thread Post Reply
Use BBcode or HTML to refer to; 'MySqlTuner MySql tuning script'
BBcode:
HTML:
Simular threads
Subject Discussion Forum         Last Post
Getting started with MySQL over SSL Linux tutorials, Tips & Tricks : 1 22-06-2014
Getting started with MySQL Master-Slave replication Linux tutorials, Tips & Tricks : 1 04-05-2014