{"id":53966,"date":"2019-12-14T00:00:00","date_gmt":"2019-12-13T21:00:00","guid":{"rendered":"https:\/\/prohoster.info\/blog\/blog_prohoster\/ispolzovanie-partitsionirovaniya-v-mysql-dlya-zabbix-s-bolshim-kolichestvom-obektov-monitoringa"},"modified":"2020-02-18T14:01:55","modified_gmt":"2020-02-18T11:01:55","slug":"ispolzovanie-partitsionirovaniya-v-mysql-dlya-zabbix-s-bolshim-kolichestvom-obektov-monitoringa","status":"publish","type":"post","link":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/ispolzovanie-partitsionirovaniya-v-mysql-dlya-zabbix-s-bolshim-kolichestvom-obektov-monitoringa","title":{"rendered":"P\u00ebrdorimi i particionimit n\u00eb MySQL p\u00ebr Zabbix me nj\u00eb num\u00ebr t\u00eb madh objektesh monitorimi","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

P\u00ebr monitorimin e server\u00ebve dhe sh\u00ebrbimeve, ne kemi p\u00ebrdorur prej koh\u00ebsh, dhe vazhdojm\u00eb me sukses, nj\u00eb zgjidhje t\u00eb kombinuar bazuar n\u00eb Nagios dhe Munin. Megjithat\u00eb, kjo kombinim ka disa disavantazhe, prandaj ne, ashtu si shum\u00eb t\u00eb tjer\u00eb, e exploatojm\u00eb aktivisht Zabbix<\/a><\/noindex>. N\u00eb k\u00ebt\u00eb artikull, ne do t\u00eb flasim p\u00ebr m\u00ebnyr\u00ebn si mund t\u00eb zgjidhet problemi me performanc\u00ebn me p\u00ebrpjekje minimale n\u00eb rritjen e numrit t\u00eb metrikave q\u00eb merren dhe rritjen e volumit t\u00eb baz\u00ebs s\u00eb t\u00eb dh\u00ebnave MySQL
\n<\/a><\/noindex><\/p>\n

Problemet e p\u00ebrdorimit t\u00eb baz\u00ebs s\u00eb t\u00eb dh\u00ebnave MySQL n\u00eb bashk\u00ebpunim me Zabbix<\/h3>\n

\nNd\u00ebrsa baza e t\u00eb dh\u00ebnave ishte e vog\u00ebl dhe numri i metrikave t\u00eb ruajtura n\u00eb t\u00eb ishte i vog\u00ebl, gjith\u00e7ka ishte shk\u00eblqyer. Procesi standard housekeeper, i cili aktivizonte vet\u00eb Zabbix Server, suksessh\u00ebm fshinte rekordet e vjetra nga baza e t\u00eb dh\u00ebnave, duke mos e lejuar at\u00eb t\u00eb rritej. Megjithat\u00eb, sapo numri i metrikave t\u00eb marrura u rrit dhe volumi i baz\u00ebs s\u00eb t\u00eb dh\u00ebnave arriti nj\u00eb madh\u00ebsi t\u00eb caktuar, gjith\u00e7ka u b\u00eb m\u00eb e v\u00ebshtir\u00eb. Housekeeper nuk mund t\u00eb p\u00ebrfundonte t\u00eb fshir\u00eb t\u00eb dh\u00ebnat brenda intervalit t\u00eb caktuar t\u00eb koh\u00ebs, dhe n\u00eb baz\u00ebn e t\u00eb dh\u00ebnave filluan t\u00eb mbeteshin t\u00eb dh\u00ebna t\u00eb vjetra. Gjat\u00eb pun\u00ebs s\u00eb housekeeper, kishte nj\u00eb ngarkes\u00eb t\u00eb shtuar n\u00eb Zabbix Server, e cila mund t\u00eb zgjaste p\u00ebr nj\u00eb koh\u00eb t\u00eb gjat\u00eb. U b\u00eb e qart\u00eb se duhej t\u00eb zgjidheshin ndonj\u00ebher\u00eb sfidat e krijuara.<\/p>\n

Ky \u00ebsht\u00eb nj\u00eb problem i njohur; praktikisht \u00e7do kush q\u00eb ka punuar me volumet e m\u00ebdha t\u00eb monitorimit n\u00eb Zabbix \u00ebsht\u00eb zhvilluar nga t\u00eb nj\u00ebjt\u00ebt. Zgjidhjet ishin gjithashtu disa: p\u00ebr shembull, z\u00ebvend\u00ebsimi i MySQL me PostgreSQL ose madje Elasticsearch, por zgjidhja m\u00eb e leht\u00eb dhe e provuar ishte kalimi n\u00eb partitizimin e tabelave q\u00eb ruajn\u00eb t\u00eb dh\u00ebnat e metrikave n\u00eb baz\u00ebn e t\u00eb dh\u00ebnave MySQL. Ne vendos\u00ebm t\u00eb ndjekim k\u00ebt\u00eb rrug\u00eb.<\/p>\n

Kalimi nga tabelat e zakonshme MySQL n\u00eb tabelat e partitizuara<\/h3>\n

\nZabbix \u00ebsht\u00eb mir\u00eb dokumentuar dhe tabelat ku ai ruan metrikat jan\u00eb t\u00eb njohura. K\u00ebto jan\u00eb tabelat: history<\/code>, ku ruhen vlerat float, history_str<\/code>, ku ruhen vlerat e shkurtra t\u00eb stringjeve, history_text<\/code>, ku ruhen vlerat e gjata t\u00eb teksteve dhe history_uint<\/code>, ku ruhen vlerat e numrave t\u00eb plot\u00eb. Ka gjithashtu nj\u00eb tabel\u00eb trends<\/code>, e cila ruan dinamik\u00ebn e ndryshimeve, por ne vendos\u00ebm ta l\u00ebm\u00eb t\u00eb paprekur, sepse madh\u00ebsia e saj \u00ebsht\u00eb e vog\u00ebl dhe m\u00eb von\u00eb do t'i kthehemi.<\/p>\n

N\u00eb p\u00ebrgjith\u00ebsi, ishte e qart\u00eb se cilat tabela duhej trajtuar. Ne vendos\u00ebm t\u00eb krijonim parti p\u00ebr \u00e7do jav\u00eb, p\u00ebrve\u00e7 jav\u00ebs s\u00eb fundit, mbi baz\u00ebn e numrave t\u00eb muajit, dmth. kat\u00ebr parti p\u00ebr muaj: nga 1 deri n\u00eb 7, nga 8 deri n\u00eb 14, nga 15 deri n\u00eb 21 dhe nga 22 deri n\u00eb 1 (t\u00eb muajit pasues). V\u00ebshtir\u00ebsia ishte se duhej t\u00eb transformonim tabelat q\u00eb na duheshin n\u00eb tabela t\u00eb partitizuara \"n\u00eb fluturim\", pa nd\u00ebrprer\u00eb pun\u00ebn e Zabbix Server dhe grumbullimin e metrikave.<\/p>\n

Si\u00e7 \u00ebsht\u00eb e \u00e7uditshme, struktura e dh\u00ebnave t\u00eb tabelave na ndihmoi n\u00eb k\u00ebt\u00eb. P\u00ebr shembull, tabela history <\/code>ka struktur\u00ebn e m\u00ebposhtme:<\/p>\n

`itemid` bigint(20) unsigned NOT NULL,\n`clock` int(11) NOT NULL DEFAULT '0',\n`value` double(16,4) NOT NULL DEFAULT '0.0000',\n`ns` int(11) NOT NULL DEFAULT '0',<\/code><\/pre>\n
\np\u00ebr m\u00eb tep\u00ebr<\/p>\n
KEY `history_1` (`itemid`,`clock`)<\/code><\/pre>\n
\nSi\u00e7 shohim, \u00e7do metrik\u00eb n\u00eb fund regjistrohet n\u00eb tabel\u00eb me dy fusha shum\u00eb t\u00eb r\u00ebnd\u00ebsishme dhe t\u00eb dobishme p\u00ebr ne itemid<\/b> dhe clock<\/b>. K\u00ebshtu, ne mund t\u00eb krijojm\u00eb nj\u00eb tabel\u00eb temporare, p\u00ebr shembull, me emrin history_tmp<\/code>, t\u00eb vendosim p\u00ebr t\u00eb partitizimin dhe pastaj t\u00eb transferojm\u00eb t\u00eb gjitha t\u00eb dh\u00ebnat nga tabela history<\/code>, dhe pas k\u00ebsaj ta ribllokojm\u00eb tabel\u00ebn history<\/code> n\u00eb history_old<\/code>, dhe tabel\u00ebn history_tmp<\/code> n\u00eb history<\/code>, pas s\u00eb cil\u00ebs do t\u00eb shtojm\u00eb t\u00eb dh\u00ebnat q\u00eb na kan\u00eb mbetur t\u00eb pashkruara nga history_old<\/code> n\u00eb history <\/code>dhe do ta fshijm\u00eb history_old<\/code>. Kjo mund t\u00eb b\u00ebhet n\u00eb m\u00ebnyr\u00eb t\u00eb sigurt, ne nuk do t\u00eb humbasim asgj\u00eb, sepse fushat e m\u00ebsip\u00ebrme itemid <\/b>dhe clock <\/b>sigurojn\u00eb lidhjen e metrik\u00ebs konkrete me nj\u00eb koh\u00eb t\u00eb caktuar dhe jo me ndonj\u00eb num\u00ebr rendor.<\/p>\n
Procedura e kalimit<\/h3>\n
<\/p>\n
Kujdes! \u00cbsht\u00eb shum\u00eb e d\u00ebshirueshme q\u00eb para fillimit t\u00eb ndonj\u00eb veprimi, t\u00eb b\u00ebni nj\u00eb kopje rezerv\u00eb t\u00eb plot\u00eb t\u00eb baz\u00ebs s\u00eb t\u00eb dh\u00ebnave. Ne t\u00eb gjith\u00eb jemi njer\u00ebz t\u00eb gjall\u00eb dhe mund t\u00eb b\u00ebjm\u00eb gabime n\u00eb komandat e shkruara, gj\u00eb q\u00eb mund t\u00eb \u00e7oj\u00eb n\u00eb humbjen e t\u00eb dh\u00ebnave. Po. nj\u00eb kopje rezerv\u00eb nuk do ta siguroj\u00eb maksimale t\u00eb aktualitetit, por \u00ebsht\u00eb m\u00eb mir\u00eb ta kemi nj\u00eb t\u00eb till\u00eb sesa asnj\u00eb.<\/p><\/blockquote>\n
 Pra, asgj\u00eb nuk p\u00ebrjashtohet dhe as nuk ndalohet. M\u00eb e r\u00ebnd\u00ebsishme \u00ebsht\u00eb q\u00eb n\u00eb serverin MySQL t\u00eb ket\u00eb mjaft hap\u00ebsir\u00eb t\u00eb lir\u00eb n\u00eb disk, dmth. q\u00eb p\u00ebr secil\u00ebn nga tabelat e p\u00ebrmendura m\u00eb sip\u00ebr history<\/code>, history_text<\/code>, history_str<\/code>, history_uint<\/code>, t\u00eb pakt\u00ebn t\u00eb ket\u00eb hap\u00ebsir\u00eb p\u00ebr krijimin e tabelave me sufixin \"_tmp\", duke marr\u00eb parasysh se ajo do t\u00eb ket\u00eb t\u00eb nj\u00ebjtin volum si tabela origjinale.<\/p>\n
Ne nuk do t\u00eb p\u00ebrshkruajm\u00eb gjith\u00e7ka disa her\u00eb p\u00ebr secil\u00ebn nga tabelat e p\u00ebrmendura dhe do t\u00eb shqyrtojm\u00eb t\u00eb gjitha n\u00eb shembuj t\u00eb vet\u00ebm nga ajo - tabela history<\/code>.<\/p>\n
Pra, krijojm\u00eb nj\u00eb tabel\u00eb t\u00eb zbraz\u00ebt history_tmp <\/code>n\u00eb baz\u00eb t\u00eb struktur\u00ebs s\u00eb tabel\u00ebs history<\/code>.<\/p>\n
Krijoni TABEL\u00cb `history_tmp` SI `history`;<\/code><\/pre>\n
\nKrijojm\u00eb partit\u00eb q\u00eb na duhen. P\u00ebr shembuj, le t\u00eb b\u00ebjm\u00eb k\u00ebt\u00eb p\u00ebr nj\u00eb muaj. \u00c7do parti krijohet mbi baz\u00ebn e rregullit t\u00eb partitizimit, t\u00eb cilin e bazojm\u00eb n\u00eb vler\u00ebn e fush\u00ebs clock<\/b>, t\u00eb cilin e krahasojm\u00eb me sh\u00ebnimin e koh\u00ebs:<\/p>\n
ALTER TABLE `history_tmp` PARTITION BY RANGE( clock ) (\nPARTITION p20190201 VALUES LESS THAN (UNIX_TIMESTAMP(\"2019-02-01 00:00:00\")),\nPARTITION p20190207 VALUES LESS THAN (UNIX_TIMESTAMP(\"2019-02-07 00:00:00\")),\nPARTITION p20190214 VALUES LESS THAN (UNIX_TIMESTAMP(\"2019-02-14 00:00:00\")),\nPARTITION p20190221 VALUES LESS THAN (UNIX_TIMESTAMP(\"2019-02-21 00:00:00\")),\nPARTITION p20190301 VALUES LESS THAN (UNIX_TIMESTAMP(\"2019-03-01 00:00:00\"))\n);<\/code><\/pre>\n
\nKy ky operator, shton\u00eb ndarjen p\u00ebr tabel\u00ebn q\u00eb kemi krijuar. history_tmp<\/code>. T\u00eb sqarojm\u00eb, q\u00eb t\u00eb dh\u00ebnat p\u00ebr t\u00eb cilat vlera e fush\u00ebs clock <\/b>\u00ebsht\u00eb m\u00eb e vog\u00ebl se \"2019-02-01 00:00:00\" do t\u00eb shkojn\u00eb n\u00eb ndarjen p20190201<\/i>, pastaj t\u00eb dh\u00ebnat p\u00ebr t\u00eb cilat vlera e fush\u00ebs clock<\/b> \u00ebsht\u00eb m\u00eb e madhe se \"2019-02-01 00:00:00\" por m\u00eb e vog\u00ebl se \"2019-02-07 00:00:00\" do t\u00eb shkojn\u00eb n\u00eb ndarjen p20190207 <\/i>etj.<\/p>\n
Sh\u00ebnim i r\u00ebnd\u00ebsish\u00ebm:<\/b> \u00c7far\u00eb do t\u00eb ndodh\u00eb n\u00ebse kemi t\u00eb dh\u00ebna n\u00eb tabel\u00ebn e ndar\u00eb p\u00ebr t\u00eb cilat vlera e fush\u00ebs clock do t\u00eb jet\u00eb m\u00eb e madhe ose e barabart\u00eb me \"2019-03-01 00:00:00\"? P\u00ebr shkak se p\u00ebr k\u00ebto t\u00eb dh\u00ebna nuk ka nj\u00eb ndarje t\u00eb p\u00ebrshtatshme, ato nuk do t\u00eb hyjn\u00eb n\u00eb tabel\u00eb dhe do t\u00eb humbasin. Prandaj, ju duhet t\u00eb mos harroni t\u00eb krijoni ndarjet shtes\u00eb n\u00eb koh\u00eb p\u00ebr t\u00eb shmangur humbjen e t\u00eb dh\u00ebnave (p\u00ebr t\u00eb cilat flitet m\u00eb posht\u00eb).<\/p><\/blockquote>\n
 Pra, tabela p\u00ebrkohshme \u00ebsht\u00eb p\u00ebrgatitur. Po ngarkohet t\u00eb dh\u00ebnat. Procesi mund t\u00eb marr\u00eb nj\u00eb koh\u00eb t\u00eb gjat\u00eb, por p\u00ebr fat t\u00eb mir\u00eb nuk bllokon asnj\u00eb k\u00ebrkes\u00eb tjet\u00ebr, k\u00ebshtu q\u00eb \u00ebsht\u00eb e nevojshme thjesht t\u00eb b\u00ebni durim:<\/p>\n
INSERT IGNORE INTO `history_tmp` SELECT * FROM history;<\/code><\/pre>\n
\nFjal\u00ebky\u00e7i IGNORE gjat\u00eb ngarkes\u00ebs fillestare nuk \u00ebsht\u00eb i detyruesh\u00ebm, pasi t\u00eb dh\u00ebnat nuk ekzistojn\u00eb ende n\u00eb tabel\u00eb, megjithat\u00eb do t'ju nevojitet gjat\u00eb ngarkesave shtes\u00eb t\u00eb t\u00eb dh\u00ebnave. P\u00ebr m\u00eb tep\u00ebr, ai mund t\u00eb jet\u00eb i dobish\u00ebm n\u00ebse ju keni ndalur k\u00ebt\u00eb proces gjat\u00eb ngarkes\u00ebs dhe duhet ta filloni p\u00ebrs\u00ebri.<\/p>\n
Pra, pas nj\u00eb kohe (ndoshta edhe disa or\u00eb), ngarkesa e par\u00eb e t\u00eb dh\u00ebnave \u00ebsht\u00eb p\u00ebrfunduar. Si\u00e7 e kuptoni, tani tabela history_tmp <\/code>p\u00ebrmban jo t\u00eb gjitha t\u00eb dh\u00ebnat nga tabela history<\/code>, por vet\u00ebm ato q\u00eb ishin n\u00eb t\u00eb n\u00eb momentin e fillimit t\u00eb ekzekutimit t\u00eb k\u00ebrkes\u00ebs. K\u00ebtu ju keni nj\u00eb zgjedhje: ose b\u00ebni nj\u00eb kalim tjet\u00ebr (n\u00ebse procesi i ngarkes\u00ebs ka zgjatur shum\u00eb), ose kaloni menj\u00ebher\u00eb n\u00eb rinovimin e tabelave, p\u00ebr t\u00eb cilin \u00ebsht\u00eb folur m\u00eb lart. Le t\u00eb flasim fillimisht p\u00ebr kalimin e dyt\u00eb. P\u00ebr t\u00eb filluar, na nevojitet t\u00eb kuptojm\u00eb koh\u00ebn e regjistrimit t\u00eb fundit t\u00eb futur n\u00eb history_tmp<\/code>:<\/p>\n
SELECT max(clock) FROM history_tmp;<\/code><\/pre>\n
\nSupozoni se keni marr\u00eb: 1551045645<\/b>. Tani p\u00ebrdorni vler\u00ebn e marr\u00eb p\u00ebr kalimin e dyt\u00eb t\u00eb ngarkes\u00ebs s\u00eb t\u00eb dh\u00ebnave:<\/p>\n
INSERT IGNORE INTO `history_tmp` SELECT * FROM history WHERE clock>=1551045645;<\/code><\/pre>\n
\nKy kalim duhet t\u00eb p\u00ebrfundoj\u00eb ndjesh\u00ebm m\u00eb shpejt. Por n\u00ebse kalimi i par\u00eb ka zgjatur or\u00eb, dhe kalimi i dyt\u00eb gjithashtu ka marr\u00eb nj\u00eb koh\u00eb t\u00eb gjat\u00eb, ndoshta do t\u00eb ishte e arsyeshme t\u00eb b\u00ebni edhe nj\u00eb kalim t\u00eb tret\u00eb, i cili ekzekutohet krejt\u00ebsisht si kalimi i dyt\u00eb.<\/p>\n
N\u00eb fund, ne p\u00ebrs\u00ebri realizojm\u00eb operacionin e marrjes s\u00eb koh\u00ebs s\u00eb regjistrimit t\u00eb fundit t\u00eb futur n\u00eb history_tmp<\/code>, duke ekzekutuar:<\/p>\n
SELECT max(clock) FROM history_tmp;<\/code><\/pre>\n
\nSupozoni se keni marr\u00eb 1551085645<\/b>. Ruani k\u00ebt\u00eb vler\u00eb \u2014 do na nevojitet p\u00ebr ngarkes\u00ebn shtes\u00eb.<\/p>\n
Dhe tani, kur ngarkesa fillestare e t\u00eb dh\u00ebnave n\u00eb history_tmp <\/code>ka p\u00ebrfunduar, ne kalojm\u00eb n\u00eb rinovimin e tabelave:<\/p>\n
BEGIN;\nRENAME TABLE history TO history_old;\nRENAME TABLE history_tmp TO history;\nCOMMIT;<\/code><\/pre>\n
\nE kemi paraqitur k\u00ebt\u00eb bllok si nj\u00eb transaksion, p\u00ebr t\u00eb shmangur momentin e futjes s\u00eb t\u00eb dh\u00ebnave n\u00eb nj\u00eb tabel\u00eb q\u00eb nuk ekziston, sepse pas RENAME t\u00eb par\u00eb deri n\u00eb momentin e ekzekutimit t\u00eb RENAME t\u00eb dyt\u00eb, tabela history <\/code>nuk do t\u00eb ekzistoj\u00eb. Por edhe n\u00ebse midis operacioneve RENAME n\u00eb tabel\u00eb history <\/code>kan\u00eb ardhur disa t\u00eb dh\u00ebna, dhe tabela vet\u00eb ende nuk do t\u00eb ekzistoj\u00eb (p\u00ebr shkak t\u00eb rinovimit), do t\u00eb marrim nj\u00eb num\u00ebr t\u00eb vog\u00ebl gabimesh t\u00eb futjes, t\u00eb cilat mund t\u00eb injorohen (ne kemi monitorim, jo bank\u00eb).<\/p>\n
Tani kemi nj\u00eb tabel\u00eb t\u00eb re history<\/code> me ndarjen, por mungojn\u00eb t\u00eb dh\u00ebnat q\u00eb jan\u00eb marr\u00eb gjat\u00eb kalimit t\u00eb fundit t\u00eb futjes s\u00eb t\u00eb dh\u00ebnave n\u00eb tabel\u00eb history_tmp<\/code>. Por k\u00ebto t\u00eb dh\u00ebna i kemi n\u00eb tabel\u00ebn history_old <\/code>dhe tani do t'i ngarkojm\u00eb aty. P\u00ebr k\u00ebt\u00eb, do t\u00eb na nevojitet vlera e ruajtur 1551085645. Pse e ruam k\u00ebt\u00eb vler\u00eb dhe jo t\u00eb p\u00ebrdorim koh\u00ebn maksimale t\u00eb ngarkes\u00ebs nga tabela aktuale history<\/code>? \u041f\u043e\u0442\u043e\u043c\u0443 \u0447\u0442\u043e \u043d\u043e\u0432\u044b\u0435 \u0434\u0430\u043d\u043d\u044b\u0435 \u0443\u0436\u0435 \u0432 \u043d\u0435\u0451 \u043f\u043e\u0441\u0442\u0443\u043f\u0430\u044e\u0442 \u0438 \u043c\u044b \u043f\u043e\u043b\u0443\u0447\u0438\u043c \u043d\u0435\u0432\u0435\u0440\u043d\u043e\u0435 \u0432\u0440\u0435\u043c\u044f. \u0418\u0442\u0430\u043a, \u0434\u043e\u0437\u0430\u043b\u0438\u0432\u0430\u0435\u043c \u0434\u0430\u043d\u043d\u044b\u0435:<\/p>\n
INSERT IGNORE INTO `history` SELECT * FROM history_old WHERE clock>=1551045645;<\/code><\/pre>\n
\nPas p\u00ebrfundimit t\u00eb k\u00ebsaj operacioni, tabela e re, e ndar\u00eb, history <\/code>ka t\u00eb gjitha t\u00eb dh\u00ebnat q\u00eb ishin n\u00eb t\u00eb vjet\u00ebr, plus ato q\u00eb kan\u00eb ardhur tashm\u00eb pas rinovimit t\u00eb tabel\u00ebs. Tabela history_old <\/code>nuk na nevojitet m\u00eb. Mund ta fshijm\u00eb menj\u00ebher\u00eb, ose mund t\u00eb b\u00ebjm\u00eb nj\u00eb kopje rezerv\u00eb para fshirjes (n\u00ebse jeni paranojak).<\/p>\n
Procesi i p\u00ebrshkruar m\u00eb sip\u00ebr duhet t\u00eb p\u00ebrs\u00ebritet p\u00ebr tabelat history_str<\/code>, history_text <\/code>dhe history_uint<\/code>.<\/p>\n
\u00c7far\u00eb duhet t\u00eb korrigjojm\u00eb n\u00eb konfigurimin e Zabbix Server<\/h3>\n
\nTani mir\u00ebmbajtja e baz\u00ebs s\u00eb t\u00eb dh\u00ebnave n\u00eb pjes\u00ebn e historis\u00eb s\u00eb t\u00eb dh\u00ebnave bie mbi shpatullat tona. Kjo do t\u00eb thot\u00eb q\u00eb Zabbix nuk duhet m\u00eb t\u00eb fshij\u00eb t\u00eb dh\u00ebnat e vjetra \u2014 ne do t\u00eb merremi me k\u00ebt\u00eb vet\u00eb. P\u00ebr t\u00eb parandaluar q\u00eb Zabbix Server t\u00eb p\u00ebrpiqet t\u00eb pastroj\u00eb t\u00eb dh\u00ebnat vet\u00eb, duhet t\u00eb hyni n\u00eb nd\u00ebrfaqen web t\u00eb Zabbix, t\u00eb zgjidhni n\u00eb menu \"Administrim\", pastaj n\u00ebnmenu \"T\u00eb P\u00ebrgjithshme\", pastaj n\u00eb list\u00ebn e shfaqur nj\u00ebk\u00ebnd me djathtas t\u00eb zgjidhni \"Pastrimi i historis\u00eb\". N\u00eb faqen q\u00eb shfaqet, hiqni t\u00eb gjitha shenjat p\u00ebr grupin \"Historik\" dhe klikoni n\u00eb butonin \"Rifresko\". Kjo do t\u00eb parandaloj\u00eb pastrimin e panevojsh\u00ebm t\u00eb tabelave history*<\/code> n\u00ebp\u00ebrmjet housekeeper.<\/p>\n
Vini re n\u00eb k\u00ebt\u00eb faqe grupin \"Dinamikat e ndryshimeve\". Ky \u00ebsht\u00eb inxhinierias trends<\/code>, t\u00eb cil\u00ebn premtuam se do t\u00eb kthehemi. N\u00ebse po ashtu \u00ebsht\u00eb b\u00ebr\u00eb shum\u00eb e madhe dhe k\u00ebrkon ndarje, hiqni shenjat nga kjo grup dhe m\u00eb pas trajtoni k\u00ebt\u00eb tabel\u00eb ashtu si\u00e7 \u00ebsht\u00eb b\u00ebr\u00eb p\u00ebr tabelat e tjera. history*<\/code>.<\/p>\n
Mir\u00ebmbajtja e m\u00ebtejshme e baz\u00ebs s\u00eb t\u00eb dh\u00ebnave<\/h3>\n
\nSi\u00e7 u shkrua m\u00eb par\u00eb, p\u00ebr funksionimin normal t\u00eb tabelave t\u00eb ndara, \u00ebsht\u00eb e nevojshme t\u00eb krijoni n\u00eb koh\u00eb ndarjet. Kjo mund t\u00eb b\u00ebhet k\u00ebshtu:<\/p>\n
ALTER TABLE `history` ADD PARTITION (PARTITION p20190307 VALUES LESS THAN (UNIX_TIMESTAMP(\"2019-03-07 00:00:00\")));<\/code><\/pre>\n
\nP\u00ebr m\u00eb tep\u00ebr, pasi krijuam tabela t\u00eb ndara dhe ndaluam Zabbix Server q\u00eb t'i pastroj\u00eb ato, eliminimi i t\u00eb dh\u00ebnave t\u00eb vjetra tani \u00ebsht\u00eb p\u00ebrgjegj\u00ebsia jon\u00eb. Fatmir\u00ebsisht, k\u00ebtu nuk ka asnj\u00eb problem. Kjo b\u00ebhet thjesht duke hequr at\u00eb ndarje, t\u00eb dh\u00ebnat e s\u00eb cil\u00ebs nuk na duhen m\u00eb. <\/p>\n
P\u00ebr shembull:<\/p>\n
ALTER TABLE history DROP PARTITION p20190201;<\/code><\/pre>\n
\nNdryshe nga operator\u00ebt DELETE FROM me specifikimin e intervalit t\u00eb datave, DROP PARTITION ekzekutohet brenda disa sekondave, pa ngarkuar aspak server<\/a> dhe po ashtu funksionon pa probleme n\u00eb rastin e p\u00ebrdorimit n\u00eb replikimin e MySQL.<\/p>\n
P\u00ebrfundimi<\/h3>\n
\nZgjidhja e p\u00ebrshkruar \u00ebsht\u00eb provuar me koh\u00ebn. V\u00ebllimi i t\u00eb dh\u00ebnave po rritet, por nuk \u00ebsht\u00eb v\u00ebn\u00eb re ndonj\u00eb ngadal\u00ebsim t\u00eb duksh\u00ebm t\u00eb performanc\u00ebs.
\n
Burimi: habr.com<\/a><\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"
\u0414\u043b\u044f \u043c\u043e\u043d\u0438\u0442\u043e\u0440\u0438\u043d\u0433\u0430 \u0441\u0435\u0440\u0432\u0435\u0440\u043e\u0432 \u0438 \u0441\u043b\u0443\u0436\u0431 \u0443 \u043d\u0430\u0441 \u0434\u0430\u0432\u043d\u043e, \u0438 \u0432\u0441\u0435 \u0435\u0449\u0435 \u0443\u0441\u043f\u0435\u0448\u043d\u043e, \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u0435\u0442\u0441\u044f \u043a\u043e\u043c\u0431\u0438\u043d\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u043e\u0435 \u0440\u0435\u0448\u0435\u043d\u0438\u0435 \u043d\u0430 \u0431\u0430\u0437\u0435 Nagios \u0438 Munin. \u041e\u0434\u043d\u0430\u043a\u043e \u044d\u0442\u0430 \u0441\u0432\u044f\u0437\u043a\u0430 \u0438\u043c\u0435\u0435\u0442 \u0440\u044f\u0434 \u043d\u0435\u0434\u043e\u0441\u0442\u0430\u0442\u043a\u043e\u0432, \u043f\u043e\u044d\u0442\u043e\u043c\u0443 \u043c\u044b, \u043a\u0430\u043a \u0438 \u043c\u043d\u043e\u0433\u0438\u0435, \u0430\u043a\u0442\u0438\u0432\u043d\u043e \u044d\u043a\u0441\u043f\u043b\u0443\u0430\u0442\u0438\u0440\u0443\u0435\u043c Zabbix. \u0412 \u044d\u0442\u043e\u0439 \u0441\u0442\u0430\u0442\u044c\u0435 \u043c\u044b \u0440\u0430\u0441\u0441\u043a\u0430\u0436\u0435\u043c \u043e \u0442\u043e\u043c, \u043a\u0430\u043a \u043c\u0438\u043d\u0438\u043c\u0430\u043b\u044c\u043d\u044b\u043c\u0438 \u0443\u0441\u0438\u043b\u0438\u044f\u043c\u0438 \u043c\u043e\u0436\u043d\u043e \u0440\u0435\u0448\u0438\u0442\u044c \u043f\u0440\u043e\u0431\u043b\u0435\u043c\u0443 \u0441 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u044c\u044e \u043f\u0440\u0438 \u0443\u0432\u0435\u043b\u0438\u0447\u0435\u043d\u0438\u0438 \u0447\u0438\u0441\u043b\u0430 \u0441\u043d\u0438\u043c\u0430\u0435\u043c\u044b\u0445 \u043c\u0435\u0442\u0440\u0438\u043a \u0438 […]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-53966","post","type-post","status-publish","format-standard","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\n","aioseo_head_json":{"title":"\ud83e\udd47P\u00ebrdorimi i ndarjes n\u00eb MySQL p\u00ebr Zabbix me nj\u00eb num\u00ebr t\u00eb madh objektiv\u00ebsh t\u00eb monitorimit | ProHoster","description":"P\u00ebr monitorimin e server\u00ebve dhe sh\u00ebrbimeve, ne p\u00ebrdorim prej koh\u00ebsh nj\u00eb zgjidhje t\u00eb kombinuar bazuar n\u00eb Nagios dhe Munin, dhe kjo vazhdon t\u00eb jet\u00eb e suksesshme.","canonical_url":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/ispolzovanie-partitsionirovaniya-v-mysql-dlya-zabbix-s-bolshim-kolichestvom-obektov-monitoringa","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"sq_AL","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u0418\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u0435 \u043f\u0430\u0440\u0442\u0438\u0446\u0438\u043e\u043d\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f \u0432 MySQL \u0434\u043b\u044f Zabbix \u0441 \u0431\u043e\u043b\u044c\u0448\u0438\u043c \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e\u043c \u043e\u0431\u044a\u0435\u043a\u0442\u043e\u0432 \u043c\u043e\u043d\u0438\u0442\u043e\u0440\u0438\u043d\u0433\u0430 | ProHoster","og:description":"\u0414\u043b\u044f \u043c\u043e\u043d\u0438\u0442\u043e\u0440\u0438\u043d\u0433\u0430 \u0441\u0435\u0440\u0432\u0435\u0440\u043e\u0432 \u0438 \u0441\u043b\u0443\u0436\u0431 \u0443 \u043d\u0430\u0441 \u0434\u0430\u0432\u043d\u043e, \u0438 \u0432\u0441\u0435 \u0435\u0449\u0435 \u0443\u0441\u043f\u0435\u0448\u043d\u043e, \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u0435\u0442\u0441\u044f \u043a\u043e\u043c\u0431\u0438\u043d\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u043e\u0435 \u0440\u0435\u0448\u0435\u043d\u0438\u0435 \u043d\u0430 \u0431\u0430\u0437\u0435 Nagios \u0438 Munin.","og:url":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/ispolzovanie-partitsionirovaniya-v-mysql-dlya-zabbix-s-bolshim-kolichestvom-obektov-monitoringa","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2019-12-13T21:00:00+00:00","article:modified_time":"2020-02-18T11:01:55+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"53966","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":"2026-01-24 09:31:21","breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 20:14:29","updated":"2026-01-24 09:31:21","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts\/53966","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/comments?post=53966"}],"version-history":[{"count":1,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts\/53966\/revisions"}],"predecessor-version":[{"id":172868,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts\/53966\/revisions\/172868"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/media?parent=53966"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/categories?post=53966"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/tags?post=53966"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}