Post Syndicated from Aigars Kadiķis original https://blog.zabbix.com/what-takes-disk-space/13349/

In today’s class let’s talk about where the disk space goes. Which items and hosts objects consume the disk space the most.

The post will cover things like:
Biggest tables in a database
Biggest data coming to the instance right now
Biggest data inside one partition of the DB table
Print hosts and items which consumes the most disk space

Biggest tables

In general, the leading tables are:

‘history_uint’ will store integers. ‘history’ will store decimal numbers.
‘history_str’, ‘history_text’, ‘history_log’ stores textual data.
In the table ‘events’ goes problem events, internal events, agent auto-registration events, discovery events.

Have a look yourself in a database which tables take the most space. On MySQL:

SELECT table_name,
       table_rows,
       data_length,
       index_length,
       round(((data_length + index_length) / 1024 / 1024 / 1024),2) "Size in GB"
FROM information_schema.tables
WHERE table_schema = "zabbix"
ORDER BY round(((data_length + index_length) / 1024 / 1024 / 1024),2) DESC
LIMIT 8;

On PostgreSQL:

SELECT *, pg_size_pretty(total_bytes) AS total , pg_size_pretty(index_bytes) AS index ,
       pg_size_pretty(toast_bytes) AS toast , pg_size_pretty(table_bytes) AS table
FROM (SELECT *, total_bytes-index_bytes-coalesce(toast_bytes, 0) AS table_bytes
   FROM (SELECT c.oid,
             nspname AS table_schema,
             relname AS table_name ,
             c.reltuples AS row_estimate ,
             pg_total_relation_size(c.oid) AS total_bytes ,
             pg_indexes_size(c.oid) AS index_bytes ,
             pg_total_relation_size(reltoastrelid) AS toast_bytes
      FROM pg_class c
      LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
      WHERE relkind = 'r' ) a) a;

Detect big data coming to instance right now

Analyze ‘history_log’ table for the last 30 minutes:

SELECT hosts.host,items.itemid,items.key_,
COUNT(history_log.itemid)  AS 'count', AVG(LENGTH(history_log.value)) AS 'avg size',
(COUNT(history_log.itemid) * AVG(LENGTH(history_log.value))) AS 'Count x AVG'
FROM history_log 
JOIN items ON (items.itemid=history_log.itemid)
JOIN hosts ON (hosts.hostid=items.hostid)
WHERE clock > UNIX_TIMESTAMP(NOW() - INTERVAL 30 MINUTE)
GROUP BY hosts.host,history_log.itemid
ORDER BY 6 DESC
LIMIT 1\G

With PostgreSQL:

SELECT hosts.host,history_log.itemid,items.key_,
COUNT(history_log.itemid) AS "count", AVG(LENGTH(history_log.value))::NUMERIC(10,2) AS "avg size",
(COUNT(history_log.itemid) * AVG(LENGTH(history_log.value)))::NUMERIC(10,2) AS "Count x AVG"
FROM history_log 
JOIN items ON (items.itemid=history_log.itemid)
JOIN hosts ON (hosts.hostid=items.hostid)
WHERE clock > EXTRACT(epoch FROM NOW()-INTERVAL '30 MINUTE')
GROUP BY hosts.host,history_log.itemid,items.key_
ORDER BY 6 DESC
LIMIT 5
\gx

Re-run the same query but replace ‘history_log’ (in all places) with ‘history_text’ or ‘history_str’.

Which hosts consume the most space

This is a very heavy query. We will go back one day and analyze 6 minutes of that data:

SELECT ho.hostid, ho.name, count(*) AS records, 
(count(*)* (SELECT AVG_ROW_LENGTH FROM information_schema.tables 
WHERE TABLE_NAME = 'history_text' and TABLE_SCHEMA = 'zabbix')/1024/1024) AS 'Total size average (Mb)', 
sum(length(history_text.value))/1024/1024 + sum(length(history_text.clock))/1024/1024 + sum(length(history_text.ns))/1024/1024 + sum(length(history_text.itemid))/1024/1024 AS 'history_text Column Size (Mb)'
FROM history_text
LEFT OUTER JOIN items i on history_text.itemid = i.itemid 
LEFT OUTER JOIN hosts ho on i.hostid = ho.hostid 
WHERE ho.status IN (0,1)
AND clock > UNIX_TIMESTAMP(now() - INTERVAL 1 DAY - INTERVAL 6 MINUTE)
AND clock < UNIX_TIMESTAMP(now() - INTERVAL 1 DAY)
GROUP BY ho.hostid
ORDER BY 4 DESC
LIMIT 5\G

If “6-minute query” works in a relatively good time frame, try “INTERVAL 60 MINUTE”.
If “INTERVAL 60 MINUTE” works good, try “INTERVAL 600 MINUTE”.

Analyze in partition level (MySQL)

On MySQL, if database table partitioning is enabled we can list the biggest partitions on a filesystem:

ls -lh history_log#*

It will print:

-rw-r-----. 1 mysql mysql  44M Jan 24 20:23 history_log#p#p2021_02w.ibd
-rw-r-----. 1 mysql mysql  24M Jan 24 21:20 history_log#p#p2021_03w.ibd
-rw-r-----. 1 mysql mysql 128K Jan 11 00:59 history_log#p#p2021_04w.ibd

From previous output, we can take partition name ‘p2021_02w’ and use it in a query:

SELECT ho.hostid, ho.name, count(*) AS records, 
(count(*)* (SELECT AVG_ROW_LENGTH FROM information_schema.tables 
WHERE TABLE_NAME = 'history_log' and TABLE_SCHEMA = 'zabbix')/1024/1024) AS 'Total size average (Mb)', 
sum(length(history_log.value))/1024/1024 + 
sum(length(history_log.clock))/1024/1024 +
sum(length(history_log.ns))/1024/1024 + 
sum(length(history_log.itemid))/1024/1024 AS 'history_log Column Size (Mb)'
FROM history_log PARTITION (p2021_02w)
LEFT OUTER JOIN items i on history_log.itemid = i.itemid 
LEFT OUTER JOIN hosts ho on i.hostid = ho.hostid 
WHERE ho.status IN (0,1)
GROUP BY ho.hostid
ORDER BY 4 DESC
LIMIT 10;

You can reproduce a similar scenario while listing:

ls -lh history_text#*
ls -lh history_str#*

Free up disk space (MySQL)

Deleting a host in GUI will not free up data space on MySQL. It will create empty rows in table where the new data can be inserted. If you want to really free up disk space, we can rebuild partition. At first list all possible partition names:

SHOW CREATE TABLE history\G

To rebuild partition:

ALTER TABLE history REBUILD PARTITION p202101160000;

Free up disk space (PostgreSQL)

On PostgreSQL, there is a process which is responsible for vacuuming the table. To ensure a vacuum has been done lately, kindly run:

SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_all_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC;

In output, we look at ‘n_dead_tup’ it means a dead tuple.
If the last auto vacuum has not occurred in last 10 days, it’s bad. We have to install a different definition. We can increase vacuum priority by having:

vacuum_cost_page_miss = 10
vacuum_cost_page_dirty = 20
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.01
autovacuum_vacuum_cost_delay = 20ms
autovacuum_vacuum_cost_limit = 3000
autovacuum_max_workers = 6

Alright. That is it for today.

Post Syndicated from Aigars Kadiķis original https://blog.zabbix.com/examine-data-overview/13225/

In this lab, let’s practice to create an on-screen report of the data (most recent metrics) which is very important for us.

This post represents one technique how to advance from functionality under:
“Monitoring” => “Overview”.

To create a report of the things you are fancy, we need to somehow mark those things. We need to mark items to belong under a specific application. The best way is to modify the name of an existing application and add some extra keywords inside. Please don’t create a second application. I will explain later why to not do so.

Here is a thought process of how to mark items under a single application.

Sample 1:

Total Memory
Total amount of CPU cores

Sample 2:

Current usage CPU
Current usage Memory

Sample 3:

TCP state ESTABLISHED
TCP state LISTEN
TCP state TIME_WAIT
...

It’s always only one application. Notice that each group has a common keyword: “Total”, “Current usage”, “TCP state”.

Now to list the data coming from a specific application:

1. “Monitoring” => “Overview”
2. Select “Data overview”
3. Pick a “Host groups”
4. Set an “Application”
5. On the right top corner set Hosts location: “Left”
6. Apply

It is always quite challenging to think of a naming system which is very independent and not overlapping. Good luck and keep “challenge accepted” running in your heart.

Of course, you can create an “extra” application for each item, for example, an application “Overview1”, but that will create a duplicate entry while browsing data under:
“Monitoring” => “Latest data”.

It’s possible to reach some limitations in the “Data overview” page if there are more than 50 entries to represent. We will see the message at the bottom of the page:

Not all results are displayed. Please provide more specific search criteria.

To solve this problem starting with 5.2 there is an option to configure the limit (default is 50):

On version 5.0 to customize this, have to modify ‘defines.inc.php’

# cd /usr/share/zabbix/include
# grep ZBX_MAX_TABLE_COLUMNS defines.inc.php
define('ZBX_MAX_TABLE_COLUMNS', 50);

Post Syndicated from Jurijs Klopovskis original https://blog.zabbix.com/staying-up-to-date-when-using-official-zabbix-packages/12806/

It is not a secret that Zabbix maintains package repositories for multiple GNU/Linux distributions to make installing the software and staying up to date with the latest releases as easy as possible. To make use of the official Zabbix packages one should follow the instructions on https://www.zabbix.com/download. In this article, we would like to talk about some common points of confusion that people have when using Zabbix packages.

Being a Zabbix package maintainer, I often notice that people are confused about which packages are provided for which operating system. That’s why we have created a table that gives users info about package availability by operating system at-a-glance. Furthermore, we would like to clarify certain specific issues to eliminate any potentially remaining misunderstandings. In particular, let’s address the issue of packages no longer being provided for certain operating systems.

It is important to understand that Zabbix packages depend on other packages provided by the operating system. Whether those are web server and PHP packages needed for the frontend or OpenSSL required pretty much by all other Zabbix components, Zabbix is limited by the versions of these packages that are shipped with the operating system, or by how up to date these packages are.

Any professional system administrator is familiar with the need to install the latest security updates as one of the central measures to keep their systems secure. Unless the system provides the necessary security updates, that system should not be used. But there are also other aspects besides security that should be taken into consideration.

One specific case that we would like to discuss is Red Hat Enterprise Linux 7. In fact, RHEL/CentOS 7 constitutes a large chunk of Zabbix installations.

Heads Up! The same packages are used for RHEL, CentOS & Oracle Linux, thus when RHEL is mentioned, CentOS is also implied.

As many of you may have noticed, only zabbix-agent, zabbix-sender & zabbix-get packages have been provided for RHEL 7 when version 5.2 was released. What’s the deal?

Red Hat backports security fixes for older packages, and this is awesome. Despite that, the essential packages that Zabbix uses as dependencies are tremendously old.
Case in point, RHEL 7 ships with:

• PHP 5.4.16
• MariaDB 5.5.68 & PostgreSQL 9.2.24
• OpenSSL 1.0.2k

Let’s talk about these in detail.

PHP 5.4

Starting with version 5.0, Zabbix frontend requires PHP version 7.2 or higher. Simply put, our frontend developers needed to make use of the new PHP features to improve the user experience. Also, 7.2 was the oldest supported version in the upstream.

Quite expectedly, this caused some problems when packaging Zabbix for RHEL 7, due to the distribution shipping PHP version 5.4. At first, the idea was to drop support for the 5.0 frontend on RHEL 7 altogether, but after consulting with the support team, it was decided to find a way around to keep providing these packages somehow.

Enter Red Hat Software Collections. Instead of being dropped completely, Zabbix 5.0 frontend packages were based on PHP 7.2 found in RH SCL. The day was saved, but in the end, this still was not the cleanest solution. A lot of things had to be altered from the way they are usually done. Changes had to be made to configuration files and user instructions. The repository structure was altered and frontend-related packages were renamed to include the “scl” suffix to reflect the changes. As a result, these changes made package maintenance pretty difficult for us. Furthermore, extra attention was required from the users when installing these packages and especially when updating from the previous versions.

As a side note, on Debian-based distros that have the same problem, the frontend package has been deprecated altogether.

Old Databases & OpenSSL

Secure connection to the database was introduced in 5.0, however, it does not work on RHEL 7.

Try for yourself. Put DBTLSConnect=required option into /etc/zabbix/zabbix_server.conf file and try to restart the Zabbix server. It will fail with the following error:

"DBTLSConnect" configuration parameter cannot be used: Zabbix server was compiled without PostgreSQL or MySQL library version that support TLS

This happens due to RHEL 7 shipping old database packages. Yes, using RH SCL is possible but implementation would be an even bigger mess than what was required for making the 5.0 frontend work. Considering that RHEL 7 is on its way out, it takes just too much effort to implement and support.

Another issue is the fact that old OpenSSL packages prevent the use of TLS 1.3 among other things.

For example, add TLSCipherPSK13=TLS_AES_128_GCM_SHA256 setting to /etc/zabbix/zabbix_proxy.conf and restart the server. You will get the following error in the proxy log file.

cannot set list of TLS 1.3 PSK ciphersuites: compiled with OpenSSL version older than 1.1.1. Consider not using parameters "TLSCipherPSK13" or "--tls-cipher13"

TLS 1.3 is fully supported in RHEL 8.

The usage of HashiCorp Vault can possibly be affected by the old OpenSSL version as well.

There are potentially other issues that haven’t been discovered yet. Because of the nature of the old packages on RHEL 7, it is hard to fully predict what can go wrong.

In conclusion

Taking into consideration all of the above, it was decided to not provide server and frontend packages for 5.2 on RHEL 7. We do understand that this is super-inconvenient for some people, but the truth is that this has to be done sooner or later. It could have been done in 5.4 or 6.0, but that is simply kicking the can further down the road. It is a painful, but necessary change.

Proxy packages for 5.2 will be provided to keep some backward compatibility, but keep in mind that a lot of the modern features will not work there, including:

• No support for TLS 1.3
• No support for encrypted database connections

And most importantly, support for proxy on RHEL 7 will be dropped in Zabbix 5.4!

Note
RHEL 7 support for existing Zabbix customers will still be provided.

In short upgrade to RHEL 8. This will have to be done sooner or later. Do that and forget about this type of problem in the foreseeable future.

Note
We are aware of recent change in CentOS 8 lifecycle and are investigating its impact on Zabbix packages.

Of course  the cost of upgrading RHEL may be prohibitive. So, if the upgrade is impossible for one reason or another, which options are available?

• Use container images. Probably the most progressive option of all. Zabbix has great container images. Consider using them, if using 5.2 is an impediment.
• Use 5.0 LTS instead. Indeed, 5.0 packages are available for RHEL 7 and will be supported for some time. Despite the known problems, described above, this can be a great option.
• Build from source. Of course, there is always a hard way. Grab the sources and build away. If you choose to go this route, then you must take into account the potential problems caused by old packages on the system.

Ultimately, we suggest thinking of this as a motivation to make an upgrade. If you really need new features of Zabbix, consider using an up-to-date operating system.