Prometheus远端数据存储方案之PostgreSQL

[复制链接]

826

主题

826

帖子

446

积分

中级会员

Rank: 3Rank: 3

积分
446
分享到:
发表于 2022-5-17 14:22:12 | 显示全部楼层 |阅读模式
今天看到德哥分享的基于postgresql的prometheus数据远端存储方案,于是有了本文的实验。

通常,prometheus的外置存储方案有好几种选择,例如influxdb,m3db, es,postgresql。
m3db一般在云原生环境下,用的更趁手些。
这里我们演示下postgresql的远端存储方案(可读可写),具体看下面的实验。

实验环境:

CentOS7.4
PG11-git-1921 编译安装版

参考文档:
88574693 编译安装 TimescaleDB 扩展
13522 远端存储的介绍
prometheus-postgresql-adapter prometheus-postgresql-adapter的官方文档介绍
官方文档
20191027_04.md 德哥blog
pg_prometheus pg_prometheus扩展的官方文档

1、安装llvm
yum install centos-release-scl-rhyum install llvm-toolset-7-clangyum install llvm5.0 yum install llvm5.0-libsyum install llvm5.0-develyum install clang

2、安装cmake-3.4以上的版本
cd /usr/local/wget https://github.com/Kitware/CMake/releases/download/v3.16.0-rc2/cmake-3.16.0-rc2-Linux-x86_64.shsh cmake-3.16.0-rc2-Linux-x86_64.sh即可在当前路径下安装cmake-3.16载入cmake-3.16到环境变量:export PATH=/usr/local/cmake-3.16.0-rc2-Linux-x86_64/binPATH

3、编译安装PG11带jit模块
su - postgrescd /home/postgres/wget yum localinstall epel-release-latest-7.noarch.rpmyum install llvm5.0 llvm5.0-devel clangcd /home/postgres/pg_sources/postgresql-11./configure --prefix=/usr/local/pgsql-11.5-jit-1921 \--with-python --with-perl --with-tcl --with-pam \--with-openssl --with-libxml --with-libxslt \--with-llvm LLVM_CONFIG='/usr/lib64/llvm5.0/bin/llvm-config'make make install 然后,还有些数据库的基础配置,这里就不细说了。

4、安装TimescaleDB扩展
su - postgrescd /home/postgres/cd timescaledb-1.4.2/export PATH=/usr/local/pgsql-11.5-jit-1921/binPATH./bootstrap 输入ycd ./build && makemake install 日志如下:[ 0%] Built target sqlfile[ 25%] Built target sqlupdatescripts[ 81%] Built target timescaledb[ 85%] Built target timescaledb-loader[100%] Built target timescaledb-tslInstall the project...-- Install configuration: "Release"-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb.control-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.4.1--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.4.1--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.4.0--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.3.2--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.3.1--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.3.0--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.2.2--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.2.1--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.2.0--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.1.1--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.1.0--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.0.1--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.0.0--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.0.0-rc3--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.0.0-rc2--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--1.0.0-rc1--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.12.1--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.12.0--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.11.0--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.10.1--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.10.0--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.9.2--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.9.1--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.9.0--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.8.0--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.7.1--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.7.0--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.6.1--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.6.0--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.5.0--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.4.2--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.4.1--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.4.0--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.3.0--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.2.0--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/share/extension/timescaledb--0.1.0--1.4.2.sql-- Installing: /usr/local/pgsql-11.5-jit-1921/lib/timescaledb-1.4.2.so-- Installing: /usr/local/pgsql-11.5-jit-1921/lib/timescaledb.so-- Installing: /usr/local/pgsql-11.5-jit-1921/lib/timescaledb-tsl-1.4.2.so即可完成timescaledb扩展的安装

编辑pg的配置文件,修改如下:
shared_preload_libraries = 'timescaledb'完成后重启PG进程

然后,登录到PG中,创建扩展:
postgres=# CREATE EXTENSION timescaledb;

5、安装pg_prometheus扩展
su - postgrescd /home/postgres/export PATH=/usr/local/pgsql-11.5-jit-1921/binPATHUSE_PGXS=1 make USE_PGXS=1 make install

编辑pg的配置文件,修改如下:
shared_preload_libraries = 'timescaledb,pg_prometheus'完成后重启PG进程

然后,登录到PG中,创建扩展:
postgres=# CREATE EXTENSION pg_prometheus ;postgres=# SELECT create_prometheus_table('metrics'); 或者使用 SELECT create_prometheus_table('metrics',use_timescaledb=>true);postgres=# INSERT INTO metrics VALUES ('cpu_usage{service="nginx",host="machine1"} 34.6 1494595898000'); postgres=# \d List of relations Schema | Name | Type | Owner --------+-----------------------+----------+---------- public | metrics | view | postgres public | metrics_copy | table | postgres public | metrics_labels | table | postgres public | metrics_labels_id_seq | sequence | postgres public | metrics_values | table | postgres(5 rows)

6、启动prometheus-postgresql-adapter
直接到prometheus-postgresql-adapter 下载解压prometheus-postgresql-adapter
./prometheus-postgresql-adapter -pg-port 1921 --pg-user postgres 即可在前台启动adapter进程

7、使用prometheus连接adapter端口:
vim /usr/local/prometheus/prometheus.yml 在最后加2个配置;
remote_write: - url: "http://192.168.2.4:9201/write" write_relabel_configs: - source_labels: [__name__] regex: 'go_.*' action: dropremote_read: - url: "http://192.168.2.4:9201/read" read_recent: false说明: 我这里设置的read_recent为false,表示查询prometheus近期数据不要到PG库里来捞数据,而是直接查prometheus本地缓存的数据,这样提高查询的速度并且能降低PG库的负载。

不足:
目前的配置来看,我们同事运行的prometheus和PG库, 存放同样历史周期的的metrics,PG库的体积远远超过了prometheus的体积。
PG库的metrics_values表已经 40 MB,此时的prometheus的data目录才 16MB 。 磁盘空间占用上,相差接近3倍。

优化方案待定:
调整remote_write 写入策略,通过drop操作来降低存储到PG的metrics的体积?
云专线_海外IDC http://www.vecloud.com/

使用高级回帖 (可批量传图、插入视频等)快速回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则   Ctrl + Enter 快速发布  

发帖时请遵守我国法律,网站会将有关你发帖内容、时间以及发帖IP地址等记录保留,只要接到合法请求,即会将信息提供给有关政府机构。
快速回复 返回顶部 返回列表