🥇Usebenzisa i-MySQL Partitioning ye-Zabbix enenombolo Enkulu Yezinto Ezigadwayo

Ukuqapha amaseva namasevisi, besisebenzisa isisombululo esihlanganisiwe esisekelwe ku-Nagios ne-Munin isikhathi eside, futhi namanje siphumelele. Kodwa-ke, le nhlanganisela inenqwaba yezinkinga, ngakho-ke thina, njengabaningi, sixhaphaza ngenkuthalo Zabbix. Kulesi sihloko sizokhuluma ngokuthi, ngomzamo omncane, ungayixazulula kanjani inkinga yokusebenza lapho ukhulisa inani lamamethrikhi athathiwe futhi ukhulisa amavolumu edatha ye-MySQL

Izinkinga ngokusebenzisa isizindalwazi se-MySQL ne-Zabbix

Ngenkathi i-database yayincane futhi inani lamamethrikhi agcinwe kuyo lalincane, yonke into yayiyinhle. Inqubo evamile yomgcini wasendlini, eyethulwa i-Zabbix Server ngokwayo, isuse ngempumelelo amarekhodi aphelelwe yisikhathi kusizindalwazi, ivimbele ukuthi ingakhuli. Nokho, ngokushesha nje lapho inani lamamethrikhi athathiwe likhula futhi usayizi wesizindalwazi ufinyelela usayizi othile, izinto zaba zimbi kakhulu. Umnikazi wendlu uyekile ukukwazi ukususa idatha phakathi nesikhathi esabelwe sona, futhi idatha endala yaqala ukuhlala kusizindalwazi. Ngenkathi umgcini wendlu esebenza, kube nomthwalo owengeziwe ku-Zabbix Server, obungaqhubeka isikhathi eside. Kwacaca ukuthi sidinga ngandlela thize ukuxazulula isimo esikhona manje.

Lena inkinga eyaziwayo, cishe wonke umuntu osebenze ngenani elikhulu lokuqapha ku-Zabbix uhlangabezane nento efanayo. Bekukhona nezixazululo ezimbalwa: isibonelo, ukufaka esikhundleni se-MySQL nge-PostgreSQL noma i-Elasticsearch, kodwa isisombululo esilula nesifakazelwe kakhulu kwaba ukushintshela kumathebula okuhlukanisa agcina idatha yamamethrikhi kusizindalwazi se-MySQL. Sanquma ukuhamba ngayo le ndlela.

Inguquko isuka kumathebula e-MySQL ajwayelekile iye kulawo ahlukanisiwe

I-Zabbix ibhalwe kahle futhi amatafula lapho igcina khona amamethrikhi ayaziwa. Lawa amatafula: history, lapho amanani okuntanta agcinwa khona, history_str, lapho kugcinwa khona amanani emicu emifushane, history_text, lapho amanani ombhalo omude agcinwa futhi history_uint, lapho amanani aphelele agcinwa khona. Kukhona netafula trends, egcina ukuguquguquka kwezinguquko, kodwa sanquma ukuthi singayithinti, ngoba ubukhulu bayo buncane futhi sizobuyela kuyo kancane kancane.

Ngokuvamile, kwakucacile ukuthi yimaphi amathebula okudingeka acutshungulwe. Sinqume ukwenza ama-partitions isonto ngalinye, ngaphandle kweyokugcina, ngokusekelwe ezinombolweni zenyanga, i.e. amaqoqo amane ngenyanga: kusukela ku-1 kuya ku-7, kusukela ku-8 kuya ku-14, kusukela ku-15 kuya ku-21 futhi kusukela ku-22 kuya ku-1st (yenyanga ezayo). Ubunzima kwakuwukuthi kwakudingeka siguqule amatafula esiwadingayo abe ahlukanisiwe “ngokuphazima kweso”, ngaphandle kokuphazamisa ukusebenza kwe-Zabbix Server kanye nokuqoqwa kwamamethrikhi.

Ngokudabukisayo, ukwakheka kwedatha yamathebula ngokwawo kwasisiza kulokhu. Isibonelo ithebula history inesakhiwo esilandelayo:

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

lapho

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

Njengoba ubona, imethrikhi ngayinye ekugcineni ifakwa etafuleni elinezinkambu ezimbili ezibaluleke kakhulu nezisilungele itemid и Iwashi. Ngakho, singakwazi ukudala kalula itafula lesikhashana, isibonelo, ngegama history_tmp, setha ukwahlukanisa kwakho bese udlulisela yonke idatha etafuleni lapho historybese uqamba kabusha ithebula history в history_old, kanye netafula history_tmp в history, bese wengeza idatha esingazange siyigcwalise history_old в history bese ususa history_old. Lokhu kungenziwa ngokuphepha ngokuphelele, ngeke silahlekelwe lutho, ngoba izinkambu ezingenhla itemid и Iwashi hlinzeka ngokubophezela kwemethrikhi ethile esikhathini esithile, futhi hhayi enombolweni ethile yomkhiqizo.

Inqubo yenguquko ngokwayo

Qaphela! Kutuseka kakhulu, ngaphambi kokuqala noma yiziphi izenzo, ukwenza ikhophi eyisipele ephelele yesizindalwazi. Sonke singabantu abaphilayo futhi singenza iphutha kusethi yemiyalo, okungaholela ekulahlekeni kwedatha. Yebo. Ukwenza ikhophi yasenqolobaneni ngeke kuqinisekise ukufaneleka kwesimanjemanje, kodwa kungcono ukuba nakho kunokungabi nakho.

Ngakho-ke, asivali lutho noma simise noma yini. Into eyinhloko ukuthi iseva ye-MySQL ngokwayo inenani elanele lesikhala sediski samahhala, i.e. ukuze kulelo nalelo tafula abalwe ngenhla history, history_text, history_str, history_uint, okungenani, bekunesikhala esanele sokudala ithebula elinesijobelelo esithi “_tmp”, uma kubhekwa ukuthi lizoba nosayizi ofanayo nowethebula lokuqala.

Ngeke sichaze yonke into kaningana kulelo nalelo tafula elingenhla futhi sizocubungula yonke into sisebenzisa isibonelo soyedwa wabo - itafula. history.

Ngakho-ke, masidale itafula elingenalutho history_tmp ngokusekelwe esakhiweni setafula history.

CREATE TABLE `history_tmp` LIKE `history`;

Sakha ama-partitions esiwadingayo. Ngokwesibonelo, ake sikwenze lokhu inyanga yonke. Ingxenye ngayinye idalwe ngokusekelwe kumthetho wokuhlukanisa ngokusekelwe kunani lensimu Iwashi, esiyiqhathanisa nesitembu sesikhathi:

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

Lo opharetha wengeza ukwahlukanisa kwetafula esilidalile history_tmp. Ake sicacise leyo datha inani layo lenkundla Iwashi ngaphansi kuka-"2019-02-01 00:00:00" kuzofakwa eqeqebeni p20190201, bese kuba yidatha inani layo lenkambu Iwashi ngaphezu kuka-“2019-02-01 00:00:00” kodwa ngaphansi kuka-“2019-02-07 00:00:00” kuzofakwa esabelweni p20190207 nokunye.

Inothi elibalulekile: Kwenzekani uma sinedatha kuthebula elihlukanisiwe inani layo enkambini yewashi likhulu noma elilingana nokuthi “2019-03-01 00:00:00”? Njengoba ingekho ingxenye efanelekile yale datha, ngeke ivele kuthebula futhi izolahleka. Ngakho-ke, udinga ukukhumbula ukudala izingxenye ezingeziwe ngesikhathi ukuze ugweme ukulahleka kwedatha okunjalo (njengoba kuxoxwe ngakho ngezansi).

Ngakho, itafula lesikhashana liyalungiswa. Gcwalisa idatha. Inqubo ingathatha isikhathi eside, kodwa ngenhlanhla ayivimbi noma yiziphi ezinye izicelo, ngakho-ke udinga nje ukuba nesineke:

INSERT IGNORE INTO `history_tmp` SELECT * FROM history;

Igama elingukhiye elithi IGNORE alidingeki phakathi nokugcwaliswa kokuqala, njengoba kungekho datha kuthebula noma kunjalo, kodwa uzoyidinga lapho wengeza idatha. Ngaphezu kwalokho, kungase kube usizo uma, ngenkathi ulayisha idatha, kufanele uphazamise le nqubo futhi uqale futhi.

Ngakho, ngemva kwesikhathi esithile (mhlawumbe ngisho namahora ambalwa), ukulayishwa kwedatha kokuqala kwenzeka. Njengoba uqonda, manje itafula history_tmp ayiqukethe yonke idatha evela kuthebula history, kodwa yilabo kuphela ababekuyo ngesikhathi isicelo siqala. Lapha empeleni unokukhetha: noma senza enye iphasi (uma inqubo yokugcwalisa ithatha isikhathi eside), noma siqhubekela phambili ngokuqamba kabusha amatafula, okuxoxwe ngawo ngenhla. Ake sikhulume ngepasi lesibili kuqala. Okokuqala sidinga ukuqonda isikhathi serekhodi lokugcina elifakiwe history_tmp:

SELECT max(clock) FROM history_tmp;

Ake sithi uthole: 1551045645. Manje sisebenzisa inani eliwumphumela kuphasi yesibili yokugcwaliswa kwedatha:

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

Le ndima kufanele iphele ngokushesha kakhulu. Kodwa uma iphasi lokuqala lithatha amahora amaningi ukuqedwa, kanti elesibili nalo lithatha isikhathi eside, kungase kube okulungile ukudlula okwesithathu, okwenziwa ngendlela efanayo ncamashi neyesibili.

Okokugcina, senza futhi umsebenzi wokuthola isikhathi sokufakwa kokugcina kwerekhodi history_tmpngokugijima:

SELECT max(clock) FROM history_tmp;

Ake sithi uthole 1551085645. Londoloza leli nani - sizolidinga ukuze siligcwalise futhi.

Futhi manje, empeleni, lapho ukugcwaliswa kokuqala kwedatha ku history_tmp sesiqedile, asiqale ukuqamba kabusha amatafula:

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

Lesi vimba siklame njengomsebenzi owodwa ukuze sigweme ukufaka idatha kuthebula elingekho, ngoba ngemva kwe-RENAME yokuqala kuze kusetshenziswe i-RENAME yesibili, ithebula history ngeke abe khona. Kodwa noma ngabe phakathi kwe-RENAME yokusebenza etafuleni history idatha ethile izofika, kodwa ithebula ngokwalo ngeke libe khona (ngenxa yokuqamba kabusha), sizothola inombolo encane yamaphutha okufaka anganakwa (sinokuqapha, hhayi ibhange).

Manje sinetafula elisha history ngokuhlukanisa, kodwa ayinayo idatha etholwe ngesikhathi sokugcina sokufaka idatha etafuleni history_tmp. Kodwa sinale datha etafuleni history_old futhi manje sizowagcwalisa kusukela lapho. Ukuze senze lokhu, sidinga inani elilondolozwe ngaphambilini elithi 1551085645. Kungani silondoloze leli nani futhi singasebenzisi isikhathi esiphezulu sokugcwalisa kusuka kuthebula lamanje history? Ngoba idatha entsha isivele iyifaka futhi sizothola isikhathi esingalungile. Ngakho-ke, masingeze idatha:

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

Ngemva kokuba lo msebenzi usuqediwe, etafuleni lethu elisha, elihlukanisiwe history kukhona yonke idatha ebikweyakudala, kanye nalezo esezivele zifikile ngemuva kokuqamba kabusha itafula. Ithebula history_old asisakudingi. Ungayisusa ngokushesha, noma ungenza ikhophi yayo eyisipele ngaphambi kokuyisusa (uma udidekile).

Yonke inqubo engenhla idinga ukuphinda kumathebula history_str, history_text и history_uint.

Yini okudingeka ilungiswe kuzilungiselelo Zeseva ye-Zabbix

Manje ukugcinwa kwesizindalwazi ngokomlando wedatha kuwela emahlombe ethu. Lokhu kusho ukuthi i-Zabbix akusadingeki isuse idatha endala - sizokwenzela thina lokhu. Ukuze uvimbele i-Zabbix Server ekuzameni ukusula idatha ngokwayo, udinga ukuya kusixhumi esibonakalayo sewebhu se-Zabbix, ukhethe u-“Administration” kumenyu, bese kuba imenyu engaphansi ethi “Okuvamile”, bese ukhetha okuthi “Sula umlando” ohlwini lokudonsela phansi oluvuliwe. ilungelo. Ekhasini elivelayo, udinga ukususa ukumaka wonke amabhokisi eqembu elithi "Umlando" bese uchofoza inkinobho ethi "Buyekeza". Lokhu kuzosivimbela ekususeni amatafula kungenasidingo history* ngomsizi wasendlini.

Ekhasini elifanayo, naka iqembu elithi "Dynamics of Changes". Leli itafula nje trends, esathembisa ukuphindela kuyo. Uma nalo selilikhulu kakhulu futhi lidinga ukuhlukaniswa, susa ukumaka amabhokisi akuleli qembu, bese ucubungula leli thebula ngendlela efanayo naleyo owenze ngayo emathebula. history*.

Ukugcinwa okwengeziwe kwesizindalwazi

Njengoba kwakulotshiwe ngaphambili, ukuze usebenze okuvamile kumatafula ahlukanisiwe, kuyadingeka ukudala ukwahlukanisa ngesikhathi. Ungakwenza kanje:

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

Ngaphezu kwalokho, njengoba sidale amathebula ahlukanisiwe futhi savimbela Iseva ye-Zabbix ukuthi iwahlanze, ukususa idatha endala manje kuyindaba yethu. Ngenhlanhla, azikho nhlobo izinkinga lapha. Lokhu kwenziwa kalula ngokususa ukwahlukanisa idatha yayo esingasayidingi.

Isibonelo:

ALTER TABLE history DROP PARTITION p20190201;

Ngokungafani nezitatimende ze-DELETE FROM ezinebanga ledethi, i-DROP PARTITION ithatha imizuzwana embalwa ukuqeda futhi ilayishiwe ngokuphelele. isifiso futhi isebenza kahle uma usebenzisa i-MySQL replication.