4 Припрема auditlog табеле за партиционисање

Преглед

Неке базе података (на пример, MySQL) захтевају да колона за партиционисање буде део јединственог ограничења табеле. Стога, да бисте поделили табелу auditlog по времену, примарни кључ мора да се промени из auditid у композитни кључ auditid + clock.

Ова секција пружа упутства за промену примарног кључа табеле auditlog.

Партиционисање табеле auditlog може побољшати, на пример, одржавање система у великим подешавањима. Иако Zabbix housekeeping тренутно не може да искористи предности партиционисаних табела (осим за TimescaleDB), можете онемогућити Zabbix одржавање система и обрисати партиције помоћу скрипти.

Од Zabbix-а 7.0, табела auditlog за TimescaleDB је конвертована у хипертабелу, што омогућава housekeeper-у да одбацује податке по деловима. Да бисте надоградили постојећу табелу auditlog на хипертабелу, поново покрените скрипту postgresql/timescaledb/schema.sql пре покретања Zabbix сервера. Zabbix сервер ће пријавити упозорење ако се покрене без претходног покретања ове скрипте. Видите такође: TimescaleDB подешавање.

MySQL

Важне напомене о реконструкцији индекса

MySQL аутоматски реконструкцира индексе за примарни кључ током операције ALTER TABLE. Међутим, топло се препоручује да се индекси ручно реконструкцирају помоћу наредбе OPTIMIZE TABLE како би се осигурале оптималне перформансе базе података.

Реконструкција индекса може привремено захтевати онолико додатног простора на диску колико сама табела користи. Да бисте добили тренутну величину података и индекса, можете извршити следеће наредбе:

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

Ако је расположиви простор на диску проблем, пратите упутства Altering primary key without downtime. Доступне су и друге опције:

• Повећање параметра sort_buffer_size MySQL може помоћи у смањењу коришћења простора на диску приликом ручне реконструкције индекса. Међутим, модификовање ове променљиве може утицати на укупну меморију коришћење базе података.
• Размислите о ослобађању простора брисањем потенцијално непотребних података.
• Размислите о смањењу параметра Период чувања података housekeeper пре него што извршите функцију housekeeper.

Измена примарног кључа уз застоје

1. Обришите тренутни примарни кључ табеле auditlog и додајте нови примарни кључ.

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

2. Поново изградите индексе (опционо, али се веома препоручује, погледајте Важне напомене о поновној изградњи индекса).

OPTIMIZE TABLE auditlog;

Промена примарног кључа без застоја

Овде је описан ручни метод промене примарног кључа. Алтернативно, можете користити комплет алата pt-online-schema-change од Перконе. Овај комплет алата аутоматски обавља следеће радње, а истовремено минимизира простор који се користи за промену табеле auditlog.

1. Направите нову табелу са новим примарним кључем и креирајте индексе.

CREATE TABLE `auditlog_new` (
          `auditid`varchar(25)NOT NULL,
          `userid`bigint unsignedNULL,
          `username`varchar(100)DEFAULT ''NOT NULL,
          `clock`integerDEFAULT '0'NOT NULL,
          `ip`varchar(39)DEFAULT ''NOT NULL,
          `action`integerDEFAULT '0'NOT NULL,
          `resourcetype`integerDEFAULT '0'NOT NULL,
          `resourceid` bigint unsignedNULL,`
            resource_cuid`varchar(25)NULL,
          `resourcename`varchar(255)DEFAULT ''NOT NULL,
          `recordsetid`varchar(25)NOT NULL,
          `details`longtextNOT NULL,
          PRIMARY KEY (аудитид,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. Замена табела.

RENAME TABLE auditlog TO auditlog_old, auditlog_new TO auditlog;

3. Копирање података из старе табеле у нову табелу.

INSERT INTO auditlog SELECT * FROM auditlog_old;

Ово се може урадити у деловима (више INSERT INTO наредби са WHERE clock клаузулама по потреби) како би се избегла прекомерна употреба ресурса.

4. Одбаците стару табелу.

DROP TABLE auditlog_old;

PostgreSQL

Важне напомене о поновном изградњи индекса

PostgreSQL аутоматски поново изграђује индексе за примарни кључ током операције ALTER TABLE. Међутим, топло се препоручује да се индекси ручно поново изграде помоћу наредбе REINDEX TABLE CONCURRENTLY како би се осигурале оптималне перформансе базе података.

Поновно изградња индекса може привремено захтевати до три пута више простора на диску од онога што тренутно користе индекси. Да бисте добили тренутну величину индекса, можете извршити следећи упит:

SELECT pg_size_pretty(pg_indexes_size('auditlog'));

Ако је расположиви простор на диску проблем, пратите упутства Измена примарног кључа без застоја. Доступне су и друге опције:

• Повећање параметра maintenance_work_mem PostgreSQL може помоћи у смањењу коришћење простора на диску приликом ручног поновног изградње индекса. Међутим, измена ове променљиве може утицати на укупно коришћење меморије базе података.
• Ако имате други диск или табеларни простор са више расположивог простора, можете размотрити промену привремене локације за складиштење за поновно изградњу индекса. Можете подесити параметар temp_tablespaces PostgreSQL-а да бисте навели други табеларни простор за привремене објекте.
• Размислите о ослобађању простора брисањем потенцијално непотребних података.
• Размислите о смањењу параметра Период складиштења података housekeeper пре него што извршите housekeeper-а.

Промена примарног кључа са застојем

1. Уклоните тренутни примарни кључ табеле auditlog и додајте нови примарни кључ.

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

2. Поново изградите индексе (опционо, али се веомапрепоручује, погледајте Важне напомене о поновној изградњи индекса).

REINDEX TABLE CONCURRENTLY auditlog;

Промена примарног кључа без застоја

Овде је описан ручни метод промене примарног кључа. Алтернативно, екстензија pg_repack може се размотрити за креирање нове табеле, копирање података и замену табела.

1. Направите нову табелу са новим примарним кључем и креирајте индексе.

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. Замена табела.

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

3. Копирање података из старе табеле у нову табелу.

INSERT INTO auditlog SELECT * FROM auditlog_old;

Ово се може урадити у деловима (више INSERT INTO наредби са WHERE clock клаузулама по потреби) како би се избегла прекомерна употреба ресурса.

4. Одбацивање старе табеле.

DROP TABLE auditlog_old;

Такође погледати

• Надоградња базе података на примарне кључеве