4 Подготовка таблицы auditlog к секционированию

Обзор

Некоторые базы данных (например, MySQL) требуют, чтобы столбец секционирования был частью уникального ограничения таблицы. Поэтому, чтобы секционировать таблицу auditlog по времени, первичный ключ необходимо изменить с auditid на составной ключ auditid + clock.

В этом разделе приведены инструкции по изменению первичного ключа таблицы auditlog.

Инструкции, приведённые на этой странице, предназначены для опытных пользователей. Обратите внимание, что эти инструкции, возможно, потребуется скорректировать в соответствии с вашей конкретной конфигурацией. Изменение первичного ключа также может быть несовместимо с будущими патчами обновления, поэтому в дальнейшем может потребоваться выполнять обновления вручную.

Изменение первичного ключа может быть ресурсоёмкой операцией, которая занимает много времени в зависимости от размера таблицы auditlog. На время выполнения изменения рекомендуется остановить сервер Zabbix и перевести веб-интерфейс Zabbix в режим обслуживания. Однако при крайней необходимости существует способ изменить первичный ключ без простоя (см. ниже).

Секционирование таблицы auditlog может улучшить, например, очистку истории в крупных установках. Хотя очистка истории Zabbix в настоящее время не может использовать преимущества секционированных таблиц (за исключением TimescaleDB), вы можете отключить очистку истории Zabbix и удалять секции с помощью скриптов.

Начиная с Zabbix 7.0, таблица auditlog для TimescaleDB была преобразована в гипертаблицу, что позволяет процессу очистки истории удалять данные по чанкам. Чтобы обновить существующую таблицу auditlog до гипертаблицы, см. Обновление схемы TimescaleDB.

MySQL

Важные замечания по перестроению индексов

MySQL автоматически перестраивает индексы для первичного ключа во время операции ALTER TABLE. Однако настоятельно рекомендуется также вручную перестроить индексы с помощью инструкции 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;
Изменение первичного ключа без простоя

Ручной метод изменения первичного ключа описан здесь. В качестве альтернативы можно использовать набор инструментов pt-online-schema-change от Percona. Этот набор инструментов автоматически выполняет следующие действия, а также минимизирует объем пространства, используемого для изменения таблицы 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;

Это можно сделать по частям (несколькими инструкциями INSERT INTO с условиями WHERE clock по мере необходимости), чтобы избежать чрезмерного использования ресурсов.

4. Удалить старую таблицу.

DROP TABLE auditlog_old;

PostgreSQL

Важные примечания по перестроению индексов

PostgreSQL автоматически перестраивает индексы для первичного ключа во время операции ALTER TABLE. Однако настоятельно рекомендуется также вручную перестроить индексы с помощью инструкции REINDEX TABLE CONCURRENTLY, чтобы обеспечить оптимальную производительность базы данных.

Для перестроения индексов может временно потребоваться до трёхкратного объёма дискового пространства, используемого индексами в настоящий момент. Чтобы получить текущий размер индексов, вы можете выполнить следующий запрос:

SELECT pg_size_pretty(pg_indexes_size('auditlog'));

Если доступное дисковое пространство вызывает опасения, следуйте инструкциям Изменение первичного ключа без простоя. Также доступны и другие варианты:

  • Увеличение параметра PostgreSQL maintenance_work_mem может помочь уменьшить использование дискового пространства при ручном перестроении индексов. Однако изменение этой переменной может повлиять на общее использование памяти базой данных.
  • Если у вас есть другой диск или табличное пространство с большим объёмом доступного места, можно рассмотреть изменение расположения временного хранилища для перестроения индекса. Вы можете задать параметр PostgreSQL temp_tablespaces, чтобы указать другое табличное пространство для временных объектов.
  • Рассмотрите возможность освобождения места путём удаления потенциально ненужных данных.
  • Рассмотрите возможность уменьшения параметра Период хранения данных housekeeper перед выполнением 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;

Это можно сделать по частям (несколькими инструкциями INSERT INTO с условиями WHERE clock по мере необходимости), чтобы избежать чрезмерного использования ресурсов.

4. Удалите старую таблицу.

DROP TABLE auditlog_old;

См. также