系统监控平台部署第二节--zabbix安装时序数据库和

在上一章节已经安装了zabbix，本想继续编写报警相关配置文章，但奈何随着监控的系统逐渐增多，在运行了一段时间后发现，随着历史数据的越来越大，一但并发过大或者数据量过大，处理能力就显得捉襟见肘，现在zabbix从4.2版本就开始支持时序数据库，安装也不复杂，类似插件的功能，所以本次记录下在现有条件下时序数据库的安装和数据迁移过程。

一、什么是TimescaleDB

Timscaledb(TSDB)是PostgreSQL扩展,它将基于时间序列的性能和数据管理优化添加至常规PostgreSQL(PG)数据库中。虽然不乏可扩展伸缩的时间 序列解决方案,但TimescaleDB最好的部分是传统SQL数据库之外的时间序列感知，实践中这意味着双方的优势可以兼得。数据库知道哪些表必须被视为时间序列数据(所有需要的优化都已到位),同时你又可以继续对时间序列和常规数据库表使用SQLs。应用程序甚至不需要知道在传统的SQL接口下有一个Timscaledb扩展

二、系统现状

• 操作系统：centos8
• 数据库：PostgreSQL12
• 软件版本：zabbix5.4

三、设置Timescale源

[root@zabbix5 yum.repos.d]# tee /etc/yum.repos.d/timescale_timescaledb.repo <<EOL enabled=1 > [timescale_timescaledb] gpgkey=https://packagecloud.io/timescale/timescaledb/gpgkey > name=timescale_timescaledb > baseurl=https://packagecloud.io/timescale/timescaledb/el/$(rpm -E %{rhel})/\$basearch > repo_gpgcheck=1 > gpgcheck=0 > enabled=1 > gpgkey=https://packagecloud.io/timescale/timescaledb/gpgkey > sslverify=1 > sslcacert=/etc/pki/tls/certs/ca-bundle.crt > metadata_expire=300 > EOL [root@zabbix5 yum.repos.d]# yum update –y

四、安装timescaledb

使用yum install -y timescaledb-postgresql-12默认安装的时候，会将timescaledb-2-loader-postgresql安装为12-2.4.1的版本 为了避免此种情况，我在官网下载了配套的1.7.5版本的timescaledb-2-loader-postgresql，先rpm安装后，再YUM方式安装。当然也可以参考官网的2.X版本的手册，全部更换最新版，本例是要利旧postgresql12的数据库，所以稳妥点还是继续用老版本。 https://packagecloud.io/app/timescale/timescaledb/search?q=1.7.5 由于我之前直接YUM安装，发现版本不太合适，所以需要先卸载了

yum remove timescaledb-2-loader-postgresql-12-2.4.1-0.el8.x86_64

卸载的时候会提示之前安装的timescaledb三个软件包都需卸载了 卸载完后，先安装下载的安装包，再通过yum方式完成安装

rpm -ivh timescaledb-loader-postgresql-12-1.7.5-0.el7.x86_64.rpm

五、初始化timescaledb

通过脚本进行参数初始化并重启postgresql

[root@zabbix5 tmp]# timescaledb-tune --pg-config=/usr/pgsql-12/bin/pg_config Using postgresql.conf at this path: /var/lib/pgsql/12/data/postgresql.conf Is this correct? [(y)es/(n)o]: y Writing backup to: /tmp/timescaledb_tune.backup202109191638 shared_preload_libraries needs to be updated Current: #shared_preload_libraries = '' Recommended: shared_preload_libraries = 'timescaledb' Is this okay? [(y)es/(n)o]: y success: shared_preload_libraries will be updated Tune memory/parallelism/WAL and other settings? [(y)es/(n)o]: y Recommendations based on 62.64 GB of available memory and 16 CPUs for PostgreSQL 12 Memory settings recommendations Current: shared_buffers = 128MB #effective_cache_size = 4GB #maintenance_work_mem = 64MB #work_mem = 4MB Recommended: shared_buffers = 16036MB effective_cache_size = 48110MB maintenance_work_mem = 2047MB work_mem = 10263kB Is this okay? [(y)es/(s)kip/(q)uit]: y success: memory settings will be updated Parallelism settings recommendations Current: missing: timescaledb.max_background_workers #max_worker_processes = 8 #max_parallel_workers_per_gather = 2 #max_parallel_workers = 8 Recommended: timescaledb.max_background_workers = 8 max_worker_processes = 27 max_parallel_workers_per_gather = 8 max_parallel_workers = 16 Is this okay? [(y)es/(s)kip/(q)uit]: y success: parallelism settings will be updated WAL settings recommendations Current: #wal_buffers = -1 min_wal_size = 80MB Recommended: wal_buffers = 16MB min_wal_size = 512MB Is this okay? [(y)es/(s)kip/(q)uit]: y success: WAL settings will be updated Miscellaneous settings recommendations Current: #default_statistics_target = 100 #random_page_cost = 4.0 #checkpoint_completion_target = 0.5 #max_locks_per_transaction = 64 #autovacuum_max_workers = 3 #autovacuum_naptime = 1min #effective_io_concurrency = 1 Recommended: default_statistics_target = 500 random_page_cost = 1.1 checkpoint_completion_target = 0.9 max_locks_per_transaction = 512 autovacuum_max_workers = 10 autovacuum_naptime = 10 effective_io_concurrency = 200 Is this okay? [(y)es/(s)kip/(q)uit]: y success: miscellaneous settings will be updated Saving changes to: /var/lib/pgsql/12/data/postgresql.conf [root@zabbix5 tmp]# systemctl restart postgresql-12

执行以下命令为特定的数据库启用TimescaleDB扩展项：

[root@zabbix5 tmp]# echo "CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;" | s udo -u postgres psql zabbix WARNING: WELCOME TO _____ _ _ ____________ |_ _(_) | | | _ \ ___ \ | | _ _ __ ___ ___ ___ ___ __ _| | ___| | | | |_/ / | | | | _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \ | | | | | | | | | __/\__ \ (_| (_| | | __/ |/ /| |_/ / |_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/ Running version 1.7.5 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

六、数据迁移

通过脚本将现有的数据迁移，在迁移期间。Zabbix Server和前端必须关闭。

[root@zabbix5 postgresql]# systemctl stop zabbix-server zabbix-agent nginx php-fpm [root@zabbix5 doc]# cd /usr/share/doc/zabbix-sql-scripts/postgresql/ [root@zabbix5 postgresql]# ls create.sql.gz double.sql schema.sql.gz timescaledb.sql.gz [root@zabbix5 postgresql]# zcat /usr/share/doc/zabbix-sql-scripts/postgresql/timescaledb.sql.gz | sudo -u zabbix psql zabbix NOTICE: PostgreSQL version 12.7 is valid NOTICE: TimescaleDB extension is detected NOTICE: TimescaleDB version 1.7.5 is valid NOTICE: migrating data to chunks DETAIL: Migration might take a while depending on the amount of data. NOTICE: migrating data to chunks DETAIL: Migration might take a while depending on the amount of data. NOTICE: migrating data to chunks DETAIL: Migration might take a while depending on the amount of data. NOTICE: migrating data to chunks DETAIL: Migration might take a while depending on the amount of data. NOTICE: migrating data to chunks DETAIL: Migration might take a while depending on the amount of data. NOTICE: migrating data to chunks DETAIL: Migration might take a while depending on the amount of data. NOTICE: TimescaleDB is configured successfully [root@zabbix5 postgresql]# systemctl start zabbix-server zabbix-agent nginx php-fpm

在web页面中，可以设置对时序数据库启用压缩和压缩时间设置

OK,至此已经完成zabbix时序数据库的部署工作，后续再继续开展各项优化部署。