Brad Fitzpatrick (bradfitz) wrote in lj_maintenance,
Brad Fitzpatrick
bradfitz
lj_maintenance

Corrupt index talk index; an explanation.

** NOTE: Don't ask tech support questions here. Go here. ***

The index on the legacy talk table went corrupt. That's why comments are down on non-clustered journals. At 3 GB, it's taking awhile to repair.

When you hear the word "corrupt" you probably get paranoid about data loss, but if you know a bit about how databases work you won't be scared.... so I'll explain:

When data is sent to the database it's stored in a data file. This never goes corrupt (at least, I've never seen it in ~7 years of doing this crap). The problem with a data file by itself is that it's slow finding stuff. To find, say, your journal comments you'd have to scan over the whole thing until you found what you wanted. Typical textbook example: Imagine looking up your friend's phone number in the phone book if it were sorted by "time person moved to the city" and not by last name.

So the "magic" part of the databases are the indexes. The key to making any database application fast is creating, maintaining, and using the right indexes.

Problem with indexes is that they more often go corrupt. I've seen this happen over time on a number of popular database engines ... it shouldn't ever happen, but it does, simply because there's so much optimization in there, and tons of corner cases.

Luckily, when an index is corrupt you can either fix it or just delete it and have it rebuilt. This just takes a lot of time. (Imagine sorting 3.1 million rows by hand, 5 times, each time sorting on something different)

So that's what's happening now. It's fixing the last index now.... should be done somewhat shortly.

Another benefit to clusters is that instead of having one massive 3 GB table, we have, say, three 1 GB tables, and if one's index goes corrupt, the fix is 3 times faster and only affects 1/3 of the users. So I guess that's 9 times better, eh? :-)

The cluster conversion is over 1/5th done, but that'll move a ton faster now ... the last few days I've been speeding the conversion process up a ton. It's flying now. I'll post more updates in the future.

** NOTE: Don't ask tech support questions here. Go here. ***
Subscribe
  • Post a new comment

    Error

    Anonymous comments are disabled in this journal

    default userpic

    Your reply will be screened

  • 76 comments
Previous
← Ctrl ← Alt
Next
Ctrl → Alt →
Previous
← Ctrl ← Alt
Next
Ctrl → Alt →