データ集計など時間がかかわるデータの検索に時系列DBは必須。 使い方や関数は早めにキャッチアップが必要だと実感。
時系列DBとは
時間情報を持ったデータを格納し、最適化されたデータベース。
TimescaleDBはその中の1つであり、今回TimescaleDBをお試ししてみる。
詳細は参考の資料を参照。
コンテナの起動
本体をインストールしたくないので、コンテナで起動する。
パスワードを設定したい場合、POSTGRES_PASSWORD環境変数に設定する。ここはPostgreSQLと一緒。
docker pull timescale/timescaledb-ha:pg16 docker run -d --name timescaledb -p 5432:5432 -e POSTGRES_PASSWORD=example timescale/timescaledb-ha:pg16 docker exec -it timescaledb psql -U postgres
ハイパーテーブルの作成
TimescaleDBの紹介資料を参照して、ハイパーテーブルを作成する。
通常のテーブルを作成して、create_hypertable関数を実行。
サーバのCPU使用率、メモリ使用率の情報をもつserverspecテーブルを作成。
- テーブルの作成
CREATE TABLE serverspec ( time timestamp NOT NULL, servername text NOT NULL, cpu_userd double precision, memory_used double precision );
- create_hypertable関数の実行
create_hypertable関数には、ハイパーテーブルに変換するテーブル名、分割の条件にする時間の列名を指定する。 戻り値は、ハイパーテーブルのID、スキーマ名、テーブル名。
SELECT create_hypertable('serverspec', 'time'); WARNING: column type "timestamp without time zone" used for "time" does not follow best practices HINT: Use datatype TIMESTAMPTZ instead. create_hypertable ------------------------- (1,public,serverspec,t)
データの操作
実際にデータの挿入、検索、更新、削除をする。
- 挿入
INSERT INTO serverspec VALUES ('2024-02-18 21:00:00', 'server1', 19.4, 10.4); INSERT 0 1
- 検索
SELECT * FROM serverspec; time | servername | cpu_userd | memory_used ---------------------+------------+-----------+------------- 2024-02-18 21:00:00 | server1 | 19.4 | 10.4 (1 row)
- 更新
UPDATE serverspec SET cpu_userd = cpu_userd + 0.1 WHERE time = '2024-02-18 21:00:00' and servername = 'server1 '; UPDATE 1 select * from serverspec; time | servername | cpu_userd | memory_used ---------------------+------------+-----------+------------- 2024-02-18 21:00:00 | server1 | 19.5 | 10.4 (1 row)
- 削除
DELETE FROM serverspec WHERE time = '2024-02-18 21:00:00' and servername = 'server1';
チャンクの確認準備
チャンクの確認のために、事前にデータを投入する。 1分間隔のデータを1年間分投入する。
INSERT INTO serverspec SELECT time, servername, round(random()::numeric * (100 - 0), 1) AS cpu_used, round(random()::numeric * (100 - 0), 1) AS memory_used FROM generate_series('2024-01-01 00:00:00'::timestamp, '2024-12-31 23:59:00', '1 minute') AS time, unnest(ARRAY['server1', 'server2', 'server3']) AS servername; ANALYZE;
投入データのSQLを少し解説する。 random関数を使用して、0から100の範囲で数値を生成し、round関数で小数第一位を四捨五入する。
round(random()::numeric * (100 - 0), 1)
generate_series関数の第一パラメータに開始値、第二パラメータに終了値、第三パラメータに刻み値を設定する。 下記の場合は、1分刻みのデータを生成する。
generate_series('2024-01-01 00:00:00'::timestamp, '2024-12-31 23:59:00', '1 minute')
そもそも、チャンクとは
TimescaleDBでのチャンクとは、データを内部的に時間と空間で分割したテーブルのこと。
ユーザは、ハイパーテーブルを介して、チャンクのテーブルを意識することなく、データにアクセスができる。
空間は時間以外にチャンクを分割する条件。
チャンクのデフォルトは、7日ごとに分割される。チャンクを分割する間隔は、create_hypertableを実行する
際に指定可能。
チャンクの確認
登録したデータによって、チャンクがどのように生成されている状態なのかを確認する。
チャンクを確認する場合、show_chunks関数を使用する。
select show_chunks('serverspec'); show_chunks ----------------------------------------- _timescaledb_internal._hyper_1_1_chunk _timescaledb_internal._hyper_1_2_chunk _timescaledb_internal._hyper_1_3_chunk _timescaledb_internal._hyper_1_4_chunk _timescaledb_internal._hyper_1_5_chunk _timescaledb_internal._hyper_1_6_chunk _timescaledb_internal._hyper_1_7_chunk _timescaledb_internal._hyper_1_8_chunk <省略> (53 rows)
オプションをつけることで、取得する範囲を指定できる。 2024-12-01よりも古い日付のものを取得。
SELECT show_chunks('serverspec', older_than => DATE '2024-02-01'); show_chunks ---------------------------------------- _timescaledb_internal._hyper_1_2_chunk _timescaledb_internal._hyper_1_3_chunk _timescaledb_internal._hyper_1_4_chunk _timescaledb_internal._hyper_1_5_chunk _timescaledb_internal._hyper_1_6_chunk (5 rows)
チャンクの削除
チャンクの削除は、drop_chunk関数を使用する。
データの削除は、DELETEコマンドでできるが、その場合、不要領域が発生し、再利用するためにVACUUMが必要になる。
チャンクごと削除すれば、不要領域が発生せず、VACUUMを実行する必要がない。
SELECT drop_chunks('serverspec', older_than => DATE '2024-02-01'); drop_chunks ---------------------------------------- _timescaledb_internal._hyper_1_2_chunk _timescaledb_internal._hyper_1_3_chunk _timescaledb_internal._hyper_1_4_chunk _timescaledb_internal._hyper_1_5_chunk _timescaledb_internal._hyper_1_6_chunk (5 rows)
データ取得
実際に、データの取得をいろいろとやってみる。 TimescaleDBのGettingStartedを参考にする。
firstとlast
firstは、別の列をキーに並べ替え、最初の値を返す。
lastは、fistの反対で、最後の値を返す。
SELECT servername, first(cpu_userd, time), last(cpu_userd, time) FROM serverspec WHERE time > now() - INTERVAL '4 days' GROUP BY servername; servername | first | last ------------+-------+------ server2 | 14.4 | 19.4 server3 | 26.4 | 2.2 server1 | 1.4 | 47.2 (3 rows)
time_bucket
time_bucket関数は時間を任意の間隔で丸める関数。 time_bucket関数を使用した場合、任意の時間で丸めることが可能。
SELECT time_bucket('1 hour', time) as bucket, first(cpu_userd, time), last(cpu_userd,time) FROM serverspec GROUP BY bucket LIMIT 10; bucket | first | last ---------------------+-------+------ 2024-02-01 00:00:00 | 45.8 | 82.3 2024-02-01 01:00:00 | 45 | 75.3 2024-02-01 02:00:00 | 2.1 | 67.4 2024-02-01 03:00:00 | 9 | 66.8 2024-02-01 04:00:00 | 12.4 | 5.5 2024-02-01 05:00:00 | 53.8 | 52.9 2024-02-01 06:00:00 | 12.2 | 69.9 2024-02-01 07:00:00 | 99.6 | 12.1 2024-02-01 08:00:00 | 39.2 | 25.8 2024-02-01 09:00:00 | 3.7 | 29.1
30分間隔にする場合、30 minutesを指定する。
SELECT time_bucket('30 minutes', time) as bucket, first(cpu_userd, time), last(cpu_userd,time) FROM serverspec GROUP BY bucket LIMIT 10; bucket | first | last ---------------------+-------+------ 2024-02-01 00:00:00 | 45.8 | 36.5 2024-02-01 00:30:00 | 71.3 | 82.3 2024-02-01 01:00:00 | 45 | 76.4 2024-02-01 01:30:00 | 78 | 75.3 2024-02-01 02:00:00 | 2.1 | 56.5 2024-02-01 02:30:00 | 29.8 | 67.4 2024-02-01 03:00:00 | 9 | 0.2 2024-02-01 03:30:00 | 47.8 | 66.8 2024-02-01 04:00:00 | 12.4 | 41.1 2024-02-01 04:30:00 | 37.6 | 5.5
Aggregation
時間ごとのCPUのMAX値などを集計してみる。
SELECT time_bucket('1 day', time) as day, servername, max(cpu_userd) as cpu_max, first(cpu_userd, time) as open, last(cpu_userd, time) as close, min(cpu_u serd) as cpu_min FROM serverspec GROUP BY day, servername ORDER BY day DESC, servername; day | servername | cpu_max | open | close | cpu_min ---------------------+------------+---------+------+-------+--------- 2024-12-31 00:00:00 | server1 | 99.8 | 60.1 | 47.2 | 0.1 2024-12-31 00:00:00 | server2 | 100 | 14.1 | 19.4 | 0 2024-12-31 00:00:00 | server3 | 99.9 | 66.8 | 2.2 | 0.1 2024-12-30 00:00:00 | server1 | 100 | 33.5 | 78.3 | 0 2024-12-30 00:00:00 | server2 | 99.9 | 44.8 | 34.5 | 0.1 2024-12-30 00:00:00 | server3 | 100 | 32.5 | 50.7 | 0.1 2024-12-29 00:00:00 | server1 | 99.8 | 42.7 | 31.4 | 0.1
最後に
TimescaleDBのコンテナの起動からデータの集計を試してみて、PostgreSQL単体よりも検索スピードが高速であることを体感できた。（計測まではしていない） さまざまな機器からの情報を集計し、グラフ化するといった集計処理には最適なデータベースと理解。 Zabbixとも連携ができるように、監視装置からのアラームの情報を集計等でも活躍すると思うので、これからも時系列DBについて勉強しておく。
参考
透過的という言葉とは
パーティショニング
