Database seems to be growing too quick

[fsousa]

Hi Ingus,

The decision came from my manager. Now he want to keep 60 days for history and 2 years for trends.

[ingus.vilnis]

So create partitions and modify the maintenance script accordingly. In this case you don't need to create more than 60 partitions for history. But keep in mind that there will no longer be ability to have individually controlled history settings for each item.

[fsousa]

With the 60 days for history I only will be able to have 60 days individually controlled history settings for each item?

With the 2 years of trends what I will able to see? Graphs? CPU Usage? Ram Usage?

[kloczek]

Hi Ingus,

The decision came from my manager. Now he want to keep 60 days for history and 2 years for trends.

With 15d (2w + 1d) of history data and 3 years trends data trends tables are only about 4-5% of whole database.
With 60d history you can increase trends window and impact on total DB size will be barely noticeable.
Outside 2-3w raw history data will be probably not needed at all. 2-3w it is enough time to perform detailed post factum investigation in case any fault. Beyond this window trends data are enough.

[fsousa]

I'm a little confuse, I didn't understand yet the values I need to have 1 year of detailed information. My manager says 60 days for history and 2 years for trends are the ideal but I don't think so. It seems too much. What do you think?

My concern is the disk usage, we only have 300 GB.

Thank you.

[ingus.vilnis]

Have you ever even looked at the detailed history with one minute granularity back as far as 1 year? When was the last time you did this?

In the data retention equation there is one static variable - 300G disk. That limits how much of total data you can store.

If you think you currently have the capacity to store the detailed values fora a year - welcome, please do so. If your incoming flow of data is so low that your disk space allow it, there is nothing wrong with it. If there is a reasonable use case in your organization, there is no one that can stop you from keeping the data for so long. When you will be nearing the 300G disk capacity, you will need to make decisions - gather the data values less frequently (unlikely but for some worth considering), stop monitoring some hosts (unlikely) or drop some oldest data and reduce the history retention period. I have made such choice several times to keep the disk usage leveled.

How much new values per second you have? Number of hosts and items?

With the 60 days for history I only will be able to have 60 days individually controlled history settings for each item?

With the 2 years of trends what I will able to see? Graphs? CPU Usage? Ram Usage?

With partitioning you will not be able to individually control the history retention settings fer each item separately. With Housekeeper (no partitioning) you can have one item to store history for 3 days, another for a year and Zabbix internally will keep track of that. With partitioning you have say 60 days or whatever you choose to be the retention period, and that is it.

With 2 years of trends you will be able to see a graph for every numeric item that you have.

[fsousa]

Thank you Ingus,

I 've created the script (available here: https://zabbix.org/wiki/Docs/howto/m...ning_decisions) with my keep_history values:

my $tables = {  'history' => { 'period' => 'day', 'keep_history' => '60'},
        'history_log' => { 'period' => 'day', 'keep_history' => '60'},
        'history_str' => { 'period' => 'day', 'keep_history' => '60'},
        'history_text' => { 'period' => 'day', 'keep_history' => '60'},
        'history_uint' => { 'period' => 'day', 'keep_history' => '60'},
        'trends' => { 'period' => 'month', 'keep_history' => '12'},
        'trends_uint' => { 'period' => 'month', 'keep_history' => '12'},
        };

Then I run the script manually but I realized the disk space haven't reduced.

What I'm missing?

Thank you.

[ingus.vilnis]

Can you go to the directory where your database table files are stored on the server and show the full output of this command:

ls -lah history*

[fsousa]

Sure

[root@zabbix-test zabbix]# ls -lah history*
-rw-rw----  1 mysql mysql  13K Nov 27 02:10 history.frm
-rw-rw----  1 mysql mysql  13K Nov 27 02:10 history_log.frm
-rw-rw----  1 mysql mysql 1,2K Nov 27 02:10 history_log.par
-rw-rw----  1 mysql mysql 1,2K Nov 27 02:10 history.par
-rw-rw----  1 mysql mysql  13K Nov 27 02:10 history_str.frm
-rw-rw----  1 mysql mysql 1,2K Nov 27 02:10 history_str.par
-rw-rw----  1 mysql mysql  13K Nov 27 02:10 history_text.frm
-rw-rw----  1 mysql mysql 1,2K Nov 27 02:10 history_text.par
-rw-rw----. 1 mysql mysql 8,5K Nov 22 16:43 history_uint.frm
[root@zabbix-test zabbix]#

[ingus.vilnis]

There are no partitions yet. Did you not run the ALTER TABLE... commands for each table? You are supposed to convert the tables to partitioned first, and then use the script.

By looking at the table size you are having an empty database at the moment, by the way.

[fsousa]

strange

I did the ALTER TABLE commands for each table.

ALTER TABLE `[B]history[/B]` PARTITION BY RANGE ( clock)(
PARTITION p2018_09_28 VALUES LESS THAN (UNIX_TIMESTAMP("2018-09-29 00:00:00")) ENGINE = InnoDB,
(...)
PARTITION p2018_11_25 VALUES LESS THAN (UNIX_TIMESTAMP("2018-11-26 00:00:00")) ENGINE = InnoDB);

ALTER TABLE `[B]history_uint[/B]` PARTITION BY RANGE ( clock)(
PARTITION p2018_09_28 VALUES LESS THAN (UNIX_TIMESTAMP("2018-09-29 00:00:00")) ENGINE = InnoDB,
(...)
PARTITION p2018_11_25 VALUES LESS THAN (UNIX_TIMESTAMP("2018-11-26 00:00:00")) ENGINE = InnoDB);

ALTER TABLE `[B]history_str[/B]` PARTITION BY RANGE ( clock)(
PARTITION p2018_09_28 VALUES LESS THAN (UNIX_TIMESTAMP("2018-09-29 00:00:00")) ENGINE = InnoDB,
(...)
PARTITION p2018_11_25 VALUES LESS THAN (UNIX_TIMESTAMP("2018-11-26 00:00:00")) ENGINE = InnoDB);


ALTER TABLE `[B]history_text[/B]` PARTITION BY RANGE ( clock)(
PARTITION p2018_09_28 VALUES LESS THAN (UNIX_TIMESTAMP("2018-09-29 00:00:00")) ENGINE = InnoDB,
(...)
PARTITION p2018_11_25 VALUES LESS THAN (UNIX_TIMESTAMP("2018-11-26 00:00:00")) ENGINE = InnoDB);


ALTER TABLE `[B]history_log[/B]` PARTITION BY RANGE ( clock)(
PARTITION p2018_09_28 VALUES LESS THAN (UNIX_TIMESTAMP("2018-09-29 00:00:00")) ENGINE = InnoDB,
(...)
PARTITION p2018_11_25 VALUES LESS THAN (UNIX_TIMESTAMP("2018-11-26 00:00:00")) ENGINE = InnoDB);


ALTER TABLE `[B]trends_uint[/B]` PARTITION BY RANGE ( clock) (
PARTITION p2018_12 VALUES LESS THAN (UNIX_TIMESTAMP("2018-01-01 00:00:00")) ENGINE = InnoDB,
(...)
PARTITION p2019_11 VALUES LESS THAN (UNIX_TIMESTAMP("2018-12-01 00:00:00")) ENGINE = InnoDB);

ALTER TABLE `[B]trends[/B]` PARTITION BY RANGE ( clock) (
PARTITION p2018_12 VALUES LESS THAN (UNIX_TIMESTAMP("2018-01-01 00:00:00")) ENGINE = InnoDB,
(...)
PARTITION p2019_11 VALUES LESS THAN (UNIX_TIMESTAMP("2018-12-01 00:00:00")) ENGINE = InnoDB);

[ingus.vilnis]

And no errors once running them??

BTW, you got the years wrong for the first partition of both trends tables.

This is the expected output for a partitioned table in filesystem:

# ls -lah history*
-rw-r-----. 1 mysql mysql 8.5K Nov 27 02:10 /zabbixdb/zabbix/history.frm
-rw-r-----. 1 mysql mysql 8.7K Nov 27 02:10 /zabbixdb/zabbix/history_log.frm
-rw-r-----. 1 mysql mysql  23M Nov 17 23:43 /zabbixdb/zabbix/history_log#P#p2018_11_17.ibd
-rw-r-----. 1 mysql mysql  10M Nov 19 00:03 /zabbixdb/zabbix/history_log#P#p2018_11_18.ibd
-rw-r-----. 1 mysql mysql  11M Nov 20 00:11 /zabbixdb/zabbix/history_log#P#p2018_11_19.ibd
-rw-r-----. 1 mysql mysql  14M Nov 21 00:19 /zabbixdb/zabbix/history_log#P#p2018_11_20.ibd
-rw-r-----. 1 mysql mysql  18M Nov 22 00:12 /zabbixdb/zabbix/history_log#P#p2018_11_21.ibd
-rw-r-----. 1 mysql mysql  10M Nov 23 00:09 /zabbixdb/zabbix/history_log#P#p2018_11_22.ibd
-rw-r-----. 1 mysql mysql  20M Nov 24 00:14 /zabbixdb/zabbix/history_log#P#p2018_11_23.ibd
-rw-r-----. 1 mysql mysql 9.0M Nov 25 00:12 /zabbixdb/zabbix/history_log#P#p2018_11_24.ibd
-rw-r-----. 1 mysql mysql  11M Nov 26 00:11 /zabbixdb/zabbix/history_log#P#p2018_11_25.ibd
-rw-r-----. 1 mysql mysql  15M Nov 27 00:09 /zabbixdb/zabbix/history_log#P#p2018_11_26.ibd
-rw-r-----. 1 mysql mysql 9.0M Nov 27 08:24 /zabbixdb/zabbix/history_log#P#p2018_11_27.ibd
-rw-r-----. 1 mysql mysql 112K Nov 18 02:27 /zabbixdb/zabbix/history_log#P#p2018_11_28.ibd
-rw-r-----. 1 mysql mysql 112K Nov 19 02:30 /zabbixdb/zabbix/history_log#P#p2018_11_29.ibd
-rw-r-----. 1 mysql mysql 112K Nov 20 02:30 /zabbixdb/zabbix/history_log#P#p2018_11_30.ibd
-rw-r-----. 1 mysql mysql 112K Nov 21 02:26 /zabbixdb/zabbix/history_log#P#p2018_12_01.ibd
-rw-r-----. 1 mysql mysql 112K Nov 22 02:25 /zabbixdb/zabbix/history_log#P#p2018_12_02.ibd
-rw-r-----. 1 mysql mysql 112K Nov 23 02:25 /zabbixdb/zabbix/history_log#P#p2018_12_03.ibd
-rw-r-----. 1 mysql mysql 112K Nov 24 02:24 /zabbixdb/zabbix/history_log#P#p2018_12_04.ibd
-rw-r-----. 1 mysql mysql 112K Nov 25 02:24 /zabbixdb/zabbix/history_log#P#p2018_12_05.ibd
-rw-r-----. 1 mysql mysql 112K Nov 26 02:24 /zabbixdb/zabbix/history_log#P#p2018_12_06.ibd
-rw-r-----. 1 mysql mysql 112K Nov 27 02:24 /zabbixdb/zabbix/history_log#P#p2018_12_07.ibd

[fsousa]

The ALTER TABLE run without any error... I have pasted the wrong code. The ALTER TABLE I've ran have he years correct, sorry.

I don't understand what happen, I will try once again.

[fsousa]

Hi,

I've some troubles with my database.

I've done a dump of my production database :

mysqldump -u root -p zabbix > db_zabbix.sql

And then I've imported to my test server:

mysql -u root -p zabbix < db_zabbix.sql

Apparently everything is ok, but when I got go my database folder and run the command

ls -lah history

I realized the

history_uint.ibd

is missing.

I'm missing something when I dump my database?

Thank you.

[ingus.vilnis]

Well, start with that your dump command is incorrect.
Should be at least this.

mysqldump --single-transaction -u root -p zabbix > db_zabbix.sql

[fsousa]

Ok, I will do that. Now I'm tryng to dump my database with the

--single-transaction

option.