Hi Markus,
Référence: Dmitry Lambert
Don’t underestimate Database tuning
Hey guys, It’s been a while for me posting here. Mostly because I am kind’a struggling with balancing content creation over Patreon and Youtube platforms. Reason is quite simple I want to post for as large audience as possible, but at the same time my dear patreons are supporting me also in financial way, so they(you) deserve some extra right? And another minus of Patreon platform is formatting of text… which is fine for just blogging, but just a nightmare to share any code snippets.
Alright, off from introduction and back to topic. Somehow, the most simple thing which has been pushed to Zabbix users in all possible ways and platforms still get’s underestimated. I really have a feeling that 9 out of 10 installations have their database configuration file with default settings. Trust me, It is straight road to performance problems with Zabbix, even if you are not ( and you won’t ) feel any of those right after your installation.
I understand that fine tuning kernel parameters, filesystem is basically a min/maxing, but database configuration tuning is a must!
Let me briefly explain what is happening when your database is not optimized. You deploy your Zabbix instance where database by default would weight probably around 10MB which will forgive any mistakes or lazyness of tuning. You have a default set of processes from Zabbix server, which probably varies around 20-30, Housekeeper runs in couple of seconds, you don’t see any slow queries in server log, all frontend pages are opening in a second, so everything is totally fine.
Thing is that database size will grow. Especially after a clean install, it simply must grow! How much? That strictly depends on NVPS ( New values processed by server ) and your history storage period. Let’s take one example based on simple formulas.
300 hosts
120 average items per host
36000 Total items
60 seconds average update interval
60 days storing history
1 year for trends
Such setup just like any other will start only from 10-20 Mb of database and will grow very fast for next 60 days ( history retention period ) then growth will slow down, because housekeeper will start to delete data. So in general, after 2 months, we should have it like this:
600 NVPS
418G database
At this moment Housekeeper finally will start to work on a full load, because in next day ( 61 ) there will be 1 day of data to delete. Database size is much bigger, which worsens performance of Zabbix server and also housekeeper.
As a result we will eventually start to receive notifications from Trigger is more then 75% busy. In server log file we will start to notice Slow queries ( by default they are noted for every query that is slower then 3 seconds ) Initially most of those queries will be by housekeeper process with DELETE statements to database.
When housekeeper is not able to keep up with all the data ( You write more then housekeeper is able to delete ) Database growth will continue. When database size increases, housekeeper becomes even more slower. So it is able to delete even less data, so database grows even more. I believe you understood where this is going.
And it would not be that bad if all of this would affect only housekeeper. When housekeeper is slow and runs for many minutes, what it does is scanning History tables for data to delete. Remember that those are same tables where History syncers have to insert new data. While housekeeper is scanning, history syncer is not able to insert new data. So then we will start to receive other alerts. History syncer more then 75% busy. Nodata triggers storm, internal process graph busy graph would show most of processes 100% busy. Gaps in the graphs, slow queries in the log. And all of this would be corelated with cycles of Housekeeper, when eventually you will reach situation when housekeeper will run for 1 hour ( by default it cycles every hour ) so basically it will be running 24/7
You might in panic throw more memory on your database server, but without touching configuration file, it won’t help at all. Thing is that database will not utilize your server resoruces up to maximum. You need to allow database to do that.
Even now, simply open SSH to your database server and type free -h
If you see couple of Gigs listed as available memory, it means that your database could be using this memory to increase performance. But it’s not, simply because you did not allow it to do that.
I understand that tuning the database also required deeper knowledge of what to do, when and how much. That’s why I will post you just two websites that focus on most critical parameters that will dramatically improve performance of your database.
For MySQL/MariaDB use this URL:
For PostgreSQL use this URL: https://pgtune.leopard.in.ua/#/
Do the tuning guys!
Michel-André
P.S. I received this by email because I support financially (too small amount) Dmitri at Patreon: https://www.patreon.com/dmitryl