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?

2 Answers 2

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.

4
  • indeed I did a cleanup of the queue, after which the initial size didn't change.
    – Aris
    Jan 29, 2016 at 7:20
  • If you get another "spike", the disk space will get big again.
    – Rick James
    Jan 29, 2016 at 18:59
  • does the extra disk size cause any performance issues?
    – Aris
    Aug 13, 2018 at 13:52
  • 1
    @Aris - "extra disk size" usually incurs only a small performance issue. That is, focus on more costly issues. 164MB vs 7MB probably, but not necessarily will show a performance issue. It depends on the queries, the access pattern, how much is "free" space, etc. etc.
    – Rick James
    Aug 16, 2018 at 5:17
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.

1
  • thanks for your suggestion Rolando, will try the checksum.
    – Aris
    Jan 29, 2016 at 7:22

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.