Reclaiming Disk Space for MariaDB Table

Question

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:

1. Stop MariaDB service
2. Change datadir location in mysqld.cnf to point to extra disk
3. Start MariDB service
4. OPTIMIZE table using this query - ALTER TABLE foo ENGINE innodb;
5. Repeat 1-3 to go back to using main disk.

Are there any alternative methods you suggest to handle this situation effectively?

Answer 1

• For future "big deletes", see multiple tips here: https://mysql.rjweb.org/doc.php/deletebig

• Much of the "Data_free" and "fragmentation" in unavoidable. In particular, a 'Data_free' of between 4MB and 7MB is unlikely to go away. Fragmentation is also elusive. Don't bother trying to OPTIMIZE such tables.

• If multiple tables have more Data_free than 7MB, Optimize them, starting with the smallest. But check the sizes to see if each one is likely to succeed with the limited disk space. This may free up enough space for the naughty table.

• Another approach that may work is to move small tables into ibdata1. This will especially help if ibdata1 has a lot of free space. Watch the size of that file, while doing this, starting with smaller tables:

    SET GLOBAL @@innodb_file_per_table = OFF;
    ALTER TABLE smallish_table ENGINE=InnoDB;
  

• You seem to need about 26+16 = 42GB, but have only 8GB. It is not obvious that the progressive Optimizes or moving of small tables would provide that much space.

• In the future, avoid filling up more than half of disk. Or at least keep enough free space for a copy of the largest table. Plan for getting a bigger disk soon.

• You mentioned a spare disk. Why not simply expand into it? That is, don't bother moving the table back into the current drive. Check out the syntax for specifying multiple disk drives in my.cnf. (MySQL/MariaDB dates back to the days when 2GB was the biggest disk on the market.)