Zabbix Design Choices - MySQL Partitioning?

[finalbeta]

Hello,

I need to set up a new Zabbix enviroment and I have a few choices where I would like some input on.

In the past I've set up Zabbix with a MySQL backend. A possible problem was that MySQL did not recover the white space the housekeeper processes recovered. The only way to recover that seemed to be to backup the database and restore it.

As PostgreSQL did not have this issue, I've also ran that. However, that means yet another type of database server in the mix.

I've learned about partition, that that would eliminate the need for the housekeeping entirely. Something that would take some load of the server.

So this time I was thinking of setting up Zabbix with a MySQL backend and configuring MySQL with partitioning. I've found two guides ( https://www.zabbix.org/wiki/Docs/howto/mysql_partition / https://www.zabbix.org/wiki/Docs/how...ing_.28YAMP.29 )

However, I've got a few questions.

1) Should the guides work or do they require alterations for 3.0?
2) They recommend not to configure partitioning on some tables. Do the other tables still need maintenance (like houskeeping?)
3) Would you even suggest to use partitioning with mysql? or does the complexity it adds make it unrecommendable? (upgrades)

Thank you for your advice.

[xanadu]

No one? Have the same question in mind.

[DaveFaught]

Hi, I also have these questions. I am doing a self-directed proof of concept test of Zabbix 3.0 with MySQL 5.5, so I have a working server to play around with. It does not look like it will scale up to the number of hosts and items that I would like to monitor, but I would like to get the most out of MySQL and the server that I can. It seems like partitioning would be an obvious performance improvement over the Housekeeping task.

I implemented partitioning using parts of both of the wiki documents you referenced, but mostly the stored procedure method of the "YAMP" document. In addition to the questions already posed in this thread, I would also like to know:

1. There are differences between the 2 wiki documents for how the keys and indexes of the history_log and history_text tables are altered. What is the best way?

2. I also implemented the innodb_file_per_table = 1 and innodb_flush_method = O_DIRECT options mentioned on the community forums. Does this help when combined with partitioning or is there any interaction between the two?

Thanks for any help.
Dave

[DaveFaught]

I don't have answers to these questions, but I do have a little bit of guidance to share. In the "YAMP" document explaining how to partition, it gives a choice of by day or by month partitioning. I originally did it the way that the author presents it with the history tables done by day and the trends tables done by month.

After running for a few weeks, I noticed that the CPU I/O Wait Time kept creeping up through the month and then dropped way down at the beginning of the next month. I'm sure that there is a tradeoff in longer time period queries against the trends tables, but I like day-to-day response times to be relatively stable, so I have now redone the trends databases partitioning to also be by day.

[lordeldor]

However, I've got a few questions.

1) Should the guides work or do they require alterations for 3.0?
2) They recommend not to configure partitioning on some tables. Do the other tables still need maintenance (like houskeeping?)
3) Would you even suggest to use partitioning with mysql? or does the complexity it adds make it unrecommendable? (upgrades)

Thank you for your advice.

1) I can't speak with a great deal of authority on this because I don't yet run 3.0 but I am pretty sure the instructions still apply just the same. The valuable/practical tables to partition in the zabbix database are the history and trend tables. I am reasonably certain this remains the same in 3.0.

2) The housekeeping processes are there to curate the history and trend tables. The other tables in the database do not benefit from periodic cleaning in the same way. The reason you don't need the housekeeper processes after partitioning is because when the data becomes stale you just drop the necessary partition instead. Which causes a great deal less load than having to query for stale data and purge it record by record.

3) Without a doubt yes. I commonly see comments that indicate partitioning is necessary for large installations, but the truth is every Zabbix installation could benefit from not wasting CPU time on housekeeping.

I would also recommend partitioning early if not right away. The guide isn't joking about how it could potentially take days to execute the partitioning commands if there is a fair amount of data in those tables.

I am currently running a bit over 200,000 items at about 800 values per second. When I made the change over to partitioned tables it took a little over 1 day and 20 hours to execute the partitioning stored procedures. In the end it was well worth it though. Once the partitioning was completed the load on our mysql server halved.

[Sugarman]

Just to answer #1: I attended a Zabbix training a few weeks ago and the instructor explicitly refered to the aforementioned documentation to be valid for V3.0. So just go ahead.

I am currently building a new Zabbix environment - much less items per second but still will do the partitioning (on V3.0). Its just more efficient than housekeeping...

[xanadu]

None of the two guides work for Zabbix 3.2:

MariaDB [zabbix]> Alter table history_text drop primary key, add index (id), drop index history_text_2, add index history_text_2 (itemid, id);
ERROR 1091 (42000): Can't DROP 'PRIMARY'; check that column/key exists


MariaDB [zabbix]> ALTER TABLE `history_log` DROP PRIMARY KEY, ADD INDEX `history_log_0` (`id`);
ERROR 1091 (42000): Can't DROP 'PRIMARY'; check that column/key exists


Is there anyone with the same problem? I cannot find more information around MySQL db partitioning on Zabbix 3.2. Recently I have applied the guide to Zabbix 3.0 with succes. Is there anything that has changed in v3.2?

Cheers,
Kevin

[whitehat]

also accidentally updated to 3.2
I had partitioning already enabled in 3.0, now I don't know if it fails to create new partitions for 3.2

[kloczek]

In the past I've set up Zabbix with a MySQL backend. A possible problem was that MySQL did not recover the white space the housekeeper processes recovered. The only way to recover that seemed to be to backup the database and restore it.

No, it is not true.
Just create slave database and you can do do all partitioning on the slave and whent it will be done all what you need to do is just promote partitioned slave as new master.

[Pzero]

To answer xanadu's question...

The id column has been dropped from the history_log & history_text tables in 3.2.

If you download the source code for 3.2 you will find (in database folder?) the script for creating the database so you can see exactly which columns & indexs/keys in which tables are required in 3.2. Make changes to your tables to match. Our zabbix installation originated from v1.8 and I found several superfluous columns that I could manually drop.

[xanadu]

To answer xanadu's question...

The id column has been dropped from the history_log & history_text tables in 3.2.

If you download the source code for 3.2 you will find (in database folder?) the script for creating the database so you can see exactly which columns & indexs/keys in which tables are required in 3.2. Make changes to your tables to match. Our zabbix installation originated from v1.8 and I found several superfluous columns that I could manually drop.

Thanks for your answer. Unfortunately my expertise into this matter doesn't cover this. I think I'll need to wait until someone publishes a new howto for mysql partitioning for Zabbix 3.2..

[moixcruz]

Following both guidelines for partitioning I understand that primary keys must be deleted and ordinary indexes created instead, but the steps only do it for history tables and not trend. Am I missing something or are these keys in trend new in later versions of zabbix (I'm running 3.0)?
Thanks

[emz]

Help for partitioning

Hello,

I am trying to implement partitioning in my Zabbix environment, so I looked up at the following link:

https://www.zabbix.org/wiki/Docs/howto/mysql_partition

However, I still have some questions, so I would be very grateful if somebody could help.

My Environment:
One Zabbix Server v3.0.8
One Zabbix Proxy (same version)
CentOS 7 OS - two VMs - one for Zabbix-Server and one for DB;
MariaDB v5.5.52 InnoDB engine (file per table enabled)

Number of hosts (enabled/disabled/templates) 822 661 / 111 / 50
Number of items (enabled/disabled/not supported) 53179 36420 / 13095 / 3664
Number of triggers (enabled/disabled [problem/ok]) 33091 25103 / 7988 [281 / 24822]
Required server performance, new values per second 267.38

Current DB Statistics:
# Data Base Name, Data Base Size in MB, Free Space in MB
'information_schema', '0.07', '0.00'
'zabbix', '24212.45', '368.00'

Biggest Tables (planned for partitioning):
# Table, Size in MB
'history_uint', '11240.03'
'history', '5665.39'
'trends_uint', '2942.05'
'trends', '2113.02'
'history_str', '1216.47'
'history_log', '456.44'

Before partitioning: Housekeeper is set to:
History Data storage period: 7 days;
Trends Data storage period: 90 days.

Desired periods after partitioning:
History tables retention period: 28 days;
Trends tables retention period: 180 days;

Questions:
1. How much space would the partitioning take? Would it take additional disk space?
2. What is the proper procedure to perform partitioning on DB with existing data?
3. If all current data is created in one partitition, what then? Should the Housekeeping be disabled? Will that partition be dropped eventually?
4. How can I decide what is the best value for "future partitions"?
5. If the "future partitions" are set to 14, how often the "partition_maintenance_all" should be executed? Can you recommend a cronjob syntaxis to execute for that?
6. Should the Zabbix monitoring be disabled while performing the initial partitioning?
7. Would the partitioning be efficient on CentOS 7 and MariaDB v5.5.52 and InnoDB?
8. Is "mysqldump --single-transaction -uroot -p zabbix > backup.sql" sufficient for backup before performing the partitioning?
9. Can the "days to keep" parameter be modified so the data retention can be increased in the future?

Would it be suitable to follow the plan below:

1. Perform a backup of the database using "mysqldump --single-transaction -uroot -p zabbix > backup.sql".
2. Import the data on another server where no other data is being inserted as follows:
zcat create.sql.gz | mysql -uzabbix -p
mysql -uroot -p zabbix < backup.sql
3. Perform the partitioning procedure.
4. Backup the already partitioned database using "mysqldump --single-transaction -uroot -p zabbix > backup.sql".
5. Stop Zabbix-Server and import the data using mysql -uroot -p zabbix < backup.sql.
6. Start Zabbix-Server.

The idea is to minimize the time for which the monitoring won't be available.
Of course, I would welcome any advise in that direction.

[ovas]

Hello emz!

Generally, this article is better in every aspect: https://www.zabbix.org/wiki/Docs/how...l_partitioning, especially the management by external script.

As per your questions:
1. Same space as if table was not partitioned.
2. Put all the older data to the first partition and create new ones for newer data. This is the fastest way, but you can also put the contents of existing table to the day/week/month based partitions by selecting table values within the time frame in timestamp format. Example: https://stackoverflow.com/questions/...ect-date-range
3. The partitioning process outlives housekeeper process. Yes, it should be disabled completely. The partitions may be dropped if you wish them to.
4. Eventually you will need automate new partition creation, so for the testing purposes begin with 7-10 days of history to see how it fares for you.
5. This one is instruction-specific. I do not have experience with this one, sorry.
6. Executing ALTER operations on a table, the DB will lock itself out only to read operations. No new data will be stored. Yes, it is best to stop Zabbix server while partitioning is in process, while buffering the collected data by proxies and agents (see ProxyLocalBuffer setting for proxy and BufferSize for agent) to minimize the impact of data loss.
7. Yes, the process is efficient regardless of OS and (I believe) DB engine. You should be more concerned about the hardware/resource aspect of both partitioning process operation and DB operating.
8. It should be, actually. It is best to test the backup before proceeding with partitioning. $)
9. A good question, requiring testing to be performed... Haven't tried myself yet.

[kloczek]

6. Executing ALTER operations on a table, the DB will lock itself out only to read operations. No new data will be stored. Yes, it is best to stop Zabbix server while partitioning is in process, while buffering the collected data by proxies and agents (see ProxyLocalBuffer setting for proxy and BufferSize for agent) to minimize the impact of data loss.

Better is use slave DB and do partitioning on slave and after finis sync with master this promote slave as new master.