greenplum常用运维命令

最新推荐文章于 2024-03-29 11:10:00 发布

zyset

最新推荐文章于 2024-03-29 11:10:00 发布

阅读量1.4k

点赞数 1

分类专栏：数据库文章标签： postgresql 数据库运维

本文链接：https://blog.csdn.net/u010473354/article/details/120453505

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

本文介绍了Greenplum数据库的运维操作，包括备用节点的恢复流程，如查看数据量、检查session、启动备用节点；集群管理，如卸载集群、查杀任务；以及用户权限的管理，如初始化权限、开设账户、修改密码和撤销超级管理员权限。

摘要由CSDN通过智能技术生成

备用节点重新恢复三连发

# 删除备节点 (-a 不需要用户确认)
gpinitstandby -ra
# 添加备节点 并 启动 (-a 不需要用户确认)
gpinitstandby -as node1-prod-dataplatform-sciences.yonyouiuap.com
# 启动备用节点
gpinitstandby -n

查看数据量大小表大小数据库大小

# 表大小
select pg_size_pretty(pg_relation_size('gp_test'));

# 表和索引大小
select pg_size_pretty(pg_total_relation_size('gp_test'));

# 查看指定数据库
select pg_size_pretty(pg_database_size('zwcdb'));

# 所有数据库
select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;
  datname  | pg_size_pretty 
-----------+----------------
 zwcdb     | 2241 MB
 postgres  | 47 MB
 template1 | 47 MB
 template0 | 45 MB
 gpperfmon | 67 MB
(5 rows)


# 查看数据分布情况和磁盘空间
select gp_segment_id,count(*) from gp_test group by gp_segment_id order by 1;
 gp_segment_id |  count  
---------------+---------
             0 | 5000000
             1 | 4999999
             2 | 5000001
             3 | 5000000
(4 rows)

select dfhostname, dfspace,round(dfdevice/1024/1024/1024) "Size_GB"  from gp_toolkit.gp_disk_free order by dfhostname;
 dfhostname | dfspace  |  dfdevice  
------------+----------+------------
  sdw1      | 12273372 |  /dev/sdb1
  sdw1      | 12273372 |  /dev/sdb1
  sdw2      | 12273404 |  /dev/sdb1
  sdw2      | 12273404 |  /dev/sdb1

  
# 查询模式大小
select schemaname ,round(sum(pg_total_relation_size(schemaname||'.'||tablename))/1024/1024/1024) "Size_GB"  
from pg_tables where schemaname='cpu_basedoc' group by 1;

查看没有断掉session的进程

select datname,sess_id,usename,application_name,client_addr,state_change,state,query
 from pg_stat_activity  where datname = 'market' and usename = 'market_user' and state = 'active' and query like 'COPY%'

启动备用节点

gpinitstandby -n

卸载集群

su - gpadmin
source ~/.bashrc && (echo "yes";sleep 3;echo "yes")|gpdeletesystem -B 8 -f

查杀任务

-- 查看空闲资源
select * from pg_stat_activity where state = 'idle' and usename = 'hr';
-- 查杀任务
select pg_terminate_backend(pid) from pg_stat_activity where state = 'idle' and usename = 'hr';

资源队列


-- 查看有哪些资源队列
select DISTINCT(rsqname) from pg_resqueue_attributes;

-- 查看资源队列参数
select * from pg_resqueue_attributes where rsqname = 'ysdata';

-- 创建资源队列
PRIORITY={MIN|LOW|MEDIUM|HIGH|MAX}
CREATE RESOURCE QUEUE intelliv WITH (ACTIVE_STATEMENTS=150,MEMORY_LIMIT='50GB',PRIORITY=MEDIUM);

-- 修改资源队列
ALTER RESOURCE QUEUE ysdata WITH (ACTIVE_STATEMENTS=150,MEMORY_LIMIT='50GB',PRIORITY=MEDIUM);

-- 指派资源队列
ALTER ROLE intelliv0629 RESOURCE QUEUE intelliv;

-- 移除用户
ALTER ROLE role_name RESOURCE QUEUE none;

-- 删除资源队列
DROP RESOURCE QUEUE intelliv;

-- 查看用户和所属队列
SELECT rolname, rsqname FROM pg_roles, gp_toolkit.gp_resqueue_status WHERE pg_roles.rolresqueue=gp_toolkit.gp_resqueue_status.queueid;

-- 查看资源队列相关使用情况：
SELECT * FROM gp_toolkit.gp_resqueue_status;






-- 用户访问ip查询
select client_addr,count(*) as a from pg_stat_activity  where usename = 'intelliv_prod' group by client_addr order by a desc ;
-- 查看队列参数
select * from pg_resqueue_attributes where resname= 'memory_limit';
-- 修改队列参数
ALTER RESOURCE QUEUE intelliv_prod WITH (MEMORY_LIMIT='30GB'); 

-- 查看资源队列参数
select * from pg_resqueue_attributes;
-- 查看用户和所属队列
SELECT rolname, rsqname FROM pg_roles, gp_toolkit.gp_resqueue_status WHERE pg_roles.rolresqueue=gp_toolkit.gp_resqueue_status.queueid;
-- 查看资源队列相关使用情况：
SELECT * FROM gp_toolkit.gp_resqueue_status;
-- 查看资源队列统计信息：
SELECT * FROM pg_stat_resqueues;
-- 查询角色分配的资源队列：
SELECT * from gp_toolkit.gp_resq_role;
-- 查询资源队列中的等待查询：
SELECT * FROM gp_toolkit.gp_locks_on_resqueue WHERE lorwaiting='true';

初始化zs权限

alter user gpadmin with password 'gp@yonyou#123';
CREATE DATABASE datalake_intellit OWNER zs ENCODING 'UTF8';
GRANT ALL PRIVILEGES ON DATABASE datalake_intellit TO zs WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON PROTOCOL gphdfs TO zs;
GRANT ALL PRIVILEGES ON PROTOCOL gphdfs TO gpadmin;

初始化sjyd权限

CREATE USER sjyd WITH PASSWORD 'sjyd@yonyou.com';
GRANT ALL PRIVILEGES ON PROTOCOL gphdfs TO sjyd;

greenplum开设账户

--创建用户
CREATE USER zhangruochi WITH PASSWORD 'ANfPeeoJEuHjQKWW' CONNECTION LIMIT 20;
--创建数据库
CREATE DATABASE yyfxdb OWNER zhangruochi ENCODING 'UTF8';
--为用户给数据库赋权
GRANT ALL PRIVILEGES ON database yyfxdb TO zhangruochi;

修改用户权限

ERROR: permission denied: “gp_segment_configuration” is a system catalog
set allow_system_table_mods='dml';

查看greenplum中用户拥有的表权限

select * from pg_tables where tableowner =  'xiaoming';

查询对应用户的连接

- 停止 查杀
select pg_terminate_backend(procpid) from pg_stat_activity where current_query='<IDLE>' and usename = 'xiezn' ;

- 查找
select client_addr,count(*) from pg_stat_activity where current_query='<IDLE>' and usename = 'xiezn' group by client_addr;

撤销超级管理员权限

ALTER ROLE manager_01 WITH  NOSUPERUSER;

修改gpadmin管理密码

alter user gpadmin with password 'gp@yonyou#123';

调优参考

https://www.cnblogs.com/kuang17/p/9968415.html

[gpadmin@Hadoop-node-02-iuap-idc gpseg-1]$ gpconfig --show max_connections
Values on all segments are consistent
GUC          : max_connections
Master  value: 4000
Segment value: 750
[gpadmin@Hadoop-node-02-iuap-idc gpseg-1]$ gpconfig --show work_mem
Values on all segments are consistent
GUC          : work_mem
Master  value: 32MB
Segment value: 32MB
[gpadmin@Hadoop-node-02-iuap-idc gpseg-1]$ gpconfig -c work_mem  -v 128MB
20190128:16:15:10:002469 gpconfig:Hadoop-node-02-iuap-idc:gpadmin-[INFO]:-completed successfully with parameters '-c work_mem -v 128MB'
[gpadmin@Hadoop-node-02-iuap-idc gpseg-1]$ gpconfig --show mainteance_work_mem
20190128:16:15:56:003380 gpconfig:Hadoop-node-02-iuap-idc:gpadmin-[ERROR]:-Failed to retrieve GUC information, guc does not exist: mainteance_work_mem
'NoneType' object is not iterable
[gpadmin@Hadoop-node-02-iuap-idc gpseg-1]$ gpconfig --s maintenance_work_mem
Usage: gpconfig [options]

gpconfig: error: ambiguous option: --s (--show, --skipvalidation?)
[gpadmin@Hadoop-node-02-iuap-idc gpseg-1]$ gpconfig -s maintenance_work_mem
Values on all segments are consistent
GUC          : maintenance_work_mem
Master  value: 64MB
Segment value: 64MB
[gpadmin@Hadoop-node-02-iuap-idc gpseg-1]$ gpconfig -c maintenance_work_mem  -v 512MB 
20190128:16:17:47:004972 gpconfig:Hadoop-node-02-iuap-idc:gpadmin-[INFO]:-completed successfully with parameters '-c maintenance_work_mem -v 512MB'
[gpadmin@Hadoop-node-02-iuap-idc gpseg-1]$ gpconfig -s max_statement_mem
Values on all segments are consistent
GUC          : max_statement_mem
Master  value: 2000MB
Segment value: 2000MB
[gpadmin@Hadoop-node-02-iuap-idc gpseg-1]$ gpconfig -c statement_mem  -v 256MB
20190128:16:18:40:005704 gpconfig:Hadoop-node-02-iuap-idc:gpadmin-[INFO]:-completed successfully with parameters '-c statement_mem -v 256MB'
[gpadmin@Hadoop-node-02-iuap-idc gpseg-1]$ gpconfig -s gp_vmem_protect_limit
Values on all segments are consistent
GUC          : gp_vmem_protect_limit
Master  value: 8192
Segment value: 8192
[gpadmin@Hadoop-node-02-iuap-idc gpseg-1]$ gpconfig -s gp_workfile_limit_files_per_query
Values on all segments are consistent
GUC          : gp_workfile_limit_files_per_query
Master  value: 100000
Segment value: 100000
[gpadmin@Hadoop-node-02-iuap-idc gpseg-1]$ gpconfig -s effective_cache_size
Values on all segments are consistent
GUC          : effective_cache_size
Master  value: 512MB
Segment value: 512MB
[gpadmin@Hadoop-node-02-iuap-idc gpseg-1]$ gpconfig -s shared_buffers
Values on all segments are consistent
GUC          : shared_buffers
Master  value: 125MB
Segment value: 125MB
[gpadmin@Hadoop-node-02-iuap-idc gpseg-1]$ gpconfig -c effective_cache_size  -v 10240MB
20190128:16:23:54:008887 gpconfig:Hadoop-node-02-iuap-idc:gpadmin-[INFO]:-completed successfully with parameters '-c effective_cache_size -v 10240MB'
[gpadmin@Hadoop-node-02-iuap-idc gpseg-1]$ gpconfig -s shared_buffers
Values on all segments are consistent
GUC          : shared_buffers
Master  value: 125MB
Segment value: 125MB
[gpadmin@Hadoop-node-02-iuap-idc gpseg-1]$ gpconfig -s gp_resqueue_priority_cpucores_per_segment
Values on all segments are consistent
GUC          : gp_resqueue_priority_cpucores_per_segment
Master  value: 4
Segment value: 4
[gpadmin@Hadoop-node-02-iuap-idc gpseg-1]$ gpconfig -s gp_resqueue_priority_cpucores_per_segment
Values on all segments are consistent
GUC          : gp_resqueue_priority_cpucores_per_segment
Master  value: 4
Segment value: 4

# 总核数 = 物理CPU个数 X 每颗物理CPU的核数 
# 总逻辑CPU数 = 物理CPU个数 X 每颗物理CPU的核数 X 超线程数
# 查看物理CPU个数
cat /proc/cpuinfo| grep "physical id"| sort| uniq| wc -l
# 查看每个物理CPU中core的个数(即核数)
cat /proc/cpuinfo| grep "cpu cores"| uniq
# 查看逻辑CPU的个数
cat /proc/cpuinfo| grep "processor"| wc -l

gpconfig -c gp_resqueue_priority_cpucores_per_segment  -v 40

[gpadmin@Hadoop-node-02-iuap-idc pg_log]$ gpconfig -s max_connections
Values on all segments are consistent
GUC          : max_connections
Master  value: 4000
Segment value: 750

gpconfig -c max_connections -v 3200 -m 800
#段实例上的参数必须大于master的参数值。Pivotal建议段实例的max_connections的值是master的5-10倍。

[gpadmin@Hadoop-node-02-iuap-idc pg_log]$ gpconfig -s max_prepared_transactions
Values on all segments are consistent
GUC          : max_prepared_transactions
Master  value: 250
Segment value: 250

gpconfig -c max_prepared_transactions  -v 300

max_prepared_transactions (integer)
# 这个参数只有在启动数据库时，才能被设置。它决定能够同时处于prepared状态的事务的最大数目（参考PREPARE TRANSACTION命令）。如果它的值被设为0。则将数据库将关闭prepared事务的特性。它的值通常应该和max_connections的值一样大。每个事务消耗600字节(b)共享内存。

配置参考

记一次GreenPlum性能调优
在部署了的GreenPlum集群中进行数据查询时，发现数据量一旦大了，查询一跑就中断，提示某个segment中断了连接。

ERROR 58M01 "Error on receive from seg0 slice1 192.168.110.84:6000 pid=xxx: server closed the connection unexpectedly"
 
This probably means the server terminated abnormally before or while processing the request
 

查看master的pg_log中的日志：

1
seg-1 could not connect to segment: initalization of segwork group failed (cdbgang.c:237)
 

经过简单的分析可以猜测是内存相关参数没有优化好，因为同样的SQL查询小一点时间间隔能很快出结果。那么就从两个方面着手：

1.服务器的内存不够，导致segment中断了交互。

2.数据库参数设置不合理，导致用不了那么多内存。

 

针对第一个问题：

1）首先排查虚拟机的内存，发现只有8G，感觉有点少，于是加到了32G，仍然出现之前的问题。

2）那么猜想是不是系统设置的限制了内存的使用？于是使用：

　　prctl -n project.max-shm-memory -i project default

　　prctl -n project.max-sem-ids -i project default

查看了相应的设置，都很大，不需要修改。因此可以排除是系统的瓶颈，接下来查看数据库的：

 

1）直接打开master节点的postgresql.conf文件查看配置，发现shared_buffers及所有的参数都是默认值，优化后。重启数据库仍然有上述问题。

2）查看max_statement_mem、statement_mem、gp_vmem_protect_limit三个参数，发现在master上没有设置，估计这里有问题。

3）使用gpconfig -s statement_mem报错，提示节点中断连接。感觉这个错误报的非常蛋疼，混淆了思路，以为数据节点有误。

4）确定gp_vmem_protect_limit参数，发现是8GB，完全够了。一度陷入停滞，经过查资料和对白配置文件，发现statement_mem参数在conf文件中没有，那么很可能就是默认值了，于是进了一步操作。

5）打开数据库，执行select * from gp_settings; 查看数据库的所有配置，发现statement_mem参数为默认的128MB，因此可以肯定问题了。

6）由于gpconfig查询参数失败，就没有尝试使用gpconfig来设置参数，采用手动修改每个节点的值。重启数据库后，成功解决问题。

后记，后来通过pgconfig -c statement_mem -v 2GB发现是可以设置参数的，就是-s来查询的时候报错。rlg，手动改了那么多文件完全可以很简单搞定的······

 

Greenplum参数配置优化:

查询参数
gpconfig --show max_connections
修改参数配置命令
gpconfig-c <parameter name> -v <parameter value>

比如：gpconfig-c log_statement -v DDL

删除配置
gpconfig -r <parameter name> 
work_mem
work_mem（,global,物理内存的2%-4%）,segment用作sort,hash操作的内存大小
当PostgreSQL对大表进行排序时，数据库会按照此参数指定大小进行分片排序，将中间结果存放在临时文件中，这些中间结果的临时文件最终会再次合并排序，所以增加此参数可以减少临时文件个数进而提升排序效率。当然如果设置过大，会导致swap的发生，所以设置此参数时仍需谨慎。

查看现有配置值
gpconfig -s work_mem 

Values on all segments are consistent
GUC          : work_mem
Master  value: 32MB
Segment value: 32MB

修改配置
gpconfig -c work_mem  -v 128MB

另一种写法：SET work_mem TO '64MB'

配置成功返回：
gpadmin-[INFO]:-completed successfully with parameters 
mainteance_work_mem（
global，CREATE INDEX, VACUUM等时用到,segment用于VACUUM,CREATE INDEX等操作的内存大小，缺省是16兆字节(16MB)。因为在一个数据库会话里， 任意时刻只有一个这样的操作可以执行，并且一个数据库安装通常不会有太多这样的工作并发执行， 把这个数值设置得比work_mem更大是安全的。 更大的设置可以改进清理和恢复数据库转储的速度。

查看现有配置值
gpconfig -s  maintenance_work_mem
GUC          : maintenance_work_mem
Master  value: 64MB
Segment value: 64MB

修改配置
gpconfig -c maintenance_work_mem  -v 256MB  
max_statement_mem
设置每个查询最大使用的内存量，该参数是防止statement_mem参数设置的内存过大导致的内存溢出.

查看现有配置值
gpconfig -s max_statement_mem

Values on all segments are consistent
GUC          : max_statement_mem
Master  value: 2000MB
Segment value: 2000MB

修改配置
gpconfig -c max_statement_mem  -v 2000MB
statement_mem
设置每个查询在segment主机中可用的内存，该参数设置的值不能超过max_statement_mem设置的值，如果配置了资源队列，则不能超过资源队列设置的值。 

查看现有配置值
gpconfig -s statement_mem
Values on all segments are consistent
GUC          : statement_mem
Master  value: 125MB
Segment value: 125MB

修改配置
gpconfig -c statement_mem  -v 256MB
gp_vmem_protect_limit
控制了每个segment数据库为所有运行的查询分配的内存总量。如果查询需要的内存超过此值，则会失败。

查看现有配置值
gpconfig -s gp_vmem_protect_limit
Values on all segments are consistent
GUC          : gp_vmem_protect_limit
Master  value: 8192
Segment value: 8192
gp_workfile_limit_files_per_query
SQL查询分配的内存不足，Greenplum数据库会创建溢出文件（也叫工作文件）。在默认情况下，一个SQL查询最多可以创建 100000 个溢出文件，这足以满足大多数查询。 
该参数决定了一个查询最多可以创建多少个溢出文件。0 意味着没有限制。限制溢出文件数据可以防止失控查询破坏整个系统。 

查看现有配置值
gpconfig -s gp_workfile_limit_files_per_query
Values on all segments are consistent
GUC          : gp_workfile_limit_files_per_query
Master  value: 100000
Segment value: 100000
gp_statement_mem
服务器配置参数 gp_statement_mem 控制段数据库上单个查询可以使用的内存总量。如果语句需要更多内存，则会溢出数据到磁盘。

effective_cache_size
（master节点，可以设为物理内存的85%）
这个参数告诉PostgreSQL的优化器有多少内存可以被用来缓存数据，以及帮助决定是否应该使用索引。这个数值越大，优化器使用索引的可能性也越大。因此这个数值应该设置成shared_buffers加上可用操作系统缓存两者的总量。通常这个数值会超过系统内存总量的50%以上。

查看现有配置值：
gpconfig -s effective_cache_size
Values on all segments are consistent
GUC          : effective_cache_size
Master  value: 512MB
Segment value: 512MB

修改配置
gpconfig -c effective_cache_size  -v 40960MB
gp_resqueue_priority_cpucores_per_segment
master和每个segment的可以使用的cpu个数,每个segment的分配线程数;

查看现有配置值
gpconfig -s gp_resqueue_priority_cpucores_per_segment
Values on all segments are consistent
GUC          : gp_resqueue_priority_cpucores_per_segment
Master  value: 4
Segment value: 4

gpconfig -s  checkpoint_segments  

修改配置
gpconfig -c gp_resqueue_priority_cpucores_per_segment  -v 8
max_connections
最大连接数，Segment建议设置成Master的5-10倍。
max_connections = 200 #(master、standby)
max_connections = 1200 #(segment)

查看现有配置值：
gpconfig -s max_connections

GUC          : max_connections
Master  value: 250
Segment value: 750

修改配置
gpconfig -c max_connections -v 1200 -m 300
max_prepared_transactions
这个参数只有在启动数据库时，才能被设置。它决定能够同时处于prepared状态的事务的最大数目（参考PREPARE TRANSACTION命令）。如果它的值被设为0。则将数据库将关闭prepared事务的特性。它的值通常应该和max_connections的值一样大。每个事务消耗600字节(b)共享内存。

查看现有配置值：
gpconfig -s max_prepared_transactions
Values on all segments are consistent
GUC          : max_prepared_transactions
Master  value: 250
Segment value: 250
修改配置
gpconfig -c max_prepared_transactions  -v 300
max_files_per_process
设置每个服务器进程允许同时打开的最大文件数目。缺省是1000。 如果内核强制一个合理的每进程限制，那么你不用操心这个设置。 但是在一些平台上(特别是大多数BSD系统)， 内核允许独立进程打开比个系统真正可以支持的数目大得多得文件数。 如果你发现有"Too many open files"这样的失败现像，那么就尝试缩小这个设置。 这个值只能在服务器启动的时候设置。

查看现有配置值：
gpconfig -s max_files_per_process
Values on all segments are consistent
GUC          : max_files_per_process
Master  value: 1000
Segment value: 1000
修改配置
gpconfig -c max_files_per_process -v 1000
shared_buffers
只能配置segment节点，用作磁盘读写的内存缓冲区,开始可以设置一个较小的值，比如总内存的15%，然后逐渐增加，过程中监控性能提升和swap的情况。

gpconfig -s shared_buffers
Values on all segments are consistent
GUC          : shared_buffers
Master  value: 64MB
Segment value: 125MB

修改配置
gpconfig -c shared_buffers -v 1024MB

gpconfig -r shared_buffers -v 1024MB
temp_buffers: 即临时缓冲区，拥有数据库访问临时数据，GP中默认值为1M，在访问比较到大的临时表时，对性能提升有很大帮助。
查看现有配置值：
gpconfig -s temp_buffers
Values on all segments are consistent
GUC          : temp_buffers
Master  value: 1024
Segment value: 1024

修改配置
gpconfig -c temp_buffers -v 4096
gp_fts_probe_threadcount:
设置ftsprobe线程数，此参数建议大于等于每台服务器segments的数目。

查看现有配置值：
gpconfig -s gp_fts_probe_threadcount
Values on all segments are consistent
GUC          : gp_fts_probe_threadcount
Master  value: 16
Segment value: 16
重启数据库，使参数生效
gpstop -u 重新加载配置文件 postgresql.conf 和 pg_hba.conf