GaussDB(DWS)实践系列-常用命令FAQ

举报
四叶草 发表于 2021/04/23 11:44:32 2021/04/23
【摘要】 本文结合GaussDB(DWS)的实践交付和运维经验，从集群操作类、资源监控类、元数据查询类、权限管理类四大模块分类整理常用命令，希望通过这些常用命令给大家提供一些参考和启发，提升后续交付和运维效率。

        本文结合GaussDB(DWS)的实践交付和运维经验，从集群操作类、资源监控类、元数据查询类、权限管理类四大模块分类整理常用命令，希望通过这些常用命令给大家提供一些参考和启发，提升后续交付和运维效率。

GaussDB(DWS)常用命令FAQ
分类 功能描述 命令 备注
集群操作类 查看集群状态 cm_ctl query -Cv 增加-d参数可以查看对应实例目录。命令：cm_ctl query -Cvd
集群启动 cm_ctl start 启动指定实例：
cm_ctl start -n 3 -D /srv/BigData/mppdb/data2/master2/
集群停止 cm_ctl stop 集群停止默认超时时间20min，如果集群在20min内还未停止成功，可以通过立即停止命令停止集群。命令：cm_ctl stop -m i
停止指定实例：
cm_ctl stop -n 3 -D /srv/BigData/mppdb/data2/master2/
均衡集群状态 cm_ctl switchover -a 登录到任何数据库节点，执行cm_ctl query -Cvs，如果无返回集群信息说明集群已经处于均衡状态。
查看磁盘使用情况 gs_ssh -c 'df -h' gs_ssh工具帮助用户在集群各节点上执行相同命令，并一起返回查询结果。
1.单个磁盘使用率达到80%告警，检查是否存在数据倾斜；
2.所有磁盘使用率达到70%，需要及时扩容，或进行数据清理
配置集群访问白名单 gs_guc set -Z coordinator -N all -I all -h "host all jack 10.10.0.35/32 sha256" 10.10.0.35/32表示只允许IP地址为10.10.0.35的主机连接，在使用过程中根据用户的网络进行配置修改。
清理指定线程 select pg_terminate_backend('140514443581184'); 针对于异常运行的SQL，可通过PID进行清理。
磁盘页面碎片恢复 针对整个表恢复：vacuum full tablename;
针对整个库恢复：vacuum full;		 业务低峰期执行vacuum操作：
简单的VACUUM（不带FULL选项）只是简单地回收空间并且令其可以再次使用,因为没有请求排他锁，这种形式的命令可以和对表的普通读写并发操作。
VACUUM FULL执行更广泛的处理，包括跨块移动行，以便把表压缩到最少的磁盘块数目里,这种形式要慢许多并且在处理的时候需要在表上施加一个排他锁。
清理数据库连接 删除数据库 postgres 在dn1和dn2节点上的连接。
clean connection to node (dn_6001_6002,dn_6003_6004) for database postgres;
删除用户 jack 在dn1节点上的连接。
clean connection to node (dn_6001_6002) to user jack;
删除在数据库 postgres 上的所有连接。
clean connection to all force for database postgres;		 当数据库有异常时，可使用clean connection命令来清理数据库连接。
更新统计信息 针对整个表更新：analyze tablename;
针对整个库更新：analyze;		 没有收集统计信息或者统计信息陈旧往往会造成执行计划严重劣化，从而导致性能问题。
设置GUC参数 动态生效方式设置GUC参数(立即生效)：
设置CN：gs_guc reload -Z coordinator -N all -I all -c "max_active_statements=10"
设置DN：gs_guc reload -Z datanode -N all -I all -c "max_active_statements=10"
重启集群生效方式设置guc参数：
设置CN：gs_guc set -Z coordinator -N all -I all -c "max_active_statements=10"
设置DN：gs_guc set -Z datanode -N all -I all -c "max_active_statements=10"		 部分参数例如POSTMASTER类型，使用set命令进行参数设置后，需要重启集群生效。
查看GUC参数 show max_active_statements; 分别登录CN或DN节点，可查询对应CN或DN的GUC参数值。
指定实例执行命令 在指定CN执行命令，例如cn_5001。
execute direct on (cn_5001) 'select * from pg_stat_activity where pid = 140596203210496';
在指定DN执行命令，例如dn_6001_6002。
execute direct on (dn_6001_6002) 'select * from pg_stat_activity where pid = 140596203210496';		 •只有系统管理员才能执行EXECUTE DIRECT；
•为了各个节点上数据的一致性，SQL语句仅支持SELECT；
•由于CN节点不存储用户表数据，不允许指定CN节点执行用户表上的SELECT查询。
访问指定数据库 访问指定数据库：
gsql -d database_name -p 8000 -r
切换到指定数据库：
\c database_name
退出数据库：
\q		 使用指定用户登录数据库增加-U 用户名 -W 密码，例如
gsql -d database_name -p 8000 -r -U user01 -W 'test@123'
查看数据库版本信息 方法1：登录数据库执行 select version();
方法2：数据库外执行  gsql -V		 登录任一CN节点执行查询命令。
导出导入命令 方法1：使用gs_dump导出导入指定表
导出：gs_dump -p 8000 postgres -t table0 -f /home/omm/backup.sql
导入：gsql -d postgres -p 8000 -f /home/omm/backup.sql
方法2：使用copy导出导入
导出：copy tpcds.ship_mode TO '/home/omm/ds_ship_mode.dat';
导入：copy tpcds.ship_mode_t1 FROM '/home/omm/ds_ship_mode.dat';
方法3：通过GDS工具，采用多DN并行导入,适用于大批量数据入库，导入效率高。。		 •gs_dump是GaussDB(DWS)用于导出数据库相关信息的工具，用户可以自定义导出一个数据库或其中的对象（模式、表、视图等）。支持导出的数据库可以是默认数据库postgres，也可以是自定义数据库。
•通过COPY命令实现在表和文件之间拷贝数据。COPY FROM从一个文件拷贝数据到一个表，COPY TO把一个表的数据拷贝到一个文件。可通过format、delimiter设置格式和分隔符。
•数据服务工具GDS帮助分发待导入的用户数据及实现数据的高速导入。GDS需部署到数据服务器上。数据量大，数据存储在多个服务器上时，在每个数据服务器上安装配置、启动GDS后，各服务器上的数据可以并行入库。
查看编码字符集 查看客户端编码：
show client_encoding;
查看服务端编码：
show server_encoding;		 •client_encoding显示客户端的字符编码类型。尽量客户端编码和服务器端编码一致，提高效率，例如可通过set client_encoding=GBK;（session级生效）进行调整修改。
•server_encoding显示当前数据库的服务端编码字符集，用户无法修改此参数，只能查看。
设置模式搜索路径 设置模式搜索路径：
set search_path to tpcds, public;
查看模式搜索路径：
show search_path;		 •通过未修饰的表名（名字中只含有表名，没有“schema名”）引用表时，系统会通过search_path（搜索路径）来判断该表是哪个schema下的表。
资源监控类 查看并发数 select coorname,count(*) from pgxc_stat_activity where state<>'idle' group by coorname; 查询活跃语句数量，衡量所有CN业务并发情况。
如果长期并发达到max_active_statements，在各方面资源充足的情况下，可以适当增大max_active_statements。
查看连接数 select coorname,count(*) from pgxc_stat_activity group by 1 order by 2 desc; 查询客户端与CN以及CN之间的连接数。
查看动态内存使用率 select p1.nodename, p1.memorytype, p2.memorytype, p1.memorymbytes/p2.memorymbytes as percent
from pgxc_total_memory_detail p1, pgxc_total_memory_detail p2
where p1.nodename=p2.nodename
and p1.memorytype='dynamic_used_memory'
and p2.memorytype='max_dynamic_memory'
order by p1.nodename;		 GaussDB(DWS)进程所使用的内存大小dynamic_used_memory占用max_dynamic_memory百分比，percent列预警值75% (可以根据需要调整)。
查看运行中SQL状态 select pid,query_id,coorname,datname,usename,current_timestamp-query_start as duration,substr(query,0,100) as sub_query from pgxc_stat_activity where state= 'active' and datname <> 'postgres' and usename <> 'Ruby' order by duration desc; 查询运行中语句状态，截取substr(query,0,100),可根据pid获得完整SQL。
检查当前语句排队情况 select coorname,usename,current_timestamp-query_start as duration, enqueue,query_id,query,pid
from pgxc_stat_activity
where enqueue is not null and state<>'idle'
and usename <> 'Ruby' order by duration desc;		 预警值，排队语句数量达到10。
查看脏页率 查询指定库的脏页率：
select * from pgxc_get_stat_dirty_tables(30,100000);
查询指定schema的脏页率：
select * from pgxc_get_stat_dirty_tables(30,100000,'mppedw');
查询指定表的脏页率：
select c.oid AS relid, n.nspname AS schemaname, c.relname,
pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins,
pg_stat_get_tuples_updated(c.oid) AS n_tup_upd,
pg_stat_get_tuples_deleted(c.oid) AS n_tup_del,
pg_stat_get_live_tuples(c.oid) AS n_live_tup,
pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,
cast( (n_dead_tup / (n_live_tup + n_dead_tup + 0.0001) * 100) AS numeric(5,2)) AS dirty_page_rate
from pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
where c.oid = (select 'pg_catalog.pg_attribute'::regclass::oid);		 (30,100000,'mppedw')，其中30代表统计脏页率大于30%的业务表，100000表示统计脏数据行数大于100000的业务表，mppedw指定查询的schema。
预警值30%(可以根据需要调整或删除)，在业务空闲时间，对脏页率高的表做vacuum full。
查看倾斜率 查看指定数据库中Hash表的数据分布情况：
SELECT * FROM pgxc_get_table_skewness ORDER BY skewratio DESC;
查看指定表在DN上的数据分布（显示每个DN上的数据量）：
SELECT a.count,b.node_name FROM (SELECT count(*) AS count,xc_node_id
FROM inventory  GROUP BY xc_node_id) a, pgxc_node b WHERE a.xc_node_id=b.node_id ORDER BY a.count desc;		 如果skewratio列(倾斜率)或DN上数据分布差大于10%，数据总量大于100GB时需处理。
可通过选择合适的分布键重建该表，重建后重新检查倾斜情况。
查看线程等待状态 select * from pgxc_thread_wait_status where queryid=xxxx; 1.如果存在等锁，例如acquire lock，找到持锁语句后杀掉线程；
2.如果大量语句都在等待同一个dn，排查是否存在表倾斜；
3.如果大量语句都在等待同一个节点，说明该节点可能存在资源瓶颈，继续排查该节点的cpu、io、网络情况；
4.查看执行计划，对语句进行调优。
查看目标锁被谁持有 select * from pg_locks where relation = 11799 and granted = 'true'; 根据表名从pg_class获取到oid(对应pg_locks中的relation字段)，通过pg_locks查询指定表上的锁被谁持有。
select oid,relname from pg_class where relname='tablename';
查看历史SQL执行耗时 select substr(query,1,100) as sub_query,dbname,username,count(*),max(duration) as max_duration,avg(duration) as avg_duratiom
from pgxc_wlm_session_info
where substr(start_time,1,10)='2021-03-17'
group by sub_query,dbname,username
order by count desc;		 前提条件：enable_resource_track,enable_resource_record参数为打开状态，查询语句query进行了截取substr(query,0,100),可根据pid获得完整SQL。
substr(start_time,1,10)='2021-03-17'设置查询指定日期的历史SQL，例如2021-03-17。
查询实时SQL在所有DN上的最大内存峰值 select a.* from pgxc_wlm_session_statistics a where warning is not null or max_peak_memory > 8 * 1024 order by max_peak_memory, warning; 预警值warning is not null or max_peak_memory > 8 GB，可排查是否存在数据倾斜或未收集统计信息，或对SQL进行优化。
查看审计日志 select * from pgxc_query_audit('2021-03-10 17:00:00','2021-03-10 21:00:00') where type = 'login_success' and username = 'user1'; •审计功能总开关(audit_enabled)已开启；
•需要审计的审计项开关已开启；
•只有拥有AUDITADMIN属性的用户才可以查看审计记录；
•pgxc_query_audit可以查询所有CN节点的审计日志。
原型：pgxc_query_audit(timestamptz startime,timestamptz endtime)。
元数据查询类 查看数据库信息 方法1：\l+
方法2：select datname,pg_size_pretty(pg_database_size(datname)) as dbsize from pg_database;		 登录集群查询所有database占用磁盘空间大小及相关信息。
查看schema信息 方法1：\dn+
方法2：SELECT n.nspname AS "Name",
  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner",
  pg_catalog.array_to_string(n.nspacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_'
AND n.nspname <> 'information_schema'
ORDER BY 1;		 查询指定数据库中schema相关信息。
查看schema大小 select schemaname,pg_size_pretty(sum(pg_table_size(schemaname||'.'||tablename))) as pretty_size
from pg_tables
group by schemaname
order by pretty_size desc;		 登录指定数据库查询schema占用磁盘空间大小。
查看表大小 查看占用磁盘空间TOP 50的表信息：
select nspname,relname,pg_table_size(c.oid) as size,pg_size_pretty(pg_table_size(c.oid)) as pretty_size from pg_class c, pg_namespace n where c.relnamespace = n.oid and c.relkind = 'r' order by 3 desc limit 50;
查看指定表大小：
方法1：select * from pg_size_pretty(pg_relation_size('tablename'));
方法2：\dt+ tablename		 登录指定数据库查询业务表占用磁盘空间大小。
查看表定义 方法1：select pg_get_tabledef('tablename');
方法2：\d+ tablename		 方法1返回完整建表语句（可执行建表），方法2反馈详细表结构信息。
其中方法2为后台数据库执行命令，方法一前后台均可。
查看视图定义 方法1：select pg_get_viewdef('viewname');
方法2：\d+ viewname		 其中方法2为后台数据库执行命令，方法一前后台均可。
查看表的分布类型 select
n.nspname as "Schema",
b.relname as "Tablename",
pg_catalog.pg_get_userbyid(b.relowner) as "Owner",
case when c.pclocatortype='H' then 'hash' else 'replication' end as "Distributetype",
getdistributekey(c.pcrelid) as "Distributekey"
from pgxc_class c
left join pg_catalog.pg_class b on b.oid = c.pcrelid
left join pg_catalog.pg_namespace n on n.oid = b.relnamespace
where  c.pclocatortype in ('H','R')
and b.relkind = 'r'
and n.nspname <> 'pg_catalog'
and n.nspname <> 'information_schema'
and n.nspname !~ '^pg_toast'
and pg_catalog.pg_table_is_visible(b.oid)
order by 4;		 DistributeType显示表的分布类型，包括Hash和Replication两种。
DistributeKey显示表的分布健。
查看表的唯一约束 select
n.nspname as "Schema",
b.relname as "Tablename",
pg_catalog.pg_get_userbyid(b.relowner) as "Owner",
case when ps.contype = 'p' then 'primary key' else 'unique index'  end as "ConstrainType",
pg_catalog.pg_get_constraintdef(ps.oid, true) as "ConstraintInfo"
from pgxc_class c
left join pg_catalog.pg_class b on b.oid = c.pcrelid
left join pg_catalog.pg_namespace n on n.oid = b.relnamespace
left join pg_index c1 on c1.indrelid = c.pcrelid
left join pg_constraint ps on ps.conrelid = c.pcrelid and ps.conindid = c1.indexrelid
where  c.pclocatortype in ('H','R')
and b.relkind = 'r'
and ps.contype in ('p','u')
and n.nspname <> 'pg_catalog'
and n.nspname <> 'information_schema'
and n.nspname !~ '^pg_toast'
and pg_catalog.pg_table_is_visible(b.oid)
order by 4; 		 ConstrainType显示表的约束类型，包括primary key主键约束，unique index唯一约束。
权限管理类 授予系统权限 将系统权限授权给用户或者角色。创建名为joe的用户，并将系统权限授权给他。
create user joe password 'Bigdata123@';
grant all privileges to joe;
回收系统权限：
revoke all privileges from joe;		 系统权限又称为用户属性，一般通过CREATE/ALTER ROLE语法来指定。其中，SYSADMIN权限可以通过GRANT/REVOKE ALL PRIVILEGE授予或撤销
授予数据库对象授权 将模式tpcds的访问权限授权给角色tpcds_manager，并授予该角色在tpcds下创建对象的权限
create role tpcds_manager password 'Bigdata123@';
grant usage,create on schema tpcds to tpcds_manager;
revoke usage,create on schema tpcds from tpcds_manager;(回收权限)
将模式tpcds数据表的查询权限赋权给用户或者角色。
grant select on test900 to kim02;
grant select on all tables in schema tpcds to kim02;
revoke select on test900 from kim02;(回收权限)
revoke select on all tables in schema tpcds from kim02;(回收权限)		 将数据库对象（表和视图、指定字段、数据库、函数、模式、表空间等）的相关权限授予特定角色或用户
将角色或用户的权限授权给其他角色或用户 创建角色senior_manager，并授予角色或用户manager的权限。
create role senior_manager password 'Bigdata123@';
grant manager to senior_manager;
撤销权限。
revoke manager from senior_manager;		 将一个角色或用户的权限授予一个或多个其他角色或用户。在这种情况下，每个角色或用户都可视为拥有一个或多个数据库权限的集合。

03.PNG

【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
EI企业智能 Gauss AP 云数据仓库 GaussDB(DWS) 数据库
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入