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ą polecenia OPTIMIZE TABLE, aby zapewnić optymalną wydajność bazy danych.

Przebudowa indeksów może tymczasowo wymagać dodatkowej ilości miejsca na dysku równej rozmiarowi samej tabeli. Aby uzyskać bieżący rozmiar danych i indeksów, można wykonać następujące polecenia:

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

Jeśli dostępne miejsce na dysku jest problemem, postępuj zgodnie z instrukcjami w sekcji 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 poprzez 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. Odbuduj indeksy (opcjonalnie, ale zdecydowanie zalecane; zobacz Ważne uwagi dotyczące odbudowy indeksów).

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

Ręczna metoda zmiany klucza głównego jest 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 գործողności, jednocześnie minimalizując ilość miejsca używanego podczas zmiany tabeli auditlog.

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

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 miejscami.

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 wykonać w częściach (za pomocą wielu instrukcji INSERT INTO z klauzulami WHERE clock, w razie potrzeby), 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. Jednak zdecydowanie zaleca się również ręczną przebudowę indeksów za pomocą polecenia REINDEX TABLE CONCURRENTLY, aby zapewnić optymalną wydajność bazy danych.

Przebudowa indeksów może tymczasowo wymagać nawet trzykrotności miejsca na dysku aktualnie zajmowanego przez indeksy. Aby sprawdzić bieżący rozmiar indeksów, możesz wykonać następujące zapytanie:

SELECT pg_size_pretty(pg_indexes_size('auditlog'));

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

  • Zwiększenie parametru PostgreSQL maintenance_work_mem może pomóc zmniejszyć zużycie miejsca na dysku podczas ręcznej przebudowy indeksów. Jednak modyfikacja tej zmiennej może wpłynąć na ogólne zuż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ę tymczasowej lokalizacji przechowywania dla przebudowy indeksu. Możesz ustawić parametr PostgreSQL temp_tablespaces, aby wskazać inny tablespace dla obiektów tymczasowych.
  • Rozważ zwolnienie miejsca przez usunięcie potencjalnie niepotrzebnych danych.
  • Rozważ zmniejszenie parametru Okres przechowywania danych housekeeper przed uruchomieniem housekeeper.
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. Odbuduj indeksy (opcjonalnie, ale zdecydowanie zalecane; zobacz Ważne uwagi dotyczące odbudowy indeksów).

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

Tutaj opisano ręczną metodę zmiany klucza głównego. 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 wykonać partiami (wiele instrukcji INSERT INTO z klauzulami WHERE clock w razie potrzeby), aby uniknąć nadmiernego zużycia zasobów.

4. Usuń starą tabelę.

DROP TABLE auditlog_old;

Zobacz także