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 </opt/skewness_xunjian/databasename |
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
- 点赞
- 收藏
- 关注作者
评论（0）