1

I am trying to move my database from MySQL to RDS MySQL. I do a simple mysqldump to transfer.

I use this query:

SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024),2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = "schema_name";

But the size I see in HeidiSQL for RDS is much smaller.

  • Before = 253MB
  • RDS = 100MB

An example of a table with similar rows, but very different size: enter image description here

Before

enter image description here

RDS

enter image description here

Is there any possible reason, or I am missing something?

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
Aris
  • 113
  • 4

2 Answers2

2

Please note this Pictorial Representation of ibdata1 (from Vadim Tkachenko)

lskx

You have many moving parts inside ibdata1

  • Data Dictionary
  • Double Write Buffer
  • Insert Buffer
  • Rollback Segments
  • Undo Space

Almost 3 years ago, I wrote a post addressing why ibdata1 can grow too fast : How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?

If the database experienced lots of INSERTs, UPDATEs, DELETEs, you will likely have all kinds of fragmentation in ibdata1. This would be due to lots of transactions of varying sizes.

Another aspect would be variable length columns in a table. If the rows in your tables have TEXT fields, data could be bloat due to fragmentation from UPDATEs of such columns. I wrote about this 4.5 years ago : Different MySQL Datafile Sizes After Restoration

What you are seeing does not surprise me.

SUGGESTION

You may want to run CHECKSUM TABLE against source and target tables to verify the content is the same. If checksums do not match after reloading a mysqldump and you have TEXT or any variable columns, then you will have to mysqldump source and target and compare the data from that aspect.

Community
  • 1
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
2

Un-panic.

Your queue had lots of rows inserted and quickly deleted, correct? And now it has very few rows in it? Specifically, about 17K? More importantly, there was, at one point, maybe a million rows? So the tablespace expanded to handle it, but has not shrunken.

The dump dumped only the data for the current 17K rows. The reload loaded only those rows. This "cleaned up" the table.

There are many other reasons why a table and/or its indexes can become bloated and why dump and reload will clean it up. But, unless the current number of rows is significantly less than the peak, the wasted space is rarely more than a factor of 2.

The table_rows for the two machines are not identical because InnoDB only approximates that number. It can sometimes be off by a factor of 2.

Rick James
  • 80,479
  • 5
  • 52
  • 119