4

After exporting and re-importing a mysql database, the size of the database on disk appears to be quadrupling in size. Any ideas why this my be?

This is occurring when importing the sql dump right back into exactly the same mysql database. To show what I mean:

  1. Export mysql database: mysqldump -u mysql_user -p database1 > dump.sql
  2. Import mysql database (into same mysql server, on same host): mysql -u mysql_user -p database 2 < dump.sql
  3. Check the database size using:
SELECT table_schema "Database Name",
    sum( data_length + index_length ) / 1024 / 1024 "database size in MB",
    sum( data_free )/ 1024 / 1024 "free space in MB"
FROM information_schema.TABLES
GROUP BY table_schema; 

Which then gives:

+--------------------+---------------------+------------------+
| Database Name      | database size in MB | free space in MB |
+--------------------+---------------------+------------------+
| information_schema |          0.01074219 |       0.00000000 |
| database2          |        978.73327637 |     120.00000000 |
| database1          |        284.76471329 |     217.00003815 |
+--------------------+---------------------+------------------+
3 rows in set (0.52 sec)
  1. Note how the database 2 is 3.5 times bigger than database 1.
  2. If I do a checksum test, I get the same checksum so the data seems to be valid. Just it's taking up much more space on the disk!
mysql> use database1

;Database changed
mysql> checksum table wp_site;
+--------------------+------------+
| Table              | Checksum   |
+--------------------+------------+
| database1.wp_site  | 2263483605 |
+--------------------+------------+
1 row in set (0.00 sec)

mysql> use database2

;Database changed
mysql> checksum table wp_site;
+---------------------+------------+
| Table               | Checksum   |
+---------------------+------------+
| database2.wp_site   | 2263483605 |
+---------------------+------------+
1 row in set (0.00 sec)

So my questions:

  1. Why is this happening?
  2. Is it really taking up more space on the disk, or is this somehow a mis-report?
  3. Will database 2 have performance issues since it's much larger in size (even though the data seems to be the same)?

Note: in reality I'm copying the data over to a new server, not duplicating it into the same server. I'm using the example of the same server here to avoid any other unknowns.

Update

After running mysqlcheck -o -A, the database size did shrink a little, but not that much:

+--------------------+-----------------+------------------+------------------+
| Database Name      | data size in MB | index size in MB | free space in MB |
+--------------------+-----------------+------------------+------------------+
| information_schema |      0.00000000 |       0.01074219 |       0.00000000 |
| database2          |    568.22155762 |     358.74609375 |       6.00000000 |
| database1          |    174.76861954 |     109.99609375 |     215.00003815 |
+--------------------+-----------------+------------------+------------------+
5
  • 2
    Possible duplicate of Different MySQL Datafile Sizes After Restoration
    – topshot
    Commented Apr 25, 2019 at 13:17
  • The only thing I can think of is indexes. They are also identical?
    – Vérace
    Commented Apr 25, 2019 at 13:20
  • Could the old db be using MYISAM and the new one INNODB? Just a stab in the dark!
    – Philᵀᴹ
    Commented Apr 25, 2019 at 13:52
  • I thought about MYISAM and INNODB, but I checked that as well and they are identical (I updated the question).
    – Patrick
    Commented Apr 27, 2019 at 4:24
  • Please provide SHOW TABLE STATUS for each of the databases. And SHOW VARIABLES LIKE 'innodb%';
    – Rick James
    Commented Apr 30, 2019 at 1:28

1 Answer 1

1

Run the following command so that free space is compressed, and indexes checked and optimized:

mysqlcheck -o -A

After that, try to execute the following query:

SELECT table_schema "Database Name",  
sum( data_length) / 1024 "data size in MB", 
sum( index_length) / 1024 "index size in MB", 
sum( data_free )/ 1024 / 1024 "free space in MB" 
FROM information_schema.TABLES GROUP BY table_schema;

Especially check if the index size in MB column is different between the 2 databases, and show us the results.

2
  • I've updated my original question with the output to this (after running mysqlcheck
    – Patrick
    Commented Apr 27, 2019 at 5:01
  • The data and index sizes are roughly proportional, so that doesnt seem to be the problem... maybe it’s a collation issue or different character sets. I would check on those Commented Apr 28, 2019 at 0:18

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.