Problem

• How does SecureTransport Server InnoDB MySQL database works?
• What is SecureTransport Server InnoDB MySQL database design?
• Why SecureTransport Server InnoDB MySQL database only grows in size even with little data in it?

Resolution

SecureTransport Server uses InnoDB storage engine within its MySQL deployment. Due to its specifics design, InnoDB doesn't shrink its storage file(s). The purpose of this article is to provide more details on this topic from SecureTransport perspective and address questions in this regard. Full information and details on InnoDB engine can be found in MySQL manual on storage engines here.

InnoDB is a multiversion system.

Being multiversion system means that every time an update or delete is performed on a table row, the previous version of the row gets copied to a "special" place called "rollback segment". The original record is written with its new data or marked as deleted. If a transaction rollback or a concurrent SELECT statement is executed, the data would be retrieved from that rollback segment.

There are multiple benefits of using InnoDB tablespace designed this way:

1. Data integrity and foreign key constraints Foreign keys establish a relationship between columns in one table and those in another.
2. Transactions InnoDB tables support transactions. A transaction allows multiple SQL commands to be treated as a single and reliable unit.
3. Row-level locking InnoDB uses row-level rather than table-level locking. If a row is being inserted, updated or deleted, only changes to the same row are held up until that request has completed. Tables that receive more updates than selects may be much faster with InnoDB.

Still, the above-mentioned benefits from InnoDB design come a specific challenge - an InnoDB tables space actually never shrinks.

If within the allocated storage, the tablespace becomes full, a new space would be allocated automatically. The space occupied for rollback information may be reused too, but if the tablespace is full it would be extended and this would be a permanent change.

As per the above, there would be no easy and no built-in mechanism to shrink (decrease) the storage of an InnoDB tablespace.The only practical way that remains to decrease a storage is to drop the associated tablespace and recreate it, while in the process effectively removing the rollback information contained in the original tablespace storage.