Size Mysql database

[RomFr]

Hello,

I test currently Zabbix for my company. And the size of Mysql database worries me.
Today, in history_uint table, I have just 58 485 recordings and the size is 6 Mio ! ! I not understand because in the table structure we have only two bigint et one int or 20 bytes ! 58 485*20 = 1,2Mio and not 6 !!!!!!!

Can you help me understand please.

RomFr

[RomFr]

I export history_uint table to SQL file, and it size is 1,52 Mo and no 6 !!

[Zaniwoop]

When housecleaning is done and records are deleted from the history table, it leaves gaps. When new records are added, these gaps are not filled so the size increases.

You need to optimize the tables to compact them again.

My history table after optimization is 19 GB

[RomFr]

=o

But it is null!

But there is another problem, it's not possible!

[nelsonab]

I'm assuming you're referring to the size of the InnoDB file. In which case it's very possible for it to be larger. If I'm not mistaken InnoDB allocates space in larger chunks than it would normally need as that is faster. I would not worry if you DB size is a couple MB bigger than your data. I would however worry if the size difference is tens or hundreds of MB off, or if your a GB or two off.

[Zaniwoop]

19 GB is not a problem, as I am logging a lot of data and keeping quite a while.

[RomFr]

But it increases rapidly. Up to 1,3Mo/days (For my test, i monitor only the ping, and in/out traffic of 8 switchs)
So the problem would InnoDB okok. The problem is resolve with PostgreSQL ?

Another small question.
My boss want have statistics on several years (5 years for exemple).
How do I adjust the settings ? Historical, trends ?

Thank you nelsonab.

RomFr

[nelsonab]

But it increases rapidly. Up to 1,3Mo/days (For my test, i monitor only the ping, and in/out traffic of 8 switchs)
So the problem would InnoDB okok. The problem is resolve with PostgreSQL ?

If that's very concerning for you then you may want to look at how quckly you are gathering your data. The default Zabbix template for Linux systems checks items WAY too quickly. Most items you need to get only once a minute or more, only a small amount really need to be less than a minute. Moving to Postgres will not fix this, go into your items and adjust how often you check them.

As noted above, Zabbix databases can grow to be quite large. I had one server stabilize out at around 25GB after a full year of operation.

Another small question.
My boss want have statistics on several years (5 years for exemple).
How do I adjust the settings ? Historical, trends ?

You'll need to go into your items configuration and adjust how often it's measured and for how long data is stored. You will want to learn the difference between history and trends, that can be found in the manual.


----------------
This post should be brought to you by Boeing, post #777

[RomFr]

If that's very concerning for you then you may want to look at how quckly you are gathering your data. The default Zabbix template for Linux systems checks items WAY too quickly. Most items you need to get only once a minute or more, only a small amount really need to be less than a minute.

I ping every minutes and the traffic every 5 minutes.

Moving to Postgres will not fix this, go into your items and adjust how often you check them.

I assumed that beacause Postgres != MySQL InnoDB and the size would be more fair.

[nelsonab]

I assumed that beacause Postgres != MySQL InnoDB and the size would be more fair.

Maybe, maybe not. Moving to Postgres will also present you with other challenges you aren't seeing with MySQL. Each DB engine brings it's own unique challenges and issues as they do strengths. I haven't set up Zabbix on Postgres but as I understand it's biggest challenge (or at least it was a few years ago) with regards to Zabbix was the need for vacuum operations to recover lost space. Vacuum tends to bring the DB down which means Zabbix isn't monitoring during that time, but if you don't Vacuum your DB grows at an explosive rate. But like I said I haven't setup Postgres and Zabbix so I'm going by what I've heard from others and my information may old.

[RomFr]

I do research.

Thank you.

RomFr