Database Logs

[Rystil Arden]

Okay, so forgive me if I don't know what I'm talking about (probably), but I'm in a course this semester (6.033: Computer Systems Engineering) wherein we learned a little bit about databases. Granted, this was not a course completely on databases, and we didn't actually do any database hacking, but we learned sound principles of database design. Anyways, in the course, we had a long section on fault tolerance wherein we were taught that any database should have a log, stored on a different disk, to be used for recovery in the case that the database becomes corrupt. Just wondering, why don't we have a log for ENWorld? Is this not common practise? It was implied in our class that even system dating back to ARIES in 1992 have been using logs. Just wondering.

 

[Rodrigo Istalindir]

The log files can theoretically be used to re-apply transactions since the last backup. Usually you'll do a full backup, and when it is complete it'll wipe the transaction logs and start them anew. When you restore, it looks at the logs and replays those transactions and the database ends up with nothing lost. (I had to do ths with an Exchange server last year and it's really cool when it works. I also had to do it with another database system and it sucks hard when it doesn't.)

Unfortunately, with remote systems, a lot of times they won't get fully backed up very often (not enough drive space, no easy way to rotate tapes, etc), so the log files get changed to 'circular' and will overwrite themselves after a certain period. This keeps you from running the system into the ground by filling up the hard drive, but it really hurts in disaster situations.

I'm not in the loop here, though, so I don't know how it was actually set up.

 

[jmucchiello]

Actually, "modern" web practice is to use mySQL on web applications and it can be run without transaction support. I don't know if this is true for vBulletin.

 

[Rystil Arden]

Well, if mySQL doesn't provide support for crashes the way a log would, then I contend that 'modern' practise is a step backwards in error tolerance.

 

[Rodrigo Istalindir]

As with most things, it's not that simple. A database application has to be written to 'package' each transaction the proper way, so that if it gets replayed, all the tables it touches are restored to a consistent state. For example, if I make a post here, it has to add the post record in one table, update my post count in the user table, add a record to the subscribed threads table for me, etc. From a system standpoint, it's better to lose all the data in a transaction then to have random bits saved without knowing what or where, so the consistency functionality is crucial.

It can be very trick to do properly, especially for a system that's designed to be end-user customizeable and (essentialy) non-mission-critical. There can also be a non-negligible performance hit if a lot of tables are involved.

MySQL not only would have to support transactions (and it does), but vBulletin would have to take advantage of that, the system would need sufficient space to handle it, etc.

As I posted in another thread, I work with clients that have full-time DBAs making good bucks to manage systems less intensive than ENworld, and they have big budgets and support personnel to do on-site backups, etc. The loss sucks, but given the nature of the beast, not 100% preventable. For a system that isn't tracking large amounts of critical data (or upon which a company's fortunes depend), you make compromises where you can. In any event, even if it had been fully logged, I can't imagine there would have been sufficient space on the system to replay every transaction since last December. The transactions are only supposed to protect you during temporary glitches, or at most between frequent and regular backups.

 

[Rystil Arden]

In any event, even if it had been fully logged, I can't imagine there would have been sufficient space on the system to replay every transaction since last December. The transactions are only supposed to protect you during temporary glitches, or at most between frequent and regular backups.

Well, it would be even better to combine logging with more frequent backups, yes. In fact, more frequent backups are just a good idea in general, or at least checkpoint snapshots so that the log only needs to continue from the checkpoint.

 

[MavrickWeirdo]

My impression from working with DBA's, is that logs are a "black box" process.

For example (back in the old days of "film") when you took a photograph, you didn't know if everything worked right till you developed it.

When you make a backup log, you can't tell if is a "good copy" until you try to use it.

My guess is that logs (on ENWordl) are normally taken every 2 or 3 months, but that the latest log was corrupted, and the Dec. 28th log was the most recent uncorrupted copy.

 

[Rystil Arden]

My impression from working with DBA's, is that logs are a "black box" process.

For example (back in the old days of "film") when you took a photograph, you didn't know if everything worked right till you developed it.

When you make a backup log, you can't tell if is a "good copy" until you try to use it.

My guess is that logs (on ENWordl) are normally taken every 2 or 3 months, but that the latest log was corrupted, and the Dec. 28th log was the most recent uncorrupted copy.

Ah, the logs I'm talking about are a bit different than what you're talking about They are write-ahead logs of all the transactions, rather than the actual snapshot of the database itself.

 

[IronWolf]

MySQL can support transaction logging if you use InnoDB tables. By default though, MySQL uses MyISAM tables which do not have transaction support.

There are various design decisions that are typically made when building up a website or application on a DB. Sometimes performance is more critical than recoverability, other times not. Sometimes you just inherit a site and work with that you have. Some sites can either get away with the lack of transaction support because a site is primarily read-only or the backend architecture allows you to get away with it.

Frequent automated backups are important whether you use transaction based DBs or not. I am sure the issue is being looked at now.

 

[MavrickWeirdo]

Ah, the logs I'm talking about are a bit different than what you're talking about They are write-ahead logs of all the transactions, rather than the actual snapshot of the database itself.

Is that like Audit Tables?