Mysql – Database size twice as large after thesql dump and re-import

database-sizedumpimportMySQLmysqldump

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 |
+--------------------+-----------------+------------------+------------------+

Best Answer

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.