Database seems to be growing too quick

[ingus.vilnis]

Yes, normally all of them are partitioned because those performance problems of housekeeper depend on all seven of them. However, if you go through the partitioning manuals, they refer to partitioning of events, alerts etc tables. You don't need to do that, only listed history and trends tables.

[fsousa]

Thank you Ingus.

I'm a little confuse with two things:

1. After I run the script the space on my disk will be released? On mysql when the space of some databases was release the space in disk isn't. Normally I have to dump my database, drop the database and the import the dump.
2. Sometimes we have to monitor the availability and performance some machines. I need to be able to generate this graphs, counters for one year. But I can't understand what table/tables this data are stored. Is the trends or history or other tables?

Thank you.

[ingus.vilnis]

1. Correct, with partitioning the disk space will be released with every partition that you drop older than predefined number of retention days. For normal InnoDB tables in MySQL there indeed is the problem that even if you delete the data from DB itself, the disk space reserved by that table is not released.

2. Graphs over longer time periods are created from trends data. That is exactly the purpose of these tables as they contain hourly averages generated from all data points first stored in history tables. You lose the granularity if you zoom in the trend data but really how often do you need to see every single datapoint with minute granularity e.g. three months ago? Trends save you valuable disk space and improves the performance.

[fsousa]

Sorry, I can't understand what intervals I've to define in my script.

If I want to be able to generate graphs about one year ago I've to put trends to keep 12 months?

'trends' => { 'period' => 'month', 'keep_history' => '12'},
'trends_uint' => { 'period' => 'month', 'keep_history' => '12'}

And my history* tables? 30 days is ok?

Thank you for your explanation and help.

[ingus.vilnis]

Yes, correct. 12 months for trends if you want to see the graphs for a year. You can set a longer period there as well depending on what is the oldest entry in the trends tables. Now your disk space is the limit. You have to keep an eye on the disk usage and you can start with longer periods keeping your existing data. Once you feel the disk is getting full, the only way is to shorten the retention periods and store less data. Or alternatively increase the item update intervals to have less frequent polling. That will also slow down the growth of DB.

[fsousa]

Thank you Ingus.

I just add a new disk on my server with 300 GB capacity to "buy" some time.

I dumped my database and now I will setup one test environment and I will test the partitioning.

After the partitioning I've to setup the housekeeping with the same parameters defined in my partitioning script?

[ingus.vilnis]

Did you extend the disk to 300GB or will you dump the DB and move it to the new one?

If you do the dump then import then go with the partitioning immediately. On the new disk create the DB schema with partitions already and just fill them with data from the dump.

In any scenario when the partitioning is on you have to go to Administration -> General -> select Housekeeping in top right menu and deselect housekeeping for History and Trends sections. Otherwise Zabbix will still attempt to delete the data and unnecessary utilize the disk IO.

Oh, and the more you will grow your DB by buying time the longer the partitioning configuration will be. Don't buy the time too much.

[fsousa]

I extended the disk to 300GB.

Oh, and the more you will grow your DB by buying time the longer the partitioning configuration will be. Don't buy the time too much.

Yes, I will do this week or next week for sure.

So if I understand what are you trying to explain, if I have my DB dumped I can:

1. Erase database
2. Create the DB schema
3. Partitioning the tables
4. Import my dump

It is possible?

[ingus.vilnis]

It is possible. It could be faster than actually converting tables on a live DB because of large amount of data. But dropping and re-creating the DB also is a procedure where lots of stuff can go wrong.

It's your call. Safer would be to just stop Zabbix, take a full backup and do the ALTER TABLE commands till they are done.

[fsousa]

I will try the second option, stop zabbix, take a full bakup and do the alter table but first I will do in my test environment.

Thank you for all your support and explanation

[fsousa]

Hi Ingus,

So I'm trying to partitioning my tables but I've the following error:

ERROR 1697 (HY000): VALUES value for partition 'p2018_01_01' must have type INT

My first ALTER TABLE goes from 2018_01_01 and goes to 21_11_05:

ALTER TABLE `history` PARTITION BY RANGE ( clock) 
(PARTITION p2018_01_01 VALUES LESS THAN (UNIX_TIMESTAMP("p2018_01_02 00:00:00")) ENGINE = InnoDB,
PARTITION p2018_01_02 VALUES LESS THAN (UNIX_TIMESTAMP("p2018_01_03 00:00:00")) ENGINE = InnoDB,
PARTITION p2018_01_03 VALUES LESS THAN (UNIX_TIMESTAMP("p2018_01_04 00:00:00")) ENGINE = InnoDB,
PARTITION p2018_01_04 VALUES LESS THAN (UNIX_TIMESTAMP("p2018_01_05 00:00:00")) ENGINE = InnoDB,
PARTITION p2018_01_05 VALUES LESS THAN (UNIX_TIMESTAMP("p2018_01_06 00:00:00")) ENGINE = InnoDB,
PARTITION p2018_01_06 VALUES LESS THAN (UNIX_TIMESTAMP("p2018_01_07 00:00:00")) ENGINE = InnoDB,
PARTITION p2018_01_07 VALUES LESS THAN (UNIX_TIMESTAMP("p2018_01_08 00:00:00")) ENGINE = InnoDB,
PARTITION p2018_01_08 VALUES LESS THAN (UNIX_TIMESTAMP("p2018_01_09 00:00:00")) ENGINE = InnoDB,
(...)
PARTITION p2018_11_01 VALUES LESS THAN (UNIX_TIMESTAMP("p2018_11_02 00:00:00")) ENGINE = InnoDB,
PARTITION p2018_11_02 VALUES LESS THAN (UNIX_TIMESTAMP("p2018_11_03 00:00:00")) ENGINE = InnoDB,
PARTITION p2018_11_03 VALUES LESS THAN (UNIX_TIMESTAMP("p2018_11_04 00:00:00")) ENGINE = InnoDB,
PARTITION p2018_11_04 VALUES LESS THAN (UNIX_TIMESTAMP("p2018_11_05 00:00:00")) ENGINE = InnoDB,
PARTITION p2018_11_05 VALUES LESS THAN (UNIX_TIMESTAMP("p2018_11_06 00:00:00")) ENGINE = InnoDB);

What I'm doing wrong?

I saw the div 1 option in your post

https://www.zabbix.com/forum/zabbix-for-large-environments/367740-mysql-partitioning-recommendations-request

, what is the propose?

Thank you.

[ingus.vilnis]

Please do the commands as in the linked post. My example earlier was for you to just understand the dates, not a fully working command, sorry.

[ingus.vilnis]

And you are missing dates up to today.

[fsousa]

The database I'm using only have information until 2018-11-05, I've to up date to today?

[ingus.vilnis]

In such case probably no. Strange concept you have then.