探索BI系统搭建的必要性与AI技术的应用潜力
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 = []
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小时内删除侵权内容。