Snova运维篇（七）：GP数据迁移和监控

  • 2019 年 12 月 27 日
  • 笔记

本节主要从数据迁移和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='[email protected]'  gp_email_smtp_password='mypassword'  gp_email_from='Greenplum数据库<[email protected]>'  gp_email_to='[email protected];

重新装载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
  • 查看数据库日志文件

待续；

VirMach 便宜 VPS

QNews

QNews