Unique SQL特性原理与应用

举报
Zhang Jingyao 发表于 2020/09/12 16:53:56 2020/09/12
【摘要】 1 什么是Unique SQL用户执行SQL语句时,每一个SQL语句文本都会进入解析器(Parser),生成“解析树”(parse tree)。遍历解析树中各个结点,忽略其中的常数值,以一定的算法结合树中的各结点,计算出来一个整数值,用来唯一标识这一类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,然后再更新统计信息,如下图所示:

图1.PNG


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来源CNID(等同于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;



【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。