金仓数据库性能诊断
<h2>一、查看错误日志</h2>
<ul>
<li>
<p><strong>数据库错误日志，默认位于数据库data目录下的sys_log：</strong></p>
<pre><code>ls -l 数据库data目录/sys_log</code></pre>
<p><img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/a0e072bad8e23aa1d1b80534201c33a2" alt="" /></p>
</li>
<li><strong>操作系统日志：</strong>
<pre><code>/var/log/messages</code></pre>
<p>包括整体系统信息，其中也包含系统启动期间的日志，此外，mail，cron，daemon，kern和auth等内容也记录在var/log/messages日志中</p>
<pre><code>/var/log/dmesg</code></pre>
<p>内核缓冲信息（kernel ring buffer），在系统启动时，会在屏幕上显示许多与硬件有关的信息。可以用dmesg查看它们</p></li>
</ul>
<h2>二、查看当前数据库连接数</h2>
<ul>
<li>
<p><strong>通过进程数查看：</strong></p>
<pre><code>ps aux|grep kingbase|wc -l</code></pre>
<p><img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/4a6c130b0e2e02f74a02c1eb0cfddaeb" alt="" /></p>
</li>
<li><strong>通过SQL语句查看：</strong>
<pre><code>select count(*) from sys_stat_activity;</code></pre>
<p><img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/b876747ce41de013454859b17e6c674d" alt="" /></p></li>
</ul>
<h2>三、数据库连接监控</h2>
<p><strong>sys_stat_activity，实例维护的一个进程相关的视图,是实时变化的，样例：</strong></p>
<ul>
<li>
<p><strong>查看总连接数：</strong></p>
<pre><code>select count(*) from sys_stat_activity;</code></pre>
<p><img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/abe00f0384ae97a369946bb2519ad763" alt="" /></p>
</li>
<li>
<p><strong>查看正在执行的查询数：</strong></p>
<pre><code>select datname,count(*) as open,
count(*) filter (where state = 'active' ) as active,
count(*) filter (where state = 'idle' ) as idle,
count(*) filter
(where state = 'idle in transaction' ) as idle_in_trans
from sys_stat_activity group by rollup(1);</code></pre>
<p><img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/c295f0099485a2d036428e08c5ae1b17" alt="" /></p>
</li>
<li>
<p><strong>查看连接数：</strong></p>
<pre><code>select datname,usename,client_addr,count(*)
from sys_stat_activity
group by 1,2,3 order by 1,2,4 desc;</code></pre>
<p><img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/1cf215d3145385c8383e7fe3b0892565" alt="" /></p>
</li>
<li>
<p><strong>查看当前正在执行的、执行时间超过3s的SQL：</strong></p>
<pre><code>select datname, pid, client_addr, query_start,
now()-query_start as execute_time,
state, query from sys_stat_activity
where state not in ('idle', 'idle in transaction')
and now()-query_start &gt; interval '3 s' order by query_start;</code></pre>
<p><img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/80fd112f5ec87b137cf2726fe793229d" alt="" /></p>
</li>
<li>
<p><strong>查看大于5分钟的长事务：</strong></p>
<pre><code>select datname, pid, client_addr,query,
xact_start,now()-xact_start as idle_time,
state from sys_stat_activity
where state&lt;&gt;'idle'
and (backend_xid is not null or backend_xmin is not null)
and now()-xact_start &gt; interval '5 min' order by xact_start;</code></pre>
<p><img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/b0ed0c24830417bef97f2a007af387be" alt="" /></p>
</li>
<li><strong>查询活动会话：</strong>
<pre><code>select datname,pid,client_addr,query, query_start,
now()-query_start execute_time
from sys_stat_activity
where state='active' order by execute_time desc;</code></pre>
<p><img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/e5c53c8bbf66738b316e6d7235d17f8c" alt="" /></p></li>
</ul>
<h2>四、长事务清理</h2>
<ul>
<li>
<p><strong>为什么要清理长事务？</strong>
• 垃圾回收原理
• FREEZE
• 频繁唤醒autovacuum worker , 无用功，浪费IO
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/253818a952b95c7965d624aa5a182e73" alt="" /></p>
</li>
<li><strong>当我们需要强杀掉一个数据库连接时，可以执行下面的sql</strong>
<pre><code>select sys_terminate_backend(pid);</code></pre>
<p>其中pid是会话在sys_stat_activity中对应的pid列
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/ff039c03dd759755fd9bae7abca7b1b2" alt="" /></p></li>
</ul>
<h2>五、锁等待清理</h2>
<ul>
<li><strong>锁冲突数量监控</strong>
<pre><code>select count(*) from sys_locks where granted='f';</code></pre>
<p><img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/056decb13c3ca23cb2a0cf4ff3542fdf" alt="" /></p></li>
</ul>
<h2>六、查看数据库状态</h2>
<ul>
<li>
<p><strong>通过进程来看数据库是否运行：</strong>
<code>ps -ef|grep kingbase</code>
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/bb04c341653d420b502a9bd7e6732898" alt="" /></p>
</li>
<li>
<p><strong>通过服务数据库状态：</strong>
<code>systemctl status kingbase8d.service</code>
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/21db6c99b8d8570278613c538def3b9c" alt="" /></p>
</li>
<li><strong>通过工具查看数据库状态：</strong>
<code>./sys_ctl -D data目录 status</code> (需要在kingbase用户下执行)
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/c1467f0dc351096e59c301d208d49172" alt="" /></li>
</ul>
<h2>七、查找数据库相关目录</h2>
<ul>
<li>
<p><strong>查找数据库data目录：</strong>
<code>ps –ef|grep kingbase|grep D</code>
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/eeda349abec3d69c20a99d815ba0a9f9" alt="" /></p>
</li>
<li><strong>查看sys_ctl工具目录，并确定bin目录：</strong>
<code>find / -name sys_ctl</code>
<img src="http://60.191.64.5:16100/server/index.php?s=/api/attachment/visitFile/sign/44651def02321fd208a1f4684ae570ab" alt="" /></li>
</ul>