4

I am using MySQL and mysqldump v8.0.19 on Windows 10. I have an InnoDB database, old. I use mysqldump (and the --no-create-db option) to dump its tables. I then create a new database, new, and import the dumped tables with

mysql -u myName -p new < old.sql

This procedure seems to work in the sense that the tables in old and new seem to have exactly the same data and exactly the same keys. CHECK TABLE reveals no problems with the tables in new, and commands like SELECT * FROM old.table1 UNION SELECT * FROM new.table1 reveal no discrepancies between the old and new tables.

But for all of that, the new tables are smaller. Specifically, this command shows that they're smaller in both data_length and index_length:

SELECT concat( table_schema, '.', table_name ) table_name,
  concat( round( data_length / ( 1024 *1024 ) , 2 ) , 'M' ) data_length,
  concat( round( index_length / ( 1024 *1024 ) , 2 ) , 'M' ) index_length, 
  concat( round( round( data_length + index_length ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_size
  FROM information_schema.TABLES
  ORDER BY ( data_length + index_length ) DESC;

Why are the restored tables smaller (and should I be concerned)?

A [https://stackoverflow.com/questions/24707770/mysql-database-dump-restores-to-smaller-size](question like this) was asked six years ago on Stack Overflow, but it never got any replies.

3

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.

| improve this answer | |
  • 1
    ANALYZE recomputes the statistics; OPTIMIZE rebuilds the table. – Rick James Nov 21 at 1:05
  • @RickJames, yes, sure you are right, my mistake, fixed – NikitaSerbskiy Nov 21 at 9:41
3

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

| improve this answer | |
3

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.