4 准备用于分区的审计日志表

概述

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

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

对 auditlog 表进行分区可以改进例如大型部署中的清理工作。

尽管 Zabbix 清理 目前无法利用分区表（TimescaleDB 除外），但您可以禁用 Zabbix 清理，并使用脚本删除分区。

从 Zabbix 7.0 开始，TimescaleDB 的 auditlog 表已转换为 hypertable，这使得清理进程能够按 chunk 删除数据。 要将现有的 auditlog 表升级为 hypertable，请参见升级 TimescaleDB schema。

MySQL

重建索引的重要说明

在 ALTER TABLE 操作期间，MySQL 会自动为主键重建索引。
不过，强烈建议同时使用 OPTIMIZE TABLE 语句手动重建索引，以确保数据库性能达到最佳。

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

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

如果可用磁盘空间是一个问题，请按照无需停机更改主键中的说明进行操作。
还可以考虑以下其他选项：

• 增加 MySQL 参数 sort_buffer_size 可能有助于在手动重建索引时减少磁盘空间使用。
  但是，修改此变量可能会影响数据库的整体内存使用。
• 考虑通过删除可能不必要的数据来释放空间。
• 在执行 housekeeper 之前，考虑降低 数据存储周期 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 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 语句手动重建索引，以确保数据库性能达到最佳。

重建索引时，临时所需的磁盘空间最多可能达到当前索引占用空间的三倍。
要获取索引的当前大小，可以执行以下查询：

SELECT pg_size_pretty(pg_indexes_size('auditlog'));

如果可用磁盘空间是一个问题，请按照无需停机更改主键中的说明进行操作。
还可以使用以下其他选项：

• 增加 PostgreSQL 参数 maintenance_work_mem 可能有助于在手动重建索引时减少磁盘空间使用。
  但是，修改此变量可能会影响数据库的整体内存使用。
• 如果你有另一块磁盘或一个可用空间更多的表空间，可以考虑更改索引重建时的临时存储位置。
  你可以设置 PostgreSQL 参数 temp_tablespaces 来为临时对象指定不同的表空间。
• 考虑通过删除可能不必要的数据来释放空间。
• 在执行 housekeeper 之前，考虑降低 Data storage period housekeeper 参数。

修改主键并停机

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 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;

另请参阅

• 数据库升级到主键