Whoops!

[photon]

Sorry about that, didn't consider the full implications of what I was about to do, I thought it would be a quick thing but it very well could have take days!

I'll do it some other time and plan ahead a bit.

[Azure]

So what changed?

[HOOT]

Did you say days?

[Yeah_Baby]

Originally Posted by HOOT

Did you say days?

I break into convulsions and a nasty sweat.

[photon]

Originally Posted by Azure

So what changed?

Nothing, I killed the process I was running.

If you want the dirty details, the post table in the database has a field to store the IP address for each post. This is useful in one way because I can check new registrations' IP addresses against existing posts to see if there's a match.

The problem is now with millions of posts, this query can take a bit to run, like 15-20 seconds, and when it's doing that the whole forum tends to freeze up.. so approving accounts actually impacts the forum.

So I was going to add an index on that column which would make the query a lot faster.

Unfortunately since it's so big, it ate up all the temporary disk space. Which isn't a big deal, the process will still complete, but it'll take orders of magnitude longer, so instead of 10 minutes maybe 100 or 1000 minutes.

I'll just wait until I do a forum software upgrade.

[alltherage]

Do you guys ever clean house? Like for the accounts that haven't been active in over 6 months or something, you could send a warning message and then kill the account if no new activity?

Also, I thought you archived older posts in the forum... are we beyond 1M recent posts??

[photon]

Nah, old accounts don't actually have any impact on the system, and having the history is good.. plus some people come along years later and still use their accounts.

We don't archive old posts as of yet, though it has been considered. The problem is some old posts are worth keeping (I'm a pack rat at heart).

I've thought about pruining old posts from the OT forum though.

There are forums with MUCH bigger databases than ours though so hopefully we aren't at that point yet.

[Bobblehead]

Why the heck would an index creation on one column use so many resources?

[photon]

2.5 million rows I guess, when I was running it I was doing a show processlist on it and the status changed to something I didn't recognize, and basically it said it is a fallback method of generating the index that doesn't use a lot of disk space; our tmp partition mustn't have been big enough.