Problem with size history_uint table

[Adrian_midway]

Hello all,

Mi name is Adrian and i have a problem with size Zabbix database, specifically with history_uint table.

The size os this table is:

MariaDB [zabbix]> SELECT TABLE_SCHEMA, TABLE_NAME,(INDEX_LENGTH+DATA_LENGTH)/(1024*1024) AS SIZE_MB, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN ("mysql", "information_schema") ORDER BY SIZE_MB DESC LIMIT 10;

+-----------------------+-----------------------+-----------------+--------------------+

| TABLE_SCHEMA | TABLE_NAME | SIZE_MB | TABLE_ROWS |

+-----------------------+-----------------------+-----------------+--------------------+

| zabbix | history_uint | 84541.0313 | 947528396 |

| zabbix | history | 13215.0781 | 170720936 |

| zabbix | trends_uint | 4746.4063 | 62148075 |

| zabbix | trends | 590.8750 | 8094904 |

| zabbix | events | 420.8281 | 3210823 |

| zabbix | event_recovery | 167.3125 | 1546844 |

| zabbix | alerts | 85.3906 | 109549 |

| zabbix | auditlog | 65.1719 | 468274 |

| zabbix | items | 12.9063 | 18074 |

| zabbix | history_str | 11.1250 | 110821 |

+-----------------------+-----------------------+----------------+---------------------+

10 rows in set (0.00 sec)

If I view the column "clock" on this table I descovered data of 2017:


MariaDB [zabbix]> select itemid, FROM_UNIXTIME(clock),value, ns FROM history_uint limit 0,5;

+---------+-----------------------------------+--------+----------------+

| itemid | FROM_UNIXTIME(clock) | value | ns |

+---------+-----------------------------------+--------+----------------+

| 37125 | 2017-07-28 17:02:35 | 0 | 388657237 |

| 37126 | 2017-07-28 17:02:35 | 0 | 388657237 |

| 37126 | 2017-07-28 17:03:35 | 0 | 944036464 |

| 37125 | 2017-07-28 17:03:35 | 0 | 944036464 |

| 37125 | 2017-07-28 17:04:37 | 0 | 336764628 |

+---------+----------------------------------+---------+----------------+

5 rows in set (0.19 sec)


MariaDB [zabbix]> select itemid, clock,value, ns FROM history_uint limit 0,5;

+-----------+-----------------+---------+-----------------+

| itemid | clock | value | ns |

+-----------+-----------------+---------+-----------------+

| 37125 | 1501261355 | 0 | 388657237 |

| 37126 | 1501261355 | 0 | 388657237 |

| 37126 | 1501261415 | 0 | 944036464 |

| 37125 | 1501261415 | 0 | 944036464 |

| 37125 | 1501261477 | 0 | 336764628 |

+----------+------------------+---------+-----------------+

5 rows in set (0.11 sec)


I will like to delete all old data, for exemple I execute command for delete information before 201-07-29 (only delete 1 old data day for a test):

root@TEST-ZABBIX3:~#: date +%s -d "Jul 29, 2017 00:00:00"

1501286400

MariaDB [zabbix]> DELETE FROM history_uint WHERE clock < 1501286400;


And the command run infinite, never end.

I also tried it with the command: DELETE FROM history_uint WHERE clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 90 DAY));…….Same results.


I need delete old data to liberate disk.


Additionally I check the Housekeeper configuration (picture adjunted) and i think that it's OK. This configuration it's changed 1 month ago.


We have a disk with 128GB and now it have only 8gb free

Status of Zabbix:

Number of hosts (enabled/disabled/templates) 235 - 142 / 12 / 81

Number of items (enabled/disabled/not supported) 16485 - 12965 / 355 / 3165

Number of triggers (enabled/disabled [problem/ok]) 2845 - 2006 / 839 [189 / 1817]

Number of users (online) 29 1


Any ideas?

If you need more information, tell me.


Thank you,


Adrian.

[Andrzej PAwlik]

How you have set Haousekepeer in zabbix_server.conf?

You try up interval item from 1 minut to 2 minut or 30s to 2m on the host?

In your picture you store data 270 days .. I story raw data from 30 days and trend data from 90 days..

[Adrian_midway]

How you have set Haousekepeer in zabbix_server.conf?

You try up interval item from 1 minut to 2 minut or 30s to 2m on the host?

In your picture you store data 270 days .. I story raw data from 30 days and trend data from 90 days..

I have reviewed zabbix_server.conf and it don't contain any Housekeeper parameters, I don't know how they installed first time.

When you say "In your picture you store data 270 days .. I story raw data from 30 days and trend data from 90 days." I understan that 30 days for history data and 90 days from trend data are default parameters, right?.
I review my configurations items and I view diferent configurations from History (1w, 30d and 90d) and only configuration from trends (365d)
Would I have to change the configuration of 270 days for 90 days on housekeeping? (image adjunted)

Thanks a lot for your reply