why does mysqldump restore to a smaller size?
InnoDB stores records inside pages (by default each page size is equal to 16 KB). For different reasons you may have partially filled pages (for example, after you deleted some records). When you restore database using mysqldump your rebuild tables from scratch and receive less fragmented version of your database. You can use OPTIMIZE TABLE command to defragment table in your old database and release some space.
My guess would be MySqlDump is causing some sort of "empty" reserved space in the tables (and indexes especially) to be eliminated. For example, your indexes previously might not have been 100% filling their pages of data but when moved over with MySqlDump, it might sequentially fill the pages with the indexes. (Compression in an inadvertent sense.)
Honestly, this is a pretty wild guess and I haven't found any documented information to support it yet but will update my answer if I do.
The only thing that may be relevant is the zstd compression level, which defaults to 3 (which is a small level of compression). You can read up on it here: zstd-compression-level
Is the new size about 69% of the size of the old one?
mysqldump
produces a bunch of multi-row ("batch") INSERTs
. They are in PRIMARY KEY
order.
When loading at table, if the rows are in PK order, they is very little "fragmentation" (wasted space) in each block.
A block is 16KB; it contains dozens or hundreds or rows.
The Data is stored in a BTree of those blocks. (Ditto for each secondary Index.)
INSERTs
/DELETEs
/UPDATEs
act on the block(s) where the rows are. If a block gets over-filled, it is "split" into two blocks, each about half full.
After a lot of modifications, blocks settle down to an average of 69% full.
Depending on the details of the dump, the secondary indexes may be built after loading all the data. This is done partially outside MySQL with a big sort. Hence, the index can also be loaded in the desired order, thereby minimizing fragmentation.