I'm facing a disk space issue with a MariaDB database and would appreciate your insights on resolving it.
Here's the scenario:
MariaDB version: 10.3
OS: Ubuntu 20.04
Engine: Innodb
Table name: 'foo'
Data_length: 26GB
Index_length: 16GB
Data_free: 7GB
Fragmentation ~ 17%
innodb_file_per_table=ON
The problem is that the main disk, where the database data is stored, has only 8GB of free space left. We recently encountered a bulk delete operation on the 'foo' table, which has resulted in fragmentation. I want to reclaim space back to OS.
Considering the limited free space, running the OPTIMIZE TABLE
command directly on the 'foo' table is not feasible. The operation would require additional disk space to create a defragmented copy of the table.
I have an extra disk with more than enough space, which could be utilized to address the issue. The downtime can be up to 6 hours long at night time.
Here's the approach I'm considering, and I'd appreciate your feedback on its viability:
- Stop MariaDB service
- Change datadir location in mysqld.cnf to point to extra disk
- Start MariDB service
- OPTIMIZE table using this query -
ALTER TABLE foo ENGINE innodb;
- Repeat 1-3 to go back to using main disk.
Are there any alternative methods you suggest to handle this situation effectively?