4 Przygotowanie tabeli auditlog do partycjonowania

Przegląd

Niektóre bazy danych (na przykład MySQL) wymagają, aby kolumna partycjonowania była częścią unikalnego ograniczenia tabeli. Dlatego, aby partycjonować tabelę auditlog według czasu, klucz główny musi zostać zmieniony z auditid na klucz złożony auditid + clock.

Ta sekcja zawiera instrukcje dotyczące zmiany klucza głównego tabeli auditlog.

Instrukcje podane na tej stronie są przeznaczone dla zaawansowanych użytkowników. Należy pamiętać, że instrukcje te mogą wymagać dostosowania do konkretnej konfiguracji. Zmiana klucza głównego może być również niezgodna z przyszłymi poprawkami aktualizacyjnymi, dlatego może być konieczna ręczna obsługa przyszłych aktualizacji.

Zmiana klucza głównego może być operacją wymagającą dużych zasobów i zająć dużo czasu, w zależności od rozmiaru tabeli auditlog. Zaleca się zatrzymanie serwera Zabbix i przełączenie frontend Zabbix w tryb konserwacji na czas wprowadzania zmiany. Jeśli jednak jest to absolutnie konieczne, istnieje sposób na zmianę klucza głównego bez przestoju (patrz poniżej).

Partycjonowanie tabeli auditlog może poprawić na przykład housekeeping w dużych środowiskach. Chociaż housekeeping Zabbix obecnie nie może korzystać z tabel partycjonowanych (z wyjątkiem TimescaleDB), można wyłączyć housekeeping Zabbix i usuwać partycje za pomocą skryptów.

Od Zabbix 7.0 tabela auditlog dla TimescaleDB została przekształcona w hypertable, co pozwala housekeeperowi usuwać dane całymi chunkami. Aby zaktualizować istniejącą tabelę auditlog do hypertable, zobacz Upgrading TimescaleDB schema.

MySQL

Ważne uwagi dotyczące przebudowy indeksów

MySQL automatycznie przebudowuje indeksy dla klucza głównego podczas operacji ALTER TABLE. Jednak zdecydowanie zaleca się również ręczną przebudowę indeksów za pomocą instrukcji OPTIMIZE TABLE, aby zapewnić optymalną wydajność bazy danych.

Przebudowa indeksów może tymczasowo wymagać nawet tyle dodatkowego miejsca na dysku, ile zajmuje sama tabela. Aby uzyskać bieżący rozmiar danych i indeksów, można wykonać następujące instrukcje:

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

Jeśli dostępne miejsce na dysku jest problemem, postępuj zgodnie z instrukcjami Zmiana klucza głównego bez przestoju. Dostępne są również inne opcje:

  • Zwiększenie parametru MySQL sort_buffer_size może pomóc zmniejszyć zużycie miejsca na dysku podczas ręcznej przebudowy indeksów. Należy jednak pamiętać, że modyfikacja tej zmiennej może wpłynąć na ogólne zużycie pamięci przez bazę danych.
  • Rozważ zwolnienie miejsca przez usunięcie potencjalnie niepotrzebnych danych.
  • Rozważ zmniejszenie parametru Okres przechowywania danych housekeeper przed uruchomieniem housekeepera.
Zmiana klucza głównego z przestojem

1. Usuń bieżący klucz główny tabeli auditlog i dodaj nowy klucz główny.

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

2. Przebuduj indeksy (opcjonalne, ale zdecydowanie zalecane, zobacz Ważne uwagi dotyczące przebudowy indeksów).

OPTIMIZE TABLE auditlog;
Zmiana klucza głównego bez przestoju

Ręczna metoda zmiany klucza głównego została opisana tutaj. Alternatywnie można użyć zestawu narzędzi pt-online-schema-change firmy Percona. Ten zestaw narzędzi automatycznie wykonuje następujące działania, jednocześnie minimalizując ilość miejsca używanego podczas modyfikacji tabeli auditlog.

1. Utwórz nową tabelę z nowym kluczem głównym i utwórz indeksy.

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. Zamień tabele.

RENAME TABLE auditlog TO auditlog_old, auditlog_new TO auditlog;

3. Skopiuj dane ze starej tabeli do nowej tabeli.

INSERT INTO auditlog SELECT * FROM auditlog_old;

Można to zrobić partiami (wieloma instrukcjami INSERT INTO z klauzulami WHERE clock, zależnie od potrzeb), aby uniknąć nadmiernego zużycia zasobów.

4. Usuń starą tabelę.

DROP TABLE auditlog_old;

PostgreSQL

Ważne uwagi dotyczące przebudowy indeksów

PostgreSQL automatycznie przebudowuje indeksy dla klucza głównego podczas operacji ALTER TABLE. Zaleca się jednak również ręczne przebudowanie indeksów za pomocą instrukcji REINDEX TABLE CONCURRENTLY, aby zapewnić optymalną wydajność bazy danych.

Przebudowa indeksów może tymczasowo wymagać nawet trzykrotności przestrzeni dyskowej obecnie używanej przez indeksy. Aby uzyskać bieżący rozmiar indeksów, możesz wykonać następujące zapytanie:

SELECT pg_size_pretty(pg_indexes_size('auditlog'));

Jeśli dostępna przestrzeń dyskowa jest problemem, postępuj zgodnie z instrukcjami Zmiana klucza głównego bez przestoju. Dostępne są również inne opcje:

  • Zwiększenie parametru PostgreSQL maintenance_work_mem może pomóc zmniejszyć użycie przestrzeni dyskowej podczas ręcznej przebudowy indeksów. Jednak modyfikacja tej zmiennej może wpłynąć na ogólne użycie pamięci przez bazę danych.
  • Jeśli masz inny dysk lub tablespace z większą ilością dostępnego miejsca, możesz rozważyć zmianę lokalizacji pamięci tymczasowej dla przebudowy indeksów. Możesz ustawić parametr PostgreSQL temp_tablespaces, aby określić inny tablespace dla obiektów tymczasowych.
  • Rozważ zwolnienie miejsca przez usunięcie potencjalnie niepotrzebnych danych.
  • Rozważ zmniejszenie parametru Okres przechowywania danych housekeeper przed uruchomieniem housekeepera.
Zmiana klucza głównego z przestojem

1. Usuń bieżący klucz główny tabeli auditlog i dodaj nowy klucz główny.

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

2. Przebuduj indeksy (opcjonalne, ale zdecydowanie zalecane, zobacz Ważne uwagi dotyczące przebudowy indeksów).

REINDEX TABLE CONCURRENTLY auditlog;
Zmiana klucza głównego bez przestoju

Ręczna metoda zmiany klucza głównego jest opisana tutaj. Alternatywnie można rozważyć rozszerzenie pg_repack do utworzenia nowej tabeli, skopiowania danych i zamiany tabel.

1. Utwórz nową tabelę z nowym kluczem głównym i utwórz indeksy.

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. Zamień tabele.

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

3. Skopiuj dane ze starej tabeli do nowej tabeli.

INSERT INTO auditlog SELECT * FROM auditlog_old;

Można to zrobić partiami (wieloma instrukcjami INSERT INTO z klauzulami WHERE clock według potrzeb), aby uniknąć nadmiernego zużycia zasobów.

4. Usuń starą tabelę.

DROP TABLE auditlog_old;

Zobacz także