0

I am using percona_mysql_5.6, both master and slave are in sync as terms of replication and table wise records are also same. However there is a difference is the disk space consumed. The slave is consuming relatively more space than master. Even the mysql.conf is same for both master and slave. Any idea why this might happen or any leads that i can check?

Server            Name           Engine Version Row_format  Rows    Avg Data_length Max Index_length    Data_free
Master            MasterTable       InnoDB  10  Compact 148087092   320 47392686080     0   31006982144 6291456 
Slave(10.52.146.189)    SlaveTable  InnoDB  10  Compact 214554824   476 102218530816    0   40179859456 5242880         
3
  • Relatively more space, meaning roughly how much more? 5%? 10%? 25%? Jul 28, 2020 at 21:27
  • slave consumes around 15% more space than master. Jul 29, 2020 at 5:48
  • That looks like 2x (100% more) for data and 1.3x for index. Quite significant. ("Rows" cannot be trusted and "Data_free" is "small" on both.)
    – Rick James
    Aug 3, 2020 at 16:20

1 Answer 1

1

The tables on the Primary and the Replica are maintained separately. All InnoDB tables are B+Trees.

  • A block might need splitting on one but not the other.
  • A long-running SELECT on one might lock rows in such a way that there are more block splits.
  • Small blocks may get recombined, but not necessarily with the same frequency, etc, on both machine.
  • Were all the same ALTERs done on both machines?
  • Which columns of SHOW TABLE STATUS show the most difference?

If it is a few percent difference, the above things might be the reason.

If it is 50%, something strange is going on; we need more details. For starters, which one is larger.

7
  • The difference is about 15% i.e. slave consumes 15% more disk space than master. Alters are done only in master, so it would be the same. For show table status, Rows,Avg_row_length and Data_length are about 50% higher in slave than master. Could you kindly comment what might be the possible cause for this? Jul 29, 2020 at 6:10
  • @SoubhikBanerjee - Another question -- How did you build the Replica? Copying the disk at a low level? mysqldump? Something else? Do all the tables have explicit PRIMARY KEYs?
    – Rick James
    Jul 29, 2020 at 15:30
  • sorry but i did not get your question. I am taking about a cluster with one master and 2 slaves. The slaves replicates the data from master by reading binlogs as part of normal replication process. I am not creating a new slave and dumping the data once. Jul 31, 2020 at 10:50
  • @SoubhikBanerjee - When the Replicas (this is now the preferred term) were originally created, what was the size difference? That is, did the difference occur then? Or has it gradually grown over time? Could you actually show me the pair of SHOWs.
    – Rick James
    Jul 31, 2020 at 16:27
  • there was a difference in space consumption even when it was originally created but certainly it wasn't this much(was less than 3%). This 15% variance has grown over time. below is the show table status Server Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Master MasterTable InnoDB 10 Compact 148087092 320 47392686080 0 31006982144 6291456 Slave(10.52.146.189) SlaveTable InnoDB 10 Compact 214554824 476 102218530816 0 40179859456 5242880 44 53 Aug 3, 2020 at 6:44

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.