TimescaleDB と Grafana
TimescaleDB とは?
Grafana とは?
- ログ・データなどの可視化ツール
- Apache License v2 で公開されているOSS
TimescaleDB と Grafana の組み合わせ
Grafana v5.3 で TimescaleDB への対応が機能追加されました。
この機能追加により TimescaleDB に格納した時系列データを Grafana を使って可視化できるようになりました。
こちらの内容を触ってみたので、書き留めておきます。
TimescaleDB、Grafana の セットアップログ
TimescaleDB/Grafanaともに、公式ドキュメントに、ディストリビューション毎のセットアップ手順が記載されていつので、そちらを確認してください。
私は Ubuntu Server 18.04 LTS 上にオールインワンで構築してみました。そのログを置いておきます。
(インストールバージョン)
PostgreSQL 10.5
TimescaleDB 0.12.1
Grafana 5.3.0-beta
※ 2018年10月頃にセットアップしたので、現在の最新バージョンより古め(TimescaleDBもv0.xx)です。手順は基本的には一緒だと思うのですが、正しくは公式ドキュメントを参照してください。
① PostgreSQL の インスト―ル
# apt-get update
# apt-get install postgresql
# psql --version
psql (PostgreSQL) 10.5 (Ubuntu 10.5-0ubuntu0.18.04)
# systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Fri 2018-09-28 17:25:06 JST; 27s ago
Main PID: 2725 (code=exited, status=0/SUCCESS)
Tasks: 0 (limit: 1118)
CGroup: /system.slice/postgresql.service
② TimescaleDB の インスト―ル
## add-apt-repository を使うための事前準備
# apt install software-properties-common
# add-apt-repository ppa:timescale/timescaledb-ppa
# apt-get update
## PG 10.2+はこのバージョンをインストールする
# apt install timescaledb-postgresql-10
③ postgresql.conf の設定変更
# cd /etc/postgresql/10/main/
# vi postgresql.conf
## shared_preload_libraries に timescaledb を追加する
==============================
<変更前>
#shared_preload_libraries = '' # (change requires restart)
↓
<変更後>
shared_preload_libraries = 'timescaledb' # (change requires restart)
==============================
## synchronous_commit を無効にする
==============================
<変更前>
#synchronous_commit = on # synchronization level;
↓
<変更後>
synchronous_commit = off # synchronization level;
==============================
## max_locks_per_transaction を とりあえず最小にする
==============================
<変更前>
#max_locks_per_transaction = 64 # min 10
↓
<変更後>
max_locks_per_transaction = 10 # min 10
==============================
## 反映
# service postgresql restart
④ postgresユーザのパスワード設定しておく ユーザ/postgresqlの両方
## ユーザパスワードの設定
# passwd postgres
# su - postgres
## posgresqlユーザパスワードの設定
postgres$ psql
postgres=# ALTER ROLE postgres with password 'postgres';
postgres=# SELECT * FROM pg_shadow;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+-------------------------------------+----------+-----------
postgres | 10 | t | t | t | t | md53175bce1d3201d16594cebf9d7eb3f9d | |
(1 row)
postgres=# \q
試用なので、postgresユーザ&パスワードpostgresで接続しています。
⑤ TimescaleDB用 の データベース 作成
# psql -U postgres -h localhost
Password for user postgres:
postgres=# CREATE database tutorial;
CREATE DATABASE
postgres=# \c tutorial
# CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
WARNING:
WELCOME TO
_____ _ _ ____________
|_ _(_) | | | _ \ ___ \
| | _ _ __ ___ ___ ___ ___ __ _| | ___| | | | |_/ /
| | | | _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \
| | | | | | | | | __/\__ \ (_| (_| | | __/ |/ /| |_/ /
|_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
Running version 0.12.1
For more information on TimescaleDB, please visit the following links:
1. Getting started: https://docs.timescale.com/getting-started
2. API reference documentation: https://docs.timescale.com/api
3. How TimescaleDB is designed: https://docs.timescale.com/introduction/architecture
Note: TimescaleDB collects anonymous reports to better understand and assist our users.
For more information and how to disable, please see our docs https://docs.timescaledb.com/using-timescaledb/telemetry.
CREATE EXTENSION
## 作成したDBへの接続確認
postgres$ psql -d tutorial
psql (10.5 (Ubuntu 10.5-0ubuntu0.18.04))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
tutorial=# \dt
Did not find any relations.
⑥ Grafana のインストール
# wget https://s3-us-west-2.amazonaws.com/grafana-releases/release/grafana_5.3.0-beta1_amd64.deb
# dpkg -i grafana_5.3.0-beta1_amd64.deb
# systemctl status grafana-server
● grafana-server.service - Grafana instance
Loaded: loaded (/usr/lib/systemd/system/grafana-server.service; disabled; ven
Active: inactive (dead)
Docs: http://docs.grafana.org
## HTTPポート番号の確認
# grep http_port /etc/grafana/grafana.ini
;http_port = 3000
# systemctl start grafana-server
⑦ Grafana のセットアップ
- http://(対象サーバ):3000 にログイン
- http://(対象サーバ):3000/datasources よりデータソースを登録する
(設定例)
(標準)
Name: 任意のデータソース名、Default: お好みで設定、Type: PostgreSQL
(PostgreSQL接続)
Host: サーバ+ポート、Database: 対象のDB名、User: 接続ユーザ、Password: 接続ユーザのパスワード、SSL Mode: 接続時のSSL利用
(PostgreSQL詳細)
Version: PostgreSQLのバージョン、TimescaleDB: チェックする、Min time interval: お好みで設定
TimescaleDB に デモ用のログを投入
Webサーバへのアクセスログを想定したデモ用のログを生成してみます。
(保有カラム)
- time: 日時
- http_host: { first.com, second.com, third.com }
- schema: { http, https }
- http_status: { 1xx, 2xx, 3xx, 4xx, 5xx }
TimescaleDB用のテーブルの作成
データ投入するテーブルを作成します。
# psql -U postgres -d tutorial
Password for user postgres:
psql (10.5 (Ubuntu 10.5-0ubuntu0.18.04))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
tutorial=# CREATE TABLE demo (
tutorial(# time TIMESTAMPTZ NOT NULL,
tutorial(# http_host TEXT NOT NULL,
tutorial(# schema TEXT NULL,
tutorial(# http_status TEXT NULL
tutorial(# );
CREATE TABLE
tutorial=# EXPLAIN SELECT * FROM demo ;
QUERY PLAN
---------------------------------------------------------
Seq Scan on demo (cost=0.00..16.10 rows=610 width=104)
(1 row)
HyperTableを作成します。(テーブルが空でないと作成できません)
tutorial=# SELECT create_hypertable('demo', 'time') ;
create_hypertable
--------------------
(31,public,demo,t)
(1 row)
tutorial=# EXPLAIN SELECT * FROM demo ;
QUERY PLAN
---------------------------------------------------------------
Append (cost=0.00..16.10 rows=610 width=104)
-> Seq Scan on demo (cost=0.00..16.10 rows=610 width=104)
(2 rows)
デモ用ログ投入実行
雑にランダムなデータを作成 して 投入しました。
テーブルに格納されたログを確認
tutorial=# SELECT * FROM demo ORDER BY time ;
time | http_host | schema | http_status
-------------------------------+------------+--------+-------------
2019-04-15 19:01:00.123457+09 | second.com | http | 3xx
2019-04-15 19:01:00.123457+09 | third.com | http | 4xx
2019-04-15 19:01:00.123457+09 | second.com | https | 4xx
2019-04-15 19:01:00.123457+09 | third.com | http | 2xx
2019-04-15 19:01:00.123457+09 | first.com | https | 2xx
2019-04-15 19:01:00.123457+09 | second.com | https | 3xx
2019-04-15 19:01:00.123457+09 | second.com | http | 2xx
(略)
Grafanaで可視化
ダッシュボード の Edit で 登録します。デフォルトのMetricではGUIベースで登録できます。
SQLで記述する場合 Toggle Edit Mode を選択します。
上記は、通常の関数countを使っています。こちらでグラフを描画すると、下記のようになりした。
TimescaleDBの関数を用いて可視化
TimescaleDBには強力な関数が複数存在します。その一部を試してみます。
① 時系列最後尾のデータを取得
SELECT http_host,
last(http_status, time) AS last_status
FROM demo
GROUP BY http_host
② 丸め時間 15分で集計
SELECT
time_bucket('15 minutes', time) AS "time",
count(http_status) AS "2xx"
FROM demo
WHERE http_status = '2xx'
GROUP BY time
ORDER BY 1
TimescaleDB と Grafana を組わせて使う紹介でした。