Windows MySql Low CPU Usage

MySql only uses about 4%[Max, usually 2%] of my CPU to process a VERY LARGE cron job.

System/Software Specs:
Quad-Core Intel Core i7 920
6GB DDR3-1600 Ram
4x 320GB hard drives in Raid 0
Windows 7 Ultimate 64-bit
Apache Version : 2.2.11  
PHP Version: 5.3.0  
MySQL Version :5.1.36  

The Task:
Cron Job on a MySql DB consisting about about 250,000 rows.
I use PHP PDO for database access.
Each row will be ranked in several catagories.
For speed purposes, I generate all rankings in mysql using @variables
Example code attached.

I assume that mysql is possibly hitting some kind of memory limit? So my beast of a computer isn't being feed enough information for the task to get done faster? I just think it could take a lot less than 30 minutes in I could get more CPU usage.

Also, when I'm running this cron job, I can't open up any other pages on my site. Why would that be and how do I fix that?  [EDIT: I can navigate PHP MyAdmin all I want though]

Thanks for any help!!

function get_ranking($rank_parameters, $rank_order, $DBH, $test_mode=false) {

	$test_mode == true ? $limter = ' LIMIT 50' : $limter = '';
	
	$query0 = 'SET @rank=0';
	$query1 = 'SELECT @rank:=@rank+1 AS rank, id, rowName, '.$rank_parameters.' FROM tableName ORDER BY '.$rank_order.$limter;
	
	echo $test_mode == true ? $query0.'<br>' : '';
	echo $test_mode == true ? $query1.'<br>' : '';
	
	try {
	
		$DBH->exec($query0); 
		$STH = $DBH->prepare($query1);
		$STH->setFetchMode(PDO::FETCH_ASSOC);
		$STH->execute();
		
		while ($user = $STH->fetch()) {
			$ranks[] = $user;
		}

		echo '<br>get_ranking:: parameters::'.$rank_parameters.' - rank_order::'.$rank_order;
		echo $test_mode == true ? print_r($ranks) : '';
		return !empty($ranks) ? $ranks : false;
		
	} catch (PDOException $e) {
		save_PDO_error_to_file($e);
		return false;
	}	
}

function update_ranking($new_ranks, $config, $db, $DBH) {
		
	foreach ($new_ranks as $key => $value) {
		
		//$db, $id, $col, $val, $DBH
		if (!update_stat_col_val($db, $value['id'], $config['db_rank_field'], $value['rank'], $DBH)) {
			echo ' failed: '.$value['id'].' ';
		}
	}
	
	echo '<br>update_ranking:: '.$config['db_rank_field'].' ';
	return true; 
}

Open in new window

PiZzL3Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Muhammad WasifConnect With a Mentor Commented:
Your cron is not using much resources of MySQL. For effective utilization of MySQL, you need to run multiple instances of this.

Checkout this thread
http://www.experts-exchange.com/Database/MySQL/Q_26803616.html
0
 
psimationCommented:
try to set priority higher for the task in Task Manager and see if that helps? (right click on task in task manager). Also check to make sure the affinity is set for multiple cpu's.
0
 
PiZzL3Connect With a Mentor Author Commented:
@wasifq

You're absolutely right about the ineffective usage of mysql. I'll go ahead and add php to that too.

I ran raw rank queries in PHP MyAdmin and it only took 0.5 seconds to rank everyone.

I believe that the slowest part of my code is row updating. It loops through the result array and updates each row individually.

What would be perfect is to query the rank and update at the same time/same sql statement.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Muhammad WasifCommented:
@PiZzL3

Does your UPDATE query using index?
0
 
PiZzL3Author Commented:
I really don't know. I'm not very knowledgable with SQL. I do know that each row is updated with a separate query. Looping through 250,000 updates several times over is a large task.
0
 
Muhammad WasifCommented:
Post your UPDATE query and the output of the following query

SHOW CREATE TABLE table_name;

Open in new window

Replace table_name with the table name you have in database.
0
 
PiZzL3Author Commented:
I figured out how to cut the slower PHP update loop out of it. Now it only takes a couple seconds at most to update each ranking.

SET @rank=0;
UPDATE dbTable SET rankfd= @rank:=@rank+1 ORDER BY fd2 DESC, fd3 DESC;

Open in new window


I''ve attached an image of what you requested.
showcreate.gif
0
 
PiZzL3Author Commented:
My CPU uses an average of 12% now (during the cron job). It also updates the ranks of all 29 categories in less than 1 minute.
0
 
PiZzL3Author Commented:
Thanks for the help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.