时间：2021-07-01 10:21:17 帮助过：20人阅读
由于数据库连接timeout产生页面5xx错误
由于慢查询造成页面无法加载
由于阻塞造成数据无法提交
很多数据库问题都是由于低效的查询引起的
流畅页面的访问速度
良好的网站功能体验
可以从哪几个方面进行数据库的优化？如下图所示：
根据需求写出良好的SQL，并创建有效的索引，实现某一种需求可以多种写法，这时候我们就要选择一种效率最高的写法。这个时候就要了解sql优化
根据数据库的范式，设计表结构，表结构设计的好直接关系到写SQL语句。
大多数运行在Linux机器上，如tcp连接数的限制、打开文件数的限制、安全性的限制，因此我们要对这些配置进行相应的优化。
选择适合数据库服务的cpu，更快的IO，更高的内存；cpu并不是越多越好，某些数据库版本有最大的限制，IO操作并不是减少阻塞。
注：通过上图可以看出，该金字塔中，优化的成本从下而上逐渐增高，而优化的效果会逐渐降低。
select @@version;
?
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.6.25 |
+-----------+
1 row in set (0.00 sec)
?
网址：https://dev.mysql.com/doc/sakila/en/sakila-installation.html
![(file:///C:/Users/Deborah/AppData/Local/Temp/msohtmlclip1/01/clip_image002.png)
sakila-db.zip压缩包所包含的文件如下解释
加载数据
步骤如下图所示
步骤：
1、通过命令行来连接数据库
shell> mysql -u root -p
?
2、创建表及语句执行
mysql> SOURCE C:/temp/sakila-db/sakila-schema.sql;
3、加载数据
mysql> SOURCE C:/temp/sakila-db/sakila-data.sql;
4、使用数据库
USE sakila;
5、检查创建的表
SHOW TABLES;
?
+----------------------------+
| Tables_in_sakila |
+----------------------------+
| actor |
| actor_info |
| address |
| category |
| city |
| country |
| customer |
| customer_list |
| film |
| film_actor |
| film_category |
| film_list |
| film_text |
| inventory |
| language |
| nicer_but_slower_film_list |
| payment |
| rental |
| sales_by_film_category |
| sales_by_store |
| staff |
| staff_list |
| store |
+----------------------------+
6、检验数据是否加载进去
mysql> select count(*) from film;
+----------+
| count(*) |
+----------+
| 1000 |
+----------+
1 row in set (0.00 sec)
?
?
mysql> select count(*) from payment ;
+----------+
| count(*) |
+----------+
| 16049 |
+----------+
1 row in set (0.00 sec)
?
mysql> select count(*) from staff ;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
?
mysql> select count(*) from store;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
检验数据是否加载进去
注：该表结构关系是用工具生成的。（powerDisigner）
MySQL慢查日志的开启方式和存储格式
show variables like ‘slow_query_log‘
?
mysql> show variables like ‘slow_query_log‘;
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF |
+----------------+-------+
1 row in set (0.00 sec)
mysql> show variables like ‘%log%‘; +-----------------------------------------+------------------------------------+ | Variable_name | Value | +-----------------------------------------+------------------------------------+ | back_log |80 | | binlog_cache_size | 32768 | | binlog_checksum | CRC32 | | binlog_direct_non_transactional_updates |OFF | | binlog_error_action | IGNORE_ERROR | | binlog_format | STATEMENT | | binlog_gtid_simple_recovery | OFF | | binlog_max_flush_queue_time | 0 | | binlog_order_commits | ON | | binlog_row_image | FULL | | binlog_rows_query_log_events | OFF | | binlog_stmt_cache_size | 32768 | | binlogging_impossible_mode | IGNORE_ERROR | | expire_logs_days | 0 | | general_log | OFF | | general_log_file |/var/lib/mysql/mysql-host.log | | innodb_api_enable_binlog | OFF | | innodb_flush_log_at_timeout | 1 | | innodb_flush_log_at_trx_commit | 1 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_buffer_size | 8388608 | | innodb_log_compressed_pages | ON | | innodb_log_file_size | 50331648 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_mirrored_log_groups | 1 | | innodb_online_alter_log_max_size | 134217728 | | innodb_undo_logs | 128 | | log_bin | OFF | | log_bin_basename | | | log_bin_index | | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | log_error |/var/log/mysqld.log | | log_output | FILE | | log_queries_not_using_indexes | ON | | log_slave_updates | OFF | | log_slow_admin_statements | OFF | | log_slow_slave_statements | OFF | |log_throttle_queries_not_using_indexes |0 | | log_warnings | 1 | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | max_relay_log_size | 0 | | relay_log | | | relay_log_basename | | | relay_log_index | | | relay_log_info_file | relay-log.info | | relay_log_info_repository | FILE | | relay_log_purge | ON | | relay_log_recovery | OFF | | relay_log_space_limit | 0 | | simplified_binlog_gtid_recovery | OFF | |slow_query_log |OFF | | slow_query_log_file |/var/lib/mysql/mysql-host-slow.log | | sql_log_bin | ON | | sql_log_off | OFF | | sync_binlog | 0 | | sync_relay_log | 10000 | | sync_relay_log_info | 10000 | +-----------------------------------------+------------------------------------+
61 rows in set (0.01 sec)
开启慢查日志：
show variables like ‘%log%‘
log_queries_not_using_indexes | ON | slow_query_log | OFF | | slow_query_log_file | /var/lib/mysql/mysql-slow.log | //通过这个命令来查看是否开启慢日志 mysql> set global slow_query_log=on; Query OK, 0 rows affected (0.32 sec)
验证慢查询日志是否开启：
# Time: 181026 0:39:29 # User@Host: root[root] @ localhost [] Id: 3 # Query_time: 0.000098 Lock_time: 0.000050 Rows_sent: 1 Rows_examined: 2 SET timestamp=1540485569; select count(*) from staff;
在mysql操作中，
Show databases;
Use sakila;
select * from store;
select * from staff;
监听日志文件，看是否写入
tail -f /var/lib/mysql/mysql-slow.log
如下图所示：
说明：
1、# Time: 180526 1:06:54 -------à查询的执行时间
2、# User@Host: root[root] @ localhost [] Id: 4 -------à执行sql的主机信息
3、# Query_time: 0.000401 Lock_time: 0.000105 Rows_sent: 2 Rows_examined: 2-------àSQL的执行信息：
Query_time：SQL的查询时间
Lock_time：锁定时间
Rows_sent：所发送的行数
Rows_examined：锁扫描的行数
4、SET timestamp=1527268014; -------àSQL执行时间
5、select * from staff; -------àSQL的执行内容
如何进行查看慢查询日志，如果开启了慢查询日志，就会生成很多的数据，然后我们就可以通过对日志的分析，生成分析报表，然后通过报表进行优化。
接下来我们查看一下这个工具的用法：
注意：在mysql数据库所在的服务器上，而不是在mysql>命令行中
该工具如何使用：mysqldumpslow -h
查看verbose信息
Mysqldumpslow -v
查看慢查询日志的前10个，mysqldumpslow 分析的结果如下
[root@mysql ~]# mysqldumpslow -t 10 /var/lib/mysql/mysql-slow.log Reading mysql slow query log from /var/lib/mysql/mysql-slow.log Count: 1 Time=0.01s (0s) Lock=0.00s (0s) Rows=1000.0 (1000), root[root]@localhost select * from film Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=200.0 (200), root[root]@localhost select * from actor Count: 2 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@localhost select count(*) from film Count: 2 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@localhost select count(*) from staff Died at /usr/bin/mysqldumpslow line 161, <> chunk 6.
如上图两条就是分析的结果，每条结果都显示是执行时间，锁定时间，发送的行数，扫描的行数
[root@mysql mysql]# tail -f /var/lib/mysql/mysql-slow.log # Time: 181026 0:41:46 # User@Host: root[root] @ localhost [] Id: 3 # Query_time: 0.000121 Lock_time: 0.000059 Rows_sent: 1 Rows_examined: 2 SET timestamp=1540485706; select count(*) from staff; # Time: 181026 0:42:12 # User@Host: root[root] @ localhost [] Id: 3 # Query_time: 0.000634 Lock_time: 0.000069 Rows_sent: 200 Rows_examined: 200 SET timestamp=1540485732; select * from actor; # Time: 181026 1:05:41 # User@Host: root[root] @ localhost [] Id: 3 # Query_time: 0.008336 Lock_time: 0.000101 Rows_sent: 1000 Rows_examined: 1000 SET timestamp=1540487141; select * from film;
这个工具是最常用的工具，通过安装mysql进行附带安装，但是该工具统计的结果比较少，对我们的优化锁表现的数据还是比较少。
作为一名优秀的mysql dba也需要有掌握几个好用的mysql管理工具，所以我也一直在整理和查找一些能够便于管理mysql的利器。以后的一段时间内，将会花一大部分的精力去搜索这些工具。
性 能的管理一直都是摆在第一位的，dba的很多工作管理层都看不到也没有办法衡量价值，但是如果一个系统慢的跟蜗牛一样，dba通过监控调优把系统从崩溃边缘重新拉回到高铁时代。这种价值和触动应该是巨大的。（很多企业的领导认为系统跑不动了就需要换更快的CPU、更大的内存、更快的存储，而且这还不是少数，所以DBA的价值也一直体现不出来，薪水自然也就不会很高）
mysql 的日志是跟踪mysql性能瓶颈的最快和最直接的方式了，系统性能出现瓶颈的时候，首先要打开慢查询日志，进行跟踪；这段时间关于慢查询日志的管理和查看已经整理过两篇文章了，不经意间又发现了一个查看慢查询日志的工具：mk-query-digest，这个工具网上号称mysql dba必须掌握的十大工具之首。
命令行中输入：pt-summary
显示如下图所示：说明安装成功！输入【[root@node03 mysql]# pt-query-digest --help】
[root@mysql ~]# pt-summary --help
Usage: pt-summary
For more information, ‘man pt-summary‘ or ‘perldoc /usr/bin/pt-summary‘.
Command line options:
--config Read this comma-separated list of config files.
--help Print help and exit.
--read-samples Create a report from the files in this directory.
--save-samples Save the collected data in this directory.
--sleep How long to sleep when gathering samples from vmstat.
--summarize-mounts
Report on mounted filesystems and disk usage.
--summarize-network
Report on network controllers and configuration.
--summarize-processes
Report on top processes and C<vmstat> output.
--version Print tool‘s version and exit.
Options and values after processing arguments:
--config (No value)
--help TRUE
--read-samples (No value)
--save-samples (No value)
--sleep 5
--summarize-mounts TRUE
--summarize-network TRUE
--summarize-processes TRUE
--version FALSE
[root@mysql ~]# pt-summary --version
pt-summary 2.2.16
[root@mysql ~]#
[root@mysql ~]# man pt-summary
PT-SUMMARY(1p) User Contributed Perl Documentation PT-SUMMARY(1p)
NAME
pt-summary - Summarize system information nicely.
SYNOPSIS
Usage: pt-summary
pt-summary conveniently summarizes the status and configuration of a server. It is not a tuning tool
or diagnosis tool. It produces a report that is easy to diff and can be pasted into emails without
losing the formatting. This tool works well on many types of Unix systems.
Download and run:
wget http://percona.com/get/pt-summary
bash ./pt-summary
RISKS
Percona Toolkit is mature, proven in the real world, and well tested, but all database tools can pose
a risk to the system and the database server. Before using this tool, please:
?· Read the toola€?s documentation
?· Review the toola€?s known "BUGS"
?· Test the tool on a non-production server
?· Backup your production server and verify the backups
DESCRIPTION
pt-summary runs a large variety of commands to inspect system status and configuration, saves the
output into files in a temporary directory, and then runs Unix commands on these results to format
them nicely. It works best when executed as a privileged user, but will also work without
privileges, although some output might not be possible to generate without root.
OUTPUT
Many of the outputs from this tool are deliberately rounded to show their magnitude but not the exact
detail. This is called fuzzy-rounding. The idea is that it doesna€?t matter whether a particular
counter is 918 or 921; such a small variation is insignificant, and only makes the output hard to
compare to other servers. Fuzzy-rounding rounds in larger increments as the input grows. It begins by
rounding to the nearest 5, then the nearest 10, nearest 25, and then repeats by a factor of 10 larger
(50, 100, 250), and so on, as the input grows.
The following is a simple report generated from a CentOS virtual machine, broken into sections with
commentary following each section. Some long lines are reformatted for clarity when reading this
documentation as a manual page in a terminal.
# Percona Toolkit System Summary Report ######################
Date | 2012-03-30 00:58:07 UTC (local TZ: EDT -0400)
Hostname | localhost.localdomain
Uptime | 20:58:06 up 1 day, 20 min, 1 user,
load average: 0.14, 0.18, 0.18
System | innotek GmbH; VirtualBox; v1.2 ()
Service Tag | 0
Platform | Linux
Release | CentOS release 5.5 (Final)
Kernel | 2.6.18-194.el5
Architecture | CPU = 32-bit, OS = 32-bit
Threading | NPTL 2.5
Compiler | GNU CC version 4.1.2 20080704 (Red Hat 4.1.2-48).
SELinux | Enforcing
Virtualized | VirtualBox
This section shows the current date and time, and a synopsis of the server and operating system.
# Processor ##################################################
Processors | physical = 1, cores = 0, virtual = 1, hyperthreading = no
Speeds | 1x2510.626
Models | 1xIntel(R) Core(TM) i5-2400S CPU @ 2.50GHz
Caches | 1x6144 KB
This section is derived from /proc/cpuinfo.
# Memory #####################################################
Total | 503.2M
Free | 29.0M
Used | physical = 474.2M, swap allocated = 1.0M,
swap used = 16.0k, virtual = 474.3M
Buffers | 33.9M
Caches | 262.6M
Dirty | 396 kB
UsedRSS | 201.9M
Swappiness | 60
DirtyPolicy | 40, 10
Locator Size Speed Form Factor Type Type Detail
======= ==== ===== =========== ==== ===========
Information about memory is gathered from "free". The Used statistic is the total of the rss sizes
displayed by "ps". The Dirty statistic for the cached value comes from /proc/meminfo. On Linux, the
swappiness settings are gathered from "sysctl". The final portion of this section is a table of the
DIMMs, which comes from "dmidecode". In this example there is no output.
# Mounted Filesystems ########################################
Filesystem Size Used Type Opts Mountpoint
/dev/mapper/VolGroup00-LogVol00 15G 17% ext3 rw /
/dev/sda1 99M 13% ext3 rw /boot
tmpfs 252M 0% tmpfs rw /dev/shm
The mounted filesystem section is a combination of information from "mount" and "df". This section is
skipped if you disable "--summarize-mounts".
# Disk Schedulers And Queue Size #############################
dm-0 | UNREADABLE
dm-1 | UNREADABLE
hdc | [cfq] 128
md0 | UNREADABLE
sda | [cfq] 128
The disk scheduler information is extracted from the /sys filesystem in Linux.
# Disk Partioning ############################################
Device Type Start End Size
============ ==== ========== ========== ==================
/dev/sda Disk 17179869184
/dev/sda1 Part 1 13 98703360
/dev/sda2 Part 14 2088 17059230720
Information about disk partitioning comes from "fdisk -l".
# Kernel Inode State #########################################
dentry-state | 10697 8559 45 0 0 0
file-nr | 960 0 50539
inode-nr | 14059 8139
These lines are from the files of the same name in the /proc/sys/fs directory on Linux. Read the
"proc" man page to learn about the meaning of these files on your system.
# LVM Volumes ################################################
LV VG Attr LSize Origin Snap% Move Log Copy% Convert
LogVol00 VolGroup00 -wi-ao 269.00G
LogVol01 VolGroup00 -wi-ao 9.75G
This section shows the output of "lvs".
# RAID Controller ############################################
Controller | No RAID controller detected
The tool can detect a variety of RAID controllers by examining "lspci" and "dmesg" information. If
the controller software is installed on the system, in many cases it is able to execute status
commands and show a summary of the RAID controllera€?s status and configuration. If your system is not
supported, please file a bug report.
# Network Config #############################################
Controller | Intel Corporation 82540EM Gigabit Ethernet Controller
FIN Timeout | 60
Port Range | 61000
The network controllers attached to the system are detected from "lspci". The TCP/IP protocol
configuration parameters are extracted from "sysctl". You can skip this section by disabling the
"--summarize-network" option.
# Interface Statistics #######################################
interface rx_bytes rx_packets rx_errors tx_bytes tx_packets tx_errors
========= ======== ========== ========= ======== ========== =========
lo 60000000 12500 0 60000000 12500 0
eth0 15000000 80000 0 1500000 10000 0
sit0 0 0 0 0 0 0
Interface statistics are gathered from "ip -s link" and are fuzzy-rounded. The columns are received
and transmitted bytes, packets, and errors. You can skip this section by disabling the
"--summarize-network" option.
# Network Connections ########################################
Connections from remote IP addresses
127.0.0.1 2
Connections to local IP addresses
127.0.0.1 2
Connections to top 10 local ports
38346 1
60875 1
States of connections
ESTABLISHED 5
LISTEN 8
This section shows a summary of network connections, retrieved from "netstat" and "fuzzy-rounded" to
make them easier to compare when the numbers grow large. There are two sub-sections showing how many
connections there are per origin and destination IP address, and a sub-section showing the count of
ports in use. The section ends with the count of the network connectionsa€? states. You can skip this
section by disabling the "--summarize-network" option.
# Top Processes ##############################################
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1 root 15 0 2072 628 540 S 0.0 0.1 0:02.55 init
2 root RT -5 0 0 0 S 0.0 0.0 0:00.00 migration/0
3 root 34 19 0 0 0 S 0.0 0.0 0:00.03 ksoftirqd/0
4 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/0
5 root 10 -5 0 0 0 S 0.0 0.0 0:00.97 events/0
6 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 khelper
7 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 kthread
10 root 10 -5 0 0 0 S 0.0 0.0 0:00.13 kblockd/0
11 root 20 -5 0 0 0 S 0.0 0.0 0:00.00 kacpid
# Notable Processes ##########################################
PID OOM COMMAND
2028 +0 sshd
This section shows the first few lines of "top" so that you can see what processes are actively using
CPU time. The notable processes include the SSH daemon and any process whose out-of-memory-killer
priority is set to 17. You can skip this section by disabling the "--summarize-processes" option.
# Simplified and fuzzy rounded vmstat (wait please) ##########
procs ---swap-- -----io---- ---system---- --------cpu--------
r b si so bi bo ir cs us sy il wa st
2 0 0 0 3 15 30 125 0 0 99 0 0
0 0 0 0 0 0 1250 800 6 10 84 0 0
0 0 0 0 0 0 1000 125 0 0 100 0 0
0 0 0 0 0 0 1000 125 0 0 100 0 0
0 0 0 0 0 450 1000 125 0 1 88 11 0
# The End ####################################################
This section is a trimmed-down sample of "vmstat 1 5", so you can see the general status of the
system at present. The values in the table are fuzzy-rounded, except for the CPU columns. You can
skip this section by disabling the "--summarize-processes" option.
OPTIONS
--config
type: string
Read this comma-separated list of config files. If specified, this must be the first option on
the command line.
--help
Print help and exit.
--read-samples
type: string
Create a report from the files in this directory.
--save-samples
type: string
Save the collected data in this directory.
--sleep
type: int; default: 5
How long to sleep when gathering samples from vmstat.
--summarize-mounts
default: yes; negatable: yes
Report on mounted filesystems and disk usage.
--summarize-network
default: yes; negatable: yes
Report on network controllers and configuration.
--summarize-processes
default: yes; negatable: yes
Report on top processes and "vmstat" output.
--version
Print toola€?s version and exit.
ENVIRONMENT
This tool does not use any environment variables.
SYSTEM REQUIREMENTS
This tool requires the Bourne shell (/bin/sh).
BUGS
For a list of known bugs, see http://www.percona.com/bugs/pt-summary <http://www.percona.com/bugs/pt-
summary>.
Please report bugs at https://bugs.launchpad.net/percona-toolkit <https://bugs.launchpad.net/percona-
toolkit>. Include the following information in your bug report:
?· Complete command-line used to run the tool
?· Tool "--version"
?· MySQL version of all servers involved
?· Output from the tool including STDERR
?· Input files (log/dump/config files, etc.)
If possible, include debugging output by running the tool with "PTDEBUG"; see "ENVIRONMENT".
DOWNLOADING
Visit http://www.percona.com/software/percona-toolkit/ <http://www.percona.com/software/percona-
toolkit/> to download the latest release of Percona Toolkit. Or, get the latest release from the
command line:
wget percona.com/get/percona-toolkit.tar.gz
wget percona.com/get/percona-toolkit.rpm
wget percona.com/get/percona-toolkit.deb
You can also get individual tools from the latest release:
wget percona.com/get/TOOL
Replace "TOOL" with the name of any tool.
AUTHORS
Baron Schwartz, Kevin van Zonneveld, and Brian Fraser
ABOUT PERCONA TOOLKIT
This tool is part of Percona Toolkit, a collection of advanced command-line tools for MySQL developed
by Percona. Percona Toolkit was forked from two projects in June, 2011: Maatkit and Aspersa. Those
projects were created by Baron Schwartz and primarily developed by him and Daniel Nichter. Visit
<http://www.percona.com/software/> to learn about other free, open-source software from Percona.
COPYRIGHT, LICENSE, AND WARRANTY
This program is copyright 2011-2015 Percona LLC and/or its affiliates, 2010-2011 Baron Schwartz.
THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, WITHOUT
LIMITATION, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
This program is free software; you can redistribute it and/or modify it under the terms of the GNU
General Public License as published by the Free Software Foundation, version 2; OR the Perl Artistic
License. On UNIX and similar systems, you can issue a€?man perlgpla€? or a€?man perlartistica€? to read
these licenses.
You should have received a copy of the GNU General Public License along with this program; if not,
write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.
VERSION
pt-summary 2.2.16
perl v5.14.2 2015-11-06 PT-SUMMARY(1p)
[root@mysql ~]# pt-diskstats #ts device rd_s rd_avkb rd_mb_s rd_mrg rd_cnc rd_rt wr_s wr_avkb wr_mb_s wr_mrg wr_cnc wr_rt busy in_prg io_s qtime stime 1.0 sda 0.0 0.0 0.0 0% 0.0 0.0 5.0 4.0 0.0 0% 0.0 4.8 0% 0 5.0 3.8 1.0 1.0 sda2 0.0 0.0 0.0 0% 0.0 0.0 5.0 4.0 0.0 0% 0.0 4.8 0% 0 5.0 3.8 1.0 1.0 dm-0 0.0 0.0 0.0 0% 0.0 0.0 5.0 4.0 0.0 0% 0.0 4.8 0% 0 5.0 3.8 1.0 1.0 sda 0.0 0.0 0.0 0% 0.0 0.0 0.0 0.0 0.0 0% 0.0 0.0 0% 0 0.0 0.0 0.0 1.0 sda2 0.0 0.0 0.0 0% 0.0 0.0 0.0 0.0 0.0 0% 0.0 0.0 0% 0 0.0 0.0 0.0 1.0 dm-0 0.0 0.0 0.0 0% 0.0 0.0 0.0 0.0 0.0 0% 0.0 0.0 0% 0 0.0 0.0 0.0 1.0 sda 0.0 0.0 0.0 0% 0.0 0.0 0.0 0.0 0.0 0% 0.0 0.0 0% 0 0.0 0.0 0.0 1.0 sda2 0.0 0.0 0.0 0% 0.0 0.0 0.0 0.0 0.0 0% 0.0 0.0 0% 0 0.0 0.0 0.0 1.0 dm-0 0.0 0.0 0.0 0% 0.0 0.0 0.0 0.0 0.0 0% 0.0 0.0 0% 0 0.0 0.0 0.0
[root@mysql ~]# pt-query-digest /var/lib/mysql/mysql-slow.log # 410ms user time, 110ms system time, 24.40M rss, 204.76M vsz # Current date: Fri Oct 26 02:02:37 2018 # Hostname: mysql # Files: /var/lib/mysql/mysql-slow.log # Overall: 8 total, 6 unique, 0.00 QPS, 0.00x concurrency ________________ # Time range: 2018-10-26 00:36:02 to 01:50:11 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 27ms 98us 15ms 3ms 15ms 5ms 1ms # Lock time 2ms 50us 2ms 293us 2ms 471us 105us # Rows sent 1.18k 1 1000 150.75 964.41 315.86 0.99 # Rows examine 3.14k 2 1000 401.88 964.41 450.80 578.59 # Query size 279 18 92 34.88 88.31 22.24 24.84 # Profile # Rank Query ID Response time Calls R/Call V/M Item # ==== ================== ============= ===== ====== ===== =============== # 1 0x3A23B0CB7839AF05 0.0153 55.9% 1 0.0153 0.00 SELECT INFORMATION_SCHEMA.TRIGGERS # 2 0x687D590364E29465 0.0083 30.5% 1 0.0083 0.00 SELECT film # 3 0x9134F278CE0AB549 0.0017 6.2% 1 0.0017 0.00 SELECT mysql.user # 4 0xEBA2FBA69B1FF476 0.0012 4.2% 2 0.0006 0.00 SELECT film # MISC 0xMISC 0.0009 3.1% 3 0.0003 0.0 <2 ITEMS> # Query 1: 0 QPS, 0x concurrency, ID 0x3A23B0CB7839AF05 at byte 1678 _____ # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: all events occurred at 2018-10-26 01:50:11 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 12 1 # Exec time 55 15ms 15ms 15ms 15ms 15ms 0 15ms # Lock time 16 387us 387us 387us 387us 387us 0 387us # Rows sent 0 1 1 1 1 1 0 1 # Rows examine 0 6 6 6 6 6 0 6 # Query size 17 48 48 48 48 48 0 48 # String: # Databases sakila # Hosts localhost # Users root # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `INFORMATION_SCHEMA` LIKE ‘TRIGGERS‘\G # SHOW CREATE TABLE `INFORMATION_SCHEMA`.`TRIGGERS`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM INFORMATION_SCHEMA.TRIGGERS\G # Query 2: 0 QPS, 0x concurrency, ID 0x687D590364E29465 at byte 1208 _____ # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: all events occurred at 2018-10-26 01:05:41 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 12 1 # Exec time 30 8ms 8ms 8ms 8ms 8ms 0 8ms # Lock time 4 101us 101us 101us 101us 101us 0 101us # Rows sent 82 1000 1000 1000 1000 1000 0 1000 # Rows examine 31 1000 1000 1000 1000 1000 0 1000 # Query size 6 18 18 18 18 18 0 18 # String: # Databases sakila # Hosts localhost # Users root # Query_time distribution # 1us # 10us # 100us # 1ms ################################################################ # 10ms # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `sakila` LIKE ‘film‘\G # SHOW CREATE TABLE `sakila`.`film`\G # EXPLAIN /*!50100 PARTITIONS*/ select * from film\G # Query 3: 0 QPS, 0x concurrency, ID 0x9134F278CE0AB549 at byte 1409 _____ # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: all events occurred at 2018-10-26 01:50:11 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 12 1 # Exec time 6 2ms 2ms 2ms 2ms 2ms 0 2ms # Lock time 64 2ms 2ms 2ms 2ms 2ms 0 2ms # Rows sent 0 1 1 1 1 1 0 1 # Rows examine 0 5 5 5 5 5 0 5 # Query size 32 92 92 92 92 92 0 92 # String: # Databases sakila # Hosts localhost # Users root # Query_time distribution # 1us # 10us # 100us # 1ms ################################################################ # 10ms # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `mysql` LIKE ‘user‘\G # SHOW CREATE TABLE `mysql`.`user`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*), SUM(user=""), SUM(password=""), SUM(password NOT LIKE "*%") FROM mysql.user\G # Query 4: 0.01 QPS, 0.00x concurrency, ID 0xEBA2FBA69B1FF476 at byte 0 __ # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2018-10-26 00:36:02 to 00:39:22 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 25 2 # Exec time 4 1ms 413us 746us 579us 746us 235us 579us # Lock time 7 167us 51us 116us 83us 116us 45us 83us # Rows sent 0 2 1 1 1 1 0 1 # Rows examine 62 1.95k 1000 1000 1000 1000 0 1000 # Query size 17 50 25