背景
公司的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