通过Java定时取消Greenplum的慢查询

重点来啦，直接上干货，走起

package cn.xhh.monitor.task;
 
import java.time.LocalDate;
import java.time.LocalTime;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.TimeUnit;
import javax.annotation.PostConstruct;
import javax.annotation.Resource;
 
import com.google.common.base.Strings;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
 
@Component
@Slf4j
public class AutoCancelSlowSql {
 
    @Resource
    private JdbcTemplate redshiftJdbcTemplate;
 
    @Resource
    private JdbcTemplate mysqlJdbcTemplate;
 
    @PostConstruct
    public void init() {
        execute();
        executeProduction();
        log.info("定时取消慢查询任务开始执行，周期30秒钟");
    }
 
    public void execute() {
        Runnable runnable = new Runnable() {
            public void run() {
                int hour = LocalTime.now().getHour();
                log.info("当前小时：{}。", hour);
                if (hour < 5) {
                    return;
                }
 
                log.info("取消慢查询任务执行");
                try {
                    // 超过3分钟的查询，取消掉
                    String querySlowSql = "select pid, (now() - query_start) as exec, query from pg_stat_activity where  pg_stat_activity.query <> ''::text AND pg_stat_activity.state <> 'idle'::text and (now() - query_start) > '00:03:00';";
                    cancelActiveSql(querySlowSql);
 
                    // 清除活跃session，超过10分钟的就kill掉
                    String querySessionSql = "select pid, (now() - query_start) as exec, query from pg_stat_activity where usename not in ('gpadmin', 'gpmon') and (now() - query_start) > '00:05:00' order by now() - query_start desc limit 10";
                    cancelActiveSql(querySessionSql);
 
                    // 清除vacuum full 超过1小时就kill掉
                    String queryVacuumSql = "select pid, (now() - query_start) as exec, query from pg_stat_activity where usename = 'gpadmin' and (now() - query_start) > '01:00:00' and query like 'vacuum full%';";
                    cancelActiveSql(queryVacuumSql);
                } catch (Exception e) {
                    log.error("定时取消慢查询任务出错:" + e.getMessage());
                }
            }
        };
 
        ScheduledExecutorService service = Executors.newSingleThreadScheduledExecutor();
        // 第二个参数为首次执行的延时时间，第三个参数为定时执行的间隔时间
        service.scheduleAtFixedRate(runnable, 30, 30, TimeUnit.SECONDS);
    }
 
    private void cancelActiveSql(String querySql) {
        List> slowSqlList = redshiftJdbcTemplate.queryForList(querySql);
        if (slowSqlList.size() > 0) {
            slowSqlList.forEach(result -> {
                try {
                    String pid = result.get("pid").toString();
                    String query = result.get("query").toString();
                    Object exec = result.get("exec");
                    if (exec != null && !exec.toString().equals("")) {
                        log.info("canal sql pid : {}, exec {}.", pid, exec);
                        String sql = null;
                        if (!Strings.isNullOrEmpty(query) && query.toLowerCase().trim().startsWith("select")) {
                            sql = "select pg_cancel_backend( ? )";
                        } else {
                            sql = "select pg_terminate_backend( ? )";
                        }
 
                        redshiftJdbcTemplate.execute(sql, (CallableStatementCallback