Database seems to be growing too quick

[fsousa]

Hello,

My database seems to be growing too quick

The housekeeper seems not able to clean up the old data. I have the following config:



My largest files are:

[B]4,3G history.ibd
36G history_text.ibd
27G history_uint.ibd
2,3G trends.ibd
11G trends_uint.ibd[/B]

What I'm missing?

Could someone please share me steps how to clean up databases in Mysql?

Thanks

[ingus.vilnis]

Hi,
Partitioning for history* and trends* tables is the way to go.

http://zabbix.org/wiki/Docs/howto/mysql_partitioning

Read through this whole thing and try to understand it first. It is very version specific regarding both Zabbix and MySQL. Once you know your versions and know what exact steps are needed only then you are safe to proceed.

[fsousa]

Hi,

I read the docs but I don't understand how partitioning of history and trends gonna help me. Can you help me to understand that?

Thank you.

[ingus.vilnis]

The problem with housekeeper at larger setups is that it can't delete all oldest history entries in time and it utilizes database heavily.

To fix that you can configure partitioning which just drops the oldest data by days or months and at the same time disable housekeeper.

How have you verified your original assumption that housekeeper is not catching up with the deletes? Do you see that in Zabbix internal performance graphs or elsewhere?

[fsousa]

Thank you Ingus. The partitions will be deleted automatically? I've approximately 500 hosts, what is your suggestions for my history in days?

I see that information when I realize my hard disk size increase every week.

My versions are:
Zabbix 3.4.1
MySql 5.6.37

Thank you

[ingus.vilnis]

Yes, partitions are supposed to be deleted automatically if everything is configured properly.
Well the number of days to keep really depends on the disk size, number of items you have and how frequently are they updated (new values per second).

Just be aware that it may take some hours to do the partitioning on an existing database.

[fsousa]

My disk have 120 GB.

So if I understand the documentation I have to run the external script present in the step "Partitioning with an external script", right?

Thank you.

[ingus.vilnis]

Yes, you have to run the script but before that you need to also partition the existing data if you don't want it to be lost.

First, have you got this enabled?
SELECT plugin_status FROM information_schema.plugins WHERE plugin_name = 'partition';
What OS are you running?
What is the free space on DB disk?
Can you afford having downtime of Zabbix during the process?
Do you have full DB backup to which you KNOW how to recover if things go very wrong?

[fsousa]

SELECT plugin_status FROM information_schema.plugins WHERE plugin_name = 'partition';

mysql> SELECT plugin_status FROM information_schema.plugins WHERE plugin_name = 'partition';
+---------------+
| plugin_status |
+---------------+
| ACTIVE        |
+---------------+

What OS are you running?
R: CentOS 7

What is the free space on DB disk?
R: 26G

Can you afford having downtime of Zabbix during the process?
R: yes

Do you have full DB backup to which you KNOW how to recover if things go very wrong?
R: I've dumped my zabbix database. If the things go very wrong I can erase the database and use my dump?


Thank for your patience and help.

[ingus.vilnis]

SELECT FROM_UNIXTIME(MIN(clock)) FROM `history_uint`;
SELECT FROM_UNIXTIME(MIN(clock)) FROM `history_str`;
SELECT FROM_UNIXTIME(MIN(clock)) FROM `history_text`;
SELECT FROM_UNIXTIME(MIN(clock)) FROM `history_log`;
SELECT FROM_UNIXTIME(MIN(clock)) FROM `history`;
SELECT FROM_UNIXTIME(MIN(clock)) FROM `trends_uint`;
SELECT FROM_UNIXTIME(MIN(clock)) FROM `trends`;

In this way you will tell what are the oldest entries in your DB and for how long you need to partition the data. Once done this, you are ready to prepare the queries for existing tables in the following manner.

ALTER TABLE `history_uint` PARTITION BY RANGE ( clock)
(PARTITION p2018_11_03 VALUES LESS THAN (UNIX_TIMESTAMP("2018-11-04 00:00:00")) ENGINE = InnoDB,
 PARTITION p2018_11_04 VALUES LESS THAN (UNIX_TIMESTAMP("2018-11-05 00:00:00")) ENGINE = InnoDB,
 PARTITION p2018_11_05 VALUES LESS THAN (UNIX_TIMESTAMP("2018-11-06 00:00:00")) ENGINE = InnoDB);

You do this for all five history tables. Create those partitions for every day which you have in the history. The dates must be in increasing order. Pay attention that when you create the partition for e.g. today's date, the values must be less than the beginning of next day.

These commands take time. Can be up to hours. Don't interrupt them. And I would advise to stop Zabbix server and not use frontend to avoid any extra load on DB server if possible. And do the same for trends and trends_uint tables, just use months.

Regarding backup then you should be able to recover if you accidentally really break something.

P.S. I got to leave now. Here is what you have to do after you are done with the existing tables: https://www.zabbix.com/forum/zabbix-...854#post367854
But please look at the values in the attached script for how many days you keep the history. It should match the oldest current entries in your DB. Or you have to decide how long do you really need to keep your data to match the disk.

Good luck!

[EduardoAran]

Sry for the suden question, but in those first comands, how long does it takes to give you an answer in a big DB (120 GB).

[fsousa]

Thank you so much Ingus.

I have only one more question. I have to create the partitions with the start date I want to save my history?

For example , if I want to maintain the history since August 2018, I have to do the following partition?

ALTER TABLE `history` PARTITION BY RANGE ( clock)
(PARTITION p2018_08_01 VALUES LESS THAN (UNIX_TIMESTAMP("p2018_08_01 00:00:00")) ENGINE = InnoDB,
PARTITION p2018_08_02 VALUES LESS THAN (UNIX_TIMESTAMP("p2018_08_02 00:00:00")) ENGINE = InnoDB,
..
PARTITION p2018_11_06 VALUES LESS THAN (UNIX_TIMESTAMP("p2018_11_06 00:00:00")) ENGINE = InnoDB);

or I have to add every month?

ALTER TABLE `trends_uint` PARTITION BY RANGE ( clock)
(PARTITION p2018_08 VALUES LESS THAN (UNIX_TIMESTAMP("2018-08-01 00:00:00")) ENGINE = InnoDB,
 PARTITION p2018_09 VALUES LESS THAN (UNIX_TIMESTAMP("2018-09-01 00:00:00")) ENGINE = InnoDB,
 PARTITION p2018_10 VALUES LESS THAN (UNIX_TIMESTAMP("2018-10-01 00:00:00")) ENGINE = InnoDB,
...
 PARTITION p2018_11 VALUES LESS THAN (UNIX_TIMESTAMP("2018-11-01 00:00:00")) ENGINE = InnoDB);

Than you.

[ingus.vilnis]

You have to create the partitions till the date where your oldest data entries are. You could find out the dates wit the "SELECT FROM_UNIXTIME(MIN(clock)) FROM...." queries above.
Or you agree that you keep e.g. 30 days of history and 12 months of trends. Then create your partitions accordingly.

But there is one mistake in the dates. You have missed this one sentence. "Pay attention that when you create the partition for e.g. today's date, the values must be less than the beginning of next day."

Therefore correct statements are like this:

ALTER TABLE `history` PARTITION BY RANGE ( clock)
(PARTITION p2018_08_01 VALUES LESS THAN (UNIX_TIMESTAMP("p2018_08_02 00:00:00")) ENGINE = InnoDB,
  (partition for whole day August 01 must contain values less than August 02 00:00:00)

And same with trends but accordingly with months. Please look again carefully in the provided examples and how the dates are set.

[fsousa]

Thank You Ingus!!

[fsousa]

Hi ingus,

In your post in: https://www.zabbix.com/forum/zabbix-...ations-request you have only one ALTER TABLE per table:

ALTER TABLE `history_uint` PARTITION BY RANGE ( clock) (PARTITION p2017_11_24 VALUES LESS THAN (UNIX_TIMESTAMP("2011-11-25 00:00:00") div 1) ENGINE = InnoDB);

What the option div 1 do?

I this discussion we discuss something like this:

ALTER TABLE `history_uint` PARTITION BY RANGE ( clock)
(PARTITION p2018_11_24 VALUES LESS THAN (UNIX_TIMESTAMP("2018-11-25 00:00:00")) ENGINE = InnoDB,
PARTITION p2018_11_25 VALUES LESS THAN (UNIX_TIMESTAMP("2018-11-26 00:00:00")) ENGINE = InnoDB,
PARTITION p2018_11_26 VALUES LESS THAN (UNIX_TIMESTAMP("2018-11-27 00:00:00")) ENGINE = InnoDB)

My min clock from history_unit is 2017_11_24.

My questions is: I have to create one partition per day since my first oldest data entries? Since 2017-11-24 to 2018-11-12? Or exist any way to do this without the entries unnecessary?

Thank you.

[ingus.vilnis]

Hi,
Yes, that linked post had only one partition because it was a clean install with no historical data to preserve, so partition only for that current day was needed.

In your case if you want to preserve all that history data then you need to create partitions for all the days you have the history. Yes, you have the oldest entries year ago. If you decide that you need the history only for a month then you need to create the partitions for the last one month and all data older than that will be in the last oldest partition and will be dropped by the removal script.

There is one concern that I have though. When creating partitions MySQL creates temporary tables. I have recently done partitioning only on clean installs where that is not a problem but I don't remember how much of temporary free space was needed when converting the tables. MySQL documentation is not explicitly mentioning this either.

[fsousa]

Hi,

I understand now what have you done

Now I only have one more question, I just need do partitioning the history_unit and trens_unit or I have to do for the other tables?

[B]history
history_log
history_str
history_text
history_uint
trends
trends_uint[/B]

Thank you for your explanation.