I've been trying to put this off, but have to do something.

[CreationNation]

Heya all,

So I've been trying to put this off, well, I have tried to do it but it failed and I wasted a few hours.

My forum is fairly large: Threads: 152,711, Posts: 3,020,660, Members: 40,212

It's gotten to the point that I had to turn of searching because the server just stalls out. I am happy to see that 3.1 is apparently going to introduce some solutions on the search for large forums! Another thing is I can pretty much no longer delete threads/posts with ease. Sometimes it does it, sometimes not. Sometimes it takes doing it about 5 times for it finally to delete (times out the other times after 30 seconds). Also when I go to prune users posts I get the same kind of results as with the manual delete process.

I purchased this forum from a friend about a year ago and all in all the DB hasn't been regularly maintained as far as running error checks/repairs etc. I have run a repair on post/post index a couple times when I had it go bad on me but that's all. On one issue I was having Eva (George) had recommended turning the forum off and running: mysqlcheck -s -u root -p --all-databases . I did this but it zonked out and just wouldn't complete the process (I was watching via mytop in shell). It just dies out. I even did it late at night off peak hours for my server.

I am looking to what I should do next. I charge my clients a yearly fee to host their forums on my site and it's getting to a scary point where I am afraid many will bail. Can someone suggest a solution? Should I downloadthe entire DB andrun locally on my computer somehow, is there a way to get in there and get this database to be successful with the CHECK / REPAIR process? I'm up for anything at this point.

I should add that with search turned off the forum screams even at peak times so it isn't a matter of my server not baing able to handle this size forum (Dual Xeon 2.4 2 GB ram 2x73 GB SCSI). Every now and then it will have a brief stall and the MySQL processes stack a bit not all in all it runs very awesome with 160-200 users on at any given time.

[Zachery]

In, my own expirence, larger threads have more data so they do take more time to delete and or remove.

I personally limit my threads on my smaller forums to about 500-1000 posts depending on what is going on in that thread and what the topic is.

I've found that for the most part, within 500 posts, people are no longer on topic.
So they are closed and moved to an closed forum that anyone can read.

I believe, Erwin who runs a fairly large forum closes his threads after about 5000 posts (I remember reading it some where, I may be wrong.)


As far as repairing tables, you may want to run though and repair each one, one by one (if its poissible by the command line).

The bigger the table the longer it will take to repair.

Since you are on a single server, are you still storing attachments in the database? If so I would move them out. MySQL was never intended for storing large chunks of biniary data.

[CreationNation]

The one thread I was trying to delete was only two posts long and was posted recently, so wasn't part of a huge thread. Even at that it wouldn't delete (kept timing out).

That's exactly what I was doing for the repair, just focusing on one at a time and it just wouldn't do it.

I don't allow attachments on the forum.

[CreationNation]

Another thing I have noticed in regards to the search...I have it turned off except for myself. Even if I run just one search the forum will hang (in case that is indicitive (sp?) of anything).

Any guidance/help appreciated.

[Steve Machol]

See Freddie's explanation for how the search engine works:

http://www.vbulletin.com/forum/showthread.php?p=831219#post831219

[CreationNation]

Ugh, now I can't even delete users without it timing out.

[splooge]

Just a couple quick ideas. This won't necessarily fix anything but could lead to other paths.

First, try running this: mysqlcheck -s -u root -p --all-databases but without '-s'. -s means 'silent' and doesn't show output. Perhaps if you can tell which database it's freezing on we can narrow something down.

Second, if you haven't modified your my.conf file, find my-huge.conf (my-huge.ini for windows) and rename it to my.conf (my.ini for windows). Restart MySQL. It should have a little more breathing room now.

Good luck! =)

[George L]

need to refresh my memory but you running vB 2.3.x right ? hard to remember all the vB forums i help each day

[CreationNation]

need to refresh my memory but you running vB 2.3.x right ? hard to remember all the vB forums i help each day

No I'm on VB 3.0.6 - Dual Xeon 2.4 2 GB ram 2x73 GB SCSI, here's my server spec thread that is most recent in Server opt. forum: http://www.vbulletin.com/forum/showp...13&postcount=5

Most of that is still fairly accurate (the thread evolved and I tried/did everything you asked in there). I figured this post I made here was borderline server opt. forum / general problems. Actually now that I type it I think it is more something doing with General issue with the DB and what to do about it to fix.

[CreationNation]

Unbelievably I was able to delete a few posts/threads/prune users threads a bit ago. I don't know how or why it worked other than I went to prune a banned users posts and just let it go. It never timed out and never showed the "deleting posts/threads" message but I went to look and the post was gone. Since that worked I tried going in and managing some soft deleted threads and it worked really fast. I'm trying a prune right now and it isn't going at this time (I let the other run 10 minutes before I stopped the page). Just posting in case this info helps.

EDIT: It did same thing, deelted without telling me and I can delete a few posts after that.