4 Preparando la tabla de registro de auditoría para particionado

Descripción general

Algunas bases de datos (por ejemplo, MySQL) requieren que la columna de partición sea parte de la "constraint" única de la tabla. Por lo tanto, para particionar la tabla auditlog por tiempo, la clave principal debe cambiarse de auditid a una clave compuesta auditid + clock.

Esta sección proporciona instrucciones para modificar la clave principal de la tabla "auditlog".

Particionar la tabla auditlog puede mejorar, por ejemplo, el mantenimiento en configuraciones grandes. Aunque actualmente la limpieza de Zabbix no puede aprovechar las tablas particionadas (excepto para TimescaleDB), puede deshabilitar el mantenimiento de Zabbix y eliminar particiones mediante scripts.

Desde Zabbix 7.0, la tabla auditlog para TimescaleDB se ha convertido en una hipertabla, lo que permite al encargado de mantenimiento eliminar datos en fragmentos. Para actualizar la tabla auditlog existente a una hipertabla, consulte Actualización del esquema de TimescaleDB.

MySQL

Notas importantes sobre la reconstrucción de índices

MySQL reconstruye automáticamente los índices de la clave principal durante la operación "ALTER TABLE". Sin embargo, se recomienda encarecidamente reconstruir también los índices manualmente con la instrucción "OPTIMIZE TABLE" para garantizar un rendimiento óptimo de la base de datos.

La reconstrucción de índices puede requerir temporalmente tanto espacio en disco adicional como el que utiliza la propia tabla. Para obtener el tamaño actual de datos e índices, puede ejecutar las siguientes declaraciones:

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

Si le preocupa el espacio disponible en disco, siga las instrucciones Alteración de la clave principal sin tiempo de inactividad. Otras opciones también están disponibles:

• Aumentar el parámetro MySQL sort_buffer_size puede ayudar a reducir el uso de espacio en disco al reconstruir índices manualmente. Sin embargo, modificar esta variable puede afectar el uso general de la memoria de la base de datos.
• Considere la posibilidad de liberar espacio eliminando datos potencialmente innecesarios.
• Considere disminuir el parámetro Período de almacenamiento de datos housekeeper antes de ejecutar el housekeeper.

Alteración de la clave principal con tiempo de inactividad

1. Elimine la clave principal de la tabla auditlog actual y agregue la nueva clave principal.

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

2. Reconstruir índices (opcional pero muy recomendado, consulte Notas importantes sobre la reconstrucción de índices).

OPTIMIZE TABLE auditlog;

Modificación de la clave principal sin tiempo de inactividad

El método manual para modificar la clave principal se describe aquí. Alternativamente, puede utilizar el kit de herramientas pt-online-schema-change de Percona. Este kit de herramientas realiza las siguientes acciones automáticamente y al mismo tiempo minimiza el espacio utilizado para modificar la tabla "auditlog".

1. Cree una nueva tabla con la nueva clave principal y cree índices.

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. Intercambie las tablas.

RENAME TABLE auditlog TO auditlog_old, auditlog_new TO auditlog;

3. Copie los datos de la tabla anterior a la tabla nueva.

INSERT INTO auditlog SELECT * FROM auditlog_old;

Esto se puede hacer en fragmentos (múltiples declaraciones INSERT INTO con cláusulas WHERE clock según sea necesario) para evitar el uso excesivo de recursos.

4. Elimine la antigua tabla.

DROP TABLE auditlog_old;

PostgreSQL

Notas importantes sobre la reconstrucción de índices

PostgreSQL reconstruye automáticamente los índices de la clave principal durante la operación "ALTER TABLE". Sin embargo, se recomienda encarecidamente reconstruir también los índices manualmente con la instrucción REINDEX TABLE CONCURRENTLY para garantizar un rendimiento óptimo de la base de datos.

La reconstrucción de índices puede requerir temporalmente hasta tres veces el espacio en disco que utilizan actualmente los índices. Para obtener el tamaño actual de los índices, puede ejecutar la siguiente consulta:

SELECT pg_size_pretty(pg_indexes_size('auditlog'));

Si le preocupa el espacio disponible en disco, siga las instrucciones Alteración de la clave principal sin tiempo de inactividad. Otras opciones también están disponibles:

• Aumentar el parámetro PostgreSQL maintenance_work_mem puede ayudar a reducir el uso de espacio en disco cuando se realiza manualmente reconstruir índices. Sin embargo, modificar esta variable puede afectar el uso general de la memoria de la base de datos.
• Si tiene otro disco o espacio de tabla con más espacio disponible, podría considerar cambiar la ubicación de almacenamiento temporal para la reconstrucción del índice. Puede configurar el parámetro temp_tablespaces de PostgreSQL para especificar un espacio de tabla diferente para objetos temporales.
• Considere la posibilidad de liberar espacio eliminando datos potencialmente innecesarios.
• Considere disminuir el parámetro Período de almacenamiento de datos housekeeper antes de ejecutar el housekeeper.

Alteración de la clave principal con tiempo de inactividad

1. Elimine la clave principal de la tabla auditlog actual y agregue la nueva clave principal.

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

2. Reconstruya índices (opcional pero muy recomendable; consulte Notas importantes sobre la reconstrucción de índices).

REINDEX TABLE CONCURRENTLY auditlog;

Modificación de la clave principal sin tiempo de inactividad

El método manual para modificar la clave principal se describe aquí. Alternativamente, se puede considerar la extensión pg_repack para crear una nueva tabla, copiar datos e intercambiar tablas.

1. Cree una nueva tabla con la nueva clave principal y cree índices.

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. Intercambiar tablas.

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

3. Copie los datos de la tabla anterior a la tabla nueva.

INSERT INTO auditlog SELECT * FROM auditlog_old;

Esto se puede hacer en fragmentos (múltiples declaraciones INSERT INTO con cláusulas WHERE clock según sea necesario) para evitar el uso excesivo de recursos.

4. Elimine la antigua tabla.

DROP TABLE auditlog_old;

Ver también

• Actualización de la base de datos a claves primarias