IP Search freezes server

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Dennis Olson
    Senior Member
    • Oct 2002
    • 2789

    IP Search freezes server

    A few weeks ago, I'd posted that doing an IP seach ("Search for more IPs for this user") effectively froze my server, due to there being over 1 million posts on my forum. Fair enough.

    I was also told to create an index on the IP addy. But no one ever told me:

    1) Exactly how to do that (table name, field name, and SQL syntax)

    2) What were the ramifications vis-a-vis board performance

    3) Would any CODE CHANGES be required in order to "activate" this new index

    I would appreciate these issues being addressed.

    Thanks
  • Lats
    Senior Member
    • Mar 2002
    • 3671

    #2
    1. ALTER TABLE user ADD INDEX (ipaddress)

    2. Increase in performance when search on IP address.

    3. None required, the database will use the index automatically.
    Lats...

    Comment

    • Dennis Olson
      Senior Member
      • Oct 2002
      • 2789

      #3
      Thanks Lats! One more question - do I have to shut the board down for this, or can I do it "live"? (I assume I have to shut down).

      Comment

      • Lats
        Senior Member
        • Mar 2002
        • 3671

        #4
        Umm, good question.

        At a guess, it should only take around 5 - 10 seconds to perform that action on the number of posts you have, but to be on the safe side, close it down.
        Lats...

        Comment

        • Lats
          Senior Member
          • Mar 2002
          • 3671

          #5
          How good was my guess on this Dennis?
          Lats...

          Comment

          • Dennis Olson
            Senior Member
            • Oct 2002
            • 2789

            #6
            Haven't done it yet. My board is VERY busy, and I'm also mirrioring to another server. I need to run down an issue or two before I perform the SQL. I'm going to ask about the dropping of the board on a separate thread, in hops someone from vB can answer that issue specifically. I'll let you know how it works out...

            Thanks for the info BTW!

            Comment

            • Dennis Olson
              Senior Member
              • Oct 2002
              • 2789

              #7
              To the vB development team - will this change negatively affect server performance?

              Comment

              • Dennis Olson
                Senior Member
                • Oct 2002
                • 2789

                #8
                Pleasse note: After index was installed, atempted an IP search - brought the board to a halt for two hours. What other schema changes are necessary to make this work properly?

                Specifically, the query that croaks the database is the one where you searchfor OTHER MEMBERS using that IP address.

                Comment

                • Zachery
                  Former vBulletin Support
                  • Jul 2002
                  • 59097

                  #9
                  It scans the whole post table. and depending on how large that is, this is where your problem lies.

                  Comment

                  • Dennis Olson
                    Senior Member
                    • Oct 2002
                    • 2789

                    #10
                    I have over a million posts. I need to be able to perform that function. Do I need to index the IPaddress in the post table as well? If so, what are the ramifications of doing that?

                    Comment

                    • Lats
                      Senior Member
                      • Mar 2002
                      • 3671

                      #11
                      Ouch!! That's bad news Dennis.

                      Just in case you're wondering what the command is...
                      Code:
                      ALTER TABLE post ADD INDEX (ipaddress)
                      To remove the other index, and put things back to 'normal'...
                      Code:
                      ALTER TABLE user DROP INDEX ipaddress
                      Lats...

                      Comment

                      • Dennis Olson
                        Senior Member
                        • Oct 2002
                        • 2789

                        #12
                        Thanks Lats. Before I change it, I want to know what issues might arise from the change.

                        Comment

                        • Dennis Olson
                          Senior Member
                          • Oct 2002
                          • 2789

                          #13
                          bump for the day shift....

                          Comment

                          • Dennis Olson
                            Senior Member
                            • Oct 2002
                            • 2789

                            #14
                            bump again....

                            Comment

                            • Scott MacVicar
                              Former vBulletin Developer
                              • Dec 2000
                              • 13286

                              #15
                              I'd close your forum while its being done as it will lock the entire post table as it builds the index. The only problem is that it will take a little longer to insert entries into the Post table as it rebuilds the index.
                              Scott MacVicar

                              My Blog | Twitter

                              Comment

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