4 Preparazione della tabella auditlog per il partizionamento

Panoramica

Alcuni database (ad esempio MySQL) richiedono che la colonna di partizionamento faccia parte del vincolo univoco della tabella. Pertanto, per partizionare la tabella auditlog in base al tempo, la chiave primaria deve essere modificata da auditid a una chiave composta auditid + clock.

Questa sezione fornisce istruzioni per modificare la chiave primaria della tabella auditlog.

Le istruzioni fornite in questa pagina sono pensate per utenti avanzati. Si noti che queste istruzioni potrebbero dover essere adattate alla configurazione specifica in uso. La modifica della chiave primaria può inoltre essere incompatibile con future patch di aggiornamento, quindi potrebbe essere necessario gestire manualmente i futuri aggiornamenti.

La modifica della chiave primaria può essere un'operazione che richiede molte risorse e molto tempo, a seconda delle dimensioni della tabella auditlog. Si consiglia di arrestare Zabbix server e di impostare Zabbix frontend in modalità di manutenzione per la durata della modifica. Tuttavia, se strettamente necessario, esiste un modo per modificare la chiave primaria senza tempi di inattività (vedere sotto).

Il partizionamento della tabella auditlog può migliorare, ad esempio, l'housekeeping in configurazioni di grandi dimensioni. Sebbene l'housekeeping di Zabbix attualmente non possa sfruttare le tabelle partizionate (tranne che per TimescaleDB), è possibile disabilitare l'housekeeping di Zabbix ed eliminare le partizioni utilizzando script.

A partire da Zabbix 7.0, la tabella auditlog per TimescaleDB è stata convertita in una hypertable, il che consente al processo di housekeeping di eliminare i dati per chunk. Per aggiornare la tabella auditlog esistente a una hypertable, vedere Aggiornamento dello schema TimescaleDB.

MySQL

Note importanti sulla ricostruzione degli indici

MySQL ricostruisce automaticamente gli indici per la chiave primaria durante l'operazione ALTER TABLE. Tuttavia, è fortemente consigliato ricostruire manualmente anche gli indici con l'istruzione OPTIMIZE TABLE per garantire prestazioni ottimali del database.

La ricostruzione degli indici può richiedere temporaneamente una quantità di spazio su disco aggiuntivo pari a quella utilizzata dalla tabella stessa. Per ottenere la dimensione corrente dei dati e degli indici, è possibile eseguire le seguenti istruzioni:

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

Se lo spazio disponibile su disco è un problema, seguire le istruzioni in Modifica della chiave primaria senza tempi di inattività. Sono disponibili anche altre opzioni:

  • L'aumento del parametro MySQL sort_buffer_size può aiutare a ridurre l'utilizzo dello spazio su disco durante la ricostruzione manuale degli indici. Tuttavia, la modifica di questa variabile può influire sull'utilizzo complessivo della memoria del database.
  • Valutare la possibilità di liberare spazio eliminando dati potenzialmente non necessari.
  • Valutare la possibilità di ridurre il parametro Periodo di conservazione dei dati di housekeeper prima di eseguire housekeeper.
Modifica della chiave primaria con downtime

1. Eliminare la chiave primaria corrente della tabella auditlog e aggiungere la nuova chiave primaria.

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

2. Ricostruire gli indici (opzionale ma fortemente consigliato, vedere Note importanti sulla ricostruzione degli indici).

OPTIMIZE TABLE auditlog;
Modifica della chiave primaria senza tempi di inattività

Il metodo manuale per modificare la chiave primaria è descritto qui. In alternativa, è possibile utilizzare il toolkit pt-online-schema-change di Percona. Questo toolkit esegue automaticamente le seguenti azioni, riducendo al minimo anche lo spazio utilizzato per modificare la tabella auditlog.

1. Creare una nuova tabella con la nuova chiave primaria e creare gli indici.

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. Scambiare le tabelle.

RENAME TABLE auditlog TO auditlog_old, auditlog_new TO auditlog;

3. Copiare i dati dalla vecchia tabella a quella nuova.

INSERT INTO auditlog SELECT * FROM auditlog_old;

Questa operazione può essere eseguita in blocchi (più istruzioni INSERT INTO con clausole WHERE clock secondo necessità) per evitare un utilizzo eccessivo delle risorse.

4. Eliminare la vecchia tabella.

DROP TABLE auditlog_old;

PostgreSQL

Note importanti sulla ricostruzione degli indici

PostgreSQL ricostruisce automaticamente gli indici per la chiave primaria durante l'operazione ALTER TABLE. Tuttavia, è fortemente consigliato ricostruire manualmente anche gli indici con l'istruzione REINDEX TABLE CONCURRENTLY per garantire prestazioni ottimali del database.

La ricostruzione degli indici può richiedere temporaneamente fino a tre volte lo spazio su disco attualmente utilizzato dagli indici. Per ottenere la dimensione attuale degli indici, è possibile eseguire la seguente query:

SELECT pg_size_pretty(pg_indexes_size('auditlog'));

Se lo spazio disponibile su disco è un problema, seguire le istruzioni in Modifica della chiave primaria senza downtime. Sono disponibili anche altre opzioni:

  • Aumentare il parametro PostgreSQL maintenance_work_mem può aiutare a ridurre l'utilizzo di spazio su disco durante la ricostruzione manuale degli indici. Tuttavia, la modifica di questa variabile può influire sull'utilizzo complessivo della memoria del database.
  • Se si dispone di un altro disco o tablespace con più spazio disponibile, si può valutare di modificare la posizione di archiviazione temporanea per la ricostruzione degli indici. È possibile impostare il parametro PostgreSQL temp_tablespaces per specificare un tablespace diverso per gli oggetti temporanei.
  • Valutare la possibilità di liberare spazio eliminando dati potenzialmente non necessari.
  • Valutare la possibilità di ridurre il parametro Periodo di conservazione dei dati di housekeeper prima di eseguire housekeeper.
Modifica della chiave primaria con downtime

1. Eliminare l'attuale chiave primaria della tabella auditlog e aggiungere la nuova chiave primaria.

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

2. Ricostruire gli indici (facoltativo ma fortemente consigliato, vedere Note importanti sulla ricostruzione degli indici).

REINDEX TABLE CONCURRENTLY auditlog;
Modifica della chiave primaria senza tempi di inattività

Il metodo manuale per modificare la chiave primaria è descritto qui. In alternativa, si può prendere in considerazione l'estensione pg_repack per creare una nuova tabella, copiare i dati e scambiare le tabelle.

1. Creare una nuova tabella con la nuova chiave primaria e creare gli indici.

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. Scambiare le tabelle.

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

3. Copiare i dati dalla vecchia tabella a quella nuova.

INSERT INTO auditlog SELECT * FROM auditlog_old;

Questa operazione può essere eseguita in blocchi (più istruzioni INSERT INTO con clausole WHERE clock secondo necessità) per evitare un utilizzo eccessivo delle risorse.

4. Eliminare la vecchia tabella.

DROP TABLE auditlog_old;

Vedi anche