显示 MySQL 健康状态的脚本？

yes123 提出的问题非常相关，因为我们都想知道在不触及使用该数据的应用程序的情况下，我们可以做些什么来提高 MySQL 的性能。什么是让一切变得更好的完美配置？尽管没有一个程序可以简单地吐出完美的配置，但有一些方法可以在不触及任何代码的情况下显着提高服务器的效率。首先，我从服务器的健康状况以及该服务器注定要提供的数据类型开始。

了解 MySQL 数据库服务器的健康状况以及如何更好地配置它必须包括基于大量变量的许多因素。您的 MySQL 服务器用于什么用途，博客、电子商务、报告？您使用的主要引擎是什么，InnoDB、MyISAM？什么是操作系统、Windows、Linux、Unix？是否涉及复制？

了解这一点很重要，因为您配置 MySQL 的方式取决于所有这些因素以及安装 MySQL 的机器的健康状况。通常，人们很快就会将系统范围内的问题归咎于 MySQL。如果您在只有 1 个 CPU、1Gb 内存的服务器上设置 Magento，并且由于您有 1000 个在 Apache 上同时竞争而导致内存不足，这是 MySQL 的错吗？明显不是。

所以在你开始问自己，MySQL 怎么样之前，问问你自己，它安装的服务器怎么样？

例如，在处理 Linux 服务器时，以下是我执行一般健康检查所遵循的步骤

1. 有足够的内存吗？答案：免费 -m
2. 安装 MySQL 的地方是否有足够的磁盘空间，通常是 /var/lib/mysql. 答案：df -h
3. 什么是平均负载，是 100% 的 CPU 或 CPU。答案：top 或 htop
4. 网络上有多少流量。答案：bwm-ng

对于 Windows Server，我将使用 perfmon 或进程资源管理器。

如果我没有剩余磁盘空间，没有剩余内存和/或您的网络带宽饱和，则可能不是 MySQL 的错。由于 MySQL 停止工作或超时，我可能认为 MySQL 只是另一个问题的受害者

一个非常好的免费的 Linux 图形程序称为 ksar http://sourceforge.net/projects/ksar/。您可以使用ksar根据 sar 服务收集的统计信息生成图表。sar 可以很容易地安装在 debian 和 centos 上，并收集有关内存、cpu、磁盘、网络使用情况的统计信息。sar 本身会在数字报告中为您提供该信息。ksar 更进一步，将这些信息放入基于时间的图表中，帮助可视化您的服务器在一天中的任何给定时间点或过去任何给定日期的运行状况。

在 Windows 中，您需要可以做同样事情的东西，例如 Process Explorer http://technet.microsoft.com/en-us/sysinternals/bb896653或 perfmon。ksar 和 process explorer 都是免费的。

一旦我消除了服务器或其他应用程序（如 Apache）作为可能的罪魁祸首，那就是我开始研究 MySQL 的时候。

如果有错误日志，我将从 MySQL 的错误日志开始，然后查找磁盘空间错误、表损坏错误或其他突出的消息。如果我没有发现任何异常，我会使用我最喜欢的工具 Jet Profiler http://www.jetprofiler.com。

Jet Profiler为您提供给定时间段内 MySQL 服务器状态的实时统计信息。这比任何给定时刻的简单活动快照要有用得多。了解您的服务器在低高峰和高峰时段的行为最终将揭示基础设施的薄弱环节，这就是 Jet Profiler 的用武之地。它的分析和统计基于服务器随时间推移的工作负载。

查找有问题的查询

作为第一步，我尝试确定哪些有问题的查询。检查顶部查询和慢查询，并比较它们各自的相对线程负载。可能，其中一些会从其他人中脱颖而出（根据 90/10 规则左右）。这些是要关注的查询，因为它们将在每次修复时提供最大的性能改进。

负载模式

负载模式也可能是服务器在大多数时间内运行良好，但有时会出现重复的峰值。放大峰值以发现在该时间范围内哪些查询很繁重将非常有帮助。即，如果您有每天运行一次的繁重 cron 作业，它们可能不会在过去 24 小时内的热门查询中显示得太高，因此您可能不会考虑它们。但是，它们仍然会导致如此多的负载和拥塞，以至于服务器在午夜变得非常缓慢。我分析的一个数据库具有这种模式 - 开发人员添加了每日 cron 作业，这些作业执行各种清理、归档和统计计算。问题是所有作业都从 0 0 * * * 开始。虽然服务器可以处理其中的一两个工作并且仍然为常规网站访问者提供服务，但 5+ 并发工作太多了。所以网站每个午夜都会停机一两分钟。但是除非我们查看那个特定的峰值，否则这些查询可能不会出现。

更进一步，我继续讨论特定于我的 MySQL 表使用 MyISAM 或 InnoDB 的引擎的统计信息。

不过，在此之前，我问自己一个重要的问题。数据库主要是 InnoDB 表还是 MyISAM 表？

    use database;
    show table status \G;

*************************** 148. row ***************************
           Name: sometable
         Engine: InnoDB

MySQL 中的许多生产数据库成为 InnoDB 和 MyISAM 的混合体。这需要对两个引擎的 MySQL 进行优化。

InnoDB 特定信息

1. 缓冲池命中率：通常应为 100%。如果不是，MySQL 将花费大量时间读取磁盘而不是内存来检索数据。增加缓冲池，可能会解决这个问题。
2. InnoDB 磁盘 I/O 统计信息。服务器是否有磁盘问题，如果 InnoDB 正在向服务器写入千兆字节的数据，也许我需要升级到更快的东西。

MyISAM 特定信息

1. 锁定查询：由于锁定，查询很容易在 MyISAM 表中相互阻塞。Jet Profiler 将向您显示哪些查询被锁定以及在哪些表上。然后，您可以通过将表引擎从 MyISAM 切换到 InnoDB 轻松解决此问题。

2. MyISAM 缓存：这也应该尽可能接近 100%。如果没有，您可以增加键缓冲区缓存来解决问题和/或构建更好的索引。

接下来我看看全球正在发生什么。

全球因素

1. 状态：MySQL 在做什么？是将数据复制到临时表，是通过网络发送数据还是排序。换句话说，MySQL 中的哪些进程占用了最多的 CPU、内存和 IO。
2. 慢查询：哪些查询占用了 CPU 的大部分时间。您也可以通过激活服务器上的慢查询日志来获得此信息，但这会影响服务器，因为它会将这些查询写入磁盘。
3. 同时查询/用户的数量。也许我的小服务器上的用户太多了。

关联 KSAR 和 Jet Profiler 统计数据

使用 Ksar 和 Jet Profiler 的图表，您实际上可以将服务器性能图表与 MySQL 的性能图表关联起来。MySQL 开始运行该批处理作业时服务器的行为如何。如果您在 MySQL 处理大量导入时注意到 KSAR 中的大量主要页面错误，而当 MySQL 正在同时为 1000 个用户提供服务时，您看到在 Jet Profiler 中作为 Top Query 运行，那么您可以简单地在存在时运行导入没有那么多传入连接。

然后还有其他因素，例如是否打开了跳过名称解析功能。如果 MySQL 必须花时间解析域名，那么它在检索数据上花费的时间就更少了。我还查看了查询计划缓存以确保它也是 100%

就网络解决方案而言。我开始发现 Zabbix 的乐趣http://www.zabbix.com/。它旨在同时监控多台服务器，但能够提供有关 MySQL 以及所有类型的服务器和技术的非常精确的详细信息。

拥有这样完美的配置是可能的。通过对您的操作系统的透彻了解、对服务器运行状况的智能分析、查询其处理以及您要求它处理的工作负载类型，您可以专注于那些需要您关注的领域。只需在缓冲池中添加几 GB 或获得更快的磁盘可能会提高服务器的性能，但是为什么要在 ram 和更快的磁盘上花钱，因为解决方案可能只需要花费几个小时来分析服务器的工作负载及其如何即使您不在那里监视它也能表现得很好。

这个空间有几个选项。

mysqltuner.pl是缓冲池太小等信息的最爱。开发人员做了一个很好的技巧，您可以通过运行wget mysqltuner.pl. 它输出类似的东西

 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.61
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 6M (Tables: 30)
[--] Data in InnoDB tables: 359M (Tables: 1206)
[!!] Total fragmented tables: 21

-------- Security Recommendations  -------------------------------------------
[!!] User '@localhost' has no password set.
[!!] User '[email protected]' has no password set.
[!!] User 'root@localhost' has no password set.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 9d 15h 35m 57s (30K q [0.036 qps], 9K conn, TX: 7M, RX: 1M)
[--] Reads / Writes: 50% / 50%
[--] Total buffers: 322.0M global + 5.4M per thread (15 max threads)
[OK] Maximum possible memory usage: 402.6M (6% of installed RAM)
[OK] Slow queries: 0% (0/30K)
[OK] Highest usage of available connections: 13% (2/15)
[OK] Key buffer size / total MyISAM indexes: 2.0M/921.0K
[OK] Key buffer hit rate: 100.0% (19K cached / 8 reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 3 sorts)
[OK] Temporary tables created on disk: 0% (0 on disk / 6 total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 34% (10 open / 29 opened)
[OK] Open file limit used: 0% (19/2K)
[OK] Table locks acquired immediately: 99% (10K immediate / 10K locks)
[!!] InnoDB data size / buffer pool: 359.3M/256.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Set thread_cache_size to 4 as a starting value
Variables to adjust:
    query_cache_size (>= 8M)
    thread_cache_size (start at 4)
    innodb_buffer_pool_size (>= 359M)

正如 PythianMoore 提到的，pt-variable-advisor是另一种选择。它检查与 mysqltuner.pl 不同的东西。例如，它输出

# NOTE auto_increment: Are you trying to write to more than one server in a dual-master or ring replication configuration?  This is potentially very dangerous and in most cases is a serious mistake.

# WARN delay_key_write: MyISAM index blocks are never flushed until necessary.

# WARN innodb_additional_mem_pool_size: This variable generally doesn't need to be larger than 20MB.

# WARN innodb_checksums: InnoDB checksums are disabled.

# WARN innodb_doublewrite: InnoDB doublewrite is disabled.

# WARN innodb_flush_log_at_trx_commit-1: InnoDB is not configured in strictly ACID mode.

# NOTE log_warnings-2: Log_warnings must be set greater than 1 to log unusual events such as aborted connections.

# NOTE max_connect_errors: max_connect_errors should probably be set as large as your platform allows.

# NOTE read_buffer_size-1: The read_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.

# NOTE read_rnd_buffer_size-1: The read_rnd_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.

# WARN slave_net_timeout: This variable is set too high.

# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.

# WARN sync_frm: It is best to set sync_frm so that .frm files are flushed safely to disk in case of a server crash.

# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.

# WARN innodb_support_xa: MySQL's internal XA transaction support between InnoDB and the binary log is disabled.

# WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.

# WARN myisam_recover_options: myisam_recover_options should be set to some value such as BACKUP,FORCE to ensure that table corruption is noticed.

# WARN sync_binlog: Binary logging is enabled, but sync_binlog isn't configured so that every transaction is flushed to the binary log for durability.

这些是我遇到的人使用的主要两个。祝你好运！

您可以运行此查询来检查 innodb 缓冲池大小。它将根据您的数据库的工作负载、数据大小、索引等对您必须设置的 innodb 缓冲池的实际大小给出建议。

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS 
FROM
  (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
  FROM information_schema.tables
  WHERE ENGINE='InnoDB') A;

使用这个查询来检查你的 innodb 缓冲池的使用情况

SELECT (PagesData*PageSize)/POWER(1024,3) DataGB 
FROM
  (SELECT variable_value PagesData
  FROM performance_schema.global_status
  WHERE variable_name='Innodb_buffer_pool_pages_data') A,
  (SELECT variable_value PageSize
  FROM performance_schema.global_status
  WHERE variable_name='Innodb_page_size') B;