同乐学堂一、数据库是做什么的？
答：我们们可以通过它访问大量的持久化数据。
二、运维需要对数据库做些什么？
答：尽可能地保证数据库的高效、可靠、易用、安全、高并发和高吞吐
三、让我们看看安装完MySQL之后它目录底下的布拉布拉~一堆堆是干嘛的？
3.1 先来简单看下数据库数据目录下的一些文件，这些看着很眼熟，却不造干嘛的，很多同手想动手删除它的绝对不止我一个，但是告诉你，如果您删除其中一个你不认识的文件
造成的灾难是无法估量的，别害怕，小编来带你认识这些文件！即使误删除，碰到比较厉害的DBA，也能做很快的做硬件恢复。只有你想不到，没有DBA做不到！描述当时操作环境和出现的结果很重要，在哪个领域都适用。不仅仅是IT领域！
（1）db.opt
数据库的结构定义和设置。
（2）*.frm
数据表的结构定义。
（3）*.MYD
MyISAM表数据。
（4）*.MYI
MyISAM索引数据。
（5）ibdata*
InnoDB表空间数据文件。
如果将innodb_file_per_table设置为1，那么InnoDB数据表可以各自存储为一个文件，称为独立表空间。如果innodb_file_per_table等于0，那么InnoDB数据表则可以统一存放在一个共享表空间里。默认innodb_file_per_table等于0，即InnoDB将使用共享表空间的方式，所有的数据都会存储在类似ibdata*这样的文件内。
（6）ib_logfile*
InnoDB日志数据。
（7）*.idb
InnoDB数据和索引（当将innodb_file_per_table设置为1，即为独立表空间的方式）。
（8）*.trg
触发器。
以下将主要讨论InnoDB表空间数据文件和它的日志文件。
如果你指定了无InnoDB配置选项，那么MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件。对于一般的生产负荷来说，这种配置太小了，可能会导致性能问题，所以需要手动设置大小。笔者建议日志文件应大于256MB，数据文件初始可以分配1GB到5GB，并设置为自动扩展，这样的配置在一般情况下已经够用了，相关的配置项设置如下。
innodb_data_file_path = ibdata1:1000M:autoextend innodb_log_file_size = 256M
innodb_data_file_path的值应该为一个或多个数据文件规格的列表。如果要命名一个以上的数据文件，请用分号“;”分隔它们。其语法格式为：innodb_data_file_path=datafile_spec1[;datafile_spec2]...
例如：
innodb_data_file_path=ibdata1:5000M;ibdata2:5000M:autoextend
其中，autoextend属性和后面跟着的属性只能被用于innodb_data_file_path行里的最后一个数据文件。
如果对最后的数据文件指定autoextend选项，那么当数据文件耗尽表空间中的自由空间时，InnoDB就会扩展这个数据文件，扩展的幅度默认是每次8MB。
3.2.独立表空间的原理和设置
共享表空间的使用很简单，维护方便，同时它也是MySQL默认的配置，所以在生产中得到了广泛的应用，但它也存在一些劣势，使用共享表空间比较明显的缺点是，不能快速回收删除大表的空间，I/O操作可能会消耗更多的资源等待。而独立表空间是很多DBA推荐使用的方式，它刚好在这两点上弥补了共享表空间的不足。使用独立表空间，可以在它自己的文件中存储每个InnoDB表和它的索引，这种情况下，每个表都有它自己的表空间。
可以向my.cnf的[mysqld]节中添加下面的语句来允许使用独立表空间，重启MySQL实例（MySQL Server）即可生效。
[mysqld]
innodb_file_per_table
重启实例之后，InnoDB将会把每个新创建的表存储到数据库目录下的文件tbl_name.ibd中。这类似于MyISAM存储引擎所做的，但MyISAM是把表分成数据文件tbl_name.MYD和索引文件tbl_name.MYI。对于InnoDB，数据和索引则会被一起存放到.ibd文件中。不过tbl_name.frm文件照旧会被创建。
如果从my.cnf文件里删除了innodb_file_per_table行，并重启了实例，那么InnoDB将会在共享的表空间文件里再次创建表。也就是说，innodb_file_per_table只会影响表的创建。如果用这个选项启动实例，那么新表将会被.ibd文件创建，但是你仍然能够访问共享表空间中的表。如果删掉了这个选项，那么新表将在共享表空间内被创建，但是你仍然可以访问用独立表空间创建的任何表。
即使使用了独立表空间，也仍然有一部分共享数据需要存放在共享表空间内，所以idata*文件仍然存在。
你不能像对待MyISAM一样，在数据目录之间随意地移动.ibd文件。这是因为表定义是被存放在InnoDB共享表空间内的，而且InnoDB必须保持事务ID和事务日志顺序号的一致性。
如果某个数据文件变得很大，比如上百GB，这时你可能想要另外增加一个数据文件；或者磁盘已满，这时你想要把其他数据添加到另一个硬盘上，那么这时可以手动添加一个数据文件。
3.3.InnoDB增加数据文件
手动增加一个数据文件时需要重启MySQL实例，我们可以计算出最后一个文件的大小（针对按MB计算的大小取整，即字节数除以10242，再四舍五入），然后修改配置文件，把innodb_data_file_path参数指定的最后一个文件大小设置为该值，并在其后继续追加新的数据文件。
解决方案具体如下。
当你要添加一个新文件名到innodb_data_file_path参数指定的文件名列表时，请确信它并不存在。当你重启实例时，InnoDB会创建并初始化这个文件。
如果最后一个数据文件是用关键字autoextend定义的，那么在编辑my.cnf文件时必须考虑最后一个数据文件已经增长到多大了。你需要获取这个数据文件的大小，四舍五入，使其最接近1024*1024 bytes的乘积（即1MB），然后在innodb_data_file_path中明确指定大致的尺寸。然后添加另一个数据文件。记住，只有innodb_data_file_path里的最后一个数据文件才可以被指定为自动扩展。
如下是一个修改数据文件大小的示例。
首先关闭实例，查看最后一个数据文件的大小。如下是Linux操作系统ll命令的输出。
-rw-rw--- 1 mysql mysql 10829692928 Mar 10 10:27 ibdata4
然后计算最后一个数据文件的大小。
10829692928/1024/1024=10328 MB（四舍五入）
那么对原配置文件：
innodb_data_file_path = ibdata1:4000M;ibdata2:4000M;ibdata3:4000M;ibdata4:4000M:autoextend做如下修改，增加一个数据文件ibdata5，初始值为8000MB，可自动扩展。
innodb_data_file_path = ibdata1:4000M;ibdata2:4000M;ibdata3:4000M;ibdata4:10328M;ibdata5:8000M:autoextend
最后，重新启动实例，MySQL Server会自动创建ibdata5。
4.改变InnoDB事务日志大小
不要试图通过直接更改配置文件来设置InnoDB事务日志的大小，这会导致不能启动数据库。如果想要改变InnoDB事务日志文件的数量和大小，那么必须要停止MySQL实例，并确定它被无错误地关闭了。随后复制旧日志文件到一个安全的地方作为备份，万一出错还可以恢复，然后从日志文件目录删除所有的旧日志文件，之后编辑my.cnf改变日志文件配置，并再次启动MySQL实例。mysqld在启动之时会发现没有日志文件，然后告诉你它正在创建一个新的日志文件。
更改InnoDB事务日志大小的具体步骤如下。
1）干净关闭MySQL。
2）使用mv命令移走旧的InnoDB事务日志。
3）修改配置文件，更改innodb_log_file_size。
4）启动MySQL。
注意，在旧版本的MySQL中，所有事务日志大小的总和不能超过4GB。MySQL 5.6将总大小的限制扩展到了512GB。
5.InnoDB的undo区域
undo区域也称为undo空间、undo表空间，是InnoDB设计的一个特殊存储区域，它保存了被活动事务更改的数据的副本（前像），如果另一个事务需要查看原来的数据（例如，满足一致性读），那么可以从undo区域中获得未被更改的数据。默认情况下，undo区域也是在InnoDB共享表空间内。MySQL的更高版本（MySQL 5.6及以上）也提供了该选项，可以把undo空间放到独立的表空间里，这样就可以把undo表空间放到其他更快的磁盘设备上，进行专门的优化。
如果undo暴涨可能会把共享表空间撑大。出现这种情况，可能是因为写负载很大，比如执行了大量的删除和修改操作，但在生产环境中，更可能出现的一种情况是存在长时间未提交的事务。
如果一个事务长时间未提交，而我们默认使用的是repeatable read事务隔离级别，那么InnoDB不会去清理旧的行版本（old row versions），因为未提交的事务仍然需要看到它。当这个事务一直保持打开而不提交，就可能会导致大量旧的版本数据无法删除，从而导致undo暴涨。将事务的隔离级别更改为read committed可以解决此问题。但根本的处理措施还是检查代码，找到未提交的事务。
通过命令 show engine innodb status\G的输出，可以看到当前有多少没有被清理的记录。对比下面的Purge done for trx和Trx id counter，如果差异很大，则可能是因为大量事务所导致，也可能是操作大量数据的个别事务所导致的。
<span style="font-size: 16px;"--<----------
TRANSACTIONS
<span style="font-size: 16px;"--<----------
Trx id counter 0 80157601
Purge done for trx
s n:o <0 80154573 undo n:o <0 0
对于写操作很频繁的应用，InnoDB清理线程的速度可能会跟不上，从而导致undo表空间越来越大，可以通过设置innodb_max_purge_lag参数，来避免InnoDB表空间的过分增大。InnoDB事务系统维持了一个事务列表，该列表记录被UPDATE或DELETE操作标志为删除的索引记录。这个列表的长度为purge_lag。当purge_lag超过innodb_max_purge_lag之时，每个INSERT、UPDATE和DELETE操作都将被延迟一定的时间，比如我们可以将其设置为100万。即允许有100万条未清理的记录，在达到100万的阈值后，就会触发延迟其他的查询操作。
简而言之，undo里保存了数据的前像，它可以满足一致性查询，同时，在灾难恢复过程中，它也扮演了重要的角色，它的主要功能是在灾难恢复过程中回滚那些没有提交的变更。灾难恢复的具体过程请参考10.2节。
10.1.3 临时文件
MySQL使用环境变量TMPDIR的值作为保存临时文件的目录路径名。如果未设置TMPDIR，那么MySQL将使用系统的默认值，通常为/tmp、/var/tmp或/usr/tmp。如果包含临时文件目录的文件系统过小，则可以对mysqld使用“--tmpdir”选项，在具有足够空间的文件系统内指定1个目录，或者修改配置文件内的参数tmpdir。
在MySQL 5.1中，“--tmpdir”选项可被设置为多个路径的列表，以循环的方式使用。在Unix平台上，路径可用冒号字符“:”隔开，在Windows、NetWare和OS/2平台上，路径可用分号字符“;”隔开。注意，为了有效地分布负载，这些路径应位于不同的物理磁盘上，而不是位于相同磁盘的不同分区中。
如果MySQL服务器正作为复制从服务器使用，那么不应将“--tmpdir”设置为指向基于内存的文件系统的目录，或者当服务器主机重启时将要清空的目录。对于复制从服务器，需要在机器重启时仍保留一些临时文件，以便能够复制临时表或执行LOAD DATA INFILE操作，如果在服务器重启时丢失了临时文件目录下的文件，那么复制将会失败。
MySQL会以隐含的方式创建所有的临时文件。这样，就能确保在中止mysqld时会删除所有的临时文件。使用隐含文件的缺点在于，在临时文件目录所在的位置中，看不到占用了文件系统的大临时文件。
进行排序时（ORDER BY或GROUP BY），MySQL通常会使用1个或多个临时
文件。对于大数据量的排序，临时空间可能会超过/tmp空间，此时，执行查询将会失败，MySQL错误日志里会出现错误记录“sort abort”。解决方案是优化查询或把临时目录设置到另一个空间足够大的分区中。
对于某些SELECT查询，MySQL还会创建临时SQL表，它们有sql_*形式的名称。ALTER TABLE会在与原始表目录相同的目录下创建临时表。
10.1.4 MySQL套接字文件（重点标注）
服务器用来与本地客户端进行通信的Linux套接字文件（也称为socket文件），其默认位置是/tmp/mysql.sock。此文件位于/tmp目录下可能会导致一些问题，原因在于，在某些版本的Linux上，任何人都能删除/tmp目录下的文件。在Linux系统下，系统会自动删除/tmp目录下的一些文件，但并不会删除socket文件。但某些没有经验的系统管理员可能配置了定时任务去删除/tmp目录下的文件，很可能连socket文件也会被删除，这将导致MySQL无法通过socket文件的方式进行登录。由于现在的服务器一般都很强劲，多实例的配置也很普遍，建议不要将socket文件集中放在/tmp目录下，最好是放在单独的实例自身的目录中。我们可以在全局配置文件中指定socket文件路径。例如，将下述行置于文件/etc/my.cnf中。
[mysqld]
socket=/path/to/socket
[client]
socket=/path/to/socket
如果你不放心socket文件，那么可以保留默认的root的其他登录方式，默认的root账号可以通过socket文件或127.0.0.1进行登录。建议保留127.0.0.1的root登录账号，以防socket文件被异常清除。
10.2 MySQL如何进行灾难恢复
MySQL的灾难恢复类似于其他传统数据库的灾难恢复。
MySQL靠预写式日志（Write-Ahead Logging，WAL）来保证持久性，也就是说，数据文件不会马上写入脏数据，而是会先写日志。InnoDB的脏数据是存在于innodb_buffer_pool里的，它会按一定的机制批量刷新到磁盘，这样做可以提高吞吐率。
脏数据权威解读：数据库技术中,脏数据在临时更新（脏读）中产生。事务A更新了某个数据项X，但是由于某种原因，事务A出现了问题，于是要把A回滚。但是在回滚之前，另一个事务B读取了数据项X的值(A更新后)，A回滚了事务，数据项恢复了原值。事务B读取的就是数据项X的就是一个“临时”的值，就是脏数据。
我们把上面这种日志称为redo日志，即InnoDB的事务日志。如果突然断电了，那么InnoDB是不能保证数据已经写入磁盘的，数据库重启后，MySQL需要知道当时执行的操作是成功了还是部分成功或失败了这时，只要使用了预写式日志，程序就可以检查redo日志，并将突然断电时计划执行的操作内容跟实际上执行的操作内容进行比较。在这个比较的基础上，MySQL就可以决定是撤销已做的操作还是继续完成相应的操作，或者是保持原样。这就是灾难恢复的过程。
由于MySQL知道宕机时有哪些日志是还没有被实际写入到数据文件的，所以它会找到事务日志的某个点，把这个点之后的日志运行一遍，这个时候就会产生一个新的问题，虽然把所有日志都执行了一遍，但有一些更改并没有被提交，需要回滚。我们配合undo日志（在undo区域内）可以确定哪些变更是需要回滚的，然后回滚那些没有提交的日志，简单地说，灾难恢复过程可以分为redo（重做）和undo（回退）两个步骤。
由上可知，InnoDB事务日志在很大程度上决定了数据的安全性，事务日志的持久性决定了灾难恢复后最多丢失了多少记录？事务日志都是顺序写入的，因此可以设置参数来调整commit（事务提交）时写入事务日志的频率。MySQL的事务日志刷新可能会出现如下3种情况。
（1）innodb_flush_log_at_trx=1
每次commit时都写入磁盘。这样理论上我们只会丢失一个事务。
（2）innodb_flush_log_at_trx=2
每次commit时，写日志只缓冲（buffer）到操作系统缓存，但不刷新到磁盘，InnoDB会每秒刷新一次日志，所以宕机丢失的是最近1秒的事务。生产环境中建议使用此配置。
（3）innodb_flush_log_at_trx=0
每秒把日志缓冲区的内容写到日志文件，并且刷新到磁盘，但commit时什么也不做。
数据文件的写操作，可能会将块写坏，MySQL设计了一个数据存储区域双写缓冲（double write buffer），InnoDB使用双写缓冲来确保数据的安全，避免损坏块。双写缓冲是InnoDB表空间的一个特殊的区域，主要用于写入页的备份，并且是顺序写入。当InnoDB刷新数据（从InnoDB缓冲池到磁盘）时，首先写入双写缓冲，然后写入实际数据文件。这样即可确保所有写操作的原子性和持久性。
崩溃重启后，Innodb会检查每个块（page）的校验和，判断块是否损坏，如果写入双写缓冲的是坏块，那么显然没有写入实际数据文件，就要用实际数据文件的块来恢复双写缓冲，如果写入了双写缓冲，但是数据文件写的是坏块，那么就用双写缓冲的块来重写数据文件。这样的机制虽然提供了安全保障，但也增加了I/O。
对于读操作，InnoDB通过页校验码来保证数据的存取，每页在内存中都先算好一个校验值，放在文件头部，写入的时候先写校验值，读的时候也会校验一下校验值。
通过如上描述的预写式日志机制和双写缓冲区域，MySQL提供了极佳的灾难恢复性。MySQL的稳定版本很少会因为主机断电等硬件故障而导致数据损坏。
10.3 变量设置、配置文件和主要参数
10.3.1 概述
很多人都喜欢研究各种参数配置文件，然后给自己的生产环境加上很多参数。笔者的建议是，可以去研究它，测试它，但是在生产环境中，你应该在确定某个选项能解决特定的性能问题时，才去设置它，否则你应该尽量保持简单。配置文件添加了过多的参数可能会导致混淆，维护性可能会变差，后来接手的DBA往往会问，为什么要这么设置。实际的数据库产品中，很多参数只有在特定的上下文中才有意义，时过境迁，一些参数可能反而会成为性能问题的根源所在。所以建议让生产环境的配置文件尽可能地保持简单，在确定需要时，才去设置相应的参数。
另外，数据库配置文件所起的作用有限。系统的性能更多地取决于物理部署和架构，取决于数据库设计、索引和SQL质量。设置好正确的基本参数之后，最好就不用再去关注它，应该花费更多的时间在库表设计、索引和查询优化上。
官方的安装包内有附带的示例配置文件，但不建议使用。里面的一些设置不太符合生产实践，可能会有误导，而且这些配置也过时了，不适合现在的硬件和负载，也不适合互联网公司流量比较大的业务。
本章稍后会给出一份比较简单的配置文件，大家可以去对比下，然后检验下你的生产环境设置得是否合理。注意，适合生产环境的才是最佳的，而任何建议的参考配置文件，往往是不可能覆盖到各种应用类型的，仅仅是为你的决策提供一个参照物。所以，仍然建议以自己的生产配置为准。
10.3.2 如何设置参数、变量
配置文件内的参数需要尽量保持一样的书写风格，要么都是用下划线（如slow_query_log_file）要么都使用中线（slow-query-log-file）。
配置文件内的参数有些是影响全局的，有些是会话（session）级别的，即我们也可以在独立的连接内进行设置。
sort_buffer_size可用于设置全局和会话级，如下：
SET sort_buffer_size = <value>; #设置会话级。
SET GLOBAL sort_buffer_size = <value>; #设置全局。
set sort_buffer_size =default; #恢复默认值。
生产中尽量不要使用32位系统，32位系统的机器有内存寻址的限制，不能突破二点几GB的限制。如果一定要使用，那么配置参数的时候，注意不要设置得过高，内存参数如果设置得太高，可能会导致32位的MySQL实例崩溃。
我们可以在SET命令中使用表达式，即，SET sort_buffer_size=10*1024*1024，但配置文件不允许使用表达式。
有时我们需要临时设置会话变量，执行操作，然后恢复原来的设置，通行的办法如下所示。
SET @saved_<unique_variable_name> := @@session.sort_buffer_size;
SET @@session.sort_buffer_size := <value>;
<span style="font-size: 16px;"--< Execute the query...
SET @@session.sort_buffer_size := @saved_<unique_variable_name>;
有时我们需要临时调整一些参数或变量，来验证自己的一些想法，但在此过程中需要注意以下两点。
1）调整参数需要有一个基准，调整参数后，我们需要衡量调整的结果。最好是有一套监控系统来收集实例的运行状态，这样可以方便我们进行对比。
2）应尽量小步调整参数，一次不要调整太多参数，调整太多参数会比较危险，也会使我们无法明确到底是哪些参数调整后有效果。
随着对生产环境的日渐熟悉，我们总能找到一套适合自己生产环境的配置。
10.3.3 配置文件的读取顺序
在Unix中，MySQL程序从表10-2所示的文件中读取启动选项。
其中，MYSQL_HOME是一个环境变量，包含与服务器相关的my.cnf文件驻留的目录路径。
如果未设置MYSQL_HOME，并且DATADIR中有一个my.cnf文件，而BASEDIR中没有my.cnf文件，那么mysqld_safe将会把MYSQL_HOME设置为DATADIR。如果未设置MYSQL_HOME并且在DATADIR中没有my.cnf，则mysqld_safe将MYSQL_HOME设置为BASEDIR。也就是说，数据目录内的配置文件和安装目录下的配置文件都可能生效。
典型情况下二进制的安装目录为/usr/local/mysql/data，源代码的安装目录为/usr/local/var。请注意这是配置时指定的数据目录的位置，而不是mysqld启动时用--datadir指定的。运行时使用--datadir对寻找选项文件的服务器没有效果，因为服务器在处理命令行参量之前就寻找这些选项了。
MySQL按照上述顺序寻找选项文件，如果存在多个选项文件，那么文件中指定的后读取的选项要优先于文件中指定的先读取的选项。所以理论上在datadir或basedir内放置一个my.cnf即可。
在Unix平台上，MySQL忽略了人人可写的配置文件。这是特意设置的，它其实是一个安全措施。
MySQL默认加载配置文件的先后顺序也可以通过应用如下命令来得知。
$ which mysqld /usr/local/mysql/bin/mysqld /usr/local/mysql/bin/mysqld --verbose --help | grep -A 1 ‘ Default options ’ Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~ /.my.cnf 通过以上命令可以知道加载配置文件的顺序。
注意
不要在生产环境中运行，因为会真的启动mysqld程序。
虽然官方文档中说明了配置文件的读取顺序，可是该顺序不一定可靠。建议读者不要依赖于官方文档所说明的顺序来部署自己的多个MySQL配置文件。对于生产环境的部署，建议仅存在并加载一个配置文件，而不要配置多个配置文件。有些人除了配置文件，还喜欢在命令行内也设置一些参数，这样容易导致混淆，维护性也会变差，最终将丢失你所做的变更。
10.3.4 环境变量、配置文件、命令行选项的优先级
MySQL程序首先会检查环境变量，然后检查选项文件，最后再来检查命令行以确定给出了哪些选项。如果多次指定一个选项，那么最后出现的选项占先。这说明环境变量具有最低的优先级，命令行选项具有最高的优先级。
可以在选项文件中指定程序选项的默认值来让MySQL程序处理各个选项。不需要在每次运行程序时都输入选项，但可以根据需要通过命令行选项来覆盖默认值。
10.4 MySQL Query Cache和优化器
MySQL Query Cache内缓存了我们提交的SQL语句的结果集及相关信息，有助于加速查询响应。一般不需要考虑Query Cache带来的额外开销，除非是写操作很频繁的应用。
工作原理
当MySQL运行查询语句时，首先会检查是否命中缓存，如果命中那么此时会增加Qcache_hits状态变量的值，并返回结果集给客户端。
如果在缓存中找不到此语句的缓存，则进入如下步骤。
1）MySQL解析器将分解查询语句，并建立一棵“解析树”，解析器会使用MySQL的语法解析并验证查询语句的语法是否正确，是否符合规范，当然各种符号也包含在检查范围之内。
2）预处理器检查“解析树”中的表和列是否存在，列的别名是否混淆，并进行相关权限的检查。
3）如果前面两步都通过了检验，那么再进行如下步骤。
步骤1：优化器对“解析树”进行优化，生成执行成本最低的执行计划。
步骤2：执行此计划，存储查询结果。
步骤3：返回结果集给客户端。
Query Cache默认是关闭的，临时禁用Query Cache的办法是设置query_cache_size为0，注意FLUSH QUERY CACHE命令并不会清空缓存。清除缓存的命令是RESET QUERY CACHE。
查看相关参数的语句为mysql>show variables like'%query_cache%';
查看相关状态变量的语句为mysql>show global status like'%Qcache%';
至于是否可以禁用Query Cache，对此我们要谨慎些，如果命中率不高，比如才70%~80%，那么关闭Query Cache一般不会有太大的问题，但如果Query Cache有98%~99%，那么关闭Query Cache可能会导致比较大的冲击，要仔细评估因为缓存失效而可能对数据库造成的冲击。
任何不是从缓存块中取得数据的查询语句都称为“缓存错失（cache miss）”，造成缓存错失的原因有以下几种。
1）所发送的查询语句是不可缓存的，查询语句不可缓存的原因主要有两种：一是语句包含了不确定的值；二是所得到的结果集太大而无法将它保存到缓存中。这两种原因造成的结果都会增加Qcache_not_cached变量的值，可以通过查看这个变量的值来检查查询语句的缓存情况。
2）所发送的查询语句之前没有发送过，所以也不会有什么缓存存在。
3）所发送的查询语句的结果集是之前存在于缓存中的，但由于内存不足，MySQL不得不将之前的一些缓存清除掉，以腾出空间来放置其他新的缓存结果。
4）数据的变更也会引发缓存的失效。如果是数据的变更引起的缓存失效，那么可以通过查看Com_*变量的值来确认有多少查询语句更改了数据，这些变量包括Com_update、Com_delete等。
Query Cache有如下一些要点需要注意。
-
·SQL语句在Query Cache中是通过散列映射表来查找的，大小写、空格等差异都会导致不同的散列结果，所以开发人员应该有一致的代码规范，以保证SQL语句风格一致。
-
·Query Cache不会缓存子查询。
-
·如果Query Cache结果集中相关的对象发生了变化，那么这个结果集就会被失效。比如某张表修改了数据，那么Query Cache内所有涉及这张表的结果集都会失效。需要注意的是，长时间运行的事务，会降低Query Cache的效率。因为如果InnoDB事务内的一条语句更改了表，那么MySQL就会让Query Cache与这个表相关的Cache都失效掉。直到这个事务提交之后，才可以重新缓存这个表的结果集。
-
·Query Cache分配内存的时候，每次至少要分配query_cache_min_res_unit大小的内存块，Query Cache并不需要等待所有的结果集在Cache内全部生成后才发送给客户端。因为失效等原因，实际上生产环境结果集所需要的Query Cache并不是很大，一般256MB就足够了。
-
·对于写操作很频繁的应用，可以考虑禁用Query Cache。
-
·留意碎片（fragmentation）的原因是，如果每次都分配较大的内存（query_cache_min_res_unit较大），那么更容易导致碎片化；如果每次分配较小的内存（query_cache_min_res_unit较小），则需要更频繁的分配，所以需要在内存的浪费和CPU的成本之间做一个取舍。我们可以计算下平均查询大小（Query Size）。公式为：Query Size=(query_cache_size–Qcache_free_memory)/Qcache_queries_in_cache，通过平均查询的大小来大致确定一个合适的query_cache_min_res_unit应该设置为多大。
-
·如果Qcache_lowmem_prunes比较大，而Qcache_free_blocks也比较大，那么可能是碎片比较严重，导致了查询缓冲被大量剔除。
-
·我们不太好衡量开启了Query Cache是否真的有帮助。最简单的办法是衡量缓冲命中率，公式为Qcache_hits/(Qcache_hits+Com_select)，如果缓冲命中率比较高，那么它就是有效的。但即使不高（如20%~30%），也不一定意味着低效，我们关注的是提高特定查询的访问速度而不是只关注命中率这个指标相对查询来说，将结果集存储到Query Cache比结果集失效的成本更低。如果一个系统中，大部分都是复杂的查询，那么用Query Cache将是一个很好的选择。
-
·如果Qcache_not_cached比较小，但有大量缓存未命中，那么可能会有很多失效的操作，或者MySQL没有预热数据，或者重复的查询很少。Qcache_inserts在预热数据后，应该比Com_select小得多。
-
·可监控一下Qcache_lowmem_prunes，确定是否因为内存不够而剔除了结果集。Query Cache的效率比较高的时候，Qcache_inserts应该比Com_select小得多。
-
如果查询结果没有被缓存，那么，MySQL将解析查询（Parse），通过优化器（Optimizer）生成执行计划，然后运行执行计划获取数据。MySQL优化器生成的执行计划，在很大程度上决定了其性能，随着新版本的发布，MySQL优化器越来越智能，但它仍然存在很多限制，DBA和研发人员需要熟悉所使用的MySQL版本的优化器规则，充分利用优化器，撰写高质量的SQL。
-
让优化器工作得更好，本质上就是进行查询优化，具体可参考第6章“查询优化”。
10.5 SHOW INNODB STATUS解析
SHOW ENGINE INNODB STATUS是一种常用的工具，但运行这个命令的输出却不容易阅读。
我们可以通过创建一些InnoDB监控表（注意必须是InnoDB引擎的表），来启用性能监控输出，输出InnoDB的各种信息，默认输出至MySQL错误日志。
如下命令将创建InnoDB标准监视器，即SHOW INNODB STATUS输出。
CREATE TABLE innodb_monitor (a INT) ENGINE=InnoDB;
如下命令将创建表空间监视器，以输出共享表空间的信息。对独立表空间来说，它不适用，如果关闭了数据文件的自动扩展，那么通过这个监控，可以监视数据文件是否需要扩展。
CREATE TABLE innodb_tablespace_monitor (a INT) ENGINE = InnoDB;
如下命令将开启表监控器，会输出系统中所有InnoDB表的一些结构和内部信息。
CREATE TABLE innodb_table_monitor (a INT) ENGINE = InnoDB;
如下命令将开启InnoDB锁监控器，它的输出结果和标准监视器基本类似，但会有更多关于锁的信息。
CREATE TABLE innodb_lock_monitor(a INT) ENGINE = InnoDB;
创建表只是发出一个命令给InnoDB引擎，同理，删除表也是发送一个停止监控的命令给InnoDB引擎。所以MySQL在重启后是不会自动启动InnoDB监控的。
以下将对InnoDB进行标准监控，也就是运行SHOW ENGINE INNODB STATUS，对其输出做一些解析，其他监控器（如对于表空间的监控）可参考官方文档。
SHOW INNODB STATUS命令的输出信息不太方便进行脚本解析，而且输出信息里有很多平均值，不太好估算我们自己指定范围的统计结果，SHOW GLOBAL STATUS命令也有很多InnoDB的输出信息，使用SHOW GLOBAL STATUS会更好估算一些，也会更易于监控系统性能。
创建这些表之后，MySQL就会输出各种内部结构和性能信息到MySQL错误日
志，对于InnoDB标准监视器，大概是每隔15s输出一次。笔者个人很少启用各种性能监控，一般是在做诊断的时候，直接运行命令，例如：
SHOW ENGINE INNODB STATUS \G
具体的输出解析如下。
*************************** 1. row *************************** Status: ===================================== 100206 21:51:18 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 26 seconds 以上输出结果为最近26s的统计。如果是前1~2s的统计那么结果将不太可信。我们需要确保至少有20~30s的统计，否则结果会不太准确，还需要重新运行这个命令。 SHOW ENGINE INNODB STATUS的输出主要包含如下几个部分，这里以MySQL 5.1/5.5为例来进行讲述，其他版本与此类似。 ·Background Thread ·Semaphores ·Latest Foreign Key Error ·Latest Detect Deadlock ·File I/O ·Insert Buffer and Adaptive Hash Index ·Log ·Buffer Pool and Memory ·Row Operations ·Transactions
（1）信号量（Semaphores）
下面是信号量相关信息。
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 13569, signal count 11421
--Thread 1152170336 has waited at ./../include/buf0buf.ic line 630 for 0.00 seconds the semaphore:
Mutex at 0x2a957858b8 created file buf0buf.c line 517, lock var 0
waiters flag 0
wait is ending
--Thread 1147709792 has waited at ./../include/buf0buf.ic line 630 for 0.00 seconds the semaphore:
Mutex at 0x2a957858b8 created file buf0buf.c line 517, lock var 0
waiters flag 0
wait is ending
Mutex spin waits 5672442, rounds 3899888, OS waits 4719
RW-shared spins 5920, OS waits 2918; RW-excl spins 3463, OS waits 3163
解析：信号量（SEMAPHORES）节包含两部分信息，一部分信息是当前的操作系统等待（OS WAIT ARRAY INFO），在高并发的环境下，我们可能会看到这部分信息，因为InnoDB自旋等待超过了阈值，就会触发操作系统等待，如果等待通过自旋能够解决，那么这些信息就不会显示了。
通过检查这部分信息，可以大致判断负荷的热点在哪里，由于输出行只包含了一些文件名，因此还需要有一些源码的知识，才能判断出现等待的真实原因。
另一部分信息是事件统计（event counter），reservation count和signal count的值表征了InnoDB需要OS WAIT的频率。我们也可以使用操作系统命令，如vmstat，通过检查上下文切换（context switch）的频率来确认OS WAIT的严重程度。
我们还需要了解一些操作系统进程调度的知识，如果进程不能获取锁（mutex可以理解为一种轻量级的锁），则CPU会自旋（spin），也就是CPU空转，以等待资源，此时并不需要进行上下文切换这种高成本的操作，也许CPU空转一些时间片，就可以获取到资源，但如果自旋超过了一定的次数，仍然无法获得资源，那么进程就需要切换到睡眠状态进行等待（OS WAIT），大量的OS WAIT意味着资源竞争很厉害，将造成很高的上下文切换频率。如果每秒有几万次的OS WAIT，那么很可能系统中存在性能问题。
大量的spin waits和spin rounds，意味着CPU在空转而没有实际做事，这会消耗大量的CPU资源，所以有时我们看到系统的CPU利用率很高，但也许并不是真正地在做事，而是CPU正在空转等待资源。通过调整innodb_sync_spin_loops参数，可以在CPU资源消耗和上下文切换之间找到平衡点。
（2）死锁
下面是一个系统的死锁信息。
———————— LATEST DETECTED DEADLOCK ———————— 100206 14:46:39 *** (1) TRANSACTION: TRANSACTION 0 353348573, ACTIVE 0 sec, process no 22381, OS thread id 823933856 inserting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 320, 2 row lock(s), undo log entries 1 MySQL thread id 3176551, query id 27696260 del40 10.12.14.181 ooes_rss update insert into ooes_fav(id,name,uid,mtime,ctime,wapflag,url,parent_id,type) values( ’1′,'QQ',’7080277′,’1265438796′,’1265438796′,”,”,”,’2′ ) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 1484846 n bits 144 index `uid` of table `ooes_rss`.`ooes_fav` trx id 0 353348573 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** (2) TRANSACTION: TRANSACTION 0 353348572, ACTIVE 0 sec, process no 22381, OS thread id 894077856 inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 7 lock struct(s), heap size 1024, 103 row lock(s), undo log entries 101 # 这个事务更大 MySQL thread id 3176549, query id 27696261 del40 10.12.14.180 ooes_rss update *** (2) HOLDS THE LOCK(S): #Note – InnoDB only prints information about few of the locks which transaction is holding. RECORD LOCKS space id 0 page no 1484846 n bits 72 index `uid` of table `ooes_rss`.`ooes_fav` trx id 0 353348572 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 1484846 n bits 144 index `uid` of table `ooes_rss`.`ooes_fav` trx id 0 353348572 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** WE ROLL BACK TRANSACTION (1)
解析：这段信息展示了是哪些事务导致了死锁、死锁过程中它们的状态、它们持有的锁、要等待的锁、回退到哪个事务（据官方文档可知，MySQL会回滚成本较小的事务，比如更新更少的行）等内容。由输出的最后一行可以得知，回退到了事务1。需要留意的是，这里只显示了部分持有的锁，只显示了事务中最近的语句，而实际上占据资源的可能是事务中前面的语句。在一些简单情况下，可以通过SHOW ENGINE INNODB STATUS的输出确认导致死锁的原因；在复杂的情况下，则需要打开通用日志，检查具体各个事务是如何互相等待资源从而导致死锁的。
MySQL 5.6可以通过参数innodb_print_all_deadlocks将死锁信息打印到错误日志中。
（3）外键冲突
以下为外键冲突信息，开发人员需要注意。
———————— LATEST FOREIGN KEY ERROR ———————— 060717 4:29:00 Transaction: TRANSACTION 0 336342767, ACTIVE 0 sec, process no 3946, OS thread id 1151088992 inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 3 lock struct(s), heap size 368, undo log entries 1 MySQL thread id 9697561, query id 188161264 localhost root update insert into child values(2,2) Foreign key constraint fails for table `test/child`: , CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE Trying to add in child table, in index `par_ind` tuple: DATA TUPLE: 2 fields; 0: len 4; hex 80000002; asc ;; 1: len 6; hex 000000000401; asc ;; But in parent table `test/parent`, in index `PRIMARY`, the closest match we can find is record: PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 0000140c2d8f; asc – ;; 2: len 7; hex 80009c40050084; asc
（4）事务信息
------------ TRANSACTIONS ------------ Trx id counter 0 80157601 Purge done for trx ’s n:o < 0 80154573 undo n:o < 0 0 History list length 6 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 3396, OS thread id 1152440672 MySQL thread id 8080, query id 728900 localhost root show innodb status ---TRANSACTION 0 80157600, ACTIVE 4 sec, process no 3396, OS thread id 1148250464, thread declared inside InnoDB 442 mysql tables in use 1, locked 0 MySQL thread id 8079, query id 728899 localhost root Sending data select sql_calc_found_rows * from b limit 5 Trx read view will not see trx with id >= 0 80157601, sees < 0 80157597 ---TRANSACTION 0 80157599, ACTIVE 5 sec, process no 3396, OS thread id 1150142816 fetching rows, thread declared inside InnoDB 166 mysql tables in use 1, locked 0 MySQL thread id 8078, query id 728898 localhost root Sending data select sql_calc_found_rows * from b limit 5 Trx read view will not see trx with id >= 0 80157600, sees < 0 80157596 ---TRANSACTION 0 80157598, ACTIVE 7 sec, process no 3396, OS thread id 1147980128 fetching rows, thread declared inside InnoDB 114 mysql tables in use 1, locked 0 MySQL thread id 8077, query id 728897 localhost root Sending data select sql_calc_found_rows * from b limit 5 Trx read view will not see trx with id >= 0 80157599, sees < 0 80157595 ---TRANSACTION 0 80157597, ACTIVE 7 sec, process no 3396, OS thread id 1152305504 fetching rows, thread declared inside InnoDB 400 mysql tables in use 1, locked 0 MySQL thread id 8076, query id 728896 localhost root Sending data select sql_calc_found_rows * from b limit 5 Trx read view will not see trx with id >= 0 80157598, sees < 0 80157594 解析：事务列表可能会很长，所以对于存在大量并发事务的系统，SHOW ENGINE INNOD STATUS会截去部分内容，只显示部分事务。
具体输出参数及其解析如下所示。
·Trx id counter…：当前事务号，每创建一个新事务，这个值就会递增。
·Purge done for trx’s n:o…：最近一次进行线程清理的事务号，事务如果过期，则可以被清除，清除的标准是这些事务已经提交，且不会再被其他的事务所需要。
我们可以检查当前事务号和最近一次清理线程所清理的事务号的差异，例如，0（64位）80154573（32位）与0（64位）80157601（32位），如果差异很大，则可能有大量未被清理的事务，或者少量事务更新了大量数据。
事务应该被及时提交。长时间未提交的事务可能会阻塞清理操作，耗尽资源，不过对于Web访问，一般都是很小的事务，这点不太可能会成为问题。
事务更新记录时，将在UNDO中保存记录的前像。UNDO记录保存在InnoDB的共享表空间内。
如果事务未提交，或者其他用户需要查询UNDO记录以获得一致性读，此时是不能清理这部分事务的。大量未清理的事务，可能会导致UNDO空间暴涨，在紧急
情况下，我们可以设置innodb_max_purge_lag参数来延缓新事务的更新，不过这个参数要慎用，因为它会降低性能，治标不治本。
下面来举个例子说明一下这个参数。如果你的InnoDB表空间可以忍受100M未清理的行，也就是平均每个事务大概影响1K的行，那么你可以设置这个值为100000（100M/1K）。
·undo n:o：Purge操作正在处理的UNDO日志记录号。
·History list length 6：在UNDO空间内未被清理的事务数量，在事务更新数据的时候该值会增加，在事务清理后该值会减少。
·Total number of lock structs in row lock hash table 0：行锁哈希表（row lock hash table）中的锁结构（lock struct）的数量，该值不同于被锁定的行，因为通常会有多个行对应一个锁结构。
·LIST OF TRANSACTIONS FOR EACH SESSION：
---TRANSACTION 00，not started，process no 3396，OS thread id 1152440672：每个事务都有两个状态，即not started或active。在生产系统中，同时运行的线程一般最多只有几个，所以大部分事务都是not started。
需要留意的是，即使连接的状态是sleep，事务也可能是active的，因为事务可能是多语句的，在生产环境中可以发现，一些长时间sleep的异常线程可能会持有着资源不释放，从而导致整个系统出现异常。
InnoDB有一个参数为innodb_thread_concurrency，用来控制并发执行的线程数。InnoDB试着在其内部控制操作系统线程的数量，使其少于或等于这个参数给出的限制。如果SHOW INNODB STATUS显示有很多线程在等待（waiting in InnoDB queue或sleeping before joining InnoDB queue）进入队列，那么往往是有性能上的问题，导致系统挂死。MySQL让等待的线程睡眠，从避免太多线程并发竞争，如果你的计算机有多个处理器和磁盘，则可以试着将这个值调整得更大以更好地利用计算机的资源。一个推荐的值是采用系统上处理器和磁盘的个数之和。
注意
MySQL的配置里还有一个thread_concurrency参数，建议设置为CPU数的2倍大小。此变量仅仅影响Solaris系统。在Solaris中，mysqld用该值调用thr_setconcurrency()函数。该函数使得应用程序可以向线程系统提供需要同时运行的、期望的线程数目。此外，其实innodb_thread_concurrency这个参数才会影响到所有的平台。
·mysql tables in use 1,locked 0：访问的表数目，锁定的表数目。一般的操作是不会锁表的，InnoDB支持行级锁，所以locked一般等于0，除非是进行ALTER TABLE、LOCK TABLE之类的操作。
·MySQL thread id 52111305：SHOW PROCESSLIST命令输出中的id列。
（5）I/O信息
以下是IO helper threads的状态。
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
这4个线程（Unix/Linux下总是4个）的作用分别是insert buffer merges、asynchronous log flushes、read-ahead和flushing of dirty buffers。
当前看到它们的状态都是waiting for i/o request。
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o
’s: 0, sync i/o’: 0
Pending flushes (fsync) log: 0; buffer pool: 0
6845394 OS file reads, 209547550 OS file writes, 1051178 OS fsyncs
7.27 reads/s, 16384 avg bytes/read, 256.68 writes/s, 1.88 fsyncs/s
如果以上Pending为非零值，则可能存在I/O瓶颈。
对于随机I/O，因InnoDB的I/O最小单元（page size）=16KB。所以为16384 avg bytes/read，对于全表扫描（full table scan）、索引范围扫描（index scan），这个avg bytes/read会大得多。
（6）INSERT BUFFER AND ADAPTIVE HASH INDEX
MySQL并没有提供手段对以下结构进行调优。
————————————
INSERT BUFFER AND ADAPTIVE HASH INDEX
————————————
Ibuf: size 1, free list len 0, seg size 2,
这里ibuf即Insert buffer，虽然英文中说的是“buffer”，但实际上这是分配在InnoDB表空间中的一块区域，它可以和其他数据块一样，缓存在InnoDB缓冲池里，Insert buffer可以减少I/O，因为它可以合并对索引叶节点的更改操作。
（7）LOG
下面将讲述InnoDB的log子系统。
—
LOG
—
Log sequence number 449 61757582
Log flushed up to 449 61751106
Last checkpoint at 448 4209429402
0 pending log writes, 0 pending chkp writes
201992232 log i/o
’s done, 250.14 log i/o’
s/second
其中的输出参数及其解析具体如下。
·Log sequence number 44961757582：表空间创建后写入log buffer的字节数，这个值可以用来衡量日志的写入速度。通过采样Log sequence number的输出，可以获取每秒写入的日志量，如果我们要设置InnoDB事务日志的大小，那么能保持连续写入日志30~60分钟为佳。
·Log flushed up to 44961751106：最近刷新（flush）数据的位置。
由此可以计算还有多少未刷新到日志文件（logfile）的数据。如果这些数据大于innodb_log_buffer_size的30%，那么就要考虑是否应增加日志缓冲（log buffer）了。
·Last checkpoint at 4484209429402：最近一次检查点的位置。
·0 pending log writes,0 pending chkp writes：pending如果大于0，则可能有I/O瓶颈。
·201992232 log i/o’s done,250.14 log i/o’s/second：这些输出衡量了我们的log I/O。
（8）BUFFER POOL AND MEMORY
以下是InnoDB缓冲池的信息。
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 4648979546; in additional pool allocated 16773888
Buffer pool size 262144
Free buffers 0
Database pages 258053
Modified db pages 37491
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 57973114, created 251137, written 10761167
9.79 reads/s, 0.31 creates/s, 6.00 writes/s
Buffer pool hit rate 999 / 1000
需要说明的是“Buffer pool hit rate”的参考价值不是很大。即使有很高的命中率，也可能有大量的物理磁盘读写。
（9）ROW OPERATIONS
以下是行操作信息。
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 10099, id 88021936, state: waiting for server activity
Number of rows inserted 143, updated 3000041, deleted 0, read 24865563
563
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
我们可以由以上信息获知各种查询的大概频率，需要留意的是如果“0 queries in queue”不为0，则是有查询需要等待，可能意味着系统忙，你需要做进一步的诊断。
小结
本章介绍了MySQL运维所需要了解的各种数据库文件及MySQL如何进行灾难恢复。你必须了解各种文件的作用和机制，避免在操作系统下对数据库文件误操作。本章还介绍了数据库的参数设置与配置文件，MySQL的配置不应该经常变动，你应该使用大多数人建议的配置，根据自己的生产环境做适当调整即可。最后介绍了查询缓冲和MySQL优化器，我们要熟悉这些主要的组件。此外，还讲述了如何阅读SHOW INNODB STATUS\G命令的输出。
其他的一些基础知识已在开发篇中进行了介绍，比如索引设计、查询优化。读者也应该熟悉这些内容。
四、我们如何做MySQL的全面监控
1、为什么我们需要监控呢？
答：因为如果没有了监控，那么我们的服务可用性就无从度量，我们也无法及时地发现问题和处理问题。
备注说明： 一个完善的监控体系，不仅需要进行实时的监控，也需要分析历史的监控数据，以便掌握性能和容量趋势的变化，从而为产品、架构人员提供决策的依据。
对于集中式的监控产品，一般需要在被监控的服务器中部署一个代理服务（agent）来收集数据，如Ganglia、Nagios等，或者通过一些系统服务收集信息，比如snmp。广泛使用的一些平台有Zabbix、Nagios、Ganglia、Cacti，读者可以自行阅读相关图书，学习如何使用它们，本书将只关注数据库的性能监控和故障发现。
有时我们希望能够开发出自己的数据库监控平台，自己编写脚本、工具来收集信息。这样会更有针对性。不过笔者建议读者使用市面上流行的监控工具或平台，很多监控平台都有MySQL相关的监控插件，我们需要做的只是少量的二次开发工作。完全重新开发一个监控平台的成本往往会比较高，需要综合权衡是否有必要投入人力去实现，有时，在一些开源软件上做二次开发，是更经济的方式
2、自行编写程序进行监控需要考虑到如下一些要点。
1）Linux的I/O是比较难监控的，如果在一台主机之上有多个应用，那么判断I/O负荷重的业务有哪些将会很困难。安装iotop之类的工具可以更快地定位到I/O负荷重的进程之上，但iotop之类的工具需要新的内核支持。
2）由于SSD的大量使用，因此还需要增加对SSD的监控，常用的方式是使用smartctl命令进行监控。
3）有时我们需要模拟业务访问。人的行为是复杂的，复杂的业务系统更是充满了变数，模拟人的行为是一件困难且富有挑战性的事情，我们应该模拟尽量真实的访问，这样才能得到真实的反馈，从而衡量服务是否真的健壮、可靠、体验良好。
4）要注意收集信息的频率，粒度太大了可能不能及时发现问题。
5）要防止积压收集信息的程序任务。
6）要确保报警通知到人，还要确保邮件服务、短信等通道的正常。
11.2.1 数据库服务的基本监控方式
一般数据库的监控包括探测数据库主库的可用性、复制状态监控、数据库的性能监控、数据库的故障发现等。
对于数据库主库的监控，可以在主库上创建一张监控表，使用监控程序定期去读写这张表中的数据，以判断数据库是否可以正常提供服务。
对于数据库从库的监控，由于从库一般都是只读的，因此只需要定期查询从库上监控表的数据即可。
对于复制状态的监控，由于主库有定期更新的监控表，因此可以认为它也是一张心跳表，表里的数据带有时间戳信息，主库监控表（心跳表）每分钟被UPDATE一次，去从库中查询对应的记录，就可以依据记录内的时间戳信息来确认延时了多少。这里需要说明的是，MySQL自身的SHOW SLAVE STATUS\G显示的延时时间可能是不准确的，所以，推荐使用心跳表的方式。
需要注意的是，每次信息收集的时间间隔不能太大，否则会难以发现和诊断问题。比如磁盘I/O数据每10分钟才去收集一次，数据库性能每隔几分钟才去收集一次，就不是一个好的选择。
数据库的性能监控主要依靠于收集MySQL的一些状态变量，也就是SHOW GLOBAL STATUS的输出。
数据库的故障发现涉及的内容包括：分析MySQL的查询响应、错误日志，以及监控是否可以读写数据库。
11.2.2 应该收集的信息和收集方法
我们收集的信息主要包括MySQL的运行状态、程序性能日志、慢查询日志、状态变量、数据量、数据占用空间等。
1.MySQL的参数及运行状态
以下代码可查看MySQL实例的参数及运行状态。
SHOW VARIABLES LIKE '%parameter%' ;
SHOW FULL PROCESSLIST ;
SHOW ENGINE INNODB STATUS \G;
以下是对一个SHOW PROCESSLIST的解析。可以看到不同状态下线程的比例。
mysql -uroot -p -S /path/to/tmp/3306/mysql.sock -e 'SHOW PROCESSLIST\G' | grep State: | sort | uniq -c | sort -rn
下面来解释一些常用的状态。
·Sleep：线程正在等待来自客户端的新查询。 ·Query：线程正在执行查询，或者正在发送结果给客户端。 ·Locked：线程正在等待表锁。 ·Analyzing和statistics：线程正在获取存储引擎的统计数据和优化查询。 ·Copying to tmp table[on disk]：线程正在处理查询，复制数据到临时表中。如果后面有“on disk”字样，则表明MySQL正在将内存临时表转换为磁盘临时表。 ·Sorting result：线程正在排序结果集。 ·Sending data：这个状态有多种可能，可能是内部各步骤之间传递数据，生成结果集；或者是将结果集返回给客户端。
大多数状态对应的操作都非常快。如果一个线程停留在一个给定的状态好几秒，那么它可能是有问题的，需要进一步查明。
下面来查看InnoDB的一些统计数据，命令如下所示。
SHOW INNODB STATUS \G;
如下命令可查看SQL的执行频率，实时显示当前各种SQL的执行频率等信息，该命令摘录自网上。
mysqladmin -uroot -p -r -i 2 extended-status |awk -F "|" 'BEGIN { count=0; } { if($2 ~ /Variable_name/ && ++count%15 == 1){print "----------|---------|--- MySQL Command Status --|----- InnoDB row operation -----|-- Buffer Pool Read --"; print "---Time---|---QPS---|select insert update delete| read inserted updated deleted| logical physical";} else if ($2 ~ /Queries/){queries=$3;} else if ($2 ~ /Com_select /){com_select=$3;} else if ($2 ~ /Com_insert /){com_insert=$3;} else if ($2 ~ /Com_update /){com_update=$3;} else if ($2 ~ /Com_delete /){com_delete=$3;} else if ($2 ~ /InnoDB_rows_read/){innodb_rows_read=$3;} else if ($2 ~ /InnoDB_rows_deleted/){innodb_rows_deleted=$3;} else if ($2 ~ /InnoDB_rows_inserted/){innodb_rows_inserted=$3;} else if ($2 ~ /InnoDB_rows_updated/){innodb_rows_updated=$3;} else if ($2 ~ /InnoDB_buffer_pool_read_requests/){innodb_lor=$3;} else if ($2 ~ /InnoDB_buffer_pool_reads/){innodb_phr=$3;} else if ($2 ~ /Uptime / &&
count >= 2){ printf(
“
%s |%9d",strftime(
“
%H:%M:%S
”
),queries);printf("|%6d %6d %6d %6d",com_select,com_insert,com_update,com_delete);printf("|%8d %7d %7d %7d",innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted); printf("|%10d %11d\n",innodb_lor,innodb_phr);}}'
如果需要做进一步的分析，也可以用tcpdump配合pt-query-digest工具来获取更多的信息，它所生成的报告不仅包括SQL的计数，还包括SQL的耗时及其他成本信息。 首先，在root用户下执行如下命令。 nohup tcpdump -i eth1 port 3306 -s 65535 -x -nn -q -tttt > db1000_sql_new.log & 然后在mysql用户下执行如下命令。 ./pt-query-digest --type=tcpdump --watch-server 11.11.11.11:3306 db1000_sql_new.log > app_db.rtf
对于以上生产报告，可以发送邮件给DBA阅读，或者将其过滤后存放在数据库中。
SQL的统计最好在应用层收集信息，这是笔者推荐的方式，SQL的很多统计，结合应用才能易于理解，才能更好地评判是否应该进行优化，大致方法如下。
1）直接记录SQL到日志，统计日志中各种查询的比例。
2）根据Web服务器日志，例如根据一天中高峰期一个小时的日志，统计涉及某些功能（SQL）页面的日志在总的日志中所占的比例。
2.性能日志
这里所说的性能日志，一般是指程序性能日志。很多公司并没有考虑性能日志，主要是出于开发的成本考虑。但一个良好的、完善的服务程序，应该包含自诊断的信息，以协助诊断问题。
我们应先查看整个应用的性能表现，从总体上来分析。一般来说，程序的性能日志是最容易诊断出性能瓶颈的，性能日志也可以用图形的方式展示出应用的性能变化趋势，方便作为以后扩容的依据。
例如，对于一个PHP程序，应该收集的信息主要有如下几点。
·合计执行时间（页面执行时间）。
其他各部分时间相加应等于合计执行时间，差别不能过大，否则就要研究是否哪部分操作未做记录。
·每个查询的执行时间。
·打开的连接。
·对外部服务的调用。
·可能消耗资源较大的数据库操作。
如果性能日志足够详细，那么就可以快速地定位性能的瓶颈所在了，从而判断是否真的是MySQL导致了性能问题，是否访问MySQL耗费了绝大部分的页面时间。进行压力测试的时候，可以定位伸缩性存在问题的环节。
3.慢查询日志
除了SHOW GLOBAL STATUS和SHOW PROCESSLIST之外，还可以检查慢查询日志，一般推荐优先采用前面两种方式检查系统，慢查询的检查又耗时又复杂。
MySQL提供了两类日志：通用日志（general log）和慢查询日志（slow log）。通用日志记录了接收的所有查询。这个日志有助于判断读写的比例，看MySQL的主要工作是什么？但打开通用日志需要注意日志的空间消耗，可能还需要考虑轮询切割日志。一般情况下没必要启用通用日志。这里仅分析慢查询日志。
发人员进行优化，MySQL 5.1及以上版本可以动态启用慢查询日志，MySQL 5.0则需要重启后才能生效。
需要设置的参数如下。
MySQL 5.0需要设置log_slow_queries和slow_launch_time。
MySQL 5.1需要设置slow_query_log和long_query_time，这里不需要再使用slow_launch_time这个参数了，因为这个参数不能设置到毫秒级。MySQL 5.1.21后可以进行毫秒级的慢查询记录，例如，设置long_query_time=0.01。
有一个参数log_queries_not_using_indexes，也可以协助分析，不过小表无须建立索引速度也很快，这样的情况下，使用该参数可能会导致产生大量的日志记录。因此建议忽略这个参数，不予设置。
有一些补丁或MySQL分支，如Percona Server，可以显示出更翔实的慢查询信息，这样就有助于我们探查到底是什么原因导致的查询慢，因为官方版本中慢查询日志默认的输出信息都比较粗略，并没有告诉我们查询为什么会变慢。
某个SQL出现在慢查询日志里，并不意味着这就是一个质量差的SQL，也并不表示现在或未来这个查询很慢，也许你手动执行它，会非常之快。有诸多因素会影响到SQL的响应：如锁表、数据或索引初次使用时未被缓存、磁盘I/O紧张、内存泄露等。现实中，如果一个查询平时运行得很快，但在发现性能问题时被记入慢查询日志，可能是因为其他查询占用了大量的系统资源，被阻塞而导致的。
对于慢查询日志的分析可以使用MySQL自带的mysqldumpslow来实现，还有一些比mysqldumpslow更强大的分析工具，如pt-query-digest。
对于慢查询日志，可以关注执行时间过长的查询，或者执行次数过多的查询，或者结果集过大的查询。
通过如下命令，可以看到每秒的慢查询统计，以方便绘图。当检查到有突变时，往往这个时候会有异常发生，可以更进一步到具体的慢查询日志中去查找可能的原因。
awk '/^# Time:/{print $3, $4, c;c=0}/^# User/{c++}' slowquery.log > /tmp/aaa.log
4.状态变量
如SHOW GLOBAL STATUS、SHOW SESSION STATUS和SHOW PROFILE 查看全局状态变量的命令如下。 SHOW GLOBAL STATUS LIKE '%parameter%'; 查看吞吐率时，可多次运行下面的命令，检查增量。 SHOW GLOBAL STATUS LIKE‘%question%’; SHOW GLOBAL STATUS LIKE Com_%; 也可以使用如下命令检查多个系统状态变量的变化。 SHOW GLOBAL STATUS WHERE Variable_name LIKE 'Com_select' OR Variable_name LIKE 'Com_insert' OR variable_name LIKE 'com_update' OR variable_name LIKE 'com_delete' OR variable_name LIKE '%Qcache_hits'; 使用mysqladmin命令可监视状态变量的变化，注意如下命令中添加了参数-r。 mysqladmin -uroot -p extended-status -r -i 10 |egrep "Com_select|Com_insert|Com_delete|Com_update|Qcache_hits|Handler_write|Handler_read” mysqladmin命令的另一个示例如下。
mysqladmin -uroot -p -i1 | awk '
/Queries/{q=$4-qp;qp=$4}
/Threads_connected/{tc=$4}
/Threads_running/{printf "%5d %5d %5d\n"，q，tc，$4}'
也可以使用监控工具的一些插件来监控状态变量的变化，如使用Cacti的MySQL插件。Cacti有丰富的模板支持，可以近乎实时地监察MySQL的运行状态。它主要也是用于获取SHOW GLOBAL STATUS的信息。
关于查询读写比率的计算，可以大致采用如下的公式进行计算。
(SELECT + Qcache_hits ) / (INSERT + UPDATE + REPLCACE + DELETE)
相应的状态变量可以查询以“com_”或以“handler_”为前缀的一些变量。
在SHOW GLOBAL STATUS中，我们需要关注的主要有以下几个计数器：handler、temporary files、command、wait。
有时我们需要单独分析一些查询的成本，需要先手动清除状态变量（运行命令FLUSH STATUS），然后再运行查询，最后重新运行SHOW SESSION STATUS，从此来查看查询所耗费的成本。
SHOW SESSION STATUS，顾名思义，显示的是当前会话的状态变量，它不受其他进程的影响。
以下示例显示了执行一个SQL后会话的Select%状态的变化，为了节省空间，这里没有列出所有状态的值。
flush status; mysql> SELECT SQL_NO_CACHE ... from ... mysql> show session status like 'Select%'; | Variable_name | Value +---------------------+---------+ Select_full_join | 0 Select_full_range_join | Select_range | 0 | Select_range_check | 0 | Select_scan | 2 +---------------------+---------+
·Select_full_join：全表扫描连接的次数，如果该值比较高，那么可能是没有正确地创建索引。
·Select_full_range_join：在引用的表中使用范围查找的连接数量。
·Select_scan：执行了全表扫描的数量。
如下命令将检查存储引擎操作。
mysql> SHOW SESSION STATUS LIKE 'Handler%';
+------------------------+---------+
| Variable_name | Value
+------------------------+---------+
| Handler_commit | 0
| Handler_delete | 0
| Handler_discover | 0
| Handler_prepare | 0
| Handler_read_first | 1
| Handler_read_key | 5665
| Handler_read_next | 5662
| Handler_read_prev | 0 | DESC
。
| Handler_read_rnd | 200
| Handler_read_rnd_next | 207
| Handler_rollback | 0
| Handler_savepoint | 0
| Handler_savepoint_rollback | 0
| Handler_update | 5262
| Handler_write | 219
·Handler_read_first：索引中第一条被读的次数。如果较高，则代表服务器正在执行大量全索引扫描。
·Handler_read_key：根据键读取一行记录的请求数。如果该值较高，则说明查询和表的索引是正确的。
·Handler_read_next：按照键顺序读下一行的请求数。如果你使用范围约束或执行索引扫描来查询索引列，那么该值会增加。
·Handler_read_prev：按照键顺序读取前一行的请求数。
·Handler_read_rnd：根据固定位置读取一行的请求数。如果你正执行大量的查询并且需要对结果进行排序，那么该值会较高。如果使用了大量需要MySQL扫描整个表的查询语句，或者连接没有正确地使用键，那么该值也会较高。
·Handler_read_rnd_next：在数据文件中读取下一行的请求数。如果你正在进行大量的表扫描，那么该值会较高。通常情况下，该值高说明你的表索引不正确，或者写入的查询没有利用索引。
·Handler_update：在表内插入一行的请求数。以上示例中Handler_update计数器的值比较高，是因为MySQL的GROUP BY、ORDER BY操作会先把表写入一个临时表，扫描后进行排序，然后进行输出。
·Sort_merge_passes：排序算法已经执行了合并的数量。如果这个变量值较大，则应该考虑增加sort_buffer_size系统变量的值。
如下命令将检查sort相关的统计。
mysql> SHOW SESSION STATUS LIKE'Sort%';
+---------------------+---------+
| Variable_name | Value
+---------------------+---------+
| Sort_merge_passes | 0 | | Sort_range | 0
| Sort_rows | 200
| Sort_scan | 1
+---------------------+---------+
·Sort_rows：已经排序的行数。
·Sort_scan：通过扫描表完成排序的数量。
如下命令将查看临时表的创建情况。
mysql> SHOW SESSION STATUS LIKE 'Created%';
+---------------------+---------+
| Variable_name | Value
+---------------------+---------+
| Created_tmp_disk_tables | 0
| Created_tmp_files | 0
| Created_tmp_tables | 5
·Created_tmp_disk_tables：如果持续增加，那么可能是有性能问题。
以上输出可能仍然会受到内部操作的影响，建议多运行几次查询，从而得到一个比较可靠的增量。笔者将在后续章节里更详细地解释一些状态变量的含义。
另外还有一个简单易用的方法，使用SHOW PROFILE。该功能默认是关闭的，但是会话级别可以开启这个功能。开启它可以让MySQL收集在执行语句的时候所使用的资源和耗时。
下面的示例将使用SET profiling=1开启这个功能。
root@localhost test>SHOW VARIABLES LIKE '%profil%'; +---------------------+---------+ | Variable_name | Value +---------------------+---------+ | profiling | OFF | profiling_history_size | 15 +---------------------+---------+ 2 rows in set (0.00 sec) root@localhost test>SET profiling = 1; Query OK, 0 rows affected (0.00 sec) root@localhost test>SELECT COUNT(*) FROM testad; +------------+ | count(*) +------------+ 1 +------------+ row in set (0.00 sec)
root@localhost test>SHOW PROFILES \G; *************************** 1. row *************************** Query_ID: 1 Duration: 0.00015100 Query: select count(*) from testad 1 row in set (0.00 sec) ERROR: No query specified root@localhost test>SHOW PROFILES; +--------------+-------------+---------+ | Query_ID | Duration | Query +--------------+-------------+---------+ | 1 | 0.00015100 | select count(*) from testad | 2 | 0.00017100 | select count(*) from testac
+--------------+-------------+---------+
如果SHOW PROFILE后不加参数，则显示最近的查询统计。Status栏位与SHOW FULL PROCESSLIST的Status栏位相同。
root@localhost test>SHOW PROFILE; +---------------------------------+-----------+ Status | Duration +---------------------------------+-----------+ | starting | 0.000031 | checking query cache for query | 0.000035 | Opening tables | 0.000011 | System lock | 0.000004 | Table lock | 0.000019 | init | 0.000010 optimizing | 0.000006 | executing | 0.000009 | end | 0.000003 | query end | 0.000002 | freeing items | 0.000011 | storing result in query cache | 0.000006 | logging slow query | 0.000002 | cleaning up | 0.000002 +---------------------------------+-----------+ 14 rows in set (0.01 sec) mysql> SHOW PROFILE CPU FOR QUERY 1;
如上介绍了SHOW SESSION STATUS及SHOW PROFILE命令，笔者很少使用它们，一般来说，使用SHOW GLOBAL STATUS命令检查状态变量即可。
5.MySQL实例的数据增长
我们需要获取MySQL实例的数据增长情况，以便提前进行扩容，MySQL的information_schema库记录了各个库、表的数据量大小，可以据此统计实例的数据增长情况，以及各个库，甚至各个表的数据增长情况，研发人员通过判断表的数据量增长趋势及数据库的操作频率，大致判断应用的数据库流量的特点，从而更有针对性地进行数据库的应用优化。
需要注意的一点是，SHOW TABLE STATUS命令可以查看表的很多信息，但InnoDB引擎表的统计信息可能不是很准确，尤其是在表特别大的时候。
数据表的大小可根据information_schema.tables表中Data_length和Index_length列的和大致统计。
当我们使用共享表空间的时候，有时希望能够合理分配每个数据文件的大小，还可能需要知道数据文件的空闲空间还有多少。这时，可以启动Tablespace Monitor，通过日志输出收集表空间的信息，通过计算使用的块和空闲的块来判断表空间的空闲空间还有多少，以及数据增长的趋势。但此种方式不易操作，分析也较复杂，所以更合适的办法是简单查询MySQL自带的统计表，据此进行估算。
在实际生产环境中，我们可以定期查询INFORMATION_SCHEMA信息数据库，把收集的数据库大小插入监控数据库，在收集的信息的基础上进行空间趋势分析。
下面的查询将检查数据库argls下面的所有基础表的信息。 SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,ENGINE,TABLE_ROWS ,DATA_LENGTH,INDEX_LENGTH,DATA_FREE FROM tables WHERE TABLE_SCHEMA='argls' AND TABLE_TYPE='BASE TABLE'; 下面的查询将统计数据库argls的大小。 SELECT SUM(DATA_LENGTH),SUM(INDEX_LENGTH),SUM(DATA_FREE) FROM tables WHERE TABLE_SCHEMA='argls' AND TABLE_TYPE='BASE TABLE'; SUM(DATA_LENGTH) | SUM(INDEX_LENGTH) | SUM(DATA_FREE) | 35503304092 | 5593716736 | 42949672960
关于DATA_FREE列，没有太大的参考意义此处不做讲解。
11.2.3 MySQL需要关注的参数及状态变量
以下的一些状态变量，是监控系统需要着重关注的，由于篇幅所限，这里并没有列出所有值得关注的状态变量。
（1）open_files_limit
操作系统允许mysqld打开的文件数量。这个值可以设置得比较大，比如50000，最好在系统初始化安装时就设置了一个较大的值。可修改文件/etc/security/limits.conf来实现，命令如下。
vi /etc/security/limits.conf
* - nofile 50000
（2）max_connect_errors
此值应设置得比较大，如大于5000，以避免因为连接出错而超过出错阈值，导致MySQL阻止该主机连接。如被阻塞，则须手动执行flush-hosts进行复位。
（3）max_connections
允许并行的客户端连接数目。默认值100太小，一般会不够用。
生产环境中建议设置为2000~5000。注意，对于32位的MySQL由于有内存限制，连接数不能过大（建议小于800），否则可能会由于连接过多，造成MySQL实例崩溃。
（4）max_used_connections
MySQL Server启动后曾经到达的最大连接数。如果该值达到max_connections，那么某个时刻存在突然的高峰连接时，可能会有性能问题。
（5）threads_connected
当前打开的连接数量。这个值不能超过设置的max_connections*80%。需要注意及时调整max_connections的值。一旦连接数超过了max_connections，就会出现客户端连接不上的错误。
（6）aborted_connects
试图连接到MySQL服务器而失败的连接数。正常情况下，该值不会持续增加，出现连接失败的原因主要有如下几点。
·客户端程序在退出之前未调用mysql_close()。
· 客户端的空闲时间超过了wait_timeout或interactive_timeout秒，未向服务器发出任何请求。
· 客户端在数据传输中途突然结束。
（7）Aborted_clients
由于客户端没有正确关闭连接导致客户端终止而中断的连接数。
出现下述情况时，服务器将增加“Aborted_clients”（放弃客户端）的状态变量。
·客户端不具有连接至数据库的权限。
·客户端采用了不正确的密码。
·连接信息包含不正确的信息。
·获取连接信息包的时间超过了connect_timeout秒。
我们可以使用如下的命令发现异常。
mysqladmin -uroot -p -S /path/to/tmp//3306/mysql.sock ext | grep Abort
也可以使用tcpdump来判断是什么原因导致了异常。
tcpdump -s 1500 -w tcp.out port 3306
strings tcpdump.out
（8）thread_cache_size
服务器应缓存多少线程以便重新使用？当客户端断开连接时，如果线程少于thread_cache_size，则客户端的线程将被放入缓存。如果有新连接请求分配线程则可以从缓存中重新利用线程，只有当缓存空了时才会创建新线程。如果新连接很多，则可以增加该变量以提高性能。如果是大量并发的短连接，则可能会因为thread_cache_size不够而导致性能问题。生产环境中一般将其设置为100~200。
由于线程可以缓存，所以线程持有的内存不会被轻易释放。
（9）Threads_created
创建用来处理连接的线程数。应该监视Threads_created的增量，如果较多，则需要增加thread_cache_size的值。
以上对thread_cache_size的设置在高并发的时候会很有效。高并发时大量并发短连接对CPU的冲击不容忽视。
（10）threads_running
指同时运行的线程数目。这个值一般不会大于逻辑CPU的个数，如果经常有过多的线程同时运行，那么可能就意味着有性能问题。这个指标很重要，往往表明了一个系统的繁忙程度，它在系统爆发性能问题之前，会有一个上升的趋势，此时收集的性能信息，将有助于我们诊断复杂的性能问题。
（11）slow_launch_threads
如果这个值比较大，则意味着创建线程太慢了，可能是系统出现了性能问题，存在资源瓶颈，从而导致操作系统没有安排足够的CPU时间给新创建的线程。
（12）query_cache_size
为缓存查询结果分配的内存大小。一般设置为256MB。注意不要设置得太大。
可监控查询缓存命中率：Qcache_hits/(Qcache_hits+Com_select)。
更改这个值，会清空所有的缓存结果集，对于非常繁忙的系统，可能会很耗时，导致服务停顿，因为MySQL在删除所有的缓存查询时是逐个进行的。
（13）Qcache_lowmem_prunes
该变量记录了由于查询缓存出现内存不足，而需要从缓存中删除的查询数量，可通过监控Qcache_lowmem_prunes的增量，来衡量是否需要增大query_cache_size。
Qcache_lowmem_prunes状态变量提供的信息能够帮助你调整查询缓存的大小。它可计算为了缓存新的查询而从查询缓存区中移出到自由内存中的查询数目。查询缓存区使用最近最少使用（LRU）策略来确定哪些查询需要从缓存区中移出。
（14）InnoDB_buffer_pool_wait_free
一般情况下，是通过后台向InnoDB缓冲池中写入数据的。但是，如果需要读或创建页，并且没有干净的页可用，那么它还需要先等待页面清空。如果已经适当设置了缓冲池的大小，那么该值应该会很小。
（15）Slow_queries
查询时间超过long_query_time秒的查询个数。应该监控此变量的增量变化，一般1秒内不要超过5~10个，否则可能是有性能问题。
（16）Select_full_join
没有使用索引的连接数量。如果该值较大，则应该仔细检查一下表的索引。
（17）Created_tmp_tables
创建内存临时表的数量，如果Created_tmp_disk_tables比较大，则应该考虑增加tmp_table_size的大小。
注意：应该将tmp_table_size和max_heap_table_size简单调整到大小一样。32MB一般足够了。对这两个参数的控制通常基于内存引擎的临时表可以增长的阈值，若超过了这个阈值，就会转化成On-disk MyISAM表。
（18）Created_tmp_disk_tables
服务器执行语句时在硬盘上自动创建的临时表的数量。
（19）Bytes_received和Bytes_sent
可以用来监控MySQL的流量。
（20）key_buffer_size
MyISAM索引缓冲，实际用到多少就分配多少。不一定需要分配很大的空间，可参考实际观察到的值，不要大于实际值。如下命令可用于评估索引空间的大小。
SELECT SUM(INDEX_LENGTH) FROM INFORMATION_SCHEMA.TABLESWHERE ENGINE='MYISAM';
或者使用操作系统下的命令du进行统计。
$ du -sch `find /path/to/mysql/data/directory/ -name "*.MYI"`
如下公式将计算访问Key的命中率：100-((Key_blocks_unused*key_cache_block_size)*100/key_buffer_size)，但是，该值没有什么实际意义，相对而言，key_reads更有实际意义，因此更值得关注，如下：
$ mysqladmin extended-status -r -i 10 | grep Key_reads
不要把key_buffer_size设置为0，至少也应设置为一个较小的值，比如32MB或64MB，因为MySQL的一些内部操作需要用到MyISAM引擎，如临时表。
（21）Open_tables
当前打开的表的数量
（22）Opened_tables
已经打开的表的数量。查看Open_tables及Opened_tables的增量时，如果Opened_tables的增量比较大，那么可能table_open_cache（或者table_cache）不够用了。如果Open_tables对比table_cache_size并不大，但Opened_tables还在持续增长，那么也可能是显式临时表被不断打开而导致的。
（23）table_open_cache（table_cache 5.1.3之前的参数名）
默认的设置太小了，生产环境中应该将其设置得足够大，数千到一万是比较合理的值。
检查Opened_tables status变量，如果该值比较大，而我们不经常运行FLUSH TABLES命令，那么应该增加table_open_cache的变量值。
（24）table_definition_cache
一般可以将其设置为足够高的值来缓存表定义，比如4096，这并不会耗费什么资源。默认的256太小了。
其他一些反应数据库访问请求、读写数据量的状态变量，这里将不再赘述。
11.3 数据库监控的实现
此处略，这块小编推荐用开源监控工具来做MySQL的监控。
Nagios，zabbix等。具体可以根据自己公司的业务进行选择合适的监控工具。可以对服务器整体和Mysql内核做全面监控的基础上，看看哪款工具，二次开发难度最小，配置比较方便，扩展良好。就选哪个！
此处就不说了！自己查各自的开源论坛的文档，进行部署和实验！
参考：
微信扫一扫，打赏作者吧～
