tech memo

調べたこと、試したことの覚書です

TimescaleDB + Grafana で 時系列データを可視化する

TimescaleDB と Grafana

TimescaleDB とは?

  • PostgreSQL の EXTENSION の 時系列データベース
  • Apache License v2 で公開されているOSS

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 よりデータソースを登録する

(設定例)

f:id:nozomi1773:20190414232500p:plain

(標準)

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ベースで登録できます。

f:id:nozomi1773:20190416000337p:plain

SQLで記述する場合 Toggle Edit Mode を選択します。

f:id:nozomi1773:20190416000531p:plain

f:id:nozomi1773:20190416000613p:plain

上記は、通常の関数countを使っています。こちらでグラフを描画すると、下記のようになりした。

f:id:nozomi1773:20190416000704p:plain

 

TimescaleDBの関数を用いて可視化

 TimescaleDBには強力な関数が複数存在します。その一部を試してみます。

 ① 時系列最後尾のデータを取得

SELECT http_host, 
last(http_status, time) AS last_status
FROM demo
GROUP BY http_host

f:id:nozomi1773:20190416002817p:plain

 ② 丸め時間 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

f:id:nozomi1773:20190416004707p:plain

 

  TimescaleDB と Grafana を組わせて使う紹介でした。