🥇Amfani da ɓangarori na MySQL don Zabbix tare da adadi mai yawa na abubuwan saka idanu

Don sa ido kan sabar da ayyuka, mun sami nasarar yin amfani da haɗin haɗin gwiwa dangane da Nagios da Munin na dogon lokaci. Duk da haka, wannan haɗin yana da ƙididdiga masu yawa, don haka mu, kamar sauran mutane, muna amfani da shi sosai ZabbixA cikin wannan labarin, za mu tattauna yadda za a magance matsalolin aiki tare da ƙaramin ƙoƙari yayin da adadin ma'auni da aka tattara da girman bayanan MySQL yana ƙaruwa.

Matsaloli ta amfani da bayanan MySQL tare da Zabbix

Yayin da ma'ajin bayanai ya yi ƙanƙanta kuma adadin ma'auni da aka adana a ciki ya yi ƙanƙanta, komai ya yi kyau. Tsarin da aka gina a cikin ma'aikatan gidan, wanda ke gudanar da Zabbix Server da kansa, ya yi nasarar goge bayanan da ba a daɗe ba daga ma'adanar bayanai, tare da hana shi girma. Koyaya, yayin da adadin ma'aunin da aka tattara ya ƙaru kuma ma'aunin bayanai ya kai wani ƙayyadaddun girman, abubuwa sun ta'azzara. Ma'aikacin gida ya kasa ci gaba da goge bayanan a cikin tazarar lokacin da aka ware, kuma tsoffin bayanai sun fara ci gaba da kasancewa a cikin ma'ajin bayanai. Yayin da mai aikin gida ke gudana, Zabbix Server ya sami ƙarin nauyi, wanda zai iya dawwama na dogon lokaci. Ya bayyana cewa ana bukatar mafita.

Wannan lamari ne sananne; kusan duk wanda ya yi aiki tare da babban saka idanu akan Zabbix ya fuskanci matsala iri ɗaya. Akwai mafita da yawa, kamar maye gurbin MySQL tare da PostgreSQL ko ma Elasticsearch, amma mafi sauƙi kuma mafi tabbataccen mafita shine a raba teburin da ke adana bayanan awo a cikin bayanan MySQL. Mun yanke shawarar ɗaukar wannan hanyar.

Yin ƙaura daga tebur na MySQL na yau da kullun zuwa waɗanda aka raba

Zabbix yana da rubuce-rubuce sosai, kuma teburin da yake adana awo sananne ne. Wadannan teburi sune: history, inda ake adana kimar ruwa, history_str, inda aka adana gajeriyar kirtani, history_text, inda ake adana dogayen kimar rubutu da history_uint, inda ake adana ƙimar lamba. Akwai kuma tebur trends, wanda ke adana yanayin sauye-sauye, amma mun yanke shawarar kada mu taɓa shi saboda girmansa kaɗan ne kuma za mu dawo da shi nan gaba kadan.

Gabaɗaya, ya bayyana a fili waɗanne teburan da ake buƙatar sarrafa su. Mun yanke shawarar samar da bangare na kowane mako, sai dai na karshe, bisa la’akari da kwanakin watan, watau kashi hudu a kowane wata: daga na daya zuwa na bakwai, daga na 8 zuwa na 14, daga na 15 zuwa na 21, da kuma daga na 22 zuwa na daya (na wata mai zuwa). Wahalar ita ce muna buƙatar musanya teburin da muke buƙata don rarraba "a kan tashi," ba tare da katse aikin Zabbix Server da tarin awo ba.

Abin ban mamaki, tsarin bayanan tebur da kansa ya taimaka mana a cikin wannan. Misali, tebur history yana da tsari mai zuwa:

`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',

a ciki

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

Kamar yadda muke iya gani, kowane awo a ƙarshe an shigar dashi cikin tebur tare da filaye biyu masu mahimmanci kuma masu dacewa a gare mu abu и Agogon. Don haka, za mu iya ƙirƙirar tebur na ɗan lokaci cikin sauƙi, alal misali, tare da sunan history_tmp, saita partitioning don shi sannan kuma canja wurin duk bayanan daga tebur a can history, sa'an nan kuma sake suna tebur history в history_old, da tebur history_tmp в history, sannan mu cika bayanan da ba mu cika ba tukuna history_old в history kuma share history_old. Ana iya yin wannan gaba ɗaya cikin aminci, ba za mu rasa komai ba, saboda filayen da aka ƙayyade a sama abu и Agogon samar da hanyar haɗi tsakanin ƙayyadaddun ma'auni da ƙayyadaddun lokaci, maimakon zuwa wani lamba.

Hanyar mika mulki kanta

Muhimmanci! Ana ba da shawarar sosai don ƙirƙirar cikakken madadin bayananku kafin yin kowane ayyuka. Mu duka mutane ne kuma muna iya yin kuskure yayin buga umarni, wanda zai iya haifar da asarar bayanai. Ee, wariyar ajiya ba zai tabbatar da mafi girman sabuntawar zamani ba, amma yana da kyau a sami ɗaya fiye da ɗaya.

Don haka, kar a rufe ko dakatar da komai. Babban abu shi ne cewa uwar garken MySQL ita kanta tana da isassun sararin faifai kyauta, watau, ga kowane tebur da aka jera a sama. history, history_text, history_str, history_uint, aƙalla, akwai isasshen sarari don ƙirƙirar tebur mai taken "_tmp", ganin cewa girmansa zai yi daidai da tebur na asali.

Ba za mu kwatanta komai sau da yawa ga kowane tebur na sama kuma za mu yi la'akari da duk abin da ke amfani da misalin ɗaya daga cikinsu kawai - tebur. history.

Don haka, bari mu ƙirƙiri tebur mara komai. history_tmp bisa tsarin tebur history.

CREATE TABLE `history_tmp` LIKE `history`;

Muna ƙirƙirar sassan da muke buƙata. Misali, za mu yi haka har tsawon wata guda. An ƙirƙiri kowane bangare bisa ka'idar rarraba bisa ƙimar filin. Agogon, wanda muke kwatanta da tambarin lokaci:

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"))
);

Wannan ma'aikaci yana ƙara rarrabuwa zuwa teburin da muka ƙirƙira. history_tmpBari mu fayyace cewa bayanan da ƙimar filin take Agogon kasa da "2019-02-01 00:00:00" za a haɗa a cikin ɓangaren. p20190201, sannan bayanan da filin darajar su Agogon fiye da "2019-02-01 00:00:00" amma kasa da "2019-02-07 00:00:00" za a haɗa a cikin ɓangaren. p20190207 da sauransu.

Muhimmin bayanin kula: Me zai faru idan muna da bayanai a cikin tebur da aka raba tare da ƙimar filin agogo mafi girma ko daidai da "2019-03-01 00:00:00"? Tun da babu wani bangare da ya dace don wannan bayanan, ba za a haɗa shi a cikin tebur ba kuma zai ɓace. Sabili da haka, dole ne ku tuna don ƙirƙirar ƙarin ɓangarori a cikin lokaci don guje wa irin wannan asarar bayanai (an tattauna a ƙasa).

Don haka, tebur na wucin gadi yana shirye. Mu loda bayanan. Tsarin na iya ɗaukar ɗan lokaci kaɗan, amma an yi sa'a, baya toshe wasu tambayoyi, don haka kawai kuna buƙatar haƙuri:

INSERT IGNORE INTO `history_tmp` SELECT * FROM history;

Ba a buƙatar kalmar IGNORE don nauyin bayanan farko, saboda babu bayanai a cikin tebur ta wata hanya. Koyaya, zaku buƙaci ta lokacin loda ƙarin bayanai. Hakanan yana iya zama mai taimako idan dole ne ka katse aikin lodin bayanai kuma ka sake farawa.

Don haka, bayan wani lokaci (wataƙila ma ƴan awoyi kaɗan), an gama ƙaddamar da bayanan farko. Kamar yadda kake gani, tebur yanzu history_tmp baya ƙunsar duk bayanan daga teburin history, amma kawai waɗanda ke cikinta a farkon tambayar. Anan, hakika kuna da zaɓi: ko dai mu sake yin wani fasinja (idan aikin lodi ya ɗauki lokaci mai tsawo), ko kuma nan da nan mu ci gaba da canza sunan tebur, kamar yadda aka tattauna a sama. Bari mu fara magana game da wucewa ta biyu. Da farko, muna buƙatar fahimtar lokacin da aka saka rikodin ƙarshe a ciki history_tmp:

SELECT max(clock) FROM history_tmp;

A ce kun karba: 1551045645Yanzu muna amfani da ƙimar da aka samu a cikin wucewa na biyu na cika bayanai:

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

Wannan izinin ya kamata ya cika sosai da sauri. Amma idan izinin farko ya ɗauki sa'o'i, kuma na biyu ya ɗauki lokaci mai tsawo daidai, yana iya dacewa a yi ta uku, wanda aka yi daidai da na biyu.

A ƙarshe muna sake yin aikin samun lokacin shigar ƙarshe na rikodin a ciki history_tmp, ta hanyar kammala:

SELECT max(clock) FROM history_tmp;

A ce kun karba 1551085645Ajiye wannan ƙimar - za mu buƙaci ta don sake cikawa.

Kuma yanzu, a zahiri, lokacin da aka yi lodin bayanan farko history_tmp Ya ƙare, bari mu fara canza sunan tebur:

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

Mun tsara wannan block a matsayin ciniki guda ɗaya don guje wa shigar da bayanai a cikin tebur ɗin da ba a wanzu ba, domin bayan an yi SAKEN na farko har sai an yi RENAME na biyu, teburin. history ba zai wanzu ba. Amma koda tsakanin ayyukan RENAME a cikin tebur history Idan wasu bayanai sun zo, amma teburin da kansa bai wanzu ba tukuna (saboda sake suna), za mu sami ƙaramin adadin kurakurai da za a iya watsi da su (muna sa ido, ba banki ba).

Yanzu muna da sabon tebur history tare da rarrabawa, amma ya ɓace bayanan da aka dawo da su yayin wucewa ta ƙarshe na saka bayanai a cikin tebur. history_tmpAmma muna da wannan bayanan a cikin tebur. history_old Kuma za mu cika su daga nan yanzu. Don yin wannan, za mu buƙaci ƙimar da aka adana a baya 1551085645. Me yasa muka ajiye wannan ƙimar maimakon amfani da matsakaicin lokacin cikawa daga tebur na yanzu? history? Domin sabbin bayanai sun riga sun shigo, kuma za mu sami lokacin da bai dace ba. Don haka, bari mu ƙara ƙarin bayanai:

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

Bayan kammala wannan aiki, muna da a cikin sabon partitioned tebur history Akwai duk bayanan da ke cikin tsohon tebur, da kuma bayanan da suka zo bayan an sake sunan tebur. history_old Ba ma bukatarsa kuma. Kuna iya share shi nan da nan, ko kuma kuna iya yin kwafinsa kafin ku goge shi (idan kun kasance masu ɓarna).

Dukkanin tsarin da ke sama yana buƙatar maimaitawa don tebur. history_str, history_text и history_uint.

Me ya kamata a gyara a cikin saitunan Sabar Zabbix?

Yanzu muna da alhakin kiyaye tarihin bayanan. Wannan yana nufin Zabbix ba zai sake share tsoffin bayanai ba - za mu sarrafa su da kanmu. Don hana Zabbix Server yunƙurin tsaftace bayanai ta atomatik, je zuwa cibiyar sadarwar yanar gizo ta Zabbix, zaɓi "Administration" daga menu, sannan ƙaramin menu na "General", sannan zaɓi "Clear History" daga jerin zaɓuka na dama. A shafin da ya bayyana, cire duk akwatunan da ke cikin rukunin "Tarihi" kuma danna maɓallin "Refresh". Wannan zai hana tsabtace tebur mara amfani. history* ta maigadi.

Kula da rukunin "Dynamics of Change" a wannan shafin. Wannan tebur ne. trends, wanda muka yi alkawarin komawa. Idan naku ma ya yi girma kuma yana buƙatar rabuwa, cire alamar akwatunan da ke cikin wannan rukunin kuma, sannan ku sarrafa wannan tebur kamar yadda na tebur. history*.

Ƙarin kiyaye bayanai

Kamar yadda aka ambata a baya, don tabbatar da aikin da ya dace na teburin da aka raba, dole ne a ƙirƙiri ɓangarori a cikin lokaci. Ana iya yin haka kamar haka:

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

Bugu da ƙari, tun da mun ƙirƙiri faifan tebur kuma mun hana Zabbix Server daga tsaftace su, share tsoffin bayanai yanzu alhakinmu ne. Abin farin ciki, wannan gaba ɗaya ba shi da wahala. Ana yin hakan ne ta hanyar share sashin da ba ma buƙatar bayanansa.

Alal misali:

ALTER TABLE history DROP PARTITION p20190201;

Ba kamar bayanan DELETE FROM masu kewayon kwanan wata ba, DROP PARTITION yana ɗaukar 'yan daƙiƙa kaɗan kafin a kammala kuma an cire shi gaba ɗaya. sabar kuma yana aiki daidai lokacin amfani da kwafi na MySQL.