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:
- Export mysql database:
mysqldump -u mysql_user -p database1 > dump.sql
- Import mysql database (into same mysql server, on same host):
mysql -u mysql_user -p database 2 < dump.sql
- 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)
- Note how the database 2 is 3.5 times bigger than database 1.
- 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:
- Why is this happening?
- Is it really taking up more space on the disk, or is this somehow a mis-report?
- 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 |
+--------------------+-----------------+------------------+------------------+
SHOW TABLE STATUS
for each of the databases. AndSHOW VARIABLES LIKE 'innodb%';