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.

Particionar a tabela auditlog pode melhorar, por exemplo, a limpeza em instalações 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 índices manualmente. 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 a 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 é descrito aqui. Como alternativa, você pode usar o pt-online-schema-change toolkit da Percona. Esta ferramenta executa as seguintes ações automaticamente, além de minimizar 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 em disco disponível 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 (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;

Oracle

Notas importantes sobre a reconstrução de índices

O Oracle 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 as instruções ALTER INDEX <index> REBUILD PARALLEL para garantir o desempenho ideal do banco de dados.

A reconstrução de índices pode exigir temporariamente um espaço significativo em disco.

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

• Aumentar o parâmetro Oracle SORT_AREA_SIZE pode ajudar a reduzir o uso de espaço em disco ao reconstruir índices manualmente. No entanto, modificar essa variável impactará o uso geral de memória do banco de dados.
• Você pode definir o grau de paralelismo usando a cláusula PARALLEL, por exemplo: ALTER INDEX auditlog_1 REBUILD PARALLEL 4
• 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 downtime

1. Recupere o nome da restrição.

SELECT CONSTRAINT_NAME FROM all_constraints WHERE TABLE_NAME = 'AUDITLOG' AND CONSTRAINT_TYPE = 'P';

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

ALTER TABLE auditlog DROP CONSTRAINT <constraint_name>;
       ALTER TABLE auditlog ADD CONSTRAINT auditlog_pk PRIMARY KEY (auditid, clock);

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

3.1. Obtenha os nomes dos índices.

SELECT index_name FROM user_indexes WHERE table_name='AUDITLOG';

3.2. Reconstrua cada índice.

ALTER INDEX auditlog_pk REBUILD PARALLEL;
       ALTER INDEX auditlog_1 REBUILD PARALLEL;
       ALTER INDEX auditlog_2 REBUILD PARALLEL;
       ALTER INDEX auditlog_3 REBUILD PARALLEL;

Alterando a chave primária sem tempo de inatividade

1. Crie uma nova tabela com a nova chave primária e crie índices.

CREATE TABLE auditlog_new (
         auditid              nvarchar2(25)                             ,
         userid               number(20)                                NULL,
         username             nvarchar2(100)  DEFAULT ''                ,
         clock                number(10)      DEFAULT '0'               NOT NULL,
         ip                   nvarchar2(39)   DEFAULT ''                ,
         action               number(10)      DEFAULT '0'               NOT NULL,
         resourcetype         number(10)      DEFAULT '0'               NOT NULL,
         resourceid           number(20)                                NULL,
         resource_cuid        nvarchar2(25)                             ,
         resourcename         nvarchar2(255)  DEFAULT ''                ,
         recordsetid          nvarchar2(25)                             ,
         details              nclob           DEFAULT ''                ,
         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 (várias instruções INSERT INTO com cláusulas WHERE clock conforme necessário) para evitar o 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