背景
公司的zabbix監控用的是mysql資料庫，mysql資料庫單表超過1億行資料的時候，會産生性能問題；由于zabbix自帶housekeeper會定時清理過期的曆史資料，同時又有好多資料在寫入，導緻産生過多的慢sql，影響zabbix server正常運作；現在考慮将mysql資料庫替換成timescaledb時間序列資料庫（官方已經支援）
zabbix官網部落格： https://blog.zabbix.com/cn/zabbix-time-series-data-and-timescaledb-2/6959/
zabbix4.2實驗性的支援timescaledb；zabbix4.4已經支援timescaledb；
下面自己先部署timescaledb1.3.2，簡單熟悉一下時序資料庫timescaledb
環境
系統：centos7
Cmake版本 3.9.2
PostgreSQL版本号10.10
TimescaleDB版本号1.3.2
虛拟機配置4CPU 8G記憶體 200G系統盤
postgresql 10.10
1.環境準備
yum install -y systemtap-sdt-devel.x86_64 gcc perl-ExtUtils-Embed readline readline-devel zlib zlib-devel openssl openssl-devel pam pam-devel libxml2 libxml2-devel libxslt libxslt-devel tcl tcl-devel python python-devel
下載下傳源碼包：wget https://ftp.postgresql.org/pub/source/v10.10/postgresql-10.10.tar.gz
2.建立使用者
useradd postgres
3.編譯源碼
./configure --prefix=/usr/local/postgresql --with-perl --with-tcl --with-python --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt --enable-thread-safety --with-wal-blocksize=16 --with-blocksize=16 --enable-debug
make
make install
4.建立目錄并把目錄權限賦給postgres使用者
mkdir /home/postgresql_data 存儲資料的目錄
chown -R postgres:postgres /home/postgresql_data
chown -R /usr/local/postgresql
chmod 750 /home/postgresql_data
5.配置環境變量
vi /etc/profile
export PATH=/usr/local/postgresql/bin:$PATH
export LD_LIBRARY_PATH=/usr/local/postgresql/lib
export PGDATA=/home/postgresql_data
source /etc/profile
6.初始化資料庫并啟動
切換到postgres使用者，使用initdb初始化資料庫，這樣在/home/postgresql_data下會增加很多東西
su postgres
檢視資料庫初始化文檔
啟動資料庫
pg_ctl -D /home/postgresql_data -l logfile start
7.修改密碼
分為postgres使用者密碼和資料庫密碼，保持一緻吧
passwd postgres
設定資料庫密碼：
su postgres
psql
ALTER USER postgres WITH PASSWORD '密碼';
列出目前庫
timescaledb
timescaledb部署參照：https://docs.timescale.com/v1.3/getting-started/installation
cmake版本需要大于3.4
git clone https://github.com/timescale/timescaledb.git
cd timescaledb
git checkout <release_tag> # e.g., git checkout 1.3.2
# Bootstrap the build system
./bootstrap
# To build the extension
cd build && make
# To install
make install
配置PostgreSQL
切換到postgres使用者
su - postgres
在$PGDATA/postgresql.conf添加配置
shared_preload_libraries = 'timescaledb'
重新開機PostgreSQL
切換到postgres使用者
su - postgres
pg_ctl restart -D $PGDATA (PGDATA=/home/postgresql_data)
驗證TimescaleDB功能
登入PostgreSQL
psql -U postgres
建立名為tutorial的資料庫
CREATE database tutorial;
切換到tutorial資料庫
\c tutorial
加載TimescaleDB的extensions
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
示例輸出
WARNING:
WELCOME TO
_____ _ _ ____________
|_ _(_) | | | _ \ ___ \
| | _ _ __ ___ ___ ___ ___ __ _| | ___| | | | |_/ /
| | | | _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \
| | | | | | | | | __/\__ \ (_| (_| | | __/ |/ /| |_/ /
|_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
Running version 1.3.2
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
建立一個普通SQL标準的表
CREATE TABLE conditions (
time TIMESTAMPTZ NOT NULL,
location TEXT NOT NULL,
temperature DOUBLE PRECISION NULL,
humidity DOUBLE PRECISION NULL
);
檢視表結構
tutorial=# \d conditions
Table "public.conditions"
Column | Type | Collation | Nullable | Default
-------------+--------------------------+-----------+----------+---------
time | timestamp with time zone | | not null |
location | text | | not null |
temperature | double precision | | |
humidity | double precision | | |
使用create_hypertable建立hypertable
SELECT create_hypertable('conditions', 'time');
create_hypertable
-------------------------
(1,public,conditions,t)
(1 row)
這時再看表結構的時候，會發現不一樣了
tutorial=# \d conditions
Table "public.conditions"
Column | Type | Collation | Nullable | Default
-------------+--------------------------+-----------+----------+---------
time | timestamp with time zone | | not null |
location | text | | not null |
temperature | double precision | | |
humidity | double precision | | |
Indexes:
"conditions_time_idx" btree ("time" DESC)
Triggers:
ts_insert_blocker BEFORE INSERT ON conditions FOR EACH ROW EXECUTE PROCEDURE _timescaledb_internal.insert_blocker()
插入資料
tutorial=# INSERT INTO conditions(time, location, temperature, humidity) VALUES (NOW(), 'office', 70.0, 50.0);
查詢資料
tutorial=# SELECT * FROM conditions ORDER BY time DESC LIMIT 100;
time | location | temperature | humidity
-------------------------------+----------+-------------+----------
YYYY-MM-DD HH:mm:SS.354351+08 | office | 70 | 50
(1 row)
TimescaleDB Tutorials
這裡使用TimescaleDB官方的測試樣例 https://docs.timescale.com/v1.3/tutorials/tutorial-hello-nyc
參考：
https://blog.csdn.net/xjc504751194/article/details/80094198
https://luanlengli.github.io/2019/05/25/PostgreSQL10%E6%90%AD%E5%BB%BA%E6%97%B6%E9%97%B4%E5%BA%8F%E5%88%97%E6%95%B0%E6%8D%AE%E5%BA%93TimescaleDB.html
https://blog.csdn.net/u010430471/article/details/81663248
https://docs.timescale.com/v1.3/getting-started/installation/rhel-centos/installation-source