Shulou(Shulou.com)06/03 Report--

Today, I saw the remote storage scheme of prometheus data based on postgresql shared by Dege, so I have the experiment in this paper.

In general, there are several external storage options for prometheus, such as influxdb,m3db and es,postgresql.

M3db is generally used more easily in a native cloud environment.

Here we demonstrate postgresql's remote storage scheme (readable and writable), as shown in the following experiment.

Experimental environment:

CentOS7.4

PG11-git-1921 compiled and installed version

Reference documentation:

Https://blog.csdn.net/qq_43303221/article/details/88574693#cmake_34_50 compilation and installation of TimescaleDB extension

Introduction of https://studygolang.com/articles/13522?fr=sidebar remote Storage

Introduction to the official documentation of https://github.com/timescale/prometheus-postgresql-adapter prometheus-postgresql-adapter

Https://prometheus.io/docs/prometheus/latest/configuration/configuration/#remote_write official documentation

Https://github.com/digoal/blog/blob/master/201910/20191027_04.md, brother blog.

Official documentation for https://github.com/timescale/pg_prometheus pg_prometheus extensions

1. Install 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. Install a version above 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 can install cmake-3.16 under the current path and load cmake-3.16 to the environment variable: export PATH=/usr/local/cmake-3.16.0-rc2-Linux-x86_64/bin:$PATH

3. Compile and install PG11 with jit module

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 then There are also some basic configurations of the database, so I won't go into details here.

4. Install the TimescaleDB extension

Su-postgrescd / home/postgres/cd timescaledb-1.4.2/export PATH=/usr/local/pgsql-11.5-jit-1921/bin:$PATH./bootstrap enter ycd. / build & & makemake install log as follows: [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 Murray 1.4.2.sqlmuri-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.1Murray 1.4.2.sqlMel-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-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.sqlmuri-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 .0Murray 1.4.2.sqlmuri-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

You can complete the installation of the timescaledb extension

Edit the configuration file of pg and modify it as follows:

Shared_preload_libraries = 'timescaledb'

Restart the PG process after completion

Then, log in to PG and create the extension:

Postgres=# CREATE EXTENSION timescaledb

5. Install the pg_prometheus extension

Su-postgrescd / home/postgres/export PATH=/usr/local/pgsql-11.5-jit-1921/bin:$PATHUSE_PGXS=1 make USE_PGXS=1 make install

Edit the configuration file of pg and modify it as follows:

Shared_preload_libraries = 'timescaledb,pg_prometheus'

Restart the PG process after completion

Then, log in to PG and create the extension:

Postgres=# CREATE EXTENSION pg_prometheus; postgres=# SELECT create_prometheus_table ('metrics'); or use 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. Start prometheus-postgresql-adapter

Download and decompress prometheus-postgresql-adapter directly to https://github.com/timescale/prometheus-postgresql-adapter

. / prometheus-postgresql-adapter-pg-port 1921-pg-user postgres can start the adapter process in the foreground

7. Use prometheus to connect the adapter port:

Vim / usr/local/prometheus/prometheus.yml adds 2 configurations at the end

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

Note: the read_recent I set here is false, which means that querying the recent data of prometheus does not come to the PG database for data, but directly look up the data cached locally by prometheus, so as to improve the speed of query and reduce the load of PG library.

Deficiency:

In the current configuration, the prometheus and PG libraries run by our colleagues, the metrics,PG libraries that hold the same historical cycle, are much larger than the size of prometheus.

The metrics_ values table of the PG library is 40 MB, and the data directory of prometheus is 16MB. In terms of disk space consumption, the difference is nearly 3 times.

Optimization plan to be determined:

Adjust the remote_write write policy to reduce the size of the metrics stored in PG through drop operations.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.