DWS节点扩容一---重分布常用SQL

1. 查看重分布进度

gsql -dpostgres -p 8000 -c 'SELECT * FROM redis_progress ORDER BY name;'

2. 查看还需要重分布的大表

select relname,pg_size_pretty(table_size) from pgxc_redistb where redistributed != 'y' and pg_size_pretty(table_size) like '%GB' order by 2 desc;

3. 查看重分布配置

gsql -dpostgres -p 8000 -c 'SELECT * FROM redis_config;'

4. 查看重分布完成表清单

gsql -d postgres -p 8000 -c 'SELECT * FROM redis_progress_detail;'

5. 更新并发度

gsql –d postgres -p 8000 -c "update redis_config set attributeValue='12' where attributeName='maxWorkerNum';"

6. 更新大小表优化

调整大表优先模式：

update redis_config set attributeValue = 'large' where attributeName = 'tableSelectionMethod' ;

调整小标优先模式：

update redis_config set attributeValue = 'small' where attributeName = 'tableSelectionMethod' ;

调整常规模式：

update redis_config set attributeValue = 'default' where attributeName = 'tableSelectionMethod' ;

调整参数信息：

update redis_config set attributeValue='12' where attributeName='defaultInsertParallelNum';

7. kill 重分布进程

ps -ef |grep gs_redis

kill -9 xxx

8. 查看会话

select coorname, usename, client_addr, query_start,sysdate-query_start as dur, state, enqueue,waiting, pid,query_id, substr(query,1,100),datname from pgxc_stat_activity where state = 'active' order by dur desc;

9. 查看业务会话

select coorname, usename, client_addr, query_start,sysdate-query_start as dur, state, enqueue,waiting, pid,query_id, substr(query,1,100),datname from pgxc_stat_activity where usename != 'Ruby' and state = 'active' order by dur desc;

10. 查看重分布会话

select coorname, usename, client_addr, query_start,sysdate-query_start as dur, state, enqueue,waiting, pid,query_id, substr(query,1,200),datname from pgxc_stat_activity where application_name = 'gs_redis' and state = 'active' and query like '%xx%';

11. 查询分区表重分布活跃语句

select coorname, usename, client_addr, query_start,sysdate-query_start as dur, state, enqueue,waiting, pid,query_id, substr(query,1,200),datname from pgxc_stat_activity where application_name = 'gs_redis' and state = 'active' and query not like '%partition%';

12. 查看临时表

select pid,query_start,query_id,substring(query,50,200) from pg_stat_activity where application_name='gs_redis' and state='active';

13. 查看节点

select * from pgxc_node;

14. 查看group

select group_name from pgxc_group

15. 需要查看还剩多少表未重分布，步骤如下：

         1）、 找到老的nodegroup名称old_group_name

            select group_name from pgxc_group where in_redistribution='y';

         2）、 分别连接每个database统计各个database下未重分布的表数量

             select count(*) from pgxc_class where pgroup='old_group_name';

         3）、 如果想知道具体未重分布的表名称，可换成如下sql：

             select pcrelid::regclass from pgxc_class where pgroup='old_group_name';

16. 异常处理

ALTER TABLE '||tablename||' SET (append_mode = off) '

17. 屏蔽/重分布跳过部分表

1）update pgxc_class set redis_order=0 where pcrelid={oid};

然后重新拉起

gsql -dxxxx -p 8000 -c " ALTER TABLE pgxc_redistb SET (append_mode=off); "

gsql -dxxxx -p 8000 -c " UPDATE pgxc_redistb SET redis_order=0 WHERE nspname= 'xx' and relname= 'xx';

select relname from pg_class where reloptions::text like "%append_mode%";

ALTER TABLE '||tablename||' SET (append_mode = off) '

18. 拉起在线重分布

nohup gs_expand -t redistribute --fast-redis --parallel-jobs=4 --redis-mode=insert --dws-mode &

19. 查看重分布日志

cd $GAUSSLOG/bin/gs_redis 看一下最新的日志

20. 查看锁等待,query_id 从会话中获取

select * from pg_thread_wait_status where query_id=xxx;

21. 删除残留的临时表

gs_guc reload -Z coordinator -Z datanode -N all -I all -h "local all all trust"

gsql –d edw -p 8000 -ar

set xc_maintenance_mode=on；

drop schema if exists xxx cascade;

22. 手动重分布小表

start transaction;

set enable_cluster_resize=on;

truncate dbms_om.gs_wlm_session_info;

alter table dbms_om.gs_wlm_session_info to group xxxx;

commit;

23. 元数据不一致时，设置维护模式，然后删除表

set xc_maintenance_mode=on；

drop table IF EXISTS xxx；