如何从 Greenplum6 迁移到 YMatrix 超融合数据库？

yMatrix 2022-12-27

442

前言

随着互联网行业的多年发展，数据量增大，很多用户使用 Greenplum6 的大数据平台，逐渐发现了性能上的缺陷，包括复杂查询、大数据量的关联分析查询出现了性能瓶颈，已经无法满足客户的需求。

YMatrix 针对以上客户的需求做了大量优化，并经过客户真实场景以及 TPCH、TPCDS 等专业的测试模型进行了测试对比，结果显示 YMatrix 在数仓模型的查询场景性能是 Greenplum6 的 4 倍以上，客户非常满意这样的结果，并且主动找到 YMatrix，想将 Greenplum6 的数据迁移到 YMatrix 上。

希望 YMatrix 能提供一份迁移文档。下面我们针对客户的诉求并结合 YMatrix 迁移 Greenplum6 的多个项目的经验来编写一份完整的迁移步骤。

YMatrix 安装

安装部署

https://www.ymatrix.cn/doc/4.7/install/mx4_cluster

安装部署方式有以下两种：

在当前运行的 gp6 集群服务器安装部署 YMatrix。
YMatrix 可以与 gp6 共存，不影响当前 gp6 上面的业务运行，使用 YMatrix 的 UI 部署会自动判断端口是否已被占用。
有一套新的服务器环境安装部署 YMatrix。

license 替换

如果是企业版客户，需要申请替换长期授权的 license。

#所有节点都要执行


#删除旧的license
rm usr/local/matrixdb/bin/LICENSE_XXX 


#拷贝新的license到该目录下，并赋权限
chmod 755 usr/local/matrixdb/bin/LICENSE_XXX

验证 license

验证license是否生效，启动mxgate就可以验证
--创建test数据库
CREATE DATABASE test;


--切换到test数据库
\c test


--创建测试表
CREATE TABLE dest(
    time timestamp,
    c1 int,
    c2 text
)DISTRIBUTED BY(c1);

#生成mxgate配置文件
mxgate config --db-database test \
            --db-master-host localhost \
            --db-master-port 5432 \
            --db-user mxadmin \
            --target public.dest \
            --time-format raw \
            --delimiter ',' \
            > mxgate.conf

#启动MatrixGate
mxgate start --config mxgate.conf


如果可以启动就说明license没问题，如果没办法启动就说明license有问题。验证完成后将MatrixGate关闭。


#关闭MatrixGate
mxgate stop

应用业务

停止应用

停止客户端的业务访问 Greenplum6。

迁移应用

gp6 集群禁止对外访问

1. 关闭 gp6 集群

#强制xlog落盘，防止数据丢失
psql -d postgres -c "checkpoint"   #可以连续执行3-6次


#关闭gp6集群
gpstop -fa

2. 修改白名单

#备份pg_hba.conf
cp $MASTER_DATA_DIRECTORY/pg_hba.conf $MASTER_DATA_DIRECTORY/pg_hba.conf.20221208


#编辑pg_hba.conf，将业务端的访问权限都删掉
vi $MASTER_DATA_DIRECTORY/pg_hba.conf

3. 启动 gp6 集群

#启动gp6集群
gpstart -a

硬件性能检测

注意：路径创建一个有权限，没有数据的路径


#gpcheckperf 网络、磁盘性能检测
su - mxadmin
/usr/local/matrixdb/bin/gpcheckperf -f home/mxadmin/all_hosts -r ds -D -d data

gp6 集群备份 DDL

#备份gp6用户信息
pg_dumpall -h 127.0.0.1 -U gpadmin -s -p 5432 --roles-only -f owner.sql


#备份gp6准备迁移的数据库，例如：testdb
pg_dump -h 127.0.0.1 -U gpadmin -p 5432 -d testdb -s -f testdb.ddl

调整 DDL

#屏蔽掉资源队列
sed -i 's#ALTER RESOURCE#--ALTER RESOURCE#Ig' testdb.ddl
sed -i 's#CREATE RESOURCE#--CREATE RESOURCE#Ig' testdb.ddl
    
#修改压缩算法，提高压缩比、提高数据插入性能,(将所有的ao、aoco表的压缩算法改成zstd、并且压缩级调整为1级)
sed -i "s#compresstype=quicklz#compresstype=zstd#Ig" testdb.ddl
sed -i "s#compresstype=zlib#compresstype=zstd#Ig" testdb.sql
sed -i "s#compresslevel=5#compresslevel=1#Ig" testdb.sql
sed -i "s#compresslevel='5'#compresslevel=1#Ig" testdb.sql


#修改plpythonu为plpython3u
sed -i 's#plpythonu#plpython3u#Ig' testdb.ddl


#修改数据类型unknown为text
sed -i 's#unknown#text#Ig' testdb.ddl


#把索引信息挑出来，单出存放
cat testdb.ddl | egrep -i 'CREATE INDEX|CREATE UNIQUE INDEX' > testdb.idx


#把索引屏蔽掉，待数据迁移完成后再创建索引，提高效率
sed -i 's#CREATE INDEX#--CREATE INDEX#Ig' testdb.ddl
sed -i 's#CREATE UNIQUE INDEX#--CREATE UNIQUE INDEX#Ig' testdb.ddl


#检查有没有表空间的信息，手动挑出来修正为目标值
cat testdb.ddl | grep -i TABLESPACE

YMatrix 执行创建 DDL

#创建用户
psql -h localhost -p 6433 -d testdb -U mxadmin -f ./owner.sql > owner.out 2>&1 &


#检查owner.out文件是否有报错，有报错及时解决
cat owner.out | grep -i error 


#创建DDL
psql -h localhost -p 6433 -d testdb -U mxadmin -f ./testdb.ddl > ddl.out 2>& 1 &


#检查ddl.out文件是否有报错，有报错及时解决
cat ddl.out | grep -i error

mxshift 迁移数据

mxshift 属于 YMatrix 内部组件，自 4.7.3 版本之后集成在 YMatrix 数据库中，安装 YMatrix 即可使用。

编辑白名单文件

mxshift迁移是通过segment直连实现高速并发迁移，所以执行mxshift工具需要在gp6的所有实例添加白名单访问权限。


#快速的查找gp6实例的白名单文件
find data/gpdata/ -name pg_hba.conf


#将YMatrix集群所有IP放到gp6所有实例的白名单文件里面。允许相互访问。
例如：
host     all         all             172.16.236.0/24       md5

编辑配置文件

vi testdb.json 


{
    "gphome": "/usr/local/greenplum-db-6.7.1",
    "mode": "normal",
    "verify": false,
    "bothway": true,
    "concurrency": 6,
    "log_level": "info",
    "src_host": "localhost",
    "src_db": "testdb",
    "src_user": "gpadmin",
    "src_password": "123123",
    "src_port": 5432,
    "target_host": "localhost",
    "target_db": "testdb",
    "target_user": "mxadmin",
    "target_password": "123123",
    "target_port": 6433
}

执行数据迁移

time mxshift --config_path testdb.json

参数说明

向上滑动阅览

gphome
: 源端数据库的 GPHOME 绝对路径。
mode
: 运行模式，允许下列几个值

normal
: 正常的数据迁移流程。
dryrun
: 验证两边数据库的连通性，验证 DDL 操作，但不进行数据的读取和写入。
fetch
: 在 dryrun 基础上，读取源端的数据并发送到目标端 segment，但直接丢弃，不进行处理，此选项用于验证源端读取效率和源端到目标端网络传输效率。
motion
: 在 fetch 基础上，在目标端数据库解析数据并交换到真正的目标 segment，此选项用于进一步测试目标端数据库的数据交换速率。

verify
: 是否校验数据迁移前后的行数是否匹配。
bothway
: mxshift 默认会先迁移最大的表，以达到最短时间内迁移完全体数据。如果设置 bothway 为
true，则 mxshift 会从最大的表和最小的表两端同时消费。
concurrency
: 最大并行迁移多少张表，通常建议为 5，不超过 10。
log_level
: 默认 info，会打印正常的进度信息以及错误和警告信息。如果改为 debug
，则会输出更多调试信息，仅用于开发和故障定位。
src_host
: 需要迁出数据的源 gp 集群 master 节点 Host 或 IP。
src_db
: 需要迁出数据的源 gp 数据库。
src_user
: 需要迁出数据的源 gp 数据用户名。
src_password
: 需要迁出数据的源 gp 数据用户密码。
src_port
: 需要迁出数据的源 gp 数据库的端口号。
target_host
: 需要迁入数据的目标 YMatrix 集群 master 节点 Host 或 IP。
target_db
: 需要迁入数据的目标 YMatrix 数据库。
target_user
: 需要迁入数据的目标 YMatrix 数据库用户名。
target_password
: 需要迁入数据的目标 YMatrix 数据库用户密码。
target_port
: 需要迁入数据的目标 YMatrix 数据库的端口号。
schema_list
: 需要迁出数据的目标 gp 数据库的 schema，优先级是如果 table_list 有配置具体的表，则只传输 table_list 中的表，如果没有，则检查schema_list 中是否配置了 schema，如果配置了 schema，则传输该 schema 下的所有表，如果没有 schema，则传输 src_db 下的所有表。
exclude_schema_list
: 排除需要迁出数据的目标 gp 数据库的 schema，不对该 schema 下的 tables 进行数据迁移，如果 schema_list和 exclude_schema_list 中配置了同名的 schema，那么该 schema 会被 exclude 掉。
table_list
: 需要迁移的源 gp 数据表的列表，如果是分区表需填写分区表，如果需要迁移整个 gp 数据库，只需要配置 src_db
参数，该 table_list
参数配置为空即可，mxshift 会从大到小开始迁移所有大小不为 0 的表。
exclude_table_list
: 需排除的表的schema和名称列表。

更多解释详见

https://www.ymatrix.cn/doc/4.7/tools/mxshift

YMatrix 执行创建索引

#执行创建索引
psql -h localhost -p 6433 -d testdb -U mxadmin -f testdb.idx >idx.out 2>&1 &


#检查idx.out文件是否有报错，有报错及时解决
cat idx.out | grep -i error

YMatrix 执行 anaylze

export PGPORT=6433
time analyzedb -d testdb -p 10 -a

数据校验

数据校验通过 python 脚本实现，目前还没有集成到 YMatrix 产品里。

https://github.com/ymatrix-data/poc-workload/tree/master/code_demo/data_dump

编辑配置文件

vi config.ini


[db]
s_ip = localhost
s_port = 5432
s_user = gpadmin
s_pass = 123123
s_db = edp


d_ip = localhost
d_port = 6433
d_user = mxadmin
d_pass = 123123
d_db= edp


[thread_control]
# 控制大小超过多少GB使用mxgate，否则使用pgdump
vault = 1
gate_pool = 10
dump_pool = 10
dump_concurrency = 15
dump_compress = false
check_concurrency = 15




# 不需要迁移的schema，table
[exclude]
schemas = information_schema, gp_toolkit, matrixgate_internal, matrixts_internal
tables = tep_tes.tes2_source_data,tep_tes.tes2_source_data

执行数据校验

脚本

vi run_count_check.sh


#!/bin/bash


date
python3 data_transfer.py plan


cat pgdump_transfer_plan.csv >>mxgate_transfer_plan.csv


python3 data_transfer.py -cgate 


date

执行脚本

nohup sh run_count_check.sh > run_count_check.out 2>&1 &


#检查run_count_check.out，找到数据校验不一致的，查找原因。
cat run_count_check.out  | grep "count diff"

对象数量校验

--查询普通表+分区主表信息
--YMatrix
SELECT n.nspname as "Schema",
       count(c.relname) as ct
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('p','r','')
 AND NOT c.relispartition
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
      and n.nspname <> 'matrixts_internal'
      and n.nspname <> 'gp_toolkit'
group by 1;


--gp6
SELECT n.nspname as "Schema",
       count(c.relname) as ct
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r')
AND c.relstorage IN ('h','a','c')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
      and n.nspname <> 'gp_toolkit'
      AND c.oid NOT IN (select inhrelid from pg_catalog.pg_inherits)
  AND pg_catalog.pg_table_is_visible(c.oid)
GROUP BY 1;




--索引
SELECT n.nspname as "Schema",
  count(c.relname) as ct
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('i','I','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
group BY 1;




--视图
SELECT n.nspname as "Schema",
  count(c.relname) as ct
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('v','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
      and n.nspname <> 'matrixts_internal'
      and n.nspname <> 'gp_toolkit'
group BY 1;




--函数
SELECT n.nspname as "Schema",
  count(p.proname) as ct
FROM pg_catalog.pg_proc p
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE 1=1 
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
      and n.nspname <> 'matrixts_internal'
      and n.nspname <> 'gp_toolkit'  
      and p.prokind <> 'a'
      and p.prokind <> 'w'
      and p.prokind <> 'p'  
group BY 1;




--序列
SELECT n.nspname as "Schema",
       count(c.relname) as ct
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('S','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
      and n.nspname <> 'matrixts_internal'
      and n.nspname <> 'gp_toolkit'
          and n.nspname <> 'mxshift_internal_catalog'
group BY 1;




--extension
SELECT n.nspname AS "Schema",
       count(e.extname) AS ct
FROM pg_catalog.pg_extension e 
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace 
LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid 
AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
group BY 1;




--type
SELECT n.nspname as "Schema",
  count(pg_catalog.format_type(t.oid, NULL)) AS ct
FROM pg_catalog.pg_type t
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
WHERE (t.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid))
  AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
      and n.nspname <> 'matrixts_internal'
      and n.nspname <> 'gp_toolkit'
group BY 1;




--language
SELECT l.lanname AS "Name",
       pg_catalog.pg_get_userbyid(l.lanowner) as "Owner",
       l.lanpltrusted AS "Trusted",
       d.description AS "Description"
FROM pg_catalog.pg_language l
LEFT JOIN pg_catalog.pg_description d
  ON d.classoid = l.tableoid AND d.objoid = l.oid
  AND d.objsubid = 0
WHERE l.lanplcallfoid != 0
ORDER BY 1;




--role
SELECT count(r.rolname) as ct_role
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
;

修改 gp6 端口

将 gp6 集群的端口修改为一个不被占用的端口，防止后面升级完成后想启动 gp6 验证一些内容时，端口与 YMatrix 冲突无法启动。

#utility模式进库
su - gpadmin
[gpadmin@mdw ~]$ PGOPTIONS='-c gp_session_role=utility' psql postgres


--修改master和standby端口
postgres=# BEGIN;
BEGIN
postgres=# set allow_system_table_mods=true;
SET
postgres=# update gp_segment_configuration set port=6432 where content=-1;
UPDATE 2
--验证修改已成功
postgres=# select * from gp_segment_configuration where content=-1;


postgres=# end;
COMMIT




#停止集群
gpstop -af


#修改postgresql.conf（如果有standby也要修改）
[gpadmin@mdw ~]$ vi $MASTER_DATA_DIRECTORY/postgresql.conf
修改前:
port = 5432
修改后:
port = 6432


#修改环境变量
vi home/gpadmin/.bash_profile
修改前：
export PGPORT=5432
修改后：
export PGPORT=6432


#新的参数立马生效
source home/gpadmin/.bash_profile 


#启动集群,校验是否修改成功
gpstart -a


#检查端口
psql postgres


postgres=# show port;

停止 gp6 集群

sudo su - gpadmin
gpstop -fa

修改 YMatrix 端口

将 YMatrix的Master端口修改为和 gp6 集群 Master 节点的端口一致。

#utility模式进库
su - mxadmin
[mxadmin@mdw1 ~]$ PGOPTIONS='-c gp_role=utility' psql postgres


--修改master和standby端口
postgres=# BEGIN;
BEGIN
postgres=# set allow_system_table_mods=true;
SET
postgres=# update gp_segment_configuration set port=5432 where content=-1;
UPDATE 2
--验证修改已成功
postgres=# select * from gp_segment_configuration where content=-1;


postgres=# end;
COMMIT


#停止集群
gpstop -af


#修改postgresql.conf（如果有standby也要修改）
[mxadmin@mdw1 ~]$ vi $MASTER_DATA_DIRECTORY/postgresql.conf
修改前:
port = 6433
修改后:
port = 5432


#修改环境变量
vi home/mxadmin/.matrixdb.env
修改前：
export PGPORT=6433
修改后：
export PGPORT=5432


#新的参数立马生效
source home/mxadmin/.matrixdb.env


#修改以下（不限于）文件中的port值
cat etc/matrixdb/supervisor.conf
cat etc/matrixdb/cluster.conf
cat etc/matrixdb/service/telegraf.conf
cat etc/matrixdb/service/mxmgr_gate_ctrl.conf
cat etc/matrixdb/service/mxmgr_telegraf_ctrl.conf


#启动集群
gpstart -a


#如果之前有部署监控，需要修改监控的端口号
psql -d matrixmgr
select * from local.matrix_manager_config ;
update local.matrix_manager_config set value='5432' where values='6433';
SELECT mxmgr_remove_all('local');
SELECT mxmgr_deploy('local');


#重启supervisor
#master节点root用户登录
systemctl stop matrixdb.supervisor.service
systemctl start matrixdb.supervisor.service

调优 YMatrix 数据库参数

#根据服务器硬件资源进行调整
gpconfig -c shared_buffers -v 6GB
gpconfig -c statement_mem -v 2GB
gpconfig -c work_mem -v 2GB
gpconfig -c max_statement_mem -v 4GB
gpconfig -c maintenance_work_mem -v 2GB
gpconfig -c effective_cache_size -v 64GB
gpconfig -c gp_workfile_limit_per_segment -v 128GB
gpconfig -c max_connections -v 3000 -m 800
gpconfig -c max_prepared_transactions -v 1500 -m 500
gpconfig -c superuser_reserved_connections -v 150 -m 30


#这部分的参数可以作为通用数仓场景调整
gpconfig -c runaway_detector_activation_percent -v 90
gpconfig -c statement_timeout -v 0
gpconfig -c gp_segment_connect_timeout -v 3min
gpconfig -c gp_fts_probe_retries -v 30
gpconfig -c resource_scheduler -v on
gpconfig -c mx_interconnect_compress -v off   #网络性能较差的环境建议开启
gpconfig -c tcp_keepalives_idle -v 7200
gpconfig -c gp_enable_global_deadlock_detector -v on
gpconfig -c gp_global_deadlock_detector_period -v 2min
gpconfig -c enable_hashjoin -v on
gpconfig -c enable_mergejoin -v off
gpconfig -c enable_nestloop -v off
gpconfig -c enable_seqscan -v on
gpconfig -c enable_bitmapscan -v on
gpconfig -c gp_max_packet_size -v 32000
gpconfig -c gp_snapshotadd_timeout -v 60s --skipvalidation
gpconfig -c gp_autostats_mode -v none
gpconfig -c enable_parallel_mode -v on
gpconfig -c max_parallel_workers_per_gather -v 2
gpconfig -c parallel_tuple_cost -v 10000000
gpconfig -c random_page_cost -v 1.1
gpconfig -c parallel_setup_cost -v 1000000
gpconfig -c join_collapse_limit -v 10
gpconfig -c from_collapse_limit -v 10
gpconfig -c wal_keep_segments -v 128
gpconfig -c max_wal_size -v 4GB
gpconfig -c log_transaction_sample_rate -v 0 -m 0.001
gpconfig -c log_checkpoints -v on
gpconfig -c log_statement -v none -m ddl
gpconfig -c log_duration -v off
gpconfig -c log_min_duration_statement -v 5000ms
gpconfig -c log_rotation_age -v 1d
gpconfig -c log_timezone -v PRC
gpconfig -c log_min_messages -v warning
gpconfig -c mxstat_statements.harvest_interval -v '10min'
gpconfig -c mxstat_statements.harvest_usage -v off
gpconfig -c gp_max_slices -v 50

调优内核参数

YMatrix使用UI部署后，另外添加的内核参数


vim etc/sysctl.d/99-matrixdb.conf 


net.core.somaxconn = 65535
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_max_syn_backlog = 65535
net.netfilter.nf_conntrack_max = 6553600
net.core.netdev_max_backlog = 65535

重新加载内核参数
sysctl --load=/etc/sysctl.d/99-matrixdb.conf

调优网卡参数

调整网卡参数为了防止网络丢包


#根据实际网卡名称替换
ethtool -G ens9f0 rx 8192 tx 8192
ethtool -G ens3f0 rx 8192 tx 8192


ethtool -K ens9f0 gro off
ethtool -K ens3f0 gro off


ethtool -K ens9f0 lro off
ethtool -K ens3f0 lro off

配置监控

https://www.ymatrix.cn/doc/4.7/monitor/deploy_monitor

配置 YMatrix 资源组

开启资源组配置

--验证
grep cgroup/proc/mounts
ls -l sys/fs/cgroup/cpu/${db_cluster_id}/gpdb


--开启资源组
gpconfig -c gp_resource_manager -v group
gpconfig -c resource_scheduler -v on
gpconfig -c gp_resource_group_memory_limit -v 0.9


--重启数据库
mxstop -raf

role 分配默认资源组

--修改资源组资源限制
ALTER RESOURCE GROUP default_group SET CPU_RATE_LIMIT 60;
ALTER RESOURCE GROUP default_group SET MEMORY_LIMIT 60;
ALTER RESOURCE GROUP default_group SET CONCURRENCY 200;
ALTER RESOURCE GROUP admin_group SET CONCURRENCY 50;


--将执行出来的SQL，复制在数据库里面再执行。
SELECT 'alter role '||a.rolname||' RESOURCE GROUP default_group;'  
FROM pg_roles a
inner join pg_resgroup b 
on a.rolresgroup=b.oid 
and rsgname <> 'admin_group';

开启 YMatrix 对外访问

修改白名单文件

#将gp6集群原始的pg_hba.conf文件里面开通的业务访问白名单信息复制到YMatrix的pg_hba.conf文件里面，切记是以追加的方式进行添加，YMatrix原始的pg_hba.conf文件里面的内容记得保留。


su - mxadmin
vi $MASTER_DATA_DIRECTORY/pg_hba.conf


#重新加载配置文件
gpstop -u

配置 vacuum 定时任务

编辑脚本

[mxadmin@IT0140015 crontab]$ vi vacuum_v4.sh 
#!/bin/bash


if [ $# -ne 1 ]
then
  echo "Usage $0 database[1]"
exit 1;
fi


source home/mxadmin/.bash_profile


dataname=$1
vacuum_sql_dir=/home/mxadmin/vacuum_log
is_vacuum=true


mkdir -p ${vacuum_sql_dir}/${dataname}


starttime=`date +"%Y-%m-%d %H:%M:%S"`
startts=`date +%s`
echo ">>> ---------------------------------------------------------------------- <<<"
echo ">>> --------------------$starttime start vacuum------------------ <<<"
echo ">>> ---------------------------------------------------------------------- <<<"


fun_sql="CREATE or replace FUNCTION is_vacuum(schemaname text,tablename text) RETURNS bool AS \$\$
DECLARE
     is_vacuum bool := false;
     tablename text;
     threshold float;
BEGIN
    select current_setting('gp_appendonly_compaction_threshold') INTO threshold;
    tablename:=\$1||'.'||\$2;
    select (case when 100*sum(hidden_tupcount)/sum(total_tupcount)::numeric(32,10) > threshold then true else false end) as bloat into is_vacuum from gp_toolkit
.__gp_aovisimap_compaction_info(tablename::regclass);
    
    RETURN is_vacuum;
END;
\$\$ LANGUAGE plpgsql;"


#psql -Aqc "$fun_sql" -d$dataname




VACUUM_ANALYZE(){
    echo ">>> `date +"%Y-%m-%d %H:%M:%S"` 开始Vacuum $1"
    if [ "$is_vacuum" = "true" ];then 
        psql -c"\timing" -Aqc "vacuum (analyze,skip_locked) $1" -d$dataname
    fi
    echo ">>> `date +"%Y-%m-%d %H:%M:%S"` $1 Vacuum 完成"
}




VACUUM_ANALYZE_AO(){
    echo ">>> `date +"%Y-%m-%d %H:%M:%S"` 开始Vacuum $1"
        psql -c"\timing" -Aqc "vacuum (analyze,skip_locked) $1" -d$dataname
    echo ">>> `date +"%Y-%m-%d %H:%M:%S"` $1 Vacuum 完成"
}




reorganize(){
    echo ">>> `date +"%Y-%m-%d %H:%M:%S"` 开始Vacuum $1"
    psql -c"\timing" -Aqc "alter table $1 set with(reorganize=true) " -d$dataname
    echo ">>> `date +"%Y-%m-%d %H:%M:%S"` $1 Vacuum 完成"
}


#vacuum analyze catalog
catalog_start=`date +"%Y-%m-%d %H:%M:%S"`
catalog_sts=`date +%s`
echo ">>> $catalog_start 开始Vacuum Analyze Catalog"
SQL="select schemaname||'.'||relname from pg_stat_sys_tables where n_live_tup>0 and n_dead_tup/n_live_tup::float>0.1 and schemaname in ('pg_catalog','informatio
n_schema');"
for table in `psql -X -Atc "$SQL" -d$dataname`
do
    echo "vacuum analyze $table;" >> $vacuum_sql_dir/$dataname/vacuum_catalog_`date '+%Y%m%d'`.sql
    VACUUM_ANALYZE $table
done
catalog_end=`date +"%Y-%m-%d %H:%M:%S"`
catalog_ets=`date +%s`
echo ">>> $catalog_end Vacuum Analyze Catalog 结束，耗时：$((catalog_ets-catalog_sts))s"


echo ">>> ---------------------------------------------------------------------- <<<"
echo ">>> ---------------------------------------------------------------------- <<<"
echo ">>> ---------------------------------------------------------------------- <<<"


#vacuum analyze ao table
aotable_start=`date +"%Y-%m-%d %H:%M:%S"`
aotable_sts=`date +%s`
echo ">>> $aotable_start 开始Vacuum Analyze AO"


THRESHOLD=`psql  -Aqt -c "select current_setting('gp_appendonly_compaction_threshold')" -d $dataname`


for TABLE_AO_LIST in `
    psql -Aqt -c "
    select 
     (c.relnamespace::regnamespace)::text||'.'||c.relname
    from pg_class c 
     inner join pg_am am on am.oid=c.relam 
     where am.amname in ('ao_row','ao_column')" -d $dataname`


do


    ANALYZE_TABLE=`
        psql  -Aqt -c "SELECT table_name from
        (
        select '${TABLE_AO_LIST}' as table_name,
               (case when 100*sum(hidden_tupcount)/sum(total_tupcount)::numeric(32,10) > ${THRESHOLD} then true 
               else false end) as bloat
        from gp_toolkit.__gp_aovisimap_compaction_info('${TABLE_AO_LIST}'::regclass)
        ) f where bloat=true and f.bloat is not null"  -d $dataname`
    if [[ $ANALYZE_TABLE != '' ]]
    then
    echo "vacuum analyze $ANALYZE_TABLE;" >> $vacuum_sql_dir/$dataname/vacuum_ao_`date '+%Y%m%d'`.sql
    echo "alter table $ANALYZE_TABLE set with(reorganize=true);" >> $vacuum_sql_dir/$dataname/reorganize_ao_`date '+%Y%m%d'`.sql
    VACUUM_ANALYZE_AO $ANALYZE_TABLE
    #reorganize $ANALYZE_TABLE
    fi
 done


aotable_end=`date +"%Y-%m-%d %H:%M:%S"`
aotable_ets=`date +%s`
echo ">>> $aotable_end Vacuum Analyze AO 结束，耗时：$((aotable_ets-aotable_sts))s"




echo ">>> ---------------------------------------------------------------------- <<<"
echo ">>> ---------------------------------------------------------------------- <<<"
echo ">>> ---------------------------------------------------------------------- <<<"
#vacuum analyze heap table
heaptable_start=`date +"%Y-%m-%d %H:%M:%S"`
heaptable_sts=`date +%s`
echo ">>> $heaptable_start 开始Vacuum Analyze Heap"
SQL="select a.schemaname||'.'||a.relname from pg_class c inner join pg_stat_all_tables a on a.relname=c.relname and a.schemaname=(c.relnamespace::regnamespace):
:name inner join pg_am am on am.oid=c.relam where am.amname in('heap','mars2') and n_live_tup>0 and n_dead_tup/n_live_tup::float>0.1 and a.schemaname not in ('i
nformation_schema','pg_catalog');"
for table in `psql -X -Atc "$SQL" -d$dataname`
do
    echo "vacuum analyze $table;" >> $vacuum_sql_dir/$dataname/vacuum_heap_`date '+%Y%m%d'`.sql
    VACUUM_ANALYZE $table
done
heaptable_end=`date +"%Y-%m-%d %H:%M:%S"`
heaptable_ets=`date +%s`
echo ">>> $heaptable_end Vacuum Analyze Heap 结束，耗时：$((heaptable_ets-heaptable_sts))s"


endtime=`date +"%Y-%m-%d %H:%M:%S"`
endts=`date +%s`
echo ">>> ---------------------------------------------------------------------- <<<"
echo ">>> --------------$endtime end vacuum，耗时：$((endts-startts))s--------------- <<<"
echo ">>> ---------------------------------------------------------------------- <<<"

配置定时任务

#创建日志目录
mkdir -p /home/mxadmin/vacuum_log/testdb


#定时任务
crontab -e 


35 */1 * * * sh /home/mxadmin/crontab/vacuum_v4.sh testdb | grep -v "gp_appendonly_compaction_threshold " >> /home/mxadmin/vacuum_log/testdb/vacuum_`date '+\%Y\%m\%d'`.log 2>&1