SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2007
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to reduce mysql CPU usage

    I have a PHP script that reads about 100,000 records from a mysql database, does some processing, and then inserts them into a new table. I want this to run every week or so via a cron job. The problem is, when this script runs, mysql takes 100% of my CPU. Any ideas on how I could solve this? Since this is a backup cron job, it doesnt have to be super fast or efficient. It just shouldn't affect my public website.
    Thanks for any help!

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you should figure out how to do the processing in mysql instead of PHP. if you have to resort to temp tables, that will probably be better.

    as an absolute last resort, you can process the changes in small batches instead.

    can you explain what processing you're doing? or post your code?
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2007
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I dont think I can do the processing in mysql in my case. Also, temp tables wont work either. Processing the changes in smaller chunks might be possible, though will require more work to set up. my code is pasted below. Its basically a search indexer, where each search term is given a particular score depending upon certain criteria. The main problem is that theres a lot of data to process...2 nested loops with 50k x 10k entries.

    Thanks for the help!


    PHP Code:
    <?php
    include_once("config.php"); //database connection is created here

    $sql "Select keyword from search_keywords"//this has about 50k rows

    $rs $conn->execute($sql);
    while(!
    $rs->EOF)
    {
        
    $keyword $rs->fields['keyword'];

        
    $sql1 "select ID, title, description from data_entries"//this has about 10k rows
        
    $rs2 $conn->execute($sql1);
        while(!
    $rs2->EOF)
        {
            
    $id $rs2->fields['id'];
            
    $title $rs2->fields['title'];
            
    $description $rs2->fields['description']; 

            
    $score 0;
            if(
    strpos($title$keyword))
                
    $score $score 20;
            if(
    strpos($description$keyword))
                
    $score $score 10;

            if(
    $score 0)
            {
                
    $sql_fin "insert into search_index entryID={$id}, keyword={$keyword}, score={$score}";
                
    $conn->execute($sql_fin);
            }
            
    $rs2->movenext();
        }
        
    $rs->movenext();
    }
    mysql_close();
    ?>

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    that is completely doable in sql:
    Code:
    insert search_index
         ( entryid
         , keyword
         , score )
    select d.id
         , k.keyword
         , sum(case when locate(k.keyword, d.title) > 0  then 20 end) +
           sum(case when locate(k.keyword, d.description) > 0  then 10 end)
      from data_entries d
         , search_keywords k
    but i have to ask why you're doing this because you can do exactly this with FULLTEXT indexes.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  5. #5
    SitePoint Enthusiast
    Join Date
    Dec 2007
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow...I didnt know sql was that powerful! I am currently doing this with FULLTEXT indexes, but the results I'm getting with that are not very accurate in some cases (i want search to be really accurate on the website). The idea is to include more of these checks over time to make the search better.
    By the way, any good sql books on online tutorials you recommend?
    Thanks a lot for all the pointers!

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by smarty_pockets View Post
    I am currently doing this with FULLTEXT indexes, but the results I'm getting with that are not very accurate in some cases (i want search to be really accurate on the website).
    then you probably need to switch to BOOLEAN mode.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    oops, i forgot the group by clause:
    Code:
    insert search_index
         ( entryid
         , keyword
         , score )
    select d.id
         , k.keyword
         , sum(case when locate(k.keyword, d.title) > 0  then 20 end) +
           sum(case when locate(k.keyword, d.description) > 0  then 10 end)
      from data_entries d
         , search_keywords k
    group
        by d.id
         , k.keyword
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  8. #8
    SitePoint Enthusiast
    Join Date
    Dec 2007
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The problem I was facing was that boolean mode worked well with some queries, and not at all with others. Using a regular full text search, the more common search queries (queries that appear more frequently in the fields being searched) worked fine, but i didnt get a lot of results for infrequent search queries. When I changed to boolean mode, the infrequent query results got better, but the regular query results lost their revelance. I guess there is no way to fine tune FULLTEXT searches.

  9. #9
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    what do you mean by "infrequent search queries"?
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  10. #10
    SitePoint Enthusiast
    Join Date
    Dec 2007
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry...I meant queries for uncommon words, that do not occur a lot in the test.


Bookmarks

Posting Permissions

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