SitePoint Sponsor |
|
User Tag List
Results 1 to 10 of 10
Thread: How to reduce mysql CPU usage
-
May 4, 2008, 15:48 #1
- 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!
-
May 4, 2008, 19:32 #2
- 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
-
May 4, 2008, 23:25 #3
- 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();
?>
-
May 5, 2008, 20:13 #4
- 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
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
-
May 5, 2008, 22:34 #5
- 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!
-
May 6, 2008, 06:10 #6
- Join Date
- Feb 2004
- Location
- Tampa, FL (US)
- Posts
- 9,854
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
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
-
May 6, 2008, 07:58 #7
- 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
-
May 7, 2008, 00:31 #8
- 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.
-
May 7, 2008, 06:28 #9
- 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
-
May 7, 2008, 16:45 #10
- 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