Searching causes exclusive table lock on post

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • eoc_Jason
    Member
    • Dec 2001
    • 61

    Searching causes exclusive table lock on post

    Okay, here's my problem. Running a dual socket woodcrest (4 cores), 2GB of RAM, PHP 5.1.4, MySQL 4.1.20 with fulltext enabled. When I do a search (example: Machine Deck) it will lock the post table so no other concurrent reads (or writes obviously) can be done, thus when a search takes in excess of a minute a ton of queries build up and things start to fall apart shortly thereafter...

    (edit - low-priority-updates semi-fixes the issue allow selects to be processed, but all new insert / updates must wait)

    Examply of the query that causes the lock (even searching just 1 forum takes a long time):

    Code:
    SELECT thread.threadid
    FROM thread AS thread
    INNER JOIN post AS post ON ( thread.threadid = post.threadid ) 
    WHERE MATCH (
    post.title, post.pagetext
    )
    AGAINST (
    '+Machine +Deck'
    IN BOOLEAN
    MODE 
    )
    AND thread.forumid
    IN ( 107 )
    Is there any sort of remedy? Why would a search lock the post table?
    Last edited by eoc_Jason; Wed 16 Aug '06, 8:58pm.
  • eoc_Jason
    Member
    • Dec 2001
    • 61

    #2
    Ah, nevemind, I found the answer to my question after doing a ton of digging around. Apparently fulltext queries start to increase exponentially in time once your forum passes ~3 million posts. Since the fourm has over 4 million then I guess performance is what it should be.

    The only remedy I read is to run a master/slave setup, but even then I'm a little hesitant since what I read was all 'in theory'. If the search holds an exclusive lock then other read queries will still get held until the search completes....

    Comment

    • InfidelMatt
      Member
      • Nov 2005
      • 55
      • 3.5.x

      #3
      This doesn't fix the actual table locking issue but if you set low priority updates it allows the rest of your users to browse as normal while a massive search is being performed. Those trying to submit a post/thread/edit would still have a delay but it isn't server crashing anymore.

      Matt

      Comment

      • eoc_Jason
        Member
        • Dec 2001
        • 61

        #4
        Yeah, thanks for the reply matt, I actually was digging around the mysql site and and found that bit of info (and made the change).

        Things are running much smoother, but I still have the issue of searches taking forever, even worse is the idiot users tend to click the search multiple times if it doesn't respond right away, and that starts to cause a cascade effect.

        I increased the min word length in mysql to 5 and rebuilt the indexs, that decreased the post table index from like 1.9GB down to 750MB-ish...

        My next step is to look at some 3rd party fulltext search plugins for mysql (i.e. Sphinx).

        Comment

        • jasonlitka
          Senior Member
          • Mar 2006
          • 1489
          • 4.0.x

          #5
          I had/have the same problem with one of my sites that has about 3M posts. Adding an extra 2GB of RAM (for a total of 4GB) helped significantly. I also wrote a somewhat-buggy plugin to penalize the users that decided to click "Search" more than once.

          I tried setting up a master/slave setup but ran into problems getting it to work properly. For some reason, "CREATE TABLE", "ALTER TABLE", and "DROP TABLE" statements wern't logged and replicated, and then as soon as I enabled the slave, I started getting '127' errors during searches. Even after repairing the slave (which didn't need it) I still got the errors.

          In any case, I'm interested in what you mentioned about Sphinx. How exactly would it work with vB? From what they claim on their web site, it looks to be wicked-fast...
          Jason Litka - Utter Ramblings

          Comment

          • eoc_Jason
            Member
            • Dec 2001
            • 61

            #6
            Yeah, I'm waiting for an extra 2GB to be added to the box, then I can increase some caching and tmp tables for mysql.

            I posted asking about more details about the improved master / slave support in 3.6.0, and commented about how searching locks the post table and if there is code to prevent errors when a person makes a new thread / post. I will have to see what they say, but again that doesn't really overcome the issue of slow searches. I changed the mysql minimum characters to 5 which helped *some*, I also disabled boolean search which also helped *some*. But there are still some queries that pound the DB, especially when it has to copy them to a tmp table (to disk).

            The most promising thing about sphinx was this page. Basically 7 minutes for a fulltext search vs .23 seconds for sphinx.

            I was going to give it a whirl on my local dev box this weekend seeing what all was involved.

            The only other alternative I can see is a restructuring of how searching would work and the indexes. Like limiting by date or only certain forum sections.

            Comment

            • Joe
              Senior Member
              • May 2000
              • 2435

              #7
              Originally posted by eoc_Jason
              The only other alternative I can see is a restructuring of how searching would work and the indexes. Like limiting by date or only certain forum sections.
              I would love to only index the last 12 months worth of data for search, it seems like it would be an easy feature to add to 3.6.1 or for somebody to hack in.
              Bike Forums.net

              Comment

              • InfidelMatt
                Member
                • Nov 2005
                • 55
                • 3.5.x

                #8
                Originally posted by Joe
                I would love to only index the last 12 months worth of data for search, it seems like it would be an easy feature to add to 3.6.1 or for somebody to hack in.

                If you only want to index the last twelve months why not just purge old threads? If they can't search for them anyway it seems pointless to keep them around!

                Matt

                Comment

                • Dontom
                  Senior Member
                  • Mar 2001
                  • 120

                  #9
                  Originally posted by eoc_Jason
                  The most promising thing about sphinx was this page. Basically 7 minutes for a fulltext search vs .23 seconds for sphinx.
                  Did you try sphinx? what were the results?
                  Internet Bike Community
                  Mountainbike News Mountainbike Forum

                  www.mtb-news.de

                  Comment

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