探索BI系统搭建的必要性与AI技术的应用潜力
669
2022-05-29
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信息。两个视图的格式相同，均由下表中的字段组成：
字段名称
字段类型
解释
node_name
name
Unique SQL来源CN的名称
node_id
integer
Unique SQL来源CN的ID(等同于pgxc_node表中的node_id)
user_name
name
执行SQL语句的用户名称
user_id
oid
执行SQL语句的用户OID
unique_sql_id
bigint
归一化的Unique SQL ID
query
text
归一化的Unique SQL字符串
n_calls
bigint
调用次数
min_elapse_time
bigint
SQL在内核内的最小运行时间（单位：微秒）
max_elapse_time
bigint
SQL在内核内的最大运行时间（单位：微秒）
total_elapse_time
bigint
SQL在内核内的总运行时间（单位：微秒）
n_returned_rows
bigint
SELECT返回的结果集行数
n_tuples_fetched
bigint
随机扫描行数
n_tuples_returned
bigint
顺序扫描行数
n_tuples_inserted
bigint
插入行数
n_tuples_updated
bigint
更新行数
n_tuples_deleted
bigint
删除行数
n_blocks_fetched
bigint
buffer的块访问次数
n_blocks_hit
bigint
buffer的块命中次数
n_soft_parse
bigint
软解析次数, n_soft_parse + n_bard_parse可能大于n_calls, 因为子查询未计入n_calls
n_hard_parse
bigint
硬解析次数, n_soft_parse + n_bard_parse可能大于n_calls, 因为子查询未计入n_calls
db_time
bigint
有效的DB时间花费，多线程将累加（单位：微秒）
cpu_time
bigint
CPU时间（单位：微秒）
execution_time
bigint
执行器内执行时间（单位：微秒）
parse_time
bigint
SQL解析时间（单位：微秒）
plan_time
bigint
SQL生成计划时间（单位：微秒）
rewrite_time
bigint
SQL重写时间（单位：微秒）
pl_execution_time
bigint
plpgsql上的执行时间（单位：微秒）
pl_compilation_time
bigint
plpgsql上的编译时间（单位：微秒）
net_send_time
bigint
网络上的时间花费（单位：微秒）
data_io_time
bigint
IO上的时间花费(单位：微秒)
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;
EI企业智能 数据仓库服务 GaussDB(DWS) Gauss AP 应用性能调优
版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。
相关文章
推荐文章
最近发表
