Mysqlreport use of the detailed

For MySQL configuration optimization, you must first identify the MySQL performance bottleneck, and the show status output report is the reference data used to calculate the performance bottleneck. Mysqlreport is not as simple as Show status to list the data, but to the reference data to the fusion calculation, organized into a single optimization reference point, and then can be based on the value of the optimization reference point and the point of measurement, the corresponding adjustment.

First, Installation:

Download Mysqlreport tool, directly unzip,: http://hackmysql.com/scripts/mysqlreport-3.5.tgz

It is important to note that Mysqlreport is developed based on Perl language, and its operation relies on PERL-DBI and perl-dbd-mysql, so the two software needs to be installed first.

Second, use:

Mysqlreport Common directives:

--user # Specify the user to connect to the database

--password #指定连接数据库的密码

--port #指定端口

--host #指定主机

--sokcet #指定socket文件

--flush-status #显示完报告后, execute the "flush status" statement

--outfile #将报告输出至某个文件中

Third, the output of the report detailed description

1. Basic information

Describes the current version of MySQL, the time of the run, and the current system time

MySQL 5.1.61-log uptime 262 23:51:59 Wed Jul 16 17:58:05 2014

2. Index Report

Indicates the usage of the current index buffer for MySQL, and if it is too high, the size of the key_buffer_size needs to be adjusted. Write hit and read hit illustrate the efficiency of writing and reading indexes, respectively

__ Key _________________________________________________________________ Buffer used 8.67M of 512.00M%used:1.69 Current 51.75M%usage:10.11 Write hit 34.33% Read hit 99.17%

3. Request a report

1) The total number of requests processed by MySQL in the first part, the average number of requests processed per second, and the request type.

QC hists: Refers to the number of MySQL request results directly from the query cache, that is, the query cache hit rate, the higher the better

DMS: Refers to the data manipulation language, that is, adding and deleting changes

Com_: Refers to the overhead that MySQL uses to handle its own operations

2) The second part is a description of MySQL slow query number, how long the query is considered to be slow query, is defined by Long_query_time. The lower the ratio of slow queries, the better, generally not more than 0.05%. The slow query log is recommended to open.

3) The third part describes in detail the processing number and proportion of various statements in DMS.

4) The fourth part details the processing number and proportion of each event in Com_.

__ Questions ___________________________________________________________ Total 4.12G 181.3/s QC Hits 2.93G 129.0/s%total:71.14 DMS 714.48M 31.4/s 17.34 Com_ 563.75M 24.8/s 13.68 -unknown 186.61M 8.2/s 4.53 Com_quit 97.48M 4.3/s 2.37 Slow 3 S 49.32k 0.0/s 0.00%dms:0.01 Log:on DMS 714.48M 31.4/s 17.34 SELECT 536.16M 23.6/s 13.01 75.04 DELETE 66.80M 2.9/s 1.62 9.35 UPDATE 61.78M 2.7/s 1.50 8.65 INSERT 49.34M 2.2/s 1.20 6.91 REPLACE 401.78k 0.0/s 0.01 0.06 Com_ 563.75M 24.8/s 13.68 Show_fields 214.45M 9.4/s 5.20 Set_option 194.83M 8.6/s 4.73 change_db 144.83M 6.4/s 3.51

5. Query and sort

Detailed description of MySQL resource consumption at query time

Scan shows the number of SELECT statements scanned for a full table

Full join is the number of multiple-table union queries

__ SELECT and Sort _____________________________________________________ Scan 284.42M 12.5/s%select:53.05 Range 53.32M 2.3/s 9.95 Full join 3.40M 0.1/s 0.63 Range Check 0 0/s 0.00 Full RNG join 2.09k 0.0/s 0.00 Sort Scan 242.41M 10.7/s Sort Range 5.78M 0.3/s Sort MRG Pass 0 0/s

6. Query cache (only appears if query cache is turned on)

Detailed description of the memory usage of the query cache

Block fragmnt: Refers to memory block fragmentation, if you have a large number of queries that return very small results, the default block size (that is, 4KB) can lead to a lot of memory fragmentation, this time, you need to reduce the value of "query_cache_min_res_unit", the ratio is greater, More fragments are generally not recommended for more than 10%

Hits/inserts/prunes: Where Hits is the most important, it reflects how many queries are obtained directly from the query cache, and prunes is the lower the better when the fragment is deleted per second.

Insert/prune: is a volatile QC indicator. The number of queries in a stable running Qc,insert QC should be greater than the number of queries prune out. And an unstable QC, the ratio may be 1:1, or even biased to prune. This shows two problems: 1, the QC size is not enough; 2, MySQL tries to cache everything

Hit/insert: Used to reflect the effectiveness of QC. The ideal situation is: MySQL inserted a batch of stable query to QC, and then continuously hit the results ... Therefore, if the QC is effective enough, this ratio should be biased to hit.

__ Query Cache _________________________________________________________ Memory usage 68.37M of 128.00M%used:53.42 Block fragmnt 18.92% Hits 2.93G 129.0/s Inserts 353.54M 15.6/s Insrt:prune 4.17:1 11.8/s Hit:insert 8.29:1

7. Table lock

A row is the total number, and the row is the current number. Lock waiting is always a bad thing for a database. The total ratio of the third column reflects a review, in any case not higher than 10%, otherwise it will definitely bring a lot of index and slow query problems!

__ Table Locks _________________________________________________________ Waited 949 0.0/s%total:0.00 Immediate 2.01G 88.4/s

8. Table

One line is the number of tables currently open by MySQL, and the usage of table caches, and the other is the average value since MySQL was run. Here are two values are important, one is the table cache use frankness, if 100%, you need to adjust the size of "TABLE_OPEN_CAHCE", and the other is the current frequency of open table, generally this value should be less than 1 times per second. But a high-load and running good MySQL, may be able to open 7 times per second table, still maintain a table cache of 100%

__ Tables ______________________________________________________________ Open%cache:100.00 Opened 39.85M 1.8/s

9. Number of database connections

If the maximum number of connections is close to 100%, you need to adjust the "max_connetions" parameter, of course, if the number of connections is too large, it is possible to slow query, bad index, DNS resolution is too slow caused by the problem, the number of connections per second, generally less than 5 per second, but usually as long as MySQL runs normally, it doesn't matter

__ Connections _________________________________________________________ Max used 547 of 886%max:61.74 Total 97.48M 4.3/s

7. Temporary table

MySQL can create temporary tables on memory, disk, and temporary files, to avoid creating temporary tables on disk as much as possible, because the slowest, to create in memory as much as possible, the third row of table mother represents the size of the temporary table in memory, size is the size of the temporary table that the specified memory allows to create, Size can be adjusted by the "tmp_table_size" parameter

__ Created Temp ________________________________________________________ Disk Table 186.19M 8.2/s Table 434.05M 19.1/s size:64.0m File 5 0.0/s

8. Threads

The size of the "thread_cache_size" parameter needs to be increased when the number of connections to MySQL exceeds the number of thread caches

__ Threads _____________________________________________________________ Running 1 of 1 Cached 7 of 8%hit:99.48 Created 509.01k 0.0/s Slow 0 0/s

9. Interruption and traffic

__ aborted _____________________________________________________________ Clients 2.36k 0.0/s Connects 111.05k 0.0/s __ Bytes _______________________________________________________________ Sent 2.56T 112.8k/s Received 892.91G 39.3k/s

10. InnoDB Buffer Pool

The size of the InnoDB buffer pool can be adjusted by the "innodb_buffer_size" parameter, as a database for a InnoDB engine, make the value as large as possible

__ InnoDB Buffer Pool __________________________________________________ Usage 1.00G of 1.00G%used:100.00 Read hit 100% Pages Free 3%total:0.00 Data 62.67k 95.63%drty:0.01 Misc 2861 4.37 Latched 0.00 Reads 4.27T 188.1k/s #innodb缓冲池读性能 From File 4.35M 0.2/s 0.00 #从文件读取 Ahead Rnd 243362 0.0/s #随机读 Ahead Sql 134409 0.0/s #顺序读, only occurs when full table scan is performed Writes 3.17G 139.4/s #缓冲池写的数量 Flushes 17.36M 0.8/s #缓冲池的页刷新请求数 Wait free 0 0/s #空闲等待时间, the smaller the better

11, InnoDB Lock

__ InnoDB Lock _________________________________________________________ Waits 0 0/s #等待某行解锁的累积次数, preferably 0 times. Current 0 #当前正在等待解锁的行个数, preferably 0 times Time acquiring Total 0 ms Average 0 ms Max 0 ms

12, InnoDB data, page, line

The first section lists four types of data operations, namely, read, write, refresh, wait

The second section lists the page information for the InnoDB, the number of pages created, read, and written in the buffer pool

The third part lists some of the amount of data to be increased and censored for InnoDB rows.

__ InnoDB data, Pages, Rows ____________________________________________ Data   Reads 5.93M 0.3/s         #整个innodb引擎完成所有的数据读取次数, not data-won bytes       writes 23.12M 1.0/S & nbsp   #也是统计写的次数   Fsync 14.65M 0.6/s         #刷新的次数   Pending     &NBS P                 #读, write, refresh wait     Reads 0     Write S 0     Fsync 0 Pages Created 2.04M 0.1/s Read 21.45M 0.9/s Written 17.36M 0.8/s Rows Deleted 62.97M 2.8/s Inserted 283.26M 12.5/s Read 4.56T 200.7k/s Updated 34.59M 1.5/s

This article is from the "Nobody" blog, please be sure to keep this source http://breezey.blog.51cto.com/2400275/1529492