Solved

Windows MySql Low CPU Usage

Posted on 2011-02-18
9
999 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Plug and play, no additional software required!

The ATEN UE3310 USB3.1 Gen1 Extender Cable allows users to extend the distance between the computer and USB devices up to 10 m (33 ft). The UE3310 is a high-quality, cost-effective solution for professional environments such as hospitals, factories and business facilities.

 
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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

632 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