GaussDB(DWS)实践系列-数据仓库日常巡检策略总结

数据仓库日常巡检策略总结

一、     摘要

为保证现网数据库稳定运行，需要进行日常例行巡检，以保障系统的软硬件处于良好的工作状态，并能够及时发现可能出现的问题，做好风险预警和问题处理。

二、     巡检内容介绍

（一）检查数据库基本状况

登录数据库后台，查询数据库集群状态和实例是否正常，Normal为正常状态。

（二）检查数据库资源使用

1、检查数据库连接

登录数据库使用如下命令查询数据库连接数，查看数据库连接数是否超过设置的最大连接数的60%：

select coorname,count(1)    from pgxc_stat_activity group by coorname；

2、检查CPU使用

top命令是Linux下常用的性能分析工具，能够实时显示系统中各个进程的资源占用状况，类似于Windows的任务管理器，查询结果如下：

其中第三行%Cpu(s):  4.9 us,  2.4 sy,  0.0 ni, 92.6 id,  0.1 wa,  0.0 hi,  0.0 si,  0.0 st

us：用户cpu使用率，反映用户进程CPU使用情况

sy：系统cpu使用率，反应系统进程（函数调用等）CPU使用情况

id：空闲CPU百分比，反应CPU是否繁忙。

当id值大于50%时，数据库CPU资源充裕。

3、检查磁盘空间

为了防止磁盘占用超过90%，触发集群只读，需要确认磁盘空间占用在正常范围内，如果占用超60%，可以删除时间过久的备份文件，使用如下命令查询：df -h

4、检查内存使用

使用free  -g或者top命令查询内存使用情况，例如top命令：

total：总内存

free：空闲内存

used：已使用内存

buff/cache：缓存

当free值大于50%时，数据库内存资源充裕。

5、检查I/O使用

使用iostat -xm 1 命令查询I/O使用情况：

rMB/s：每秒读单位MB

wMB/s：每秒写单位MB

await：io等待单位ms

%util：磁盘使用率

如果%util接近100%,表明I/O请求太多,I/O系统已经满负荷，磁盘可能存在瓶颈,一般%util大于70%,I/O压力就比较大，读取速度有较多的wait。

6、检查网络资源

使用sar -n DEV 1命令查看网络资源使用情况。

rxkB/s：每秒网络接收数据量单位kb

txkB/s：每秒网络发送数据量单位kb

关注发送和接收的数据量是否达到网卡的瓶颈，例如万兆网卡瓶颈约为1G。

（三）检查数据库备份结果

例如将如下备份脚本加入到crontab定时任务中，每日凌晨3点归档到指定目录，需要定期检查备份脚本运行和备份文档的归档情况：

import os

import commands

import datetime

import time

env = 'source /opt/huawei/Bigdata/mppdb/.mppdbgs_profile;'

dump_home = '/srv/BigData/mppdb/data2/gs_dump/'

dump_log = '%sbackupdb.log' % dump_home

cmdfile = '%sbackup_cmd.txt' % dump_home

if not os.path.isdir(dump_home):

cmd =   'mkdir -p %s' % dump_home

os.system(cmd)

def log(msg,logfilefullpath):

os.system('echo "%s" >> %s' % (msg, logfilefullpath))

def exec_sql(sql):

cmd = '%s   gsql -d postgres -p 25308 -t -c "%s"' %  (env,sql)

(status,   output) = commands.getstatusoutput(cmd)

return   status,output

def getalldb():

databases   = []

sql =   "select datname from pg_database where   datname='paas_basedateservice';"

status,output = exec_sql(sql)

if status   != 0 :

log(output,dump_log)

for i in   output.split('\n'):

if   i.strip() != '':

databases.append(i.strip())

log('database count is : %s' % len(databases),dump_log)

return   databases

def exec_dump(databases,begin_date):

dump_dir =   '%s%s/' % (dump_home,datetime.datetime.now().strftime('%Y%m%d'))

if not   os.path.isdir(dump_dir):

cmd =   'mkdir -p %s' % dump_dir

os.system(cmd)

dumpallfile = '%sgs_dumpall_%s.sql' %(dump_dir,begin_date)

dumpalllog   = '%sgs_dumpall_%s.log' %(dump_dir,begin_date)

log('dumpall : start time is [%s]' % (   datetime.datetime.now().strftime('%Y%m%d%H%M%S')),dump_log)

cmd =   '%sgs_dumpall -s -g -p 25308 -f %s > %s 2>&1' %   (env,dumpallfile,dumpalllog)

log('--------------%s--------------'% begin_date,cmdfile)

log(cmd,   cmdfile)

os.system(cmd)

log('dumpall : end   time is   [%s]' % (datetime.datetime.now().strftime('%Y%m%d%H%M%S')),dump_log)

for db in   databases:

desfile = '%s%s_%s.sql' % (dump_dir,db,begin_date)

logfile = '%s%s_%s.log' % (dump_dir,db,begin_date)

log('dump database [%s] : start time is [%s]' %   (db,datetime.datetime.now().strftime('%Y%m%d%H%M%S')),dump_log)

cmd =   '%sgs_dump %s -p 25308 -C -F p -f %s > %s 2>&1' %   (env,db,desfile,logfile)

log(cmd,cmdfile)

os.system(cmd)

log('dump database [%s] : end     time is [%s]' % (db,   datetime.datetime.now().strftime('%Y%m%d%H%M%S')),dump_log)

def main():

while   True:

current_time = datetime.datetime.now()

hour =   current_time.strftime('%H')

begin_date = current_time.strftime('%Y%m%d%H%M%S')

log('check time [%s]' % begin_date,dump_log)

if   hour == '03':

log('start time is %s' % begin_date,dump_log)

databases = getalldb()

exec_dump(databases,begin_date)

end_time = datetime.datetime.now().strftime('%Y%m%d%H%M%S')

log('end   time is %s' %   end_time,dump_log)

time.sleep(3600)

if __name__ == '__main__':

main()

（四）检查数据库性能

1、      查询数据库锁信息

如果 pgxc_thread_wait_status 视图wait_status有acquire lock状态，说明此sql存在锁冲突，通过以下语句查询锁冲突的sql。

select query from   pgxc_stat_activity where query_id in(select query_id from   pgxc_thread_wait_status where query_id > 0 and wait_status like 'acquire   lock%');

无法释放的锁的线程，可以使用SELECT  pg_cancel_backend(pid)命令释放。

2、      识别未作analyze的表

在数据库中，统计信息是规划器生成计划的源数据。没有收集统计信息或者统计信息陈旧往往会造成执行计划严重劣化，从而导致性能问题。以下命令能够查询表最近执行analyze的时间。

SELECT n.nspname,pg_stat_get_last_analyze_time(c.oid)

FROM pg_class c

LEFT JOIN pg_namespace n ON n.oid = c.relnamespace

WHERE c.relkind = 'r'::"char" and c.relname='t1';

3、      检查耗时久的SQL

使用如下命令可以查询当前数据库中耗时TOP 20的SQL，可根据具体SQL进一步分析耗时久的原因。

select   pid,coorname,datname,usename,application_name,(now()-query_start) as   queryruntime,(now()-xact_start) as   xactruntime,waiting,query_id,substr(query,1,80) as query from   pgxc_stat_Activity where state<>'idle'

and datname <>'postgres' order by xactruntime   desc,queryruntime desc limit 20;

4、      检查有倾斜的表

数据倾斜问题是分布式架构的重要难题，它破坏了各个节点对等的要求，导致单节点（倾斜节点）所存储或者计算的数据量远大于其他节点，所以会造成以下危害：

存储上的倾斜会严重限制系统容量，在系统容量不饱和的情况下，由于单节点倾斜的限制，使得整个系统容量无法继续增长。

计算上的倾斜会严重影响系统性能，由于倾斜节点所需要运算的数据量远大于其它节点，导致倾斜节点降低系统整体性能。

数据倾斜还严重影响了分布式架构的扩展性。由于在存储或者计算时，往往会将相同值的数据放到同一节点，因此当倾斜数据（大量数据的值相同）出现之后，即使我们增加节点，系统瓶颈仍然受限于倾斜节点的容量或者性能。

可参考如下脚本检查生产库中存在数据分布倾斜的表。

#!/bin/bash

resultdir=/opt/skewness_xunjian/check_result_`date   +%Y%m%d%p`

mkdir $resultdir

source /opt/huawei/Bigdata/mppdb/.mppdbgs_profile

gsql -d postgres -p   25308 -c "select datname from PG_DATABASE where datname!='template1' and   datname!='template0' and datname!='postgres'" | grep -v datname|grep -v   - | grep -v row > /opt/skewness_xunjian/databasename

while read i; do

if [[ -z $i ]]; then

continue

fi

gsql -d $i -p 25308 -c "select   t.tableowner, s.schemaname, s.tablename, (s.totalsize/1024/1024/1024)::int as   \"totalsize(GB)\", s.skewratio from pgxc_get_table_skewness s,   pg_tables t where s.schemaname=t.schemaname and s.tablename=t.tablename and   s.skewratio>=0.05 and s.totalsize>1*1024*1024*1024::bigint order by   s.skewratio desc;" > ${resultdir}/${i}_`date   +%Y%m%d%p`_result_filter.txt

done  

5、      历史TOPSQL巡检

定期对生产集群运行的SQL进行统计分析，可以识别低效SQL并推动优化整改，从而提升系统的整体性能。

步骤1：抓取指定时间段，指定database的topsql并创建统计表。

create table topsql_1930_2000s DISTRIBUTE BY HASH(queryid) as

select t.dbname,t.count,t.avg_duration,t.count*t.avg_duration as totaltime,t.max_duration,t.stream_count,t.subplan_count,t.queryid,p.query   from (

select

substr(query,1,60) as sub_query,

dbname,

count(1) as count,

round(avg(duration),2) as avg_duration,

max(duration) as max_duration,

public.stream_count(query_plan) as stream_count,

public.subplan_count(query_plan) as subplan_count,

max(queryid) as queryid

from pgxc_wlm_session_info

where dbname in('ch_yszx_gz','ch_bmys_xa','ch_pems')

and start_time >'2020-05-12 19:30:00'

and finish_time < '2020-05-12 20:00:00'

group by 1,2,6,7

having (count >0  )

)t left join pgxc_wlm_session_info    p on t.queryid = p.queryid;

步骤2：使用copy语句把统计表数据导出到服务器指定路径上，保存为csv格式。

步骤3：通过ftp工具把topsql_1930_2000.csv文件取到本地分析topsql。针对耗时长、stream多的SQL进行分析优化。

（五）检查数据库安全性

登录数据库，通过审计日志查询client_conninfo，detail_info等信息，检查是否有异常IP登录或者异常操作。

SELECT * FROM pg_query_audit('2020-06-10 08:00:00','2020-06-10 23:55:33');

华为云社区论坛链接：https://bbs.huaweicloud.com/forum/forum-598-1.html

EI企业智能 Gauss AP 数据仓库服务 GaussDB(DWS)

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。