Table of Contents

4 为分区准备审计日志表

4 为分区准备审计日志表

概述

某些数据库（例如，MySQL）要求分区列必须是表的唯一约束的一部分。因此，为了按时间对 auditlog 表进行分区，必须将主键从 auditid 更改为复合键 auditid + clock。

本节提供了更改 auditlog 表主键的说明。

本页提供的说明适用于高级用户。请注意，这些说明可能需要根据您的特定配置进行调整。修改主键可能与未来的升级补丁不兼容，因此可能需要手动处理未来的升级。

修改主键可能是一项资源消耗较大的操作，所需时间取决于 auditlog 表的大小。建议在修改期间停止 Zabbix server 并将 Zabbix 前端切换到 maintenance mode。但是，如果确实必要，也可以在不停机的情况下修改主键（见下文）。

对 auditlog 表进行分区可以改善例如在大型环境中的 housekeeping 操作。
尽管 Zabbix housekeeping 当前还无法利用分区表（TimescaleDB 除外），但您可以禁用 Zabbix 的 housekeeping 功能，并通过脚本管理 delete 分区。

自 Zabbix 7.0 开始，用于 TimescaleDB 的 auditlog 表已转换为 hypertable，这使得 housekeeper 可以按数据块来删除数据。
要将现有的 auditlog 表升级为 hypertable，请参阅升级timescaledb架构。

MySQL

重建索引的重要注意事项

MySQL在执行ALTER TABLE操作期间会自动重建主键索引。
但是，建议同时使用OPTIMIZE TABLE语句手动重建索引，以确保数据库性能处于最佳状态。

重建索引可能会临时占用与表本身大小相当的额外磁盘空间。
要获取当前数据和索引的大小，可以execute执行以下语句：

ANALYZE TABLE auditlog;
       SHOW TABLE STATUS LIKE 'auditlog';

如果磁盘可用空间不足，请按照无停机时间修改主键的说明进行操作。
还有其他选项可供选择：

增加sort_buffer_size MySQL参数可能有助于手动重建索引时减少磁盘空间的使用。
但是，修改此变量可能会影响数据库的整体memory使用情况。
考虑通过删除可能不必要的数据来释放空间。
在执行Housekeeper之前，可以考虑减少数据存储周期概述参数。

修改主键并停机

1。删除当前的 auditlog 表主键并添加新的主键。

ALTER TABLE auditlog DROP PRIMARY KEY, ADD PRIMARY KEY (auditid, clock);

2。重建索引（可选但强烈建议，参见有关重建索引的重要说明）。

OPTIMIZE TABLE auditlog;

无停机时间修改主键

修改主键的手动方法在此处描述。另外，您可以使用 Percona 提供的 pt-online-schema-change 工具包。该工具包可以自动执行以下操作，同时最小化用于修改 auditlog 表所需的空间。

1. 创建一个具有新主键和 create 索引的新表。

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. 交换表。

RENAME TABLE auditlog TO auditlog_old, auditlog_new TO auditlog;

3. 将数据从旧表复制到新表。

INSERT INTO auditlog SELECT * FROM auditlog_old;

可以分块完成此操作（根据需要使用多个带有 WHERE clock 子句的 INSERT INTO 语句），以避免过度使用资源。

4. 删除旧表。

DROP TABLE auditlog_old;

PostgreSQL

重建索引的重要注意事项

在 ALTER TABLE 操作期间，PostgreSQL 会自动重建主键的索引。
但是，建议同时使用 REINDEX TABLE CONCURRENTLY 语句手动重建索引，以确保数据库性能处于最佳状态。

重建索引可能会暂时需要多达当前索引所占用磁盘空间三倍的存储空间。
要获取索引的当前大小，您可以 execute 以下 query：

SELECT pg_size_pretty(pg_indexes_size('auditlog'));

如果可用磁盘空间有限，可参考 Altering primary key without downtime 中的说明进行操作。
此外，还有其他可选方案：

增加 PostgreSQL 参数 maintenance_work_mem 可能有助于手动重建索引时减少磁盘空间的使用。
但是，修改此变量可能会影响数据库的整体 memory 使用情况。
如果您有其他具有更多可用空间的磁盘或表空间，可以考虑更改索引重建的临时存储位置。
您可以设置 PostgreSQL 参数 temp_tablespaces，以指定一个不同的表空间用于临时 objects。
考虑删除可能不必要的数据以释放空间。
在执行清理器（housekeeper）之前，考虑减少 Data storage period 概述参数的值。

修改主键并停机

1。删除当前的 auditlog 表主键并添加新的主键。

ALTER TABLE auditlog DROP CONSTRAINT auditlog_pkey;
       ALTER TABLE auditlog ADD PRIMARY KEY (auditid,clock);

2。重建索引（可选但强烈建议，请参阅有关重建索引的重要说明)。

REINDEX TABLE CONCURRENTLY auditlog;

无停机时间修改主键

手动修改主键的方法在此处描述。
或者，可以考虑使用 pg_repack 扩展来创建新表、复制数据以及交换表。

1. 创建具有新主键和 create 索引的新表。

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. 交换表。

ALTER TABLE auditlog RENAME TO auditlog_old;
       ALTER TABLE auditlog_new RENAME TO auditlog;

3. 将数据从旧表复制到新表。

INSERT INTO auditlog SELECT * FROM auditlog_old;

可以分批次完成此操作（根据需要使用多个带有 WHERE clock 子句的 INSERT INTO 语句），以避免过度使用资源。

4. 删除旧表。

DROP TABLE auditlog_old;

Oracle

重建索引的重要注意事项

Oracle 在执行 ALTER TABLE 操作期间会自动重建主键的索引。
但是，建议同时使用 ALTER INDEX <index> REBUILD PARALLEL 语句手动重建索引，以确保数据库性能最优。

重建索引可能会临时占用大量磁盘空间。

如果磁盘空间有限，请参考 Altering primary key without downtime 的说明进行操作。
此外还可以考虑以下选项：

增加 SORT_AREA_SIZE Oracle 参数可能有助于在手动重建索引时减少磁盘空间的使用。
但是，修改此变量将影响数据库的整体 memory 使用情况。
您可以使用 PARALLEL 子句设置并行度，例如： ALTER INDEX auditlog_1 REBUILD PARALLEL 4
考虑通过删除可能不必要的数据来释放空间。
在执行 housekeeper 之前，可以考虑减少 Data storage period 概述参数。

修改主键并停机

1。获取约束名称。

SELECT CONSTRAINT_NAME FROM all_constraints WHERE TABLE_NAME = 'AUDITLOG' AND CONSTRAINT_TYPE = 'P';

2。删除当前的 auditlog 表主键并添加新的主键。

ALTER TABLE auditlog DROP CONSTRAINT <constraint_name>;
       ALTER TABLE auditlog ADD CONSTRAINT auditlog_pk PRIMARY KEY (auditid, clock);

3。重建索引（可选但强烈推荐，请参阅 [有关重建索引的重要说明](#important-notes-on-rebuilding-indexes-2）。

3.1。获取索引名称。

SELECT index_name FROM user_indexes WHERE table_name='AUDITLOG';

3.2。重建每个索引。

ALTER INDEX auditlog_pk REBUILD PARALLEL;
       ALTER INDEX auditlog_1 REBUILD PARALLEL;
       ALTER INDEX auditlog_2 REBUILD PARALLEL;
       ALTER INDEX auditlog_3 REBUILD PARALLEL;

无停机时间修改主键

1。使用新的主键和create索引创建一个新表。

CREATE TABLE auditlog_new (
         auditid              nvarchar2(25)                             ,
         userid               number(20)                                NULL,
         username             nvarchar2(100)  DEFAULT ''                ,
         clock                number(10)      DEFAULT '0'               NOT NULL,
         ip                   nvarchar2(39)   DEFAULT ''                ,
         action               number(10)      DEFAULT '0'               NOT NULL,
         resourcetype         number(10)      DEFAULT '0'               NOT NULL,
         resourceid           number(20)                                NULL,
         resource_cuid        nvarchar2(25)                             ,
         resourcename         nvarchar2(255)  DEFAULT ''                ,
         recordsetid          nvarchar2(25)                             ,
         details              nclob           DEFAULT ''                ,
         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。交换表。

ALTER TABLE auditlog RENAME TO auditlog_old;
       ALTER TABLE auditlog_new RENAME TO auditlog;

3。将数据从旧表复制到新表。

INSERT INTO auditlog SELECT * FROM auditlog_old;

可以分块完成（根据需要使用多个包含WHERE clock子句的INSERT INTO语句），以避免过度的资源使用。

4。删除旧表。

DROP TABLE auditlog_old;

另请参阅

Database upgrade to primary keys

Documentation

4 为分区准备审计日志表

概述

MySQL

重建索引的重要注意事项

修改主键并停机

无停机时间修改主键

PostgreSQL

重建索引的重要注意事项

修改主键并停机

无停机时间修改主键

Oracle

重建索引的重要注意事项

修改主键并停机

无停机时间修改主键

另请参阅