把数据从其它数据库或者GaussDB集群迁移到本GaussDB集群的过程中，通过什么工具或者方法完成数据一致性的校验？请展开详细讲一讲

云社区 博客 博客详情【云小课】EI第9课 车海茫茫中寻找你--GaussDB(DWS)海量数据分析 Hi,EI 发表于 2020-08-25 11:56:20 1061  1  3数据库数据仓库服务 GaussDB(DWS)云小课EI企业智能【摘要】 数据仓库服务GaussDB(DWS)使用OBS作为集群数据与外部数据互相转化的平台，支持用户将数据从集群外导入到集群中，快速将样例数据从OBS导入集群。 本示例将加载8.9亿条交通卡口车辆通行模拟数据到数据仓库单个数据库表中，并进行车辆精确查询和车辆模糊查询，展示GaussDB(DWS)对于历史详单数据的高性能查询能力。准备工作已注册华为云账号，且在使用GaussDB(DWS) 前检查账号状态，账号不能处于欠费或冻结状态。已下载客户端并连接到集群。已预先将样例数据上传到OBS桶的“traffic-data”文件夹中，并给所有华为云用户赋予了该OBS桶的只读访问权限。导入交通卡口样例数据使用SQL客户端工具连接到集群后，就可以在SQL客户端工具中，执行以下步骤导入交通卡口车辆通行的样例数据并执行查询。执行以下语句，创建traffic数据库。create database traffic encoding 'utf8' template template0;执行以下步骤切换为连接新建的数据库。在Data Studio客户端的“对象浏览器”窗口，右键单击数据库连接名称，在弹出菜单中单击“刷新”，刷新后就可以看到新建的数据库。右键单击“traffic”数据库名称，在弹出菜单中单击“打开连接”。右键单击“traffic”数据库名称，在弹出菜单中单击“打开新的终端”，即可打开连接到指定数据库的SQL命令窗口，后面的步骤，请全部在该命令窗口中执行。执行以下语句，创建用于存储卡口车辆信息的数据库表。create schema traffic_data; set current_schema= traffic_data; drop table if exists GCJL; CREATE TABLE GCJL ( kkbh VARCHAR(20), hphm VARCHAR(20), gcsj DATE , cplx VARCHAR(8), cllx VARCHAR(8), csys VARCHAR(8) ) with (orientation = column, COMPRESSION=MIDDLE) distribute by hash(hphm);创建外表。外表用于识别和关联OBS上的源数据。<Access_Key_Id>和<Secret_Access_Key>替换为实际值，在创建访问密钥（AK和SK）中获取。create schema tpchobs; set current_schema = 'tpchobs'; drop FOREIGN table if exists GCJL_OBS; CREATE FOREIGN TABLE GCJL_OBS ( like traffic_data.GCJL ) SERVER gsmpp_server OPTIONS ( encoding 'utf8', location 'obs://dws-demo-cn-north-4/traffic-data/gcxx', format 'text', delimiter ',', access_key '<Access_Key_Id>', secret_access_key '<Secret_Access_Key>', chunksize '64', IGNORE_EXTRA_DATA 'on' );执行以下语句，将数据从外表导入到数据库表中。insert into traffic_data.GCJL select * from tpchobs.GCJL_OBS;导入数据需要一些时间，请耐心等待。车辆分析执行Analyze用于收集与数据库中普通表内容相关的统计信息，统计结果存储在系统表PG_STATISTIC中。执行计划生成器会使用这些统计数据，以生成最有效的查询执行计划。执行以下语句生成表统计信息：Analyze;查询数据表中的数据量执行如下语句，可以查看已加载的数据条数。set current_schema= traffic_data; Select count(*) from traffic_data.gcjl;车辆精确查询执行以下语句，指定车牌号码和时间段查询车辆轨迹。GaussDB(DWS) 在应对点查时秒级响应。set current_schema= traffic_data; select hphm, kkbh, gcsj from traffic_data.gcjl where hphm = '粤D12345' and gcsj between '2016-01-06' and '2016-01-07' order by gcsj desc;车辆模糊查询执行以下语句，指定车牌号码和时间段查询车辆轨迹，GaussDB(DWS) 在应对模糊查询时秒级响应。set current_schema= traffic_data; select hphm, kkbh, gcsj from traffic_data.gcjl where hphm like '%A23F%' and kkbh in('508', '1125', '2120') and gcsj between '2016-01-01' and '2016-01-07' order by hphm,gcsj desc;转自https://bbs.huaweicloud.com/blogs/195410

【操作步骤&问题现象】GaussDB A 8.0.0.1 版本集群 是否有方式或方法可以判断产生笛卡尔积的sql?

步骤一：检查集群是否存在catchup结果为空的话判断步骤二，否则需要等catchup完再进行主备切换:select * from pgxc_get_senders_catchup_time();步骤二：查看对端实例redo是否追齐当前实例，在当前主实例所在结点查询： gs_ctl query -D xxxsender_write_location、sender_flush_location、receiver_flush_location、receiver_replay_location该四项值相同说明目前对端实例redo与当前主实例追齐状态，可以进行switchover。  注：xxx为当前主实例路径，一主多备集群需要关注原主实例redo情况。附：GaussDB (DWS) 集群均衡失败原理分析

问题描述在执行查询的时候报错 Error:  temporary file size exceeds temp_file_limit.问题分析通过报错信息可知，当下盘的临时文件超过 temp_file_limit 大小的时候就会产生这个报错。该参数的作用是，限制一个会话中，触发落盘操作时，单个落盘文件的空间大小。例如一次会话中，排序和哈希表使用的临时文件，或者游标占用的临时文件。可以通过调整这个参数的大小，来规避这个报错。

【问题现象】spark连接dws的时候报错，报错为org.postgresql.util.PSQLException:The authentication type 5 is not supported.：【问题根因】因为jdbc包加载的时候和内置的spark待的驱动包有冲突【解决措施】方案一：连接dws的时候驱动使用自带的gsjdbs200.jar这个驱动包方案二：在连接的配置参数是将spark.yarn.user.classpath.first、spark.driver.userClassPathFirst、spark.executor.userClassPathFirst三个参数均设置true

只让用线上DWS了？还有私有云部署的gaussdb A卖吗？

--杀掉会话：select pg_terminate_backend(procpid);--取消正在执行的语句：select pg_cancel_backend(procpid);--查看分布列SELECT getdistributekey('item');--查看表大小select pg_size_pretty(pg_table_size('public.item'));--查看表倾斜select table_skewness('inventory');--审计日志：select * from pgxc_query_audit('2020-07-16 10:36:05','2020-07-16 12:36:05') where username!='omm';--查看一个表有没有做过统计信息收集。postgres=# select * from pg_stat_get_last_analyze_time('test3'::regclass); pg_stat_get_last_analyze_time ------------------------------- (1 row)postgres=# analyze test11;ANALYZEpostgres=# select * from pg_stat_get_last_analyze_time('test11'::regclass); pg_stat_get_last_analyze_time ------------------------------- 2020-07-23 19:07:06.698894+08(1 row)execute direct on (datanode1) 'select * from pg_stat_activity where usename == ''omm''';pg_stat_get_tuples_changedCN上执行下面两个函数：select table_distribution('xrapuser', 'aj_qtzjtxsm');select table_distribution('xrapuser', 'dz_fwtxxx');select pg_size_pretty(pg_total_relation_size('xrapuser.dz_fwtxxx'));select pg_size_pretty(pg_total_relation_size('xrapuser.aj_qtzjtxsm'));select pg_size_pretty(pg_relation_size('xrapuser.dz_fwtxxx'));select pg_size_pretty(pg_relation_size('xrapuser.aj_qtzjtxsm'));--根据relfilenode 查找物理表：select oid, * from pg_class where reltoastrelid = (select oid from pg_class where relfilenode =  103892072);--查询表以及分布列信息SELECT n.nspname    ,c.relname    ,getdistributekey(c.oid)FROM pg_catalog.pg_class cLEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespaceWHERE n.nspname <> 'pg_catalog'    AND n.nspname <> 'information_schema'    AND n.nspname <> 'cstore'    AND c.relkind = 'r'ORDER BY 1,2;--查看活跃的连接select * from pgxc_stat_activity where usename <> 'omm' and state = 'active';select coorname,  usename, datname, enqueue , count(*) from pgxc_stat_activity  where  usename <> 'omm' and state = 'active' group by coorname, usename, datname, enqueue ;select coorname, usename, client_addr, sysdate - query_start as dur, enqueue, query_id,  replace(query, chr(10), ' ') from pgxc_stat_activity where usename!= 'omm' and state = 'active' order by coorname, dur desc;SELECT coorname, usename     ,client_addr     ,sysdate - query_start AS dur     ,query_id     ,substr(replace(query, chr(10), ' '), 0, 100) FROM pgxc_stat_activity WHERE usename != 'omm' AND STATE = 'active' ORDER BY dur DESC;SELECT usename     ,client_addr     ,sysdate - query_start AS dur     ,query_id     ,substr(replace(query, chr(10), ' '), 0, 100) FROM pgxc_stat_activity WHERE usename != 'omm' AND STATE = 'active' ORDER BY dur DESC;SELECT usename     ,client_addr     ,sysdate - query_start AS dur     ,query_id     ,replace(query, chr(10), ' ') FROM pgxc_stat_activity WHERE usename != 'omm' AND STATE = 'active' ORDER BY dur DESC;select coorname,  usename, datname, enqueue , count(*) from pgxc_stat_activity  where  usename <> 'omm' and state = 'active' group by coorname, usename, datname, enqueue ;select coorname,  usename, datname, enqueue , count(*) from pgxc_stat_activity  where  usename <> 'omm' and state = 'active' group by coorname, usename, datname, enqueue ;select substr(query, 1, 100) as sql, count(*) from pgxc_stat_activity where usename <>'omm' and state = 'active' group by sql;SELECT coorname,usename,client_addr,client_hostname,application_name,state_change,connection_info FROM pgxc_stat_activity WHERE client_addr is not null AND application_name NOT SIMILAR TO ('cn_%|dn_%') AND application_name NOT IN  ('JobScheduler','WorkloadMonitor','workload','cm_agent','WLMArbiter','gs_rewind','gs_dump') order by  state_change asc limit 10;--查看DN的连接：select node_name, in_use, count(*) from pg_pooler_status group by node_name, in_use;--查看内存使用情况select * from PV_TOTAL_MEMORY_DETAIL;select split_part(pv_session_memory_detail.sessid,'.',2),sum(totalsize),count(*) from pv_session_memory_detail group by split_part(pv_session_memory_detail.sessid,'.',2) order by sum(totalsize) desc;--查看所有节点的内存使用情况：select * from pgxc_total_memory_detail where memorytype = 'dynamic_used_memory' order by 3 desc; --监控单个DN上每个内存使用情况#!/bin/bash     while true do date >> mem.log     gsql -d postgres -p 25300 -ar -c "select * from pv_total_memory_detail" >> mem.log     gsql -d postgres -p 25300 -ar -c "select * from PV_SESSION_MEMORY_DETAIL order by totalsize desc  limit 100" >> mem.log     gsql -d postgres -p 25300 -ar -c "select split_part(pv_session_memory_detail.sessid,'.',2),sum(totalsize),count(*) from pv_session_memory_detail group by split_part(pv_session_memory_detail.sessid,'.',2) order by sum(totalsize) desc;" >> mem.log     gsql -d postgres -p 25300 -ar -c "select sessid, contextname, level,parent, pg_size_pretty(totalsize) as total ,pg_size_pretty(freesize) as freesize, pg_size_pretty(usedsize) as usedsize, datname,query_id, query from pv_session_memory_detail a , pg_stat_activity b where split_part(a.sessid,'.',2) = b.pid order by totalsize desc limit 100; " >> mem.log     ls -ltrh /srv/BigData/mppdb/data1/coordinator/base/pgsql_tmp/    sleep 20 done --查看每个语句的内存使用情况select sessid, contextname, level,parent, pg_size_pretty(totalsize) as total ,pg_size_pretty(freesize) as freesize, pg_size_pretty(usedsize) as usedsize, datname,query_id, query from pv_session_memory_detail a , pg_stat_activity b where split_part(a.sessid,'.',2) = b.pid  and query_id = '76561193666355359' order by totalsize desc limit 100; select * from pgxc_stat_activity where  query like '%pg_table_size%'select a.query_id, a.tid, a.lwtid, b.contextname, b.parent, b.usedsize/1024/1024 as usedsizeMB from pg_thread_wait_status a , pv_session_memory_detail b where tid =  split_part(b.sessid,'.',2)  and a.query_id = ? order by usedsize desc limit 10;select * from pgxc_total_memory_detail where memorytype = 'dynamic_used_memory' order by 3 desc;select * from PV_SESSION_MEMORY_DETAIL order by totalsize desc  limit 10; --查看等待视图select * from pgxc_thread_wait_status where query_id = ;select query_start, state_change, waiting, enqueue, state, a.query_id, substr(replace(query, chr(10), ' '), 0, 10), node_name,thread_name,tid,lwtid,ptid,tlevel,smpid,wait_status,wait_event  from pgxc_stat_activity a, pgxc_thread_wait_status b  where state = 'active' and  a.query_id = b.query_id and a.query_id <> 0;--语句执行的时间dur.select sysdate - query_start as dur, waiting, enqueue, state, a.query_id, substr(replace(query, chr(10), ' '), 0, 10), node_name,thread_name,tid,lwtid,ptid,tlevel,smpid,wait_status,wait_event  from pgxc_stat_activity a, pgxc_thread_wait_status b  where state = 'active' and  a.query_id = b.query_id and a.query_id <> 0 order by a.query_id, 1 desc;--语句等待视图增加过滤 wait cmd状态. select sysdate - query_start as dur, waiting, enqueue, state, a.query_id, substr(replace(query, chr(10), ' '), 0, 10), node_name,thread_name,tid,lwtid,ptid,tlevel,smpid,wait_status,wait_event  from pgxc_stat_activity a, pgxc_thread_wait_status b  where state = 'active' and  a.query_id = b.query_id and a.query_id <> 0 and wait_status != 'wait cmd'  order by a.query_id, 1 desc;SELECT coorname,usename,client_addr,client_hostname,application_name,state,enqueue,state_change,connection_info FROM pg_stat_activity WHERE client_addr is not null AND application_name NOT SIMILAR TO ('cn_%|dn_%') AND application_name NOT IN ('JobScheduler','WorkloadMonitor','workload','cm_agent','WLMArbiter','gs_rewind','gs_dump')  order by  state_change asc limit 10;select * from pg_thread_wait_status where lwtid = 4742;select * from pg_stat_activity where query_id = 41943725;select * from pg_thread_wait_status where query_id = 41943725; --查看网络首发数据视图：select * from pgxc_comm_send_stream where query_id = 21862846;select * from pgxc_comm_recv_stream where query_id = 21862846;--检查active sql配置show use_workload_manager;show enable_control_group;show enable_resource_record;show enable_resource_track;show resource_track_level;show resource_track_duration;show resource_track_cost; -- 打开 active sqlset use_workload_manager = on;set enable_control_group = on;set enable_resource_record = on;set resource_track_level = query; --每三分钟转储到这个系统表里。select * from GS_WLM_SESSION_INFO where  queryid = 81064793292682836;select * from GS_WLM_SESSION_INFO where query like '%HAVING MAX%';--查看系统中实时的TOPSQL（存储在内存中的）select count(*) from pg_stat_get_wlm_realtime_session_info(null);--内存自适应 --关掉动态负载管理，如果使用了资源池还是会走内存自适应。--表上没有统计信息也不会走内存自适应。show use_workload_manager;show enable_dynamic_workload;--通过debug2 可以查看到内存自适应估算出来的内存信息，下面是关键字Calculated query max --TOP SQL 分析语句select nodename,username,application_name, start_time,  max_peak_memory , queryid, substr(query,1, 10), substring(warning from 'Statistic Not Collect') as warning from  wlm_session where max_peak_memory > 9000 and warning like '%Statistic Not Collect%' order by max_peak_memory desc;select nodename,username,application_name, start_time,  max_peak_memory , queryid, substr(query,1, 10), substring(warning from 'Statistic Not Collect') as warning from  wlm_session where max_peak_memory > 9000 and warning like '%Statistic Not Collect%' and application_name <> 'Data Studio' order by max_peak_memory desc;select nodename,username,application_name, start_time,  max_peak_memory , queryid, substr(query,1, 10), substring(warning from 'Statistic Not Collect') as warning from  wlm_session where  warning like '%Statistic Not Collect%' and application_name = 'Data Studio' order by max_peak_memory desc;select nodename,username,application_name, start_time,  max_peak_memory , queryid, substr(query,1, 10), substring(warning from 'Statistic Not Collect') as warning from  wlm_session where   application_name like '%Admin%' order by max_peak_memory desc;select nodename,username,application_name, start_time,  max_peak_memory , queryid, substr(query,1, 10), substring(warning from 'Statistic Not Collect') as warning from  wlm_session where application_name <> 'Data Studio'  order by max_peak_memory desc;select nodename,duration, username,application_name, start_time,  max_peak_memory , queryid, query, query_plan from  pgxc_wlm_session_info where  start_time + 8 > '2021-04-29' and query like '%xxxx%' order by start_time desc;select nodename,duration, username,application_name, start_time,  max_peak_memory , queryid, query from  pgxc_wlm_session_info where  start_time + 8 > '2021-04-29' and query like '%OFFSET %' order by start_time desc;select * from pgxc_stat_activity where  state = 'active' and  query like '%xxxx%' ;--负载管理相关视图select usename,enqueue,datname,status,attribute,count(*),sum(statement_mem) from pg_session_wlmstat group by 3,1,2,4,5 order by 1,3,4,5 ;select usename,processid,threadid,priority,attribute,lane,enqueue,status,block_time,elapsed_time,statement_mem from pg_session_wlmstat where usename='usr1';select * from pg_stat_get_workload_struct_info(); --CCN排队的都是复杂作业， --开启动态负载管理，当语句估算的内存大于32MB就会在CCN上进行判断，是否应该排队。parctl_min_cost     --资源池上并发控制的最小执行代价max_dop             --资源池上简单作业的并发。active_statements   --资源池上的复杂作业并发。mem_percent         --资源池最大占用内存百分比（mem_percent=0内存管控不起作用） --查看实时的语句和计划。show resource_track_duration ;show resource_track_cost; set enable_resource_track = on;set resource_track_level=query;select * from gs_wlm_session_statistics where query_id = xxxx;select * from gs_wlm_session_statistics where query_id = XXX;select to_number(cast('20201013' as date)-cast('20201013' as date)); --查看历史的语句。select * from GS_WLM_SESSION_INFO;--硬件相关的：--查看raid 卡缓存策略 Write through，IO性能比WriteBack 要慢。/opt/MegaRAID/MegaCli/MegaCli64 -LDinfo -Lall –aAll --多租户问题定位思路，分析步骤：1、看界面报错信息；2、/var/log/Bigdata/tomcat/web.log3、/var/log/Bigdata/controller/aos/ aos.log查看是否存在错误日志4、/var/log/Bigdata/controller/aos/ plugin.log查看是否存在错误日志5、第一个CN看/var/log/Bigdata/mpp/scriptlog/permission/userpermission.log6、cms节点/var/log/Bigdata/mpp/scriptlog/sqlexecutor.log查看创建租户相关sql执行日志7、执行sql对资源池进行控制。 --FIM 常用日志定位信息：ControllerService：/var/log/Bigdata/controller/（OMS安装、运行日志）Httpd：/var/log/Bigdata/httpd（httpd安装、运行日志）logman：/var/log/Bigdata/logman（日志打包工具日志）NodeAgent：/var/log/Bigdata/nodeagent（NodeAgent安装、运行日志）okerberos：/var/log/Bigdata/okerberos（okerberos安装、运行日志）oldapserver：/var/log/Bigdata/oldapserver（oldapserver安装、运行日志）MetricAgent：/var/log/Bigdata/metric_agent（MetricAgent运行日志）omm：/var/log/Bigdata/omm（omm安装、运行日志）timestamp：/var/log/Bigdata/timestamp（NodeAgent启动时间日志）tomcat：/var/log/Bigdata/tomcat（Web进程日志）watchdog：/var/log/Bigdata/watchdog（watchdog日志）upgrade：/var/log/Bigdata/upgrade（升级OMS日志）UpdateService：/var/log/Bigdata/update-service（升级服务日志）patch：/var/log/Bigdata/patch（补丁安装日志）Sudo：/var/log/Bigdata/sudo（sudo脚本执行日志）OS：/var/log/message文件（OS系统日志）OS Performance：/var/log/osperf（OS性能统计日志）OS Statistics：/var/log/osinfo/statistics（OS参数配置信息日志）--收集10%的统计信息。 set default_statistics_target = -10;analzye public.mid_bss_all_customer;--打开debug2日志SET log_min_messages=debug2;SET logging_module='on(STREAM)';set logging_module='on(ALL)';SET log_min_messages=debug5;set logging_module='on(ALL)';--设置打印日志的语句阈值set log_statement='mod';set log_min_duration_statement=1000;--通信库参数：persistent_datanode_connectionsshow comm_usable_memory;--打开通信库的debug日志set enable_fast_query_shipping = off;set comm_debug_mode = on;set log_min_message='DEBUG3';set logging_module='on(COMM_IPC)'; set logging_module='on(COMM_IPC)';   --打开set logging_module='off(COMM_IPC)';   --关闭show logging_module;   --查看设置结果。--资源参数：select * from pg_resource_pool;ALTER RESOURCE POOL pool1 WITH (CONTROL_GROUP="class1:wg2:3"); copy test_dur from '/home/omm/ab.log' with(delimiter ' ');awk -F ' ' '{print NR ',' $14}' aa.log  > ab.log--shell 杀进程ps -ef | grep gsql | grep -v grep | awk '{print $2}' | xargs kill -9 --性能问题排查思路。1, 可以配置上statement_timeout 这个参数，对于执行时间长的语句，自动杀掉。2. 观察集群IO，CPU情况。是否存在满载的情况。各个节点都要看一下。3，根据query_id查一下等待视图pgxc_thread_wait_status，看等哪个节点。4. 对表做一下analyze，再执行查询看一下是否变快。--事务相关参数start transaction read only;set enable_show_any_tuples = true;set enable_indexscan = off;set enable_bitmapscan = off;select xmin,xmax,pgxc_is_committed(xmin),pgxc_is_committed(xmax),oid,* from pg_class where relname='表名' ;start transaction read write;set enable_show_any_tuples = true;set enable_indexscan = off;set enable_bitmapscan = off;select xmin,xmax,pgxc_is_committed(xmin),pgxc_is_committed(xmax),oid,* from pg_class where relname='表名' ; max_prepared_transactions   CN 2048   DN 6072--系统表vacuum 操作set synchronize_seqscans=off;vacuum full  pg_statistic;vacuum full pg_attribute; --shell 常用命令 --删除10天前的文件find ./ -mtime +10 -name "*.*" -exec rm {} \; ---gdb常用命令set logging file llvm_core1.txtset logging onthread apply all btinfo registersdisassemblep $_siginfoinfo files maintenance info sectionsset logging file llvm_core1.txtset logging oninfo registersdisassemblep $_siginfoinfo files maintenance info sections --sar命令sar -r 5 4  输出物理内存和虚拟内存的统计信息sar -B 5 5  分页统计sar -u 3 5  显示CPU使用信息sar -b 3 5  磁盘IO信息sar -n DEV 2 3 网络流量信息 --查看文件数量，快速。ls -f | wc -l--网络问题定位常用命令netstat –anop|grep "on ("| sort –rnk 3|head -50tcpdump tcp -i ethx and host ip1 and ip2 and port port1 -w target.pcap  --使用TCPdump 抓包tcpdump tcp -i eth0 -s 0 -vv host 10.185.181.247 and port 46755 -w  4675.pcapnetstat -naop | grep 54321   --查看端口被占用。netstat -anop | awk '{print $4}' | grep 10.185.181.249|sort|uniq -c|grep " 1 "|wc -l --随机端口不足telnet 10.185.181.249 31001   --防火墙问题    iptables -L speed_test connect IP 端口    --防火墙问题ping -s 8192  -I eth0 10.185.181.249   --对端IP是否可达ssh 10.185.181.249   --对端IP是否可达netstat -l | grep 监听端口号     --连接超时，接收端hang或者繁忙。EPIPE Broken pipe  --本端关闭，丢包导致keepalive心跳失效，重传超时(tcp_retries2)netstat -anop|sort -rnk3|head   --按发送缓冲区从大到小排序gs_check -i CheckNetSpeed  --集群多对多带宽压测ethtool -i enp189s0f0modinfo hns3gsql本地连接CN报connect reset by peer   CN日志中报fork线程资源不足。通过/proc/buddyinfo发现碎片内存过多导致连接不上sysctl -w vm.compact_memory=1 --通信库相关的guc参数set log_min_messages = 'DEBUG5';set logging_module = 'on(all)';set comm_stat_mode=on; set comm_debug_mode=on;--通信相关视图select * from pgxc_comm_recv_stream where node_name = 'xxxx' and remote_name = 'xxxx' and query_id = xxxx;select * from pgxc_comm_send_stream where node_name = 'xxxx' and remote_name = 'xxxx' and query_id = xxxx;select * from pgxc_comm_recv_stream where query_id = xxxx;select * from pgxc_comm_send_stream where query_id = xxxx; --进程相关ps -eo pid,lstart,etime,cmd | grep gaussdb;datestrace -p 47148 -r -T -o strace.log--过滤message的core信息cat /var/log/message | grep segfault --集群启停checkpoint;cm_ctl stop -mi  cm_ctl start -mi --主备切换将DN备实例切换为主实例。假设备实例所在主机plat1，路径为“/gaussdb/data/data_dnS1”。gs_om -t switch -h plat1 -D /gaussdb/data/data_dnS1参数q表示快速切换，nodeid为需要升主的备实例所在节点ID，/srv/BigData/mppdb/data2为备DN或GTM的数据目录。cm_ctl switchover -n nodeid -D /srv/BigData/mppdb/data2 -q --####HA 相关命令。--查询复制曹select pg_get_replication_slots();--删除复制曹pg_drop_replication_slot('slot_name');pg_controldata --查看主备节点日志推进情况.--xlog积压，查看select * from pg_disable_delay_xlog_recycle();--查看进程控制文件。--查看进程控制文件。data/coordinator1 > pg_controldata ./--3. 查询日志同步的进度。 gs_ctl query -D [datapath]select pg_last_xlog_replay_location(); --连接到DN上执行--解析日志pg_xlogdump 000000010000000000000002 -zpg_xlogdump 000000010000000000000004 -n--pagehack 系统表和relfilenode 对应pagehack -f pg_filenode.map -t filenode_map--行存表解析pagehack -f 16502 -t heap --4. 查看发送日志的情况。catchup相关。select pg_stat_get_wal_senders();gsql -d postgres -p 25308 -c 'select * from pgxc_get_senders_catchup_time();' --处理集群只读问题 --设置集群只读。1.分别将主备cms节点的/opt/huawei/Bigdata/mppdb/cm/cm_server/cm_server.conf文件中修改 enable_transaction_read_only =on2.依次执行kill -9 cm_server备机和主机进程（kill前后分别观察进程号，进程号变化表示成功）--取消集群只读。gs_guc reload -Z coordinator -Z datanode -N all -I all -c "default_transaction_read_only=off"gs_guc reload -Z coordinator -Z datanode -N all -I all -c "datastorage_threshold_value_check=95" datastorage_threshold_check_interval : cm_server每隔一段时间（默认600s）调用一次gs_check检查数据盘磁盘空间占用率datastorage_threshold_value_check: 当磁盘空间占用率超过设定阈值（默认90），设置guc参数default_transaction_read_only=on--查找删除复制槽。select * from pg_get_replication_slots();select pg_drop_replication_slot('dn_6004'); --常用建表测试命令postgres=# create table test3 as select generate_series(1, 1000) as a,generate_series(1, 1000)  as b  from dual;NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default.HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.INSERT 0 1000postgres=# create table test4 as select generate_series(1, 1000) as a,generate_series(1, 1000)  as b  from dual;NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default.HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.create table t5(a int ,b varchar(100));insert into t5  select  generate_series(1, 1000) as a,generate_series(1, 1000) || 'abc'  as b from dual;create table t6(a int ,b text);insert into t6  select  generate_series(1, 1000) as a, 'abc' || generate_series(1, 1000)   as b from dual;explain performance select sum(b) from t6 where b = 'abc100';create table t7(a int ,b text) distribute by hash(b) ;insert into t7  select  generate_series(1, 1000) as a, 'abc' || generate_series(1, 1000)   as b from dual;explain performance select sum(b) from t7 where b = 'abc';create table t8(a int ,b varchar(100)) distribute by hash(b) ;;insert into t8  select  generate_series(1, 1000) as a,generate_series(1, 1000) || 'abc'  as b from dual;explain performance select sum(b) from t8 where b = 'abc' order by 1; --fatal的时候打印堆栈gs_guc reload -Z coordinator -Z datanode -N all -I all -c "backtrace_min_messages=fatal"

【功能模块】【操作步骤&问题现象】1、2、【截图信息】【日志信息】（可选，上传日志内容或者附件）

使用FusionCare巡检工具对DWS巡检后在巡检工具的界面上会有“导出巡检报告”的选项，那这个巡检报告导出所调用的链接是什么？并且链接调用的巡检报告存放位置从哪里可以看到？

【功能模块】【操作步骤&问题现象】1、2、【截图信息】【日志信息】（可选，上传日志内容或者附件）

大数据融合分析时代，GaussDB(DWS)如需访问MRS数据源，该如何实现？本期云小课带您开启MRS数据源之门，通过远程读取MRS集群Hive上的ORC数据表完成数据导入DWS。准备环境需确保MRS和DWS集群在同一个区域、可用区、同一VPC子网内，确保集群网络互通。基本流程1、创建MRS分析集群（选择Hive组件）。2、通过将本地txt数据文件上传至OBS桶，再通过OBS桶导入Hive，并由txt存储表导入ORC存储表。3、创建MRS数据源连接。4、创建外部服务器。5、创建外表。6、通过外表导入DWS本地表。创建MRS分析集群登录华为云控制台，选择“EI企业智能 > MapReduce服务”，单击“购买集群”，选择“自定义购买”，填写软件配置参数，单击“下一步”。表1 软件配置参数项取值区域华北-北京四集群名称MRS01集群版本MRS 3.0.5集群类型分析集群填写硬件配置参数，单击“下一步”。表1 硬件配置参数项取值计费模式按需计费可用区可用区2虚拟私有云vpc-01子网subnet-01安全组自动创建弹性公网IP10.x.x.x企业项目defaultMaster节点打开“集群高可用”分析Core节点3分析Task节点0填写高级配置参数，单击“立即购买”，等待约15分钟，集群创建成功。表1 高级配置参数项取值标签test01委托保持默认即可告警保持默认即可规则名称保持默认即可主题名称保持默认即可Kerberos认证默认打开用户名admin密码设置密码，例如：Huawei@12345。该密码用于登录集群管理页面。确认密码再次输入设置admin用户密码登录方式密码用户名root密码设置密码，例如：Huawei_12345。该密码用于远程登录ECS机器。确认密码再次输入设置的root用户密码通信安全授权勾选“确认授权”准备MRS的ORC表数据源本地PC新建一个product_info.txt，并拷贝以下数据，保存到本地。100,XHDK-A-1293-#fJ3,2017-09-01,A,2017 Autumn New Shirt Women,red,M,328,2017-09-04,715,good 205,KDKE-B-9947-#kL5,2017-09-01,A,2017 Autumn New Knitwear Women,pink,L,584,2017-09-05,406,very good! 300,JODL-X-1937-#pV7,2017-09-01,A,2017 autumn new T-shirt men,red,XL,1245,2017-09-03,502,Bad. 310,QQPX-R-3956-#aD8,2017-09-02,B,2017 autumn new jacket women,red,L,411,2017-09-05,436,It's really super nice 150,ABEF-C-1820-#mC6,2017-09-03,B,2017 Autumn New Jeans Women,blue,M,1223,2017-09-06,1200,The seller's packaging is exquisite 200,BCQP-E-2365-#qE4,2017-09-04,B,2017 autumn new casual pants men,black,L,997,2017-09-10,301,The clothes are of good quality. 250,EABE-D-1476-#oB1,2017-09-10,A,2017 autumn new dress women,black,S,841,2017-09-15,299,Follow the store for a long time. 108,CDXK-F-1527-#pL2,2017-09-11,A,2017 autumn new dress women,red,M,85,2017-09-14,22,It's really amazing to buy 450,MMCE-H-4728-#nP9,2017-09-11,A,2017 autumn new jacket women,white,M,114,2017-09-14,22,Open the package and the clothes have no odor 260,OCDA-G-2817-#bD3,2017-09-12,B,2017 autumn new woolen coat women,red,L,2004,2017-09-15,826,Very favorite clothes 980,ZKDS-J-5490-#cW4,2017-09-13,B,2017 Autumn New Women's Cotton Clothing,red,M,112,2017-09-16,219,The clothes are small 98,FKQB-I-2564-#dA5,2017-09-15,B,2017 autumn new shoes men,green,M,4345,2017-09-18,5473,The clothes are thick and it's better this winter. 150,DMQY-K-6579-#eS6,2017-09-21,A,2017 autumn new underwear men,yellow,37,2840,2017-09-25,5831,This price is very cost effective 200,GKLW-l-2897-#wQ7,2017-09-22,A,2017 Autumn New Jeans Men,blue,39,5879,2017-09-25,7200,The clothes are very comfortable to wear 300,HWEC-L-2531-#xP8,2017-09-23,A,2017 autumn new shoes women,brown,M,403,2017-09-26,607,good 100,IQPD-M-3214-#yQ1,2017-09-24,B,2017 Autumn New Wide Leg Pants Women,black,M,3045,2017-09-27,5021,very good. 350,LPEC-N-4572-#zX2,2017-09-25,B,2017 Autumn New Underwear Women,red,M,239,2017-09-28,407,The seller's service is very good 110,NQAB-O-3768-#sM3,2017-09-26,B,2017 autumn new underwear women,red,S,6089,2017-09-29,7021,The color is very good 210,HWNB-P-7879-#tN4,2017-09-27,B,2017 autumn new underwear women,red,L,3201,2017-09-30,4059,I like it very much and the quality is good. 230,JKHU-Q-8865-#uO5,2017-09-29,C,2017 Autumn New Clothes with Chiffon Shirt,black,M,2056,2017-10-02,3842,very good登录OBS控制台，单击“创建桶”，填写以下参数，单击“立即创建”。表1 桶参数参数项取值区域华北-北京四数据冗余存储策略单AZ存储桶mrs-datasource存储类别标准存储桶策略私有默认加密关闭归档数据直读关闭企业项目default标签-等待桶创建好，单击桶名称，选择“对象 > 上传对象”，将product_info.txt上传至OBS桶。切换回MRS控制台，单击创建好的MRS集群名称，进入“概览”，单击“IAM用户同步”所在行的“单击同步”，等待约5分钟同步完成。回到MRS集群页面，单击“节点管理”，单击任意一台master节点，进入该节点页面，切换到“弹性公网IP”，单击“绑定弹性公网IP”，勾选已有弹性IP并单击“确定”，如果没有，请创建。记录此公网IP。确认主master节点。使用SSH工具以root用户登录以上节点，root密码为Huawei_12345，切换到omm用户。su - omm执行以下命令查询主master节点，回显信息中“HAActive”参数值为“active”的节点为主master节点。sh ${BIGDATA_HOME}/om-0.0.1/sbin/status-oms.sh使用root用户登录主master节点，切换到omm用户，并进入Hive客户端所在目录。su - ommcd /opt/client在Hive上创建存储类型为TEXTFILE的表product_info。在/opt/client路径下，导入环境变量。source bigdata_env登录Hive客户端。beeline依次执行以下SQL语句创建demo数据库及表product_info。CREATE DATABASE demo;USE demo;DROP TABLE product_info; CREATE TABLE product_info ( product_price int not null, product_id char(30) not null, product_time date , product_level char(10) , product_name varchar(200) , product_type1 varchar(20) , product_type2 char(10) , product_monthly_sales_cnt int , product_comment_time date , product_comment_num int , product_comment_content varchar(200) ) row format delimited fields terminated by ',' stored as TEXTFILE将product_info.txt数据文件导入Hive。切回到MRS集群，单击“文件管理”，单击“导入数据”。OBS路径：选择上面创建好的OBS桶名，找到product_info.txt文件，单击“是”。HDFS路径：选择/user/hive/warehouse/demo.db/product_info/，单击“是”。单击“确定”，等待导入成功，此时product_info的表数据已导入成功。创建ORC表，并将数据导入ORC表。执行以下SQL语句创建ORC表。DROP TABLE product_info_orc; CREATE TABLE product_info_orc ( product_price int not null, product_id char(30) not null, product_time date , product_level char(10) , product_name varchar(200) , product_type1 varchar(20) , product_type2 char(10) , product_monthly_sales_cnt int , product_comment_time date , product_comment_num int , product_comment_content varchar(200) ) row format delimited fields terminated by ',' stored as orc;将product_info表的数据插入到Hive ORC表product_info_orc中。insert into product_info_orc select * from product_info;查询ORC表数据导入成功。select * from product_info_orc;创建MRS数据源连接登录DWS管理控制台，单击已创建好的DWS集群，确保DWS集群与MRS在同一个区域、可用分区，并且在同一VPC子网下。切换到“MRS数据源”，单击“创建MRS数据源连接”。选择前序步骤创建名为的“MRS01”数据源，用户名：admin，密码：Huawei@12345，单击“确定”，创建成功。创建外部服务器使用Data Studio连接已创建好的DWS集群。新建一个具有创建数据库权限的用户dbuser：CREATE USER dbuser WITH CREATEDB PASSWORD "Bigdata@123";切换为新建的dbuser用户：SET ROLE dbuser PASSWORD "Bigdata@123";创建新的mydatabase数据库：CREATE DATABASE mydatabase;执行以下步骤切换为连接新建的mydatabase数据库。在Data Studio客户端的“对象浏览器”窗口，右键单击数据库连接名称，在弹出菜单中单击“刷新”，刷新后就可以看到新建的数据库。右键单击“mydatabase”数据库名称，在弹出菜单中单击“打开连接”。右键单击“mydatabase”数据库名称，在弹出菜单中单击“打开新的终端”，即可打开连接到指定数据库的SQL命令窗口，后面的步骤，请全部在该命令窗口中执行。为dbuser用户授予创建外部服务器的权限：GRANT ALL ON FOREIGN DATA WRAPPER hdfs_fdw TO dbuser;其中FOREIGN DATA WRAPPER的名字只能是hdfs_fdw，dbuser为创建SERVER的用户名。执行以下命令赋予用户使用外表的权限。ALTER USER dbuser USEFT;切换回Postgres系统数据库，查询创建MRS数据源后系统自动创建的外部服务器。SELECT * FROM pg_foreign_server;返回结果如： srvname | srvowner | srvfdw | srvtype | srvversion | srvacl | srvoptions --------------------------------------------------+----------+--------+---------+------------+--------+--------------------------------------------------------------------------------------------------------------------- gsmpp_server | 10 | 13673 | | | | gsmpp_errorinfo_server | 10 | 13678 | | | | hdfs_server_8f79ada0_d998_4026_9020_80d6de2692ca | 16476 | 13685 | | | | {"address=192.168.1.245:9820,192.168.1.218:9820",hdfscfgpath=/MRS/8f79ada0-d998-4026-9020-80d6de2692ca,type=hdfs} (3 rows)切换到mydatabase数据库，并切换到dbuser用户。SET ROLE dbuser PASSWORD "Bigdata@123";创建外部服务器。SERVER名字、地址、配置路径保持与8一致即可。CREATE SERVER hdfs_server_8f79ada0_d998_4026_9020_80d6de2692ca FOREIGN DATA WRAPPER HDFS_FDW OPTIONS ( address '192.168.1.245:9820,192.168.1.218:9820', //MRS管理面的Master主备节点的内网IP，可与DWS通讯。 hdfscfgpath '/MRS/8f79ada0-d998-4026-9020-80d6de2692ca', type 'hdfs' );查看外部服务器。SELECT * FROM pg_foreign_server WHERE srvname='hdfs_server_8f79ada0_d998_4026_9020_80d6de2692ca';返回结果如下所示，表示已经创建成功： srvname | srvowner | srvfdw | srvtype | srvversion | srvacl | srvoptions --------------------------------------------------+----------+--------+---------+------------+--------+--------------------------------------------------------------------------------------------------------------------- hdfs_server_8f79ada0_d998_4026_9020_80d6de2692ca | 16476 | 13685 | | | | {"address=192.168.1.245:9820,192.168.1.218:29820",hdfscfgpath=/MRS/8f79ada0-d998-4026-9020-80d6de2692ca,type=hdfs} (1 row)创建外表获取Hive的product_info_orc的文件路径。登录MRS管理控制台。选择“集群列表 > 现有集群”，单击要查看的集群名称，进入集群基本信息页面。单击“文件管理”，选择“HDFS文件列表”。进入您要导入到GaussDB(DWS)集群的数据的存储目录，并记录其路径。创建外表。 SERVER名字填写创建的外部服务器名称，foldername填写查到的路径。DROP FOREIGN TABLE IF EXISTS foreign_product_info; CREATE FOREIGN TABLE foreign_product_info ( product_price integer not null, product_id char(30) not null, product_time date , product_level char(10) , product_name varchar(200) , product_type1 varchar(20) , product_type2 char(10) , product_monthly_sales_cnt integer , product_comment_time date , product_comment_num integer , product_comment_content varchar(200) ) SERVER hdfs_server_8f79ada0_d998_4026_9020_80d6de2692ca OPTIONS ( format 'orc', encoding 'utf8', foldername '/user/hive/warehouse/demo.db/product_info_orc/' ) DISTRIBUTE BY ROUNDROBIN;执行数据导入创建本地目标表。DROP TABLE IF EXISTS product_info; CREATE TABLE product_info ( product_price integer not null, product_id char(30) not null, product_time date , product_level char(10) , product_name varchar(200) , product_type1 varchar(20) , product_type2 char(10) , product_monthly_sales_cnt integer , product_comment_time date , product_comment_num integer , product_comment_content varchar(200) ) with ( orientation = column, compression=middle ) DISTRIBUTE BY HASH (product_id);从外表导入目标表。INSERT INTO product_info SELECT * FROM foreign_product_info;查询导入结果。SELECT * FROM product_info;转自【云小课】EI第17课 大数据融合分析：GaussDB(DWS)轻松导入MRS-Hive数据源-云社区-华为云 (huaweicloud.com)

【功能模块】gaussDB有没有类似于oracle的那种检索功能，根据注释查询表名，字段信息另外guassDB如何查询元数据信息

使用admin登录OC导出报表1.运维分析--》我的报表--》自定义报表2.我的报表 +3.数据集：数据仓库服务性能分析（高阶）   时间选择：年，月，日，30天等  维度/行：资源类型，UUID,名称，资源子类型  指标/行：可以选择内存，cpu，磁盘等监控信息4.时间段，指标设置好后点击右下角更新--》右上角保存  5.输入报表名称，报表类型（保存好后在此类型下查找报表） 6.查找下载报表返回我的报表-->租户资源统计分析报表--》导出报表--》下载报表  

【操作步骤&问题现象】通过日志发现报错 ERROR Failed to get blkid dev ,fstab_dev is /dev/mapper/VG-root (/dev/mapper/VG-root /).                            ERROR check mount failed检查“/etc/fstab”文件中配置的分区，能在“/proc/mounts”中能找到。和其他节点未告警节点比对 未发现异常，问下有其他排查思路没？cat /proc/swaps都为空