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

2019-12-27 18:12:39 浏览数 (1)

本节主要从数据迁移和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

  • 服务器准备

准备好源集群和目标集群

主机映射文件：

代码语言：javascript复制
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值

  • 失败的传输会被记录到文件中
代码语言：javascript复制
[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监控：

代码语言：javascript复制
[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
代码语言：javascript复制
[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
代码语言：javascript复制
[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
代码语言：javascript复制
[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'
代码语言：javascript复制
[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 ~]$
代码语言：javascript复制
[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'
代码语言：javascript复制
 /usr/sbin/snmptrapd -m ALL -Lf ~/filename.log

配置email通知：

代码语言：javascript复制
$MASTER_DATA_DIRECTORY/postgresql.conf
代码语言：javascript复制
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配置：

代码语言：javascript复制
$ gpstop -u
  • 系统状态检查
代码语言：javascript复制
[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
代码语言：javascript复制
[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

查看镜像状态：

代码语言：javascript复制
[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
代码语言：javascript复制
[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
代码语言：javascript复制
[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
  • 检查集群磁盘空间使用
代码语言：javascript复制
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)
  • 检查数据库磁盘空间
代码语言：javascript复制
komablog=# SELECT * FROM gp_toolkit.gp_size_of_database
komablog-#    ORDER BY sodddatname;
 sodddatname | sodddatsize
------------- -------------
 komablog    |    34354184
(1 row)
  • 查看表磁盘空间
代码语言：javascript复制
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)
  • 查看索引的磁盘空间
代码语言：javascript复制
=> 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';
  • 查看表定义
代码语言：javascript复制
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)
  • 查看数据分布
代码语言：javascript复制
komablog=# SELECT gp_segment_id, count(*)   FROM users GROUP BY gp_segment_id;
 gp_segment_id | count
--------------- -------
             1 |     3
             0 |     3
(2 rows)
  • 查询倾斜
代码语言：javascript复制
=# SELECT gp_segment_id, count(*) FROM table_name
   WHERE column='value' GROUP BY gp_segment_id;
  • 查看元数据信息
代码语言：javascript复制
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)
  • 查看会话内存使用信息

创建视图

代码语言：javascript复制
[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
  • 查看数据库日志文件
代码语言：javascript复制
[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|
代码语言：javascript复制
//批量的查询

$ gpssh -f seg_host_file

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

3.日常系统维护

  • 定期清理数据库
代码语言：javascript复制
komablog=# vacuum analyze;
VACUUM
komablog=# vacuum users;
VACUUM
komablog=#
云数据仓库PostgreSQL

0 人点赞