4 Preparando la tabla auditlog para particionado

Descripción general

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

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

La partición de la tabla auditlog puede mejorar, por ejemplo, el mantenimiento en instalaciones grandes. Aunque actualmente el mantenimiento de Zabbix no puede aprovechar las tablas particionadas (excepto para TimescaleDB), puede desactivar el mantenimiento de Zabbix y eliminar particiones utilizando scripts.

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

MySQL

Notas importantes sobre la reconstrucción de índices

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

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

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

Si el espacio disponible en disco es una preocupación, siga las instrucciones de Modificar la clave primaria sin tiempo de inactividad. También hay otras opciones disponibles:

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

Modificar la clave primaria con tiempo de inactividad

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

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

2. Reconstruya los índices (opcional pero altamente recomendado, consulte Notas importantes sobre la reconstrucción de índices).

OPTIMIZE TABLE auditlog;

Modificar la clave primaria sin tiempo de inactividad

El método manual para modificar la clave primaria se describe aquí. Como alternativa, puede utilizar la herramienta pt-online-schema-change de Percona. Esta herramienta realiza las siguientes acciones automáticamente, minimizando también el espacio utilizado para modificar la tabla auditlog.

1. Crear una nueva tabla con la nueva clave primaria y crear los í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. Intercambiar las tablas.

RENAME TABLE auditlog TO auditlog_old, auditlog_new TO auditlog;

3. Copiar los datos de la tabla antigua a la nueva.

INSERT INTO auditlog SELECT * FROM auditlog_old;

Esto se puede hacer por partes (varias sentencias INSERT INTO con cláusulas WHERE clock según sea necesario) para evitar un uso excesivo de recursos.

4. Eliminar la tabla antigua.

DROP TABLE auditlog_old;

PostgreSQL

Notas importantes sobre la reconstrucción de índices

PostgreSQL reconstruye automáticamente los índices para la clave primaria durante la operación ALTER TABLE. Sin embargo, se recomienda encarecidamente reconstruir manualmente los índices con la sentencia 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 actualmente utilizan 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 el espacio disponible en disco es una preocupación, siga las instrucciones de Modificar la clave primaria sin tiempo de inactividad. También hay otras opciones disponibles:

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

Modificar la clave primaria con tiempo de inactividad

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

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

2. Reconstruya los índices (opcional pero altamente recomendado, consulte Notas importantes sobre la reconstrucción de índices).

REINDEX TABLE CONCURRENTLY auditlog;

Modificar la clave primaria sin tiempo de inactividad

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

1. Crear una nueva tabla con la nueva clave primaria y crear los í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 las tablas.

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

3. Copiar los datos de la tabla antigua a la nueva.

INSERT INTO auditlog SELECT * FROM auditlog_old;

Esto se puede hacer por partes (varias sentencias INSERT INTO con cláusulas WHERE clock según sea necesario) para evitar un uso excesivo de recursos.

4. Eliminar la tabla antigua.

DROP TABLE auditlog_old;

Véase también

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