详解Unique SQL原理和应用
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li>1、什么是unique sql</li><li>
2、unique sql如何统计</li><li>
3、如何使用unique sql</li><li>
4、用unique sql辅助定位问题<ul class="second_class_ul"><li>
4.1查询异常的行活动导致的磁盘争用</li><li>
4.2查询top sql对资源的占用情况</li><li>
4.3查询逻辑读/物理读数量</li><li>
4.4诊断内存配额不足导致性能低下</li></ul></li></ul></div><div id="navcategory">
<h5 class="catalogue">
目录</h5>
<ul class="first_class_ul">
<li>
1、什么是unique sql</li>
<li>
2、unique sql如何统计</li>
<li>
3、如何使用unique sql</li>
<li>
4、用unique sql辅助定位问题
<ul class="second_class_ul">
<li>
4.1查询异常的行活动导致的磁盘争用</li>
<li>
4.2查询top sql对资源的占用情况</li>
<li>
4.3查询逻辑读/物理读数量</li>
<li>
4.4诊断内存配额不足导致性能低下</li>
</ul>
</li>
</ul>
</div>
<p class="maodian">
</p>
<p class="maodian"></p><h2>
1、什么是unique sql</h2>
<p>
用户执行sql语句时，每一个sql语句文本都会进入解析器（parser），生成“解析树”（parse tree）。遍历解析树中各个结点，忽略其中的常数值，以一定的算法结合树中的各结点，计算出来一个整数值，用来唯一标识这一类sql，这个整数值被称为unique sql id，unique sql id相同的sql语句属于同一个“unique sql”。</p>
<p>
例如，用户先后输入如下两条sql语句：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_152877">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">t1 </code><code class="sql keyword">where</code> <code class="sql plain">id = 1;</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">t1 </code><code class="sql keyword">where</code> <code class="sql plain">id = 2;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
这两条sql语句除了过滤条件的常数值不同，其他地方都相同，由此生成的解析树的拓扑结构完全相同，故unique sql id也相同。因此两条语句属于如下同一个unique sql：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_337323">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">t1 </code><code class="sql keyword">where</code> <code class="sql plain">id = ?;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
gaussdb内核会对所有上面形式的sql语句汇总统计信息，通过视图呈现给用户。通过这种方式，可以排除一些无关的常量值的干扰，获得某一类sql语句的统计数据，为性能分析和问题定位提供数值依据。</p>
<p>
注意，对于unique sql id的计算，只会排除常数值，而不会排除其他的差异。例如，sql语句“select * from t2 where id = 1;” 与上面的sql不属于同一个unique sql，不同用户，从不同的cn节点执行的相同的sql语句也不属于同一个unique sql。</p>
<p class="maodian">
</p>
<p class="maodian"></p><h2>
2、unique sql如何统计</h2>
<p>
收到sql请求后，gaussdb内核首先算出其unique sql id。如果该unique sql id已存在，则直接更新相关的统计信息。如果不存在，首先创建一个unique sql，然后再更新统计信息，如下图所示：</p>
<p>
<img title="详解Unique SQL原理和应用" alt="详解Unique SQL原理和应用" src="https://zhuji.jb51.net/uploads/img/202305/ee38b6db58928a6d947e129a75d93c64.jpg"></p>
<p>
unique sql的统计信息包括执行次数，响应时间，cache/io数量，行活动和时间分布等信息，可以通过如下两个视图查询：</p>
<ul>
<li>
gs_instr_unique_sql</li>
<li>
pgxc_instr_unique_sql</li>
</ul>
<p>
前者显示当前cn（coordinator node）节点（执行当前sql命令的节点）上的unique sql信息，后者显示系统中所有cn节点上的unique sql信息。两个视图的格式相同，均由下表中的字段组成：</p>
<p>
<img title="详解Unique SQL原理和应用" alt="详解Unique SQL原理和应用" src="https://zhuji.jb51.net/uploads/img/202305/3829699dee7c3b73376a1d88dbdbfb43.jpg"></p>
<p class="maodian">
</p>
<p class="maodian"></p><h2>
3、如何使用unique sql</h2>
<p>
使用unique sql功能需要打开以下变量开关：</p>
<ul>
<li>
enable_resource_check（默认为on）</li>
<li>
track_counts（默认为on，影响行活动和cache/io相关字段）</li>
</ul>
<p>
此外还需要将instr_unique_sql_count设为正整数。该变量默认为0，且不能在gsql会话中修改，需要通过sighup的方式设置，例如：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_450842">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">gs_guc reload -z coordinator -d /path/</code><code class="sql keyword">to</code><code class="sql plain">/coordinator1/ -c </code><code class="sql string">"instr_unique_sql_count=20"</code> <code class="sql plain">> /dev/</code><code class="sql color1">null</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
instr_unique_sql_count参数决定了系统收集的unique sql的数量。当收集的unique数量达到这个数后，新的sql不再被收集。如果将该数值改大，原有的unique sql信息保留，同时开始收集新的unique sql。如果将该数值改小，则会清空当前cn节点所有已收集的unique sql信息，然后开始收集新的unique sql。</p>
<p>
设置好上述变量后，unique sql统计视图可以像普通视图一样查询，例如：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_803430">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">postgres=# </code><code class="sql keyword">select</code> <code class="sql plain">node_name,query,n_calls </code><code class="sql keyword">from</code> <code class="sql plain">pgxc_instr_unique_sql;</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql spaces"> </code><code class="sql plain">node_name | query | n_calls</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql comments">--------------+------------------------------------------------------------+---------</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces"> </code><code class="sql plain">coordinator2 | </code><code class="sql keyword">select</code> <code class="sql plain">node_name,query,n_calls </code><code class="sql keyword">from</code> <code class="sql plain">pgxc_instr_unique_sql; | 0</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql plain">(1 row)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
系统函数reset_instr_unique_sql可以清理unique sql信息，该函数有3个参数，含义如下：</p>
<p>
1. scope：如果为"global"，则清除所有cn节点上的数据；如果为"local"，只清空当前cn上的数据。</p>
<p>
2. type：如果为“all”，则清除所有数据；如果为"by_userid"，只清除指定用户的unique sql；如果为"by_cnid"，只清除指定cn的unique sql。</p>
<p>
3. value：如果type=“all”，该参数无意义；如果type="by_userid"，该参数为指定用户的id，如果type="by_cnid"，该参数为指定cn的id。</p>
<p>
例如：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_306783">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">postgres=# </code><code class="sql keyword">select</code> <code class="sql plain">reset_instr_unique_sql(</code><code class="sql string">'global'</code><code class="sql plain">,</code><code class="sql string">'all'</code><code class="sql plain">,0);</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql spaces"> </code><code class="sql plain">reset_instr_unique_sql</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql comments">------------------------</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces"> </code><code class="sql plain">t</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql plain">(1 row)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
此外，如果数据库进程重启，也会导致之前收集的unique sql信息被清空。</p>
<p class="maodian">
</p>
<p class="maodian"></p><h2>
4、用unique sql辅助定位问题</h2>
<p>
unique sql视图提供了丰富的信息，用户可以根据需要选取对自己有帮助的信息使用。本节针对客户在生产环境中遇到的实际情况，举例说明几种该视图的使用方法，可供性能优化参考。</p>
<p class="maodian">
</p>
<p class="maodian"></p><h3>
4.1查询异常的行活动导致的磁盘争用</h3>
<p>
异常的行活动可能引起磁盘争用，导致业务运行缓慢。通过查看扫描的行数、返回的函数、更改的行数等指标的波动情况，可以发现异常的行活动，帮助定位原因。</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_223544">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">postgres=# </code><code class="sql keyword">select</code> <code class="sql color2">sum</code><code class="sql plain">(n_returned_rows) n_returned_rows, </code><code class="sql color2">sum</code><code class="sql plain">(n_tuples_fetched) n_tuples_fetched,</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql spaces"> </code><code class="sql color2">sum</code><code class="sql plain">(n_tuples_returned) n_tuples_returned, </code><code class="sql color2">sum</code><code class="sql plain">(n_tuples_inserted) n_tuples_inserted,</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql spaces"> </code><code class="sql color2">sum</code><code class="sql plain">(n_tuples_updated) n_tuples_updated, </code><code class="sql color2">sum</code><code class="sql plain">(n_tuples_deleted) n_tuples_deleted </code><code class="sql keyword">from</code> <code class="sql plain">pgxc_instr_unique_sql;</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces"> </code><code class="sql plain">n_returned_rows | n_tuples_fetched | n_tuples_returned | n_tuples_inserted | n_tuples_updated | n_tuples_deleted</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql comments">-----------------+------------------+-------------------+-------------------+------------------+------------------</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql spaces"> </code><code class="sql plain">234 | 0 | 0 | 0 | 0 | 0</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql plain">(1 row)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p class="maodian">
</p>
<p class="maodian"></p><h3>
4.2查询top sql对资源的占用情况</h3>
<p>
可以基于执行时间、cpu时间、扫描行数、物理读/逻辑读等指标，对unique sql视图中的sql语句进行排序，找出占用资源最多的那些sql语句，有针对性地其分析对性能的影响和原因，帮助查找和定位问题。例如,</p>
<p>
按sql执行时间顺序或倒序排序：</p>
<p>
select user_name, unique_sql_id, query, total_elapse_time from pgxc_instr_unique_sql order by total_elapse_time asc 或 desc;</p>
<p>
按sql执行占用cpu时间进行顺序或倒序排序：</p>
<p>
select user_name, unique_sql_id, query, cpu_time from pgxc_instr_unique_sql order by cpu_time asc 或 desc;</p>
<p>
按sql顺序扫描行数顺序或倒序排序：</p>
<p>
select user_name, unique_sql_id, query, n_tuples_returned from pgxc_instr_unique_sql order by n_tuples_returned asc 或 desc;</p>
<p>
按sql总扫描行进行顺序或倒序排序：</p>
<p>
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;</p>
<p>
按sql执行执行器时间进行顺序或倒序排序：</p>
<p>
select user_name, unique_sql_id, query, execution_time from pgxc_instr_unique_sql order by execution_time asc 或 desc;</p>
<p>
按sql执行物理读次数进行顺序或倒序排序：</p>
<p>
select user_name, unique_sql_id, query, n_blocks_fetched from pgxc_instr_unique_sql order by n_blocks_fetched asc 或 desc;</p>
<p>
按sql执行逻辑读次数进行顺序或倒序排序：</p>
<p>
select user_name, unique_sql_id, query, n_blocks_hit from pgxc_instr_unique_sql order by n_blocks_hit asc 或 desc;</p>
<p class="maodian">
</p>
<p class="maodian"></p><h3>
4.3查询逻辑读/物理读数量</h3>
<p>
逻辑读/物理读过多可能导致sql语句占用较多的cpu时间。通过查询unique sql视图可以得到sql语句逻辑/物理读数据块的数量，辅助判断响应过慢的原因：</p>
<p>
查询物理读块数量：</p>
<p>
select n_blocks_fetched from pgxc_instr_unique_sql;</p>
<p>
查询逻辑读块数量：</p>
<p>
select n_blocks_hit from pgxc_instr_unique_sql;</p>
<p class="maodian">
</p>
<p class="maodian"></p><h3>
4.4诊断内存配额不足导致性能低下</h3>
<p>
如果数据库缓冲区设置得太小，会导致每个sql语句执行的结果不能被缓存，当前sql执行完毕如果有其他sql执行就会把内存中上一个或上几个sql缓存的执行结果挤出去，下一轮如果当前这个sql再次执行时候又需要从磁盘进行物理io读取数据，而不能直接从缓存中获取数据，进而导致sql执行性能较差。</p>
<p>
缓冲区配额是否足够大，可以通过命中率来判断。缓冲区命中率=n_blocks_hit/n_blocks_fetched，可以通过查询unique sql来诊断是否存在内存配额不足的问题：</p>
<p>
select (n_blocks_hit/ n_blocks_fetched) as hit_ratio from pgxc_instr_unique_sql;</p>
<p>
以上就是详解unique sql原理和应用的详细内容，更多关于unique sql原理和应用的资料请关注其它相关文章！</p>
<p>
原文链接：https://www.cnblogs.com/huaweiyun/p/14143018.html</p>
頁:
[1]