The Winds Have Shifted: Hive is On the Move Again

[Ralle]

After investigating the extreme overload on the server hosting THW we have finally found out what was the cause and fixed it.

You shouldn't get any more server busy messages unless something is very wrong.

For those interested the problem lies in a database table containing cached searches for the resource sections. A cached search is deleted after 60 seconds but after months of deleting searches without using the optimize function of MySQL the table was 900 MB big which is a lot for a table containing 100 rows at peak times. A single select query took >5 seconds to run. After an optimize it ran fine again. We have added a optimize query to the daily scheduled tasks so it should never happen again.

I am so happy it works well again

 

[Anachron]

Oh well, what a nice fix.
I never hard problems with MySQL indexes through, since I have an automatic procedure (just as you have) to clean when needed.

However, I am really glad you fixed the problems.

 

[thelifelessone]

You guys should run a separate SQL server. I'd make the site much fast, I think.
If you ever get a new server, you could use the current one for SQL, and the new one for Apache/PHP (though lighttp is supposed to be faster).

 

[Anachron]

Ralle, have you changed server time?

Check

 

[Ralle]

I haven't touched the server time but it often changes itself

 

[Devine]

Yay. Congrats and thanks, Ralle.

 

[aaron]

yeah, a table can get huge, im glad that you fixed it.

 

[Hakeem]

So I heard you like accidentally the whole search cache.

 

[Rommstein]

That's nice it means search is used by people. D:
Good job !

 

[vAnKaTa_G.G.]

Great job indeed I got the server busy message at least 20 times yesterday. Thank God the problem is solved!!!

 

[The Reborn Devil]

Awesome ^^

 

[Fussiler1]

Ralle for admin!


.. wait




Anyways, good job!

 

[Vengeancekael]

awww the snow theme is gone, but still nice that you've fixed it

 

[YourNameHere]

awww the snow theme is gone

For good, I'm sick of all the snow

Anyway, good fix

 

[Xarwin]

I like to MOVE it MOVE it.

 

[Rui]

I'm glad it's gone. It was really annoying to get one of those messages after I had just logged on.

awww the snow theme is gone, but still nice that you've fixed it

The snow theme won't be gone for me until the cache renews the data, so I can still see it ;P
But is Spring here yet? I think it should remain for a few more days.

 

[Dr Super Good]

There was a snow theme? I must have missed it.

Anyway, I did have a hunch that the whole server is busy thing was some sort of index gone wrong as generally servers use a lot of them and the performance was notably degrading over time.

 

[vAnKaTa_G.G.]

There was a snow theme? I must have missed it.

How can you miss the snow
It was on top of your User CP, New Posts etc.

 

[En_Fuego]

I'm with DSG on this.

On a more related note, I'm happy the Hive is back in tip top shape.

 

[baassee]

I still got the snow ...

 

[Solecompanysole]

Good work ralle Hope it wont happen again XD

 

[Waldbaer]

Very nice to have an admin really caring about the performance of his server. Thanks for investing that time, Ralle!

 

[Nurga]

One Word: Yay!

 

[baassee]

My snow is gone ...

 

[Dr. Boom]

For those interested the problem lies in a database table containing cached searches for the resource sections. A cached search is deleted after 60 seconds but after months of deleting searches without using the optimize function of MySQL the table was 900 MB big which is a lot for a table containing 100 rows at peak times. A single select query took >5 seconds to run. After an optimize it ran fine again. We have added a optimize query to the daily scheduled tasks so it should never happen again.

Seas =)

That was what I said for month

Nah - very good job dude =D

 

[The Panda]

Nice stuff Ralle.

 

[Gamecrafter_DK]

Hehe, yeah, it was really bad for about a week or so... i went complaining to pyre about it ^^

 

[RiotZ]

The way you put it is so artistic "The Winds have shifted: Hive is on the move again" it sounds like hive is a bigass tree or something just sliding through endless dunes of sand XD beautifully put ralle lol

MY RED GEM IS GONE O: O: O: sad face ):

 

[Gamecrafter_DK]

Can't wait for the first of April

 

[Alfagar]

Finally it's gone.