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

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.

1 Like

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

1 Like

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

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