2019年6月16日 星期日

Zabbix 4.2 + TimescaleDB 安裝小筆記


Zabbix 4.2 + TimescaleDB


Zabbix 4.2 的新功能部份已支援 TimescaleDB,故留下這個小筆記,方便日後使用

(1) OS CentOS 7

(2) PostgreSQL 11

https://docs.timescale.com/v1.3/getting-started/installation/rhel-centos/installation-yum

PostgreSQL repository & Install

# yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# yum install postgresql11

# yum install postgresql11-server

# /usr/pgsql-11/bin/postgresql-11-setup initdb

# systemctl enable postgresql-11

# systemctl start postgresql-11


Add PATH


PATH=$PATH:/usr/pgsql-11/bin/
export PATH
PGDATA=/var/lib/pgsql/11/data
export PGDATA


# vi /var/lib/pgsql/11/data/pg_hba.conf

local   all all md5
# or  local   all all trust
host    all     all     127.0.0.1/32 md5

# vi /var/lib/pgsql/11/data/postgresql.conf


# - Connection Settings -

listen_addresses = '*'       # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                     # (change requires restart)
max_connections = 100  # (change requires restart)



(3) TimescaleDB 1.3


TimescaleDB repository & Install

sudo cat > /etc/yum.repos.d/timescale_timescaledb.repo <<EOL
[timescale_timescaledb]
name=timescale_timescaledb
baseurl=https://packagecloud.io/timescale/timescaledb/el/7/\$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

# yum update -y

# yum install -y timescaledb-postgresql-11




(4) Configure database

# timescaledb-tune


[root@centos75 ~]# timescaledb-tune
Using postgresql.conf at this path:
/var/lib/pgsql/11/data/postgresql.conf

Is this correct? [(y)es/(n)o]: y
Writing backup to:
/tmp/timescaledb_tune.backup201906151823

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 3.69 GB of available memory and 4 CPUs for PostgreSQL 11

Memory settings recommendations
Current:
shared_buffers = 128MB
#effective_cache_size = 4GB
#maintenance_work_mem = 64MB
#work_mem = 4MB
Recommended:
shared_buffers = 966208kB
effective_cache_size = 2830MB
maintenance_work_mem = 483104kB
work_mem = 2415kB
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 = 15
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
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
max_wal_size = 1GB
Recommended:
wal_buffers = 16MB
min_wal_size = 4GB
max_wal_size = 8GB
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
#effective_io_concurrency = 1
Recommended:
default_statistics_target = 500
random_page_cost = 1.1
checkpoint_completion_target = 0.9
max_locks_per_transaction = 64
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/11/data/postgresql.conf
[root@centos75 ~]#


# systemctl restart postgresql-11


(4) Zabbix 4.2


https://www.zabbix.com/download?zabbix=4.2&os_distribution=centos&os_version=7&db=postgresql

Install Zabbix repository

# rpm -Uvh https://repo.zabbix.com/zabbix/4.2/rhel/7/x86_64/zabbix-release-4.2-1.el7.noarch.rpm

Install Zabbix server, frontend, agent

# yum -y install zabbix-server-pgsql zabbix-web-pgsql zabbix-agent

Create initial database

# sudo -u postgres createuser --pwprompt zabbix
# sudo -u postgres createdb -O zabbix zabbix

Import initial schema and data. You will be prompted to enter your newly created password.

# zcat /usr/share/doc/zabbix-server-pgsql*/create.sql.gz | sudo -u zabbix psql zabbix

========================================

https://www.zabbix.com/documentation/4.2/manual/appendix/install/timescaledb



[root@centos75 zabbix-server-pgsql-4.2.3]# pwd
/usr/share/doc/zabbix-server-pgsql-4.2.3
[root@centos75 zabbix-server-pgsql-4.2.3]# ll
total 2292
-rw-r--r-- 1 root root      98 Jun  7 18:18 AUTHORS
-rw-r--r-- 1 root root 1000750 Jun  7 18:19 ChangeLog
-rw-r--r-- 1 root root   17990 Jun  7 18:18 COPYING
-rw-r--r-- 1 root root 1305981 Jun  7 18:26 create.sql.gz
-rw-r--r-- 1 root root      52 Jun  7 18:18 NEWS
-rw-r--r-- 1 root root    1317 Jun  7 18:19 README
-rw-r--r-- 1 root root     219 Jun  7 18:19 timescaledb.sql.gz
[root@centos75 zabbix-server-pgsql-4.2.3]#

root@centos75 zabbix-server-pgsql-4.2.3]# echo "CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;" | sudo -u postgres psql zabbix
WARNING:
WELCOME TO
 _____ _                               _     ____________
|_   _(_)                             | |    |  _  \ ___ \
  | |  _ _ __ ___   ___  ___  ___ __ _| | ___| | | | |_/ /
  | | | |  _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \
  | | | | | | | | |  __/\__ \ (_| (_| | |  __/ |/ /| |_/ /
  |_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
               Running version 1.3.1
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
[root@centos75 zabbix-server-pgsql-4.2.3]# zcat /usr/share/doc/zabbix-server-pgsql-4.2.3/timescaledb.sql.gz | sudo -u zabbix psql zabbix
  create_hypertable 
----------------------
 (1,public,history,t)
(1 row)

     create_hypertable   
---------------------------
 (2,public,history_uint,t)
(1 row)

    create_hypertable   
--------------------------
 (3,public,history_log,t)
(1 row)

     create_hypertable   
---------------------------
 (4,public,history_text,t)
(1 row)

    create_hypertable   
--------------------------
 (5,public,history_str,t)
(1 row)

  create_hypertable
---------------------
 (6,public,trends,t)
(1 row)

    create_hypertable   
--------------------------
 (7,public,trends_uint,t)
(1 row)

UPDATE 1
[root@centos75 zabbix-server-pgsql-4.2.3]#




=======================================

Configure the database for Zabbix server

vi  /etc/zabbix/zabbix_server.conf

--> DBPassword=password


Configure PHP for Zabbix frontend

vi /etc/httpd/config.d/zabbix.conf

--> php_value date.timezone Asia/Taipei


Start Zabbix server and agent processes

Start Zabbix server and agent processes and make it start at system boot:

# systemctl restart zabbix-server zabbix-agent httpd
# systemctl enable zabbix-server zabbix-agent httpd

其它參考的 URL

https://www.opensourcetech.tokyo/entry/20190510/1557484230