No announcement yet.

Server drags when searching IP addresses

  • Filter
  • Time
  • Show
Clear All
new posts

  • Server drags when searching IP addresses

    Lately whenever I search users by IP address my site really drags and comes to a slow crawl. The forum itself and surfingaround it is fine. Is this a sign of anything in particular?

  • #2
    Yes. If you have a large number of posts, you need to INDEX the IP Address field in the POSTS table. I had this exact issue (over a million posts). IP searches now take about 5 seconds. It took about 30 seconds to index the field in MySQL. I posted a couple of threads on this exact issue a couple months ago. (One of the vB team said that adding an index was hacking, which IMO is not the case. Further, I cannot figure out why the vB developers never indexed the field, since vB is supposed to support "large forums", but the lack of index essentially makes IP searches unusable.)


    • #3
      I think the index itself could slow the forum down, who told you it was hacking your forum O.o?


      • #4
        Dunno Zach. Might have been Steve. The performance hit has been negligible (that's the first question I asked )


        • #5
          It depends on the range of the ip address users use to visit your board, if they are all from within the same ip range or a set of ranges, i believe the problem grows when no one revisits the board.


          • #6
            Zach, there are certain Admin functions (from an IP search standpoint) that search through the ENTIRE *posts* table. It worked fine up to about 500,000 posts, but degraded steadily after that. By the time I hit 1,000,000, searching for an IP brought the board down for 20 minutes. (Using dual Xeon/866 MHz CPU's and 2 Gb RAM)

            Once the index was added, these searches now take about 5-10 seconds.


            • #7
              Interesting I will research this index thingy. As of today I am at:
              Threads: 141,291, Posts: 2,851,439, Members: 35,862

              ...and to my knowledge there's no index there or anything that's not "out of the box" VB 3.0.3. I will look up your post about it (is it tutorial or how-to style?).


              • #8
                It's a discussion with HOW? and answers. Lemmee see if I can find it....

                Here you go:


                Specifically, here's the command:

                ALTER TABLE post ADD INDEX (ipaddress)

                NOTE: Since adding the index, IP searches take about 15-30 seconds max. 1.2 million posts.
                Last edited by Dennis Olson; Thu 14 Oct '04, 12:24pm.


                • #9
                  IP searches bring my board to a grinding halt... and that's with 2.8 million posts.

                  Even with a dual server setup (quad CPU DB server / dual CP web server).
                  Bimmerforums - The Ultimate BMW Forum


                  widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.