GreenPlum数据库日常维护运维(持续更新)
Tags: DBA脚本GreenPlum日常维护日常运维脚本脚本分享
简介
现在企业客户中搭建的Greenplum集群服务器数量是越来越大,在电信行业和银行业,搭建50台服务器以上的Greenplum集群越来越多。而集群服务器数量越多也就代表故障发生率越高。作为Greenplum的DBA和运维人员,不单只关注Greenplum本身,还要关注集群中各硬件的状况,及时发现及时处理。硬盘状态、阵列卡状态、硬件告警、操作系统告警、空间使用率等都是应关注的重点。这些都可通过厂商提供的工具,编写监控程序,SNMP协议对接企业监控平台等手段提升日常巡检和监控的效率。
要保持一个Greenplum数据库系统高效运行,必须对数据库定期清理过期数据并且更新表统计信息, 这样查询优化器才能有准确的信息。
Greenplum数据库要求定期执行特定的任务来达到最优性能。这里讨论的任务都是必须的,但数据库管理员可以使用 标准的UNIX工具(如cron脚本)来自动化这些任务。管理员建立适当的脚本并且检查它们是否 成功执行。
日常维护关注点
针对Greenplum,DBA需要关注的重点:
(1)Greenplum的状态:Standby master的同步状态往往容易被忽略。通过监控平台或者脚本程序,能够及时告警则最好。
(2)系统表:日常系统表维护(vacuum analyze),在系统投产时就应该配置好每天执行维护。
(3)统计信息收集:统计信息的准确性影响到运行效率,用户表应该及时收集统计信息。在应用程序中增加收集统计信息的处理逻辑,通过脚本定时批量收集统计信息,或者两者相结合。针对分区表日常可按需收集子分区的统计信息,可节省时间提升效率。
(4)表倾斜:表倾斜情况应该DBA的关注点之一,但无需每天处理。
(5)表膨胀:基于postgresql的MVCC机制,表膨胀情况不能忽视。重点应该关注日常更新和删除操作的表。
(6)报错信息:在日志中错误信息多种多样,大部分不是DBA需要关注的。应该重点关注PANIC、OOM、Internal error等关键信息。
官方文档
一切尽在官方文档中......
http://docs-cn.greenplum.org/v6/homenav.html
https://www.bookstack.cn/read/greenplum-admin_guide-6.0-zh/9ccd913ea8be24b9.md
日常运维查询命令
启动和关闭
数据库启动:gpstart
常用可选参数: -a : 直接启动,不提示终端用户输入确认
-m:只启动master 实例,主要在故障处理时使用
数据库停止:gpstop
常用可选参数:-a:直接停止,不提示终端用户输入确认
-m:只停止master 实例,与gpstart –m 对应使用
-M fast:停止数据库,中断所有数据库连接,回滚正在运行的事务
-u:不停止数据库,只加载pg_hba.conf 和postgresql.conf中运行时参数,当改动参数配置时候使用。
总结
1 2 3 4 5 6 7 8 9 10 11 12 | -- 启动 gpstart -a -- 关闭:默认情况下,如果有任何客户端连接存在,就不允许关闭Greenplum数据库。 使用-M fast选项可以在关闭前回滚所有正在进行中的事务并且中断所有连接。 gpstop -M fast -a -- 重载配置文件 gpstop -u -- 不用输入Y进行确定而直接重启 gpstop -M fast -ra |
查看实例配置和状态
1 | select * from gp_segment_configuration order by 1 ; |
主要字段说明:
Content:该字段相等的两个实例,是一对P(primary instance)和M(mirror Instance)
Isprimary:实例是否作为primary instance 运行
Valid:实例是否有效,如处于false 状态,则说明该实例已经down 掉。
Port:实例运行的端口
Datadir:实例对应的数据目录
集群状态查询
查询命令:gpstate
显示Greenplum数据库运行状态,详细配置等信息
常用可选参数:-c:primary instance 和 mirror instance 的对应关系
-m:只列出mirror 实例的状态和配置信息
-f:显示standby master 的详细信息
-Q:显示状态综合信息
该命令默认列出数据库运行状态汇总信息,常用于日常巡检。
评:最开始由于网卡驱动的问题,做了mirror后,segment经常down掉,用-Q参数查询综合信息还是比较有用的。
查看集群主实例与镜像实例同步状态
查询命令:gpstate -m
Status均为Passive,Data Status均为为Synchronized则正常,否则异常
负载查询
使用vmstat命令可以监控 CPU 使用、进程状态、内存使用、虚拟内存使用、硬盘输入/输出状态等信息,常使用方法为:vmstat [刷新延时 刷新次数]
也可以使用gpcc历史视图查询,参考:https://www.xmmup.com/greenplumguanfangjiankonggongjugpccjianjiejishiyong.html
磁盘空间查询
数据库查看使用量
1 2 3 4 5 | select dfsegment,dfhostname,dfdevice,round(dfspace/1024/1024,2) as free_disk_GB from gp_toolkit.gp_disk_free ORDER BY dfsegment ; -- dfspace 实例所在的文件系统的空闲磁盘空闲空间(千字节为单位) |
系统命令行查看(在此例中“host_list”为所有服务器节点清单,“data”为数据存放目录,根据实际目录文件名查询即可)
1 | gpssh -f host_list -e "df -h |grep data" |
查询数据库、表占用空间大小
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | select pg_size_pretty(pg_relation_size('schema.tablename')); select pg_size_pretty(pg_database_size('databasename')); -- 1.查看所有库大小 select sodddatname,sodddatsize/1024/1024 as db_size_MB from gp_toolkit.gp_size_of_database order by sodddatname; -- 2.查看库下面的所有schema占用的磁盘空间 select sosdnsp,round(sosdschematablesize/1024/1024,2) schema_tb_size_MB,round(sosdschemaidxsize /1024/1024,2) as schema_idx_size_MB from gp_toolkit.gp_size_of_schema_disk; -- 单个schema大小 SELECT pg_size_pretty(sum(pg_total_relation_size('"' || nspname || '".' || relname))) AS total_size FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE nspname = 'public'; -- 3.查看schema下所有表和索引占用的磁盘空间 SELECT soatioid,soatischemaname,soatitablename,round(soatisize/1024/1024,2) as INDEX_SIZE_MB FROM gp_toolkit.gp_size_of_all_table_indexes order by INDEX_SIZE_MB desc,soatischemaname,soatitablename ; -- 4.查看表占用空间(表大小) select (pg_relation_size('public.tb_name')); select sotuoid, sotuschemaname,sotutablename,round(cast(sotusize as integer )/1024/1024,2) as table_SIZE_MB from gp_toolkit.gp_size_of_table_uncompressed order by sotuschemaname, table_SIZE_MB desc,sotutablename -- 5.查看膨胀表 select * from gp_toolkit.gp_bloat_diag order by bdinspname,(bdirelpages-bdiexppages)desc,bdirelname; -- 没有统计信息且可能需要ANALYZE的表。 select * from gp_toolkit.gp_stats_missing order by smischema,smicols desc; |
必须在数据库所对应的存储系统里,至少保留30%的自由空间,日常巡检,要检查存储空间的剩余容量。
评:可以查看任何数据库对象的占用空间,pg_size_pretty可以显示如mb之类的易读数据,另外,可以与pg_tables,pg_indexes之类的系统表链接,统计出各类关于数据库对象的空间信息。
查询数据库大小
参考:https://www.xmmup.com/greenplumchaxunshujukudaxiao.html
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate", d.datctype as "Ctype", pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges", CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname)) ELSE 'No Access' END as "Size", t.spcname as "Tablespace", pg_catalog.shobj_description(d.oid, 'pg_database') as "Description" FROM pg_catalog.pg_database d JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid UNION ALL SELECT '总计' as "Name", '' as "Owner", '' as "Encoding", '' as "Collate", '' as "Ctype", '' AS "Access privileges", pg_catalog.pg_size_pretty(SUM((pg_catalog.pg_database_size(d.datname)))) as "Size", '' as "Tablespace", '' as "Description" FROM pg_catalog.pg_database d JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid ORDER BY 1; -- 或 select sodddatname,sodddatsize/1024/1024 as db_size_MB from gp_toolkit.gp_size_of_database d order by d.sodddatname ; -- 或 \l+ |
表占用空间大小
1 2 3 4 5 6 7 8 9 | select pg_size_pretty(pg_total_relation_size('test')); select pg_size_pretty(pg_relation_size('test')); select pg_size_pretty(pg_table_size('test')); select pg_size_pretty(pg_indexes_size('test')); SELECT relname as name, sotdsize/1024/1024 as size_MB, sotdtoastsize as toast, sotdadditionalsize as other FROM gp_toolkit.gp_size_of_table_disk as sotd, pg_class WHERE sotd.sotdoid = pg_class.oid ORDER BY relname; |
索引占用空间大小
1 2 3 4 5 6 7 8 9 | select pg_size_pretty(pg_total_relation_size('test')); select pg_size_pretty(pg_relation_size('test')); select pg_size_pretty(pg_table_size('test')); select pg_size_pretty(pg_indexes_size('test')); SELECT soisize/1024/1024 as size_MB, relname as indexname FROM pg_class, gp_toolkit.gp_size_of_index WHERE pg_class.oid = gp_size_of_index.soioid AND pg_class.relkind='i'; |
分区表大小
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | -- 分区表信息和大小 select * from ( SELECT pt.schemaname,pt.tablename,pt.partitiontype,pt.partitionschemaname,pt.partitiontablename,pt.partitionname,pt.partitionposition||'' partitionposition,pt.partitionlistvalues,pt.partitionlevel||'' partitionlevel,pt.partitionboundary, pg_size_pretty( pg_total_relation_size('"' || pt.partitionschemaname || '"."' || pt.partitiontablename || '"')) pt_tb_size, pg_size_pretty( pg_indexes_size('"' || pt.partitionschemaname || '"."' || pt.partitiontablename || '"')) pt_index_size FROM pg_partitions pt union ALL SELECT pt.schemaname,pt.tablename,pt.partitiontype,'','','','','','','', pg_size_pretty(sum(pg_total_relation_size('"' || pt.partitionschemaname || '"."' || pt.partitiontablename || '"'))) pt_tb_size, pg_size_pretty(sum(pg_indexes_size('"' || pt.partitionschemaname || '"."' || pt.partitiontablename || '"'))) pt_index_size FROM pg_partitions pt GROUP BY pt.schemaname,pt.tablename,pt.partitiontype ) aa WHERE aa.tablename='topiclog_month' order by aa.partitionname ; |
查看GreenPlum表的分布键和压缩类型
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | SELECT n.nspname AS "schema", C.relname AS "tb_name", CASE WHEN P.policytype = 'p' THEN '分区策略' WHEN P.policytype = 'r' THEN '复制策略' END "policytype", P.numsegments, P.distkey, pg_get_table_distributedby ( C.oid ) "distributed_key", C.reloptions, case c.relstorage when 'a' then ' append-optimized' when 'c' then 'column-oriented' when 'h' then 'heap' when 'v' then 'virtual' when 'x' then 'external table' end as "data_storage_mode" FROM pg_class C LEFT JOIN gp_distribution_policy P ON C.oid = P.localoid LEFT JOIN pg_namespace n ON C.relnamespace = n.oid where n.nspname NOT IN ( 'pg_catalog', 'pg_toast','information_schema','gp_toolkit' ) and C.relkind in ('r','m') and P.policytype is not null order by n.nspname,C.relname; |
查询对象类型和个数
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | select nsp.nspname as SchemaName ,case cls.relkind when 'r' then 'TABLE' when 'm' then 'MATERIALIZED_VIEW' when 'i' then 'INDEX' when 'S' then 'SEQUENCE' when 'v' then 'VIEW' when 'c' then 'composite type' when 't' then 'TOAST' when 'f' then 'foreign table' when 'p' then 'partitioned_table' when 'I' then 'partitioned_index' else cls.relkind::text end as ObjectType, COUNT(*) cnt from pg_class cls join pg_namespace nsp on nsp.oid = cls.relnamespace where nsp.nspname not in ('information_schema', 'pg_catalog','gp_toolkit') and nsp.nspname not like 'pg_toast%' GROUP BY nsp.nspname,cls.relkind UNION all SELECT n.nspname as "Schema", 'pg_proc' as "pg_proc", COUNT(*) cnt FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE pg_catalog.pg_function_is_visible(p.oid) AND n.nspname not in ('information_schema', 'pg_catalog','gp_toolkit') GROUP BY n.nspname order by SchemaName,ObjectType; |
查看表数据分布情况(数据倾斜)
参考:https://www.xmmup.com/greenplumxingnengdiaoyouzhishujuqingxie.html
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | -- 用count(*)方式计算每个segment上的记录数,慢,不建议 select gp_segment_id, count(*) from table_name group by 1 order by 1; -- 计算一张表在不同segment上所占空间来评估是否发生数据倾斜,推荐 select gp_segment_id, pg_size_pretty(pg_relation_size('table_name')) from gp_dist_random('gp_id') order by 1; -- 真实大小 select pg_size_pretty(sum(tbsize)) from ( select gp_segment_id, (pg_relation_size('table_name')) tbsize from gp_dist_random('gp_id') order by 1) a; -- 该视图通过计算存储在每个Segment上的数据的变异系数(CV)来显示数据分布倾斜。 select * from gp_toolkit.gp_skew_coefficients; -- 该视图通过计算在表扫描过程中系统空闲的百分比来显示数据分布倾斜,这是一种数据处理倾斜的指示器。 select * from gp_toolkit.gp_skew_idle_fractions; 在命令运行: gpskew -t public.ate -a postgres -- 如果表的分布键合理,直接重分布数据: alter table table_name set with(reorganize=true); -- 指定分步键重新平衡 alter table table_name set distributed by (col1,col2...); -- 随机分布 alter table bi.fat_zy_cwsyqk set distributed randomly; |
如数据分布不均匀,将发挥不了并行计算的优势,严重影响性能。
评:非常有用,gp要保障数据分布均匀。
分步键的选择参数gp_create_table_random_default_distribution
参考:https://www.xmmup.com/greenplumchuangjianheguanlibiao.html#can_shugp_create_table_random_default_distribution
查看数据库数据分布情况(数据倾斜)
1 | select gp_segment_id,pg_size_pretty(pg_catalog.pg_database_size('DW')) from gp_dist_random('gp_id') order by 1; |
会话、锁信息管理
会话查询、慢查询SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 | SELECT pgsa.pid, pgsa.sess_id, pgsa.client_port, pgsa.datname AS datname, pgsa.usename AS usename, (SELECT nb.rrrsqname from gp_toolkit.gp_resq_role nb where nb.rrrolname=pgsa.usename) rsqname, pgsa.client_addr client_addr, pgsa.application_name AS application_name, pgsa.state AS state, pgsa.waiting, pgsa.waiting_reason, pgsa.backend_start AS backend_start, pgsa.xact_start AS xact_start, state_change, pgsa.query_start AS query_start, TRUNC(EXTRACT( epoch FROM (NOW() - pgsa.xact_start) )) AS xact_stay, trunc(EXTRACT( epoch FROM (NOW() - pgsa.query_start) )) AS query_stay, REPLACE(pgsa.QUERY, chr(10), ' ') AS QUERY, yc_filesize_GB, yc_numfiles, 'select pg_terminate_backend('||pgsa.pid||');' kill1, 'select pg_cancel_backend('||pgsa.pid||');' kill2 FROM pg_stat_activity AS pgsa -- 溢出文件大小查询 LEFT JOIN (select pid,trunc(sum(size)/1024/1024/1024) as yc_filesize_GB,sum(numfiles) as yc_numfiles from gp_toolkit.gp_workfile_usage_per_query nb group by pid) as d ON pgsa.pid=d.pid WHERE pgsa.state not in ( 'idle' ,'idle in transaction','idle in transaction (aborted)' ) ORDER BY query_stay,xact_stay DESC LIMIT 100; -- 会话占用内存 CREATE EXTENSION gp_internal_tools; SELECT * FROM session_state.session_level_memory_consumption d WHERE d.datname IS NOT NULL ORDER BY d.vmem_mb DESC, d.sess_id, d.segid LIMIT 20; select datid, --数据库OID datname, --数据库名 pid, --后台服务器进程号(即对应该会话在master节点操作系统进程ID号) sess_id, --会话线程号 usesysid, --role ID号 usename, --role名称 client_addr, --客户端地址 client_port, --客户端端口 application_name, --客户端应用名称 substr(query,1,80) --请求SQL,较长SQL截取部分 query_start, --请求开始时间 backend_start, --backend进程开始时间,即用户连接数据库时建立会话的时间 xact_start, --事务开始时间 waiting, --是否等待True if waiting on a lock, false if not waiting waiting_reason --等待原因. The value can be:lock, replication, or resgroup from pg_stat_activity where state<>'idle'; |
pg_stat_activity
该表能查看到当前数据库连接的IP 地址,用户名,提交的查询等。
另外也可以在master 主机上查看进程,对每个客户端连接,master 都会创建一个进程。ps -ef |grep -i postgres |grep -i con
锁查询
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 | SELECT * FROM pg_locks d where d.pid in (192019,191474) order by pid; select * from gp_toolkit.gp_locks_on_relation ; SELECT pg_locks.pid as pid, --进程ID transactionid as transaction_id, --事务ID pd.datname db_name, nspname as schemaname, --schema名 relname as object_name, --对象名 locktype as lock_type, --锁类型 mode lock_mode, --锁模式 CASE WHEN granted = 'f' THEN 'get_lock' WHEN granted = 't' THEN 'wait_lock' END lock_satus, --锁状态:持有锁|等待锁 CASE WHEN waiting = 'false' THEN 'already get lock,sql executing' WHEN waiting = 'true' THEN 'waiting get lock,sql waiting execute' END waitting_satus, --当前会话状态:执行中|等待中 least(query_start, xact_start) AS query_start, --query请求开始时间 substr(query, 1, 25) AS query_text --当前SQL语句 FROM pg_locks left join pg_database pd on (pg_locks.database=pd.oid) LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid) LEFT OUTER JOIN pg_namespace ON (pg_namespace.oid = pg_class.relnamespace), pg_stat_activity WHERE NOT pg_locks.pid = pg_backend_pid() AND pg_locks.pid = pg_stat_activity.pid ORDER BY query_start; -- 正被锁定的关系 select lorlocktype 被锁的关系的类型, lordatabase 对象标识符_共享对象为0, lorrelname 关系的名字, lorrelation 关系的对象标识符, lortransaction 锁影响到的事务标识符, lorpid 获取或等待锁的服务器进程号_准备型0, lormode 锁模式, lorgranted 锁是T否F, lorcurrentquery 会话当前的查询 from gp_toolkit.gp_locks_on_relation; select lorusename 当前会话用户, lorrsqname 资源队列, lorlocktype 锁对象类型, lorobjid 锁定的事务对象标识符, lortransaction 锁影响到的事务标识符, lorpid 锁影响到的事务的服务器进程号, lormode 锁模式的名字, lorgranted 锁是True否alse, lorwaiting 会话是否正在等待 from gp_toolkit.gp_locks_on_resqueue; -- GP 6.0不能使用了 SELECT locktype, database, c.relname, l.relation, l.transactionid, l.transaction, l.pid, l.mode, l.granted, a.current_query FROM pg_locks l, pg_class c, pg_stat_activity a WHERE l.relation=c.oid AND l.pid=a.procpid ORDER BY c.relname; SELECT max_conn 最大连接数, now_conn 当前连接数, max_conn - now_conn 剩余连接数 FROM ( SELECT setting::int8 AS max_conn, ( SELECT COUNT (*) FROM pg_stat_activity ) AS now_conn FROM pg_settings WHERE NAME = 'max_connections' ) T; -- 杀会话 select 'select pg_terminate_backend('||a.pid||');' kill1, 'select pg_cancel_backend('||a.pid||');' kill2 from pg_stat_activity a where pid<>pg_backend_pid() and state in ( 'idle'); |
会话kill、停止会话、杀会话
当报Too many sessions会话数满或数据库无响应或事务被锁需要中断锁源头时,可以使用杀会话方式临时解决。优先建议使pg_cancel_backend()函数(长事务会话需要等待较长时间中断),在无法等待较长时间等待时,可pg_terminate_backend()函数(该方式杀会话有极小概率导致系统元数据不一致)。在数据库响应等极端情况可,方可考虑使用 kill -9 pid进行强制会话进程中断,一般先使用 kill -15 pid终端会话进程。
区别:pg_cancel_backend 只是取消当前某一个进程的查询操作,但不能释放数据库连接。但pg_terminate_backend可以在pg的后台杀死这个进程,从而释放出宝贵的连接资源,类似于kill -9 pid操作。
正常取消会话
1 | select pg_cancel_backend(pid); |
强制中断会话
1 | select pg_terminate_backend(pid); |
其中:pid为pg_stat_activity视图中的pid查询结果,如:
1 2 3 4 5 6 7 8 9 10 | gpdw=# select pg_cancel_backend(2243); pg_cancel_backend ------------------- t (1 row) gpdw=# select pg_terminate_backend(2243); pg_terminate_backend ---------------------- t (1 row) |
查询空闲会话ID并使用杀会话方式进行会话资源释放。
1 | select pid from pg_stat_activity where state='idle'; |
查询某时间段之后的会话
1 | select pid from pg_stat_activity where query_start>CAST('2022-06-26 11:12:10' AS TIMESTAMP); |
查询30分钟内会话
1 | select * from pg_stat_activity where backend_start>(now() - interval '30 min' |
操作系统层面查看idle会话进程 kill后台进程
1 | ps -ef |grep -i postgres |grep -i idle|grep -v grep | awk '{print $2}' |xargs kill -15 |
登陆与退出Greenplum
1 2 3 4 5 6 7 8 9 10 11 12 13 | #正常登陆 psql gpdb psql -d gpdb -h gphostm -p 5432 -U gpadmin #使用utility方式 PGOPTIONS="-c gp_session_role=utility" psql -h -d dbname hostname -p port #退出 在psql命令行执行q |
参数查询
1 2 3 4 5 | psql -c 'SHOW ALL;' -d gpdb gpconfig --show max_connections SELECT * FROM gp_toolkit.gp_param_settings(); |
这个有用,可以管道给grep。
查询分步键
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | select c.oid, n.nspname as schemaname, c.relname as tablename, pg_get_table_distributedby(c.oid) distributedby, case c.relstorage when 'a' then ' append-optimized' when 'c' then 'column-oriented' when 'h' then 'heap' when 'v' then 'virtual' when 'x' then 'external table' end as "data storage mode" from pg_class as c inner join pg_namespace as n on c.relnamespace = n.oid where n.nspname = 'poc' and c.relstorage = 'c' order by n.nspname, c.relname ; |
在Greenplum中,建表时指定分布键的语法如下:
1 2 3 4 5 6 | cssCopy codeCREATE TABLE table_name ( column1 datatype [optional parameters] [distribution_method], column2 datatype [optional parameters] [distribution_method], ..., CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ...) ) DISTRIBUTED BY (column1, column2, ...); |
其中,DISTRIBUTED BY
子句用于指定分布键,可以指定一个或多个列。一般来说,指定的分布键字段组合的大小应该控制在2-4个左右,这样可以保证分布键的效果,即让数据尽可能均匀地分布到各个节点上,提高查询性能。如果组合的字段过多,可能会导致某些节点上的数据倾斜,进而影响查询性能。
需要注意的是,指定的分布键字段不一定需要是表的主键,但应该是常用于查询和筛选的字段。另外,为了进一步提高查询性能,还可以考虑在表上创建合适的索引。
创建数据库
1 | createdb -h localhost -p 5432 dhdw |
创建GP文件系统
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | 文件系统名 gpfsdw 子节点,视segment数创建目录 mkdir -p /gpfsdw/seg1 mkdir -p /gpfsdw/seg2 chown -R gpadmin:gpadmin /gpfsdw 主节点 mkdir -p /gpfsdw/master chown -R gpadmin:gpadmin /gpfsdw gpfilespace -o gpfilespace_config gpfilespace -c gpfilespace_config |
创建GP表空间
1 2 3 4 5 6 | psql gpdb create tablespace TBS_DW_DATA filespace gpfsdw; SET default_tablespace = TBS_DW_DATA; |
删除GP数据库
1 2 | gpdeletesystem -d /gpmaster/gpseg-1 -f |
查看GP日志
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT * FROM gp_toolkit.__gp_log_master_ext d where d.logseverity not in ('LOG') and d.logtime >= now()-interval '7 day' order by d.logtime desc limit 50; SELECT * FROM gp_toolkit.__gp_log_segment_ext d where d.logseverity not in ('LOG') and d.logtime >= now()-interval '7 day' order by d.logtime desc limit 50; |
OBJECT的DDL操作统计
1 2 3 4 5 6 7 | SELECT schemaname as schema, objname as table, usename as role, actionname as action, subtype as type, statime as time FROM pg_stat_operations WHERE objname = '<name>'; |
队列查询
1 2 | SELECT * FROM pg_resqueue_status; |
gpfdist外部表
参考:https://www.xmmup.com/greenplumzhongdeshujujiazaigongjuzhigpfdisthegpload.html
注意:
1、数据库服务端必须能访问客户端,且
telnet 客户端IP 8081
是通的。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | # 启动服务 mkdir -p /home/gpadmin/txt gpfdist -d /home/gpadmin/txt -p 8081 -l /home/gpadmin/txt/gpfdist.log & # 创建外部表 drop EXTERNAL TABLE test; CREATE EXTERNAL TABLE test ( id int, name text) LOCATION ('gpfdist://127.0.0.1:8081/1.txt') FORMAT 'TEXT' ( DELIMITER '|' NULL '' FILL MISSING FIELDS) SEGMENT REJECT LIMIT 1 percent; select * from test; # 创建普通表 create table test1 select * from test; # 索引 CREATE INDEX idx_test ON test1 USING bitmap (id); # 查询数据 select id , count(*) from test group by id order by count(*); |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | # 创建可写外部表 CREATE WRITABLE EXTERNAL TABLE unload_expenses ( LIKE expenses ) LOCATION ('gpfdist://etlhost-1:8081/expenses1.out', 'gpfdist://etlhost-2:8081/expenses2.out') FORMAT 'TEXT' (DELIMITER ',') DISTRIBUTED BY (exp_id); # 写权限 GRANT INSERT ON writable_ext_table TO <name>; # 写数据 INSERT INTO writable_ext_table SELECT * FROM regular_table; |
gpload导入数据
1 2 3 4 5 6 | # 创建控制文件 # 加载数据 gpload -f my_load.yml |
copy导出导入数据
1 2 3 4 5 6 | COPY country FROM '/data/gpdb/country_data' WITH DELIMITER '|' LOG ERRORS INTO err_country SEGMENT REJECT LIMIT 10 ROWS; COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO '/home/gpadmin/a_list_countries.out'; |
执行sql文件
1 | psql gpdbname –f yoursqlfile.sql |
或者psql登陆后执行
1 | \i yoursqlfile.sql |
查询gp最后vacuum时间
1 2 3 | SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_user_tables ORDER BY last_vacuum; |
磁盘空间不足
1 2 3 4 5 6 7 8 9 10 | 1.vacuum -- 生成 vacuum ANALYZE命令(或者使用 VACUUM FULL) select 'VACUUM FULL' || ' gpmg.' || relname || ';' as vacuum_sql from pg_class a,pg_namespace b where a.relnamespace=b.oid and b.nspname= 'gpmg' and a.relkind='r'; -- 脚本执行命令 -- VCOMMAND="VACUUM ANALYZE" -- psql -tc "select '$VCOMMAND' || ' pg_catalog.' || relname || ';' from pg_class a,pg_namespace b where a.relnamespace=b.oid and b.nspname= 'pg_catalog' and a.relkind='r'" $DBNAME | psql -a $DBNAME 2. 清理日志 -- pg_log目录下并使用约定命名方式:gpdb-YYYY-MM-DD.log |
将查询结果导出到文件
1 2 | nohup psql -c "show all;" >./test.log & -- ps -fe | grep test.log |
添加和删除standby master
添加和删除standby master的命令:
1 2 3 4 5 6 7 8 9 | -- 添加standby master gpinitstandby -s mdw2 -S /opt/greenplum/data/master/gpseg-1 -P 5432 -- 删除standby master gpinitstandby -r -- 查询standby master gpstate -f |
添加segment镜像mirror
1 2 3 4 5 6 7 | -- 添加segment镜像mirror gpssh -h sdw1 -h sdw2 -e "mkdir -p /data/gpdata/seg1/mirror" gpssh -h sdw1 -h sdw2 -e "mkdir -p /data/gpdata/seg2/mirror" gpaddmirrors -o mirror_config cat mirror_config gpaddmirrors -i mirror_config gpstate -m |
需要注意一点的是:greenplum目前只提供添加mirror命令,而没有提供删除mirror命令,不过可以通过其它方式删除,在生产库不建议操作(我做了一次把库搞挂了)
Master节点故障恢复
1、若master故障,则直接在standby master上执行如下命令将standby master激活为主master:
1 | gpactivatestandby -d /opt/greenplum/data/master/gpseg-1 |
若有VIP也需要切换VIP。
然后原来的master节点可以删除数据后,然后作为新的standby加入:
1 | gpinitstandby -a -s mdw1 |
2、若standby master故障,则分情况:若是宕机,其实可以正常启动,使用如下命令正常启动:
1 | nohup /usr/local/greenplum-db-6.23.0/bin/postgres -D /opt/greenplum/data/master/gpseg-1 -p 5432 -E & |
若数据库文件损坏不能正常启动,则需要将standby节点数据删除,然后重新初始化一下standby服务器即可,需要先删除再添加:
1 2 | gpinitstandby -r gpinitstandby -s mdw1 -S /opt/greenplum/data/master/gpseg-1 -P 5432 |
3、master和standby master就是PG中的主从复制,要想实现自动故障转移,则需要借助keepalived或repmgr或patroni或pgpool实现高可用自动切换。
Segment节点故障恢复
当一个primary segment节点故障,那么它所对应的mirror segment节点会自动接替primary的状态,继续保证整个集群的数据完整性
当一个mirror segment节点出现故障,它不会影响整个集群的可用性,但是需要尽快修复,保证所有的primary segment都有备份
如果primary segment 和它所对应的mirror segment 节点都出现故障,那么greenplum认为集群数据不完整,整个集群将不再提供服务,直到primary segment 或 mirror segment恢复
primary segment节点和mirror segment节点的故障修复方式是一样的,都执行如下命令即可:
gprecoverseg -a
恢复方式都一样,若没有业务连接,则可以直接进行恢复:
12345-- 恢复segmentgprecoverseg -a-- 重平衡回到最初状态gprecoverseg -r若有业务连接,则需要关闭和重启集群:
1234567891011121314151617181920212223-- 关闭集群gpstop -a -M fast-- 以restricted方式启动数据库gpstart -a -R-- 修复集群(默认增量,若不能恢复,可以考虑加-F参数进行全量恢复,恢复时间和数据库大小成正比)-- 特别注意:在GP低版本中,这个命令很快就完成了,但是其实恢复还在后台进行,可以使用gpstate -e查看进度gprecoverseg -a-- 检测状态(一直等到Data Status 这个属性全部都是Synchronized即可进行下一步操作)gpstate -m-- 查看恢复的进度gpstate -e-- 重平衡回到最初状态(前提是执行gpstate -m后Data Status 这个属性全部都是Synchronized)-- 执行时间和数据库大小有少量关系gprecoverseg -r-- 重启集群gpstop -a -M fast -r在执行
gprecoverseg
恢复segment后,原有的primary和mirror会发生变动,可能导致所有的primary都在同一台几点,会导致性能问题,所以应该执行命令进行重平衡gprecoverseg -r
就可以让集群回到最初的配置角色了。
扩容GreenPlum系统
1、主要是4步扩容:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | su - gpadmin -- 1、使用gpexpand创建初始化文件,输入Y,然后个回车即可 cd /home/gpadmin/conf gpexpand -f /home/gpadmin/conf/seg_hosts -- 2、利用生成的初始化文件,初始化Segment并且创建扩容schema -- 该步骤若报错,可以修复错误后,再重复运行如下命令 gpexpand -i gpexpand_inputfile_20230313_131204 -- 3、重新分布数据,最长时间1小时 gpexpand -d 1:00:00 -- 4、移除扩容schema gpexpand -c |
2、监控视图,在postgres数据库中:
1 2 3 4 5 | select * from gpexpand.status; select * from gpexpand.status_detail; select * from gpexpand.expansion_progress; gpstate -x |
例行清理和分析
Greenplum数据库中使用的MVCC事务并发模型的设计意味着被删除或者被更新的数据行仍在磁盘上占据物理空间, 即便它们已经对新事务不可见。如果数据库进行了很多更新和删除,会有很多过期行存在并且它们所使用的空间必须使用 VACUUM命令来回收。VACUUM命令还会收集表级的统计信息,例如行数和 页面数,因此即便无需从被更新或者被删除行回收空间,也还是有必要去清理追加优化表。
清理一个追加优化表遵循一种和清理堆表不同的处理逻辑。在每一个Segment上,会创建一个新的Segment文件并且 把可见行从当前Segment复制到该文件中。当Segment文件被拷贝完时,将会安排删除原始的Segment文件并且让新 的Segment文件变得可用。这要求足够的可用磁盘空间用于拷贝可见行,直到原始的Segment文件被删除为止。
如果一个Segment文件中隐藏行和所有行的比率低于一个阈值(默认是10),该Segment文件不会被紧缩。 该阈值可以通过gp_appendonly_compaction_threshold服务器配置参数配置。VACUUM FULL 忽略gp_appendonly_compaction_threshold的值并且不管该比率为多少都会重写Segment文件。
可以使用gp_toolkit
模式中的__gp_aovisimap_compaction_info()函数来查看 追加优化表上的VACUUM操作的效果。
有关__gp_aovisimap_compaction_info()函数的信息请见Greenplum Database Reference Guide “检查追加优化表”部分
可以使用gp_appendonly_compaction服务器配置参数为追加优化表禁用 VACUUM操作。
有关清理数据库的细节请见清理数据库。
有关gp_appendonly_compaction_threshold服务器配置参数和VACUUM 命令的信息请见Greenplum Database Reference Guide。
事务ID管理
Greenplum的MVCC事务机制依赖于比较事务ID(XID)号来判断当前数据对于其他事务的可见性。 事务ID号使用一种模232的算法来比较,因此一个运行了超过二十亿事务的 Greenplum系统可能会遇到事务ID回卷,即过去的事务变成了未来的事务。这意味着过去的事务的 输出变得不可见。因此,每过二十亿个事务就有必要VACUUM每个数据库中的 每个表至少一次。
Greenplum数据库只对涉及DDL或者DML操作的事务分配XID值,通常也只有这些事务需要XID。
Important: Greenplum数据库会监控事务ID。如果没有定期清理数据库,Greenplum 数据库将产生警告和错误。
当事务ID中相当多的一部分变得不再可用并且事务ID回卷还没有发生时,Greenplum数据库会发出 下面的警告:
WARNING: database “database_name” must be vacuumed within number_of_transactions transactions
当这个警告被发出时,需要一次VACUUM操作。如果没有执行VACUUM 操作,当Greenplum数据库在事务ID回卷发生前达到一个限制,它会停止创建新的事务。在停止创建 事务以避免可能的数据丢失时,Greenplum数据库会发出这个错误:
1 | FATAL: database is not accepting commands to avoid wraparound data loss in database "database_name" |
Greenplum数据库配置参数xid_warn_limit控制何时显示该警告。参数 xid_stop_limit控制何时Greenplum数据库停止创建事务。
从一次事务ID限制错误中恢复
当Greenplum数据库由于不频繁的VACUUM维护而达到 xid_stop_limit事务ID限制时,它会变得没有响应。为了从这种情况中 恢复过来,作为数据库管理员执行下面的步骤:
- 关闭Greenplum数据库。
- 临时将xid_stop_limit降低10,000,000。
- 启动Greenplum数据库。
- 在所有受影响的数据库上运行VACUUM FREEZE。
- 将xid_stop_limit重置为原来的值。
- 重启Greenplum数据库。
有关这些配置参数的信息请见Greenplum Database Reference Guide。
有关事务ID回卷的信息请见PostgreSQL documentation.
系统目录维护
多次使用CREATE和DROP命令的数据库更新会增长系统目录尺寸并且 影响系统性能。例如,运行很多次DROP TABLE语句会降低总体系统性能,因为在目录表上 的元数据查询期间会需要更多扫描时间。性能损失会在数千次或者数万次DROP TABLE语句 之间发生,具体时间取决于系统。
应该定期维护系统目录来回收已删除对象所占据的空间。如果长时间没有运行这种定期回收操作,那可能需要运行 一个更彻底的回收操作来清理系统目录。这个主题会描述这两种方式。
常规系统目录维护
推荐周期性地在系统目录上运行REINDEX和VACUUM来清理系统索引和 表中已删除对象所占用的空间。如果常规的数据库操作包括很多DROP语句,那么每天在非峰值 时间用VACUUM命令运行一次系统目录维护是安全且适当的。用户可以在系统可用时执行这种操作。
下面是Greenplum数据库系统目录维护步骤。
在系统表上执行REINDEX命令以重建系统表索引。该操作移除索引膨胀并提高VACUUM 性能。
Note: 当在系统表上执行REINDEX操作时,表上会产生锁,此时可能会对当前正在运行的 查询产生比较大的影响。建议在系统负载较低时执行REINDEX操作,以避免对正在运行的 业务操作产生较大的干扰。
在系统表上执行VACUUM操作。
在系统表上执行ANALYZE操作,用以更新统计信息。
下面的示例脚本在一个Greenplum数据库系统目录上执行一次REINDEX、VACUUM 以及ANALYZE操作: 将脚本中的替换为真实数据库名。
1 2 3 4 5 6 7 | #!/bin/bash DBNAME="<database-name>" SYSTABLES="' pg_catalog.' || relname || ';' FROM pg_class a, pg_namespace b WHERE a.relnamespace=b.oid AND b.nspname='pg_catalog' AND a.relkind='r'" reindexdb --system -d $DBNAME psql -tc "SELECT 'VACUUM' || $SYSTABLES" $DBNAME | psql -a $DBNAME analyzedb -s pg_catalog -d $DBNAME |
Note: 如果在系统维护时间内已经开始了正常的系统目录维护操作,但是由于时间原因想要停止某一维护进程, 此时可以运行Greenplum数据库函数pg_cancel_backend() 以安全停止该Greenplum数据库进程。
深度系统目录维护
如果很长时间都没有执行一次系统目录维护操作,该目录可能因为废弃空间而膨胀。这会导致简单的元数据 操作都会等待很长时间。在psql中用\d命令列出用户表需要超过 两秒的等待,就是目录膨胀的一种征兆。
如果发现系统目录膨胀的征兆,就必须在计划好的停机时段用VACUUM FULL执行一次 深度系统目录维护操作。在这一时段中,停止系统上的所有目录活动,这种VACUUM FULL 系统目录维护过程会对系统目录加排他锁。
运行定期系统目录维护操作可以防止对这种更高开销操作的需求。
以下是深度系统目录维护操作的步骤。
- 停止Greenplum数据库系统上的所有活动元数据操作。
- 在系统表上执行REINDEX操作以重建系统表索引。该操作移除索引上的膨胀并提高 VACUUM操作性能。
- 在系统表上执行VACUUM FULL命令,具体查看下面的注释。
- 在系统表上执行ANALYZE命令以更新系统表的统计信息。
Note: 通常来说,表pg_attribute是最大的系统表。如果pg_attribute 表膨胀的特别厉害,该表上的VACUUM FULL操作可能需要更长的时间并且可能需要分开执行。 以下两种情况的出现预示着pg_attribute表膨胀很厉害并且需要长时间的VACUUM FULL 操作:
- pg_attribute表包含大量记录。
- gp_toolkit.gp_bloat_diag视图中的信息显示pg_attribute的状态为significant amount of bloat
为查询优化进行清理和分析
Greenplum数据库使用一种基于代价的查询优化器,它依赖于数据库的统计信息。准确的统计信息帮助查询优化器 更好的评估选择度以及一个查询操作检索的行数。这些评估会帮助它选择最有效的查询计划。ANALYZE 命令会为查询优化器收集列级的统计信息。
可以在同一个命令中同时执行VACUUM和ANALYZE操作。例如:
1 | =# VACUUM ANALYZE mytable; |
当在一个显著膨胀的表(显著膨胀的表磁盘空间被已删除或者已废弃行占据)上运行VACUUM ANALYZE 命令时,该命令可能会产生不正确的统计信息。对于大型的表,ANALYZE命令会从行的一个随机 采样中计算统计信息。它会通过计算采样中每页的平均行数与表中实际页数的成绩来估算表中的总行数。如果采样包含 很多空页,估计出的行计数可能会不准确。
对于一个表,可以在gp_toolkit
视图gp_bloat_diag中查看有关未使用磁盘空间(被已删除或者 已废弃行占据的空间)量的信息。如果一个表的bdidiag列包含值 significant amount of bloat suspected,那么相当多的表磁盘空间由未使用空间组成。 在一个表被清理后,相关项会被加入到gp_bloat_diag视图中。
要从表中移除未使用的磁盘空间,可以在该表上运行命令VACUUM FULL。由于对表锁的需求, VACUUM FULL可能无法在非维护时段运行。
作为一种临时的变通方案,可以运行ANALYZE来计算列统计信息,然后在该表上运行 VACUUM来生成准确的行计数。这个例子在cust_info表上先运行ANALYZE, 然后运行VACUUM。
1 2 | ANALYZE cust_info; VACUUM cust_info; |
Important: 如果想要在启用了GPORCA(默认启用)的情况下对分区表执行查询, 必须用ANALYZE命令在分区表根分区上收集统计信息。有关GPORCA的信息,请见GPORCA概述。
Note: 可以使用Greenplum数据库工具analyzedb来更新表统计信息。表可以被并行地分析。 对于追加优化表,只有统计信息不是当前值时,analyzedb才会更新统计信息。有关analyzedb 工具的信息,请见analyzedb。
常规的索引重建
对于B-树索引,一个刚刚构建的索引访问起来比一个已经更新过很多次的索引要快一点,因为在新构建的索引中逻辑 上相邻的页面在物理上也相邻。定期地重建旧的索引可以提升访问速度。如果一个页面上除了索引以外的数据都被删除, 那么索引页上对应的也会产生浪费的空间。重建索引操作会回收这些被浪费的空间。在Greenplum数据库中,删除索引 (DROP INDEX)再重建 (CREATE INDEX)通常比直接使用REINDEX 命令要快。
对于有索引的表列,批量更新或者插入之类的一些操作可能会执行得更慢,因为需要更新索引。为了提高带有索引 的表上的批量操作性能,可以先删除掉索引,然后执行批量操作,最后再重建索引。
管理Greenplum数据库日志文件
数据库服务器日志文件
Greenplum数据库的日志输出常常会体量很大,尤其是在调试级别时,用户不需要无限期保存它。管理员可以应该 定期清理日志文件。
Greenplum数据库在Master和所有的Segment实例上都启用了日志文件轮转。 日志文件被创建在Master以及每个Segment的数据目录中的pg_log子目录下,这些文件使用 命名规则:gpdb-YYYY-MM-DD_hhmmss.csv。数据库管理员需要 编写脚本或程序定期清理Master以及每个Segment日志目录pg_log下的旧文件。
日志轮转可以被当前日志文件的大小或当前日志文件的年龄触发。log_rotation_size 配置参数设置触发日志轮转的单个日志文件大小。当日志文件大小等于或大于特定的大小时,该文件会停止写入, 系统重新创建一个新的日志文件。log_rotation_size的值的单位为KB。默认为1048576, 即1GB。如果log_rotation_size设置为0,表示基于文件大小的日志轮转被禁用。
log_rotation_age配置参数定义了出发日志轮转的日志文件年龄。当文件自创建时开始到达 特定长度的时间时,该文件停止写入,系统重新创建一个新的日志文件。默认的log_rotation_age 时间为1d,即当前日志文件创建24小时之后。如果log_rotation_age设置为0,表示基于时间 的轮转被禁用。
更多查看数据库日志文件的信息,请见查看数据库服务器日志文件.
管理工具日志文件
reenplum数据库管理工具的日志文件默认被写入到~/gpAdminLogs。管理日志文件的命名规则是:
1 | script_name_date.log |
日志详情的格式是:
1 | timestamp:utility:host:user:[INFO|WARN|FATAL]:message |
每次一个工具运行时,就会向其每日的日志文件中增加与其执行相关的日志信息。
收集统计信息,回收空间
定期使用Vacuum analyze tablename 回收垃圾和收集统计信息,尤其在大数据量删除,导入以后,非常重要
vacuum分两种,一种是analyze,优化查询计划的,还有一种是清理垃圾数据。
postgres删除工作,并不是真正删除数据,而是在被删除的数据上,做一个标记,只有执行vacuum时,才会真正的物理删除,这个非常重用,有些经常更新的表,各种查询、更新效率会越来越慢,这个多是因为没有做vacuum的原因。
以下脚本用于生成收集统计信息的shell脚本:
1 2 3 4 5 6 7 | -- 日志位置: /opt/greenplum/data/master/gpseg-1/db_analyze/ SELECT 'analyzedb -d '||d.datname||' -a -p 10' analyzedb from pg_database d WHERE d.datname not in ('template0','template1'); -- 做vacuum及更新统计信息 vacuumdb -a -z |
缺失统计信息的表可以通过如下SQL查询
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | -- 没有统计信息且可能需要ANALYZE的表。 qian'ti select a.*, 'analyze ' || a.smischema || '.' || a.smitable||';' anaylze_tb from gp_toolkit.gp_stats_missing a order by smischema, smicols desc; SELECT schemaname||'.'||relname as table_name, pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size, n_dead_tup, n_live_tup, 'analyze ' || a.schemaname || '.' || a.relname||';' anaylze_tb FROM pg_stat_all_tables a WHERE a.last_analyze is null and pg_relation_size(schemaname||'.'||relname) >=1*1024*1024*1024 and a.schemaname not in ('pg_toast') ORDER BY pg_relation_size(schemaname||'.'||relname) desc LIMIT 50; |
对表做DDL操作会导致统计信息失效,而gp_toolkit将统计信息失效的表也归纳为统计信息缺失。
ANALYZE会给目标表加SHARE UPDATE EXCLUSIVE
锁,也就是与UPDATE,DELETE,还有DDL语句冲突。
定时收集统计信息
vacuumdb
缺点:不能并行,执行较慢
优点:占用资源少
1 2 3 4 5 | su - gpadmin crontab -e 0 2 * * * . /usr/local/greenplum-db/greenplum_path.sh && vacuumdb -a -Z > /tmp/vacuumdb.log |
analyzedb
优点:可以并行,执行较快
缺点:占用资源多
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | -- 日志位置: /opt/greenplum/data/master/gpseg-1/db_analyze/ SELECT 'analyzedb -d '||d.datname||' -a -p 10' analyzedb from pg_database d WHERE d.datname not in ('template0','template1'); su - gpadmin crontab -e 0 2 * * * sh /home/gpadmin/analyzegp.sh > /tmp/analyzegp.log cat >> /home/gpadmin/analyzegp.sh <<"EOF" . /usr/local/greenplum-db/greenplum_path.sh analyzedb -d db1 -a -p 10 analyzedb -d db2 -a -p 10 ... analyzedb -d dbn -a -p 10 EOF chmod +x /home/gpadmin/analyzegp.sh |
分析执行计划
explain:在提交大的查询之前,使用explain分析执行计划、发现潜在优化机会,避免将系统资源熬尽。
少写了个analyze,如果只是explain,统计出来的执行时间,是非常坑爹的,如果希望获得准确的执行时间,必须加上analyze。
集群修复
查看节点状态
1 2 3 4 | select * from gp_segment_configuration where status='d' or mode <>'s' --查看segment处于down的状态 select * from gp_segment_configuration where status='d' or mode <>'s'--观察数据库是否发生切换 |
修复前负载判定(是否是实例宕机,是否需要kill会话,是否需要重启等)
在查看集群状态为异常后,进一步查看是否存在超过一个小时以上的会话,是否存在锁等情况
1 2 3 4 | select * from gp_segment_configuration; -- 以上语句查询结果中status存在'd'结果,则是存在实例宕机,可以往下继续判断 -- 如果均为'u',可以参考后面的2.6小节 select pid,usename,query_start,client_addr,xact_start,waiting ,waiting_reason,query from pg_stat_activity where state <>'idle' and query_start < now()-interval '1 hour' order by query_start; |
查看节点负载,可以使用1.2中的vmstat命令,也可以使用nmon监控工具(需安装)
c 查看CPU相关信息
m 查看内存相关信息
d 查看磁盘相关信息
n 查看网络相关信息
t 查看相关进程信息
日志备份([可选]判定实例>判定路径>备份日志)
1 2 3 4 5 6 7 8 9 10 | Select con.time, con.dbid, seg.content, seg.status, seg.port, seg.hostname, seg.datadir , con."desc" from gp_segment_configuration seg ,gp_configuration_history con where con.dbid=seg.dbid and seg.status='d' order by con.time desc limit 10; |
集群实例宕机后首要是先恢复集群状态,但这恢复的时候数据节点日志往往会被清理掉,所以可以先备份当天节点日志后,先恢复集群,后面再查看日志寻找宕机的具体原因,根据seg.hostname和seg.datadir,以及 con.time备份对应实例pg_log目录下对应日期的日志
常规修复(以防突然断电或远程断开等情况,一般建议后台运行)
通过gpstate 或gp_configuration 发现有实例down 掉以后,使用该命令进行恢复。
1 | nohup gprecoverseg -a & |
若存在主备切换,则需要在修复完成后进行实例切回
1 | nohup gprecoverseg -ra & |
查看修复进度
1 | gpstate -e |
全量修复方式
1 | nohup gprecoverseg -Fa & |
若存在主备切换,则需要在修复完成后进行实例切回
1 | nohup gprecoverseg -ra & |
非实例宕机的
如果集群状态显示异常,但在2.1中查询结果并不存在实例宕机的情况,可以先查看是否是集群用户存在密码过期导致无法互信的情况
查看互信情况,发现其中一台服务器无法连接,ssh时并提示密码过期
这样就需要重新设置改服务器gpadmin用户的密码或者过期时间即可
用户管理
创建role/schema
创建用户可以使用CREATE USER 或者CREATE ROLE命令,唯一区别是CREATE USER默认情况下假定LOGIN, 而CREATE ROLE默认情况下假定NOLOGIN.
如创建test用户可以登陆资源队列为 pg_default,密码为passwd:
1 | create role test with login resource queue pg_default password 'passwd'; |
创建模式
1 | Create schema test; |
如果是为角色创建一个同名模式:如
1 | Create schema authorization test; |
创建资源队列
1 | create resource queue rq_df with(active_statements=1,priority=medium); |
修改用户资源队列
1 | alter role test with resource queue rq_df; |
查看用户资源队列对应情况
1 | select rolname,rsqname from pg_roles,gp_toolkit.gp_resqueue_status where pg_roles.rolresqueue=gp_toolkit.gp_resqueue_status.queueid; |
授权与回收权限
根据需要授权用户使用相应权限
1 2 3 4 5 6 7 8 9 | GRANT { {SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER | TRUNCATE } [, ...] | ALL [PRIVILEGES] } ON { [TABLE] table_name [, ...] | ALL TABLES IN SCHEMA schema_name [, ...] } TO { [ GROUP ] role_name | PUBLIC} [, ...] [ WITH GRANT OPTION ] GRANT { {USAGE | SELECT | UPDATE} [, ...] | ALL [PRIVILEGES] } ON { SEQUENCE sequence_name [, ...] | ALL SEQUENCES IN SCHEMA schema_name [, ...] } TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] |
例如:
授权用户使用模式权限(用户需要先有schema的使用权限,才能使用其他权限)
1 | GRANT USAGE ON SCHEMA schema to user; |
授权用户查询表权限
1 | grant select on tablename to user; |
postgresql 9.0 版本以后,如果是一个用户对另一个用户新建的表需要有查询等权限,可以使用一些语句,不用再对新建表去重新授权
1 2 | alter default privileges for user test in schema test grant select on tables to test1; --举例中,test用户在test模式下再建新表时,test1用户自动对test模式下新建的表有select权限 |
回收权限则是revoke命令,后面to 改为from即可,如回收用户使用模式权限
1 | REVOKE USAGE ON SCHEMA schema from user; |
在上例中,回收上面的权限的语句为:
1 | alter default privileges for user test in schema test revoke select on tables from test1; |
权限查看
查看哪些用户对具体表有哪些权限
1 | select * from information_schema.table_privileges where table_name ='test'; |
查看具体用户有哪些表的哪些权限
1 | select * from information_schema.table_privileges where grantee='test' or grantor='test'; |
锁定role
—锁定用户
1 | alter role test with nologin; |
删除schema
—删除schema
1 | DROP SCHEMA [IF EXISTS] name [, ...] [CASCADE | RESTRICT]; |
删除role
—删除用户