EC2自定义监控推到cloudwatch

已ec2自建gp连接数监控为例：

编写监控脚本monitor.sh配置文件：

#!/usr/bin/env bash
. /home/gpadmin/.bashrc
export PGPASSWORD=xxxxx
home=/home/gpadmin/scripts/
host=xxxx
## transactions
psql -qtAX -h$host -p5432 -Udev_user -dcamel -f /home/gpadmin/scripts/sql/pgsql.transactions.sql > /home/gpadmin/scripts/logs/transactions.txt
## diskfree
psql -qtAX -h$host -p5432 -Udev_user -dcamel -f /home/gpadmin/scripts/sql/pgsql.diskfree.sql|sort -r > /home/gpadmin/scripts/logs/diskfree.txt
## connections
psql -qtAX -h$host -p5432 -Udev_user -dcamel -f /home/gpadmin/scripts/sql/pgsql.connections.sum.sql > /home/gpadmin/scripts/logs/connections.txt
## bgwriter
#psql -qtAX -h$host -p5432 -Udev_user -dcamel -f /home/gpadmin/scripts/sql/pgsql.bgwriter.sql > /home/gpadmin/scripts/logs/bgwriter.txt
## tps_qps
#psql -qtAX -h$host -p5432 -Udev_user -dcamel -f /home/gpadmin/scripts/sql/pgsql.tpqs.sql > /home/gpadmin/scripts/logs/tqps.txt
transactions_idle=$(cat /$home/logs/transactions.txt|jq .idle)
transactions_active=$(cat /$home/logs/transactions.txt|jq .active)
transactions_waiting=$(cat /$home/logs/transactions.txt|jq .waiting)
transactions_prepared=$(cat /$home/logs/transactions.txt|jq .prepared)
active_cnt=$(cat /$home/logs/connections.txt|jq .active)
idle_cnt=$(cat /$home/logs/connections.txt|jq .idle)
total_cnt=$(cat /$home/logs/connections.txt|jq .total)
#tps=$(cat /home/gpadmin/scripts/logs/tqps.txt|jq .segment_disk_free_gb|sed -n 4p)

#checkpoints_timed=$(cat /home/gpadmin/scripts/logs/bgwriter.txt|jq .checkpoints_timed)
#checkpoints_req=$(cat /home/gpadmin/scripts/logs/bgwriter.txt|jq .checkpoints_timed)
#buffers_checkpoint=$(cat /home/gpadmin/scripts/logs/bgwriter.txt|jq .checkpoints_timed)
#buffers_backend=$(cat /home/gpadmin/scripts/logs/bgwriter.txt|jq .checkpoints_timed)
#buffers_alloc=$(cat /home/gpadmin/scripts/logs/bgwriter.txt|jq .checkpoints_timed)
 
aws cloudwatch put-metric-data --metric-name transactions_idle --dimensions Instance=i-xxxx  --namespace "GreenPlum" --value $transactions_idle
aws cloudwatch put-metric-data --metric-name transactions_active --dimensions Instance=i-xxxx  --namespace "GreenPlum" --value $transactions_active
aws cloudwatch put-metric-data --metric-name transactions_waiting --dimensions Instance=i-xxx  --namespace "GreenPlum" --value $transactions_waiting
aws cloudwatch put-metric-data --metric-name transactions_prepared --dimensions Instance=i-xxx  --namespace "GreenPlum" --value $transactions_prepared
aws cloudwatch put-metric-data --metric-name active_cnt --dimensions Instance=i-xxx  --namespace "GreenPlum" --value $active_cnt
aws cloudwatch put-metric-data --metric-name idle_cnt --dimensions Instance=i-xxx  --namespace "GreenPlum" --value $idle_cnt
aws cloudwatch put-metric-data --metric-name total_cnt --dimensions Instance=i-xxx  --namespace "GreenPlum" --value $total_cnt

以上监控了gp会话，可用磁盘空间以及事物情况，可以自己根据实际需求修改。

然后把再服务器上放置好配置文件里面所需.sql查询语句。

gpadmin@ip-10-10-34-163:~/scripts$ cat sql/pgsql.connections.sum.sql 
DO LANGUAGE plpgsql $$
DECLARE
        ver integer;
        res text;
BEGIN
        SELECT current_setting('server_version_num') INTO ver;

        IF (ver >= 90600) THEN
                SELECT row_to_json(T) INTO res from (
                        SELECT
                                sum(CASE WHEN state = 'active' THEN 1 ELSE 0 END) AS active,
                                sum(CASE WHEN state = 'idle' THEN 1 ELSE 0 END) AS idle,
                                sum(CASE WHEN state = 'idle in transaction' THEN 1 ELSE 0 END) AS idle_in_transaction,
                                count(*) AS total,
                                count(*)*100/(SELECT current_setting('max_connections')::int) AS total_pct,
                                sum(CASE WHEN waiting_reason IS NOT NULL THEN 1 ELSE 0 END) AS waiting,
                                (SELECT count(*) FROM pg_prepared_xacts) AS prepared
                        FROM pg_stat_activity WHERE datid is not NULL
                        ) T;

        ELSE
                SELECT row_to_json(T) INTO res from (
                        SELECT
                                sum(CASE WHEN state = 'active' THEN 1 ELSE 0 END) AS active,
                                sum(CASE WHEN state = 'idle' THEN 1 ELSE 0 END) AS idle,
                                sum(CASE WHEN state = 'idle in transaction' THEN 1 ELSE 0 END) AS idle_in_transaction,
                                count(*) AS total,
                                count(*)*100/(SELECT current_setting('max_connections')::int) AS total_pct,
                                sum(CASE WHEN waiting IS TRUE THEN 1 ELSE 0 END) AS waiting,
                                (SELECT count(*) FROM pg_prepared_xacts) AS prepared
                        FROM pg_stat_activity
                        ) T;
        END IF;

        perform set_config('conn_json_res', res, false);
END $$;

select current_setting('conn_json_res');

每一个监控指标数据，都可以用sql写出来，写好sql查询指标放到指定目录，然后配置好awsconfig,然后在服务器上就可以取到所需值了。

最后把monitor.sh加到crontab,1min推送一次。

gpadmin@ip-10-10-34-163:~/scripts$ crontab -l

#*/1 * * * * script -c "/home/gpadmin/scripts/monitor.sh >>/tmp/crontab.log"
*/1 * * * * /home/gpadmin/scripts/monitor.sh >>/tmp/crontab.log  2>&1

最后查看cloudwatch指标，发现之前的监控图所示：

有数据收集上来表示没有问题。