我终于搞清楚了！ MariaDB性能提升

【.com原创稿件】Query Profiling，即查问剖析技术，是 MySQL 数据库提供的一种诊断 SQL 性能的方法，同时也被视为剖析数据库全体性能的有效技术。

图片来自 Pexels

用户可以在开启 Profiling 的状况下，检查会话中 SQL 口头时期消耗散布，系统时期，CPU 用户时期，以及环节中触及到的关键函数在源代码文件中的定位等。

由于单个大中型运行程序可以在单位时期内成功多个查问，因此 Query Profiling 是数据库提升调整的关键组成局部，它既可作为数据库性能提升的踊跃被动措施，亦可用于诊断数据库性能能否存在疑问。

在实践上班场景中，假设不驳回牢靠的查问剖析技术，相关技术人员往往很难定位数据库中性能瓶颈及性能不佳疑问的根源所在。

作为 MySQL 的一个分支，MariaDB Server 自带的内置工具中为咱们提供了 Query Profiling 相关的查问概要剖析技术。

咱们以 Slow Query Log(慢速查问日志)和 Performance Schema(性能战略模型)这两类 MariaDB Server 内置工具为例，深化探求查问剖析技术的价值。

首先让咱们来回忆一下 MariaDB 和 MySQL 这两种产品间的亲属相关。

早在 2010 甲骨文发表收买 Sun 公司的那天，MySQL 之父 Michael“Monty”Widenius就派生了 MySQL，进而推出 MariaDB，从此便吸引了一少量 MySQL 开发人员为之效能。

如今 MariaDB 曾经成为了 MySQL 开展最快的一个分支，相较于 MySQL 自身，具备更丰盛的性能及更优越的性能。

MariaDB 并非孤立的一个分支，它是基于相应的 MySQL 版本而存在的。例如，MariaDB 5.1.53 是在 MySQL 5.1.53 基础上，修复了之前的 Bug，增加了存储引擎，新性能等，性能方面也做了相应改良。

MariaDB 和 MySQL 都有 Slow Query Log(慢速查问日志)这一性能。该日志中记载了一些被以为口头速度十分缓慢且或者存在疑问的查问语句。

这里的“慢速”查问定义为运转时期比 [long_query_time] 全局系统变量值(默以为 10 秒)长的查问语句。

值得一提的是在文件记载中准许经常使用“微秒”，而在表记载中却不行，因此这里的时期单位为“秒”。

经过全局系统变量性能慢查问日志

除了上方提到的 [long_query_time] 全局系统变量外，还有一些其他变量用来确定 Slow Query Log(慢查问日志)的行为形态。

在自动状况下，Slow Query Log 是禁用的，若要启用，则须要将 [slow_query_log] 系统变量值设置为 1。

此外“log_output”主机系统变量选择了输入是以什么方式被写入的，这个变量值也可以设置为禁用。在自动状况下，日志准许被写入文件，也可以写入表。

[log_output] 主机系统变量的有效取值为 [TABLE”，“FILE”或“NONE]。

该文件的自动称号为 [host_name-slow.log]，也可以经常使用 [–slow_query_log_file = file_name] 选项启动设置，这里经常使用的表是 MySQL 系统数据库中的 [slow_log] 表。

倡导这些变量最好在“my.cnf”或“mariadb.cnf”性能文件中启动设置，这类文件通常存储在 Linux 的“/ etc / mysql /”目录。

假设是 Windows 系统，那么就存储在 Windows 系统目录(通常为 C：\ Windows\System)中。

在性能文件中做如下设置：

以上设置在服务重视启后失效。

已写入文件的慢查问日志可以经过任何文本编辑器关上启动检查，上方是一则慢查问日志的示例内容：

经过文本编辑器来检查慢查问日志看似十分繁难，但随着日志内容(数据量)的增长，很或者存在内容失落的状况。

即显示不完整，这是由于文本编辑器自身无法承载越来越大的日志容量，而形成日志中局部内容解析缺失的危险。

为了防止这类状况的出现，MariaDB 为咱们提供了 mysqldumpslow 工具，该工具可以经过汇总信息来简化环节，从而更牢靠且有效地展现日志内容。

“mysqldumpslow”的可口头文件与 MariaDB 是捆绑在一同的，所以只有经过命令行将须要显示的日志门路传递给它即可。

从上方的 Demo 中可以得知，经过“mysqldumpslow”出现的日志内容可读性更强，并且还支持分组显示。

“mysqldumpslow”命令可以经过指定不同的参数来定制化输入格局，如下示例中将显示按平均查问时期排序的前 5 个查问：

假设你对上述 log 日志中显示的内容不相熟，可以联合 slow_log 表来协助了解。

如下是日志中每个字段对应的概略形容：

下图所示是针对 slow_log 表的 SELECT ALL 示例结果：

还可以经过 slow_log 表来模拟 Linux 的“ tail -100 log-slow.log”命令，列出最新查问记载(最后 100 个查问)，如下图所示：

为了繁难日后频繁调用，咱们也可以专门创立一个存储环节(如SHOW_LATEST_SLOW_QUERIES)，须要显示的查问个数可以经过输入参数传递给这个存储环节。

这样一来当咱们须要列出指定数量的查问记载时，就不须要每次都重复键入相反的 SELECT 语句了。

为了更有效地在消费环境中失掉咱们想要的慢查问日志信息，通常状况下，咱们须要做一些设置，例如规则哪些查问必需被写入 Slow Query Log(慢查问日志)。

正如上文中提到，在启用日志记载后，依据 log_output 变量值的设定，运转时期比 [long_query_time] 全局系统变量值长的那些查问将记载在 Slow Query Log(慢查问日志)或 slow_log 表中。

除了指定 [long_query_time] 时期外，咱们还可以经过 select 语句依据不同的需求指定相应的可变时期。

这个操作须要联合 sleep() 函数经常使用，该函数(依据传入的 duration 参数值 N)会暂停查问 N 秒，而后前往 0， 假设 sleep() 函数被终止，则前往 1。

如下所示，假定尚未指定 [long_query_time] 全局系统变量的值，那么自动值为 10 秒。

因此，[SELECT SLEEP(11);] 这条 select 语句会被记载到慢日志中：

咱们可以经过另一种主机性能工具 Performance Schema 来监视主机性能。

Performance Schema 是 MariaDB 5.5 中被引入的，以存储引擎的方式成功;因此，在 MariaDB 的存储引擎列表中可以找到 Performance Schema。

图中的“Performance Schema”的性能自动状况下是禁用的，咱们可以经过如下设置逐个开启：

①在 my.cnf 或 my.ini 文件的 [mysqld] 局部中增加以下行：

performance_schema=

须要留意的是，“performance schema”无法在运转时被激活，它必需在主机启动时经过性能文件启动设置。

Performance Schema 存储引擎蕴含一个名为 performance_schema 的数据库，该数据库又由许多表组成，可以经常使用惯例 SQL 语句查问这些表以失掉各种性能信息。

②消费者数据设置

为了搜集数据，咱们须要对搜集哪些消费者触发的数据启动设置，这些设置可以在主机启动时或在运转时启动。

经过以下语句在运转时对所需数据启动设置并检测：

经过 WHERE NAME 启用/禁用对应的查问语句，经过将 ENABLED 设置为“ NO”来禁用检测。

以下将启用性能文件中一切阶段的一切检测：

经过降级 setup_instruments 表，确保启用了语句和阶段检测：

performance_schema.setup_instrumentsENABLED=,TIMED=;performance_schema.setup_instrumentsENABLED=,TIMED=;

启用 events_statements_ * 和 events_stages_ * 经常使用者：

performance_schema.setup_consumersENABLED=;performance_schema.setup_consumersENABLED=;

在增加了感兴味的范围后，有两种方法可以启动监控：

上方咱们以检查原始摘要数据为例：

运转要剖析的语句：

经过查问“events_statements_history_long”这张表，来标识语句的 EVENT_ID。此步骤相似于运转 SHOW PROFILES 来标识 Query_ID。

以下查问将发生相似于 SHOW PROFILES 的输入：

查问“events_stages_history_long”这张表，来检索语句的阶段事情。阶段经常使用事情嵌套链接到语句。

每个阶段事情记载都有一个 NESTING_EVENT_ID 列，其中蕴含父语句的 EVENT_ID。

如今咱们曾经知道 Slow Query Log(慢查问日志)专门用于记载那些运转时期过久且被以为存在疑问的查问语句，即运转时期超越 long_query_time 全局系统变量值的查问语句。

而 Performance Schema 是一个存储引擎，可用于在摘要视图中检查原始数据以及随时期推移的环节中触及到的性能形态。

这两种工具都有其自身的短处。例如，Slow Query Log(慢查问日志)易于经常使用，且可以经过任何文本编辑器启动检查。

Performance Schema 准许咱们经常使用惯例的 SQL 语句对其一系列表启动查问，以失掉各类性能信息。

但是，此二者都无法防止会发生少量的数据信息，从而造成冗余且减轻各自数据处置的累赘。

庆幸的是，Monyog 监控工具可以有效地为咱们缓解这个疑问，不只如此，作为专业的监控工具，它还能给咱们带来渺小的价值。

Monyog 是一款低劣的 MySQL 监控工具，可以实时监测 MySQL 主机，检查 MySQL 主机的运转形态，支持查问剖析性能，还可以协助用户把握主机的运转形态，检查在恣意时期点绘制的具备具体查问信息的图表。

Monyog 不只是实时监视工具，还具备 RDS OS 和基于文件的日志监视性能，包括在单个视图中的惯例查问，慢速查问和失误日志，还能经过 CloudWatch API 检查 RDS OS 目的，例如 CPU 应用率，RAM 应用率等。

由于在 MariaDB 中，自动状况下禁用慢查问日志。必需将 slow_query_log 全局系统变量设置为 1 来启用它。

此外还有一些其他系统变量的运行设置，如：

在 Monyog 中的 Slow Query Log(慢查问日志)设置，可以间接经过“Server Settings dialog”对话框的“ADVANCED ”选项卡性能上述一切设置。

步骤如下：

MySQL 查问日志项的 ADVANCED 选项卡蕴含惯例查问，慢速查问和失误日志的设置。

该 Server Settings dialog (主机设置对话框)可以让咱们把 Slow Query Log(慢查问日志)的设置运行到主机，或与主机领有相反标签的其他主机。

最后单击“Save”按钮封锁对话框，并保留 Slow Query Log(慢查问日志)设置。

①Dashboard Metrics

DBA 经过 Dashboard 显示的一组图表，就可以轻松了解一切 MySQL 主机的安保性，可用性以及性能状况。

Monyog 自带自动的 Dashboard“Performance metrics”性能目的，DBA 也可以为一个或多个主机指定数据库和操作系统目的，创立一组自己的专属图表。

例如查问性能目的中蕴含“Queries Executed”，“ Statements”和“Query Cache Efficiency”。

Dashboard 上显示的一切图表均可以 PDF/JPG/PNG 格局导出：

②检查 MySQL 日志具体信息

Monyog Monitors 页面显示主机参数和目的的具体显示。

单击“ MONITOR GROUP”下的“MySQL Logs”项，将会显示被监控下的主机对应的“惯例查问”，“慢速查问”(白色框标注)和失误日志的具体信息。

Slow Query 慢查问信息包括：

③趋向值图

与原始数据相比，应用图表显示少量数据以及数据间不同局部的相关性，愈加繁复明了，易于了解，可读性也更强。

趋向值图就是这样一类图表，用于显示一段时期内数据的变化趋向。由于数据的动摇，单点测量或者会不准确，发生误差。

因此，随着时期推移来出现数据的趋向走向，可以使咱们更有效地失掉实践性能，有针对性地基于已树立的目的监控实践性能状况。

下图是某主主机趋向图示例：

上图中“SERVERS”图例列出了 SQL 日志中的一切主机。每个主机图例都有各自的色彩，以便在图表中能轻松识别。

由于图中只显示了主主机趋向数据，其他对应主机的趋向值均未出如今图中，所以呈灰色。经过单击主机图例，可以随意切换须要显示的主机数据趋向。

④显示特定时期范围内的趋向值

在上方的趋向图中，仅仅显示了时期段内某主机上一切的趋向数据。

在 Monyog Professional，Enterprise 和 Ultimate 版本中，咱们还可以经过 TIMEFRAME 下拉列表中的“History”选项来指定特定时期的范围。

下图所示基于特定时期范围内的各主机慢查问数量趋向值：

⑤查问剖析器

在“查问剖析器”选项卡中，选用所需的 MySQL 主机以及要剖析的日志类型(包括慢查问日志)，单击剖析按钮开局剖析。

几秒钟后，将显示如下剖析结果，页面上半局部蕴含基于总时期的“抢手查问”，而下半局部显示了经常使用结果分页的一切查问：

基于总时期的“抢手查问”局部显示排名靠前的查问，以便最慢的查问可以在顶部显示。

包括：

每条语句在查问数据最上方以条形图的方式显示，因此每个查问都对应惟一的色彩。

每个查问的总口头时期依照从左到右的方式显示，最慢的显示在最左边。条形图有助于极速评价每个慢查问语句间的对比。

在上图中，咱们可以看到最慢的查问比一切其他慢查问时期的总和慢了好几个数量级。

单击某一行将显示对应慢查问的具体信息，例如查问初次和最后一次性口头的时期点，查问所破费的最大

⑥Query 查问面板中的过滤设置

“查问”局部为咱们提供了更完整的已剖析查问列表，除了经过火页导航遍历一切查问外，还可以自定义过滤条件，从而将显示列表的内容增加到咱们感兴味的范围。

过滤条件共有以下四种选项：

例如，将结果限度为婚配正则表白式“ sakila *”语句的过滤器：

经过单击题目，按恣意列启动排序，箭头显示排序顺序(即升序，降序)：

经过单击题目，按恣意列启动排序，箭头显示排序顺序(即升序，降序)：

⑦以 CSV 方式导出

单击 Query 面板上的“Export as CSV”可以将查问数据保留到“.csv”文件中：

保留后的 CSV 文件可以经过 EXCEL 关上预览：

查问剖析是用于剖析数据库全体性能的有效技术，文中该技术驳回了 MariaDB 主机内置工具：Slow Query Log 慢查问日志和 Performance Schema 性能形式。

慢查问日志经过设置 long_query_time 全局系统变量值，来跟踪记载运转超时且存在疑问的查问语句。

性能形式则是一个存储引擎，其中 Performance_schema 数据库，又由多个表组成，咱们可以经常使用惯例 SQL 语句查问这些表以失掉更宽泛的性能信息。

但是以上这两种工具都会发生少量的数据，惹起繁琐的上班，Monyog 的引入为咱们很好地处置此类疑问，应用 Monyog 来监视 MariaDB 慢查问日志和性能形式是最有效的方法之一。

作者：罗小罗

简介：英国 TOP10 计算机专业，计算机迷信与技术硕士，先后到任于汇丰，JPMorgan，HP，交行，阿里等国际外出名企业。触及名目畛域关键有：互联网金融，电商，教育，医疗等。现任到任于某环球 500 强公司，担任测试开发团队担任人，率领团队构建并继续提升智能化测试框架，研发智能化测试辅佐类工具;长于畛域：单元/接口/性能/安保/智能化测试/CD/CI/DevOps;团体继续钻研畛域：智能化测试模型/数据剖析/算法/机器学习等。

编辑：陶家龙