专栏首页Snova最佳实践系列Snova运维篇(七):GP数据迁移和监控
原创

Snova运维篇(七):GP数据迁移和监控

本节主要从数据迁移和GP集群监控的角度进一步深入snova运维业务工作。

目录:

  1. gptransfer迁移数据
  2. gp集群监控
  3. 日常系统维护


基本概念:

snmp

简单网络管理协议(Simple Network Management Protocol)是一种应用层协议,是TCP/IP协议族的一部分。它使网络设备之间能够方便地交换管理信息。能够让网络管理员管理网络的性能,发现和解决网络问题及进行网络的扩充。


1.gptransfer迁移数据

  • 基本流程

在源数据库中创建一个可写外部表

在目标数据库中创建一个可读外部表

创建命名管道以及源集群中Segment主机上的gpfdist进程

在源数据库中执行一个SELECT INTO语句把源数据插入到可写外部表

在目标数据库中执行一个SELECT INTO语句把数据从可读外部表插入到目标表

通过比较源和目标中行的行数或者MD5哈希来有选择地验证数据

清除外部表、命令管道和gpfdist进程

  • fast模式和slow模式

gptransfer会为每个源Segment设置一个命名管道和一个gpfdist进程。这是用于最优数据传输率的配置并且被称为Fast模式。

当目标集群的Segment比源集群少时,命名管道的输入端配置有点不同。gptransfer自动分配gpfdist数量,这被称为Slow模式,因为只有较少的gpfdist进程供应数据给目标集群,然而通过每台Segment主机上的一个gpfdist,传输依然很快。

  • 批处理和子批处理

--batch-size 默认为2 同时两张表传输进行,范围为1-10

--sub-batch-size 批处理时单个进程的线程并发数量,默认25 最大50

默认值最后可得 2*25 单次并发线程50

  • 服务器准备

准备好源集群和目标集群

主机映射文件:

host1_name,host1_ip_addr
host2_name,host2_ipaddr
  • 缺陷

默认库和模版库不能被传输,postgres、template0和template1 ,管理员必须手工传输配置文件并且用gppkg在目标数据库中安装扩展。

  • 完全模式和表模式

--full选项不能与-t、-d、-f或者--partition-transfer选项一起使用,完全模式会一次性迁移所有库,表,视图,角色,索引到新的集群。如果目标集群有任何自定义,传输会失败。

-t 选项可以进行自定义,库表,schema,索引,视图等。用--skip-existing、--truncate或者--drop选项可以对目标集群进行覆盖。

对象

完全模式

表模式

数据

Yes

Yes

索引

Yes

Yes

角色

Yes

No

函数

Yes

No

资源队列

Yes

No

postgresql.conf

No

No

pg_hba.conf

No

No

gppkg

No

No

  • -x 表锁定 设置排它锁
  • 数据校验

--validate=type 启用此选项

校验:

count:表行数进行统计并比对

MD5:排序表并比较hash值

  • 失败的传输会被记录到文件中
[WARNING]:-Some tables failed to transfer. A list of these tables
[WARNING]:-has been written to the file failed_transfer_tables_20140808_101813.txt
[WARNING]:-This file can be used with the -f option to continue
  • 推荐做法

1>合理设置批处理的并发量防止内存耗尽 --batch-size和--sub-batch-size 2>分阶段传输数据 --schema-only和-d database选项运行gptransfer

3>合理选择gpfdist和外部表的参数

4>避免空表传输

5>不要提前创建索引,反倒会慢

2.gp集群监控

  • 设置告警邮件——snmp监控

告警事件:

所有的PANIC级错误情况

所有的FATAL级错误情况

属于“内部错误”(例如,SIGSEGV错误)的ERROR级错误情况

数据库系统关闭和重启

Segment失效和恢复

后备Master不同步情况

Master主机人为关闭或者其他软件问题(在特定失效场景中,Greenplum数据库无法发送告警或者通知)

配置snmp监控:

[root@gp-master ~]# yum -y install net-snmp-utils
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
Resolving Dependencies
--> Running transaction check
---> Package net-snmp-utils.x86_64 1:5.7.2-43.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==================================================================================================================================
 Package                            Arch                       Version                               Repository              Size
==================================================================================================================================
Installing:
 net-snmp-utils                     x86_64                     1:5.7.2-43.el7                        os                     199 k
[root@gp-master ~]# systemctl start snmpd
[root@gp-master ~]# systemctl status snmpd
● snmpd.service - Simple Network Management Protocol (SNMP) Daemon.
   Loaded: loaded (/usr/lib/systemd/system/snmpd.service; disabled; vendor preset: disabled)
   Active: active (running) since Fri 2019-12-27 16:15:40 CST; 4s ago
 Main PID: 20841 (snmpd)
   CGroup: /system.slice/snmpd.service
           └─20841 /usr/sbin/snmpd -LS0-6d -f
[root@gp-master ~]# snmpwalk -v 1 -c public localhost .1.3.6.1.2.1.1.1.0
SNMPv2-MIB::sysDescr.0 = STRING: Linux gp-master 3.10.0-862.el7.x86_64 #1 SMP Fri Apr 20 16:44:24 UTC 2018 x86_64
[gpadmin@gp-master ~]$ gpconfig -c gp_snmp_community -v public --masteronly
20191227:16:22:56:022224 gpconfig:gp-master:gpadmin-[INFO]:-completed successfully with parameters '-c gp_snmp_community -v public --masteronly'
[gpadmin@gp-master ~]$ gpconfig -c gp_snmp_monitor_address -v gp-master:162 --masteronly
20191227:16:23:31:022412 gpconfig:gp-master:gpadmin-[INFO]:-completed successfully with parameters '-c gp_snmp_monitor_address -vgp-master:162 --masteronly'
[gpadmin@gp-master ~]$
[gpadmin@gp-master ~]$ gpconfig -c gp_snmp_use_inform_or_trap -v trap --masteronly
20191227:16:24:09:022607 gpconfig:gp-master:gpadmin-[INFO]:-completed successfully with parameters '-c gp_snmp_use_inform_or_trap-v trap --masteronly'
 /usr/sbin/snmptrapd -m ALL -Lf ~/filename.log

配置email通知:

$MASTER_DATA_DIRECTORY/postgresql.conf
gp_email_smtp_server='smtp.company.com:25'
gp_email_smtp_userid='gpadmin@example.com'
gp_email_smtp_password='mypassword'
gp_email_from='Greenplum数据库<gpadmin@example.com>'
gp_email_to='dba@example.com;

重新装载email配置:

$ gpstop -u
  • 系统状态检查
[gpadmin@gp-master ~]$ gpstate
20191227:16:49:56:010233 gpstate:gp-master:gpadmin-[INFO]:-Starting gpstate with args:
20191227:16:49:56:010233 gpstate:gp-master:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 5.21.1 build commit:ca0b8106b893028d18f241dcb858d85f12af90b6'
20191227:16:49:56:010233 gpstate:gp-master:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 8.3.23 (Greenplum Database 5.21.1 build commit:ca0b8106b893028d18f241dcb858d85f12af90b6) on x86_64-pc-linux-gnu, compiled by GCC gcc (GCC) 6.2.0, 64-bit compiled on Jul 24 2019 22:51:37'
20191227:16:49:56:010233 gpstate:gp-master:gpadmin-[INFO]:-Obtaining Segment details from master...
20191227:16:49:56:010233 gpstate:gp-master:gpadmin-[INFO]:-Gathering data from segments...
..
20191227:16:49:58:010233 gpstate:gp-master:gpadmin-[INFO]:-Greenplum instance status summary
20191227:16:49:58:010233 gpstate:gp-master:gpadmin-[INFO]:-----------------------------------------------------
20191227:16:49:58:010233 gpstate:gp-master:gpadmin-[INFO]:-   Master instance                                = Active
20191227:16:49:58:010233 gpstate:gp-master:gpadmin-[INFO]:-   Master standby                                 = gp-standby
20191227:16:49:58:010233 gpstate:gp-master:gpadmin-[INFO]:-   Standby master state                           = Standby host passive
20191227:16:49:58:010233 gpstate:gp-master:gpadmin-[INFO]:-   Total segment instance count from metadata     = 2
20191227:16:49:58:010233 gpstate:gp-master:gpadmin-[INFO]:-----------------------------------------------------
20191227:16:49:58:010233 gpstate:gp-master:gpadmin-[INFO]:-   Primary Segment Status
[gpadmin@gp-master ~]$ gpstate -s
20191227:16:50:32:010399 gpstate:gp-master:gpadmin-[INFO]:-Starting gpstate with args: -s
20191227:16:50:32:010399 gpstate:gp-master:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 5.21.1 build commit:ca0b8106b893028d18f241dcb858d85f12af90b6'
20191227:16:50:32:010399 gpstate:gp-master:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 8.3.23 (Greenplum Database 5.21.1 build commit:ca0b8106b893028d18f241dcb858d85f12af90b6) on x86_64-pc-linux-gnu, compiled by GCC gcc (GCC) 6.2.0, 64-bit compiled on Jul 24 2019 22:51:37'
20191227:16:50:32:010399 gpstate:gp-master:gpadmin-[INFO]:-Obtaining Segment details from master...
20191227:16:50:32:010399 gpstate:gp-master:gpadmin-[INFO]:-Gathering data from segments...
.
20191227:16:50:33:010399 gpstate:gp-master:gpadmin-[INFO]:-----------------------------------------------------
20191227:16:50:33:010399 gpstate:gp-master:gpadmin-[INFO]:--Master Configuration & Status

查看镜像状态:

[gpadmin@gp-master ~]$ gpstate -m
20191227:16:54:49:011056 gpstate:gp-master:gpadmin-[INFO]:-Starting gpstate with args: -m
20191227:16:54:49:011056 gpstate:gp-master:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 5.21.1 build commit:ca0b8106b893028d18f241dcb858d85f12af90b6'
20191227:16:54:49:011056 gpstate:gp-master:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 8.3.23 (Greenplum Database 5.21.1 build commit:ca0b8106b893028d18f241dcb858d85f12af90b6) on x86_64-pc-linux-gnu, compiled by GCC gcc (GCC) 6.2.0, 64-bit compiled on Jul 24 2019 22:51:37'
20191227:16:54:49:011056 gpstate:gp-master:gpadmin-[INFO]:-Obtaining Segment details from master...
20191227:16:54:49:011056 gpstate:gp-master:gpadmin-[WARNING]:--------------------------------------------------------------
20191227:16:54:49:011056 gpstate:gp-master:gpadmin-[WARNING]:-physical mirroring not used
[gpadmin@gp-master ~]$ gpstate -c
20191227:16:57:47:011489 gpstate:gp-master:gpadmin-[INFO]:-Starting gpstate with args: -c
20191227:16:57:47:011489 gpstate:gp-master:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 5.21.1 build commit:ca0b8106b893028d18f241dcb858d85f12af90b6'
20191227:16:57:47:011489 gpstate:gp-master:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 8.3.23 (Greenplum Database 5.21.1 build commit:ca0b8106b893028d18f241dcb858d85f12af90b6) on x86_64-pc-linux-gnu, compiled by GCC gcc (GCC) 6.2.0, 64-bit compiled on Jul 24 2019 22:51:37'
20191227:16:57:47:011489 gpstate:gp-master:gpadmin-[INFO]:-Obtaining Segment details from master...
20191227:16:57:47:011489 gpstate:gp-master:gpadmin-[INFO]:--------------------------------------------------------------
20191227:16:57:47:011489 gpstate:gp-master:gpadmin-[INFO]:--Primary list [physical mirroring not used]
20191227:16:57:47:011489 gpstate:gp-master:gpadmin-[INFO]:--------------------------------------------------------------
20191227:16:57:47:011489 gpstate:gp-master:gpadmin-[INFO]:-   Primary    Datadir                Port
20191227:16:57:47:011489 gpstate:gp-master:gpadmin-[INFO]:-   gp-node1   /data/primary/gpseg0   6000
20191227:16:57:47:011489 gpstate:gp-master:gpadmin-[INFO]:-   gp-node2   /data/primary/gpseg1   6000
[gpadmin@gp-master ~]$ gpstate -f
20191227:17:01:40:012091 gpstate:gp-master:gpadmin-[INFO]:-Starting gpstate with args: -f
20191227:17:01:40:012091 gpstate:gp-master:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 5.21.1 build commit:ca0b8106b893028d18f241dcb858d85f12af90b6'
20191227:17:01:40:012091 gpstate:gp-master:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 8.3.23 (Greenplum Database 5.21.1 build commit:ca0b8106b893028d18f241dcb858d85f12af90b6) on x86_64-pc-linux-gnu, compiled by GCC gcc (GCC) 6.2.0, 64-bit compiled on Jul 24 2019 22:51:37'
20191227:17:01:40:012091 gpstate:gp-master:gpadmin-[INFO]:-Obtaining Segment details from master...
20191227:17:01:41:012091 gpstate:gp-master:gpadmin-[INFO]:-Standby master details
20191227:17:01:41:012091 gpstate:gp-master:gpadmin-[INFO]:-----------------------
20191227:17:01:41:012091 gpstate:gp-master:gpadmin-[INFO]:-   Standby address          = gp-standby
20191227:17:01:41:012091 gpstate:gp-master:gpadmin-[INFO]:-   Standby data directory   = /data/master/gpseg-1
20191227:17:01:41:012091 gpstate:gp-master:gpadmin-[INFO]:-   Standby port             = 5432
20191227:17:01:41:012091 gpstate:gp-master:gpadmin-[INFO]:-   Standby PID              = 6650
20191227:17:01:41:012091 gpstate:gp-master:gpadmin-[INFO]:-   Standby status           = Standby host passive
  • 检查集群磁盘空间使用
komablog=# SELECT * FROM gp_toolkit.gp_disk_free
komablog-#    ORDER BY dfsegment;
 dfsegment | dfhostname |  dfdevice  | dfspace
-----------+------------+------------+----------
         0 |  gp-node1  |  /dev/vda1 | 44602148
         1 |  gp-node2  |  /dev/vda1 | 44558804
(2 rows)
  • 检查数据库磁盘空间
komablog=# SELECT * FROM gp_toolkit.gp_size_of_database
komablog-#    ORDER BY sodddatname;
 sodddatname | sodddatsize
-------------+-------------
 komablog    |    34354184
(1 row)
  • 查看表磁盘空间
komablog=# SELECT relname AS name, sotdsize AS size, sotdtoastsize
komablog-#    AS toast, sotdadditionalsize AS other
komablog-#    FROM gp_toolkit.gp_size_of_table_disk as sotd, pg_class
komablog-#    WHERE sotd.sotdoid=pg_class.oid ORDER BY relname;
   name   | size  | toast | other
----------+-------+-------+-------
 twitters | 65536 |     0 |     0
 users    | 65536 |     0 |     0
(2 rows)
  • 查看索引的磁盘空间
=> SELECT soisize, 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';
  • 查看表定义
komablog=# \d+ users
                                             Table "public.users"
 Column |          Type          |                     Modifiers                      | Storage  | Description
--------+------------------------+----------------------------------------------------+----------+-------------
 id     | integer                | not null default nextval('users_id_seq'::regclass) | plain    |
 player | character varying(255) | not null                                           | extended |
 score  | real                   |                                                    | plain    |
 team   | character varying(255) |                                                    | extended |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
Has OIDs: no
Distributed by: (id)
  • 查看数据分布
komablog=# SELECT gp_segment_id, count(*)   FROM users GROUP BY gp_segment_id;
 gp_segment_id | count
---------------+-------
             1 |     3
             0 |     3
(2 rows)
  • 查询倾斜
=# SELECT gp_segment_id, count(*) FROM table_name
   WHERE column='value' GROUP BY gp_segment_id;
  • 查看元数据信息
komablog=# 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='users';
 schema | table |  role   | action  | type  |             time
--------+-------+---------+---------+-------+-------------------------------
 public | users | gpadmin | CREATE  | TABLE | 2019-12-27 10:19:31.49169+08
 public | users | gpadmin | ANALYZE |       | 2019-12-27 10:19:32.678677+08
(2 rows)
  • 查看会话内存使用信息

创建视图

[gpadmin@gp-master ~]$ psql -d komablog -f $GPHOME/share/postgresql/contrib/gp_session_state.sql
CREATE SCHEMA
SET
BEGIN
CREATE FUNCTION
GRANT
CREATE VIEW
GRANT
COMMIT
  • 查看数据库日志文件
[gpadmin@gp-master ~]$ gplogfilter -n 3  //查询当前机器
requested timestamp range from beginning of data to end of data
----------  /data/master/gpseg-1/pg_log/gpdb-2019-12-24_153623.csv ----------
2019-12-24 15:36:24.443022 CST|||p19902|th-1383237760||||0|||seg-1|||||LOG: |00000|received fast shutdown request|||||||0||postmaster.c|4117|
2019-12-24 15:36:24.446558 CST|||p19908|th-1383237760||||0|||seg-1|||||LOG: |00000|shutting down|||||||0||xlog.c|8568|
2019-12-24 15:36:24.464534 CST|||p19908|th-1383237760||||0|||seg-1|||||LOG: |00000|database system is shut down|||||||0||xlog.c|8584|
       in:      57 lines,      57 log entries; timestamps from 2019-12-24 15:36:23.027330 to 2019-12-24 15:36:24.464534
      out:       3 lines,       3 log entries; timestamps from 2019-12-24 15:36:24.443022 to 2019-12-24 15:36:24.464534
----------  /data/master/gpseg-1/pg_log/gpdb-2019-12-23_000000.csv ----------
       in:       0 lines,       0 log entries; no timestamps found
      out:       0 lines,       0 log entries
----------  /data/master/gpseg-1/pg_log/gpdb-2019-12-24_150758.csv ----------
2019-12-24 15:07:59.510735 CST|||p14617|th186308480||||0|||seg-1|||||LOG: |00000|received fast shutdown request|||||||0||postmaster.c|4117|
//批量的查询

$ gpssh -f seg_host_file

=> source /usr/local/greenplum-db/greenplum_path.sh
=> gplogfilter -n 3 /gpdata/gp*/pg_log/gpdb*.log

3.日常系统维护

  • 定期清理数据库
komablog=# vacuum analyze;
VACUUM
komablog=# vacuum users;
VACUUM
komablog=#

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

相关文章

  • Snova基础篇(三):Greenplum集群初始化问题及调试

    gpinitsystem 的日志文件。默认路径为 ~/gpAdmin/gpinitsystem_***

    snova-最佳实践
  • Snova运维篇(二):GP集群配置和高可用特性

    Greenplum数据的配置文件postgresql.conf位于数据库实例的数据目录之下。

    snova-最佳实践
  • Snova运维篇(三):GP数据库备份和恢复

    本节主要从gp数据备份和恢复角度深入学习gp数据库。定期执行备份能确保在数据损坏或者系统失效发生时能恢复数据或者重建Greenplum数据库系统。用户还可以使用...

    snova-最佳实践
  • Snova运维篇(一):基础维护工具和命令

    gpstart工具来启动一个已经由gpinitsystem工具初始化好但已经被gpstop工具停止的Greenplum数据库系统

    snova-最佳实践
  • 在window的cmd窗口下运行linux命令

    之前看很多视频老师都是用Linux命令操作命令框,感觉很方便,自己在cmd窗口试了一下,所有这些命令都提示不是内部或外部命令,后来发现了windows还有一个p...

    蓓蕾心晴
  • 译-机器学习的十大误解

    用户1107453
  • 加州理工学院研发双足机器人,或可为行走困难的人带来便利

    在加州理工学院Amber Lab里,研究者正在开发双足机器人。一位研究尝试绊倒一个名叫Amber的半人形机器人,它身高5英尺,正在跑步机上行走。它是用绳子绑在上...

    AiTechYun
  • react native

    版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。

    sofu456
  • 设计模式01——Iterator模式

    迭代器(Iterator)模式,从其英文单词可以看出,有反复做某件事的意思。迭代器模式常用于数据集合中,对数据集合中的数据按照顺序进行遍历。它能提供一种方法访问...

    itlemon
  • 几天后,GitHub的“master”将更改为“main”

    GitHub 官方表示,从今年 10 月 1 日起,在该平台上创建的所有新的源代码仓库将默认被命名为 "main",而不是原先的"master"。值得注意的是,...

    会呼吸的Coder

扫码关注云+社区

领取腾讯云代金券