2

We are trying to reclaim file space on our replication server. The space we want to reduce are .ibd files on the slave. We have a simple Master -> Slave (read only) replication setup, for some reason, we have innodb_file_per_table set up on the slave but not the master. So on the slave we have very large .ibd files, but not on the slave, of tables that have already been purged, but have not reduced in size.

Is there a way we can reduce those .ibdfiles on the slave with out dropping the db and re importing it?

Would running optimize table on the master reduce them?

Thanks

  • Unless I am misreading your question, you simply need to do OPTIMIZE TABLE on the Slave. – Rick James Jun 9 '15 at 18:56
1

Running OPTIMIZE TABLE on all the tables in the Master is a very bad idea. Why ???

Since the Master has innodb_file_per_table disabled, running OPTIMIZE TABLE on all the table in the Master would make ibdata1 grow. I discussed this in my old StackOverflow post like 5 years ago : Howto: Clean a mysql InnoDB storage engine?

Can I run OPTIMIZE TABLE ?

Unfortunately, running OPTIMIZE TABLE against an InnoDB table stored in the shared table-space file ibdata1 does two things:

  • Makes the table’s data and indexes contiguous inside ibdata1
  • Makes ibdata1 grow because the contiguous data and index pages are appended to ibdata1

You could run OPTIMIZE TABLE on the Slave as follows:

OPTIMIZE_SCRIPT=/tmp/OptimizeInnoDBTables.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('OPTIMIZE TABLE ',table_schema,'.',table_name,';')"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE engine='InnoDB' ORDER BY (data_length+index_length) DESC"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${OPTIMIZE_SCRIPT}
mysql ${MYSQL_CONN} < ${OPTIMIZE_SCRIPT}

This will shrink every .ibd file. Again, DO NOT RUN THIS ON THE MASTER !!!

GIVE IT A TRY !!!

  • Running this command on the slave but not the Master will not "unsync" the slave with the master? – Roland Jun 8 '15 at 12:54
  • If it matters, replication is row based. – Roland Jun 8 '15 at 13:23
  • Not at all. The command OPTIMIZE TABLE would be effectively be ALTER TABLE tblname ENGINE=InnoDB; followed by ANALYZE TABLE tblname; for every InnoDB table. No data will be affected or lost. You do not have to run STOP SLAVE;. The SQL thread for replication will simply wait until a table it has to process is done with OPTIMIZE TABLE. – RolandoMySQLDBA Jun 8 '15 at 14:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.