乐趣区

关于sql:一文为你详解Unique-SQL原理和应用

jiezi

摘要： 以肯定的算法联合解析树中的各结点，计算出来一个整数值，用来惟一标识这一类 SQL，这个整数值被称为 Unique SQL ID，Unique SQL ID 雷同的 SQL 语句属于同一个“Unique SQL”。

1、什么是 Unique SQL

用户执行 SQL 语句时，每一个 SQL 语句文本都会进入解析器（Parser），生成“解析树”（parse tree）。遍历解析树中各个结点，疏忽其中的常数值，以肯定的算法联合树中的各结点，计算出来一个整数值，用来惟一标识这一类 SQL，这个整数值被称为 Unique SQL ID，Unique SQL ID 雷同的 SQL 语句属于同一个“Unique SQL”。

例如，用户先后输出如下两条 SQL 语句：

select from t1 where id = 1; select from t1 where id = 2;

这两条 SQL 语句除了过滤条件的常数值不同，其余中央都雷同，由此生成的解析树的拓扑构造完全相同，故 Unique SQL ID 也雷同。因而两条语句属于如下同一个 Unique SQL：

select * from t1 where id = ?;

GaussDB 内核会对所有下面模式的 SQL 语句汇总统计信息，通过视图出现给用户。通过这种形式，能够排除一些无关的常量值的烦扰，取得某一类 SQL 语句的统计数据，为性能剖析和问题定位提供数值根据。

留神，对于 Unique SQL ID 的计算，只会排除常数值，而不会排除其余的差别。例如，SQL 语句“select * from t2 where id = 1;”与下面的 SQL 不属于同一个 Unique SQL，不同用户，从不同的 CN 节点执行的雷同的 SQL 语句也不属于同一个 Unique SQL。

2、Unique SQL 如何统计

收到 SQL 申请后，GaussDB 内核首先算出其 Unique SQL ID。如果该 Unique SQL ID 已存在，则间接更新相干的统计信息。如果不存在，首先创立一个 Unique SQL，而后再更新统计信息，如下图所示：

Unique SQL 的统计信息包含执行次数，响应工夫，Cache/IO 数量，行流动和工夫散布等信息，能够通过如下两个视图查问：

  • gs_instr_unique_sql
  • pgxc_instr_unique_sql

前者显示以后 CN（Coordinator Node）节点（执行以后 SQL 命令的节点）上的 Unique SQL 信息，后者显示零碎中所有 CN 节点上的 Unique SQL 信息。两个视图的格局雷同，均由下表中的字段组成：

3、如何应用 Unique SQL

应用 Unique SQL 性能须要关上以下变量开关：

  • enable_resource_check（默认为 on）
  • track_counts（默认为 on，影响行流动和 Cache/IO 相干字段）

此外还须要将 instr_unique_sql_count 设为正整数。该变量默认为 0，且不能在 gsql 会话中批改，须要通过 SIGHUP 的形式设置，例如：

gs_guc reload -Z coordinator -D /path/to/coordinator1/ -c “instr_unique_sql_count=20” > /dev/null

instr_unique_sql_count 参数决定了零碎收集的 unique sql 的数量。当收集的 unique 数量达到这个数后，新的 sql 不再被收集。如果将该数值改大，原有的 unique sql 信息保留，同时开始收集新的 unique sql。如果将该数值改小，则会清空以后 CN 节点所有已收集的 unique sql 信息，而后开始收集新的 unique sql。

设置好上述变量后，Unique sql 统计视图能够像一般视图一样查问，例如：

postgres=# select node_name,query,n_calls from pgxc_instr_unique_sql;
node_name | query | n_calls ————–+————————————————————+——— coordinator2 | select node_name,query,n_calls from pgxc_instr_unique_sql; | 0 (1 row)

零碎函数 reset_instr_unique_sql 能够清理 unique sql 信息，该函数有 3 个参数，含意如下：

1. scope： 如果为 ”GLOBAL”，则革除所有 CN 节点上的数据；如果为 ”LOCAL”，只清空以后 CN 上的数据。

2. type： 如果为“ALL”，则革除所有数据；如果为 ”BY_USERID”，只革除指定用户的 unique SQL；如果为 ”BY_CNID”，只革除指定 CN 的 unique SQL。

3. value： 如果 type=“ALL”，该参数无意义；如果 type=”BY_USERID”，该参数为指定用户的 ID，如果 type=”BY_CNID”，该参数为指定 CN 的 ID。

例如：

postgres=# select reset_instr_unique_sql(‘global’,’all’,0);
reset_instr_unique_sql ———————— t
(1 row)

此外，如果数据库过程重启，也会导致之前收集的 unique SQL 信息被清空。

4、用 Unique SQL 辅助定位问题

unique sql 视图提供了丰盛的信息，用户能够依据须要选取对本人有帮忙的信息应用。本节针对客户在生产环境中遇到的理论状况，举例说明几种该视图的应用办法，可供性能优化参考。

4.1 查问异样的行流动导致的磁盘争用

异样的行流动可能引起磁盘争用，导致业务运行迟缓。通过查看扫描的行数、返回的函数、更改的行数等指标的稳定状况，能够发现异常的行流动，帮忙定位起因。

postgres=# select sum(n_returned_rows) n_returned_rows, sum(n_tuples_fetched) n_tuples_fetched,

sum(n_tuples_returned) n_tuples_returned, sum(n_tuples_inserted) n_tuples_inserted,
sum(n_tuples_updated) n_tuples_updated, sum(n_tuples_deleted) n_tuples_deleted from pgxc_instr_unique_sql;

n_returned_rows | n_tuples_fetched | n_tuples_returned | n_tuples_inserted | n_tuples_updated | n_tuples_deleted —————–+——————+——————-+——————-+——————+——————

         234 |                0 |                 0 |                 0 |                0 |                0 (1 row)

4.2 查问 Top SQL 对资源的占用状况

能够基于执行工夫、CPU 工夫、扫描行数、物理读 / 逻辑读等指标，对 unique SQL 视图中的 SQL 语句进行排序，找出占用资源最多的那些 SQL 语句，有针对性地其剖析对性能的影响和起因，帮忙查找和定位问题。例如,

  • 按 SQL 执行工夫程序或倒序排序：

SELECT user_name, unique_sql_id, query, total_elapse_time FROM pgxc_instr_unique_sql ORDER BY total_elapse_time ASC 或 DESC;

  • 按 SQL 执行占用 CPU 工夫进行程序或倒序排序：

SELECT user_name, unique_sql_id, query, cpu_time FROM pgxc_instr_unique_sql ORDER BY cpu_time ASC 或 DESC;

  • 按 SQL 程序扫描行数程序或倒序排序：

SELECT user_name, unique_sql_id, query, n_tuples_returned FROM pgxc_instr_unique_sql ORDER BY n_tuples_returned ASC 或 DESC;

  • 按 SQL 总扫描行进行程序或倒序排序：

SELECT user_name, unique_sql_id, query, n_tuples_fetched + n_tuples_returned FROM pgxc_instr_unique_sql ORDER BY n_tuples_fetched + n_tuples_returned ASC 或 DESC;

  • 按 SQL 执行执行器工夫进行程序或倒序排序：

SELECT user_name, unique_sql_id, query, execution_time FROM pgxc_instr_unique_sql ORDER BY execution_time ASC 或 DESC;

  • 按 SQL 执行物理读次数进行程序或倒序排序：

SELECT user_name, unique_sql_id, query, n_blocks_fetched FROM pgxc_instr_unique_sql ORDER BY n_blocks_fetched ASC 或 DESC;

  • 按 SQL 执行逻辑读次数进行程序或倒序排序：

SELECT user_name, unique_sql_id, query, n_blocks_hit FROM pgxc_instr_unique_sql ORDER BY n_blocks_hit ASC 或 DESC;

4.3 查问逻辑读 / 物理读数量

逻辑读 / 物理读过多可能导致 SQL 语句占用较多的 CPU 工夫。通过查问 unique SQL 视图能够失去 sql 语句逻辑 / 物理读数据块的数量，辅助判断响应过慢的起因：

  • 查问物理读块数量：

SELECT n_blocks_fetched FROM pgxc_instr_unique_sql;

  • 查问逻辑读块数量：

SELECT n_blocks_hit FROM pgxc_instr_unique_sql;

4.4 诊断内存配额有余导致性能低下

如果数据库缓冲区设置得太小，会导致每个 SQL 语句执行的后果不能被缓存，以后 SQL 执行结束如果有其余 SQL 执行就会把内存中上一个或上几个 SQL 缓存的执行后果挤出去，下一轮如果以后这个 SQL 再次执行时候又须要从磁盘进行物理 IO 读取数据，而不能间接从缓存中获取数据，进而导致 SQL 执行性能较差。

缓冲区配额是否足够大，能够通过命中率来判断。缓冲区命中率 =n_blocks_hit/n_blocks_fetched，能够通过查问 unique SQL 来诊断是否存在内存配额有余的问题：

SELECT (n_blocks_hit/ n_blocks_fetched) AS hit_ratio from pgxc_instr_unique_sql;

点击关注，第一工夫理解华为云陈腐技术~

退出移动版