4 Preparant la taula d'auditoria per a la partició

Visió general

Algunes bases de dades (per exemple, MySQL) requereixen que la columna de particions formi part de la restricció única de la taula. Per tant, per particionar la taula auditlog per temps, la clau primària s'ha de canviar de auditid a una clau composta auditid + clock.

Aquesta secció proporciona instruccions per alterar la clau primària de la taula auditlog.

Particionar la taula auditlog pot millorar, per exemple, la neteja en configuracions grans. Tot i que la neteja a Zabbix actualment no pot aprofitar les taules particionades (excepte TimescaleDB), podeu desactivar la neteja Zabbix i esborrar particions mitjançant scripts.

Des de Zabbix 7.0, la taula auditlog de TimescaleDB s'ha convertit en una hipertaula, que permet que la neteja esborri dades per trossos. Per actualitzar la taula auditlog existent a una hipertaula, veieu Actualitzar l'esquema de TimescaleDB.

MySQL

Notes importants sobre la reconstrucció d'índexs

MySQL reconstrueix automàticament els índexs per a la clau primària durant l'operació ALTER TABLE. Tanmateix, és molt recomanable també reconstruir manualment els índexs amb la instrucció OPTIMIZE TABLE per garantir un rendiment òptim de la base de dades.

La reconstrucció d'índexs pot requerir temporalment tant d'espai de disc addicional com la mateixa taula empra. Per obtindre la mida actual de dades i índexs, podeu executar les instruccions següents:

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

Si l'espai disponible en disc és un problema, seguiu les instruccions Alteració de la clau primària sense temps d'inactivitat. També hi ha altres opcions disponibles:

• Augmentar el paràmetre sort_buffer_size MySQL pot ajudar a reduir l'ús d'espai en disc quan es reconstrueixen manualment índexs. Tanmateix, modificar aquesta variable pot afectar l'ús general de la memòria de la base de dades.
• Penseu en alliberar espai esborrant dades potencialment innecessàries.
• Considereu la possibilitat de reduir el paràmetre neteja del Període d'emmagatzematge de dades abans d'executar la neteja.

Alteració de la clau primària amb temps d'inactivitat

1. Deixeu anar la clau primària de la taula auditlog actual i afegiu la nova clau primària.

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

2. Reconstruïu índexs (opcional però molt recomanable, vegeu Notes importants sobre la reconstrucció d'índexs).

OPTIMIZE TABLE auditlog;

Alteració de la clau primària sense temps d'inactivitat

Aquí es descriu el mètode manual per alterar la clau primària. Alternativament, podeu emprar el conjunt d'eines pt-online-schema-change de Percona. Aquest conjunt d'eines realitza les accions següents automàticament, alhora que minimitza l'espai emprat per alterar la taula auditlog.

1. Creeu una taula nova amb la nova clau primària i creeu índexs.

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. Intercanvieu les taules.

RENAME TABLE auditlog TO auditlog_old, auditlog_new TO auditlog;

3. Copieu les dades de la taula antiga a la nova.

INSERT INTO auditlog SELECT * FROM auditlog_old;

Això es pot fer en fragments (múltiples sentències INSERT INTO amb clàusules WHERE clock segons sigui necessari) per evitar un ús excessiu de recursos.

4. Dropeu la taula antiga.

DROP TABLE auditlog_old;

PostgreSQL

Notes importants sobre la reconstrucció d'índexs

PostgreSQL reconstrueix automàticament els índexs per a la clau primària durant l'operació ALTER TABLE. Tanmateix, és molt recomanable també reconstruir manualment els índexs amb la sentència REINDEX TABLE CONCURRENTLY per garantir un rendiment òptim de la base de dades.

La reconstrucció d'índexs pot requerir temporalment fins a tres vegades l'espai de disc emprat actualment pels índexs. Per obtenir la mida actual dels índexs, podeu executar la consulta següent:

SELECT pg_size_pretty(pg_indexes_size('auditlog'));

Si l'espai disponible en disc és un problema, seguiu les instruccions Alteració de la clau primària sense temps d'inactivitat. També hi ha altres opcions disponibles:

• Augmentar el paràmetre PostgreSQL maintenance_work_mem pot ajudar a reduir l'ús d'espai en disc quan es fa manualment reconstruint índexs. Tanmateix, modificar aquesta variable pot afectar l'ús general de la memòria de la base de dades.
• Si teniu un altre disc o espai de taula amb més espai disponible, podeu considerar canviar la ubicació d'emmagatzematge temporal per a la reconstrucció de l'índex. Podeu establir el paràmetre PostgreSQL temp_tablespaces per especificar un espai de taula diferent per als objectes temporals.
• Penseu en alliberar espai esborrant dades potencialment innecessàries.
• Considereu la possibilitat de reduir el paràmetre neteja del Període d'emmagatzematge de dades abans d'executar la neteja.

Alteració de la clau primària amb temps d'inactivitat

1. Deixeu anar la clau primària de la taula auditlog actual i afegiu la nova clau primària.

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

2. Reconstruïu índexs (opcional però molt recomanable, vegeu Notes importants sobre la reconstrucció d'índexs).

REINDEX TABLE CONCURRENTLY auditlog;

Alteració de la clau primària sense temps d'inactivitat

Aquí es descriu el mètode manual per alterar la clau primària. Alternativament, l'extensió pg_repack es pot considerar per crear una taula nova, copiar dades i intercanviar taules.

1. Creeu una taula nova amb la nova clau primària i creeu índexs.

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. Intercanvieu les taules.

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

3. Copieu les dades de la taula antiga a la taula nova.

INSERT INTO auditlog SELECT * FROM auditlog_old;

Això es pot fer en fragments (múltiples sentències INSERT INTO amb clàusules WHERE clock segons sigui necessari) per evitar un ús excessiu de recursos.

4. Dropeu caure la taula antiga.

DROP TABLE auditlog_old;

Veieu també

• Actualització de la base de dades a claus primàries