乐趣区

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

摘要: 以肯定的算法联合解析树中的各结点,计算出来一个整数值,用来惟一标识这一类 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;

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

退出移动版