obey-robots.txt
Web Master Trouble
Users Online
Guests Online: 1

Members Online: 0

Total Members: 3
Newest Member: Proxyhotdeals
Login
Username

Password



Not a member yet?
Click here to register.

Forgotten your password?
Request a new one here.
Last Seen Users
Your ad here below
View Thread
Web Master Trouble » Linux Apache MySQL PHP optimalisation tips tricks » MySQL optimalisation Tools
Here all MySQL optimalisation Tools are discussed on how to use them.
 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!

Download source  Code
#!/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!)
Download source  Code
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.
Download source  Code
wget -O mysqltuner.pl http://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl



You must make it executable otherwise it won't run.
Download source  Code
chmod +x mysqltuner.pl



Now we can run/execute it from the directory where you saved it in an SSH terminal as root with
Download source  Code
./mysqltuner.pl




You'll see:
Download source  Code
[root@demo]# ./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
Please enter your MySQL administrative login:



Download source  Code
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:
Download source  Code
-------- 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:
Download source  Code
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:
Download source  Code
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!!
Download source  Code
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:
Please login or register to see attachments in this thread.
 
Jump to Forum:
New Thread Post Reply
Use this BBcode or HTML to refer to; 'MySqlTuner MySql tuning script'
BBcode:
HTML:
Vergelijkbare onderwerpen
Thread Forum Replies Last Post
Getting started with MySQL over SSL Linux tutorials, Tips & Tricks 1 22-06-2014 05:00
Getting started with MySQL Master-Slave replication Linux tutorials, Tips & Tricks 1 04-05-2014 05:00