4 Préparation de la table d'auditlog pour le partitionnement

Présentation

Certaines bases de données (par exemple, MySQL) nécessitent que la colonne de partitionnement fasse partie de la contrainte unique de la table. Par conséquent, pour partitionner la table auditlog par heure, la clé primaire doit être modifiée de auditid à une clé composite auditid + clock.

Cette section fournit des instructions pour modifier la clé primaire de la table auditlog.

Les instructions fournies sur cette page sont destinées aux utilisateurs avancés. Notez que ces instructions peuvent devoir être ajustées en fonction de votre configuration spécifique. La modification de la clé primaire peut également être incompatible avec les futurs correctifs de mise à niveau, il peut donc être nécessaire de gérer manuellement les futures mises à niveau.

La modification de la clé primaire peut être une opération gourmande en ressources qui prend beaucoup de temps en fonction de la taille de la table auditlog. Il est recommandé d'arrêter le serveur Zabbix et de basculer l'interface Zabbix en mode maintenance pendant la durée de la modification. Cependant, si cela est absolument nécessaire, il existe un moyen de modifier la clé primaire sans interruption de service (voir ci-dessous).

Le partitionnement de la table auditlog peut améliorer, par exemple, la gestion interne dans les grandes configurations. Bien que Zabbix gestion interne ne puisse actuellement pas tirer parti des tables partitionnées (à l'exception de TimescaleDB), vous pouvez désactiver la gestion interne Zabbix et supprimer des partitions à l'aide de scripts.

Depuis Zabbix 7.0, la table auditlog de TimescaleDB a été convertie en hypertable, ce qui permet au gestionnaire de supprimer des données par blocs. Pour mettre à niveau la table auditlog existante vers une hypertable, voir Mise à niveau du schéma TimescaleDB.

MySQL

Remarques importantes sur la reconstruction des index

MySQL reconstruit automatiquement les index de la clé primaire pendant l’opération ALTER TABLE. Cependant, il est fortement recommandé de reconstruire également manuellement les index à l’aide de l’instruction OPTIMIZE TABLE afin de garantir des performances optimales de la base de données.

La reconstruction des index peut temporairement nécessiter autant d’espace disque supplémentaire que la table elle-même en utilise. Pour obtenir la taille actuelle des données et des index, vous pouvez exécuter les instructions suivantes :

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

Si l’espace disque disponible est une préoccupation, suivez les instructions de Modification de la clé primaire sans interruption. D’autres options sont également disponibles :

  • Augmenter le paramètre MySQL sort_buffer_size peut aider à réduire l’utilisation de l’espace disque lors de la reconstruction manuelle des index. Cependant, la modification de cette variable peut avoir un impact sur l’utilisation globale de la mémoire de la base de données.
  • Envisagez de libérer de l’espace en supprimant des données potentiellement inutiles.
  • Envisagez de diminuer le paramètre Période de conservation des données de housekeeper avant d’exécuter le housekeeper.
Modification de la clé primaire avec interruption de service

1. Supprimez la clé primaire actuelle de la table auditlog et ajoutez la nouvelle clé primaire.

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

2. Reconstruisez les index (facultatif mais fortement recommandé, voir Remarques importantes sur la reconstruction des index).

OPTIMIZE TABLE auditlog;
Modification de la clé primaire sans interruption de service

La méthode manuelle de modification de la clé primaire est décrite ici. Vous pouvez également utiliser l’outil pt-online-schema-change de Percona. Cet outil effectue automatiquement les actions suivantes, tout en minimisant l’espace utilisé pour modifier la table auditlog.

1. Créer une nouvelle table avec la nouvelle clé primaire et créer les index.

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. Permuter les tables.

RENAME TABLE auditlog TO auditlog_old, auditlog_new TO auditlog;

3. Copier les données de l’ancienne table vers la nouvelle table.

INSERT INTO auditlog SELECT * FROM auditlog_old;

Cela peut être fait par blocs (plusieurs instructions INSERT INTO avec des clauses WHERE clock selon les besoins) afin d’éviter une utilisation excessive des ressources.

4. Supprimer l’ancienne table.

DROP TABLE auditlog_old;

PostgreSQL

Remarques importantes sur la reconstruction des index

PostgreSQL reconstruit automatiquement les index de la clé primaire pendant l’opération ALTER TABLE. Cependant, il est fortement recommandé de reconstruire également manuellement les index avec l’instruction REINDEX TABLE CONCURRENTLY afin de garantir des performances optimales de la base de données.

La reconstruction des index peut temporairement nécessiter jusqu’à trois fois l’espace disque actuellement utilisé par les index. Pour obtenir la taille actuelle des index, vous pouvez exécuter la requête suivante :

SELECT pg_size_pretty(pg_indexes_size('auditlog'));

Si l’espace disque disponible est un sujet de préoccupation, suivez les instructions de Modification de la clé primaire sans interruption de service. D’autres options sont également disponibles :

  • Augmenter le paramètre PostgreSQL maintenance_work_mem peut aider à réduire l’utilisation de l’espace disque lors de la reconstruction manuelle des index. Cependant, la modification de cette variable peut avoir un impact sur l’utilisation globale de la mémoire de la base de données.
  • Si vous disposez d’un autre disque ou tablespace avec davantage d’espace disponible, vous pouvez envisager de modifier l’emplacement de stockage temporaire pour la reconstruction des index. Vous pouvez définir le paramètre PostgreSQL temp_tablespaces afin de spécifier un autre tablespace pour les objets temporaires.
  • Envisagez de libérer de l’espace en supprimant des données potentiellement inutiles.
  • Envisagez de réduire le paramètre Période de conservation des données de housekeeper avant d’exécuter le housekeeper.
Modification de la clé primaire avec interruption de service

1. Supprimez la clé primaire actuelle de la table auditlog et ajoutez la nouvelle clé primaire.

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

2. Reconstruisez les index (facultatif mais fortement recommandé, voir Remarques importantes sur la reconstruction des index).

REINDEX TABLE CONCURRENTLY auditlog;
Modification de la clé primaire sans interruption de service

La méthode manuelle de modification de la clé primaire est décrite ici. Comme alternative, l’extension pg_repack peut être envisagée pour créer une nouvelle table, copier les données et permuter les tables.

1. Créez une nouvelle table avec la nouvelle clé primaire et créez les index.

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. Permutez les tables.

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

3. Copiez les données de l’ancienne table vers la nouvelle table.

INSERT INTO auditlog SELECT * FROM auditlog_old;

Cela peut être effectué par lots (plusieurs instructions INSERT INTO avec des clauses WHERE clock selon les besoins) afin d’éviter une utilisation excessive des ressources.

4. Supprimez l’ancienne table.

DROP TABLE auditlog_old;

Voir aussi