你真的了解 MySQL 数据库的运行状况吗？

jopen 9年前

2015年第三方市场调查机构 Evans 数据公司最近公布的一系列客户调查数据显示，在过去两年里，MySQL 在所有开发者使用的数据库中获得了25%的市场份额，Evans 公司的本次调查显示，数据库的使用者中有40%是开发人员，而两年前这一数据是32%。

此外 MySQL 越来越被企业级所接受，如今数据日益膨胀，应用越来越广泛，随之而来的 MySQL 性能分析 ， 监控告警 ，集成可视化的讨论也越来越多了，还有利用各种工具对 MySQL 各指标数据进行分析的文章也曾出不穷，今天本文就几个需要注意的重点指标总结一下。

InnoDB 缓冲池

InnoDB 引擎在内存中有一个缓冲池用于缓存数据和索引，这有助于你更快地执行 MySQL 查询语句。选择合适的内存大小需要一些重要的决策并对系统的内存消耗有较多的认识，你需要考虑：

  • 其它的进程需要消耗多少内存。这包括你的系统进程，页表，套接字缓冲。
  • 你的服务器是否专门用于 MySQL 还是你运行着其它非常消耗内存的服务。

在一个专用的机器上，你可能会把 60-70％ 的内存分配给 innodb_buffer_pool_size ，如果你打算在一个机器上运行更多的服务，你应该重新考虑专门用于 innodb_buffer_pool_size 的内存大小，此时需要关注一下几个指标，InnoDB 缓冲池可用页面的数量，使用了多少，总计多少，以及缓冲池的使用率。通过这些指标数据判断数据库的健康状况以及调节内存。

  • mysql.innodb.buffer_pool_free
  • mysql.innodb.buffer_pool_total
  • mysql.innodb.buffer_pool_used
  • mysql.innodb.buffer_pool_utilization

MySQL 进程数，最大连接数

threads_connected 当前客户端已连接的数量。这个值会少于预设的值，但你也会监控到这个值较大，它可保证客户端是处在活跃状态。如果 threads_connected == max_connections 时，数据库系统就不能提供更多的连接数了，这时，如果程序还想新建连接线程，数据库系统就会拒绝，如果程序没做过多的错误处理，就会出现报错信息。

threads_running 处于激活状态的线程的个数，如果数据库超负荷了，你将会得到一个正在（查询的语句持续）增长的数值。这个值也可以少于预先设定的值。这个值在很短的时间内超过限定值是没问题的。当 threads_running 值超过预设值时并且该值在5秒内没有回落时，要同时监视其他的一些值。

max_connections 当前服务器允许多少并发连接。MySQL 服务器允许有 SUPER 权限的用户在最大连接之外再建立一个连接。只有当执行 MySQL 请求的时候才会建立连接，执行完成后会关闭连接并被新的连接取代。

但要记住，过多的连接会导致内存的使用量过高并且会锁住你的 MySQL 服务器。一般小网站需要 100-200 的连接数，而较大可能需要 500-800 甚至更多。此值很大程度上取决于你 MySQL 的使用情况。一下指标表明服务器当前连接数以及最大连接数，

  • mysql.threads_connected
  • mysql.threads_running
  • mysql.net.max_connections

MySQL 临时表和内存表

临时表可以使用任何存储引擎，临时表只在单个连接中可见，当连接断开时，临时表也会消失。MySQL 最初会将临时表创建在内存中，当数据变的太大后，就会转储到磁盘上。内存表是指用 MEMORY 引擎创建的表。表结构存在于磁盘，数据放在内存中。

如果起初在内存中创建的临时表变的太大，MySQL会自动将其转成磁盘上的临时表。内存中的临时表由 tmp_table_size 和 max_heap_table_size 两个参数决定，这与创建 MEMORY 引擎的表不同。MEMORY 引擎的表由 max_heap_table_size 参数决定表的大小，并且它不会转成到在磁盘上的格式。

每次创建临时表（包括内存上和磁盘上）， created_tmp_tables 都会增加，如果是在磁盘上创建临时表（包括从内存上转成磁盘的）， created_tmp_disk_tables 也会增加， created_tmp_files 表示 MySQL 服务创建的临时文件文件数，比较理想的配置是： created_tmp_disk_tables / created_tmp_tables * 100% <= 25%

  • mysql.performance.created_tmp_disk_tables
  • mysql.performance.created_tmp_files
  • mysql.performance.created_tmp_tables

MySQL 查询

数据库是很容易产生瓶颈的地方，其中最影响速度的就是那些查询非常慢的语句，这些慢查询语句，可能是写的不够合理或者是大数据下多表的联合查询等等，所以要重点监控找出这些语句并进行优化。

  • mysql.performance.queries
  • mysql.performance.questions
  • mysql.performance.slow_queries

queries 由服务器执行的语句的数目，该变量包括存储程序中执行的语句，不像 questions 变量。它不包含 COM_PING 和 COM_STATISTICS 命令。这个变量添加近 MySQL 5.0.76版本中。

questions 由服务器执行的语句的数目，如在 MySQL 5.0.72版本，它包括由客户机发送到服务器的执行状态，不再包含存储程序中执行语句，不同于 queries 变量。这个变量不包含 COM_PING，COM_STATISTICS，COM_STMT_PREPARE，COM_STMT_CLOSE，和 COM_STMT_RESET 等命令。

queries 是一个全局性状态变量，而 questions 是一个会话，可以用来看看有多少会话通过当前连接发到服务器。queries 速度上升和下降都是正常的，它不是一个固定阈值的指标。但需要注意的是如果其数值发生急剧下降等突然变化，那就可能出现了严重问题。

slow_queries：查询时间超过 long_query_time 时间的数量，如何定义一个慢查询取决于数据库的使用情况和性能要求。但总之如果慢查询的数量很高，那你需要记录慢查询来定位数据库中的问题并进行调试。可以通过在你的 MySQL 配置文件中添加以下值来启用：

slow-query-log = 1 slow-query-log-file = /var/lib/mysql/mysql-slow.log long_query_time = 1

第一个变量启用慢查询日志，第二个指定 MySQL 实际的日志文件存储位置。使用 long_query_time 来定义完成 MySQL 查询多少用时算长。

MySQL 的查询缓存

如果你有很多重复的查询并且数据不经常改变那建议使用缓存查询。人们经常不理解 query_cache_size 的实际含义而将它设置为 GB 级，但这样设置实际上会降低服务器的性能。

原因是在更新过程中线程需要锁定缓存。通常设置为 200-300 MB应该足够了。如果你的网站比较小的，你可以尝试给 64M 并在以后需要时及时增加。以下指标是查询缓存命中率，键缓存利用率：

  • mysql.performance.qcache_hits
  • mysql.performance.key_cache_utilization

MySQL 主从复制

说了那么多，还有一个很重要的 MySQL 主从复制，主从复制的好处不用多说：采用主从服务器这种架构，稳定性得以提升。如果主服务器发生故障，可以使用从服务器来提供服务。在主从服务器上分开处理用户的请求，可以提升数据处理效率。将主服务器上的数据复制到从服务器上，保护数据免受意外的损失等等。其连接状态可以通过下面命令查看：

mysql&gt;SHOW SLAVE STATUS\G；

在 Master 与 Slave 之间完成一个异步的复制过程需要由三个线程来完成，其中两个线程（ Sql 线程和 IO 线程）在 Slave 端，另外一个线程（ IO 线程）在 Master 端。 Slave_IO_Running 和 Slave_SQL_Running 两列的值都为 "Yes"，这表明 Slave 的 I/O 和 SQL 线程都在正常运行，主从同步功能也就是正常的。

  • mysql.replication.slave_io_running
  • mysql.replication.slave_sql_running
  • mysql.replication.last_io_errno
  • mysql.replication.last_sql_errno

Cloud Insight 可视化监控

说了那么多 MySQL 查询，缓冲，连接数，内存表临时表，主从复制，现在回到主题上，你了解自己 MySQL 数据库的运行状况吗，现在有使用什么监控工具，是否可以实时可视化监控，是否需要专人来进行配置，是否可以内网部署监控，是否可以对每个指标设置报警策略？如果想体验拥有以上功能的监控工具，那必然要试试 Cloud Insight ，一定不会让您失望，它支持 MySQL 以上所有指标，更多指标参考 Cloud Insight MySQL 监控 ，其实也没什么大不了的功能，无非是：

  • 安装上即可实时监控数据库指标数据
  • 探针一键安装（大概14s），简单部署（有的只需复制一份配置文件）
  • 支持内网部署
  • 可针对每个指标设置报警策略

最后再贴个图，随意放几个指标数据：

好啦，说完了撤啦 Y(^_^)Y

参考文章：

Cloud Insight 集监控、管理、计算、协作、可视化于一身，帮助所有 IT 公司，减少在系统监控上的人力和时间成本投入，让运维工作更加高效、简单。想阅读更多技术文章，请访问 OneAPM 官方技术博客 。 本文转自 OneAPM 官方博客

来自： http://www.ituring.com.cn/article/210697