Some databases (for example, MySQL) require the partitioning column to be part of the table's unique constraint. Therefore, to partition the auditlog
table by time, the primary key must be changed from auditid
to a composite key auditid
+ clock
.
This section provides instructions for altering the primary key of the auditlog
table.
The instructions provided on this page are designed for advanced users. Note that these instructions might need to be adjusted for your specific configuration. Altering the primary key can also be incompatible with future upgrade patches, so manually handling future upgrades may be necessary.
Altering the primary key can be a resource-intensive operation that takes a lot of time depending on the auditlog
table size. Stopping Zabbix server and switching Zabbix frontend to maintenance mode for the time of the alteration is recommended. However, if absolutely necessary, there is a way to alter the primary key without downtime (see below).
Partitioning the auditlog
table can improve, for example, housekeeping in large setups. Although Zabbix housekeeping currently cannot take advantage of partitioned tables (except for TimescaleDB), you can disable Zabbix housekeeping and delete partitions using scripts.
Since Zabbix 7.0, the auditlog
table for TimescaleDB has been converted to a hypertable, which allows the housekeeper to drop data by chunks. To upgrade the existing auditlog
table to a hypertable, rerun the timescaledb.sql
script (located in database/postgresql), before starting Zabbix server. Zabbix server will log a warning, if started without running this script first. See also: TimescaleDB setup.
MySQL automatically rebuilds indexes for the primary key during the ALTER TABLE
operation. However, it is highly recommended to also manually rebuild indexes with the OPTIMIZE TABLE
operation to ensure optimal database performance.
Rebuilding indexes may temporarily require as much additional disk space as the table itself uses. To obtain the current size of data and indexes, you can execute the ANALYZE TABLE auditlog;
operation and then the SHOW TABLE STATUS LIKE 'auditlog';
operation.
If the available disk space is a concern, follow the Altering primary key without downtime instructions. Other options are also available:
sort_buffer_size
MySQL parameter may help to reduce disk space usage when manually rebuilding indexes. However, modifying this variable may impact overall memory usage of the database.1. Drop the current auditlog
table primary key and add the new primary key.
2. Rebuild indexes (optional but highly recommended, see Important notes on rebuilding indexes).
Manual method of altering the primary key is described here. Alternatively, you can use the pt-online-schema-change toolkit from Percona. This toolkit performs the following actions automatically, while also minimizing the space used for altering the auditlog
table.
1. Create a new table with the new primary key and create indexes.
CREATE TABLE `auditlog_new` (
`auditid` varchar(25) NOT NULL,
`userid` bigint unsigned NULL,
`username` varchar(100) DEFAULT '' NOT NULL,
`clock` integer DEFAULT '0' NOT NULL,
`ip` varchar(39) DEFAULT '' NOT NULL,
`action` integer DEFAULT '0' NOT NULL,
`resourcetype` integer DEFAULT '0' NOT NULL,
`resourceid` bigint unsigned NULL,
`resource_cuid` varchar(25) NULL,
`resourcename` varchar(255) DEFAULT '' NOT NULL,
`recordsetid` varchar(25) NOT NULL,
`details` longtext NOT NULL,
PRIMARY KEY (auditid,clock)
) ENGINE=InnoDB;
CREATE INDEX `auditlog_1` ON `auditlog_new` (`userid`,`clock`);
CREATE INDEX `auditlog_2` ON `auditlog_new` (`clock`);
CREATE INDEX `auditlog_3` ON `auditlog_new` (`resourcetype`,`resourceid`);
2. Swap tables.
3. Copy data from the old table to the new table.
This can be done in chunks (multiple INSERT INTO
statements with WHERE clock
clauses as needed) to avoid excessive resource usage.
4. Drop the old table.
PostgreSQL automatically rebuilds indexes for the primary key during the ALTER TABLE
operation. However, it is highly recommended to also manually rebuild indexes with the REINDEX TABLE CONCURRENTLY
operation to ensure optimal database performance.
Rebuilding indexes may temporarily require up to three times of disk space currently used by indexes. To obtain the current size of indexes, you can execute the SELECT pg_size_pretty(pg_indexes_size('auditlog'));
operation.
If the available disk space is a concern, follow the Altering primary key without downtime instructions. Other options are also available:
maintenance_work_mem
PostgreSQL parameter may help to reduce disk space usage when manually rebuilding indexes. However, modifying this variable may impact overall memory usage of the database.temp_tablespaces
PostgreSQL parameter to specify a different tablespace for temporary objects.1. Drop the current auditlog
table primary key and add the new primary key.
ALTER TABLE auditlog DROP CONSTRAINT auditlog_pkey;
ALTER TABLE auditlog ADD PRIMARY KEY (auditid,clock);
2. Rebuild indexes (optional but highly recommended, see Important notes on rebuilding indexes).
Manual method of altering the primary key is described here. Alternatively, the pg_repack
extension can be considered for creating a new table, copying data, and swapping tables.
1. Create a new table with the new primary key and create indexes.
CREATE TABLE auditlog_new (
auditid varchar(25) NOT NULL,
userid bigint NULL,
username varchar(100) DEFAULT '' NOT NULL,
clock integer DEFAULT '0' NOT NULL,
ip varchar(39) DEFAULT '' NOT NULL,
action integer DEFAULT '0' NOT NULL,
resourcetype integer DEFAULT '0' NOT NULL,
resourceid bigint NULL,
resource_cuid varchar(25) NULL,
resourcename varchar(255) DEFAULT '' NOT NULL,
recordsetid varchar(25) NOT NULL,
details text DEFAULT '' NOT NULL,
PRIMARY KEY (auditid,clock)
);
CREATE INDEX auditlog_new_1 ON auditlog_new (userid,clock);
CREATE INDEX auditlog_new_2 ON auditlog_new (clock);
CREATE INDEX auditlog_new_3 ON auditlog_new (resourcetype,resourceid);
2. Swap tables.
3. Copy data from the old table to the new table.
This can be done in chunks (multiple INSERT INTO
statements with WHERE clock
clauses as needed) to avoid excessive resource usage.
4. Drop the old table.