🥇Użycie partycjonowania w MySQL dla Zabbix z dużą liczbą obiektów monitorowania

Od dawna używamy połączenia Nagios i Munin do monitorowania serwerów i usług, i nadal odnosi ono sukcesy. Jednak to rozwiązanie ma pewne wady, dlatego my, podobnie jak wielu innych, aktywnie wykorzystujemy Zabbix. W tym artykule opowiemy, jak przy minimalnym wysiłku rozwiązać problem wydajności przy rosnącej liczbie pobieranych metryk i wzroście baz danych MySQL.

Problemy z używaniem bazy danych MySQL razem z Zabbixem

Dopóki baza danych była mała, a liczba przechowywanych w niej metryk niewielka, wszystko było idealne. Proces housekeeper, który uruchamia sam serwer Zabbixa, skutecznie usuwał przestarzałe rekordy z bazy danych, nie pozwalając jej rosnąć. Jednak gdy liczba pobieranych metryk wzrosła, a objętość bazy danych osiągnęła pewien rozmiar, sytuacja zaczęła się pogarszać. Housekeeper przestał nadążać z usuwaniem danych w przydzielonym czasie, w bazie danych zaczęły gromadzić się stare dane. Podczas pracy housekeepera występowało zwiększone obciążenie serwera Zabbixa, które mogło trwać długo. Zrozumieliśmy, że musimy jakoś rozwiązać tę sytuację.

To znany problem, prawie każdy, kto pracował z dużymi ilościami monitoringu w Zabbixie, napotkał ten sam problem. Było kilka rozwiązań: na przykład wymiana MySQL na PostgreSQL lub nawet Elasticsearch, ale najprostszym i wypróbowanym rozwiązaniem było przejście na partycjonowanie tabel, które przechowują dane metryk w bazie danych MySQL. Postanowiliśmy wybrać tę drogę.

Przejście od zwykłych tabel MySQL do tabel partycjonowanych

Zabbix jest dobrze udokumentowany, a tabele, w których przechowuje metryki, są znane. To są tabele: historia, w których przechowywane są wartości float, history_str, w których przechowywane są krótkie wartości tekstowe, history_text, w których przechowywane są długie wartości tekstowe i history_uint, w których przechowywane są wartości całkowite. Jest też tabela trends, która przechowuje dynamikę zmian, ale postanowiliśmy jej nie ruszać, ponieważ jej rozmiar jest niewielki i wrócimy do niej nieco później.

Ogólnie wiadomo, które tabele należało przetworzyć. Postanowiliśmy tworzyć partycje co tydzień, z wyjątkiem ostatniego, na podstawie liczb miesiąca, tzn. w czterech partycjach na miesiąc: od 1 do 7, od 8 do 14, od 15 do 21 oraz od 22 do 1 (następnego miesiąca). Problematyczne było to, że musieliśmy przekształcić odpowiednie tabele w partycjonowane „w locie”, nie przerywając pracy Zabbix Server ani zbierania metryk.

Jak dziwnie by to nie brzmiało, w tej sprawie pomogła nam sama struktura danych tabel. Na przykład tabela historia ma następującą strukturę:

`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` double(16,4) NOT NULL DEFAULT '0.0000',
`ns` int(11) NOT NULL DEFAULT '0',

przy czym

KEY `history_1` (`itemid`,`clock`)

Jak widzimy, każda metryka ostatecznie trafia do tabeli z dwoma bardzo ważnymi i wygodnymi dla nas polami itemid i clock. W ten sposób możemy stworzyć tymczasową tabelę, na przykład o nazwie history_tmp, skonfigurować dla niej partycjonowanie, a następnie przelać do niej wszystkie dane z tabeli historia, a potem zmienić nazwę tabeli historia do history_old, a tabelę history_tmp do historia, po czym dodać te dane, które nie zostały jeszcze dodane z history_old do historia i usunąć history_old. Można to zrobić całkowicie bezpiecznie, niczego nie stracimy, ponieważ wspomniane powyżej pola itemid i clock zapewniają powiązanie konkretnej metryki z konkretnym czasem, a nie z jakimś numerem porządkowym.

Sama procedura przejścia

Uwaga! Zdecydowanie zaleca się, przed rozpoczęciem jakichkolwiek działań, wykonanie pełnej kopii zapasowej bazy danych. Jesteśmy wszystkimi ludźmi i możemy popełnić błąd w zestawie poleceń, co może prowadzić do utraty danych. Tak, kopia zapasowa nie zapewni maksymalnej aktualności, ale lepiej mieć taką, niż żadną.

Otóż niczego nie wyłączamy ani nie zatrzymujemy. Najważniejsze, aby na samym serwerze MySQL było wystarczająco dużo wolnego miejsca na dysku, tzn. żeby na każdą z wymienionych powyżej tabel historia, history_text, history_str, history_uint, przynajmniej było wystarczająco miejsca na utworzenie tabeli z sufiksem „_tmp”, mając na uwadze, że będzie ona miała taką samą objętość jak tabela źródłowa.

Nie będziemy opisywać wszystkiego kilka razy dla każdej z wymienionych tabel i rozważymy wszystko na przykładzie tylko jednej z nich — tabeli historia.

Zatem tworzymy pustą tabelę history_tmp na podstawie struktury tabeli historia.

CREATE TABLE `history_tmp` LIKE `history`;

Tworzymy potrzebne nam partycje. Na przykład, zrobimy to na miesiąc. Każda partycja jest tworzona na podstawie reguły partycjonowania, opartej na wartości pola clock, które porównujemy z znakiem czasu:

ALTER TABLE `history_tmp` PARTITION BY RANGE( clock ) (
PARTITION p20190201 VALUES LESS THAN (UNIX_TIMESTAMP("2019-02-01 00:00:00")),
PARTITION p20190207 VALUES LESS THAN (UNIX_TIMESTAMP("2019-02-07 00:00:00")),
PARTITION p20190214 VALUES LESS THAN (UNIX_TIMESTAMP("2019-02-14 00:00:00")),
PARTITION p20190221 VALUES LESS THAN (UNIX_TIMESTAMP("2019-02-21 00:00:00")),
PARTITION p20190301 VALUES LESS THAN (UNIX_TIMESTAMP("2019-03-01 00:00:00"))
);

Ten operator dodaje partycjonowanie do utworzonej przez nas tabeli history_tmp. Uściślijmy, że dane, których wartość pola clock jest mniejsza niż „2019-02-01 00:00:00” trafią do partycji p20190201, następnie dane, których wartość pola clock jest większa niż „2019-02-01 00:00:00” ale mniejsza niż „2019-02-07 00:00:00” trafią do partycji p20190207 i tym podobne.

Ważna uwaga: Co się stanie, jeśli w naszej partycjonowanej tabeli pojawią się dane, których wartość pola clock będzie większa lub równa „2019-03-01 00:00:00”? Ponieważ dla tych danych nie ma odpowiedniej partycji, nie trafią one do tabeli i zostaną utracone. Dlatego musisz pamiętać o regularnym tworzeniu dodatkowych partycji, aby uniknąć takich utrat danych (o czym poniżej).

A zatem, tymczasowa tabela jest przygotowana. Wprowadzamy dane. Proces może zająć dość długi czas, ale na szczęście nie blokuje żadnych innych zapytań, więc trzeba po prostu uzbroić się w cierpliwość:

INSERT IGNORE INTO `history_tmp` SELECT * FROM history;

Słowo kluczowe IGNORE przy początkowym wprowadzaniu nie jest obowiązkowe, ponieważ w tabeli i tak nie ma danych, jednak przyda się przy ponownym wprowadzaniu danych. Ponadto może być przydatne, jeśli podczas wprowadzania danych musiałeś przerwać ten proces i zacząć od nowa.

Po pewnym czasie (może nawet kilku godzinach), pierwsze wprowadzenie danych zostało zakończone. Jak rozumiesz, teraz tabela history_tmp zawiera nie wszystkie dane z tabeli historia, a tylko te, które były w niej w momencie rozpoczęcia wykonywania zapytania. Tutaj masz wybór: albo wykonujemy kolejny przebieg (jeśli proces wprowadzania trwał długo), albo od razu przechodzimy do zmiany nazw tabel, o których wspomniano wcześniej. Zacznijmy od drugiego przebiegu. Na początek musimy zrozumieć czas ostatnio wstawionego wpisu w history_tmp:

SELECT max(clock) FROM history_tmp;

Załóżmy, że otrzymałeś: 1551045645Teraz wykorzystujemy uzyskaną wartość w drugim przebiegu ładowania danych:

INSERT IGNORE INTO `history_tmp` SELECT * FROM history WHERE clock>=1551045645;

Ten przebieg powinien zakończyć się znacznie szybciej. Ale jeśli pierwszy przebieg trwał godziny, a drugi również trwał długo, może być słuszne wykonanie trzeciego przebiegu, który będzie przebiegał podobnie do drugiego.

Na koniec ponownie wykonujemy operację uzyskania czasu ostatniego wstawienia rekordu w history_tmp, wykonując:

SELECT max(clock) FROM history_tmp;

Załóżmy, że otrzymaliście 1551085645. Zachowaj tę wartość — będzie nam potrzebna do doładowania.

A teraz, gdy początkowe ładowanie danych w history_tmp zakończyło się, przystępujemy do zmiany nazw tabel:

BEGIN;
RENAME TABLE history TO history_old;
RENAME TABLE history_tmp TO history;
COMMIT;

Zorganizowaliśmy ten blok jako jedną transakcję, aby uniknąć momentu wstawienia danych do nieistniejącej tabeli, ponieważ po pierwszym RENAME do momentu wykonania drugiego RENAME tabela historia nie będzie istniała. Ale nawet jeśli między operacjami RENAME do tabeli historia przejdą jakieś dane, a sama tabela jeszcze nie będzie (z powodu zmiany nazwy), otrzymamy niewielką liczbę błędów wstawiania, które można zignorować (mamy monitoring, a nie bank).

Teraz mamy nową tabelę historia z partycjonowaniem, ale brakuje w niej danych, które zostały uzyskane podczas ostatniego przebiegu wstawiania danych do tabeli history_tmp. Ale te dane mamy w tabeli history_old i teraz je tam dolejemy. W tym celu potrzebujemy wcześniej zapisanej wartości 1551085645. Dlaczego zapisaliśmy tę wartość, a nie użyliśmy maksymalnego czasu wstawienia już z bieżącej tabeli historia? Потому что новые данные уже в неё поступают и мы получим неверное время. Итак, дозаливаем данные:

INSERT IGNORE INTO `history` SELECT * FROM history_old WHERE clock>=1551045645;

Po zakończeniu tej operacji w naszej nowej, partycjonowanej tabeli historia są wszystkie dane, które były w starej, plus te, które już przyszły po zmianie nazwy tabeli. Tabela history_old nie jest już nam potrzebna. Można ją od razu usunąć, a można przed usunięciem zrobić jej kopię zapasową (jeśli masz paranoję).

Cały opisany powyżej proces należy powtórzyć dla tabel history_str, history_text i history_uint.

Co należy poprawić w ustawieniach Zabbix Server

Teraz zarządzanie bazą danych w zakresie historii danych spoczywa na naszych barkach. Oznacza to, że Zabbix nie musi już usuwać starych danych — zajmiemy się tym sami. Aby Zabbix Server nie próbował samoczynnie oczyszczać danych, należy wejść w interfejs webowy Zabbix, wybrać w menu „Administracja”, następnie podmenu „Ogólne”, a w rozwijanym menu po prawej stronie wybrać „Oczyszczanie historii”. Na pojawiającej się stronie należy odznaczyć wszystkie opcje dla grupy „Historia” i nacisnąć przycisk „Aktualizuj”. To zapobiegnie zbędnemu oczyszczaniu tabel. historia* przez housekeeper.

Zwróć uwagę na tej samej stronie na grupę „Dynamika zmian”. To właśnie tabela trends, do której obiecaliśmy wrócić. Jeśli również stała się zbyt duża i wymaga partycjonowania, odznacz opcje w tej grupie, a następnie przetwórz tę tabelę dokładnie tak jak zrobiono dla tabel historia*.

Dalsze zarządzanie bazą danych

Jak wcześniej wspomniano, dla prawidłowego działania z partycjonowanymi tabelami, należy na czas tworzyć partycje. Można to zrobić w następujący sposób:

ALTER TABLE `history` ADD PARTITION (PARTITION p20190307 VALUES LESS THAN (UNIX_TIMESTAMP("2019-03-07 00:00:00")));

Ponadto, ponieważ stworzyliśmy partycjonowane tabele i zabroniliśmy Zabbix Serverowi ich oczyszczać, usunięcie starych danych również spoczywa na nas. Na szczęście nie ma z tym żadnych problemów. Robi się to po prostu usuwając tę partycję, której dane stały się niepotrzebne.

Na przykład:

ALTER TABLE history DROP PARTITION p20190201;

W przeciwieństwie do operatorów DELETE FROM z określonym zakresem dat, DROP PARTITION wykonuje się w kilka sekund, całkowicie nie obciążając serwer i równie bezproblemowo działa w przypadku wykorzystania replikacji MySQL.