By default, MySQL will retain any disk space it claims during operation for future use, and will not release it back to the operating system.

If you have previously used the Database storage method for attachments, or you experienced a flood of emails to your helpdesk, MySQL may be retaining disk space which it will never use.

This can be reclaimed by optimizing the tables which are retaining the free space. MySQL will rebuild the table, copying all the content over to the new table, then it will delete the existing table freeing up the space. This means that MySQL will need additional space to work during this procedure, so optimization should be run as a maintenance procedure, and not if you immediately need to recover disk space.

How much free space is my database retaining?

Within the service page for your MySQL server, the On-Premise Controller will display an estimated total size for your database. By selecting your database, it will display the top 10 largest tables, the total space used, and the estimated amount of free space available.

How can I reclaim this space?

This will need to be done via the MySQL prompt on the command line.

Preparations

Before you start, ensure you have enough free space for MySQL to work by running the command df -h. This will display the amount of disk space available on your server.

root@opc:~# df -h
Filesystem                         Size  Used Avail Use% Mounted on
tmpfs                              1.2G  2.6M  1.2G   1% /run
/dev/mapper/ubuntu--vg-ubuntu--lv  244G   81G  151G  35% /
tmpfs                              5.9G     0  5.9G   0% /dev/shm
tmpfs                              5.0M     0  5.0M   0% /run/lock
/dev/sda2                          2.0G  251M  1.6G  14% /boot
overlay                            244G   81G  151G  35% /var/lib/docker/overlay2/6298938ad71fefc995e0dd88bfad9cb0ec1a4ac304738a84adacd61b0835c862/merged
overlay                            244G   81G  151G  35% /var/lib/docker/overlay2/78b6063afa5525b531bc3b1ffbc6245e3e856160a39c52825c38ef7c46993cfb/merged
tmpfs                              1.2G  4.0K  1.2G   1% /run/user/1101
 copy

For most users, you will need to find the filesystem mounted on / (root), then check the Avail column to see how much disk space is available.

In the example above, the Filesystem /dev/mapper/ubuntu--vg-ubuntu--lv is mounted on /, and that disk has 151G of disk space available.

We recommend ensuring you have at least 1.5x the size of the table you are looking to optimize available in free disk space.

We also recommend you have a backup of your database stored outside the server, in the event of any failures.

Optimizing your table

Login to your MySQL prompt with the following command:

mysql
 copy

You should see a welcome page, then the prompt should change to mysql>

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7979100
Server version: 8.0.34-0ubuntu0.22.04.1 (Ubuntu)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
 copy

Select your helpdesk's database with the command USE <database_name>;

mysql> USE helpdesk;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 
 copy

Run the table optimization with the command OPTIMIZE TABLE <table_name>;. This may take some time to complete, but should output the following:

mysql> OPTIMIZE TABLE blobs_storage;
+------------------------+----------+----------+-------------------------------------------------------------------+
| Table                  | Op       | Msg_type | Msg_text                                                          |
+------------------------+----------+----------+-------------------------------------------------------------------+
| helpdesk.blobs_storage | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| helpdesk.blobs_storage | optimize | status   | OK                                                                |
+------------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (15.40 sec)
 copy

MySQL should now have rebuilt the table relinquishing all free space retained within. You can check the OPC page to confirm the database and table sizes have been reduced to a normal size, and you can re-run the df -h command to confirm the available space on your disk has increased.