How to decrease disk space occupied by /var/lib/mysql?

MySQL & MariaDB Other MySQL® Questions
1

In /var/lib/mysql we have:

ibdata1 with 204M;
folder zabbix with 238G;

The Zabbix system offers the housekeeper feature, which removes old data according to the set times.

I recently activated housekeeper, and in zabbix_server.log I see thousands of logs being removed. But I don’t see disk space being freed. Why?

The database server is: mysql Ver 15.1 Distrib 10.0.38-MariaDB, for Linux (x86_64) using readline 5.1;The tables are InnoDB engine.

2 Likes
2

How would I optimize with the pt-online-schema-change command?

1 Like
3

Tablespaces do not give space back to the OS. Deleted rows simply leave free space in the tablespace.

If the tables were generated while innodb_file_per_table was ON, then do SHOW TABLE STATUS to see which ones have more than a few MB of “Data_free”. For those, do OPTIMIZE TABLE. (Start with the smallest).

If all the tables were built with OFF, you are in a bad situation. The standard fix is to take a full dump, clear the disk (consider upgrading mysql at the same time), and reload everything. This time have innodb_file_per_table=ON before starting.

1 Like
4

If you want to optimize using pt-online-schema-change, then you can run NULL Alter to optimize the table.
alter table tablename engine=INNODB;

1 Like
5

I ran into an issue where the Zabbix DB was expanding by around 10gb a month, which ate up my space over time. After digging into the /var/lib/mysql and found an excessive amount of binlog (binary log) files. Checking the usage with “sudo du -h”, I was able to see that the binlogs were taking 200gb. We set up log rotate to resolve and deleted the old files.

6

You can setup the variable in mysql “expire_logs_days” instead of rotating logs.
For further discussion, you can create a new thread, as this thread was for separate topic.