Results 1 to 3 of 3

Thread: Database hangs

Hybrid View

  1. #1

    Default Database hangs

    Do you think you can integrate dynamics to optionally use sphinx as the search engine? I have so many posts within this addon that I believe is causing my database to hang (copying to tmp table) when someone is trying to search.

    This query I believe is a search query:

    SELECT entries.entryid
    FROM vb3_adv_dyna_entries_pages AS entries_pages
    LEFT JOIN vb3_adv_dyna_entries AS entries ON (entries_pages.entryid = entries.entryid)
    LEFT JOIN vb3_adv_dyna_posts AS posts ON (posts.entryid = entries.entryid)
    LEFT JOIN vb3_adv_dyna_cfields_entries AS customfields ON (entries.entryid = customfields.eid)
    WHERE draft = 0
    AND ( MATCH(entries.title) AGAINST ('+ao* +no* +exorcist*' IN BOOLEAN MODE) OR MATCH(entries_pages.pagetext) AGAINST ('+ao* +no* +exorcist*' IN BOOLEAN MODE) OR MATCH(entries.keywords) AGAINST ('+ao* +no* +exorcist*' IN BOOLEAN MODE) OR MATCH(posts.pagetext) AGAINST ('+ao* +no* +exorcist*' IN BOOLEAN MODE) OR field12 LIKE '%ao%' AND field12 LIKE '%no%' AND field12 LIKE '%exorcist%') AND (posts.visible IS NULL OR posts.visible = 1) AND entries.valid = 1
    ORDER BY entries.dateline ASC LIMIT 500;
    Everytime my server becomes unresponsive, I do a mysql processlist and it shows me a query similar to that. It last a very long time, sometimes up to 30 mins, which locks up my database and no one can do anything until that query dies.

    I used to think it was something else in dynamics that caused my queries to hang, but I think this might finally be it.

  2. #2

    Default

    Searching my dynamics takes at least 11 seconds for any results to show up normally. I am not sure how exactly the queries hang sometimes, causing the entire dynamics to become unresponsive. Then leads to hanging of the entire database.

    I have a lot of fields, would that contribute to the long search? If we can't get sphinx to integrate, can we somehow speed up these slow queries? My members are not used to this since the forum post search only takes .5 secs to generate, and they are giving 11 secs to search dynamics.

  3. #3

    Default

    Ok, I found the culprit for making my database hang. If a user is searching and tick the "posts" as part of the additional options, the query will run forever until the database crashes. I have disabled it for now. I think because of the quality (some posts have over 10,000 characters) and quantity (over 300,000 posts) that's causing the query to hang.

    Is there a solution?

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Installation hangs
    By Varsh in forum Troubleshooting & Problems
    Replies: 1
    Last Post: 11-29-2008, 12:01 PM
  2. Installation Hangs...
    By Andy R in forum Troubleshooting & Problems
    Replies: 1
    Last Post: 08-28-2008, 04:18 PM
  3. Update images hangs?
    By ComputerVitals in forum Troubleshooting & Problems
    Replies: 2
    Last Post: 05-15-2008, 09:19 PM
  4. Install just hangs
    By wintermute in forum Troubleshooting & Problems
    Replies: 3
    Last Post: 08-03-2007, 09:50 AM
  5. installation hangs on do=doimages
    By tpr in forum Troubleshooting / "How do I..." Questions
    Replies: 3
    Last Post: 05-17-2004, 10:01 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •