详解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;
以上就是详解unique sql原理和应用的详细内容,更多关于unique sql原理和应用的资料请关注其它相关文章!