The use of tuning-primer.sh for MySQL performance optimization

Tuning-primer.sh detects the current operation of MySQL, generates reports, and gives recommendations for optimization.

Download and use:
1.wget http://www.day32.com/MySQL/tuning-primer.sh

2. Copy the tuning-primer.sh to the my.cnf Sibling directory
3.chmod +xtuning-primer.sh Execution: SH tuning-primer.sh

[[email protected] software]#./tuning-primer.sh all

Using Login values from ~/.MY.CNF

-INITIAL LOGIN attempt FAILED-

Testing for stored webmin passwords:

Nonefound

Could not auto Detect login info!

Found Potential Sockets:/home/mysql/data/mysql.sock

Using:/home/mysql/data/mysql.sock

Would provide a differentsocket?: [y/n] N

Do you have your login handy? [y/n]: Y

User:root

Password: [Email protected]

Would you like me to create a ~/.my.cnffile? [y/n]: N

--MYSQL Performance TUNING PRIMER--

-By:matthew Montgomery-

MySQL Version 5.5.17-log x86_64

Uptime = 5 days hrs min to sec

Avg. QPS = 0

Total Questions = 106649

Threads Connected = 1

Server has been running for over 48hrs.

It should is safe to follow theserecommendations

To find out more information in how each Ofthese

Runtime variables effects performancevisit:

Http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html

Visithttp://www.mysql.com/products/enterprise/advisors.html

For info about MySQL ' s enterprisemonitoring and advisory Service

# Slow Query checking

SLOW QUERIES

The slow query log is enabled.

Current Long_query_time = 3.000000 sec.

You have 0 out of 106670 that take Longerthan 3.000000 sec.

Your Long_query_time seems to be fine

# Binary log Check

BINARY UPDATE LOG

The binary update log is enabled

# worker thread Check

WORKER THREADS

Current thread_cache_size = 64

Current threads_cached = 4

Current THREADS_PER_SEC = 0

Historic Threads_per_sec = 0

Your Thread_cache_size is fine

# Link Check

MAX CONNECTIONS

Current max_connections = 2000

Current threads_connected = 1

Historic Max_used_connections = 5

The number of used connections is 0% of theconfigured maximum.

You is using less than 10% of yourconfigured max_connections.

Lowering max_connections could help toavoid an over-allocation of memory

See "MEMORY USAGE" sections tomake sure you is not over-allocating

# InnoDB status Check

INNODB STATUS

Current InnoDB Index space = K

Current InnoDB Data space = 224 K

Current InnoDB buffer Pool free = 99

Current Innodb_buffer_pool_size = 4.00 G

Depending on what much space your innodbindexes take up it may safe

To increase the value to up to 2/3 oftotal system memory

# Memory Status Check

MEMORY USAGE

Max Memory ever allocated:4.30 G

Configured Max Per-thread buffers:4.21 G

Configured Max Global buffers:4.29 G

Configured Max Memory limit:8.50 G

Physical memory:7.68 G

Max memory limit exceeds 90% of physicalmemory

# MyISAM Key value status check

KEY BUFFER

Current MyISAM Index space = K

Current key_buffer_size = + M

Key Cache Miss Rate is 1:2

Key Buffer free ratio = 81

Your Key_buffer_size seems to be fine

#  Query Cache Check

QUERY CACHE

Query cache is supported and not enabled

Perhaps you should set the Query_cache_size

# Sort Operation Check

SORT OPERATIONS

Current sort_buffer_size = 1 M

Current read_rnd_buffer_size = K

Sort buffer seems to be fine

# Link Inspection of tables

JOINS

Current join_buffer_size = 132.00 K

You have had 0 queries where a joins Couldnot use an index properly

Your joins seem to be using indexesproperly

# File restriction Check

OPEN FILES LIMIT

Current Open_files_limit = 65535 files

The open_files_limit should typically beset to at least 2x-3x

That's Table_cache if you have heavymyisam usage.

Your open_files_limit value seems to befine

# variable Cache Check

TABLE CACHE

Current Table_open_cache = 4220 tables

Current Table_definition_cache = Tables

You has a total of tables

You have a tables open.

The Table_cache value seems to be fine

# Temporary table check status check

TEMP TABLES

Current max_heap_table_size = M

Current tmp_table_size = M

of 53466 temp tables, 0% were created Ondisk

Created disk tmp tables ratio seems fine

# Table Scan Check

TABLE SCANS

Current read_buffer_size = K

Current table Scan ratio = 52,589:1

You have a high ratio of sequential accessrequests to selects

Benefit from raisingread_buffer_size and/or improving your use of indexes.

# table lock Check

TABLE LOCKING

Current Lock Wait ratio = 1:91,130

Your table locking seems to be fine

compared with mysqltunner.sql, Tuning-primer detection is more detailed , mainly for InnoDB detection,

Mysqltunner.sql cannot check for InnoDB .

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

The use of tuning-primer.sh for MySQL performance optimization