Database seems to be growing too quick

[fsousa]

It seems the tmpdir defined in my mysql config isn't used for the partitioning

mysql> SHOW VARIABLES LIKE 'tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        | /tmp  |
+---------------+-------+
1 row in set (0,01 sec)

The partitioning files are created in my database dir /mnt/zabbix-data0/zabbix

 

[root@zabbix zabbix]# ls -lah *#sql* | head -10
-rw-rw----. 1 mysql mysql  13K Jan 10 09:19 #sql-7259_d.frm
-rw-rw----. 1 mysql mysql  796 Jan 10 09:19 #sql-7259_d.par
-rw-rw----. 1 mysql mysql  22G Jan 10 11:36 #sql-7259_d#P#p2018_11_09.ibd
-rw-rw----. 1 mysql mysql 112K Jan 10 09:19 #sql-7259_d#P#p2018_11_10.ibd
(...)
-rw-rw----. 1 mysql mysql 112K Jan 10 09:19 #sql-7259_d#P#p2019_01_06.ibd
-rw-rw----. 1 mysql mysql 112K Jan 10 09:19 #sql-7259_d#P#p2019_01_07.ibd

So if I understand this files (#sql-7259_d#P#) are the my partitioned files and after the proceed they stay in this folder and my history_uint.ibd disappear right?

[ingus.vilnis]

Please look in MySQL docs if what I suggested is even possible. As said I am not sure about it.

But what is table sql-7259_d ???

[fsousa]

The sql-7259_d are the temporary files of partitioning tables.

[fsousa]

Hi Ingus,

I finished the partitioning and now everything is alright. To escape the problem with the disk space on my server during the process of partitioning of my tables I'd to change some tables to another temporary disk and after partitioning the history_uint move again the tables to the right place.

Thank you for your patience and explanations.

[ingus.vilnis]

Hi,

Great that you managed to finally partition all tables. Now keep an eye if they are dropped nightly and how your disk space is affected.

[fsousa]

Hi,

With the partitioning tables my database decreased from 223GB to 82G.

The script is working good. Every day one table is dropped and a new one is created.

I've one more question but isn't related with this topic but maybe you can help me. I want to backup my zabbix server configuration (hosts configuration, templates, do you know how I can do this?

Thank you.

[ingus.vilnis]

Great that the partitioning is working and now you use the disks much more effectively.

Backup scenarios are the following.

You can export the Hosts and Templates into XML files in case you need to do a doomsday recovery. You will be able to import the configs back in Zabbix if needed. Go with templates and hosts separately.

https://www.zabbix.com/documentation/4.0/manual/xml_export_import

And then the "usual" backup of DB tables. You can do a full backup e.g. on weekends and then do daily incremental backup. But for configuration backup you may want to consider excluding the history* and trends* tables from the sql file.

Percona Xtrabackup is a good tool to look into for regular backups.

[fsousa]

Hi,

I thought it was possible do that only with the "usual" backup of DB tables without history* and trends* tables.

So if I dump DB tables I will losing the templates and host configurations?

[ingus.vilnis]

Hold on, Zabbix DB has 110+ tables to store everything, including hosts and templates. But also the full config is scattered around several tables.

I did not understand your last sentence really. You lose the configuration or you remove it from the DB or drop some DB tables. In normal circumstances you should be fine. Do DB backups and from time to time export your templates and hosts to XML files. That should be enough to recover if really you lose the configuration data.

[Kampai]

i decided to go for partition also but from the very begining i have this situation:
after launching this command:


SELECT FROM_UNIXTIME(MIN(clock)) FROM `history_uint`;

it's takes forever probably to get output, i am still waiting more then 1+ hour already. my db size in 80GB should it take so much?