Solved

Windows MySql Low CPU Usage

Posted on 2011-02-18
9
977 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:PiZzL3
  • 5
  • 3
9 Comments
 
LVL 20

Accepted Solution

by:
Muhammad Wasif earned 500 total points
ID: 34929422
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
 
LVL 17

Expert Comment

by:psimation
ID: 34929450
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
 

Assisted Solution

by:PiZzL3
PiZzL3 earned 0 total points
ID: 34929764
@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
 
LVL 20

Expert Comment

by:Muhammad Wasif
ID: 34931628
@PiZzL3

Does your UPDATE query using index?
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:PiZzL3
ID: 34931651
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
 
LVL 20

Expert Comment

by:Muhammad Wasif
ID: 34931661
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
 

Author Comment

by:PiZzL3
ID: 34933346
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
 

Author Comment

by:PiZzL3
ID: 34933762
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
 

Author Closing Comment

by:PiZzL3
ID: 34959211
Thanks for the help!
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now