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