{"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\/cs\/blog\/administrirovanie\/ispolzovanie-partitsionirovaniya-v-mysql-dlya-zabbix-s-bolshim-kolichestvom-obektov-monitoringa","title":{"rendered":"Pou\u017eit\u00ed partitioningu v MySQL pro Zabbix s velk\u00fdm mno\u017estv\u00edm monitorovac\u00edch objekt\u016f","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Pro monitorov\u00e1n\u00ed server\u016f a slu\u017eeb jsme dlouho, a st\u00e1le \u00fasp\u011b\u0161n\u011b, pou\u017e\u00edvali kombinovan\u00e9 \u0159e\u0161en\u00ed zalo\u017een\u00e9 na Nagios a Munin. Tato kombinace v\u0161ak m\u00e1 \u0159adu nedostatk\u016f, a proto my, jako mnoz\u00ed, aktivn\u011b vyu\u017e\u00edv\u00e1me Zabbix<\/a><\/noindex>. V tomto \u010dl\u00e1nku se pod\u011bl\u00edme o to, jak lze s minim\u00e1ln\u00edm \u00fasil\u00edm vy\u0159e\u0161it probl\u00e9m v\u00fdkonnosti p\u0159i zvy\u0161uj\u00edc\u00edm se po\u010dtu sledovan\u00fdch metrik a r\u016fstu objem\u016f datab\u00e1ze MySQL.
\n<\/a><\/noindex><\/p>\n

Probl\u00e9my pou\u017e\u00edv\u00e1n\u00ed datab\u00e1ze MySQL ve spolupr\u00e1ci se Zabbixem<\/h3>\n

\nDokud byla datab\u00e1ze mal\u00e1 a po\u010det v n\u00ed ulo\u017een\u00fdch metr\u016f mal\u00fd, bylo v\u0161e skv\u011bl\u00e9. Standardn\u00ed proces housekeeper, kter\u00fd spou\u0161t\u00ed s\u00e1m Zabbix Server, \u00fasp\u011b\u0161n\u011b odstra\u0148oval zastaral\u00e9 z\u00e1znamy z datab\u00e1ze, \u010d\u00edm\u017e nedovolil jej\u00edmu r\u016fstu. Jakmile v\u0161ak po\u010det sledovan\u00fdch metrik vzrostl a objem datab\u00e1ze dos\u00e1hl ur\u010dit\u00e9 velikosti, situace se zhor\u0161ila. Housekeeper p\u0159estal st\u00edhat odstra\u0148ovat data ve stanoven\u00e9m \u010dase, v datab\u00e1zi z\u016fst\u00e1vala star\u00e1 data. B\u011bhem \u010dinnosti housekeepera byla na Zabbix Server vyv\u00edjena zv\u00fd\u0161en\u00e1 z\u00e1t\u011b\u017e, kter\u00e1 mohla trvat dlouhou dobu. Bylo jasn\u00e9, \u017ee je t\u0159eba n\u011bjak vy\u0159e\u0161it vzniklou situaci.<\/p>\n

To je zn\u00e1m\u00fd probl\u00e9m, s n\u00edm\u017e se prakticky ka\u017ed\u00fd, kdo pracoval s velk\u00fdmi objemy monitorov\u00e1n\u00ed na Zabbix, setkal. \u0158e\u0161en\u00ed bylo n\u011bkolik: nap\u0159\u00edklad p\u0159echod z MySQL na PostgreSQL nebo dokonce Elasticsearch, ale nejjednodu\u0161\u0161\u00edm a osv\u011bd\u010den\u00fdm \u0159e\u0161en\u00edm byl p\u0159echod k partitionov\u00e1n\u00ed tabulek, kter\u00e9 ukl\u00e1daj\u00ed data metrik v datab\u00e1zi MySQL. Rozhodli jsme se j\u00edt pr\u00e1v\u011b touto cestou.<\/p>\n

P\u0159echod od obvykl\u00fdch tabulek MySQL k partitionovan\u00fdm<\/h3>\n

\nZabbix je dob\u0159e zdokumentov\u00e1n a tabulky, ve kter\u00fdch ukl\u00e1d\u00e1 metriky, jsou zn\u00e1my. Jsou to tabulky: history<\/code>, kde se ukl\u00e1daj\u00ed float hodnoty, history_str<\/code>, kde se ukl\u00e1daj\u00ed kr\u00e1tk\u00e9 \u0159et\u011bzcov\u00e9 hodnoty, history_text<\/code>, kde se ukl\u00e1daj\u00ed dlouh\u00e9 textov\u00e9 hodnoty a history_uint<\/code>, kde se ukl\u00e1daj\u00ed celo\u010d\u00edseln\u00e9 hodnoty. Je tak\u00e9 tabulka trends<\/code>, kter\u00e1 ukl\u00e1d\u00e1 dynamiku zm\u011bn, ale tu jsme se rozhodli nedot\u00fdkat, proto\u017ee jej\u00ed velikost je mal\u00e1 a pozd\u011bji se k n\u00ed vr\u00e1t\u00edme.<\/p>\n

Celkov\u011b bylo jasn\u00e9, kter\u00e9 tabulky je pot\u0159eba zpracovat. Rozhodli jsme se vytvo\u0159it partitiony na ka\u017ed\u00fd t\u00fdden, s v\u00fdjimkou posledn\u00edho, na z\u00e1klad\u011b \u010d\u00edsel m\u011bs\u00edce, tj. \u010dty\u0159i partitiony za m\u011bs\u00edc: od 1. do 7., od 8. do 14., od 15. do 21. a od 22. do 1. (n\u00e1sleduj\u00edc\u00edho m\u011bs\u00edce). Obt\u00ed\u017enost spo\u010d\u00edvala v tom, \u017ee bylo pot\u0159eba p\u0159ev\u00e9st po\u017eadovan\u00e9 tabulky na partitionovan\u00e9 \u201eza b\u011bhu\u201c, ani\u017e by do\u0161lo k p\u0159eru\u0161en\u00ed pr\u00e1ce Zabbix Serveru a shroma\u017e\u010fov\u00e1n\u00ed metrik.<\/p>\n

Jak ne\u010dekan\u00e9, pomoc p\u0159i\u0161la sama od struktury dat tabulek. Nap\u0159\u00edklad tabulka history <\/code>m\u00e1 n\u00e1sleduj\u00edc\u00ed strukturu:<\/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\u0159i\u010dem\u017e<\/p>\n
KEY `history_1` (`itemid`,`clock`)<\/code><\/pre>\n
\nJak vid\u00edme, ka\u017ed\u00e1 metrika je nakonec zaznamen\u00e1na v tabulce se dv\u011bma velmi d\u016fle\u017eit\u00fdmi a v\u00fdhodn\u00fdmi pro n\u00e1s poli itemid<\/b> a clock<\/b>. Tak m\u016f\u017eeme vytvo\u0159it do\u010dasnou tabulku, nap\u0159\u00edklad s n\u00e1zvem history_tmp<\/code>, nastavit pro ni partitioning a pot\u00e9 do n\u00ed p\u0159en\u00e9st v\u0161echna data z tabulky history<\/code>, pot\u00e9 p\u0159ejmenovat tabulku history<\/code> v history_old<\/code>, a tabulku history_tmp<\/code> v history<\/code>, po kter\u00e9m dopln\u00edme ta data, kter\u00e1 n\u00e1m chyb\u011bla z history_old<\/code> v history <\/code>a odstranit history_old<\/code>. M\u016f\u017eeme to ud\u011blat zcela bezpe\u010dn\u011b, nic neztrat\u00edme, proto\u017ee v\u00fd\u0161e uveden\u00e1 pole itemid <\/b>a clock <\/b>zaji\u0161\u0165uj\u00ed p\u0159idru\u017een\u00ed konkr\u00e9tn\u00ed metriky k ur\u010dit\u00e9mu \u010dasu, nikoli k n\u011bjak\u00e9mu po\u0159adov\u00e9mu \u010d\u00edslu.<\/p>\n
Samotn\u00fd p\u0159echod<\/h3>\n
<\/p>\n
Pozor! Je velmi \u017e\u00e1douc\u00ed, p\u0159ed zah\u00e1jen\u00edm jak\u00fdchkoli akc\u00ed, prov\u00e9st \u00faplnou z\u00e1lohu datab\u00e1ze. Jsme v\u0161ichni \u017eiv\u00ed lid\u00e9 a m\u016f\u017eeme ud\u011blat chybu p\u0159i zad\u00e1v\u00e1n\u00ed p\u0159\u00edkaz\u016f, co\u017e m\u016f\u017ee v\u00e9st ke ztr\u00e1t\u011b dat. Ano, z\u00e1loha nezajist\u00ed maxim\u00e1ln\u00ed aktu\u00e1lnost, ale je lep\u0161\u00ed m\u00edt n\u011bjakou ne\u017e \u017e\u00e1dnou.<\/p><\/blockquote>\n
 Tak\u017ee nic nevyp\u00edn\u00e1me a nezastavujeme. Hlavn\u00ed je, aby na samotn\u00e9m MySQL serveru bylo dostate\u010dn\u00e9 mno\u017estv\u00ed voln\u00e9ho m\u00edsta na disku, tj. aby pro ka\u017edou z v\u00fd\u0161e uveden\u00fdch tabulek history<\/code>, history_text<\/code>, history_str<\/code>, history_uint<\/code>, bylo alespo\u0148 dost m\u00edsta na vytvo\u0159en\u00ed tabulky s p\u0159\u00edponou \u201e_tmp\u201c, p\u0159i\u010dem\u017e bude m\u00edt objem stejn\u00fd jako p\u016fvodn\u00ed tabulka.<\/p>\n
Nebudeme popisovat v\u0161e n\u011bkolikr\u00e1t pro ka\u017edou z uveden\u00fdch tabulek a zam\u011b\u0159\u00edme se pouze na jeden p\u0159\u00edklad \u2014 tabulku history<\/code>.<\/p>\n
Tak\u017ee vytv\u00e1\u0159\u00edme pr\u00e1zdnou tabulku history_tmp <\/code>na z\u00e1klad\u011b struktury tabulky history<\/code>.<\/p>\n
CREATE TABLE `history_tmp` LIKE `history`;<\/code><\/pre>\n
\nVytv\u00e1\u0159\u00edme pot\u0159ebn\u00e9 partice. Pro p\u0159\u00edklad to ud\u011bl\u00e1me na m\u011bs\u00edc. Ka\u017ed\u00e1 partice se vytv\u00e1\u0159\u00ed na z\u00e1klad\u011b pravidla rozd\u011blen\u00ed na hodnotu pole clock<\/b>, kter\u00e9 porovn\u00e1v\u00e1me s \u010dasov\u00fdm raz\u00edtkem:<\/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
\nTento p\u0159\u00edkaz p\u0159id\u00e1v\u00e1 rozd\u011blen\u00ed pro n\u00e1mi vytvo\u0159enou tabulku history_tmp<\/code>. Ujasn\u011bme, \u017ee data, jejich\u017e hodnota pole clock <\/b>je men\u0161\u00ed ne\u017e \u201e2019-02-01 00:00:00\u201c, se dostanou do partice p20190201<\/i>, pot\u00e9 data, jejich\u017e hodnota pole clock<\/b> je v\u011bt\u0161\u00ed ne\u017e \u201e2019-02-01 00:00:00\u201c, ale men\u0161\u00ed ne\u017e \u201e2019-02-07 00:00:00\u201c, se dostanou do partice p20190207 <\/i>a tak d\u00e1le.<\/p>\n
D\u016fle\u017eit\u00e1 pozn\u00e1mka:<\/b> Co se stane, pokud se v na\u0161\u00ed rozd\u011blen\u00e9 tabulce objev\u00ed data, jejich\u017e hodnota pole clock bude v\u011bt\u0161\u00ed nebo rovna \u201e2019-03-01 00:00:00\u201c? Proto\u017ee pro tato data neexistuje vhodn\u00e1 partice, nebudou za\u0159azena do tabulky a budou ztracena. Proto je nutn\u00e9 nezapom\u00ednat v\u010das vytv\u00e1\u0159et dal\u0161\u00ed partitce, aby se p\u0159ede\u0161lo t\u011bmto ztr\u00e1t\u00e1m dat (o tom, co se stane n\u00ed\u017ee).<\/p><\/blockquote>\n
 Tak\u017ee, do\u010dasn\u00e1 tabulka je p\u0159ipravena. Nahr\u00e1v\u00e1me data. Proces m\u016f\u017ee trvat docela dlouho, ale na\u0161t\u011bst\u00ed nezablokuje \u017e\u00e1dn\u00e9 jin\u00e9 dotazy, tak\u017ee je pot\u0159eba jen po\u0159\u00eddit si trp\u011blivost:<\/p>\n
INSERT IGNORE INTO `history_tmp` SELECT * FROM history;<\/code><\/pre>\n
\nKl\u00ed\u010dov\u00e9 slovo IGNORE p\u0159i prvotn\u00edm nahr\u00e1v\u00e1n\u00ed nen\u00ed povinn\u00e9, proto\u017ee v tabulce stejn\u011b \u017e\u00e1dn\u00e1 data nejsou, ale to budete pot\u0159ebovat p\u0159i dodate\u010dn\u00e9m nahr\u00e1v\u00e1n\u00ed dat. Krom\u011b toho se m\u016f\u017ee uk\u00e1zat u\u017eite\u010dn\u00e9, pokud jste museli b\u011bhem nahr\u00e1v\u00e1n\u00ed p\u0159eru\u0161it tento proces a za\u010d\u00edt znovu.<\/p>\n
Tak\u017ee po n\u011bjak\u00e9m \u010dase (mo\u017en\u00e1 dokonce n\u011bkolika hodin\u00e1ch) prob\u011bhlo prvn\u00ed nahr\u00e1v\u00e1n\u00ed dat. Jak ch\u00e1pete, nyn\u00ed tabulka history_tmp <\/code>obsahuje ne v\u0161echna data z tabulky history<\/code>, ale pouze ta, kter\u00e1 byla v n\u00ed v okam\u017eiku zah\u00e1jen\u00ed prov\u00e1d\u011bn\u00ed dotazu. Tady m\u00e1te na v\u00fdb\u011br: bu\u010f provedeme je\u0161t\u011b jeden pr\u016fchod (pokud proces nahr\u00e1v\u00e1n\u00ed trval dlouho), nebo hned p\u0159ejdeme k p\u0159ejmenov\u00e1n\u00ed tabulek, o kter\u00e9m se hovo\u0159ilo v\u00fd\u0161e. Nejprve se pod\u00edvejme na druh\u00fd pr\u016fchod. Nejd\u0159\u00edve mus\u00edme zjistit \u010das posledn\u00edho vlo\u017een\u00e9ho z\u00e1znamu v history_tmp<\/code>:<\/p>\n
SELECT max(clock) FROM history_tmp;<\/code><\/pre>\n
\nP\u0159edpokl\u00e1dejme, \u017ee jste obdr\u017eeli: 1551045645<\/b>Nyn\u00ed pou\u017eijeme z\u00edskanou hodnotu p\u0159i druh\u00e9m pr\u016fchodu vkl\u00e1d\u00e1n\u00ed dat:<\/p>\n
INSERT IGNORE INTO `history_tmp` SELECT * FROM history WHERE clock>=1551045645;<\/code><\/pre>\n
\nTento pr\u016fchod by m\u011bl skon\u010dit mnohem rychleji. Pokud v\u0161ak prvn\u00ed pr\u016fchod trval hodiny a druh\u00fd tak\u00e9 trv\u00e1 dlouho, mo\u017en\u00e1 bude spr\u00e1vn\u00e9 prov\u00e9st i t\u0159et\u00ed pr\u016fchod, kter\u00fd se prov\u00e1d\u00ed p\u0159esn\u011b stejn\u011b jako druh\u00fd.<\/p>\n
Nakonec znovu provedeme operaci z\u00edsk\u00e1n\u00ed \u010dasu posledn\u00edho vlo\u017een\u00ed z\u00e1znamu do history_tmp<\/code>, a to proveden\u00edm:<\/p>\n
SELECT max(clock) FROM history_tmp;<\/code><\/pre>\n
\nP\u0159edstavme si, \u017ee jste z\u00edskali 1551085645<\/b>. Ulo\u017ete tuto hodnotu \u2014 budeme ji pot\u0159ebovat pro dodate\u010dn\u00e9 vkl\u00e1d\u00e1n\u00ed.<\/p>\n
A nyn\u00ed vlastn\u011b, kdy\u017e prim\u00e1rn\u00ed vkl\u00e1d\u00e1n\u00ed dat do history_tmp <\/code>skon\u010dilo, p\u0159istupujeme k p\u0159ejmenov\u00e1n\u00ed tabulek:<\/p>\n
BEGIN;\nRENAME TABLE history TO history_old;\nRENAME TABLE history_tmp TO history;\nCOMMIT;<\/code><\/pre>\n
\nTento blok jsme zform\u00e1tovali jako jednu transakci, abychom se vyhnuli situaci, kdy se data vkl\u00e1daj\u00ed do neexistuj\u00edc\u00ed tabulky, proto\u017ee po prvn\u00edm RENAME do doby proveden\u00ed druh\u00e9ho RENAME, tabulka history <\/code>existovat nebude. Ale i kdy\u017e mezi operacemi RENAME do tabulky history <\/code>p\u0159ijdou n\u011bjak\u00e1 data a samotn\u00e1 tabulka je\u0161t\u011b nebude (kv\u016fli p\u0159ejmenov\u00e1n\u00ed), dostaneme mal\u00e9 mno\u017estv\u00ed chyb vkl\u00e1d\u00e1n\u00ed, kter\u00fdmi se m\u016f\u017eeme zab\u00fdvat (m\u00e1me monitoring, ne banku).<\/p>\n
Nyn\u00ed m\u00e1me novou tabulku history<\/code> s partitonac\u00ed, ale chyb\u00ed v n\u00ed data, kter\u00e1 byla z\u00edsk\u00e1na b\u011bhem posledn\u00edho pr\u016fchodu vkl\u00e1d\u00e1n\u00ed dat do tabulky history_tmp<\/code>. Ale tato data m\u00e1me v tabulce history_old <\/code>a nyn\u00ed je odtud dopln\u00edme. Za t\u00edmto \u00fa\u010delem budeme pot\u0159ebovat d\u0159\u00edve ulo\u017eenou hodnotu 1551085645. Pro\u010d jsme tuto hodnotu ulo\u017eili, a nevyu\u017eili maxim\u00e1ln\u00ed \u010das vkl\u00e1d\u00e1n\u00ed ji\u017e z aktu\u00e1ln\u00ed tabulky 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
\nPo dokon\u010den\u00ed t\u00e9to operace m\u00e1me v nov\u00e9, partitonovan\u00e9 tabulce history <\/code>v\u0161echna data, kter\u00e1 byla ve star\u00e9, plus ta, kter\u00e1 ji\u017e p\u0159i\u0161la po p\u0159ejmenov\u00e1n\u00ed tabulky. Tabulka history_old <\/code>u\u017e nen\u00ed pot\u0159eba. M\u016f\u017eeme ji okam\u017eit\u011b smazat, nebo ji p\u0159ed smaz\u00e1n\u00edm z\u00e1lohovat (pokud m\u00e1te paranoia).<\/p>\n
Cel\u00fd v\u00fd\u0161e pops\u00e1n\u00fd proces je t\u0159eba opakovat pro tabulky history_str<\/code>, history_text <\/code>a history_uint<\/code>.<\/p>\n
Co je t\u0159eba opravit v nastaven\u00edch Zabbix Server<\/h3>\n
\nNyn\u00ed \u00fadr\u017eba datab\u00e1ze v \u010d\u00e1sti historie dat pad\u00e1 na na\u0161e ramena. To znamen\u00e1, \u017ee Zabbix u\u017e nemus\u00ed odstra\u0148ovat star\u00e1 data \u2014 budeme se o to starat sami. Aby se Zabbix Server nesna\u017eil data \u010distit s\u00e1m, mus\u00edte se p\u0159ihl\u00e1sit do webov\u00e9ho rozhran\u00ed Zabbix, vybrat z menu \u201eAdministrace\u201c, pot\u00e9 podmenu \u201eObecn\u00e9\u201c, a pak v prav\u00e9m rozev\u00edrac\u00edm seznamu vybrat \u201e\u00dadr\u017eba historie\u201c. Na zobrazen\u00e9 str\u00e1nce je t\u0159eba od\u0161krtnout v\u0161echny za\u0161krt\u00e1vac\u00ed pol\u00ed\u010dka pro skupinu \u201eHistorie\u201c a kliknout na tla\u010d\u00edtko \u201eAktualizovat\u201c. To zabr\u00e1n\u00ed ne\u017e\u00e1douc\u00edmu \u010di\u0161t\u011bn\u00ed tabulek, kter\u00e9 nepot\u0159ebujeme. historie*<\/code> p\u0159es housekeeper.<\/p>\n
V\u0161imn\u011bte si na t\u00e9to str\u00e1nce skupiny \u201eDynamika zm\u011bn\u201c. To je p\u0159esn\u011b tabulka trends<\/code>, na kterou jsme sl\u00edbili vr\u00e1tit se. Pokud se v\u00e1m tak\u00e9 stala p\u0159\u00edli\u0161 velkou a pot\u0159ebuje b\u00fdt partition, od\u0161krtn\u011bte pol\u00ed\u010dka i v t\u00e9to skupin\u011b a pot\u00e9 zpracujte tuto tabulku stejn\u011b jako bylo provedeno u tabulek historie*<\/code>.<\/p>\n
Dal\u0161\u00ed \u00fadr\u017eba datab\u00e1ze<\/h3>\n
\nJak bylo naps\u00e1no d\u0159\u00edve, pro norm\u00e1ln\u00ed fungov\u00e1n\u00ed na partition tabulk\u00e1ch je nutn\u00e9 v\u010das vytv\u00e1\u0159et partition. M\u016f\u017eete to ud\u011blat takto:<\/p>\n
ALTER TABLE `historie` ADD PARTITION (PARTITION p20190307 VALUES LESS THAN (UNIX_TIMESTAMP(\"2019-03-07 00:00:00\")));<\/code><\/pre>\n
\nKrom\u011b toho, proto\u017ee jsme vytvo\u0159ili partition tabulky a zak\u00e1zali Zabbix Serveru je \u010distit, je nyn\u00ed odstra\u0148ov\u00e1n\u00ed star\u00fdch dat na\u0161\u00ed starost\u00ed. Na\u0161t\u011bst\u00ed s t\u00edm nejsou v\u016fbec \u017e\u00e1dn\u00e9 probl\u00e9my. Prov\u00e1d\u00ed se jen odstran\u011bn\u00edm t\u00e9 partition, jej\u00ed\u017e data u\u017e nepot\u0159ebujeme. <\/p>\n
Nap\u0159\u00edklad:<\/p>\n
ALTER TABLE historie DROP PARTITION p20190201;<\/code><\/pre>\n
\nNa rozd\u00edl od p\u0159\u00edkaz\u016f DELETE FROM s ur\u010den\u00edm \u010dasov\u00e9ho rozsahu se DROP PARTITION prov\u00e1d\u00ed za p\u00e1r sekund, v\u016fbec nezat\u011b\u017euje server<\/a> a stejn\u011b bezprobl\u00e9mov\u011b funguje v p\u0159\u00edpad\u011b pou\u017eit\u00ed replikace MySQL.<\/p>\n
Z\u00e1v\u011br<\/h3>\n
\nPopisovan\u00e9 \u0159e\u0161en\u00ed bylo ov\u011b\u0159eno \u010dasem. Objem dat roste, ale nezaznamenalo se \u017e\u00e1dn\u00e9 v\u00fdrazn\u00e9 zpomalen\u00ed v\u00fdkonu.
\n
Zdroj: 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\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\udd47Pou\u017eit\u00ed partition v MySQL pro Zabbix s velk\u00fdm mno\u017estv\u00edm monitorovan\u00fdch objekt\u016f | ProHoster","description":"Pro monitorov\u00e1n\u00ed server\u016f a slu\u017eeb m\u00e1me u\u017e dlouho a st\u00e1le \u00fasp\u011b\u0161n\u011b pou\u017e\u00edv\u00e1no kombinovan\u00e9 \u0159e\u0161en\u00ed zalo\u017een\u00e9 na Nagios a Munin.","canonical_url":"https:\/\/prohoster.info\/cs\/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":"cs_CZ","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\/cs\/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\/cs\/wp-json\/wp\/v2\/posts\/53966","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/comments?post=53966"}],"version-history":[{"count":1,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts\/53966\/revisions"}],"predecessor-version":[{"id":172868,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts\/53966\/revisions\/172868"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media?parent=53966"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/categories?post=53966"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/tags?post=53966"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}