Migrate from MySQL to Elastic Search

[spriebe]

I searched through the forum but it seems nobody has already tried a migration from MySQL to Elastic Search. I'm searching for a way to keep our History and Migrate from MySQL to ElasticSearch. Any ideas or hints?

[kloczek]

If you re thinking that using elastic search may improve something from point of view of performance you are right .. you are only thinking that this is true.
Stick with mysql. Just configure it correctly using metrics values from proper SQL engine monitoring.

[spriebe]

Thanks but i‘m not sure what you wanna tell me. Yes i‘m mainly focussed on performance and long term storage. For MySQL innodb i‘m getting a lot of wholes in the tables and optimize is not an option (takes too long). Currently 700gb database (but still throwing away too many values. Currently 1800val/s

[kloczek]

Thanks but i‘m not sure what you wanna tell me. Yes i‘m mainly focussed on performance and long term storage. For MySQL innodb i‘m getting a lot of wholes in the tables and optimize is not an option (takes too long). Currently 700gb database (but still throwing away too many values. Currently 1800val/s

Just ask yourself: What is the performance difference in case accessing in-memory data stored in ES and MySQL data buffered in innodb memory pool?
You are not using always all 700GB data. Mostly you are using not more than probably few tenths of GBs of last day data. Total size of the DB is in such cases totally irrelevant.
1.8k nvps it is now like mid size zabbix stack.
Adding to this ES is waste of resources mostly memory. By adding the same memory to innodb memory pool you will be able to speedup MySQL engine way more than any other way.
If your DB engine is generating more read IOs than write ones it means that you need add more memory to innodb pool.
Typically DB engine used by zabbix server if it works with enough memory it shoud generate not less than 1:15-20 read to write IOs.
Do you have IO statistics of the storage used by your MySQL DB? if not focus on this first.
If this ratio is lower 1:15-20 than just add memory to innodb pool because your DB engine need read more and more data from storage (even NVMe storage like Intel Optane latency about 10us cannot beat RAM latency which is about nanoseconds).

[jvillain]

Some thing we are running into is the size of the history table becoming so large that the house cleaning task bogs the server down. Make sure you enable indexing by date in the zabbix_server.conf to help with that. If you see database locks coresponding to those holes that is your cuase. You also migh want to tweek your time outs as well.

And like kloczek said make sure ypou have enough RAM.

[spriebe]

But there is no history cleanup for ES? Is it?

[telder]

There is not an automated History cleanup for ES right out of the box however you can use Date based indices and setup Elasticsearch Curator via an automated cron job to perform the cleanup. We made the move to ES and when it works it's great however Zabbix has a bad habit of not documenting breaking changes when it comes to using Elasticsearch. We performed the upgrade to 4.0.1 the other day and it wiped out the ES mappings that were there and support was pretty useless on the matter. I would stick with using MySQL for general purposes and use the real time data export feature to export the data you want to push into Elasticsearch then push it via a script and cron job. We're actually looking to move to Prometheus due to it's integration with Elasticsearch and overall capabilities and ease of maintenance. Don't need 60 VM's to keep it supported in a large environment