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

网友投稿 962 2022-05-28

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

一、     摘要

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

二、     巡检内容介绍

(一)检查数据库基本状况

登录数据库后台,查询数据库集群状态和实例是否正常,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   = []

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

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小时内删除侵权内容。

上一篇:使用 HTML、CSS、JavaScript 创建一个简单的井字游戏
下一篇:华为云携手爱奇艺 | 助你随时随地玩转斗图,让沟通更好玩
相关文章