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

Преглед

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

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

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

Од Zabbix-а 7.0, табела auditlog за TimescaleDB је конвертована у хипертабелу, што омогућава домаћици да испусти податке по деловима. Да бисте надоградили постојећу табелу аудитлог у хипертабелу, погледајте Надоградњу TimescaleDB шеме.

MySQL

Важне напомене о обнављању индекса

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

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

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

Ако је доступан простор на диску забрињавајући, пратите упутства [Промена примарног кључа без прекида рада] (#altering-primary-key-without-downtime). Доступне су и друге опције:

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

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

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

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

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

OPTIMIZE TABLE auditlog;

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

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

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

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

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 параметар да наведете другачији простор табеле за привремене објекте.
• Размислите ослобађање простора брисањем потенцијално непотребних података.
• Размислите смањење параметра Период чувања података одржавање пре него што извршите одржавање.

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

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;

Oracle

Важне напомене о обнављању индекса

Oracle аутоматски поново гради индексе за примарни кључ током операције ALTER TABLE. Међутим, веома је препоручљиво да се индекси ручно реконструишу са наредбама ALTER INDEX <index> REBUILD PARALLEL како би се обезбедиле оптималне перформансе базе података.

Обнова индекса може привремено захтевати значајан простор на диску.

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

• Повећање Oracle параметра SORT_AREA_SIZE може помоћи да се смањи употреба простора на диску приликом ручног обнављања индекса . Међутим, измена ове променљиве ће утицати на укупну употребу меморије базе података.
• Можете поставити паралелни степен користећи PARALLEL клаузулу, на пример: ALTER INDEX auditlog_1 REBUILD PARALLEL 4
• Размислите о ослобађању простора брисањем потенцијално непотребних података.
• Размислите о смањењу параметра Период складиштења података одржавање пре него што извршите одржавање.

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

1. Преузмите име ограничења.

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

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

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

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

3.1. Добавите имена индекса.

SELECT index_name FROM user_indexes WHERE table_name='AUDITLOG';

3.2. Поново изградите сваки индекс.

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

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

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

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

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;

Погледајте такође

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