Searching IP addresses = Crash of server

[magicfox]

Hi there,

we're running a big forum with 10.000+ users (vBulletin, still version 2.3.0).

When we try to search and compare IP addresses or use the function "who voted" the server sometimes breakes down and reboots automatically. Beside this, the board is really fast, but when we use such features, the game is over.

Is there a way to delete old, no more needed data? For example, old IP addresses or stuff like that, which could cause the server to crash down? Our forums database is 700+ MB big, by the way.

Thanks for your help.

[Drifter Jet]

First, you might want to upgrade..since that forum probably isn't supported (Not sure as of yet.) But the upgrade to 3.5.4 would probably take care of that problem.

[SKNY]

Yea.. I would really upgrade a forum that big...

There has been a lot of improvements over the versions and you would really see from performance improvements, depending on both your server and forum settings..

[magicfox]

Yes, I know about all the advantages. But since we installed a lot of hacks, a straight upgrade would not be possible anymore. :-/

Would it help to delete the "0 postings" users? And aren't there any other things I could do?

[dbembibre]

Add index to post table by ip field

[magicfox]

Thanks for your reply, but could you please tell me more about the HOW?

Would it perhaps make sense to delete all IP adresses? If yes, how?

[Paul M]

Thanks for your reply, but could you please tell me more about the HOW?See https://vborg.vbsupport.ru/showpost.php?p=933575

Would it perhaps make sense to delete all IP adresses? If yes, how?Nope, just add the index as above.

[magicfox]

Thanks a lot!

Is there something I should pay attention to? Temporary disable the board. f.e.? Will the server load increase big time?

And how long might this thing take until it's finished (we have 10.000 users, 20.000 threads and approx. 800.000 postings)?

[orban]

Yeah close the board while you add the index, even better shut down the webserver during that time.

[Wired1]

I would STRONGLY recommend starting a local testing server for vB 3.5.4 (or wait till 3.6.0) and start looking for updated hacks of what you currently have.

[Bulent Tekcan]

ALTER TABLE post ADD INDEX ipaddress


Nope, just add the index as above.


Hi Paul,

Is it any problem for future update ?

Example:Our board still 3.5.4 but I'll update to 3.6.0 when it is available.

Because I have a 240.000 member and 2.000.000 + messages.I have no load problem, but yes sometimes search IP peak up our server.

This trick delete all IP's or only move other tables ? I'm already running fulltextsearch.


Thanks

[magicfox]

How can I delete the stored IP addresses from only the last 6 month ago, or isn't it possible? Perhaps this would speed up the creation of the index.

[magicfox]

Anyone? :(

[Paul M]

Just add the index, no need to shut down anything, and it's fine in any vb version.

[magicfox]

Thanks again, it works great!

Would it make sense to add an second index to the table "USER", since this table also contains many ip addresses? Right now no index for "ipaddress" is defined in "USER".

[Paul M]

You could, but it's not so important, that field is not used much, even in ip searches (and then only in vb 3.5 / 3.6).