Solved

Windows MySql Low CPU Usage

Posted on 2011-02-18
9
992 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
Flexible connectivity for any environment

The KE6900 series can extend and deploy computers with high definition displays across multiple stations in a variety of applications that suit any environment. Expand computer use to stations across multiple rooms with dynamic access.

 
LVL 20

Expert Comment

by:Muhammad Wasif
ID: 34931628
@PiZzL3

Does your UPDATE query using index?
0
 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

685 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