Large history_log table 576GB

[joergi]

Hello,

I have checked the database files of the mysql db and the history_log.ibd has a size of 576GB. Is it possible to shrink this table (houskeeper or script)?
In addition I am checking the monitored items (history storage period of type "log").

Many thanks,
Jörg

[radix]

Delete from table don't reclaim disk space on OS level in innodb case. Table partitioning is way to go in your case.

[joergi]

but the table is big, looks like there is too much data in it. Maybe there is a lot of orphaned data in it.


SELECT TABLE_NAME AS 'Table', ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS 'Size (MB)' FROM information_schema.TABLES WHERE TABLE_SCHEMA = "zabbix" ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

+----------------------------+-----------+
| Table | Size (MB) |
+----------------------------+-----------+
| history_log | 576914 |
| history_uint | 44663 |
| history_text | 11899 |
| history | 11015 |

[joergi]

Hello Radix,

do you have a guide for Table Partitioning on Zabbix 4.0.15?

Thanks,
Jörg

[joergi]

Hello,

is it possible to re-create only that one table?

Thanks,
Jörg

[joergi]

Hello,

I have found this link: http://whatizee.blogspot.com/2016/10...-clean-up.html
It uses a temp table for importing data from a history table (eg last three month) and after this, it drops the origanal table and rename the temp table back to the original name. Has someone experiences with such workaround? For me it looks not so bad.

Thanks,
Jörg