MySQL Slow query Mysqldumpslow analysis method

MySQL tutorial optimization of the first step should be to troubleshoot problems, to find bottlenecks, and usually the bottleneck and problems need to observe the operation of MySQL to analyze, "view the MySQL health" This article lists some commonly used to view the MySQL running information ways.

For most programmers, the most easily discovered and solved problem is MySQL's slow query or no indexed query, so here's how to use the official Mysqldumps Tutorial Low tool to easily view this information. How to open a slow query for MySQL, please click

The simplest way to view the MyAdmin direct click State in the PHP tutorial is detailed in the following figure.

This is a view of some of MySQL's health values, which is despised by most system administrators because professional MySQL servers are rarely accessible via phpmyadmin, and other viewing options are described later. However, it is instructive and referential for phpMyAdmin to provide optimization advice.

Show status
Login directly under the command line MySQL run show status; Query statements, detailed below

The same statement and show Variables;,show status is to look at the MySQL operation, and the above kind of information through the PMA look at the basic similarity.

Show variables
Show variables is looking at MySQL's configuration parameters, and you can also use a variables like ' key% '

Show Processlist
Show Processlist is a review of ongoing processes and is useful for troubleshooting situations such as locking tables. Under normal circumstances, open MySQL slow query record is also conducive to troubleshooting.

Show Open Tables
Show open tables displays a list of the tables that are currently open.

Mysqladmin status
Use the Mysqladmin tool with MySQL to view status, using the following command
mysqladmin-uroot–password= ' Password ' status
The results shown are as follows:
uptime:87117 threads:1 questions:5481626 Slow queries:16 opens:2211 flush tables:1 Open tables:512 queries per Seco nd avg:62.923
You can also add the-I 5 parameter to automatically refresh every five seconds.
mysqladmin-uroot–password= ' Password ' status-i 5

Mysqladmin Extended-status
Similarly, you can use mysqladmin-uroot–password= ' password ' extended-status to see more MySQL running information, which is basically the same as the first view of the information.

About MySQL Run slow view method

Open MySQL Slow query
MySQL Slow query log for tracking Php+mysql system under the MySQL load tuning problem is very useful, such as installing a lot of discuz! plug-ins users, so you can probably troubleshoot those plug-ins have code problems. In fact, the slow query log to enable MySQL is very simple, only need to add log-slow-queries and long_query_time two parameters in the MySQL configuration file.

Today, a friend of mine asked me to record it in passing. More MySQL optimization information can be viewed here: http://www.ccvita.com/category/mysql

windows open MySQL slow query
MySQL in Windows system configuration file is generally My.ini find [mysqld] below plus
Log-slow-queries = F:mysqllogmysqlslowquery.log
Long_query_time = 2

MySQL slow query enabled under Linux
MySQL in Windows system configuration file is generally my.cnf find [mysqld] below plus
Log-slow-queries=/data/mysqldata/slowquery.log
long_query_time=2

Attention
Log-slow-queries = F:mysqllogmysqlslowquery.log for slow query log storage location, general this directory to have MySQL running account can write permission, generally this directory is set to MySQL data storage directory;
2 of the long_query_time=2 means that the query is recorded for more than two seconds;

Updated September 24, 2009
Add the Log-queries-not-using-indexes parameter to the MY.CNF or My.ini, which indicates that the query that does not use the index is logged. Like what:
Log-slow-queries=/data/mysqldata/slowquery.log
long_query_time=2
Log-queries-not-using-indexes

Mysqldumpslow command
/path/mysqldumpslow-s c-t 10/database/mysql/slow-log
This outputs the 10 SQL statements that have the highest number of records:

-S, is to indicate the way in which C, T, L, R are sorted according to the number of records, time, query time, the number of records returned, AC, at, AL, AR, the corresponding flashback;
-T is the meaning of top N, which is the data that returns the previous number of bars;
-G, you can write a regular matching mode, the case is not sensitive;
Like what
/path/mysqldumpslow-s r-t 10/database/mysql/slow-log
Gets the 10 queries that return the recordset the most.
/path/mysqldumpslow-s t-t 10-g "left join"/database/mysql/slow-log
Get the first 10 items in chronological order that contain the query statement with the left connection.

Summary
Using the Mysqldumpslow command can be very clear to all kinds of query statements we need, the MySQL query statement monitoring, analysis, optimization is the first step of MySQL optimization, but also a very important step