Not the answer you need?
Register and ask your own question!

Datafiles missing and inconsistent size on Slave host

krishnakrishna ContributorInactive User Role Beginner
Hi Team,

We have 2 way replication enabled on 2 hosts where mysql version is 5.5.23 and replication is absolutely working fine. However, as a precautionary measure we have tried to replicate the Database onto two other hosts using native replication method (mixed).

121G Master DB
121G Slave I (master master enabled DB)
58G Slave-II
69G Slave-III

Master host files:
-rw-r--r-- 1 mysql mysql 37G Jan 8 11:09 abc.ibd
-rw-r--r-- 1 mysql mysql 50G Jan 8 11:09 xyz.ibd

Slave-I files:
-rw-rw---- 1 mysql mysql 37G Jan 8 11:14 abc.ibd
-rw-rw---- 1 mysql mysql 50G Jan 8 11:14 xyz.ibd

Slave-II files:
-rw-rw---- 1 mysql mysql 37G Jan 8 11:18 abc.ibd
NO xyz.ibd file

Slave-III files:
-rw-rw---- 1 mysql mysql 12G Jan 8 11:21 abc.ibd
-rw-rw---- 1 mysql mysql 26G Jan 8 11:21 xyz.ibd

Observed that in one of the slave hosts ibd file related to one table whose size is around 50GB is not showing up, but when we check the record count/records in the table they are showing up fine.

Similarly on the second slave the ibd file size is not matching with that of master, but strange thing is count of both the huge tables are matching with that of master.

Need to understand what went wrong on the slave hosts due to which the files are not showing up and also the inconsistency in the file size.

Best Regards,
Krishna

Comments

  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi krishna;

    What process did you use to setup the new slaves? Definitely not normally for a table to go missing, so likely there is either a kink in your slave backup/restore process or perhaps the table got dropped somehow.

    The file size difference is not too surprising, especially if you did a mysqldump to load the data into the slaves. Since MySQL does not automatically shrink the ibd files when data is deleted, the ibd file size is often much greater than the size of the actual data stored. So when you load a new slave with a logical backup like mysqldump, the slave usually ends up with smaller ibd files because you are just loading in the data that is actually there without the extra space left over on the master from deleted records.

    -Scott
  • krishnakrishna Contributor Inactive User Role Beginner
    Hi Scott,

    Thanks for your reply.

    We use normal replication "Change Master ******" process to setup replication on the slave. If the table got dropped, how could we get the details in the DB is that possible...! Also, when ibd file is not existing do we get the table details as usual but in my scenario we are able to get the record count and details as if we have complete data available.

    At the same time, on slave -III when the file size is quite lesser than the actual files that of master we still see the record count matching. Is it just because we must have purged the data and it could have shrink the ibd file size.

    One more query, the database size is comparatively lower than that of the file size in the data directory.

    1. Master
    File size : 121 GB
    DB size : 61 GB

    2. Slave-I
    File size : 121 GB
    DB size : 61 GB

    3. Slave-II
    File size : 58 GB
    DB size : 50.55

    4. Slave-III
    File size : 69 GB
    DB size : 52.36 GB

    Best Regards,
    Krishna
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi krishna;

    I would suspect that any issues you have setting up a slave would be in the backup/restore phase, not when you are setting up replication itself. Either way, if you believe the slave is missing data, I would just re-seed the slave by taking a fresh backup of an existing slave that you think has all the data or the master itself. Percona has a good guide for setting up a slave from scratch:

    http://www.percona.com/doc/percona-xtrabackup/2.2/howtos/setting_up_replication.html

    As for the table file size differences, it's hard to say without looking at it myself. If the row counts are the same, then likely the difference is due to the fact the there is less "empty" space in the ibd files on some of the servers than others. If you are using innodb_file_per_table, you could optimize the tables in question and then their sizes on disk would likely match (or at least be really close).

    -Scott
  • krishnakrishna Contributor Inactive User Role Beginner
    Hi Scott,

    Thanks for the update.

    Best Regards,
    Krishna
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.