Report by Daria Vilkova for Zabbix Meetup Online
I want to introduce you to the PostgreSQL and operating system monitoring tool, which is being developed by our company using Zabbix.
We have chosen Zabbix as our monitoring tool for a long time because it is an open source platform supported by an active community that is very popular in Russia.
We created an active agent - Mamonsu, which provided more flexible monitoring than standard tools at that time allowed, and ensured the collection of metrics and their sending to Zabbix Server. In our company, Mamonsu is used in the audit.
Mamonsu
Mamonsu is an active agent (Zabbix Trapper) for monitoring PostgreSQL and the operating system. Mamonsu (written in Python) allows you to configure PostgreSQL and operating system monitoring settings in five minutes.
Mamonsu has additional tools:
- mamonsu tune is a command that edits the settings in the PostgreSQL configuration file for the machine on which the Mamonsu agent is installed.
- mamonsu report is a command that generates answers about the operating system and PostgreSQL.
Mamonsu is installed on the DBMS server, collects information, composes it into JSON, which sends it to Zabbix Server for visualization, where there should be a template for its metrics.
Mamonsu work scheme
Features Mamonsu
- Efficient work with PostgreSQL. A persistent connection to PostgreSQL is the main advantage of Mamonsu. In this case, the maximum number of connections is equal to the maximum number of databases to which it connects.
- Extensibility. Mamonsu is a fully "plugin" agent, and due to the fixed structure of each plugin and the relative simplicity of Python, one can easily learn how to write new or edit standard plugins, i.e. metrics collection parameters.
- Wide coverage of monitoring metrics for PotgreSQL including extension-specific metrics.
- quick launch, availability out of the box.
- Uploading templates and configuration files, as well as uploading to Zabbix Server.
- Cross Platform, which is important for our customers who use various Linux distributions, including domestic ones.
- BSD-clause license.
At the moment we offer a lot of plugins and in each next version we try to add something new.
- 14 plugins for PostgreSQL,
- 8 plugins for OS Linux,
- 4 plugins for OS Windows.
Mamonsu collects over 110 PostgreSQL and operating system metrics:
- 70 PostgreSQL metrics,
- 40 OS Linux metrics,
- 8 OS Windows metrics.
Key metrics include DBMS availability, number of connections, database size, checkpoints, read/write speed, locks, number of autovacuum processes, and WAL generation speed. A complete list of available metrics, as well as a detailed description of all tools, is available in on the GitHub site.
List of available metrics on GitHub
Run Mamonsu in 5 minutes
To set up monitoring of PostgreSQL and the operating system using Mamonsu, you can do it in 5 minutes by following 5 simple steps.
- Installing Mamonsu. Mamonsu can be built from source or use available packages.
$ git clone ... && cd mamonsu && python setup.py
build && python setup.py install
- Connection setup. It is necessary to set the connection parameters for PostgreSQL and Zabbix Server in the agent.conf file.
/etc/mamonsu/agent.conf
- Export template to Zabbix Server.
$ mamonsu zabbix template export
/usr/share/mamonsu/example.xml
- Adding a host to Zabbix Server. The exported template will be automatically connected to the new host on Zabbix Server.
$ mamonsu zabbix host create mamonsu-demo
- Release.
$ service mamonsu start
Mamonsu Development Directions
As part of the development of Mamonsu, we plan to refine the metrics and create new plugins, such as a plugin for monitoring the size of individual tables. We also plan to improve and create additional tools, as well as expand auto-tuning capabilities through the command mamonsu tune.
PostgreSQL monitoring module as part of Zabbix Agent 2
A fast and popular driver is used to connect to PostgreSQL (PG driver and toolkit for Go).
So far, we are using two interfaces: Exporter, which calls the handler by key, and Configurator Zabbix Agent 2, which reads and checks the connection parameters with the server specified in the configuration file.
We tried to optimize the work of the DBMS by grouping metrics and using a handler (handler) for metrics and metric groups, as well as using groups of metrics in JSON as dependent variables (dependency items), and low-level discovery (discovery rules).
Main Features
- maintaining a persistent connection to PostgreSQL between checks;
- support for flexible polling intervals;
- compatibility with PostgreSQL versions starting from 10 and Zabbix Server starting from version 4.4;
- the ability to connect and monitor multiple PostgreSQL instances at the same time due to the fact that Zabbix Agent 2 allows you to create multiple sessions.
PostgreSQL Connection Parameter Levels
In total, there are three levels of PostgreSQL connection parameters, i.e. tasks and settings:
- Global,
- sessions
- Macros.
-
The Global parameters are set at the agent level, the Session and Macros parameters define the database connection parameters.
-
Connection parameters to PostgreSQL - Sessions are set in the file zabbix_agent2.conf.
PostgreSQL Connection Options - Sessions
- After keyword Sessions a unique session name is specified, which must be specified in the key (template).
- Parameters URI ΠΈ UserName required for every session.
- If the base name is not specified, the default common base name for all PostgreSQL sessions is used, which is also set in the configuration file.
- Connection parameters to PostgreSQL - Macros are set in the metric key in the template (similar to the method used in Zabbix Agent 1), i.e. they are created in the template and then specified as parameters in the key. In this case, the sequence of macros is fixed, i.e., for example, URI always listed first.
PostgreSQL connection parameters - Macros
The PostgreSQL monitoring module already includes more than 95 metrics that allow you to cover a fairly wide range of PostgreSQL parameters, including:
- number of connections
- database size,
- archiving wal files,
- checkpoints,
- the number of "bloated" tables,
- replication status,
- replica delay.
PostgreSQL metrics are not informative without operating system parameters. But Zabbix Agent 2 already knows how to collect operating system parameters, so to get the full picture, we simply connect the necessary templates to the host.
Handler
The handler is the main unit of the module in which the request itself is executed and which allows you to receive metrics.
To get a simple metric:
- Create a file to get a new metric:
zabbix/src/go/plugins/postgres/handler_uptime.go
- We connect the package and specify the unique key (keys) of the metrics:
- We create a handler (handler) with a request, i.e., we initiate a variable that will contain the result:
- We execute the request:
It is necessary to check the request for errors, after which the result will be picked up by the Zabbix Agent 2 process.
- Register the new metric key:
After registering the metric, you can rebuild the agent with the new metric.
The module is available starting from Zabbix 5.0 on the site . In this version of Zabbix, the parameters are set separately via host and port. In Zabbix 5.0.2, which will be released soon, the connection parameters will be packaged into a single URI.
Thank you for attention!
Useful links
Source: habr.com