1

Have a MySQL (percona, 14.14 Distrib 5.6.28-76.1) machine with a 1TB SSD hosting around 712Gb of mysql data. MySQL has 4 databases, with, say database1, taking major space, 656Gb. Lets call this machine machine1.

Want to rebuild another MySQL DB machine, say, machine2, which has exact same configuration. So took an LVM Snapshot on machine1.

df -h command will show the following output for the MySQL and LVM partitions:

On Machine1, after taking LVM snapshot:

/dev/mapper/vgmysql-mysql_tmp        100G   33M  100G   1% /var/tmp/mysql
/dev/mapper/vgmysql-mysql            900G  713G  188G  79% /var/lib/mysql
/dev/mapper/vgmysql-mysql_snapshot   900G  713G  188G  79% /var/cache/mylvmbackup/mnt/backup
/dev/mapper/vgroot-mysql_backup      905G   33M  905G   1% /var/cache/mylvmbackup/backup

Mounted the SSD partition of machine2 (/var/lib/mysql) onto machine1 using NFS export mount option, and did an rsync from the LVM snapshot to the remote mount. After mounting the remote partition and doing the rsync, df -h showed the following output on machine1.

On Machine1, after rsyncing to Machine2's remote mount:

/dev/mapper/vgmysql-mysql           800G  713G   88G  90% /var/lib/mysql
/dev/mapper/vgmysql-mysql_tmp       100G   33M  100G   1% /var/tmp/mysql
/dev/mapper/vgmysql-mysql_snapshot  800G  712G   89G  89% /var/cache/mylvmbackup/mnt/backup
/dev/mapper/vgroot-mysql_backup     805G   33M  805G   1% /var/cache/mylvmbackup/backup
machine2:/var/lib/mysql             900G  864G   36G  97% /mnt/machine2

So after rsync the total size of data increases by 150Gb. On comparing sizes of each individual files and file counts, all are same. Sizes were checked using ls -l and du command. When checked the partition size on machine2, the following is the output, confirming that disk usage has increased:

On Machine2:

/dev/mapper/vgmysql-mysql_tmp      100G   33M  100G   1% /var/tmp/mysql
/dev/mapper/vgmysql-mysql          900G  864G   36G  97% /var/lib/mysql

Deleted the entire data on machine2, and re-did the rsync, and again the same result. Now, in all their are 4 databases that MySQL is holding, lets call then database1, database2 .... and database4. Each database has its own directory in /var/lib/mysql.

As mentioned earlier, database1 has the biggest size, or around 656Gb. When check the size of database1 directory in /var/lib/mysql on machine2, its around 799G. Rest all directories and files have same size. Checked each file (ibd and frm) and compared them to files on machine1, and each have same size. No extra hidden file.

So deleted all data again, destroyed the Logical Volume (/dev/mapper/vgmysql-mysql) and recreated them. Did rsync again and got same results, disk usage increasing by 150Gb.

Checked for file system (XFS) parameters on both machines, all are same. Used ncdu command, and difference is clear:

Total disk usage: 864.3GiB Apparent size: 713.3GiB Items: 1500

What is causing the disk usage to increase so much? What am I missing here?

  • i wonder how it all goes if you use xtrabackup or mysqldump or mydumper/myloader instead of rsync – jerichorivera Jun 15 '16 at 9:01
1

I'm not sure, but maybe your MySQL is using sparse files

You can try to do your rsync with --sparse or --inplace as explained here : Rsync and sparse files

  • This helped somewhat, in the sense that now going up to 860Gb to 870Gb, it went only to 807Gb, as against the source of 720Gb. RSYNC did not work, but CP did. So Sparse may be one reason, but not the complete? – Gautam Somani May 26 '16 at 18:18
  • Correction, it did not work. Had missed a file while doing the CP, adding it back made the situation same again. Any further clues? – Gautam Somani May 27 '16 at 3:46

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.