4 Preparando a tabela auditlog para particionamento

Visão geral

Alguns bancos de dados (por exemplo, MySQL) exigem que a coluna de particionamento faça parte da restrição exclusiva da tabela. Portanto, para particionar a tabela auditlog por tempo, a chave primária deve ser alterada de auditid para uma chave composta auditid + clock.

Esta seção fornece instruções para alterar a chave primária da tabela auditlog.

O particionamento da tabela auditlog pode melhorar, por exemplo, a limpeza em ambientes grandes. Embora o housekeeping do Zabbix atualmente não possa tirar proveito de tabelas particionadas (exceto para TimescaleDB), você pode desabilitar o housekeeping do Zabbix e excluir partições usando scripts.

Desde o Zabbix 7.0, a tabela auditlog para TimescaleDB foi convertida em uma hypertable, o que permite que o housekeeper exclua dados por chunks. Para atualizar a tabela auditlog existente para uma hypertable, consulte Atualizando o schema do TimescaleDB.

MySQL

Notas importantes sobre a reconstrução de índices

O MySQL reconstrói automaticamente os índices para a chave primária durante a operação ALTER TABLE. No entanto, é altamente recomendável também reconstruir manualmente os índices com a instrução OPTIMIZE TABLE para garantir o desempenho ideal do banco de dados.

A reconstrução de índices pode exigir temporariamente tanto espaço em disco adicional quanto a própria tabela utiliza. Para obter o tamanho atual dos dados e dos índices, você pode executar as seguintes instruções:

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

Se o espaço disponível em disco for uma preocupação, siga as instruções de Alterando a chave primária sem tempo de inatividade. Outras opções também estão disponíveis:

• Aumentar o parâmetro do MySQL sort_buffer_size pode ajudar a reduzir o uso de espaço em disco ao reconstruir manualmente os índices. No entanto, modificar esta variável pode impactar o uso geral de memória do banco de dados.
• Considere liberar espaço excluindo dados potencialmente desnecessários.
• Considere diminuir o parâmetro Período de armazenamento de dados do housekeeper antes de executar o housekeeper.

Alterando a chave primária com downtime

1. Remova a chave primária atual da tabela auditlog e adicione a nova chave primária.

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

2. Reconstrua os índices (opcional, mas altamente recomendado, veja Notas importantes sobre reconstrução de índices).

OPTIMIZE TABLE auditlog;

Alterando a chave primária sem tempo de inatividade

O método manual de alteração da chave primária está descrito aqui. Como alternativa, você pode usar o pt-online-schema-change da Percona. Essa ferramenta executa as seguintes ações automaticamente, minimizando também o espaço utilizado para alterar a tabela auditlog.

1. Crie uma nova tabela com a nova chave primária e crie os í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. Troque as tabelas.

RENAME TABLE auditlog TO auditlog_old, auditlog_new TO auditlog;

3. Copie os dados da tabela antiga para a nova tabela.

INSERT INTO auditlog SELECT * FROM auditlog_old;

Isso pode ser feito em partes (várias instruções INSERT INTO com cláusulas WHERE clock conforme necessário) para evitar uso excessivo de recursos.

4. Exclua a tabela antiga.

DROP TABLE auditlog_old;

PostgreSQL

Notas importantes sobre a reconstrução de índices

O PostgreSQL reconstrói automaticamente os índices para a chave primária durante a operação ALTER TABLE. No entanto, é altamente recomendável também reconstruir manualmente os índices com a instrução REINDEX TABLE CONCURRENTLY para garantir o desempenho ideal do banco de dados.

A reconstrução de índices pode exigir temporariamente até três vezes o espaço em disco atualmente usado pelos índices. Para obter o tamanho atual dos índices, você pode executar a seguinte consulta:

SELECT pg_size_pretty(pg_indexes_size('auditlog'));

Se o espaço disponível em disco for uma preocupação, siga as instruções de Alterando a chave primária sem tempo de inatividade. Outras opções também estão disponíveis:

• Aumentar o parâmetro do PostgreSQL maintenance_work_mem pode ajudar a reduzir o uso de espaço em disco ao reconstruir manualmente os índices. No entanto, modificar esta variável pode impactar o uso geral de memória do banco de dados.
• Se você tiver outro disco ou tablespace com mais espaço disponível, pode considerar alterar o local de armazenamento temporário para a reconstrução do índice. Você pode definir o parâmetro do PostgreSQL temp_tablespaces para especificar um tablespace diferente para objetos temporários.
• Considere liberar espaço excluindo dados potencialmente desnecessários.
• Considere diminuir o parâmetro Período de armazenamento de dados housekeeper antes de executar o housekeeper.

Alterando a chave primária com tempo de inatividade

1. Remova a chave primária atual da tabela auditlog e adicione a nova chave primária.

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

2. Reconstrua os índices (opcional, mas altamente recomendado, veja Notas importantes sobre a reconstrução de índices).

REINDEX TABLE CONCURRENTLY auditlog;

Alterando a chave primária sem tempo de inatividade

O método manual de alteração da chave primária é descrito aqui. Como alternativa, a extensão pg_repack pode ser considerada para criar uma nova tabela, copiar os dados e trocar as tabelas.

1. Crie uma nova tabela com a nova chave primária e crie os í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. Troque as tabelas.

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

3. Copie os dados da tabela antiga para a nova tabela.

INSERT INTO auditlog SELECT * FROM auditlog_old;

Isso pode ser feito em partes (múltiplas instruções INSERT INTO com cláusulas WHERE clock conforme necessário) para evitar uso excessivo de recursos.

4. Exclua a tabela antiga.

DROP TABLE auditlog_old;

Veja também

• Atualização do banco de dados para chaves primárias