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.

4 Answers 4

5

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.

2
  • 1
    ANALYZE recomputes the statistics; OPTIMIZE rebuilds the table.
    – Rick James
    Nov 21, 2020 at 1:05
  • @RickJames, yes, sure you are right, my mistake, fixed Nov 21, 2020 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

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.

1

I encountered the same problem and have looked into it. In my case, I'm using this query to compare the total_data_length ,total_index_length, total_size of all table between two database.

SELECT
table_name,
table_schema ,
CONCAT(ROUND(SUM(data_length) / (1024 * 1024), 2), 'M') AS total_data_length,
CONCAT(ROUND(SUM(index_length) / (1024 * 1024), 2), 'M') AS total_index_length,
CONCAT(ROUND(SUM((data_length + index_length)) / (1024 * 1024), 2), 'M') AS total_size
FROM information_schema.TABLES
WHERE
(table_name IN (
SELECT table_name
FROM information_schema.TABLES
WHERE table_schema = 'original'
) AND table_schema = 'new')
OR
(table_name IN (
SELECT table_name
FROM information_schema.TABLES
WHERE table_schema = 'new'
) AND table_schema = 'original')
GROUP BY table_name, table_schema

It indicates some table in original database always have larger capacity with the new database. Even when the data stored in each row of table is completely identical. So based on the answer of @JD and @NikitaSerbskiy, mysqldump perform kind of optimize with the database and it will (and need to) not cause any data loss.

PS: but I think this kind of action seems unnecessary, because when an accident occurs and you need to use the dump file to restore the database but then found out that the size of the restored database is smaller than the original database. You will be extremely panicked.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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