Question:

In Server > File Uploads I switched Deskpro to use filesystem storage. I'd expect that moving all the attachments and so on from the database to the filesystem would decrease the size of the MySQL database.

However, the database size doesn't seem to have gone down. Is something wrong?

Answer:

The default behaviour for MySQL is that it never releases disk space. Unless you enable the innodb_file_per_table parameter when you first set up the server, it will keep disk space for future use after stored objects are deleted; as a result, the disk space used never decreases, only increases.

This also means that if you move from filesystem to database storage, the size of the database may not increase as much as you expect - MySQL may have reserved disk space that it is not using.

To find a better estimate of the size in MB of a particular table, use a query like this:

SELECT TABLE_NAME, table_rows, round(((data_length + index_length) / 1024 / 1024),2) size
FROM information_schema.TABLES 
WHERE table_schema = 'your_database_name' AND TABLE_TYPE='BASE TABLE' 
ORDER BY data_length DESC

Replace your_database_name with the name of your database. Binary objects, like images/attachments, are found in the blobs_storage table.

After switching to filesystem storage, you may want to reclaim disk space from MySQL by enabling innodb_file_per_table.

In MySQL 5.5 and higher, innodb_file_per_table is dynamic, and can be set ON or OFF using SET GLOBAL. Dynamically changing the value of this parameter requires the SUPER privilege and immediately affects the operation of all connections.

In older versions of MySQL, this parameter is set in the configuration file (my.cnf or my.ini). You must shut down and restart the database server for changes to take effect. (Note that we always recommend running the latest 5.x.x version).