4 監査ログテーブルのパーティション化の準備

概要

一部のデータベース（MySQL など）では、パーティション列をテーブルの一意制約の一部にする必要があります。 したがって、auditlog テーブルを時間でパーティション分割するには、主キーを auditid から複合キー auditid + clock に変更する必要があります。

このセクションでは、auditlog テーブルの主キーを変更する手順について説明します。

auditlog テーブルをパーティション分割すると、例えば大規模なシステムにおけるハウスキーピング機能の向上につながります。 Zabbix の housekeeping は現在、パーティション分割されたテーブルを利用できません（TimescaleDB を除く）が、スクリプトを使用して Zabbix ハウスキーピングを無効化し、パーティションを削除することができます。

Zabbix 7.0 以降、TimescaleDB の auditlog テーブルはハイパーテーブルに変換され、ハウスキーパーがデータをチャンク単位で削除できるようになりました。 既存の auditlog テーブルをハイパーテーブルにアップグレードするには、TimescaleDB スキーマのアップグレード を参照してください。

MySQL

Important notes on rebuilding indexes

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 statement 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 following statements:

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

If the available disk space is a concern, follow the Altering primary key without downtime instructions. Other options are also available:

• Increasing the 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.
• Consider freeing up space by deleting potentially unnecessary data.
• Consider decreasing the Data storage period housekeeper parameter before executing the housekeeper.

ダウンタイムを伴う主キーの変更

1. 現在の auditlog テーブルの主キーを削除し、新しい主キーを追加します。

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

2. インデックスを再構築します（オプション設定ですが、強く推奨します。インデックスの再構築に関する重要な注意事項を参照してください）

OPTIMIZE TABLE Auditlog;

Altering primary key without downtime

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.

RENAME TABLE auditlog TO auditlog_old, auditlog_new TO auditlog;

3. Copy data from the old table to the new table.

INSERT INTO auditlog SELECT * FROM auditlog_old;

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.

DROP TABLE auditlog_old;

PostgreSQL

Important notes on rebuilding indexes

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 statement 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 following query:

SELECT pg_size_pretty(pg_indexes_size('auditlog'));

If the available disk space is a concern, follow the Altering primary key without downtime instructions. Other options are also available:

• Increasing the 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.
• If you have another disk or tablespace with more available space, you might consider changing the temporary storage location for the index rebuild. You can set the temp_tablespaces PostgreSQL parameter to specify a different tablespace for temporary objects.
• Consider freeing up space by deleting potentially unnecessary data.
• Consider decreasing the Data storage period housekeeper parameter before executing the housekeeper.

ダウンタイムを伴う主キーの変更

1. 現在の auditlog テーブルの主キーを削除し、新しい主キーを追加します。

ALTER TABLE Auditlog DROP CONSTRAINT Auditlog_pkey;
       ALTER TABLE Auditlog ADD PRIMARY KEY (auditid,clock);

2. インデックスを再構築します（オプション作業ですが、強く推奨します。インデックスの再構築に関する重要な注意事項を参照してください）

REINDEX TABLE CONCURRENTLY Auditlog;

Altering primary key without downtime

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.

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

3. Copy data from the old table to the new table.

INSERT INTO auditlog SELECT * FROM auditlog_old;

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.

DROP TABLE auditlog_old;

参照

• データベースの主キーと倍精度データ型へのアップグレード