Database Backup Size Trend...

[iago]

Here are the database backup sizes (for all my databases, the biggest by far being x86) ever since I've been doing an off-site backup (to Newby's computer):

11913233 2006-09-20
11883895 2006-09-21
11925220 2006-09-22
11942180 2006-09-23
11786364 2006-09-24
11671094 2006-09-25
11639622 2006-09-26
11652841 2006-09-27
11624742 2006-09-28
11565242 2006-09-29
11419406 2006-09-30
11481782 2006-10-01
11440649 2006-10-02
11381452 2006-10-03
11231198 2006-10-04
11259756 2006-10-05
11256370 2006-10-06
11262270 2006-10-07
11282502 2006-10-08
11281652 2006-10-09
11380596 2006-10-10
11233194 2006-10-11
11172562 2006-10-12
11166165 2006-10-13
11052575 2006-10-14
11035266 2006-10-15
10977348 2006-10-16
10941582 2006-10-17
10970075 2006-10-18
11009642 2006-10-19
10880674 2006-10-20
10757001 2006-10-21
10723845 2006-10-22

Notice what's funny?  The backup size has a downward trend!  The more posts here, the smaller my backup is! 

Weird?

The only reason I can think of is that more text = less overall entropy = better compression...

[Sidoh]

I would think there would be more entropy.  Even still, I don't think less entropy with lots more text would decrease the size of the entire thing if you're still adding on text.  If you have something that's very random and you add on something less random (without changing the original), I don't think the compressed outcome would be any smaller.

I'm not an expert on compression, but I still don't think that would be the case.  That is weird, though. O_o

Do we have that feature enabled that deletes older posts?

[iago]

No, unless SMF does it automatically, but I'm pretty sure that posts last forever. 

You're right, the idea of increasing both entropy and data size probably doesn't explain it.  But it's the best thing I could think of .... I suppose I can check if the actual uncompressed data is smaller..

And it's true, the uncompressed database dump is actually smaller. 

My question is, why?  The only databases that might have activity are SMF and Joe, and I doubt Joe's has daily activity.  So SMF must be somehow shrinking....

[MyndFyre]

Out of curiousity, why do you do a full backup and not a differential backup?

[Newby]

Out of curiousity, why do you do a full backup and not a differential backup?

Incase his drive decides to die I suppose.

[iago]

Out of curiousity, why do you do a full backup and not a differential backup?

Easier this way.  If something happens, I just run "mysql -u root -p < backup.sql" and everything's back the way it was that day, no trouble.  I don't 'have to worry about one backup file breaking and everything falling apart, or something. 

I do a full database backup, and every month or so I delete the previous month.  I don't need backups for archives or anything.  So in the long term, full backups might take up less room

[Joe]

My database hasn't had activity in around two weeks, I think, except hours logged in (I had my forums set as a homepage tab) and active viewers.

[Sidoh]

Easier this way.  If something happens, I just run "mysql -u root -p < backup.sql" and everything's back the way it was that day, no trouble.  I don't 'have to worry about one backup file breaking and everything falling apart, or something. 

I do a full database backup, and every month or so I delete the previous month.  I don't need backups for archives or anything.  So in the long term, full backups might take up less room

Haha, that's what I do too!

However, I don't delete previous months...

total 7.8G
 32M 2006-07-16-backup.tar.bz2      132M 2006-08-11-backup.tar.bz2       87M 2006-09-05-backup.tar.bz2       85M 2006-09-30-backup.tar.bz2
436K 2006-07-16-sql-backup.tar.bz2  400K 2006-08-11-sql-backup.tar.bz2  476K 2006-09-05-sql-backup.tar.bz2  532K 2006-09-30-sql-backup.tar.bz2
 62M 2006-07-17-backup.tar.bz2       60M 2006-08-12-backup.tar.bz2       94M 2006-09-06-backup.tar.bz2       96M 2006-10-01-backup.tar.bz2
436K 2006-07-17-sql-backup.tar.bz2  400K 2006-08-12-sql-backup.tar.bz2  476K 2006-09-06-sql-backup.tar.bz2  532K 2006-10-01-sql-backup.tar.bz2
 51M 2006-07-18-backup.tar.bz2       56M 2006-08-13-backup.tar.bz2       97M 2006-09-07-backup.tar.bz2       95M 2006-10-02-backup.tar.bz2
436K 2006-07-18-sql-backup.tar.bz2  400K 2006-08-13-sql-backup.tar.bz2  480K 2006-09-07-sql-backup.tar.bz2  540K 2006-10-02-sql-backup.tar.bz2
 51M 2006-07-19-backup.tar.bz2       55M 2006-08-14-backup.tar.bz2       97M 2006-09-08-backup.tar.bz2       96M 2006-10-03-backup.tar.bz2
400K 2006-07-19-sql-backup.tar.bz2  400K 2006-08-14-sql-backup.tar.bz2  480K 2006-09-08-sql-backup.tar.bz2  544K 2006-10-03-sql-backup.tar.bz2
 48M 2006-07-20-backup.tar.bz2       77M 2006-08-15-backup.tar.bz2       97M 2006-09-09-backup.tar.bz2       96M 2006-10-04-backup.tar.bz2
400K 2006-07-20-sql-backup.tar.bz2  404K 2006-08-15-sql-backup.tar.bz2  484K 2006-09-09-sql-backup.tar.bz2  548K 2006-10-04-sql-backup.tar.bz2
 70M 2006-07-21-backup.tar.bz2       75M 2006-08-16-backup.tar.bz2       97M 2006-09-10-backup.tar.bz2       96M 2006-10-05-backup.tar.bz2
400K 2006-07-21-sql-backup.tar.bz2  404K 2006-08-16-sql-backup.tar.bz2  484K 2006-09-10-sql-backup.tar.bz2  492K 2006-10-05-sql-backup.tar.bz2
141M 2006-07-22-backup.tar.bz2       77M 2006-08-17-backup.tar.bz2       97M 2006-09-11-backup.tar.bz2       96M 2006-10-06-backup.tar.bz2
400K 2006-07-22-sql-backup.tar.bz2  404K 2006-08-17-sql-backup.tar.bz2  484K 2006-09-11-sql-backup.tar.bz2  504K 2006-10-06-sql-backup.tar.bz2
109M 2006-07-23-backup.tar.bz2       77M 2006-08-18-backup.tar.bz2       97M 2006-09-12-backup.tar.bz2       96M 2006-10-07-backup.tar.bz2
400K 2006-07-23-sql-backup.tar.bz2  404K 2006-08-18-sql-backup.tar.bz2  488K 2006-09-12-sql-backup.tar.bz2  504K 2006-10-07-sql-backup.tar.bz2
 42M 2006-07-24-backup.tar.bz2       80M 2006-08-19-backup.tar.bz2       97M 2006-09-13-backup.tar.bz2       96M 2006-10-08-backup.tar.bz2
404K 2006-07-24-sql-backup.tar.bz2  420K 2006-08-19-sql-backup.tar.bz2  488K 2006-09-13-sql-backup.tar.bz2  508K 2006-10-08-sql-backup.tar.bz2
129M 2006-07-25-backup.tar.bz2       84M 2006-08-20-backup.tar.bz2       97M 2006-09-14-backup.tar.bz2       96M 2006-10-09-backup.tar.bz2
404K 2006-07-25-sql-backup.tar.bz2  420K 2006-08-20-sql-backup.tar.bz2  492K 2006-09-14-sql-backup.tar.bz2  512K 2006-10-09-sql-backup.tar.bz2
113M 2006-07-26-backup.tar.bz2       84M 2006-08-21-backup.tar.bz2       46M 2006-09-15-backup.tar.bz2       96M 2006-10-10-backup.tar.bz2
404K 2006-07-26-sql-backup.tar.bz2  424K 2006-08-21-sql-backup.tar.bz2  492K 2006-09-15-sql-backup.tar.bz2  516K 2006-10-10-sql-backup.tar.bz2
 41M 2006-07-27-backup.tar.bz2       84M 2006-08-22-backup.tar.bz2       46M 2006-09-16-backup.tar.bz2       96M 2006-10-11-backup.tar.bz2
404K 2006-07-27-sql-backup.tar.bz2  424K 2006-08-22-sql-backup.tar.bz2  496K 2006-09-16-sql-backup.tar.bz2  520K 2006-10-11-sql-backup.tar.bz2
 67M 2006-07-28-backup.tar.bz2       84M 2006-08-23-backup.tar.bz2       94M 2006-09-17-backup.tar.bz2       96M 2006-10-12-backup.tar.bz2
404K 2006-07-28-sql-backup.tar.bz2  428K 2006-08-23-sql-backup.tar.bz2  496K 2006-09-17-sql-backup.tar.bz2  524K 2006-10-12-sql-backup.tar.bz2
7.1M 2006-07-29-backup.tar.bz2       88M 2006-08-24-backup.tar.bz2       93M 2006-09-18-backup.tar.bz2       96M 2006-10-13-backup.tar.bz2
404K 2006-07-29-sql-backup.tar.bz2  432K 2006-08-24-sql-backup.tar.bz2  500K 2006-09-18-sql-backup.tar.bz2  528K 2006-10-13-sql-backup.tar.bz2
 75M 2006-07-31-backup.tar.bz2       88M 2006-08-25-backup.tar.bz2       93M 2006-09-19-backup.tar.bz2       96M 2006-10-14-backup.tar.bz2
404K 2006-07-31-sql-backup.tar.bz2  432K 2006-08-25-sql-backup.tar.bz2  504K 2006-09-19-sql-backup.tar.bz2  528K 2006-10-14-sql-backup.tar.bz2
106M 2006-08-01-backup.tar.bz2       88M 2006-08-26-backup.tar.bz2       94M 2006-09-20-backup.tar.bz2       36M 2006-10-15-backup.tar.bz2
404K 2006-08-01-sql-backup.tar.bz2  424K 2006-08-26-sql-backup.tar.bz2  504K 2006-09-20-sql-backup.tar.bz2  532K 2006-10-15-sql-backup.tar.bz2
 25M 2006-08-02-backup.tar.bz2       88M 2006-08-27-backup.tar.bz2       43M 2006-09-21-backup.tar.bz2       96M 2006-10-16-backup.tar.bz2
404K 2006-08-02-sql-backup.tar.bz2  428K 2006-08-27-sql-backup.tar.bz2  504K 2006-09-21-sql-backup.tar.bz2  536K 2006-10-16-sql-backup.tar.bz2
 78M 2006-08-03-backup.tar.bz2       88M 2006-08-28-backup.tar.bz2       96M 2006-09-22-backup.tar.bz2       96M 2006-10-17-backup.tar.bz2
404K 2006-08-03-sql-backup.tar.bz2  440K 2006-08-28-sql-backup.tar.bz2  508K 2006-09-22-sql-backup.tar.bz2  540K 2006-10-17-sql-backup.tar.bz2
 70M 2006-08-04-backup.tar.bz2       88M 2006-08-29-backup.tar.bz2       40M 2006-09-23-backup.tar.bz2       96M 2006-10-18-backup.tar.bz2
404K 2006-08-04-sql-backup.tar.bz2  452K 2006-08-29-sql-backup.tar.bz2  508K 2006-09-23-sql-backup.tar.bz2  540K 2006-10-18-sql-backup.tar.bz2
104M 2006-08-05-backup.tar.bz2       88M 2006-08-30-backup.tar.bz2       96M 2006-09-24-backup.tar.bz2       96M 2006-10-19-backup.tar.bz2
412K 2006-08-05-sql-backup.tar.bz2  460K 2006-08-30-sql-backup.tar.bz2  512K 2006-09-24-sql-backup.tar.bz2  544K 2006-10-19-sql-backup.tar.bz2
9.9M 2006-08-06-backup.tar.bz2       90M 2006-08-31-backup.tar.bz2       96M 2006-09-25-backup.tar.bz2      9.8M 2006-10-20-backup.tar.bz2
412K 2006-08-06-sql-backup.tar.bz2  464K 2006-08-31-sql-backup.tar.bz2  520K 2006-09-25-sql-backup.tar.bz2  548K 2006-10-20-sql-backup.tar.bz2
 31M 2006-08-07-backup.tar.bz2       79M 2006-09-01-backup.tar.bz2       46M 2006-09-26-backup.tar.bz2       66M 2006-10-21-backup.tar.bz2
412K 2006-08-07-sql-backup.tar.bz2  468K 2006-09-01-sql-backup.tar.bz2  524K 2006-09-26-sql-backup.tar.bz2  548K 2006-10-21-sql-backup.tar.bz2
 72M 2006-08-08-backup.tar.bz2       79M 2006-09-02-backup.tar.bz2       96M 2006-09-27-backup.tar.bz2       96M 2006-10-22-backup.tar.bz2
412K 2006-08-08-sql-backup.tar.bz2  468K 2006-09-02-sql-backup.tar.bz2  524K 2006-09-27-sql-backup.tar.bz2  552K 2006-10-22-sql-backup.tar.bz2
 76M 2006-08-09-backup.tar.bz2       94M 2006-09-03-backup.tar.bz2       86M 2006-09-28-backup.tar.bz2      8.0K homed/
412K 2006-08-09-sql-backup.tar.bz2  472K 2006-09-03-sql-backup.tar.bz2  528K 2006-09-28-sql-backup.tar.bz2  4.0K remote/
 99M 2006-08-10-backup.tar.bz2       94M 2006-09-04-backup.tar.bz2       96M 2006-09-29-backup.tar.bz2       16K web/
412K 2006-08-10-sql-backup.tar.bz2  476K 2006-09-04-sql-backup.tar.bz2  528K 2006-09-29-sql-backup.tar.bz2

[iago]

Do you keep the backups on the same system as the database? 

I do my backups to Newby now.  I also gave Newby access to my domain.  That way, if anything happens to me for whatever reason that I can no longer maintain the forums with no advance notice, he can easily fail over to somewhere else.

[Newby]

I do my backups to Newby now.  I also gave Newby access to my domain.  That way, if anything happens to me for whatever reason that I can no longer maintain the forums with no advance notice, he can easily fail over to somewhere else.

Are you hinting that you plan to disappear/die? If so, I better make plans so incase you do randomly disappear/die I can get the forums up ASAP and make a memorial thread for your disappearance.

[Sidoh]

Do you keep the backups on the same system as the database? 

I do my backups to Newby now.  I also gave Newby access to my domain.  That way, if anything happens to me for whatever reason that I can no longer maintain the forums with no advance notice, he can easily fail over to somewhere else.

Yes.  Same system, different hard drive.  I'm not running anything vitally important to anyone but me, so I don't see the point of sending the database dumps somewhere else.  I could set it up pretty easily to scp them to the VM I have running on my machine here, but I don't really want to!

[iago]

Are you hinting that you plan to disappear/die? If so, I better make plans so incase you do randomly disappear/die I can get the forums up ASAP and make a memorial thread for your disappearance.

I don't have any plans to, but that's not the kind of thing you plan

Yes.  Same system, different hard drive.  I'm not running anything vitally important to anyone but me, so I don't see the point of sending the database dumps somewhere else.  I could set it up pretty easily to scp them to the VM I have running on my machine here, but I don't really want to!

So if you get hacked or the building it's in burns down, you're pwned

But yeah, it's all about what you have to lose.  I'd be rather upset if I lost old data from these forums.

[Nate]

PMs but that would huge numbers of PMs.  And you would all have to be deleting some everyday.

[iago]

Yeah, there would have to be more text being sent and deleted in PMs than text posted to the forum..

[Sidoh]

So if you get hacked or the building it's in burns down, you're pwned

But yeah, it's all about what you have to lose.  I'd be rather upset if I lost old data from these forums.

No.  Because again, I'd be much more concerned with the building burning down.  I'm not too worried about getting hacked, either.  That's not a challenge, by the way.