8.12 优化 MySQL 服务器
8.12.1 系统因素
- 如果您有足够的 RAM,则可以删除所有交换设备。即使您有空闲内存,某些操作系统在某些情况下也会使用交换设备。
- 避免 MyISAM表的外部锁定。默认为禁用外部锁定。和 选项显式启用--external-locking和 --skip-external-locking 禁用外部锁定。
只要您只运行一台服务器,禁用外部锁定就不会影响 MySQL 的功能。请记住在运行myisamchk之前关闭服务器(或锁定并刷新相关表) 。在某些系统上,必须禁用外部锁定,因为无论如何它都不起作用。
唯一不能禁用外部锁定的情况是当您 在同一数据上 运行多个 MySQL服务器(不是客户端)时,或者如果您运行myisamchk来检查(而不是修复)表而不告诉服务器先刷新和锁定表. 请注意,通常不 建议使用多个 MySQL 服务器同时访问相同的数据,除非使用 NDB Cluster。
LOCK TABLESand UNLOCK TABLES语句使用内部锁定,因此即使外部锁定被禁用,您也可以使用它们 。
8.12.2 优化磁盘 I/O
本节介绍当您可以将更多更快的存储硬件用于数据库服务器时配置存储设备的方法。有关优化 InnoDB
配置以提高 I/O 性能的信息,请参阅第 8.5.8 节,“优化 InnoDB 磁盘 I/O”。
- 磁盘寻道是一个巨大的性能瓶颈。当数据量开始增长到无法进行有效缓存时,这个问题就会变得更加明显。对于您或多或少随机访问数据的大型数据库,您可以确定您至少需要一个磁盘搜索来读取数据,并且需要几个磁盘搜索来写入数据。为尽量减少此问题,请使用寻道时间较短的磁盘。
- 通过将文件符号链接到不同的磁盘或对磁盘进行条带化,增加可用磁盘轴的数量(从而减少寻道开销):
- 使用符号链接
这意味着,对于MyISAM
表,您将索引文件和数据文件从它们在数据目录中的通常位置符号链接到另一个磁盘(也可能是条带化的)。假设磁盘也没有用于其他目的,这使得查找和读取时间都变得更好。请参阅 第 8.12.3 节,“使用符号链接”。
不支持将符号链接与 InnoDB
表一起使用。但是,可以将InnoDB
数据和日志文件放在不同的物理磁盘上。有关更多信息,请参阅第 8.5.8 节,“优化 InnoDB 磁盘 I/O”。
条带化意味着您有许多磁盘并将第一个块放在第一个磁盘上,第二个块放在第二个磁盘上,N
第一个块放在 ( ) 磁盘上,依此类推。这意味着如果您的正常数据大小小于条带大小(或完全对齐),您将获得更好的性能。条带化非常依赖于操作系统和条带大小,因此请使用不同的条带大小对您的应用程序进行基准测试。请参阅第 8.13.2 节,“使用您自己的基准”。 N
MOD
number_of_disks
条带化的速度差异 很大程度上取决于参数。根据您设置条带化参数和磁盘数量的方式,您可能会得到数量级的差异。您必须选择针对随机或顺序访问进行优化。
- 为了可靠性,您可能希望使用 RAID 0+1(条带化加镜像),但在这种情况下,您需要 2 个
N
驱动器来保存N
数据驱动器。如果你有钱,这可能是最好的选择。但是,您可能还必须投资一些卷管理软件才能有效地处理它。 - 一个不错的选择是根据数据类型的重要性来改变 RAID 级别。例如,将可以重新生成的次重要数据存储在 RAID 0 磁盘上,但将主机信息和日志等真正重要的数据存储在 RAID 0+1 或 RAID
N
磁盘上。N
由于更新奇偶校验位需要时间,如果您有很多写入, RAID 可能会成为问题。 - 您还可以为数据库使用的文件系统设置参数:
如果您不需要知道上次访问文件的时间(这在数据库服务器上并不是很有用),您可以使用该-o noatime
选项挂载文件系统。这会跳过对文件系统上 inode 中最后一次访问时间的更新,从而避免了一些磁盘寻道。
在许多操作系统上,您可以通过使用-o async
选项挂载文件系统来将文件系统设置为异步更新。如果您的计算机相当稳定,这应该会在不牺牲太多可靠性的情况下为您提供更好的性能。(此标志在 Linux 上默认开启。)
在考虑是否将 NFS 与 MySQL 一起使用时,您应该谨慎。潜在问题因操作系统和 NFS 版本而异,包括:
- 放置在 NFS 卷上的 MySQL 数据和日志文件被锁定且无法使用。在 MySQL 的多个实例访问同一数据目录或 MySQL 因断电等原因未正确关闭的情况下,可能会出现锁定问题。NFS 版本 4 通过引入咨询和基于租约的锁定解决了潜在的锁定问题。但是,不建议在 MySQL 实例之间共享数据目录。
- 由于消息接收无序或网络流量丢失而引入的数据不一致。要避免此问题,请使用 TCP
hard
和intr
挂载选项。 - 最大文件大小限制。NFS 版本 2 客户端只能访问文件的最低 2GB(带符号的 32 位偏移)。NFS 版本 3 客户端支持更大的文件(最多 64 位偏移)。支持的最大文件大小还取决于 NFS 服务器的本地文件系统。
在专业的 SAN 环境或其他存储系统中使用 NFS 往往比在此类环境之外使用 NFS 提供更高的可靠性。但是,SAN 环境中的 NFS 可能比直接连接或总线连接的非旋转存储慢。
如果您选择使用 NFS,建议使用 NFS 版本 4 或更高版本,因为在部署到生产环境之前彻底测试您的 NFS 设置。
8.12.3 使用符号链接
8.12.3.1 在 Unix 上为数据库使用符号链接
在 Unix 上,符号链接数据库的方法是首先在您有可用空间的某个磁盘上创建一个目录,然后从 MySQL 数据目录创建一个到它的软链接。
$> mkdir /dr1/databases/test
$> ln
-s /dr1/databases/test
/path/to/datadir
MySQL 不支持将一个目录链接到多个数据库。只要您不在数据库之间建立符号链接,就可以用符号链接替换数据库目录。假设您 db1
在 MySQL 数据目录下有一个数据库,然后创建一个db2
指向 的符号链接db1
:
$> cd
/path/to/datadir
$> ln
-s db1 db2
结果是,对于 中的任何表tbl_a
, db1
似乎也存在 中的 表tbl_a
。db2
如果一个客户端更新db1.tbl_a
,另一个客户端更新db2.tbl_a
,很可能会出现问题。
8.12.3.2 在 Unix 上为 MyISAM 表使用符号链接
8.12.3.2 在 Unix 上为 MyISAM 表使用符号链接
仅 MyISAM
表完全支持符号链接。对于其他存储引擎的表使用的文件,如果您尝试使用符号链接,您可能会遇到奇怪的问题。对于InnoDB
表,请改用 第 14.6.1.2 节“在外部创建表”中解释的替代技术。
不要在没有完全可操作realpath()
调用的系统上对表进行符号链接。(Linux 和 Solaris 支持realpath()
)。要确定您的系统是否支持符号链接,请have_symlink使用以下语句检查系统变量的值:
SHOW
VARIABLES
LIKE
'have_symlink';
表的符号链接处理MyISAM
如下:
- 在数据目录中,您始终拥有表格格式 (
.frm
) 文件、数据 (.MYD
) 文件和索引 (.MYI
) 文件。数据文件和索引文件可以移动到其他地方并通过符号链接在数据目录中替换。格式文件不能。 - 您可以将数据文件和索引文件独立地符号链接到不同的目录。
- 要指示正在运行的 MySQL 服务器执行符号链接,请使用
DATA DIRECTORY
和INDEX DIRECTORY
选项来 CREATE TABLE. 请参阅 第 13.1.18 节,“CREATE TABLE 语句”。或者,如果 mysqld未运行,则可以使用 命令行中的 ln -s手动完成符号链接。
笔记
DATA DIRECTORY
与和选项 中的一个或两个一起使用的路径INDEX DIRECTORY
可能不包括 MySQL data
目录。(错误 #32167)
- myisamchk不会用数据文件或索引文件替换符号链接。它直接作用于符号链接指向的文件。任何临时文件都在数据文件或索引文件所在的目录中创建。对于 ALTER TABLE, OPTIMIZE TABLE和 REPAIR TABLE语句也是如此。
- 笔记
当您删除使用符号链接的表时,符号 链接和符号链接指向的文件都将被删除。这是不以操作系统用户身份运行 mysqldroot
或允许操作系统用户对 MySQL 数据库目录具有写入权限 的一个非常好的理由。
- 如果使用 ALTER TABLE ... RENAMEor重命名表RENAME TABLE并且不将该表移动到另一个数据库,则数据库目录中的符号链接将重命名为新名称,并且数据文件和索引文件也将相应地重命名。
- 如果使用 ALTER TABLE ... RENAME或RENAME TABLE将一个表移动到另一个数据库,则该表将移动到另一个数据库目录。如果表名更改,则新数据库目录中的符号链接将重命名为新名称,数据文件和索引文件也将相应重命名。
- 如果您不使用符号链接,请使用选项启动 mysqld, --skip-symbolic-links 以确保没有人可以使用 mysqld删除或重命名数据目录之外的文件。
不支持这些表符号链接操作:
- ALTER TABLE忽略
DATA DIRECTORY
和INDEX DIRECTORY
表选项。 - 如前所述,只有数据和索引文件可以是符号链接。该
.frm
文件绝 不能是符号链接。尝试这样做(例如,使一个表名成为另一个表名的同义词)会产生不正确的结果。假设您db1
在 MySQL 数据目录下有一个数据库,tbl1
该数据库中有一个表,并且在该目录中创建了一个指向db1
的符号链接:tbl2tbl1
- $> cd
/db1
- $> ln
tbl1
.frm tbl2
.frm
- $> ln
tbl1
.MYD tbl2
.MYD
$> ln
-s tbl1
.MYI tbl2
.MYI
如果一个线程读取 db1.tbl1
并且另一个线程更新, 则会导致问题db1.tbl2
:
-
- 查询缓存被“愚弄”(它无法知道
tbl1
尚未更新,因此它返回过时的结果)。 ALTER
关于tbl2
失败的陈述。
- 查询缓存被“愚弄”(它无法知道
8.12.3.3 在 Windows 上为数据库使用符号链接
8.12.3.3 在 Windows 上为数据库使用符号链接
在 Windows 上,符号链接可用于数据库目录。这使您可以通过设置符号链接将数据库目录放在不同的位置(例如,在不同的磁盘上)。Windows 上数据库符号链接的使用与 Unix 上的使用类似,尽管设置链接的过程不同。
假设您要放置名为mydb
at 的数据库的数据库目录D:\data\mydb
。为此,请在 MySQL 数据目录中创建一个指向 D:\data\mydb
. D:\data\mydb
但是,在创建符号链接之前,如果需要,请通过创建该目录来确保该 目录存在。如果您已经mydb
在数据目录中命名了一个数据库目录,请将其移动到D:\data
. 否则,符号链接无效。为避免出现问题,请确保在移动数据库目录时服务器未运行。
在 Windows 上,您可以使用mklink命令 创建符号链接 。此命令需要管理权限。
- 确保存在所需的数据库路径。对于此示例,我们使用
D:\data\mydb
, 和一个名为 的数据库mydb
。 - 如果数据库尚不存在,
CREATE DATABASE mydb
请在 mysql客户端中发出以创建它。 - 停止 MySQL 服务。
- 使用 Windows 资源管理器或命令行,将目录
mydb
从数据目录移动到D:\data
,替换同名目录。 - 如果您尚未使用命令提示符,请将其打开,然后将位置更改为数据目录,如下所示:
C:\> cd
\path\to\datadir
如果你的 MySQL 安装在默认位置,你可以使用这个:
C:\> cd C
:\ProgramData\MySQL\MySQL Server 5
.7\Data
- 在数据目录中,创建一个名为
mydb
指向数据库目录位置的符号链接:
C:\> mklink /d mydb D
:\data\mydb
- 启动 MySQL 服务。
在此之后,在数据库 mydb
中创建的所有表都在 D:\data\mydb
.
.sym
或者,在 MySQL 支持的任何 Windows 版本上,您可以通过在包含目标目录路径的数据目录中 创建文件来创建指向 MySQL 数据库的符号链接。该文件应命名为 db_name
.sym
,其中db_name
是数据库名称。
在 Windows 上使用文件支持数据库符号链接 .sym
默认启用。如果您不需要文件符号链接,您可以通过使用该 选项启动mysqld.sym
来禁用对它们的支持 。要确定您的系统是否支持 文件符号链接,请使用以下语句检查系统变量的值: --skip-symbolic-links.sym
have_symlink
SHOW
VARIABLES
LIKE
'have_symlink';
要创建.sym
文件符号链接,请使用以下过程:
- 将位置更改为数据目录:
C:\> cd
\path\to\datadir
- 在数据目录中,创建一个名为的文本文件
mydb.sym
,其中包含此路径名:D:\data\mydb\
笔记
新数据库和表的路径名应该是绝对的。如果您指定相对路径,则该位置是相对于mydb.sym
文件的。
在此之后,在数据库 mydb
中创建的所有表都在 D:\data\mydb
.
笔记
由于对.sym
文件的支持与使用 mklink提供的本机符号链接支持是多余的,因此不推荐使用.sym
文件;期望在未来的 MySQL 版本中删除对它们的支持。
以下限制适用于 .sym
在 Windows 上使用文件进行数据库符号链接。这些限制不适用于使用mklink创建的符号链接。
- 如果 MySQL 数据目录中存在与数据库同名的目录,则不使用符号链接。
- --innodb_file_per_table 无法使用 该选项。
- 如果将mysqld作为服务运行,则不能将映射到远程服务器的驱动器用作符号链接的目标。作为一种解决方法,您可以使用完整路径 (
\\servername\path\
)。
您可以将数据库或表从数据库目录移动到其他位置,并将它们替换为指向新位置的符号链接。您可能希望这样做,例如,将数据库移动到具有更多可用空间的文件系统,或者通过将表分散到不同的磁盘来提高系统的速度。
对于InnoDB表,请使用语句的DATA DIRECTORY子句CREATE TABLE而不是符号链接,如第 14.6.1.2 节,“在外部创建表”中所述。此新功能是一种受支持的跨平台技术。
推荐的方法是将整个数据库目录符号链接到不同的磁盘。符号链接 MyISAM表仅作为最后的手段。
要确定数据目录的位置,请使用以下语句:
SHOW VARIABLES LIKE 'datadir';
本节讨论数据库服务器的优化技术,主要处理系统配置而不是调整 SQL 语句。本节中的信息适用于希望确保其管理的服务器的性能和可伸缩性的 DBA;适用于构建安装脚本(包括设置数据库)的开发人员;以及为开发、测试等而自己运行 MySQL 的人,他们希望最大限度地提高自己的生产力。
8.12.4 优化内存使用
8.12.4.1 MySQL 如何使用内存
MySQL 分配缓冲区和缓存以提高数据库操作的性能。默认配置旨在允许 MySQL 服务器在具有大约 512MB RAM 的虚拟机上启动。您可以通过增加某些缓存和缓冲区相关系统变量的值来提高 MySQL 性能。您还可以修改默认配置以在内存有限的系统上运行 MySQL。
下面的列表描述了 MySQL 使用内存的一些方式。在适用的情况下,引用了相关的系统变量。有些项目是存储引擎或特定于功能的。
InnoDB
缓冲池是一个内存区域,用于保存InnoDB
表、索引和其他辅助缓冲区的缓存数据 。为了提高大容量读取操作的效率,缓冲池被划分为 可能包含多行的页面。为了缓存管理的效率,缓冲池被实现为页链表;使用LRU算法的变体,很少使用的数据会从缓存中老化 。有关更多信息,请参阅第 14.5.1 节,“缓冲池”。
缓冲池的大小对系统性能很重要:
-
InnoDBmalloc()
在服务器启动时使用操作为整个缓冲池分配内存 。系统 innodb_buffer_pool_size 变量定义缓冲池大小。通常,推荐 innodb_buffer_pool_size 值为系统内存的 50% 到 75%。 innodb_buffer_pool_size 可以在服务器运行时动态配置。有关更多信息,请参阅 第 14.8.3.1 节,“配置 InnoDB 缓冲池大小”。- 在具有大量内存的系统上,您可以通过将缓冲池划分为多个 缓冲池实例来提高并发性。系统变量定义缓冲池实例的 innodb_buffer_pool_instances 数量。
- 太小的缓冲池可能会导致过度搅动,因为页面从缓冲池中刷新,只是在短时间内再次需要。
- 过大的缓冲池可能会因为内存竞争而导致交换。
- 所有线程共享MyISAM 密钥缓冲区。key_buffer_size系统变量决定了它的大小 。
对于服务器打开的每个MyISAM
表,索引文件打开一次;对于访问该表的每个并发运行的线程,数据文件打开一次。对于每个并发线程,分配一个表结构、每列的列结构和一个大小的缓冲区 (其中是最大行长度,不包括 列)。一 列需要五到八个字节加上 数据的长度。存储引擎维护一个额外的行缓冲区供内部使用 。 3 *
N
N
BLOBBLOBBLOBMyISAM
- 系统myisam_use_mmap 变量可以设置为 1 以启用所有
MyISAM
表的内存映射。 - 如果内部内存临时表变得太大(由 tmp_table_size和 max_heap_table_size 系统变量确定),MySQL 会自动将表从内存转换为磁盘格式。磁盘临时表使用 internal_tmp_disk_storage_engine 系统变量定义的存储引擎。您可以增加允许的临时表大小,如 第 8.4.4 节,“MySQL 中的内部临时表使用”中所述。
对于使用MEMORY显式创建的表CREATE TABLE,只有 max_heap_table_size 系统变量确定表可以增长到多大,并且不会转换为磁盘格式。
- MySQL Performance Schema是一种用于在低级别监视 MySQL 服务器执行 的功能。性能模式以增量方式动态分配内存,将其内存使用扩展到实际的服务器负载,而不是在服务器启动期间分配所需的内存。一旦分配了内存,在服务器重新启动之前它不会被释放。有关更多信息,请参阅 第 25.17 节,“性能模式内存分配模型”。
- 服务器用来管理客户端连接的每个线程都需要一些特定于线程的空间。以下列表指示这些以及哪些系统变量控制它们的大小:
- 一个堆栈 ( thread_stack)
- 连接缓冲区 ( net_buffer_length)
- 结果缓冲区 ( net_buffer_length)
连接缓冲区和结果缓冲区 net_buffer_length均以字节大小开始,但根据需要动态扩大到 max_allowed_packet字节。结果缓冲区 net_buffer_length在每个 SQL 语句之后缩小为字节。在运行语句时,还会分配当前语句字符串的副本。
每个连接线程都使用内存来计算语句摘要。服务器为每个会话分配 max_digest_length字节。请参阅 第 25.10 节,“性能模式语句摘要”。
- 所有线程共享相同的基本内存。
- 当不再需要某个线程时,分配给它的内存将被释放并返回给系统,除非该线程返回到线程缓存中。在这种情况下,内存保持分配状态。
- 每个对表执行顺序扫描的请求都会分配一个读取缓冲区。系统 read_buffer_size变量确定缓冲区大小。
- 当以任意顺序读取行时(例如,在排序之后),可能会分配一个 随机读取缓冲区 以避免磁盘寻道。系统 read_rnd_buffer_size 变量确定缓冲区大小。
- 所有连接都在一次执行中执行,大多数连接甚至可以在不使用临时表的情况下完成。大多数临时表是基于内存的哈希表。具有大行长度(计算为所有列长度的总和)或包含 BLOB列的临时表存储在磁盘上。
- 大多数执行排序的请求会根据结果集大小分配一个排序缓冲区和零到两个临时文件。请参阅第 B.3.3.5 节,“MySQL 存储临时文件的位置”。
- 几乎所有的解析和计算都是在线程本地和可重用的内存池中完成的。小项目不需要内存开销,从而避免了正常缓慢的内存分配和释放。仅为意外的大字符串分配内存。
- 对于每个具有BLOB 列的表,动态扩大缓冲区以读取更大的BLOB值。如果你扫描一个表,缓冲区会增长到 BLOB最大值。
- MySQL 需要用于表缓存的内存和描述符。所有正在使用的表的处理程序结构都保存在表缓存中,并按照“先进先出”(FIFO)进行管理。系统 table_open_cache变量定义了初始表缓存大小;请参阅 第 8.4.3.1 节,“MySQL 如何打开和关闭表”。
MySQL 还需要内存用于表定义缓存。table_definition_cache 系统变量定义.frm
可以存储在表定义缓存中的表定义(来自文件)的 数量。如果使用大量的表,可以创建一个大的表定义缓存来加快表的打开速度。与表缓存不同,表定义缓存占用较少空间并且不使用文件描述符。
- 一条FLUSH TABLES语句或 mysqladmin flush-tables命令会立即关闭所有未使用的表,并将所有正在使用的表标记为在当前执行的线程完成时关闭。这有效地释放了大多数正在使用的内存。FLUSH TABLES在所有表都关闭之前不会返回。
- GRANT作为, CREATE USER, CREATE SERVER, 和 INSTALL PLUGIN语句 的结果,服务器将信息缓存在内存中 。该内存不会被相应 REVOKE的 , DROP USER, DROP SERVER和 UNINSTALL PLUGIN 语句释放,因此对于执行许多导致缓存的语句实例的服务器,缓存内存使用很可能会增加,除非它被释放 FLUSH PRIVILEGES。
ps和其他系统状态程序可能会报告mysqld使用大量内存。这可能是由不同内存地址上的线程堆栈引起的。例如,Solaris 版本的 ps将堆栈之间的未使用内存计为已用内存。要验证这一点,请检查可用的交换 swap -s
。我们 用几个内存泄漏检测器(商业和开源) 测试mysqld ,所以应该没有内存泄漏。
以下示例演示了如何使用 Performance Schema 和sys schema来监控 MySQL 内存使用情况。
默认情况下,大多数性能模式内存检测是禁用的。可以通过更新 Performance Schema表的ENABLED
列 来启用仪器。setup_instruments记忆仪器的名称格式为 ,其中是或等值, 是仪器详细信息。 memory/
code_area
/
instrument_name
code_area
sqlinnodb
instrument_name
- 要查看可用的 MySQL 内存工具,请查询 Performance Schema setup_instruments表。以下查询为所有代码区域返回数百个内存工具。
- mysql>
performance_schema
.setup_instruments
WHERE
NAME
LIKE
'%memory%';
您可以通过指定代码区域来缩小结果范围。例如,您可以 InnoDB
通过指定innodb
为代码区域来将结果限制为内存工具。
mysql>
SELECT
*
FROM performance_schema
.setup_instruments
WHERE
NAME
LIKE
'%memory/innodb%';
+-------------------------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+-------------------------------------------+---------+-------+
| memory/innodb/adaptive hash index | NO | NO |
| memory/innodb/buf_buf_pool | NO | NO |
| memory/innodb/dict_stats_bg_recalc_pool_t | NO | NO |
| memory/innodb/dict_stats_index_map_t | NO | NO |
| memory/innodb/dict_stats_n_diff_on_level | NO | NO |
| memory/innodb/other | NO | NO |
| memory/innodb/row_log_buf | NO | NO |
| memory/innodb/row_merge_sort | NO | NO |
| memory/innodb/std | NO | NO |
| memory/innodb/trx_sys_t::rw_trx_ids | NO | NO |
...
根据您的 MySQL 安装,代码区域可能包括performance_schema
, sql
, client
, innodb
, myisam
, csv
, memory
, blackhole
, archive
, partition
等。
- 要启用内存工具,请将
performance-schema-instrument
规则添加到您的 MySQL 配置文件。例如,要启用所有内存工具,请将此规则添加到您的配置文件并重新启动服务器:
performance-schema-instrument='memory/%=COUNTED'
笔记
在启动时启用内存工具可确保计算启动时发生的内存分配。
重新启动服务器后, ENABLED
Performance Schemasetup_instruments 表的列应报告YES
您启用的内存工具。对于内存仪器,表中的 TIMED
列将 setup_instruments被忽略,因为内存操作不是定时的。
mysql>
SELECT
*
FROM performance_schema
.setup_instruments
WHERE
NAME
LIKE
'%memory/innodb%';
+-------------------------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+-------------------------------------------+---------+-------+
| memory/innodb/adaptive hash index | NO | NO |
| memory/innodb/buf_buf_pool | NO | NO |
| memory/innodb/dict_stats_bg_recalc_pool_t | NO | NO |
| memory/innodb/dict_stats_index_map_t | NO | NO |
| memory/innodb/dict_stats_n_diff_on_level | NO | NO |
| memory/innodb/other | NO | NO |
| memory/innodb/row_log_buf | NO | NO |
| memory/innodb/row_merge_sort | NO | NO |
| memory/innodb/std | NO | NO |
| memory/innodb/trx_sys_t::rw_trx_ids | NO | NO |
...
- 查询内存仪器数据。在此示例中,在 Performance Schema memory_summary_global_by_event_name 表中查询内存仪器数据,该表通过
EVENT_NAME
.EVENT_NAME
是仪器的名称 。
以下查询返回 InnoDB
缓冲池的内存数据。有关列描述,请参阅 第 25.12.15.9 节,“内存汇总表”。
mysql>
SELECT
*
FROM performance_schema
.memory_summary_global_by_event_name
WHERE EVENT_NAME
LIKE
'memory/innodb/buf_buf_pool'\G
EVENT_NAME:
memory/innodb
/buf_buf_pool
COUNT_ALLOC:
1
COUNT_FREE:
0
SUM_NUMBER_OF_BYTES_ALLOC:
137428992
SUM_NUMBER_OF_BYTES_FREE:
0
LOW_COUNT_USED:
0
CURRENT_COUNT_USED:
1
HIGH_COUNT_USED:
1
LOW_NUMBER_OF_BYTES_USED:
0
CURRENT_NUMBER_OF_BYTES_USED:
137428992
HIGH_NUMBER_OF_BYTES_USED:
137428992
可以使用 sys模式 memory_global_by_current_bytes 表查询相同的基础数据,该表显示了全局服务器内的当前内存使用情况,按分配类型细分。
mysql>
SELECT
*
FROM sys
.memory_global_by_current_bytes
WHERE event_name
LIKE
'memory/innodb/buf_buf_pool'\G
*************************** 1. row ***************************
event_name: memory/innodb/buf_buf_pool
current_count: 1
current_alloc: 131.06 MiB
current_avg_alloc: 131.06 MiB
high_count: 1
high_alloc: 131.06 MiB
high_avg_alloc: 131.06 MiB
此模式查询按代码区域 sys聚合当前分配的内存 ( ):current_alloc
mysql>
SELECT
SUBSTRING_INDEX(event_name
,'/',2)
AS
code_area
, sys
.format_bytes
(SUM(current_alloc
))
AS current_alloc
FROM sys
.x$memory_global_by_current_bytes
GROUP
BY
SUBSTRING_INDEX(event_name
,'/',2)
ORDER
BY
SUM(current_alloc
)
DESC;
+---------------------------+---------------+
| code_area | current_alloc |
+---------------------------+---------------+
| memory/innodb | 843.24 MiB |
| memory/performance_schema | 81.29 MiB |
| memory/mysys | 8.20 MiB |
| memory/sql | 2.47 MiB |
| memory/memory | 174.01 KiB |
| memory/myisam | 46.53 KiB |
| memory/blackhole | 512 bytes |
| memory/federated | 512 bytes |
| memory/csv | 512 bytes |
| memory/vio | 496 bytes |
+---------------------------+---------------+
有关 sysschema 的更多信息,请参阅 第 26 章,MySQL sys Schema。
8.12.4.2 启用大页面支持
一些硬件/操作系统架构支持大于默认值(通常为 4KB)的内存页。这种支持的实际实现取决于底层硬件和操作系统。由于减少了转换后备缓冲区 (TLB) 未命中,执行大量内存访问的应用程序可能会通过使用大页面来提高性能。
在 MySQL 中,InnoDB 可以使用大页面来为其缓冲池和附加内存池分配内存。
MySQL 中大页面的标准使用尝试使用支持的最大大小,最多 4MB。在 Solaris 下, “超大页面”功能允许使用高达 256MB 的页面。此功能适用于最新的 SPARC 平台。可以使用 --super-large-pagesor --skip-super-large-pages 选项启用或禁用它。
MySQL 还支持 Linux 实现的大页面支持(在 Linux 中称为 HugeTLB)。
在 Linux 上可以使用大页面之前,必须启用内核来支持它们,并且需要配置 HugeTLB 内存池。作为参考,HugeTBL API 记录在 Documentation/vm/hugetlbpage.txt
您的 Linux 源文件中。
一些最近的系统(例如 Red Hat Enterprise Linux)的内核似乎默认启用了大页面功能。要检查这是否适用于您的内核,请使用以下命令并查找包含 “巨大”的输出行:
$> cat /proc/meminfo | grep
-i huge
HugePages_Total
: 0
HugePages_Free
: 0
HugePages_Rsvd
: 0
HugePages_Surp
: 0
Hugepagesize
: 4096 kB
nonempty 命令输出表明存在大页面支持,但零值表明没有配置页面以供使用。
如果您的内核需要重新配置以支持大页面,请查阅该hugetlbpage.txt
文件以获取说明。
假设您的 Linux 内核启用了大页面支持,请使用以下命令对其进行配置以供 MySQL 使用。通常,您将这些放在 rc
系统引导序列期间执行的文件或等效启动文件中,以便在每次系统启动时执行命令。这些命令应该在引导序列的早期执行,在 MySQL 服务器启动之前。请务必根据您的系统更改分配编号和组编号。
# Set the number of pages to be used.
# Each page is normally 2MB, so a value of 20 = 40MB.
# This command actually allocates memory, so this much
# memory must be available.
echo 20 > /proc/sys/vm/nr_hugepages
# Set the group number that is permitted to access this
# memory (102 in this case). The mysql user must be a
# member of this group.
echo 102 > /proc/sys/vm/hugetlb_shm_group
# Increase the amount of shmem permitted per segment
# (12G in this case).
echo 1560281088 > /proc/sys/kernel/shmmax
# Increase total amount of shared memory. The value
# is the number of pages. At 4KB/page, 4194304 = 16GB.
echo 4194304 > /proc/sys/kernel/shmall
对于 MySQL 的使用,您通常希望 的值 shmmax
接近 的值 shmall
。
要验证大页面配置,请 /proc/meminfo
如前所述再次检查。现在您应该看到一些非零值:
$> cat /proc/meminfo | grep
-i huge
HugePages_Total
: 20
HugePages_Free
: 20
HugePages_Rsvd
: 0
HugePages_Surp
: 0
Hugepagesize
: 4096 kB
使用的最后一步 hugetlb_shm_group
是给 mysql
用户一个“无限” 的内存锁限制值。这可以通过编辑/etc/security/limits.conf
或将以下命令添加到 mysqld_safe脚本来完成:
ulimit
-l unlimited
将ulimit命令添加到 mysqld_safe会导致 用户在切换到用户之前 root
将 memlock 限制设置为 。(这假设 mysqld_safe由 启动 。) unlimitedmysqlroot
MySQL 中的大页面支持默认是禁用的。要启用它,请使用该 --large-pages选项启动服务器。例如,您可以在服务器 my.cnf
文件中使用以下行:
[mysqld]
large-pages
使用此选项,InnoDB
自动为其缓冲池和附加内存池使用大页面。如果InnoDB
不能这样做,它会退回到使用传统内存并将警告写入错误日志:警告:使用传统内存池
要验证是否正在使用大页面,请 /proc/meminfo
再次检查:
$> cat /proc/meminfo | grep
-i huge
HugePages_Total
: 20
HugePages_Free
: 20
HugePages_Rsvd
: 2
HugePages_Surp
: 0
Hugepagesize
: 4096 kB
8.13 衡量绩效(基准测试)
8.13.1 测量表达式和函数的速度
要测量特定 MySQL 表达式或函数的速度,请使用mysql客户端程序调用该BENCHMARK()函数。它的语法是 . 返回值始终为零,但mysql 打印一行显示语句执行大约需要多长时间。例如: BENCHMARK(loop_count,expr)
mysql>
SELECT
BENCHMARK(1000000,1+1);
+------------------------+
| BENCHMARK(1000000,1+1) |
+------------------------+
| 0 |
+------------------------+
1 row in set (0.32 sec)
该结果是在 Pentium II 400MHz 系统上获得的。它表明 MySQL 可以在该系统上在 0.32 秒内执行 1,000,000 个简单的加法表达式。
内置的 MySQL 函数通常是高度优化的,但也可能有一些例外。 BENCHMARK()是找出某些功能是否对您的查询有问题的绝佳工具。
8.13.2 使用自己的基准
对您的应用程序和数据库进行基准测试以找出瓶颈所在。在修复一个瓶颈(或用“虚拟”模块替换它)之后,您可以继续识别下一个瓶颈。即使你的应用程序当前的整体性能是可以接受的,你至少应该为每个瓶颈制定一个计划,并决定如果有一天你真的需要额外的性能,如何解决它。
一个免费的基准套件是开源数据库基准,可在The Open Source Database Benchmark获得。
仅当系统负载非常重时才会出现问题是很常见的。我们有很多客户在生产(经过测试)系统并遇到负载问题时与我们联系。在大多数情况下,性能问题最终是由于基本数据库设计的问题(例如,表扫描在高负载下不好)或操作系统或库的问题。大多数情况下,如果系统尚未投入生产,这些问题会更容易解决。
为避免此类问题,请在可能的最坏负载下对整个应用程序进行基准测试:
- mysqlslap程序有助于模拟由多个客户端同时发出查询产生的高负载。请参阅第 4.5.8 节,“mysqlslap - 负载仿真客户端”。
- 您还可以尝试 SysBench 和 DBT2 等基准测试包,可在 sysbench in Launchpad和 Database Test Suite获得。
这些程序或软件包可能会使系统崩溃,因此请确保仅在您的开发系统上使用它们。
8.13.3 使用 performance_schema 测量性能
8.13.3 使用 performance_schema 测量性能
您可以查询 performance_schema
数据库中的表,以查看有关服务器性能特征及其运行的应用程序的实时信息。有关详细信息,请参阅 第 25 章,MySQL 性能模式。
- 无论您是测量安静系统上单个操作的速度,还是测量一组操作( “工作负载”)在一段时间内的工作方式。通过简单的测试,您通常会测试更改某个方面(配置设置、表上的索引集、查询中的 SQL 子句)如何影响性能。基准测试通常是长时间运行且复杂的性能测试,其结果可能决定高级别的选择,例如硬件和存储配置,或者多久升级到新的 MySQL 版本。
- 对于基准测试,有时您必须模拟繁重的数据库工作负载才能获得准确的图像。
- 性能可能会因许多不同的因素而异,因此几个百分点的差异可能不是决定性的胜利。当您在不同的环境中进行测试时,结果可能会发生相反的变化。
- 某些 MySQL 功能有助于或不有助于提高性能,具体取决于工作负载。为了完整起见,请始终在打开和关闭这些功能的情况下测试性能。对每个工作负载尝试的两个最重要的特性是 MySQL 查询缓存和 表 的自适应哈希索引。
InnoDB
本节从单个开发人员可以执行的简单直接测量技术发展到需要额外专业知识来执行和解释结果的更复杂的测量技术。
8.14 检查服务器线程(进程)信息
8.14.1 访问进程列表
以下讨论列举了进程信息的来源、查看进程信息所需的权限,并描述了进程列表条目的内容。
流程信息可从以下来源获得:
- SHOW PROCESSLIST 语句: 第13.7.5.29 节,“SHOW PROCESSLIST 语句”
- mysqladmin processlist命令: 第4.5.2 节,“mysqladmin - MySQL 服务器管理程序”
- 表
INFORMATION_SCHEMA
PROCESSLIST: 第 24.3.18 节,“INFORMATION_SCHEMA PROCESSLIST 表” - 名称前缀为的性能模式 threads表列
PROCESSLIST_
: 第 25.12.16.3 节,“线程表” sys
模式 processlist和 session视图: 第26.4.3.22 节,“processlist 和 x$processlist 视图”和 第 26.4.3.33 节,“会话和 x$session 视图”
该threads表与 SHOW PROCESSLIST、 INFORMATION_SCHEMA
PROCESSLIST和 mysqladmin 进程列表的比较如下:
- 访问threads表不需要互斥体,对服务器性能的影响最小。其他来源具有负面的性能后果,因为它们需要互斥体。
- 该threads表显示后台线程,其他来源没有。它还为每个线程提供其他来源没有的附加信息,例如线程是前台线程还是后台线程,以及与线程关联的服务器中的位置。这意味着该 threads表可用于监控其他来源无法监控的线程活动。
- 您可以启用或禁用 Performance Schema 线程监控,如 第 25.12.16.3 节,“线程表”中所述。
由于这些原因,使用其他线程信息源之一执行服务器监视的 DBA 可能希望使用threads表进行监视。
sys
模式 视图以更易于访问的格式processlist显示来自性能模式表的信息 。模式 视图像模式 视图一样显示有关用户会话的信息 threads,但过滤掉了后台进程。 sys
sessionsys
processlist
对于大多数进程信息源,如果您有 PROCESS权限,您可以查看所有线程,甚至是属于其他用户的线程。否则(没有PROCESS 特权),非匿名用户可以访问有关他们自己的线程的信息,但不能访问其他用户的线程,并且匿名用户无权访问线程信息。
Performance Schemathreads 表还提供线程信息,但表访问使用不同的权限模型。请参阅 第 25.12.16.3 节,“线程表”。
每个进程列表条目包含几条信息。以下列表使用SHOW PROCESSLIST 输出中的标签描述了它们。其他过程信息源使用类似的标签。
Id
是与线程关联的客户端的连接标识符。User
并Host
指明与线程关联的帐户。db
是线程的默认数据库,或者NULL
如果没有选择。Command
并State
指出线程在做什么。
大多数状态对应于非常快速的操作。如果一个线程在给定状态下停留数秒,则可能存在需要调查的问题。
以下部分列出了可能的 Command
值以及 State
按类别分组的值。其中一些值的含义是不言而喻的。对于其他人,提供了额外的描述。
笔记
检查进程列表信息的应用程序应该知道命令和状态可能会发生变化。
Time
指示线程处于其当前状态的时间。在某些情况下,线程的当前时间概念可能会改变:线程可以使用 . 对于副本 SQL 线程,该值是上次复制事件的时间戳与副本主机的实际时间之间的秒数。请参阅 第 16.2.3 节,“复制线程”。 SET TIMESTAMP = valueInfo
指示线程正在执行的语句,或者NULL
如果它不执行任何语句。对于SHOW PROCESSLIST,此值仅包含语句的前 100 个字符。要查看完整的语句,请使用 SHOW FULL PROCESSLIST(或查询不同的流程信息源)。
8.14.2 线程命令值
这是复制源上的一个线程,用于将二进制日志内容发送到副本。
线程正在执行更改用户操作。
线程正在关闭准备好的语句。
副本连接到其源。
副本正在连接到其源。
线程正在执行创建数据库操作。
该线程在服务器内部,而不是为客户端连接提供服务的线程。
线程正在生成调试信息。
该线程是一个延迟插入处理程序。
线程正在执行删除数据库操作。
线程正在执行准备好的语句。
线程正在从执行准备好的语句中获取结果。
该线程正在检索表列的信息。
该线程正在选择一个默认数据库。
该线程正在杀死另一个线程。
线程在执行准备好的语句的结果中检索长数据。
该线程正在处理服务器 ping 请求。
该线程正在准备一个准备好的语句。
该线程正在生成有关服务器线程的信息。
线程正在执行一条语句。
线程正在终止。
线程正在刷新表、日志或缓存,或重置状态变量或复制服务器信息。
该线程正在注册副本服务器。
该线程正在重置准备好的语句。
线程正在设置或重置客户端语句执行选项。
线程正在关闭服务器。
该线程正在等待客户端向其发送新语句。
该线程正在生成服务器状态信息。
没用过。
8.14.3 一般线程状态
下面的列表描述State
了与一般查询处理相关联的线程值,而不是更专业的活动,如复制。其中许多仅用于查找服务器中的错误。
当线程在创建表的函数结束时创建表(包括内部临时表)时,就会发生这种情况。即使由于某些错误而无法创建表,也会使用此状态。
服务器正在执行就地 ALTER TABLE.
该线程正在计算MyISAM
表键分布(例如, for ANALYZE TABLE)。
该线程正在检查服务器是否具有执行语句所需的权限。
线程正在执行表检查操作。
线程已经处理了一个命令并准备释放内存并重置某些状态变量。
该线程正在将更改的表数据刷新到磁盘并关闭已使用的表。这应该是一个快速的操作。如果没有,请确认您没有完整的磁盘并且该磁盘的使用量不是很大。
该线程正在将内部临时表从 MEMORY
表转换为磁盘表。
线程正在处理一条ALTER TABLE语句。此状态发生在创建具有新结构的表之后但在将行复制到其中之前。
对于处于这种状态的线程,可以使用 Performance Schema 来获取有关复制操作的进度。请参阅 第 25.12.5 节,“性能模式阶段事件表”。
如果语句具有不同ORDER BY
的 GROUP BY
条件,则将按组对行进行排序并复制到临时表中。
服务器正在复制到内存中的临时表。
服务器正在复制到磁盘上的临时表。临时结果集变得太大(参见 第 8.4.4 节,“MySQL 中的内部临时表使用”)。因此,线程将临时表从内存中更改为基于磁盘的格式以节省内存。
线程正在处理ALTER TABLE ... ENABLE KEYS
一个MyISAM
表。
线程正在处理 SELECT使用内部临时表解析的 a。
该线程正在创建一个表。这包括创建临时表。
该线程正在内存或磁盘上创建一个临时表。如果表是在内存中创建的,但后来转换为磁盘表,则该操作期间的状态为Copying to tmp table on disk
.
服务器已就地完成 ALTER TABLE并正在提交结果。
服务器正在执行多表删除的第一部分。它仅从第一个表中删除,并保存用于从其他(参考)表中删除的列和偏移量。
服务器正在执行多表删除的第二部分,并从其他表中删除匹配的行。
线程正在处理ALTER TABLE ... DISCARD TABLESPACE
orALTER TABLE ... IMPORT TABLESPACE
语句。
这发生在最后,但在清理 ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT, or UPDATE语句之前。
对于end
状态,可能会发生以下操作:
线程已开始执行语句。
线程正在执行 init_command
系统变量值中的语句。
线程已执行命令。在此状态期间完成的某些项目释放涉及查询缓存。此状态通常后跟cleaning up
.
服务器正准备执行自然语言全文搜索。
这发生在 ALTER TABLE, DELETE, INSERT, SELECT, or UPDATE语句的初始化之前。服务器在此状态下采取的动作包括刷新二进制日志、InnoDB
日志和一些查询缓存清理操作。
有人KILL 向线程发送了一条语句,它应该在下次检查 kill 标志时中止。在 MySQL 的每个主要循环中都会检查该标志,但在某些情况下,线程可能仍需要很短的时间才能结束。如果线程被其他线程锁定,则在其他线程释放其锁定后立即终止。
该线程正在向慢查询日志写入一条语句。
连接线程的初始状态,直到客户端成功通过身份验证。
服务器正在启用或禁用表索引。
该线程正在尝试打开一个表。这应该是一个非常快的过程,除非有东西阻止打开。例如,一个ALTER TABLEor LOCK TABLE语句可以阻止打开表,直到语句完成。检查您的table_open_cache值是否足够大也是值得的。
服务器正在为查询执行初始优化。
此状态发生在查询优化期间。
该线程正在删除不需要的中继日志文件。
此状态发生在处理查询之后但在 freeing items
状态之前。
服务器正在从客户端读取数据包。此状态Reading from net
在 MySQL 5.7.8 之前被调用。
查询的使用 SELECT DISTINCT方式使得 MySQL 无法在早期阶段优化掉不同的操作。因此,MySQL 需要一个额外的阶段来删除所有重复的行,然后再将结果发送到客户端。
SELECT 线程在处理语句 后正在删除内部临时表。如果没有创建临时表,则不使用此状态。
该线程正在重命名一个表。
线程正在处理一条ALTER TABLE语句,已创建新表,并正在重命名它以替换原始表。
线程获得了表的锁,但在获得锁后注意到底层表结构发生了变化。它释放了锁,关闭了表,并试图重新打开它。
修复代码使用排序来创建索引。
服务器正准备执行就地 ALTER TABLE.
该线程已完成对 MyISAM
表的多线程修复。
修复代码正在使用通过密钥缓存一一创建密钥。这比Repair by sorting
.
线程正在回滚事务。
对于MyISAM
修复或分析等表操作,线程正在将新表状态保存到.MYI
文件头。状态包括行数、 AUTO_INCREMENT
计数器和键分布等信息。
该线程正在执行第一阶段以在更新它们之前找到所有匹配的行。如果 UPDATE正在更改用于查找相关行的索引,则必须这样做。
Sending data
该线程正在读取和处理 SELECT语句的行,并将数据发送到客户端。由于在此状态期间发生的操作往往会执行大量磁盘访问(读取),因此它通常是给定查询生命周期内运行时间最长的状态。
服务器正在向客户端写入数据包。此状态Writing to net
在 MySQL 5.7.8 之前被调用。
线程正在开始一个ALTER TABLE操作。
线程正在执行排序以满足 a GROUP BY
。
该线程正在执行排序以满足ORDER BY
.
MyISAM
该线程正在对索引页进行排序,以便在表优化操作 期间进行更有效的访问。
对于SELECT语句,这类似于Creating sort index
,但对于非临时表。
语句执行开始的第一阶段。
服务器正在计算统计信息以制定查询执行计划。如果一个线程长时间处于这种状态,服务器可能正在磁盘绑定执行其他工作。
线程已调用mysql_lock_tables()
且线程状态自此未更新。这是一种非常普遍的状态,可能由于多种原因而发生。
例如,线程将要请求或正在等待表的内部或外部系统锁。在InnoDB执行 LOCK TABLES. 如果此状态是由外部锁请求引起的,并且您没有使用多个访问相同表的mysqld服务器,则可以使用该 选项MyISAM 禁用外部系统锁 。--skip-external-locking但是,默认情况下外部锁定是禁用的,因此该选项很可能没有效果。对于 SHOW PROFILE,此状态意味着线程正在请求锁(不等待它)。
线程正准备开始更新表。
该线程正在搜索要更新的行并正在更新它们。
服务器正在执行多表更新的第一部分。它只更新第一个表,并保存用于更新其他(参考)表的列和偏移量。
服务器正在执行多表更新的第二部分,并从其他表中更新匹配的行。
线程将要请求或正在等待调用请求的咨询锁 GET_LOCK()。对于 SHOW PROFILE,此状态意味着线程正在请求锁(不等待它)。
线程调用了一个 SLEEP()调用。
FLUSH TABLES WITH READ LOCK 正在等待提交锁。
FLUSH TABLES WITH READ LOCK 正在等待全局读锁或 read_only正在设置全局系统变量。
线程收到一个表的基础结构已更改的通知,它需要重新打开表以获取新结构。但是,要重新打开表,它必须等到所有其他线程都关闭了有问题的表。
FLUSH TABLES如果另一个线程已使用或在相关表上使用以下语句之一, 则会发生此通知 : 、 、 、 、 或 。 FLUSH TABLES
tbl_name
ALTER TABLERENAME TABLEREPAIR TABLEANALYZE TABLEOPTIMIZE TABLE
线程正在执行FLUSH TABLES并等待所有线程关闭其表,或者线程收到表的底层结构已更改的通知,它需要重新打开表以获取新结构。但是,要重新打开表,它必须等到所有其他线程都关闭了有问题的表。
FLUSH TABLES如果另一个线程已使用或在相关表上使用以下语句之一, 则会发生此通知 : 、 、 、 、 或 。 FLUSH TABLES
tbl_name
ALTER TABLERENAME TABLEREPAIR TABLEANALYZE TABLEOPTIMIZE TABLE
THR_LOCK
服务器正在等待从元数据锁定子系统 获取 锁或锁,其中lock_type
指示锁的类型。
此状态表示等待 a THR_LOCK
:
-
Waiting for table level lock
这些状态表示等待元数据锁定:
-
Waiting for event metadata lock
Waiting for global read lock
Waiting for schema metadata lock
Waiting for stored function metadata lock
Waiting for stored procedure metadata lock
Waiting for table metadata lock
Waiting for trigger metadata lock
有关表锁定指示器的信息,请参阅 第 8.11.1 节,“内部锁定方法”。有关元数据锁定的信息,请参阅第 8.11.4 节,“元数据锁定”。要查看哪些锁正在阻塞锁请求,请使用 第 25.12.12 节,“性能模式锁表”中描述的性能模式锁表。
线程正在等待条件变为真的一般状态。没有具体的状态信息可用。
服务器正在向网络写入数据包。从 MySQL 5.7.8 开始,这种状态被称为Sending to client
。
8.14.4 查询缓存线程状态
这些线程状态与查询缓存相关联(参见 第 8.10.3 节,“MySQL 查询缓存”)。
服务器正在检查用户是否有权访问缓存的查询结果。
服务器正在检查当前查询是否存在于查询缓存中。
由于基础表已更改,查询缓存条目被标记为无效。
服务器从查询缓存中获取查询结果并将其发送给客户端。
服务器将查询结果存储在查询缓存中。
当会话等待获取查询缓存锁定时会出现此状态。这可能发生在需要执行某些查询缓存操作的任何语句上,例如 使查询缓存无效的INSERTor 、查找缓存条目的 等等。 DELETESELECTRESET QUERY CACHE
8.14.5 复制源线程状态
以下列表显示了您可能在复制源线程State
列中 看到的最常见状态。Binlog Dump
如果您 Binlog Dump
在源上没有看到任何线程,这意味着复制没有运行;也就是说,当前没有连接副本。
该线程已完成读取二进制日志文件并正在打开下一个要发送到副本的文件。
该线程已从二进制日志中读取所有剩余的更新并将它们发送到副本。线程现在处于空闲状态,等待源上发生的新更新导致新事件出现在二进制日志中。
二进制日志由事件组成,其中事件通常是更新加上一些其他信息。该线程已从二进制日志中读取了一个事件,现在正在将其发送到副本。
线程停止时发生的非常短暂的状态。
8.14.6 复制副本 I/O 线程状态
以下列表显示了您在 State
列中看到的副本服务器 I/O 线程的最常见状态。此状态也出现在Slave_IO_State
显示的列中SHOW SLAVE STATUS,因此您可以使用该语句很好地了解正在发生的事情。
在与源的连接建立后非常短暂地出现的状态。
该线程正在尝试连接到源。
线程已读取事件并将其复制到中继日志,以便 SQL 线程可以处理它。
该线程正在尝试重新连接到源。
该线程正在尝试重新连接到源。再次建立连接时,状态变为 Waiting for master to send event
。
与源的连接建立后非常短暂地出现的状态。
在与源的连接建立后非常短暂地出现的状态。线程从请求的二进制日志文件名和位置开始向源发送对其二进制日志内容的请求。
如果启用,则在副本线程等待较旧的工作线程提交时发生的状态 slave_preserve_commit_order 。
该线程已连接到源并正在等待二进制日志事件到达。如果源处于空闲状态,这可能会持续很长时间。如果等待持续 slave_net_timeout几秒钟,则会发生超时。此时,线程认为连接已断开并尝试重新连接。
之前的初始状态Connecting to master
。
线程停止时短暂出现的状态。
您正在使用一个非零 relay_log_space_limit 值,并且中继日志已经增长到足够大,以至于它们的组合大小超过了这个值。I/O 线程一直在等待,直到 SQL 线程通过处理中继日志内容释放足够的空间,以便它可以删除一些中继日志文件。
如果二进制日志转储请求失败(由于断开连接),线程会在休眠时进入此状态,然后尝试定期重新连接。可以使用 CHANGE MASTER TO语句指定重试之间的间隔。
读取时发生错误(由于断开连接)。CHANGE MASTER TO在尝试重新连接之前 ,线程正在休眠语句设置的秒数 (默认为 60)。
8.14.7 复制副本 SQL 线程状态
以下列表显示了您可能在State
列中看到的副本服务器 SQL 线程的最常见状态:
该线程正在执行一条LOAD DATA语句,并将数据附加到一个临时文件中,该文件包含副本从中读取行的数据。
该线程正在执行一条LOAD DATA语句,并正在创建一个临时文件,其中包含副本从中读取行的数据。LOAD DATA仅当原始语句由运行低于 MySQL 5.0.3 的 MySQL 版本的源记录 时才会遇到此状态 。
该线程已从中继日志中读取了一个事件,以便可以处理该事件。
该线程已处理中继日志文件中的所有事件,现在正在等待 I/O 线程将新事件写入中继日志。
使用多线程副本(slave_parallel_workers大于 1),副本工作线程之一正在等待来自协调线程的事件。
线程停止时发生的非常短暂的状态。
当 Workers 正在处理的事件的总大小超过 slave_pending_jobs_size_max 系统变量的大小时,就会发生此等待操作。当大小低于此限制时,协调器恢复调度。仅当 slave_parallel_workers设置大于 0 时才会出现此状态。
之前的初始状态Reading event from the relay log
。
SQL 线程已读取一个事件,但正在等待副本延迟结束。这个延迟是用 MASTER_DELAY
选项设置的 CHANGE MASTER TO。
SQL 线程的Info
列也可以显示语句的文本。这表明线程已经从中继日志中读取了一个事件,从中提取了语句,并且可能正在执行它。
8.14.8 复制副本连接线程状态
这些线程状态发生在副本服务器上,但与连接线程相关联,而不是与 I/O 或 SQL 线程相关联。
线程正在处理一条CHANGE MASTER TO语句。
线程正在处理一条STOP SLAVE
语句。
这种状态发生在 之后Creating table from master dump
。
这种状态发生在 之后Opening master dump table
。
这种状态发生在 之后Reading master dump table data
。
8.14.9 NDB 集群线程状态
该线程正在处理二进制日志记录的事件。
该线程正在执行模式复制的工作。
这用于为 NDB 提供正确的模式操作二进制日志。
线程正在等待获取全局模式锁的权限。
服务器充当 NDB Cluster 中的 SQL 节点,并连接到集群管理节点。
Waiting for first event from ndbcluster
Waiting for ndbcluster binlog update to reach current position
Waiting for ndbcluster global schema lock
该线程正在等待另一个线程持有的全局模式锁被释放。
线程正在等待一个模式时期(即一个全局检查点)。
8.14.10 事件调度程序线程状态
这些状态发生在 Event Scheduler 线程、为执行调度事件而创建的线程或终止调度程序的线程上。
调度程序线程或正在执行事件的线程正在终止并且即将结束。
调度程序线程或执行事件的线程已被初始化。
调度程序有一个非空事件队列,但下一次激活是在将来。
线程已发出SET GLOBAL event_scheduler=OFF
并正在等待调度程序停止。
调度程序的事件队列是空的,它正在休眠。
要确定您的 MySQL 服务器在做什么,检查进程列表会很有帮助,该列表指示当前由服务器内执行的线程集执行的操作。例如:
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 1
User: event_scheduler
Host: localhost
db: NULL
Command: Daemon
Time: 2756681
State: Waiting on empty queue
Info: NULL
*************************** 2. row ***************************
Id: 20
User: me
Host: localhost:52943
db: test
Command: Query
Time: 0
State: starting
Info: SHOW PROCESSLIST
可以使用KILL 语句杀死线程。请参阅第 13.7.6.4 节,“KILL 语句”。