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进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容。
  • 点赞
  • 收藏
  • 关注作者

评论(0

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

全部回复

上滑加载中

设置昵称

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

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

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