Mackerel → Grafana

# 概要

* Mackerel のメトリックを Grafana で見る。

# 目的

* Mackerel の (無償プランでは) 保存期間が短いので、定期的に Mackerel API でデータを取得して、Grafana で長期的なリソース分析をできるようにする。

# 構成

OS: Fedora 35
DB: PostgreSQL 13
Server: Apache 2.4
その他: jq

# 構築

## grafana

* リポジトリの追加

```bash:
$ sudo vi /etc/yum.repos.d/grafana.repo
```
```conf:grafana.repo
[grafana]
name=grafana
baseurl=https://packages.grafana.com/oss/rpm
repo_gpgcheck=1
enabled=1
gpgcheck=1
gpgkey=https://packages.grafana.com/gpg.key
sslverify=1
sslcacert=/etc/pki/tls/certs/ca-bundle.crt
```

* インストール

```bash:
$ sudo dnf install grafana
```

* 設定

```bash:
$ sudo vi /etc/grafana/grafana.ini
```

公開URI

```ini:grafana.ini
;root_url = %(protocol)s://%(domain)s:%(http_port)s/
root_url = %(protocol)s://%(domain)s:%(http_port)s/grafana
```

* 起動

```bash:
$ sudo systemctl daemon-reload
$ sudo systemctl start grafana-server
$ sudo systemctl status grafana-server
$ sudo systemctl enable grafana-server
```

## Apache

`既にインストール済み`

* 設定

```bash:
$ sudo vi /etc/httpd/conf.d/ssl.conf
```
```diff_conf:ssl.conf
<VirtualHost _default_:443>
...
+ ProxyPass "/grafana" "http://127.0.0.1:3000"
+ ProxyPassReverse "/grafana" "http://127.0.0.1:3000"
...
</VirtualHost>
```

* サービスの再起動

```bash:
$ sudo httpd -t
$ sudo systemctl restart httpd
```

`SSL サーバー証明書は Let's Encrypt (ISRG) を使用`

## DB

`PostgreSQL はインストール済み`

* DB、およびテーブルの作成

```bash:
$ sudo su - postgres
```
```sql:
postgres=# createdb mackereldb
postgres=# psql -d mackereldb
mackereldb=# CREATE TABLE hosts (
    id serial primary key,
    agent_id varchar(11) unique not null,
    name varchar(20) unique not null,
    status varchar(20) not null,
    created_at timestamptz not null,
    is_retired boolean not null
)
mackereldb=# CREATE TABLE graphs (
    id serial primary key,
    host_id integer not null,
    metric_name varchar(60) not null,
    foreign key (host_id) references hosts (id)
)
mackereldb=# CREATE TABLE metrics (
    graph_id integer not null,
    time integer not null,
    value numeric not null
)
```
* **【重要】** metrics テーブルに複数列のインデックスを追加する (`graph_id` と `time` を指定して検索するのが通常のため) ← Zabbix のデータベースを参考
```sql:
mackereldb=# create index metrics_idx on metrics (graph_id, time);
```

* ユーザーの追加
    * grafana: grafana がアクセス (読み取り専用)
    * sysuser: Mackerel からメトリックを取得して DB へストア

```bash:
$ sudo su - postgres
```
```bash:
# createuser grafana -P
# createuser sysuser -P
# psql -d mackereldb
```
```sql:
mackereldb=# grant select on graphs to grafana;
mackereldb=# grant select on hosts to grafana;
mackereldb=# grant select on metrics to grafana;
```
```sql:
mackereldb=# grant all on graphs to sysuser;
mackereldb=# grant all on hosts to sysuser;
mackereldb=# grant all on metrics to sysuser;
mackereldb=# grant all on sequence graphs_id_seq to sysuser;
mackereldb=# grant all on sequence hosts_id_seq to sysuser;
```

* ホワイトベース認証の設定

```bash:
$ sudo vi /var/lib/pgsql/14/data/pg_hba.conf
```
```diff_conf:pg_hba.conf
+ host    all             grafana         127.0.0.1/32            md5
+ local   all             sysuser                                 md5
```

* DB アクセスパスワードの設定

```bash:
$ sudo touch ~sysuser/.pgpass
$ sudo chown sysuser. ~sysuser/.pgpass
$ sudo vi ~sysuser/.pgpass
```
```conf:.pgpass
*:*:*:*:パスワード
```
```bash:
$ sudo chmod 600 ~sysuser/.pgpass
```

* サービスの再起動

```bash:
$ sudo systemctl restart postgresql-13.service
```

## Mackerel

* バッチファイルなどの構成
```
~/mackerel
├ metrics
│ ├ {YYYYmm}
│ │  ├ {graph_id}-{YYYYmmddhh}.csv
│ │  ├ {YYYYmmdd}.tar.gz
│ │  ├ ...
│ ├ ...
│
├ archive_last_day.sh
├ backup_host_metrics.sh
├ backup_metrics.sh
├ get_hosts.sh
├ get_metric_names.sh
├ get_metric_names.sh
├ get_metrics.sh
└ init_tables.sh
``` 
```bash:backup_metrics.sh
#!/bin/bash

dbuser=sysuser

cd /home/sysuser/mackerel

IFS=$'\n'
for line in `./get_hosts.sh`
do
    agent_id=`echo $line | awk '{print $1}'`
    name=`echo $line | awk '{print $2}'`
    stat=`echo $line | awk '{print $3}'`
    created=`echo $line | awk '{print $4}'`
    retired=`echo $line | awk '{print $5}'`
    host_id=$(psql -U $dbuser -d mackereldb -t -A -c "select id from hosts where agent_id='$agent_id'")
    echo "select: $host_id" 
    if [ -z "$host_id" ] ; then
        res=$(psql -U $dbuser -d mackereldb -c "insert into hosts (agent_id,name,status,created_at,is_retired) values ('$agent_id','$name','$stat',to_timestamp($created),'$retired')")
        echo "insert: $res" 
        host_id=$(psql -U $dbuser -d mackereldb -t -A -c "select id from hosts where agent_id='$agent_id'")
    fi
    echo "host id: $host_id" 

    enabled=$(psql -U $dbuser -d mackereldb -t -A -c "select is_enabled from hosts where id=$host_id")
    if [ "$enabled" == "f" ] ; then
        continue
    fi

    ./backup_host_metrics.sh $host_id $agent_id &

done
```
```bash:backup_host_metrics.sh
#!/bin/bash

host_id=$1
agent_id=$2

to_date=$(($(date +%s)-($(date +%s)%3600)))

if [ -z "$host_id" ] ; then
    echo "host_id is empty." 
    exit 1
fi

if [ -z "$agent_id" ] ; then
    echo "agent_id is empty." 
    exit 1
fi

dbuser=csuser

cd /home/csuser/mackerel

for metric in `./get_metric_names.sh -H $agent_id | jq -r '.names[]'`
do
    graph_id=$(psql -U $dbuser -d mackereldb -t -A -c "select id from graphs where host_id=$host_id and metric_name='$metric'")
    if [ -z "$graph_id" ] ; then
        res=$(psql -U $dbuser -d mackereldb -c "insert into graphs (host_id,metric_name) values ('$host_id','$metric')")
        echo "insert: $res" 
        graph_id=$(psql -U $dbuser -d mackereldb -t -A -c "select id from graphs where host_id=$host_id and metric_name='$metric'")
    fi
    echo "$graph_id - $metric" 
    from_date=$(($(psql -U $dbuser -d mackereldb -t -A -c "select max(time) from metrics where graph_id=$graph_id")+60))
    ./import2db.sh $graph_id $agent_id $metric $from_date $to_date
done
```
```bash:get_hosts.sh
#!/bin/bash

api_key=MackerelAPIキー

/usr/bin/curl \
    -s "https://api.mackerelio.com/api/v0/hosts" \
    -H "Content-Type: application/json" \
    -H "X-Api-Key: $api_key" \
    -X GET | /usr/bin/jq -r '.hosts | map("\(.id)\t\(.name)\t\(.status)\t\(.createdAt)\t\(.isRetired)") | join("\n")'
```
```bash:get_metric_names.sh
#!/bin/bash

api_key=MackerelAPIキー
host_id=

if [ $# -gt 0 ]; then
        while [ $# -gt 0 ]
        do
            param=$1
            case $param in
                -H | --host)
                    host_id=$2
                    ;;
                *)
                    ;;
            esac
            shift
        done
fi

if [ -z $host_id ]; then
    echo "- {host_id} is required (-H, --host {host_id})" 
    exit -1
fi

/usr/bin/curl \
    -s "https://api.mackerelio.com/api/v0/hosts/$host_id/metric-names" \
    -H "Content-Type: application/json" \
    -H "X-Api-Key: $api_key" \
    -X GET
```
各メトリック値は一旦CSVへ落として、DB へインポート (`\copy` コマンド)
```bash:import2db.sh
#!/bin/bash

graph_id=$1
agent_id=$2
metric=$3
from_date=$4
to_date=$5

if [ -z "$graph_id" ] ; then
        echo "graph_id is empty." 
        exit 1
fi
if [ -z "$agent_id" ] ; then
        echo "agent_id is empty." 
        exit 1
fi
if [ -z "$metric" ] ; then
        echo "metric is empty." 
        exit 1
fi
if [ -z "$from_date" ] ; then
        echo "from_date is empty." 
        exit 1
fi
if [ -z "$to_date" ] ; then
        echo "to_date is empty." 
        exit 1
fi

dbuser=sysuser

cd /home/sysuser/mackerel

fname=`/usr/bin/date --date @$from_date +%Y%m%d%H%M`
csv=$graph_id-$fname.csv
dir=./metrics/`date +%Y%m`

if [ ! -d $dir ] ; then
        mkdir $dir
fi

for tv in `./get_metrics.sh -H $agent_id -f $from_date -t $to_date $metric | jq -r '.metrics | map("\(.time),\(.value)") | join("\n")'`
do
        time=`echo $tv | awk -F, '{print $1}'`
        value=`echo $tv | awk -F, '{print $2}'`

        echo "$graph_id,`date --date @$time '+%F %T'`,$value" >> $dir/$csv
done

echo "copying..." 
psql -U $dbuser -d mackereldb -c "\copy metrics FROM '$dir/$csv' WITH (FORMAT CSV)" 
echo "done." 
```
```bash:get_metrics.sh
#!/bin/bash

api_key=MackerelAPIキー
to_date=$(($(date +%s)-($(date +%s)%3600)))
from_date=$(expr $to_date - 3600)
host_id=
name=

if [ $# -gt 0 ]; then
        while [ $# -gt 0 ]
        do
            param=$1
            case $param in
                -H | --host)
                    host_id=$2
            shift
                    ;;
                -f | --from)
                    from_date=$2
            shift
                    ;;
                -t | --to)
                    to_date=$2
            shift
                    ;;
                *)
                    name=$1
                    ;;
            esac
            shift
        done
fi

if [ -z "$host_id" ]; then
        echo "- {host_id} is required (-H, --host {host_id})" 
fi

if [ -z "$name" ]; then
        echo "- metric name is required" 
fi

if [ -z "$host_id" ] || [ -z "$name" ]; then
        exit 1
fi

/usr/bin/curl \
    -s "https://api.mackerelio.com/api/v0/hosts/$host_id/metrics?name=$name&from=$from_date&to=$to_date" \
    -H "Content-Type: application/json" \
    -H "X-Api-Key: $api_key" \
    -X GET
```
```bash:achive_last_day.sh
#!/bin/bash

epoch=$(($(date +%s) - 24*60*60))
dir_name=$(date --date @$epoch +%Y%m)
last_day=$(date --date @$epoch +%Y%m%d)

cd /home/sysuser/mackerel/metrics/$dir_name

find . -type f -name "*$last_day*.csv" | xargs tar --remove-files -czf $last_day.tar.gz
```
初回のテーブルデータ格納用 (現在の mackerel-agent のホストと、メトリックスをDBへ保存)
```bash:init_tables.sh
#!/bin/bash

dbuser=sysuser

cd /home/sysuser/mackerel

IFS=$'\n'
for line in `./get_hosts.sh`
do
        agent_id=`echo $line | awk '{print $1}'`
        name=`echo $line | awk '{print $2}'`
        stat=`echo $line | awk '{print $3}'`
        created=`echo $line | awk '{print $4}'`
        retired=`echo $line | awk '{print $5}'`
        res=$(psql -U $dbuser -d mackereldb -c "insert into hosts (agent_id,name,status,created_at,is_retired) values ('$agent_id','$name','$stat',to_timestamp($created),'$retired')")
        echo "insert: $res" 
        host_id=$(psql -U $dbuser -d mackereldb -t -A -c "select id from hosts where agent_id='$agent_id'")
        echo "host id: $host_id" 

        for metric in `./get_metric_names.sh -H $agent_id | jq -r '.names[]'`
        do
                res=$(psql -U $dbuser -d mackereldb -c "insert into graphs (host_id,metric_name) values ('$host_id','$metric')")
                echo "insert: $res" 
                echo "graph id($metric): $(psql -U $dbuser -d mackereldb -t -A -c "select id from graphs where host_id=$host_id and metric_name='$metric'")" 
        done
done
```

* cron 設定 (1時間おきに実行)

```
$ sudo vi /var/spool/cron/sysuser
```
```conf:crontab
# backup mackerel metrics
0 */1 * * * ~/mackerel/backup_metrics.sh
# archive mackerel metrics
0 1 * * * ~/mackerel/archive_last_day.sh
```