Cleaning up auditlog.ibd?

[jeskasper]

Hello forum,

My zabbiz server is running out of disk space on the /var/lib/mysql partition. I know that I can just expand that partition, but I would rather schring the audit log - as I don't use it at all. However, I have lowered the retention of audit logs, but the housekeeping does not seem to delete records, or the table is not shrinking. How can I get around to reclaiming the 8+ GB this table is taking up on my server?

[Markku]

Regarding reclaiming the used space for auditlog table. I first deleted records older than I needed ("delete from auditlog where clock < xxxxx;"), and then used the same export-import method as in the primary key adding process: https://www.zabbix.com/documentation...ary_keys#mysql

- Shutdown Zabbix
- Renamed the auditlog table to auditlog_old
- Used "show create table auditlog_old" to get the command to create new auditlog table
- Exported the data from auditlog_old to a CSV file
- Imported the CSV data to auditlog table
- Started Zabbix and checked everything was fine
- Dropped the old and huge auditlog_old table.

Markku

[tim.mooney]

Really good information you've provided in this thread on how you fixed this issue for your site. I wish more people on these forums posted their solutions, like you did.

Some possible optimizations ("improvements") I can think of that might have saved a little time:

1. since you were able to successfully delete records that you didn't need from your existing auditlog table, if you had enough free disk space for another copy the size of the auditlog table, you might have been able to skip the entire procedure that you did by instead issuing an "OPTIMIZE TABLE auditlog" (https://dev.mysql.com/doc/refman/8.0...ize-table.html). It creates a (hopefully smaller) copy of the table, which is why you may need the size of the auditlog table available as free space, while it's working to create an optimized, shrunk table.

2. if the OPTIMIZE TABLE operation didn't do enough or couldn't be attempted because you didn't have the free space, and you had to create a new empty table and move data into, you don't need to use "SHOW CREATE TABLE ..." and then run what it outputs. You can just use "CREATE TABLE LIKE ..." (https://dev.mysql.com/doc/refman/8.0...able-like.html). That does it for you and gets all the indexes, etc. Super easy, once you're familiar with it.

3. if your version of MariaDB/MySQL/Percona supports it, the export and import can be done without an intermediate file, using a combination of INSERT and SELECT: https://dev.mysql.com/doc/refman/8.0...rt-select.html . Even if your version of the database isn't new enough to support the "entire table" syntax ( INSERT INTO auditlog TABLE auditlog_old ), when there's only a few columns for a table then using the older syntax that specifies specific columns to insert works well for simple tables like auditlog.

Those are just suggestions; the process you used was very good.

[Markku]

Thanks Tim, I appreciate your comments!

Markku

[jeskasper]

Thank you very much Markku. This worked perfectly, and I have now reclaimed my space. Just as Tim said, it is really great that you provide the steps.

[Markku]

Let's just say that my interest for good practices for the auditlog table on Zabbix 6.0 has raised... Just realized it was over 16 GB on one of my installs.

I tried partitioning it (to be better able to drop old data), but that doesn't work the same way as with history/trends tables:

MariaDB [zabbix]> ALTER TABLE auditlog PARTITION BY RANGE (clock)
-> (
-> PARTITION p2022_07_01 VALUES LESS THAN (UNIX_TIMESTAMP("2022-07-02 00:00:00")) ENGINE = InnoDB,
-> PARTITION p2022_07_02 VALUES LESS THAN (UNIX_TIMESTAMP("2022-07-03 00:00:00")) ENGINE = InnoDB,
-> PARTITION p2022_07_03 VALUES LESS THAN (UNIX_TIMESTAMP("2022-07-04 00:00:00")) ENGINE = InnoDB,
-> PARTITION p2022_07_04 VALUES LESS THAN (UNIX_TIMESTAMP("2022-07-05 00:00:00")) ENGINE = InnoDB,
-> PARTITION p2022_07_05 VALUES LESS THAN (UNIX_TIMESTAMP("2022-07-06 00:00:00")) ENGINE = InnoDB,
-> PARTITION p2022_07_06 VALUES LESS THAN (UNIX_TIMESTAMP("2022-07-07 00:00:00")) ENGINE = InnoDB,
-> PARTITION p2022_07_07 VALUES LESS THAN (UNIX_TIMESTAMP("2022-07-08 00:00:00")) ENGINE = InnoDB,
-> PARTITION p2022_07_08 VALUES LESS THAN (UNIX_TIMESTAMP("2022-07-09 00:00:00")) ENGINE = InnoDB,
-> PARTITION p2022_07_09 VALUES LESS THAN (UNIX_TIMESTAMP("2022-07-10 00:00:00")) ENGINE = InnoDB
-> );
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
MariaDB [zabbix]>

Then I tried this (I'm not a database expert...):

MariaDB [zabbix]> alter table auditlog add primary key (`clock`);
ERROR 1068 (42000): Multiple primary key defined
MariaDB [zabbix]>

This is how "show create table auditlog" shows the keys:

PRIMARY KEY (`auditid`),
KEY `auditlog_1` (`userid`,`clock`),
KEY `auditlog_2` (`clock`),
KEY `auditlog_3` (`resourcetype`,`resourceid`)

Comments anyone? Other than disabling the audit log?

Markku

[Markku]

Just to add: I also see that the housekeeper is not deleting anything from auditlog, regardless of the audit log housekeeping settings. This is Zabbix 6.0.5.

Markku

[Markku]

Realized this in the 6.0.6 release notes (https://www.zabbix.com/rn/rn6.0.6):

ZBX-21145

Changed host, item and trigger tag merging logic during template linking and LLD to reduce database updates

It doesn't say "auditlog" but it apparently deals with most of the auditlog entries.

Markku

[Markku]

Regarding reclaiming the used space for auditlog table. I first deleted records older than I needed ("delete from auditlog where clock < xxxxx;"), and then used the same export-import method as in the primary key adding process: https://www.zabbix.com/documentation...ary_keys#mysql

- Shutdown Zabbix
- Renamed the auditlog table to auditlog_old
- Used "show create table auditlog_old" to get the command to create new auditlog table
- Exported the data from auditlog_old to a CSV file
- Imported the CSV data to auditlog table
- Started Zabbix and checked everything was fine
- Dropped the old and huge auditlog_old table.

Markku

[tim.mooney]

Really good information you've provided in this thread on how you fixed this issue for your site. I wish more people on these forums posted their solutions, like you did.

Some possible optimizations ("improvements") I can think of that might have saved a little time:

1. since you were able to successfully delete records that you didn't need from your existing auditlog table, if you had enough free disk space for another copy the size of the auditlog table, you might have been able to skip the entire procedure that you did by instead issuing an "OPTIMIZE TABLE auditlog" (https://dev.mysql.com/doc/refman/8.0...ize-table.html). It creates a (hopefully smaller) copy of the table, which is why you may need the size of the auditlog table available as free space, while it's working to create an optimized, shrunk table.

2. if the OPTIMIZE TABLE operation didn't do enough or couldn't be attempted because you didn't have the free space, and you had to create a new empty table and move data into, you don't need to use "SHOW CREATE TABLE ..." and then run what it outputs. You can just use "CREATE TABLE LIKE ..." (https://dev.mysql.com/doc/refman/8.0...able-like.html). That does it for you and gets all the indexes, etc. Super easy, once you're familiar with it.

3. if your version of MariaDB/MySQL/Percona supports it, the export and import can be done without an intermediate file, using a combination of INSERT and SELECT: https://dev.mysql.com/doc/refman/8.0...rt-select.html . Even if your version of the database isn't new enough to support the "entire table" syntax ( INSERT INTO auditlog TABLE auditlog_old ), when there's only a few columns for a table then using the older syntax that specifies specific columns to insert works well for simple tables like auditlog.

Those are just suggestions; the process you used was very good.

[Markku]

Thanks Tim, I appreciate your comments!

Markku

[jeskasper]

Thank you very much Markku. This worked perfectly, and I have now reclaimed my space. Just as Tim said, it is really great that you provide the steps.

[Markku]

Realized this in the 6.0.6 release notes (https://www.zabbix.com/rn/rn6.0.6):

ZBX-21145

Changed host, item and trigger tag merging logic during template linking and LLD to reduce database updates

It doesn't say "auditlog" but it apparently deals with most of the auditlog entries.

To confirm: This upgrade from 6.0.5 to 6.0.6 got the auditlog growth in control, no more huge amount of entries in audit log due to LLD runs.

Markku

[kawanjaberi]

Dear @Markku

I have one environment which has production and all data is very importance. But I don't need auditlog table and 50% percent(800GB just auditlog) of diskspace utilize because of this. my zabbix version is 6.0.0. So I want delete all records from auditlog. I have innodb cluster on DB. So below steps is correct or any comment for me.???

1.without stoping service or I should stop my zabbix server?
2.CREATE TABLE auditlog_new LIKE auditlog; ????
3.DROP TABLE auditlog; ???
4.Rename auditlog_new to auditlog ????
5.start my zabbix server service.

[kawanjaberi]

Like this
CREATE TABLE auditlog_new LIKE auditlog;
ALTER TABLE auditlog RENAME auditlog_old;
ALTER TABLE auditlog_new RENAME auditlog;
DROP TABLE auditlog_old;

[Markku]

Oh, I haven't ever done anything like that (CREATE TABLE LIKE, ALTER TABLE RENAME) except when specifically following some instructions, so you need to verify the commands yourself. If you don't want to copy your big database for testing, just install a new Zabbix server with the same version, run it for a while, and then test it with that data.

But note that if you are still using the old Zabbix 6.0.0 version your auditlog will start growing again, so it's highly recommended to upgrade to 6.0.12 (or so) anyway.

Markku