0

Our production site runs on a LAMP stack and we have a daily mysql backup that produces a tar.gz file. I recently reduced the size of some tables by deleting old rows after 3 months rather than 6, and the daily tar.gz file got bigger. The next day I removed an inefficient TEXT field and replaced it with a VARCHAR(3) field...and the daily tar.gz file got bigger. After unpacking into folders, all the individual files and folders (including the ibdata file) in the daily backups appear to be the same size - but on a day by day basis the tar.gz files get ~14mb bigger each day. According to the table size info I get from MySQL, I have reduced the size of my db.

Nothing is actually broken here - site is running fine and the db is responsive - but I cannot find any explanation as to why my backups keep getting bigger even though the db is getting smaller and it's frankly doing my head in. If anyone can offer an explanation that would be great.

Improve this question
6
  • 2
    How exactly do you create your backup?
    – tombom
    Oct 6, 2017 at 8:13
  • 1
    Could it be that the data in the database has expanded more than you won on deleting the obsolete stuff by the time you redid those backups? I guess that would explain a lot.
    – Valentin Grégoire
    Oct 6, 2017 at 9:10
  • @fancyPants with inobackupex (percona.com/doc/percona-xtrabackup/LATEST/innobackupex/…)
    – dartacus
    Oct 6, 2017 at 12:18
  • @ValentinGrégoire I did consider this possibility. Looking at the 52 days before I made any changes, the tar.gz file grew in size by just over 1mb each day, which is a plausible amount for new rows created by a day's worth of customer orders, new products in the products table etc. Since the changes it's been ~15mb each day. Dropping a TEXT field and adding a VARCHAR(3) could have had some weird side-effect, I suppose. I also added an index, so I can check if that added on a big whack in size.
    – dartacus
    Oct 6, 2017 at 12:27
  • Update: no, index size on the table in question has been reduced also.
    – dartacus
    Oct 6, 2017 at 13:18

1 Answer 1

Reset to default
1

You are backing up the raw files, not a "dump" of the rows (via mysqldump or Xtrabackup), correct?

When you DELETE, the rows are held onto in case of a ROLLBACK (explicit or due to crash). This may increase the file size.

Data (and index) files never shrink in MySQL without explicit action. In particular, there is no way to shrink ibdata1 without dumping and reloading.

So...

Plan A: Use mysqldump or xtrabackup to extract the rows. Then, if you like, send that through tar and gzip (or whatever).

Plan B: If the tables are "file_per_table", OPTIMIZE TABLE to shrink the .ibd files before the next dump. (You are stuck with the bloated ibdata1.)

Plan C: If not file_per_table, that is, if all the data is in ibdata1, do a one-time dump, rm ibdata1, reload. This is somewhat tricky and somewhat risky. Test before risking the production data.

If you will regularly be purging old data, consider using Partitioning as an efficient way to purge old data. If you intend to keep only 3 months' worth, then partition by week (14 partitions). Details here. It involves DROP PARTITION which will not leave behind stuff like DELETE does.

As for why the "steep increase" did not continue to happen... The deleted rows left "free" space in the tablespace; that was being reused. For now, your dumps will continue to be about the same size. (Note: gzip, alone, will fluctuate in size as the data changes, even if the the input size seems to stay the same. This is a characteristic of compression algorithms.)

Improve this answer
1
  • Correct re raw files. Many thanks. I'm stuck with a non file-per-table production server for at least another year, but hopefully in the meantime I can export and re-import if a suitable maintenance window comes up. Also will look into partitions, as that sounds frankly perfect as a sizeable subset of the db is made up of data we don't need for longer than 3 months or so.
    – dartacus
    Oct 13, 2017 at 7:33

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.