Mysqltuner of MySQL parameter Optimization Assistant tool

use and installation of Mysqltuner.sql.

Mysqltuner is a mysql parameter optimization helper that provides a comprehensive physical examination of MySQL and then gives a targeted medical report

1.mysqltuner Downloads

Mysqltuner official website:http://mysqltuner.com/ can download the latest version.

installation and use of 2.mysqltuner.

Mysqltuner is a Perl script that does not require any installation to unzip the downloaded tar package.

[Email protected] software]# TAR-ZXVF major-mysqltuner-perl-v1.4.0-4-gfa47d9c.tar.gz

[Email protected] software]# CD major-mysqltuner-perl-fa47d9c

[Email protected]]#/mysqltuner.pl--user root--pass [email protected]

>> Mysqltuner 1.4.0-major Hayden<[email protected]>

>> Bug reports, feature requests, and Downloadsat http://mysqltuner.com/

>> Run with '--help ' for additional options andoutput filtering

[OK] Logged in using credentials passed on the command line

[OK] Currently running supported MySQL version 5.5.17-log

[OK] Operating on 64-bit Architecture

--------Storage Engine Statistics-------------------------------------------

[--] Status: +csv +innodb +mrg_myisam

[--] Data in InnoDB tables:32g (tables:15)

[--] Data in Performance_schema tables:0b (tables:17)

[!!] Total Fragmented tables:1

--------Security Recommendations-------------------------------------------

[OK] All database users has passwords assigned

--------Performance Metrics-------------------------------------------------

[--] Up for:11d 22h 38m 29s (11M Q [11.157 QPS], 5K Conn, tx:95b,rx:29b)

[--] reads/writes:45%/55%

[--] Total BUFFERS:4.3G global + 2.1M per thread (max threads)

[!!] Maximum Possible memory usage:8.5g (110% of installed RAM)

[OK] Slow queries:0% (41k/11m)

[!!] Highest connection usage:99% (1996/2000)

[OK] Key Buffer size/total MyISAM indexes:256.0m/96.0k

[OK] Key buffer hit rate:100.0% (1M cached/8 reads)

[!!] Query Cache is disabled

[OK] Sorts requiring temporary tables:0% (0 temp sorts/649 sorts)

[OK] Temporary tables created on disk:0% (134 to disk/133k total)

[!!] Thread Cache Hit rate:24% (4K created/5k connections)

[!!] Table Cache Hit rate:0% (open/7k opened)

[OK] Open file Limit used:0% (57/65k)

[OK] Table Locks acquired immediately:97% (22M immediate/22mlocks)

[!!] InnoDB Buffer Pool/data size:4.0g/32.0g

[OK] InnoDB Log waits:0

--------Recommendations-----------------------------------------------------

General recommendations:

Run OPTIMIZE TABLE todefragment tables for better performance

Reduce your overall mysqlmemory footprint for system stability

Reduce or eliminatepersistent connections to reduce connection usage

Increase table_open_cachegradually To avoid file descriptor limits

Read this beforeincreasing table_open_cache over 64:http://bit.ly/1mi7c4c

Variables to adjust:

MySQL ' s maximum memoryusage is dangerously high * * *

ADD RAM beforeincreasing MySQL Buffer Variables * * *

Max_connections (>2000)

Wait_timeout (< 200)

Interactive_timeout (<200)

Query_cache_size (>=8m)

Thread_cache_size (>64)

Table_open_cache (>4220)

Innodb_buffer_pool_size (>= 32G)

from the output of Mysqltuner, we can see that the output of Mysqltuner mainly consists of the following chunks

Storage engine Statistics: related statistics of storage engines

Security Recommendations: Installation Check

Performance Metrics: Performance Indicators

General recommendations: Physical planning recommendations

Variables to adjust: Variable adjustment recommendations

This article is from the "SQL Server MySQL" blog, so be sure to keep this source http://dwchaoyue.blog.51cto.com/2826417/1636616

Mysqltuner of MySQL parameter Optimization Assistant tool