{"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\/en\/blog\/administrirovanie\/ispolzovanie-partitsionirovaniya-v-mysql-dlya-zabbix-s-bolshim-kolichestvom-obektov-monitoringa","title":{"rendered":"Using Partitioning in MySQL for Zabbix with a Large Number of Monitoring Objects","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

For server and service monitoring, we have long been successfully using a combined solution based on Nagios and Munin. However, this combination has its drawbacks, so we, like many others, are actively utilizing Zabbix<\/a><\/noindex>. In this article, we will discuss how to solve performance issues with minimal effort as the number of metrics collected increases and the volume of the MySQL database grows.
\n<\/a><\/noindex><\/p>\n

Problems of Using MySQL with Zabbix<\/h3>\n

\nAs long as the database was small and the number of metrics stored in it was limited, everything was fine. The built-in housekeeper process, which runs on the Zabbix Server, effectively removed outdated entries from the database, preventing it from growing. However, once the number of metrics collected increased and the database volume reached a certain size, things took a turn for the worse. The housekeeper could no longer delete data within the allotted time, leaving old data in the database. During the housekeeper's operation, there was increased load on the Zabbix Server, which could persist for a long time. It became clear that the situation needed to be addressed somehow.<\/p>\n

This is a well-known problem; practically everyone who has worked with large volumes of monitoring on Zabbix has encountered the same issue. There were several solutions, such as switching from MySQL to PostgreSQL or even Elasticsearch, but the simplest and most tried-and-true solution was to move to partitioning the tables storing metric data in the MySQL database. We decided to pursue this path.<\/p>\n

Transitioning from Regular MySQL Tables to Partitioned Ones<\/h3>\n

\nZabbix is well documented, and the tables where it stores metrics are known. These tables include: history<\/code>, which stores float values, history_str<\/code>, which stores short string values, history_text<\/code>, which stores long text values, and history_uint<\/code>, which stores integer values. There is also a table trends<\/code>, which stores change dynamics, but we decided not to touch it since its size is small, and we will come back to it later.<\/p>\n

Overall, it was clear which tables needed to be processed. We decided to create partitions for each week, except for the last one, based on the days of the month, i.e., four partitions per month: from the 1st to the 7th, from the 8th to the 14th, from the 15th to the 21st, and from the 22nd to the 1st (of the next month). The challenge was to transform the necessary tables into partitioned ones 'on the fly' without interrupting the operation of the Zabbix Server and metric collection.<\/p>\n

Strangely enough, the structure of the table data itself helped us with this. For example, the table history <\/code>has the following structure:<\/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
\nwith<\/p>\n
KEY `history_1` (`itemid`,`clock`)<\/code><\/pre>\n
\nAs we can see, each metric is eventually recorded in the table with two very important and convenient fields for us: itemid<\/b> and clock<\/b>. Therefore, we can easily create a temporary table, for example, named history_tmp<\/code>, set it up for partitioning, and then transfer all the data from the table history<\/code>, and then rename the table history<\/code> downward API support (simultaneously with this in history_old<\/code>, and the table history_tmp<\/code> downward API support (simultaneously with this in history<\/code>, after which we can add the data that we haven't yet transferred from history_old<\/code> downward API support (simultaneously with this in history <\/code>and delete history_old<\/code>. This can be done completely safely, as we will not lose anything because the aforementioned fields itemid <\/b>and clock <\/b>ensure the binding of a specific metric to a specific time, not to some ordinal number.<\/p>\n
The transition procedure itself<\/h3>\n
<\/p>\n
Attention! It is highly recommended to make a complete backup of the database before starting any actions. We are all human and can make mistakes in the command input, which may lead to data loss. Yes, a backup may not ensure maximum currency, but it is better to have one than none.<\/p><\/blockquote>\n
 So, we do not turn off or stop anything. The main thing is that there is enough free disk space on the MySQL server, i.e., for each of the tables listed above history<\/code>, history_text<\/code>, history_str<\/code>, history_uint<\/code>, there must be enough space to create a table with the suffix '_tmp', considering that it will be of the same size as the original table.<\/p>\n
We will not describe everything several times for each of the above tables and will consider everything using only one example \u2014 the table history<\/code>.<\/p>\n
So, let's create an empty table history_tmp <\/code>based on the structure of the table history<\/code>.<\/p>\n
CREATE TABLE `history_tmp` LIKE `history`;<\/code><\/pre>\n
\nCreating the necessary partitions. For example, we'll do this for a month. Each partition is created based on a partitioning rule derived from the value of the field clock<\/b>, which we compare with the timestamp:<\/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
\nThis operator adds partitioning to the table we created history_tmp<\/code>. Let\u2019s clarify that the data where the field value clock <\/b>is less than \"2019-02-01 00:00:00\" will go into partition p20190201<\/i>, then the data where the field value clock<\/b> is greater than \"2019-02-01 00:00:00\" but less than \"2019-02-07 00:00:00\" will go into partition p20190207 <\/i>and so on.<\/p>\n
Important note:<\/b> What will happen if we have data in the partitioned table where the field value clock is greater than or equal to \"2019-03-01 00:00:00\"? Since there is no suitable partition for this data, it will not enter the table and will be lost. Therefore, you need to remember to create additional partitions in a timely manner to avoid such data loss (as discussed below).<\/p><\/blockquote>\n
 So, the temporary table is prepared. Let's load the data. The process may take a considerable amount of time, but fortunately, it does not block any other queries, so you just need to be patient:<\/p>\n
INSERT IGNORE INTO `history_tmp` SELECT * FROM history;<\/code><\/pre>\n
\nThe IGNORE keyword during the initial loading is not mandatory since there is no data in the table yet, however, you will need it when loading data later. Additionally, it may prove useful if you had to interrupt the process of loading data and restart it.<\/p>\n
So, after some time (possibly even several hours), the first data load was completed. As you understand, now the table history_tmp <\/code>contains not all the data from the table history<\/code>, but only those that were present at the moment the query began executing. Here, you have a choice: either we make another pass (if the loading process took a long time), or we proceed directly to renaming the tables mentioned above. Let\u2019s first discuss the second pass. First, we need to determine the time of the last inserted record in history_tmp<\/code>:<\/p>\n
SELECT max(clock) FROM history_tmp;<\/code><\/pre>\n
\nSuppose you received: 1551045645<\/b>Now we use the obtained value in the second data loading pass:<\/p>\n
INSERT IGNORE INTO `history_tmp` SELECT * FROM history WHERE clock>=1551045645;<\/code><\/pre>\n
\nThis pass should finish significantly faster. But if the first pass took hours, and the second one also takes a long time, it may be reasonable to perform a third pass, which operates exactly like the second.<\/p>\n
In the end, we again execute the operation to get the timestamp of the last record insertion into history_tmp<\/code>, by executing:<\/p>\n
SELECT max(clock) FROM history_tmp;<\/code><\/pre>\n
\nSuppose you have obtained 1551085645<\/b>. Save this value - we will need it for the additional upload.<\/p>\n
And now, when the initial data loading into history_tmp <\/code>is finished, we proceed to rename the tables:<\/p>\n
BEGIN;\nRENAME TABLE history TO history_old;\nRENAME TABLE history_tmp TO history;\nCOMMIT;<\/code><\/pre>\n
\nWe structured this block as a single transaction to avoid the moment of inserting data into a non-existent table, since after the first RENAME and before the second RENAME, the table history <\/code>will not exist. But even if some data comes to the table between the RENAME operations, and the table itself does not exist yet (due to the renaming), we will get a small number of insertion errors, which can be ignored (since we have monitoring, not banking). history <\/code>Now we have a new table<\/p>\n
with partitioning, but it lacks the data that was obtained during the last data insertion pass into the table history<\/code> . But we have this data in the table history_tmp<\/code>and we will fill it in from there now. For this, we will need the previously saved value 1551085645. Why did we save this value and not use the maximum upload time already from the current table? history_old <\/code>INSERT IGNORE INTO `history` SELECT * FROM history_old WHERE clock>=1551045645; 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
After this operation finishes, we will have all the data in the new, partitioned table<\/code><\/pre>\n
\nthat were in the old one, plus those that have already arrived after the table renaming. The table history <\/code>is no longer needed. You can delete it immediately, or make a backup of it before deletion (if you\u2019re paranoid). history_old <\/code>The entire process described above needs to be repeated for the tables<\/p>\n
What needs to be adjusted in the Zabbix Server settings history_str<\/code>, history_text <\/code>and history_uint<\/code>.<\/p>\n
What needs to be corrected in the Zabbix Server settings?<\/h3>\n
\nFrom now on, the management of the database regarding historical data is on us. This means that Zabbix will no longer need to delete old data \u2013 we will handle it ourselves. To prevent the Zabbix Server from attempting to clean data itself, you need to access the Zabbix web interface, select 'Administration' from the menu, then 'General' from the submenu, and then select 'History Cleanup' from the dropdown on the right. On the page that appears, uncheck all boxes for the 'History' group and click the 'Update' button. This will prevent unnecessary cleanup of tables. history*<\/code> through housekeeper.<\/p>\n
Note on this same page the 'Change Dynamics' group. This is precisely the table trends<\/code>, which we promised to return to. If this table has also become too large and requires partitioning, uncheck the boxes in this group as well, and then process this table just as it was done for the tables. history*<\/code>.<\/p>\n
Further database maintenance<\/h3>\n
\nAs mentioned earlier, to ensure proper operation on partitioned tables, partitions must be created in a timely manner. This can be done as follows:<\/p>\n
ALTER TABLE `history` ADD PARTITION (PARTITION p20190307 VALUES LESS THAN (UNIX_TIMESTAMP(\"2019-03-07 00:00:00\")));<\/code><\/pre>\n
\nFurthermore, since we have created partitioned tables and prohibited the Zabbix Server from cleaning them, the removal of old data is now our responsibility. Fortunately, this poses no problems at all. It can be done simply by deleting the partition whose data we no longer need. <\/p>\n
For example:<\/p>\n
ALTER TABLE history DROP PARTITION p20190201;<\/code><\/pre>\n
\nUnlike DELETE FROM operators with a specified date range, DROP PARTITION executes in seconds, does not overload server<\/a> and works just as smoothly in cases of MySQL replication.<\/p>\n
Conclusion<\/h3>\n
\nThe described solution has stood the test of time. Data volume is increasing, but no noticeable performance degradation has been observed.
\n
Source: 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\udd47Using partitioning in MySQL for Zabbix with a large number of monitoring objects | ProHoster","description":"For monitoring servers and services, we have long been, and still successfully, using a combined solution based on Nagios and Munin.","canonical_url":"https:\/\/prohoster.info\/en\/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":"en_US","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\/en\/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\/en\/wp-json\/wp\/v2\/posts\/53966","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/comments?post=53966"}],"version-history":[{"count":1,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/53966\/revisions"}],"predecessor-version":[{"id":172868,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/53966\/revisions\/172868"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media?parent=53966"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/categories?post=53966"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/tags?post=53966"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}