Clear History Data

[datacare]

I am running Zabbix 5.2 my history table is full and drive is almost full. I have read several articles but the commands I have tried give me a syntax error. Can someone please give me the command to delete the older data in the history table so I can free up some space?

I am running: Zabbix 5.2 Server version: 10.1.48-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

[datacare]

Got this resolved, but here's what happened.

First concern was backing up the database. This happens to be a pretty poorly documented topic. On this forum or any other. What I did was
1. Stopped zabbix server
2. mysqldump -u zabbix -p zabbix --single-transaction --quick --lock-tables=false > /mnt/zabbix_server/zabbix_database-backup-$(date +%F).sql

Next I discovered that using commands such as DELETE FROM history and DELETE FROM history)uint or even using commands like DELETE FROM history WHERE clock < 1641790800 these commands only delete records but do not restore ANY disk space in fact it made the tables grow in size completely filling the drive on the server!!
By the way anyone looking for the Linux time stamp used in DELETE FROM history WHERE clock < 1641790800 that information can be found here: https://www.unixtimestamp.com/?unixT...B%7Bs%7D%7D%7D

So in order to restore space I had to create a new history and historry_uint tables and drop the old tables. I used these commands to do so.
CREATE history_new LIKE history
CREATE history_uint_new LIKE history_uint
ALTER TABLE history RENAME history_old
ALTER TABLE history_uint RENAME history_uint_old
ALTER TABLE history_new RENAME history
ALTER TABLE history_uint_new RENAME history_uint
DROP history_old
DROP history_uint_old

This created new empty tables formatted the same as the old ones and deleted the old tables taking up so much space.


At this point Apache was crashed I had to reinstall apache2 and redownload the Zabbix 5.2 server using wget and unpack it with tar command. Then copy the contents of the UI folder in what I unpacked to the root of my /var/www/html folder and set the permissions using the following commands:
mv /home/administrator/zabbix-5.2.7rc1/ui /var/www/html/zabbix
chown www-data.www-data /var/www/html/zabbix/* -R


****** I hope this helps someone else someday that may get into this same bind and looking for help, which was difficult to find in my case....

[egorky]

Hi, I´m going to need to the same. My space ran out and the DB is not shrinking. But, I have a question datacare , you create the new tables BEFORE you drop the old ones? doesn't doing that create a duplicate of said tables? thanks in advance.

[tim.mooney]

The order that datacare did stuff is correct. Review the "CREATE TABLE" command in the MySQL doc. The first line created a new table named "history_new", with the exact same layout as as the existing "history" table, but without any data in it. You can't drop the existing table first *if* you're going to use the "... LIKE old_table_name" to duplicate the schema from old table to new table name.

Most databases don't shrink tables when you delete data from them. They leave the space for future use. That's very standard database behavior.

With MySQL, if you've deleted a bunch of rows from a table and you want to actually shrink the table's on-disk footprint, you need to look at the "OPTIMIZE TABLE" command. This requires additional space while the optimization is going on, so if you've already filled your disk, it's not really an option.

[egorky]

you are right, Tim, my mistake. Thanks.

[doutdex]

I saved 17GB deleting history only for zabbix zabbix-server-pgsql-5.0.20-1.el7.x86_64 also I was checking that zabbix file config. /etc/zabbix/zabbix_server.conf , uncomment HousekeepingFrequency=24
MaxHousekeeperDelete=50000




truncate table history;

truncate table history_log;

truncate table history_str;

truncate table history_uint;

truncate table history_text ;

I am running Zabbix 5.2 my history table is full and drive is almost full. I have read several articles but the commands I have tried give me a syntax error. Can someone please give me the command to delete the older data in the history table so I can free up some space?

I am running: Zabbix 5.2 Server version: 10.1.48-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

[cyber]

I saved 17GB deleting history only for zabbix zabbix-server-pgsql-5.0.20-1.el7.x86_64 also I was checking that zabbix file config. /etc/zabbix/zabbix_server.conf , uncomment HousekeepingFrequency=24
MaxHousekeeperDelete=50000




truncate table history;

truncate table history_log;

truncate table history_str;

truncate table history_uint;

truncate table history_text ;

Please, do not follow this kind of suggestions to delete all of your data... Most irresponsible advice one can give..