Mysql-one of slow query logs, Mysql-one of slow logs

Source: Internet
Author: User
Tags time 0 mysql slow query log percona

Mysql-one of slow query logs, Mysql-one of slow logs
Slow query log
1. When no results are returned after a certain period of time, the slow query log is recorded.
2. Slow query logs can help DBAs find SQL statements with slow execution efficiency and help database optimization.
3. Slow query logs are disabled by default, and do not need to be enabled all the time. (If enabled, the system performance will be consumed)
4. Enable sampling analysis manually.
Slow query log parameters
After modifying the following configuration in mysql. cnf, You need to restart the Database Service to make it take effect.
Slow_query_log = on | off --- whether to enable slow query logs
Slow_query_log_file = filename --- specify the log storage path and file name for slow query. If this parameter is not set, the default value is used. The default storage location is under the directory where the database file is located, named hostname-slow.log
Long_query_time = 2 --- the query statement that specifies the number of seconds for which no results are returned is a slow query.
Long-queries-not-using-indexes --- records all query statements that are not indexed.
Min_examined_row_limit = 1000 --- record the slow queries caused by an extra 1000 queries.
Log-slow-admin-statements --- records slow OPTIMIZE tables, ANALYZE tables, and alter table statements.
Log-slow-slave-statements --- records slow queries generated by slave.
Modify the slow query configuration without restarting
SET @ global. slow_query_log = 1 --- 1 enable 0 disable
SET @ global. long_query_time = 3
Or
SET global slow_query_log = 1
SET global long_query_time = 3
For other parameters, run the following command:
Show variables like '% slow % ';
Enable slow query log for Model:
Mysql> set global slow_query_log = 1;
Query OK, 0 rows affected (0.22 sec)

Mysql> set global long_query_time = 2;
Query OK, 0 rows affected (0.04 sec)

[Root @ gusha ~] # Cd/var/lib/mysql/
[Root @ gusha mysql] # pwd
/Var/lib/mysql
[Root @ gusha mysql] # ls --- the directory contains an extra host name-slow. log file.
Auto. cnf gusha.wyzc.com. err ib_logfile1 RPM_UPGRADE_HISTORY
Gusha gusha.wyzc.com. pid mysql RPM_UPGRADE_MARKER-LAST.
Gusha. log ibdata1 mysql. sock t1.ibd
Gusha-slow.log ib_logfile0 performance_schema worldb
Mysql> select sleep (3 );
+ ---------- +
| Sleep (3) |
+ ---------- +
| 0 |
+ ---------- +
1 row in set (3.05 sec)


Mysql> select variables like 'long % ';
ERROR 1054 (42S22): Unknown column 'variables 'in 'field list'
Mysql> show variables like 'long % ';
+ ----------------- + ---------- +
| Variable_name | Value |
+ ----------------- + ---------- +
| Long_query_time | 2.000000 |
+ ----------------- + ---------- +
1 row in set (0.25 sec)
[Root @ gusha mysql] # tail-f gusha-slow.log --- view information related to the log
/Usr/sbin/mysqld, Version: 5.6.16 (MySQL Community Server (GPL). started:
Tcp port: 3306 Unix socket:/var/lib/mysql. sock
Time Id Command Argument
# Time: 150204 3:13:06
# User @ Host: root [root] @ localhost [] Id: 23
# Query_time: 3.022983 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp = 1422990786;
Select sleep (3 );
Common slow query and analysis tools
1. mysqldumpslow --- mysql tool, which is not friendly
2. mysqlsla
An analysis tool developed by hackmysql.com. You can download it from your website, or enter the following command at a system prompt to download it:
# Wget http://hackmysql.com/scripts/mysqlsla
3. pt-query-digest in percona-toolkit --- pay attention to the dependent package during installation
A-mysqldumpslow
[Root @ gusha mysql] # mysqldumpslow gusha-slow.log --- slow Log File Name

Reading mysql slow query log from gusha-slow.log
Count: 1 Time = 3.02 s (3 s) Lock = 0.00 s (0 s) Rows = 1.0 (1), root [root] @ localhost
Select sleep (N)
B-mysqlsla
[Root @ gusha ~] # Mysql-7/cd/var/
[Root @ gusha mysql-7] # ls
MySQL-5.6.17-1.el6.src.rpm mysqlsla-2.03
Mysql-5.6.17.tar.gz percona-toolkit-2.2.7-1.noarch.rpm
[Root @ gusha mysql-7] # chmod + x mysqlsla-2.03
[Root @ gusha mysql-7] #./mysqlsla-2.03/var/lib/mysql/gusha-slow.log
Auto-detected logs as slow logs
Reports for slow logs:/var/lib/mysql/gusha-slow.log
1 queries total, 1 unique
Sorted by 't_ Sum'
Grand Totals: Time 3 s, Lock 0 s, Rows sent 1, Rows Examined 0
__________________________________________________________________________ 001 ___
Count: 1 (100.00%)
Time: 3.022983 s total, 3.022983 s avg, 3.022983 s to 3.022983 s max (100.00%)
Lock Time (s): 0 total, 0 avg, 0 to 0 max (0.00%)
Rows sent: 1 avg, 1 to 1 max (100.00%)
Rows examined: 0 avg, 0 to 0 max (0.00%)
Database:
Users:
Root @ localhost: 100.00% (1) of query, 100.00% (1) of all users

Query abstract:
SET timestamp = N; SELECT sleep (N );

Query sample:
SET timestamp = 1422990786;
Select sleep (3 );
C-percona-toolkit
[Root @ gusha mysql-7] # yum install percona-toolkit-2.2.7-1.noarch.rpm
.......
[Root @ gusha mysql-7] # pt-query-digest/var/lib/mysql/gusha-slow.log

#170 ms user time, 20 ms system time, 23.98 M rss, 202.45 M vsz
# Current date: Wed Feb 4 03:33:40 2015
# Hostname: gusha.wyzc.com
# Files: // var/lib/mysql/gusha-slow.log
# Overall: 1 total, 1 unique, 0 QPS, 0x concurrency ______________________
# Time range: all events occurred at 03:13:06
# Attribute total min max avg 95% stddev median
#===================================================== ======================================
# Exec time 3 s 3 s 3 s 3 s 3 s 0 3 s
# Lock time 0 0 0 0 0 0 0 0
# Rows sent 1 1 1 1 1 0 1
# Rows examine 0 0 0 0 0 0 0
# Query size 15 15 15 15 15 0 15


# Profile
# Rank Query ID Response time callr/Call V/M Item
#===================================================== ============================
#1 0xF9A57DD5A41825CA 3.0230 100.0% 1 3.0230 0.00 SELECT


# Query 1: 0 QPS, 0x concurrency, ID 0xF9A57DD5A41825CA at byte 0 ________
# This item is wrongly ded in the report because it matches -- limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 03:13:06
# Attribute pct total min max avg 95% stddev median
#===================================================== ======================================
# Count 100 1
# Exec time 100 3 s 3 s 3 s 3 s 3 s 0 3 s
# Lock time 0 0 0 0 0 0 0 0 0
# Rows sent 100 1 1 1 1 1 0 1
# Rows examine 0 0 0 0 0 0 0 0
# Query size 100 15 15 15 15 15 0 15
# String:
# Hosts localhost
# Users root
# Query_time distribution
# 1us
# 10us
# 100us
#1 ms
#10 ms
#100 ms
#1 s ##################################### ###########################
#10 s +
# EXPLAIN /*! 50100 PARTITIONS */
Select sleep (3) \ G
Delete slow query logs
Slow query logs will continue to grow. Therefore, this function is usually enabled only when special tasks such as sampling and analysis are performed, and disabled when other tasks are performed. Remember to delete useless slow logs in time.
There are two ways to delete slow query logs:
1. The operating system command is deleted directly.
2. Use the flush logs command at the MySQL prompt or the mysqladmin flush-logs command at the system prompt.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.